1 PACKAGE AMS_Act_List_PVT AUTHID CURRENT_USER AS
2 /* $Header: amsvalss.pls 120.2 2005/09/08 09:40:46 bmuthukr ship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- AMS_Act_List_PVT
7 -- Purpose
8 --
9 -- History
10 --
11 -- NOTE
12 --
13 -- End of Comments
14 -- ===============================================================
15
16 -- Default number of records fetch per call
17 G_DEFAULT_NUM_REC_FETCH NUMBER := 30;
18 --TYPE sql_string IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
19 TYPE child_type IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
20 --===================================================================
21 -- Start of Comments
22 -- -------------------------------------------------------
23 -- Record name
24 -- act_list_rec_type
25 -- -------------------------------------------------------
26 -- Parameters:
27 -- act_list_header_id
28 -- last_update_date
29 -- last_updated_by
30 -- creation_date
31 -- created_by
32 -- object_version_number
33 -- last_update_login
34 -- list_header_id
35 -- list_used_by_id
36 -- list_used_by
37 -- list_act_type
38 -- list_action_type
39 -- order_number
40 --
41 -- Required
42 --
43 -- Defaults
44 --
45 -- Note: This is automatic generated record definition, it includes all columns
46 -- defined in the table, developer must manually add or delete some of the attributes.
47 --
48 -- End of Comments
49
50 --===================================================================
51 TYPE act_list_rec_type IS RECORD
52 (
53 act_list_header_id NUMBER := FND_API.G_MISS_NUM,
54 last_update_date DATE := FND_API.G_MISS_DATE,
55 last_updated_by NUMBER := FND_API.G_MISS_NUM,
56 creation_date DATE := FND_API.G_MISS_DATE,
57 created_by NUMBER := FND_API.G_MISS_NUM,
58 object_version_number NUMBER := FND_API.G_MISS_NUM,
59 last_update_login NUMBER := FND_API.G_MISS_NUM,
60 list_header_id NUMBER := FND_API.G_MISS_NUM,
61 group_code VARCHAR2(10) := FND_API.G_MISS_CHAR,
62 list_used_by_id NUMBER := FND_API.G_MISS_NUM,
63 list_used_by VARCHAR2(30) := FND_API.G_MISS_CHAR,
64 list_act_type VARCHAR2(30) := FND_API.G_MISS_CHAR,
65 list_action_type VARCHAR2(30) := FND_API.G_MISS_CHAR,
66 order_number NUMBER := FND_API.G_MISS_NUM
67 );
68
69 g_miss_act_list_rec act_list_rec_type;
70 TYPE act_list_tbl_type IS TABLE OF act_list_rec_type INDEX BY BINARY_INTEGER;
71 g_miss_act_list_tbl act_list_tbl_type;
72
73 -- ==============================================================================
74 -- Start of Comments
75 -- ==============================================================================
76 -- API Name
77 -- Create_Act_List
78 -- Type
79 -- Private
80 -- Pre-Req
81 --
82 -- Parameters
83 --
84 -- IN
85 -- p_api_version_number IN NUMBER Required
86 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
87 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
88 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
89 -- p_act_list_rec IN act_list_rec_type Required
90 --
91 -- OUT
92 -- x_return_status OUT VARCHAR2
93 -- x_msg_count OUT NUMBER
94 -- x_msg_data OUT VARCHAR2
95 -- Version : Current version 1.0
96 -- Note: This automatic generated procedure definition, it includes standard IN/OUT parameters
97 -- and basic operation, developer must manually add parameters and business logic as necessary.
98 --
99 -- End of Comments
100 -- ==============================================================================
101 --
102
103 PROCEDURE Create_Act_List(
104 p_api_version_number IN NUMBER,
105 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
106 p_commit IN VARCHAR2 := FND_API.G_FALSE,
107 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
108
109 x_return_status OUT NOCOPY VARCHAR2,
110 x_msg_count OUT NOCOPY NUMBER,
111 x_msg_data OUT NOCOPY VARCHAR2,
112
113 p_act_list_rec IN act_list_rec_type := g_miss_act_list_rec,
114 x_act_list_header_id OUT NOCOPY NUMBER
115 );
116
117 -- ==============================================================================
118 -- Start of Comments
119 -- ==============================================================================
120 -- API Name
121 -- Update_Act_List
122 -- Type
123 -- Private
124 -- Pre-Req
125 --
126 -- Parameters
127 --
128 -- IN
129 -- p_api_version_number IN NUMBER Required
130 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
131 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
132 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
133 -- p_act_list_rec IN act_list_rec_type Required
134 --
135 -- OUT
136 -- x_return_status OUT VARCHAR2
137 -- x_msg_count OUT NUMBER
138 -- x_msg_data OUT VARCHAR2
139 -- Version : Current version 1.0
140 -- Note: This automatic generated procedure definition, it includes standard IN/OUT parameters
141 -- and basic operation, developer must manually add parameters and business logic as necessary.
142 --
143 -- End of Comments
144 -- ==============================================================================
145 --
146
147 PROCEDURE Update_Act_List(
148 p_api_version_number IN NUMBER,
149 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
150 p_commit IN VARCHAR2 := FND_API.G_FALSE,
151 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
152
153 x_return_status OUT NOCOPY VARCHAR2,
154 x_msg_count OUT NOCOPY NUMBER,
155 x_msg_data OUT NOCOPY VARCHAR2,
156
157 p_act_list_rec IN act_list_rec_type,
158 x_object_version_number OUT NOCOPY NUMBER
159 );
160
161 -- ==============================================================================
162 -- Start of Comments
163 -- ==============================================================================
164 -- API Name
165 -- Delete_Act_List
166 -- Type
167 -- Private
168 -- Pre-Req
169 --
170 -- Parameters
171 --
172 -- IN
173 -- p_api_version_number IN NUMBER Required
174 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
175 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
176 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
177 -- p_ACT_LIST_HEADER_ID IN NUMBER
178 -- p_object_version_number IN NUMBER Optional Default = NULL
179 --
180 -- OUT
181 -- x_return_status OUT VARCHAR2
182 -- x_msg_count OUT NUMBER
183 -- x_msg_data OUT VARCHAR2
184 -- Version : Current version 1.0
185 -- Note: This automatic generated procedure definition, it includes standard IN/OUT parameters
186 -- and basic operation, developer must manually add parameters and business logic as necessary.
187 --
188 -- End of Comments
189 -- ==============================================================================
190 --
191
192 PROCEDURE Delete_Act_List(
193 p_api_version_number IN NUMBER,
194 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
195 p_commit IN VARCHAR2 := FND_API.G_FALSE,
196 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
197 x_return_status OUT NOCOPY VARCHAR2,
198 x_msg_count OUT NOCOPY NUMBER,
199 x_msg_data OUT NOCOPY VARCHAR2,
200 p_act_list_header_id IN NUMBER,
201 p_object_version_number IN NUMBER
202 );
203
204 -- ==============================================================================
205 -- Start of Comments
206 -- ==============================================================================
207 -- API Name
208 -- Lock_Act_List
209 -- Type
210 -- Private
211 -- Pre-Req
212 --
213 -- Parameters
214 --
215 -- IN
216 -- p_api_version_number IN NUMBER Required
217 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
218 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
219 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
220 -- p_act_list_rec IN act_list_rec_type Required
221 --
222 -- OUT
223 -- x_return_status OUT VARCHAR2
224 -- x_msg_count OUT NUMBER
225 -- x_msg_data OUT VARCHAR2
226 -- Version : Current version 1.0
227 -- Note: This automatic generated procedure definition, it includes standard IN/OUT parameters
228 -- and basic operation, developer must manually add parameters and business logic as necessary.
229 --
230 -- End of Comments
231 -- ==============================================================================
232 --
233
234 PROCEDURE Lock_Act_List(
235 p_api_version_number IN NUMBER,
236 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
237
238 x_return_status OUT NOCOPY VARCHAR2,
239 x_msg_count OUT NOCOPY NUMBER,
240 x_msg_data OUT NOCOPY VARCHAR2,
241
242 p_act_list_header_id IN NUMBER,
243 p_object_version IN NUMBER
244 );
245
246
247 -- Start of Comments
248 --
249 -- validation procedures
250 --
251 -- p_validation_mode is a constant defined in AMS_UTILITY_PVT package
252 -- For create: G_CREATE, for update: G_UPDATE
253 -- Note: 1. This is automated generated item level validation procedure.
254 -- The actual validation detail is needed to be added.
255 -- 2. We can also validate table instead of record. There will be an option for user to choose.
256 -- End of Comments
257
258 PROCEDURE Validate_act_list(
259 p_api_version_number IN NUMBER,
260 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
261 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
262 p_act_list_rec IN act_list_rec_type,
263 x_return_status OUT NOCOPY VARCHAR2,
264 x_msg_count OUT NOCOPY NUMBER,
265 x_msg_data OUT NOCOPY VARCHAR2
266 );
267
268 -- Start of Comments
269 --
270 -- validation procedures
271 --
272 -- p_validation_mode is a constant defined in AMS_UTILITY_PVT package
273 -- For create: G_CREATE, for update: G_UPDATE
274 -- Note: 1. This is automated generated item level validation procedure.
275 -- The actual validation detail is needed to be added.
276 -- 2. Validate the unique keys, lookups here
277 -- End of Comments
278
279 PROCEDURE Check_act_list_Items (
280 P_act_list_rec IN act_list_rec_type,
281 p_validation_mode IN VARCHAR2,
282 x_return_status OUT NOCOPY VARCHAR2
283 );
284
285 -- Start of Comments
286 --
287 -- Record level validation procedures
288 --
289 -- p_validation_mode is a constant defined in AMS_UTILITY_PVT package
290 -- For create: G_CREATE, for update: G_UPDATE
291 -- Note: 1. This is automated generated item level validation procedure.
292 -- The actual validation detail is needed to be added.
293 -- 2. Developer can manually added inter-field level validation.
294 -- End of Comments
295
296 PROCEDURE Validate_act_list_rec(
297 p_api_version_number IN NUMBER,
298 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
299 x_return_status OUT NOCOPY VARCHAR2,
300 x_msg_count OUT NOCOPY NUMBER,
301 x_msg_data OUT NOCOPY VARCHAR2,
302 p_act_list_rec IN act_list_rec_type
303 );
304
305 PROCEDURE generate_target_group_list
306 ( p_api_version IN NUMBER,
307 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
308 p_commit IN VARCHAR2 := FND_API.G_FALSE,
312 x_return_status OUT NOCOPY VARCHAR2,
309 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
310 p_list_used_by in VARCHAR2,
311 p_list_used_by_id in NUMBER,
313 x_msg_count OUT NOCOPY NUMBER,
314 x_msg_data OUT NOCOPY VARCHAR2
315 ) ;
316 PROCEDURE generate_target_group_list_old
317 ( p_api_version IN NUMBER,
318 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
319 p_commit IN VARCHAR2 := FND_API.G_FALSE,
320 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
321 p_list_used_by in VARCHAR2,
322 p_list_used_by_id in NUMBER,
323 x_return_status OUT NOCOPY VARCHAR2,
324 x_msg_count OUT NOCOPY NUMBER,
325 x_msg_data OUT NOCOPY VARCHAR2
326 ) ;
327
328
329 PROCEDURE create_target_group_list
330 ( p_api_version IN NUMBER,
331 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
332 p_commit IN VARCHAR2 := FND_API.G_FALSE,
333 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
334 p_list_used_by_name in VARCHAR2,
335 p_list_used_by in VARCHAR2,
336 p_list_used_by_id in NUMBER,
337 p_list_type in VARCHAR2 := 'TARGET' ,
338 p_owner_user_id in NUMBER,
339 x_return_status OUT NOCOPY VARCHAR2,
340 x_msg_count OUT NOCOPY NUMBER,
341 x_msg_data OUT NOCOPY VARCHAR2,
342 x_list_header_id OUT NOCOPY NUMBER ) ;
343 PROCEDURE init_act_list_Rec (
344 x_act_list_rec OUT NOCOPY act_list_rec_type);
345 PROCEDURE Complete_act_list_Rec (
346 p_act_list_rec IN act_list_rec_type,
347 x_complete_rec OUT NOCOPY act_list_rec_type);
348 PROCEDURE process_cell
349 (p_action_used_by_id in number,
350 p_act_list_header_id in number,
351 p_incl_object_id in number,
352 x_msg_count OUT NOCOPY number,
353 x_msg_data OUT NOCOPY varchar2,
354 x_return_status IN OUT NOCOPY VARCHAR2,
355 x_std_sql OUT NOCOPY varchar2 ,
356 x_include_sql OUT NOCOPY varchar2
357 ) ;
358 PROCEDURE copy_target_group
359 (p_from_schedule_id in number,
360 p_to_schedule_id in number,
361 p_list_used_by in VARCHAR2 DEFAULT 'CSCH',
362 x_msg_count OUT NOCOPY number,
363 x_msg_data OUT NOCOPY varchar2,
364 x_return_status IN OUT NOCOPY VARCHAR2
365 ) ;
366
367 PROCEDURE copy_target_group
368 (p_from_schedule_id in number,
369 p_to_schedule_id in number,
370 p_list_used_by in VARCHAR2 DEFAULT 'CSCH',
371 p_repeat_flag in VARCHAR2,
372 x_msg_count OUT NOCOPY number,
373 x_msg_data OUT NOCOPY varchar2,
374 x_return_status IN OUT NOCOPY VARCHAR2
375 ) ;
376 ------------------------------------------------------------------------------------------------------------------
377 --------------------------Procedure to INVOKE TARGETGROUP LOCK Begins here----------------------------------------
378 ------------------------------------------------------------------------------------------------------------------
379
380 --===============================================================================================
381 -- Procedure
382 -- INVOKE_TARGET_GROUP_LOCK
383 --
384 -- PURPOSE
385 -- This api is called to check for the schedules in ACTIVE State(Campaign or Event).
386 --
387 -- ALGORITHM
388 -- 1. Get All parameter Types
389 --
390 -- Any error in any of the API callouts?
391 -- => a) Set RETURN STATUS to E
392 --
393 -- OPEN ISSUES
394 -- 1. Should we do a explicit exit on Object_type not found.
395 --
396 -- HISTORY
397 -- 19-Apr-2005 ndadwal
398 --===============================================================================================
399
400 FUNCTION INVOKE_TARGET_GROUP_LOCK ( p_subscription_guid IN RAW,
401 p_event IN OUT NOCOPY WF_EVENT_T) RETURN VARCHAR2;
402
403
404
405
406 ------------------------------------------------------------------------------------------------------------------
407 --------------------------Procedure to INVOKE TARGETGROUP LOCK Ends here----------------------------------------
408 ------------------------------------------------------------------------------------------------------------------
409
410 PROCEDURE Control_Group_Generation(p_list_header_id IN NUMBER,
411 p_pct_random IN NUMBER,
412 p_no_random IN NUMBER,
413 p_total_rows IN NUMBER,
414 x_return_status OUT NOCOPY VARCHAR2,
415 x_msg_count OUT NOCOPY NUMBER,
416 x_msg_data OUT NOCOPY VARCHAR2);
417
418 PROCEDURE check_supp(p_list_used_by varchar2,
419 p_list_used_by_id number,
420 p_list_header_id number,
421 x_return_status out nocopy varchar2,
422 x_msg_count out nocopy number,
423 x_msg_data out nocopy varchar2);
424
425 END AMS_Act_List_PVT;