[Home] [Help]
PACKAGE BODY: APPS.RG_REPORT_CALCULATIONS_PKG
Source
1 PACKAGE BODY RG_REPORT_CALCULATIONS_PKG AS
2 -- $Header: rgiraclb.pls 120.3 2006/03/13 19:52:39 ticheng ship $
3 -- Name
4 -- RG_REPORT_CALCULATIONS_PKG
5 -- Purpose
6 -- to include all sever side procedures and packages for table
7 -- RG_REPORT_CALCULATIONS
8 -- Notes
9 --
10 -- History
11 -- 11/01/93 A Chen Created
12 --
13 -- PRIVATE VARIABLES
14 -- None.
15 --
16 -- PRIVATE FUNCTIONS
17 -- None.
18 --
19 -- PUBLIC FUNCTIONS
20 --
21 FUNCTION check_existence(X_axis_set_id NUMBER,
22 X_axis_seq NUMBER) RETURN BOOLEAN IS
23 dummy NUMBER;
24 BEGIN
25 select 1 into dummy
26 from rg_report_calculations
27 where axis_set_id = X_axis_set_id
28 and axis_seq = X_axis_seq
29 and rownum < 2;
30 RETURN (TRUE);
31
32 EXCEPTION
33 WHEN NO_DATA_FOUND THEN
34 RETURN (FALSE);
35 END check_existence;
36
37
38 PROCEDURE delete_rows(X_axis_set_id NUMBER,
39 X_axis_seq NUMBER) IS
40 BEGIN
41 IF (X_axis_seq = -1) THEN
42 delete from rg_report_calculations
43 where axis_set_id = X_axis_set_id;
44 ELSE
45 delete from rg_report_calculations
46 where axis_set_id = X_axis_set_id
47 and axis_seq = X_axis_seq;
48 END IF;
49 END delete_rows;
50
51
52 PROCEDURE check_unique(X_rowid VARCHAR2, X_axis_set_id NUMBER,
53 X_axis_seq NUMBER, X_calculation_seq NUMBER) IS
54 Dummy NUMBER;
55 BEGIN
56 SELECT 1 INTO Dummy FROM dual WHERE NOT EXISTS
57 (SELECT 1 FROM rg_report_calculations
58 WHERE axis_set_id = X_axis_set_id
59 AND axis_seq = X_axis_seq
60 AND calculation_seq = X_calculation_seq
61 AND ((X_rowid IS NULL) OR (rowid <> X_rowid))
62 );
63
64 EXCEPTION
65 WHEN NO_DATA_FOUND THEN
66 FND_MESSAGE.set_name('RG', 'RG_DUP_CALC_SEQ');
67 APP_EXCEPTION.raise_exception;
68
69 END check_unique;
70
71
72 PROCEDURE Load_Row(X_application_id NUMBER,
73 X_axis_set_id NUMBER,
74 X_axis_seq NUMBER,
75 X_calculation_seq NUMBER,
76 X_operator VARCHAR2,
77 X_axis_seq_low NUMBER,
78 X_axis_seq_high NUMBER,
79 X_axis_name_low VARCHAR2,
80 X_axis_name_high VARCHAR2,
81 X_constant NUMBER,
82 X_context VARCHAR2,
83 X_attribute1 VARCHAR2,
84 X_attribute2 VARCHAR2,
85 X_attribute3 VARCHAR2,
86 X_attribute4 VARCHAR2,
87 X_attribute5 VARCHAR2,
88 X_attribute6 VARCHAR2,
89 X_attribute7 VARCHAR2,
90 X_attribute8 VARCHAR2,
91 X_attribute9 VARCHAR2,
92 X_attribute10 VARCHAR2,
93 X_attribute11 VARCHAR2,
94 X_attribute12 VARCHAR2,
95 X_attribute13 VARCHAR2,
96 X_attribute14 VARCHAR2,
97 X_attribute15 VARCHAR2,
98 X_owner VARCHAR2,
99 X_force_edits VARCHAR2) IS
100 v_user_id NUMBER := 0;
101 v_creation_date DATE;
102 v_last_updated_by NUMBER;
103 BEGIN
104 /* Make sure primary key is not null */
105 IF ( X_axis_set_id IS NULL
106 OR X_axis_seq IS NULL
107 OR X_calculation_seq IS NULL) THEN
108 FND_MESSAGE.set_name('SQLGL', 'GL_LOAD_ROW_NO_DATA');
109 APP_EXCEPTION.raise_exception;
110 END IF;
111
112 /* Set user id for seeded data */
113 IF (X_owner = 'SEED') THEN
114 v_user_id := 1;
115 END IF;
116
117 BEGIN
118 /* Retrieve creation date from existing row */
119 SELECT creation_date, last_updated_by
120 INTO v_creation_date, v_last_updated_by
121 FROM RG_REPORT_CALCULATIONS
122 WHERE axis_set_id = X_axis_set_id
123 AND axis_seq = X_axis_seq
124 AND calculation_seq = X_calculation_seq;
125
126 /* Do not overwrite if it has been customized */
127 IF (v_last_updated_by <> 1) THEN
128 RETURN;
129 END IF;
130
131 /*
132 * Update only if force_edits is 'Y' or owner = 'SEED'
133 */
134 IF (v_user_id = 1 or X_force_edits = 'Y') THEN
135 UPDATE RG_REPORT_CALCULATIONS
136 SET application_id = X_application_id,
137 last_update_date = sysdate,
138 last_updated_by = v_user_id,
139 last_update_login = 0,
140 operator = X_operator,
141 axis_seq_low = X_axis_seq_low,
142 axis_seq_high = X_axis_seq_high,
143 axis_name_low = X_axis_name_low,
144 axis_name_high = X_axis_name_high,
145 constant = X_constant,
146 context = X_context,
147 attribute1 = X_attribute1,
148 attribute2 = X_attribute2,
149 attribute3 = X_attribute3,
150 attribute4 = X_attribute4,
151 attribute5 = X_attribute5,
152 attribute6 = X_attribute6,
153 attribute7 = X_attribute7,
154 attribute8 = X_attribute8,
155 attribute9 = X_attribute9,
156 attribute10 = X_attribute10,
157 attribute11 = X_attribute11,
158 attribute12 = X_attribute12,
159 attribute13 = X_attribute13,
160 attribute14 = X_attribute14,
161 attribute15 = X_attribute15
162 WHERE axis_set_id = X_axis_set_id
163 AND axis_seq = X_axis_seq
164 AND calculation_seq = X_calculation_seq;
165
166 IF (SQL%NOTFOUND) THEN
167 RAISE NO_DATA_FOUND;
168 END IF;
169 END IF;
170
171 EXCEPTION
172 WHEN NO_DATA_FOUND THEN
173 /*
174 * If the row doesn't exist yet, insert.
175 */
176 INSERT INTO RG_REPORT_CALCULATIONS
177 (application_id,
178 axis_set_id,
179 axis_seq,
180 calculation_seq,
181 last_update_date,
182 last_updated_by,
183 last_update_login,
184 creation_date,
185 created_by,
186 operator,
187 axis_seq_low,
188 axis_seq_high,
189 axis_name_low,
190 axis_name_high,
191 constant,
192 context,
193 attribute1,
194 attribute2,
195 attribute3,
196 attribute4,
197 attribute5,
198 attribute6,
199 attribute7,
200 attribute8,
201 attribute9,
202 attribute10,
203 attribute11,
204 attribute12,
205 attribute13,
206 attribute14,
207 attribute15)
208 VALUES
209 (X_application_id,
210 X_axis_set_id,
211 X_axis_seq,
212 X_calculation_seq,
213 sysdate,
214 v_user_id,
215 0,
216 sysdate,
217 v_user_id,
218 X_operator,
219 X_axis_seq_low,
220 X_axis_seq_high,
221 X_axis_name_low,
222 X_axis_name_high,
223 X_constant,
224 X_context,
225 X_attribute1,
226 X_attribute2,
227 X_attribute3,
228 X_attribute4,
229 X_attribute5,
230 X_attribute6,
231 X_attribute7,
232 X_attribute8,
233 X_attribute9,
234 X_attribute10,
235 X_attribute11,
236 X_attribute12,
237 X_attribute13,
238 X_attribute14,
239 X_attribute15);
240 END;
241 END Load_Row;
242
243
244 PROCEDURE Translate_Row(X_axis_set_id NUMBER,
245 X_axis_seq NUMBER,
246 X_calculation_seq NUMBER,
247 X_axis_name_low VARCHAR2,
248 X_axis_name_high VARCHAR2,
249 X_owner VARCHAR2,
250 X_force_edits VARCHAR2) IS
251 v_user_id NUMBER := 0;
252 BEGIN
253 /* Set user id for seeded data */
254 IF (X_owner = 'SEED') THEN
255 v_user_id := 1;
256 END IF;
257
258 /*
259 * Update only if force_edits is 'Y' or owner = 'SEED'
260 */
261 IF (v_user_id = 1 or X_force_edits = 'Y') THEN
262 UPDATE RG_REPORT_CALCULATIONS
263 SET axis_name_low = X_axis_name_low,
264 axis_name_high = X_axis_name_high
265 WHERE axis_set_id = X_axis_set_id
266 AND axis_seq = X_axis_seq
267 AND calculation_seq = X_calculation_seq
268 AND userenv('LANG') = (SELECT language_code
269 FROM FND_LANGUAGES
270 WHERE installed_flag = 'B');
271 END IF;
272 END Translate_Row;
273
274 END RG_REPORT_CALCULATIONS_PKG;