[Home] [Help]
PACKAGE BODY: APPS.ALR_ACTION_OUTPUTS_PKG
Source
1 package body ALR_ACTION_OUTPUTS_PKG as
2 /* $Header: ALRAOUTB.pls 120.4.12010000.1 2008/07/27 06:58:25 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_ID in NUMBER,
8 X_NAME in VARCHAR2,
9 X_CRITICAL_FLAG in VARCHAR2,
10 X_END_DATE_ACTIVE in DATE,
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_OUTPUTS
18 where APPLICATION_ID = X_APPLICATION_ID
19 and ALERT_ID = X_ALERT_ID
20 and ACTION_ID = X_ACTION_ID
21 and NAME = X_NAME
22 ;
23 begin
24 insert into ALR_ACTION_OUTPUTS (
25 APPLICATION_ID,
26 ACTION_ID,
27 ALERT_ID,
28 NAME,
29 CRITICAL_FLAG,
30 END_DATE_ACTIVE,
31 LAST_UPDATE_DATE,
32 LAST_UPDATED_BY,
33 CREATION_DATE,
34 CREATED_BY,
35 LAST_UPDATE_LOGIN
36 ) values (
37 X_APPLICATION_ID,
38 X_ACTION_ID,
39 X_ALERT_ID,
40 X_NAME,
41 X_CRITICAL_FLAG,
42 X_END_DATE_ACTIVE,
43 X_LAST_UPDATE_DATE,
44 X_LAST_UPDATED_BY,
45 X_CREATION_DATE,
46 X_CREATED_BY,
47 X_LAST_UPDATE_LOGIN);
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_ACTION_NAME in VARCHAR2,
63 X_ACTION_END_DATE_ACTIVE in VARCHAR2,
64 X_ACTION_OUT_NAME in VARCHAR2,
65 X_OWNER in VARCHAR2,
66 X_CRITICAL_FLAG in VARCHAR2,
67 X_END_DATE_ACTIVE in VARCHAR2,
68 X_LAST_UPDATE_DATE in VARCHAR2,
69 X_CUSTOM_MODE in VARCHAR2
70 ) is
71 l_user_id number := 0;
72 l_app_id number := 0;
73 l_alert_id number := 0;
74 l_action_id number := 0;
75 l_row_id varchar2(64);
76
77 f_luby number; -- entity owner in file
78 f_ludate date; -- entity update date in file
79 db_luby number; -- entity owner in db
80 db_ludate date; -- entity update date in db
81
82
83 begin
84
85 -- DBMS_SESSION.SET_SQL_TRACE(FALSE);
86 -- DBMS_SESSION.SET_SQL_TRACE(TRUE);
87
88 -- Translate owner to file_last_updated_by
89 f_luby := fnd_load_util.owner_id(X_OWNER);
90
91 -- Translate char last_update_date to date
92 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
93
94 select application_id into l_app_id
95 from fnd_application
96 where application_short_name = X_APPLICATION_SHORT_NAME;
97
98 select alert_id into l_alert_id
99 from alr_alerts
100 where application_id = l_app_id
101 and alert_name = X_ALERT_NAME;
102
103 select action_id into l_action_id
104 from alr_actions
105 where application_id = l_app_id
106 and alert_id = l_alert_id
107 and name = X_ACTION_NAME
108 and (((END_DATE_ACTIVE is null)
109 and (X_ACTION_END_DATE_ACTIVE is null))
110 or ((END_DATE_ACTIVE is not null)
111 and (END_DATE_ACTIVE =
112 to_date(X_ACTION_END_DATE_ACTIVE,'YYYY/MM/DD HH24:MI:SS'))));
113
114 select last_updated_by, last_update_date
115 into db_luby, db_ludate
116 from ALR_ACTION_OUTPUTS
117 where application_id = l_app_id
118 and alert_id = l_alert_id
119 and action_id = l_action_id
120 and name = X_ACTION_OUT_NAME;
121
122 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,db_ludate,
123 x_custom_mode)) then
124
125
126 ALR_ACTION_OUTPUTS_PKG.UPDATE_ROW(
127 X_APPLICATION_ID => l_app_id,
128 X_ALERT_ID => l_alert_id,
129 X_ACTION_ID => l_action_id,
130 X_NAME => X_ACTION_OUT_NAME,
131 X_CRITICAL_FLAG => X_CRITICAL_FLAG,
132 X_END_DATE_ACTIVE =>
133 to_date(X_END_DATE_ACTIVE,'YYYY/MM/DD HH24:MI:SS'),
134 X_LAST_UPDATE_DATE => f_ludate,
135 X_LAST_UPDATED_BY => f_luby,
136 X_LAST_UPDATE_LOGIN => 0 );
137
138 end if;
139
140 exception
141
142 when NO_DATA_FOUND then
143
144 ALR_ACTION_OUTPUTS_PKG.INSERT_ROW(
145 X_ROWID => l_row_id,
146 X_APPLICATION_ID => l_app_id,
147 X_ALERT_ID => l_alert_id,
148 X_ACTION_ID => l_action_id,
149 X_NAME => X_ACTION_OUT_NAME,
150 X_CRITICAL_FLAG => X_CRITICAL_FLAG,
151 X_END_DATE_ACTIVE =>
152 to_date(X_END_DATE_ACTIVE,'YYYY/MM/DD HH24:MI:SS'),
153 X_CREATION_DATE => f_ludate,
154 X_CREATED_BY => f_luby,
155 X_LAST_UPDATE_DATE => f_ludate,
156 X_LAST_UPDATED_BY => f_luby,
157 X_LAST_UPDATE_LOGIN => 0 );
158
159 end LOAD_ROW;
160
161 procedure LOCK_ROW (
162 X_APPLICATION_ID in NUMBER,
163 X_ALERT_ID in NUMBER,
164 X_ACTION_ID in NUMBER,
165 X_NAME in VARCHAR2,
166 X_CRITICAL_FLAG in VARCHAR2,
167 X_END_DATE_ACTIVE in DATE
168 ) is
169 cursor c1 is select
170 CRITICAL_FLAG,
171 END_DATE_ACTIVE,
172 APPLICATION_ID,
173 ALERT_ID,
174 ACTION_ID,
175 NAME
176 from ALR_ACTION_OUTPUTS
177 where APPLICATION_ID = X_APPLICATION_ID
178 and ALERT_ID = X_ALERT_ID
179 and ACTION_ID = X_ACTION_ID
180 and NAME = X_NAME
181 for update of APPLICATION_ID nowait;
182 begin
183 for recinfo in c1 loop
184 if ( (recinfo.APPLICATION_ID = X_APPLICATION_ID)
185 AND (recinfo.ALERT_ID = X_ALERT_ID)
186 AND (recinfo.ACTION_ID = X_ACTION_ID)
187 AND (recinfo.NAME = X_NAME)
188 AND (recinfo.CRITICAL_FLAG = X_CRITICAL_FLAG)
189 AND ((recinfo.END_DATE_ACTIVE = X_END_DATE_ACTIVE)
190 OR ((recinfo.END_DATE_ACTIVE is null) AND (X_END_DATE_ACTIVE is null)))
191 ) then
192 null;
193 else
194 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
195 app_exception.raise_exception;
196 end if;
197 end loop;
198 return;
199 end LOCK_ROW;
200
201 procedure UPDATE_ROW (
202 X_APPLICATION_ID in NUMBER,
203 X_ALERT_ID in NUMBER,
204 X_ACTION_ID in NUMBER,
205 X_NAME in VARCHAR2,
206 X_CRITICAL_FLAG in VARCHAR2,
207 X_END_DATE_ACTIVE in DATE,
208 X_LAST_UPDATE_DATE in DATE,
209 X_LAST_UPDATED_BY in NUMBER,
210 X_LAST_UPDATE_LOGIN in NUMBER
211 ) is
212 begin
213 update ALR_ACTION_OUTPUTS set
214 CRITICAL_FLAG = X_CRITICAL_FLAG,
215 END_DATE_ACTIVE = X_END_DATE_ACTIVE,
216 APPLICATION_ID = X_APPLICATION_ID,
217 ALERT_ID = X_ALERT_ID,
218 ACTION_ID = X_ACTION_ID,
219 NAME = X_NAME,
220 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
221 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
222 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
223 where APPLICATION_ID = X_APPLICATION_ID
224 and ALERT_ID = X_ALERT_ID
225 and ACTION_ID = X_ACTION_ID
226 and NAME = X_NAME;
227
228 if (sql%notfound) then
229 raise no_data_found;
230 end if;
231 end UPDATE_ROW;
232
233 procedure DELETE_ROW (
234 X_APPLICATION_ID in NUMBER,
235 X_ALERT_ID in NUMBER,
236 X_ACTION_ID in NUMBER,
237 X_NAME in VARCHAR2
238 ) is
239 begin
240 delete from ALR_ACTION_OUTPUTS
241 where APPLICATION_ID = X_APPLICATION_ID
242 and ALERT_ID = X_ALERT_ID
243 and ACTION_ID = X_ACTION_ID
244 and NAME = X_NAME;
245
246 if (sql%notfound) then
247 raise no_data_found;
248 end if;
249
250 end DELETE_ROW;
251
252
253 end ALR_ACTION_OUTPUTS_PKG;