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