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