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