1 PACKAGE AMS_FORMULA_PVT AUTHID CURRENT_USER AS
2 /* $Header: amsvfmls.pls 115.7 2002/11/22 01:52:55 yzhao ship $*/
3 -- Start of Comments
4 --
5 -- NAME
6 -- AMS_FORMULA_PVT
7 --
8 -- PURPOSE
9 -- This Package provides procedures to allow Insertion, Deletion,
10 -- Update and Locking of Marketing On-Line formulas and formula entries.
11 --
12 -- This Package also stores the seeded Functions which can be executed as
13 -- part of a formula entry.
14 --
15 -- This Package also provides functions to execute a formula.
16 --
17 -- Procedures:
18 --
19 -- Create_Formula.
20 -- Update_Formula.
21 -- Delete_Formula.
22 -- Lock_Formula.
23 -- Execute_Formula.
24
25 -- Create_Formula_Entry.
26 -- Update_Formula_Entry.
27 -- Delete_Formula_Entry.
28 -- Lock_Formula_Entry.
29
30 -- NOTES
31 --
32 --
33 -- HISTORY
34 -- 31-May-2000 tdonohoe created
35 -- End of Comments
36
37 TYPE ams_formula_rec_type
38 IS RECORD ( formula_id number
39 ,activity_metric_id number
40 ,level_depth number
41 ,parent_formula_id number
42 ,last_update_date date
43 ,last_updated_by number
44 ,creation_date date
45 ,created_by number
46 ,last_update_login number
47 ,object_version_number number
48 ,formula_type varchar2(30));
49
50 TYPE ams_formula_entry_rec_type
51 IS RECORD ( formula_entry_id number
52 ,formula_id number
53 ,order_number number
54 ,formula_entry_type varchar2(30)
55 ,formula_entry_value varchar2(150)
56 ,metric_column_value varchar2(30)
57 ,formula_entry_operator varchar2(30)
58 ,last_update_date date
59 ,last_updated_by number
60 ,creation_date date
61 ,created_by number
62 ,last_update_login number
63 ,object_version_number number);
64
65 ---------------------------------------------------------------------
66 -- Start of comments
67 -- API Name Execute_Formula
68 -- Type Private
69 -- Pre-reqs None.
70 -- Function Executes an Acttivity Metric Formula and stores the
71 -- result in the the Ams_Act_Metric_Facts table.
72 -- Parameters
73 -- IN p_api_version IN NUMBER Required
74 -- p_init_msg_list IN VARCHAR2 Optional
75 -- Default := FND_API.G_FALSE
76 -- p_commit IN VARCHAR2 Optional
77 -- Default := FND_API.G_FALSE
78 -- p_validation_level IN NUMBER Optional
79 -- Default := FND_API.G_VALID_LEVEL_FULL
80 -- p_fornula_id IN NUMBER Required
81 -- OUT x_return_status OUT VARCHAR2
82 -- x_msg_count OUT NUMBER
83 -- x_msg_data OUT VARCHAR2
84 -- x_result OUT NUMBER
85 -- Version Current version: 1.0
86 -- Previous version: 1.0
87 -- Initial version: 1.0
88 -- End of comments
89 ---------------------------------------------------------------------
90 PROCEDURE Execute_Formula (
91 p_api_version IN NUMBER,
92 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
93 p_commit IN VARCHAR2 := FND_API.G_FALSE,
94 p_validation_level IN NUMBER := FND_API.G_Valid_Level_Full,
95
96 x_return_status OUT NOCOPY VARCHAR2,
97 x_msg_count OUT NOCOPY NUMBER,
98 x_msg_data OUT NOCOPY VARCHAR2,
99 x_result OUT NOCOPY NUMBER,
100
101 p_formula_id IN NUMBER,
102 p_hierarchy_id IN NUMBER,
103 p_parent_node_id IN NUMBER,
104 p_node_id IN NUMBER
105 );
106
107 ---------------------------------------------------------------------
108 -- Start of comments
109 -- API Name Create_Formula
110 -- Type Private
111 -- Pre-reqs None.
112 -- Function Creates an Activity Metric Formula.
113 -- Parameters
114 -- IN p_api_version IN NUMBER Required
115 -- p_init_msg_list IN VARCHAR2 Optional
116 -- Default := FND_API.G_FALSE
117 -- p_commit IN VARCHAR2 Optional
118 -- Default := FND_API.G_FALSE
119 -- p_validation_level IN NUMBER Optional
120 -- Default := FND_API.G_VALID_LEVEL_FULL
121 -- p_act_forecast_rec IN ams_formula_rec_type Required
122 -- OUT x_return_status OUT VARCHAR2
123 -- x_msg_count OUT NUMBER
124 -- x_msg_data OUT VARCHAR2
125 -- Version Current version: 1.0
126 -- Previous version: 1.0
127 -- Initial version: 1.0
128 -- End of comments
129 ---------------------------------------------------------------------
130
131 PROCEDURE Create_Formula (
132 p_api_version IN NUMBER,
133 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
134 p_commit IN VARCHAR2 := FND_API.G_FALSE,
135 p_validation_level IN NUMBER := FND_API.G_Valid_Level_Full,
136
137 x_return_status OUT NOCOPY VARCHAR2,
138 x_msg_count OUT NOCOPY NUMBER,
139 x_msg_data OUT NOCOPY VARCHAR2,
140
141 p_formula_rec IN ams_formula_rec_type,
142 x_formula_id OUT NOCOPY NUMBER
143 );
144
145
146
147 -- Start of comments
148 -- API Name Update_Formula
149 -- Type Private
150 -- Pre-reqs None.
151 -- Function Updates the activity metric formula.
152 -- Parameters
153 -- IN p_api_version IN NUMBER Required
154 -- p_init_msg_list IN VARCHAR2 Optional
155 -- Default := FND_API.G_FALSE
156 -- p_commit IN VARCHAR2 Optional
157 -- Default := FND_API.G_FALSE
158 -- p_validation_level IN NUMBER Optional
159 -- Default := FND_API.G_VALID_LEVEL_FULL
160 -- p_formula_rec IN ams_formula_rec_type Required
161 -- OUT x_return_status OUT VARCHAR2
162 -- x_msg_count OUT NUMBER
163 -- x_msg_data OUT VARCHAR2
164 -- Version Current version: 1.0
165 -- Previous version: 1.0
166 -- Initial version: 1.0
167 -- End of comments
168
169 PROCEDURE Update_Formula (
170 p_api_version IN NUMBER,
171 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
172 p_commit IN VARCHAR2 := FND_API.G_FALSE,
173 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
174
175 x_return_status OUT NOCOPY VARCHAR2,
176 x_msg_count OUT NOCOPY NUMBER,
177 x_msg_data OUT NOCOPY VARCHAR2,
178
179 p_formula_rec IN ams_formula_rec_type
180 );
181
182
183 -- Start of comments
184 -- API Name Delete_Formula
185 -- Type Private
186 -- Pre-reqs None.
187 -- Function Deletes the Activity Metric Formula.
188 -- Parameters
189 -- IN p_api_version IN NUMBER Required
190 -- p_init_msg_list IN VARCHAR2 Optional
191 -- Default := FND_API.G_FALSE
192 -- p_commit IN VARCHAR2 Optional
193 -- Default := FND_API.G_FALSE
194 -- p_forecast_id IN NUMBER Required
195 -- p_object_version_number IN NUMBER
196 -- OUT x_return_status OUT VARCHAR2
197 -- x_msg_count OUT NUMBER
198 -- x_msg_data OUT VARCHAR2
199 -- Version Current version: 1.0
200 -- Previous version: 1.0
201 -- Initial version: 1.0
202 -- End of comments
203
204 PROCEDURE Delete_Formula (
205 p_api_version IN NUMBER,
206 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
207 p_commit IN VARCHAR2 := FND_API.G_FALSE,
208
209 x_return_status OUT NOCOPY VARCHAR2,
210 x_msg_count OUT NOCOPY NUMBER,
211 x_msg_data OUT NOCOPY VARCHAR2,
212
213 p_formula_id IN NUMBER ,
214 p_object_version_number IN NUMBER
215 );
216
217
218
219 -- Start of comments
220 -- API Name Lock_Formula
221 -- Type Private
222 -- Pre-reqs None.
223 -- Function Lock the given row in AMS_ACT_METRIC_FORMULAS table.
224 -- Parameters
225 -- IN p_api_version IN NUMBER Required
226 -- p_init_msg_list IN VARCHAR2 Optional
227 -- Default := FND_API.G_FALSE
228 -- p_commit IN VARCHAR2 Optional
229 -- Default := FND_API.G_FALSE
230 -- p_formula_id IN NUMBER Required
231 -- p_object_version_number IN NUMBER Required
232 -- OUT x_return_status OUT VARCHAR2
233 -- x_msg_count OUT NUMBER
234 -- x_msg_data OUT VARCHAR2
235 -- Version Current version: 1.0
236 -- Previous version: 1.0
237 -- Initial version: 1.0
238 -- End of comments
239
240
241 PROCEDURE Lock_Formula (
242 p_api_version IN NUMBER,
243 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
244
245 x_return_status OUT NOCOPY VARCHAR2,
246 x_msg_count OUT NOCOPY NUMBER,
247 x_msg_data OUT NOCOPY VARCHAR2,
248
249 p_formula_id IN NUMBER,
250 p_object_version_number IN NUMBER
251 );
252
253
254
255 -- Start of comments
256 -- API Name Validate_Formula
257 -- Type Private
258 -- Pre-reqs None.
259 -- Function Validate items in the activity metric forecast table.
260
261 -- Parameters
262 -- IN p_api_version IN NUMBER Required
263 -- p_init_msg_list IN VARCHAR2 Optional
264 -- Default := FND_API.G_FALSE
265 -- p_commit IN VARCHAR2 Optional
266 -- Default := FND_API.G_FALSE
267 -- p_validation_level IN NUMBER Optional
268 -- Default := FND_API.G_VALID_LEVEL_FULL
269 -- p_formula_rec IN ams_formula_rec_type Required
270 -- OUT x_return_status OUT VARCHAR2
271 -- x_msg_count OUT NUMBER
272 -- x_msg_data OUT VARCHAR2
273 -- Version Current version: 1.0
274 -- Previous version: 1.0
275 -- Initial version: 1.0
276 -- End of comments
277
278
279 PROCEDURE Validate_Formula (
280 p_api_version IN NUMBER,
281 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
282 p_validation_level IN NUMBER := FND_API.G_Valid_Level_Full,
283
284 x_return_status OUT NOCOPY VARCHAR2,
285 x_msg_count OUT NOCOPY NUMBER,
286 x_msg_data OUT NOCOPY VARCHAR2,
287
288 p_formula_rec IN ams_formula_rec_type
289 );
290
291
292 -- Start of comments
293 -- API Name Validate_Formula_Items
294 -- Type Private
295 -- Pre-reqs None.
296 -- Function Validate Activity Metric Formula Items
297 -- Parameters
298 -- IN p_formula_rec IN ams_formula_rec_type Required
299 -- p_validate_mode IN VARCHAR2
300 -- OUT x_return_status OUT VARCHAR2
301 -- Version Current version: 1.0
302 -- Previous version: 1.0
303 -- Initial version: 1.0
304 -- End of comments
305
306 PROCEDURE Validate_Formula_Items(
307 p_formula_rec IN ams_formula_rec_type,
308 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
309 x_return_status OUT NOCOPY VARCHAR2
310 ) ;
311
312
313 -- Start of comments
314 -- API Name Validate_Formula_Rec
315 -- Type Private
316 -- Pre-reqs None.
317 -- Function Validate Activity Metric Formula Records
318 -- Parameters
319 -- IN p_formula_rec IN ams_formula_rec_type Required
320 -- p_complete_formula_rec IN ams_formula_rec_type Required
321 -- OUT x_return_status OUT VARCHAR2
322 -- Version Current version: 1.0
323 -- Previous version: 1.0
324 -- Initial version: 1.0
325 -- End of comments
326
327 PROCEDURE Validate_Formula_Rec(
328 p_formula_rec IN ams_formula_rec_type ,
329 p_complete_formula_rec IN ams_formula_rec_type ,
330 x_return_status OUT NOCOPY VARCHAR2
331 ) ;
332
333
334 -- Start of comments
335 -- API Name Complete_Formula_Rec
336 -- Type Private
337 -- Pre-reqs None.
338 -- Function This Process returns the details for the Activity Metric Formula record.
339 --
340 -- Parameters
341 -- IN p_formula_rec IN ams_formula_rec_type Required
342 -- OUT x_complete_formula_rec OUT ams_formula_rec_type
343 -- Version Current version: 1.0
344 -- Previous version: 1.0
345 -- Initial version: 1.0
346 -- End of comments
347
348 PROCEDURE Complete_Formula_Rec(
349 p_formula_rec IN ams_formula_rec_type,
350 x_complete_formula_rec OUT NOCOPY ams_formula_rec_type
351 );
352
353
354
355
356
357 ---------------------------------------------------------------------
358 -- Start of comments
359 -- API Name Create_Formula_Entry
360 -- Type Private
361 -- Pre-reqs None.
362 -- Function Creates an Activity Metric Formula_Entry.
363 -- Parameters
364 -- IN p_api_version IN NUMBER Required
365 -- p_init_msg_list IN VARCHAR2 Optional
366 -- Default := FND_API.G_FALSE
367 -- p_commit IN VARCHAR2 Optional
368 -- Default := FND_API.G_FALSE
369 -- p_validation_level IN NUMBER Optional
370 -- Default := FND_API.G_VALID_LEVEL_FULL
371 -- p_formula_entry_rec IN ams_formula_entry_rec_type Required
372 -- OUT x_return_status OUT VARCHAR2
373 -- x_msg_count OUT NUMBER
374 -- x_msg_data OUT VARCHAR2
375 -- Version Current version: 1.0
376 -- Previous version: 1.0
377 -- Initial version: 1.0
378 -- End of comments
379 ---------------------------------------------------------------------
380
381 PROCEDURE Create_Formula_Entry (
382 p_api_version IN NUMBER,
386
383 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
384 p_commit IN VARCHAR2 := FND_API.G_FALSE,
385 p_validation_level IN NUMBER := FND_API.G_Valid_Level_Full,
387 x_return_status OUT NOCOPY VARCHAR2,
388 x_msg_count OUT NOCOPY NUMBER,
389 x_msg_data OUT NOCOPY VARCHAR2,
390
391 p_formula_entry_rec IN ams_formula_entry_rec_type,
392 x_formula_entry_id OUT NOCOPY NUMBER
393 );
394
395
396
397 -- Start of comments
398 -- API Name Update_formula_entry
399 -- Type Private
400 -- Pre-reqs None.
401 -- Function Updates the activity metric formula_entry.
402 -- Parameters
403 -- IN p_api_version IN NUMBER Required
404 -- p_init_msg_list IN VARCHAR2 Optional
405 -- Default := FND_API.G_FALSE
406 -- p_commit IN VARCHAR2 Optional
407 -- Default := FND_API.G_FALSE
408 -- p_validation_level IN NUMBER Optional
409 -- Default := FND_API.G_VALID_LEVEL_FULL
410 -- p_formula_entry_rec IN ams_formula_entry_rec_type Required
411 -- OUT x_return_status OUT VARCHAR2
412 -- x_msg_count OUT NUMBER
413 -- x_msg_data OUT VARCHAR2
414 -- Version Current version: 1.0
415 -- Previous version: 1.0
416 -- Initial version: 1.0
417 -- End of comments
418
419 PROCEDURE Update_formula_entry (
420 p_api_version IN NUMBER,
421 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
422 p_commit IN VARCHAR2 := FND_API.G_FALSE,
423 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
424
425 x_return_status OUT NOCOPY VARCHAR2,
426 x_msg_count OUT NOCOPY NUMBER,
427 x_msg_data OUT NOCOPY VARCHAR2,
428
429 p_formula_entry_rec IN ams_formula_entry_rec_type
430 );
431
432
433 -- Start of comments
434 -- API Name Delete_formula_entry
435 -- Type Private
436 -- Pre-reqs None.
437 -- Function Deletes the Activity Metric formula_entry.
438 -- Parameters
439 -- IN p_api_version IN NUMBER Required
440 -- p_init_msg_list IN VARCHAR2 Optional
441 -- Default := FND_API.G_FALSE
442 -- p_commit IN VARCHAR2 Optional
443 -- Default := FND_API.G_FALSE
444 -- p_forecast_id IN NUMBER Required
445 -- p_object_version_number IN NUMBER
446 -- OUT x_return_status OUT VARCHAR2
447 -- x_msg_count OUT NUMBER
448 -- x_msg_data OUT VARCHAR2
449 -- Version Current version: 1.0
450 -- Previous version: 1.0
451 -- Initial version: 1.0
452 -- End of comments
453
454 PROCEDURE Delete_formula_entry (
455 p_api_version IN NUMBER,
456 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
457 p_commit IN VARCHAR2 := FND_API.G_FALSE,
458
459 x_return_status OUT NOCOPY VARCHAR2,
460 x_msg_count OUT NOCOPY NUMBER,
461 x_msg_data OUT NOCOPY VARCHAR2,
462
463 p_formula_entry_id IN NUMBER ,
464 p_object_version_number IN NUMBER
465 );
466
467
468
469 -- Start of comments
470 -- API Name Lock_formula_entry
471 -- Type Private
472 -- Pre-reqs None.
473 -- Function Lock the given row in AMS_ACT_METRIC_formula_entries table.
474 -- Parameters
475 -- IN p_api_version IN NUMBER Required
476 -- p_init_msg_list IN VARCHAR2 Optional
477 -- Default := FND_API.G_FALSE
478 -- p_commit IN VARCHAR2 Optional
479 -- Default := FND_API.G_FALSE
480 -- p_formula_entry_id IN NUMBER Required
481 -- p_object_version_number IN NUMBER Required
482 -- OUT x_return_status OUT VARCHAR2
483 -- x_msg_count OUT NUMBER
484 -- x_msg_data OUT VARCHAR2
485 -- Version Current version: 1.0
486 -- Previous version: 1.0
487 -- Initial version: 1.0
488 -- End of comments
489
490
491 PROCEDURE Lock_formula_entry (
492 p_api_version IN NUMBER,
493 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
494
495 x_return_status OUT NOCOPY VARCHAR2,
496 x_msg_count OUT NOCOPY NUMBER,
497 x_msg_data OUT NOCOPY VARCHAR2,
498
499 p_formula_entry_id IN NUMBER,
500 p_object_version_number IN NUMBER
501 );
502
503
504
505 -- Start of comments
506 -- API Name Validate_formula_entry
507 -- Type Private
508 -- Pre-reqs None.
509 -- Function Validate items in the activity metric forecast table.
510
514 -- Default := FND_API.G_FALSE
511 -- Parameters
512 -- IN p_api_version IN NUMBER Required
513 -- p_init_msg_list IN VARCHAR2 Optional
515 -- p_commit IN VARCHAR2 Optional
516 -- Default := FND_API.G_FALSE
517 -- p_validation_level IN NUMBER Optional
518 -- Default := FND_API.G_VALID_LEVEL_FULL
519 -- p_formula_entry_rec IN ams_formula_entry_rec_type Required
520 -- OUT x_return_status OUT VARCHAR2
521 -- x_msg_count OUT NUMBER
522 -- x_msg_data OUT VARCHAR2
523 -- Version Current version: 1.0
524 -- Previous version: 1.0
525 -- Initial version: 1.0
526 -- End of comments
527
528
529 PROCEDURE Validate_formula_entry (
530 p_api_version IN NUMBER,
531 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
532 p_validation_level IN NUMBER := FND_API.G_Valid_Level_Full,
533
534 x_return_status OUT NOCOPY VARCHAR2,
535 x_msg_count OUT NOCOPY NUMBER,
536 x_msg_data OUT NOCOPY VARCHAR2,
537
538 p_formula_entry_rec IN ams_formula_entry_rec_type
539 );
540
541
542 -- Start of comments
543 -- API Name Validate_form_ent_Items
544 -- Type Private
545 -- Pre-reqs None.
546 -- Function Validate Activity Metric formula_entry Items
547 -- Parameters
548 -- IN p_formula_entry_rec IN ams_formula_entry_rec_type Required
549 -- p_validate_mode IN VARCHAR2
550 -- OUT x_return_status OUT VARCHAR2
551 -- Version Current version: 1.0
552 -- Previous version: 1.0
553 -- Initial version: 1.0
554 -- End of comments
555
556 PROCEDURE Validate_form_ent_Items(
557 p_formula_entry_rec IN ams_formula_entry_rec_type,
558 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
559 x_return_status OUT NOCOPY VARCHAR2
560 ) ;
561
562
563 -- Start of comments
564 -- API Name Validate_form_ent_Rec
565 -- Type Private
566 -- Pre-reqs None.
567 -- Function Validate Activity Metric formula_entry Records
568 -- Parameters
569 -- IN p_formula_entry_rec IN ams_formula_entry_rec_type Required
570 -- p_complete_formula_entry_rec IN ams_formula_entry_rec_type Required
571 -- OUT x_return_status OUT VARCHAR2
572 -- Version Current version: 1.0
573 -- Previous version: 1.0
574 -- Initial version: 1.0
575 -- End of comments
576
577 PROCEDURE Validate_form_ent_Rec(
578 p_formula_entry_rec IN ams_formula_entry_rec_type ,
579 p_complete_formula_entry_rec IN ams_formula_entry_rec_type ,
580 x_return_status OUT NOCOPY VARCHAR2
581 ) ;
582
583
584
585 -- Start of comments
586 -- API Name Complete_form_ent_Rec
587 -- Type Private
588 -- Pre-reqs None.
589 -- Function This Process returns the details for the Activity Metric formula_entry record.
590 --
591 -- Parameters
592 -- IN p_formula_entry_rec IN ams_formula_entry_rec_type Required
593 -- OUT x_complete_formula_entry_rec OUT ams_formula_entry_rec_type
594 -- Version Current version: 1.0
595 -- Previous version: 1.0
596 -- Initial version: 1.0
597 -- End of comments
598
599 PROCEDURE Complete_form_ent_Rec(
600 p_formula_entry_rec IN ams_formula_entry_rec_type,
601 x_complete_formula_entry_rec OUT NOCOPY ams_formula_entry_rec_type
602 );
603
604
605
606 END AMS_FORMULA_PVT;
[Home] [Help]