[Home] [Help]
PACKAGE BODY: APPS.ECX_TRANSACTIONS_PKG
Source
1 package body ECX_TRANSACTIONS_PKG as
2 /* $Header: ECXTXNB.pls 120.2.12000000.3 2007/07/20 07:41:17 susaha ship $ */
3
4 procedure TRANSLATE_ROW(
5 X_TRANSACTION_TYPE IN VARCHAR2,
6 X_TRANSACTION_SUBTYPE IN VARCHAR2,
7 X_PARTY_TYPE IN VARCHAR2,
8 X_TRANSACTION_DESCRIPTION IN VARCHAR2,
9 X_OWNER IN VARCHAR2,
10 X_CUSTOM_MODE IN VARCHAR2
11 )
12 is
13 l_luby number; -- entity owner in file
14 l_ludate date; -- entity update date in file
15 l_db_luby number; -- entity owner in db
16 l_db_ludate date; -- entity update date in db
17 l_transaction_id number;
18 begin
19 -- Translate owner to file_last_updated_by
20 if (x_owner = 'SEED') then
21 l_luby := 1;
22 else
23 l_luby := 0;
24 end if;
25 -- Translate char last_update_date to date
26 l_ludate := sysdate;
27
28 begin
29 select transaction_id
30 into l_transaction_id
31 from ecx_transactions
32 where transaction_type = X_TRANSACTION_TYPE
33 and transaction_subtype = X_TRANSACTION_SUBTYPE
34 and party_type = X_PARTY_TYPE;
35
36 select last_updated_by, last_update_date
37 into l_db_luby, l_db_ludate
38 from ecx_transactions_tl
39 where transaction_id = l_transaction_id
40 and LANGUAGE = userenv('LANG');
41
42 -- Update record, honoring customization mode.
43 -- Record should be updated only if:
44 -- a. CUSTOM_MODE = FORCE, or
45 -- b. file owner is CUSTOM, db owner is SEED
46 -- c. owners are the same, and file_date > db_date
47 if ((x_custom_mode = 'FORCE') or
48 ((l_luby = 0) and (l_db_luby = 1)) or
49 ((l_luby = l_db_luby) and (l_ludate > l_db_ludate)))
50 then
51 update ECX_TRANSACTIONS_TL set
52 TRANSACTION_DESCRIPTION = nvl(X_TRANSACTION_DESCRIPTION, TRANSACTION_DESCRIPTION),
53 SOURCE_LANG = userenv('LANG'),
54 LAST_UPDATE_DATE = l_ludate,
55 LAST_UPDATED_BY = l_luby,
56 LAST_UPDATE_LOGIN = 0
57 where TRANSACTION_ID = L_TRANSACTION_ID
58 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
59 end if;
60 exception
61 when no_data_found then
62 null;
63 end;
64 end TRANSLATE_ROW;
65
66 procedure LOAD_ROW (
67 X_TRANSACTION_TYPE IN VARCHAR2,
68 X_TRANSACTION_SUBTYPE IN VARCHAR2,
69 X_PARTY_TYPE IN VARCHAR2,
70 X_TRANSACTION_DESCRIPTION IN VARCHAR2,
71 X_ADMIN_USER IN VARCHAR2 DEFAULT NULL,
72 X_OWNER IN VARCHAR2,
73 X_CUSTOM_MODE IN VARCHAR2
74 )
75 is
76 l_row_id varchar2(64);
77 l_ret_code pls_integer := 0;
78 l_errmsg varchar2(2000) := null;
79 l_transaction_id pls_integer := -1;
80 l_luby number; -- entity owner in file
81 l_ludate date; -- entity update date in file
82 l_db_luby number; -- entity owner in db
83 l_db_ludate date; -- entity update date in db
84 begin
85 -- Translate owner to file_last_updated_by
86 if (x_owner = 'SEED') then
87 l_luby := 1;
88 else
89 l_luby := 0;
90 end if;
91
92 -- Translate char last_update_date to date
93 l_ludate := sysdate;
94
95 begin
96 select TRANSACTION_ID,
97 LAST_UPDATED_BY,
98 LAST_UPDATE_DATE
99 into l_transaction_id,
100 l_db_luby,
101 l_db_ludate
102 from ECX_TRANSACTIONS_B
103 where TRANSACTION_TYPE = X_TRANSACTION_TYPE
104 and TRANSACTION_SUBTYPE = X_TRANSACTION_SUBTYPE
105 and PARTY_TYPE = X_PARTY_TYPE;
106
107 -- Update record, honoring customization mode.
108 -- Record should be updated only if:
109 -- a. CUSTOM_MODE = FORCE, or
110 -- b. file owner is CUSTOM, db owner is SEED
111 -- c. owners are the same, and file_date > db_date
112 if ((x_custom_mode = 'FORCE') or
113 ((l_luby = 0) and (l_db_luby = 1)) or
114 ((l_luby = l_db_luby) and (l_ludate > l_db_ludate)))
115 then
116 ecx_transactions_api.update_transaction (
117 X_RETURN_STATUS => l_ret_code,
118 X_MSG => l_errmsg,
119 P_TRANSACTION_ID => l_transaction_id,
120 P_TRANSACTION_TYPE => X_TRANSACTION_TYPE,
121 P_TRANSACTION_SUBTYPE => X_TRANSACTION_SUBTYPE,
122 P_PARTY_TYPE => X_PARTY_TYPE,
123 P_TRANSACTION_DESCRIPTION => X_TRANSACTION_DESCRIPTION,
124 P_OWNER => X_OWNER);
125
126 if (l_ret_code <> ECX_UTIL_API.G_NO_ERROR) then
127 raise_application_error(-20000, l_errmsg);
128 end if;
129 end if;
130 exception
131 when no_data_found then
132 ecx_transactions_api.create_transaction (
133 x_return_status => l_ret_code,
134 x_msg => l_errmsg,
135 x_transaction_id => l_transaction_id,
136 p_transaction_type => X_TRANSACTION_TYPE,
137 p_transaction_subtype => X_TRANSACTION_SUBTYPE,
138 p_transaction_description => X_TRANSACTION_DESCRIPTION,
139 p_admin_user => X_ADMIN_USER,
140 p_party_type => X_PARTY_TYPE,
141 p_owner => X_OWNER);
142
143 if (l_ret_code <> ECX_UTIL_API.G_NO_ERROR) then
144 raise_application_error(-20000, l_errmsg);
145 end if;
146 end;
147
148 end LOAD_ROW;
149
150 procedure INSERT_ROW (
151 X_ROWID in out nocopy VARCHAR2,
152 X_TRANSACTION_ID in NUMBER,
153 X_TRANSACTION_TYPE in VARCHAR2,
154 X_TRANSACTION_SUBTYPE in VARCHAR2,
155 X_PARTY_TYPE in VARCHAR2,
156 X_TRANSACTION_DESCRIPTION in VARCHAR2,
157 X_ADMIN_USER in VARCHAR2 DEFAULT NULL,
158 X_CREATION_DATE in DATE,
159 X_CREATED_BY in NUMBER,
160 X_LAST_UPDATE_DATE in DATE,
161 X_LAST_UPDATED_BY in NUMBER,
162 X_LAST_UPDATE_LOGIN in NUMBER
163 ) is
164 cursor C is select ROWID from ECX_TRANSACTIONS_B
165 where TRANSACTION_ID = X_TRANSACTION_ID
166 ;
167 begin
168 insert into ECX_TRANSACTIONS_B (
169 TRANSACTION_ID,
170 TRANSACTION_TYPE,
171 TRANSACTION_SUBTYPE,
172 PARTY_TYPE,
173 ADMIN_USER,
174 CREATION_DATE,
175 CREATED_BY,
176 LAST_UPDATE_DATE,
177 LAST_UPDATED_BY,
178 LAST_UPDATE_LOGIN
179 ) values (
180 X_TRANSACTION_ID,
181 X_TRANSACTION_TYPE,
182 X_TRANSACTION_SUBTYPE,
183 X_PARTY_TYPE,
184 X_ADMIN_USER,
185 X_CREATION_DATE,
186 X_CREATED_BY,
187 X_LAST_UPDATE_DATE,
188 X_LAST_UPDATED_BY,
189 X_LAST_UPDATE_LOGIN
190 );
191
192 insert into ECX_TRANSACTIONS_TL (
193 TRANSACTION_ID,
194 TRANSACTION_DESCRIPTION,
195 CREATED_BY,
196 CREATION_DATE,
197 LAST_UPDATED_BY,
198 LAST_UPDATE_DATE,
199 LAST_UPDATE_LOGIN,
200 LANGUAGE,
201 SOURCE_LANG
202 ) select
203 X_TRANSACTION_ID,
204 X_TRANSACTION_DESCRIPTION,
205 X_CREATED_BY,
206 X_CREATION_DATE,
207 X_LAST_UPDATED_BY,
208 X_LAST_UPDATE_DATE,
209 X_LAST_UPDATE_LOGIN,
210 L.CODE,
211 userenv('LANG')
212 from WF_LANGUAGES L
213 where L.INSTALLED_FLAG ='Y'
214 and not exists
215 (select NULL
216 from ECX_TRANSACTIONS_TL T
217 where T.TRANSACTION_ID = X_TRANSACTION_ID
218 and T.LANGUAGE = L.CODE);
219
220 open c;
221 fetch c into X_ROWID;
222 if (c%notfound) then
223 close c;
224 raise no_data_found;
225 end if;
226 close c;
227
228 end INSERT_ROW;
229
230 procedure LOCK_ROW (
231 X_TRANSACTION_ID in NUMBER,
232 X_TRANSACTION_TYPE in VARCHAR2,
233 X_TRANSACTION_SUBTYPE in VARCHAR2,
234 X_PARTY_TYPE in VARCHAR2,
235 X_TRANSACTION_DESCRIPTION in VARCHAR2
236 ) is
237 cursor c is select
238 TRANSACTION_TYPE,
239 TRANSACTION_SUBTYPE,
240 PARTY_TYPE
241 from ECX_TRANSACTIONS_B
242 where TRANSACTION_ID = X_TRANSACTION_ID
243 for update of TRANSACTION_ID nowait;
244 recinfo c%rowtype;
245
246 cursor c1 is select
247 TRANSACTION_DESCRIPTION,
248 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
249 from ECX_TRANSACTIONS_TL
250 where TRANSACTION_ID = X_TRANSACTION_ID
251 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
252 for update of TRANSACTION_ID nowait;
253 begin
254 open c;
255 fetch c into recinfo;
256 if (c%notfound) then
257 close c;
258 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
259 app_exception.raise_exception;
260 end if;
261 close c;
262 if ( (recinfo.TRANSACTION_TYPE = X_TRANSACTION_TYPE)
263 AND (recinfo.TRANSACTION_SUBTYPE = X_TRANSACTION_SUBTYPE)
264 AND (recinfo.PARTY_TYPE = X_PARTY_TYPE)
265 ) then
266 null;
267 else
268 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
269 app_exception.raise_exception;
270 end if;
271
272 for tlinfo in c1 loop
273 if (tlinfo.BASELANG = 'Y') then
274 if ( ((tlinfo.TRANSACTION_DESCRIPTION = X_TRANSACTION_DESCRIPTION)
275 OR ((tlinfo.TRANSACTION_DESCRIPTION is null) AND (X_TRANSACTION_DESCRIPTION is null)))
276 ) then
277 null;
278 else
279 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
280 app_exception.raise_exception;
281 end if;
282 end if;
283 end loop;
284 return;
285 end LOCK_ROW;
286
287 procedure UPDATE_ROW (
288 X_TRANSACTION_ID in NUMBER,
289 X_TRANSACTION_TYPE in VARCHAR2,
290 X_TRANSACTION_SUBTYPE in VARCHAR2,
291 X_PARTY_TYPE in VARCHAR2,
292 X_TRANSACTION_DESCRIPTION in VARCHAR2,
293 X_ADMIN_USER in VARCHAR2 DEFAULT NULL,
294 X_LAST_UPDATE_DATE in DATE,
295 X_LAST_UPDATED_BY in NUMBER,
296 X_LAST_UPDATE_LOGIN in NUMBER
297 ) is
298 begin
299 update ECX_TRANSACTIONS_B set
300 TRANSACTION_TYPE = X_TRANSACTION_TYPE,
301 TRANSACTION_SUBTYPE = X_TRANSACTION_SUBTYPE,
302 PARTY_TYPE = X_PARTY_TYPE,
303 ADMIN_USER = X_ADMIN_USER,
304 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
305 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
306 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
307 where TRANSACTION_ID = X_TRANSACTION_ID;
308
309 if (sql%notfound) then
310 raise no_data_found;
311 end if;
312
313 update ECX_TRANSACTIONS_TL set
314 TRANSACTION_DESCRIPTION = X_TRANSACTION_DESCRIPTION,
315 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
316 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
317 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
318 SOURCE_LANG = userenv('LANG')
319 where TRANSACTION_ID = X_TRANSACTION_ID
320 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
321
322 if (sql%notfound) then
323 raise no_data_found;
324 end if;
325 end UPDATE_ROW;
326
327 procedure DELETE_ROW (
328 X_TRANSACTION_ID in NUMBER
329 ) is
330 begin
331 delete from ECX_TRANSACTIONS_TL
332 where TRANSACTION_ID = X_TRANSACTION_ID;
333
334 if (sql%notfound) then
335 raise no_data_found;
336 end if;
337
338 delete from ECX_TRANSACTIONS_B
339 where TRANSACTION_ID = X_TRANSACTION_ID;
340
341 if (sql%notfound) then
342 raise no_data_found;
343 end if;
344 end DELETE_ROW;
345
346 procedure ADD_LANGUAGE
347 is
348 begin
349 delete from ECX_TRANSACTIONS_TL T
350 where not exists
351 (select NULL
352 from ECX_TRANSACTIONS_B B
353 where B.TRANSACTION_ID = T.TRANSACTION_ID
354 );
355
356 update ECX_TRANSACTIONS_TL T set (
357 TRANSACTION_DESCRIPTION
358 ) = (select
359 B.TRANSACTION_DESCRIPTION
360 from ECX_TRANSACTIONS_TL B
361 where B.TRANSACTION_ID = T.TRANSACTION_ID
362 and B.LANGUAGE = T.SOURCE_LANG)
363 where (
364 T.TRANSACTION_ID,
365 T.LANGUAGE
366 ) in (select
367 SUBT.TRANSACTION_ID,
368 SUBT.LANGUAGE
369 from ECX_TRANSACTIONS_TL SUBB, ECX_TRANSACTIONS_TL SUBT
370 where SUBB.TRANSACTION_ID = SUBT.TRANSACTION_ID
371 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
372 and (SUBB.TRANSACTION_DESCRIPTION <> SUBT.TRANSACTION_DESCRIPTION
373 or (SUBB.TRANSACTION_DESCRIPTION is null and SUBT.TRANSACTION_DESCRIPTION is not null)
374 or (SUBB.TRANSACTION_DESCRIPTION is not null and SUBT.TRANSACTION_DESCRIPTION is null)));
375
376 insert into ECX_TRANSACTIONS_TL (
377 TRANSACTION_ID,
378 TRANSACTION_DESCRIPTION,
379 CREATED_BY,
380 CREATION_DATE,
381 LAST_UPDATED_BY,
382 LAST_UPDATE_DATE,
383 LAST_UPDATE_LOGIN,
384 LANGUAGE,
385 SOURCE_LANG
386 ) select
387 B.TRANSACTION_ID,
388 B.TRANSACTION_DESCRIPTION,
389 B.CREATED_BY,
390 B.CREATION_DATE,
391 B.LAST_UPDATED_BY,
392 B.LAST_UPDATE_DATE,
393 B.LAST_UPDATE_LOGIN,
394 L.CODE,
395 B.SOURCE_LANG
396 from ECX_TRANSACTIONS_TL B, WF_LANGUAGES L
397 where L.INSTALLED_FLAG = 'Y'
398 and B.LANGUAGE = userenv('LANG')
399 and not exists
400 (select NULL
401 from ECX_TRANSACTIONS_TL T
402 where T.TRANSACTION_ID = B.TRANSACTION_ID
403 and T.LANGUAGE = L.CODE);
404 end ADD_LANGUAGE;
405
406 end ECX_TRANSACTIONS_PKG;