1 PACKAGE AMS_Utility_PVT as
2 /* $Header: amsvutls.pls 120.0 2005/05/31 14:31:02 appldev noship $ */
3
4 ------------------------------------------------------------------------------
5 -- HISTORY
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 -- 07-Jul-2000 KHUNG Un-commented for R2
10 -- 13-Jul-2000 choang Added get_resource_id
11 -- 07-Aug-2000 ptendulk Added procedure to write Concurrent Programs logs.
12 -- 14-Sep-2000 holiu Added check_status_change, get_default_user_status,
13 -- get_system_status_type, get_system_status_code.
14 -- 03/27/2001 MPANDE MOved 4 Procedures from OZF to AMS
15 -- 04/13/2001 FELIU Modify create_log procedure by adding threshold_id
16 -- and budget_id
17 -- 04/23/2001 FELIU Modify create_log procedure by adding p_transaction_id,
18 -- p_notification_creat_date.
19 -- 05/24/2001 FELIU Modify create_log procedure by adding p_activity_log_id.
20 -- 16-Jun-2001 ptendulk Added check_new_status_change procedure to obsolute
21 -- the old check_status_change api
22 -- 09-Jul-2001 ptendulk Added new function Check_Status_Change
23 -- 13-Aug-2001 slkrishn Added a new function for currency rounding
24 -- 05-Nov-2001 sveerave Added specs for rec and table type of delete dependencies.
25 -- 14-Jan-2001 sveerave Added send_wf_standalone_message procedure, and
26 -- Get_Resource_Role procedures for sending standalone mesages.
27 -- 06-Jun-2002 sveerave Added overloaded check_lookup_exists
28 -- which accepts view_application_id, and query from fnd_lookups
29 -- 19-Dec-2002 mayjain Added get_install_info
30 ------------------------------------------------------------------------------
31
32 g_number CONSTANT NUMBER := 1; -- data type is number
33 g_varchar2 CONSTANT NUMBER := 2; -- data type is varchar2
34 g_ams_lookups CONSTANT VARCHAR2(12) := 'AMS_LOOKUPS';
35
36 resource_locked EXCEPTION;
37 pragma EXCEPTION_INIT(resource_locked, -54);
38
39 G_RCS_ID CONSTANT VARCHAR2(80) := 'RCS_ID';
40 G_OBJ_ID CONSTANT VARCHAR2(80) := 'OBJECT_ID';
41 G_OBJ_TYPE CONSTANT VARCHAR2(80) := 'OBJECT_TYPE';
42 G_ERRNO CONSTANT VARCHAR2(80) := 'ERROR_NUMBER';
43 G_REASON CONSTANT VARCHAR2(80) := 'REASON';
44 G_METHOD_NAME CONSTANT VARCHAR2(80) := 'METHOD_NAME';
45 G_LABEL CONSTANT VARCHAR2(80) := 'LABEL';
46 G_MODULE_NAME CONSTANT VARCHAR2(80) := 'MODULE_NAME';
47
48 --======================================================================
49 -- PROCEDURE
50 -- debug_message
51 --
52 -- PURPOSE
53 -- Writes the message to the log file for the spec'd level and module
54 -- if logging is enabled for this level and module
55 --
56 -- HISTORY
57 -- 01-Oct-2003 huili Create.
58 --======================================================================
59 PROCEDURE debug_message (p_log_level IN NUMBER,
60 p_module_name IN VARCHAR2,
61 p_text IN VARCHAR2);
62
63 --======================================================================
64 -- PROCEDURE
65 -- log_message
66 --
67 -- PURPOSE
68 -- Writes a message to the log file if this level and module is enabled
69 -- The message gets set previously with FND_MESSAGE.SET_NAME,
70 -- SET_TOKEN, etc.
71 -- The message is popped off the message dictionary stack, if POP_MESSAGE
72 -- is TRUE. Pass FALSE for POP_MESSAGE if the message will also be
73 -- displayed to the user later.
74 -- Example usage:
75 -- FND_MESSAGE.SET_NAME(...); -- Set message
76 -- FND_MESSAGE.SET_TOKEN(...); -- Set token in message
77 -- AMS_Utility_PVT.log_message(..., FALSE); -- Log message
78 --
79 -- HISTORY
80 -- 01-Oct-2003 huili Create.
81 --======================================================================
82
83 PROCEDURE log_message(p_log_level IN NUMBER,
84 p_module_name IN VARCHAR2,
85 p_RCS_ID IN VARCHAR2 := NULL,
86 p_pop_message IN BOOLEAN DEFAULT NULL);
87
88 --======================================================================
89 -- FUNCTION
90 -- logging_enabled
91 --
92 -- PURPOSE
93 -- Return whether logging is enabled for a particular level
94 --
95 -- HISTORY
96 -- 03-Oct-2003 huili Create.
97 --======================================================================
98 FUNCTION logging_enabled (p_log_level IN NUMBER)
99 RETURN BOOLEAN;
100
101
102 ---------------------------------------------------------------------
103 -- FUNCTION
104 -- check_fk_exists
105 --
106 -- PURPOSE
107 -- This function checks if a foreign key is valid.
108 --
109 -- NOTES
110 -- 1. It will return FND_API.g_true/g_false.
111 -- 2. Exception encountered will be raised to the caller.
112 -- 3. p_pk_data_type can be AMS_Global_PVT.g_number/g_varchar2.
113 -- 4. Please don't put 'AND' at the beginning of your additional
114 -- where clause.
115 ---------------------------------------------------------------------
116 FUNCTION check_fk_exists(
117 p_table_name IN VARCHAR2,
118 p_pk_name IN VARCHAR2,
119 p_pk_value IN VARCHAR2,
120 p_pk_data_type IN NUMBER := g_number,
121 p_additional_where_clause IN VARCHAR2 := NULL
122 )
123 RETURN VARCHAR2; -- FND_API.g_true/g_false
124
125
126 ---------------------------------------------------------------------
127 -- FUNCTION
128 -- check_lookup_exists
129 --
130 -- PURPOSE
131 -- This function checks if a lookup_code is valid.
132
133 ---------------------------------------------------------------------
134 FUNCTION check_lookup_exists(
135 p_lookup_table_name IN VARCHAR2 := g_ams_lookups,
136 p_lookup_type IN VARCHAR2,
137 p_lookup_code IN VARCHAR2
138 )
139 Return VARCHAR2; -- FND_API.g_true/g_false
140
141 ---------------------------------------------------------------------
142 -- FUNCTION
143 -- check_lookup_exists
144 --
145 -- PURPOSE
146 -- This function checks if a lookup_code is valid from fnd_lookups when
147 -- view_application_id is passed in.
148 ---------------------------------------------------------------------
149 FUNCTION check_lookup_exists(
150 p_lookup_type IN VARCHAR2,
151 p_lookup_code IN VARCHAR2,
152 p_view_application_id IN NUMBER
153 )
154 Return VARCHAR2; -- FND_API.g_true/g_false
155
156
157 ---------------------------------------------------------------------
158 -- FUNCTION
159 -- check_uniqueness
160 --
161 -- PURPOSE
162 -- This function is to check the uniqueness of the keys.
163 -- In order to make this function more flexible, you need to
164 -- pass in where clause of your unique key's check.
165 ---------------------------------------------------------------------
166 FUNCTION check_uniqueness(
167 p_table_name IN VARCHAR2,
168 p_where_clause IN VARCHAR2
169 )
170 RETURN VARCHAR2; -- FND_API.g_true/g_false
171
172
173 ---------------------------------------------------------------------
174 -- FUNCTION
175 -- is_Y_or_N
176 --
177 -- PURPOSE
178 -- Return FND_API.g_true if p_value='Y' or p_value='N';
179 -- return FND_API.g_flase otherwise.
180 ---------------------------------------------------------------------
181 FUNCTION is_Y_or_N(
182 p_value IN VARCHAR2
183 )
184 RETURN VARCHAR2; -- FND_API.g_true/g_false
185
186
187 ---------------------------------------------------------------------
188 -- PROCEDURE
189 -- get_qual_table_name_and_pk
190 --
191 -- PURPOSE
192 -- This procedure will return the table name and the primary key
193 -- field which are associated with each System Qualifer values.
194 -- The will allow for easier FK validation.
195 ---------------------------------------------------------------------
196 PROCEDURE get_qual_table_name_and_pk(
197 p_sys_qual IN VARCHAR2,
198 x_return_status OUT NOCOPY VARCHAR2,
199 x_table_name OUT NOCOPY VARCHAR2,
200 x_pk_name OUT NOCOPY VARCHAR2
201 );
202
203
204 ---------------------------------------------------------------------
205 -- PROCEDURE
206 -- debug_message
207 --
208 -- PURPOSE
209 -- This procedure will check the message level and try to add a
210 -- debug message into the message table of FND_MSG_API package.
211 -- Note that this debug message won't be translated.
212 ---------------------------------------------------------------------
213 PROCEDURE debug_message(
214 p_message_text IN VARCHAR2,
215 p_message_level IN NUMBER := NULL
216 );
217
218
219 ---------------------------------------------------------------------
220 -- PROCEDURE
221 -- error_message
222 --
223 -- PURPOSE
224 -- Add an error message to the message_list for an expected error.
225 ---------------------------------------------------------------------
226 PROCEDURE error_message(
227 p_message_name VARCHAR2,
228 p_token_name VARCHAR2 := NULL,
229 P_token_value VARCHAR2 := NULL
230 );
231
232
233 ---------------------------------------------------------------------
234 -- PROCEDURE
235 -- display_messages
236 --
237 -- PURPOSE
238 -- This procedure will display all messages in the message list
239 -- using DBMS_OUTPUT.put_line( ) .
240 ---------------------------------------------------------------------
241 PROCEDURE display_messages;
242
243
244 ---------------------------------------------------------------
245 -- PROCEDURE
246 -- create_log
247 --
248 -- PURPOSE
249 -- This procedure is to create a row in ams_act_logs table
250 -- to record the log
251 ---------------------------------------------------------------
252 PROCEDURE create_log(
253 x_return_status OUT NOCOPY VARCHAR2,
254 p_arc_log_used_by IN VARCHAR2,
255 p_log_used_by_id IN VARCHAR2,
256 p_msg_data IN VARCHAR2,
257 p_msg_level IN NUMBER DEFAULT NULL,
258 p_msg_type IN VARCHAR2 DEFAULT NULL,
259 p_desc IN VARCHAR2 DEFAULT NULL,
260 p_budget_id IN NUMBER DEFAULT NULL,
261 p_threshold_id IN NUMBER DEFAULT NULL,
262 p_transaction_id IN NUMBER DEFAULT NULL,
263 p_notification_creat_date IN DATE DEFAULT NULL,
264 p_activity_log_id IN NUMBER DEFAULT NULL
265 );
266
267
268 ---------------------------------------------------------------------
269 -- FUNCTION
270 -- get_object_name
271 --
272 -- PURPOSE
273 -- Return the name of the object identified by the four-letter
274 -- p_sys_arc_qualifier and the p_object_id.
275 ---------------------------------------------------------------------
276 FUNCTION get_object_name(
277 p_sys_arc_qualifier IN VARCHAR2,
278 p_object_id IN NUMBER
279 )
280 RETURN VARCHAR2;
281 PRAGMA RESTRICT_REFERENCES(get_object_name, WNDS, WNPS, RNPS);
282
283
284 ---------------------------------------------------------------------
285 -- PROCEDURE
286 -- Convert_Currency
287 -- PURPOSE
288 -- Call the GL API to convert one currency to another, which
289 -- has a different currency code.
290 ---------------------------------------------------------------------
291 PROCEDURE Convert_Currency (
292 x_return_status OUT NOCOPY VARCHAR2,
293 p_from_currency IN VARCHAR2,
294 p_to_currency IN VARCHAR2,
295 p_conv_date IN DATE DEFAULT SYSDATE,
296 p_from_amount IN NUMBER,
297 x_to_amount OUT NOCOPY NUMBER
298 );
299
300 ---------------------------------------------------------------------
301 -- PROCEDURE
302 -- get_lookup_meaning
303 --
304 -- PURPOSE
305 -- This procedure will return the meaning from ams_lookups if
306 -- you pass the right lookup_type and lookup_code
307 ---------------------------------------------------------------------
308 PROCEDURE get_lookup_meaning(
309 p_lookup_type IN VARCHAR2,
310 p_lookup_code IN VARCHAR2,
311 x_return_status OUT NOCOPY VARCHAR2,
312 x_meaning OUT NOCOPY VARCHAR2
313 );
314
315 ---------------------------------------------------------------------
316 -- FUNCTION
317 -- get_lookup_meaning
318 -- DESCRIPTION
319 -- Given a lookup_type and lookup_code, return the meaning from
320 -- AMS_LOOKUPS.
321 ---------------------------------------------------------------------
322 FUNCTION get_lookup_meaning (
323 p_lookup_type IN VARCHAR2,
324 p_lookup_code IN VARCHAR2
325 )
326 RETURN VARCHAR2;
327 PRAGMA RESTRICT_REFERENCES(get_lookup_meaning, WNDS);
328
329 ---------------------------------------------------------------------
330 -- PROCEDURE
331 -- get_System_Timezone
332 --
333 -- PURPOSE
334 -- This procedure will return the timezone from the System Timezone profile option
335 -- HISTORY created 04/24/2000 sugupta
336 ---------------------------------------------------------------------
337 PROCEDURE get_System_Timezone(
338 x_return_status OUT NOCOPY VARCHAR2,
339 x_sys_time_id OUT NOCOPY NUMBER,
340 x_sys_time_name OUT NOCOPY VARCHAR2
341 );
342 ---------------------------------------------------------------------
343 -- PROCEDURE
344 -- get_User_Timezone
345 --
346 -- PURPOSE
347 -- This procedure will return the timezone from the User Timezone profile option
348 -- HISTORY created 04/24/2000 sugupta
349 ---------------------------------------------------------------------
350 PROCEDURE get_User_Timezone(
351 x_return_status OUT NOCOPY VARCHAR2,
352 x_user_time_id OUT NOCOPY NUMBER,
353 x_user_time_name OUT NOCOPY VARCHAR2
354 );
355 ---------------------------------------------------------------------
356 -- PROCEDURE
357 -- Convert_Timezone
358 --
359 -- PURPOSE
360 -- This procedure will take the user timezone and the input time, depending on the parameter
364 -- 04/26/2000 ptendulk Modified Added a parameter which will tell
361 -- p_convert_type it will convert the input time to System timezone or sent Usertimezone
362 -- HISTORY
363 -- 04/24/2000 sugupta created
365 -- which timezone to convert time into
366 -- If the convert type is 'SYS' then input time will be
367 -- converted into system timezone else it will be
368 -- converted to user timezone sent.
369 -- 06-sep-2001 choang set default for p_user_tz_id for bug 1857131.
370 ---------------------------------------------------------------------
371 PROCEDURE Convert_Timezone(
372 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
373 x_return_status OUT NOCOPY VARCHAR2,
374 x_msg_count OUT NOCOPY NUMBER,
375 x_msg_data OUT NOCOPY VARCHAR2,
376
377 p_user_tz_id IN NUMBER := null,
378 p_in_time IN DATE, -- required
379 p_convert_type IN VARCHAR2 := 'SYS' , -- (SYS/USER)
380
381 x_out_time OUT NOCOPY DATE
382 );
383
384
385 ---------------------------------------------------------------------
386 -- FUNCTION
387 -- get_resource_name
388 -- DESCRIPTION
389 -- Given a resource ID, returns the full_name from
390 -- JTF_RS_RES_EMP_VL.
391 ---------------------------------------------------------------------
392 FUNCTION get_resource_name (
393 p_resource_id IN VARCHAR2
394 )
395 RETURN VARCHAR2;
396 PRAGMA RESTRICT_REFERENCES(get_resource_name, WNDS);
397
398
399 ---------------------------------------------------------------------
400 -- PROCEDURE
401 -- get_source_code
402 --
403 -- PURPOSE
404 -- Returns The Source Code for The Activity Type specified by the
405 -- p_activity_type parameter.
406 ---------------------------------------------------------------------
407 PROCEDURE get_source_code(
408 p_activity_type IN VARCHAR2,
409 p_activity_id IN NUMBER,
410 x_return_status OUT NOCOPY VARCHAR2,
411 x_source_code OUT NOCOPY VARCHAR2,
412 x_source_id OUT NOCOPY NUMBER
413 );
414
415 -----------------------------------------------------------------------
416 -- FUNCTION
417 -- is_in_my_division
418 --
419 -- PURPOSE
420 -- Check if the object is running within the same division
421 -- as the specified country.
422 -- -> 'Y' : the object is within my division
423 -- -> 'N' : the object is not within my division
424 --
425 -- PARAMETERS
426 -- p_object_id: (10001)
427 -- p_object_type: ('CAMP')
428 -- p_country_id: (location_hierarchy_id of the country)
429 -----------------------------------------------------------------------
430 FUNCTION is_in_my_division(
431 p_object_type IN VARCHAR2,
432 p_object_id IN NUMBER,
433 p_country_id IN NUMBER
434 )
435 RETURN VARCHAR2;
436 PRAGMA RESTRICT_REFERENCES(is_in_my_division, WNDS);
437
438 ---------------------------------------------------------------------
439 -- FUNCTION
440 -- get_product_name
441 -- DESCRIPTION
442 -- Get the product or product family name.
443 ---------------------------------------------------------------------
444 FUNCTION get_product_name(
445 p_prod_level IN VARCHAR2,
446 p_prod_id IN NUMBER,
447 p_org_id IN NUMBER := NULL
448 )
449 RETURN VARCHAR2;
450 PRAGMA RESTRICT_REFERENCES(get_product_name, WNDS);
451
452
453 ---------------------------------------------------------------------
454 -- FUNCTION
455 -- get_price_list_name
456 -- DESCRIPTION
457 -- Get the price list name for a given price list line.
458 ---------------------------------------------------------------------
459 FUNCTION get_price_list_name(
460 p_price_list_line_id IN NUMBER
461 )
462 RETURN VARCHAR2;
463 PRAGMA RESTRICT_REFERENCES(get_price_list_name, WNDS);
464
465
466 ---------------------------------------------------------------------
467 -- FUNCTION
468 -- get_uom_name
469 -- DESCRIPTION
470 -- Get the uom name for a given uom code.
471 ---------------------------------------------------------------------
472 FUNCTION get_uom_name(
473 p_uom_code IN VARCHAR2
474 )
475 RETURN VARCHAR2;
476 PRAGMA RESTRICT_REFERENCES(get_uom_name, WNDS);
477
478
479 ---------------------------------------------------------------------
480 -- FUNCTION
481 -- get_qp_lookup_meaning
482 -- DESCRIPTION
483 -- Get the meaning of the given lookup code in qp_lookups.
484 ---------------------------------------------------------------------
485 FUNCTION get_qp_lookup_meaning(
486 p_lookup_type IN VARCHAR2,
487 p_lookup_code IN VARCHAR2
488 )
489 RETURN VARCHAR2;
490 PRAGMA RESTRICT_REFERENCES(get_qp_lookup_meaning, WNDS);
491
492 ---------------------------------------------------------------------
493 -- FUNCTION
494 -- get_resource_id
495 -- DESCRIPTION
496 -- Returns resource_id from the JTF Resource module given
497 -- an AOL user_id.
498 -- NOTE
502 -- or the resource import was done before the association was made
499 -- Calling programs should check if the returned resource_id
500 -- is -1, which indicates an error in resource setup. Usually,
501 -- this means either the user does not have an associated resource
503 -- between the user and the employee.
504 ---------------------------------------------------------------------
505 FUNCTION get_resource_id (
506 p_user_id IN NUMBER
507 )
508 RETURN NUMBER;
509 PRAGMA RESTRICT_REFERENCES (get_resource_id, WNDS);
510
511
512 ---------------------------------------------------------------------
513 -- FUNCTION
514 -- Write_Conc_Log
515 -- DESCRIPTION
516 -- Writes the log for Concurrent programs
517 -- History
518 -- 07-Aug-2000 PTENDULK Created
519 -- NOTE
520 -- If the parameter p_text is passed then the value sent will be printed
521 -- as log else the messages in the stack are printed.
522 ---------------------------------------------------------------------
523 PROCEDURE Write_Conc_Log
524 ( p_text IN VARCHAR2 := NULL)
525 ;
526
527
528 -----------------------------------------------------------------------
529 -- FUNCTION
530 -- get_system_status_type
531 --
532 -- PURPOSE
533 -- Return the system_status_type in ams_status_order_rules table
534 -- for an object.
535 -----------------------------------------------------------------------
536 FUNCTION get_system_status_type(
537 p_object IN VARCHAR2
538 )
539 RETURN VARCHAR2;
540
541
542 -----------------------------------------------------------------------
543 -- FUNCTION
544 -- get_system_status_code
545 --
546 -- PURPOSE
547 -- Return the system_status_code based on user_status_id.
548 -----------------------------------------------------------------------
549 FUNCTION get_system_status_code(
550 p_user_status_id IN NUMBER
551 )
552 RETURN VARCHAR2;
553
554
555 -----------------------------------------------------------------------
556 -- FUNCTION
557 -- get_default_user_status
558 --
559 -- PURPOSE
560 -- Return the default user_status_id based on system_status_type
561 -- and system_status_code.
562 -----------------------------------------------------------------------
563 FUNCTION get_default_user_status(
564 p_status_type IN VARCHAR2,
565 p_status_code IN VARCHAR2
566 )
567 RETURN VARCHAR2;
568
569
570 -----------------------------------------------------------------------
571 -- PROCEDURE
572 -- check_status_change
573 --
574 -- PURPOSE
575 -- Check if approval is needed when changing status.
576 -----------------------------------------------------------------------
577 PROCEDURE check_status_change(
578 p_object_type IN VARCHAR2,
579 p_object_id IN NUMBER,
580 p_old_status_id IN NUMBER,
581 p_new_status_id IN NUMBER,
582 x_approval_type OUT NOCOPY VARCHAR2,
583 x_return_status OUT NOCOPY VARCHAR2
584 );
585
586 -----------------------------------------------------------------------
587 -- PROCEDURE
588 -- check_new_status_change
589 --
590 -- PURPOSE
591 -- Check if approval is needed when changing status. This will override the
592 -- previous procedure as the object attribute table is obsoleted now.
593 --
594 -- History
595 -- 16-Jun-2001 ptendulk Created to replace the old check_status_change
596 --
597 -----------------------------------------------------------------------
598 PROCEDURE Check_New_Status_Change(
599 p_object_type IN VARCHAR2,
600 p_object_id IN NUMBER,
601 p_old_status_id IN NUMBER,
602 p_new_status_id IN NUMBER,
603 p_custom_setup_id IN NUMBER,
604 x_approval_type OUT NOCOPY VARCHAR2,
605 x_return_status OUT NOCOPY VARCHAR2
606 );
607
608 ---------------------------------------------------------------------
609 -- PROCEDURE
610 -- Convert_Currency
611 -- NOTE
612 -- Moved from OZF
613 ---------------------------------------------------------------------
614
615 PROCEDURE convert_currency(
616 p_set_of_books_id IN NUMBER
617 ,p_from_currency IN VARCHAR2
618 ,p_conversion_date IN DATE
619 ,p_conversion_type IN VARCHAR2
620 ,p_conversion_rate IN NUMBER
621 ,p_amount IN NUMBER
622 ,x_return_status OUT NOCOPY VARCHAR2
623 ,x_acc_amount OUT NOCOPY NUMBER
624 ,x_rate OUT NOCOPY NUMBER);
625 ---------------------------------------------------------------------
626 -- PROCEDURE
627 -- get_code_combinations
628 --
629 -- PURPOSE
630 -- get code_combination concacnenated segments and ids
631 -- 20-Sep-2000 slkrishn Created
632 -- NOTE
633 -- Moved from OZF
634 ---------------------------------------------------------------------
635 FUNCTION get_code_combinations(
636 p_code_combination_id IN NUMBER
637 ,p_chart_of_accounts_id IN NUMBER)
638 RETURN VARCHAR2;
639
640
641 ---------------------------------------------------------------------
642 -- PROCEDURE
646 --the converted amount,exchange_rate,set_of_book_id,f-nctional_currency_code,exchange_rate_date
643 -- Convert_functional_Curr
644 -- NOTE
645 --This procedures takes in amount and converts it to the functional currency and returns
647
648 -- HISTORY
649 -- 20-Jul-2000 mpande Created.
650 -- 02/23/2001 MPAnde Updated for getting org id query
651 -- 01/13/2003 yzhao fix bug BUG 2750841(same as 2741039) - add org_id, default to null
652 --parameter x_Amount1 IN OUT NUMBER -- reqd Parameter -- amount to be converted
653 -- x_TC_CURRENCY_CODE IN OUT VARCHAR2,
654 -- x_Set_of_books_id OUT NUMBER,
655 -- x_MRC_SOB_TYPE_CODE OUT NUMBER, 'P' and 'R' - We only do it for primary ('P' because we donot supprot MRC)
656 -- x_FC_CURRENCY_CODE OUT VARCHAR2,
657 -- x_EXCHANGE_RATE_TYPE OUT VARCHAR2,-- comes from a AMS profile or what ever is passed
658 -- x_EXCHANGE_RATE_DATE OUT DATE, -- could come from a AMS profile but right now is sysdate
659 -- x_EXCHANGE_RATE OUT VARCHAR2,
660 -- x_return_status OUT VARCHAR2
661 -- The following is the rule in the GL API
662 -- If x_conversion_type = 'User', and the relationship between the
663 -- two currencies is not fixed, x_user_rate will be used as the
664 -- conversion rate to convert the amount
665 -- else no_user_rate is required
666 -- NOTE
667 -- Moved from OZF
668 ---------------------------------------------------------------------
669
670 PROCEDURE calculate_functional_curr(
671 p_from_amount IN NUMBER
672 ,p_conv_date IN DATE DEFAULT SYSDATE
673 ,p_tc_currency_code IN VARCHAR2
674 ,p_org_id IN NUMBER DEFAULT NULL
675 ,x_to_amount OUT NOCOPY NUMBER
676 ,x_set_of_books_id OUT NOCOPY NUMBER
677 ,x_mrc_sob_type_code OUT NOCOPY VARCHAR2
678 ,x_fc_currency_code OUT NOCOPY VARCHAR2
679 ,x_exchange_rate_type IN OUT NOCOPY VARCHAR2
680 ,x_exchange_rate IN OUT NOCOPY NUMBER
681 ,x_return_status OUT NOCOPY VARCHAR2);
682 ---------------------------------------------------------------------
683 -- PROCEDURE
684 -- Convert_Currency
685 -- NOTE
686
687 -- HISTORY
688 -- 20-Jul-2000 mpande Created.
689 --parameter p_from_currency IN VARCHAR2,
690 -- p_to_currency IN VARCHAR2,
691 -- p_conv_date IN DATE DEFAULT SYSDATE,
692 -- p_from_amount IN NUMBER,
693 -- x_to_amount OUT NUMBER
694 -- If x_conversion_type = 'User', and the relationship between the
695 -- two currencies is not fixed, x_user_rate will be used as the
696 -- conversion rate to convert the amount
697 -- else no_user_rate is required
698
699 -- 02/23/2001 MPAnde Updated for getting org id query
700 -- 04/07/2001 slkrishn Added p_conv_type and p_conv_rate with defaults
701 ---------------------------------------------------------------------
702 PROCEDURE convert_currency(
703 p_from_currency IN VARCHAR2
704 ,p_to_currency IN VARCHAR2
705 ,p_conv_type IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR
706 ,p_conv_rate IN NUMBER DEFAULT FND_API.G_MISS_NUM
707 ,p_conv_date IN DATE DEFAULT SYSDATE
708 ,p_from_amount IN NUMBER
709 ,x_return_status OUT NOCOPY VARCHAR2
710 ,x_to_amount OUT NOCOPY NUMBER
711 ,x_rate OUT NOCOPY NUMBER);
712
713 /*============================================================================*/
714 -- Start of Comments
715 -- NAME
716 -- Get_Resource_Role
717 --
718 -- PURPOSE
719 -- This Procedure will be return the workflow user role for
720 -- the resourceid sent
721 -- Called By
722 -- NOTES
723 -- End of Comments
724
725 /*============================================================================*/
726
727 PROCEDURE Get_Resource_Role
728 ( p_resource_id IN NUMBER,
729 x_role_name OUT NOCOPY VARCHAR2,
730 x_role_display_name OUT NOCOPY VARCHAR2 ,
731 x_return_status OUT NOCOPY VARCHAR2
732 );
733
734 --======================================================================
735 -- Procedure Name: send_wf_standalone_message
736 -- Type : Generic utility
737 -- Pre-Req :
738 -- Notes:
739 -- Common utility to send standalone message without initiating
740 -- process using workflow.
741 -- Parameters:
742 -- IN:
743 -- p_item_type IN VARCHAR2 Required Default = 'MAPGUTIL'
744 -- item type for the workflow utility.
745 -- p_message_name IN VARCHAR2 Required Default = 'GEN_STDLN_MESG'
746 -- Internal name for standalone message name
747 -- p_subject IN VARCHAR2 Required
748 -- Subject for the message
749 -- p_body IN VARCHAR2 Optional
750 -- Body for the message
751 -- p_send_to_role_name IN VARCHAR2 Optional
752 -- Role name to whom message is to be sent.
753 -- Instead of this, one can send even p_send_to_res_id
754 -- p_send_to_res_id IN NUMBER Optional
755 -- Resource Id that will be used to get role name from WF_DIRECTORY.
759 -- x_notif_id OUT NUMBER
756 -- This is required if role name is not passed.
757
758 -- OUT:
760 -- Notification Id created that is being sent to recipient.
761 -- x_return_status OUT VARCHAR2
762 -- Return status. If it is error, messages will be put in mesg pub.
763 -- History:
764 -- 11-Jan-2002 sveerave Created.
765 --======================================================================
766
767 PROCEDURE send_wf_standalone_message(
768 p_item_type IN VARCHAR2 := 'MAPGUTIL'
769 ,p_message_name IN VARCHAR2 := 'GEN_STDLN_MESG'
770 ,p_subject IN VARCHAR2
771 ,p_body IN VARCHAR2 := NULL
772 ,p_send_to_role_name IN VARCHAR2 := NULL
773 ,p_send_to_res_id IN NUMBER := NULL
774 ,x_notif_id OUT NOCOPY NUMBER
775 ,x_return_status OUT NOCOPY VARCHAR2
776 );
777
778 --======================================================================
779 -- FUNCTION
780 -- Check_Status_Change
781 --
782 -- PURPOSE
783 -- Created to check if the status change is valid or not.
784 -- Returns FND_API.G_TRUE if it is valid status change
785 -- or will return FND_API.G_FALSE
786 --
787 -- HISTORY
788 -- 09-Jul-2001 ptendulk Create.
789 --======================================================================
790 FUNCTION Check_Status_Change(
791 p_status_type IN VARCHAR2,
792 p_current_status IN VARCHAR2,
793 p_next_status IN VARCHAR2
794 )
795 RETURN VARCHAR2;
796
797 --======================================================================
798 -- FUNCTION
799 -- CurrRound
800 --
801 -- PURPOSE
802 -- Returns the round value for an amount based on the currency
803 --
804 -- HISTORY
805 -- 13-Sep-2001 slkrishn Create.
806 --======================================================================
807 FUNCTION CurrRound(
808 p_amount IN NUMBER,
809 p_currency_code IN VARCHAR2
810 )
811 RETURN NUMBER;
812
813 --======================================================================
814 -- PROCEDURE
815 -- get_install_info
816 --
817 -- PURPOSE
818 -- Gets the installation information for an application
819 -- with application_id p_dep_appl_id
820 --
821 -- HISTORY
822 -- 19-Dec-2002 mayjain Create.
823 --======================================================================
824 procedure get_install_info(p_appl_id in number,
825 p_dep_appl_id in number,
826 x_status out nocopy varchar2,
827 x_industry out nocopy varchar2,
828 x_installed out nocopy number);
829
830 --======================================================================
831 -- PROCEDURE
832 -- Get_Object_Name
833 --
834 -- PURPOSE
835 -- Callback method for IBC to get the Associated Object name for an
836 -- Electronic Deliverable Attachment.
837 --
838 -- HISTORY
839 -- 3/7/2003 mayjain Create.
840 --======================================================================
841 PROCEDURE Get_Object_Name(
842 p_association_type_code IN VARCHAR2
843 ,p_associated_object_val1 IN VARCHAR2
844 ,p_associated_object_val2 IN VARCHAR2
845 ,p_associated_object_val3 IN VARCHAR2 DEFAULT NULL
846 ,p_associated_object_val4 IN VARCHAR2 DEFAULT NULL
847 ,p_associated_object_val5 IN VARCHAR2 DEFAULT NULL
848 ,x_object_name OUT NOCOPY VARCHAR2
849 ,x_object_code OUT NOCOPY VARCHAR2
850 ,x_return_status OUT NOCOPY VARCHAR2
851 ,x_msg_count OUT NOCOPY NUMBER
852 ,x_msg_data OUT NOCOPY VARCHAR2
853 );
854
855
856 --======================================================================
857 -- PL/SQL RECORD
858 -- dependent_objects_rec_type
859 --
860 -- PURPOSE
861 -- Dependent Objects Record definition so that it can be used
862 -- across all objects for delete.
863 -- This stores details of all the dependent objects for the object to
864 -- be deleted.
865 --
866 -- HISTORY
867 -- 05-Nov-2001 sveerave Create.
868 --======================================================================
869 TYPE dependent_objects_rec_type IS RECORD
870 (
871 name VARCHAR2(240)
872 ,type VARCHAR2(30)
873 ,status VARCHAR2(30)
874 ,owner VARCHAR2(240)
875 ,deletable_flag VARCHAR2(1)
876 );
877
878 --======================================================================
879 -- PL/SQL TABLE OF RECORDS
880 -- dependent_objects_tbl_type
881 --
882 -- PURPOSE
883 -- Dependent Objects table definition which holds dependent objects rec
884 -- so that it can be used across all objects for delete.
885 -- This stores details of all the dependent objects for the object to
886 -- be deleted.
887 --
888 -- HISTORY
889 -- 05-Nov-2001 sveerave Create.
890 --======================================================================
891
892 TYPE dependent_objects_tbl_type IS TABLE OF dependent_objects_rec_type INDEX BY BINARY_INTEGER;
893
894
895
896 --========================================================================
897 -- PROCEDURE
898 -- get_user_id
899 --
900 -- PURPOSE
901 -- This api will take a resource id and give the corresponding user_id
902 --
903 -- NOTE
904 --
905 -- HISTORY
906 -- 19-mar-2002 soagrawa Created
907 --========================================================================
908
909
910 FUNCTION get_user_id (
911 p_resource_id IN NUMBER
912 )
913 RETURN NUMBER ;
914
915
916
917 ---------------------------------------------------------------------
918 -- FUNCTION
919 -- validate_locking_rules
920 --
921 -- PURPOSE
922 -- This function to validate locking rules
923 --
924 -- NOTES
925 --
926 ---------------------------------------------------------------------
927 PROCEDURE validate_locking_rules(
928 p_app_short_name IN VARCHAR2,
929 p_obj_type IN VARCHAR2,
930 p_obj_attribute IN VARCHAR2,
931 p_obj_status IN VARCHAR2,
932 p_fileld_ak_name_array IN JTF_VARCHAR2_TABLE_100,
933 p_change_indicator_array IN JTF_VARCHAR2_TABLE_100,
934 x_return_status OUT NOCOPY VARCHAR2
935 );
936
937 END AMS_Utility_PVT;
[Home] [Help]