[Home] [Help]
PACKAGE BODY: APPS.OKL_DEAL_PAYMENTS_PVT
Source
1 Package body OKL_DEAL_PAYMENTS_PVT as
2 /* $Header: OKLRDPYB.pls 120.0 2007/05/04 14:55:55 sjalasut noship $ */
3 -------------------------------------------------------------------------------------------------
4 -- GLOBAL MESSAGE CONSTANTS
5 -------------------------------------------------------------------------------------------------
6 G_INVALID_CRITERIA CONSTANT VARCHAR2(200) := 'OKL_LLA_INVALID_CRITERIA';
7 G_FND_APP CONSTANT VARCHAR2(200) := OKL_API.G_FND_APP;
8 G_INVALID_VALUE CONSTANT VARCHAR2(200) := OKL_API.G_INVALID_VALUE;
9 G_COL_NAME_TOKEN CONSTANT VARCHAR2(200) := OKL_API.G_COL_NAME_TOKEN;
10 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKC_CONTRACTS_UNEXP_ERROR';
11 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'SQLerrm';
12 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'SQLcode';
13 -------------------------------------------------------------------------------------------------
14 -- GLOBAL OKL MESSAGES
15 -------------------------------------------------------------------------------------------------
16 G_AMOUNT_FORMAT CONSTANT VARCHAR2(200) := 'OKL_AMOUNT_FORMAT';
17 G_REQUIRED_VALUE CONSTANT VARCHAR2(200) := 'OKL_REQUIRED_VALUE';
18 G_LLA_AST_SERIAL CONSTANT VARCHAR2(200) := 'OKL_LLA_AST_SERIAL';
19 G_MISSING_CONTRACT CONSTANT Varchar2(200) := 'OKL_LLA_CONTRACT_NOT_FOUND';
20 G_CONTRACT_ID_TOKEN CONSTANT Varchar2(30) := 'CONTRACT_ID';
21 -------------------------------------------------------------------------------------------------
22 -- GLOBAL EXCEPTION
23 -------------------------------------------------------------------------------------------------
24 G_EXCEPTION_HALT_VALIDATION EXCEPTION;
25 G_EXCEPTION_STOP_VALIDATION EXCEPTION;
26 G_API_TYPE CONSTANT VARCHAR2(4) := '_PVT';
27 G_API_VERSION CONSTANT NUMBER := 1.0;
28 G_SCOPE CONSTANT VARCHAR2(4) := '_PVT';
29 -------------------------------------------------------------------------------------------------
30 -- GLOBAL VARIABLES
31 -------------------------------------------------------------------------------------------------
32 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKL_DEAL_ASSET_PVT';
33 G_APP_NAME CONSTANT VARCHAR2(3) := OKL_API.G_APP_NAME;
34 -------------------------------------------------------------------------------------------------
35
36 FUNCTION get_fee_service_name(
37 p_chr_id IN NUMBER,
38 p_cle_id IN NUMBER,
39 p_lse_id IN NUMBER,
40 p_parent_cle_id IN NUMBER)
41 RETURN VARCHAR2 IS
42
43 CURSOR l_fee_name_csr(p_chr_id IN NUMBER,
44 p_cle_id IN NUMBER) IS
45 SELECT styt.name
46 FROM okl_strm_type_tl styt,
47 okc_k_items cim_fee
48 WHERE cim_fee.cle_id = p_cle_id
49 AND cim_fee.dnz_chr_id = p_chr_id
50 AND cim_fee.jtot_object1_code = 'OKL_STRMTYP'
51 AND styt.id = cim_fee.object1_id1
52 AND styt.language = USERENV('LANG');
53
54 CURSOR l_service_name_csr(p_chr_id IN NUMBER,
55 p_cle_id IN NUMBER) IS
56 SELECT msit.description
57 FROM mtl_system_items_tl msit,
58 okc_k_items cim_svc
59 WHERE cim_svc.cle_id = p_cle_id
60 AND cim_svc.dnz_chr_id = p_chr_id
61 AND cim_svc.jtot_object1_code = 'OKX_SERVICE'
62 AND msit.inventory_item_id = cim_svc.object1_id1
63 AND msit.organization_id = cim_svc.object1_id2
64 AND msit.language = USERENV('LANG');
65
66 l_fee_service_name VARCHAR2(2000);
67
68 BEGIN
69
70 l_fee_service_name := NULL;
71
72 IF p_lse_id = 52 THEN --'FEE'
73
74 OPEN l_fee_name_csr(p_cle_id => p_cle_id,
75 p_chr_id => p_chr_id);
76 FETCH l_fee_name_csr INTO l_fee_service_name;
77 CLOSE l_fee_name_csr;
78
79 ELSIF p_lse_id = 48 THEN --'SOLD_SERVICE'
80
81 OPEN l_service_name_csr(p_cle_id => p_cle_id,
82 p_chr_id => p_chr_id);
83 FETCH l_service_name_csr INTO l_fee_service_name;
84 CLOSE l_service_name_csr;
85
86 ELSIF p_lse_id = 53 THEN --'LINK_FEE_ASSET'
87
88
89 OPEN l_fee_name_csr(p_cle_id => p_parent_cle_id,
90 p_chr_id => p_chr_id);
91 FETCH l_fee_name_csr INTO l_fee_service_name;
92 CLOSE l_fee_name_csr;
93
94 ELSIF p_lse_id = 49 THEN --'LINK_SERV_ASSET'
95
96
97 OPEN l_service_name_csr(p_cle_id => p_parent_cle_id,
98 p_chr_id => p_chr_id);
99 FETCH l_service_name_csr INTO l_fee_service_name;
100 CLOSE l_service_name_csr;
101
102 END IF;
103
104 RETURN l_fee_service_name;
105 END;
106
107 FUNCTION get_asset_number(
108 p_chr_id IN NUMBER,
109 p_cle_id IN NUMBER,
110 p_lse_id IN NUMBER)
111 RETURN VARCHAR2 IS
112
113 l_asset_number OKC_K_LINES_TL.name%TYPE;
114
115 CURSOR l_asset_num_fin_csr(p_cle_id IN NUMBER) IS
116 SELECT clet_fin.name
117 FROM okc_k_lines_tl clet_fin
118 WHERE clet_fin.id = p_cle_id
119 AND clet_fin.language = USERENV('LANG');
120
121 CURSOR l_asset_num_cov_asset_csr(p_cle_id IN NUMBER,
122 p_chr_id IN NUMBER) IS
123 SELECT clet_fin.name
124 FROM okc_k_lines_tl clet_fin,
125 okc_k_items cim_cov_asset
126 WHERE cim_cov_asset.cle_id = p_cle_id
127 AND cim_cov_asset.dnz_chr_id = p_chr_id
128 AND cim_cov_asset.jtot_object1_code = 'OKX_COVASST'
129 AND clet_fin.id = cim_cov_asset.object1_id1
130 AND clet_fin.language = USERENV('LANG');
131
132 BEGIN
133
134 l_asset_number := NULL;
135
136 IF p_lse_id = 33 THEN --'FREE_FORM1'
137
138 OPEN l_asset_num_fin_csr(p_cle_id => p_cle_id);
139 FETCH l_asset_num_fin_csr INTO l_asset_number;
140 CLOSE l_asset_num_fin_csr;
141
142 ELSIF p_lse_id IN (49,53) THEN --'LINK_FEE_ASSET','LINK_SERV_ASSET'
143
144 OPEN l_asset_num_cov_asset_csr(p_cle_id => p_cle_id,
145 p_chr_id => p_chr_id);
146 FETCH l_asset_num_cov_asset_csr INTO l_asset_number;
147 CLOSE l_asset_num_cov_asset_csr;
148 END IF;
149
150 RETURN l_asset_number;
151 END;
152
153 PROCEDURE load_payment_header(
154 p_api_version IN NUMBER,
155 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
156 x_return_status OUT NOCOPY VARCHAR2,
157 x_msg_count OUT NOCOPY NUMBER,
158 x_msg_data OUT NOCOPY VARCHAR2,
159 p_chr_id IN NUMBER,
160 p_cle_id IN NUMBER,
161 x_service_fee_cle_id OUT NOCOPY NUMBER,
162 x_service_fee_name OUT NOCOPY VARCHAR2,
163 x_asset_cle_id OUT NOCOPY NUMBER,
164 x_asset_number OUT NOCOPY VARCHAR2,
165 x_asset_description OUT NOCOPY VARCHAR2) IS
166
167 l_api_name CONSTANT VARCHAR2(30) := 'LOAD_PAYMENT_HEADER';
168 l_api_version CONSTANT NUMBER := 1;
169
170 CURSOR l_line_csr(p_cle_id IN NUMBER) IS
171 SELECT lse_id,
172 cle_id
173 FROM okc_k_lines_b cle
174 WHERE id = p_cle_id;
175
176 l_line_rec l_line_csr%ROWTYPE;
177
178 CURSOR l_asset_num_fin_csr(p_cle_id IN NUMBER) IS
179 SELECT clet_fin.id,
180 clet_fin.name,
181 clet_fin.item_description
182 FROM okc_k_lines_tl clet_fin
183 WHERE clet_fin.id = p_cle_id
184 AND clet_fin.language = USERENV('LANG');
185
186 CURSOR l_asset_num_cov_asset_csr(p_cle_id IN NUMBER,
187 p_chr_id IN NUMBER) IS
188 SELECT clet_fin.id,
189 clet_fin.name,
190 clet_fin.item_description
191 FROM okc_k_lines_tl clet_fin,
192 okc_k_items cim_cov_asset
193 WHERE cim_cov_asset.cle_id = p_cle_id
194 AND cim_cov_asset.dnz_chr_id = p_chr_id
195 AND cim_cov_asset.jtot_object1_code = 'OKX_COVASST'
196 AND clet_fin.id = cim_cov_asset.object1_id1
197 AND clet_fin.language = USERENV('LANG');
198
199
200 l_service_fee_cle_id OKC_K_LINES_B.id%TYPE;
201 l_service_fee_name VARCHAR2(2000);
202 l_asset_cle_id OKC_K_LINES_B.id%TYPE;
203 l_asset_number OKC_K_LINES_TL.name%TYPE;
204 l_asset_description OKC_K_LINES_TL.item_description%TYPE;
205
206 BEGIN
207 x_return_status := OKL_API.G_RET_STS_SUCCESS;
208 -- Call start_activity to create savepoint, check compatibility and initialize message list
209 x_return_status := OKL_API.START_ACTIVITY(
210 p_api_name => l_api_name,
211 p_pkg_name => g_pkg_name,
212 p_init_msg_list => p_init_msg_list,
213 l_api_version => l_api_version,
214 p_api_version => p_api_version,
215 p_api_type => g_api_type,
216 x_return_status => x_return_status);
217
218 -- check if activity started successfully
219 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
220 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
221 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
222 RAISE OKL_API.G_EXCEPTION_ERROR;
223 END IF;
224
225 l_service_fee_cle_id := NULL;
226 l_service_fee_name := NULL;
227 l_asset_cle_id := NULL;
228 l_asset_number := NULL;
229 l_asset_description := NULL;
230
231 IF p_cle_id IS NOT NULL THEN
232
233 OPEN l_line_csr(p_cle_id => p_cle_id);
234 FETCH l_line_csr INTO l_line_rec;
235 CLOSE l_line_csr;
236
237 IF l_line_rec.lse_id = 33 THEN
238
239 OPEN l_asset_num_fin_csr(p_cle_id => p_cle_id);
240 FETCH l_asset_num_fin_csr INTO l_asset_cle_id,
241 l_asset_number,
242 l_asset_description;
243 CLOSE l_asset_num_fin_csr;
244
245 ELSIF l_line_rec.lse_id IN (49,53) THEN --'LINK_FEE_ASSET','LINK_SERV_ASSET'
246
247 OPEN l_asset_num_cov_asset_csr(p_chr_id => p_chr_id,
248 p_cle_id => p_cle_id);
249 FETCH l_asset_num_cov_asset_csr INTO l_asset_cle_id,
250 l_asset_number,
251 l_asset_description;
252 CLOSE l_asset_num_cov_asset_csr;
253 END IF;
254
255 IF l_line_rec.lse_id IN (48,52) THEN -- 'FEE', 'SOLD_SERVICE'
256
257 l_service_fee_cle_id := p_cle_id;
258 l_service_fee_name := get_fee_service_name(p_chr_id => p_chr_id
259 ,p_cle_id => p_cle_id
260 ,p_lse_id => l_line_rec.lse_id
261 ,p_parent_cle_id => l_line_rec.cle_id);
262
263 ELSIF l_line_rec.lse_id IN (49,53) THEN --'LINK_FEE_ASSET','LINK_SERV_ASSET'
264
265 l_service_fee_cle_id := l_line_rec.cle_id;
266 l_service_fee_name := get_fee_service_name(p_chr_id => p_chr_id
267 ,p_cle_id => p_cle_id
268 ,p_lse_id => l_line_rec.lse_id
269 ,p_parent_cle_id => l_line_rec.cle_id);
270 END IF;
271
272 END IF;
273
274 x_service_fee_cle_id := l_service_fee_cle_id;
275 x_service_fee_name := l_service_fee_name;
276 x_asset_cle_id := l_asset_cle_id;
277 x_asset_number := l_asset_number;
278 x_asset_description := l_asset_description;
279
280 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
281
282 EXCEPTION
283 WHEN OKL_API.G_EXCEPTION_ERROR THEN
284 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
285 p_api_name => l_api_name,
286 p_pkg_name => g_pkg_name,
287 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
288 x_msg_count => x_msg_count,
289 x_msg_data => x_msg_data,
290 p_api_type => g_api_type);
291
292 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
293 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
294 p_api_name => l_api_name,
295 p_pkg_name => g_pkg_name,
296 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
297 x_msg_count => x_msg_count,
298 x_msg_data => x_msg_data,
299 p_api_type => g_api_type);
300
301 WHEN OTHERS THEN
302 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
303 p_api_name => l_api_name,
304 p_pkg_name => g_pkg_name,
305 p_exc_name => 'OTHERS',
306 x_msg_count => x_msg_count,
307 x_msg_data => x_msg_data,
308 p_api_type => g_api_type);
309 END load_payment_header;
310
311 End OKL_DEAL_PAYMENTS_PVT;