[Home] [Help]
PACKAGE BODY: APPS.HZ_FINANCIAL_NUMBERS_PKG
Source
1 PACKAGE BODY HZ_FINANCIAL_NUMBERS_PKG as
2 /* $Header: ARHOFNTB.pls 120.5 2005/05/25 23:51:58 achung ship $ */
3
4 G_MISS_CONTENT_SOURCE_TYPE CONSTANT VARCHAR2(30) := 'USER_ENTERED';
5
6 PROCEDURE Insert_Row (
7 x_rowid IN OUT NOCOPY VARCHAR2,
8 x_financial_number_id IN OUT NOCOPY NUMBER,
9 x_financial_report_id IN NUMBER,
10 x_financial_number IN NUMBER,
11 x_financial_number_name IN VARCHAR2,
12 x_financial_units_applied IN NUMBER,
13 x_financial_number_currency IN VARCHAR2,
14 x_projected_actual_flag IN VARCHAR2,
15 x_content_source_type IN VARCHAR2,
16 x_status IN VARCHAR2,
17 x_object_version_number IN NUMBER,
18 x_created_by_module IN VARCHAR2,
19 x_actual_content_source IN VARCHAR2
20 ) IS
21
22 l_success VARCHAR2(1) := 'N';
23
24 BEGIN
25
26 WHILE l_success = 'N' LOOP
27 BEGIN
28 INSERT INTO HZ_FINANCIAL_NUMBERS (
29 financial_number_id,
30 financial_report_id,
31 financial_number,
32 financial_number_name,
33 financial_units_applied,
34 financial_number_currency,
35 projected_actual_flag,
36 created_by,
37 creation_date,
38 last_update_login,
39 last_update_date,
40 last_updated_by,
41 request_id,
42 program_application_id,
43 program_id,
44 program_update_date,
45 content_source_type,
46 status,
47 object_version_number,
48 created_by_module,
49 application_id,
50 actual_content_source
51 )
52 VALUES (
53 DECODE(x_financial_number_id,
54 FND_API.G_MISS_NUM, HZ_FINANCIAL_NUMBERS_S.NEXTVAL,
55 NULL, HZ_FINANCIAL_NUMBERS_S.NEXTVAL,
56 x_financial_number_id),
57 DECODE(x_financial_report_id,
58 FND_API.G_MISS_NUM, NULL,
59 x_financial_report_id),
60 DECODE(x_financial_number,
61 FND_API.G_MISS_NUM, NULL,
62 x_financial_number),
63 DECODE(x_financial_number_name,
64 FND_API.G_MISS_CHAR, NULL,
65 x_financial_number_name),
66 DECODE(x_financial_units_applied,
67 FND_API.G_MISS_NUM, NULL,
68 x_financial_units_applied),
69 DECODE(x_financial_number_currency,
70 FND_API.G_MISS_CHAR, NULL,
71 x_financial_number_currency),
72 DECODE(x_projected_actual_flag,
73 FND_API.G_MISS_CHAR, NULL,
74 x_projected_actual_flag),
75 hz_utility_v2pub.created_by,
76 hz_utility_v2pub.creation_date,
77 hz_utility_v2pub.last_update_login,
78 hz_utility_v2pub.last_update_date,
79 hz_utility_v2pub.last_updated_by,
80 hz_utility_v2pub.request_id,
81 hz_utility_v2pub.program_application_id,
82 hz_utility_v2pub.program_id,
83 hz_utility_v2pub.program_update_date,
84 DECODE(x_content_source_type,
85 FND_API.G_MISS_CHAR, G_MISS_CONTENT_SOURCE_TYPE,
86 NULL, G_MISS_CONTENT_SOURCE_TYPE,
87 x_content_source_type),
88 DECODE(x_status,
89 FND_API.G_MISS_CHAR, 'A',
90 NULL, 'A',
91 x_status),
92 DECODE(x_object_version_number,
93 FND_API.G_MISS_NUM, NULL,
94 x_object_version_number),
95 DECODE(x_created_by_module,
96 FND_API.G_MISS_CHAR, NULL,
97 x_created_by_module),
98 hz_utility_v2pub.application_id,
99 DECODE(x_actual_content_source,
100 FND_API.G_MISS_CHAR, G_MISS_CONTENT_SOURCE_TYPE,
101 NULL, G_MISS_CONTENT_SOURCE_TYPE,
102 x_actual_content_source)
103 ) RETURNING
104 rowid,
105 financial_number_id
106 INTO
107 x_rowid,
108 x_financial_number_id;
109
110 l_success := 'Y';
111
112 EXCEPTION
113 WHEN DUP_VAL_ON_INDEX THEN
114 IF INSTR(SQLERRM, 'HZ_FINANCIAL_NUMBERS_U1') <> 0 THEN
115 DECLARE
116 l_count NUMBER;
117 l_dummy VARCHAR2(1);
118 BEGIN
119 l_count := 1;
120 WHILE l_count > 0 LOOP
121 SELECT HZ_FINANCIAL_NUMBERS_S.NEXTVAL
122 INTO x_financial_number_id FROM dual;
123 BEGIN
124 SELECT 'Y' INTO l_dummy
125 FROM HZ_FINANCIAL_NUMBERS
126 WHERE financial_number_id = x_financial_number_id;
127 l_count := 1;
128 EXCEPTION
129 WHEN NO_DATA_FOUND THEN
130 l_count := 0;
131 END;
132 END LOOP;
133 END;
134 END IF;
135
136 END;
137 END LOOP;
138
139 END Insert_Row;
140
141 PROCEDURE Update_Row (
142 x_rowid IN OUT NOCOPY VARCHAR2,
143 x_financial_number_id IN NUMBER,
144 x_financial_report_id IN NUMBER,
145 x_financial_number IN NUMBER,
146 x_financial_number_name IN VARCHAR2,
147 x_financial_units_applied IN NUMBER,
148 x_financial_number_currency IN VARCHAR2,
149 x_projected_actual_flag IN VARCHAR2,
150 x_content_source_type IN VARCHAR2,
151 x_status IN VARCHAR2,
152 x_object_version_number IN NUMBER,
153 x_created_by_module IN VARCHAR2,
154 x_actual_content_source IN VARCHAR2
155 ) IS
156 BEGIN
157
158 UPDATE HZ_FINANCIAL_NUMBERS
159 SET
160 financial_number_id =
161 DECODE(x_financial_number_id,
162 NULL, financial_number_id,
163 FND_API.G_MISS_NUM, NULL,
164 x_financial_number_id),
165 financial_report_id =
166 DECODE(x_financial_report_id,
167 NULL, financial_report_id,
168 FND_API.G_MISS_NUM, NULL,
169 x_financial_report_id),
170 financial_number =
171 DECODE(x_financial_number,
172 NULL, financial_number,
173 FND_API.G_MISS_NUM, NULL,
174 x_financial_number),
175 financial_number_name =
176 DECODE(x_financial_number_name,
177 NULL, financial_number_name,
178 FND_API.G_MISS_CHAR, NULL,
179 x_financial_number_name),
180 financial_units_applied =
181 DECODE(x_financial_units_applied,
182 NULL, financial_units_applied,
183 FND_API.G_MISS_NUM, NULL,
184 x_financial_units_applied),
185 financial_number_currency =
186 DECODE(x_financial_number_currency,
187 NULL, financial_number_currency,
188 FND_API.G_MISS_CHAR, NULL,
189 x_financial_number_currency),
190 projected_actual_flag =
191 DECODE(x_projected_actual_flag,
192 NULL, projected_actual_flag,
193 FND_API.G_MISS_CHAR, NULL,
194 x_projected_actual_flag),
195 created_by = created_by,
196 creation_date = creation_date,
197 last_update_login = hz_utility_v2pub.last_update_login,
198 last_update_date = hz_utility_v2pub.last_update_date,
199 last_updated_by = hz_utility_v2pub.last_updated_by,
200 request_id = hz_utility_v2pub.request_id,
201 program_application_id = hz_utility_v2pub.program_application_id,
202 program_id = hz_utility_v2pub.program_id,
203 program_update_date = hz_utility_v2pub.program_update_date,
204 content_source_type =
205 DECODE(x_content_source_type,
206 NULL, content_source_type,
207 FND_API.G_MISS_CHAR, NULL,
208 x_content_source_type),
209 status =
210 DECODE(x_status,
211 NULL, status,
212 FND_API.G_MISS_CHAR, NULL,
213 x_status),
214 object_version_number =
215 DECODE(x_object_version_number,
216 NULL, object_version_number,
217 FND_API.G_MISS_NUM, NULL,
218 x_object_version_number),
219 created_by_module =
220 DECODE(x_created_by_module,
221 NULL, created_by_module,
222 FND_API.G_MISS_CHAR, NULL,
223 x_created_by_module),
224 application_id = hz_utility_v2pub.application_id/*,
225
226 ** SSM SST Integration and Extension
227 ** actual_content_source will not be updateable for non-SSM enabled entities.
228
229 actual_content_source =
230 DECODE(x_actual_content_source,
231 NULL, actual_content_source,
232 FND_API.G_MISS_CHAR, NULL,
233 x_actual_content_source) */
234 WHERE rowid = x_rowid;
235
236 IF ( SQL%NOTFOUND ) THEN
237 RAISE NO_DATA_FOUND;
238 END IF;
239
240 END Update_Row;
241
242 PROCEDURE Lock_Row (
243 x_rowid IN OUT NOCOPY VARCHAR2,
244 x_financial_number_id IN NUMBER,
245 x_financial_report_id IN NUMBER,
246 x_financial_number IN NUMBER,
247 x_financial_number_name IN VARCHAR2,
248 x_financial_units_applied IN NUMBER,
249 x_financial_number_currency IN VARCHAR2,
250 x_projected_actual_flag IN VARCHAR2,
251 x_created_by IN NUMBER,
252 x_creation_date IN DATE,
253 x_last_update_login IN NUMBER,
254 x_last_update_date IN DATE,
255 x_last_updated_by IN NUMBER,
256 x_request_id IN NUMBER,
257 x_program_application_id IN NUMBER,
258 x_program_id IN NUMBER,
259 x_program_update_date IN DATE,
260 x_content_source_type IN VARCHAR2,
261 x_status IN VARCHAR2,
262 x_object_version_number IN NUMBER,
263 x_created_by_module IN VARCHAR2,
264 x_application_id IN NUMBER,
265 x_actual_content_source IN VARCHAR2
266 ) IS
267
268 CURSOR c IS
269 SELECT * FROM hz_financial_numbers
270 WHERE rowid = x_rowid
271 FOR UPDATE NOWAIT;
272 Recinfo c%ROWTYPE;
273
274 BEGIN
275
276 OPEN c;
277 FETCH c INTO Recinfo;
278 IF ( c%NOTFOUND ) THEN
279 CLOSE c;
280 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
281 APP_EXCEPTION.RAISE_EXCEPTION;
282 END IF;
283 CLOSE C;
284
285 IF (
286 ( ( Recinfo.financial_number_id = x_financial_number_id )
287 OR ( ( Recinfo.financial_number_id IS NULL )
288 AND ( x_financial_number_id IS NULL ) ) )
289 AND ( ( Recinfo.financial_report_id = x_financial_report_id )
290 OR ( ( Recinfo.financial_report_id IS NULL )
291 AND ( x_financial_report_id IS NULL ) ) )
292 AND ( ( Recinfo.financial_number = x_financial_number )
293 OR ( ( Recinfo.financial_number IS NULL )
294 AND ( x_financial_number IS NULL ) ) )
295 AND ( ( Recinfo.financial_number_name = x_financial_number_name )
296 OR ( ( Recinfo.financial_number_name IS NULL )
297 AND ( x_financial_number_name IS NULL ) ) )
298 AND ( ( Recinfo.financial_units_applied = x_financial_units_applied )
299 OR ( ( Recinfo.financial_units_applied IS NULL )
300 AND ( x_financial_units_applied IS NULL ) ) )
301 AND ( ( Recinfo.financial_number_currency = x_financial_number_currency )
302 OR ( ( Recinfo.financial_number_currency IS NULL )
303 AND ( x_financial_number_currency IS NULL ) ) )
304 AND ( ( Recinfo.projected_actual_flag = x_projected_actual_flag )
305 OR ( ( Recinfo.projected_actual_flag IS NULL )
306 AND ( x_projected_actual_flag IS NULL ) ) )
307 AND ( ( Recinfo.created_by = x_created_by )
308 OR ( ( Recinfo.created_by IS NULL )
309 AND ( x_created_by IS NULL ) ) )
310 AND ( ( Recinfo.creation_date = x_creation_date )
311 OR ( ( Recinfo.creation_date IS NULL )
312 AND ( x_creation_date IS NULL ) ) )
313 AND ( ( Recinfo.last_update_login = x_last_update_login )
314 OR ( ( Recinfo.last_update_login IS NULL )
315 AND ( x_last_update_login IS NULL ) ) )
316 AND ( ( Recinfo.last_update_date = x_last_update_date )
317 OR ( ( Recinfo.last_update_date IS NULL )
318 AND ( x_last_update_date IS NULL ) ) )
319 AND ( ( Recinfo.last_updated_by = x_last_updated_by )
320 OR ( ( Recinfo.last_updated_by IS NULL )
321 AND ( x_last_updated_by IS NULL ) ) )
322 AND ( ( Recinfo.request_id = x_request_id )
323 OR ( ( Recinfo.request_id IS NULL )
324 AND ( x_request_id IS NULL ) ) )
325 AND ( ( Recinfo.program_application_id = x_program_application_id )
326 OR ( ( Recinfo.program_application_id IS NULL )
327 AND ( x_program_application_id IS NULL ) ) )
328 AND ( ( Recinfo.program_id = x_program_id )
329 OR ( ( Recinfo.program_id IS NULL )
330 AND ( x_program_id IS NULL ) ) )
331 AND ( ( Recinfo.program_update_date = x_program_update_date )
332 OR ( ( Recinfo.program_update_date IS NULL )
333 AND ( x_program_update_date IS NULL ) ) )
334 AND ( ( Recinfo.content_source_type = x_content_source_type )
335 OR ( ( Recinfo.content_source_type IS NULL )
336 AND ( x_content_source_type IS NULL ) ) )
337 AND ( ( Recinfo.status = x_status )
338 OR ( ( Recinfo.status IS NULL )
339 AND ( x_status IS NULL ) ) )
340 AND ( ( Recinfo.object_version_number = x_object_version_number )
341 OR ( ( Recinfo.object_version_number IS NULL )
342 AND ( x_object_version_number IS NULL ) ) )
343 AND ( ( Recinfo.created_by_module = x_created_by_module )
344 OR ( ( Recinfo.created_by_module IS NULL )
345 AND ( x_created_by_module IS NULL ) ) )
346 AND ( ( Recinfo.application_id = x_application_id )
347 OR ( ( Recinfo.application_id IS NULL )
348 AND ( x_application_id IS NULL ) ) )
349 AND ( ( Recinfo.actual_content_source = x_actual_content_source )
350 OR ( ( Recinfo.actual_content_source IS NULL )
351 AND ( x_actual_content_source IS NULL ) ) )
352 ) THEN
353 RETURN;
354 ELSE
355 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
356 APP_EXCEPTION.RAISE_EXCEPTION;
357 END IF;
358
359 END Lock_Row;
360
361 PROCEDURE Select_Row (
362 x_financial_number_id IN OUT NOCOPY NUMBER,
363 x_financial_report_id OUT NOCOPY NUMBER,
364 x_financial_number OUT NOCOPY NUMBER,
365 x_financial_number_name OUT NOCOPY VARCHAR2,
366 x_financial_units_applied OUT NOCOPY NUMBER,
367 x_financial_number_currency OUT NOCOPY VARCHAR2,
368 x_projected_actual_flag OUT NOCOPY VARCHAR2,
369 x_content_source_type OUT NOCOPY VARCHAR2,
370 x_status OUT NOCOPY VARCHAR2,
371 x_actual_content_source OUT NOCOPY VARCHAR2
372 ) IS
373 BEGIN
374
375 SELECT
376 NVL(financial_number_id, FND_API.G_MISS_NUM),
377 NVL(financial_report_id, FND_API.G_MISS_NUM),
378 NVL(financial_number, FND_API.G_MISS_NUM),
379 NVL(financial_number_name, FND_API.G_MISS_CHAR),
383 NVL(content_source_type, FND_API.G_MISS_CHAR),
380 NVL(financial_units_applied, FND_API.G_MISS_NUM),
381 NVL(financial_number_currency, FND_API.G_MISS_CHAR),
382 NVL(projected_actual_flag, FND_API.G_MISS_CHAR),
384 NVL(status, FND_API.G_MISS_CHAR),
385 NVL(actual_content_source, FND_API.G_MISS_CHAR)
386 INTO
387 x_financial_number_id,
388 x_financial_report_id,
389 x_financial_number,
390 x_financial_number_name,
391 x_financial_units_applied,
392 x_financial_number_currency,
393 x_projected_actual_flag,
394 x_content_source_type,
395 x_status,
396 x_actual_content_source
397 FROM HZ_FINANCIAL_NUMBERS
398 WHERE financial_number_id = x_financial_number_id;
399
400 EXCEPTION
401 WHEN NO_DATA_FOUND THEN
402 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NO_RECORD');
403 FND_MESSAGE.SET_TOKEN('RECORD', 'hz_financial_number_rec');
404 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(x_financial_number_id));
405 FND_MSG_PUB.ADD;
406 RAISE FND_API.G_EXC_ERROR;
407
408 END Select_Row;
409
410 PROCEDURE Delete_Row (
411 x_financial_number_id IN NUMBER
412 ) IS
413 BEGIN
414
415 DELETE FROM HZ_FINANCIAL_NUMBERS
416 WHERE financial_number_id = x_financial_number_id;
417
418 IF ( SQL%NOTFOUND ) THEN
419 RAISE NO_DATA_FOUND;
420 END IF;
421
422 END Delete_Row;
423
424 END HZ_FINANCIAL_NUMBERS_PKG;