[Home] [Help]
PACKAGE BODY: APPS.PFT_VAL_INDEX_FORMULA_PKG
Source
1 package body PFT_VAL_INDEX_FORMULA_PKG as
2 /* $Header: PFTVALINDFB.pls 120.0 2005/10/19 19:16:12 appldev noship $ */
3
4 procedure INSERT_ROW (
5 X_ROWID in out nocopy VARCHAR2,
6 X_VALUE_INDEX_FORMULA_ID in NUMBER,
7 X_OBJECT_VERSION_NUMBER in NUMBER,
8 X_DISPLAY_NAME in VARCHAR2,
9 X_DESCRIPTION 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 PFT_VAL_INDEX_FORMULA_B
17 where VALUE_INDEX_FORMULA_ID = X_VALUE_INDEX_FORMULA_ID
18 ;
19 begin
20 insert into PFT_VAL_INDEX_FORMULA_B (
21 VALUE_INDEX_FORMULA_ID,
22 OBJECT_VERSION_NUMBER,
23 CREATION_DATE,
24 CREATED_BY,
25 LAST_UPDATE_DATE,
26 LAST_UPDATED_BY,
27 LAST_UPDATE_LOGIN
28 ) values (
29 X_VALUE_INDEX_FORMULA_ID,
30 X_OBJECT_VERSION_NUMBER,
31 X_CREATION_DATE,
32 X_CREATED_BY,
33 X_LAST_UPDATE_DATE,
34 X_LAST_UPDATED_BY,
35 X_LAST_UPDATE_LOGIN
36 );
37
38 insert into PFT_VAL_INDEX_FORMULA_TL (
39 VALUE_INDEX_FORMULA_ID,
40 DISPLAY_NAME,
41 DESCRIPTION,
42 CREATED_BY,
43 CREATION_DATE,
44 LAST_UPDATED_BY,
45 LAST_UPDATE_DATE,
46 LAST_UPDATE_LOGIN,
47 LANGUAGE,
48 SOURCE_LANG
49 ) select
50 X_VALUE_INDEX_FORMULA_ID,
51 X_DISPLAY_NAME,
52 X_DESCRIPTION,
53 X_CREATED_BY,
54 X_CREATION_DATE,
55 X_LAST_UPDATED_BY,
56 X_LAST_UPDATE_DATE,
57 X_LAST_UPDATE_LOGIN,
58 L.LANGUAGE_CODE,
59 userenv('LANG')
60 from FND_LANGUAGES L
61 where L.INSTALLED_FLAG in ('I', 'B')
62 and not exists
63 (select NULL
64 from PFT_VAL_INDEX_FORMULA_TL T
65 where T.VALUE_INDEX_FORMULA_ID = X_VALUE_INDEX_FORMULA_ID
66 and T.LANGUAGE = L.LANGUAGE_CODE);
67
68 open c;
69 fetch c into X_ROWID;
70 if (c%notfound) then
71 close c;
72 raise no_data_found;
73 end if;
74 close c;
75
76 end INSERT_ROW;
77
78 procedure LOCK_ROW (
79 X_VALUE_INDEX_FORMULA_ID in NUMBER,
80 X_OBJECT_VERSION_NUMBER in NUMBER,
81 X_DISPLAY_NAME in VARCHAR2,
82 X_DESCRIPTION in VARCHAR2
83 ) is
84 cursor c is select
85 OBJECT_VERSION_NUMBER
86 from PFT_VAL_INDEX_FORMULA_B
87 where VALUE_INDEX_FORMULA_ID = X_VALUE_INDEX_FORMULA_ID
88 for update of VALUE_INDEX_FORMULA_ID nowait;
89 recinfo c%rowtype;
90
91 cursor c1 is select
92 DISPLAY_NAME,
93 DESCRIPTION,
94 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
95 from PFT_VAL_INDEX_FORMULA_TL
96 where VALUE_INDEX_FORMULA_ID = X_VALUE_INDEX_FORMULA_ID
97 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
98 for update of VALUE_INDEX_FORMULA_ID nowait;
99 begin
100 open c;
101 fetch c into recinfo;
102 if (c%notfound) then
103 close c;
104 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
105 app_exception.raise_exception;
106 end if;
107 close c;
108 if ( ((recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
109 OR ((recinfo.OBJECT_VERSION_NUMBER is null) AND (X_OBJECT_VERSION_NUMBER 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.DISPLAY_NAME = X_DISPLAY_NAME)
120 AND (tlinfo.DESCRIPTION = X_DESCRIPTION)
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_VALUE_INDEX_FORMULA_ID in NUMBER,
134 X_OBJECT_VERSION_NUMBER in NUMBER,
135 X_DISPLAY_NAME in VARCHAR2,
136 X_DESCRIPTION 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 PFT_VAL_INDEX_FORMULA_B set
143 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
144 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
145 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
146 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
147 where VALUE_INDEX_FORMULA_ID = X_VALUE_INDEX_FORMULA_ID;
148
149 if (sql%notfound) then
150 raise no_data_found;
151 end if;
152
153 update PFT_VAL_INDEX_FORMULA_TL set
154 DISPLAY_NAME = X_DISPLAY_NAME,
155 DESCRIPTION = X_DESCRIPTION,
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 VALUE_INDEX_FORMULA_ID = X_VALUE_INDEX_FORMULA_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_VALUE_INDEX_FORMULA_ID in NUMBER
170 ) is
171 begin
172 delete from PFT_VAL_INDEX_FORMULA_TL
173 where VALUE_INDEX_FORMULA_ID = X_VALUE_INDEX_FORMULA_ID;
174
175 if (sql%notfound) then
176 raise no_data_found;
177 end if;
178
179 delete from PFT_VAL_INDEX_FORMULA_B
180 where VALUE_INDEX_FORMULA_ID = X_VALUE_INDEX_FORMULA_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 PFT_VAL_INDEX_FORMULA_TL T
191 where not exists
192 (select NULL
193 from PFT_VAL_INDEX_FORMULA_B B
194 where B.VALUE_INDEX_FORMULA_ID = T.VALUE_INDEX_FORMULA_ID
195 );
196
197 update PFT_VAL_INDEX_FORMULA_TL T set (
198 DISPLAY_NAME,
199 DESCRIPTION
200 ) = (select
201 B.DISPLAY_NAME,
202 B.DESCRIPTION
203 from PFT_VAL_INDEX_FORMULA_TL B
204 where B.VALUE_INDEX_FORMULA_ID = T.VALUE_INDEX_FORMULA_ID
205 and B.LANGUAGE = T.SOURCE_LANG)
206 where (
207 T.VALUE_INDEX_FORMULA_ID,
208 T.LANGUAGE
209 ) in (select
210 SUBT.VALUE_INDEX_FORMULA_ID,
211 SUBT.LANGUAGE
212 from PFT_VAL_INDEX_FORMULA_TL SUBB, PFT_VAL_INDEX_FORMULA_TL SUBT
213 where SUBB.VALUE_INDEX_FORMULA_ID = SUBT.VALUE_INDEX_FORMULA_ID
214 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
215 and (SUBB.DISPLAY_NAME <> SUBT.DISPLAY_NAME
216 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
217 ));
218
219 insert into PFT_VAL_INDEX_FORMULA_TL (
220 VALUE_INDEX_FORMULA_ID,
221 DISPLAY_NAME,
222 DESCRIPTION,
223 CREATED_BY,
224 CREATION_DATE,
225 LAST_UPDATED_BY,
226 LAST_UPDATE_DATE,
227 LAST_UPDATE_LOGIN,
228 LANGUAGE,
229 SOURCE_LANG
230 ) select /*+ ORDERED */
231 B.VALUE_INDEX_FORMULA_ID,
232 B.DISPLAY_NAME,
233 B.DESCRIPTION,
234 B.CREATED_BY,
235 B.CREATION_DATE,
236 B.LAST_UPDATED_BY,
237 B.LAST_UPDATE_DATE,
238 B.LAST_UPDATE_LOGIN,
239 L.LANGUAGE_CODE,
240 B.SOURCE_LANG
241 from PFT_VAL_INDEX_FORMULA_TL B, FND_LANGUAGES L
242 where L.INSTALLED_FLAG in ('I', 'B')
243 and B.LANGUAGE = userenv('LANG')
244 and not exists
245 (select NULL
246 from PFT_VAL_INDEX_FORMULA_TL T
247 where T.VALUE_INDEX_FORMULA_ID = B.VALUE_INDEX_FORMULA_ID
248 and T.LANGUAGE = L.LANGUAGE_CODE);
249 end ADD_LANGUAGE;
250
251 end PFT_VAL_INDEX_FORMULA_PKG;