1 package body QPR_REPORT_TYPE_HDRS_PKG as
2 /* $Header: QPRURTHB.pls 120.0 2007/12/24 20:06:41 vinnaray noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_REPORT_TYPE_HEADER_ID in NUMBER,
6 X_REPORT_DOMAIN_CODE in VARCHAR2,
7 X_REPORT_LINE_MERGE_CODE in VARCHAR2,
8 X_USER_ID in NUMBER,
9 X_PLAN_ID in NUMBER,
10 X_REPORT_OPTIONS in CLOB,
11 X_REPORT_TYPE_NAME in VARCHAR2,
12 X_REPORT_TYPE_TITLE 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 QPR_REPORT_TYPE_HDRS_B
20 where REPORT_TYPE_HEADER_ID = X_REPORT_TYPE_HEADER_ID
21 ;
22 begin
23 insert into QPR_REPORT_TYPE_HDRS_B (
24 REPORT_TYPE_HEADER_ID,
25 REPORT_DOMAIN_CODE,
26 REPORT_LINE_MERGE_CODE,
27 USER_ID,
28 PLAN_ID,
29 REPORT_OPTIONS,
30 CREATION_DATE,
31 CREATED_BY,
32 LAST_UPDATE_DATE,
33 LAST_UPDATED_BY,
34 LAST_UPDATE_LOGIN
35 ) values (
36 X_REPORT_TYPE_HEADER_ID,
37 X_REPORT_DOMAIN_CODE,
38 X_REPORT_LINE_MERGE_CODE,
39 X_USER_ID,
40 X_PLAN_ID,
41 X_REPORT_OPTIONS,
42 X_CREATION_DATE,
43 X_CREATED_BY,
44 X_LAST_UPDATE_DATE,
45 X_LAST_UPDATED_BY,
46 X_LAST_UPDATE_LOGIN
47 );
48
49 insert into QPR_REPORT_TYPE_HDRS_TL (
50 REPORT_TYPE_HEADER_ID,
51 REPORT_TYPE_NAME,
52 REPORT_TYPE_TITLE,
53 CREATION_DATE,
54 CREATED_BY,
55 LAST_UPDATE_DATE,
56 LAST_UPDATED_BY,
57 LAST_UPDATE_LOGIN,
58 LANGUAGE,
59 SOURCE_LANG
60 ) select
61 X_REPORT_TYPE_HEADER_ID,
62 X_REPORT_TYPE_NAME,
63 X_REPORT_TYPE_TITLE,
64 X_CREATION_DATE,
65 X_CREATED_BY,
66 X_LAST_UPDATE_DATE,
67 X_LAST_UPDATED_BY,
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 QPR_REPORT_TYPE_HDRS_TL T
76 where T.REPORT_TYPE_HEADER_ID = X_REPORT_TYPE_HEADER_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_REPORT_TYPE_HEADER_ID in NUMBER,
91 X_REPORT_DOMAIN_CODE in VARCHAR2,
92 X_REPORT_LINE_MERGE_CODE in VARCHAR2,
93 X_USER_ID in NUMBER,
94 X_PLAN_ID in NUMBER,
95 X_REPORT_OPTIONS in CLOB,
96 X_REPORT_TYPE_NAME in VARCHAR2,
97 X_REPORT_TYPE_TITLE in VARCHAR2
98 ) is
99 cursor c is select
100 REPORT_DOMAIN_CODE,
101 REPORT_LINE_MERGE_CODE,
102 USER_ID,
103 PLAN_ID,
104 REPORT_OPTIONS
105 from QPR_REPORT_TYPE_HDRS_B
106 where REPORT_TYPE_HEADER_ID = X_REPORT_TYPE_HEADER_ID
107 for update of REPORT_TYPE_HEADER_ID nowait;
108 recinfo c%rowtype;
109
110 cursor c1 is select
111 REPORT_TYPE_NAME,
112 REPORT_TYPE_TITLE,
113 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
114 from QPR_REPORT_TYPE_HDRS_TL
115 where REPORT_TYPE_HEADER_ID = X_REPORT_TYPE_HEADER_ID
116 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
117 for update of REPORT_TYPE_HEADER_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.REPORT_DOMAIN_CODE = X_REPORT_DOMAIN_CODE)
128 OR ((recinfo.REPORT_DOMAIN_CODE is null) AND (X_REPORT_DOMAIN_CODE is null)))
129 AND ((recinfo.REPORT_LINE_MERGE_CODE = X_REPORT_LINE_MERGE_CODE)
130 OR ((recinfo.REPORT_LINE_MERGE_CODE is null) AND (X_REPORT_LINE_MERGE_CODE is null)))
131 AND ((recinfo.USER_ID = X_USER_ID)
132 OR ((recinfo.USER_ID is null) AND (X_USER_ID is null)))
133 AND ((recinfo.PLAN_ID = X_PLAN_ID)
134 OR ((recinfo.PLAN_ID is null) AND (X_PLAN_ID is null)))
135 AND ((recinfo.REPORT_OPTIONS = X_REPORT_OPTIONS)
136 OR ((recinfo.REPORT_OPTIONS is null) AND (X_REPORT_OPTIONS is null)))
137 ) then
138 null;
139 else
140 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
141 app_exception.raise_exception;
142 end if;
143
144 for tlinfo in c1 loop
145 if (tlinfo.BASELANG = 'Y') then
146 if ( ((tlinfo.REPORT_TYPE_NAME = X_REPORT_TYPE_NAME)
147 OR ((tlinfo.REPORT_TYPE_NAME is null) AND (X_REPORT_TYPE_NAME is null)))
148 AND ((tlinfo.REPORT_TYPE_TITLE = X_REPORT_TYPE_TITLE)
149 OR ((tlinfo.REPORT_TYPE_TITLE is null) AND (X_REPORT_TYPE_TITLE is null)))
150 ) then
151 null;
152 else
153 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
154 app_exception.raise_exception;
155 end if;
156 end if;
157 end loop;
158 return;
159 end LOCK_ROW;
160
161 procedure UPDATE_ROW (
162 X_REPORT_TYPE_HEADER_ID in NUMBER,
163 X_REPORT_DOMAIN_CODE in VARCHAR2,
164 X_REPORT_LINE_MERGE_CODE in VARCHAR2,
165 X_USER_ID in NUMBER,
166 X_PLAN_ID in NUMBER,
167 X_REPORT_OPTIONS in CLOB,
168 X_REPORT_TYPE_NAME in VARCHAR2,
169 X_REPORT_TYPE_TITLE in VARCHAR2,
170 X_LAST_UPDATE_DATE in DATE,
171 X_LAST_UPDATED_BY in NUMBER,
172 X_LAST_UPDATE_LOGIN in NUMBER
173 ) is
174 begin
175 update QPR_REPORT_TYPE_HDRS_B set
176 REPORT_DOMAIN_CODE = X_REPORT_DOMAIN_CODE,
177 REPORT_LINE_MERGE_CODE = X_REPORT_LINE_MERGE_CODE,
178 USER_ID = X_USER_ID,
179 PLAN_ID = X_PLAN_ID,
180 REPORT_OPTIONS = X_REPORT_OPTIONS,
181 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
182 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
183 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
184 where REPORT_TYPE_HEADER_ID = X_REPORT_TYPE_HEADER_ID;
185
186 if (sql%notfound) then
187 raise no_data_found;
188 end if;
189
190 update QPR_REPORT_TYPE_HDRS_TL set
191 REPORT_TYPE_NAME = X_REPORT_TYPE_NAME,
192 REPORT_TYPE_TITLE = X_REPORT_TYPE_TITLE,
193 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
194 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
195 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
196 SOURCE_LANG = userenv('LANG')
197 where REPORT_TYPE_HEADER_ID = X_REPORT_TYPE_HEADER_ID
198 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
199
200 if (sql%notfound) then
201 raise no_data_found;
202 end if;
203 end UPDATE_ROW;
204
205 procedure DELETE_ROW (
206 X_REPORT_TYPE_HEADER_ID in NUMBER
207 ) is
208 begin
209 delete from QPR_REPORT_TYPE_HDRS_TL
210 where REPORT_TYPE_HEADER_ID = X_REPORT_TYPE_HEADER_ID;
211
212 if (sql%notfound) then
213 raise no_data_found;
214 end if;
215
216 delete from QPR_REPORT_TYPE_HDRS_B
217 where REPORT_TYPE_HEADER_ID = X_REPORT_TYPE_HEADER_ID;
218
219 if (sql%notfound) then
220 raise no_data_found;
221 end if;
222 end DELETE_ROW;
223
224 procedure ADD_LANGUAGE
225 is
226 begin
227 delete from QPR_REPORT_TYPE_HDRS_TL T
228 where not exists
229 (select NULL
230 from QPR_REPORT_TYPE_HDRS_B B
231 where B.REPORT_TYPE_HEADER_ID = T.REPORT_TYPE_HEADER_ID
232 );
233
234 update QPR_REPORT_TYPE_HDRS_TL T set (
235 REPORT_TYPE_NAME,
236 REPORT_TYPE_TITLE
237 ) = (select
238 B.REPORT_TYPE_NAME,
239 B.REPORT_TYPE_TITLE
240 from QPR_REPORT_TYPE_HDRS_TL B
241 where B.REPORT_TYPE_HEADER_ID = T.REPORT_TYPE_HEADER_ID
242 and B.LANGUAGE = T.SOURCE_LANG)
243 where (
244 T.REPORT_TYPE_HEADER_ID,
245 T.LANGUAGE
246 ) in (select
247 SUBT.REPORT_TYPE_HEADER_ID,
248 SUBT.LANGUAGE
249 from QPR_REPORT_TYPE_HDRS_TL SUBB, QPR_REPORT_TYPE_HDRS_TL SUBT
250 where SUBB.REPORT_TYPE_HEADER_ID = SUBT.REPORT_TYPE_HEADER_ID
251 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
252 and (SUBB.REPORT_TYPE_NAME <> SUBT.REPORT_TYPE_NAME
253 or (SUBB.REPORT_TYPE_NAME is null and SUBT.REPORT_TYPE_NAME is not null)
254 or (SUBB.REPORT_TYPE_NAME is not null and SUBT.REPORT_TYPE_NAME is null)
255 or SUBB.REPORT_TYPE_TITLE <> SUBT.REPORT_TYPE_TITLE
256 or (SUBB.REPORT_TYPE_TITLE is null and SUBT.REPORT_TYPE_TITLE is not null)
257 or (SUBB.REPORT_TYPE_TITLE is not null and SUBT.REPORT_TYPE_TITLE is null)
258 ));
259
260 insert into QPR_REPORT_TYPE_HDRS_TL (
261 REPORT_TYPE_HEADER_ID,
262 REPORT_TYPE_NAME,
263 REPORT_TYPE_TITLE,
264 CREATION_DATE,
265 CREATED_BY,
266 LAST_UPDATE_DATE,
267 LAST_UPDATED_BY,
268 LAST_UPDATE_LOGIN,
269 LANGUAGE,
270 SOURCE_LANG
271 ) select /*+ ORDERED */
272 B.REPORT_TYPE_HEADER_ID,
273 B.REPORT_TYPE_NAME,
274 B.REPORT_TYPE_TITLE,
275 B.CREATION_DATE,
276 B.CREATED_BY,
277 B.LAST_UPDATE_DATE,
278 B.LAST_UPDATED_BY,
279 B.LAST_UPDATE_LOGIN,
280 L.LANGUAGE_CODE,
281 B.SOURCE_LANG
282 from QPR_REPORT_TYPE_HDRS_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_REPORT_TYPE_HDRS_TL T
288 where T.REPORT_TYPE_HEADER_ID = B.REPORT_TYPE_HEADER_ID
289 and T.LANGUAGE = L.LANGUAGE_CODE);
290 end ADD_LANGUAGE;
291
292 end QPR_REPORT_TYPE_HDRS_PKG;
[Home] [Help]