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