[Home] [Help]
PACKAGE BODY: APPS.EDR_STANDARD_PUB
Source
1 PACKAGE BODY edr_standard_pub AS
2 /* $Header: EDRPSTDB.pls 120.0.12000000.1 2007/01/18 05:54:50 appldev ship $ */
3
4 -- globle variables
5 G_PKG_NAME CONSTANT VARCHAR2(100) := 'EDR_STANDARD_PUB';
6
7
8
9 -- --------------------------------------
10 -- API name : Get_PsigStatus
11 -- Type : Public
12 -- Pre-reqs : None
13 -- Function : Obtains signature status ('PENDING','COMPLETE','ERROR') for a given event
14 -- Parameters
15 -- IN : p_event_name VARCHAR2 event name, eg oracle.apps.gmi.item.create
16 -- p_event_key VARCHAR2 event key, eg ItemNo3125
17 -- OUT : x_psig_status VARCHAR2 event psig status,
18 -- null: no data found, SQLERROR: exception in callee
19 -- Versions : 1.0 17-Jul-03 created from edr_standard.Psig_Status
20 -- ---------------------------------------
21
22 PROCEDURE Get_PsigStatus (
23 p_api_version IN NUMBER,
24 p_init_msg_list IN VARCHAR2,
25 x_return_status OUT NOCOPY VARCHAR2,
26 x_msg_count OUT NOCOPY NUMBER,
27 x_msg_data OUT NOCOPY VARCHAR2,
28 p_event_name in varchar2,
29 p_event_key in varchar2,
30 x_psig_status out NOCOPY varchar2 )
31 IS
32 l_api_version CONSTANT NUMBER := 1.0;
33 l_api_name CONSTANT VARCHAR2(50) := 'Get_PsigStatus';
34 BEGIN
35 -- Standard call to check for call compatibility
36 IF NOT fnd_api.Compatible_API_Call ( l_api_version, p_api_version, l_api_name, G_PKG_NAME )
37 THEN RAISE fnd_api.G_EXC_UNEXPECTED_ERROR;
38 END IF;
39
40 -- Initialize message list if the caller asks me to do so
41 IF FND_API.to_Boolean( p_init_msg_list ) THEN
42 FND_MSG_PUB.initialize;
43 END IF;
44
45 -- Initialize API return status to success
46 x_return_status := FND_API.G_RET_STS_SUCCESS;
47
48 edr_standard.Psig_Status(
49 P_EVENT => p_event_name,
50 P_EVENT_KEY => p_event_key,
51 P_STATUS => x_psig_status );
52
53 -- Standard call to get message count, and if count is 1, get message info
54 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data );
55
56 EXCEPTION
57 WHEN FND_API.G_EXC_ERROR THEN
58 x_return_status := FND_API.G_RET_STS_ERROR ;
59 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data );
60 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
61 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
62 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data );
63 WHEN OTHERS THEN
64 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
65 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
66 FND_MSG_PUB.Add_Exc_Msg ( g_pkg_name, l_api_name );
67 END IF;
68 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data );
69 END Get_PsigStatus;
70
71
72 -- --------------------------------------
73 -- API name : Is_Psig_Required
74 -- Type : Public
75 -- Pre-reqs : None
76 -- Function : return True/False on signature requirement for a given event
77 -- Parameters
78 -- IN : p_event_name VARCHAR2 event name, eg oracle.apps.gmi.item.create
79 -- p_event_key VARCHAR2 event key, eg ItemNo3125
80 -- OUT : x_psig_required VARCHAR2 event psig status
81 -- Versions : 1.0 17-Jul-03 created from edr_standard.PSIG_REQUIRED
82 -- ---------------------------------------
83
84 PROCEDURE Is_eSig_Required (
85 p_api_version IN NUMBER,
86 p_init_msg_list IN VARCHAR2,
87 x_return_status OUT NOCOPY VARCHAR2,
88 x_msg_count OUT NOCOPY NUMBER,
89 x_msg_data OUT NOCOPY VARCHAR2,
90 p_event_name IN varchar2,
91 p_event_key IN varchar2,
92 x_isRequired_eSig OUT NOCOPY VARCHAR2 )
93 IS
94 l_api_version CONSTANT NUMBER := 1.0;
95 l_api_name CONSTANT VARCHAR2(50) := 'Is_Psig_Required';
96 l_isRequired BOOLEAN;
97 BEGIN
98 -- Standard call to check for call compatibility
99 IF NOT fnd_api.Compatible_API_Call ( l_api_version, p_api_version, l_api_name, G_PKG_NAME )
100 THEN RAISE fnd_api.G_EXC_UNEXPECTED_ERROR;
101 END IF;
102
103 -- Initialize message list if the caller asks me to do so
104 IF FND_API.to_Boolean( p_init_msg_list ) THEN
105 FND_MSG_PUB.initialize;
106 END IF;
107
108 -- Initialize API return status to success
109 x_return_status := FND_API.G_RET_STS_SUCCESS;
110
111 BEGIN
112 edr_standard.Psig_Required(
113 P_EVENT => p_event_name,
114 P_EVENT_KEY => p_event_key,
115 P_STATUS => l_isRequired );
116 IF l_isRequired THEN x_isRequired_eSig := FND_API.G_TRUE;
117 ELSE x_isRequired_eSig := FND_API.G_FALSE;
118 END IF;
119 EXCEPTION
120 WHEN OTHERS THEN
121 x_isRequired_eSig := FND_API.G_FALSE;
122 fnd_msg_pub.Add;
123 raise fnd_api.G_EXC_ERROR;
124 END;
125 -- fetch the message off the dictionary stack and add to API message list
126 -- would only add the last one message in the above api call
127 -- need to do this in the above api after each fnd_message.set_name/set_token
128 FND_MSG_PUB.Add;
129
130 -- Standard call to get message count, and if count is 1, get message info
131 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data );
132
133 EXCEPTION
134 WHEN FND_API.G_EXC_ERROR THEN
135 x_return_status := FND_API.G_RET_STS_ERROR ;
136 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data );
137 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
138 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
139 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data );
140 WHEN OTHERS THEN
141 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
142 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
143 FND_MSG_PUB.Add_Exc_Msg ( g_pkg_name, l_api_name );
144 END IF;
145 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data );
146 END Is_eSig_Required;
147
148
149 -- --------------------------------------
150 -- API name : Is_Erec_Required
151 -- Type : Public
152 -- Pre-reqs : None
153 -- Function : return True/False on eRecord Requirement for a given event
154 -- Parameters
155 -- IN : p_event_name VARCHAR2 event name, eg oracle.apps.gmi.item.create
156 -- p_event_key VARCHAR2 event key, eg ItemNo3125
157 -- OUT : x_erec_required VARCHAR2 event psig status
158 -- Versions : 1.0 17-Jul-03 created from edr_standard.EREC_REQUIRED
159 -- ---------------------------------------
160
161 PROCEDURE Is_eRec_Required (
162 p_api_version IN NUMBER,
163 p_init_msg_list IN VARCHAR2,
164 x_return_status OUT NOCOPY VARCHAR2,
165 x_msg_count OUT NOCOPY NUMBER,
166 x_msg_data OUT NOCOPY VARCHAR2,
167 p_event_name IN varchar2,
168 p_event_key IN varchar2,
169 x_isRequired_eRec OUT NOCOPY VARCHAR2 )
170 IS
171 l_api_version CONSTANT NUMBER := 1.0;
172 l_api_name CONSTANT VARCHAR2(50) := 'Is_Erec_Required';
173 l_isRequired BOOLEAN;
174 BEGIN
175 -- Standard call to check for call compatibility
176 IF NOT fnd_api.Compatible_API_Call ( l_api_version, p_api_version, l_api_name, G_PKG_NAME )
177 THEN RAISE fnd_api.G_EXC_UNEXPECTED_ERROR;
178 END IF;
179
180 -- Initialize message list if the caller asks me to do so
181 IF FND_API.to_Boolean( p_init_msg_list ) THEN
182 FND_MSG_PUB.initialize;
183 END IF;
184
185 -- Initialize API return status to success
186 x_return_status := FND_API.G_RET_STS_SUCCESS;
187
188 BEGIN
189 edr_standard.Erec_Required(
190 P_EVENT => p_event_name,
191 P_EVENT_KEY => p_event_key,
192 P_STATUS => l_isRequired );
193 IF l_isRequired THEN x_isRequired_eRec := FND_API.G_TRUE;
194 ELSE x_isRequired_eRec := FND_API.G_FALSE;
195 END IF;
196 EXCEPTION
197 WHEN OTHERS THEN
198 x_isRequired_eRec := FND_API.G_FALSE;
199 fnd_msg_pub.Add;
200 raise fnd_api.G_EXC_ERROR;
201 END;
202 -- fetch the message off the dictionary stack and add to API message list
203 -- would only add the last one message in the above api call
204 -- need to do this in the above api after each fnd_message.set_name/set_token
205 FND_MSG_PUB.Add;
206
207 -- Standard call to get message count, and if count is 1, get message info
208 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data );
209
210 EXCEPTION
211 WHEN FND_API.G_EXC_ERROR THEN
212 x_return_status := FND_API.G_RET_STS_ERROR ;
213 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data );
214 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
215 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
216 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data );
217 WHEN OTHERS THEN
218 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
219 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
220 FND_MSG_PUB.Add_Exc_Msg ( g_pkg_name, l_api_name );
221 END IF;
222 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data );
223 END Is_eRec_Required;
224
225
226 -- --------------------------------------
227 -- API name : Get_PsigQuery_Id
228 -- Type : Public
229 -- Pre-reqs : None
230 -- Function : obtain a query id for events based on records of event informations
231 -- Parameters
232 -- IN : p_event_name VARCHAR2 event name, eg oracle.apps.gmi.item.create
233 -- p_event_key VARCHAR2 event key, eg ItemNo3125
234 -- OUT : x_psig_status VARCHAR2 event psig status
235 -- Versions : 1.0 17-Jul-03 created from edr_standard.PSIG_QUERY
236 -- ---------------------------------------
237
238 Procedure Get_QueryId_OnEvents (
239 p_api_version IN NUMBER,
240 p_init_msg_list IN VARCHAR2,
241 p_commit IN VARCHAR2,
242 x_return_status OUT NOCOPY VARCHAR2,
243 x_msg_count OUT NOCOPY NUMBER,
244 x_msg_data OUT NOCOPY VARCHAR2,
245 p_eventQuery_recTbl IN EventDetail_tbl_type,
246 x_query_id OUT NOCOPY NUMBER )
247 IS
248 l_api_version CONSTANT NUMBER := 1.0;
249 l_api_name CONSTANT VARCHAR2(50) := 'Get_QueryId_OnEvents';
250 l_eventDetl_tbl edr_standard.eventQuery;
251 lth NUMBER;
252 BEGIN
253 -- Standard call to check for call compatibility
254 IF NOT fnd_api.Compatible_API_Call ( l_api_version, p_api_version, l_api_name, G_PKG_NAME )
255 THEN RAISE fnd_api.G_EXC_UNEXPECTED_ERROR;
256 END IF;
257
258 -- Initialize message list if the caller asks me to do so
259 IF FND_API.to_Boolean( p_init_msg_list ) THEN
260 FND_MSG_PUB.initialize;
261 END IF;
262
263 -- Initialize API return status to success
264 x_return_status := FND_API.G_RET_STS_SUCCESS;
265
266 FOR lth in 1..p_eventQuery_recTbl.COUNT LOOP
267 l_eventDetl_tbl(lth) := p_eventQuery_recTbl(lth);
268 END LOOP;
269 x_query_id := edr_standard.Psig_Query ( l_eventDetl_tbl );
270
271 IF FND_API.To_Boolean( p_commit ) THEN
272 COMMIT WORK;
273 END IF;
274 -- Standard call to get message count, and if count is 1, get message info
275 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data );
276
277 EXCEPTION
278 WHEN FND_API.G_EXC_ERROR THEN
279 x_return_status := FND_API.G_RET_STS_ERROR ;
280 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data );
281 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
282 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
283 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data );
284 WHEN OTHERS THEN
285 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
286 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
287 FND_MSG_PUB.Add_Exc_Msg ( g_pkg_name, l_api_name );
288 END IF;
289 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data );
290 END Get_QueryId_OnEvents;
291
292
293 -- --------------------------------------
294 -- API name : Display_Date
295 -- Type : Public
296 -- Pre-reqs : None
297 -- Function : convert a date to string in server timezone
298 -- Parameters
299 -- IN : p_api_version NUMBER 1.0
300 -- p_init_msg_list VARCHAR2
301 -- p_date_in DATE date that need to be converted
302 -- OUT : x_return_status VARCHAR2
303 -- x_msg_count NUMBER
304 -- x_msg_data VARCHAR2
305 -- x_date_out VARCHAR2 converted date
306 -- Versions : 1.0 17-Jul-03 created from edr_standard.DISPLAY_DATE
307 -- ---------------------------------------
308
309 PROCEDURE Display_Date (
310 p_api_version IN NUMBER,
311 p_init_msg_list IN VARCHAR2,
312 x_return_status OUT NOCOPY VARCHAR2,
313 x_msg_count OUT NOCOPY NUMBER,
314 x_msg_data OUT NOCOPY VARCHAR2,
315 p_date_in IN DATE ,
316 x_date_out OUT NOCOPY Varchar2 )
317 IS
318 l_api_name CONSTANT VARCHAR2(50) := 'Display_Date';
319 l_api_version CONSTANT NUMBER := 1.0;
320 BEGIN
321 IF NOT fnd_api.Compatible_API_Call ( l_api_version, p_api_version, l_api_name, G_PKG_NAME )
322 THEN RAISE fnd_api.G_EXC_UNEXPECTED_ERROR;
323 END IF;
324
325 IF FND_API.to_Boolean( p_init_msg_list ) THEN
326 FND_MSG_PUB.initialize;
327 END IF;
328
329 x_return_status := FND_API.G_RET_STS_SUCCESS;
330
331 edr_standard.Display_Date ( P_DATE_IN => p_date_in, P_DATE_OUT => x_date_out );
332
333 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data );
334 EXCEPTION
335 WHEN FND_API.G_EXC_ERROR THEN
336 x_return_status := FND_API.G_RET_STS_ERROR ;
337 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data );
338 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
339 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
340 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data );
341 WHEN OTHERS THEN
342 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
343 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
344 FND_MSG_PUB.Add_Exc_Msg ( g_pkg_name, l_api_name );
345 END IF;
346 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data );
347 END Display_Date;
348
349 -- BUG: 3075771 New Procedure added to return date only
350 -- --------------------------------------
351 -- API name : Display_Date_Only
352 -- Type : Public
353 -- Pre-reqs : None
354 -- Function : convert a date to string and time is truncated
355 -- Parameters
356 -- IN : p_api_version NUMBER 1.0
357 -- p_init_msg_list VARCHAR2
358 -- p_date_in DATE date that need to be converted
359 -- OUT : x_return_status VARCHAR2
360 -- x_msg_count NUMBER
361 -- x_msg_data VARCHAR2
362 -- x_date_out VARCHAR2 converted date
363 -- Versions : 1.0 07-Nov-03 created from edr_standard.DISPLAY_DATE_ONLY
364 -- ---------------------------------------
365
366 PROCEDURE Display_Date_Only (
367 p_api_version IN NUMBER,
368 p_init_msg_list IN VARCHAR2,
369 x_return_status OUT NOCOPY VARCHAR2,
370 x_msg_count OUT NOCOPY NUMBER,
371 x_msg_data OUT NOCOPY VARCHAR2,
372 p_date_in IN DATE ,
373 x_date_out OUT NOCOPY Varchar2 )
374 IS
375 l_api_name CONSTANT VARCHAR2(50) := 'Display_Date_Only';
376 l_api_version CONSTANT NUMBER := 1.0;
377 BEGIN
378 IF NOT fnd_api.Compatible_API_Call ( l_api_version, p_api_version, l_api_name, G_PKG_NAME )
379 THEN RAISE fnd_api.G_EXC_UNEXPECTED_ERROR;
380 END IF;
381
382 IF FND_API.to_Boolean( p_init_msg_list ) THEN
383 FND_MSG_PUB.initialize;
384 END IF;
385
386 x_return_status := FND_API.G_RET_STS_SUCCESS;
387
388 edr_standard.Display_Date_Only ( P_DATE_IN => p_date_in, P_DATE_OUT => x_date_out );
389
390 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data );
391 EXCEPTION
392 WHEN FND_API.G_EXC_ERROR THEN
393 x_return_status := FND_API.G_RET_STS_ERROR ;
394 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data );
395 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
396 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
397 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data );
398 WHEN OTHERS THEN
399 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
400 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
401 FND_MSG_PUB.Add_Exc_Msg ( g_pkg_name, l_api_name );
402 END IF;
403 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data );
404 END Display_Date_Only;
405
406 -- BUG: 3075771 New Procedure added to return time only
407 -- --------------------------------------
408 -- API name : Display_Time_Only
409 -- Type : Public
410 -- Pre-reqs : None
411 -- Function : convert a date to string and time is truncated
412 -- Parameters
413 -- IN : p_api_version NUMBER 1.0
414 -- p_init_msg_list VARCHAR2
415 -- p_date_in DATE date that need to be converted
416 -- OUT : x_return_status VARCHAR2
417 -- x_msg_count NUMBER
418 -- x_msg_data VARCHAR2
419 -- x_date_out VARCHAR2 converted time
420 -- Versions : 1.0 12-Nov-03 created from edr_standard.DISPLAY_TIME_ONLY
421 -- ---------------------------------------
422
423 PROCEDURE Display_Time_Only (
424 p_api_version IN NUMBER,
425 p_init_msg_list IN VARCHAR2,
426 x_return_status OUT NOCOPY VARCHAR2,
427 x_msg_count OUT NOCOPY NUMBER,
428 x_msg_data OUT NOCOPY VARCHAR2,
429 p_date_in IN DATE ,
430 x_date_out OUT NOCOPY Varchar2 )
431 IS
432 l_api_name CONSTANT VARCHAR2(50) := 'Display_Time_Only';
433 l_api_version CONSTANT NUMBER := 1.0;
434 BEGIN
435 IF NOT fnd_api.Compatible_API_Call ( l_api_version, p_api_version, l_api_name, G_PKG_NAME )
436 THEN RAISE fnd_api.G_EXC_UNEXPECTED_ERROR;
437 END IF;
438
439 IF FND_API.to_Boolean( p_init_msg_list ) THEN
440 FND_MSG_PUB.initialize;
441 END IF;
442
443 x_return_status := FND_API.G_RET_STS_SUCCESS;
444
445 edr_standard.Display_Time_Only ( P_DATE_IN => p_date_in, P_DATE_OUT => x_date_out );
446
447 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data );
448 EXCEPTION
449 WHEN FND_API.G_EXC_ERROR THEN
450 x_return_status := FND_API.G_RET_STS_ERROR ;
451 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data );
452 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
453 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
454 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data );
455 WHEN OTHERS THEN
456 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
457 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
458 FND_MSG_PUB.Add_Exc_Msg ( g_pkg_name, l_api_name );
459 END IF;
460 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data );
461 END Display_Time_Only;
462
463
464 -- --------------------------------------
465 -- API name : Get_AmeRule_VarValues
466 -- Type : Public
467 -- Pre-reqs : None
468 -- Function : check if the table column has been audited with a different old value
469 -- Parameters
470 -- IN : p_transaction_name VARCHAR2 transaction name, eg GMI ERES Item Creation
471 -- p_ameRule_id NUMBER AME rule id, eg 11400
472 -- p_ameRule_name VARCHAR2 AME rule name, eg Item Creation Rule
473 -- OUT : x_inputVar_values_tbl inputvar_values_tbl_type table of inputVar values
474 -- Versions : 1.0 17-Jul-03 created from edr_standard.GET_AMERULE_INPUT_VALUES
475 -- ---------------------------------------
476
477 -- Bug 3214495 : Start
478
479 /* This API is deprecated. Use Get_AmeRule_VariableValues */
480
481 PROCEDURE Get_AmeRule_VarValues (
482 p_api_version IN NUMBER,
483 p_init_msg_list IN VARCHAR2,
484 x_return_status OUT NOCOPY VARCHAR2,
485 x_msg_count OUT NOCOPY NUMBER,
486 x_msg_data OUT NOCOPY VARCHAR2,
487 p_transaction_name IN VARCHAR2,
488 p_ameRule_id IN NUMBER,
489 p_ameRule_name IN VARCHAR2,
490 x_inputVar_values_tbl OUT NOCOPY InputVar_Values_tbl_type )
491 IS
492 l_api_name CONSTANT VARCHAR2(50) := 'Get_AmeRule_VarValues';
493 l_api_version CONSTANT NUMBER := 1.0;
494 l_ruleVar_values EDR_STANDARD.ameruleinputvalues;
495 lth NUMBER;
496 BEGIN
497 IF NOT fnd_api.Compatible_API_Call ( l_api_version, p_api_version, l_api_name, G_PKG_NAME )
498 THEN RAISE fnd_api.G_EXC_UNEXPECTED_ERROR;
499 END IF;
500
501 IF FND_API.to_Boolean( p_init_msg_list ) THEN
502 FND_MSG_PUB.initialize;
503 END IF;
504
505 x_return_status := FND_API.G_RET_STS_SUCCESS;
506
507 edr_standard.GET_AMERULE_INPUT_VALUES (
508 ameapplication => p_transaction_name,
509 ameruleid => p_ameRule_id,
510 amerulename => p_ameRule_name,
511 ameruleinputvalues => l_ruleVar_values );
512 FOR lth IN 1..l_ruleVar_values.COUNT LOOP
513 x_inputVar_values_tbl(lth) := l_ruleVar_values(lth);
514 END LOOP;
515
516 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data );
517 EXCEPTION
518 WHEN FND_API.G_EXC_ERROR THEN
519 x_return_status := FND_API.G_RET_STS_ERROR ;
520 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data );
521 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
522 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
523 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data );
524 WHEN OTHERS THEN
525 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
526 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
527 FND_MSG_PUB.Add_Exc_Msg ( g_pkg_name, l_api_name );
528 END IF;
529 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data );
530 END Get_AmeRule_VarValues;
531
532
533 /* New API that takes Transaction Type Id as the Input parameter instead of Transaction Name */
534
535 -- --------------------------------------
536 -- API name : Get_AmeRule_VarValues
537 -- Type : Public
538 -- Pre-reqs : None
539 -- Function : check if the table column has been audited with a different old value
540 -- Parameters
541 -- IN : p_transaction_id VARCHAR2 transaction id, eg oracle.apps.gme.item.create
542 -- p_ameRule_id NUMBER AME rule id, eg 11400
543 -- p_ameRule_name VARCHAR2 AME rule name, eg Item Creation Rule
544 -- OUT : x_inputVar_values_tbl inputvar_values_tbl_type table of inputVar values
545 -- Versions : 1.0 19-Feb-05 created from edr_standard.GET_AMERULE_INPUT_VARIABLES
546 -- ---------------------------------------
547
548
549 PROCEDURE Get_AmeRule_VariableValues (
550 p_api_version IN NUMBER,
551 p_init_msg_list IN VARCHAR2,
552 x_return_status OUT NOCOPY VARCHAR2,
553 x_msg_count OUT NOCOPY NUMBER,
554 x_msg_data OUT NOCOPY VARCHAR2,
555 p_transaction_id IN VARCHAR2,
556 p_ameRule_id IN NUMBER,
557 p_ameRule_name IN VARCHAR2,
558 x_inputVar_values_tbl OUT NOCOPY InputVar_Values_tbl_type )
559 IS
560 l_api_name CONSTANT VARCHAR2(50) := 'Get_AmeRule_VarValues';
561 l_api_version CONSTANT NUMBER := 1.0;
562 l_ruleVar_values EDR_STANDARD.ameruleinputvalues;
563 lth NUMBER;
564 BEGIN
565 IF NOT fnd_api.Compatible_API_Call ( l_api_version, p_api_version, l_api_name, G_PKG_NAME )
566 THEN RAISE fnd_api.G_EXC_UNEXPECTED_ERROR;
567 END IF;
568
569 IF FND_API.to_Boolean( p_init_msg_list ) THEN
570 FND_MSG_PUB.initialize;
571 END IF;
572
573 x_return_status := FND_API.G_RET_STS_SUCCESS;
574
575 edr_standard.GET_AMERULE_INPUT_VARIABLES (
576 transactiontypeid => p_transaction_id,
577 ameruleid => p_ameRule_id,
578 amerulename => p_ameRule_name,
579 ameruleinputvalues => l_ruleVar_values );
580 FOR lth IN 1..l_ruleVar_values.COUNT LOOP
581 x_inputVar_values_tbl(lth) := l_ruleVar_values(lth);
582 END LOOP;
583
584 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data );
585 EXCEPTION
586 WHEN FND_API.G_EXC_ERROR THEN
587 x_return_status := FND_API.G_RET_STS_ERROR ;
588 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data );
589 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
590 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
591 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data );
592 WHEN OTHERS THEN
593 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
594 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
595 FND_MSG_PUB.Add_Exc_Msg ( g_pkg_name, l_api_name );
596 END IF;
597 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data );
598
599 END Get_AmeRule_VariableValues;
600
601 -- Bug 3214495 : End
602
603
604 -- --------------------------------------
605 -- API name : Is_AuditValue_Old
606 -- Type : Public
607 -- Pre-reqs : None
608 -- Function : check if the table column has been audited with a different old value
609 -- Parameters
610 -- IN : p_table_name VARCHAR2 table being audited, eg oracle.apps.gmi.item.create
611 -- p_column_name VARCHAR2 column to be checked, eg ItemNo3125
612 -- p_primKey_name VARCHAR2 name of primary key, eg ItemNo3125
613 -- p_primKey_value VARCHAR2 value of primary key, eg ItemNo3125
614 -- OUT : x_auditValue_old VARCHAR2 indicate if the audited value is updated
615 -- Versions : 1.0 17-Jul-03 created from edr_standard.COMPARE_AUDITVALUES
616 -- ---------------------------------------
617
618 Procedure Is_AuditValue_Old (
619 p_api_version IN NUMBER,
620 p_init_msg_list IN VARCHAR2,
621 x_return_status OUT NOCOPY VARCHAR2,
622 x_msg_count OUT NOCOPY NUMBER,
623 x_msg_data OUT NOCOPY VARCHAR2,
624 p_table_name IN VARCHAR2,
625 p_column_name IN VARCHAR2,
626 p_primKey_name IN VARCHAR2,
627 p_primKey_value IN VARCHAR2,
628 x_isOld_auditValue OUT NOCOPY VARCHAR2 )
629 IS
630 l_api_version CONSTANT NUMBER := 1.0;
631 l_api_name CONSTANT VARCHAR2(50) := 'Is_AuditValue_Old';
632 l_isAudited VARCHAR2(10);
633 BEGIN
634 -- Standard call to check for call compatibility
635 IF NOT fnd_api.Compatible_API_Call ( l_api_version, p_api_version, l_api_name, G_PKG_NAME )
636 THEN RAISE fnd_api.G_EXC_UNEXPECTED_ERROR;
637 END IF;
638
639 -- Initialize message list if the caller asks me to do so
640 IF FND_API.to_Boolean( p_init_msg_list ) THEN
641 FND_MSG_PUB.initialize;
642 END IF;
643
644 -- Initialize API return status to success
645 x_return_status := FND_API.G_RET_STS_SUCCESS;
646
647 BEGIN
648 l_isAudited := edr_standard.Compare_AuditValues ( p_table_name,
649 p_column_name, p_primKey_name, p_primKey_value );
650 IF l_isAudited = 'true' THEN x_isOld_auditValue := FND_API.G_TRUE;
651 ELSE x_isOld_auditValue := FND_API.G_FALSE;
652 END IF;
653 EXCEPTION
654 WHEN OTHERS THEN
655 x_isOld_auditValue := FND_API.G_FALSE;
656 fnd_message.Set_Name( 'EDR', 'EDR_PLS_STDMSG_AUDIT_DISABLED' );
657 fnd_msg_pub.Add;
658 raise fnd_api.G_EXC_ERROR;
659 END;
660 -- fetch the message off the dictionary stack and add to API message list
661 -- would only add the last one message in the above api call
662 -- need to do this in the above api after each fnd_message.set_name/set_token
663 FND_MSG_PUB.Add;
664
665 -- Standard call to get message count, and if count is 1, get message info
666 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data );
667
668 EXCEPTION
669 WHEN FND_API.G_EXC_ERROR THEN
670 x_return_status := FND_API.G_RET_STS_ERROR ;
671 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data );
672 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
673 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
674 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data );
675 WHEN OTHERS THEN
676 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
677 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
678 FND_MSG_PUB.Add_Exc_Msg ( g_pkg_name, l_api_name );
679 END IF;
680 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data );
681 END Is_AuditValue_Old;
682
683
684 -- --------------------------------------
685 -- API name : Get_Notif_Routing_Info
686 -- Type : Public
687 -- Pre-reqs : None
688 -- Function : Obtain the overriding recipient and routing comment for a routed notification
689 -- Parameters
690 -- IN : p_original_recipient VARCHAR2 original recipient
691 -- p_message_type VARCHAR2 message type
692 -- p_message_name VARCHAR2 message name
693 -- OUT : x_final_recipient VARCHAR2 final recipient
694 -- x_allrout_comments VARCHAR2 all routing comments
695 -- Versions : 1.0 17-Jul-03 created from edr_standard.FIND_WF_NTF_RECIPIENT
696 -- ---------------------------------------
697
698 PROCEDURE Get_Notif_Routing_Info (
699 p_api_version IN NUMBER,
700 p_init_msg_list IN VARCHAR2,
701 x_return_status OUT NOCOPY VARCHAR2,
702 x_msg_count OUT NOCOPY NUMBER,
703 x_msg_data OUT NOCOPY VARCHAR2,
704 p_original_recipient IN VARCHAR2,
705 p_message_type IN VARCHAR2,
706 p_message_name IN VARCHAR2,
707 x_final_recipient OUT NOCOPY VARCHAR2,
708 x_allrout_comments OUT NOCOPY VARCHAR2 )
709 IS
710 l_api_name CONSTANT VARCHAR2(50) := 'Get_Notif_Routing_Info';
711 l_api_version CONSTANT NUMBER := 1.0;
712 l_error_code NUMBER;
713 l_error_mesg VARCHAR2(1000);
714 BEGIN
715 IF NOT fnd_api.Compatible_API_Call ( l_api_version, p_api_version, l_api_name, G_PKG_NAME )
716 THEN RAISE fnd_api.G_EXC_UNEXPECTED_ERROR;
717 END IF;
718
719 -- Initialize message list if the caller asks me to do so
720 IF FND_API.to_Boolean( p_init_msg_list ) THEN
721 FND_MSG_PUB.initialize;
722 END IF;
723
724 x_return_status := FND_API.G_RET_STS_SUCCESS;
725
726 BEGIN
727 edr_standard.FIND_WF_NTF_RECIPIENT(
728 P_ORIGINAL_RECIPIENT => p_original_recipient,
729 P_MESSAGE_TYPE => p_message_type,
730 P_MESSAGE_NAME => p_message_name,
731 P_RECIPIENT => x_final_recipient,
732 P_NTF_ROUTING_COMMENTS => x_allrout_comments,
733 P_ERR_CODE => l_error_code,
734 P_ERR_MSG => l_error_mesg );
735 EXCEPTION
736 WHEN OTHERS THEN
737 IF l_error_code > 0 THEN
738 fnd_message.Set_Name( 'EDR', 'EDR_PLS_STDMSG_GENERATED' );
739 fnd_message.Set_Token( 'ERR_CODE', l_error_code );
740 fnd_message.Set_Token( 'ERR_MESG', l_error_mesg );
741 fnd_msg_pub.Add;
742 raise fnd_api.G_EXC_ERROR;
743 ELSE
744 fnd_message.Set_Name( 'EDR', 'EDR_PLS_STDMSG_UNEXPECTED' );
745 fnd_message.Set_Token( 'ERR_CODE', l_error_code );
746 fnd_message.Set_Token( 'ERR_MESG', l_error_mesg );
747 fnd_msg_pub.Add;
748 raise fnd_api.G_EXC_UNEXPECTED_ERROR;
749 END IF;
750 END;
751
752 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data );
753 EXCEPTION
754 WHEN FND_API.G_EXC_ERROR THEN
755 x_return_status := FND_API.G_RET_STS_ERROR ;
756 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data );
757 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
758 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
759 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data );
760 WHEN OTHERS THEN
761 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
762 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
763 FND_MSG_PUB.Add_Exc_Msg ( g_pkg_name, l_api_name );
764 END IF;
765 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data );
766 END Get_Notif_Routing_Info;
767
768
769
770 -- --------------------------------------
771 -- API name : Get_DescFlex_OnePrompt
772 -- Type : Public
773 -- Pre-reqs : None
774 -- Function : Obtains a single descriptive flexfield prompt for designated column
775 -- Parameters
776 -- IN : p_application_id NUMBER descriptive flexfield owner application id
777 -- p_descFlex_defName VARCHAR2 name of flexfield definiation
778 -- p_descFlex_context VARCHAR2 flex definition context (ATTRIBUTE_CATEGORY)
779 -- p_column_name VARCHAR2 name of the column corresponding to a flex segment
780 -- p_prompt_type VARCHAR2 indicate the field from which to return values
781 -- 'LEFT' --> FORM_LEFT_PROMPT; 'ABOVE' --> FORM_ABOVE_PROMPT
782 -- OUT : x_column_prompt VARCHAR2 the prompt values for the columns
783 -- Versions : 1.0 17-Jul-03 created from edr_standard.GET_DESC_FLEX_SINGLE_PROMPT
784 -- ---------------------------------------
785
786 PROCEDURE Get_DescFlex_OnePrompt (
787 p_api_version IN NUMBER,
788 p_init_msg_list IN VARCHAR2,
789 x_return_status OUT NOCOPY VARCHAR2,
790 x_msg_count OUT NOCOPY NUMBER,
791 x_msg_data OUT NOCOPY VARCHAR2,
792 p_application_id IN NUMBER,
793 p_descFlex_defName IN VARCHAR2,
794 p_descFlex_context IN VARCHAR2,
795 P_COLUMN_NAME IN VARCHAR2,
796 p_prompt_type IN VARCHAR2,
797 x_column_prompt OUT NOCOPY VARCHAR2 )
798 IS
799 l_api_version CONSTANT NUMBER := 1.0;
800 l_api_name CONSTANT VARCHAR2(50) := 'Get_DescFlex_OnePrompt';
801 l_prompt_type VARCHAR2(20);
802 BEGIN
803 -- Standard call to check for call compatibility
804 IF NOT fnd_api.Compatible_API_Call ( l_api_version, p_api_version, l_api_name, G_PKG_NAME )
805 THEN RAISE fnd_api.G_EXC_UNEXPECTED_ERROR;
806 END IF;
807
808 -- Initialize message list if the caller asks me to do so
809 IF FND_API.to_Boolean( p_init_msg_list ) THEN
810 FND_MSG_PUB.initialize;
811 END IF;
812
813 -- Initialize API return status to success
814 x_return_status := FND_API.G_RET_STS_SUCCESS;
815
816 IF p_prompt_type is NULL OR p_prompt_type = fnd_api.g_miss_char THEN
817 l_prompt_type := 'LEFT';
818 ELSE l_prompt_type := p_prompt_type;
819 END IF;
820
821 edr_standard.Get_Desc_Flex_Single_Prompt (
822 P_APPLICATION_ID => p_application_id ,
823 P_DESC_FLEX_DEF_NAME => p_descFlex_defName ,
824 P_DESC_FLEX_CONTEXT => p_descFlex_context ,
825 P_COLUMN_NAME => P_COLUMN_NAME ,
826 P_PROMPT_TYPE => l_prompt_type ,
827 P_COLUMN_PROMPT => x_column_prompt );
828
829 -- Standard call to get message count, and if count is 1, get message info
830 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data );
831
832 EXCEPTION
833 WHEN FND_API.G_EXC_ERROR THEN
834 x_return_status := FND_API.G_RET_STS_ERROR ;
835 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data );
836 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
837 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
838 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data );
839 WHEN OTHERS THEN
840 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
841 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
842 FND_MSG_PUB.Add_Exc_Msg ( g_pkg_name, l_api_name );
843 END IF;
844 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data );
845 END Get_DescFlex_OnePrompt;
846
847
848 -- --------------------------------------
849 -- API name : Get_DescFlex_AllPrompts
850 -- Type : Public
851 -- Pre-reqs : None
852 -- Function : Obtains all 30 descriptive flexfield prompts
853 -- Parameters
854 -- IN : p_application_id NUMBER descriptive flexfield owner application id
855 -- p_descFlex_defName VARCHAR2 name of flexfield definiation
856 -- p_descFlex_context VARCHAR2 flex definition context (ATTRIBUTE_CATEGORY)
857 -- p_colnNames_rec Flex_ColnName_Rec_Type
858 -- names of the columns corresponding to a flex segment
859 -- p_prompt_type VARCHAR2 indicate the field from which to return values
860 -- 'LEFT': FORM_LEFT_PROMPT; 'ABOVE': FORM_ABOVE_PROMPT
861 -- OUT : x_colnPrompts_rec Flex_ColnPrompt_Rec_Type
862 -- the prompt values for the columns
863 -- Versions : 1.0 17-Jul-03 created from edr_standard.GET_DESC_FLEX_ALL_PROMPTS
864 -- ---------------------------------------
865
866 PROCEDURE Get_DescFlex_AllPrompts (
867 p_api_version IN NUMBER,
868 p_init_msg_list IN VARCHAR2,
869 x_return_status OUT NOCOPY VARCHAR2,
870 x_msg_count OUT NOCOPY NUMBER,
871 x_msg_data OUT NOCOPY VARCHAR2,
872 p_application_id IN NUMBER,
873 p_descFlex_defName IN VARCHAR2,
874 p_descFlex_context IN VARCHAR2,
875 p_colnNames_rec IN edr_standard_pub.Flex_ColnName_Rec_Type,
876 p_prompt_type IN VARCHAR2,
877 x_colnPrompts_rec OUT NOCOPY edr_standard_pub.Flex_ColnPrompt_Rec_Type )
878 IS
879 l_api_version CONSTANT NUMBER := 1.0;
880 l_api_name CONSTANT VARCHAR2(50) := 'Get_DescFlex_AllPrompts';
881 l_prompt_type VARCHAR2(20);
882 BEGIN
883 -- Standard call to check for call compatibility
884 IF NOT fnd_api.Compatible_API_Call ( l_api_version, p_api_version, l_api_name, G_PKG_NAME )
885 THEN RAISE fnd_api.G_EXC_UNEXPECTED_ERROR;
886 END IF;
887
888 -- Initialize message list if the caller asks me to do so
889 IF FND_API.to_Boolean( p_init_msg_list ) THEN
890 FND_MSG_PUB.initialize;
891 END IF;
892
893 -- Initialize API return status to success
894 x_return_status := FND_API.G_RET_STS_SUCCESS;
895
896 IF p_prompt_type is NULL OR p_prompt_type = fnd_api.g_miss_char THEN
897 l_prompt_type := 'LEFT';
898 ELSE l_prompt_type := p_prompt_type;
899 END IF;
900
901 edr_standard.Get_Desc_Flex_All_Prompts (
902 P_APPLICATION_ID => p_application_id,
903 P_DESC_FLEX_DEF_NAME => p_descFlex_defName,
904 P_DESC_FLEX_CONTEXT => p_descFlex_context,
905 P_PROMPT_TYPE => l_prompt_type,
906 P_COLUMN1_NAME => p_colnNames_rec.P_COLUMN1_NAME,
907 P_COLUMN2_NAME => p_colnNames_rec.P_COLUMN2_NAME,
908 P_COLUMN3_NAME => p_colnNames_rec.P_COLUMN3_NAME,
909 P_COLUMN4_NAME => p_colnNames_rec.P_COLUMN4_NAME,
910 P_COLUMN5_NAME => p_colnNames_rec.P_COLUMN5_NAME,
911 P_COLUMN6_NAME => p_colnNames_rec.P_COLUMN6_NAME,
912 P_COLUMN7_NAME => p_colnNames_rec.P_COLUMN7_NAME,
913 P_COLUMN8_NAME => p_colnNames_rec.P_COLUMN8_NAME,
914 P_COLUMN9_NAME => p_colnNames_rec.P_COLUMN9_NAME,
915 P_COLUMN10_NAME => p_colnNames_rec.P_COLUMN10_NAME,
916 P_COLUMN11_NAME => p_colnNames_rec.P_COLUMN11_NAME,
917 P_COLUMN12_NAME => p_colnNames_rec.P_COLUMN12_NAME,
918 P_COLUMN13_NAME => p_colnNames_rec.P_COLUMN13_NAME,
919 P_COLUMN14_NAME => p_colnNames_rec.P_COLUMN14_NAME,
920 P_COLUMN15_NAME => p_colnNames_rec.P_COLUMN15_NAME,
921 P_COLUMN16_NAME => p_colnNames_rec.P_COLUMN16_NAME,
922 P_COLUMN17_NAME => p_colnNames_rec.P_COLUMN17_NAME,
923 P_COLUMN18_NAME => p_colnNames_rec.P_COLUMN18_NAME,
924 P_COLUMN19_NAME => p_colnNames_rec.P_COLUMN19_NAME,
925 P_COLUMN20_NAME => p_colnNames_rec.P_COLUMN20_NAME,
926 P_COLUMN21_NAME => p_colnNames_rec.P_COLUMN21_NAME,
927 P_COLUMN22_NAME => p_colnNames_rec.P_COLUMN22_NAME,
928 P_COLUMN23_NAME => p_colnNames_rec.P_COLUMN23_NAME,
929 P_COLUMN24_NAME => p_colnNames_rec.P_COLUMN24_NAME,
930 P_COLUMN25_NAME => p_colnNames_rec.P_COLUMN25_NAME,
931 P_COLUMN26_NAME => p_colnNames_rec.P_COLUMN26_NAME,
932 P_COLUMN27_NAME => p_colnNames_rec.P_COLUMN27_NAME,
933 P_COLUMN28_NAME => p_colnNames_rec.P_COLUMN28_NAME,
934 P_COLUMN29_NAME => p_colnNames_rec.P_COLUMN29_NAME,
935 P_COLUMN30_NAME => p_colnNames_rec.P_COLUMN30_NAME,
936 P_COLUMN1_PROMPT => x_colnPrompts_rec.P_COLUMN1_PROMPT,
937 P_COLUMN2_PROMPT => x_colnPrompts_rec.P_COLUMN2_PROMPT,
938 P_COLUMN3_PROMPT => x_colnPrompts_rec.P_COLUMN3_PROMPT,
939 P_COLUMN4_PROMPT => x_colnPrompts_rec.P_COLUMN4_PROMPT,
940 P_COLUMN5_PROMPT => x_colnPrompts_rec.P_COLUMN5_PROMPT,
941 P_COLUMN6_PROMPT => x_colnPrompts_rec.P_COLUMN6_PROMPT,
942 P_COLUMN7_PROMPT => x_colnPrompts_rec.P_COLUMN7_PROMPT,
943 P_COLUMN8_PROMPT => x_colnPrompts_rec.P_COLUMN8_PROMPT,
944 P_COLUMN9_PROMPT => x_colnPrompts_rec.P_COLUMN9_PROMPT,
945 P_COLUMN10_PROMPT => x_colnPrompts_rec.P_COLUMN10_PROMPT,
946 P_COLUMN11_PROMPT => x_colnPrompts_rec.P_COLUMN11_PROMPT,
947 P_COLUMN12_PROMPT => x_colnPrompts_rec.P_COLUMN12_PROMPT,
948 P_COLUMN13_PROMPT => x_colnPrompts_rec.P_COLUMN13_PROMPT,
949 P_COLUMN14_PROMPT => x_colnPrompts_rec.P_COLUMN14_PROMPT,
950 P_COLUMN15_PROMPT => x_colnPrompts_rec.P_COLUMN15_PROMPT,
951 P_COLUMN16_PROMPT => x_colnPrompts_rec.P_COLUMN16_PROMPT,
952 P_COLUMN17_PROMPT => x_colnPrompts_rec.P_COLUMN17_PROMPT,
953 P_COLUMN18_PROMPT => x_colnPrompts_rec.P_COLUMN18_PROMPT,
954 P_COLUMN19_PROMPT => x_colnPrompts_rec.P_COLUMN19_PROMPT,
955 P_COLUMN20_PROMPT => x_colnPrompts_rec.P_COLUMN20_PROMPT,
956 P_COLUMN21_PROMPT => x_colnPrompts_rec.P_COLUMN21_PROMPT,
957 P_COLUMN22_PROMPT => x_colnPrompts_rec.P_COLUMN22_PROMPT,
958 P_COLUMN23_PROMPT => x_colnPrompts_rec.P_COLUMN23_PROMPT,
959 P_COLUMN24_PROMPT => x_colnPrompts_rec.P_COLUMN24_PROMPT,
960 P_COLUMN25_PROMPT => x_colnPrompts_rec.P_COLUMN25_PROMPT,
961 P_COLUMN26_PROMPT => x_colnPrompts_rec.P_COLUMN26_PROMPT,
962 P_COLUMN27_PROMPT => x_colnPrompts_rec.P_COLUMN27_PROMPT,
963 P_COLUMN28_PROMPT => x_colnPrompts_rec.P_COLUMN28_PROMPT,
964 P_COLUMN29_PROMPT => x_colnPrompts_rec.P_COLUMN29_PROMPT,
965 P_COLUMN30_PROMPT => x_colnPrompts_rec.P_COLUMN30_PROMPT );
966
967 -- Standard call to get message count, and if count is 1, get message info
968 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data );
969
970 EXCEPTION
971 WHEN FND_API.G_EXC_ERROR THEN
972 x_return_status := FND_API.G_RET_STS_ERROR ;
973 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data );
974 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
975 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
976 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data );
977 WHEN OTHERS THEN
978 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
979 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
980 FND_MSG_PUB.Add_Exc_Msg ( g_pkg_name, l_api_name );
981 END IF;
982 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data );
983 END Get_DescFlex_AllPrompts;
984
985
986 -- --------------------------------------
987 -- API name : Get_Lookup_Meaning
988 -- Type : Public
989 -- Pre-reqs : None
990 -- Function : Obtains lookup code meaning using fnd_lookups view
991 -- Parameters
992 -- IN : p_lookup_type VARCHAR2 event name, eg oracle.apps.gmi.item.create
993 -- p_lookup_code VARCHAR2 event key, eg ItemNo3125
994 -- OUT : x_psig_status VARCHAR2 event psig status
995 -- Versions : 1.0 17-Jul-03 created from edr_standard.Get_Meaning
996 -- ---------------------------------------
997
998 PROCEDURE Get_Lookup_Meaning (
999 p_api_version IN NUMBER,
1000 p_init_msg_list IN VARCHAR2,
1001 x_return_status OUT NOCOPY VARCHAR2,
1002 x_msg_count OUT NOCOPY NUMBER,
1003 x_msg_data OUT NOCOPY VARCHAR2,
1004 p_lookup_type IN VARCHAR2,
1005 p_lookup_code IN VARCHAR2,
1006 x_lkup_meaning OUT NOCOPY VARCHAR2 )
1007 IS
1008 l_api_name CONSTANT VARCHAR2(50) := 'Get_Lookup_Meaning';
1009 l_api_version CONSTANT NUMBER := 1.0;
1010 BEGIN
1011 IF NOT fnd_api.Compatible_API_Call ( l_api_version, p_api_version, l_api_name, G_PKG_NAME )
1012 THEN RAISE fnd_api.G_EXC_UNEXPECTED_ERROR;
1013 END IF;
1014
1015 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1016 FND_MSG_PUB.initialize;
1017 END IF;
1018
1019 x_return_status := FND_API.G_RET_STS_SUCCESS;
1020
1021 edr_standard.Get_Meaning ( P_LOOKUP_TYPE => p_lookup_type,
1022 P_LOOKUP_CODE => p_lookup_code,
1023 P_MEANING => x_lkup_meaning );
1024
1025 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data );
1026 EXCEPTION
1027 WHEN FND_API.G_EXC_ERROR THEN
1028 x_return_status := FND_API.G_RET_STS_ERROR ;
1029 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data );
1030 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1031 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1032 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data );
1033 WHEN OTHERS THEN
1034 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1035 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1036 FND_MSG_PUB.Add_Exc_Msg ( g_pkg_name, l_api_name );
1037 END IF;
1038 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data );
1039 END Get_Lookup_Meaning;
1040
1041
1042 /**** It is a simplied version wrapped over PSIG_QUERY for Java api ***/
1043 -- --------------------------------------
1044 -- API name : Get_QueryId_OnParams
1045 -- Type : Public
1046 -- Pre-reqs : None
1047 -- Function : obtain a query id for events based on arrays of event parameters (name, key)
1048 -- Parameters
1049 -- IN : p_event_name VARCHAR2 event name, eg oracle.apps.gmi.item.create
1050 -- p_event_key VARCHAR2 event key, eg ItemNo3125
1051 -- OUT : x_query_id NUMBER one query id for all events details
1052 -- Versions : 1.0 17-Jul-03 created from edr_standard.Psig_Query_One
1053 -- ---------------------------------------
1054
1055 PROCEDURE Get_QueryId_OnParams (
1056 p_api_version IN NUMBER,
1057 p_init_msg_list IN VARCHAR2,
1058 p_commit IN VARCHAR2,
1059 x_return_status OUT NOCOPY VARCHAR2,
1060 x_msg_count OUT NOCOPY NUMBER,
1061 x_msg_data OUT NOCOPY VARCHAR2,
1062 p_eventNames_tbl IN FND_TABLE_OF_VARCHAR2_255,
1063 p_eventKeys_tbl IN FND_TABLE_OF_VARCHAR2_255,
1064 x_query_id OUT NOCOPY NUMBER )
1065 IS
1066 l_api_version CONSTANT NUMBER := 1.0;
1067 l_api_name CONSTANT VARCHAR2(50) := 'Get_QueryId_OnParams';
1068 BEGIN
1069 -- Standard call to check for call compatibility
1070 IF NOT fnd_api.Compatible_API_Call ( l_api_version, p_api_version, l_api_name, G_PKG_NAME )
1071 THEN RAISE fnd_api.G_EXC_UNEXPECTED_ERROR;
1072 END IF;
1073
1074 -- Initialize message list if the caller asks me to do so
1075 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1076 FND_MSG_PUB.initialize;
1077 END IF;
1078
1079 -- Initialize API return status to success
1080 x_return_status := FND_API.G_RET_STS_SUCCESS;
1081
1082 edr_standard.Psig_Query_One(
1083 P_EVENT_NAME => p_eventNames_tbl,
1084 P_EVENT_KEY => p_eventKeys_tbl,
1085 O_QUERY_ID => x_query_id );
1086
1087 IF FND_API.To_Boolean( p_commit ) THEN
1088 COMMIT WORK;
1089 END IF;
1090 -- Standard call to get message count, and if count is 1, get message info
1091 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data );
1092
1093 EXCEPTION
1094 WHEN FND_API.G_EXC_ERROR THEN
1095 x_return_status := FND_API.G_RET_STS_ERROR ;
1096 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data );
1097 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1098 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1099 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data );
1100 WHEN OTHERS THEN
1101 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1102 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1103 FND_MSG_PUB.Add_Exc_Msg ( g_pkg_name, l_api_name );
1104 END IF;
1105 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data );
1106 END Get_QueryId_OnParams;
1107
1108
1109 -- Following procedure returns current eRecord ID for
1110 -- event name and event key passed in
1111
1112 PROCEDURE GET_ERECORD_ID ( p_api_version IN NUMBER ,
1113 p_init_msg_list IN VARCHAR2 ,
1114 x_return_status OUT NOCOPY VARCHAR2 ,
1115 x_msg_count OUT NOCOPY NUMBER ,
1116 x_msg_data OUT NOCOPY VARCHAR2 ,
1117 p_event_name IN VARCHAR2 ,
1118 p_event_key IN VARCHAR2 ,
1119 x_erecord_id OUT NOCOPY NUMBER ) AS
1120
1121 l_api_name CONSTANT VARCHAR2(30) := 'GET_ERECORD_ID';
1122 l_api_version CONSTANT NUMBER := 1.0;
1123
1124 CURSOR GET_CUR_ERECORD_ID IS
1125 SELECT max(DOCUMENT_ID)
1126 FROM EDR_PSIG_DOCUMENTS
1127 WHERE EVENT_NAME = p_event_name
1128 AND EVENT_KEY = p_event_key;
1129
1130 BEGIN
1131 -- Standard call to check for call compatibility.
1132
1133 IF NOT FND_API.Compatible_API_Call (l_api_version ,
1134 p_api_version ,
1135 l_api_name ,
1136 G_PKG_NAME )
1137 THEN
1138 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1139 END IF;
1140
1141 -- Initialize message list if p_init_msg_list is set to TRUE.
1142
1143 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1144 FND_MSG_PUB.initialize;
1145 END IF;
1146
1147 -- Initialize API return status to success
1148 x_return_status := FND_API.G_RET_STS_SUCCESS;
1149
1150 -- Following code gets the current eRecord Id into out variable
1151
1152 --Bug 3935913: Start
1153 -- Added security context logic
1154 edr_ctx_pkg.set_secure_attr;
1155 OPEN GET_CUR_ERECORD_ID;
1156 FETCH GET_CUR_ERECORD_ID INTO x_erecord_id;
1157 CLOSE GET_CUR_ERECORD_ID;
1158 edr_ctx_pkg.unset_secure_attr;
1159 --Bug 3935913: End
1160 -- Standard call to get message count and if count is 1,
1161 --get message info.
1162 FND_MSG_PUB.Count_And_Get
1163 ( p_count => x_msg_count ,
1164 p_data => x_msg_data
1165 );
1166
1167 EXCEPTION
1168 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1169 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1170
1171 FND_MSG_PUB.Count_And_Get
1172 ( p_count => x_msg_count ,
1173 p_data => x_msg_data
1174 );
1175
1176 WHEN OTHERS THEN
1177 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1178
1179 IF FND_MSG_PUB.Check_Msg_Level
1180 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1181 THEN
1182 FND_MSG_PUB.Add_Exc_Msg
1183 ( G_PKG_NAME ,
1184 l_api_name
1185 );
1186 END IF;
1187
1188 FND_MSG_PUB.Count_And_Get
1189 (p_count => x_msg_count ,
1190 p_data => x_msg_data
1191 );
1192 END GET_ERECORD_ID;
1193
1194 --Bug 3437422: Start
1195 PROCEDURE USER_DATA_XML (X_USER_DATA OUT NOCOPY VARCHAR2)
1196 AS
1197 BEGIN
1198 X_USER_DATA := '#EDR_USER_DATA';
1199 END USER_DATA_XML;
1200 --Bug 3437422: End
1201
1202
1203 -- Bug 3848049 : Start
1204 -- API name : GET_ERECORD_ID
1205 -- Type : public
1206 -- Function : Accepts event name and event key and returns Latest
1207 -- eRecord ID
1208 --
1209 -- Pre-reqs : None.
1210 -- Parameters :
1211 -- IN :p_event_name IN VARCHAR2 Required
1212 -- p_event_key IN VARCHAR2 Required
1213 --
1214 -- Return Paramter
1215 -- erecord_id Return Number
1216 --
1217 -- Version :Current version 1.0
1218 -- Initial version 1.0
1219 --
1220 -- Notes :
1221
1222 FUNCTION GET_ERECORD_ID ( p_event_name IN VARCHAR2,
1223 p_event_key IN VARCHAR2) return NUMBER
1224 is
1225 L_RETURN_STATUS varchar2(100);
1226 L_MSG_COUNT number;
1227 l_MSG_DATA varchar2(4000);
1228 l_erecord_id NUMBER;
1229 BEGIN
1230
1231 GET_ERECORD_ID ( p_api_version =>1.0 ,
1232 p_init_msg_list =>'T',
1233 x_return_status=> l_return_status ,
1234 x_msg_count=>L_MSG_COUNT ,
1235 x_msg_data=>l_MSG_DATA,
1236 p_event_name=>P_EVENT_NAME ,
1237 p_event_key=>p_EVENT_KEY ,
1238 x_erecord_id =>L_ERECORD_ID);
1239 return l_ERECORD_ID;
1240
1241 END GET_ERECORD_ID;
1242 --Bug 3848049 : End
1243
1244
1245
1246
1247
1248
1249 END EDR_STANDARD_PUB;