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