1 PACKAGE PVX_Utility_PVT AUTHID CURRENT_USER as
2 /* $Header: pvxvutls.pls 120.4 2006/01/25 15:43:14 ktsao ship $ */
3
4
5 g_number CONSTANT NUMBER := 1; -- data type is number
6 g_varchar2 CONSTANT NUMBER := 2; -- data type is varchar2
7 g_pv_lookups CONSTANT VARCHAR2(12) := 'PV_LOOKUPS';
8
9 G_INTERACTION_LEVEL_10 CONSTANT NUMBER := 10;
10 G_INTERACTION_LEVEL_20 CONSTANT NUMBER := 20;
11 G_INTERACTION_LEVEL_30 CONSTANT NUMBER := 30;
12 G_INTERACTION_LEVEL_40 CONSTANT NUMBER := 40;
13 G_INTERACTION_LEVEL_50 CONSTANT NUMBER := 50;
14
15 resource_locked EXCEPTION;
16 API_RECORD_CHANGED EXCEPTION;
17 pragma EXCEPTION_INIT(resource_locked, -54);
18
19 /* Param record */
20
21 TYPE log_params_rec_type IS RECORD
22 (
23 param_name VARCHAR2(30)
24 ,param_value VARCHAR2(2000)
25 ,param_type VARCHAR2(50)
26 ,param_lookup_type VARCHAR2(100)
27 );
28
29 TYPE log_params_tbl_type IS TABLE OF log_params_rec_type INDEX BY BINARY_INTEGER;
30
31 ---------------------------------------------------------------------
32 -- FUNCTION
33 -- check_fk_exists
34 --
35 -- PURPOSE
36 -- This function checks if a foreign key is valid.
37 --
38 -- NOTES
39 -- 1. It will return FND_API.g_true/g_false.
40 -- 2. Exception encountered will be raised to the caller.
41 -- 4. Please don't put 'AND' at the beginning of your additional
42 -- where clause.
43 ---------------------------------------------------------------------
44 FUNCTION check_fk_exists(
45 p_table_name IN VARCHAR2,
46 p_pk_name IN VARCHAR2,
47 p_pk_value IN VARCHAR2,
48 p_pk_data_type IN NUMBER := g_number,
49 p_additional_where_clause IN VARCHAR2 := NULL
50 )
51 RETURN VARCHAR2; -- FND_API.g_true/g_false
52
53 ---------------------------------------------------------------------
54 -- FUNCTION
55 -- is_vendor_admin_user
56 --
57 -- PURPOSE
58 -- This function checks whether the user is vendor administrator
59 --
60 -- NOTES
61 -- 1. It will return FND_API.g_true/g_false.
62 -- 2. No exception block.
63 ---------------------------------------------------------------------
64 FUNCTION is_vendor_admin_user(
65 p_resource_id IN NUMBER
66 )
67 RETURN VARCHAR2; -- FND_API.g_true/g_false
68 ---------------------------------------------------------------------
69
70 -- FUNCTION
71 -- check_lookup_exists
72 --
73 -- PURPOSE
74 -- This function checks if a lookup_code is valid.
75 ---------------------------------------------------------------------
76 FUNCTION check_lookup_exists(
77 p_lookup_table_name IN VARCHAR2 := g_pv_lookups,
78 p_lookup_type IN VARCHAR2,
79 p_lookup_code IN VARCHAR2
80 )
81 Return VARCHAR2; -- FND_API.g_true/g_false
82
83
84 ---------------------------------------------------------------------
85 -- FUNCTION
86 -- check_uniqueness
87 --
88 -- PURPOSE
89 -- This function is to check the uniqueness of the keys.
90 -- In order to make this function more flexible, you need to
91 -- pass in where clause of your unique key's check.
92 ---------------------------------------------------------------------
93 FUNCTION check_uniqueness(
94 p_table_name IN VARCHAR2,
95 p_where_clause IN VARCHAR2
96 )
97 RETURN VARCHAR2; -- FND_API.g_true/g_false
98
99
100 ---------------------------------------------------------------------
101 -- FUNCTION
102 -- is_Y_or_N
103 --
104 -- PURPOSE
105 -- Return FND_API.g_true if p_value='Y' or p_value='N';
106 -- return FND_API.g_flase otherwise.
107 ---------------------------------------------------------------------
108 FUNCTION is_Y_or_N(
109 p_value IN VARCHAR2
110 )
111 RETURN VARCHAR2; -- FND_API.g_true/g_false
112
113
114 ---------------------------------------------------------------------
115 -- PROCEDURE
116 -- debug_message
117 --
118 -- PURPOSE
119 -- This procedure will check the message level and try to add a
120 -- debug message into the message table of FND_MSG_API package.
121 -- Note that this debug message won't be translated.
122 ---------------------------------------------------------------------
123 PROCEDURE debug_message(
124 p_message_text IN VARCHAR2,
125 p_message_level IN NUMBER := FND_MSG_PUB.g_msg_lvl_debug_high
126 );
127
128
129 ---------------------------------------------------------------------
130 -- PROCEDURE
131 -- error_message
132 --
133 -- PURPOSE
134 -- Add an error message to the message_list for an expected error.
135 ---------------------------------------------------------------------
136 PROCEDURE error_message(
137 p_message_name VARCHAR2,
138 p_token_name VARCHAR2 := NULL,
139 P_token_value VARCHAR2 := NULL
140 );
141
142
143 ---------------------------------------------------------------------
144 -- PROCEDURE
145 -- display_messages
146 --
147 -- PURPOSE
148 -- This procedure will display all messages in the message list
149 -- using DBMS_OUTPUT.put_line( ) .
150 ---------------------------------------------------------------------
151 PROCEDURE display_messages;
152
153
154 ---------------------------------------------------------------------
155 -- PROCEDURE
156 -- get_lookup_meaning
157 --
158 -- PURPOSE
159 -- This procedure will return the meaning from pvx_lookups if
160 -- you pass the right lookup_type and lookup_code
161 ---------------------------------------------------------------------
162 PROCEDURE get_lookup_meaning(
163 p_lookup_type IN VARCHAR2,
164 p_lookup_code IN VARCHAR2,
165 x_return_status OUT NOCOPY VARCHAR2,
166 x_meaning OUT NOCOPY VARCHAR2
167 );
168
169 ---------------------------------------------------------------------
170 -- FUNCTION
171 -- get_lookup_meaning
172 -- DESCRIPTION
173 -- Given a lookup_type and lookup_code, return the meaning from
174 -- PVX_LOOKUPS.
175 ---------------------------------------------------------------------
176 FUNCTION get_lookup_meaning (
177 p_lookup_type IN VARCHAR2,
178 p_lookup_code IN VARCHAR2
179 )
180 RETURN VARCHAR2;
181 PRAGMA RESTRICT_REFERENCES(get_lookup_meaning, WNDS);
182
183 ---------------------------------------------------------------------
184 -- PROCEDURE
185 -- get_System_Timezone
186 --
187 -- PURPOSE
188 -- This procedure will return the timezone from the System Timezone profile option
189 ---------------------------------------------------------------------
190 PROCEDURE get_System_Timezone(
191 x_return_status OUT NOCOPY VARCHAR2,
192 x_sys_time_id OUT NOCOPY NUMBER,
193 x_sys_time_name OUT NOCOPY VARCHAR2
194 );
195 ---------------------------------------------------------------------
196 -- PROCEDURE
197 -- get_User_Timezone
198 --
199 -- PURPOSE
200 -- This procedure will return the timezone from the User Timezone profile option
201 ---------------------------------------------------------------------
202 PROCEDURE get_User_Timezone(
203 x_return_status OUT NOCOPY VARCHAR2,
204 x_user_time_id OUT NOCOPY NUMBER,
205 x_user_time_name OUT NOCOPY VARCHAR2
206 );
207 ---------------------------------------------------------------------
208 -- PROCEDURE
209 -- Convert_Timezone
210 --
211 -- PURPOSE
212 -- This procedure will take the user timezone and the input time, depending on the parameter
213 -- p_convert_type it will convert the input time to System timezone or sent Usertimezone
214 ---------------------------------------------------------------------
215 PROCEDURE Convert_Timezone(
216 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
217 x_return_status OUT NOCOPY VARCHAR2,
218 x_msg_count OUT NOCOPY NUMBER,
219 x_msg_data OUT NOCOPY VARCHAR2,
220
221 p_user_tz_id IN NUMBER, -- required
222 p_in_time IN DATE, -- required
223 p_convert_type IN VARCHAR2 := 'SYS' , -- (SYS/USER)
224
225 x_out_time OUT NOCOPY DATE
226 );
227
228
229 ---------------------------------------------------------------------
230 -- FUNCTION
231 -- get_resource_name
232 -- DESCRIPTION
233 -- Given a resource ID, returns the full_name from
234 -- JTF_RS_RES_EMP_VL.
235 ---------------------------------------------------------------------
236 FUNCTION get_resource_name (
237 p_resource_id IN VARCHAR2
238 )
239 RETURN VARCHAR2;
240 PRAGMA RESTRICT_REFERENCES(get_resource_name, WNDS);
241
242
243 -- FUNCTION
244 -- get_contact_account_id
245 --
246 -- PURPOSE
247 -- This function gets partner contact account ID
248 --
249 -- NOTES
250 -- 1. It will return partner contact account ID
251 -- 2. No exception block.
252 ---------------------------------------------------------------------
253 FUNCTION get_contact_account_id(
254 p_contact_rel_party_id IN NUMBER
255 )
256 RETURN NUMBER;
257 ---------------------------------------------------------------------
258
259
260 -------------------------------------------------------------------------------
261 -- PROCEDURE
262 -- create_history_log
263 -- DESCRIPTION
264 -- Creates a history log
265 -------------------------------------------------------------------------------
266 PROCEDURE create_history_log(
267 p_arc_history_for_entity_code IN VARCHAR2,
268 p_history_for_entity_id IN NUMBER,
269 p_history_category_code IN VARCHAR2 DEFAULT NULL,
270 p_message_code IN VARCHAR2,
271 p_partner_id IN NUMBER,
272 p_access_level_flag IN VARCHAR2 DEFAULT 'V',
273 p_interaction_level IN NUMBER DEFAULT G_INTERACTION_LEVEL_10,
274 p_comments IN VARCHAR2 DEFAULT NULL,
275 p_log_params_tbl IN PVX_UTILITY_PVT.log_params_tbl_type,
276 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
277 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
278 x_return_status OUT NOCOPY VARCHAR2,
279 x_msg_count OUT NOCOPY NUMBER,
280 x_msg_data OUT NOCOPY VARCHAR2
281 );
282
283 ---------------------------------------------------------------------
284 -- FUNCTION
285 -- get_business_days
286 -- DESCRIPTION
287 -- Gets number of business days between two dates
288 ---------------------------------------------------------------------
289 PROCEDURE get_business_days
290 (
291 p_from_date IN DATE,
292 p_to_date IN DATE,
293 x_bus_days OUT NOCOPY NUMBER
294
295 );
296
297
298 ---------------------------------------------------------------------
299 -- FUNCTION
300 -- add_business_days
301 -- DESCRIPTION
302 -- Given the interval in business days, this procedure will return
303 -- the date that is past the interval from current date excluding weekends
304 ---------------------------------------------------------------------
305 PROCEDURE add_business_days
306 (
307 p_no_of_days IN NUMBER,
308 x_business_date OUT NOCOPY DATE
309
310 );
311
312 END PVX_Utility_PVT;
[Home] [Help]