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