[Home] [Help]
PACKAGE BODY: APPS.OKL_ACCT_GEN_RULE_PVT
Source
1 PACKAGE BODY OKL_ACCT_GEN_RULE_PVT AS
2 /* $Header: OKLRACGB.pls 115.8 2002/11/30 08:42:02 spillaip noship $ */
3
4
5 PROCEDURE GET_RULE_LINES_COUNT(p_api_version IN NUMBER,
6 p_init_msg_list IN VARCHAR2,
7 x_return_status OUT NOCOPY VARCHAR2,
8 x_msg_count OUT NOCOPY NUMBER,
9 x_msg_data OUT NOCOPY VARCHAR2,
10 p_ae_line_type IN VARCHAR2,
11 x_line_count OUT NOCOPY NUMBER)
12
13 IS
14
15 l_api_name CONSTANT VARCHAR2(40) := 'GET_RULE_LINES_COUNT';
16 l_api_version CONSTANT NUMBER := 1.0;
17 l_row_count NUMBER;
18 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
19 l_acc_lines aulv_tbl_type;
20 i NUMBER := 0;
21 l_set_of_books_id NUMBER ;
22 l_agr_id NUMBER;
23 l_line_count NUMBER := 0;
24
25 l_array_of_segments OKL_ACCOUNTING_UTIL.seg_num_name_type;
26
27
28 CURSOR acg_csr(v_set_of_books_id NUMBER) IS
29
30 SELECT ID
31 FROM OKL_ACC_GEN_RULES_V
32 WHERE ae_line_type = p_ae_line_type
33 AND set_of_books_id = v_set_of_books_id;
34
35
36
37 CURSOR acgl_csr(v_agr_id NUMBER) IS
38 SELECT ID
39 ,SEGMENT
40 ,SEGMENT_NUMBER
41 ,AGR_ID
42 ,SOURCE
43 ,CONSTANTS
44 ,OBJECT_VERSION_NUMBER
45 ,ATTRIBUTE_CATEGORY
46 ,ATTRIBUTE1
47 ,ATTRIBUTE2
48 ,ATTRIBUTE3
49 ,ATTRIBUTE4
50 ,ATTRIBUTE5
51 ,ATTRIBUTE6
52 ,ATTRIBUTE7
53 ,ATTRIBUTE8
54 ,ATTRIBUTE9
55 ,ATTRIBUTE10
56 ,ATTRIBUTE11
57 ,ATTRIBUTE12
58 ,ATTRIBUTE13
59 ,ATTRIBUTE14
60 ,ATTRIBUTE15
61 ,CREATED_BY
62 ,CREATION_DATE
63 ,LAST_UPDATED_BY
64 ,LAST_UPDATE_DATE
65 ,LAST_UPDATE_LOGIN
66 FROM OKL_ACC_GEN_RUL_LNS_V
67 WHERE agr_id = v_agr_id;
68
69 acgl_rec acgl_csr%ROWTYPE;
70
71
72
73 BEGIN
74
75 x_return_status := OKL_API.G_RET_STS_SUCCESS;
76
77 -- Get set of books id
78
79 l_set_of_books_id := OKL_ACCOUNTING_UTIL.GET_SET_OF_BOOKS_ID;
80
81
82 -- Check if the record exists in the Header Table.
83
84 OPEN acg_csr(l_set_of_books_id);
85 FETCH acg_csr INTO l_agr_id;
86 IF (acg_csr%NOTFOUND) THEN
87
88 OKL_ACCOUNTING_UTIL.GET_ACCOUNTING_SEGMENT(p_segment_array => l_array_of_segments);
89 l_line_count := l_array_of_segments.seg_num.COUNT;
90
91 ELSE
92
93 i := 0;
94
95 FOR acgl_rec IN acgl_csr(l_agr_id)
96
97 LOOP
98
99 i := i + 1;
100
101 END LOOP;
102
103 l_line_count := i;
104
105 END IF;
106
107 CLOSE acg_csr;
108
109 x_line_count := l_line_count;
110
111 EXCEPTION
112
113 WHEN OTHERS THEN
114 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
115
116 END GET_RULE_LINES_COUNT;
117
118
119
120 PROCEDURE GET_RULE_LINES(p_api_version IN NUMBER,
121 p_init_msg_list IN VARCHAR2,
122 x_return_status OUT NOCOPY VARCHAR2,
123 x_msg_count OUT NOCOPY NUMBER,
124 x_msg_data OUT NOCOPY VARCHAR2,
125 p_ae_line_type IN VARCHAR2,
126 x_acc_lines OUT NOCOPY ACCT_TBL_TYPE)
127
128 IS
129
130 l_api_name CONSTANT VARCHAR2(40) := 'GET_RULE_LINES';
131 l_api_version CONSTANT NUMBER := 1.0;
132 l_row_count NUMBER;
133 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
134 l_acc_lines acct_tbl_type;
135 i NUMBER := 0;
136 l_set_of_books_id NUMBER ;
137 l_agr_id NUMBER;
138
139 l_array_of_segments OKL_ACCOUNTING_UTIL.seg_num_name_type;
140
141
142 CURSOR acg_csr(v_set_of_books_id NUMBER) IS
143
144 SELECT ID
145 FROM OKL_ACC_GEN_RULES_V
146 WHERE ae_line_type = p_ae_line_type
147 AND set_of_books_id = v_set_of_books_id;
148
149
150
151 CURSOR acgl_csr(v_agr_id NUMBER) IS
152 SELECT ID
153 ,SEGMENT
154 ,SEGMENT_NUMBER
155 ,AGR_ID
156 ,SOURCE
157 ,CONSTANTS
158 ,OBJECT_VERSION_NUMBER
159 ,ATTRIBUTE_CATEGORY
160 ,ATTRIBUTE1
161 ,ATTRIBUTE2
162 ,ATTRIBUTE3
163 ,ATTRIBUTE4
164 ,ATTRIBUTE5
165 ,ATTRIBUTE6
166 ,ATTRIBUTE7
167 ,ATTRIBUTE8
168 ,ATTRIBUTE9
169 ,ATTRIBUTE10
170 ,ATTRIBUTE11
171 ,ATTRIBUTE12
172 ,ATTRIBUTE13
173 ,ATTRIBUTE14
174 ,ATTRIBUTE15
175 ,CREATED_BY
176 ,CREATION_DATE
177 ,LAST_UPDATED_BY
178 ,LAST_UPDATE_DATE
179 ,LAST_UPDATE_LOGIN
180 FROM OKL_ACC_GEN_RUL_LNS_V
181 WHERE agr_id = v_agr_id;
182
183 acgl_rec acgl_csr%ROWTYPE;
184
185
186
187
188 BEGIN
189
190 x_return_status := OKL_API.G_RET_STS_SUCCESS;
191
192 -- Get set of books id and ORG id
193
194 l_set_of_books_id := OKL_ACCOUNTING_UTIL.GET_SET_OF_BOOKS_ID;
195
196
197 -- Check if the record exists in the Header Table.
198
199 OPEN acg_csr(l_set_of_books_id);
200 FETCH acg_csr INTO l_agr_id;
201 IF (acg_csr%NOTFOUND) THEN
202
203 OKL_ACCOUNTING_UTIL.GET_ACCOUNTING_SEGMENT(p_segment_array => l_array_of_segments);
204
205 FOR i IN 1..l_array_of_segments.seg_num.COUNT
206
207 LOOP
208 l_acc_lines(i).SEGMENT_NUMBER := l_array_of_segments.seg_num(i);
209 l_acc_lines(i).SEGMENT := l_array_of_segments.seg_name(i);
210 l_acc_lines(i).SEGMENT_DESC := l_array_of_segments.seg_desc(i);
211 l_acc_lines(i).ae_line_type := p_ae_line_type;
212 END LOOP;
213
214 ELSE
215
216 i := 0;
217
218 FOR acgl_rec IN acgl_csr(l_agr_id)
219
220 LOOP
221
222 i := i + 1;
223 l_acc_lines(i).ID := acgl_rec.ID;
224 l_acc_lines(i).AE_LINE_TYPE := p_ae_line_type;
225 l_acc_lines(i).SEGMENT := acgl_rec.SEGMENT;
226 l_acc_lines(i).SEGMENT_DESC
227 := OKL_ACCOUNTING_UTIL.get_segment_desc(acgl_rec.SEGMENT);
228 l_acc_lines(i).SEGMENT_NUMBER := acgl_rec.SEGMENT_NUMBER;
229 l_acc_lines(i).AGR_ID := acgl_rec.AGR_ID;
230 l_acc_lines(i).SOURCE := acgl_rec.SOURCE;
231 l_acc_lines(i).CONSTANTS := acgl_rec.CONSTANTS;
232 l_acc_lines(i).OBJECT_VERSION_NUMBER := acgl_rec.OBJECT_VERSION_NUMBER;
233 l_acc_lines(i).ATTRIBUTE_CATEGORY := acgl_rec.ATTRIBUTE_CATEGORY;
234 l_acc_lines(i).ATTRIBUTE1 := acgl_rec.ATTRIBUTE1;
235 l_acc_lines(i).ATTRIBUTE2 := acgl_rec.ATTRIBUTE2;
236 l_acc_lines(i).ATTRIBUTE3 := acgl_rec.ATTRIBUTE3;
237 l_acc_lines(i).ATTRIBUTE4 := acgl_rec.ATTRIBUTE4;
238 l_acc_lines(i).ATTRIBUTE5 := acgl_rec.ATTRIBUTE5;
239 l_acc_lines(i).ATTRIBUTE6 := acgl_rec.ATTRIBUTE6;
240 l_acc_lines(i).ATTRIBUTE7 := acgl_rec.ATTRIBUTE7;
241 l_acc_lines(i).ATTRIBUTE8 := acgl_rec.ATTRIBUTE8;
242 l_acc_lines(i).ATTRIBUTE9 := acgl_rec.ATTRIBUTE9;
243 l_acc_lines(i).ATTRIBUTE10 := acgl_rec.ATTRIBUTE10;
244 l_acc_lines(i).ATTRIBUTE11 := acgl_rec.ATTRIBUTE11;
245 l_acc_lines(i).ATTRIBUTE12 := acgl_rec.ATTRIBUTE12;
246 l_acc_lines(i).ATTRIBUTE13 := acgl_rec.ATTRIBUTE13;
247 l_acc_lines(i).ATTRIBUTE14 := acgl_rec.ATTRIBUTE14;
248 l_acc_lines(i).ATTRIBUTE15 := acgl_rec.ATTRIBUTE15;
249 l_acc_lines(i).CREATED_BY := acgl_rec.CREATED_BY;
250 l_acc_lines(i).CREATION_DATE := acgl_rec.CREATION_DATE;
251 l_acc_lines(i).LAST_UPDATED_BY := acgl_rec.LAST_UPDATED_BY;
252 l_acc_lines(i).LAST_UPDATE_DATE := acgl_rec.LAST_UPDATE_DATE;
253 l_acc_lines(i).LAST_UPDATE_LOGIN := acgl_rec.LAST_UPDATE_LOGIN;
254
255 END LOOP;
256
257 END IF;
258
259 CLOSE acg_csr;
260
261 x_acc_lines := l_acc_lines;
262
263
264 EXCEPTION
265
266 WHEN OTHERS THEN
267
268 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
269
270 END GET_RULE_LINES;
271
272
273 PROCEDURE MOVE_ACC_TO_AUL(p_acc_lines IN acct_tbl_type,
274 p_aulv_tbl OUT NOCOPY aulv_tbl_type)
275 IS
276
277 i NUMBER := 0;
278
279 BEGIN
280
281 FOR i IN 1..p_acc_lines.COUNT
282
283 LOOP
284
285 p_aulv_tbl(i).ID := p_acc_lines(i).ID;
286 p_aulv_tbl(i).SEGMENT := p_acc_lines(i).SEGMENT;
287 p_aulv_tbl(i).SEGMENT_NUMBER := p_acc_lines(i).SEGMENT_NUMBER;
288 p_aulv_tbl(i).AGR_ID := p_acc_lines(i).AGR_ID;
289 p_aulv_tbl(i).SOURCE := p_acc_lines(i).SOURCE;
290 p_aulv_tbl(i).OBJECT_VERSION_NUMBER := p_acc_lines(i).OBJECT_VERSION_NUMBER;
291 p_aulv_tbl(i).CONSTANTS := p_acc_lines(i).CONSTANTS;
292 p_aulv_tbl(i).ATTRIBUTE_CATEGORY := p_acc_lines(i).ATTRIBUTE_CATEGORY;
293 p_aulv_tbl(i).ATTRIBUTE1 := p_acc_lines(i).ATTRIBUTE1;
294 p_aulv_tbl(i).ATTRIBUTE2 := p_acc_lines(i).ATTRIBUTE2;
295 p_aulv_tbl(i).ATTRIBUTE3 := p_acc_lines(i).ATTRIBUTE3;
296 p_aulv_tbl(i).ATTRIBUTE4 := p_acc_lines(i).ATTRIBUTE4;
297 p_aulv_tbl(i).ATTRIBUTE5 := p_acc_lines(i).ATTRIBUTE5;
298 p_aulv_tbl(i).ATTRIBUTE6 := p_acc_lines(i).ATTRIBUTE6;
299 p_aulv_tbl(i).ATTRIBUTE7 := p_acc_lines(i).ATTRIBUTE7;
300 p_aulv_tbl(i).ATTRIBUTE8 := p_acc_lines(i).ATTRIBUTE8;
301 p_aulv_tbl(i).ATTRIBUTE9 := p_acc_lines(i).ATTRIBUTE9;
302 p_aulv_tbl(i).ATTRIBUTE10 := p_acc_lines(i).ATTRIBUTE10;
303 p_aulv_tbl(i).ATTRIBUTE11 := p_acc_lines(i).ATTRIBUTE11;
304 p_aulv_tbl(i).ATTRIBUTE12 := p_acc_lines(i).ATTRIBUTE12;
305 p_aulv_tbl(i).ATTRIBUTE13 := p_acc_lines(i).ATTRIBUTE13;
306 p_aulv_tbl(i).ATTRIBUTE14 := p_acc_lines(i).ATTRIBUTE14;
307 p_aulv_tbl(i).ATTRIBUTE15 := p_acc_lines(i).ATTRIBUTE15;
308 p_aulv_tbl(i).CREATED_BY := p_acc_lines(i).CREATED_BY;
309 p_aulv_tbl(i).CREATION_DATE := p_acc_lines(i).CREATION_DATE;
310 p_aulv_tbl(i).LAST_UPDATED_BY := p_acc_lines(i).LAST_UPDATED_BY;
311 p_aulv_tbl(i).LAST_UPDATE_DATE := p_acc_lines(i).LAST_UPDATE_DATE;
312 p_aulv_tbl(i).LAST_UPDATE_LOGIN := p_acc_lines(i).LAST_UPDATE_LOGIN;
313
314 END LOOP;
315
316 END MOVE_ACC_TO_AUL;
317
318
319 PROCEDURE MOVE_AUL_TO_ACC(p_aulv_tbl IN aulv_tbl_type,
320 p_acc_lines OUT NOCOPY acct_tbl_type)
321 IS
322
323 i NUMBER := 0;
324
325 BEGIN
326
327 FOR i IN 1..p_acc_lines.COUNT
328
329 LOOP
330
331 p_acc_lines(i).ID := p_aulv_tbl(i).ID;
332 p_acc_lines(i).SEGMENT := p_aulv_tbl(i).SEGMENT;
333 p_acc_lines(i).SEGMENT_DESC
334 := OKL_ACCOUNTING_UTIL.get_segment_desc(p_aulv_tbl(i).SEGMENT);
335 p_acc_lines(i).SEGMENT_NUMBER := p_aulv_tbl(i).SEGMENT_NUMBER;
336 p_acc_lines(i).AGR_ID := p_aulv_tbl(i).AGR_ID;
337 p_acc_lines(i).SOURCE := p_aulv_tbl(i).SOURCE;
338 p_acc_lines(i).OBJECT_VERSION_NUMBER := p_aulv_tbl(i).OBJECT_VERSION_NUMBER;
339 p_acc_lines(i).CONSTANTS := p_aulv_tbl(i).CONSTANTS;
340 p_acc_lines(i).ATTRIBUTE_CATEGORY := p_aulv_tbl(i).ATTRIBUTE_CATEGORY;
341 p_acc_lines(i).ATTRIBUTE1 := p_aulv_tbl(i).ATTRIBUTE1;
342 p_acc_lines(i).ATTRIBUTE2 := p_aulv_tbl(i).ATTRIBUTE2;
343 p_acc_lines(i).ATTRIBUTE3 := p_aulv_tbl(i).ATTRIBUTE3;
344 p_acc_lines(i).ATTRIBUTE4 := p_aulv_tbl(i).ATTRIBUTE4;
345 p_acc_lines(i).ATTRIBUTE5 := p_aulv_tbl(i).ATTRIBUTE5;
346 p_acc_lines(i).ATTRIBUTE6 := p_aulv_tbl(i).ATTRIBUTE6;
347 p_acc_lines(i).ATTRIBUTE7 := p_aulv_tbl(i).ATTRIBUTE7;
348 p_acc_lines(i).ATTRIBUTE8 := p_aulv_tbl(i).ATTRIBUTE8;
349 p_acc_lines(i).ATTRIBUTE9 := p_aulv_tbl(i).ATTRIBUTE9;
350 p_acc_lines(i).ATTRIBUTE10 := p_aulv_tbl(i).ATTRIBUTE10;
351 p_acc_lines(i).ATTRIBUTE11 := p_aulv_tbl(i).ATTRIBUTE11;
352 p_acc_lines(i).ATTRIBUTE12 := p_aulv_tbl(i).ATTRIBUTE12;
353 p_acc_lines(i).ATTRIBUTE13 := p_aulv_tbl(i).ATTRIBUTE13;
354 p_acc_lines(i).ATTRIBUTE14 := p_aulv_tbl(i).ATTRIBUTE14;
355 p_acc_lines(i).ATTRIBUTE15 := p_aulv_tbl(i).ATTRIBUTE15;
356 p_acc_lines(i).CREATED_BY := p_aulv_tbl(i).CREATED_BY;
357 p_acc_lines(i).CREATION_DATE := p_aulv_tbl(i).CREATION_DATE;
358 p_acc_lines(i).LAST_UPDATED_BY := p_aulv_tbl(i).LAST_UPDATED_BY;
359 p_acc_lines(i).LAST_UPDATE_DATE := p_aulv_tbl(i).LAST_UPDATE_DATE;
360 p_acc_lines(i).LAST_UPDATE_LOGIN := p_aulv_tbl(i).LAST_UPDATE_LOGIN;
361
362 END LOOP;
363
364 END MOVE_AUL_TO_ACC;
365
366
367
368 PROCEDURE UPDT_RULE_LINES(p_api_version IN NUMBER,
369 p_init_msg_list IN VARCHAR2,
370 x_return_status OUT NOCOPY VARCHAR2,
371 x_msg_count OUT NOCOPY NUMBER,
372 x_msg_data OUT NOCOPY VARCHAR2,
373 p_acc_lines IN ACCT_TBL_TYPE,
374 x_acc_lines OUT NOCOPY ACCT_TBL_TYPE)
375
376 IS
377
378
379 CURSOR acg_csr(v_set_of_books_id NUMBER,
380 v_ae_line_type VARCHAR2) IS
381
382 SELECT '1'
383 FROM OKL_ACC_GEN_RULES_V
384 WHERE ae_line_type = v_ae_line_type
385 AND set_of_books_id = v_set_of_books_id;
386
387 l_dummy VARCHAR2(1);
388 l_api_version NUMBER := 1.0;
389 l_init_msg_list VARCHAR2(1) := OKL_API.G_FALSE;
390 l_msg_count NUMBER := 0;
391 l_msg_data VARCHAR2(2000);
392 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
393 l_ae_line_type OKL_ACCT_GEN_RULES_V.AE_LINE_TYPE%TYPE;
394
395 l_aulv_tbl_in AULV_TBL_TYPE;
396 l_aulv_tbl_out AULV_TBL_TYPE;
397
398 l_agrv_rec_in AGRV_REC_TYPE;
399 l_agrv_rec_out AGRV_REC_TYPE;
400 l_set_of_books_id NUMBER ;
401 i NUMBER := 0;
402 l_api_name CONSTANT VARCHAR2(40) := 'UPDT_RULE_LINES';
403
404 BEGIN
405 -- Added by Santonyr 13-Aug-2002
406 -- Fixed bug 2500771
407
408 l_return_status := Okl_Api.START_ACTIVITY(l_api_name
409 ,G_PKG_NAME
410 ,p_init_msg_list
411 ,l_api_version
412 ,p_api_version
413 ,'_PVT'
414 ,l_return_status);
415 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
416 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
417 ELSIF (l_return_Status = OKL_API.G_RET_STS_ERROR) THEN
418 RAISE FND_API.G_EXC_ERROR;
419 END IF;
420
421
422 -- Get set of books id and ORG id
423
424 l_set_of_books_id := OKL_ACCOUNTING_UTIL.GET_SET_OF_BOOKS_ID;
425 l_ae_line_type := p_acc_lines(1).ae_line_type;
426
427
428 MOVE_ACC_TO_AUL(p_acc_lines => p_acc_lines,
429 p_aulv_tbl => l_aulv_tbl_in);
430
431
432 OPEN acg_csr(l_set_of_books_id,
433 l_ae_line_type);
434
435 FETCH acg_csr INTO l_dummy;
436
437 IF (acg_csr%NOTFOUND) THEN
438
439
440 l_agrv_rec_in.ae_line_type := l_ae_line_type;
441
442 OKL_ACC_GEN_RULE_PUB.create_acc_gen_rule(p_api_version => l_api_version,
443 p_init_msg_list => l_init_msg_list,
444 x_return_status => l_return_status,
445 x_msg_count => l_msg_count,
446 x_msg_data => l_msg_data,
447 p_agrv_rec => l_agrv_rec_in,
448 p_aulv_tbl => l_aulv_tbl_in,
449 x_agrv_rec => l_agrv_rec_out,
450 x_aulv_tbl => l_aulv_tbl_out);
451
452
453
454
455 ELSE
456
457
458 OKL_ACC_GEN_RULE_PUB.update_acc_gen_rule_lns(p_api_version => l_api_version,
459 p_init_msg_list => l_init_msg_list,
460 x_return_status => l_return_status,
461 x_msg_count => l_msg_count,
462 x_msg_data => l_msg_data,
463 p_aulv_tbl => l_aulv_tbl_in,
464 x_aulv_tbl => l_aulv_tbl_out);
465
466
467
468 END IF;
469
470
471 CLOSE acg_csr;
472
473 x_return_status := l_return_status;
474 x_msg_data := l_msg_data;
475 x_msg_count := l_msg_count;
476
477 MOVE_AUL_TO_ACC(l_aulv_tbl_out,
478 x_acc_lines);
479
480 -- Added by Santonyr 13-Aug-2002
481 -- Fixed bug 2500771
482
483 x_return_status := l_return_status;
484 Okl_Api.end_activity(x_msg_count, x_msg_data);
485
486 EXCEPTION
487
488 WHEN FND_API.G_EXC_ERROR THEN
489 x_return_status := OKL_API.HANDLE_EXCEPTIONS(l_api_name
490 ,g_pkg_name
491 ,'OKL_API.G_RET_STS_ERROR'
492 ,x_msg_count
493 ,x_msg_data
494 ,'_PVT');
495 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
496 x_return_status := OKL_API.HANDLE_EXCEPTIONS(l_api_name
497 ,g_pkg_name
498 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
499 ,x_msg_count
500 ,x_msg_data
501 ,'_PVT');
502 WHEN OTHERS THEN
503 x_return_status := OKL_API.HANDLE_EXCEPTIONS(l_api_name
504 ,g_pkg_name
505 ,'OTHERS'
506 ,x_msg_count
507 ,x_msg_data
508 ,'_PVT');
509
510
511 END UPDT_RULE_LINES;
512
513
514 END OKL_ACCT_GEN_RULE_PVT;