1 PACKAGE AMS_CTD_PVT AS
2 /* $Header: amsvctds.pls 120.3 2006/09/06 17:26:55 dbiswas noship $ */
3
4
5 -- ===============================================================
6 -- Start of Comments
7 -- Package name
8 -- AMS_CTD_PVT
9 -- Purpose
10 --
11 -- This package contains all the program units for Click Through Destinations
12 --
13 -- History
14 --
15 -- NOTE
16 --
17 -- End of Comments
18 -- ===============================================================
19 G_PACKAGE_NAME CONSTANT VARCHAR2(30) := 'AMS_CTD_PVT';
20
21 -- Start of Comments
22 -- Name
23 -- CREATE_ASSOCIATION
24 --
25 -- Purpose
26 -- This procedure creates new associations between CTD Ids and the
27 -- Used By and Used By PK1
28 --
29
30 Procedure CREATE_ASSOCIATION(
31 p_ctd_id_list JTF_NUMBER_TABLE,
32 p_used_by VARCHAR2,
33 p_used_by_val1 VARCHAR2
34 );
35 -- ===============================================================
36 -- Start of Comments
37 -- Name
38 -- CREATE_AND_DELETE_ASSOCIATION
39 --
40 -- Purpose
41 -- 1. Establish new associations between CTD Id and Used-By,Used By Id combination
42 -- if not already exists in the CTD Association table.
43 --
44 -- 2. Delete the associations which are not more valid
45 --
46 Procedure CREATE_AND_DELETE_ASSOCIATION(
47 p_ctd_id_list JTF_NUMBER_TABLE,
48 p_used_by VARCHAR2,
49 p_used_by_val1 VARCHAR2
50 );
51 -- ===============================================================
52 -- Start of Comments
53 -- Name
54 -- DELETE_ASSOCIATION_AND_CTD
55 --
56 -- Purpose
57 -- This procedure deletes associations and all relevant CTD information
58 -- if no other object is using the CTD
59 --
60 Procedure DELETE_ASSOCIATION_AND_CTD(
61 p_used_by VARCHAR2,
62 p_used_by_val1 VARCHAR2
63 ) ;
64
65 -- Default number of records fetch per call
66 G_DEFAULT_NUM_REC_FETCH NUMBER := 30;
67 --===================================================================
68 -- Start of Comments
69 -- -------------------------------------------------------
70 -- Record name
71 -- ctd_rec_type
72 -- -------------------------------------------------------
73 -- Parameters:
74 -- ctd_id
75 -- action_id
76 -- forward_url
77 -- track_url
78 -- activity_product_id
79 -- activity_offer_id
80 -- object_version_number
81 -- last_update_date
82 -- last_updated_by
83 -- creation_date
84 -- created_by
85 -- last_update_login
86 -- security_group_id
87 --
88 -- Required
89 --
90 -- Defaults
91 --
92 -- Note: This is automatic generated record definition, it includes all columns
93 -- defined in the table, developer must manually add or delete some of the attributes.
94 --
95 -- End of Comments
96
97 --===================================================================
98 TYPE ctd_rec_type IS RECORD
99 (
100 ctd_id NUMBER := FND_API.G_MISS_NUM,
101 action_id NUMBER := FND_API.G_MISS_NUM,
102 forward_url VARCHAR2(2000) := FND_API.G_MISS_CHAR,
103 track_url VARCHAR2(2000) := FND_API.G_MISS_CHAR,
104 activity_product_id NUMBER := FND_API.G_MISS_NUM,
105 activity_offer_id NUMBER := FND_API.G_MISS_NUM,
106 object_version_number NUMBER := FND_API.G_MISS_NUM,
107 last_update_date DATE := FND_API.G_MISS_DATE,
108 last_updated_by NUMBER := FND_API.G_MISS_NUM,
109 creation_date DATE := FND_API.G_MISS_DATE,
110 created_by NUMBER := FND_API.G_MISS_NUM,
111 last_update_login NUMBER := FND_API.G_MISS_NUM,
112 security_group_id NUMBER := FND_API.G_MISS_NUM
113 );
114
115 g_miss_ctd_rec ctd_rec_type;
116 TYPE ctd_tbl_type IS TABLE OF ctd_rec_type INDEX BY BINARY_INTEGER;
117 g_miss_ctd_tbl ctd_tbl_type;
118
119 -- ==============================================================================
120 -- Start of Comments
121 -- ==============================================================================
122 -- API Name
123 -- Create_Ctd
124 -- Type
125 -- Private
126 -- Pre-Req
127 --
128 -- Parameters
129 --
130 -- IN
131 -- p_api_version_number IN NUMBER Required
132 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
133 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
134 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
135 -- p_ctd_rec IN ctd_rec_type Required
136 --
137 -- OUT NOCOPY
138 -- x_return_status OUT NOCOPY VARCHAR2
139 -- x_msg_count OUT NOCOPY NUMBER
140 -- x_msg_data OUT NOCOPY VARCHAR2
141 -- Version : Current version 1.0
142 -- Note: This automatic generated procedure definition, it includes standard IN/OUT NOCOPY parameters
143 -- and basic operation, developer must manually add parameters and business logic as necessary.
144 --
145 -- End of Comments
146 -- ==============================================================================
147 --
148
149 PROCEDURE Create_Ctd(
150 p_api_version_number IN NUMBER,
151 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
152 p_commit IN VARCHAR2 := FND_API.G_FALSE,
153 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
154
155 x_return_status OUT NOCOPY VARCHAR2,
156 x_msg_count OUT NOCOPY NUMBER,
157 x_msg_data OUT NOCOPY VARCHAR2,
158
159 p_ctd_rec IN ctd_rec_type := g_miss_ctd_rec,
160 x_ctd_id OUT NOCOPY NUMBER
161 );
162
163 -- ==============================================================================
164 -- Start of Comments
165 -- ==============================================================================
166 -- API Name
167 -- Update_Ctd
168 -- Type
169 -- Private
170 -- Pre-Req
171 --
172 -- Parameters
173 --
174 -- IN
175 -- p_api_version_number IN NUMBER Required
176 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
177 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
178 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
179 -- p_ctd_rec IN ctd_rec_type Required
180 --
181 -- OUT NOCOPY
182 -- x_return_status OUT NOCOPY VARCHAR2
183 -- x_msg_count OUT NOCOPY NUMBER
184 -- x_msg_data OUT NOCOPY VARCHAR2
185 -- Version : Current version 1.0
186 -- Note: This automatic generated procedure definition, it includes standard IN/OUT NOCOPY parameters
187 -- and basic operation, developer must manually add parameters and business logic as necessary.
188 --
189 -- End of Comments
190 -- ==============================================================================
191 --
192
193 PROCEDURE Update_Ctd(
194 p_api_version_number IN NUMBER,
195 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
196 p_commit IN VARCHAR2 := FND_API.G_FALSE,
197 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
198
199 x_return_status OUT NOCOPY VARCHAR2,
200 x_msg_count OUT NOCOPY NUMBER,
201 x_msg_data OUT NOCOPY VARCHAR2,
202
203 p_ctd_rec IN ctd_rec_type,
204 x_object_version_number OUT NOCOPY NUMBER
205 );
206
207 -- ==============================================================================
208 -- Start of Comments
209 -- ==============================================================================
210 -- API Name
211 -- Delete_Ctd
212 -- Type
213 -- Private
214 -- Pre-Req
215 --
216 -- Parameters
217 --
218 -- IN
219 -- p_api_version_number IN NUMBER Required
220 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
221 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
222 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
223 -- p_CTD_ID IN NUMBER
224 -- p_object_version_number IN NUMBER Optional Default = NULL
225 --
226 -- OUT NOCOPY
227 -- x_return_status OUT NOCOPY VARCHAR2
228 -- x_msg_count OUT NOCOPY NUMBER
229 -- x_msg_data OUT NOCOPY VARCHAR2
230 -- Version : Current version 1.0
231 -- Note: This automatic generated procedure definition, it includes standard IN/OUT NOCOPY parameters
232 -- and basic operation, developer must manually add parameters and business logic as necessary.
233 --
234 -- End of Comments
235 -- ==============================================================================
236 --
237
238 PROCEDURE Delete_Ctd(
239 p_api_version_number IN NUMBER,
240 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
241 p_commit IN VARCHAR2 := FND_API.G_FALSE,
242 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
243 x_return_status OUT NOCOPY VARCHAR2,
244 x_msg_count OUT NOCOPY NUMBER,
245 x_msg_data OUT NOCOPY VARCHAR2,
246 p_ctd_id IN NUMBER,
247 p_object_version_number IN NUMBER
248 );
249
250 -- ==============================================================================
251 -- Start of Comments
252 -- ==============================================================================
253 -- API Name
254 -- Lock_Ctd
255 -- Type
256 -- Private
257 -- Pre-Req
258 --
259 -- Parameters
260 --
261 -- IN
262 -- p_api_version_number IN NUMBER Required
263 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
264 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
265 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
266 -- p_ctd_rec IN ctd_rec_type Required
267 --
268 -- OUT NOCOPY
269 -- x_return_status OUT NOCOPY VARCHAR2
270 -- x_msg_count OUT NOCOPY NUMBER
271 -- x_msg_data OUT NOCOPY VARCHAR2
272 -- Version : Current version 1.0
273 -- Note: This automatic generated procedure definition, it includes standard IN/OUT NOCOPY parameters
274 -- and basic operation, developer must manually add parameters and business logic as necessary.
275 --
276 -- End of Comments
277 -- ==============================================================================
278 --
279
280 PROCEDURE Lock_Ctd(
281 p_api_version_number IN NUMBER,
282 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
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_ctd_id IN NUMBER,
289 p_object_version IN NUMBER
290 );
291
292
293 -- Start of Comments
294 --
295 -- validation procedures
296 --
297 -- p_validation_mode is a constant defined in AMS_UTILITY_PVT package
298 -- For create: G_CREATE, for update: G_UPDATE
299 -- Note: 1. This is automated generated item level validation procedure.
300 -- The actual validation detail is needed to be added.
301 -- 2. We can also validate table instead of record. There will be an option for user to choose.
302 -- End of Comments
303
304 PROCEDURE Validate_ctd(
305 p_api_version_number IN NUMBER,
306 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
307 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
308 p_ctd_rec IN ctd_rec_type,
309 x_return_status OUT NOCOPY VARCHAR2,
310 x_msg_count OUT NOCOPY NUMBER,
311 x_msg_data OUT NOCOPY VARCHAR2
312 );
313
314 -- Start of Comments
315 --
316 -- validation procedures
317 --
318 -- p_validation_mode is a constant defined in AMS_UTILITY_PVT package
319 -- For create: G_CREATE, for update: G_UPDATE
320 -- Note: 1. This is automated generated item level validation procedure.
321 -- The actual validation detail is needed to be added.
322 -- 2. Validate the unique keys, lookups here
323 -- End of Comments
324
325 PROCEDURE Check_ctd_Items (
326 P_ctd_rec IN ctd_rec_type,
327 p_validation_mode IN VARCHAR2,
328 x_return_status OUT NOCOPY VARCHAR2
329 );
330
331 -- Start of Comments
332 --
333 -- Record level validation procedures
334 --
335 -- p_validation_mode is a constant defined in AMS_UTILITY_PVT package
336 -- For create: G_CREATE, for update: G_UPDATE
337 -- Note: 1. This is automated generated item level validation procedure.
338 -- The actual validation detail is needed to be added.
339 -- 2. Developer can manually added inter-field level validation.
340 -- End of Comments
341
342 PROCEDURE Validate_ctd_rec(
343 p_api_version_number IN NUMBER,
344 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
345 x_return_status OUT NOCOPY VARCHAR2,
346 x_msg_count OUT NOCOPY NUMBER,
347 x_msg_data OUT NOCOPY VARCHAR2,
348 p_ctd_rec IN ctd_rec_type
349 );
350
351 --========================================================================
352 -- PROCEDURE
353 -- CHECK_MANDATORY_FIELDS
354 --
355 -- PURPOSE
356 -- This api is created to be used for validating ctd mandatory fields during
357 -- schedule status changes. Check ams_ctd_assoc_v
358 --
359 -- HISTORY
360 -- 30-Aug-2006 dbiswas Created.
361 --========================================================================
362
363 PROCEDURE CHECK_MANDATORY_FIELDS(
364 P_ctd_rec IN ctd_rec_type,
365 x_return_status OUT NOCOPY VARCHAR2
366 );
367
368 END AMS_CTD_PVT;