1 package body PA_REPORT_TYPES_PUB as
2 /* $Header: PARTYPPB.pls 120.1 2005/08/19 17:02:31 mwasowic noship $ */
3
4 PROCEDURE CREATE_REPORT_TYPE
5 (
6 p_api_version IN NUMBER := 1.0,
7 p_init_msg_list IN VARCHAR2 := 'T',
8 p_commit IN VARCHAR2 := 'F',
9 p_validate_only IN VARCHAR2 := 'F',
10 p_max_msg_count IN NUMBER := 100,
11 P_NAME IN VARCHAR2,
12 P_PAGE_ID IN NUMBER,
13 P_PAGE_LAYOUT IN VARCHAR2 := '^',
14 P_OVERRIDE_PAGE_LAYOUT IN VARCHAR2 := 'N',
15 P_DESCRIPTION IN VARCHAR2 := '',
16 P_GENERATION_METHOD IN VARCHAR2 := '',
17 P_START_DATE_ACTIVE IN DATE := trunc(sysdate),
18 P_END_DATE_ACTIVE IN DATE := to_date(null),
19
20 x_report_type_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
21 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
22 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
23 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
24 ) IS
25
26 l_page_id number;
27 l_dummy varchar2(1) := 'N';
28 l_msg_index_out number;
29 BEGIN
30 -- Initialize the Error Stack
31 PA_DEBUG.init_err_stack('PA_REPORT_TYPES_PUB.Create_Report_Type');
32
33 -- Initialize the return status to success
34 x_return_status := 'S';
35
36 if nvl(p_init_msg_list,'T') = 'T' then
37 fnd_msg_pub.initialize;
38 end if;
39
40 -- Issue API savepoint if the transaction is to be committed
41 IF p_commit = 'T' THEN
42 SAVEPOINT CREATE_REPORT_TYPE;
43 END IF;
44
45 -- check the mandatory report_name
46 IF (p_name IS NULL) then
47 PA_UTILS.Add_Message( p_app_short_name => 'PA'
48 ,p_msg_name => 'PA_REPORT_NAME_INV');
49 x_return_status := 'E';
50 else
51 begin
52 select 'Y'
53 into l_dummy
54 from pa_report_types
55 where upper(name) = upper(p_name);
56 exception when no_data_found then
57 null;
58 end;
59 if l_dummy = 'Y' then
60 PA_UTILS.Add_Message( p_app_short_name => 'PA'
61 ,p_msg_name => 'PA_REPORT_NAME_DUPLICATE');
62 x_return_status := 'E';
63 end if;
64 End if;
65
66 -- check the page id is not null
67 IF (p_page_id IS NULL)THEN
68 If (p_page_layout is null or p_page_layout = '^') then
69 PA_UTILS.Add_Message( p_app_short_name => 'PA'
70 ,p_msg_name => 'PA_PAGE_LAYOUT_NAME_INV');
71 x_return_status := 'E';
72 else
73 --- get page_id from page layout
74 pa_report_Types_utils.get_page_id_from_layout(p_init_msg_list => 'F',
75 p_page_layout => p_page_layout,
76 x_page_id => l_page_id,
77 x_return_status => x_return_status,
78 x_msg_count => x_msg_count,
79 x_msg_data => x_msg_data);
80 end if;
81 else
82 l_page_id := p_page_id;
83 End If;
84
85 -- check the end date and start date
86 IF (p_end_date_active IS NOT NULL AND p_end_date_active < p_start_date_active) THEN
87 PA_UTILS.Add_Message( p_app_short_name => 'PA'
88 ,p_msg_name => 'PA_EFFECTIVE_ED_DATE_INV');
89 x_return_status := 'E';
90 End If;
91
92
93 fnd_msg_pub.count_and_get(p_count => x_msg_count,
94 p_data => x_msg_data);
95
96 IF x_msg_count = 1 THEN
97 pa_interface_utils_pub.get_messages ( p_encoded => FND_API.G_TRUE
98 ,p_msg_index => 1
99 ,p_data => x_msg_data
100 ,p_msg_index_out => l_msg_index_out
101 );
102 End If;
103 if (x_msg_count > 0) then
104 x_return_status := 'E';
105 end if;
106
107 IF (p_validate_only <> 'T' AND x_return_status = 'S') then
108
109 pa_report_types_pkg.Insert_Row
110 (
111 p_name => p_name,
112 p_page_id => l_page_id,
113 p_override_page_layout => p_override_page_layout,
114 p_description => p_description,
115 p_generation_method => p_generation_method,
116 p_start_date_active => p_start_date_active,
117 p_end_date_active => p_end_date_active,
118 x_report_type_id => x_report_type_id,
119 p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
120 p_CREATED_BY => FND_GLOBAL.USER_ID,
121 p_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID
122 );
123
124 END IF;
125
126
127 -- Commit if the flag is set and there is no error
128 IF (p_commit = 'T' AND x_return_status = 'S')THEN
129 COMMIT;
130 END IF;
131
132
133 -- Reset the error stack when returning to the calling program
134 PA_DEBUG.Reset_Err_Stack;
135
136
137 EXCEPTION
138 WHEN OTHERS THEN
139 IF p_commit = FND_API.G_TRUE THEN
140 ROLLBACK TO CREATE_REPORT_TYPE;
141 END IF;
142 x_return_status := 'U' ;
143 RAISE; -- This is optional depending on the needs
144
145 END create_report_type;
146
147
148
149 PROCEDURE Update_Report_Type
150 (
151 p_api_version IN NUMBER := 1.0,
152 p_init_msg_list IN VARCHAR2 := 'T',
153 p_commit IN VARCHAR2 := 'F',
154 p_validate_only IN VARCHAR2 := 'T',
155 p_max_msg_count IN NUMBER := 100,
156 P_REPORT_TYPE_ID IN NUMBER,
157 P_NAME IN VARCHAR2 := '^',
158 P_PAGE_ID IN NUMBER := -99,
159 P_PAGE_LAYOUT IN VARCHAR2 := '^',
160 P_OVERRIDE_PAGE_LAYOUT IN VARCHAR2 := '^',
161 P_DESCRIPTION IN VARCHAR2 := '^',
162 P_GENERATION_METHOD IN VARCHAR2 := '',
163 P_START_DATE_ACTIVE IN DATE := TO_DATE('01/01/4712','DD/MM/YYYY'),
164 P_END_DATE_ACTIVE IN DATE := TO_DATE('01/01/4712','DD/MM/YYYY'),
165 P_RECORD_VERSION_NUMBER IN NUMBER,
166
167 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
168 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
169 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
170 ) IS
171
172 l_page_id number;
173 l_dummy varchar2(1) := 'N';
174 l_start_date_active date;
175 l_end_date_active date;
176 l_created_by number;
177 l_msg_index_out number;
178
179 BEGIN
180 -- Initialize the Error Stack
181 PA_DEBUG.init_err_stack('PA_REPORT_TYPES_PUB.Update_Report_Type');
182
183 -- Initialize the return status to success
184 x_return_status := 'S';
185
186 if nvl(p_init_msg_list,'T') = 'T' then
187 fnd_msg_pub.initialize;
188 end if;
189
190 -- Issue API savepoint if the transaction is to be committed
191 IF p_commit = 'T' THEN
192 SAVEPOINT UPDATE_REPORT_TYPE;
193 END IF;
194
195 /* -- check if the record is seeded or not
196 begin
197 select created_by
198 into l_created_by
199 from pa_report_Types
200 where report_Type_id = p_report_type_id;
201 exception when others then
202 null;
203 end;
204
205 if (l_created_by = 1) then
206 PA_UTILS.Add_Message( p_app_short_name => 'PA'
207 ,p_msg_name => 'PA_REPORT_TYPE_SEED');
208 x_return_status := 'E';
209 return;
210 end if; */
211
212 -- check the mandatory report_name
213 IF (p_name IS NULL or p_name = '^') then
214 PA_UTILS.Add_Message( p_app_short_name => 'PA'
215 ,p_msg_name => 'PA_REPORT_NAME_INV');
216 x_return_status := 'E';
217 else
218 begin
219 select 'Y'
220 into l_dummy
221 from pa_report_types
222 where upper(name) = upper(p_name)
223 and report_type_id <> p_report_type_id;
224 exception when no_data_found then
225 null;
226 end;
227 if l_dummy = 'Y' then
228 PA_UTILS.Add_Message( p_app_short_name => 'PA'
229 ,p_msg_name => 'PA_REPORT_NAME_DUPLICATE');
230 x_return_status := 'E';
231 end if;
232 End if;
233
234 -- check the page id is not null
235 IF (p_page_id IS NULL or p_page_id = -99)THEN
236 If (p_page_layout is null or p_page_layout = '^') then
237 PA_UTILS.Add_Message( p_app_short_name => 'PA'
238 ,p_msg_name => 'PA_PAGE_LAYOUT_NAME_INV');
239 x_return_status := 'E';
240 else
241 --- get page_id from page layout
242 pa_report_Types_utils.get_page_id_from_layout(p_init_msg_list => 'F',
243 p_page_layout => p_page_layout,
244 x_page_id => l_page_id,
245 x_return_status => x_return_status,
246 x_msg_count => x_msg_count,
247 x_msg_data => x_msg_data);
248 end if;
249 else
250 l_page_id := p_page_id;
251 End If;
252
253 -- check the end date and start date
254 If (p_start_date_active is null or p_start_date_active = TO_DATE('01/01/4712','DD/MM/YYYY')) then
255 l_start_date_active := trunc(sysdate);
256 else
257 l_start_date_active := p_start_date_active;
258 end if;
259
260 If (p_end_date_active is null or p_end_date_active = TO_DATE('01/01/4712','DD/MM/YYYY')) then
261 l_end_date_active := to_date(null);
262 else
263 l_end_date_active := p_end_date_active;
264 end if;
265
266 IF (l_end_date_active IS NOT NULL AND l_end_date_active < l_start_date_active) THEN
267 PA_UTILS.Add_Message( p_app_short_name => 'PA'
268 ,p_msg_name => 'PA_EFFECTIVE_ED_DATE_INV');
269 x_return_status := 'E';
270 End If;
271
272 fnd_msg_pub.count_and_get(p_count => x_msg_count,
273 p_data => x_msg_data);
274
275 IF x_msg_count = 1 THEN
276 pa_interface_utils_pub.get_messages ( p_encoded => FND_API.G_TRUE
277 ,p_msg_index => 1
278 ,p_data => x_msg_data
279 ,p_msg_index_out => l_msg_index_out
280 );
281 End If;
282 if (x_msg_count > 0) then
283 x_return_status := 'E';
284 end if;
285
286 IF (p_validate_only <> 'T' AND x_return_status = 'S') then
287
288 pa_report_types_pkg.Update_Row
289 (
290 p_name => p_name,
291 p_page_id => l_page_id,
292 p_override_page_layout => p_override_page_layout,
293 p_description => p_description,
294 p_generation_method => p_generation_method,
295 p_start_date_active => l_start_date_active,
296 p_end_date_active => l_end_date_active,
297 p_report_type_id => p_report_type_id,
298 p_record_version_number => p_record_version_number,
299 p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
300 p_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID,
301 x_return_status => x_return_status
302 );
303
304 END IF;
305
306
307 -- Commit if the flag is set and there is no error
308 IF (p_commit = 'T' AND x_return_status = 'S')THEN
309 COMMIT;
310 END IF;
311
312 -- Reset the error stack when returning to the calling program
313 PA_DEBUG.Reset_Err_Stack;
314
315 EXCEPTION
316 WHEN OTHERS THEN
317 IF p_commit = FND_API.G_TRUE THEN
318 ROLLBACK TO UPDATE_REPORT_TYPE;
319 END IF;
320 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
321 RAISE; -- This is optional depending on the needs
322
323 END update_report_type;
324
325
326
327 PROCEDURE Delete_Report_Type
328 (
329 p_api_version IN NUMBER := 1.0,
330 p_init_msg_list IN VARCHAR2 := 'T',
331 p_commit IN VARCHAR2 := 'F',
332 p_validate_only IN VARCHAR2 := 'T',
333 p_max_msg_count IN NUMBER := 100,
334
335 p_report_type_id IN NUMBER,
336 p_record_version_number IN NUMBER ,
337
338 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
339 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
340 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
341 ) IS
342
343 l_dummy varchar2(1) := 'N';
344 l_created_by number;
345 l_msg_index_out number;
346 BEGIN
347 -- Initialize the Error Stack
348 PA_DEBUG.init_err_stack('PA_REPORT_TYPES_PUB.Delete_Report_Type');
349
350 -- Initialize the return status to success
351 x_return_status := 'S';
352
353 if nvl(p_init_msg_list,'T') = 'T' then
354 fnd_msg_pub.initialize;
355 end if;
356
357 -- Issue API savepoint if the transaction is to be committed
358 IF p_commit = 'T' THEN
359 SAVEPOINT DELETE_REPORT_TYPE;
360 END IF;
361
362 -- check if the record is seeded or not
363 begin
364 select created_by
365 into l_created_by
366 from pa_report_Types
367 where report_Type_id = p_report_type_id;
368 exception when others then
369 null;
370 end;
371
372 if (l_created_by = 1) then
373 PA_UTILS.Add_Message( p_app_short_name => 'PA'
374 ,p_msg_name => 'PA_REPORT_TYPE_SEED');
375 x_return_status := 'E';
376 return;
377 end if;
378
379 begin
380 select 'Y'
381 into l_dummy
382 from pa_object_page_layouts ----pa_progress_report_vers
383 where report_type_id = p_report_type_id;
384 exception when no_data_found then
385 null;
386 when too_many_rows then
387 l_dummy := 'Y';
388 end;
389 if (l_dummy = 'Y') then
390 PA_UTILS.Add_Message( p_app_short_name => 'PA'
391 ,p_msg_name => 'PA_REPORT_TYPE_IN_USE');
392 x_return_status := 'E';
393 else
394 if (p_validate_only <> 'T' and x_return_status = 'S') then
395 pa_report_Types_pkg.delete_row(P_REPORT_TYPE_ID => p_report_Type_id,
396 P_RECORD_VERSION_NUMBER => P_RECORD_VERSION_NUMBER,
397 x_return_status => x_return_status);
398 end if;
399 end if;
400
401 -- Commit if the flag is set and there is no error
402 IF (p_commit = 'T' AND x_return_status = 'S')THEN
403 COMMIT;
404 END IF;
405
406 fnd_msg_pub.count_and_get(p_count => x_msg_count,
407 p_data => x_msg_data);
408
409 IF x_msg_count = 1 THEN
410 pa_interface_utils_pub.get_messages ( p_encoded => FND_API.G_TRUE
411 ,p_msg_index => 1
412 ,p_data => x_msg_data
413 ,p_msg_index_out => l_msg_index_out
414 );
415 End If;
416
417 -- Reset the error stack when returning to the calling program
418 PA_DEBUG.Reset_Err_Stack;
419
420 EXCEPTION WHEN OTHERS THEN
421 IF p_commit = FND_API.G_TRUE THEN
422 ROLLBACK TO DELETE_REPORT_TYPE;
423 END IF;
424 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
425 RAISE; -- This is optional depending on the needs
426
427 END delete_report_type;
428
429 END PA_REPORT_TYPES_PUB;
430
[Home] [Help]