eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.ALR_DISTRIBUTION_LISTS_PKG

Source


1 package body ALR_DISTRIBUTION_LISTS_PKG as
2 /* $Header: ALRDLSTB.pls 120.4.12010000.1 2008/07/27 06:58:38 appldev ship $ */
3 procedure INSERT_ROW (
4   X_ROWID in out nocopy VARCHAR2,
5   X_APPLICATION_ID in NUMBER,
6   X_LIST_ID in NUMBER,
7   X_END_DATE_ACTIVE in DATE,
8   X_NAME in VARCHAR2,
9   X_DESCRIPTION in VARCHAR2,
10   X_TO_RECIPIENTS in VARCHAR2,
11   X_CC_RECIPIENTS in VARCHAR2,
12   X_BCC_RECIPIENTS in VARCHAR2,
13   X_PRINT_RECIPIENTS in VARCHAR2,
14   X_PRINTER in VARCHAR2,
15   X_REPLY_TO in VARCHAR2,
16   X_ENABLED_FLAG in VARCHAR2,
17   X_CREATION_DATE in DATE,
18   X_CREATED_BY in NUMBER,
19   X_LAST_UPDATE_DATE in DATE,
20   X_LAST_UPDATED_BY in NUMBER,
21   X_LAST_UPDATE_LOGIN in NUMBER
22 ) is
23   cursor C is select ROWID from ALR_DISTRIBUTION_LISTS
24     where APPLICATION_ID = X_APPLICATION_ID
25     and LIST_ID = X_LIST_ID
26     and ((end_date_active  is null)
27     or ((end_date_active is not null)
28     and (end_date_active =
29       to_date(X_END_DATE_ACTIVE,'YYYY/MM/DD HH24:MI:SS'))))
30     ;
31 begin
32   insert into ALR_DISTRIBUTION_LISTS (
33     APPLICATION_ID,
34     LIST_ID,
35     NAME,
36     LAST_UPDATE_DATE,
37     LAST_UPDATED_BY,
38     CREATION_DATE,
39     CREATED_BY,
40     LAST_UPDATE_LOGIN,
41     DESCRIPTION,
42     TO_RECIPIENTS,
43     CC_RECIPIENTS,
44     BCC_RECIPIENTS,
45     PRINT_RECIPIENTS,
46     PRINTER,
47     REPLY_TO,
48     END_DATE_ACTIVE,
49     ENABLED_FLAG
50   ) values (
51     X_APPLICATION_ID,
52     X_LIST_ID,
53     X_NAME,
54     X_LAST_UPDATE_DATE,
55     X_LAST_UPDATED_BY,
56     X_CREATION_DATE,
57     X_CREATED_BY,
58     X_LAST_UPDATE_LOGIN,
59     X_DESCRIPTION,
60     X_TO_RECIPIENTS,
61     X_CC_RECIPIENTS,
62     X_BCC_RECIPIENTS,
63     X_PRINT_RECIPIENTS,
64     X_PRINTER,
65     X_REPLY_TO,
66     X_END_DATE_ACTIVE,
67     X_ENABLED_FLAG );
68 
69   open c;
70   fetch c into X_ROWID;
71   if (c%notfound) then
72     close c;
73     raise no_data_found;
74   end if;
75   close c;
76 
77 end INSERT_ROW;
78 
79 procedure LOAD_ROW (
80   X_APPLICATION_SHORT_NAME in VARCHAR2,
81   X_NAME in VARCHAR2,
82   X_END_DATE_ACTIVE in VARCHAR2,
83   X_OWNER in VARCHAR2,
84   X_DESCRIPTION in VARCHAR2,
85   X_TO_RECIPIENTS in VARCHAR2,
86   X_CC_RECIPIENTS in VARCHAR2,
87   X_BCC_RECIPIENTS in VARCHAR2,
88   X_PRINT_RECIPIENTS in VARCHAR2,
89   X_PRINTER in VARCHAR2,
90   X_REPLY_TO in VARCHAR2,
91   X_ENABLED_FLAG in VARCHAR2,
92   X_LAST_UPDATE_DATE in VARCHAR2,
93   X_CUSTOM_MODE in VARCHAR2
94 ) is
95     l_user_id number := 0;
96     l_app_id  number := 0;
97     l_list_id number := 0;
98     l_row_id varchar2(64);
99 
100     f_luby    number;  -- entity owner in file
101     f_ludate  date;    -- entity update date in file
102     db_luby   number;  -- entity owner in db
103     db_ludate date;    -- entity update date in db
104 
105 begin
106 
107   -- Translate owner to file_last_updated_by
108   f_luby := fnd_load_util.owner_id(X_OWNER);
109 
110   -- Translate char last_update_date to date
111   f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
112 
113   select application_id into l_app_id
114   from fnd_application
115   where application_short_name = X_APPLICATION_SHORT_NAME;
116 
117   /* end_date_active can be null */
118   select list_id into l_list_id from alr_distribution_lists
119   where application_id = l_app_id
120   and name = x_name
121   and ((end_date_active  is null)
122     or ((end_date_active is not null)
123     and (end_date_active =
124       to_date(X_END_DATE_ACTIVE,'YYYY/MM/DD HH24:MI:SS'))));
125 
126   select last_updated_by, last_update_date
127   into  db_luby, db_ludate
128   from ALR_DISTRIBUTION_LISTS
129   where application_id = l_app_id
130   and   ((end_date_active  is null)
131     or ((end_date_active is not null)
132     and (end_date_active =
133       to_date(X_END_DATE_ACTIVE,'YYYY/MM/DD HH24:MI:SS'))))
134   and   list_id = l_list_id;
135 
136  if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,db_ludate,
137                                 x_custom_mode)) then
138 
139 
140   ALR_DISTRIBUTION_LISTS_PKG.UPDATE_ROW(
141     X_APPLICATION_ID => l_app_id,
142     X_LIST_ID => l_list_id,
143     X_END_DATE_ACTIVE => to_date(X_END_DATE_ACTIVE,'YYYY/MM/DD HH24:MI:SS'),
144     X_NAME => X_NAME,
145     X_DESCRIPTION => X_DESCRIPTION,
146     X_TO_RECIPIENTS => X_TO_RECIPIENTS,
147     X_CC_RECIPIENTS => X_CC_RECIPIENTS,
148     X_BCC_RECIPIENTS => X_BCC_RECIPIENTS,
149     X_PRINT_RECIPIENTS => X_PRINT_RECIPIENTS,
150     X_PRINTER => X_PRINTER,
151     X_REPLY_TO => X_REPLY_TO,
152     X_ENABLED_FLAG => X_ENABLED_FLAG,
153     X_LAST_UPDATE_DATE => f_ludate,
154     X_LAST_UPDATED_BY => f_luby,
155     X_LAST_UPDATE_LOGIN => 0 );
156 
157  end if;
158 exception
159   when NO_DATA_FOUND then
160 
161   select alr_distribution_lists_s.nextval into l_list_id from dual;
162 
163   ALR_DISTRIBUTION_LISTS_PKG.INSERT_ROW(
164     X_ROWID => l_row_id,
165     X_APPLICATION_ID => l_app_id,
166     X_LIST_ID => l_list_id,
167     X_END_DATE_ACTIVE => to_date(X_END_DATE_ACTIVE,'YYYY/MM/DD HH24:MI:SS'),
168     X_NAME => X_NAME,
169     X_DESCRIPTION => X_DESCRIPTION,
170     X_TO_RECIPIENTS => X_TO_RECIPIENTS,
171     X_CC_RECIPIENTS => X_CC_RECIPIENTS,
172     X_BCC_RECIPIENTS => X_BCC_RECIPIENTS,
173     X_PRINT_RECIPIENTS => X_PRINT_RECIPIENTS,
174     X_PRINTER => X_PRINTER,
175     X_REPLY_TO => X_REPLY_TO,
176     X_ENABLED_FLAG => X_ENABLED_FLAG,
177     X_CREATION_DATE => f_ludate,
178     X_CREATED_BY => f_luby,
179     X_LAST_UPDATE_DATE => f_ludate,
180     X_LAST_UPDATED_BY => f_luby,
181     X_LAST_UPDATE_LOGIN => 0 );
182 
183 end LOAD_ROW;
184 
185 procedure LOCK_ROW (
186   X_APPLICATION_ID in NUMBER,
187   X_LIST_ID in NUMBER,
188   X_END_DATE_ACTIVE in DATE,
189   X_NAME in VARCHAR2,
190   X_DESCRIPTION in VARCHAR2,
191   X_TO_RECIPIENTS in VARCHAR2,
192   X_CC_RECIPIENTS in VARCHAR2,
193   X_BCC_RECIPIENTS in VARCHAR2,
194   X_PRINT_RECIPIENTS in VARCHAR2,
195   X_PRINTER in VARCHAR2,
196   X_REPLY_TO in VARCHAR2,
197   X_ENABLED_FLAG in VARCHAR2
198 ) is
199   cursor c1 is select
200       NAME,
201       DESCRIPTION,
202       TO_RECIPIENTS,
203       CC_RECIPIENTS,
204       BCC_RECIPIENTS,
205       PRINT_RECIPIENTS,
206       PRINTER,
207       REPLY_TO,
208       ENABLED_FLAG,
209       APPLICATION_ID,
210       LIST_ID,
211       END_DATE_ACTIVE
212     from ALR_DISTRIBUTION_LISTS
213     where APPLICATION_ID = X_APPLICATION_ID
214     and LIST_ID = X_LIST_ID
215     and END_DATE_ACTIVE = X_END_DATE_ACTIVE
216     for update of APPLICATION_ID nowait;
217 begin
218   for recinfo in c1 loop
219       if (    (recinfo.APPLICATION_ID = X_APPLICATION_ID)
220           AND (recinfo.LIST_ID = X_LIST_ID)
221           AND ((recinfo.END_DATE_ACTIVE = X_END_DATE_ACTIVE)
222                OR ((recinfo.END_DATE_ACTIVE is null)
223                AND (X_END_DATE_ACTIVE is null)))
224           AND (recinfo.NAME = X_NAME)
225           AND ((recinfo.DESCRIPTION = X_DESCRIPTION)
226                OR ((recinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
227           AND ((recinfo.TO_RECIPIENTS = X_TO_RECIPIENTS)
228                OR ((recinfo.TO_RECIPIENTS is null) AND (X_TO_RECIPIENTS is null)))
229           AND ((recinfo.CC_RECIPIENTS = X_CC_RECIPIENTS)
230                OR ((recinfo.CC_RECIPIENTS is null) AND (X_CC_RECIPIENTS is null)))
231           AND ((recinfo.BCC_RECIPIENTS = X_BCC_RECIPIENTS)
232                OR ((recinfo.BCC_RECIPIENTS is null) AND (X_BCC_RECIPIENTS is null)))
233           AND ((recinfo.PRINT_RECIPIENTS = X_PRINT_RECIPIENTS)
234                OR ((recinfo.PRINT_RECIPIENTS is null) AND (X_PRINT_RECIPIENTS is null)))
235           AND ((recinfo.PRINTER = X_PRINTER)
236                OR ((recinfo.PRINTER is null) AND (X_PRINTER is null)))
237           AND ((recinfo.REPLY_TO = X_REPLY_TO)
238                OR ((recinfo.REPLY_TO is null) AND (X_REPLY_TO is null)))
239           AND ((recinfo.ENABLED_FLAG = X_ENABLED_FLAG)
240                OR ((recinfo.ENABLED_FLAG is null) AND (X_ENABLED_FLAG is null)))
241       ) then
242         null;
243       else
244         fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
245         app_exception.raise_exception;
246       end if;
247   end loop;
248   return;
249 end LOCK_ROW;
250 
251 procedure UPDATE_ROW (
252   X_APPLICATION_ID in NUMBER,
253   X_LIST_ID in NUMBER,
254   X_END_DATE_ACTIVE in DATE,
255   X_NAME in VARCHAR2,
256   X_DESCRIPTION in VARCHAR2,
257   X_TO_RECIPIENTS in VARCHAR2,
258   X_CC_RECIPIENTS in VARCHAR2,
259   X_BCC_RECIPIENTS in VARCHAR2,
260   X_PRINT_RECIPIENTS in VARCHAR2,
261   X_PRINTER in VARCHAR2,
262   X_REPLY_TO in VARCHAR2,
263   X_ENABLED_FLAG in VARCHAR2,
264   X_LAST_UPDATE_DATE in DATE,
265   X_LAST_UPDATED_BY in NUMBER,
266   X_LAST_UPDATE_LOGIN in NUMBER
267 ) is
268 begin
269   if (X_END_DATE_ACTIVE is null) then
270   update ALR_DISTRIBUTION_LISTS set
271     NAME = X_NAME,
272     DESCRIPTION = X_DESCRIPTION,
273     TO_RECIPIENTS = X_TO_RECIPIENTS,
274     CC_RECIPIENTS = X_CC_RECIPIENTS,
275     BCC_RECIPIENTS = X_BCC_RECIPIENTS,
276     PRINT_RECIPIENTS = X_PRINT_RECIPIENTS,
277     PRINTER = X_PRINTER,
278     REPLY_TO = X_REPLY_TO,
279     ENABLED_FLAG = X_ENABLED_FLAG,
280     APPLICATION_ID = X_APPLICATION_ID,
281     LIST_ID = X_LIST_ID,
282     END_DATE_ACTIVE = X_END_DATE_ACTIVE,
283     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
284     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
285     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
286   where APPLICATION_ID = X_APPLICATION_ID
287   and LIST_ID = X_LIST_ID
288   and END_DATE_ACTIVE is null;
289   else
290   update ALR_DISTRIBUTION_LISTS set
291     NAME = X_NAME,
292     DESCRIPTION = X_DESCRIPTION,
293     TO_RECIPIENTS = X_TO_RECIPIENTS,
294     CC_RECIPIENTS = X_CC_RECIPIENTS,
295     BCC_RECIPIENTS = X_BCC_RECIPIENTS,
296     PRINT_RECIPIENTS = X_PRINT_RECIPIENTS,
297     PRINTER = X_PRINTER,
298     REPLY_TO = X_REPLY_TO,
299     ENABLED_FLAG = X_ENABLED_FLAG,
300     APPLICATION_ID = X_APPLICATION_ID,
301     LIST_ID = X_LIST_ID,
302     END_DATE_ACTIVE = X_END_DATE_ACTIVE,
303     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
304     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
305     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
306   where APPLICATION_ID = X_APPLICATION_ID
307   and LIST_ID = X_LIST_ID
308   and END_DATE_ACTIVE = X_END_DATE_ACTIVE;
309   end if;
310 
311   if (sql%notfound) then
312     raise no_data_found;
313   end if;
314 end UPDATE_ROW;
315 
316 procedure DELETE_ROW (
317   X_APPLICATION_ID in NUMBER,
318   X_LIST_ID in NUMBER,
319   X_END_DATE_ACTIVE in DATE
320 ) is
321 begin
322   delete from ALR_DISTRIBUTION_LISTS
323   where APPLICATION_ID = X_APPLICATION_ID
324   and LIST_ID = X_LIST_ID
325   and END_DATE_ACTIVE = X_END_DATE_ACTIVE;
326 
327   if (sql%notfound) then
328     raise no_data_found;
329   end if;
330 
331 end DELETE_ROW;
332 
333 
334 end ALR_DISTRIBUTION_LISTS_PKG;