[Home] [Help]
PACKAGE BODY: APPS.AMS_CTD_UTIL_PKG
Source
1 PACKAGE BODY AMS_CTD_UTIL_PKG AS
2 /* $Header: amsvctub.pls 120.4 2006/01/31 20:29:10 batoleti noship $ */
3
4
5 -- ===============================================================
6 -- Start of Comments
7 -- Package name
8 -- AMS_CTD_UTIL_PKG`
9 -- Purpose
10 --
11 -- This package contains utility methods for CTD
12 --
13 -- History
14 --
15 -- NOTE
16 --
17 -- End of Comments
18 -- ===============================================================
19 G_PACKAGE_NAME CONSTANT VARCHAR2(30) := 'AMS_CTD_UTIL_PKG';
20 G_AMPERSAND VARCHAR2(1) := '&';
21
22 -- Start of Comments
23 -- Name
24 -- GET_TRACKING_URL
25 --
26 -- Note: Once we start supporting Offer for Web ADI,
27 -- we need to add Offer Code
28
29 Procedure GET_TRACKING_URL(
30 p_ctd_id number,
31 p_schedule_id NUMBER,
32 p_schedule_src_code varchar2,
33 p_track_flag varchar2,
34 x_tracking_url OUT nocopy varchar2
35 )
36 IS
37 BEGIN
38 x_tracking_url := 'amsWebTracking.jsp?ctdid='||p_ctd_id||G_Ampersand||'sccd='||p_schedule_src_code||G_Ampersand||'objid='||p_schedule_id||G_Ampersand||'t='||p_track_flag;
39 END GET_TRACKING_URL;
40
41 PROCEDURE GET_EVENT_INFO
42 (p_event_id number,
43 x_source_code out nocopy varchar2
44 )
45
46 IS
47
48 -- The SQL taken from oracle.apps.ams.java.oa.events.server.EventOfferDetailsVVO.xml
49
50 CURSOR C_GET_EVENT_INFO
51 IS
52 SELECT
53 DECODE(e.parent_type,'CAMP',s.source_code,e.source_code) event_source_code
54 FROM
55 AMS_EVENT_OFFERS_ALL_B e,
56 ams_campaign_schedules_b s
57 WHERE
58 e.event_offer_id = s.related_event_id (+)
59 AND s.related_event_from(+) = 'EONE'
60 AND e.EVENT_OFFER_ID = p_event_id;
61
62 BEGIN
63 open C_GET_EVENT_INFO;
64
65 fetch C_GET_EVENT_INFO
66 into x_source_code;
67
68 close C_GET_EVENT_INFO;
69
70 END;
71
72 PROCEDURE GET_SURVEY_URL(p_deployment_id number,
73 x_survey_url out nocopy varchar2)
74
75 IS
76
77 CURSOR C_CHECK_SURVEY_TABLE (p_table_owner varchar2)
78 IS
79 SELECT 'Y'
80 FROM ALL_TAB_COLUMNS
81 WHERE TABLE_NAME='IES_SVY_SURVEYS_ALL'
82 AND COLUMN_NAME = 'SURVEY_TYPE'
83 AND OWNER = p_table_owner;
84
85 CURSOR C_GET_SURVEY_TYPE
86 IS
87 select svy.survey_type
88 from ies_svy_surveys_all svy,
89 IES_SVY_CYCLES_ALL cyc,
90 IES_SVY_DEPLYMENTS_ALL dep
91 where dep.survey_deployment_id = p_deployment_id
92 and dep.survey_cycle_id = cyc.survey_cycle_id
93 and cyc.survey_id = svy.survey_id;
94
95 l_flag varchar2(1);
96 l_survey_type varchar2(30);
97 l_return_status boolean;
98 l_status varchar2(30);
99 l_industry varchar2(30);
100 l_table_owner varchar2(30);
101
102
103 BEGIN
104
105 -- Get the schema owner
106 l_return_status := FND_INSTALLATION.GET_APP_INFO
107 ( application_short_name => 'IES',
108 status => l_status,
109 industry => l_industry,
110 oracle_schema =>l_table_owner);
111
112
113 -- Check whether Survey Table exists or not
114 OPEN C_CHECK_SURVEY_TABLE(l_table_owner);
115 FETCH C_CHECK_SURVEY_TABLE
116 INTO l_flag;
117 CLOSE C_CHECK_SURVEY_TABLE;
118
119 IF (l_flag = 'Y') THEN
120
121 -- Get Survey Type
122 open C_GET_SURVEY_TYPE;
123 fetch C_GET_SURVEY_TYPE
124 into l_survey_type;
125 close C_GET_SURVEY_TYPE;
126
127 IF ((l_survey_type is null) or (l_survey_type = 'JTT')) THEN
128
129 x_survey_url := 'iessvymain.jsp?dID='||p_deployment_id;
130
131
132 ELSE
133 x_survey_url := 'OA.jsp?OAFunc=IES_SURVEY_OARUNTIME'||G_AMPERSAND||'dID='||p_deployment_id;
134
135 END IF;
136
137 ELSE
138 x_survey_url := 'iessvymain.jsp';
139
140 END IF;
141
142
143
144
145
146
147 END;
148
149 -- Start of Comments
150 -- Name
151 -- GET_FORWARDING_URL
152 --
153
154 Procedure GET_FORWARDING_URL(
155 p_action_id NUMBER,
156 p_parameter_id1 NUMBER,
157 p_parameter_id2 NUMBER,
158 p_parameter_id3 NUMBER,
159 p_add_param1 varchar2,
160 p_add_param_value1 varchar2,
161 p_add_param2 varchar2,
162 p_add_param_value2 varchar2,
163 p_add_param3 varchar2,
164 p_add_param_value3 varchar2,
165 p_add_param4 varchar2,
166 p_add_param_value4 varchar2,
167 p_add_param5 varchar2,
168 p_add_param_value5 varchar2,
169 p_url_text varchar2,
170 p_schedule_id number,
171 x_forwarding_url out nocopy varchar2
172 )
173 IS
174
175 cursor c_get_source_code_id
176 is
177 select source_code_id from ams_source_codes
178 where arc_source_code_for = 'CSCH'
179 and source_code_for_id = p_schedule_id
180 and active_flag='Y';
181
182 l_source_code_id number;
183 l_event_source_code varchar2(2000);
184 l_encrypted_source_code varchar2(2050);
185 l_survey_url varchar2(250);
186
187
188 BEGIN
189
190 -- Get Source Code Id if its an iStore Action
191 IF (p_action_id in (2,3,4,5,6,7)) THEN
192 open c_get_source_code_id;
193 fetch c_get_source_code_id into l_source_code_id;
194 close c_get_source_code_id;
195
196 END IF;
197
198 IF (p_action_id = 1) THEN
199 -- Go To URL
200 x_forwarding_url := p_url_text;
201 ELSIF (p_action_id = 6) THEN
202 -- Go To iStore Registration
203 x_forwarding_url := 'ibeCZzpEntry.jsp?go=signin'||G_Ampersand||'msource='||to_char(l_source_code_id);
204 ELSIF (p_action_id = 7) THEN
205 -- Go To Minisite
206 -- x_forwarding_url := 'ibeCZzpEntry.jsp?go=catalog'||G_Ampersand||'site='||p_parameter_id1||G_Ampersand||'msource='||to_char(l_source_code_id);
207 x_forwarding_url := 'ibeCZzpEntry.jsp?go=catalog'||G_Ampersand||'minisite='||p_parameter_id1||G_Ampersand||'msource='||to_char(l_source_code_id);
208 ELSIF (p_action_id = 5) THEN
209 -- Go to Section
210 -- x_forwarding_url := 'ibeCZzpEntry.jsp?go=section'||G_Ampersand||'site='||p_parameter_id1||G_Ampersand||'section='||p_parameter_id2||G_Ampersand||'msource='||to_char(l_source_code_id);
211 x_forwarding_url := 'ibeCZzpEntry.jsp?go=section'||G_Ampersand||'minisite='||p_parameter_id1||G_Ampersand||'section='||p_parameter_id2||G_Ampersand||'msource='||to_char(l_source_code_id);
212 ELSIF (p_action_id = 2) THEN
213 -- Go to shopping Cart
214 x_forwarding_url := 'ibeCZzpEntry.jsp?go=cart'||G_Ampersand||'msource='||to_char(l_source_code_id);
215 ELSIF (p_action_id = 4) THEN
216 -- Go to shopping Cart with an item
217 -- x_forwarding_url := 'ibeCZzpEntry.jsp?go=buy'||G_Ampersand||'site='||p_parameter_id2||G_Ampersand||'item='||p_parameter_id1||G_Ampersand||'msource='||to_char(l_source_code_id);
218 x_forwarding_url := 'ibeCZzpEntry.jsp?go=buy'||G_Ampersand||'minisite='||p_parameter_id2||G_Ampersand||'item='||p_parameter_id1||G_Ampersand||'msource='||to_char(l_source_code_id);
219 ELSIF (p_action_id = 3) THEN
220 -- Go to item details
221 -- x_forwarding_url := 'ibeCZzpEntry.jsp?go=item'||G_Ampersand||'site='||p_parameter_id2||G_Ampersand||'section='||p_parameter_id3||G_Ampersand||'item='||p_parameter_id1||G_Ampersand||'msource='||to_char(l_source_code_id);
222 x_forwarding_url := 'ibeCZzpEntry.jsp?go=item'||G_Ampersand||'minisite='||p_parameter_id2||G_Ampersand||'section='||p_parameter_id3||G_Ampersand||'item='||p_parameter_id1||G_Ampersand||'msource='||to_char(l_source_code_id);
223 ELSIF (p_action_id = 9) THEN
224 -- Go to content item
225 x_forwarding_url := 'ibcGetContentItem.jsp?cItemId='||p_parameter_id1||G_Ampersand||'stlId='||p_parameter_id2||G_Ampersand||'loadMode=deep';
226 ELSIF (p_action_id = 8) THEN
227
228 GET_SURVEY_URL (p_parameter_id1,l_survey_url);
229
230 -- Go to Web Script
231 IF (p_parameter_id2 is not null) THEN
232
233 GET_EVENT_INFO(p_parameter_id2,l_event_source_code);
234
235 Encrypt(l_event_source_code,l_encrypted_source_code);
236
237 IF (l_encrypted_source_code is not null) then
238 l_event_source_code := l_encrypted_source_code;
239 END IF;
240
241 x_forwarding_url := l_survey_url||G_Ampersand||'esscd='||l_event_source_code;
242
243 ELSE
244 x_forwarding_url := l_survey_url;
245
246 END IF;
247
248 IF ((p_add_param1 is not null) and (p_add_param_value1 is not null)) THEN
249 x_forwarding_url := x_forwarding_url||G_Ampersand||p_add_param1||'='||p_add_param_value1;
250 END IF;
251
252 IF ((p_add_param2 is not null) and (p_add_param_value2 is not null)) THEN
253 x_forwarding_url := x_forwarding_url||G_Ampersand||p_add_param2||'='||p_add_param_value2;
254 END IF;
255
256 IF ((p_add_param3 is not null) and (p_add_param_value3 is not null)) THEN
257 x_forwarding_url := x_forwarding_url||G_Ampersand||p_add_param3||'='||p_add_param_value3;
258 END IF;
259
260 IF ((p_add_param4 is not null) and (p_add_param_value4 is not null)) THEN
261 x_forwarding_url := x_forwarding_url||G_Ampersand||p_add_param4||'='||p_add_param_value4;
262 END IF;
263
264 IF ((p_add_param5 is not null) and (p_add_param_value5 is not null)) THEN
265 x_forwarding_url := x_forwarding_url||G_Ampersand||p_add_param5||'='||p_add_param_value5;
266 END IF;
267
268 END IF;
269
270 END GET_FORWARDING_URL;
271
272
273 Procedure GET_ACTION_PARAM_ID (
274 p_action_id NUMBER,
275 x_act_param_code_list out nocopy jtf_varchar2_table_100,
276 x_act_param_id_list out nocopy jtf_number_table
277 )
278 IS
279 cursor c_get_act_param
280 is
281 select action_param_id,action_param_code
282 from ams_clik_thru_act_params_b
283 where action_id=p_action_id;
284
285 BEGIN
286 OPEN c_get_act_param;
287 fetch c_get_act_param
288 bulk collect into x_act_param_id_list,x_act_param_code_list;
289 close c_get_act_param;
290 END;
291
292 Procedure GetUsedByType(
293 p_activity_id number,
294 x_used_by_type out nocopy varchar2
295 )
296 IS
297 BEGIN
298 IF (p_activity_id = 30) THEN
299 x_used_by_type := 'WEB_AD';
300 ELSIF (p_activity_id = 40) THEN
301 x_used_by_type := 'WEB_OFFER';
302 END IF;
303 END;
304
305 Procedure Encrypt (
306 p_value varchar2,
307 x_value out nocopy varchar2
308 )
309 IS
310
311 l_encryption_key varchar2(30);
312 l_left_enc_delim varchar2(30);
313 l_right_enc_delim varchar2(30);
314 l_encrypted_value varchar2(2000);
315
316 BEGIN
317
318 IF (p_value is not null) THEN
319
320 -- Check the Fulfillment security profile
321 -- Get the Encryption Key
322 l_encryption_key := FND_PROFILE.Value('JTF_FM_SECURITY_KEY');
323
324 l_left_enc_delim := FND_PROFILE.Value('JTF_FM_LENCRYPT_DELIM');
325 l_right_enc_delim := FND_PROFILE.VALUE('JTF_FM_RENCRYPT_DELIM');
326
327 IF ((l_encryption_key is not null) AND (l_left_enc_delim is not null)
328 AND (l_right_enc_delim is not null))
329 THEN
330
331 l_encrypted_value := fnd_web_sec.encrypt(l_encryption_key,p_value);
332 x_value := l_left_enc_delim||l_encrypted_value||l_right_enc_delim;
333
334 END IF;
335
336 END IF;
337
338 END;
339
340
341
342 END AMS_CTD_UTIL_PKG;