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