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