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