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