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