[Home] [Help]
PACKAGE BODY: APPS.OKL_DEAL_TERMS_PVT
Source
1 Package body OKL_DEAL_TERMS_PVT as
2 /* $Header: OKLRDTRB.pls 120.8 2010/07/22 07:57:01 smadhava ship $ */
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_REQUIRED_VALUE CONSTANT VARCHAR2(200) := 'OKL_REQUIRED_VALUE';
17 G_LLA_AST_SERIAL CONSTANT VARCHAR2(200) := 'OKL_LLA_AST_SERIAL';
18 G_MISSING_CONTRACT CONSTANT Varchar2(200) := 'OKL_LLA_CONTRACT_NOT_FOUND';
19 G_CONTRACT_ID_TOKEN CONSTANT Varchar2(30) := 'CONTRACT_ID';
20 -------------------------------------------------------------------------------------------------
21 -- GLOBAL EXCEPTION
22 -------------------------------------------------------------------------------------------------
23 G_EXCEPTION_HALT_VALIDATION EXCEPTION;
24 G_EXCEPTION_STOP_VALIDATION EXCEPTION;
25 G_API_TYPE CONSTANT VARCHAR2(4) := '_PVT';
26 G_API_VERSION CONSTANT NUMBER := 1.0;
27 G_SCOPE CONSTANT VARCHAR2(4) := '_PVT';
28 -------------------------------------------------------------------------------------------------
29 -- GLOBAL VARIABLES
30 -------------------------------------------------------------------------------------------------
31 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKL_DEAL_TERMS_PVT';
32 G_APP_NAME CONSTANT VARCHAR2(3) := OKL_API.G_APP_NAME;
33 -------------------------------------------------------------------------------------------------
34
35 PROCEDURE delete_terms(
36 p_api_version IN NUMBER,
37 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
38 x_return_status OUT NOCOPY VARCHAR2,
39 x_msg_count OUT NOCOPY NUMBER,
40 x_msg_data OUT NOCOPY VARCHAR2,
41 p_chr_id IN NUMBER,
42 p_rgp_id IN NUMBER,
43 p_page_name IN VARCHAR2) IS
44 BEGIN
45 null;
46 END delete_terms;
47
48
49 PROCEDURE process_billing_setup(
50 p_api_version IN NUMBER,
51 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
52 x_return_status OUT NOCOPY VARCHAR2,
53 x_msg_count OUT NOCOPY NUMBER,
54 x_msg_data OUT NOCOPY VARCHAR2,
55 p_rgpv_rec IN billing_setup_rec_type,
56 x_rgpv_rec OUT NOCOPY billing_setup_rec_type) IS
57
58 l_api_name VARCHAR2(30) := 'process_billing_setup';
59 l_api_version CONSTANT NUMBER := 1.0;
60
61 lp_labill_rgpv_rec OKL_OKC_MIGRATION_PVT.rgpv_rec_type;
62 lx_labill_rgpv_rec OKL_OKC_MIGRATION_PVT.rgpv_rec_type;
63
64 lp_lapmth_rulv_rec Okl_Rule_Pub.rulv_rec_type;
65 lx_lapmth_rulv_rec Okl_Rule_Pub.rulv_rec_type;
66
67 lp_labacc_rulv_rec Okl_Rule_Pub.rulv_rec_type;
68 lx_labacc_rulv_rec Okl_Rule_Pub.rulv_rec_type;
69
70 lp_lainvd_rulv_rec Okl_Rule_Pub.rulv_rec_type;
71 lx_lainvd_rulv_rec Okl_Rule_Pub.rulv_rec_type;
72
73 lp_lainpr_rulv_rec Okl_Rule_Pub.rulv_rec_type;
74 lx_lainpr_rulv_rec Okl_Rule_Pub.rulv_rec_type;
75
76 lp_chrv_rec OKL_OKC_MIGRATION_PVT.chrv_rec_type;
77 lx_chrv_rec OKL_OKC_MIGRATION_PVT.chrv_rec_type;
78
79 lp_khrv_rec OKL_CONTRACT_PUB.khrv_rec_type;
80 lx_khrv_rec OKL_CONTRACT_PUB.khrv_rec_type;
81 l_msg_data VARCHAR2(4000);
82 l_msg_index_out number;
83
84 --sechawla 2-Jun-09 6826580
85 cursor l_inv_frmt_csr(cp_inv_frmt_code in varchar2) IS
86 select id
87 from okl_invoice_formats_v
88 where name = cp_inv_frmt_code;
89 l_inv_frmt_id number;
90
91 BEGIN
92
93 x_return_status := OKL_API.START_ACTIVITY(
94 p_api_name => l_api_name,
95 p_pkg_name => g_pkg_name,
96 p_init_msg_list => p_init_msg_list,
97 l_api_version => l_api_version,
98 p_api_version => p_api_version,
99 p_api_type => g_api_type,
100 x_return_status => x_return_status);
101
102 -- check if activity started successfully
103 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
104 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
105 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
106 RAISE OKL_API.G_EXCEPTION_ERROR;
107 END IF;
108
109 IF (p_rgpv_rec.rgp_id IS NULL) THEN
110
111 -- Create LABILL rule group
112 lp_labill_rgpv_rec.id := NULL;
113 lp_labill_rgpv_rec.rgd_code := 'LABILL';
114 lp_labill_rgpv_rec.dnz_chr_id := p_rgpv_rec.chr_id;
115 lp_labill_rgpv_rec.chr_id := p_rgpv_rec.chr_id;
116 lp_labill_rgpv_rec.rgp_type := 'KRG';
117
118 OKL_RULE_PUB.create_rule_group(
119 p_api_version => p_api_version,
120 p_init_msg_list => p_init_msg_list,
121 x_return_status => x_return_status,
122 x_msg_count => x_msg_count,
123 x_msg_data => x_msg_data,
124 p_rgpv_rec => lp_labill_rgpv_rec,
125 x_rgpv_rec => lx_labill_rgpv_rec);
126
127 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
128 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
129 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
130 RAISE OKC_API.G_EXCEPTION_ERROR;
131 END IF;
132
133 x_rgpv_rec.rgp_id := lx_labill_rgpv_rec.id;
134 x_rgpv_rec.rgp_labill_lapmth_id := lx_labill_rgpv_rec.id;
135 x_rgpv_rec.rgp_labill_labacc_id := lx_labill_rgpv_rec.id;
136 x_rgpv_rec.rgp_labill_lainvd_id := lx_labill_rgpv_rec.id;
137 x_rgpv_rec.rgp_labill_lainpr_id := lx_labill_rgpv_rec.id;
138
139 ElSE
140 -- Update LABILL rule group
141 lp_labill_rgpv_rec.id := p_rgpv_rec.rgp_id;
142 lp_labill_rgpv_rec.rgd_code := 'LABILL';
143 lp_labill_rgpv_rec.dnz_chr_id := p_rgpv_rec.chr_id;
144 lp_labill_rgpv_rec.chr_id := p_rgpv_rec.chr_id;
145 lp_labill_rgpv_rec.rgp_type := 'KRG';
146
147 OKL_RULE_PUB.update_rule_group(
148 p_api_version => p_api_version,
149 p_init_msg_list => p_init_msg_list,
150 x_return_status => x_return_status,
151 x_msg_count => x_msg_count,
152 x_msg_data => x_msg_data,
153 p_rgpv_rec => lp_labill_rgpv_rec,
154 x_rgpv_rec => lx_labill_rgpv_rec);
155
156 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
157 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
158 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
159 RAISE OKC_API.G_EXCEPTION_ERROR;
160 END IF;
161 x_rgpv_rec.rgp_id := p_rgpv_rec.rgp_id;
162
163 END IF;
164
165 lp_chrv_rec.id := p_rgpv_rec.chr_id;
166 lp_khrv_rec.id := p_rgpv_rec.chr_id;
167 lp_chrv_rec.bill_to_site_use_id := p_rgpv_rec.bill_to_site_use_id;
168
169 OKL_CONTRACT_PUB.update_contract_header(
170 p_api_version => p_api_version,
171 p_init_msg_list => p_init_msg_list,
172 x_return_status => x_return_status,
173 x_msg_count => x_msg_count,
174 x_msg_data => x_msg_data,
175 p_restricted_update => 'F',
176 p_chrv_rec => lp_chrv_rec,
177 p_khrv_rec => lp_khrv_rec,
178 x_chrv_rec => lx_chrv_rec,
179 x_khrv_rec => lx_khrv_rec);
180
181 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
182 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
183 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
184 RAISE OKC_API.G_EXCEPTION_ERROR;
185 END IF;
186
187
188 IF (p_rgpv_rec.rul_lapmth_id IS NULL) THEN
189 -- Create LAPMTH rule
190 lp_lapmth_rulv_rec.id := NULL;
191 lp_lapmth_rulv_rec.rgp_id := x_rgpv_rec.rgp_id;
192 lp_lapmth_rulv_rec.rule_information_category := 'LAPMTH';
193 lp_lapmth_rulv_rec.dnz_chr_id := p_rgpv_rec.chr_id;
194 lp_lapmth_rulv_rec.WARN_YN := 'N';
195 lp_lapmth_rulv_rec.STD_TEMPLATE_YN := 'N';
196 IF ((p_rgpv_rec.rul_lapmth_object1_id1 IS NOT NULL) AND (p_rgpv_rec.rul_lapmth_object1_id2 IS NOT NULL)) THEN
197 lp_lapmth_rulv_rec.object1_id1 := p_rgpv_rec.rul_lapmth_object1_id1;
198 lp_lapmth_rulv_rec.object1_id2 := p_rgpv_rec.rul_lapmth_object1_id2;
199 lp_lapmth_rulv_rec.jtot_object1_code := 'OKX_RCPTMTH';
200 END IF;
201
202 OKL_RULE_PUB.create_rule(
203 p_api_version => p_api_version,
204 p_init_msg_list => p_init_msg_list,
205 x_return_status => x_return_status,
206 x_msg_count => x_msg_count,
207 x_msg_data => x_msg_data,
208 p_rulv_rec => lp_lapmth_rulv_rec,
209 x_rulv_rec => lx_lapmth_rulv_rec);
210
211 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
212 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
213 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
214 RAISE OKC_API.G_EXCEPTION_ERROR;
215 END IF;
216
217 x_rgpv_rec.rul_lapmth_id := lx_lapmth_rulv_rec.id;
218
219 ELSE
220 -- update LAPMTH rule
221 lp_lapmth_rulv_rec.id := p_rgpv_rec.rul_lapmth_id;
222 lp_lapmth_rulv_rec.rgp_id := p_rgpv_rec.rgp_id;
223 lp_lapmth_rulv_rec.rule_information_category := 'LAPMTH';
224 lp_lapmth_rulv_rec.dnz_chr_id := p_rgpv_rec.chr_id;
225 lp_lapmth_rulv_rec.WARN_YN := 'N';
226 lp_lapmth_rulv_rec.STD_TEMPLATE_YN := 'N';
227 IF ((p_rgpv_rec.rul_lapmth_object1_id1 IS NOT NULL) AND (p_rgpv_rec.rul_lapmth_object1_id2 IS NOT NULL)) THEN
228 lp_lapmth_rulv_rec.object1_id1 := p_rgpv_rec.rul_lapmth_object1_id1;
229 lp_lapmth_rulv_rec.object1_id2 := p_rgpv_rec.rul_lapmth_object1_id2;
230 lp_lapmth_rulv_rec.jtot_object1_code := 'OKX_RCPTMTH';
231 --Bug# 7702487
232 ELSIF ((p_rgpv_rec.rul_lapmth_object1_id1 IS NULL) AND (p_rgpv_rec.rul_lapmth_object1_id2 IS NULL)) THEN
233 lp_lapmth_rulv_rec.object1_id1 := NULL;
234 lp_lapmth_rulv_rec.object1_id2 := NULL;
235 lp_lapmth_rulv_rec.jtot_object1_code := NULL;
236 END IF;
237
238 OKL_RULE_PUB.update_rule(
239 p_api_version => p_api_version,
240 p_init_msg_list => p_init_msg_list,
241 x_return_status => x_return_status,
242 x_msg_count => x_msg_count,
243 x_msg_data => x_msg_data,
244 p_rulv_rec => lp_lapmth_rulv_rec,
245 x_rulv_rec => lx_lapmth_rulv_rec);
246
247 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
248 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
249 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
250 RAISE OKC_API.G_EXCEPTION_ERROR;
251 END IF;
252
253 END IF;
254
255
256 IF (p_rgpv_rec.rul_labacc_id IS NULL) THEN
257 -- Create LABACC rule
258 lp_labacc_rulv_rec.id := NULL;
259 lp_labacc_rulv_rec.rgp_id := x_rgpv_rec.rgp_id;
260 lp_labacc_rulv_rec.rule_information_category := 'LABACC';
261 lp_labacc_rulv_rec.dnz_chr_id := p_rgpv_rec.chr_id;
262 lp_labacc_rulv_rec.WARN_YN := 'N';
263 lp_labacc_rulv_rec.STD_TEMPLATE_YN := 'N';
264 IF ((p_rgpv_rec.rul_labacc_object1_id1 IS NOT NULL) AND (p_rgpv_rec.rul_labacc_object1_id2 IS NOT NULL)) THEN
265 lp_labacc_rulv_rec.object1_id1 := p_rgpv_rec.rul_labacc_object1_id1;
266 lp_labacc_rulv_rec.object1_id2 := p_rgpv_rec.rul_labacc_object1_id2;
267 lp_labacc_rulv_rec.jtot_object1_code := 'OKX_CUSTBKAC';
268 END IF;
269
270 OKL_RULE_PUB.create_rule(
271 p_api_version => p_api_version,
272 p_init_msg_list => p_init_msg_list,
273 x_return_status => x_return_status,
274 x_msg_count => x_msg_count,
275 x_msg_data => x_msg_data,
276 p_rulv_rec => lp_labacc_rulv_rec,
277 x_rulv_rec => lx_labacc_rulv_rec);
278
279 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
280 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
281 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
282 RAISE OKC_API.G_EXCEPTION_ERROR;
283 END IF;
284
285 x_rgpv_rec.rul_labacc_id := lx_labacc_rulv_rec.id;
286
287 ELSE
288 -- update LABACC rule
289 lp_labacc_rulv_rec.id := p_rgpv_rec.rul_labacc_id;
290 lp_labacc_rulv_rec.rgp_id := p_rgpv_rec.rgp_id;
291 lp_labacc_rulv_rec.rule_information_category := 'LABACC';
292 lp_labacc_rulv_rec.dnz_chr_id := p_rgpv_rec.chr_id;
293 lp_labacc_rulv_rec.WARN_YN := 'N';
294 lp_labacc_rulv_rec.STD_TEMPLATE_YN := 'N';
295 IF ((p_rgpv_rec.rul_labacc_object1_id1 IS NOT NULL) AND (p_rgpv_rec.rul_labacc_object1_id2 IS NOT NULL)) THEN
296 lp_labacc_rulv_rec.object1_id1 := p_rgpv_rec.rul_labacc_object1_id1;
297 lp_labacc_rulv_rec.object1_id2 := p_rgpv_rec.rul_labacc_object1_id2;
298 lp_labacc_rulv_rec.jtot_object1_code := 'OKX_CUSTBKAC';
299 --Bug# 7702487
300 ELSIF ((p_rgpv_rec.rul_labacc_object1_id1 IS NULL) AND (p_rgpv_rec.rul_labacc_object1_id2 IS NULL)) THEN
301 lp_labacc_rulv_rec.object1_id1 := NULL;
302 lp_labacc_rulv_rec.object1_id2 := NULL;
303 lp_labacc_rulv_rec.jtot_object1_code := NULL;
304 END IF;
305
306 OKL_RULE_PUB.update_rule(
307 p_api_version => p_api_version,
308 p_init_msg_list => p_init_msg_list,
309 x_return_status => x_return_status,
310 x_msg_count => x_msg_count,
311 x_msg_data => x_msg_data,
312 p_rulv_rec => lp_labacc_rulv_rec,
313 x_rulv_rec => lx_labacc_rulv_rec);
314
315 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
316 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
317 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
318 RAISE OKC_API.G_EXCEPTION_ERROR;
319 END IF;
320
321 END IF;
322
323
324 IF (p_rgpv_rec.rul_lainvd_id IS NULL) THEN
325 -- Create LAINVD rule
326 lp_lainvd_rulv_rec.id := NULL;
327 lp_lainvd_rulv_rec.rgp_id := x_rgpv_rec.rgp_id;
328 lp_lainvd_rulv_rec.rule_information_category := 'LAINVD';
329 lp_lainvd_rulv_rec.dnz_chr_id := p_rgpv_rec.chr_id;
330
331 --sechawla 2-jun-09 6826580 : begin
332 open l_inv_frmt_csr(p_rgpv_rec.lainvd_rule_information1);
333 fetch l_inv_frmt_csr into l_inv_frmt_id;
334 close l_inv_frmt_csr;
335 -- lp_lainvd_rulv_rec.rule_information1 := p_rgpv_rec.lainvd_rule_information1;
336 lp_lainvd_rulv_rec.rule_information1 := to_char(l_inv_frmt_id);
337 --sechawla 2-jun-09 6826580 : end
338
339 lp_lainvd_rulv_rec.rule_information3 := p_rgpv_rec.lainvd_rule_information3;
340 lp_lainvd_rulv_rec.rule_information4 := p_rgpv_rec.lainvd_rule_information4;
341 lp_lainvd_rulv_rec.WARN_YN := 'N';
342 lp_lainvd_rulv_rec.STD_TEMPLATE_YN := 'N';
343 IF ((p_rgpv_rec.rul_lainvd_object1_id1 IS NOT NULL) AND (p_rgpv_rec.rul_lainvd_object1_id2 IS NOT NULL)) THEN
344 lp_lainvd_rulv_rec.object1_id1 := p_rgpv_rec.rul_lainvd_object1_id1;
345 lp_lainvd_rulv_rec.object1_id2 := p_rgpv_rec.rul_lainvd_object1_id2;
346 lp_lainvd_rulv_rec.jtot_object1_code := 'OKL_CASHAPPL';
347 END IF;
348
349 OKL_RULE_PUB.create_rule(
350 p_api_version => p_api_version,
351 p_init_msg_list => p_init_msg_list,
352 x_return_status => x_return_status,
353 x_msg_count => x_msg_count,
354 x_msg_data => x_msg_data,
355 p_rulv_rec => lp_lainvd_rulv_rec,
356 x_rulv_rec => lx_lainvd_rulv_rec);
357
358 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
359 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
360 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
361 RAISE OKC_API.G_EXCEPTION_ERROR;
362 END IF;
363
364 x_rgpv_rec.rul_lainvd_id := lx_lainvd_rulv_rec.id;
365
366 ELSE
367 -- Update LAINVD rule
368 lp_lainvd_rulv_rec.id := p_rgpv_rec.rul_lainvd_id;
369 lp_lainvd_rulv_rec.rgp_id := p_rgpv_rec.rgp_id;
370 lp_lainvd_rulv_rec.rule_information_category := 'LAINVD';
371 lp_lainvd_rulv_rec.dnz_chr_id := p_rgpv_rec.chr_id;
372
373 --sechawla 2-jun-09 6826580 : begin
374 open l_inv_frmt_csr(p_rgpv_rec.lainvd_rule_information1);
375 fetch l_inv_frmt_csr into l_inv_frmt_id;
376 close l_inv_frmt_csr;
377 -- lp_lainvd_rulv_rec.rule_information1 := p_rgpv_rec.lainvd_rule_information1;
378 lp_lainvd_rulv_rec.rule_information1 := to_char(l_inv_frmt_id);
379 --sechawla 2-jun-09 6826580 : end
380
381 lp_lainvd_rulv_rec.rule_information3 := p_rgpv_rec.lainvd_rule_information3;
382 lp_lainvd_rulv_rec.rule_information4 := p_rgpv_rec.lainvd_rule_information4;
383 lp_lainvd_rulv_rec.WARN_YN := 'N';
384 lp_lainvd_rulv_rec.STD_TEMPLATE_YN := 'N';
385 IF ((p_rgpv_rec.rul_lainvd_object1_id1 IS NOT NULL) AND (p_rgpv_rec.rul_lainvd_object1_id2 IS NOT NULL)) THEN
386 lp_lainvd_rulv_rec.object1_id1 := p_rgpv_rec.rul_lainvd_object1_id1;
387 lp_lainvd_rulv_rec.object1_id2 := p_rgpv_rec.rul_lainvd_object1_id2;
388 lp_lainvd_rulv_rec.jtot_object1_code := 'OKL_CASHAPPL';
389 --Bug# 7702487
390 ELSIF ((p_rgpv_rec.rul_lainvd_object1_id1 IS NULL) AND (p_rgpv_rec.rul_lainvd_object1_id2 IS NULL)) THEN
391 lp_lainvd_rulv_rec.object1_id1 := NULL;
392 lp_lainvd_rulv_rec.object1_id2 := NULL;
393 lp_lainvd_rulv_rec.jtot_object1_code := NULL;
394 END IF;
395
396 OKL_RULE_PUB.update_rule(
397 p_api_version => p_api_version,
398 p_init_msg_list => p_init_msg_list,
399 x_return_status => x_return_status,
400 x_msg_count => x_msg_count,
401 x_msg_data => x_msg_data,
402 p_rulv_rec => lp_lainvd_rulv_rec,
403 x_rulv_rec => lx_lainvd_rulv_rec);
404
405 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
406 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
407 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
408 RAISE OKC_API.G_EXCEPTION_ERROR;
409 END IF;
410
411 END IF;
412
413 IF (p_rgpv_rec.rul_lainpr_id IS NULL) THEN
414 -- Create LAINPR rule
415 lp_lainpr_rulv_rec.id := NULL;
416 lp_lainpr_rulv_rec.rgp_id := x_rgpv_rec.rgp_id;
417 lp_lainpr_rulv_rec.rule_information_category := 'LAINPR';
418 lp_lainpr_rulv_rec.dnz_chr_id := p_rgpv_rec.chr_id;
419 lp_lainpr_rulv_rec.rule_information1 := p_rgpv_rec.lainpr_rule_information1;
420 lp_lainpr_rulv_rec.rule_information2 := p_rgpv_rec.lainpr_rule_information2;
421 lp_lainpr_rulv_rec.WARN_YN := 'N';
422 lp_lainpr_rulv_rec.STD_TEMPLATE_YN := 'N';
423
424 OKL_RULE_PUB.create_rule(
425 p_api_version => p_api_version,
426 p_init_msg_list => p_init_msg_list,
427 x_return_status => x_return_status,
428 x_msg_count => x_msg_count,
429 x_msg_data => x_msg_data,
430 p_rulv_rec => lp_lainpr_rulv_rec,
431 x_rulv_rec => lx_lainpr_rulv_rec);
432
433 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
434 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
435 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
436 RAISE OKC_API.G_EXCEPTION_ERROR;
437 END IF;
438
439 x_rgpv_rec.rul_lainpr_id := lx_lainpr_rulv_rec.id;
440
441 ELSE
442 -- Update LAINPR rule
443 lp_lainpr_rulv_rec.id := p_rgpv_rec.rul_lainpr_id;
444 lp_lainpr_rulv_rec.rgp_id := p_rgpv_rec.rgp_id;
445 lp_lainpr_rulv_rec.rule_information_category := 'LAINPR';
446 lp_lainpr_rulv_rec.dnz_chr_id := p_rgpv_rec.chr_id;
447 lp_lainpr_rulv_rec.rule_information1 := p_rgpv_rec.lainpr_rule_information1;
448 lp_lainpr_rulv_rec.rule_information2 := p_rgpv_rec.lainpr_rule_information2;
449 lp_lainpr_rulv_rec.WARN_YN := 'N';
450 lp_lainpr_rulv_rec.STD_TEMPLATE_YN := 'N';
451
452 OKL_RULE_PUB.update_rule(
453 p_api_version => p_api_version,
454 p_init_msg_list => p_init_msg_list,
455 x_return_status => x_return_status,
456 x_msg_count => x_msg_count,
457 x_msg_data => x_msg_data,
458 p_rulv_rec => lp_lainpr_rulv_rec,
459 x_rulv_rec => lx_lainpr_rulv_rec);
460
461 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
462 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
463 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
464 RAISE OKC_API.G_EXCEPTION_ERROR;
465 END IF;
466
467 END IF;
468
469 OKC_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
470
471 EXCEPTION
472 WHEN OKC_API.G_EXCEPTION_ERROR THEN
473 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
474 p_api_name => l_api_name,
475 p_pkg_name => g_pkg_name,
476 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
477 x_msg_count => x_msg_count,
478 x_msg_data => x_msg_data,
479 p_api_type => g_api_type);
480
481 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
482 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
483 p_api_name => l_api_name,
484 p_pkg_name => g_pkg_name,
485 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
486 x_msg_count => x_msg_count,
487 x_msg_data => x_msg_data,
488 p_api_type => g_api_type);
489
490 WHEN OTHERS THEN
491 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
492 p_api_name => l_api_name,
493 p_pkg_name => g_pkg_name,
494 p_exc_name => 'OTHERS',
495 x_msg_count => x_msg_count,
496 x_msg_data => x_msg_data,
497 p_api_type => g_api_type);
498 END process_billing_setup;
499
500 PROCEDURE process_rvi(
501 p_api_version IN NUMBER,
502 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
503 x_return_status OUT NOCOPY VARCHAR2,
504 x_msg_count OUT NOCOPY NUMBER,
505 x_msg_data OUT NOCOPY VARCHAR2,
506 p_rgpv_rec IN rvi_rec_type,
507 x_rgpv_rec OUT NOCOPY rvi_rec_type) IS
508
509 l_api_name VARCHAR2(30) := 'process_rvi';
510 l_api_version CONSTANT NUMBER := 1.0;
511
512 lp_larvin_rgpv_rec OKL_OKC_MIGRATION_PVT.rgpv_rec_type;
513 lx_larvin_rgpv_rec OKL_OKC_MIGRATION_PVT.rgpv_rec_type;
514
515 lp_larvau_rulv_rec Okl_Rule_Pub.rulv_rec_type;
516 lx_larvau_rulv_rec Okl_Rule_Pub.rulv_rec_type;
517
518 lp_larvam_rulv_rec Okl_Rule_Pub.rulv_rec_type;
519 lx_larvam_rulv_rec Okl_Rule_Pub.rulv_rec_type;
520
521 lp_fee_types_rec OKL_MAINTAIN_FEE_PVT.fee_types_rec_type;
522 lx_fee_types_rec OKL_MAINTAIN_FEE_PVT.fee_types_rec_type;
523
524 l_msg_data VARCHAR2(4000);
525 l_msg_index_out number;
526
527 BEGIN
528 x_return_status := OKL_API.START_ACTIVITY(
529 p_api_name => l_api_name,
530 p_pkg_name => g_pkg_name,
531 p_init_msg_list => p_init_msg_list,
532 l_api_version => l_api_version,
533 p_api_version => p_api_version,
534 p_api_type => g_api_type,
535 x_return_status => x_return_status);
536
537 -- check if activity started successfully
538 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
539 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
540 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
541 RAISE OKL_API.G_EXCEPTION_ERROR;
542 END IF;
543
544 IF (p_rgpv_rec.rgp_id IS NULL) THEN
545
546 -- Create LARVIN rule group
547 lp_larvin_rgpv_rec.id := NULL;
548 lp_larvin_rgpv_rec.rgd_code := 'LARVIN';
549 lp_larvin_rgpv_rec.dnz_chr_id := p_rgpv_rec.chr_id;
550 lp_larvin_rgpv_rec.chr_id := p_rgpv_rec.chr_id;
551 lp_larvin_rgpv_rec.rgp_type := 'KRG';
552
553 OKL_RULE_PUB.create_rule_group(
554 p_api_version => p_api_version,
555 p_init_msg_list => p_init_msg_list,
556 x_return_status => x_return_status,
557 x_msg_count => x_msg_count,
558 x_msg_data => x_msg_data,
559 p_rgpv_rec => lp_larvin_rgpv_rec,
560 x_rgpv_rec => lx_larvin_rgpv_rec);
561
562 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
563 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
564 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
565 RAISE OKC_API.G_EXCEPTION_ERROR;
566 END IF;
567
568 x_rgpv_rec.rgp_id := lx_larvin_rgpv_rec.id;
569
570 ElSE
571
572 -- Update LARVIN rule group
573 lp_larvin_rgpv_rec.id := p_rgpv_rec.rgp_id;
574 lp_larvin_rgpv_rec.rgd_code := 'LARVIN';
575 lp_larvin_rgpv_rec.dnz_chr_id := p_rgpv_rec.chr_id;
576 lp_larvin_rgpv_rec.chr_id := p_rgpv_rec.chr_id;
577 lp_larvin_rgpv_rec.rgp_type := 'KRG';
578
579 OKL_RULE_PUB.update_rule_group(
580 p_api_version => p_api_version,
581 p_init_msg_list => p_init_msg_list,
582 x_return_status => x_return_status,
583 x_msg_count => x_msg_count,
584 x_msg_data => x_msg_data,
585 p_rgpv_rec => lp_larvin_rgpv_rec,
586 x_rgpv_rec => lx_larvin_rgpv_rec);
587
588 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
589 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
590 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
591 RAISE OKC_API.G_EXCEPTION_ERROR;
592 END IF;
593
594 END IF;
595
596 IF (p_rgpv_rec.rul_larvau_id IS NULL) THEN
597 -- Create LARVAU rule
598 lp_larvau_rulv_rec.id := NULL;
599 lp_larvau_rulv_rec.rgp_id := lx_larvin_rgpv_rec.id;
600 lp_larvau_rulv_rec.dnz_chr_id := p_rgpv_rec.chr_id;
601 lp_larvau_rulv_rec.rule_information_category := 'LARVAU';
602 lp_larvau_rulv_rec.rule_information1 := p_rgpv_rec.larvau_rule_information1;
603 lp_larvau_rulv_rec.WARN_YN := 'N';
604 lp_larvau_rulv_rec.STD_TEMPLATE_YN := 'N';
605
606 OKL_RULE_PUB.create_rule(
607 p_api_version => p_api_version,
608 p_init_msg_list => p_init_msg_list,
609 x_return_status => x_return_status,
610 x_msg_count => x_msg_count,
611 x_msg_data => x_msg_data,
612 p_rulv_rec => lp_larvau_rulv_rec,
613 x_rulv_rec => lx_larvau_rulv_rec);
614
615 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
616 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
617 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
618 RAISE OKC_API.G_EXCEPTION_ERROR;
619 END IF;
620
621 x_rgpv_rec.rul_larvau_id := lx_larvau_rulv_rec.id;
622
623 ELSE
624
625 -- Update LARVAU rule
626 lp_larvau_rulv_rec.id := p_rgpv_rec.rul_larvau_id;
627 lp_larvau_rulv_rec.rgp_id := p_rgpv_rec.rgp_id;
628 lp_larvau_rulv_rec.dnz_chr_id := p_rgpv_rec.chr_id;
629 lp_larvau_rulv_rec.rule_information_category := 'LARVAU';
630 lp_larvau_rulv_rec.rule_information1 := p_rgpv_rec.larvau_rule_information1;
631 lp_larvau_rulv_rec.WARN_YN := 'N';
632 lp_larvau_rulv_rec.STD_TEMPLATE_YN := 'N';
633
634 OKL_RULE_PUB.update_rule(
635 p_api_version => p_api_version,
636 p_init_msg_list => p_init_msg_list,
637 x_return_status => x_return_status,
638 x_msg_count => x_msg_count,
639 x_msg_data => x_msg_data,
640 p_rulv_rec => lp_larvau_rulv_rec,
641 x_rulv_rec => lx_larvau_rulv_rec);
642
643 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
644 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
645 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
646 RAISE OKC_API.G_EXCEPTION_ERROR;
647 END IF;
648
649 x_rgpv_rec.rul_larvau_id := lx_larvau_rulv_rec.id;
650 END IF;
651
652 IF (p_rgpv_rec.rul_larvam_id IS NULL) THEN
653 -- Create LARVAM rule
654 lp_larvam_rulv_rec.id := NULL;
655 lp_larvam_rulv_rec.rgp_id := lx_larvin_rgpv_rec.id;
656 lp_larvam_rulv_rec.dnz_chr_id := p_rgpv_rec.chr_id;
657 lp_larvam_rulv_rec.rule_information_category := 'LARVAM';
658 lp_larvam_rulv_rec.rule_information4 := p_rgpv_rec.larvam_rule_information4;
659 lp_larvam_rulv_rec.WARN_YN := 'N';
660 lp_larvam_rulv_rec.STD_TEMPLATE_YN := 'N';
661
662 OKL_RULE_PUB.create_rule(
663 p_api_version => p_api_version,
664 p_init_msg_list => p_init_msg_list,
665 x_return_status => x_return_status,
666 x_msg_count => x_msg_count,
667 x_msg_data => x_msg_data,
668 p_rulv_rec => lp_larvam_rulv_rec,
669 x_rulv_rec => lx_larvam_rulv_rec);
670
671 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
672 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
673 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
674 RAISE OKC_API.G_EXCEPTION_ERROR;
675 END IF;
676
677 x_rgpv_rec.rul_larvam_id := lx_larvam_rulv_rec.id;
678
679 ELSE
680
681 -- Update LARVAM rule
682 lp_larvam_rulv_rec.id := p_rgpv_rec.rul_larvam_id;
683 lp_larvam_rulv_rec.rgp_id := p_rgpv_rec.rgp_id;
684 lp_larvam_rulv_rec.dnz_chr_id := p_rgpv_rec.chr_id;
685 lp_larvam_rulv_rec.rule_information_category := 'LARVAM';
686 lp_larvam_rulv_rec.rule_information4 := p_rgpv_rec.larvam_rule_information4;
687 lp_larvam_rulv_rec.WARN_YN := 'N';
688 lp_larvam_rulv_rec.STD_TEMPLATE_YN := 'N';
689
690 OKL_RULE_PUB.update_rule(
691 p_api_version => p_api_version,
692 p_init_msg_list => p_init_msg_list,
693 x_return_status => x_return_status,
694 x_msg_count => x_msg_count,
695 x_msg_data => x_msg_data,
696 p_rulv_rec => lp_larvam_rulv_rec,
697 x_rulv_rec => lx_larvam_rulv_rec);
698
699 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
700 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
701 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
702 RAISE OKC_API.G_EXCEPTION_ERROR;
703 END IF;
704
705 x_rgpv_rec.rul_larvam_id := lx_larvam_rulv_rec.id;
706 END IF;
707
708 lp_fee_types_rec.dnz_chr_id := p_rgpv_rec.chr_id;
709 lp_fee_types_rec.line_id := p_rgpv_rec.line_id;
710 lp_fee_types_rec.item_id1 := p_rgpv_rec.item_id1;
711 lp_fee_types_rec.item_name := p_rgpv_rec.item_name;
712 lp_fee_types_rec.fee_type := 'ABSORBED';
713 lp_fee_types_rec.fee_purpose_code := 'RVI';
714
715 OKL_MAINTAIN_FEE_PVT.process_rvi_stream(
716 p_api_version => p_api_version,
717 p_init_msg_list => p_init_msg_list,
718 x_return_status => x_return_status,
719 x_msg_count => x_msg_count,
720 x_msg_data => x_msg_data,
721 p_check_box_value => p_rgpv_rec.larvau_rule_information1,
722 p_fee_types_rec => lp_fee_types_rec,
723 x_fee_types_rec => lx_fee_types_rec);
724
725 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
726 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
727 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
728 RAISE OKC_API.G_EXCEPTION_ERROR;
729 END IF;
730
731 OKC_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
732
733 EXCEPTION
734 WHEN OKC_API.G_EXCEPTION_ERROR THEN
735 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
736 p_api_name => l_api_name,
737 p_pkg_name => g_pkg_name,
738 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
739 x_msg_count => x_msg_count,
740 x_msg_data => x_msg_data,
741 p_api_type => g_api_type);
742
743 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
744 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
745 p_api_name => l_api_name,
746 p_pkg_name => g_pkg_name,
747 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
748 x_msg_count => x_msg_count,
749 x_msg_data => x_msg_data,
750 p_api_type => g_api_type);
751
752 WHEN OTHERS THEN
753 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
754 p_api_name => l_api_name,
755 p_pkg_name => g_pkg_name,
756 p_exc_name => 'OTHERS',
757 x_msg_count => x_msg_count,
758 x_msg_data => x_msg_data,
759 p_api_type => g_api_type);
760
761 END process_rvi;
762
763 PROCEDURE load_billing_setup(
764 p_api_version IN NUMBER,
765 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
766 x_return_status OUT NOCOPY VARCHAR2,
767 x_msg_count OUT NOCOPY NUMBER,
768 x_msg_data OUT NOCOPY VARCHAR2,
769 p_chr_id IN NUMBER,
770 x_billing_setup_rec OUT NOCOPY billing_setup_rec_type) IS
771
772 l_return_status VARCHAR2(1) default OKL_API.G_RET_STS_SUCCESS;
773 l_api_name CONSTANT VARCHAR2(30) := 'load_billing_setup';
774 l_api_version CONSTANT NUMBER := 1.0;
775
776 CURSOR c_bill_to(p_chr_id NUMBER) IS
777 SELECT chr.bill_to_site_use_id,
778 csu.location bill_to_site_name
779 FROM okc_k_headers_b chr,
780 hz_cust_site_uses_all csu
781 WHERE chr.id = p_chr_id
782 AND csu.site_use_id = chr.bill_to_site_use_id;
783
784 CURSOR c_rule(p_chr_id NUMBER, p_rgd_code VARCHAR2, p_rule_info_cat VARCHAR2) IS
785 SELECT rul.rgp_id,rgp.rgd_code,rul.ID,rul.object1_id1,rul.object1_id2,rul.rule_information1,rul.rule_information2,
786 rul.rule_information3, rul.rule_information4
787 FROM okc_rules_b rul,
788 okc_rule_groups_b rgp
789 WHERE rgp.chr_id = p_chr_id
790 AND rgp.dnz_chr_id = p_chr_id
791 AND rgp.rgd_code = p_rgd_code
792 AND rgp.id = rul.rgp_id
793 AND rgp.dnz_chr_id = rul.dnz_chr_id
794 AND rul.rule_information_category = p_rule_info_cat;
795
796 l_rule c_rule%ROWTYPE;
797 l_empty_rule c_rule%ROWTYPE; --Bug#9852347
798 l_rgp_id okc_rule_groups_b.ID%TYPE; --Bug#9852347
799
800 CURSOR c_payment_method(p_object1_id1 NUMBER) IS
801 SELECT name
802 FROM okx_receipt_methods_v
803 WHERE id1 = p_object1_id1;
804
805 CURSOR c_bank_info(p_object1_id1 NUMBER) IS
806 SELECT description name,bank bank_name
807 FROM okx_rcpt_method_accounts_v
808 WHERE id1 = p_object1_id1;
809
810 CURSOR c_cash_app(p_object1_id1 NUMBER) IS
811 SELECT name
812 FROM okl_bpd_active_csh_rls_v
813 WHERE id1 = p_object1_id1;
814
815 --sechawla 26-may-09 6826580 :begin
816 CURSOR c_invoice_formats(p_inv_fmt_id IN NUMBER) IS
817 SELECT name
818 FROM okl_invoice_formats_v
819 WHERE ID = p_inv_fmt_id;
820 l_format_name VARCHAR2(150);
821 --sechawla 26-may-09 6826580 :end
822
823 BEGIN
824 x_return_status := OKL_API.G_RET_STS_SUCCESS;
825 -- Call start_activity to create savepoint, check compatibility
826 -- and initialize message list
827 x_return_status := OKL_API.START_ACTIVITY (
828 l_api_name
829 ,p_init_msg_list
830 ,'_PVT'
831 ,x_return_status);
832 -- Check if activity started successfully
833 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
834 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
835 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
836 RAISE OKL_API.G_EXCEPTION_ERROR;
837 END IF;
838
839 x_billing_setup_rec.chr_id := p_chr_id;
840 OPEN c_bill_to(p_chr_id);
841 FETCH c_bill_to INTO x_billing_setup_rec.bill_to_site_use_id, x_billing_setup_rec.bill_to_site_name;
842 CLOSE c_bill_to;
843
844 l_rule := l_empty_rule; -- Bug#9852347
845 OPEN c_rule(p_chr_id , 'LABILL', 'LAPMTH');
846 FETCH c_rule INTO l_rule;
847 IF c_rule%FOUND THEN -- Bug#9852347
848 l_rgp_id := l_rule.rgp_id;
849 -- x_billing_setup_rec.rgp_id := l_rule.rgp_id; -- setting the rule group id at the end of API as potentially T and Cs could be imported without payment method and then this code will not work
850 x_billing_setup_rec.rgp_labill_lapmth_id := l_rule.rgp_id;
851 x_billing_setup_rec.rgp_labill_labacc_id := l_rule.rgp_id;
852 x_billing_setup_rec.rgp_labill_lainvd_id := l_rule.rgp_id;
853 x_billing_setup_rec.rgp_labill_lainpr_id := l_rule.rgp_id;
854 x_billing_setup_rec.rul_lapmth_id := l_rule.id;
855 x_billing_setup_rec.rul_lapmth_object1_id1 := l_rule.object1_id1;
856 x_billing_setup_rec.rul_lapmth_object1_id2 := l_rule.object1_id2;
857 END IF; -- Bug#9852347
858 CLOSE c_rule;
859
860 IF (x_billing_setup_rec.rul_lapmth_object1_id1 IS NOT NULL) THEN
861 OPEN c_payment_method(x_billing_setup_rec.rul_lapmth_object1_id1);
862 FETCH c_payment_method INTO x_billing_setup_rec.rul_lapmth_name;
863 CLOSE c_payment_method;
864 END IF;
865
866 l_rule := l_empty_rule; -- Bug#9852347
867 OPEN c_rule(p_chr_id , 'LABILL', 'LABACC');
868 FETCH c_rule INTO l_rule;
869 IF c_rule%FOUND THEN -- Bug#9852347
870 l_rgp_id := l_rule.rgp_id;
871 x_billing_setup_rec.rul_labacc_id := l_rule.id;
872 x_billing_setup_rec.rul_labacc_object1_id1 := l_rule.object1_id1;
873 x_billing_setup_rec.rul_labacc_object1_id2 := l_rule.object1_id2;
874 END IF;
875 CLOSE c_rule;
876
877 IF (x_billing_setup_rec.rul_labacc_object1_id1 IS NOT NULL) THEN
878 OPEN c_bank_info(x_billing_setup_rec.rul_labacc_object1_id1);
879 FETCH c_bank_info INTO x_billing_setup_rec.rul_labacc_name,x_billing_setup_rec.rul_labacc_bank_name;
880 CLOSE c_bank_info;
881 END IF;
882
883 l_rule := l_empty_rule; -- Bug#9852347
884 OPEN c_rule(p_chr_id , 'LABILL', 'LAINVD');
885 FETCH c_rule INTO l_rule;
886 IF c_rule%FOUND THEN -- Bug#9852347
887 l_rgp_id := l_rule.rgp_id;
888 x_billing_setup_rec.rul_lainvd_id := l_rule.id;
889
890 --x_billing_setup_rec.lainvd_rule_information1 := l_rule.rule_information1;
891
892 --sechawla 26-may-09 6826580 : get the format name : begin
893 OPEN c_invoice_formats(to_number(l_rule.rule_information1));
894 FETCH c_invoice_formats INTO l_format_name;
895 CLOSE c_invoice_formats;
896
897 x_billing_setup_rec.lainvd_rule_information1 := l_format_name;
898 --sechawla 26-may-09 6826580 : get the format name : end
899
900 x_billing_setup_rec.lainvd_invoice_format_meaning := l_format_name; --l_rule.rule_information1; --sechawla 26-may-09 6826580
901 x_billing_setup_rec.lainvd_rule_information3 := l_rule.rule_information3;
902 x_billing_setup_rec.lainvd_rule_information4 := l_rule.rule_information4;
903 x_billing_setup_rec.rul_lainvd_object1_id1 := l_rule.object1_id1;
904 x_billing_setup_rec.rul_lainvd_object1_id2 := l_rule.object1_id2;
905 END IF;
906 CLOSE c_rule;
907
908 IF (x_billing_setup_rec.rul_lainvd_object1_id1 IS NOT NULL) THEN
909 OPEN c_cash_app(x_billing_setup_rec.rul_lainvd_object1_id1);
910 FETCH c_cash_app INTO x_billing_setup_rec.rul_lainvd_name;
911 CLOSE c_cash_app;
912 END IF;
913
914 l_rule := l_empty_rule; -- Bug#9852347
915 OPEN c_rule(p_chr_id , 'LABILL', 'LAINPR');
916 FETCH c_rule INTO l_rule;
917 IF c_rule%FOUND THEN -- Bug#9852347
918 l_rgp_id := l_rule.rgp_id;
919 x_billing_setup_rec.rul_lainpr_id := l_rule.id;
920 x_billing_setup_rec.lainpr_rule_information1 := l_rule.rule_information1;
921 x_billing_setup_rec.lainpr_rule_information2 := l_rule.rule_information2;
922 END IF;
923 CLOSE c_rule;
924
925 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
926 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
927 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
928 RAISE OKL_API.G_EXCEPTION_ERROR;
929 END IF;
930
931 -- assign the rule group id
932 x_billing_setup_rec.rgp_id := l_rgp_id; -- Bug#9852347
933
934 OKL_API.END_ACTIVITY (x_msg_count,x_msg_data );
935
936 EXCEPTION
937 WHEN OKL_API.G_EXCEPTION_ERROR THEN
938
939 --sechawla 26-may-09 6826580
940 IF c_invoice_formats%ISOPEN THEN
941 CLOSE c_invoice_formats;
942 END IF;
943
944 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
945 l_api_name,
946 G_PKG_NAME,
947 'OKL_API.G_RET_STS_ERROR',
948 x_msg_count,
949 x_msg_data,
950 '_PVT');
951 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
952
953 --sechawla 26-may-09 6826580
954 IF c_invoice_formats%ISOPEN THEN
955 CLOSE c_invoice_formats;
956 END IF;
957 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
958 l_api_name,
959 G_PKG_NAME,
960 'OKL_API.G_RET_STS_UNEXP_ERROR',
961 x_msg_count,
962 x_msg_data,
963 '_PVT');
964 WHEN OTHERS THEN
965
966 --sechawla 26-may-09 6826580
967 IF c_invoice_formats%ISOPEN THEN
968 CLOSE c_invoice_formats;
969 END IF;
970 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
971 l_api_name,
972 G_PKG_NAME,
973 'OTHERS',
974 x_msg_count,
975 x_msg_data,
976 '_PVT');
977 END load_billing_setup;
978
979 PROCEDURE load_rvi(
980 p_api_version IN NUMBER,
981 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
982 x_return_status OUT NOCOPY VARCHAR2,
983 x_msg_count OUT NOCOPY NUMBER,
984 x_msg_data OUT NOCOPY VARCHAR2,
985 p_chr_id IN NUMBER,
986 x_rvi_rec OUT NOCOPY rvi_rec_type) IS
987
988 l_return_status VARCHAR2(1) default OKL_API.G_RET_STS_SUCCESS;
989 l_api_name CONSTANT VARCHAR2(30) := 'load_rvi';
990 l_api_version CONSTANT NUMBER := 1.0;
991
992 CURSOR c_rule(p_chr_id NUMBER) IS
993 SELECT rgp.dnz_chr_id,
994 rgp.id rgp_id,
995 rgp.rgd_code,
996 rgp.id rgp_larvin_larvau_id,
997 rgp.id rgp_larvin_larvam_id,
998 rul_larvau.id rul_larvau_id,
999 rul_larvau.rule_information_category larvau_rule_info_cat,
1000 rul_larvam.id rul_larvam_id,
1001 rul_larvam.rule_information_category larvam_rule_info_cat,
1002 rul_larvau.rule_information1 larvau_rule_information1,
1003 rul_larvam.rule_information4 larvam_rule_information4
1004 FROM okc_rules_b rul_larvau,
1005 okc_rules_b rul_larvam,
1006 okc_rule_groups_b rgp
1007 WHERE rgp.chr_id = p_chr_id
1008 AND rgp.dnz_chr_id = p_chr_id
1009 AND rgp.rgd_code = 'LARVIN'
1010 AND rul_larvau.rgp_id = rgp.id
1011 AND rul_larvau.dnz_chr_id = rgp.dnz_chr_id
1012 AND rul_larvau.rule_information_category = 'LARVAU'
1013 AND rul_larvam.rgp_id = rgp.id
1014 AND rul_larvam.rule_information_category = 'LARVAM'
1015 AND rul_larvam.dnz_chr_id = rgp.dnz_chr_id;
1016
1017 CURSOR c_stream_name(p_chr_id NUMBER) IS
1018 SELECT cle.id,ossv.id1,name,fee_type
1019 from okc_k_lines_b cle,
1020 okl_k_lines kle,
1021 okc_k_items itm,
1022 okl_strmtyp_source_v ossv,
1023 okc_line_styles_b olsb
1024 where cle.id = kle.id
1025 and cle.id = itm.cle_id
1026 and itm.object1_id1 = ossv.id1
1027 and kle.fee_type = 'ABSORBED'
1028 and kle.fee_purpose_code = 'RVI'
1029 and cle.lse_id = olsb.ID
1030 and olsb.LTY_CODE = 'FEE'
1031 and cle.dnz_chr_id = p_chr_id;
1032
1033 BEGIN
1034 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1035 -- Call start_activity to create savepoint, check compatibility
1036 -- and initialize message list
1037 x_return_status := OKL_API.START_ACTIVITY (
1038 l_api_name
1039 ,p_init_msg_list
1040 ,'_PVT'
1041 ,x_return_status);
1042 -- Check if activity started successfully
1043 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1044 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1045 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1046 RAISE OKL_API.G_EXCEPTION_ERROR;
1047 END IF;
1048
1049 OPEN c_rule(p_chr_id);
1050 FETCH c_rule INTO x_rvi_rec.chr_id,x_rvi_rec.rgp_id,x_rvi_rec.rgd_code,x_rvi_rec.rgp_larvin_larvau_id,x_rvi_rec.rgp_larvin_larvam_id,
1051 x_rvi_rec.rul_larvau_id,x_rvi_rec.larvau_rule_info_cat,x_rvi_rec.rul_larvam_id,x_rvi_rec.larvam_rule_info_cat,
1052 x_rvi_rec.larvau_rule_information1, x_rvi_rec.larvam_rule_information4;
1053 CLOSE c_rule;
1054
1055 OPEN c_stream_name(p_chr_id);
1056 FETCH c_stream_name INTO x_rvi_rec.line_id,x_rvi_rec.item_id1,x_rvi_rec.item_name,x_rvi_rec.fee_type;
1057 CLOSE c_stream_name;
1058
1059 OKL_API.END_ACTIVITY (x_msg_count,x_msg_data );
1060
1061 EXCEPTION
1062 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1063 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1064 l_api_name,
1065 G_PKG_NAME,
1066 'OKL_API.G_RET_STS_ERROR',
1067 x_msg_count,
1068 x_msg_data,
1069 '_PVT');
1070 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1071 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
1072 l_api_name,
1073 G_PKG_NAME,
1074 'OKL_API.G_RET_STS_UNEXP_ERROR',
1075 x_msg_count,
1076 x_msg_data,
1077 '_PVT');
1078 WHEN OTHERS THEN
1079 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
1080 l_api_name,
1081 G_PKG_NAME,
1082 'OTHERS',
1083 x_msg_count,
1084 x_msg_data,
1085 '_PVT');
1086 END load_rvi;
1087
1088 PROCEDURE delete_tnc_group(
1089 p_api_version IN NUMBER,
1090 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1091 x_return_status OUT NOCOPY VARCHAR2,
1092 x_msg_count OUT NOCOPY NUMBER,
1093 x_msg_data OUT NOCOPY VARCHAR2,
1094 p_tnc_id IN NUMBER
1095 ) is
1096
1097
1098 l_return_status VARCHAR2(1) default OKL_API.G_RET_STS_SUCCESS;
1099 l_api_name CONSTANT VARCHAR2(30) := 'delete_tnc_group';
1100 l_api_version CONSTANT NUMBER := 1.0;
1101
1102
1103 Cursor l_rgp_csr (p_rgp_id in number) IS
1104 SELECT 'Y'
1105 from okc_rule_groups_b
1106 where id = p_rgp_id;
1107
1108 l_rgp_found VARCHAR2(1) := 'N';
1109
1110 Cursor l_ppyh_csr (p_ppyh_id in number) is
1111 SELECT 'Y'
1112 from OKL_PARTY_PAYMENT_HDR
1113 where id = p_ppyh_id;
1114
1115 l_ppyh_found VARCHAR2(1) := 'N';
1116
1117 Cursor l_ppyl_csr (p_ppyh_id in number) is
1118 SELECT id, cpl_id
1119 from OKL_PARTY_PAYMENT_DTLS
1120 where payment_hdr_id = p_ppyh_id;
1121
1122 l_ppyl_id OKL_PARTY_PAYMENT_DTLS.ID%TYPE := NULL;
1123 l_cpl_id OKL_PARTY_PAYMENT_DTLS.CPL_ID%TYPE := NULL;
1124
1125 l_rgpv_rec okc_rule_pub.rgpv_rec_type;
1126 l_ppydv_rec okl_party_payments_pvt.ppydv_rec_type;
1127 l_pphv_rec okl_party_payments_pvt.pphv_rec_type;
1128
1129
1130
1131 Begin
1132 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1133 -- Call start_activity to create savepoint, check compatibility
1134 -- and initialize message list
1135 x_return_status := OKL_API.START_ACTIVITY (
1136 l_api_name
1137 ,p_init_msg_list
1138 ,'_PVT'
1139 ,x_return_status);
1140 -- Check if activity started successfully
1141 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1142 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1143 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1144 RAISE OKL_API.G_EXCEPTION_ERROR;
1145 END IF;
1146
1147 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1148 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1149 'OKL_DEAL_CREAT_PVT.delete_tnc_group.',
1150 'p_tnc_id :'||p_tnc_id);
1151 END IF;
1152
1153 --1.Check if tnc exists in rule groups
1154 open l_rgp_csr (p_rgp_id => p_tnc_id);
1155 Fetch l_rgp_csr into l_rgp_found;
1156 If l_rgp_csr%NOTFOUND then
1157 NULL;
1158 End If;
1159 Close l_rgp_csr;
1160
1161 If l_rgp_found = 'Y' then
1162 --delete the rule group
1163 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1164 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'OKL_DEAL_CREAT_PVT.delete_tnc_group.', 'Before call to delete_rule_group = '||x_return_status);
1165 END IF;
1166 l_rgpv_rec.id := p_tnc_id;
1167 okc_rule_pub.delete_rule_group(
1168 p_api_version => p_api_version
1169 ,p_init_msg_list => p_init_msg_list
1170 ,x_return_status => x_return_status
1171 ,x_msg_count => x_msg_count
1172 ,x_msg_data => x_msg_data
1173 ,p_rgpv_rec => l_rgpv_rec);
1174 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1175 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'OKL_DEAL_CREAT_PVT.delete_tnc_group.', 'After call to delete_rule_group = '||x_return_status);
1176 END IF;
1177 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1178 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1179 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1180 RAISE OKL_API.G_EXCEPTION_ERROR;
1181 END IF;
1182 ElsIf l_rgp_found = 'N' then
1183 --2. Check if record exists in OKL_PARTY_PAYMENT_HDR for
1184 -- evergreen passthrough
1185 Open l_ppyh_csr (p_ppyh_id => p_tnc_id);
1186 Fetch l_ppyh_csr into l_ppyh_found;
1187 If l_ppyh_csr%NOTFOUND then
1188 --raise error as this means that the id
1189 --passed is invalid
1190 okl_api.set_message(
1191 p_app_name => G_APP_NAME,
1192 p_msg_name => 'OKL_CONTRACTS_INVALID_VALUE',
1193 p_token1 => 'COL_NAME',
1194 p_token1_value => 'Terms and Condition group'
1195 );
1196 x_return_status := OKL_API.G_RET_STS_ERROR;
1197 RAISE OKL_API.G_EXCEPTION_ERROR;
1198
1199 End If;
1200 Close l_ppyh_csr;
1201 If l_ppyh_found = 'Y' Then
1202 --delete party payment records
1203 --Fetch party payment details records
1204 Open l_ppyl_csr (p_ppyh_id => p_tnc_id);
1205 Loop
1206 Fetch l_ppyl_csr into l_ppyl_id, l_cpl_id;
1207 Exit when l_ppyl_csr%NOTFOUND;
1208 If l_ppyl_id is Not Null Then
1209 --delete the party payment detail
1210 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1211 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'OKL_DEAL_CREAT_PVT.delete_tnc_group.', 'Before call to delete_party_payment_dtls = '||x_return_status);
1212 END IF;
1213 l_ppydv_rec.id := l_ppyl_id;
1214 OKL_PARTY_PAYMENTS_PVT.delete_party_payment_dtls(
1215 p_api_version => p_api_version,
1216 p_init_msg_list => p_init_msg_list,
1217 x_return_status => x_return_status,
1218 x_msg_count => x_msg_count,
1219 x_msg_data => x_msg_data,
1220 p_ppydv_rec => l_ppydv_rec
1221 );
1222 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1223 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'OKL_DEAL_CREAT_PVT.delete_tnc_group.', 'After call to delete_party_payment_dtls = '||x_return_status);
1224 END IF;
1225 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1226 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1227 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1228 RAISE OKL_API.G_EXCEPTION_ERROR;
1229 END IF;
1230 End If;
1231 End Loop;
1232 Close l_ppyl_csr;
1233 --Delete the party payment header record
1234 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1235 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'OKL_DEAL_CREAT_PVT.delete_tnc_group.', 'Before call to delete_party_payment_hdr = '||x_return_status);
1236 END IF;
1237 l_pphv_rec.id := p_tnc_id;
1238 OKL_PARTY_PAYMENTS_PVT.delete_party_payment_hdr(
1239 p_api_version => p_api_version,
1240 p_init_msg_list => p_init_msg_list,
1241 x_return_status => x_return_status,
1242 x_msg_count => x_msg_count,
1243 x_msg_data => x_msg_data,
1244 p_pphv_rec => l_pphv_rec
1245 );
1246 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1247 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'OKL_DEAL_CREAT_PVT.delete_tnc_group.', 'After call to delete_party_payment_hdr = '||x_return_status);
1248 END IF;
1249 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1250 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1251 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1252 RAISE OKL_API.G_EXCEPTION_ERROR;
1253 END IF;
1254 End If;
1255 End If;
1256 OKL_API.END_ACTIVITY (x_msg_count,x_msg_data );
1257
1258 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1259 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'OKL_DEAL_CREAT_PVT.delete_tnc_group.', 'End(-)');
1260 END IF;
1261
1262 EXCEPTION
1263 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1264 IF l_rgp_csr%ISOPEN THEN
1265 CLOSE l_rgp_csr;
1266 END IF;
1267 IF l_ppyh_csr%ISOPEN THEN
1268 CLOSE l_ppyh_csr;
1269 END IF;
1270 IF l_ppyl_csr%ISOPEN THEN
1271 CLOSE l_ppyl_csr;
1272 END IF;
1273 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1274 l_api_name,
1275 G_PKG_NAME,
1276 'OKL_API.G_RET_STS_ERROR',
1277 x_msg_count,
1278 x_msg_data,
1279 '_PVT');
1280 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1281 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'OKL_DEAL_CREAT_PVT.delete_tnc_group.', 'EXP - ERROR');
1282 END IF;
1283 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1284 IF l_rgp_csr%ISOPEN THEN
1285 CLOSE l_rgp_csr;
1286 END IF;
1287 IF l_ppyh_csr%ISOPEN THEN
1288 CLOSE l_ppyh_csr;
1289 END IF;
1290 IF l_ppyl_csr%ISOPEN THEN
1291 CLOSE l_ppyl_csr;
1292 END IF;
1293 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
1294 l_api_name,
1295 G_PKG_NAME,
1296 'OKL_API.G_RET_STS_UNEXP_ERROR',
1297 x_msg_count,
1298 x_msg_data,
1299 '_PVT');
1300 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1301 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'OKL_DEAL_CREAT_PVT.delete_tnc_group.', 'EXP - UNEXCP ERROR');
1302 END IF;
1303 WHEN OTHERS THEN
1304 IF l_rgp_csr%ISOPEN THEN
1305 CLOSE l_rgp_csr;
1306 END IF;
1307 IF l_ppyh_csr%ISOPEN THEN
1308 CLOSE l_ppyh_csr;
1309 END IF;
1310 IF l_ppyl_csr%ISOPEN THEN
1311 CLOSE l_ppyl_csr;
1312 END IF;
1313 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
1314 l_api_name,
1315 G_PKG_NAME,
1316 'OTHERS',
1317 x_msg_count,
1318 x_msg_data,
1319 '_PVT');
1320 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1321 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'OKL_DEAL_CREAT_PVT.delete_tnc_group.', 'EXP - OTHERS');
1322 END IF;
1323 END delete_tnc_group;
1324
1325 End OKL_DEAL_TERMS_PVT;