[Home] [Help]
PACKAGE BODY: APPS.RG_REPORT_REQUESTS_PKG
Source
1 PACKAGE BODY RG_REPORT_REQUESTS_PKG as
2 /* $Header: rgirreqb.pls 120.5 2003/04/29 00:47:54 djogg ship $ */
3
4
5 /* Name: init
6 * Desc: Initialize some variables.
7 *
8 * History:
9 * 11/27/95 S Rahman Created
10 */
11
12 PROCEDURE init(
13 ProductVersion IN OUT NOCOPY VARCHAR2,
14 ABFlag IN OUT NOCOPY VARCHAR2,
15 LedgerId NUMBER,
16 PeriodName VARCHAR2,
17 PeriodStartDate IN OUT NOCOPY DATE,
18 PeriodEndDate IN OUT NOCOPY DATE
19 ) IS
20 BEGIN
21 SELECT product_version
22 INTO ProductVersion
23 FROM fnd_product_installations
24 WHERE application_id = 101;
25
26 SELECT average_balances_flag
27 INTO ABFlag
28 FROM gl_system_usages;
29
30 IF (LedgerId IS NOT NULL) THEN
31 SELECT start_date, end_date
32 INTO PeriodStartDate, PeriodEndDate
33 FROM gl_period_statuses
34 WHERE period_name = PeriodName
35 AND application_id = 101
36 AND ledger_id = LedgerId;
37 END IF;
38
39 END init;
40
41
42 /* Name: date_to_period
43 * Desc: Return the period for the passed date.
44 *
45 * History:
46 * 11/28/95 S Rahman Created
47 */
48
49 PROCEDURE date_to_period(
50 PeriodSetName VARCHAR2,
51 PeriodType VARCHAR2,
52 AccountingDate DATE,
53 PeriodName IN OUT NOCOPY VARCHAR2
54 ) IS
55 BEGIN
56 SELECT period_name
57 INTO PeriodName
58 FROM gl_date_period_map
59 WHERE accounting_date = AccountingDate
60 AND period_set_name = PeriodSetName
61 AND period_type = PeriodType;
62
63 EXCEPTION
64 WHEN NO_DATA_FOUND THEN
65 FND_MESSAGE.set_name('RG', 'RG_ABP_NO_PERIOD_FOR_DATE');
66 APP_EXCEPTION.raise_exception;
67 END date_to_period;
68
69
70 /* Name: closest_date_for_period
71 * Desc: Return the date in the specified period that is closest to sysdate.
72 *
73 * History:
74 * 07/22/97 S Rahman Created
75 */
76 PROCEDURE closest_date_for_period(
77 LedgerId NUMBER,
78 PeriodName VARCHAR2,
79 AccountingDate IN OUT NOCOPY DATE
80 ) IS
81 BEGIN
82 SELECT greatest(least(sysdate, end_date), start_date)
83 INTO AccountingDate
84 FROM gl_period_statuses
85 WHERE application_id = 101
86 AND ledger_id = LedgerId
87 AND period_name = PeriodName;
88
89 EXCEPTION
90 WHEN NO_DATA_FOUND THEN
91 FND_MESSAGE.set_name('RG', 'RG_NO_MATCHING_PERIOD');
92 APP_EXCEPTION.raise_exception;
93 END closest_date_for_period;
94
95
96 FUNCTION new_report_request_id
97 RETURN NUMBER
98 IS
99 new_sequence_number NUMBER;
100 BEGIN
101 SELECT rg_report_requests_s.nextval
102 INTO new_sequence_number
103 FROM dual;
104
105 RETURN(new_sequence_number);
106 END new_report_request_id;
107
108
109 FUNCTION check_dup_sequence(cur_report_set_id IN NUMBER,
110 cur_report_request_id IN NUMBER,
111 new_sequence IN NUMBER)
112 RETURN BOOLEAN
113 IS
114 rec_returned NUMBER;
115 BEGIN
116 SELECT count(*)
117 INTO rec_returned
118 FROM rg_report_requests
119 WHERE report_set_id = cur_report_set_id
120 AND report_request_id <> cur_report_request_id
121 AND sequence = new_sequence;
122
123 IF rec_returned > 0 THEN
124 RETURN(TRUE);
125 ELSE
126 RETURN(FALSE);
127 END IF;
128 END check_dup_sequence;
129
130
131 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
132 X_Application_Id NUMBER,
133 X_Report_Request_Id NUMBER,
134 X_Last_Update_Date DATE,
135 X_Last_Updated_By NUMBER,
136 X_Last_Update_Login NUMBER,
137 X_Creation_Date DATE,
138 X_Created_By NUMBER,
139 X_Report_Id NUMBER,
140 X_Sequence NUMBER,
141 X_Form_Submission_Flag VARCHAR2,
142 X_Concurrent_Request_Id NUMBER,
143 X_Report_Set_Id NUMBER,
144 X_Content_Set_Id NUMBER,
145 X_Row_Order_Id NUMBER,
146 X_Exceptions_Flag VARCHAR2,
147 X_Rounding_Option VARCHAR2,
148 X_Output_Option VARCHAR2,
149 X_Ledger_Id NUMBER,
150 X_Alc_Ledger_Currency VARCHAR2,
151 X_Report_Display_Set_Id NUMBER,
152 X_Id_Flex_Code VARCHAR2,
153 X_Structure_Id NUMBER,
154 X_Segment_Override VARCHAR2,
155 X_Override_Alc_Ledger_Currency VARCHAR2,
156 X_Period_Name VARCHAR2,
157 X_Accounting_Date DATE,
158 X_Unit_Of_Measure_Id VARCHAR2,
159 X_Context VARCHAR2,
160 X_Attribute1 VARCHAR2,
161 X_Attribute2 VARCHAR2,
162 X_Attribute3 VARCHAR2,
163 X_Attribute4 VARCHAR2,
164 X_Attribute5 VARCHAR2,
165 X_Attribute6 VARCHAR2,
166 X_Attribute7 VARCHAR2,
167 X_Attribute8 VARCHAR2,
168 X_Attribute9 VARCHAR2,
169 X_Attribute10 VARCHAR2,
170 X_Attribute11 VARCHAR2,
171 X_Attribute12 VARCHAR2,
172 X_Attribute13 VARCHAR2,
173 X_Attribute14 VARCHAR2,
174 X_Attribute15 VARCHAR2,
175 X_Runtime_Option_Context VARCHAR2
176 ) IS
177 CURSOR C IS SELECT rowid FROM RG_REPORT_REQUESTS
178
179 WHERE report_request_id = X_Report_Request_Id;
180
181 BEGIN
182
183 INSERT INTO RG_REPORT_REQUESTS(
184 application_id,
185 report_request_id,
186 last_update_date,
187 last_updated_by,
188 last_update_login,
189 creation_date,
190 created_by,
191 report_id,
192 sequence,
193 form_submission_flag,
194 concurrent_request_id,
195 report_set_id,
196 content_set_id,
197 row_order_id,
198 exceptions_flag,
199 rounding_option,
200 output_option,
201 ledger_id,
202 alc_ledger_currency,
203 report_display_set_id,
204 id_flex_code,
205 structure_id,
206 segment_override,
207 override_alc_ledger_currency,
208 period_name,
209 accounting_date,
210 unit_of_measure_id,
211 context,
212 attribute1,
213 attribute2,
214 attribute3,
215 attribute4,
216 attribute5,
217 attribute6,
218 attribute7,
219 attribute8,
220 attribute9,
221 attribute10,
222 attribute11,
223 attribute12,
224 attribute13,
225 attribute14,
226 attribute15,
227 runtime_option_context
228 ) VALUES (
229 X_Application_Id,
230 X_Report_Request_Id,
231 X_Last_Update_Date,
232 X_Last_Updated_By,
233 X_Last_Update_Login,
234 X_Creation_Date,
235 X_Created_By,
236 X_Report_Id,
237 X_Sequence,
238 X_Form_Submission_Flag,
239 X_Concurrent_Request_Id,
240 X_Report_Set_Id,
241 X_Content_Set_Id,
242 X_Row_Order_Id,
243 X_Exceptions_Flag,
244 X_Rounding_Option,
245 X_Output_Option,
246 X_Ledger_Id,
247 X_Alc_Ledger_Currency,
248 X_Report_Display_Set_Id,
249 X_Id_Flex_Code,
250 X_Structure_Id,
251 X_Segment_Override,
252 X_Override_Alc_Ledger_Currency,
253 X_Period_Name,
254 X_Accounting_Date,
255 X_Unit_Of_Measure_Id,
256 X_Context,
257 X_Attribute1,
258 X_Attribute2,
259 X_Attribute3,
260 X_Attribute4,
261 X_Attribute5,
262 X_Attribute6,
263 X_Attribute7,
264 X_Attribute8,
265 X_Attribute9,
266 X_Attribute10,
267 X_Attribute11,
268 X_Attribute12,
269 X_Attribute13,
270 X_Attribute14,
271 X_Attribute15,
272 X_Runtime_Option_Context
273 );
274
275 OPEN C;
276 FETCH C INTO X_Rowid;
277 if (C%NOTFOUND) then
278 CLOSE C;
279 RAISE NO_DATA_FOUND;
280 end if;
281 CLOSE C;
282 END Insert_Row;
283
284
285 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
286 X_Application_Id NUMBER,
287 X_Report_Request_Id NUMBER,
288 X_Report_Id NUMBER,
289 X_Sequence NUMBER,
290 X_Form_Submission_Flag VARCHAR2,
291 X_Concurrent_Request_Id NUMBER,
292 X_Report_Set_Id NUMBER,
293 X_Content_Set_Id NUMBER,
294 X_Row_Order_Id NUMBER,
295 X_Exceptions_Flag VARCHAR2,
296 X_Rounding_Option VARCHAR2,
297 X_Output_Option VARCHAR2,
298 X_Ledger_Id NUMBER,
299 X_Alc_Ledger_Currency VARCHAR2,
300 X_Report_Display_Set_Id NUMBER,
301 X_Id_Flex_Code VARCHAR2,
302 X_Structure_Id NUMBER,
303 X_Segment_Override VARCHAR2,
304 X_Override_Alc_Ledger_Currency VARCHAR2,
305 X_Period_Name VARCHAR2,
306 X_Accounting_Date DATE,
307 X_Unit_Of_Measure_Id VARCHAR2,
308 X_Context VARCHAR2,
309 X_Attribute1 VARCHAR2,
310 X_Attribute2 VARCHAR2,
311 X_Attribute3 VARCHAR2,
312 X_Attribute4 VARCHAR2,
313 X_Attribute5 VARCHAR2,
314 X_Attribute6 VARCHAR2,
315 X_Attribute7 VARCHAR2,
316 X_Attribute8 VARCHAR2,
317 X_Attribute9 VARCHAR2,
318 X_Attribute10 VARCHAR2,
319 X_Attribute11 VARCHAR2,
320 X_Attribute12 VARCHAR2,
321 X_Attribute13 VARCHAR2,
322 X_Attribute14 VARCHAR2,
323 X_Attribute15 VARCHAR2,
324 X_Runtime_Option_Context VARCHAR2
325 ) IS
326 CURSOR C IS
327 SELECT *
328 FROM RG_REPORT_REQUESTS
329 WHERE rowid = X_Rowid
330 FOR UPDATE of Report_Request_Id NOWAIT;
331 Recinfo C%ROWTYPE;
332 BEGIN
333 OPEN C;
334 FETCH C INTO Recinfo;
335 if (C%NOTFOUND) then
336 CLOSE C;
337 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
338 APP_EXCEPTION.raise_exception;
339 end if;
340 CLOSE C;
341 if (
342 ( (Recinfo.application_id = X_Application_Id)
343 OR ( (Recinfo.application_id IS NULL)
344 AND (X_Application_Id IS NULL)))
345 AND ( (Recinfo.report_request_id = X_Report_Request_Id)
346 OR ( (Recinfo.report_request_id IS NULL)
347 AND (X_Report_Request_Id IS NULL)))
348 AND ( (Recinfo.report_id = X_Report_Id)
349 OR ( (Recinfo.report_id IS NULL)
350 AND (X_Report_Id IS NULL)))
351 AND ( (Recinfo.sequence = X_Sequence)
352 OR ( (Recinfo.sequence IS NULL)
353 AND (X_Sequence IS NULL)))
354 AND ( (Recinfo.form_submission_flag = X_Form_Submission_Flag)
355 OR ( (Recinfo.form_submission_flag IS NULL)
356 AND (X_Form_Submission_Flag IS NULL)))
357 AND ( (Recinfo.concurrent_request_id = X_Concurrent_Request_Id)
358 OR ( (Recinfo.concurrent_request_id IS NULL)
359 AND (X_Concurrent_Request_Id IS NULL)))
360 AND ( (Recinfo.report_set_id = X_Report_Set_Id)
361 OR ( (Recinfo.report_set_id IS NULL)
362 AND (X_Report_Set_Id IS NULL)))
363 AND ( (Recinfo.content_set_id = X_Content_Set_Id)
364 OR ( (Recinfo.content_set_id IS NULL)
365 AND (X_Content_Set_Id IS NULL)))
366 AND ( (Recinfo.row_order_id = X_Row_Order_Id)
367 OR ( (Recinfo.row_order_id IS NULL)
368 AND (X_Row_Order_Id IS NULL)))
369 AND ( (Recinfo.exceptions_flag = X_Exceptions_Flag)
370 OR ( (Recinfo.exceptions_flag IS NULL)
371 AND (X_Exceptions_Flag IS NULL)))
372 AND ( (Recinfo.rounding_option = X_Rounding_Option)
373 OR ( (Recinfo.rounding_option IS NULL)
374 AND (X_Rounding_Option IS NULL)))
375 AND ( (Recinfo.output_option = X_Output_Option)
376 OR ( (Recinfo.output_option IS NULL)
377 AND (X_Output_Option IS NULL)))
378 AND ( (Recinfo.ledger_id = X_Ledger_Id)
379 OR ( (Recinfo.ledger_id IS NULL)
380 AND (X_Ledger_Id IS NULL)))
381 AND ( (Recinfo.alc_ledger_currency = X_Alc_Ledger_Currency)
382 OR ( (Recinfo.alc_ledger_currency IS NULL)
383 AND (X_Alc_Ledger_Currency IS NULL)))
384 AND ( (Recinfo.report_display_set_id = X_Report_Display_Set_Id)
385 OR ( (Recinfo.report_display_set_id IS NULL)
386 AND (X_Report_Display_Set_Id IS NULL)))
387 AND ( (Recinfo.id_flex_code = X_Id_Flex_Code)
388 OR ( (Recinfo.id_flex_code IS NULL)
392 AND (X_Structure_Id IS NULL)))
389 AND (X_Id_Flex_Code IS NULL)))
390 AND ( (Recinfo.structure_id = X_Structure_Id)
391 OR ( (Recinfo.structure_id IS NULL)
393 AND ( (Recinfo.segment_override = X_Segment_Override)
394 OR ( (Recinfo.segment_override IS NULL)
395 AND (X_Segment_Override IS NULL)))
396 AND ( (Recinfo.override_alc_ledger_currency = X_Override_Alc_Ledger_Currency)
397 OR ( (Recinfo.override_alc_ledger_currency IS NULL)
398 AND (X_Override_Alc_Ledger_Currency IS NULL)))
399 AND ( (Recinfo.period_name = X_Period_Name)
400 OR ( (Recinfo.period_name IS NULL)
401 AND (X_Period_Name IS NULL)))
402 AND ( (Recinfo.accounting_date = X_Accounting_Date)
403 OR ( (Recinfo.accounting_date IS NULL)
404 AND (X_Accounting_Date IS NULL)))
405 AND ( (Recinfo.unit_of_measure_id = X_Unit_Of_Measure_Id)
406 OR ( (Recinfo.unit_of_measure_id IS NULL)
407 AND (X_Unit_Of_Measure_Id IS NULL)))
408 AND ( (Recinfo.context = X_Context)
409 OR ( (Recinfo.context IS NULL)
410 AND (X_Context IS NULL)))
411 AND ( (Recinfo.attribute1 = X_Attribute1)
412 OR ( (Recinfo.attribute1 IS NULL)
413 AND (X_Attribute1 IS NULL)))
414 AND ( (Recinfo.attribute2 = X_Attribute2)
415 OR ( (Recinfo.attribute2 IS NULL)
416 AND (X_Attribute2 IS NULL)))
417 AND ( (Recinfo.attribute3 = X_Attribute3)
418 OR ( (Recinfo.attribute3 IS NULL)
419 AND (X_Attribute3 IS NULL)))
420 AND ( (Recinfo.attribute4 = X_Attribute4)
421 OR ( (Recinfo.attribute4 IS NULL)
422 AND (X_Attribute4 IS NULL)))
423 AND ( (Recinfo.attribute5 = X_Attribute5)
424 OR ( (Recinfo.attribute5 IS NULL)
425 AND (X_Attribute5 IS NULL)))
426 AND ( (Recinfo.attribute6 = X_Attribute6)
427 OR ( (Recinfo.attribute6 IS NULL)
428 AND (X_Attribute6 IS NULL)))
429 AND ( (Recinfo.attribute7 = X_Attribute7)
430 OR ( (Recinfo.attribute7 IS NULL)
431 AND (X_Attribute7 IS NULL)))
432 AND ( (Recinfo.attribute8 = X_Attribute8)
433 OR ( (Recinfo.attribute8 IS NULL)
434 AND (X_Attribute8 IS NULL)))
435 AND ( (Recinfo.attribute9 = X_Attribute9)
436 OR ( (Recinfo.attribute9 IS NULL)
437 AND (X_Attribute9 IS NULL)))
438 AND ( (Recinfo.attribute10 = X_Attribute10)
439 OR ( (Recinfo.attribute10 IS NULL)
440 AND (X_Attribute10 IS NULL)))
441 AND ( (Recinfo.attribute11 = X_Attribute11)
442 OR ( (Recinfo.attribute11 IS NULL)
443 AND (X_Attribute11 IS NULL)))
444 AND ( (Recinfo.attribute12 = X_Attribute12)
445 OR ( (Recinfo.attribute12 IS NULL)
446 AND (X_Attribute12 IS NULL)))
447 AND ( (Recinfo.attribute13 = X_Attribute13)
448 OR ( (Recinfo.attribute13 IS NULL)
449 AND (X_Attribute13 IS NULL)))
450 AND ( (Recinfo.attribute14 = X_Attribute14)
451 OR ( (Recinfo.attribute14 IS NULL)
452 AND (X_Attribute14 IS NULL)))
453 AND ( (Recinfo.attribute15 = X_Attribute15)
454 OR ( (Recinfo.attribute15 IS NULL)
455 AND (X_Attribute15 IS NULL)))
456 AND ( (Recinfo.runtime_option_context = X_Runtime_Option_Context)
457 OR ( (Recinfo.runtime_option_context IS NULL)
458 AND (X_Runtime_Option_Context IS NULL)))
459 ) then
460 return;
461 else
462 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
463 APP_EXCEPTION.raise_exception;
464 end if;
465 END Lock_Row;
466
467 PROCEDURE Update_Row(X_Rowid VARCHAR2,
468 X_Application_Id NUMBER,
469 X_Report_Request_Id NUMBER,
470 X_Last_Update_Date DATE,
471 X_Last_Updated_By NUMBER,
472 X_Last_Update_Login NUMBER,
473 X_Report_Id NUMBER,
474 X_Sequence NUMBER,
475 X_Form_Submission_Flag VARCHAR2,
476 X_Concurrent_Request_Id NUMBER,
477 X_Report_Set_Id NUMBER,
478 X_Content_Set_Id NUMBER,
479 X_Row_Order_Id NUMBER,
480 X_Exceptions_Flag VARCHAR2,
481 X_Rounding_Option VARCHAR2,
482 X_Output_Option VARCHAR2,
483 X_Ledger_Id NUMBER,
484 X_Alc_Ledger_Currency VARCHAR2,
485 X_Report_Display_Set_Id NUMBER,
486 X_Id_Flex_Code VARCHAR2,
487 X_Structure_Id NUMBER,
488 X_Segment_Override VARCHAR2,
489 X_Override_Alc_Ledger_Currency VARCHAR2,
493 X_Context VARCHAR2,
490 X_Period_Name VARCHAR2,
491 X_Accounting_Date DATE,
492 X_Unit_Of_Measure_Id VARCHAR2,
494 X_Attribute1 VARCHAR2,
495 X_Attribute2 VARCHAR2,
496 X_Attribute3 VARCHAR2,
497 X_Attribute4 VARCHAR2,
498 X_Attribute5 VARCHAR2,
499 X_Attribute6 VARCHAR2,
500 X_Attribute7 VARCHAR2,
501 X_Attribute8 VARCHAR2,
502 X_Attribute9 VARCHAR2,
503 X_Attribute10 VARCHAR2,
504 X_Attribute11 VARCHAR2,
505 X_Attribute12 VARCHAR2,
506 X_Attribute13 VARCHAR2,
507 X_Attribute14 VARCHAR2,
508 X_Attribute15 VARCHAR2,
509 X_Runtime_Option_Context VARCHAR2
510 ) IS
511 BEGIN
512 UPDATE RG_REPORT_REQUESTS
513 SET
514
515 application_id = X_Application_Id,
516 report_request_id = X_Report_Request_Id,
517 last_update_date = X_Last_Update_Date,
518 last_updated_by = X_Last_Updated_By,
519 last_update_login = X_Last_Update_Login,
520 report_id = X_Report_Id,
521 sequence = X_Sequence,
522 form_submission_flag = X_Form_Submission_Flag,
523 concurrent_request_id = X_Concurrent_Request_Id,
524 report_set_id = X_Report_Set_Id,
525 content_set_id = X_Content_Set_Id,
526 row_order_id = X_Row_Order_Id,
527 exceptions_flag = X_Exceptions_Flag,
528 rounding_option = X_Rounding_Option,
529 output_option = X_Output_Option,
530 ledger_id = X_Ledger_Id,
531 alc_ledger_currency = X_Alc_Ledger_Currency,
532 report_display_set_id = X_Report_Display_Set_Id,
533 id_flex_code = X_Id_Flex_Code,
534 structure_id = X_Structure_Id,
535 segment_override = X_Segment_Override,
536 override_alc_ledger_currency = X_Override_Alc_Ledger_Currency,
537 period_name = X_Period_Name,
538 accounting_date = X_Accounting_Date,
539 unit_of_measure_id = X_Unit_Of_Measure_Id,
540 context = X_Context,
541 attribute1 = X_Attribute1,
542 attribute2 = X_Attribute2,
543 attribute3 = X_Attribute3,
544 attribute4 = X_Attribute4,
545 attribute5 = X_Attribute5,
546 attribute6 = X_Attribute6,
547 attribute7 = X_Attribute7,
548 attribute8 = X_Attribute8,
549 attribute9 = X_Attribute9,
550 attribute10 = X_Attribute10,
551 attribute11 = X_Attribute11,
552 attribute12 = X_Attribute12,
553 attribute13 = X_Attribute13,
554 attribute14 = X_Attribute14,
555 attribute15 = X_Attribute15,
556 runtime_option_context = X_Runtime_Option_Context
557 WHERE rowid = X_rowid;
558
559 if (SQL%NOTFOUND) then
560 RAISE NO_DATA_FOUND;
561 end if;
562 END Update_Row;
563
564 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
565 BEGIN
566 DELETE FROM RG_REPORT_REQUESTS
567 WHERE rowid = X_Rowid;
568
569 if (SQL%NOTFOUND) then
570 RAISE NO_DATA_FOUND;
571 end if;
572 END Delete_Row;
573
574 END RG_REPORT_REQUESTS_PKG;