[Home] [Help]
PACKAGE BODY: APPS.ALR_ACTION_SET_OUTPUTS_PKG
Source
1 package body ALR_ACTION_SET_OUTPUTS_PKG as
2 /* $Header: ALRASOTB.pls 120.4.12010000.1 2008/07/27 06:58:31 appldev ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_APPLICATION_ID in NUMBER,
6 X_ALERT_ID in NUMBER,
7 X_ACTION_SET_ID in NUMBER,
8 X_NAME in VARCHAR2,
9 X_SEQUENCE in NUMBER,
10 X_SUPPRESS_FLAG in VARCHAR2,
11 X_CREATION_DATE in DATE,
12 X_CREATED_BY in NUMBER,
13 X_LAST_UPDATE_DATE in DATE,
14 X_LAST_UPDATED_BY in NUMBER,
15 X_LAST_UPDATE_LOGIN in NUMBER
16 ) is
17 cursor C is select ROWID from ALR_ACTION_SET_OUTPUTS
18 where APPLICATION_ID = X_APPLICATION_ID
19 and ALERT_ID = X_ALERT_ID
20 and ACTION_SET_ID = X_ACTION_SET_ID
21 and NAME = X_NAME
22 ;
23 begin
24 insert into ALR_ACTION_SET_OUTPUTS (
25 APPLICATION_ID,
26 ACTION_SET_ID,
27 NAME,
28 ALERT_ID,
29 SEQUENCE,
30 LAST_UPDATE_DATE,
31 LAST_UPDATED_BY,
32 CREATION_DATE,
33 CREATED_BY,
34 LAST_UPDATE_LOGIN,
35 SUPPRESS_FLAG
36 ) values (
37 X_APPLICATION_ID,
38 X_ACTION_SET_ID,
39 X_NAME,
40 X_ALERT_ID,
41 X_SEQUENCE,
42 X_LAST_UPDATE_DATE,
43 X_LAST_UPDATED_BY,
44 X_CREATION_DATE,
45 X_CREATED_BY,
46 X_LAST_UPDATE_LOGIN,
47 X_SUPPRESS_FLAG);
48
49 open c;
50 fetch c into X_ROWID;
51 if (c%notfound) then
52 close c;
53 raise no_data_found;
54 end if;
55 close c;
56
57 end INSERT_ROW;
58
59 procedure LOAD_ROW (
60 X_APPLICATION_SHORT_NAME in VARCHAR2,
61 X_ALERT_NAME in VARCHAR2,
62 X_NAME in VARCHAR2,
63 X_ACTION_SET_OUTPUT_NAME in VARCHAR2,
64 X_OWNER in VARCHAR2,
65 X_SEQUENCE in VARCHAR2,
66 X_SUPPRESS_FLAG in VARCHAR2,
67 X_LAST_UPDATE_DATE in VARCHAR2,
68 X_CUSTOM_MODE in VARCHAR2
69 ) is
70 l_user_id number := 0;
71 l_app_id number := 0;
72 l_alert_id number := 0;
73 l_action_set_id number := 0;
74 l_row_id varchar2(64);
75
76 f_luby number; -- entity owner in file
77 f_ludate date; -- entity update date in file
78 db_luby number; -- entity owner in db
79 db_ludate date; -- entity update date in db
80
81 begin
82
83 -- Translate owner to file_last_updated_by
84 f_luby := fnd_load_util.owner_id(X_OWNER);
85
86 -- Translate char last_update_date to date
87 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
88
89 select application_id into l_app_id
90 from fnd_application
91 where application_short_name = X_APPLICATION_SHORT_NAME;
92
93 select alert_id into l_alert_id
94 from alr_alerts
95 where application_id = l_app_id
96 and alert_name = X_ALERT_NAME;
97
98 select action_set_id into l_action_set_id
99 from alr_action_sets
100 where application_id = l_app_id
101 and alert_id = l_alert_id
102 and name = X_NAME;
103
104 select last_updated_by, last_update_date
105 into db_luby, db_ludate
106 from ALR_ACTION_SET_OUTPUTS
107 where application_id = l_app_id
108 and alert_id = l_alert_id
109 and action_set_id = l_action_set_id
110 and name = X_ACTION_SET_OUTPUT_NAME;
111
112 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,db_ludate,
113 x_custom_mode)) then
114
115 ALR_ACTION_SET_OUTPUTS_PKG.UPDATE_ROW(
116 X_APPLICATION_ID => l_app_id,
117 X_ALERT_ID => l_alert_id,
118 X_ACTION_SET_ID => l_action_set_id,
119 X_NAME => X_ACTION_SET_OUTPUT_NAME,
120 X_SEQUENCE => to_number(X_SEQUENCE),
121 X_SUPPRESS_FLAG => X_SUPPRESS_FLAG,
122 X_LAST_UPDATE_DATE => f_ludate,
123 X_LAST_UPDATED_BY => f_luby,
124 X_LAST_UPDATE_LOGIN => 0 );
125
126 end if;
127
128 exception
129
130 when NO_DATA_FOUND then
131
132 ALR_ACTION_SET_OUTPUTS_PKG.INSERT_ROW(
133 X_ROWID => l_row_id,
134 X_APPLICATION_ID => l_app_id,
135 X_ALERT_ID => l_alert_id,
136 X_ACTION_SET_ID => l_action_set_id,
137 X_NAME => X_ACTION_SET_OUTPUT_NAME,
138 X_SEQUENCE => to_number(X_SEQUENCE),
139 X_SUPPRESS_FLAG => X_SUPPRESS_FLAG,
140 X_CREATION_DATE => f_ludate,
141 X_CREATED_BY => f_luby,
142 X_LAST_UPDATE_DATE => f_ludate,
143 X_LAST_UPDATED_BY => f_luby,
144 X_LAST_UPDATE_LOGIN => 0 );
145
146 end LOAD_ROW;
147
148 procedure LOCK_ROW (
149 X_APPLICATION_ID in NUMBER,
150 X_ALERT_ID in NUMBER,
151 X_ACTION_SET_ID in NUMBER,
152 X_NAME in VARCHAR2,
153 X_SEQUENCE in NUMBER,
154 X_SUPPRESS_FLAG in VARCHAR2
155 ) is
156 cursor c1 is select
157 SEQUENCE,
158 SUPPRESS_FLAG,
159 APPLICATION_ID,
160 ALERT_ID,
161 ACTION_SET_ID,
162 NAME
163 from ALR_ACTION_SET_OUTPUTS
164 where APPLICATION_ID = X_APPLICATION_ID
165 and ALERT_ID = X_ALERT_ID
166 and ACTION_SET_ID = X_ACTION_SET_ID
167 and NAME = X_NAME
168 for update of APPLICATION_ID nowait;
169 begin
170 for recinfo in c1 loop
171 if ( (recinfo.APPLICATION_ID = X_APPLICATION_ID)
172 AND (recinfo.ALERT_ID = X_ALERT_ID)
173 AND (recinfo.ACTION_SET_ID = X_ACTION_SET_ID)
174 AND (recinfo.NAME = X_NAME)
175 AND (recinfo.SEQUENCE = X_SEQUENCE)
176 AND ((recinfo.SUPPRESS_FLAG = X_SUPPRESS_FLAG)
177 OR ((recinfo.SUPPRESS_FLAG is null) AND (X_SUPPRESS_FLAG is null)))
178 ) then
179 null;
180 else
181 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
182 app_exception.raise_exception;
183 end if;
184 end loop;
185 return;
186 end LOCK_ROW;
187
188 procedure UPDATE_ROW (
189 X_APPLICATION_ID in NUMBER,
190 X_ALERT_ID in NUMBER,
191 X_ACTION_SET_ID in NUMBER,
192 X_NAME in VARCHAR2,
193 X_SEQUENCE in NUMBER,
194 X_SUPPRESS_FLAG in VARCHAR2,
195 X_LAST_UPDATE_DATE in DATE,
196 X_LAST_UPDATED_BY in NUMBER,
197 X_LAST_UPDATE_LOGIN in NUMBER
198 ) is
199 begin
200 update ALR_ACTION_SET_OUTPUTS set
201 SEQUENCE = X_SEQUENCE,
202 SUPPRESS_FLAG = X_SUPPRESS_FLAG,
203 APPLICATION_ID = X_APPLICATION_ID,
204 ALERT_ID = X_ALERT_ID,
205 ACTION_SET_ID = X_ACTION_SET_ID,
206 NAME = X_NAME,
207 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
208 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
209 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
210 where APPLICATION_ID = X_APPLICATION_ID
211 and ALERT_ID = X_ALERT_ID
212 and ACTION_SET_ID = X_ACTION_SET_ID
213 and NAME = X_NAME;
214
215 if (sql%notfound) then
216 raise no_data_found;
217 end if;
218 end UPDATE_ROW;
219
220 procedure DELETE_ROW (
221 X_APPLICATION_ID in NUMBER,
222 X_ALERT_ID in NUMBER,
223 X_ACTION_SET_ID in NUMBER,
224 X_NAME in VARCHAR2
225 ) is
226 begin
227 delete from ALR_ACTION_SET_OUTPUTS
228 where APPLICATION_ID = X_APPLICATION_ID
229 and ALERT_ID = X_ALERT_ID
230 and ACTION_SET_ID = X_ACTION_SET_ID
231 and NAME = X_NAME;
232
233 if (sql%notfound) then
234 raise no_data_found;
235 end if;
236
237 end DELETE_ROW;
238
239
240 end ALR_ACTION_SET_OUTPUTS_PKG;