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