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