1 package body ftp_payment_pattern_pkg as
2 /* $Header: ftppaypb.pls 120.1 2005/12/02 13:52:48 appldev noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_PAYMENT_ID in NUMBER,
6 X_AMRT_TYPE_CODE in NUMBER,
7 X_PATTERN_NAME in VARCHAR2,
8 X_OBJECT_VERSION_NUMBER in NUMBER,
9 X_DESCRIPTION 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 FTP_PAYMENT_PATTERN_B
17 where PAYMENT_ID = X_PAYMENT_ID
18 ;
19 begin
20 insert into FTP_PAYMENT_PATTERN_B (
21 PAYMENT_ID,
22 AMRT_TYPE_CODE,
23 PATTERN_NAME,
24 OBJECT_VERSION_NUMBER,
25 CREATION_DATE,
26 CREATED_BY,
27 LAST_UPDATE_DATE,
28 LAST_UPDATED_BY,
29 LAST_UPDATE_LOGIN
30 ) values (
31 X_PAYMENT_ID,
32 X_AMRT_TYPE_CODE,
33 X_PATTERN_NAME,
34 X_OBJECT_VERSION_NUMBER,
35 X_CREATION_DATE,
36 X_CREATED_BY,
37 X_LAST_UPDATE_DATE,
38 X_LAST_UPDATED_BY,
39 X_LAST_UPDATE_LOGIN
40 );
41
42 insert into FTP_PAYMENT_PATTERN_TL (
43 OBJECT_VERSION_NUMBER,
44 DESCRIPTION,
45 CREATION_DATE,
46 CREATED_BY,
47 LAST_UPDATED_BY,
48 LAST_UPDATE_DATE,
49 LAST_UPDATE_LOGIN,
50 PAYMENT_ID,
51 AMRT_TYPE_CODE,
52 LANGUAGE,
53 SOURCE_LANG
54 ) select
55 X_OBJECT_VERSION_NUMBER,
56 X_DESCRIPTION,
57 X_CREATION_DATE,
58 X_CREATED_BY,
59 X_LAST_UPDATED_BY,
60 X_LAST_UPDATE_DATE,
61 X_LAST_UPDATE_LOGIN,
62 X_PAYMENT_ID,
63 X_AMRT_TYPE_CODE,
64 L.LANGUAGE_CODE,
65 userenv('LANG')
66 from FND_LANGUAGES L
67 where L.INSTALLED_FLAG in ('I', 'B')
68 and not exists
69 (select NULL
70 from FTP_PAYMENT_PATTERN_TL T
71 where T.PAYMENT_ID = X_PAYMENT_ID
72 and T.LANGUAGE = L.LANGUAGE_CODE);
73
74 open c;
75 fetch c into X_ROWID;
76 if (c%notfound) then
77 close c;
78 raise no_data_found;
79 end if;
80 close c;
81
82 end INSERT_ROW;
83
84 procedure LOCK_ROW (
85 X_PAYMENT_ID in NUMBER,
86 X_AMRT_TYPE_CODE in NUMBER,
87 X_PATTERN_NAME in VARCHAR2,
88 X_OBJECT_VERSION_NUMBER in NUMBER,
89 X_DESCRIPTION in VARCHAR2
90 ) is
91 cursor c is select
92 AMRT_TYPE_CODE,
93 PATTERN_NAME,
94 OBJECT_VERSION_NUMBER
95 from FTP_PAYMENT_PATTERN_B
96 where PAYMENT_ID = X_PAYMENT_ID
97 for update of PAYMENT_ID nowait;
98 recinfo c%rowtype;
99
100 cursor c1 is select
101 DESCRIPTION,
102 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
103 from FTP_PAYMENT_PATTERN_TL
104 where PAYMENT_ID = X_PAYMENT_ID
105 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
106 for update of PAYMENT_ID nowait;
107 begin
108 open c;
109 fetch c into recinfo;
110 if (c%notfound) then
111 close c;
112 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
113 app_exception.raise_exception;
114 end if;
115 close c;
116 if ( (recinfo.AMRT_TYPE_CODE = X_AMRT_TYPE_CODE)
117 AND (recinfo.PATTERN_NAME = X_PATTERN_NAME)
118 AND ((recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
119 OR ((recinfo.OBJECT_VERSION_NUMBER is null) AND (X_OBJECT_VERSION_NUMBER is null)))
120 ) then
121 null;
122 else
123 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
124 app_exception.raise_exception;
125 end if;
126
127 for tlinfo in c1 loop
128 if (tlinfo.BASELANG = 'Y') then
129 if ( ((tlinfo.DESCRIPTION = X_DESCRIPTION)
130 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
131 ) then
132 null;
133 else
134 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
135 app_exception.raise_exception;
136 end if;
137 end if;
138 end loop;
139 return;
140 end LOCK_ROW;
141
142 procedure UPDATE_ROW (
143 X_PAYMENT_ID in NUMBER,
144 X_AMRT_TYPE_CODE in NUMBER,
145 X_PATTERN_NAME in VARCHAR2,
146 X_OBJECT_VERSION_NUMBER in NUMBER,
147 X_DESCRIPTION in VARCHAR2,
148 X_LAST_UPDATE_DATE in DATE,
149 X_LAST_UPDATED_BY in NUMBER,
150 X_LAST_UPDATE_LOGIN in NUMBER
151 ) is
152 begin
153 update FTP_PAYMENT_PATTERN_B set
154 AMRT_TYPE_CODE = X_AMRT_TYPE_CODE,
155 PATTERN_NAME = X_PATTERN_NAME,
156 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
157 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
158 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
159 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
160 where PAYMENT_ID = X_PAYMENT_ID;
161
162 if (sql%notfound) then
163 raise no_data_found;
164 end if;
165
166 update FTP_PAYMENT_PATTERN_TL set
167 DESCRIPTION = X_DESCRIPTION,
168 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
169 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
170 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
171 SOURCE_LANG = userenv('LANG')
172 where PAYMENT_ID = X_PAYMENT_ID
173 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
174
175 if (sql%notfound) then
176 raise no_data_found;
177 end if;
178 end UPDATE_ROW;
179
180 procedure DELETE_ROW (
181 X_PAYMENT_ID in NUMBER
182 ) is
183 begin
184 delete from FTP_PAYMENT_PATTERN_TL
185 where PAYMENT_ID = X_PAYMENT_ID;
186
187 if (sql%notfound) then
188 raise no_data_found;
189 end if;
190
191 delete from FTP_PAYMENT_PATTERN_B
192 where PAYMENT_ID = X_PAYMENT_ID;
193
194 if (sql%notfound) then
195 raise no_data_found;
196 end if;
197 end DELETE_ROW;
198
199 procedure ADD_LANGUAGE
200 is
201 begin
202 delete from FTP_PAYMENT_PATTERN_TL T
203 where not exists
204 (select NULL
205 from FTP_PAYMENT_PATTERN_B B
206 where B.PAYMENT_ID = T.PAYMENT_ID
207 );
208
209 update FTP_PAYMENT_PATTERN_TL T set (
210 DESCRIPTION
211 ) = (select
212 B.DESCRIPTION
213 from FTP_PAYMENT_PATTERN_TL B
214 where B.PAYMENT_ID = T.PAYMENT_ID
215 and B.LANGUAGE = T.SOURCE_LANG)
216 where (
217 T.PAYMENT_ID,
218 T.LANGUAGE
219 ) in (select
220 SUBT.PAYMENT_ID,
221 SUBT.LANGUAGE
222 from FTP_PAYMENT_PATTERN_TL SUBB, FTP_PAYMENT_PATTERN_TL SUBT
223 where SUBB.PAYMENT_ID = SUBT.PAYMENT_ID
224 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
225 and (SUBB.DESCRIPTION <> SUBT.DESCRIPTION
226 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
227 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
228 ));
229
230 insert into FTP_PAYMENT_PATTERN_TL (
231 OBJECT_VERSION_NUMBER,
232 DESCRIPTION,
233 CREATION_DATE,
234 CREATED_BY,
235 LAST_UPDATED_BY,
236 LAST_UPDATE_DATE,
237 LAST_UPDATE_LOGIN,
238 PAYMENT_ID,
239 AMRT_TYPE_CODE,
240 LANGUAGE,
241 SOURCE_LANG
242 ) select /*+ ORDERED */
243 B.OBJECT_VERSION_NUMBER,
244 B.DESCRIPTION,
245 B.CREATION_DATE,
246 B.CREATED_BY,
247 B.LAST_UPDATED_BY,
248 B.LAST_UPDATE_DATE,
249 B.LAST_UPDATE_LOGIN,
250 B.PAYMENT_ID,
251 B.AMRT_TYPE_CODE,
252 L.LANGUAGE_CODE,
253 B.SOURCE_LANG
254 from FTP_PAYMENT_PATTERN_TL B, FND_LANGUAGES L
255 where L.INSTALLED_FLAG in ('I', 'B')
256 and B.LANGUAGE = userenv('LANG')
257 and not exists
258 (select NULL
259 from FTP_PAYMENT_PATTERN_TL T
260 where T.PAYMENT_ID = B.PAYMENT_ID
261 and T.LANGUAGE = L.LANGUAGE_CODE);
262 end ADD_LANGUAGE;
263
264 end FTP_PAYMENT_PATTERN_PKG;
[Home] [Help]