[Home] [Help]
PACKAGE BODY: APPS.AHL_MR_ACTIONS_PKG
Source
1 package body AHL_MR_ACTIONS_PKG as
2 /* $Header: AHLLMRAB.pls 115.4 2002/12/04 19:22:06 rtadikon noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_MR_ACTION_ID in NUMBER,
6 X_OBJECT_VERSION_NUMBER in NUMBER,
7 X_MR_HEADER_ID in NUMBER,
8 X_MR_ACTION_CODE in VARCHAR2,
9 X_PLAN_ID in NUMBER,
10 X_ATTRIBUTE_CATEGORY in VARCHAR2,
11 X_ATTRIBUTE1 in VARCHAR2,
12 X_ATTRIBUTE2 in VARCHAR2,
13 X_ATTRIBUTE3 in VARCHAR2,
14 X_ATTRIBUTE4 in VARCHAR2,
15 X_ATTRIBUTE5 in VARCHAR2,
16 X_ATTRIBUTE6 in VARCHAR2,
17 X_ATTRIBUTE7 in VARCHAR2,
18 X_ATTRIBUTE8 in VARCHAR2,
19 X_ATTRIBUTE9 in VARCHAR2,
20 X_ATTRIBUTE10 in VARCHAR2,
21 X_ATTRIBUTE11 in VARCHAR2,
22 X_ATTRIBUTE12 in VARCHAR2,
23 X_ATTRIBUTE13 in VARCHAR2,
24 X_ATTRIBUTE14 in VARCHAR2,
25 X_ATTRIBUTE15 in VARCHAR2,
26 X_DESCRIPTION in VARCHAR2,
27 X_CREATION_DATE in DATE,
28 X_CREATED_BY in NUMBER,
29 X_LAST_UPDATE_DATE in DATE,
30 X_LAST_UPDATED_BY in NUMBER,
31 X_LAST_UPDATE_LOGIN in NUMBER
32 ) is
33 cursor C is select ROWID from AHL_MR_ACTIONS_B
34 where MR_ACTION_ID = X_MR_ACTION_ID
35 ;
36 begin
37 insert into AHL_MR_ACTIONS_B (
38 MR_ACTION_ID,
39 OBJECT_VERSION_NUMBER,
40 MR_HEADER_ID,
41 MR_ACTION_CODE,
42 PLAN_ID,
43 ATTRIBUTE_CATEGORY,
44 ATTRIBUTE1,
45 ATTRIBUTE2,
46 ATTRIBUTE3,
47 ATTRIBUTE4,
48 ATTRIBUTE5,
49 ATTRIBUTE6,
50 ATTRIBUTE7,
51 ATTRIBUTE8,
52 ATTRIBUTE9,
53 ATTRIBUTE10,
54 ATTRIBUTE11,
55 ATTRIBUTE12,
56 ATTRIBUTE13,
57 ATTRIBUTE14,
58 ATTRIBUTE15,
59 CREATION_DATE,
60 CREATED_BY,
61 LAST_UPDATE_DATE,
62 LAST_UPDATED_BY,
63 LAST_UPDATE_LOGIN
64 ) values (
65 X_MR_ACTION_ID,
66 X_OBJECT_VERSION_NUMBER,
67 X_MR_HEADER_ID,
68 X_MR_ACTION_CODE,
69 X_PLAN_ID,
70 X_ATTRIBUTE_CATEGORY,
71 X_ATTRIBUTE1,
72 X_ATTRIBUTE2,
73 X_ATTRIBUTE3,
74 X_ATTRIBUTE4,
75 X_ATTRIBUTE5,
76 X_ATTRIBUTE6,
77 X_ATTRIBUTE7,
78 X_ATTRIBUTE8,
79 X_ATTRIBUTE9,
80 X_ATTRIBUTE10,
81 X_ATTRIBUTE11,
82 X_ATTRIBUTE12,
83 X_ATTRIBUTE13,
84 X_ATTRIBUTE14,
85 X_ATTRIBUTE15,
86 X_CREATION_DATE,
87 X_CREATED_BY,
88 X_LAST_UPDATE_DATE,
89 X_LAST_UPDATED_BY,
90 X_LAST_UPDATE_LOGIN
91 );
92
93 insert into AHL_MR_ACTIONS_TL (
94 MR_ACTION_ID,
95 LAST_UPDATE_DATE,
96 LAST_UPDATED_BY,
97 CREATION_DATE,
98 CREATED_BY,
99 LAST_UPDATE_LOGIN,
100 DESCRIPTION,
101 LANGUAGE,
102 SOURCE_LANG
103 ) select
104 X_MR_ACTION_ID,
105 X_LAST_UPDATE_DATE,
106 X_LAST_UPDATED_BY,
107 X_CREATION_DATE,
108 X_CREATED_BY,
109 X_LAST_UPDATE_LOGIN,
110 X_DESCRIPTION,
111 L.LANGUAGE_CODE,
112 userenv('LANG')
113 from FND_LANGUAGES L
114 where L.INSTALLED_FLAG in ('I', 'B')
115 and not exists
116 (select NULL
117 from AHL_MR_ACTIONS_TL T
118 where T.MR_ACTION_ID = X_MR_ACTION_ID
119 and T.LANGUAGE = L.LANGUAGE_CODE);
120
121 open c;
122 fetch c into X_ROWID;
123 if (c%notfound) then
124 close c;
125 raise no_data_found;
126 end if;
127 close c;
128
129 end INSERT_ROW;
130
131 procedure UPDATE_ROW (
132 X_MR_ACTION_ID in NUMBER,
133 X_OBJECT_VERSION_NUMBER in NUMBER,
134 X_MR_HEADER_ID in NUMBER,
135 X_MR_ACTION_CODE in VARCHAR2,
136 X_PLAN_ID in NUMBER,
137 X_ATTRIBUTE_CATEGORY in VARCHAR2,
138 X_ATTRIBUTE1 in VARCHAR2,
139 X_ATTRIBUTE2 in VARCHAR2,
140 X_ATTRIBUTE3 in VARCHAR2,
141 X_ATTRIBUTE4 in VARCHAR2,
142 X_ATTRIBUTE5 in VARCHAR2,
143 X_ATTRIBUTE6 in VARCHAR2,
144 X_ATTRIBUTE7 in VARCHAR2,
145 X_ATTRIBUTE8 in VARCHAR2,
146 X_ATTRIBUTE9 in VARCHAR2,
147 X_ATTRIBUTE10 in VARCHAR2,
148 X_ATTRIBUTE11 in VARCHAR2,
149 X_ATTRIBUTE12 in VARCHAR2,
150 X_ATTRIBUTE13 in VARCHAR2,
151 X_ATTRIBUTE14 in VARCHAR2,
152 X_ATTRIBUTE15 in VARCHAR2,
153 X_DESCRIPTION in VARCHAR2,
154 X_LAST_UPDATE_DATE in DATE,
155 X_LAST_UPDATED_BY in NUMBER,
156 X_LAST_UPDATE_LOGIN in NUMBER
157 ) is
158 begin
159 update AHL_MR_ACTIONS_B set
160 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER + 1 ,
161 MR_HEADER_ID = X_MR_HEADER_ID,
162 MR_ACTION_CODE = X_MR_ACTION_CODE,
163 PLAN_ID = X_PLAN_ID,
164 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
165 ATTRIBUTE1 = X_ATTRIBUTE1,
166 ATTRIBUTE2 = X_ATTRIBUTE2,
167 ATTRIBUTE3 = X_ATTRIBUTE3,
168 ATTRIBUTE4 = X_ATTRIBUTE4,
169 ATTRIBUTE5 = X_ATTRIBUTE5,
170 ATTRIBUTE6 = X_ATTRIBUTE6,
171 ATTRIBUTE7 = X_ATTRIBUTE7,
172 ATTRIBUTE8 = X_ATTRIBUTE8,
173 ATTRIBUTE9 = X_ATTRIBUTE9,
174 ATTRIBUTE10 = X_ATTRIBUTE10,
175 ATTRIBUTE11 = X_ATTRIBUTE11,
176 ATTRIBUTE12 = X_ATTRIBUTE12,
177 ATTRIBUTE13 = X_ATTRIBUTE13,
178 ATTRIBUTE14 = X_ATTRIBUTE14,
179 ATTRIBUTE15 = X_ATTRIBUTE15,
180 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
181 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
182 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
183 where MR_ACTION_ID = X_MR_ACTION_ID
184 AND object_version_number=x_object_version_number;
185
186 if (sql%ROWCOUNT=0) then
187 FND_MESSAGE.SET_NAME('AHL','AHL_COM_RECORD_CHANGED');
188 FND_MSG_PUB.ADD;
189 else
190 update AHL_MR_ACTIONS_TL set
191 DESCRIPTION = X_DESCRIPTION,
195 SOURCE_LANG = userenv('LANG')
192 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
193 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
194 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
196 where MR_ACTION_ID = X_MR_ACTION_ID
197 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
198 end if;
199
200 end UPDATE_ROW;
201
202 procedure DELETE_ROW (
203 X_MR_ACTION_ID in NUMBER
204 ) is
205 begin
206 delete from AHL_MR_ACTIONS_TL
207 where MR_ACTION_ID = X_MR_ACTION_ID;
208
209 if (sql%ROWCOUNT=0) then
210 FND_MESSAGE.SET_NAME('AHL','AHL_COM_RECORD_CHANGED');
211 FND_MSG_PUB.ADD;
212 else
213 delete from AHL_MR_ACTIONS_B
214 where MR_ACTION_ID = X_MR_ACTION_ID;
215 if (sql%ROWCOUNT=0) then
216 FND_MESSAGE.SET_NAME('AHL','AHL_COM_RECORD_CHANGED');
217 FND_MSG_PUB.ADD;
218 END IF;
219
220 END IF;
221
222 if (sql%notfound) then
223 raise no_data_found;
224 end if;
225 end DELETE_ROW;
226
227 procedure ADD_LANGUAGE
228 is
229 begin
230 delete from AHL_MR_ACTIONS_TL T
231 where not exists
232 (select NULL
233 from AHL_MR_ACTIONS_B B
234 where B.MR_ACTION_ID = T.MR_ACTION_ID
235 );
236
237 update AHL_MR_ACTIONS_TL T set (
238 DESCRIPTION
239 ) = (select
240 B.DESCRIPTION
241 from AHL_MR_ACTIONS_TL B
242 where B.MR_ACTION_ID = T.MR_ACTION_ID
243 and B.LANGUAGE = T.SOURCE_LANG)
244 where (
245 T.MR_ACTION_ID,
246 T.LANGUAGE
247 ) in (select
248 SUBT.MR_ACTION_ID,
249 SUBT.LANGUAGE
250 from AHL_MR_ACTIONS_TL SUBB, AHL_MR_ACTIONS_TL SUBT
251 where SUBB.MR_ACTION_ID = SUBT.MR_ACTION_ID
252 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
253 and (SUBB.DESCRIPTION <> SUBT.DESCRIPTION
254 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
255 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
256 ));
257
258 insert into AHL_MR_ACTIONS_TL (
259 MR_ACTION_ID,
260 LAST_UPDATE_DATE,
261 LAST_UPDATED_BY,
262 CREATION_DATE,
263 CREATED_BY,
264 LAST_UPDATE_LOGIN,
265 DESCRIPTION,
266 LANGUAGE,
267 SOURCE_LANG
268 ) select
269 B.MR_ACTION_ID,
270 B.LAST_UPDATE_DATE,
271 B.LAST_UPDATED_BY,
272 B.CREATION_DATE,
273 B.CREATED_BY,
274 B.LAST_UPDATE_LOGIN,
275 B.DESCRIPTION,
276 L.LANGUAGE_CODE,
277 B.SOURCE_LANG
278 from AHL_MR_ACTIONS_TL B, FND_LANGUAGES L
279 where L.INSTALLED_FLAG in ('I', 'B')
280 and B.LANGUAGE = userenv('LANG')
281 and not exists
282 (select NULL
283 from AHL_MR_ACTIONS_TL T
284 where T.MR_ACTION_ID = B.MR_ACTION_ID
285 and T.LANGUAGE = L.LANGUAGE_CODE);
286 end ADD_LANGUAGE;
287
288 end AHL_MR_ACTIONS_PKG;