1 PACKAGE BODY OKL_AM_LOAD_CAT_BK_PVT AS
2 /* $Header: OKLRLCBB.pls 120.4 2006/07/11 09:49:40 dkagrawa noship $ */
3
4 -- Start of comments
5 --
6 -- Procedure Name : create_hold_setup_trx
7 -- Description : The main body of the package. This procedure finds all the unique combinations of
8 -- category_id and book_type_code from fa_category_book_defaults and loads them into
9 -- okl_amort_hold_setups_v. Before loading the data into okl_amort_hold_setups_v, API
10 -- queries this view to make sure that the category and book type code combination
11 -- does not already exist.
12 -- Business Rules :
13 -- Version : 1.0
14 -- History : SECHAWLA 05-MAY-04 3578894 : Added 'TAX' book class in cursor l_facatbookdef_csr
15 -- End of comments
16
17
18 PROCEDURE create_hold_setup_trx( p_api_version IN NUMBER,
19 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
20 p_book_type_code IN fa_book_controls.book_type_code%TYPE,
21 x_return_status OUT NOCOPY VARCHAR2,
22 x_msg_count OUT NOCOPY NUMBER,
23 x_msg_data OUT NOCOPY VARCHAR2,
24 x_amhv_tbl OUT NOCOPY amhv_tbl_type
25 ) IS
26
27
28
29 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
30 l_dummy VARCHAR2(1);
31
32
33 lp_amhv_tbl amhv_tbl_type;
34 lx_amhv_tbl amhv_tbl_type;
35 i NUMBER ;
36
37 l_api_version CONSTANT NUMBER := 1;
38 l_api_name CONSTANT VARCHAR2(30) := 'create_hold_setup_trx';
39
40 -- This cursor selects all unique combinations of category_id and book_type_code from fa_category_book_defaults
41 CURSOR l_facatbookdef_csr(p_book_type_code fa_book_controls.book_type_code%TYPE) IS
42 -- SECHAWLA 05-MAY-04 3578894 : fetch category/book combinations for both corporate and tax books
43 SELECT DISTINCT fac.category_id, fac.book_type_code
44 FROM fa_category_book_defaults fac, fa_book_controls fbc
45 WHERE fac.book_type_code = fbc.book_type_code AND fbc.book_class IN ('CORPORATE','TAX')
46 AND fbc.distribution_source_book = NVL(p_book_type_code, fbc.distribution_source_book);
47
48 -- This cursor is used to check if the unique combination of category_id and book_type_code from
49 -- fa_category_book_defaults already exists in okl_amort_hold_setups_v
50 CURSOR l_amortholdsetup_csr(p_id number, p_code varchar2) IS
51 SELECT 'x'
52 FROM OKL_AMORT_HOLD_SETUPS
53 WHERE category_id = p_id
54 AND book_type_code = p_code;
55
56
57 BEGIN
58
59 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
60 G_PKG_NAME,
61 p_init_msg_list,
62 l_api_version,
63 p_api_version,
64 '_PVT',
65 x_return_status);
66
67
68
69 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
70 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
71 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
72 RAISE OKL_API.G_EXCEPTION_ERROR;
73 END IF;
74
75 i := 0;
76
77 -- loop thru all the rows from cursor l_facatbookdef_csr and put them in the table lp_amhv_tbl if
78 -- those rows don't already exist in okl_amort_hold_setups_v
79 FOR l_facatbookdef_rec IN l_facatbookdef_csr(p_book_type_code) LOOP
80 OPEN l_amortholdsetup_csr(l_facatbookdef_rec.category_id, l_facatbookdef_rec.book_type_code);
81 FETCH l_amortholdsetup_csr INTO l_dummy;
82
83 IF l_amortholdsetup_csr%NOTFOUND THEN
84 lp_amhv_tbl(i).book_type_code := l_facatbookdef_rec.book_type_code ;
85 lp_amhv_tbl(i).category_id := l_facatbookdef_rec.category_id;
86 i := i + 1;
87
88
89 END IF;
90 CLOSE l_amortholdsetup_csr;
91
92 END LOOP;
93
94
95 IF (lp_amhv_tbl.COUNT > 0 ) THEN
96 okl_amort_hold_setups_pub.insert_amort_hold_setups(
97 p_api_version => 1.0
98 ,p_init_msg_list => FND_API.G_FALSE
99 ,x_return_status => x_return_status
100 ,x_msg_count => x_msg_count
101 ,x_msg_data => x_msg_data
102 ,p_amhv_tbl => lp_amhv_tbl
103 ,x_amhv_tbl => lx_amhv_tbl);
104
105 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
106 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
107 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
108 RAISE OKC_API.G_EXCEPTION_ERROR;
109 END IF;
110
111 x_amhv_tbl := lx_amhv_tbl;
112 END IF;
113
114
115
116 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
117
118 EXCEPTION
119 WHEN OKL_API.G_EXCEPTION_ERROR THEN
120 IF l_amortholdsetup_csr%ISOPEN THEN
121 CLOSE l_amortholdsetup_csr;
122 END IF;
123 IF l_facatbookdef_csr%ISOPEN THEN
124 CLOSE l_facatbookdef_csr;
125 END IF;
126 x_return_status := OKL_API.HANDLE_EXCEPTIONS
127 (
128 l_api_name,
129 G_PKG_NAME,
130 'OKL_API.G_RET_STS_ERROR',
131 x_msg_count,
132 x_msg_data,
133 '_PVT'
134 );
135 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
136 IF l_amortholdsetup_csr%ISOPEN THEN
137 CLOSE l_amortholdsetup_csr;
138 END IF;
139 IF l_facatbookdef_csr%ISOPEN THEN
140 CLOSE l_facatbookdef_csr;
141 END IF;
142 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
143 (
144 l_api_name,
145 G_PKG_NAME,
146 'OKL_API.G_RET_STS_UNEXP_ERROR',
147 x_msg_count,
148 x_msg_data,
149 '_PVT'
150 );
151 WHEN OTHERS THEN
152 IF l_amortholdsetup_csr%ISOPEN THEN
153 CLOSE l_amortholdsetup_csr;
154 END IF;
155 IF l_facatbookdef_csr%ISOPEN THEN
156 CLOSE l_facatbookdef_csr;
157 END IF;
158 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
159 (
160 l_api_name,
161 G_PKG_NAME,
162 'OTHERS',
163 x_msg_count,
164 x_msg_data,
165 '_PVT'
166 );
167 END create_hold_setup_trx;
168 END OKL_AM_LOAD_CAT_BK_PVT;
[Home] [Help]