[Home] [Help]
PACKAGE BODY: APPS.FEM_MRGN_AGRMNTS_PKG
Source
1 package body FEM_MRGN_AGRMNTS_PKG as
2 /* $Header: fem_mrgagrmt_pkb.plb 120.0 2005/06/06 20:35:56 appldev noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_MARGIN_AGREEMENT_CODE in VARCHAR2,
6 X_ENABLED_FLAG in VARCHAR2,
7 X_PERSONAL_FLAG in VARCHAR2,
8 X_READ_ONLY_FLAG in VARCHAR2,
9 X_OBJECT_VERSION_NUMBER in NUMBER,
10 X_MARGIN_AGREEMENT_NAME in VARCHAR2,
11 X_DESCRIPTION in VARCHAR2,
12 X_CREATION_DATE in DATE,
13 X_CREATED_BY in NUMBER,
14 X_LAST_UPDATE_DATE in DATE,
15 X_LAST_UPDATED_BY in NUMBER,
16 X_LAST_UPDATE_LOGIN in NUMBER
17 ) is
18 cursor C is select ROWID from FEM_MRGN_AGRMNTS_B
19 where MARGIN_AGREEMENT_CODE = X_MARGIN_AGREEMENT_CODE
20 ;
21 begin
22 insert into FEM_MRGN_AGRMNTS_B (
23 MARGIN_AGREEMENT_CODE,
24 ENABLED_FLAG,
25 PERSONAL_FLAG,
26 READ_ONLY_FLAG,
27 OBJECT_VERSION_NUMBER,
28 CREATION_DATE,
29 CREATED_BY,
30 LAST_UPDATE_DATE,
31 LAST_UPDATED_BY,
32 LAST_UPDATE_LOGIN
33 ) values (
34 X_MARGIN_AGREEMENT_CODE,
35 X_ENABLED_FLAG,
36 X_PERSONAL_FLAG,
37 X_READ_ONLY_FLAG,
38 X_OBJECT_VERSION_NUMBER,
39 X_CREATION_DATE,
40 X_CREATED_BY,
41 X_LAST_UPDATE_DATE,
42 X_LAST_UPDATED_BY,
43 X_LAST_UPDATE_LOGIN
44 );
45
46 insert into FEM_MRGN_AGRMNTS_TL (
47 MARGIN_AGREEMENT_CODE,
48 MARGIN_AGREEMENT_NAME,
49 DESCRIPTION,
50 CREATION_DATE,
51 CREATED_BY,
52 LAST_UPDATED_BY,
53 LAST_UPDATE_DATE,
54 LAST_UPDATE_LOGIN,
55 LANGUAGE,
56 SOURCE_LANG
57 ) select
58 X_MARGIN_AGREEMENT_CODE,
59 X_MARGIN_AGREEMENT_NAME,
60 X_DESCRIPTION,
61 X_CREATION_DATE,
62 X_CREATED_BY,
63 X_LAST_UPDATED_BY,
64 X_LAST_UPDATE_DATE,
65 X_LAST_UPDATE_LOGIN,
66 L.LANGUAGE_CODE,
67 userenv('LANG')
68 from FND_LANGUAGES L
69 where L.INSTALLED_FLAG in ('I', 'B')
70 and not exists
71 (select NULL
72 from FEM_MRGN_AGRMNTS_TL T
73 where T.MARGIN_AGREEMENT_CODE = X_MARGIN_AGREEMENT_CODE
74 and T.LANGUAGE = L.LANGUAGE_CODE);
75
76 open c;
77 fetch c into X_ROWID;
78 if (c%notfound) then
79 close c;
80 raise no_data_found;
81 end if;
82 close c;
83
84 end INSERT_ROW;
85
86 procedure LOCK_ROW (
87 X_MARGIN_AGREEMENT_CODE in VARCHAR2,
88 X_ENABLED_FLAG in VARCHAR2,
89 X_PERSONAL_FLAG in VARCHAR2,
90 X_READ_ONLY_FLAG in VARCHAR2,
91 X_OBJECT_VERSION_NUMBER in NUMBER,
92 X_MARGIN_AGREEMENT_NAME in VARCHAR2,
93 X_DESCRIPTION in VARCHAR2
94 ) is
95 cursor c is select
96 ENABLED_FLAG,
97 PERSONAL_FLAG,
98 READ_ONLY_FLAG,
99 OBJECT_VERSION_NUMBER
100 from FEM_MRGN_AGRMNTS_B
101 where MARGIN_AGREEMENT_CODE = X_MARGIN_AGREEMENT_CODE
102 for update of MARGIN_AGREEMENT_CODE nowait;
103 recinfo c%rowtype;
104
105 cursor c1 is select
106 MARGIN_AGREEMENT_NAME,
107 DESCRIPTION,
108 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
109 from FEM_MRGN_AGRMNTS_TL
110 where MARGIN_AGREEMENT_CODE = X_MARGIN_AGREEMENT_CODE
111 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
112 for update of MARGIN_AGREEMENT_CODE nowait;
113 begin
114 open c;
115 fetch c into recinfo;
116 if (c%notfound) then
117 close c;
118 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
119 app_exception.raise_exception;
120 end if;
121 close c;
122 if ( (recinfo.ENABLED_FLAG = X_ENABLED_FLAG)
123 AND (recinfo.PERSONAL_FLAG = X_PERSONAL_FLAG)
124 AND (recinfo.READ_ONLY_FLAG = X_READ_ONLY_FLAG)
125 AND (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
126 ) then
127 null;
128 else
129 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
130 app_exception.raise_exception;
131 end if;
132
133 for tlinfo in c1 loop
134 if (tlinfo.BASELANG = 'Y') then
135 if ( (tlinfo.MARGIN_AGREEMENT_NAME = X_MARGIN_AGREEMENT_NAME)
136 AND (tlinfo.DESCRIPTION = X_DESCRIPTION)
137 ) then
138 null;
139 else
140 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
141 app_exception.raise_exception;
142 end if;
143 end if;
144 end loop;
145 return;
146 end LOCK_ROW;
147
148 procedure UPDATE_ROW (
149 X_MARGIN_AGREEMENT_CODE in VARCHAR2,
150 X_ENABLED_FLAG in VARCHAR2,
151 X_PERSONAL_FLAG in VARCHAR2,
152 X_READ_ONLY_FLAG in VARCHAR2,
153 X_OBJECT_VERSION_NUMBER in NUMBER,
154 X_MARGIN_AGREEMENT_NAME in VARCHAR2,
155 X_DESCRIPTION in VARCHAR2,
156 X_LAST_UPDATE_DATE in DATE,
157 X_LAST_UPDATED_BY in NUMBER,
158 X_LAST_UPDATE_LOGIN in NUMBER
159 ) is
160 begin
161 update FEM_MRGN_AGRMNTS_B set
162 ENABLED_FLAG = X_ENABLED_FLAG,
163 PERSONAL_FLAG = X_PERSONAL_FLAG,
164 READ_ONLY_FLAG = X_READ_ONLY_FLAG,
165 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
166 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
167 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
168 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
169 where MARGIN_AGREEMENT_CODE = X_MARGIN_AGREEMENT_CODE;
170
171 if (sql%notfound) then
172 raise no_data_found;
173 end if;
174
175 update FEM_MRGN_AGRMNTS_TL set
176 MARGIN_AGREEMENT_NAME = X_MARGIN_AGREEMENT_NAME,
177 DESCRIPTION = X_DESCRIPTION,
178 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
179 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
180 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
181 SOURCE_LANG = userenv('LANG')
182 where MARGIN_AGREEMENT_CODE = X_MARGIN_AGREEMENT_CODE
183 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
184
185 if (sql%notfound) then
186 raise no_data_found;
187 end if;
188 end UPDATE_ROW;
189
190 procedure DELETE_ROW (
191 X_MARGIN_AGREEMENT_CODE in VARCHAR2
192 ) is
193 begin
194 delete from FEM_MRGN_AGRMNTS_TL
195 where MARGIN_AGREEMENT_CODE = X_MARGIN_AGREEMENT_CODE;
196
197 if (sql%notfound) then
198 raise no_data_found;
199 end if;
200
201 delete from FEM_MRGN_AGRMNTS_B
202 where MARGIN_AGREEMENT_CODE = X_MARGIN_AGREEMENT_CODE;
203
204 if (sql%notfound) then
205 raise no_data_found;
206 end if;
207 end DELETE_ROW;
208
209 procedure ADD_LANGUAGE
210 is
211 begin
212 delete from FEM_MRGN_AGRMNTS_TL T
213 where not exists
214 (select NULL
215 from FEM_MRGN_AGRMNTS_B B
216 where B.MARGIN_AGREEMENT_CODE = T.MARGIN_AGREEMENT_CODE
217 );
218
219 update FEM_MRGN_AGRMNTS_TL T set (
220 MARGIN_AGREEMENT_NAME,
221 DESCRIPTION
222 ) = (select
223 B.MARGIN_AGREEMENT_NAME,
224 B.DESCRIPTION
225 from FEM_MRGN_AGRMNTS_TL B
226 where B.MARGIN_AGREEMENT_CODE = T.MARGIN_AGREEMENT_CODE
227 and B.LANGUAGE = T.SOURCE_LANG)
228 where (
229 T.MARGIN_AGREEMENT_CODE,
230 T.LANGUAGE
231 ) in (select
232 SUBT.MARGIN_AGREEMENT_CODE,
233 SUBT.LANGUAGE
234 from FEM_MRGN_AGRMNTS_TL SUBB, FEM_MRGN_AGRMNTS_TL SUBT
235 where SUBB.MARGIN_AGREEMENT_CODE = SUBT.MARGIN_AGREEMENT_CODE
236 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
237 and (SUBB.MARGIN_AGREEMENT_NAME <> SUBT.MARGIN_AGREEMENT_NAME
238 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
239 ));
240
241 insert into FEM_MRGN_AGRMNTS_TL (
242 MARGIN_AGREEMENT_CODE,
243 MARGIN_AGREEMENT_NAME,
244 DESCRIPTION,
245 CREATION_DATE,
246 CREATED_BY,
247 LAST_UPDATED_BY,
248 LAST_UPDATE_DATE,
249 LAST_UPDATE_LOGIN,
250 LANGUAGE,
251 SOURCE_LANG
252 ) select /*+ ORDERED */
253 B.MARGIN_AGREEMENT_CODE,
254 B.MARGIN_AGREEMENT_NAME,
255 B.DESCRIPTION,
256 B.CREATION_DATE,
257 B.CREATED_BY,
258 B.LAST_UPDATED_BY,
259 B.LAST_UPDATE_DATE,
260 B.LAST_UPDATE_LOGIN,
261 L.LANGUAGE_CODE,
262 B.SOURCE_LANG
263 from FEM_MRGN_AGRMNTS_TL B, FND_LANGUAGES L
264 where L.INSTALLED_FLAG in ('I', 'B')
265 and B.LANGUAGE = userenv('LANG')
266 and not exists
267 (select NULL
268 from FEM_MRGN_AGRMNTS_TL T
269 where T.MARGIN_AGREEMENT_CODE = B.MARGIN_AGREEMENT_CODE
270 and T.LANGUAGE = L.LANGUAGE_CODE);
271 end ADD_LANGUAGE;
272 PROCEDURE TRANSLATE_ROW(
273 x_MARGIN_AGREEMENT_CODE in varchar2,
274 x_owner in varchar2,
275 x_last_update_date in varchar2,
276 x_MARGIN_AGREEMENT_NAME in varchar2,
277 x_description in varchar2,
278 x_custom_mode in varchar2) is
279
280 owner_id number;
281 ludate date;
282 row_id varchar2(64);
283 f_luby number; -- entity owner in file
284 f_ludate date; -- entity update date in file
285 db_luby number; -- entity owner in db
286 db_ludate date; -- entity update date in db
287 begin
288
289
290 -- Translate owner to file_last_updated_by
291 f_luby := fnd_load_util.owner_id(x_owner);
292
293 -- Translate char last_update_date to date
294 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
295 begin
296 select LAST_UPDATED_BY, LAST_UPDATE_DATE
297 into db_luby, db_ludate
298 from FEM_MRGN_AGRMNTS_TL
299 where MARGIN_AGREEMENT_CODE = x_MARGIN_AGREEMENT_CODE
300 and LANGUAGE = userenv('LANG');
301
302 -- Test for customization and version
303 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
304 db_ludate, x_custom_mode)) then
305 -- Update translations for this language
306 update FEM_MRGN_AGRMNTS_TL set
307 MARGIN_AGREEMENT_NAME = decode(x_MARGIN_AGREEMENT_NAME,
308 fnd_load_util.null_value, null, -- Real null
309 null, x_MARGIN_AGREEMENT_NAME, -- No change
310 x_MARGIN_AGREEMENT_NAME),
311 DESCRIPTION = nvl(x_description, DESCRIPTION),
312 LAST_UPDATE_DATE = f_ludate,
313 LAST_UPDATED_BY = f_luby,
314 LAST_UPDATE_LOGIN = 0,
315 SOURCE_LANG = userenv('LANG')
316 where userenv('LANG') in (LANGUAGE, SOURCE_LANG)
317 and MARGIN_AGREEMENT_CODE = x_MARGIN_AGREEMENT_CODE;
318 end if;
319 exception
320 when no_data_found then
321 -- Do not insert missing translations, skip this row
322 null;
323 end;
324 end TRANSLATE_ROW;
325
326
327 end FEM_MRGN_AGRMNTS_PKG;