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