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