1 package body EDR_PSIG_DOC_PARAMS_PKG as
2 /* $Header: EDRDPRMB.pls 120.0.12000000.1 2007/01/18 05:52:44 appldev ship $ */
3
4 procedure INSERT_ROW (
5 X_ROWID in out NOCOPY VARCHAR2,
6 X_PARAMETER_ID in NUMBER,
7 X_DOCUMENT_ID in NUMBER,
8 X_NAME in VARCHAR2,
9 X_VALUE in VARCHAR2,
10 X_DISPLAY_NAME 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 EDR_PSIG_DOC_PARAMS_B
18 where PARAMETER_ID = X_PARAMETER_ID
19 ;
20 begin
21 insert into EDR_PSIG_DOC_PARAMS_B (
22 PARAMETER_ID,
23 DOCUMENT_ID,
24 NAME,
25 VALUE,
26 CREATION_DATE,
27 CREATED_BY,
28 LAST_UPDATE_DATE,
29 LAST_UPDATED_BY,
30 LAST_UPDATE_LOGIN
31 ) values (
32 X_PARAMETER_ID,
33 X_DOCUMENT_ID,
34 X_NAME,
35 X_VALUE,
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 EDR_PSIG_DOC_PARAMS_TL (
44 PARAMETER_ID,
45 DISPLAY_NAME,
46 LANGUAGE,
47 SOURCE_LANG
48 ) select
49 X_PARAMETER_ID,
50 X_DISPLAY_NAME,
51 L.LANGUAGE_CODE,
52 userenv('LANG')
53 from FND_LANGUAGES L
54 where L.INSTALLED_FLAG in ('I', 'B')
55 and not exists
56 (select NULL
57 from EDR_PSIG_DOC_PARAMS_TL T
58 where T.PARAMETER_ID = X_PARAMETER_ID
59 and T.LANGUAGE = L.LANGUAGE_CODE);
60
61 open c;
62 fetch c into X_ROWID;
63 if (c%notfound) then
64 close c;
65 raise no_data_found;
66 end if;
67 close c;
68
69 end INSERT_ROW;
70
71 procedure LOCK_ROW (
72 X_PARAMETER_ID in NUMBER,
73 X_DOCUMENT_ID in NUMBER,
74 X_NAME in VARCHAR2,
75 X_VALUE in VARCHAR2,
76 X_DISPLAY_NAME in VARCHAR2
77 ) is
78 cursor c is select
79 DOCUMENT_ID,
80 NAME,
81 VALUE
82 from EDR_PSIG_DOC_PARAMS_B
83 where PARAMETER_ID = X_PARAMETER_ID
84 for update of PARAMETER_ID nowait;
85 recinfo c%rowtype;
86
87 cursor c1 is select
88 DISPLAY_NAME,
89 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
90 from EDR_PSIG_DOC_PARAMS_TL
91 where PARAMETER_ID = X_PARAMETER_ID
92 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
93 for update of PARAMETER_ID nowait;
94 begin
95 open c;
96 fetch c into recinfo;
97 if (c%notfound) then
98 close c;
99 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
100 app_exception.raise_exception;
101 end if;
102 close c;
103 if ( ((recinfo.DOCUMENT_ID = X_DOCUMENT_ID)
104 OR ((recinfo.DOCUMENT_ID is null) AND (X_DOCUMENT_ID is null)))
105 AND ((recinfo.NAME = X_NAME)
106 OR ((recinfo.NAME is null) AND (X_NAME is null)))
107 AND ((recinfo.VALUE = X_VALUE)
108 OR ((recinfo.VALUE is null) AND (X_VALUE is null)))
109 ) then
110 null;
111 else
112 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
113 app_exception.raise_exception;
114 end if;
115
116 for tlinfo in c1 loop
117 if (tlinfo.BASELANG = 'Y') then
118 if ( ((tlinfo.DISPLAY_NAME = X_DISPLAY_NAME)
119 OR ((tlinfo.DISPLAY_NAME is null) AND (X_DISPLAY_NAME is null)))
120 ) then
121 null;
122 else
123 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
124 app_exception.raise_exception;
125 end if;
126 end if;
127 end loop;
128 return;
129 end LOCK_ROW;
130
131 procedure UPDATE_ROW (
132 X_PARAMETER_ID in NUMBER,
133 X_DOCUMENT_ID in NUMBER,
134 X_NAME in VARCHAR2,
135 X_VALUE in VARCHAR2,
136 X_DISPLAY_NAME in VARCHAR2,
137 X_LAST_UPDATE_DATE in DATE,
138 X_LAST_UPDATED_BY in NUMBER,
139 X_LAST_UPDATE_LOGIN in NUMBER
140 ) is
141 begin
142 update EDR_PSIG_DOC_PARAMS_B set
143 DOCUMENT_ID = X_DOCUMENT_ID,
144 NAME = X_NAME,
145 VALUE = X_VALUE,
146 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
147 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
148 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
149 where PARAMETER_ID = X_PARAMETER_ID;
150
151 if (sql%notfound) then
152 raise no_data_found;
153 end if;
154
155 update EDR_PSIG_DOC_PARAMS_TL set
156 DISPLAY_NAME = X_DISPLAY_NAME,
157 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
158 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
159 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
160 SOURCE_LANG = userenv('LANG')
161 where PARAMETER_ID = X_PARAMETER_ID
162 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
163
164 if (sql%notfound) then
165 raise no_data_found;
166 end if;
167 end UPDATE_ROW;
168
169 procedure DELETE_ROW (
170 X_PARAMETER_ID in NUMBER
171 ) is
172 begin
173 delete from EDR_PSIG_DOC_PARAMS_TL
174 where PARAMETER_ID = X_PARAMETER_ID;
175
176 if (sql%notfound) then
177 raise no_data_found;
178 end if;
179
180 delete from EDR_PSIG_DOC_PARAMS_B
181 where PARAMETER_ID = X_PARAMETER_ID;
182
183 if (sql%notfound) then
184 raise no_data_found;
185 end if;
186 end DELETE_ROW;
187
188 procedure ADD_LANGUAGE
189 is
190 begin
191 delete from EDR_PSIG_DOC_PARAMS_TL T
192 where not exists
193 (select NULL
194 from EDR_PSIG_DOC_PARAMS_B B
195 where B.PARAMETER_ID = T.PARAMETER_ID
196 );
197
198 update EDR_PSIG_DOC_PARAMS_TL T set (
199 DISPLAY_NAME
200 ) = (select
201 B.DISPLAY_NAME
202 from EDR_PSIG_DOC_PARAMS_TL B
203 where B.PARAMETER_ID = T.PARAMETER_ID
204 and B.LANGUAGE = T.SOURCE_LANG)
205 where (
206 T.PARAMETER_ID,
207 T.LANGUAGE
208 ) in (select
209 SUBT.PARAMETER_ID,
210 SUBT.LANGUAGE
211 from EDR_PSIG_DOC_PARAMS_TL SUBB, EDR_PSIG_DOC_PARAMS_TL SUBT
212 where SUBB.PARAMETER_ID = SUBT.PARAMETER_ID
213 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
214 and (SUBB.DISPLAY_NAME <> SUBT.DISPLAY_NAME
215 or (SUBB.DISPLAY_NAME is null and SUBT.DISPLAY_NAME is not null)
216 or (SUBB.DISPLAY_NAME is not null and SUBT.DISPLAY_NAME is null)
217 ));
218
219 insert into EDR_PSIG_DOC_PARAMS_TL (
220 PARAMETER_ID,
221 DISPLAY_NAME,
222 LANGUAGE,
223 SOURCE_LANG
224 ) select
225 B.PARAMETER_ID,
226 B.DISPLAY_NAME,
227 L.LANGUAGE_CODE,
228 B.SOURCE_LANG
229 from EDR_PSIG_DOC_PARAMS_TL B, FND_LANGUAGES L
230 where L.INSTALLED_FLAG in ('I', 'B')
231 and B.LANGUAGE = userenv('LANG')
232 and not exists
233 (select NULL
234 from EDR_PSIG_DOC_PARAMS_TL T
235 where T.PARAMETER_ID = B.PARAMETER_ID
236 and T.LANGUAGE = L.LANGUAGE_CODE);
237 end ADD_LANGUAGE;
238
239 end EDR_PSIG_DOC_PARAMS_PKG;
[Home] [Help]