1 package body PN_SET_TYPES_PKG As
2 -- $Header: PNTSTTYB.pls 115.14 2004/04/09 21:49:07 ftanudja ship $
3
4 procedure INSERT_ROW (
5 X_ROWID in out NOCOPY VARCHAR2,
6 X_SET_ID in out NOCOPY NUMBER,
7 X_SET_NAME in VARCHAR2,
8 X_DESCRIPTION in VARCHAR2,
9 X_CREATION_DATE in DATE,
10 X_CREATED_BY in NUMBER,
11 X_LAST_UPDATE_DATE in DATE,
12 X_LAST_UPDATED_BY in NUMBER,
13 X_LAST_UPDATE_LOGIN in NUMBER
14 ) IS
15
16 cursor C is
17 select ROWID
18 from PN_SET_TYPES
19 where SET_ID = X_SET_ID
20 and LANGUAGE = userenv('LANG');
21
22 begin
23
24 IF X_SET_ID is null then
25
26 select PN_SET_TYPES_s.nextval
27 into X_SET_ID
28 from dual;
29
30 END IF;
31
32 insert into PN_SET_TYPES (
33 SET_ID,
34 LAST_UPDATE_DATE,
35 LAST_UPDATED_BY,
36 CREATION_DATE,
37 CREATED_BY,
38 LAST_UPDATE_LOGIN,
39 SET_NAME,
40 DESCRIPTION,
41 LANGUAGE,
42 SOURCE_LANG
43 )
44 select
45 X_SET_ID,
46 X_LAST_UPDATE_DATE,
47 X_LAST_UPDATED_BY,
48 X_CREATION_DATE,
49 X_CREATED_BY,
50 X_LAST_UPDATE_LOGIN,
51 X_SET_NAME,
52 X_DESCRIPTION,
53 L.LANGUAGE_CODE,
54 userenv('LANG')
55 from FND_LANGUAGES L
56 where L.INSTALLED_FLAG in ('I', 'B')
57 and not exists (
58 select NULL
59 from PN_SET_TYPES T
60 where T.SET_ID = X_SET_ID
61 and T.LANGUAGE = L.LANGUAGE_CODE
62 );
63
64 open c;
65 fetch c into X_ROWID;
66 if (c%notfound) then
67 close c;
68 raise no_data_found;
69 end if;
70 close c;
71
72 end INSERT_ROW;
73
74 procedure LOCK_ROW (
75 X_SET_ID in NUMBER,
76 X_SET_NAME in VARCHAR2,
77 X_DESCRIPTION in VARCHAR2
78 ) IS
79 cursor c1 is
80 select *
81 from PN_SET_TYPES
82 where SET_ID = X_SET_ID
83 and LANGUAGE = userenv('LANG')
84 for update of SET_ID nowait;
85
86 tlinfo c1%rowtype;
87
88 begin
89
90 open c1;
91 fetch c1 into tlinfo;
92 if (c1%notfound) then
93 close c1;
94 return;
95 end if;
96 close c1;
97
98 if ( (tlinfo.SET_NAME = X_SET_NAME)
99 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
100 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
101 ) then
102 null;
103 else
104 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
105 app_exception.raise_exception;
106 end if;
107 return;
108
109 end LOCK_ROW;
110
111 procedure UPDATE_ROW (
112 X_SET_ID in NUMBER,
113 X_SET_NAME in VARCHAR2,
114 X_DESCRIPTION in VARCHAR2,
115 X_LAST_UPDATE_DATE in DATE,
116 X_LAST_UPDATED_BY in NUMBER,
117 X_LAST_UPDATE_LOGIN in NUMBER
118 ) IS
119 begin
120
121 update PN_SET_TYPES
122 set
123 SET_NAME = X_SET_NAME,
124 DESCRIPTION = X_DESCRIPTION,
125 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
126 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
127 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
128 SOURCE_LANG = userenv('LANG')
129 where SET_ID = X_SET_ID
130 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
131
132 if (sql%notfound) then
133 raise no_data_found;
134 end if;
135
136 end UPDATE_ROW;
137
138 procedure DELETE_ROW (
139 X_SET_ID in NUMBER
140 ) is
141 begin
142
143 delete from PN_SET_TYPES
144 where SET_ID = X_SET_ID;
145
146 if (sql%notfound) then
147 raise no_data_found;
148 end if;
149
150 end DELETE_ROW;
151
152 --------------------------------------------------------------------------
153 -- PROCEDURE: add_language
154 -- HISTORY
155 -- 08-APR-04 ftanudja o Replace userenv('lang') w/ b.source_lang. #3537691.
156 --------------------------------------------------------------------------
157
158 procedure ADD_LANGUAGE is
159 begin
160
161 update PN_SET_TYPES T
162 set (
163 SET_NAME,
164 DESCRIPTION
165 ) = (
166 select B.SET_NAME,
167 B.DESCRIPTION
168 from PN_SET_TYPES B
169 where B.SET_ID = T.SET_ID
170 and B.LANGUAGE = T.SOURCE_LANG
171 )
172 where (
173 T.SET_ID,
174 T.LANGUAGE
175 ) in (
176 select SUBT.SET_ID,
177 SUBT.LANGUAGE
178 from PN_SET_TYPES SUBB,
179 PN_SET_TYPES SUBT
180 where SUBB.SET_ID = SUBT.SET_ID
181 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
182 and (SUBB.SET_NAME <> SUBT.SET_NAME or
183 SUBB.DESCRIPTION <> SUBT.DESCRIPTION or
184 (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null) or
185 (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
186 )
187 );
188
189 insert into PN_SET_TYPES (
190 SET_ID,
191 LAST_UPDATE_DATE,
192 LAST_UPDATED_BY,
193 CREATION_DATE,
194 CREATED_BY,
195 LAST_UPDATE_LOGIN,
196 SET_NAME,
197 DESCRIPTION,
198 LANGUAGE,
199 SOURCE_LANG
200 )
201 select
202 B.SET_ID,
203 B.LAST_UPDATE_DATE,
204 B.LAST_UPDATED_BY,
205 B.CREATION_DATE,
206 B.CREATED_BY,
207 B.LAST_UPDATE_LOGIN,
208 B.SET_NAME,
209 B.DESCRIPTION,
210 L.LANGUAGE_CODE,
211 B.SOURCE_LANG
212 from PN_SET_TYPES B,
213 FND_LANGUAGES L
214 where L.INSTALLED_FLAG in ('I', 'B')
215 and B.LANGUAGE = userenv('LANG')
216 and not exists (
217 select NULL
218 from PN_SET_TYPES T
219 where T.SET_ID = B.SET_ID
220 and T.LANGUAGE = L.LANGUAGE_CODE
221 );
222 end ADD_LANGUAGE;
223
224 end PN_SET_TYPES_PKG;
[Home] [Help]