DBA Data[Home] [Help]

PACKAGE BODY: APPS.FPA_OBJECTS_PKG

Source


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;