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