[Home] [Help]
PACKAGE BODY: APPS.AMS_ADI_CTD_PVT
Source
1 PACKAGE BODY AMS_ADI_CTD_PVT AS
2 /* $Header: amsvadtb.pls 120.3 2005/08/31 04:36:32 mayjain noship $ */
3
4
5 -- ===============================================================
6 -- Start of Comments
7 -- Package name
8 -- AMS_ADI_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_ADI_CTD_PVT';
20
21 -- Start of Comments
22 -- Name
23 -- CREATE_CTDS
24 --
25
26 Procedure CREATE_CTDS(
27 p_action_id NUMBER,
28 p_parameter_id1 NUMBER,
29 p_parameter_id2 NUMBER,
30 p_parameter_id3 NUMBER,
31 p_url_text varchar2,
32 p_adhoc_param_name1 varchar2 default null,
33 p_adhoc_param_name2 varchar2 default null,
34 p_adhoc_param_name3 varchar2 default null,
35 p_adhoc_param_name4 varchar2 default null,
36 p_adhoc_param_name5 varchar2 default null,
37 p_adhoc_param_val1 varchar2 default null,
38 p_adhoc_param_val2 varchar2 default null,
39 p_adhoc_param_val3 varchar2 default null,
40 p_adhoc_param_val4 varchar2 default null,
41 p_adhoc_param_val5 varchar2 default null,
42 p_used_by_id_list JTF_NUMBER_TABLE,
43 p_schedule_id number,
44 p_activity_id number,
45 p_schedule_src_code varchar2,
46 x_ctd_id_list OUT nocopy jtf_number_table,
47 x_msg_count number,
48 x_msg_data varchar2,
49 x_return_status out nocopy varchar2,
50 p_activity_product_id NUMBER
51 )
52 IS
53
54 l_ctd_rec AMS_CTD_PVT.ctd_rec_type;
55 l_ctd_param_val AMS_Ctd_Prm_Val_PVT.ctd_prm_val_rec_type;
56 l_adhoc_param AMS_Adhoc_Param_PVT.adhoc_param_rec_type;
57 l_act_param_code_list JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
58 l_act_param_id_list JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
59
60 l_forward_url varchar2(2000);
61 l_track_url varchar2(2000);
62 l_return_status varchar2(30);
63 l_msg_count number;
64 l_msg_data varchar2(4000);
65 l_ctd_id number;
66 l_ctd_id_list jtf_number_table := JTF_NUMBER_TABLE(1);
67 l_tracking_url varchar2(2000);
68 l_forwarding_url varchar2(2000);
69 l_used_by_id number;
70 l_act_param_val_id number;
71 l_adhoc_param_id number;
72 l_create_flag varchar2(1) := 'N';
73 l_used_by_type varchar2(30);
74
75 BEGIN
76 x_return_status := FND_API.G_RET_STS_SUCCESS;
77 x_ctd_id_list := JTF_NUMBER_TABLE();
78
79
80 AMS_CTD_UTIL_PKG.GET_FORWARDING_URL(
81 p_action_id => p_action_id,
82 p_parameter_id1 => p_parameter_id1,
83 p_parameter_id2 => p_parameter_id2,
84 p_parameter_id3 => p_parameter_id3,
85 p_add_param1 => p_adhoc_param_name1,
86 p_add_param_value1 => p_adhoc_param_val1,
87 p_add_param2 => p_adhoc_param_name2,
88 p_add_param_value2 => p_adhoc_param_val2,
89 p_add_param3 => p_adhoc_param_name3,
90 p_add_param_value3 => p_adhoc_param_val3,
91 p_add_param4 => p_adhoc_param_name4,
92 p_add_param_value4 => p_adhoc_param_val4,
93 p_add_param5 => p_adhoc_param_name5,
94 p_add_param_value5 => p_adhoc_param_val5,
95 p_url_text => p_url_text,
96 p_schedule_id => p_schedule_id,
97 x_forwarding_url => l_forwarding_url
98 );
99
100 --dbms_output.put_line('Forward URL='||l_forwarding_url);
101
102 -- Set the CTD record
103 l_ctd_rec.action_id := p_action_id;
104 l_ctd_rec.forward_url := l_forwarding_url;
105 l_ctd_rec.activity_product_id := p_activity_product_id;
106
107 For i in p_used_by_id_list.first .. p_used_by_id_list.last
108 LOOP
109 --dbms_output.put_line('Used By Id ='||p_used_by_id_list(i));
110
111 -- Create CTD
112 AMS_CTD_PVT.Create_Ctd(
113 p_api_version_number => 1.0,
114 p_ctd_rec => l_ctd_rec,
115 x_return_status => l_return_status,
116 x_msg_count => l_msg_count,
117 x_msg_data => l_msg_data,
118 x_ctd_id => l_ctd_id
119 );
120
121 --dbms_output.put_line('Before extension');
122 x_ctd_id_list.extend;
123 --dbms_output.put_line('After extension');
124 x_ctd_id_list(i) := l_ctd_id;
125
126 --dbms_output.put_line('CTD Id ='||to_char(l_ctd_id));
127
128 AMS_CTD_UTIL_PKG.GET_TRACKING_URL(
129 p_ctd_id => l_ctd_id,
130 p_schedule_id => p_schedule_id,
131 p_schedule_src_code => p_schedule_src_code,
132 p_track_flag => 'N',
133 x_tracking_url => l_tracking_url
134 );
135
136 --dbms_output.put_line('Tracking URL= '||l_tracking_url);
137
138 -- Update Tracking URL
139 update ams_ctds
140 set track_url = l_tracking_url
141 where ctd_id=l_ctd_id;
142
143 -- Create Asociation only for Web Schedules
144 --dbms_output.put_line('Create Association');
145 IF (p_activity_id in (30,40)) THEN
146 l_used_by_id := p_used_by_id_list(i);
147 l_ctd_id_list (1) := l_ctd_id;
148
149 AMS_CTD_UTIL_PKG.GetUsedByType(
150 p_activity_id => p_activity_id,
151 x_used_by_type => l_used_by_type
152 );
153
154 AMS_CTD_PVT.CREATE_ASSOCIATION (
155 l_ctd_id_list,
156 l_used_by_type,
157 to_char(l_used_by_id)
158 );
159 END IF;
160
161 -- Create URL Param Values
162 IF p_action_id not in (1,2,6) THEN
163 AMS_CTD_UTIL_PKG.GET_ACTION_PARAM_ID(
164 p_action_id => p_action_id,
165 x_act_param_code_list =>l_act_param_code_list,
166 x_act_param_id_list => l_act_param_id_list
167 );
168
169 IF (p_action_id = 7) THEN
170 -- Go To Minisite accepts only one parameter
171 l_ctd_param_val.action_param_value := to_char(p_parameter_id1);
172 l_ctd_param_val.ctd_id := l_ctd_id;
173 l_ctd_param_val.action_param_id := l_act_param_id_list(1);
174
175 AMS_Ctd_Prm_Val_PVT.Create_Ctd_Prm_Val(
176 p_api_version_number => 1.0,
177 x_return_status => l_return_status,
178 x_msg_count => l_msg_count,
179 x_msg_data => l_msg_data,
180 p_ctd_prm_val_rec => l_ctd_param_val,
181 x_action_param_value_id => l_act_param_val_id
182 );
183 ELSIF (p_action_id in (5,8,9)) THEN
184 -- Go To Section
185 -- Go To Content Item and Go To Web Script
186 -- All these sections accept 2 parameters
187 For i in 1..2
188 Loop
189
190 IF (i = 1) THEN
191
192 l_ctd_param_val.action_param_value :=
193 to_char(p_parameter_id1);
194
195 ELSE
196
197 IF (p_action_id = 8) THEN
198
199 IF (p_parameter_id2 is null) THEN
200 EXIT;
201 ELSE
202 l_ctd_param_val.action_param_value :=
203 to_char(p_parameter_id2);
204 END IF;
205
206 ELSE
207
208 l_ctd_param_val.action_param_value :=
209 to_char(p_parameter_id2);
210 END IF;
211 END IF;
212
213 l_ctd_param_val.ctd_id := l_ctd_id;
214 l_ctd_param_val.action_param_id := l_act_param_id_list(i);
215
216 AMS_Ctd_Prm_Val_PVT.Create_Ctd_Prm_Val(
217 p_api_version_number => 1.0,
218 x_return_status => l_return_status,
219 x_msg_count => l_msg_count,
220 x_msg_data => l_msg_data,
221 p_ctd_prm_val_rec => l_ctd_param_val,
222 x_action_param_value_id => l_act_param_val_id
223 );
224 END Loop;
225
226 -- For Web Script Action, insert adhoc parameter
227 IF (p_action_id=8) THEN
228 FOR k in 1.. 5
229 LOOP
230 l_create_flag := 'N';
231 IF (k = 1) THEN
232 IF ((p_adhoc_param_name1 is not null) and
233 (p_adhoc_param_val1 is not null)) THEN
234
235 l_adhoc_param.adhoc_param_code :=
236 p_adhoc_param_name1;
237 l_adhoc_param.adhoc_param_value :=
238 p_adhoc_param_val1;
239 l_create_flag := 'Y';
240 END IF;
241 ELSIF (k = 2) THEN
242 IF ((p_adhoc_param_name2 is not null) and
243 (p_adhoc_param_val2 is not null)) THEN
244
245 l_adhoc_param.adhoc_param_code :=
246 p_adhoc_param_name2;
247 l_adhoc_param.adhoc_param_value :=
248 p_adhoc_param_val2;
249 l_create_flag := 'Y';
250 ENd IF;
251
252 ELSIF (k = 3) THEN
253 IF ((p_adhoc_param_name3 is not null) and
254 (p_adhoc_param_val3 is not null)) THEN
255
256 l_adhoc_param.adhoc_param_code :=
257 p_adhoc_param_name3;
258 l_adhoc_param.adhoc_param_value :=
259 p_adhoc_param_val3;
260 l_create_flag := 'Y';
261 ENd IF;
262
263 ELSIF (k = 4) THEN
264 IF ((p_adhoc_param_name4 is not null) and
265 (p_adhoc_param_val4 is not null)) THEN
266
267 l_adhoc_param.adhoc_param_code :=
268 p_adhoc_param_name4;
269 l_adhoc_param.adhoc_param_value :=
270 p_adhoc_param_val4;
271 l_create_flag := 'Y';
272 ENd IF;
273 ELSIF (k = 5) THEN
274 IF ((p_adhoc_param_name5 is not null) and
275 (p_adhoc_param_val5 is not null)) THEN
276
277 l_adhoc_param.adhoc_param_code :=
278 p_adhoc_param_name5;
279 l_adhoc_param.adhoc_param_value :=
280 p_adhoc_param_val5;
281 l_create_flag := 'Y';
282 ENd IF;
283
284 END IF;
285
286 IF (l_create_flag = 'Y') THEN
287 l_adhoc_param.ctd_id := l_ctd_id;
288 AMS_Adhoc_Param_PVT.Create_Adhoc_Param(
289 p_api_version_number => 1.0,
290 x_return_status => l_return_status,
291 x_msg_count => l_msg_count,
292 x_msg_data => l_msg_data,
293 p_adhoc_param_rec => l_adhoc_param,
294 x_adhoc_param_id => l_adhoc_param_id
295 );
296
297 END IF;
298
299 END LOOP;
300
301 END IF;
302
303 ELSIF (p_action_id in (3,4) ) THEN
304 -- Go To Item Detail
305 -- In this case, the order of param_ids are different than
306 -- the order used in web adi
307
308 For i in 1..3
309 Loop
310
311 For j in l_act_param_code_list.FIRST .. l_act_param_code_list.LAST
312 LOOP
313 IF (i = 1) THEN
314 IF (l_act_param_code_list(j) = 'item') THEN
315 l_ctd_param_val.action_param_id := l_act_param_id_list(j);
316 l_ctd_param_val.action_param_value := to_char(p_parameter_id1);
317
318 END IF;
319 ELSIF (i = 2) THEN
320 IF (l_act_param_code_list(j) = 'minisite') THEN
321 l_ctd_param_val.action_param_id := l_act_param_id_list(j);
322 l_ctd_param_val.action_param_value := to_char(p_parameter_id2);
323
324 END IF;
325 ELSIF (i = 3) THEN
326 IF (l_act_param_code_list(j) = 'section') THEN
327 IF (p_action_id = 3) THEN
328 l_ctd_param_val.action_param_id := l_act_param_id_list(j);
329 l_ctd_param_val.action_param_value := to_char(p_parameter_id3);
330 END IF;
331 END IF;
332
333 END IF;
334 END LOOP;
335
336 l_ctd_param_val.ctd_id := l_ctd_id;
337
338 AMS_Ctd_Prm_Val_PVT.Create_Ctd_Prm_Val(
339 p_api_version_number => 1.0,
340 x_return_status => l_return_status,
341 x_msg_count => l_msg_count,
342 x_msg_data => l_msg_data,
343 p_ctd_prm_val_rec => l_ctd_param_val,
344 x_action_param_value_id => l_act_param_val_id
345 );
346 END Loop;
347
348 END IF;
349
350 END IF;
351
352
353 END LOOP;
354
355
356 EXCEPTION
357 WHEN OTHERS THEN
358 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
359 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
360 fnd_msg_pub.add_exc_msg('AMS_ADI_CTD_PVT', 'CREATE_CTDS');
361 END IF;
362 RAISE;
363
364 END CREATE_CTDS;
365
366
367 END AMS_ADI_CTD_PVT;