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