1 PACKAGE BODY AMS_Utility_PVT AS
2 /* $Header: amsvutlb.pls 120.5 2006/06/14 17:52:16 dbiswas noship $ */
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 AMS
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 Programs
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 -- 16-Dec-2003 dmvince Added ALIST to get_qual_table_name_and_pk.
50 -- 15-Oct-2004 anchaudh Fixed bug#3935312.
51 -- 05-Oct-2005 mkothari chged unit_of_measure to unit_of_measure_tl
52 -- in get_uom_name method.
53 -- 14-Jun-2006 dbiswas updated validate_locking_rules to return if admin user
54 ---------------------------------------------------------------------
55 -- Added for use by bind_parse.
56 TYPE col_val_rec IS RECORD (
57 col_name VARCHAR2(2000),
58 col_op VARCHAR2(10),
59 col_value VARCHAR2(2000) );
60
61 TYPE col_val_tbl IS TABLE OF col_val_rec INDEX BY BINARY_INTEGER;
62
63 ---------------------------------------------------------------------
64 -- FUNCTION
65 -- bind_parse
66 --
67 -- DESCRIPTION
68 -- Given a string containing the WHERE conditions in a WHERE
69 -- clause, return a tuple of column name and column value.
70 -- HISTORY
71 -- 25-Apr-2000 skarumur Created.
72 -- 26-Apr-2000 choang Modified to handle <> conditions.
73 ---------------------------------------------------------------------
74 AMS_DEBUG_HIGH_ON CONSTANT BOOLEAN := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
75 AMS_DEBUG_LOW_ON CONSTANT BOOLEAN := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
76 AMS_DEBUG_MEDIUM_ON CONSTANT BOOLEAN := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
77
78 PROCEDURE bind_parse (
79 p_string IN VARCHAR2,
80 x_col_val_tbl OUT NOCOPY col_val_tbl
81 );
82
83
84 --======================================================================
85 -- PROCEDURE
86 -- debug_message
87 --
88 -- PURPOSE
89 -- Writes the message to the log file for the spec'd level and module
90 -- if logging is enabled for this level and module
91 --
92 -- HISTORY
93 -- 01-Oct-2003 huili Create.
94 --======================================================================
95 PROCEDURE debug_message (p_log_level IN NUMBER,
96 p_module_name IN VARCHAR2,
97 p_text IN VARCHAR2)
98 IS
99 l_pLog BOOLEAN;
100
101 BEGIN
102 --IF( p_log_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
103
104 l_pLog := (p_log_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL);
105 IF (p_log_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
106 FND_LOG.STRING(p_log_level, p_module_name, p_text);
107 END IF;
108 END debug_message;
109
110
111 --======================================================================
112 -- PROCEDURE
113 -- log_message
114 --
115 -- PURPOSE
116 -- Writes a message to the log file if this level and module is enabled
117 -- The message gets set previously with FND_MESSAGE.SET_NAME,
118 -- SET_TOKEN, etc.
119 -- The message is popped off the message dictionary stack, if POP_MESSAGE
120 -- is TRUE. Pass FALSE for POP_MESSAGE if the message will also be
121 -- displayed to the user later.
122 -- Example usage:
123 -- FND_MESSAGE.SET_NAME(...); -- Set message
124 -- FND_MESSAGE.SET_TOKEN(...); -- Set token in message
125 -- FND_LOG.MESSAGE(..., FALSE); -- Log message
126 -- FND_MESSAGE.ERROR; -- Display message
127 --
128 -- HISTORY
129 -- 01-Oct-2003 huili Create.
130 --======================================================================
131
132 PROCEDURE log_message(p_log_level IN NUMBER,
133 p_module_name IN VARCHAR2,
134 p_RCS_ID IN VARCHAR2 := NULL,
135 p_pop_message IN BOOLEAN DEFAULT NULL)
136 IS
137
138 l_pLog BOOLEAN;
139
140 BEGIN
141 --IF ( p_log_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
142
143 l_pLog := (p_log_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL);
144
145 IF (p_log_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
146 IF p_RCS_ID IS NOT NULL THEN
147 FND_MESSAGE.SET_TOKEN(G_RCS_ID, p_RCS_ID);
148 END IF;
149 FND_LOG.MESSAGE(p_log_level, p_module_name, p_pop_message);
150 END IF;
151 END log_message;
152
153 --======================================================================
154 -- FUNCTION
155 -- logging_enabled
156 --
157 -- PURPOSE
158 -- Return whether logging is enabled for a particular level
159 --
160 -- HISTORY
161 -- 03-Oct-2003 huili Create.
162 --======================================================================
163 FUNCTION logging_enabled (p_log_level IN NUMBER)
164 RETURN BOOLEAN
165 IS
166 BEGIN
167 RETURN (p_log_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL);
168 END;
169
170
171
172 ---------------------------------------------------------------------
173 -- FUNCTION
174 -- check_fk_exists
175 --
176 -- HISTORY
177 -- 05/14/99 cklee Created.
178 -- 25-Apr-2000 choang modified to use bind variables.
179 ---------------------------------------------------------------------
180 FUNCTION check_fk_exists(
181 p_table_name IN VARCHAR2,
182 p_pk_name IN VARCHAR2,
183 p_pk_value IN VARCHAR2,
184 p_pk_data_type IN NUMBER := g_number,
185 p_additional_where_clause IN VARCHAR2 := NULL
186 )
187 RETURN VARCHAR2
188 IS
189
190 l_sql VARCHAR2(4000);
191 l_count NUMBER;
192
193 BEGIN
194 l_sql := 'SELECT 1 FROM DUAL WHERE EXISTS (SELECT 1 FROM ' || UPPER(p_table_name);
195 l_sql := l_sql || ' WHERE ' || UPPER(p_pk_name) || ' = :b1';
196
197 IF p_additional_where_clause IS NOT NULL THEN
198 -- given time, incorporate bind_parse
199 l_sql := l_sql || ' AND ' || p_additional_where_clause;
200 END IF;
201
202 l_sql := l_sql || ')';
203
204 IF (AMS_DEBUG_HIGH_ON) THEN
205 debug_message('SQL statement: '||l_sql);
206 END IF;
207
208 BEGIN
209 EXECUTE IMMEDIATE l_sql INTO l_count
210 USING p_pk_value;
211 EXCEPTION
212 WHEN NO_DATA_FOUND THEN
213 l_count := 0;
214 END;
215
216 IF l_count = 0 THEN
217 RETURN FND_API.g_false;
218 ELSE
219 RETURN FND_API.g_true;
220 END IF;
221
222 END check_fk_exists;
223
224
225 ---------------------------------------------------------------------
226 -- FUNCTION
227 -- check_lookup_exists
228 --
229 -- HISTORY
230 -- 05/14/99 cklee Created.
231 -- 25-Apr-2000 choang Use bind variables.
232 -- 07-jun-2002 sveerave if table name is specifically not passed, changes
233 -- are made to call overloaded procedure.
234 ---------------------------------------------------------------------
235 FUNCTION check_lookup_exists(
236 p_lookup_table_name IN VARCHAR2 := g_ams_lookups,
237 p_lookup_type IN VARCHAR2,
238 p_lookup_code IN VARCHAR2
239 )
240 Return VARCHAR2
241 IS
242
243 l_sql VARCHAR2(4000);
244 l_count NUMBER;
245
246 BEGIN
247
248 IF p_lookup_table_name = g_ams_lookups THEN
249 return check_lookup_exists (
250 p_lookup_type => p_lookup_type
251 , p_lookup_code => p_lookup_code
252 , p_view_application_id => 530
253 );
254 ELSE
255 l_sql := 'SELECT 1 FROM DUAL WHERE EXISTS (SELECT 1 FROM ' || p_lookup_table_name;
256 l_sql := l_sql || ' WHERE LOOKUP_TYPE = :b1';
257 l_sql := l_sql || ' AND LOOKUP_CODE = :b2';
258 l_sql := l_sql || ' AND ENABLED_FLAG = ''Y'')';
259
260 IF (AMS_DEBUG_HIGH_ON) THEN
261 debug_message('SQL statement: '||l_sql);
262 END IF;
263
264 BEGIN
265 EXECUTE IMMEDIATE l_sql INTO l_count
266 USING p_lookup_type, p_lookup_code;
267 EXCEPTION
268 WHEN NO_DATA_FOUND THEN
269 l_count := 0;
270 END;
271 IF l_count = 0 THEN
272 RETURN FND_API.g_false;
273 ELSE
274 RETURN FND_API.g_true;
275 END IF;
276 END IF;
277
278 END check_lookup_exists;
279
280
281
282 ---------------------------------------------------------------------
283 -- FUNCTION
284 -- overloaded check_lookup_exists
285 -- PURPOSE
286 -- This function checks if a lookup_code is valid from fnd_lookups when
287 -- view_application_id is passed in.
288 -- HISTORY
289 -- 07-jun-2002 sveerave created.
290 -- 17-Jun-2002 sveerave Modified cursor to have p_view_app_id as NUMBER
291 ---------------------------------------------------------------------
292 FUNCTION check_lookup_exists(
293 p_lookup_type IN VARCHAR2,
294 p_lookup_code IN VARCHAR2,
295 p_view_application_id IN NUMBER
296 )
297 Return VARCHAR2
298 IS
299 CURSOR cur_check_lookup_exists( p_lookup_type VARCHAR2
300 , p_lookup_code VARCHAR2
301 , p_view_app_id NUMBER) IS
302 SELECT 1 FROM fnd_lookup_values lkup
303 WHERE lkup.LOOKUP_TYPE = p_lookup_type
304 AND lkup.LOOKUP_CODE = p_lookup_code
305 AND lkup.view_application_id = p_view_app_id
306 AND lkup.ENABLED_FLAG = 'Y'
307 AND lkup.language = USERENV('LANG')
308 AND lkup.security_group_id = to_number(decode(substrb(userenv('CLIENT_INFO'),55,1
309 ), ' ', '0'
310 , NULL, '0'
311 , substrb(userenv('CLIENT_INFO'),55,10
312 )
313 )
314 );
315 l_count NUMBER := 0;
316
317 BEGIN
318
319 OPEN cur_check_lookup_exists( p_lookup_type
320 , p_lookup_code
321 , p_view_application_id);
322 FETCH cur_check_lookup_exists INTO l_count;
323 CLOSE cur_check_lookup_exists;
324
325 IF l_count = 0 THEN
326 RETURN FND_API.g_false;
327 ELSE
328 RETURN FND_API.g_true;
329 END IF;
330
331 END check_lookup_exists;
332
333
334 ---------------------------------------------------------------------
335 -- FUNCTION
336 -- check_uniqueness
337 --
338 -- HISTORY
339 -- 05/19/99 cklee Created.
340 -- 25-Apr-2000 choang Use bind_parse to enable use of bind variables.
341 ---------------------------------------------------------------------
342 FUNCTION check_uniqueness(
343 p_table_name IN VARCHAR2,
344 p_where_clause IN VARCHAR2
345 )
346 RETURN VARCHAR2
347 IS
348
349 l_sql VARCHAR2(4000);
350 l_count NUMBER;
351
352 l_bind_tbl col_val_tbl;
353
354 BEGIN
355
356 l_sql := 'SELECT 1 FROM DUAL WHERE EXISTS (SELECT 1 FROM ' || UPPER(p_table_name);
357 -- l_sql := l_sql || ' WHERE ' || p_where_clause;
358
359 bind_parse (p_where_clause, l_bind_tbl);
360
361 --
362 -- choang - 25-Apr-2000
363 -- Support up to 4 WHERE conditions for uniqueness. If
364 -- the number of conditions changes, then must also revise
365 -- the execute portion of the code.
366 IF l_bind_tbl.COUNT <= 4 THEN
367 l_sql := l_sql || ' WHERE ' || l_bind_tbl(1).col_name || ' ' || l_bind_tbl(1).col_op || ' :b1';
368 FOR i IN 2..l_bind_tbl.COUNT LOOP
369 l_sql := l_sql || ' AND ' || l_bind_tbl(i).col_name || ' ' || l_bind_tbl(i).col_op || ' :b' || i;
370 END LOOP;
371 ELSE
372 -- Exceeded the number of conditions supported
373 -- for bind variables.
374 l_sql := l_sql || ' WHERE ' || p_where_clause;
375 END IF;
376
377 l_sql := l_sql || ')';
378
379 IF (AMS_DEBUG_HIGH_ON) THEN
380 debug_message('SQL statement: '||l_sql);
381 END IF;
382
383 --
384 -- choang - 25-Apr-2000
385 -- Modify here if number of WHERE conditions
386 -- supported changes.
387 BEGIN
388 IF l_bind_tbl.COUNT = 1 THEN
389 EXECUTE IMMEDIATE l_sql INTO l_count
390 USING l_bind_tbl(1).col_value;
391 ELSIF l_bind_tbl.COUNT = 2 THEN
392 EXECUTE IMMEDIATE l_sql INTO l_count
393 USING l_bind_tbl(1).col_value, l_bind_tbl(2).col_value;
394 ELSIF l_bind_tbl.COUNT = 3 THEN
395 EXECUTE IMMEDIATE l_sql INTO l_count
396 USING l_bind_tbl(1).col_value, l_bind_tbl(2).col_value, l_bind_tbl(3).col_value;
397 ELSIF l_bind_tbl.COUNT = 4 THEN
398 EXECUTE IMMEDIATE l_sql INTO l_count
399 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;
400 ELSE
401 EXECUTE IMMEDIATE l_sql INTO l_count;
402 END IF;
403 EXCEPTION
404 WHEN NO_DATA_FOUND THEN
405 l_count := 0;
406 END;
407
408 IF l_count = 0 THEN
409 RETURN FND_API.g_true;
410 ELSE
411 RETURN FND_API.g_false;
412 END IF;
413
414 END check_uniqueness;
415
416
417 ---------------------------------------------------------------------
418 -- FUNCTION
419 -- is_Y_or_N
420 --
421 -- HISTORY
422 -- 05/19/99 cklee Created.
423 ---------------------------------------------------------------------
424 FUNCTION is_Y_or_N(
425 p_value IN VARCHAR2
426 )
427 RETURN VARCHAR2
428 IS
429 BEGIN
430 IF p_value = 'Y' or p_value = 'N' THEN
431 RETURN FND_API.g_true;
432 ELSE
433 RETURN FND_API.g_false;
434 END IF;
435 END is_Y_or_N;
436
437
438 ---------------------------------------------------------------------
439 -- PROCEDURE
440 -- debug_message
441 --
442 -- HISTORY
443 -- 10/10/99 holiu Created.
444 -- 13-mar-2002 choang bug 2262529 - g_miss_char is a nil char which
445 -- caused some problems in the java layer; removed
446 -- g_miss_char from the error message.
447 -- 14-mar-2002 choang added text G_MISS_CHAR to be displayed in place
448 -- of nil char for debugging purposes.
449 -- 09-Dec-2002 choang All calls to debug should check for msg level
450 -- before calling; removing the check in the debug
451 -- procedure to avoid reduncy.
452 ---------------------------------------------------------------------
453 PROCEDURE debug_message(
454 p_message_text IN VARCHAR2,
455 p_message_level IN NUMBER := NULL
456 )
457 IS
458 BEGIN
459 FND_MESSAGE.set_name('AMS', 'AMS_API_DEBUG_MESSAGE');
460 FND_MESSAGE.set_token('TEXT', REPLACE (p_message_text, FND_API.G_MISS_CHAR, 'G_MISS_CHAR'));
461 FND_MSG_PUB.add;
462 END debug_message;
463
464
465 ---------------------------------------------------------------------
466 -- PROCEDURE
467 -- error_message
468 --
469 -- HISTORY
470 -- 11/01/99 holiu Created.
471 ---------------------------------------------------------------------
472 PROCEDURE error_message(
473 p_message_name VARCHAR2,
474 p_token_name VARCHAR2 := NULL,
475 P_token_value VARCHAR2 := NULL
476 )
477 IS
478 BEGIN
479 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
480 FND_MESSAGE.set_name('AMS', p_message_name);
481 IF p_token_name IS NOT NULL THEN
482 FND_MESSAGE.set_token(p_token_name, p_token_value);
483 END IF;
484 FND_MSG_PUB.add;
485 END IF;
486 END error_message;
487
488
489 ---------------------------------------------------------------------
490 -- PROCEDURE
491 -- display_messages
492 --
493 -- HISTORY
494 -- 10/26/99 holiu Created.
495 ---------------------------------------------------------------------
496 PROCEDURE display_messages
497 IS
498 l_count NUMBER;
499 l_msg VARCHAR2(2000);
500 BEGIN
501 l_count := FND_MSG_PUB.count_msg;
502 FOR i IN 1 .. l_count LOOP
503 l_msg := FND_MSG_PUB.get(i, FND_API.g_false);
504 -- holiu: remove since adchkdrv does not like it
505 -- DBMS_OUTPUT.put_line('(' || i || ') ' || l_msg);
506 END LOOP;
507 END display_messages;
508
509
510 ---------------------------------------------------------------------
511 -- NAME
512 -- create_log
513 --
514 -- HISTORY
515 -- 09/21/99 ptendulk Created.
516 -- 12-Jan-2000 choang Added autonomous transaction.
517 ---------------------------------------------------------------------
518 PROCEDURE create_log(
519 x_return_status OUT NOCOPY VARCHAR2,
520 p_arc_log_used_by IN VARCHAR2,
521 p_log_used_by_id IN VARCHAR2,
522 p_msg_data IN VARCHAR2,
523 p_msg_level IN NUMBER DEFAULT NULL,
524 p_msg_type IN VARCHAR2 DEFAULT NULL,
525 p_desc IN VARCHAR2 DEFAULT NULL,
526 p_budget_id IN NUMBER DEFAULT NULL,
527 p_threshold_id IN NUMBER DEFAULT NULL,
528 p_transaction_id IN NUMBER DEFAULT NULL,
529 p_notification_creat_date IN DATE DEFAULT NULL,
530 p_activity_log_id IN NUMBER DEFAULT NULL
531 )
532 IS
533 PRAGMA AUTONOMOUS_TRANSACTION;
534 x_rowid VARCHAR2(30);
535 l_act_log_id ams_act_logs.activity_log_id%TYPE;
536 l_log_tran_id ams_act_logs.log_transaction_id%TYPE;
537
538 CURSOR c_log_seq IS
539 SELECT ams_act_logs_s.NEXTVAL,
540 ams_act_logs_transaction_id_s.NEXTVAL
541 FROM DUAL;
542
543 CURSOR c_log(l_my_log_id VARCHAR2) IS
544 SELECT rowid
545 FROM ams_act_logs
546 WHERE activity_log_id = l_my_log_id;
547
548 BEGIN
549
550 -- Standard Start of API savepoint
551 SAVEPOINT Create_act_log;
552
553 -- Initialize API return status to success
554 x_return_status := FND_API.G_RET_STS_SUCCESS;
555
556 --
557 -- API body
558 --
559
560 -- open cursor AND fetch into local variable
561 OPEN c_log_seq;
562 FETCH c_log_seq INTO l_act_log_id,l_log_tran_id ;
563 CLOSE c_log_seq;
564
565
566 INSERT INTO ams_act_logs (
567 activity_log_id
568 -- standard who columns
569 ,last_update_date
570 ,last_updated_by
571 ,creation_date
572 ,created_by
573 ,last_update_login
574 ,object_version_number
575 ,act_log_used_by_id
576 ,arc_act_log_used_by
577 ,log_transaction_id
578 ,log_message_text
579 ,log_message_level
580 ,log_message_type
581 ,description
582 ,budget_id
583 ,threshold_id
584 ,notification_creation_date
585 )
586 VALUES (
587 NVL(p_activity_log_id,l_act_log_id)
588 -- standard who columns
589 ,SYSDATE
590 ,FND_GLOBAL.User_Id
591 ,SYSDATE
592 ,FND_GLOBAL.User_Id
593 ,FND_GLOBAL.Conc_Login_Id
594 ,1 -- Object Version Number
595 ,p_log_used_by_id
596 ,p_arc_log_used_by
597 ,NVL(p_transaction_id,l_log_tran_id)
598 ,p_msg_data
599 ,p_msg_level
600 ,p_msg_type
601 ,p_desc
602 ,p_budget_id
603 ,p_threshold_id
604 ,p_notification_creat_date
605 ) ;
606
607
608 OPEN c_log(l_act_log_id);
609 FETCH c_log INTO x_rowid;
610 IF (c_log%NOTFOUND) THEN
611 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
612 END IF;
613 CLOSE c_log;
614
615 --
616 -- END of API body.
617 --
618
619 COMMIT;
620 EXCEPTION
621
622 WHEN OTHERS THEN
623 ROLLBACK TO create_act_log;
624 x_return_status := FND_API.g_ret_sts_unexp_error;
625
626 END create_log;
627
628
629 ---------------------------------------------------------------------
630 -- PROCEDURE
631 -- get_qual_table_name_and_pk
632 --
633 -- HISTORY
634 -- 05/20/99 tdonohoe Created.
635 -- 10/13/99 ptendulk Removed Parameter p_qual_id;
636 -- Added qualifiers DELI, EVEH;
637 -- Changed the name from Event Offerings to Event Offers
638 -- Changed the name from Event Offerings to Event Offers
639 -- 01/06/99 ptendulk Changed the return Statuses to Standard
640 -- return statuses
641 -- 04/24/00 tdonohoe Added Qualifier 'FCST' Forecast.
642 -- 06/14/00 ptendulk Added qualifier 'OFFR' Offers
643 -- 06/28/00 rchahal Added Qualifier 'FUND' Fund.
644 -- 30-Jan-2001 ptendulk Modified Qualifier table for Schedules.
645 -- 06-Apr-2001 choang Added DIWB, MODL and SCOR in get_qual_table_name_and_pk
646 -- added error message if no valid sys_qual mapped.
647 -- 09-Apr-2001 choang added CELL
648 -- 13-Jun-2001 ptendulk Added EONE
649 -- 15-Jun-2001 choang Changed OFFR to return ams_offers and qp_list_header_id.
650 ---------------------------------------------------------------------
651 PROCEDURE get_qual_table_name_and_pk(
652 p_sys_qual IN VARCHAR2,
653 x_return_status OUT NOCOPY VARCHAR2,
654 x_table_name OUT NOCOPY VARCHAR2,
655 x_pk_name OUT NOCOPY VARCHAR2
656 )
657 IS
658 BEGIN
659 -- initialize return status
660 x_return_status := FND_API.G_RET_STS_SUCCESS;
661
662 IF (p_sys_qual ='CSCH') THEN
663 -- Start of code modified by ptendulk on 30-Jan-2001
664 --x_table_name := 'AMS_CAMPAIGN_SCHEDULES';
665 --x_pk_name := 'CAMPAIGN_SCHEDULE_ID';
666 -- End of code modified by ptendulk on 30-Jan-2001
667 x_table_name := 'AMS_CAMPAIGN_SCHEDULES_B';
668 x_pk_name := 'SCHEDULE_ID';
669 ELSIF (p_sys_qual ='CAMP') THEN
670 -- Start of code modified by ptendulk on 30-Jan-2001
671 --x_table_name := 'AMS_CAMPAIGNS_VL';
672 -- End of code modified by ptendulk on 30-Jan-2001
673 x_table_name := 'AMS_CAMPAIGNS_ALL_B';
674 x_pk_name := 'CAMPAIGN_ID';
675 ELSIF (p_sys_qual ='EVEO') THEN
676 x_table_name := 'AMS_EVENT_OFFERS_VL';
677 x_pk_name := 'EVENT_OFFER_ID';
678 ELSIF (p_sys_qual ='EONE') THEN
679 x_table_name := 'AMS_EVENT_OFFERS_VL';
680 x_pk_name := 'EVENT_OFFER_ID';
681 ELSIF (p_sys_qual ='EVEH') THEN
682 x_table_name := 'AMS_EVENT_HEADERS_VL';
683 x_pk_name := 'EVENT_HEADER_ID';
684 ELSIF (p_sys_qual ='DELV') THEN
685 x_table_name := 'AMS_DELIVERABLES_VL';
686 x_pk_name := 'DELIVERABLE_ID';
687 ELSIF (p_sys_qual ='AMET') THEN
688 x_table_name := 'AMS_ACT_METRICS_ALL';
689 x_pk_name := 'ACTIVITY_METRIC_ID';
690 --=========================================================
691 -- Following line of code is added by ptendulk on 14Jun2000
692 --=========================================================
693 ELSIF (p_sys_qual ='OFFR') THEN --added tdonohoe 04/24/2000
694 x_table_name := 'OZF_OFFERS'; -- anchaudh changed ams_offers to ozf_offers to fix bug#3935312.
695 x_pk_name := 'QP_LIST_HEADER_ID';
696 ELSIF (p_sys_qual ='FCST') THEN --added tdonohoe 04/24/2000
697 x_table_name := 'OZF_ACT_FORECASTS_ALL';
698 x_pk_name := 'FORECAST_ID';
699 ELSIF (p_sys_qual ='FUND') THEN --added rchahal 06/28/2000
700 x_table_name := 'OZF_FUND_DETAILS_V';
701 x_pk_name := 'FUND_ID';
702 ELSIF (p_sys_qual ='PRIC') THEN --added skarumur 12/17/2000
703 x_table_name := 'AMS_PRICE_LIST_ATTRIBUTES';
704 x_pk_name := 'QP_LIST_HEADER_ID';
705 ELSIF (p_sys_qual ='LIST') THEN --added gjoby 03/26/2001
706 x_table_name := 'AMS_LIST_HEADERS_VL';
707 x_pk_name := 'LIST_HEADER_ID';
708 ELSIF (p_sys_qual ='IMPH') THEN --added gjoby 03/26/2001
709 x_table_name := 'AMS_IMP_LIST_HEADERS_VL';
710 x_pk_name := 'IMPORT_LIST_HEADER_ID';
711 ELSIF (p_sys_qual ='SQL') THEN --added gjoby 03/26/2001
712 x_table_name := 'AMS_LIST_QUERIES_ALL';
713 x_pk_name := 'LIST_QUERY_ID';
714 ELSIF p_sys_qual = 'DIWB' THEN
715 x_table_name := 'AMS_DISCOVERER_SQL';
716 x_pk_name := 'DISCOVERER_SQL_ID';
717 ELSIF p_sys_qual = 'MODL' THEN
718 x_table_name := 'AMS_DM_MODELS_ALL_B';
719 x_pk_name := 'MODEL_ID';
720 ELSIF p_sys_qual = 'SCOR' THEN
721 x_table_name := 'AMS_DM_SCORES_ALL_B';
722 x_pk_name := 'SCORE_ID';
723 ELSIF p_sys_qual = 'CELL' THEN
724 x_table_name := 'AMS_CELLS_ALL_B';
725 x_pk_name := 'CELL_ID';
726 ELSIF (p_sys_qual = 'RCAM') THEN
727 x_table_name := 'AMS_CAMPAIGNS_ALL_B';
728 x_pk_name := 'CAMPAIGN_ID';
729 ELSIF p_sys_qual = 'DILG' THEN -- Added dmvincen 03/27/2002
730 x_table_name := 'AMS_DIALOGS_ALL_B';
731 x_pk_name := 'DIALOG_ID';
732 ELSIF p_sys_qual in -- Added dmvincen 03/27/2002
733 ('AMS_COMP_START', 'AMS_COMP_SHOW_WEB_PAGE', 'AMS_COMP_END') THEN
734 x_table_name := 'AMS_DLG_FLOW_COMPS_B';
735 x_pk_name := 'FLOW_COMPONENT_ID';
736 ELSIF p_sys_qual = 'ALIST' THEN
737 x_table_name := 'AMS_ACT_LISTS';
738 x_pk_name := 'ACT_LIST_HEADER_ID';
739 ELSE
740 AMS_Utility_PVT.error_message ('AMS_INVALID_SYS_QUAL', 'SYS_QUALIFIER', p_sys_qual);
741 x_return_status := FND_API.g_ret_sts_unexp_error;
742 x_table_name := NULL;
743 x_pk_name := NULL;
744 END IF;
745
746 END get_qual_table_name_and_pk;
747
748
749 --------------------------------------------------------------------
750 -- NAME
751 -- get_source_code
752 --
753 -- HISTORY
754 -- 08/18/99 tdonohoe Created.
755 --------------------------------------------------------------------
756 PROCEDURE get_source_code(
757 p_activity_type IN VARCHAR2,
758 p_activity_id IN NUMBER,
759 x_return_status OUT NOCOPY VARCHAR2,
760 x_source_code OUT NOCOPY VARCHAR2 ,
761 x_source_id OUT NOCOPY NUMBER
762 )
763 IS
764 BEGIN
765
766 SELECT source_code,source_code_for_id INTO x_source_code,x_source_id
767 FROM ams_source_codes
768 WHERE arc_source_code_for = UPPER(p_activity_type)
769 AND source_code_for_id = UPPER(p_activity_id);
770
771
772
773 IF SQL%NOTFOUND THEN
774 x_return_status := FND_API.G_FALSE;
775 ELSE
776 x_return_status := FND_API.G_TRUE;
777 END IF;
778
779
780 EXCEPTION
781
782 WHEN OTHERS THEN
783 x_source_code := NULL;
784 x_source_id := NULL;
785 x_return_status := FND_API.G_FALSE;
786
787 End;
788
789
790 ---------------------------------------------------------------------
791 -- FUNCTION
792 -- get_object_name
793 --
794 -- HISTORY
795 -- 10/15/99 holiu Created.
796 -- 11/03/99 mpande inserted deliverable,event
797 -- 11/16/99 tdonohoe inserted campaign schedule.
798 -- 09-Dec-1999 choang Changed references of ams_event_offers_all_vl to
799 -- ams_event_offers_vl and ams_event_headers_all_vl
800 -- to ams_event_headers_vl.
801 -- 24-Aug-2000 choang Added FUND
802 -- 28-Sep-2000 choang Added PRTN
803 -- 13-Jun-2001 ptendulk Added EONE
804 -- 15-Jun-2001 choang changed PRNT to PTNR
805 --------------------------------------------------------------------
806 FUNCTION get_object_name(
807 p_sys_arc_qualifier IN VARCHAR2,
808 p_object_id IN NUMBER
809 )
810 RETURN VARCHAR2
811 IS
812
813 l_object_name VARCHAR2(1000);
814
815 CURSOR c_campaign(p_object_id IN NUMBER) IS
816 SELECT campaign_name
817 FROM ams_campaigns_vl
818 WHERE campaign_id = p_object_id;
819
820 --added 11/16/99 tdonohoe
821 CURSOR c_campaign_sched(p_object_id IN NUMBER) IS
822 SELECT c.campaign_name
823 FROM ams_campaigns_vl c,
824 ams_campaign_schedules s
825 WHERE s.campaign_schedule_id = p_object_id
826 AND s.campaign_id = c.campaign_id;
827
828 CURSOR c_deliv(p_object_id IN NUMBER) IS
829 SELECT deliverable_name
830 FROM ams_deliverables_vl
831 WHERE deliverable_id = p_object_id;
832
833 CURSOR c_event_header(p_object_id IN NUMBER) IS
834 SELECT event_header_name
835 FROM ams_event_headers_vl
836 WHERE event_header_id = p_object_id;
837
838 CURSOR c_event_offer(p_object_id IN NUMBER) IS
839 SELECT event_offer_name
840 FROM ams_event_offers_vl
841 WHERE event_offer_id = p_object_id;
842 --- updated by mpande 01/30/2001 to look into ozf_funds_all_vl not ozf_funds_vl
843 CURSOR c_fund (p_object_id IN NUMBER) IS
844 SELECT short_name
845 FROM ozf_funds_all_vl
846 WHERE fund_id = p_object_id;
847
848 CURSOR c_partner (p_object_id IN NUMBER) IS
849 SELECT party_name
850 FROM hz_parties
851 WHERE party_id = p_object_id;
852 BEGIN
853
854 l_object_name := NULL;
855
856 IF p_sys_arc_qualifier IS NULL OR p_object_id IS NULL THEN
857 RETURN l_object_name;
858 END IF;
859
860 IF p_sys_arc_qualifier = 'CAMP' THEN
861 OPEN c_campaign(p_object_id);
862 FETCH c_campaign INTO l_object_name;
863 CLOSE c_campaign;
864 ELSIF p_sys_arc_qualifier = 'CSCH' THEN
865 OPEN c_campaign_sched(p_object_id);
866 FETCH c_campaign_sched INTO l_object_name;
867 CLOSE c_campaign_sched;
868 ELSIF p_sys_arc_qualifier = 'DELI' THEN
869 OPEN c_deliv(p_object_id);
870 FETCH c_deliv INTO l_object_name;
871 CLOSE c_deliv;
872 ELSIF p_sys_arc_qualifier = 'EVEH' THEN
873 OPEN c_event_header(p_object_id);
874 FETCH c_event_header INTO l_object_name;
875 CLOSE c_event_header;
876 ELSIF p_sys_arc_qualifier = 'EVEO' THEN
877 OPEN c_event_offer(p_object_id);
878 FETCH c_event_offer INTO l_object_name;
879 CLOSE c_event_offer;
880 ELSIF p_sys_arc_qualifier = 'EONE' THEN
881 OPEN c_event_offer(p_object_id);
882 FETCH c_event_offer INTO l_object_name;
883 CLOSE c_event_offer;
884 ELSIF p_sys_arc_qualifier = 'FUND' THEN
885 OPEN c_fund (p_object_id);
886 FETCH c_fund INTO l_object_name;
887 CLOSE c_fund;
888 ELSIF p_sys_arc_qualifier = 'PTNR' THEN
889 OPEN c_partner (p_object_id);
890 FETCH c_partner INTO l_object_name;
891 CLOSE c_partner;
892 END IF;
893
894 RETURN l_object_name;
895
896 END get_object_name;
897
898
899 ---------------------------------------------------------------------
900 -- PROCEDURE
901 -- Convert_Currency
902 -- NOTE
903 -- Modified from code done by ptendulk.
904 -- HISTORY
905 -- 08-Dec-1999 choang Created.
906 -- 31-Aug-2000 ptendulk Added x_conversion_type parameter to the
907 -- Convert_Closest_Amount procedure
908 -- 09-Oct-2000 choang Modified error message handling for no rate
909 -- and invalid currency.
910 ---------------------------------------------------------------------
911 PROCEDURE Convert_Currency (
912 x_return_status OUT NOCOPY VARCHAR2,
913 p_from_currency IN VARCHAR2,
914 p_to_currency IN VARCHAR2,
915 p_conv_date IN DATE DEFAULT SYSDATE,
916 p_from_amount IN NUMBER,
917 x_to_amount OUT NOCOPY NUMBER
918 )
919 IS
920 L_CONVERSION_TYPE_PROFILE CONSTANT VARCHAR2(30) := 'AMS_CURR_CONVERSION_TYPE';
921 L_USER_RATE CONSTANT NUMBER := 1; -- Currenty not used.
922 L_MAX_ROLL_DAYS CONSTANT NUMBER := -1; -- Negative so API rolls back to find the last conversion rate.
923 l_denominator NUMBER; -- Not used in Marketing.
924 l_numerator NUMBER; -- Not used in Marketing.
925 l_rate NUMBER; -- Not used in Marketing.
926 l_conversion_type VARCHAR2(30); -- Currency conversion type; see API documention for details.
927 BEGIN
928 -- Initialize return status.
929 x_return_status := FND_API.G_RET_STS_SUCCESS;
930
931 -- Get the currency conversion type from profile option
932 l_conversion_type := FND_PROFILE.Value (L_CONVERSION_TYPE_PROFILE);
933
934 -- Call the proper GL API to convert the amount.
935 GL_Currency_API.Convert_Closest_Amount (
936 x_from_currency => p_from_currency,
937 x_to_currency => p_to_currency,
938 x_conversion_date => p_conv_date,
939 x_conversion_type => l_conversion_type,
940 x_user_rate => L_USER_RATE,
941 x_amount => p_from_amount,
942 x_max_roll_days => L_MAX_ROLL_DAYS,
943 x_converted_amount => x_to_amount,
944 x_denominator => l_denominator,
945 x_numerator => l_numerator,
946 x_rate => l_rate
947 );
948 EXCEPTION
949 WHEN GL_Currency_API.NO_RATE THEN
950 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
951 FND_MESSAGE.Set_Name ('AMS', 'AMS_NO_RATE');
952 FND_MESSAGE.Set_Token ('CURRENCY_FROM', p_from_currency);
953 FND_MESSAGE.Set_Token ('CURRENCY_TO', p_to_currency);
954 FND_MSG_PUB.Add;
955 END IF;
956 x_return_status := FND_API.G_RET_STS_ERROR;
957 WHEN GL_Currency_API.INVALID_CURRENCY THEN
958 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
959 FND_MESSAGE.Set_Name ('AMS', 'AMS_INVALID_CURR');
960 FND_MESSAGE.Set_Token ('CURRENCY_FROM', p_from_currency);
961 FND_MESSAGE.Set_Token ('CURRENCY_TO', p_to_currency);
962 FND_MSG_PUB.Add;
963 END IF;
964 x_return_status := FND_API.G_RET_STS_ERROR;
965 END Convert_Currency;
966
967 ---------------------------------------------------------------------
968 -- PROCEDURE
969 -- get_lookup_meaning
970 -- created by mpande 01/11/00
971 -- PURPOSE
972 -- This procedure will return the meaning from ams_lookups if
973 -- you pass the right lookup_type and lookup_code
974 -- HISTORY
975 -- 28-Apr-2000 choang Modified to use explicit cursor.
976 -- 07-Aug-2000 choang Added close cursor for success conditions
977 -- in the fetch.
978 ---------------------------------------------------------------------
979
980 PROCEDURE get_lookup_meaning (
981 p_lookup_type IN VARCHAR2,
982 p_lookup_code IN VARCHAR2,
983 x_return_status OUT NOCOPY VARCHAR2,
984 x_meaning OUT NOCOPY VARCHAR2
985 )
986 IS
987 CURSOR c_meaning IS
988 SELECT meaning
989 FROM ams_lookups
990 WHERE lookup_type = UPPER (p_lookup_type)
991 AND lookup_code = UPPER (p_lookup_code);
992 BEGIN
993 OPEN c_meaning;
994 FETCH c_meaning INTO x_meaning;
995 IF c_meaning%NOTFOUND THEN
996 CLOSE c_meaning;
997 x_return_status := FND_API.G_RET_STS_ERROR;
998 x_meaning:= NULL;
999 ELSE
1000 CLOSE c_meaning;
1001 x_return_status := FND_API.G_RET_STS_SUCCESS;
1002 END IF;
1003 EXCEPTION
1004 WHEN OTHERS THEN
1005 IF c_meaning%ISOPEN THEN
1006 CLOSE c_meaning;
1007 END IF;
1008 x_return_status := FND_API.G_RET_STS_ERROR;
1009 x_meaning := NULL;
1010 END get_lookup_meaning;
1011
1012
1013 ---------------------------------------------------------------------
1014 -- PROCEDURE
1015 -- get_System_Timezone
1016 --
1017 -- PURPOSE
1018 -- This procedure will return the timezone from the System Timezone profile option
1019 -- HISTORY created 04/24/2000 sugupta
1020 -- 17-May-2002 choang bug 2224836: changed to use SERVER_TIMEZONE_ID
1021 -- 16-Dec-2005 prageorg Bug 4761850: Changed HZ_TIMEZONES_VL to FND_TIMEZONES_VL
1022 ---------------------------------------------------------------------
1023 PROCEDURE get_System_Timezone(
1024
1025 x_return_status OUT NOCOPY VARCHAR2,
1026 x_sys_time_id OUT NOCOPY NUMBER,
1027 x_sys_time_name OUT NOCOPY VARCHAR2
1028 ) IS
1029
1030 l_sys_time_id NUMBER;
1031 l_sys_name VARCHAR2(80);
1032
1033 cursor c_get_name(l_time_id IN NUMBER) is
1034 select NAME
1035 from FND_TIMEZONES_VL
1036 where UPGRADE_TZ_ID = l_time_id;
1037
1038 BEGIN
1039 -- Initialize API return status to success
1040 x_return_status := FND_API.G_RET_STS_SUCCESS;
1041 l_sys_time_id := FND_PROFILE.VALUE('SERVER_TIMEZONE_ID');
1042 OPEN c_get_name(l_sys_time_id);
1043 FETCH c_get_name into l_sys_name;
1044 IF (c_get_name%NOTFOUND) THEN
1045 CLOSE c_get_name;
1046 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1047 return;
1048 END IF;
1049 CLOSE c_get_name;
1050
1051 x_sys_time_id := l_sys_time_id;
1052 x_sys_time_name := l_sys_name;
1053 EXCEPTION
1054 WHEN NO_DATA_FOUND THEN
1055 IF (c_get_name%ISOPEN) THEN
1056 CLOSE c_get_name;
1057 END IF;
1058 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1059 END get_System_Timezone;
1060
1061 ---------------------------------------------------------------------
1062 -- PROCEDURE
1063 -- get_User_Timezone
1064 --
1065 -- PURPOSE
1066 -- This procedure will return the timezone from the User Timezone profile option
1067 -- HISTORY created 04/24/2000 sugupta
1068 -- 17-May-2002 choang bug 2224836: changed to use CLIENT_TIMEZONE_ID
1069 -- 16-Dec-2005 prageorg Bug 4761850: Changed HZ_TIMEZONES_VL to FND_TIMEZONES_VL
1070 ---------------------------------------------------------------------
1071 PROCEDURE get_User_Timezone(
1072
1073 x_return_status OUT NOCOPY VARCHAR2,
1074 x_user_time_id OUT NOCOPY NUMBER,
1075 x_user_time_name OUT NOCOPY VARCHAR2
1076 ) IS
1077
1078 l_user_time_id NUMBER;
1079 l_user_time_name VARCHAR2(80);
1080
1081 cursor get_name(l_time_id IN NUMBER) is
1082 select NAME
1083 from FND_TIMEZONES_VL
1084 where UPGRADE_TZ_ID = l_time_id;
1085
1086 BEGIN
1087 -- Initialize API return status to success
1088 x_return_status := FND_API.G_RET_STS_SUCCESS;
1089 l_user_time_id := FND_PROFILE.VALUE('CLIENT_TIMEZONE_ID');
1090 OPEN get_name(l_user_time_id);
1091 FETCH get_name into l_user_time_name;
1092 IF (get_name%NOTFOUND) THEN
1093 CLOSE get_name;
1094 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1095 return;
1096 END IF;
1097 CLOSE get_name;
1098
1099 x_user_time_id := l_user_time_id;
1100 x_user_time_name := l_user_time_name;
1101 EXCEPTION
1102 WHEN NO_DATA_FOUND THEN
1103 IF (get_name%ISOPEN) THEN
1104 CLOSE get_name;
1105 END IF;
1106 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1107 END get_User_Timezone;
1108
1109 -------------------------------------------------------------------------------------------------
1110 -- PROCEDURE
1111 -- Convert_Timezone
1112 --
1113 -- PURPOSE
1114 -- This procedure will take the user timezone and the input time, depending on the parameter
1115 -- p_convert_type it will convert the input time to System timezone or sent Usertimezone
1116 -- HISTORY
1117 -- 04/24/2000 sugupta created
1118 -- 04/26/2000 ptendulk Modified Added a parameter which will tell
1119 -- which timezone to convert time into.
1120 -- If the convert type is 'SYS' then input time will be
1121 -- converted into system timezone else it will be
1122 -- converted to user timezone .
1123 ---------------------------------------------------------------------------------------------------
1124 PROCEDURE Convert_Timezone(
1125 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1126 x_return_status OUT NOCOPY VARCHAR2,
1127 x_msg_count OUT NOCOPY NUMBER,
1128 x_msg_data OUT NOCOPY VARCHAR2,
1129
1130 p_user_tz_id IN NUMBER := null,
1131 p_in_time IN DATE , -- required
1132 p_convert_type IN VARCHAR2 := 'SYS' , -- (SYS/USER)
1133
1134 x_out_time OUT NOCOPY DATE
1135 ) IS
1136
1137 l_sys_time_id NUMBER;
1138 l_user_tz_id NUMBER := p_user_tz_id ;
1139 l_sys_time_name VARCHAR2(80);
1140 l_user_time_name VARCHAR2(80);
1141 l_return_status VARCHAR2(1); -- Return value from procedures
1142
1143 l_from_timezone_id NUMBER ;
1144 l_to_timezone_id NUMBEr ;
1145 BEGIN
1146
1147 -- Initialize API return status to success
1148 x_return_status := FND_API.G_RET_STS_SUCCESS;
1149
1150 get_System_Timezone(
1151 l_return_status,
1152 l_sys_time_id,
1153 l_sys_time_name);
1154
1155 IF (l_return_status = FND_API.G_RET_STS_ERROR OR
1156 l_return_status = FND_API.G_RET_STS_UNEXP_ERROR )
1157 THEN
1158 x_return_status := l_return_status;
1159 RETURN;
1160 END IF;
1161
1162 -- If the user timezone is not sent
1163 -- get it from profiles
1164 IF l_user_tz_id IS NULL THEN
1165 Get_User_Timezone(
1166 x_return_status => l_return_status,
1167 x_user_time_id => l_user_tz_id ,
1168 x_user_time_name => l_user_time_name
1169 ) ;
1170 END IF;
1171
1172 IF (l_return_status = FND_API.G_RET_STS_ERROR OR
1173 l_return_status = FND_API.G_RET_STS_UNEXP_ERROR )
1174 THEN
1175 x_return_status := l_return_status;
1176 RETURN;
1177 END IF;
1178
1179 IF p_convert_type = 'SYS' THEN
1180 l_from_timezone_id := l_user_tz_id ;
1181 l_to_timezone_id := l_sys_time_id ;
1182 ELSIF p_convert_type = 'USER' THEN
1183 l_from_timezone_id := l_sys_time_id ;
1184 l_to_timezone_id := l_user_tz_id ;
1185 END IF;
1186
1187 HZ_TIMEZONE_PUB.get_time(
1188 p_api_version => 1.0,
1189 p_init_msg_list => p_init_msg_list,
1190 p_source_tz_id => l_from_timezone_id ,
1191 p_dest_tz_id => l_to_timezone_id ,
1192 p_source_day_time => p_in_time,
1193 x_dest_day_time => x_out_time,
1194 x_return_status => x_return_status,
1195 x_msg_count => x_msg_count,
1196 x_msg_data => x_msg_data
1197 );
1198
1199 END Convert_Timezone ;
1200
1201
1202 ---------------------------------------------------------------------
1203 -- PROCEDURE
1204 -- bind_parse
1205 -- USAGE
1206 -- bind_parse (varchar2, col_val_tbl);
1207 -- The input string must have a space between the AND and operator clause
1208 -- and it must exclude the initial WHERE/AND statement.
1209 -- Example: source_code = 'xyz' and campaign_id <> 1
1210 ---------------------------------------------------------------------
1211 PROCEDURE bind_parse (
1212 p_string IN VARCHAR2,
1213 x_col_val_tbl OUT NOCOPY col_val_tbl)
1214 IS
1215 l_new_str VARCHAR2(4000);
1216 l_str VARCHAR2(4000) := p_string;
1217 l_curr_pos NUMBER; -- the position index of the operator string
1218 l_eq_pos NUMBER;
1219 l_not_pos NUMBER;
1220 l_and_pos NUMBER;
1221 i NUMBER := 1;
1222 BEGIN
1223 LOOP
1224 l_and_pos := INSTR (UPPER (l_str), ' AND ');
1225 -- handle condition where no more AND's are
1226 -- left -- usually if only one condition or
1227 -- the last condition in the WHERE clause.
1228 IF l_and_pos = 0 THEN
1229 l_new_str := l_str;
1230 ELSE
1231 l_new_str := SUBSTR (l_str, 1, l_and_pos - 1);
1232 END IF;
1233
1234 --
1235 -- The operator should also be passed
1236 -- back to the calling program.
1237 l_eq_pos := INSTR (l_new_str, '=');
1238 l_not_pos := INSTR (l_new_str, '<>');
1239 --
1240 -----------------------------------
1241 -- operator equal not equal
1242 -- error 0 0
1243 -- = 1 0
1244 -- <> 0 1
1245 -- = 1 2
1246 -- <> 2 1
1247 -----------------------------------
1248 IF l_eq_pos = 0 AND l_not_pos = 0 THEN
1249 -- Could not find either an = or an <>
1250 -- operator.
1251 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1252 FND_MESSAGE.set_name('AMS', 'AMS_UTIL_NO_WHERE_OPERATOR');
1253 FND_MSG_PUB.add;
1254 RAISE FND_API.g_exc_unexpected_error;
1255 END IF;
1256 ELSIF l_eq_pos > 0 AND l_not_pos = 0 THEN
1257 l_curr_pos := l_eq_pos;
1258 x_col_val_tbl(i).col_op := '=';
1259 ELSIF l_not_pos > 0 AND l_eq_pos = 0 THEN
1260 l_curr_pos := l_not_pos;
1261 x_col_val_tbl(i).col_op := '<>';
1262 ELSIF l_eq_pos < l_not_pos THEN
1263 l_curr_pos := l_eq_pos;
1264 x_col_val_tbl(i).col_op := '=';
1265 ELSE
1266 l_curr_pos := l_not_pos;
1267 x_col_val_tbl(i).col_op := '<>';
1268 END IF;
1269
1270 x_col_val_tbl(i).col_name := UPPER (LTRIM (RTRIM (SUBSTR (l_new_str, 1, l_curr_pos - 1))));
1271 -- Add 2 to the current position for '<>'.
1272 x_col_val_tbl(i).col_value := LTRIM (RTRIM (SUBSTR (l_new_str, l_curr_pos + 2)));
1273 --
1274 -- Remove the single quotes from the begin and end of the string value;
1275 -- no action if a numeric value.
1276 IF INSTR (x_col_val_tbl(i).col_value, '''', 1) = 1 THEN
1277 x_col_val_tbl(i).col_value := SUBSTR (x_col_val_tbl(i).col_value,2);
1278 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);
1279 END IF;
1280
1281 IF l_and_pos = 0 THEN
1282 EXIT; -- no more to parse
1283 END IF;
1284
1285 l_str := SUBSTR (l_str, l_and_pos + 4);
1286 i := i + 1;
1287 END LOOP;
1288 END bind_parse;
1289
1290
1291 ---------------------------------------------------------------------
1292 -- FUNCTION
1293 -- get_lookup_meaning
1294 -- USAGE
1295 -- Example:
1296 -- SELECT AMS_Utility_PVT.get_lookup_meaning ('AMS_CAMPAIGN_STATUS', status_code)
1297 -- FROM ams_campaigns_vl;
1298 -- HISTORY
1299 -- 28-Apr-2000 choang Created.
1300 ---------------------------------------------------------------------
1301 FUNCTION get_lookup_meaning (
1302 p_lookup_type IN VARCHAR2,
1303 p_lookup_code IN VARCHAR2
1304 )
1305 RETURN VARCHAR2
1306 IS
1307 l_meaning VARCHAR2(80);
1308
1309 CURSOR c_meaning IS
1310 SELECT meaning
1311 FROM ams_lookups
1312 WHERE lookup_type = UPPER (p_lookup_type)
1313 AND lookup_code = UPPER (p_lookup_code);
1314 BEGIN
1315 OPEN c_meaning;
1316 FETCH c_meaning INTO l_meaning;
1317 CLOSE c_meaning;
1318
1319 RETURN l_meaning;
1320 END get_lookup_meaning;
1321
1322
1323 ---------------------------------------------------------------------
1324 -- FUNCTION
1325 -- get_resource_name
1326 -- USAGE
1327 -- Example:
1328 -- SELECT AMS_Utility_PVT.get_resource_name (owner_user_id)
1329 -- FROM ams_campaigns_vl
1330 -- HISTORY
1331 -- 28-Apr-2000 choang Created.
1332 ---------------------------------------------------------------------
1333 FUNCTION get_resource_name (
1334 p_resource_id IN VARCHAR2
1335 )
1336 RETURN VARCHAR2
1337 IS
1338 l_resource_name VARCHAR2(240);
1339
1340 CURSOR c_resource_name IS
1341 SELECT full_name
1342 FROM ams_jtf_rs_emp_v
1343 WHERE resource_id = p_resource_id;
1344 BEGIN
1345 IF p_resource_id IS NULL THEN
1346 RETURN NULL;
1347 END IF;
1348
1349 OPEN c_resource_name;
1350 FETCH c_resource_name INTO l_resource_name;
1351 CLOSE c_resource_name;
1352
1353 RETURN l_resource_name;
1354 END get_resource_name;
1355
1356
1357 -----------------------------------------------------------------------
1358 -- FUNCTION
1359 -- is_in_my_division
1360 --
1361 -- HISTORY
1362 -- 07/28/2000 holiu Created.
1363 -----------------------------------------------------------------------
1364 FUNCTION is_in_my_division(
1365 p_object_type IN VARCHAR2,
1366 p_object_id IN NUMBER,
1367 p_country_id IN NUMBER
1368 )
1369 RETURN VARCHAR2
1370 IS
1371
1372 l_area2 VARCHAR2(30);
1373 l_obj_area2 VARCHAR2(30);
1374
1375 CURSOR c_area2 IS
1376 SELECT area2_code
1377 FROM jtf_loc_hierarchies_vl
1378 WHERE location_hierarchy_id = p_country_id;
1379
1380 CURSOR c_camp_area2 IS
1381 SELECT B.area2_code
1382 FROM ams_campaigns_vl A, jtf_loc_hierarchies_vl B
1383 WHERE A.campaign_id = p_object_id
1384 AND A.city_id = B.location_hierarchy_id;
1385
1386 BEGIN
1387
1388 OPEN c_area2;
1389 FETCH c_area2 INTO l_area2;
1390 CLOSE c_area2;
1391
1392 IF l_area2 IS NULL THEN
1393 RETURN 'N';
1394 END IF;
1395
1396 IF p_object_type = 'CAMP' THEN
1397 OPEN c_camp_area2;
1398 FETCH c_camp_area2 INTO l_obj_area2;
1399 CLOSE c_camp_area2;
1400 END IF;
1401
1402 IF l_area2 = l_obj_area2 THEN
1403 RETURN 'Y';
1404 ELSE
1405 RETURN 'N';
1406 END IF;
1407
1408 END is_in_my_division;
1409
1410
1411 ---------------------------------------------------------------------
1412 -- FUNCTION
1413 -- get_product_name
1414 -- HISTORY
1415 -- 14-JUN-2000 holiu Create.
1416 -- 10-Apr-2002 choang applied changes requested by skarumur: removed
1417 -- l_product_name because it was declared as a
1418 -- varchar2(76) - too small for product name.
1419 ---------------------------------------------------------------------
1420 FUNCTION get_product_name(
1421 p_prod_level IN VARCHAR2,
1422 p_prod_id IN NUMBER,
1423 p_org_id IN NUMBER := NULL
1424 )
1425 RETURN VARCHAR2
1426 IS
1427 CURSOR c_product_name IS
1428 SELECT padded_concatenated_segments
1429 FROM mtl_system_items_kfv
1430 WHERE inventory_item_id = p_prod_id
1431 AND organization_id = p_org_id;
1432
1433 CURSOR c_category_name IS
1434 SELECT category_concat_segs
1435 FROM mtl_categories_v
1436 WHERE category_id = p_prod_id;
1437
1438 l_product_name c_product_name%ROWTYPE;
1439 l_category_name c_category_name%ROWTYPE;
1440 BEGIN
1441 IF p_prod_id IS NULL THEN
1442 RETURN NULL;
1443 END IF;
1444
1445 IF p_prod_level IN ('PRICING_ATTRIBUTE1', 'PRODUCT') THEN
1446 OPEN c_product_name;
1447 FETCH c_product_name INTO l_product_name;
1448 CLOSE c_product_name;
1449
1450 RETURN l_product_name.padded_concatenated_segments;
1451 ELSIF p_prod_level IN ('PRICING_ATTRIBUTE2', 'CATEGORY') THEN
1452 OPEN c_category_name;
1453 FETCH c_category_name INTO l_category_name;
1454 CLOSE c_category_name;
1455
1456 RETURN l_category_name.category_concat_segs;
1457 END IF;
1458 END get_product_name;
1459
1460
1461 ---------------------------------------------------------------------
1462 -- FUNCTION
1463 -- get_price_list_name
1464 -- HISTORY
1465 -- 14-JUN-2000 holiu Create.
1466 ---------------------------------------------------------------------
1467 FUNCTION get_price_list_name(
1468 p_price_list_line_id IN NUMBER
1469 )
1470 RETURN VARCHAR2
1471 IS
1472 l_name VARCHAR2(240);
1473
1474 CURSOR c_price_list_name IS
1475 SELECT qlh.name
1476 FROM qp_list_headers_vl qlh, qp_list_lines qll
1477 WHERE qll.list_header_id = qlh.list_header_id
1478 AND qll.list_line_id = p_price_list_line_id;
1479 BEGIN
1480 IF p_price_list_line_id IS NULL THEN
1481 RETURN NULL;
1482 END IF;
1483
1484 OPEN c_price_list_name;
1485 FETCH c_price_list_name INTO l_name;
1486 CLOSE c_price_list_name;
1487
1488 RETURN l_name;
1489 END get_price_list_name;
1490
1491
1492 ---------------------------------------------------------------------
1493 -- FUNCTION
1494 -- get_uom_name
1495 -- HISTORY
1496 -- 14-JUN-2000 holiu Create.
1497 ---------------------------------------------------------------------
1498 FUNCTION get_uom_name(
1499 p_uom_code IN VARCHAR2
1500 )
1501 RETURN VARCHAR2
1502 IS
1503 l_name VARCHAR2(25);
1504
1505 CURSOR c_uom IS
1506 SELECT unit_of_measure_tl
1507 FROM mtl_units_of_measure
1508 WHERE uom_code = p_uom_code;
1509 BEGIN
1510 IF p_uom_code IS NULL THEN
1511 RETURN NULL;
1512 END IF;
1513
1514 OPEN c_uom;
1515 FETCH c_uom INTO l_name;
1516 CLOSE c_uom;
1517
1518 RETURN l_name;
1519 END get_uom_name;
1520
1521
1522 ---------------------------------------------------------------------
1523 -- FUNCTION
1524 -- get_qp_lookup_meaning
1525 -- DESCRIPTION
1526 -- Get the meaning of the given lookup code in qp_lookups.
1527 ---------------------------------------------------------------------
1528 FUNCTION get_qp_lookup_meaning(
1529 p_lookup_type IN VARCHAR2,
1530 p_lookup_code IN VARCHAR2
1531 )
1532 RETURN VARCHAR2
1533 IS
1534 l_meaning VARCHAR2(80);
1535
1536 CURSOR c_meaning IS
1537 SELECT meaning
1538 FROM qp_lookups
1539 WHERE lookup_type = UPPER(p_lookup_type)
1540 AND lookup_code = UPPER(p_lookup_code);
1541 BEGIN
1542 IF p_lookup_type IS NULL OR p_lookup_code IS NULL THEN
1543 RETURN NULL;
1544 END IF;
1545
1546 OPEN c_meaning;
1547 FETCH c_meaning INTO l_meaning;
1548 CLOSE c_meaning;
1549
1550 RETURN l_meaning;
1551 END get_qp_lookup_meaning;
1552
1553 ---------------------------------------------------------------------
1554 -- FUNCTION
1555 -- get_resource_id
1556 -- DESCRIPTION
1557 -- Returns resource_id from the JTF Resource module given
1558 -- an AOL user_id.
1559 ---------------------------------------------------------------------
1560 FUNCTION get_resource_id (
1561 p_user_id IN NUMBER
1562 )
1563 RETURN NUMBER
1564 IS
1565 l_resource_id NUMBER;
1566
1567 CURSOR c_resource IS
1568 SELECT resource_id
1569 FROM ams_jtf_rs_emp_v
1570 WHERE user_id = p_user_id;
1571 BEGIN
1572 OPEN c_resource;
1573 FETCH c_resource INTO l_resource_id;
1574 IF c_resource%NOTFOUND THEN
1575 l_resource_id := -1;
1576 -- Adding an error message will cause the function
1577 -- to violate the WNDS pragma, preventing it from
1578 -- being able to be called from a SQL statement.
1579 END IF;
1580 CLOSE c_resource;
1581
1582 RETURN l_resource_id;
1583 END get_resource_id;
1584
1585 ---------------------------------------------------------------------
1586 -- FUNCTION
1587 -- Write_Conc_Log
1588 -- DESCRIPTION
1589 -- Writes the log for Concurrent programs
1590 -- History
1591 -- 07-Aug-2000 PTENDULK Created
1592 -- 08-Aug-2000 PTENDULK Write the output in to log instead of output
1593 -- NOTE
1594 -- If the parameter p_text is passed then the value sent will be printed
1595 -- as log else the messages in the stack are printed.
1596 ---------------------------------------------------------------------
1597 PROCEDURE Write_Conc_Log
1598 ( p_text IN VARCHAR2 := NULL)
1599 IS
1600 l_count NUMBER;
1601 l_msg VARCHAR2(2000);
1602 l_cnt NUMBER ;
1603 BEGIN
1604 IF p_text IS NULL THEN
1605 l_count := FND_MSG_PUB.count_msg;
1606 FOR l_cnt IN 1 .. l_count
1607 LOOP
1608 l_msg := FND_MSG_PUB.get(l_cnt, FND_API.g_false);
1609 FND_FILE.PUT_LINE(FND_FILE.LOG, '(' || l_cnt || ') ' || l_msg);
1610 END LOOP;
1611 ELSE
1612 FND_FILE.PUT_LINE(FND_FILE.LOG, p_text );
1613 END IF;
1614
1615 END Write_Conc_Log ;
1616
1617
1618 -----------------------------------------------------------------------
1619 -- FUNCTION
1620 -- get_system_status_type
1621 --
1622 -- HISTORY
1623 -- 14-SEP-2000 holiu Create.
1624 -- 29-May-2001 ptendulk Added system status for Schedule
1625 -- 20-May-2001 ptendulk Added system status for programs.
1626 -- 13-Jun-2001 ptendulk Added EONE
1627 -----------------------------------------------------------------------
1628 FUNCTION get_system_status_type(
1629 p_object IN VARCHAR2
1630 )
1631 RETURN VARCHAR2
1632 IS
1633 BEGIN
1634
1635 IF p_object = 'CAMP' THEN
1636 RETURN 'AMS_CAMPAIGN_STATUS';
1637 ELSIF p_object IN ('EVEH', 'EVEO','EONE', 'EVET') THEN
1638 RETURN 'AMS_EVENT_STATUS';
1639 ELSIF p_object = 'DELV' THEN
1640 RETURN 'AMS_DELIV_STATUS';
1641 ELSIF p_object = 'CSCH' THEN
1642 RETURN 'AMS_CAMPAIGN_SCHEDULE_STATUS' ;
1643 ELSIF p_object = 'RCAM' THEN
1644 RETURN 'AMS_PROGRAM_STATUS' ;
1645 ELSIF p_object = 'OFFR' THEN
1646 RETURN 'AMS_OFFER_STATUS' ;
1647 ELSIF p_object = 'PRIC' THEN
1648 RETURN 'AMS_PRICELIST_STATUS' ;
1649 ELSE
1650 RETURN NULL;
1651 END IF;
1652
1653 END get_system_status_type;
1654
1655
1656 -----------------------------------------------------------------------
1657 -- FUNCTION
1658 -- get_system_status_code
1659 --
1660 -- HISTORY
1661 -- 14-SEP-2000 holiu Create.
1662 -----------------------------------------------------------------------
1663 FUNCTION get_system_status_code(
1664 p_user_status_id IN NUMBER
1665 )
1666 RETURN VARCHAR2
1667 IS
1668
1669 l_status_code VARCHAR2(30);
1670
1671 CURSOR c_status_code IS
1672 SELECT system_status_code
1673 FROM ams_user_statuses_vl
1674 WHERE user_status_id = p_user_status_id
1675 AND enabled_flag = 'Y';
1676
1677 BEGIN
1678
1679 OPEN c_status_code;
1680 FETCH c_status_code INTO l_status_code;
1681 CLOSE c_status_code;
1682
1683 RETURN l_status_code;
1684
1685 END get_system_status_code;
1686
1687
1688 -----------------------------------------------------------------------
1689 -- FUNCTION
1690 -- get_default_user_status
1691 --
1692 -- HISTORY
1693 -- 14-SEP-2000 holiu Create.
1694 -----------------------------------------------------------------------
1695 FUNCTION get_default_user_status(
1696 p_status_type IN VARCHAR2,
1697 p_status_code IN VARCHAR2
1698 )
1699 RETURN VARCHAR2
1700 IS
1701
1702 l_status_id NUMBER;
1703
1704 CURSOR c_status_id IS
1705 SELECT user_status_id
1706 FROM ams_user_statuses_vl
1707 WHERE system_status_type = p_status_type
1708 AND system_status_code = p_status_code
1709 AND default_flag = 'Y'
1710 AND enabled_flag = 'Y';
1711
1712 BEGIN
1713
1714 OPEN c_status_id;
1715 FETCH c_status_id INTO l_status_id;
1716 CLOSE c_status_id;
1717
1718 RETURN l_status_id;
1719
1720 END get_default_user_status;
1721
1722
1723 -----------------------------------------------------------------------
1724 -- PROCEDURE
1725 -- check_status_change
1726 --
1727 -- HISTORY
1728 -- 14-SEP-2000 holiu Create.
1729 -----------------------------------------------------------------------
1730 PROCEDURE check_status_change(
1731 p_object_type IN VARCHAR2,
1732 p_object_id IN NUMBER,
1733 p_old_status_id IN NUMBER,
1734 p_new_status_id IN NUMBER,
1735 x_approval_type OUT NOCOPY VARCHAR2,
1736 x_return_status OUT NOCOPY VARCHAR2
1737 )
1738 IS
1739
1740 l_theme_flag VARCHAR2(1);
1741 l_budget_flag VARCHAR2(1);
1742 l_status_type VARCHAR2(30);
1743 l_old_status_code VARCHAR2(30);
1744 l_new_status_code VARCHAR2(30);
1745
1746 CURSOR c_approval_flag IS
1747 SELECT theme_approval_flag, budget_approval_flag
1748 FROM ams_status_order_rules
1749 WHERE system_status_type = l_status_type
1750 AND current_status_code = l_old_status_code
1751 AND next_status_code = l_new_status_code;
1752
1753 BEGIN
1754
1755 x_return_status := FND_API.g_ret_sts_success;
1756 x_approval_type := NULL;
1757
1758 l_status_type := get_system_status_type(p_object_type);
1759 l_old_status_code := get_system_status_code(p_old_status_id);
1760 l_new_status_code := get_system_status_code(p_new_status_id);
1761
1762 IF l_old_status_code = l_new_status_code THEN
1763 RETURN;
1764 END IF;
1765
1766 OPEN c_approval_flag;
1767 FETCH c_approval_flag INTO l_theme_flag, l_budget_flag;
1768 IF c_approval_flag%NOTFOUND THEN
1769 x_return_status := FND_API.g_ret_sts_error;
1770 AMS_Utility_PVT.error_message('AMS_CAMP_BAD_STATUS_CHANGE');
1771 END IF;
1772 CLOSE c_approval_flag;
1773
1774 IF l_budget_flag = 'Y' THEN
1775 IF AMS_ObjectAttribute_PVT.check_object_attribute(
1776 p_object_type, p_object_id, 'BAPL') = FND_API.g_true
1777 THEN
1778 x_approval_type := 'BUDGET';
1779 END IF;
1780 ELSIF l_theme_flag = 'Y' THEN
1781 IF AMS_ObjectAttribute_PVT.check_object_attribute(
1782 p_object_type, p_object_id, 'TAPL') = FND_API.g_true
1783 THEN
1784 x_approval_type := 'THEME';
1785 END IF;
1786 END IF;
1787
1788 END check_status_change;
1789
1790
1791 --========================================================================
1792 -- Function
1793 -- Approval_required_flag
1794 -- Purpose
1795 -- This function will return the approval required flag for the
1796 -- given custom setup.
1797 --
1798 -- History
1799 -- 16-Jun-2001 ptendulk Created
1800 -- 19-Jun-2001 ptendulk Check specific attribute (bug in last code)
1801 --========================================================================
1802 FUNCTION Approval_Required_Flag( p_custom_setup_id IN NUMBER ,
1803 p_approval_type IN VARCHAR2)
1804 RETURN VARCHAR2 IS
1805 CURSOR c_custom_attr IS
1806 SELECT attr_available_flag
1807 FROM ams_custom_setup_attr
1808 WHERE custom_setup_id = p_custom_setup_id
1809 -- Following line is added by ptendulk on 19-Jun-2001
1810 AND object_attribute = p_approval_type ;
1811
1812 l_flag VARCHAR2(1) ;
1813 BEGIN
1814
1815 OPEN c_custom_attr;
1816 FETCH c_custom_attr INTO l_flag ;
1817 CLOSE c_custom_attr ;
1818 RETURN l_flag ;
1819
1820 END Approval_Required_Flag;
1821
1822 -----------------------------------------------------------------------
1823 -- PROCEDURE
1824 -- check_status_change
1825 --
1826 -- PURPOSE
1827 -- This procedure is created to override the obsoleted check_status_change
1828 -- procedure as object_attribute table is obsoleted now.
1829 --
1830 -- HISTORY
1831 -- 16-Jun-2001 ptendulk Created
1832 -- 02-Jul-2002 musman Added changes for deliverable approvals
1833 -----------------------------------------------------------------------
1834 PROCEDURE check_new_status_change(
1835 p_object_type IN VARCHAR2,
1836 p_object_id IN NUMBER,
1837 p_old_status_id IN NUMBER,
1838 p_new_status_id IN NUMBER,
1839 p_custom_setup_id IN NUMBER,
1840 x_approval_type OUT NOCOPY VARCHAR2,
1841 x_return_status OUT NOCOPY VARCHAR2
1842 )
1843 IS
1844
1845 l_theme_flag VARCHAR2(1);
1846 l_budget_flag VARCHAR2(1);
1847 l_status_type VARCHAR2(30);
1848 l_old_status_code VARCHAR2(30);
1849 l_new_status_code VARCHAR2(30);
1850
1851 l_custom_setup_attr VARCHAR2(4) := 'TAPL';
1852
1853 CURSOR c_approval_flag IS
1854 SELECT theme_approval_flag, budget_approval_flag
1855 FROM ams_status_order_rules
1856 WHERE system_status_type = l_status_type
1857 AND current_status_code = l_old_status_code
1858 AND next_status_code = l_new_status_code;
1859
1860 BEGIN
1861
1862 x_return_status := FND_API.g_ret_sts_success;
1863 x_approval_type := NULL;
1864
1865 l_status_type := get_system_status_type(p_object_type);
1866 l_old_status_code := get_system_status_code(p_old_status_id);
1867 l_new_status_code := get_system_status_code(p_new_status_id);
1868
1869 IF l_old_status_code = l_new_status_code THEN
1870 RETURN;
1871 END IF;
1872
1873 OPEN c_approval_flag;
1874 FETCH c_approval_flag INTO l_theme_flag, l_budget_flag;
1875 IF c_approval_flag%NOTFOUND THEN
1876 x_return_status := FND_API.g_ret_sts_error;
1877 AMS_Utility_PVT.error_message('AMS_CAMP_BAD_STATUS_CHANGE');
1878 END IF;
1879 CLOSE c_approval_flag;
1880
1881 IF l_budget_flag = 'Y' THEN
1882 IF Approval_Required_Flag(p_custom_setup_id, 'BAPL') = 'Y'
1883 THEN
1884 x_approval_type := 'BUDGET';
1885 END IF;
1886 ELSIF l_theme_flag = 'Y' THEN
1887
1888 /* since Deliv has only concept approval */
1889 IF p_object_type = 'DELV'
1890 THEN
1891 l_custom_setup_attr := 'CAPL';
1892 END IF;
1893 IF Approval_Required_Flag(p_custom_setup_id, l_custom_setup_attr) = 'Y'
1894 THEN
1895 x_approval_type := 'THEME';
1896 END IF;
1897 END IF;
1898
1899 END check_new_status_change;
1900
1901 ---------------------------------------------------------------------
1902 -- PROCEDURE
1903 -- Convert_Currency
1904 -- NOTE
1905 -- HISTORY
1906 -- 01-Sep-2000 slkrishn Created.
1907 -- 12-SEP-2000 mpande Updated
1908 -- 02/23/2001 mpande Updated for getting org id query
1909 -- 03/27/2001 MPANDE MOved from OZF to AMS
1910 ---------------------------------------------------------------------
1911 PROCEDURE convert_currency(
1912 p_set_of_books_id IN NUMBER
1913 ,p_from_currency IN VARCHAR2
1914 ,p_conversion_date IN DATE
1915 ,p_conversion_type IN VARCHAR2
1916 ,p_conversion_rate IN NUMBER
1917 ,p_amount IN NUMBER
1918 ,x_return_status OUT NOCOPY VARCHAR2
1919 ,x_acc_amount OUT NOCOPY NUMBER
1920 ,x_rate OUT NOCOPY NUMBER)
1921 IS
1922 l_api_name VARCHAR2(30) := 'Convert Currency';
1923 l_to_currenvy VARCHAR2(30);
1924 l_max_roll_days NUMBER := -1;
1925 l_user_rate NUMBER := NVL(p_conversion_rate, 1);
1926 l_numerator NUMBER;
1927 l_denominator NUMBER;
1928 l_org_id NUMBER;
1929 l_sob NUMBER;
1930 l_to_currency VARCHAR2(30);
1931
1932 --
1933 -- get functional currency
1934 -- gs.mrc_sob_type_code,
1935
1936 /* CURSOR c_get_gl_info(
1937 p_org_id IN NUMBER)
1938 IS
1939 SELECT gs.set_of_books_id
1940 ,gs.currency_code
1941 FROM gl_sets_of_books gs
1942 ,org_organization_definitions org
1943 WHERE gs.mrc_sob_type_code = 'P'
1944 AND org.set_of_books_id = gs.set_of_books_id
1945 AND org.operating_unit = p_org_id;
1946 */
1947 --02/23/2001 mpande changed as per Hornet requirements
1948 CURSOR c_get_gl_info( p_org_id IN NUMBER)
1949 IS
1950 SELECT gs.set_of_books_id
1951 , gs.currency_code
1952 FROM gl_sets_of_books gs
1953 , ozf_sys_parameters_all org
1954 WHERE org.set_of_books_id = gs.set_of_books_id
1955 AND org.org_id = p_org_id;
1956
1957 -- org_id cursor
1958 CURSOR c_org_cur
1959 IS
1960 SELECT (TO_NUMBER(SUBSTRB(USERENV('CLIENT_INFO'), 1, 10)))
1961 FROM dual;
1962 --
1963 BEGIN
1964 OPEN c_org_cur;
1965 FETCH c_org_cur INTO l_org_id;
1966
1967 IF c_org_cur%NOTFOUND THEN
1968 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
1969 fnd_message.set_name('AMS', 'AMS_ORG_ID_NOTFOUND');
1970 fnd_msg_pub.add;
1971 END IF;
1972 RAISE fnd_api.g_exc_error;
1973 END IF;
1974 CLOSE c_org_cur;
1975
1976 OPEN c_get_gl_info(l_org_id);
1977 FETCH c_get_gl_info INTO l_sob, l_to_currency;
1978
1979 IF c_get_gl_info%NOTFOUND THEN
1980 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
1981 fnd_message.set_name('AMS', 'AMS_GL_SOB_NOTFOUND');
1982 fnd_msg_pub.add;
1983 END IF;
1984
1985 RAISE fnd_api.g_exc_error;
1986 END IF;
1987 CLOSE c_get_gl_info;
1988
1989 --
1990 gl_currency_api.convert_closest_amount(
1991 x_from_currency => p_from_currency
1992 ,x_to_currency => l_to_currency
1993 ,x_conversion_date => p_conversion_date
1994 ,x_conversion_type => p_conversion_type
1995 ,x_user_rate => l_user_rate
1996 ,x_amount => p_amount
1997 ,x_max_roll_days => l_max_roll_days
1998 ,x_converted_amount => x_acc_amount
1999 ,x_denominator => l_denominator
2000 ,x_numerator => l_numerator
2001 ,x_rate => x_rate);
2002 --
2003
2004 EXCEPTION
2005 WHEN fnd_api.g_exc_error THEN
2006 x_return_status := fnd_api.g_ret_sts_error;
2007 WHEN gl_currency_api.no_rate THEN
2008 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2009 fnd_message.set_name('AMS', 'AMS_NO_RATE');
2010 fnd_msg_pub.add;
2011 END IF;
2012
2013 x_return_status := fnd_api.g_ret_sts_error;
2014 WHEN gl_currency_api.invalid_currency THEN
2015 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2016 fnd_message.set_name('AMS', 'AMS_INVALID_CURR');
2017 fnd_msg_pub.add;
2018 END IF;
2019
2020 x_return_status := fnd_api.g_ret_sts_error;
2021 WHEN OTHERS THEN
2022 RAISE;
2023 x_return_status := fnd_api.g_ret_sts_unexp_error;
2024 END convert_currency;
2025
2026 ---------------------------------------------------------------------
2027 -- PROCEDURE
2028 -- get_code_combinations
2029 --
2030 -- PURPOSE
2031 -- get code_combination concacnenated segments and ids
2032 -- 20-Sep-2000 slkrishn Created
2033 -- 03/27/2001 MPANDE MOved from OZF to AMS
2034 ---------------------------------------------------------------------
2035 FUNCTION get_code_combinations(
2036 p_code_combination_id IN NUMBER
2037 ,p_chart_of_accounts_id IN NUMBER)
2038 RETURN VARCHAR2
2039 IS
2040 l_api_name VARCHAR2(30) := 'Get_Code_Combinations';
2041 l_result BOOLEAN;
2042 l_app_name VARCHAR2(30) := 'SQLGL';
2043 l_flex_code VARCHAR2(30) := 'GL#';
2044 BEGIN
2045 l_result := fnd_flex_keyval.validate_ccid(
2046 appl_short_name => l_app_name
2047 ,key_flex_code => l_flex_code
2048 ,structure_number => p_chart_of_accounts_id
2049 ,combination_id => p_code_combination_id);
2050
2051 IF l_result THEN
2052 RETURN fnd_flex_keyval.concatenated_descriptions;
2053 ELSE
2054 RETURN '';
2055 END IF;
2056 EXCEPTION
2057 WHEN OTHERS THEN
2058 RAISE;
2059 END get_code_combinations;
2060 ---------------------------------------------------------------------
2061 -- PROCEDURE
2062 -- Convert_functional_Curr
2063 -- NOTE
2064 -- This procedures takes in amount and converts it to the functional currency
2065 -- and returns the converted amount,exchange_rate,set_of_book_id,
2066 -- f-nctional_currency_code,exchange_rate_date
2067
2068 -- HISTORY
2069 -- 20-Jul-2000 mpande Created.
2070 -- 02/23/2001 MPAnde Updated for getting org id query
2071 -- 03/27/2001 MPANDE MOved from OZF to AMS
2072 -- 01/13/2003 yzhao fix bug BUG 2750841(same as 2741039) - pass in org_id, default to null
2073 --parameter x_Amount1 IN OUT NUMBER -- reqd Parameter -- amount to be converted
2074 -- x_TC_CURRENCY_CODE IN OUT VARCHAR2,
2075 -- x_Set_of_books_id OUT NUMBER,
2076 -- x_MRC_SOB_TYPE_CODE OUT NUMBER, 'P' and 'R'
2077 -- We only do it for primary ('P' because we donot supprot MRC)
2078 -- x_FC_CURRENCY_CODE OUT VARCHAR2,
2079 -- x_EXCHANGE_RATE_TYPE OUT VARCHAR2,
2080 -- comes from a AMS profile or what ever is passed
2081 -- x_EXCHANGE_RATE_DATE OUT DATE,
2082 -- could come from a AMS profile but right now is sysdate
2083 -- x_EXCHANGE_RATE OUT VARCHAR2,
2084 -- x_return_status OUT VARCHAR2
2085 -- The following is the rule in the GL API
2086 -- If x_conversion_type = 'User', and the relationship between the
2087 -- two currencies is not fixed, x_user_rate will be used as the
2088 -- conversion rate to convert the amount
2089 -- else no_user_rate is required
2090
2091 ---------------------------------------------------------------------
2092
2093
2094 PROCEDURE calculate_functional_curr(
2095 p_from_amount IN NUMBER
2096 ,p_conv_date IN DATE DEFAULT SYSDATE
2097 ,p_tc_currency_code IN VARCHAR2
2098 ,p_org_id IN NUMBER DEFAULT NULL
2099 ,x_to_amount OUT NOCOPY NUMBER
2100 ,x_set_of_books_id OUT NOCOPY NUMBER
2101 ,x_mrc_sob_type_code OUT NOCOPY VARCHAR2
2102 ,x_fc_currency_code OUT NOCOPY VARCHAR2
2103 ,x_exchange_rate_type IN OUT NOCOPY VARCHAR2
2104 ,x_exchange_rate IN OUT NOCOPY NUMBER
2105 ,x_return_status OUT NOCOPY VARCHAR2)
2106 IS
2107 l_conversion_type_profile CONSTANT VARCHAR2(30) := 'AMS_CURR_CONVERSION_TYPE';
2108 l_user_rate CONSTANT NUMBER := 1;
2109 -- Currenty not used. -- this should be a profile
2110 l_max_roll_days CONSTANT NUMBER := -1;
2111 -- Negative so API rolls back to find the last conversion rate.
2112 -- this should be a profile
2113 l_denominator NUMBER; -- Not used in Marketing.
2114 l_numerator NUMBER; -- Not used in Marketing.
2115 l_conversion_type VARCHAR2(30);
2116 l_org_id NUMBER;
2117
2118 -- Cursor to get the primary set_of_books_id ,functional_currency_code
2119 -- changed the above query to look into operating unit and not organization_id
2120 --SEP12 mpande
2121 /*
2122 CURSOR c_get_gl_info(
2123 p_org_id IN NUMBER)
2124 IS
2125 SELECT gs.set_of_books_id
2126 ,gs.currency_code
2127 FROM gl_sets_of_books gs
2128 ,org_organization_definitions org
2129 WHERE org.set_of_books_id = gs.set_of_books_id
2130 AND org.operating_unit = p_org_id;
2131 */
2132 --02/23/2001 mpande changed as per Hornet requirements
2133 CURSOR c_get_gl_info(p_org_id IN NUMBER)
2134 IS
2135 SELECT gs.set_of_books_id
2136 , gs.currency_code
2137 FROM gl_sets_of_books gs
2138 , ozf_sys_parameters_all org
2139 WHERE org.set_of_books_id = gs.set_of_books_id
2140 AND org.org_id = p_org_id;
2141
2142 -- org_id cursor
2143 CURSOR c_org_cur
2144 IS
2145 SELECT (TO_NUMBER(SUBSTRB(USERENV('CLIENT_INFO'), 1, 10)))
2146 FROM dual;
2147 BEGIN
2148 -- Initialize return status.
2149 x_return_status := fnd_api.g_ret_sts_success;
2150
2151 --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++--
2152 -- Mumu Pande 09/20/2000 Updated the following
2153 -- Get the currency conversion type from profile option
2154 IF x_exchange_rate_type IS NULL THEN
2155 l_conversion_type := fnd_profile.VALUE(l_conversion_type_profile);
2156 ELSE
2157 l_conversion_type := x_exchange_rate_type;
2158 END IF;
2159
2160 IF l_conversion_type IS NULL THEN
2161 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2162 fnd_message.set_name('AMS', 'AMS_NO_EXCHANGE_TYPE');
2163 fnd_msg_pub.add;
2164 END IF;
2165
2166 x_return_status := fnd_api.g_ret_sts_error;
2167 RETURN;
2168 ELSE
2169 IF ams_utility_pvt.check_fk_exists('GL_DAILY_CONVERSION_TYPES',
2170 'CONVERSION_TYPE'
2171 ,l_conversion_type) = fnd_api.g_false
2172 THEN
2173 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2174 fnd_message.set_name('AMS', 'AMS_WRONG_CONVERSION_TYPE');
2175 fnd_msg_pub.add;
2176 END IF;
2177 x_return_status := fnd_api.g_ret_sts_error;
2178 RETURN;
2179 END IF;
2180 END IF;
2181
2182 --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++--
2183 /* yzhao: 01/13/2003 fix bug BUG 2750841(same as 2741039) - use org_id if it is passed,
2184 otherwise get from login session */
2185 IF (p_org_id IS NOT NULL) THEN
2186 l_org_id := p_org_id;
2187 ELSE
2188 OPEN c_org_cur;
2189 FETCH c_org_cur INTO l_org_id;
2190
2191 IF c_org_cur%NOTFOUND THEN
2192 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2193 fnd_message.set_name('AMS', 'AMS_ORG_ID_NOTFOUND');
2194 fnd_msg_pub.add;
2195 END IF;
2196
2197 RAISE fnd_api.g_exc_error;
2198 END IF;
2199
2200 CLOSE c_org_cur;
2201 END IF;
2202
2203 IF (AMS_DEBUG_HIGH_ON) THEN
2204 ams_utility_pvt.debug_message('debug: start ' || l_org_id);
2205 END IF;
2206
2207 x_mrc_sob_type_code := 'P';
2208 OPEN c_get_gl_info(l_org_id);
2209 FETCH c_get_gl_info INTO x_set_of_books_id, x_fc_currency_code;
2210
2211 IF c_get_gl_info%NOTFOUND THEN
2212 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2213 fnd_message.set_name('AMS', 'AMS_GL_SOB_NOTFOUND');
2214 fnd_msg_pub.add;
2215 END IF;
2216
2217 RAISE fnd_api.g_exc_error;
2218 END IF;
2219
2220 CLOSE c_get_gl_info;
2221 -- Call the proper GL API to convert the amount.
2222 gl_currency_api.convert_closest_amount(
2223 x_from_currency => p_tc_currency_code
2224 ,x_to_currency => x_fc_currency_code
2225 ,x_conversion_date => p_conv_date
2226 ,x_conversion_type => l_conversion_type
2227 ,x_user_rate => x_exchange_rate
2228 ,x_amount => p_from_amount
2229 ,x_max_roll_days => l_max_roll_days
2230 ,x_converted_amount => x_to_amount
2231 ,x_denominator => l_denominator
2232 ,x_numerator => l_numerator
2233 ,x_rate => x_exchange_rate);
2234
2235 x_exchange_rate_type := l_conversion_type;
2236 --
2237
2238 EXCEPTION
2239 WHEN fnd_api.g_exc_error THEN
2240 x_return_status := fnd_api.g_ret_sts_error;
2241 WHEN gl_currency_api.no_rate THEN
2242 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2243 fnd_message.set_name('AMS', 'AMS_NO_RATE');
2244 fnd_msg_pub.add;
2245 END IF;
2246
2247 x_return_status := fnd_api.g_ret_sts_error;
2248 WHEN gl_currency_api.invalid_currency THEN
2249 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2250 fnd_message.set_name('AMS', 'AMS_INVALID_CURR');
2251 fnd_msg_pub.add;
2252 END IF;
2253
2254 x_return_status := fnd_api.g_ret_sts_error;
2255 WHEN OTHERS THEN
2256 x_return_status := fnd_api.g_ret_sts_unexp_error;
2257
2258 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2259 fnd_msg_pub.add_exc_msg('AMS_UTLITY_PVT', 'Convert_functional_curency');
2260 END IF;
2261 END calculate_functional_curr;
2262
2263 ---------------------------------------------------------------------
2264 -- PROCEDURE
2265 -- Convert_Currency
2266 -- NOTE
2267
2268 -- HISTORY
2269 -- 20-Jul-2000 mpande Created.
2270 --parameter p_from_currency IN VARCHAR2,
2271 -- p_to_currency IN VARCHAR2,
2272 -- p_conv_date IN DATE DEFAULT SYSDATE,
2273 -- p_from_amount IN NUMBER,
2274 -- x_to_amount OUT NUMBER
2275 -- If x_conversion_type = 'User', and the relationship between the
2276 -- two currencies is not fixed, x_user_rate will be used as the
2277 -- conversion rate to convert the amount
2278 -- else no_user_rate is required
2279 -- 02/23/2001 MPAnde Updated for getting org id query
2280 -- 03/27/2001 MPANDE MOved from OZF to AMS
2281 -- 04/07/2001 slkrishn Added p_conv_type and p_conv_rate with defaults
2282 ---------------------------------------------------------------------
2283
2284 PROCEDURE convert_currency(
2285 p_from_currency IN VARCHAR2
2286 ,p_to_currency IN VARCHAR2
2287 ,p_conv_type IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR
2288 ,p_conv_rate IN NUMBER DEFAULT FND_API.G_MISS_NUM
2289 ,p_conv_date IN DATE DEFAULT SYSDATE
2290 ,p_from_amount IN NUMBER
2291 ,x_return_status OUT NOCOPY VARCHAR2
2292 ,x_to_amount OUT NOCOPY NUMBER
2293 ,x_rate OUT NOCOPY NUMBER)
2294 IS
2295 l_conversion_type_profile CONSTANT VARCHAR2(30) := 'AMS_CURR_CONVERSION_TYPE';
2296 l_user_rate CONSTANT NUMBER := 1;
2297 -- Currenty not used.
2298 -- this should be a profile
2299 l_max_roll_days CONSTANT NUMBER := -1;
2300 -- Negative so API rolls back to find the last conversion rate.
2301 -- this should be a profile
2302 l_denominator NUMBER; -- Not used in Marketing.
2303 l_numerator NUMBER; -- Not used in Marketing.
2304 l_conversion_type VARCHAR2(30); -- Curr conversion type; see API doc for details.
2305 BEGIN
2306 -- Initialize return status.
2307 x_return_status := fnd_api.g_ret_sts_success;
2308
2309 -- condition added to pass conversion types
2310 IF p_conv_type = FND_API.G_MISS_CHAR THEN
2311 -- Get the currency conversion type from profile option
2312 l_conversion_type := fnd_profile.VALUE(l_conversion_type_profile);
2313 -- Conversion type cannot be null in profile
2314 IF l_conversion_type IS NULL THEN
2315 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2316 fnd_message.set_name('OZF', 'OZF_NO_EXCHANGE_TYPE');
2317 fnd_msg_pub.add;
2318 END IF;
2319 x_return_status := fnd_api.g_ret_sts_error;
2320 RETURN;
2321 END IF;
2322 ELSE
2323 l_conversion_type := p_conv_type;
2324 END IF;
2325
2326 -- Call the proper GL API to convert the amount.
2327 gl_currency_api.convert_closest_amount(
2328 x_from_currency => p_from_currency
2329 ,x_to_currency => p_to_currency
2330 ,x_conversion_date => p_conv_date
2331 ,x_conversion_type => l_conversion_type
2332 ,x_user_rate => l_user_rate
2333 ,x_amount => p_from_amount
2334 ,x_max_roll_days => l_max_roll_days
2335 ,x_converted_amount => x_to_amount
2336 ,x_denominator => l_denominator
2337 ,x_numerator => l_numerator
2338 ,x_rate => x_rate);
2339 --
2340
2341 EXCEPTION
2342 WHEN gl_currency_api.no_rate THEN
2343 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2344 fnd_message.set_name('OZF', 'OZF_NO_RATE');
2345 fnd_msg_pub.add;
2346 END IF;
2347
2348 x_return_status := fnd_api.g_ret_sts_error;
2349 WHEN gl_currency_api.invalid_currency THEN
2350 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2351 fnd_message.set_name('OZF', 'OZF_INVALID_CURR');
2352 fnd_msg_pub.add;
2353 END IF;
2354
2355 x_return_status := fnd_api.g_ret_sts_error;
2356 WHEN OTHERS THEN
2357 x_return_status := fnd_api.g_ret_sts_unexp_error;
2358
2359 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2360 fnd_msg_pub.add_exc_msg('OZF_UTLITY_PVT', 'Convert_curency');
2361 END IF;
2362 END convert_currency;
2363
2364 /*============================================================================*/
2365 -- Start of Comments
2366 -- NAME
2367 -- Get_Resource_Role
2368 --
2369 -- PURPOSE
2370 -- This Procedure will be return the workflow user role for
2371 -- the resourceid sent
2372 -- Called By
2373 -- NOTES
2374 -- End of Comments
2375
2376 /*============================================================================*/
2377
2378 PROCEDURE Get_Resource_Role
2379 ( p_resource_id IN NUMBER,
2380 x_role_name OUT NOCOPY VARCHAR2,
2381 x_role_display_name OUT NOCOPY VARCHAR2 ,
2382 x_return_status OUT NOCOPY VARCHAR2
2383 )
2384 IS
2385 l_msg_count NUMBER;
2386 l_msg_data VARCHAR2(4000);
2387 l_error_msg VARCHAR2(4000);
2388
2389 CURSOR c_resource IS
2390 SELECT employee_id , user_id, category
2391 FROM ams_jtf_rs_emp_v
2392 WHERE resource_id = p_resource_id ;
2393
2394 l_person_id number;
2395 l_user_id number;
2396 l_category varchar2(30);
2397 BEGIN
2398 x_return_status := FND_API.G_RET_STS_SUCCESS;
2399 OPEN c_resource ;
2400 FETCH c_resource INTO l_person_id , l_user_id, l_category;
2401 IF c_resource%NOTFOUND THEN
2402 CLOSE c_resource ;
2403 x_return_status := FND_API.G_RET_STS_ERROR;
2404 AMS_Utility_PVT.error_message ('AMS_APPR_INVALID_RESOURCE_ID');
2405 return;
2406 END IF;
2407 CLOSE c_resource ;
2408 -- Pass the Employee ID to get the Role
2409 IF l_category = 'PARTY' THEN
2410 WF_DIRECTORY.getrolename
2411 ( p_orig_system => 'FND_USR',
2412 p_orig_system_id => l_user_id ,
2413 p_name => x_role_name,
2414 p_display_name => x_role_display_name
2415 );
2416 IF x_role_name is null then
2417 x_return_status := FND_API.G_RET_STS_ERROR;
2418 AMS_Utility_PVT.error_message ('AMS_APPR_INVALID_ROLE');
2419 return;
2420 END IF;
2421 ELSE
2422 WF_DIRECTORY.getrolename
2423 ( p_orig_system => 'PER',
2424 p_orig_system_id => l_person_id ,
2425 p_name => x_role_name,
2426 p_display_name => x_role_display_name
2427 );
2428 IF x_role_name is null then
2429 x_return_status := FND_API.G_RET_STS_ERROR;
2430 AMS_Utility_PVT.error_message ('AMS_APPR_INVALID_ROLE');
2431 return;
2432 END IF;
2433 END IF;
2434 EXCEPTION
2435 WHEN OTHERS THEN
2436 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2437 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2438 fnd_msg_pub.add_exc_msg('AMS_UTLITY_PVT', 'Get_Resource_Role');
2439 END IF;
2440 RAISE;
2441 END Get_Resource_Role;
2442
2443 --======================================================================
2444 -- Procedure Name: send_wf_standalone_message
2445 -- Type : Generic utility
2446 -- Pre-Req :
2447 -- Notes:
2448 -- Common utility to send standalone message without initiating
2449 -- process using workflow.
2450 -- Parameters:
2451 -- IN:
2452 -- p_item_type IN VARCHAR2 Required Default = "MAPGUTIL"
2453 -- item type for the workflow utility.
2454 -- p_message_name IN VARCHAR2 Required Default = "GEN_STDLN_MESG"
2455 -- Internal name for standalone message name
2456 -- p_subject IN VARCHAR2 Required
2457 -- Subject for the message
2458 -- p_body IN VARCHAR2 Optional
2459 -- Body for the message
2460 -- p_send_to_role_name IN VARCHAR2 Optional
2461 -- Role name to whom message is to be sent.
2462 -- Instead of this, one can send even p_send_to_res_id
2463 -- p_send_to_res_id IN NUMBER Optional
2464 -- Resource Id that will be used to get role name from WF_DIRECTORY.
2465 -- This is required if role name is not passed.
2466
2467 -- OUT:
2468 -- x_notif_id OUT NUMBER
2469 -- Notification Id created that is being sent to recipient.
2470 -- x_return_status OUT VARCHAR2
2471 -- Return status. If it is error, messages will be put in mesg pub.
2472 -- History:
2473 -- 11-Jan-2002 sveerave Created.
2474 --======================================================================
2475
2476 PROCEDURE send_wf_standalone_message(
2477 p_item_type IN VARCHAR2 := 'MAPGUTIL'
2478 ,p_message_name IN VARCHAR2 := 'GEN_STDLN_MESG'
2479 ,p_subject IN VARCHAR2
2480 ,p_body IN VARCHAR2 := NULL
2481 ,p_send_to_role_name IN VARCHAR2 := NULL
2482 ,p_send_to_res_id IN NUMBER := NULL
2483 ,x_notif_id OUT NOCOPY NUMBER
2484 ,x_return_status OUT NOCOPY VARCHAR2
2485 )
2486 IS
2487 l_role_name VARCHAR2(100) := p_send_to_role_name;
2488 l_display_role_name VARCHAR2(240);
2489 l_notif_id NUMBER;
2490
2491 BEGIN
2492 x_return_status := FND_API.G_RET_STS_SUCCESS;
2493 IF p_send_to_role_name IS NULL THEN
2494 AMS_UTILITY_PVT.get_resource_role
2495 ( p_resource_id => p_send_to_res_id,
2496 x_role_name => l_role_name,
2497 x_role_display_name => l_display_role_name,
2498 x_return_status => x_return_status
2499 );
2500 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2501 return;
2502 END IF;
2503 END IF;
2504 l_notif_id := WF_NOTIFICATION.Send
2505 ( role => l_role_name
2506 , msg_type => p_item_type
2507 , msg_name => p_message_name
2508 );
2509 WF_NOTIFICATION.SetAttrText( l_notif_id
2510 , 'GEN_MSG_SUBJECT'
2511 , p_subject
2512 );
2513 WF_NOTIFICATION.SetAttrText( l_notif_id
2514 , 'GEN_MSG_BODY'
2515 , p_body
2516 );
2517 WF_NOTIFICATION.SetAttrText( l_notif_id
2518 , 'GEN_MSG_SEND_TO'
2519 , l_role_name
2520 );
2521 WF_NOTIFICATION.Denormalize_Notification(l_notif_id);
2522 x_notif_id := l_notif_id;
2523 END send_wf_standalone_message;
2524
2525 --======================================================================
2526 -- FUNCTION
2527 -- Check_Status_Change
2528 --
2529 -- PURPOSE
2530 -- Created to check if the status change is valid or not.
2531 -- Returns FND_API.G_TRUE if it is valid status change
2532 -- or will return FND_API.G_FALSE
2533 --
2534 -- HISTORY
2535 -- 09-Jul-2001 ptendulk Create.
2536 --======================================================================
2537 FUNCTION Check_Status_Change(
2538 p_status_type IN VARCHAR2,
2539 p_current_status IN VARCHAR2,
2540 p_next_status IN VARCHAR2
2541 )
2542 RETURN VARCHAR2
2543 IS
2544 CURSOR c_stat_det IS
2545 SELECT 1 FROM DUAL
2546 WHERE EXISTS (SELECT * FROM ams_status_order_rules
2547 WHERE current_status_code = p_current_status
2548 AND next_status_code = p_next_status
2549 AND system_status_type = p_status_type ) ;
2550 l_dummy NUMBER ;
2551 BEGIN
2552
2553 OPEN c_stat_det ;
2554 FETCH c_stat_det INTO l_dummy ;
2555 CLOSE c_stat_det;
2556
2557 IF l_dummy IS NULL THEN
2558 RETURN FND_API.G_FALSE ;
2559 ELSE
2560 RETURN FND_API.G_TRUE ;
2561 END IF ;
2562 END Check_Status_Change;
2563 --======================================================================
2564 -- FUNCTION
2565 -- CurrRound
2566 --
2567 -- PURPOSE
2568 -- Returns the round value for an amount based on the currency
2569 --
2570 -- HISTORY
2571 -- 13-Sep-2001 slkrishn Create.
2572 --======================================================================
2573 FUNCTION CurrRound(
2574 p_amount IN NUMBER,
2575 p_currency_code IN VARCHAR2
2576 )
2577 RETURN NUMBER
2578 IS
2579 BEGIN
2580 RETURN gl_mc_currency_pkg.CurrRound(p_amount, p_currency_code);
2581 END CurrRound;
2582
2583 --======================================================================
2584 -- PROCEDURE
2585 -- get_install_info
2586 --
2587 -- PURPOSE
2588 -- Gets the installation information for an application
2589 -- with application_id p_dep_appl_id
2590 --
2591 -- HISTORY
2592 -- 19-Dec-2002 mayjain Create.
2593 --======================================================================
2594 procedure get_install_info(p_appl_id in number,
2595 p_dep_appl_id in number,
2596 x_status out nocopy varchar2,
2597 x_industry out nocopy varchar2,
2598 x_installed out nocopy number)
2599 IS
2600 l_installed BOOLEAN;
2601
2602 BEGIN
2603 l_installed := fnd_installation.get( appl_id => p_appl_id,
2604 dep_appl_id => p_dep_appl_id,
2605 status => x_status,
2606 industry => x_industry );
2607 IF (l_installed) THEN
2608 x_installed := 1;
2609 ELSE
2610 x_installed := 0;
2611 END IF;
2612
2613 END get_install_info;
2614
2615 --======================================================================
2616 -- PROCEDURE
2617 -- Get_Object_Name
2618 --
2619 -- PURPOSE
2620 -- Callback method for IBC to get the Associated Object name for an
2621 -- Electronic Deliverable Attachment.
2622 --
2623 -- HISTORY
2624 -- 3/7/2003 mayjain Create.
2625 --======================================================================
2626 PROCEDURE Get_Object_Name(
2627 p_association_type_code IN VARCHAR2
2628 ,p_associated_object_val1 IN VARCHAR2
2629 ,p_associated_object_val2 IN VARCHAR2
2630 ,p_associated_object_val3 IN VARCHAR2 DEFAULT NULL
2631 ,p_associated_object_val4 IN VARCHAR2 DEFAULT NULL
2632 ,p_associated_object_val5 IN VARCHAR2 DEFAULT NULL
2633 ,x_object_name OUT NOCOPY VARCHAR2
2634 ,x_object_code OUT NOCOPY VARCHAR2
2635 ,x_return_status OUT NOCOPY VARCHAR2
2636 ,x_msg_count OUT NOCOPY NUMBER
2637 ,x_msg_data OUT NOCOPY VARCHAR2
2638 )
2639 IS
2640
2641
2642 CURSOR Cur_Delv(p_delv_id IN NUMBER)
2643 IS
2644 SELECT deliverable_name
2645 FROM ams_deliverables_vl
2646 WHERE deliverable_id = p_delv_id;
2647
2648 CURSOR Cur_Camp(p_camp_id IN NUMBER)
2649 IS
2650 SELECT campaign_name
2651 FROM ams_campaigns_vl
2652 WHERE campaign_id = p_camp_id;
2653
2654 CURSOR Cur_Csch(p_csch_id IN NUMBER)
2655 IS
2656 SELECT schedule_name
2657 FROM ams_campaign_schedules_vl
2658 WHERE schedule_id = p_csch_id;
2659
2660 l_api_name CONSTANT VARCHAR2(30) := 'GET_OBJECT_NAME';
2661 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AMS_UTILITY_PVT';
2662
2663 BEGIN
2664
2665 If p_association_type_code = 'AMS_DELV' then
2666 x_return_status := FND_API.G_RET_STS_SUCCESS;
2667
2668 OPEN Cur_Delv(p_associated_object_val1);
2669 FETCH Cur_Delv INTO x_object_name;
2670 CLOSE Cur_Delv;
2671 ELSIF p_association_type_code = 'AMS_CSCH'
2672 OR p_association_type_code = 'AMS_COLLAT'
2673 OR p_association_type_code = 'AMS_PLCE'
2674 THEN
2675 x_return_status := FND_API.G_RET_STS_SUCCESS;
2676
2677 OPEN Cur_Csch(p_associated_object_val1);
2678 FETCH Cur_Csch INTO x_object_name;
2679 CLOSE Cur_Csch;
2680 ELSIF p_association_type_code = 'AMS_COLB' then
2681 IF p_associated_object_val2 = 'CSCH' THEN
2682 x_return_status := FND_API.G_RET_STS_SUCCESS;
2683
2684 OPEN Cur_Csch(p_associated_object_val1);
2685 FETCH Cur_Csch INTO x_object_name;
2686 CLOSE Cur_Csch;
2687 ELSIF p_associated_object_val2 = 'CAMP' THEN
2688 x_return_status := FND_API.G_RET_STS_SUCCESS;
2689
2690 OPEN Cur_Camp(p_associated_object_val1);
2691 FETCH Cur_Camp INTO x_object_name;
2692 CLOSE Cur_Camp;
2693 END IF;
2694
2695 END IF;
2696 -- here you can add processing for other association type as the else part
2697
2698 EXCEPTION
2699 WHEN FND_API.G_EXC_ERROR THEN
2700 x_return_status := FND_API.G_RET_STS_ERROR;
2701 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
2702 p_data => x_msg_data);
2703 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2704 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2705 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
2706 p_data => x_msg_data);
2707 WHEN OTHERS THEN
2708 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2709 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2710 THEN
2711 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
2712 END IF;
2713 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
2714 p_data => x_msg_data);
2715
2716 END Get_Object_Name;
2717
2718
2719
2720 --========================================================================
2721 -- PROCEDURE
2722 -- get_user_id
2723 --
2724 -- PURPOSE
2725 -- This api will take a resource id and give the corresponding user_id
2726 --
2727 -- NOTE
2728 --
2729 -- HISTORY
2730 -- 19-mar-2002 soagrawa Created
2731 --========================================================================
2732
2733
2734 FUNCTION get_user_id (
2735 p_resource_id IN NUMBER
2736 )
2737 RETURN NUMBER
2738 IS
2739 l_user_id NUMBER;
2740
2741 CURSOR c_user IS
2742 SELECT user_id
2743 FROM ams_jtf_rs_emp_v
2744 WHERE resource_id = p_resource_id;
2745 BEGIN
2746 OPEN c_user;
2747 FETCH c_user INTO l_user_id;
2748 IF c_user%NOTFOUND THEN
2749 l_user_id := -1;
2750 END IF;
2751 CLOSE c_user;
2752
2753 RETURN l_user_id;
2754 END get_user_id;
2755
2756
2757 --======================================================================
2758 ---------------------------------------------------------------------
2759 -- FUNCTION
2760 -- validate_locking_rules
2761 --
2762 -- PURPOSE
2763 -- This function to validate locking rules
2764 --
2765 -- HISTORY
2766 -- 27-May-2005 aranka Create.
2767 --======================================================================
2768 PROCEDURE validate_locking_rules(
2769 p_app_short_name IN VARCHAR2,
2770 p_obj_type IN VARCHAR2,
2771 p_obj_attribute IN VARCHAR2,
2772 p_obj_status IN VARCHAR2,
2773 p_fileld_ak_name_array IN JTF_VARCHAR2_TABLE_100,
2774 p_change_indicator_array IN JTF_VARCHAR2_TABLE_100,
2775 x_return_status OUT NOCOPY VARCHAR2
2776 )
2777 IS
2778
2779 l_app_id NUMBER;
2780 l_region_code VARCHAR2(100);
2781 l_type VARCHAR2(4);
2782 l_ak_attribute VARCHAR2(100);
2783 l_ak_attribute_value VARCHAR2(80);
2784 l_attribute_token VARCHAR2(1000);
2785 l_comma_char VARCHAR2(1);
2786 l_found_error BOOLEAN;
2787 l_context_resource_id NUMBER;
2788
2789 CURSOR c_get_app_id(p_app_short_name IN VARCHAR2) IS
2790 SELECT APPLICATION_ID
2791 FROM FND_APPLICATION_VL
2792 WHERE APPLICATION_SHORT_NAME = p_app_short_name;
2793
2794 CURSOR c_get_ak_region_items(p_app_id IN NUMBER, p_region_code IN VARCHAR2, p_type IN VARCHAR2, p_obj_status IN VARCHAR2, p_obj_attribute IN VARCHAR2) IS
2795 SELECT ATTRIBUTE_LABEL_LONG FROM AK_REGION_ITEMS_VL
2796 WHERE REGION_APPLICATION_ID = p_app_id
2797 AND REGION_CODE = p_region_code
2798 AND ATTRIBUTE_CODE LIKE 'AMS%RULE%'
2799 AND REGION_VALIDATION_API_PKG = p_type
2800 AND SORTBY_VIEW_ATTRIBUTE_NAME = p_obj_status
2801 AND DEFAULT_VALUE_VARCHAR2 = p_obj_attribute;
2802
2803 CURSOR c_get_ak_attribute(p_app_short_name IN VARCHAR2, p_attribute_code IN VARCHAR2 ) IS
2804 SELECT ATTRIBUTE_LABEL_LONG FROM AK_ATTRIBUTES_VL
2805 WHERE APPLICATION_SHORT_NAME = p_app_short_name
2806 AND ATTRIBUTE_CODE = p_attribute_code;
2807
2808 BEGIN
2809
2810 l_app_id := NULL;
2811 l_type := 'LOCK';
2812 l_attribute_token := null;
2813 l_region_code := 'AMS_' || p_obj_type || '_METADATA_0';
2814 l_comma_char := ',';
2815 l_found_error := false;
2816 l_context_resource_id := AMS_Utility_PVT.get_resource_id(FND_GLOBAL.user_id);
2817
2818 x_return_status := FND_API.G_RET_STS_SUCCESS;
2819 IF p_obj_type IS NULL or p_obj_attribute IS NULL or p_obj_status IS NULL or p_fileld_ak_name_array IS NULL or p_change_indicator_array IS NULL THEN
2820 x_return_status := FND_API.G_RET_STS_ERROR;
2821 -- DBMS_OUTPUT.put_line('Sam incorrect Data or null data');
2822 return;
2823 END IF;
2824
2825 IF (p_fileld_ak_name_array.count <> p_change_indicator_array.count ) THEN
2826 x_return_status := FND_API.G_RET_STS_ERROR;
2827 -- DBMS_OUTPUT.put_line('Sam incorrect Data or null data');
2828 return;
2829 END IF;
2830
2831 /* LAM rules will not apply to Admin user. Bug 5306637*/
2832 IF AMS_Access_PVT.Check_Admin_Access(l_context_resource_id) THEN
2833 x_return_status := FND_API.g_ret_sts_success;
2834 return;
2835 END IF;
2836
2837 OPEN c_get_app_id(p_app_short_name);
2838 FETCH c_get_app_id INTO l_app_id;
2839 CLOSE c_get_app_id;
2840
2841
2842 -- DBMS_OUTPUT.put_line('Sam l_app_id ' || l_app_id);
2843 -- DBMS_OUTPUT.put_line('Sam l_region_code ' || l_region_code);
2844 -- DBMS_OUTPUT.put_line('Sam l_type ' || l_type);
2845 -- DBMS_OUTPUT.put_line('Sam p_obj_status ' || p_obj_status);
2846 -- DBMS_OUTPUT.put_line('Sam p_obj_attribute ' || p_obj_attribute);
2847
2848
2849 OPEN c_get_ak_region_items(l_app_id, l_region_code, l_type, p_obj_status, p_obj_attribute);
2850 LOOP
2851 FETCH c_get_ak_region_items INTO l_ak_attribute;
2852 EXIT WHEN c_get_ak_region_items%notfound;
2853 -- DBMS_OUTPUT.put_line('Sam inside outer loop ' || l_ak_attribute);
2854 FOR i IN 1 .. p_fileld_ak_name_array.count
2855 LOOP
2856 if (l_ak_attribute = p_fileld_ak_name_array(i)) then
2857 if (p_change_indicator_array(i) = 'Y') then
2858 x_return_status := FND_API.G_RET_STS_ERROR;
2859
2860 OPEN c_get_ak_attribute(p_app_short_name, l_ak_attribute);
2861 FETCH c_get_ak_attribute INTO l_ak_attribute_value;
2862 CLOSE c_get_ak_attribute;
2863
2864 l_found_error := true;
2865 if ( l_attribute_token is null ) then
2866 l_attribute_token := l_ak_attribute_value;
2867 else
2868 l_attribute_token := l_attribute_token || l_comma_char || ' ' || l_ak_attribute_value;
2869 end if;
2870 end if;
2871 end if;
2872 END LOOP;
2873 END LOOP;
2874
2875 if ( l_found_error ) then
2876 FND_MESSAGE.Set_Name ('AMS', 'AMS_VALIDATE_LOCKING_RULES');
2877 FND_MESSAGE.Set_Token ('ATTRIBUTES', l_attribute_token);
2878 FND_MSG_PUB.Add;
2879 -- DBMS_OUTPUT.put_line('Sam Error 3 ' || l_ak_attribute_value);
2880 end if;
2881
2882 CLOSE c_get_ak_region_items;
2883 -- DBMS_OUTPUT.put_line('Sam Success');
2884 return;
2885 END validate_locking_rules;
2886
2887
2888 END AMS_Utility_PVT;
[Home] [Help]