[Home] [Help]
PACKAGE BODY: APPS.OKC_REP_RELATIONSHIPS_PKG
Source
1 package body OKC_REP_RELATIONSHIPS_PKG as
2 /* $Header: OKCREPRELB.pls 120.0 2005/06/22 10:43:59 amakalin noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_RELATIONSHIP_ROLE1_ID in NUMBER,
6 X_DIRECTION in VARCHAR2,
7 X_OBJECT_VERSION_NUMBER in NUMBER,
8 X_RELATIONSHIP_ROLE2_ID in NUMBER,
9 X_DISABLED_FLAG in VARCHAR2,
10 X_RELATIONSHIP_ROLE1_NAME in VARCHAR2,
11 X_RELATIONSHIP_ROLE2_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_REP_RELATIONSHIPS_B
20 where RELATIONSHIP_ROLE1_ID = X_RELATIONSHIP_ROLE1_ID
21 ;
22 begin
23 insert into OKC_REP_RELATIONSHIPS_B (
24 DIRECTION,
25 OBJECT_VERSION_NUMBER,
26 RELATIONSHIP_ROLE1_ID,
27 RELATIONSHIP_ROLE2_ID,
28 DISABLED_FLAG,
29 CREATION_DATE,
30 CREATED_BY,
31 LAST_UPDATE_DATE,
32 LAST_UPDATED_BY,
33 LAST_UPDATE_LOGIN
34 ) values (
35 X_DIRECTION,
36 X_OBJECT_VERSION_NUMBER,
37 X_RELATIONSHIP_ROLE1_ID,
38 X_RELATIONSHIP_ROLE2_ID,
39 X_DISABLED_FLAG,
40 X_CREATION_DATE,
41 X_CREATED_BY,
42 X_LAST_UPDATE_DATE,
43 X_LAST_UPDATED_BY,
44 X_LAST_UPDATE_LOGIN
45 );
46
47 insert into OKC_REP_RELATIONSHIPS_TL (
48 RELATIONSHIP_ROLE1_ID,
49 DESCRIPTION,
50 RELATIONSHIP_ROLE1_NAME,
51 RELATIONSHIP_ROLE2_NAME,
52 CREATED_BY,
53 CREATION_DATE,
54 LAST_UPDATED_BY,
55 LAST_UPDATE_DATE,
56 LAST_UPDATE_LOGIN,
57 LANGUAGE,
58 SOURCE_LANG
59 ) select
60 X_RELATIONSHIP_ROLE1_ID,
61 X_DESCRIPTION,
62 X_RELATIONSHIP_ROLE1_NAME,
63 X_RELATIONSHIP_ROLE2_NAME,
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_REP_RELATIONSHIPS_TL T
76 where T.RELATIONSHIP_ROLE1_ID = X_RELATIONSHIP_ROLE1_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_RELATIONSHIP_ROLE1_ID in NUMBER,
91 X_DIRECTION in VARCHAR2,
92 X_OBJECT_VERSION_NUMBER in NUMBER,
93 X_RELATIONSHIP_ROLE2_ID in NUMBER,
94 X_DISABLED_FLAG in VARCHAR2,
95 X_RELATIONSHIP_ROLE1_NAME in VARCHAR2,
96 X_RELATIONSHIP_ROLE2_NAME in VARCHAR2,
97 X_DESCRIPTION in VARCHAR2
98 ) is
99 cursor c is select
100 DIRECTION,
101 OBJECT_VERSION_NUMBER,
102 RELATIONSHIP_ROLE2_ID,
103 DISABLED_FLAG
104 from OKC_REP_RELATIONSHIPS_B
105 where RELATIONSHIP_ROLE1_ID = X_RELATIONSHIP_ROLE1_ID
106 for update of RELATIONSHIP_ROLE1_ID nowait;
107 recinfo c%rowtype;
108
109 cursor c1 is select
110 RELATIONSHIP_ROLE1_NAME,
111 RELATIONSHIP_ROLE2_NAME,
112 DESCRIPTION,
113 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
114 from OKC_REP_RELATIONSHIPS_TL
115 where RELATIONSHIP_ROLE1_ID = X_RELATIONSHIP_ROLE1_ID
116 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
117 for update of RELATIONSHIP_ROLE1_ID nowait;
118 begin
119 open c;
120 fetch c into recinfo;
121 if (c%notfound) then
122 close c;
123 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
124 app_exception.raise_exception;
125 end if;
126 close c;
127 if ( (recinfo.DIRECTION = X_DIRECTION)
128 AND (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
129 AND (recinfo.RELATIONSHIP_ROLE2_ID = X_RELATIONSHIP_ROLE2_ID)
130 AND (recinfo.DISABLED_FLAG = X_DISABLED_FLAG)
131 ) then
132 null;
133 else
134 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
135 app_exception.raise_exception;
136 end if;
137
138 for tlinfo in c1 loop
139 if (tlinfo.BASELANG = 'Y') then
140 if ( (tlinfo.RELATIONSHIP_ROLE1_NAME = X_RELATIONSHIP_ROLE1_NAME)
141 AND (tlinfo.RELATIONSHIP_ROLE2_NAME = X_RELATIONSHIP_ROLE2_NAME)
142 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
143 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
144 ) then
145 null;
146 else
147 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
148 app_exception.raise_exception;
149 end if;
150 end if;
151 end loop;
152 return;
153 end LOCK_ROW;
154
155 procedure UPDATE_ROW (
156 X_RELATIONSHIP_ROLE1_ID in NUMBER,
157 X_DIRECTION in VARCHAR2,
158 X_OBJECT_VERSION_NUMBER in NUMBER,
159 X_RELATIONSHIP_ROLE2_ID in NUMBER,
160 X_DISABLED_FLAG in VARCHAR2,
161 X_RELATIONSHIP_ROLE1_NAME in VARCHAR2,
162 X_RELATIONSHIP_ROLE2_NAME in VARCHAR2,
163 X_DESCRIPTION in VARCHAR2,
164 X_LAST_UPDATE_DATE in DATE,
165 X_LAST_UPDATED_BY in NUMBER,
166 X_LAST_UPDATE_LOGIN in NUMBER
167 ) is
168 begin
169 update OKC_REP_RELATIONSHIPS_B set
170 DIRECTION = X_DIRECTION,
171 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
172 RELATIONSHIP_ROLE2_ID = X_RELATIONSHIP_ROLE2_ID,
173 DISABLED_FLAG = X_DISABLED_FLAG,
174 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
175 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
176 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
177 where RELATIONSHIP_ROLE1_ID = X_RELATIONSHIP_ROLE1_ID;
178
179 if (sql%notfound) then
180 raise no_data_found;
181 end if;
182
183 update OKC_REP_RELATIONSHIPS_TL set
184 RELATIONSHIP_ROLE1_NAME = X_RELATIONSHIP_ROLE1_NAME,
185 RELATIONSHIP_ROLE2_NAME = X_RELATIONSHIP_ROLE2_NAME,
186 DESCRIPTION = X_DESCRIPTION,
187 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
188 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
189 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
190 SOURCE_LANG = userenv('LANG')
191 where RELATIONSHIP_ROLE1_ID = X_RELATIONSHIP_ROLE1_ID
192 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
193
194 if (sql%notfound) then
195 raise no_data_found;
196 end if;
197 end UPDATE_ROW;
198
199 procedure DELETE_ROW (
200 X_RELATIONSHIP_ROLE1_ID in NUMBER
201 ) is
202 begin
203 delete from OKC_REP_RELATIONSHIPS_TL
204 where RELATIONSHIP_ROLE1_ID = X_RELATIONSHIP_ROLE1_ID;
205
206 if (sql%notfound) then
207 raise no_data_found;
208 end if;
209
210 delete from OKC_REP_RELATIONSHIPS_B
211 where RELATIONSHIP_ROLE1_ID = X_RELATIONSHIP_ROLE1_ID;
212
213 if (sql%notfound) then
214 raise no_data_found;
215 end if;
216 end DELETE_ROW;
217
218 procedure ADD_LANGUAGE
219 is
220 begin
221 delete from OKC_REP_RELATIONSHIPS_TL T
222 where not exists
223 (select NULL
224 from OKC_REP_RELATIONSHIPS_B B
225 where B.RELATIONSHIP_ROLE1_ID = T.RELATIONSHIP_ROLE1_ID
226 );
227
228 update OKC_REP_RELATIONSHIPS_TL T set (
229 RELATIONSHIP_ROLE1_NAME,
230 RELATIONSHIP_ROLE2_NAME,
231 DESCRIPTION
232 ) = (select
233 B.RELATIONSHIP_ROLE1_NAME,
234 B.RELATIONSHIP_ROLE2_NAME,
235 B.DESCRIPTION
236 from OKC_REP_RELATIONSHIPS_TL B
237 where B.RELATIONSHIP_ROLE1_ID = T.RELATIONSHIP_ROLE1_ID
238 and B.LANGUAGE = T.SOURCE_LANG)
239 where (
240 T.RELATIONSHIP_ROLE1_ID,
241 T.LANGUAGE
242 ) in (select
243 SUBT.RELATIONSHIP_ROLE1_ID,
244 SUBT.LANGUAGE
245 from OKC_REP_RELATIONSHIPS_TL SUBB, OKC_REP_RELATIONSHIPS_TL SUBT
246 where SUBB.RELATIONSHIP_ROLE1_ID = SUBT.RELATIONSHIP_ROLE1_ID
247 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
248 and (SUBB.RELATIONSHIP_ROLE1_NAME <> SUBT.RELATIONSHIP_ROLE1_NAME
249 or SUBB.RELATIONSHIP_ROLE2_NAME <> SUBT.RELATIONSHIP_ROLE2_NAME
250 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
251 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
252 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
253 ));
254
255 insert into OKC_REP_RELATIONSHIPS_TL (
256 RELATIONSHIP_ROLE1_ID,
257 DESCRIPTION,
258 RELATIONSHIP_ROLE1_NAME,
259 RELATIONSHIP_ROLE2_NAME,
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.RELATIONSHIP_ROLE1_ID,
269 B.DESCRIPTION,
270 B.RELATIONSHIP_ROLE1_NAME,
271 B.RELATIONSHIP_ROLE2_NAME,
272 B.CREATED_BY,
273 B.CREATION_DATE,
274 B.LAST_UPDATED_BY,
275 B.LAST_UPDATE_DATE,
276 B.LAST_UPDATE_LOGIN,
277 L.LANGUAGE_CODE,
278 B.SOURCE_LANG
279 from OKC_REP_RELATIONSHIPS_TL B, FND_LANGUAGES L
280 where L.INSTALLED_FLAG in ('I', 'B')
281 and B.LANGUAGE = userenv('LANG')
282 and not exists
283 (select NULL
284 from OKC_REP_RELATIONSHIPS_TL T
285 where T.RELATIONSHIP_ROLE1_ID = B.RELATIONSHIP_ROLE1_ID
286 and T.LANGUAGE = L.LANGUAGE_CODE);
287 end ADD_LANGUAGE;
288
289 end OKC_REP_RELATIONSHIPS_PKG;