[Home] [Help]
Skip to content
PACKAGE BODY: APPS.OKC_UDA_TEMPLATES_ALL_PKG
Source
1 package body OKC_UDA_TEMPLATES_ALL_PKG as
2 /* $Header: OKCUTPKGB.pls 120.0.12020000.1 2013/03/27 08:39:25 aksgoyal noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_UDA_TEMPLATE_ID in NUMBER,
6 X_UDA_TEMPLATE_NAME in VARCHAR2,
7 X_START_DATE in DATE,
8 X_END_DATE in DATE,
9 X_VERSION_NUMBER in NUMBER,
10 X_STATUS_FLAG in VARCHAR2,
11 X_ORG_ID in NUMBER,
12 X_TEMPLATE_CATEGORY in VARCHAR2,
13 X_CATEGORY_VALUE in VARCHAR2,
14 X_COMMENTS in VARCHAR2,
15 X_ORIG_TEMPLATE_ID in NUMBER,
16 X_CREATED_BY in NUMBER,
17 X_LAST_UPDATED_BY in NUMBER,
18 X_LAST_UPDATE_LOGIN in NUMBER,
19 X_CREATION_DATE in DATE,
20 X_LAST_UPDATE_DATE in DATE,
21 X_UDA_TEMPLATE_DISP_NAME in VARCHAR2
22 ) is
23 cursor C is select ROWID from OKC_UDA_TEMPLATES_ALL
24 where UDA_TEMPLATE_ID = X_UDA_TEMPLATE_ID
25 ;
26 begin
27 insert into OKC_UDA_TEMPLATES_ALL (
28 UDA_TEMPLATE_ID,
29 START_DATE,
30 END_DATE,
31 VERSION_NUMBER,
32 CREATION_DATE,
33 CREATED_BY,
34 LAST_UPDATE_DATE,
35 LAST_UPDATED_BY,
36 LAST_UPDATE_LOGIN
37 ) values (
38 X_UDA_TEMPLATE_ID,
39 X_START_DATE,
40 X_END_DATE,
41 X_VERSION_NUMBER,
42 X_CREATION_DATE,
43 X_CREATED_BY,
44 X_LAST_UPDATE_DATE,
45 X_LAST_UPDATED_BY,
46 X_LAST_UPDATE_LOGIN
47 );
48
49 insert into OKC_UDA_TEMPLATES_ALL_TL (
50 UDA_TEMPLATE_ID,
51 UDA_TEMPLATE_DISP_NAME,
52 COMMENTS,
53 CREATED_BY,
54 CREATION_DATE,
55 LAST_UPDATED_BY,
56 LAST_UPDATE_DATE,
57 LAST_UPDATE_LOGIN,
58 LANGUAGE,
59 SOURCE_LANG
60 ) select
61 X_UDA_TEMPLATE_ID,
62 X_UDA_TEMPLATE_DISP_NAME,
63 X_COMMENTS,
64 X_CREATED_BY,
65 X_CREATION_DATE,
66 X_LAST_UPDATED_BY,
67 X_LAST_UPDATE_DATE,
68 X_LAST_UPDATE_LOGIN,
69 L.LANGUAGE_CODE,
70 userenv('LANG')
71 from FND_LANGUAGES L
72 where L.INSTALLED_FLAG in ('I', 'B')
73 and not exists
74 (select NULL
75 from OKC_UDA_TEMPLATES_ALL_TL T
76 where T.UDA_TEMPLATE_ID = X_UDA_TEMPLATE_ID
77 and T.LANGUAGE = L.LANGUAGE_CODE);
78
79 open c;
80 fetch c into X_ROWID;
81 if (c%notfound) then
82 close c;
83 raise no_data_found;
84 end if;
85 close c;
86
87 end INSERT_ROW;
88
89 procedure LOCK_ROW (
90 X_UDA_TEMPLATE_ID in NUMBER,
91 X_END_DATE in DATE,
92 X_VERSION_NUMBER in NUMBER,
93 X_START_DATE in DATE,
94 X_UDA_TEMPLATE_DISP_NAME in VARCHAR2,
95 X_COMMENTS in VARCHAR2
96 ) is
97 cursor c is select
98 END_DATE,
99 VERSION_NUMBER,
100 START_DATE
101 from OKC_UDA_TEMPLATES_ALL
102 where UDA_TEMPLATE_ID = X_UDA_TEMPLATE_ID
103 for update of UDA_TEMPLATE_ID nowait;
104 recinfo c%rowtype;
105
106 cursor c1 is select
107 UDA_TEMPLATE_DISP_NAME,
108 COMMENTS,
109 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
110 from OKC_UDA_TEMPLATES_ALL_TL
111 where UDA_TEMPLATE_ID = X_UDA_TEMPLATE_ID
112 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
113 for update of UDA_TEMPLATE_ID nowait;
114 begin
115 open c;
116 fetch c into recinfo;
117 if (c%notfound) then
118 close c;
119 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
120 app_exception.raise_exception;
121 end if;
122 close c;
123 if ( ((recinfo.END_DATE = X_END_DATE)
124 OR ((recinfo.END_DATE is null) AND (X_END_DATE is null)))
125 AND (recinfo.VERSION_NUMBER = X_VERSION_NUMBER)
126 AND (recinfo.START_DATE = X_START_DATE)
127 ) then
128 null;
129 else
130 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
131 app_exception.raise_exception;
132 end if;
133
134 for tlinfo in c1 loop
135 if (tlinfo.BASELANG = 'Y') then
136 if ( (tlinfo.UDA_TEMPLATE_DISP_NAME = X_UDA_TEMPLATE_DISP_NAME)
137 AND ((tlinfo.COMMENTS = X_COMMENTS)
138 OR ((tlinfo.COMMENTS is null) AND (X_COMMENTS is null)))
139 ) then
140 null;
141 else
142 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
143 app_exception.raise_exception;
144 end if;
145 end if;
146 end loop;
147 return;
148 end LOCK_ROW;
149
150 procedure UPDATE_ROW (
151 X_UDA_TEMPLATE_ID in NUMBER,
152 X_UDA_TEMPLATE_NAME in VARCHAR2,
153 X_START_DATE in DATE,
154 X_END_DATE in DATE,
155 X_VERSION_NUMBER in NUMBER,
156 X_STATUS_FLAG in VARCHAR2,
157 X_ORG_ID in NUMBER,
158 X_TEMPLATE_CATEGORY in VARCHAR2,
159 X_CATEGORY_VALUE in VARCHAR2,
160 X_COMMENTS in VARCHAR2,
161 X_ORIG_TEMPLATE_ID in NUMBER,
162 X_LAST_UPDATED_BY in NUMBER,
163 X_LAST_UPDATE_LOGIN in NUMBER,
164 X_LAST_UPDATE_DATE in DATE,
165 X_UDA_TEMPLATE_DISP_NAME in VARCHAR2
166 ) is
167 begin
168 update OKC_UDA_TEMPLATES_ALL set
169 UDA_TEMPLATE_NAME = X_UDA_TEMPLATE_NAME,
170 STATUS_FLAG = X_STATUS_FLAG ,
171 ORG_ID = X_ORG_ID,
172 TEMPLATE_CATEGORY = X_TEMPLATE_CATEGORY,
173 CATEGORY_VALUE = X_CATEGORY_VALUE ,
174 COMMENTS = X_COMMENTS,
175 ORIG_TEMPLATE_ID = X_ORIG_TEMPLATE_ID,
176 END_DATE = X_END_DATE,
177 VERSION_NUMBER = X_VERSION_NUMBER,
178 START_DATE = X_START_DATE,
179 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
180 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
181 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
182 where UDA_TEMPLATE_ID = X_UDA_TEMPLATE_ID;
183
184 if (sql%notfound) then
185 raise no_data_found;
186 end if;
187
188 update OKC_UDA_TEMPLATES_ALL_TL set
189 UDA_TEMPLATE_DISP_NAME = X_UDA_TEMPLATE_DISP_NAME,
190 COMMENTS = X_COMMENTS,
191 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
192 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
193 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
194 SOURCE_LANG = userenv('LANG')
195 where UDA_TEMPLATE_ID = X_UDA_TEMPLATE_ID
196 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
197
198 if (sql%notfound) then
199 raise no_data_found;
200 end if;
201 end UPDATE_ROW;
202
203 procedure DELETE_ROW (
204 X_UDA_TEMPLATE_ID in NUMBER
205 ) is
206 begin
207 delete from OKC_UDA_TEMPLATES_ALL_TL
208 where UDA_TEMPLATE_ID = X_UDA_TEMPLATE_ID;
209
210 if (sql%notfound) then
211 raise no_data_found;
212 end if;
213
214 delete from OKC_UDA_TEMPLATES_ALL
215 where UDA_TEMPLATE_ID = X_UDA_TEMPLATE_ID;
216
217 if (sql%notfound) then
218 raise no_data_found;
219 end if;
220 end DELETE_ROW;
221
222 procedure ADD_LANGUAGE
223 is
224 begin
225 delete from OKC_UDA_TEMPLATES_ALL_TL T
226 where not exists
227 (select NULL
228 from OKC_UDA_TEMPLATES_ALL B
229 where B.UDA_TEMPLATE_ID = T.UDA_TEMPLATE_ID
230 );
231
232 update OKC_UDA_TEMPLATES_ALL_TL T set (
233 UDA_TEMPLATE_DISP_NAME,
234 COMMENTS
235 ) = (select
236 B.UDA_TEMPLATE_DISP_NAME,
237 B.COMMENTS
238 from OKC_UDA_TEMPLATES_ALL_TL B
239 where B.UDA_TEMPLATE_ID = T.UDA_TEMPLATE_ID
240 and B.LANGUAGE = T.SOURCE_LANG)
241 where (
242 T.UDA_TEMPLATE_ID,
243 T.LANGUAGE
244 ) in (select
245 SUBT.UDA_TEMPLATE_ID,
246 SUBT.LANGUAGE
247 from OKC_UDA_TEMPLATES_ALL_TL SUBB, OKC_UDA_TEMPLATES_ALL_TL SUBT
248 where SUBB.UDA_TEMPLATE_ID = SUBT.UDA_TEMPLATE_ID
249 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
250 and (SUBB.UDA_TEMPLATE_DISP_NAME <> SUBT.UDA_TEMPLATE_DISP_NAME
251 or SUBB.COMMENTS <> SUBT.COMMENTS
252 or (SUBB.COMMENTS is null and SUBT.COMMENTS is not null)
253 or (SUBB.COMMENTS is not null and SUBT.COMMENTS is null)
254 ));
255
256 insert into OKC_UDA_TEMPLATES_ALL_TL (
257 UDA_TEMPLATE_ID,
258 UDA_TEMPLATE_DISP_NAME,
259 COMMENTS,
260 CREATED_BY,
261 CREATION_DATE,
262 LAST_UPDATED_BY,
263 LAST_UPDATE_DATE,
264 LAST_UPDATE_LOGIN,
265 LANGUAGE,
266 SOURCE_LANG
267 ) select /*+ ORDERED */
268 B.UDA_TEMPLATE_ID,
269 B.UDA_TEMPLATE_DISP_NAME,
270 B.COMMENTS,
271 B.CREATED_BY,
272 B.CREATION_DATE,
273 B.LAST_UPDATED_BY,
274 B.LAST_UPDATE_DATE,
275 B.LAST_UPDATE_LOGIN,
276 L.LANGUAGE_CODE,
277 B.SOURCE_LANG
278 from OKC_UDA_TEMPLATES_ALL_TL B, FND_LANGUAGES L
279 where L.INSTALLED_FLAG in ('I', 'B')
280 and B.LANGUAGE = userenv('LANG')
281 and not exists
282 (select NULL
283 from OKC_UDA_TEMPLATES_ALL_TL T
284 where T.UDA_TEMPLATE_ID = B.UDA_TEMPLATE_ID
285 and T.LANGUAGE = L.LANGUAGE_CODE);
286 end ADD_LANGUAGE;
287
288 end OKC_UDA_TEMPLATES_ALL_PKG;