eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.IBY_BANK_INSTRUCTIONS_PKG

Source


1 package body IBY_BANK_INSTRUCTIONS_PKG as
2 /* $Header: ibybicdb.pls 120.3 2005/12/01 21:52:39 chhu noship $ */
3 
4 procedure INSERT_ROW (
5   X_ROWID in out nocopy VARCHAR2,
6   X_BANK_INSTRUCTION_CODE in VARCHAR2,
7   X_INACTIVE_DATE in DATE,
8   X_TERRITORY_CODE in VARCHAR2,
9   X_OBJECT_VERSION_NUMBER in NUMBER,
10   X_FORMAT_VALUE in VARCHAR2,
11   X_MEANING in VARCHAR2,
12   X_DESCRIPTION in VARCHAR2,
13   X_CREATION_DATE in DATE,
14   X_CREATED_BY in NUMBER,
15   X_LAST_UPDATE_DATE in DATE,
16   X_LAST_UPDATED_BY in NUMBER,
17   X_LAST_UPDATE_LOGIN in NUMBER,
18   X_SEEDED_FLAG in VARCHAR2
19 ) is
20   cursor C is select ROWID from IBY_BANK_INSTRUCTIONS_B
21     where BANK_INSTRUCTION_CODE = X_BANK_INSTRUCTION_CODE
22     ;
23 begin
24   insert into IBY_BANK_INSTRUCTIONS_B (
25     INACTIVE_DATE,
26     TERRITORY_CODE,
27     OBJECT_VERSION_NUMBER,
28     BANK_INSTRUCTION_CODE,
29     FORMAT_VALUE,
30     CREATION_DATE,
31     CREATED_BY,
32     LAST_UPDATE_DATE,
33     LAST_UPDATED_BY,
34     LAST_UPDATE_LOGIN,
35     SEEDED_FLAG
36   ) values (
37     X_INACTIVE_DATE,
38     X_TERRITORY_CODE,
39     X_OBJECT_VERSION_NUMBER,
40     X_BANK_INSTRUCTION_CODE,
41     X_FORMAT_VALUE,
42     X_CREATION_DATE,
43     X_CREATED_BY,
44     X_LAST_UPDATE_DATE,
45     X_LAST_UPDATED_BY,
46     X_LAST_UPDATE_LOGIN,
47     X_SEEDED_FLAG
48   );
49 
50   insert into IBY_BANK_INSTRUCTIONS_TL (
51     CREATED_BY,
52     CREATION_DATE,
53     LAST_UPDATED_BY,
54     LAST_UPDATE_DATE,
55     LAST_UPDATE_LOGIN,
56     OBJECT_VERSION_NUMBER,
57     BANK_INSTRUCTION_CODE,
58     MEANING,
59     DESCRIPTION,
60     LANGUAGE,
61     SOURCE_LANG
62   ) select
63     X_CREATED_BY,
64     X_CREATION_DATE,
65     X_LAST_UPDATED_BY,
66     X_LAST_UPDATE_DATE,
67     X_LAST_UPDATE_LOGIN,
68     X_OBJECT_VERSION_NUMBER,
69     X_BANK_INSTRUCTION_CODE,
70     X_MEANING,
71     X_DESCRIPTION,
72     L.LANGUAGE_CODE,
73     userenv('LANG')
74   from FND_LANGUAGES L
75   where L.INSTALLED_FLAG in ('I', 'B')
76   and not exists
77     (select NULL
78     from IBY_BANK_INSTRUCTIONS_TL T
79     where T.BANK_INSTRUCTION_CODE = X_BANK_INSTRUCTION_CODE
80     and T.LANGUAGE = L.LANGUAGE_CODE);
81 
82   open c;
83   fetch c into X_ROWID;
84   if (c%notfound) then
85     close c;
86     raise no_data_found;
87   end if;
88   close c;
89 
90 end INSERT_ROW;
91 
92 procedure LOCK_ROW (
93   X_BANK_INSTRUCTION_CODE in VARCHAR2,
94   X_INACTIVE_DATE in DATE,
95   X_TERRITORY_CODE in VARCHAR2,
96   X_OBJECT_VERSION_NUMBER in NUMBER,
97   X_FORMAT_VALUE in VARCHAR2,
98   X_MEANING in VARCHAR2,
99   X_DESCRIPTION in VARCHAR2
100 ) is
101   cursor c is select
102       INACTIVE_DATE,
103       TERRITORY_CODE,
104       OBJECT_VERSION_NUMBER,
105       FORMAT_VALUE
106     from IBY_BANK_INSTRUCTIONS_B
107     where BANK_INSTRUCTION_CODE = X_BANK_INSTRUCTION_CODE
108     for update of BANK_INSTRUCTION_CODE nowait;
109   recinfo c%rowtype;
110 
111   cursor c1 is select
112       MEANING,
113       DESCRIPTION,
114       decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
115     from IBY_BANK_INSTRUCTIONS_TL
116     where BANK_INSTRUCTION_CODE = X_BANK_INSTRUCTION_CODE
117     and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
118     for update of BANK_INSTRUCTION_CODE nowait;
119 begin
120   open c;
121   fetch c into recinfo;
122   if (c%notfound) then
123     close c;
124     fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
125     app_exception.raise_exception;
126   end if;
127   close c;
128   if (    ((recinfo.INACTIVE_DATE = X_INACTIVE_DATE)
129            OR ((recinfo.INACTIVE_DATE is null) AND (X_INACTIVE_DATE is null)))
130       AND ((recinfo.TERRITORY_CODE = X_TERRITORY_CODE)
131            OR ((recinfo.TERRITORY_CODE is null) AND (X_TERRITORY_CODE is null)))
132       AND (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
133       AND ((recinfo.FORMAT_VALUE = X_FORMAT_VALUE)
134            OR ((recinfo.FORMAT_VALUE is null) AND (X_FORMAT_VALUE is null)))
135   ) then
136     null;
137   else
138     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
139     app_exception.raise_exception;
140   end if;
141 
142   for tlinfo in c1 loop
143     if (tlinfo.BASELANG = 'Y') then
144       if (    ((tlinfo.MEANING = X_MEANING)
145                OR ((tlinfo.MEANING is null) AND (X_MEANING is null)))
146           AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
147                OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
148       ) then
149         null;
150       else
151         fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
152         app_exception.raise_exception;
153       end if;
154     end if;
155   end loop;
156   return;
157 end LOCK_ROW;
158 
159 procedure UPDATE_ROW (
160   X_BANK_INSTRUCTION_CODE in VARCHAR2,
161   X_INACTIVE_DATE in DATE,
162   X_TERRITORY_CODE in VARCHAR2,
163   X_OBJECT_VERSION_NUMBER in NUMBER,
164   X_FORMAT_VALUE in VARCHAR2,
165   X_MEANING in VARCHAR2,
166   X_DESCRIPTION in VARCHAR2,
167   X_LAST_UPDATE_DATE in DATE,
168   X_LAST_UPDATED_BY in NUMBER,
169   X_LAST_UPDATE_LOGIN in NUMBER
170 ) is
171 begin
172   update IBY_BANK_INSTRUCTIONS_B set
173     INACTIVE_DATE = X_INACTIVE_DATE,
174     TERRITORY_CODE = X_TERRITORY_CODE,
175     OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
176     FORMAT_VALUE = X_FORMAT_VALUE,
177     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
178     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
179     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
180   where BANK_INSTRUCTION_CODE = X_BANK_INSTRUCTION_CODE;
181 
182   if (sql%notfound) then
183     raise no_data_found;
184   end if;
185 
186   update IBY_BANK_INSTRUCTIONS_TL set
187     MEANING = X_MEANING,
188     DESCRIPTION = X_DESCRIPTION,
189     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
190     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
191     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
192     SOURCE_LANG = userenv('LANG')
193   where BANK_INSTRUCTION_CODE = X_BANK_INSTRUCTION_CODE
194   and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
195 
196   if (sql%notfound) then
197     raise no_data_found;
198   end if;
199 end UPDATE_ROW;
200 
201 procedure DELETE_ROW (
202   X_BANK_INSTRUCTION_CODE in VARCHAR2
203 ) is
204 begin
205   delete from IBY_BANK_INSTRUCTIONS_TL
206   where BANK_INSTRUCTION_CODE = X_BANK_INSTRUCTION_CODE;
207 
208   if (sql%notfound) then
209     raise no_data_found;
210   end if;
211 
212   delete from IBY_BANK_INSTRUCTIONS_B
213   where BANK_INSTRUCTION_CODE = X_BANK_INSTRUCTION_CODE;
214 
215   if (sql%notfound) then
216     raise no_data_found;
217   end if;
218 end DELETE_ROW;
219 
220 procedure ADD_LANGUAGE
221 is
222 begin
223   delete from IBY_BANK_INSTRUCTIONS_TL T
224   where not exists
225     (select NULL
226     from IBY_BANK_INSTRUCTIONS_B B
227     where B.BANK_INSTRUCTION_CODE = T.BANK_INSTRUCTION_CODE
228     );
229 
230   update IBY_BANK_INSTRUCTIONS_TL T set (
231       MEANING,
232       DESCRIPTION
233     ) = (select
234       B.MEANING,
235       B.DESCRIPTION
236     from IBY_BANK_INSTRUCTIONS_TL B
237     where B.BANK_INSTRUCTION_CODE = T.BANK_INSTRUCTION_CODE
238     and B.LANGUAGE = T.SOURCE_LANG)
239   where (
240       T.BANK_INSTRUCTION_CODE,
241       T.LANGUAGE
242   ) in (select
243       SUBT.BANK_INSTRUCTION_CODE,
244       SUBT.LANGUAGE
245     from IBY_BANK_INSTRUCTIONS_TL SUBB, IBY_BANK_INSTRUCTIONS_TL SUBT
246     where SUBB.BANK_INSTRUCTION_CODE = SUBT.BANK_INSTRUCTION_CODE
247     and SUBB.LANGUAGE = SUBT.SOURCE_LANG
248     and (SUBB.MEANING <> SUBT.MEANING
249       or (SUBB.MEANING is null and SUBT.MEANING is not null)
250       or (SUBB.MEANING is not null and SUBT.MEANING is null)
251       or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
252       or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
253       or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
254   ));
255 
256   insert into IBY_BANK_INSTRUCTIONS_TL (
257     CREATED_BY,
258     CREATION_DATE,
259     LAST_UPDATED_BY,
260     LAST_UPDATE_DATE,
261     LAST_UPDATE_LOGIN,
262     OBJECT_VERSION_NUMBER,
263     BANK_INSTRUCTION_CODE,
264     MEANING,
265     DESCRIPTION,
266     LANGUAGE,
267     SOURCE_LANG
268   ) select /*+ ORDERED */
269     B.CREATED_BY,
270     B.CREATION_DATE,
271     B.LAST_UPDATED_BY,
272     B.LAST_UPDATE_DATE,
273     B.LAST_UPDATE_LOGIN,
274     B.OBJECT_VERSION_NUMBER,
275     B.BANK_INSTRUCTION_CODE,
276     B.MEANING,
277     B.DESCRIPTION,
278     L.LANGUAGE_CODE,
279     B.SOURCE_LANG
280   from IBY_BANK_INSTRUCTIONS_TL B, FND_LANGUAGES L
281   where L.INSTALLED_FLAG in ('I', 'B')
282   and B.LANGUAGE = userenv('LANG')
283   and not exists
284     (select NULL
285     from IBY_BANK_INSTRUCTIONS_TL T
286     where T.BANK_INSTRUCTION_CODE = B.BANK_INSTRUCTION_CODE
287     and T.LANGUAGE = L.LANGUAGE_CODE);
288 end ADD_LANGUAGE;
289 
290 
291 procedure LOAD_SEED_ROW (
292   X_BANK_INSTRUCTION_CODE in VARCHAR2,
293   X_INACTIVE_DATE in DATE,
294   X_TERRITORY_CODE in VARCHAR2,
295   X_OBJECT_VERSION_NUMBER in NUMBER,
296   X_FORMAT_VALUE in VARCHAR2,
297   X_MEANING in VARCHAR2,
298   X_DESCRIPTION in VARCHAR2,
299   X_SEEDED_FLAG in VARCHAR2,
300   X_CREATION_DATE in DATE,
301   X_CREATED_BY in NUMBER,
302   X_LAST_UPDATE_DATE in DATE,
303   X_LAST_UPDATED_BY in NUMBER,
304   X_LAST_UPDATE_LOGIN in NUMBER)
305 
306 is
307     row_id VARCHAR2(200);
308   begin
309 UPDATE_ROW (
310    X_BANK_INSTRUCTION_CODE,
311   X_INACTIVE_DATE,
312   X_TERRITORY_CODE,
313   X_OBJECT_VERSION_NUMBER,
314   X_FORMAT_VALUE,
315   X_MEANING,
316   X_DESCRIPTION,
317   X_LAST_UPDATE_DATE,
318   X_LAST_UPDATED_BY,
319   X_LAST_UPDATE_LOGIN
320 );
321 
322   exception
323     when no_data_found then
324 
325 INSERT_ROW (
326   row_id,
327   X_BANK_INSTRUCTION_CODE,
328   X_INACTIVE_DATE,
329   X_TERRITORY_CODE,
330   X_OBJECT_VERSION_NUMBER,
331   X_FORMAT_VALUE,
332   X_MEANING,
333   X_DESCRIPTION,
334   X_CREATION_DATE,
335   X_CREATED_BY,
336   X_LAST_UPDATE_DATE,
337   X_LAST_UPDATED_BY,
338   X_LAST_UPDATE_LOGIN,
339   X_SEEDED_FLAG
340 );
341 
342   end;
343 
344 procedure TRANSLATE_ROW (
345   X_BANK_INSTRUCTION_CODE in VARCHAR2,
346   X_MEANING in VARCHAR2,
347   X_DESCRIPTION in VARCHAR2,
348   X_OBJECT_VERSION_NUMBER in NUMBER,
349   X_OWNER in VARCHAR2)
350 is
351 begin
352   update iby_bank_instructions_tl set
353     MEANING = X_MEANING,
354     DESCRIPTION = X_DESCRIPTION,
355     OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
356     LAST_UPDATED_BY = fnd_load_util.owner_id(X_OWNER),
357     LAST_UPDATE_DATE = trunc(sysdate),
358     LAST_UPDATE_LOGIN = fnd_load_util.owner_id(X_OWNER),
359     SOURCE_LANG = userenv('LANG')
360   where userenv('LANG') in (LANGUAGE, SOURCE_LANG)
361     and BANK_INSTRUCTION_CODE = X_BANK_INSTRUCTION_CODE;
362 end;
363 
364 end IBY_BANK_INSTRUCTIONS_PKG;