1 PACKAGE AMS_Trig_PVT as
2 /* $Header: amsvtrgs.pls 120.1 2005/08/26 02:03:08 anchaudh noship $*/
3
4 -- Start of Comments
5 --
6 -- NAME
7 -- AMS_Trig_PVT
8 --
9 -- PURPOSE
10 -- This package is a Private API for managing Triggers information in
11 -- AMS. It contains specification for pl/sql records and tables
12 --
13 -- Procedures:
14 --
15 -- ams_trigger_checks:
16 --
17 -- Create_Trigger (see below for specification)
18 -- Update_Trigger (see below for specification)
19 -- Delete_Trigger (see below for specification)
20 -- Lock_Trigger (see below for specification)
21 -- Validate_Trigger (see below for specification)
22 -- Check_Trig_Items(see below for specification)
23 -- Check_Trig_Record (see below for specification)
24 -- Validate_Trig_Child_Enty (see below for specification)
25 -- Check_Req_Trig_Items (see below for specification)
26 -- Init_Trig_Rec (see below for specification)
27 -- Complete_Trig_Rec(see below for specification)
28 --
29 -- NOTES
30 --
31 -- HISTORY
32 -- 07/26/1999 ptendulk created
33 -- 04/26/2000 ptendulk Modified , Added Date columns to support timezone
34 -- 14-Feb-2001 ptendulk Modified as triggers will have tl table.
35 -- 24-sep-2001 soagrawa Removed security group id from everywhere
36 -- End of Comments
37 --
38 -- ams_triggers
39 --
40 TYPE trig_rec_type IS RECORD
41 (
42 -- PK
43 trigger_id NUMBER
44 --
45 ,last_update_date DATE
46 ,last_updated_by NUMBER
47 ,creation_date DATE
48 ,created_by NUMBER
49 ,last_update_login NUMBER
50 ,object_version_number NUMBER
51 ,process_id NUMBER
52 ,trigger_created_for_id NUMBER
53 ,arc_trigger_created_for VARCHAR2(30)
54 ,triggering_type VARCHAR2(30)
55 ,view_application_id NUMBER
56 ,timezone_id NUMBER
57 ,user_start_date_time DATE
58 ,start_date_time DATE
59 ,user_last_run_date_time DATE
60 ,last_run_date_time DATE
61 ,user_next_run_date_time DATE
62 ,next_run_date_time DATE
63 ,user_repeat_daily_start_time DATE
64 ,repeat_daily_start_time DATE
65 ,user_repeat_daily_end_time DATE
66 ,repeat_daily_end_time DATE
67 ,repeat_frequency_type VARCHAR2(30)
68 ,repeat_every_x_frequency NUMBER
69 ,user_repeat_stop_date_time DATE
70 ,repeat_stop_date_time DATE
71 ,metrics_refresh_type VARCHAR2(30)
72 -- removed by soagrawa on 24-sep-2001
73 -- ,security_group_id NUMBER
74 ,trigger_name VARCHAR2(120)
75 ,description VARCHAR2(4000)
76 ,notify_flag VARCHAR2(1)
77 ,EXECUTE_SCHEDULE_FLAG VARCHAR2(1)
78 ,TRIGGERED_STATUS VARCHAR2(30)--anchaudh added for monitors,R12.
79 ,USAGE VARCHAR2(30)--anchaudh added for monitors,R12.
80 --
81 );
82
83 --
84 -- Start of Comments
85 --SQL> desc ams_triggers ;
86 -- Name Null? Type
87 -- ----------------------------------------------------- -------- ----------------------------
88 -- TRIGGER_ID NOT NULL NUMBER
89 -- LAST_UPDATE_DATE NOT NULL DATE
90 -- LAST_UPDATED_BY NOT NULL NUMBER(15)
91 -- CREATION_DATE NOT NULL DATE
92 -- CREATED_BY NOT NULL NUMBER(15)
93 -- LAST_UPDATE_LOGIN NUMBER(15)
94 -- OBJECT_VERSION_NUMBER NUMBER(9)
95 -- PROCESS_ID NUMBER
96 -- TRIGGER_CREATED_FOR_ID NOT NULL NUMBER
97 -- ARC_TRIGGER_CREATED_FOR NOT NULL VARCHAR2(30)
98 -- TRIGGERING_TYPE NOT NULL VARCHAR2(30)
99 -- TRIGGER_NAME NOT NULL VARCHAR2(120)
100 -- VIEW_APPLICATION_ID NOT NULL NUMBER
101 -- START_DATE_TIME NOT NULL DATE
102 -- LAST_RUN_DATE_TIME DATE
103 -- NEXT_RUN_DATE_TIME DATE
104 -- REPEAT_DAILY_START_TIME DATE
105 -- REPEAT_DAILY_END_TIME DATE
106 -- REPEAT_FREQUENCY_TYPE VARCHAR2(30)
107 -- REPEAT_EVERY_X_FREQUENCY NUMBER(15)
108 -- REPEAT_STOP_DATE_TIME DATE
109 -- METRICS_REFRESH_TYPE VARCHAR2(30)
110 -- DESCRIPTION VARCHAR2(4000)
111 -- NOTIFY_FLAG VARCHAR2(1)
112 -- EXECUTE_SCHEULE_FLAG VARCHAR2(1)
113 -- End of Comments
114 --
115
116
117 -- global constants
118 ----------------------------------------------------------------------------------------
119 ----------------------------------------------------------------------------------------
120 ----------------------------------------------------------------------------------------
121 -------------------------------- AMS_TRIGGERS-------------------------------------
122 ----------------------------------------------------------------------------------------
123 ----------------------------------------------------------------------------------------
124 ----------------------------------------------------------------------------------------
125
126
127 /*****************************************************************************************/
128 -- Start of Comments
129 --
130 -- API name : Create_Trigger
131 -- Type : Private
132 -- Function : Create a row in ams_triggers table
133 --
134 -- Pre-reqs : None
135 -- Paramaeters :
136 -- IN :
137 -- standard IN parameters
138 -- p_api_version IN NUMBER := NULL Required
139 -- p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
140 -- p_commit IN VARCHAR2 := FND_API.G_FALSE,
141 -- p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
142 --
143 -- API's IN parameters
144 -- p_Trig_Rec IN trig_rec_type%ROWTYPE,
145 -- OUT :
146 -- standard OUT parameters
147 -- x_return_status OUT VARCHAR2(1)
148 -- x_msg_count OUT NUMBER
149 -- x_msg_data OUT VARCHAR2(2000)
150 --
151 --
152 -- API's OUT parameters
153 -- x_trigger_check_id OUT NUMBER
154 --
155 --
156 -- Version : Current version 1.0
157 -- Initial version 1.0
158 --
159 -- Note : 1. The following items are required parameters
160 -- p_Trig_rec.trigger_created_for_id
161 -- p_Trig_rec.arc_trigger_created_for
162 -- p_Trig_rec.triggering_type
163 -- p_Trig_rec.trigger_name
164 -- p_Trig_rec.view_application_id
165 -- p_Trig_rec.start_date_time
166 --
167 -- Business rules:
168 -- 1. ...
169 --
170 --
171 -- End Of Comments
172
173 PROCEDURE Create_Trigger
174 ( p_api_version IN NUMBER,
175 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
176 p_commit IN VARCHAR2 := FND_API.G_FALSE,
177 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
178
179 x_return_status OUT NOCOPY VARCHAR2,
180 x_msg_count OUT NOCOPY NUMBER,
181 x_msg_data OUT NOCOPY VARCHAR2,
182
183 p_trig_Rec IN trig_rec_type,
184 x_trigger_id OUT NOCOPY NUMBER
185 );
186
187 /*****************************************************************************************/
188 -- Start of Comments
189 --
190 -- API name : Update_Trigger
191 -- Type : Private
192 -- Function : Update a row in ams_triggers table
193 --
194 -- Pre-reqs : None
195 -- Paramaeters :
196 -- IN :
197 -- standard IN parameters
198 -- p_api_version IN NUMBER := NULL Required
199 -- p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
200 -- p_commit IN VARCHAR2 := FND_API.G_FALSE,
201 -- p_validation_level IN := FND_API.G_VALID_LEVEL_FULL,
202 -- API's IN parameters
203 -- p_trig_rec IN trig_rec_type
204 --
205 -- OUT :
206 -- standard OUT parameters
207 -- x_return_status OUT VARCHAR2(1)
208 -- x_msg_count OUT NUMBER
209 -- x_msg_data OUT VARCHAR2(2000)
210 --
211 --
212 -- Version : Current version 1.0
213 -- Initial version 1.0
214 --
215 -- Note : 1. p_Trig_rec.trigger_id,p_trig_rec.object_version_number are required parameters
216 -- 2. p_Trig_rec.trigger_id is not updatable
217 --
218 -- End Of Comments
219
220 PROCEDURE Update_Trigger
221 ( p_api_version IN NUMBER,
222 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
223 p_commit IN VARCHAR2 := FND_API.G_FALSE,
224 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
225
226 x_return_status OUT NOCOPY VARCHAR2,
227 x_msg_count OUT NOCOPY NUMBER,
228 x_msg_data OUT NOCOPY VARCHAR2,
229
230 p_trig_rec IN trig_rec_type
231 ) ;
232
233 /*****************************************************************************************/
234 -- Start of Comments
235 --
236 -- API name : Delete_Trigger
237 -- Type : Private
238 -- Function : Delete a row in ams_triggers table
239 --
240 -- Pre-reqs : None
241 -- Paramaeters :
242 -- IN :
243 -- standard IN parameters
244 -- p_api_version IN NUMBER := NULL Required
245 -- p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
246 -- p_commit IN VARCHAR2 := FND_API.G_FALSE
247 -- API's IN parameters
248 -- p_Trigger_id IN NUMBER
249 -- p_object_version_number IN NUMBER
250 --
251 -- OUT :
252 -- standard OUT parameters
253 -- x_return_status OUT VARCHAR2(1)
254 -- x_msg_count OUT NUMBER
255 -- x_msg_data OUT VARCHAR2(2000)
256 --
257 -- Version : Current version 1.0
258 -- Initial version 1.0
259 --
260 -- Note : 1. p_trigger_id, p_object_version_number is a required parameter
261 --
262 -- Business rules:
263 -- 1. ...
264 --
265 -- End Of Comments
266
267 PROCEDURE Delete_Trigger
268 ( p_api_version IN NUMBER,
269 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
270 p_commit IN VARCHAR2 := FND_API.G_FALSE,
271
272 x_return_status OUT NOCOPY VARCHAR2,
273 x_msg_count OUT NOCOPY NUMBER,
274 x_msg_data OUT NOCOPY VARCHAR2,
275
276 p_trigger_id IN NUMBER,
277 p_object_version_number IN NUMBER
278 ) ;
279
280
281 /******************************************************************************/
282 -- Start of Comments
283 --
284 -- API name : Lock_Trigger
285 -- Type : Private
286 -- Function : Lock a row in ams_triggers
287 --
288 -- Pre-reqs : None
289 -- Paramaeters :
290 -- IN :
291 -- standard IN parameters
292 -- p_api_version IN NUMBER := NULL Required
293 -- p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE Optional
294 --
295 -- API's IN parameters
296 -- p_Trigger_id IN NUMBER
297 -- p_object_version_number IN NUMBER
298 --
299 -- OUT :
300 -- standard OUT parameters
301 -- x_return_status OUT VARCHAR2(1)
302 -- x_msg_count OUT NUMBER
303 -- x_msg_data OUT VARCHAR2(2000)
304 --
305 --
306 -- Version : Current version 1.0
307 -- Initial version 1.0
308 --
309 -- Note : p_trigger_id,p_object_version_number is a required parameter
310 --
311 -- End Of Comments
312
313
314 PROCEDURE Lock_Trigger
315 ( p_api_version IN NUMBER,
316 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
317
318 x_return_status OUT NOCOPY VARCHAR2,
319 x_msg_count OUT NOCOPY NUMBER,
320 x_msg_data OUT NOCOPY VARCHAR2,
321
322 p_trigger_id IN NUMBER,
323 p_object_version_number IN NUMBER
324 );
325
326
327 /******************************************************************************/
328 -- Start of Comments
329 --
330 -- API name : Validate_Trigger
331 -- Type : Private
332 -- Function : Validate a row in ams_triggers table
333 --
334 -- Pre-reqs : None
335 -- Paramaeters :
336 -- IN :
337 -- standard IN parameters
338 -- p_api_version IN NUMBER := NULL Required
339 -- p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE Optional
340 -- p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
341 --
342 -- API's IN parameters
343 -- p_Trig_Rec IN trig_rec_type
344 --
345 -- OUT :
346 -- standard OUT parameters
347 -- x_return_status OUT VARCHAR2(1)
348 -- x_msg_count OUT NUMBER
349 -- x_msg_data OUT VARCHAR2(2000)
350 --
351 -- API's OUT parameters
352 -- x_Trig_rec OUT trig_rec_type
353 --
354 -- Version : Current version 1.0
355 -- Initial version 1.0
356 --
357 -- Note : 1. p_Trig_rec.trigger_id is a required parameter
358 -- 2. x_return_status will be FND_API.G_RET_STS_SUCCESS,
359 -- FND_API.G_RET_STS_ERROR or FND_API.G_RET_STS_UNEXP_ERROR
360 -- Business rules:
361 -- 1. ...
362 --
363 --
364 -- End Of Comments
365
366 PROCEDURE Validate_Trigger(
367 p_api_version IN NUMBER,
368 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
369 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
370
371 x_return_status OUT NOCOPY VARCHAR2,
372 x_msg_count OUT NOCOPY NUMBER,
373 x_msg_data OUT NOCOPY VARCHAR2,
374
375 p_trig_rec IN trig_rec_type
376 );
377
378 /******************************************************************************/
379 -- Start of Comments
380 --
381 -- Name : check_trig_items
382 -- Type : Private
383 -- Function : Validate columns in ams_triggers
384 --
385 -- Pre-reqs : None
386 -- Paramaeters :
387 -- IN :
388 -- p_Trig_rec IN trig_rec_type
389 -- p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create
390 -- OUT :
391 -- x_return_status OUT VARCHAR2
392 --
393 -- Business rules:
394 -- 1. ...
395 --
396 -- End Of Comments
397
401 x_return_status OUT NOCOPY VARCHAR2
398 PROCEDURE check_trig_items(
399 p_trig_rec IN trig_rec_type,
400 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
402 );
403
404 /*****************************************************************************************/
405 -- Start of Comments
406 --
407 -- API name : Check_Trig_Record
408 -- Type : Private
409 -- Function : Validate a row in ams_triggers table
410 --
411 -- Pre-reqs : None
412 -- Paramaeters :
413 -- IN :
414 -- standard IN parameters
415 --
416 -- API's IN parameters
417 -- p_Trig_rec IN trig_rec_type
418 -- p_Complete_rec IN trig_rec_type
419 --
420 --
421 -- OUT :
422 -- standard OUT parameters
423 -- x_return_status OUT VARCHAR2(1)
424 -- x_msg_count OUT NUMBER
425 -- x_msg_data OUT VARCHAR2(2000)
426 --
427 -- Version : Current version 1.0
428 -- Initial version 1.0
429 --
430 -- Note : x_return_status will be FND_API.G_RET_STS_SUCCESS, FND_API.G_RET_STS_ERROR, or
431 -- FND_API.G_RET_STS_UNEXP_ERROR
432 --
433 --
434 -- Business rules:
435 -- 1. ...
436 --
437 -- End Of Comments
438
439 PROCEDURE Check_Trig_Record(
440 p_trig_rec IN trig_rec_type,
441 p_complete_rec IN trig_rec_type,
442 x_return_status OUT NOCOPY VARCHAR2
443 );
444
445
446 /*****************************************************************************************/
447 -- Start of Comments
448 --
449 -- Name : Check_REQ_Trig_Items
450 -- Type : Private
451 -- Function : Check required parameters for caller needs
452 --
453 -- Pre-reqs : None
454 -- Paramaeters :
455 -- IN :
456 -- p_Trig_rec IN trig_rec_type Required
457 --
458 -- OUT :
459 -- x_return_status OUT VARCHAR2
460 --
461 -- Business rules:
462 -- 1. ...
463 --
464 -- End Of Comments
465
466 PROCEDURE Check_Trig_Req_Items
467 ( p_trig_rec IN trig_rec_type,
468 x_return_status OUT NOCOPY VARCHAR2
469 );
470
471 /*****************************************************************************************/
472 -- Start of Comments
473 --
474 -- Name : Init_Trig_Rec
475 -- Type : Private
476 -- Function : Initialize the Trigger Record type before Update
477 --
478 -- Pre-reqs : None
479 -- Paramaeters :
480 -- OUT :
481 -- x_return_status OUT VARCHAR2
482 --
483 -- Business rules:
484 -- 1. ...
485 --
486 -- End Of Comments
487 PROCEDURE Init_Trig_Rec(
488 x_trig_rec OUT NOCOPY trig_rec_type
489 );
490
491 /*****************************************************************************************/
492 -- Start of Comments
493 --
494 -- Name : Complete_Trig_Rec
495 -- Type : Private
496 -- Function : Complete the Trigger Record type if the values are not passed
497 -- for Updation
498 --
499 -- Pre-reqs : None
500 -- Paramaeters :
501 -- IN :
502 -- p_trig_rec IN trig_rec_type
503 -- OUT :
504 -- x_complete_rec OUT trig_rec_type
505 --
506 -- Business rules:
507 -- 1. ...
508 --
509 -- End Of Comments
510
511 PROCEDURE Complete_Trig_Rec(
512 p_trig_rec IN trig_rec_type,
513 x_complete_rec OUT NOCOPY trig_rec_type
514 );
515
516
517 END AMS_Trig_PVT;
[Home] [Help]