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