1 PACKAGE AMS_ContCampaign_PVT as
2 /* $Header: amsvtces.pls 120.1 2005/08/29 11:28:59 soagrawa noship $*/
3
4
5 -- Start of Comments
6 --
7 -- NAME
8 -- AMS_Campaign_PVT
9 --
10 -- PURPOSE
11 -- This package performs Continuous Campaigning
12 -- in Oracle Marketing
13 --
14 -- Procedures:
15 --
16 --
17 -- Perform_checks (see below for specification)
18 -- Record_result (see below for specification)
19 -- Schedule_Next_Trigger_Run (see below for specification)
20 -- Validate_Sql (see below for specification)
21 --
22 -- NOTES
23 --
24 --
25 -- HISTORY
26 -- 07/12/1999 ptendulk created
27 -- End of Comments
28
29 /******************************************************************************/
30 -- global constants
31 -- Define Exception to check whether the Discoverer query returns only one Column
32 /******************************************************************************/
33 valid_no_columns EXCEPTION ;
34
35 pragma EXCEPTION_INIT(valid_no_columns, -1007);
36
37 /******************************************************************************/
38 --PL\SQL table to hold the strings that compose a valid SQL statement from
39 --a discoverer workbook
40 /******************************************************************************/
41
42 TYPE t_SQLtable is TABLE OF varchar2(2000)
43 INDEX BY BINARY_INTEGER;
44
45
46 /******************************************************************************/
47
48 -- Start of Comments
49 --
50 -- NAME
51 ---- Record_result
52 --
53 -- PURPOSE
54 -- This Procedure is to record the results of the check and also that
55 -- of action
56 --
57 -- NOTES
58 --
59 --
60 -- HISTORY
61 -- 07/21/1999 ptendulk Created
62 -- 26-aug-2005 soagrawa Added action for id to store who the NTF was sent to
63 --
64 -- End of Comments
65 PROCEDURE Record_Result(p_result_for_id IN NUMBER,
66 p_process_id IN NUMBER := NULL,
67 p_chk1_value IN NUMBER := NULL,
68 p_chk2_value IN NUMBER := NULL,
69 p_chk2_high_value IN NUMBER := NULL,
70 p_operator IN VARCHAR2 := NULL,
71 p_process_success IN VARCHAR2 := NULL,
72 p_check_met IN VARCHAR2 := NULL,
73 p_action_taken IN VARCHAR2 := NULL,
74 p_action_for_id IN NUMBER := NULL,
75 x_result_id OUT NOCOPY NUMBER,
76 x_return_status OUT NOCOPY VARCHAR2);
77
78 -- Start of Comments
79 --
80 -- NAME
81 -- Schedule_Next_Trigger_Run
82 --
83 -- PURPOSE
84 -- This Procedure will mark the Last run time fot the trigger and
85 -- will calculate the next schedule run time. Will Update the AMS_TRIGGERS
86 -- table with the new values for Last run time, next schedule run time
87 --
88 -- NOTES
89 --
90 --
91 -- HISTORY
92 -- 07/23/1999 ptendulk created
93 -- End of Comments
94
95 PROCEDURE Schedule_Next_Trigger_Run
96 (p_api_version IN NUMBER,
97 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
98 p_commit IN VARCHAR2 := FND_API.G_FALSE,
99 p_trigger_id IN NUMBER,
100 x_msg_count OUT NOCOPY NUMBER,
101 x_msg_data OUT NOCOPY VARCHAR2,
102 x_return_status OUT NOCOPY VARCHAR2,
103 x_sch_date OUT NOCOPY DATE) ;
104
105
106 -- Start of Comments
107 --
108 -- NAME
109 ---- Validate_Sql
110 --
111 -- PURPOSE
112 -- This Function is to validate the discoverer SQL query.
113 -- The discoverer SQL defined for continuous Campaign must return
114 -- only one column and it must have only one row. Also the output should be
115 -- numeric value
116 --
117 -- CALLED BY
118 -- Perform_Checks
119 --
120 -- NOTES
121 --
122 --
123 -- HISTORY
124 -- 07/12/1999 ptendulk created
125 -- End of Comments
126
127 PROCEDURE Validate_Sql( p_api_version IN NUMBER,
128 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
129
130 x_return_status OUT NOCOPY VARCHAR2,
131 x_msg_count OUT NOCOPY NUMBER ,
132 x_msg_data OUT NOCOPY VARCHAR2,
133
134 p_workbook_name IN VARCHAR2,
135 p_worksheet_name IN VARCHAR2,
136 p_workbook_owner_name IN VARCHAR2,
137
138 x_result OUT NOCOPY NUMBER) ;
139
140 -- Start of Comments
141 --
142 -- NAME
143 ---- Perform_Checks
144 --
145 -- PURPOSE
146 -- This Function is to execute various checks defined on the trigger.
147 -- Function performs the checks, stores the result in Result table and
148 -- returns the flag Y/N to indicate whether the check was met or not
149 --
150 -- NOTES
151 --
152 --
153 -- HISTORY
154 -- 07/12/1999 ptendulk created
155 -- End of Comments
156
157 PROCEDURE Perform_Checks(p_api_version IN NUMBER ,
158 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
159
160 x_msg_count OUT NOCOPY NUMBER,
161 x_msg_data OUT NOCOPY VARCHAR2,
162 x_return_status OUT NOCOPY VARCHAR2,
163
164 p_trigger_id IN NUMBER,
165 x_chk_success OUT NOCOPY VARCHAR2,
166 x_check_val OUT NOCOPY NUMBER ,
167 x_check_high_val OUT NOCOPY NUMBER ,
168 x_result_id OUT NOCOPY NUMBER
169 );
170
171 END AMS_ContCampaign_PVT;