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