[Home] [Help]
PACKAGE BODY: APPS.IBU_ADMIN
Source
1 package body ibu_admin as
2 /* $Header: ibuadmnb.pls 115.10.1158.2 2002/07/24 23:42:30 jamose ship $ */
3
4 procedure ibu_get_subscribe_details (app_Id NUMBER,
5 lang_code VARCHAR2,
6 userId VARCHAR2,
7 header out VARCHAR2,
8 footer out VARCHAR2,
9 subject out VARCHAR2,
10 lstupdt in DATE)
11 as
12 l_return_status VARCHAR2(240);
13 l_api_version NUMBER;
14 l_init_msg_list VARCHAR2(240);
15 l_commit VARCHAR2(240);
16
17 l_msg_count NUMBER;
18 l_msg_data VARCHAR2(2000);
19 l_err_msg VARCHAR2(240);
20
21 l_profile_id NUMBER;
22 l_profile_name VARCHAR2(60);
23 l_profile_type VARCHAR2(30);
24 l_profile_attrib_tbl JTF_PERZ_PROFILE_PUB.PROFILE_ATTRIB_TBL_TYPE;
25
26 l_application_id NUMBER;
27
28 l_perz_data_id NUMBER;
29 l_perz_data_name VARCHAR2(60);
30 l_perz_data_type VARCHAR2(30);
31 l_perz_data_desc VARCHAR2(240);
32 l_data_attrib_tbl JTF_PERZ_DATA_PUB.DATA_ATTRIB_TBL_TYPE ;
33 l_data_out_tbl JTF_PERZ_DATA_PUB.DATA_OUT_TBL_TYPE;
34
35 out_perz_data_id NUMBER;
36
37 out_perz_data_name VARCHAR2(60);
38 out_perz_data_type VARCHAR2(30);
39 out_perz_data_desc VARCHAR2(240);
40 newln VARCHAR2(2) := fnd_global.newline ();
41 begin
42 l_api_version := 1.0;
43 l_init_msg_list := FND_API.G_TRUE;
44 l_application_id := app_Id;
45 l_perz_data_name := 'IBU_A_SUB_' || lang_code;
46 l_profile_name := 'IBU_A_PROFILE00';
47
48 JTF_PERZ_DATA_PVT.Get_Perz_Data
49 (
50 p_api_version_number => l_api_version,
51 p_init_msg_list => l_init_msg_list,
52 p_application_id => l_application_id,
53 p_profile_id => l_profile_id,
54 p_profile_name => l_profile_name,
55 p_perz_data_id => l_perz_data_id,
56 p_perz_data_name => l_perz_data_name,
57 p_perz_data_type => l_perz_data_type,
58
59 x_perz_data_id => out_perz_data_id,
60 x_perz_data_name => out_perz_data_name,
61 x_perz_data_type => out_perz_data_type,
62 x_perz_data_desc => out_perz_data_desc,
63 x_data_attrib_tbl => l_data_attrib_tbl,
64
65 x_return_status => l_return_status,
66 x_msg_count => l_msg_count,
67 x_msg_data => l_msg_data
68 );
69
70 /* Prepend footer with a new line */
71 footer := newln;
72
73 FOR l_curr_row in 1..l_data_attrib_tbl.count
74 LOOP
75 IF (l_data_attrib_tbl (l_curr_row).ATTRIBUTE_NAME = 'HEADER')
76 THEN
77 header := l_data_attrib_tbl (l_curr_row).ATTRIBUTE_VALUE;
78
79 ELSIF (l_data_attrib_tbl (l_curr_row).ATTRIBUTE_NAME = 'FOOTER')
80 THEN
81 footer := footer || l_data_attrib_tbl (l_curr_row).ATTRIBUTE_VALUE;
82
83 ELSE
84 subject := l_data_attrib_tbl (l_curr_row).ATTRIBUTE_VALUE;
85 END IF;
86 END LOOP;
87 ibu_replace_cluewords (userId, header, lstupdt);
88 /* dbms_output.put_line ('aft header=' || header); */
89 ibu_replace_cluewords (userId, subject, lstupdt);
90 /* dbms_output.put_line ('subject=' || subject); */
91 ibu_replace_cluewords (userId, footer, lstupdt);
92 /* dbms_output.put_line ('footer=' || footer); */
93 end ibu_get_subscribe_details;
94
95 /*---------------------------------------------------------------*/
96 procedure ibu_replace_cluewords (userId varchar2,
97 str in out varchar2,
98 lstupdt DATE)
99 as
100 username varchar2 (50);
101 firstname varchar2(50);
102 lastname varchar2(50);
103 lastmaildate date;
104 company_name varchar2 (50);
105 company_site varchar2 (50);
106
107 query_custid varchar2(200);
108 query_name varchar2(200);
109 query_companyname varchar2(200);
110 query_companysite varchar2(200);
111 query_companyemail varchar2(200);
112 currdate varchar2(30);
113 custId varchar2(30);
114 lastmaildt varchar2(30);
115 begin
116 query_custid := 'select customer_id from FND_USER where user_id='|| userId;
117 execute immediate query_custid into custId;
118 /* dbms_output.put_line ('custId =' || custId); */
119
120 query_name := 'select person_first_name, person_last_name from HZ_PARTIES where party_id =' || custId;
121
122 execute immediate query_name into firstname, lastname;
123 firstname := firstname || ' ' || lastname;
124 str := replace (str, '<FULL_USERNAME>', firstname);
125 /* dbms_output.put_line ('str=' || str); */
126
127 currdate := to_char(sysdate);
128 str := replace (str, '<CURRENT_DATE>', currdate);
129 /* dbms_output.put_line ('str=' || str); */
130
131 /* Last Mail Date */
132 lastmaildt := to_char(lstupdt);
133 str := replace (str, '<LAST_MAIL_DATE>', lastmaildt);
134 /* dbms_output.put_line ('str=' || str); */
135
136 /* Retrieve data company site */
137 company_site := getCompanyData('COMPANY_URL');
138 str := replace (str, '<COMPANY_SITE>', company_site);
139
140 /* Retrieve data company name */
141 company_name := getCompanyData('COMPANY_NAME');
142 str := replace (str, '<COMPANY_NAME>', company_name);
143
144 end ibu_replace_cluewords;
145
146 /*---------------------------------------------------------------*/
147 function getCompanyData ( perzDataName varchar2) return varchar2
148 is
149 l_return_status VARCHAR2(240);
150 l_api_version NUMBER;
151 l_init_msg_list VARCHAR2(240);
152 l_commit VARCHAR2(240);
153
154 l_msg_count NUMBER;
155 l_msg_data VARCHAR2(2000);
156 l_err_msg VARCHAR2(240);
157 my_message VARCHAR2(240);
158
159 l_profile_id NUMBER;
160 l_profile_name VARCHAR2(60);
161 l_profile_type VARCHAR2(30);
162 l_profile_attrib_tbl JTF_PERZ_PROFILE_PUB.PROFILE_ATTRIB_TBL_TYPE;
163
164 l_application_id NUMBER;
165
166 l_perz_data_id NUMBER;
167 l_perz_data_name VARCHAR2(60);
168 l_perz_data_type VARCHAR2(30);
169 l_perz_data_desc VARCHAR2(240);
170 l_data_attrib_tbl JTF_PERZ_DATA_PUB.DATA_ATTRIB_TBL_TYPE ;
171 l_data_out_tbl JTF_PERZ_DATA_PUB.DATA_OUT_TBL_TYPE;
172
173 out_perz_data_id NUMBER;
174
175 out_perz_data_name VARCHAR2(60);
176 out_perz_data_type VARCHAR2(30);
177 out_perz_data_desc VARCHAR2(240);
178 out_data_attrib_tbl JTF_PERZ_DATA_PUB.DATA_ATTRIB_TBL_TYPE;
179 profile_id NUMBER;
180 str VARCHAR2(50);
181 begin
182
183 /* Retrieve Property Manager Profile ID */
184 select profile_id into profile_id from jtf_perz_profile where profile_name like 'JTF_PROPERTY_MANAGER_DEFAULT_1';
185
186 /* Assignments */
187 l_api_version := 1.0;
188 l_init_msg_list := FND_API.G_TRUE;
189
190 l_perz_data_name := perzDataName;
191 l_profile_id := profile_id;
192 l_application_id := 690;
193
194 JTF_PERZ_DATA_PVT.Get_Perz_Data
195 (
196 p_api_version_number => l_api_version,
197 p_init_msg_list => l_init_msg_list,
198 p_application_id => l_application_id,
199 p_profile_id => l_profile_id,
200 p_profile_name => l_profile_name,
201 p_perz_data_id => l_perz_data_id,
202 p_perz_data_name => l_perz_data_name,
203 p_perz_data_type => l_perz_data_type,
204
205 x_perz_data_id => out_perz_data_id,
206 x_perz_data_name => out_perz_data_name,
207 x_perz_data_type => out_perz_data_type,
208 x_perz_data_desc => out_perz_data_desc,
209 x_data_attrib_tbl => l_data_attrib_tbl,
210
211 x_return_status => l_return_status,
212 x_msg_count => l_msg_count,
213 x_msg_data => l_msg_data
214 );
215
216 /* dbms_output.put_line ('return =' || l_return_status); */
217 str := l_data_attrib_tbl (1).ATTRIBUTE_VALUE;
218 return str;
219 exception
220 when others then
221 return '';
222 end;
223 /*---------------------------------------------------------------*/
224 procedure ibu_get_subscribe_interval (app_Id NUMBER,
225 prof_name VARCHAR2,
226 e_interval out VARCHAR2)
227 as
228 l_return_status VARCHAR2(240);
229 l_api_version NUMBER;
230 l_init_msg_list VARCHAR2(240);
231 l_commit VARCHAR2(240);
232
233 l_msg_count NUMBER;
234 l_msg_data VARCHAR2(2000);
235 l_err_msg VARCHAR2(240);
236
237 l_profile_id NUMBER;
238 l_profile_name VARCHAR2(60);
239 l_profile_type VARCHAR2(30);
240 l_profile_attrib_tbl JTF_PERZ_PROFILE_PUB.PROFILE_ATTRIB_TBL_TYPE;
241
242 l_application_id NUMBER;
243
244 l_perz_data_id NUMBER;
245 l_perz_data_name VARCHAR2(60);
246 l_perz_data_type VARCHAR2(30);
247 l_perz_data_desc VARCHAR2(240);
248 l_data_attrib_tbl JTF_PERZ_DATA_PUB.DATA_ATTRIB_TBL_TYPE ;
249 l_data_out_tbl JTF_PERZ_DATA_PUB.DATA_OUT_TBL_TYPE;
250
251 out_perz_data_id NUMBER;
252
253 out_perz_data_name VARCHAR2(60);
254 out_perz_data_type VARCHAR2(30);
255 out_perz_data_desc VARCHAR2(240);
256 begin
257
258 l_api_version := 1.0;
259 l_init_msg_list := FND_API.G_TRUE;
260 l_application_id := app_Id;
261 l_perz_data_name := 'IBU_A_SUBE';
262 l_profile_name := 'IBU_A_PROFILE00';
263
264 JTF_PERZ_DATA_PVT.Get_Perz_Data
265 (
266 p_api_version_number => l_api_version,
267 p_init_msg_list => l_init_msg_list,
268 p_application_id => l_application_id,
269 p_profile_id => l_profile_id,
270 p_profile_name => l_profile_name,
271 p_perz_data_id => l_perz_data_id,
272 p_perz_data_name => l_perz_data_name,
273 p_perz_data_type => l_perz_data_type,
274
275 x_perz_data_id => out_perz_data_id,
276 x_perz_data_name => out_perz_data_name,
277 x_perz_data_type => out_perz_data_type,
278 x_perz_data_desc => out_perz_data_desc,
279 x_data_attrib_tbl => l_data_attrib_tbl,
280
281 x_return_status => l_return_status,
282 x_msg_count => l_msg_count,
283 x_msg_data => l_msg_data
284 );
285
286
287 FOR l_curr_row in 1..l_data_attrib_tbl.count
288 LOOP
289 IF (l_data_attrib_tbl (l_curr_row).ATTRIBUTE_NAME = 'DEFAULT_INTERVAL')
290 THEN
291 e_interval := l_data_attrib_tbl (l_curr_row).ATTRIBUTE_VALUE;
292 END IF;
293 END LOOP;
294 end ibu_get_subscribe_interval;
295
296 procedure ibu_get_cnews_filter (app_Id NUMBER,
297 filter_list out IBU_HOME_PAGE_PVT.Filter_Data_List_Type)
298 as
299 l_return_status VARCHAR2(240);
300 l_api_version NUMBER;
301 l_init_msg_list VARCHAR2(240);
302 l_commit VARCHAR2(240);
303
304 l_msg_count NUMBER;
305 l_msg_data VARCHAR2(2000);
306 l_err_msg VARCHAR2(240);
307
308 l_profile_id NUMBER;
309 l_profile_name VARCHAR2(60);
310 l_profile_type VARCHAR2(30);
311 l_profile_attrib_tbl JTF_PERZ_PROFILE_PUB.PROFILE_ATTRIB_TBL_TYPE;
312
313 l_application_id NUMBER;
314
315 l_perz_data_id NUMBER;
316 l_perz_data_name VARCHAR2(60);
317 l_perz_data_type VARCHAR2(30);
318 l_perz_data_desc VARCHAR2(240);
319 l_data_attrib_tbl JTF_PERZ_DATA_PUB.DATA_ATTRIB_TBL_TYPE ;
320 l_data_out_tbl JTF_PERZ_DATA_PUB.DATA_OUT_TBL_TYPE;
321
322 out_perz_data_id NUMBER;
323
324 out_perz_data_name VARCHAR2(60);
325 out_perz_data_type VARCHAR2(30);
326 out_perz_data_desc VARCHAR2(240);
327
328 data IBU_HOME_PAGE_PVT.Filter_Data_Type;
329 ind NUMBER := 1;
330 begin
331
332 l_api_version := 1.0;
333 l_init_msg_list := FND_API.G_TRUE;
334 l_application_id := app_Id;
335 l_perz_data_name := 'IBU_A_CATEGORY';
336 l_profile_name := 'IBU_A_PROFILE00';
337
338 JTF_PERZ_DATA_PVT.Get_Perz_Data
339 (
340 p_api_version_number => l_api_version,
341 p_init_msg_list => l_init_msg_list,
342 p_application_id => l_application_id,
343 p_profile_id => l_profile_id,
344 p_profile_name => l_profile_name,
345 p_perz_data_id => l_perz_data_id,
346 p_perz_data_name => l_perz_data_name,
347 p_perz_data_type => l_perz_data_type,
348
349 x_perz_data_id => out_perz_data_id,
350 x_perz_data_name => out_perz_data_name,
351 x_perz_data_type => out_perz_data_type,
352 x_perz_data_desc => out_perz_data_desc,
353 x_data_attrib_tbl => l_data_attrib_tbl,
354
355 x_return_status => l_return_status,
356 x_msg_count => l_msg_count,
357 x_msg_data => l_msg_data
358 );
359
360
361 filter_list := IBU_HOME_PAGE_PVT.Filter_Data_List_Type ();
362 FOR f_curr_row IN 1..l_data_attrib_tbl.count
363 LOOP
364 data.name := l_data_attrib_tbl (f_curr_row).ATTRIBUTE_NAME;
365 /* dbms_output.put_line ('NAme=' || data.name); */
366 data.value := l_data_attrib_tbl (f_curr_row).ATTRIBUTE_VALUE;
367
368 filter_list.extend ();
369 filter_list (ind) := data;
370 ind := ind + 1;
371 END LOOP;
372 end ibu_get_cnews_filter;
373 end ibu_admin;