[Home] [Help]
PACKAGE BODY: APPS.OKL_RGRP_RULES_PROCESS_PVT
Source
1 PACKAGE BODY OKL_RGRP_RULES_PROCESS_PVT as
2 /* $Header: OKLRRGRB.pls 120.14.12010000.4 2009/08/25 18:41:43 sechawla ship $ */
3
4 --G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'SQLerrm';
5 --G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'SQLcode';
6 G_EXCEPTION_HALT_PROCESSING exception;
7 G_EXCEPTION_STOP_VALIDATION exception;
8
9
10 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKL_RGRP_RULES_PROCESS_PVT';
11 G_APP_NAME CONSTANT VARCHAR2(3) := OKL_API.G_APP_NAME;
12 G_API_TYPE CONSTANT VARCHAR2(4) := '_PVT';
13 l_api_name VARCHAR2(35) := 'RGRP_RULES_PROCESS';
14
15 /*
16 * sjalasut: aug 18, 04 added constants used in raising business event. BEGIN
17 */
18 G_WF_EVT_CONTRACT_TERM_UPDATED CONSTANT VARCHAR2(70):= 'oracle.apps.okl.la.lease_contract.asset_filing_terms_updated';
19 G_WF_EVT_ASSET_FILING_UPDATED CONSTANT VARCHAR2(70):= 'oracle.apps.okl.la.lease_contract.asset_filing_updated';
20 G_WF_EVT_ASSET_PROPTAX_UPDATED CONSTANT VARCHAR2(70):= 'oracle.apps.okl.la.lease_contract.asset_property_tax_updated';
21 G_WF_EVT_SERV_PASS_UPDATED CONSTANT VARCHAR2(70):= 'oracle.apps.okl.la.lease_contract.service_fee_passthrough_updated';
22 G_WF_EVT_FEE_PASS_UPDATED CONSTANT VARCHAR2(70):= 'oracle.apps.okl.la.lease_contract.fee_passthrough_updated';
23 G_WF_EVT_SERV_FEXP_UPDATED CONSTANT VARCHAR2(70):= 'oracle.apps.okl.la.lease_contract.service_fee_expense_updated';
24 G_WF_EVT_FEE_EXP_UPDATED CONSTANT VARCHAR2(70):= 'oracle.apps.okl.la.lease_contract.fee_expense_updated';
25
26 G_WF_ITM_CONTRACT_ID CONSTANT VARCHAR2(30) := 'CONTRACT_ID';
27 G_WF_ITM_ASSET_ID CONSTANT VARCHAR2(30) := 'ASSET_ID';
28 G_WF_ITM_CONTRACT_PROCESS CONSTANT VARCHAR2(30) := 'CONTRACT_PROCESS';
29 G_WF_ITM_TERMS_ID CONSTANT VARCHAR2(30) := 'TERMS_ID';
30 G_WF_ITM_SERV_LINE_ID CONSTANT VARCHAR2(30) := 'SERVICE_LINE_ID';
31 G_WF_ITM_SERV_CHR_ID CONSTANT VARCHAR2(30) := 'SERVICE_CONTRACT_ID';
32 G_WF_ITM_SERV_CLE_ID CONSTANT VARCHAR2(30) := 'SERVICE_CONTRACT_LINE_ID';
33 G_WF_ITM_FEE_LINE_ID CONSTANT VARCHAR2(30) := 'FEE_LINE_ID';
34 /*
35 * sjalasut: aug 18, 04 added constants used in raising business event. END
36 */
37 -------------------------------------------------------------------------------
38 -- PROCEDURE raise_business_event
39 -------------------------------------------------------------------------------
40 -- Start of comments
41 --
42 -- Procedure Name : raise_business_event
43 -- Description : This procedure is a wrapper that raises a business event
44 -- : when ever
45 -- a. Asset Tax, Property Tax is created, updated
46 -- b. Filing for Liens,Tile and Registration is created
47 -- or updated at terms and conditions level
48 -- c. Service Expense and Passthrough are created, updated.
49 -- Business Rules :
50 -- Parameters : p_chr_id,p_asset_id, p_event_name along with other api params
51 -- Version : 1.0
52 -- History : 30-AUG-2004 SJALASUT created
53 -- End of comments
54
55 PROCEDURE raise_business_event(p_api_version IN NUMBER,
56 p_init_msg_list IN VARCHAR2,
57 x_return_status OUT NOCOPY VARCHAR2,
58 x_msg_count OUT NOCOPY NUMBER,
59 x_msg_data OUT NOCOPY VARCHAR2,
60 p_event_name IN VARCHAR2,
61 p_event_param_list IN WF_PARAMETER_LIST_T
62 ) IS
63 l_contract_process VARCHAR2(20);
64 l_event_param_list WF_PARAMETER_LIST_T := p_event_param_list;
65 BEGIN
66 x_return_status := OKL_API.G_RET_STS_SUCCESS;
67
68 -- wrapper API to get contract process. this API determines in which status the
69 -- contract in question is.
70 l_contract_process := okl_lla_util_pvt.get_contract_process(
71 p_chr_id => wf_event.GetValueForParameter(G_WF_ITM_CONTRACT_ID, l_event_param_list)
72 );
73 -- add the contract status to the event parameter list
74 wf_event.AddParameterToList(G_WF_ITM_CONTRACT_PROCESS, l_contract_process, l_event_param_list);
75
76 OKL_WF_PVT.raise_event(p_api_version => p_api_version,
77 p_init_msg_list => p_init_msg_list,
78 x_return_status => x_return_status,
79 x_msg_count => x_msg_count,
80 x_msg_data => x_msg_data,
81 p_event_name => p_event_name,
82 p_parameters => l_event_param_list);
83 EXCEPTION
84 WHEN OTHERS THEN
85 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
86 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
87 END raise_business_event;
88
89
90 PROCEDURE migrate_rec(
91 p_rgr_rec IN rgr_rec_type,
92 x_rulv_rec OUT NOCOPY rulv_rec_type) IS
93 BEGIN
94 x_rulv_rec.id := p_rgr_rec.rule_id;
95 x_rulv_rec.object_version_number := p_rgr_rec.object_version_number;
96 x_rulv_rec.sfwt_flag := p_rgr_rec.sfwt_flag;
97 x_rulv_rec.object1_id1 := p_rgr_rec.object1_id1;
98 x_rulv_rec.object2_id1 := p_rgr_rec.object2_id1;
99 x_rulv_rec.object3_id1 := p_rgr_rec.object3_id1;
100 x_rulv_rec.object1_id2 := p_rgr_rec.object1_id2;
101 x_rulv_rec.object2_id2 := p_rgr_rec.object2_id2;
102 x_rulv_rec.object3_id2 := p_rgr_rec.object3_id2;
103 x_rulv_rec.jtot_object1_code := p_rgr_rec.jtot_object1_code;
104 x_rulv_rec.jtot_object2_code := p_rgr_rec.jtot_object2_code;
105 x_rulv_rec.jtot_object3_code := p_rgr_rec.jtot_object3_code;
106 x_rulv_rec.dnz_chr_id := p_rgr_rec.dnz_chr_id;
107 x_rulv_rec.rgp_id := p_rgr_rec.rgp_id;
108 x_rulv_rec.priority := p_rgr_rec.priority;
109 x_rulv_rec.std_template_yn := p_rgr_rec.std_template_yn;
110 x_rulv_rec.comments := p_rgr_rec.comments;
111 x_rulv_rec.warn_yn := p_rgr_rec.warn_yn;
112 x_rulv_rec.attribute_category := p_rgr_rec.attribute_category;
113 x_rulv_rec.attribute1 := p_rgr_rec.attribute1;
114 x_rulv_rec.attribute2 := p_rgr_rec.attribute2;
115 x_rulv_rec.attribute3 := p_rgr_rec.attribute3;
116 x_rulv_rec.attribute4 := p_rgr_rec.attribute4;
117 x_rulv_rec.attribute5 := p_rgr_rec.attribute5;
118 x_rulv_rec.attribute6 := p_rgr_rec.attribute6;
119 x_rulv_rec.attribute7 := p_rgr_rec.attribute7;
120 x_rulv_rec.attribute8 := p_rgr_rec.attribute8;
121 x_rulv_rec.attribute9 := p_rgr_rec.attribute9;
122 x_rulv_rec.attribute10 := p_rgr_rec.attribute10;
123 x_rulv_rec.attribute11 := p_rgr_rec.attribute11;
124 x_rulv_rec.attribute12 := p_rgr_rec.attribute12;
125 x_rulv_rec.attribute13 := p_rgr_rec.attribute13;
126 x_rulv_rec.attribute14 := p_rgr_rec.attribute14;
127 x_rulv_rec.attribute15 := p_rgr_rec.attribute15;
128 --text OKC_RULES_V.TEXT%TYPE := NULL,
129 x_rulv_rec.created_by := p_rgr_rec.created_by;
130 x_rulv_rec.creation_date := p_rgr_rec.creation_date;
131 x_rulv_rec.last_updated_by := p_rgr_rec.last_updated_by;
132 x_rulv_rec.last_update_date := p_rgr_rec.last_update_date;
133 x_rulv_rec.last_update_login := p_rgr_rec.last_update_login;
134 x_rulv_rec.rule_information_category := p_rgr_rec.rule_information_category;
135 x_rulv_rec.rule_information1 := p_rgr_rec.rule_information1;
136 x_rulv_rec.rule_information2 := p_rgr_rec.rule_information2;
137 x_rulv_rec.rule_information3 := p_rgr_rec.rule_information3;
138 x_rulv_rec.rule_information4 := p_rgr_rec.rule_information4;
139 x_rulv_rec.rule_information5 := p_rgr_rec.rule_information5;
140 x_rulv_rec.rule_information6 := p_rgr_rec.rule_information6;
141 x_rulv_rec.rule_information7 := p_rgr_rec.rule_information7;
142 x_rulv_rec.rule_information8 := p_rgr_rec.rule_information8;
143 x_rulv_rec.rule_information9 := p_rgr_rec.rule_information9;
144 x_rulv_rec.rule_information10 := p_rgr_rec.rule_information10;
145 x_rulv_rec.rule_information11 := p_rgr_rec.rule_information11;
146 x_rulv_rec.rule_information12 := p_rgr_rec.rule_information12;
147 x_rulv_rec.rule_information13 := p_rgr_rec.rule_information13;
148 x_rulv_rec.rule_information14 := p_rgr_rec.rule_information14;
149 x_rulv_rec.rule_information15 := p_rgr_rec.rule_information15;
150 x_rulv_rec.template_yn := NVL(p_rgr_rec.template_yn,'N');
151 x_rulv_rec.ans_set_jtot_object_code := NVL(p_rgr_rec.ans_set_jtot_object_code,'');
152 x_rulv_rec.ans_set_jtot_object_id1 := NVL(p_rgr_rec.ans_set_jtot_object_id1,'');
153 x_rulv_rec.ans_set_jtot_object_id2 := NVL(p_rgr_rec.ans_set_jtot_object_id2,'');
154 x_rulv_rec.display_sequence := NVL(p_rgr_rec.display_sequence,'');
155 end migrate_rec;
156 PROCEDURE update_rule_rec(
157 p_api_version IN NUMBER,
158 p_init_msg_list IN VARCHAR2,
159 x_return_status OUT NOCOPY VARCHAR2,
160 x_msg_count OUT NOCOPY NUMBER,
161 x_msg_data OUT NOCOPY VARCHAR2,
162 p_rgr_rec IN rgr_rec_type) IS
163 l_rgr_rec rgr_rec_type := p_rgr_rec;
164 l_rulv_rec rulv_rec_type;
165 lx_rulv_rec rulv_rec_type;
166
167 --l_api_name VARCHAR2(35) := 'RGRP_RULES_PROCESS_PVT';
168 l_proc_name VARCHAR2(35) := 'UPDATE_RULE_REC';
169 l_api_version CONSTANT VARCHAR2(30) := p_api_version;
170
171 BEGIN
172 migrate_rec(p_rgr_rec => p_rgr_rec,
173 x_rulv_rec => l_rulv_rec);
174
175 OKL_RULE_PUB.update_rule(
176 p_api_version => l_api_version,
177 p_init_msg_list => p_init_msg_list,
178 x_return_status => x_return_status,
179 x_msg_count => x_msg_count,
180 x_msg_data => x_msg_data,
181 p_rulv_rec => l_rulv_rec,
182 x_rulv_rec => lx_rulv_rec);
183
184 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
185 RAISE OKC_API.G_EXCEPTION_ERROR;
186 END IF;
187
188 --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
189 /*
190 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
191 x_msg_data => x_msg_data);
192 */
193
194 EXCEPTION
195 when OKC_API.G_EXCEPTION_ERROR then
196 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
197 p_api_name => l_api_name,
198 p_pkg_name => G_PKG_NAME,
199 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
200 x_msg_count => x_msg_count,
201 x_msg_data => x_msg_data,
202 p_api_type => G_API_TYPE);
203
204 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
205 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
206 p_api_name => l_api_name,
207 p_pkg_name => G_PKG_NAME,
208 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
209 x_msg_count => x_msg_count,
210 x_msg_data => x_msg_data,
211 p_api_type => G_API_TYPE);
212
213 when OTHERS then
214 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
215 p_api_name => l_api_name,
216 p_pkg_name => G_PKG_NAME,
217 p_exc_name => 'OTHERS',
218 x_msg_count => x_msg_count,
219 x_msg_data => x_msg_data,
220 p_api_type => G_API_TYPE);
221
222 END update_rule_rec;
223
224 FUNCTION get_header_rule_group_id(
225 p_api_version IN NUMBER,
226 p_init_msg_list IN VARCHAR2,
227 x_return_status OUT NOCOPY VARCHAR2,
228 x_msg_count OUT NOCOPY NUMBER,
229 x_msg_data OUT NOCOPY VARCHAR2,
230 p_chr_id IN NUMBER,
231 p_rgr_rec IN rgr_rec_type)
232 RETURN OKC_RULE_GROUPS_B.ID%TYPE IS
233 i NUMBER := 0;
234 j NUMBER;
235 rule_group_id NUMBER := null;
236 l_rgr_rec rgr_rec_type := p_rgr_rec;
237 cursor RULE_GROUP_CSR(P_CHR_ID IN NUMBER, P_RGD_CODE IN VARCHAR2) is
238 SELECT ID FROM OKC_RULE_GROUPS_B
239 WHERE CHR_ID = P_CHR_ID AND
240 DNZ_CHR_ID = P_CHR_ID AND
241 CLE_ID IS NULL AND
242 RGD_CODE = P_RGD_CODE;
243 BEGIN
244 open RULE_GROUP_CSR(p_chr_id,p_rgr_rec.rgd_code);
245 fetch RULE_GROUP_CSR into rule_group_id;
246 if(RULE_GROUP_CSR%NOTFOUND) then
247 close RULE_GROUP_CSR;
248 x_return_status := OKC_API.G_RET_STS_ERROR;
249 -- halt further validation of this column
250 else
251 x_return_status := OKC_API.G_RET_STS_SUCCESS;
252 end if;
253 return rule_group_id;
254 END get_header_rule_group_id;
255 FUNCTION get_header_rule_group_id(
256 p_api_version IN NUMBER,
257 p_init_msg_list IN VARCHAR2,
258 x_return_status OUT NOCOPY VARCHAR2,
259 x_msg_count OUT NOCOPY NUMBER,
260 x_msg_data OUT NOCOPY VARCHAR2,
261 p_chr_id IN NUMBER,
262 p_rgd_code IN VARCHAR2)
263 RETURN OKC_RULE_GROUPS_B.ID%TYPE IS
264 rule_group_id NUMBER := null;
265 cursor RULE_GROUP_CSR(P_CHR_ID IN NUMBER, P_RGD_CODE IN VARCHAR2) is
266 SELECT ID FROM OKC_RULE_GROUPS_B
267 WHERE CHR_ID = P_CHR_ID AND
268 DNZ_CHR_ID = P_CHR_ID AND
269 CLE_ID IS NULL AND
270 RGD_CODE = P_RGD_CODE;
271 BEGIN
272 open RULE_GROUP_CSR(p_chr_id,p_rgd_code);
273 fetch RULE_GROUP_CSR into rule_group_id;
274 if(RULE_GROUP_CSR%NOTFOUND) then
275 close RULE_GROUP_CSR;
276 -- x_return_status := OKC_API.G_RET_STS_ERROR;
277 x_return_status := OKC_API.G_RET_STS_SUCCESS;
278 -- halt further validation of this column
279 else
280 x_return_status := OKC_API.G_RET_STS_SUCCESS;
281 end if;
282 return rule_group_id;
283 END get_header_rule_group_id;
284 FUNCTION get_line_rule_group_id(
285 p_api_version IN NUMBER,
286 p_init_msg_list IN VARCHAR2,
287 x_return_status OUT NOCOPY VARCHAR2,
288 x_msg_count OUT NOCOPY NUMBER,
289 x_msg_data OUT NOCOPY VARCHAR2,
290 p_chr_id IN NUMBER,
291 p_line_id IN NUMBER,
292 p_rgr_rec IN rgr_rec_type)
293 RETURN OKC_RULE_GROUPS_B.ID%TYPE IS
294 i NUMBER := 0;
295 j NUMBER;
296 rule_group_id NUMBER := null;
297 l_rgr_rec rgr_rec_type := p_rgr_rec;
298 cursor RULE_GROUP_CSR(P_CHR_ID IN NUMBER, P_LINE_ID IN NUMBER,
299 P_RGD_CODE IN VARCHAR2) is
300 SELECT ID FROM OKC_RULE_GROUPS_B
301 WHERE CHR_ID IS NULL AND
302 DNZ_CHR_ID = P_CHR_ID AND
303 CLE_ID = P_LINE_ID AND
304 RGD_CODE = P_RGD_CODE;
305 BEGIN
306 open RULE_GROUP_CSR(p_chr_id,p_line_id,p_rgr_rec.rgd_code);
307 fetch RULE_GROUP_CSR into rule_group_id;
308 if(RULE_GROUP_CSR%NOTFOUND) then
309 close RULE_GROUP_CSR;
310 -- x_return_status := OKC_API.G_RET_STS_ERROR;
311 x_return_status := OKC_API.G_RET_STS_SUCCESS;
312 else
313 x_return_status := OKC_API.G_RET_STS_SUCCESS;
314 end if;
315 return rule_group_id;
316 END get_line_rule_group_id;
317 FUNCTION get_rule_id(
318 p_api_version IN NUMBER,
319 p_init_msg_list IN VARCHAR2,
320 x_return_status OUT NOCOPY VARCHAR2,
321 x_msg_count OUT NOCOPY NUMBER,
322 x_msg_data OUT NOCOPY VARCHAR2,
323 p_chr_id IN NUMBER,
324 p_rgr_rec IN rgr_rec_type)
325 RETURN OKC_RULES_V.ID%TYPE IS
326 i NUMBER := 0;
327 j NUMBER;
328 rule_id NUMBER := null;
329 l_rgr_rec rgr_rec_type := p_rgr_rec;
330 cursor RULE_CSR(P_CHR_ID IN NUMBER, P_RGP_ID IN NUMBER,
331 P_RULE_CODE IN VARCHAR2) is
332 SELECT ID FROM OKC_RULES_V
333 WHERE DNZ_CHR_ID = P_CHR_ID AND
334 RGP_ID = P_RGP_ID AND
335 RULE_INFORMATION_CATEGORY = P_RULE_CODE;
336 BEGIN
337 open RULE_CSR(p_chr_id, p_rgr_rec.rgp_id,
338 p_rgr_rec.rule_information_category);
339 fetch RULE_CSR into rule_id;
340 if(RULE_CSR%NOTFOUND) then
341 close RULE_CSR;
342 -- x_return_status := OKC_API.G_RET_STS_ERROR;
343 x_return_status := OKC_API.G_RET_STS_SUCCESS;
344 -- halt further validation of this column
345 else
346 x_return_status := OKC_API.G_RET_STS_SUCCESS;
347 end if;
348 return rule_id;
349 END get_rule_id;
350 PROCEDURE create_hdr_rule_group_rules(
351 p_api_version IN NUMBER,
352 p_init_msg_list IN VARCHAR2,
353 x_return_status OUT NOCOPY VARCHAR2,
354 x_msg_count OUT NOCOPY NUMBER,
355 x_msg_data OUT NOCOPY VARCHAR2,
356 p_chr_id IN NUMBER,
357 p_rule_group_id IN NUMBER,
358 px_rgr_rec IN OUT NOCOPY rgr_rec_type) IS
359 i NUMBER := 0;
360 j NUMBER;
361 -- rule_group_id NUMBER := null;
362 -- rgs_code VARCHAR(30);
363 l_rgr_rec rgr_rec_type := px_rgr_rec;
364 l_rgpv_rec rgpv_rec_type;
365 lx_rgpv_rec rgpv_rec_type;
366 l_rulv_rec rulv_rec_type;
367 lx_rulv_rec rulv_rec_type;
368
369 --l_api_name VARCHAR2(35) := 'RGRP_RULES_PROCESS_PVT';
370 l_proc_name VARCHAR2(35) := 'CREATE_HDR_RULE_GROUP_RULES';
371 l_api_version CONSTANT VARCHAR2(30) := p_api_version;
372
373 --avsingh
374 Cursor l_lock_hdr_csr(ChrId IN NUMBER) is
375 Select chr.id,
376 chr.last_update_date
377 from okc_k_headers_b chr
378 where chr.id = ChrId
379 for update of chr.last_update_date nowait;
380
381 l_chr_id number;
382 l_update_date date;
383 --avsingh
384
385
386 BEGIN
387
388 --l_rgr_rec := px_rgr_rec;
389 if(p_rule_group_id is null or p_rule_group_id = OKC_API.G_MISS_NUM) then
390 l_rgpv_rec.rgd_code := l_rgr_rec.rgd_code;
391 l_rgpv_rec.chr_id := p_chr_id;
392 l_rgpv_rec.dnz_chr_id := p_chr_id;
393 l_rgpv_rec.cle_id := null;
394 l_rgpv_rec.dnz_chr_id := p_chr_id;
395 l_rgpv_rec.object_version_number := l_rgr_rec.object_version_number;
396 -- l_rgpv_rec.object_version_number := 1;
397 l_rgpv_rec.rgp_type := 'KRG';
398 -- l_rgpv_rec.created_by := l_rgr_rec.created_by;
399 -- l_rgpv_rec.creation_date := l_rgr_rec.creation_date;
400 -- l_rgpv_rec.last_updated_by := l_rgr_rec.last_updated_by;
401 -- l_rgpv_rec.last_update_date := l_rgr_rec.last_update_date;
402 -- l_rgpv_rec.last_update_login := l_rgr_rec.last_update_login;
403
404
405 Open l_lock_hdr_csr(p_chr_id);
406 Fetch l_lock_hdr_csr into l_chr_id, l_update_date;
407
408
409 OKL_RULE_PUB.create_rule_group(
410 p_api_version => p_api_version,
411 p_init_msg_list => p_init_msg_list,
412 x_return_status => x_return_status,
413 x_msg_count => x_msg_count,
414 x_msg_data => x_msg_data,
415 p_rgpv_rec => l_rgpv_rec,
416 x_rgpv_rec => lx_rgpv_rec);
417 px_rgr_rec.rgp_id := lx_rgpv_rec.id;
418 px_rgr_rec.dnz_chr_id := p_chr_id;
419
420
421 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
422 RAISE OKC_API.G_EXCEPTION_ERROR;
423 END IF;
424
425
426 /*
427 px_rgr_rec.id := get_rule_id(
428 p_api_version => p_api_version,
429 p_init_msg_list => p_init_msg_list,
430 x_return_status => x_return_status,
431 x_msg_count => x_msg_count,
432 x_msg_data => x_msg_data,
433 p_chr_id => p_chr_id,
434 p_rgr_rec => px_rgr_rec);
435 */
436 -- create all rules under this rule group
437 l_rulv_rec.object_version_number := l_rgr_rec.object_version_number;
438 l_rulv_rec.sfwt_flag := l_rgr_rec.sfwt_flag;
439 l_rulv_rec.dnz_chr_id := p_chr_id;
440 l_rulv_rec.rgp_id := lx_rgpv_rec.id;
441 l_rulv_rec.std_template_yn := l_rgr_rec.std_template_yn;
442 l_rulv_rec.warn_yn := l_rgr_rec.warn_yn;
443 l_rulv_rec.template_yn := l_rgr_rec.template_yn;
444 -- l_rulv_rec.created_by := 0;
445 -- l_rulv_rec.created_by := l_rgr_rec.created_by; --req
446 -- l_rulv_rec.creation_date := l_rgr_rec.creation_date;
447 -- l_rulv_rec.last_updated_by := l_rgr_rec.last_updated_by;
448 -- l_rulv_rec.last_update_date := l_rgr_rec.last_update_date;
449 -- l_rulv_rec.last_update_login := l_rgr_rec.last_update_login;
450 l_rulv_rec.rule_information_category := l_rgr_rec.rule_information_category;
451
452
453
454 OKL_RULE_PUB.create_rule(
455 p_api_version => p_api_version,
456 p_init_msg_list => p_init_msg_list,
457 x_return_status => x_return_status,
458 x_msg_count => x_msg_count,
459 x_msg_data => x_msg_data,
460 p_rulv_rec => l_rulv_rec,
461 x_rulv_rec => lx_rulv_rec);
462
463 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
464 RAISE OKC_API.G_EXCEPTION_ERROR;
465 END IF;
466 close l_lock_hdr_csr;
467 else
468 px_rgr_rec.rgp_id := p_rule_group_id;
469 end if;
470 -- px_rgr_rec.dnz_chr_id := p_chr_id;
471 px_rgr_rec.rule_id := get_rule_id(
472 p_api_version => p_api_version,
473 p_init_msg_list => p_init_msg_list,
474 x_return_status => x_return_status,
475 x_msg_count => x_msg_count,
476 x_msg_data => x_msg_data,
477 p_chr_id => p_chr_id,
478 p_rgr_rec => px_rgr_rec);
479 if(px_rgr_rec.rule_id is null or px_rgr_rec.rule_id = OKC_API.G_MISS_NUM) then
480 l_rulv_rec.object_version_number := l_rgr_rec.object_version_number;
481 l_rulv_rec.sfwt_flag := l_rgr_rec.sfwt_flag;
482 l_rulv_rec.dnz_chr_id := p_chr_id;
483 l_rulv_rec.rgp_id := px_rgr_rec.rgp_id;
484 l_rulv_rec.std_template_yn := l_rgr_rec.std_template_yn;
485 l_rulv_rec.warn_yn := l_rgr_rec.warn_yn;
486 l_rulv_rec.template_yn := l_rgr_rec.template_yn;
487 -- l_rulv_rec.created_by := 0;
488 -- l_rulv_rec.created_by := l_rgr_rec.created_by; --req
489 -- l_rulv_rec.creation_date := l_rgr_rec.creation_date;
490 -- l_rulv_rec.last_updated_by := l_rgr_rec.last_updated_by;
491 -- l_rulv_rec.last_update_date := l_rgr_rec.last_update_date;
492 -- l_rulv_rec.last_update_login := l_rgr_rec.last_update_login;
493 l_rulv_rec.rule_information_category := l_rgr_rec.rule_information_category;
494 OKL_RULE_PUB.create_rule(
495 p_api_version => p_api_version,
496 p_init_msg_list => p_init_msg_list,
497 x_return_status => x_return_status,
498 x_msg_count => x_msg_count,
499 x_msg_data => x_msg_data,
500 p_rulv_rec => l_rulv_rec,
501 x_rulv_rec => lx_rulv_rec);
502
503 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
504 RAISE OKC_API.G_EXCEPTION_ERROR;
505 END IF;
506
507 px_rgr_rec.rule_id := lx_rulv_rec.id;
508
509 end if;
510
511
512 EXCEPTION
513 when OKC_API.G_EXCEPTION_ERROR then
514 If l_lock_hdr_csr%ISOPEN then
515 close l_lock_hdr_csr;
516 End If;
517 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
518 p_api_name => l_api_name,
519 p_pkg_name => G_PKG_NAME,
520 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
521 x_msg_count => x_msg_count,
522 x_msg_data => x_msg_data,
523 p_api_type => G_API_TYPE);
524
525 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
526 If l_lock_hdr_csr%ISOPEN then
527 close l_lock_hdr_csr;
528 End If;
529
530 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
531 p_api_name => l_api_name,
532 p_pkg_name => G_PKG_NAME,
533 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
534 x_msg_count => x_msg_count,
535 x_msg_data => x_msg_data,
536 p_api_type => G_API_TYPE);
537
538 when OTHERS then
539 If l_lock_hdr_csr%ISOPEN then
540 close l_lock_hdr_csr;
541 End If;
542
543 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
544 p_api_name => l_api_name,
545 p_pkg_name => G_PKG_NAME,
546 p_exc_name => 'OTHERS',
547 x_msg_count => x_msg_count,
548 x_msg_data => x_msg_data,
549 p_api_type => G_API_TYPE);
550
551
552 END create_hdr_rule_group_rules;
553
554 PROCEDURE create_line_rule_group_rules(
555 p_api_version IN NUMBER,
556 p_init_msg_list IN VARCHAR2,
557 x_return_status OUT NOCOPY VARCHAR2,
558 x_msg_count OUT NOCOPY NUMBER,
559 x_msg_data OUT NOCOPY VARCHAR2,
560 p_chr_id IN NUMBER,
561 p_line_id IN NUMBER,
562 p_rule_group_id IN NUMBER,
563 px_rgr_rec IN OUT NOCOPY rgr_rec_type) IS
564 i NUMBER := 0;
565 j NUMBER;
566 -- rule_group_id NUMBER := null;
567 -- rgs_code VARCHAR(30);
568 l_rgr_rec rgr_rec_type := px_rgr_rec;
569 l_rgpv_rec rgpv_rec_type;
570 lx_rgpv_rec rgpv_rec_type;
571 l_rulv_rec rulv_rec_type;
572 lx_rulv_rec rulv_rec_type;
573
574 --avsingh
575 Cursor l_lock_cle_csr(CleId IN NUMBER) is
576 Select cle.id,
577 cle.last_update_date
578 from okc_k_lines_b cle
579 where cle.id = CleId
580 for update of cle.last_update_date nowait;
581
582 l_cle_id number;
583 l_update_date date;
584 --avsingh
585
586 BEGIN
587 --l_rgr_rec := px_rgr_rec;
588 if(p_rule_group_id is null or p_rule_group_id = OKC_API.G_MISS_NUM) then
589 l_rgpv_rec.rgd_code := l_rgr_rec.rgd_code;
590 l_rgpv_rec.chr_id := null;
591 l_rgpv_rec.dnz_chr_id := p_chr_id;
592 l_rgpv_rec.cle_id := p_line_id;
593 l_rgpv_rec.dnz_chr_id := p_chr_id;
594 l_rgpv_rec.object_version_number := l_rgr_rec.object_version_number;
595 -- l_rgpv_rec.object_version_number := 1;
596 l_rgpv_rec.rgp_type := 'KRG';
597 -- l_rgpv_rec.created_by := l_rgr_rec.created_by;
598 -- l_rgpv_rec.creation_date := l_rgr_rec.creation_date;
599 -- l_rgpv_rec.last_updated_by := l_rgr_rec.last_updated_by;
600 -- l_rgpv_rec.last_update_date := l_rgr_rec.last_update_date;
601 -- l_rgpv_rec.last_update_login := l_rgr_rec.last_update_login;
602
603 Open l_lock_cle_csr(p_line_id);
604 Fetch l_lock_cle_csr into l_cle_id, l_update_date;
605
606 OKL_RULE_PUB.create_rule_group(
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_rgpv_rec => l_rgpv_rec,
613 x_rgpv_rec => lx_rgpv_rec);
614
615
616 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
617 RAISE OKC_API.G_EXCEPTION_ERROR;
618 END IF;
619
620 px_rgr_rec.rgp_id := lx_rgpv_rec.id;
621 px_rgr_rec.dnz_chr_id := p_chr_id;
622 /*
623 px_rgr_rec.id := get_rule_id(
624 p_api_version => p_api_version,
625 p_init_msg_list => p_init_msg_list,
626 x_return_status => x_return_status,
627 x_msg_count => x_msg_count,
628 x_msg_data => x_msg_data,
629 p_chr_id => p_chr_id,
630 p_rgr_rec => px_rgr_rec);
631 */
632 -- create all rules under this rule group
633 l_rulv_rec.object_version_number := l_rgr_rec.object_version_number;
634 l_rulv_rec.sfwt_flag := l_rgr_rec.sfwt_flag;
635 l_rulv_rec.dnz_chr_id := p_chr_id;
636 l_rulv_rec.rgp_id := lx_rgpv_rec.id;
637 l_rulv_rec.std_template_yn := l_rgr_rec.std_template_yn;
638 l_rulv_rec.warn_yn := l_rgr_rec.warn_yn;
639 l_rulv_rec.template_yn := l_rgr_rec.template_yn;
640 -- l_rulv_rec.created_by := 0;
641 -- l_rulv_rec.created_by := l_rgr_rec.created_by; --req
642 -- l_rulv_rec.creation_date := l_rgr_rec.creation_date;
643 -- l_rulv_rec.last_updated_by := l_rgr_rec.last_updated_by;
644 -- l_rulv_rec.last_update_date := l_rgr_rec.last_update_date;
645 -- l_rulv_rec.last_update_login := l_rgr_rec.last_update_login;
646 l_rulv_rec.rule_information_category := l_rgr_rec.rule_information_category;
647 OKL_RULE_PUB.create_rule(
648 p_api_version => p_api_version,
649 p_init_msg_list => p_init_msg_list,
650 x_return_status => x_return_status,
651 x_msg_count => x_msg_count,
652 x_msg_data => x_msg_data,
653 p_rulv_rec => l_rulv_rec,
654 x_rulv_rec => lx_rulv_rec);
655
656 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
657 RAISE OKC_API.G_EXCEPTION_ERROR;
658 END IF;
659 close l_lock_cle_csr;
660 else
661 px_rgr_rec.rgp_id := p_rule_group_id;
662 end if;
663 -- px_rgr_rec.dnz_chr_id := p_chr_id;
664 px_rgr_rec.rule_id := get_rule_id(
665 p_api_version => p_api_version,
666 p_init_msg_list => p_init_msg_list,
667 x_return_status => x_return_status,
668 x_msg_count => x_msg_count,
669 x_msg_data => x_msg_data,
670 p_chr_id => p_chr_id,
671 p_rgr_rec => px_rgr_rec);
672 if(px_rgr_rec.rule_id is null or px_rgr_rec.rule_id = OKC_API.G_MISS_NUM) then
673 l_rulv_rec.object_version_number := l_rgr_rec.object_version_number;
674 l_rulv_rec.sfwt_flag := l_rgr_rec.sfwt_flag;
675 l_rulv_rec.dnz_chr_id := p_chr_id;
676 l_rulv_rec.rgp_id := px_rgr_rec.rgp_id;
677 l_rulv_rec.std_template_yn := l_rgr_rec.std_template_yn;
678 l_rulv_rec.warn_yn := l_rgr_rec.warn_yn;
679 l_rulv_rec.template_yn := l_rgr_rec.template_yn;
680 -- l_rulv_rec.created_by := 0;
681 -- l_rulv_rec.created_by := l_rgr_rec.created_by; --req
682 -- l_rulv_rec.creation_date := l_rgr_rec.creation_date;
683 -- l_rulv_rec.last_updated_by := l_rgr_rec.last_updated_by;
684 -- l_rulv_rec.last_update_date := l_rgr_rec.last_update_date;
685 -- l_rulv_rec.last_update_login := l_rgr_rec.last_update_login;
686 l_rulv_rec.rule_information_category := l_rgr_rec.rule_information_category;
687 OKL_RULE_PUB.create_rule(
688 p_api_version => p_api_version,
689 p_init_msg_list => p_init_msg_list,
690 x_return_status => x_return_status,
691 x_msg_count => x_msg_count,
692 x_msg_data => x_msg_data,
693 p_rulv_rec => l_rulv_rec,
694 x_rulv_rec => lx_rulv_rec);
695
696 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
697 RAISE OKC_API.G_EXCEPTION_ERROR;
698 END IF;
699 px_rgr_rec.rule_id := lx_rulv_rec.id;
700
701 end if;
702
703 EXCEPTION
704 when OKC_API.G_EXCEPTION_ERROR then
705 If l_lock_cle_csr%ISOPEN then
706 close l_lock_cle_csr;
707 End If;
708 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
709 p_api_name => l_api_name,
710 p_pkg_name => G_PKG_NAME,
711 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
712 x_msg_count => x_msg_count,
713 x_msg_data => x_msg_data,
714 p_api_type => G_API_TYPE);
715
716 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
717 If l_lock_cle_csr%ISOPEN then
718 close l_lock_cle_csr;
719 End If;
720 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
721 p_api_name => l_api_name,
722 p_pkg_name => G_PKG_NAME,
723 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
724 x_msg_count => x_msg_count,
725 x_msg_data => x_msg_data,
726 p_api_type => G_API_TYPE);
727
728 when OTHERS then
729 If l_lock_cle_csr%ISOPEN then
730 close l_lock_cle_csr;
731 End If;
732
733 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
734 p_api_name => l_api_name,
735 p_pkg_name => G_PKG_NAME,
736 p_exc_name => 'OTHERS',
737 x_msg_count => x_msg_count,
738 x_msg_data => x_msg_data,
739 p_api_type => G_API_TYPE);
740
741
742 END create_line_rule_group_rules;
743
744 PROCEDURE process_hdr_rule_group_rules(
745 p_api_version IN NUMBER,
746 p_init_msg_list IN VARCHAR2,
747 x_return_status OUT NOCOPY VARCHAR2,
748 x_msg_count OUT NOCOPY NUMBER,
749 x_msg_data OUT NOCOPY VARCHAR2,
750 p_chr_id IN NUMBER,
751 p_rgr_rec IN rgr_rec_type) IS
752 lx_rgr_rec rgr_rec_type := p_rgr_rec;
753 rule_group_id NUMBER := null;
754 l_return_status VARCHAR2(1);
755
756 CURSOR l_bill_to_csr IS
757 SELECT term.printing_lead_days printing_lead_days,
758 khr.cust_acct_id,
759 khr.bill_to_site_use_id
760 FROM okc_k_headers_b khr
761 ,hz_customer_profiles cp
762 ,ra_terms term
763 WHERE khr.id = p_chr_id
764 AND khr.bill_to_site_use_id = cp.site_use_id
765 AND cp.standard_terms = term.term_id;
766
767 l_bill_to_rec l_bill_to_csr%ROWTYPE;
768
769 BEGIN
770
771 if(lx_rgr_rec.rule_id is null or lx_rgr_rec.rule_id = OKC_API.G_MISS_NUM) then
772 rule_group_id := get_header_rule_group_id(
773 p_api_version => p_api_version,
774 p_init_msg_list => p_init_msg_list,
775 x_return_status => x_return_status,
776 x_msg_count => x_msg_count,
777 x_msg_data => x_msg_data,
778 p_chr_id => p_chr_id,
779 p_rgr_rec => lx_rgr_rec);
780
781 create_hdr_rule_group_rules(
782 p_api_version => p_api_version,
783 p_init_msg_list => p_init_msg_list,
784 x_return_status => x_return_status,
785 x_msg_count => x_msg_count,
786 x_msg_data => x_msg_data,
787 p_chr_id => p_chr_id,
788 p_rule_group_id => rule_group_id,
789 px_rgr_rec => lx_rgr_rec);
790 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
791 RAISE OKC_API.G_EXCEPTION_ERROR;
792 END IF;
793 end if;
794
795 --Bug#4542290
796 IF (lx_rgr_rec.rgd_code = 'LABILL' AND lx_rgr_rec.RULE_INFORMATION_CATEGORY = 'LAINVD') THEN
797 IF (lx_rgr_rec.RULE_INFORMATION3 IS NULL OR lx_rgr_rec.RULE_INFORMATION3 = OKC_API.G_MISS_CHAR) THEN
798 --FETCH print lead days and update record.
799 OPEN l_bill_to_csr;
800 FETCH l_bill_to_csr INTO l_bill_to_rec;
801 CLOSE l_bill_to_csr;
802 lx_rgr_rec.RULE_INFORMATION3 := l_bill_to_rec.printing_lead_days;
803 END IF;
804 END IF;
805
806 l_return_status := 'S';
807 IF (lx_rgr_rec.rgd_code = 'LAHDTX' AND lx_rgr_rec.RULE_INFORMATION_CATEGORY = 'LASTCL') THEN
808 okl_la_sales_tax_pvt.sync_contract_sales_tax(
809 p_api_version => p_api_version,
810 p_init_msg_list => p_init_msg_list,
811 x_return_status => l_return_status,
812 x_msg_count => x_msg_count,
813 x_msg_data => x_msg_data,
814 p_chr_id => p_chr_id);
815
816 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
817 x_return_status := l_return_status;
818 RAISE OKC_API.G_EXCEPTION_ERROR;
819 END IF;
820 END IF;
821
822 update_rule_rec(
823 p_api_version => p_api_version,
824 p_init_msg_list => p_init_msg_list,
825 x_return_status => x_return_status,
826 x_msg_count => x_msg_count,
827 x_msg_data => x_msg_data,
828 p_rgr_rec => lx_rgr_rec);
829 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
830 RAISE OKC_API.G_EXCEPTION_ERROR;
831 END IF;
832 EXCEPTION
833 when OKC_API.G_EXCEPTION_ERROR then
834 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
835 p_api_name => l_api_name,
836 p_pkg_name => G_PKG_NAME,
837 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
838 x_msg_count => x_msg_count,
839 x_msg_data => x_msg_data,
840 p_api_type => G_API_TYPE);
841
842 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
843 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
844 p_api_name => l_api_name,
845 p_pkg_name => G_PKG_NAME,
846 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
847 x_msg_count => x_msg_count,
848 x_msg_data => x_msg_data,
849 p_api_type => G_API_TYPE);
850
851 when OTHERS then
852 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
853 p_api_name => l_api_name,
854 p_pkg_name => G_PKG_NAME,
855 p_exc_name => 'OTHERS',
856 x_msg_count => x_msg_count,
857 x_msg_data => x_msg_data,
858 p_api_type => G_API_TYPE);
859
860 END process_hdr_rule_group_rules;
861 PROCEDURE process_line_rule_group_rules(
862 p_api_version IN NUMBER,
863 p_init_msg_list IN VARCHAR2,
864 x_return_status OUT NOCOPY VARCHAR2,
865 x_msg_count OUT NOCOPY NUMBER,
866 x_msg_data OUT NOCOPY VARCHAR2,
867 p_chr_id IN NUMBER,
868 p_line_id IN NUMBER,
869 p_rgr_rec IN rgr_rec_type) IS
870 lx_rgr_rec rgr_rec_type := p_rgr_rec;
871 rule_group_id NUMBER := null;
872 l_return_status VARCHAR2(1);
873 BEGIN
874 if(lx_rgr_rec.rule_id is null or lx_rgr_rec.rule_id = OKC_API.G_MISS_NUM) then
875 rule_group_id := get_line_rule_group_id(
876 p_api_version => p_api_version,
877 p_init_msg_list => p_init_msg_list,
878 x_return_status => x_return_status,
879 x_msg_count => x_msg_count,
880 x_msg_data => x_msg_data,
881 p_chr_id => p_chr_id,
882 p_line_id => p_line_id,
883 p_rgr_rec => lx_rgr_rec);
884 create_line_rule_group_rules(
885 p_api_version => p_api_version,
886 p_init_msg_list => p_init_msg_list,
887 x_return_status => x_return_status,
888 x_msg_count => x_msg_count,
889 x_msg_data => x_msg_data,
890 p_chr_id => p_chr_id,
891 p_line_id => p_line_id,
892 p_rule_group_id => rule_group_id,
893 px_rgr_rec => lx_rgr_rec);
894
895 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
896 RAISE OKC_API.G_EXCEPTION_ERROR;
897 END IF;
898
899 end if;
900
901 --Bug#4658944 ramurt
902 IF (lx_rgr_rec.rgd_code = 'LAASTX' AND lx_rgr_rec.RULE_INFORMATION_CATEGORY = 'LAASTX') THEN
903 OKL_LA_SALES_TAX_PVT.check_sales_tax_asset_rules(
904 p_api_version => p_api_version,
905 p_init_msg_list => p_init_msg_list,
906 x_return_status => l_return_status,
907 x_msg_count => x_msg_count,
908 x_msg_data => x_msg_data,
909 p_chr_id => p_chr_id,
910 p_line_id => p_line_id,
911 p_rule_group_id => rule_group_id,
912 p_rgr_rec => lx_rgr_rec);
913 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
914 x_return_status := l_return_status;
915 RAISE OKC_API.G_EXCEPTION_ERROR;
916 END IF;
917 END IF;
918
919 update_rule_rec(
920 p_api_version => p_api_version,
921 p_init_msg_list => p_init_msg_list,
922 x_return_status => x_return_status,
923 x_msg_count => x_msg_count,
924 x_msg_data => x_msg_data,
925 p_rgr_rec => lx_rgr_rec);
926
927 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
928 RAISE OKC_API.G_EXCEPTION_ERROR;
929 END IF;
930 EXCEPTION
931 when OKC_API.G_EXCEPTION_ERROR then
932 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
933 p_api_name => l_api_name,
934 p_pkg_name => G_PKG_NAME,
935 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
936 x_msg_count => x_msg_count,
937 x_msg_data => x_msg_data,
938 p_api_type => G_API_TYPE);
939
940 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
941 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
942 p_api_name => l_api_name,
943 p_pkg_name => G_PKG_NAME,
944 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
945 x_msg_count => x_msg_count,
946 x_msg_data => x_msg_data,
947 p_api_type => G_API_TYPE);
948
949 when OTHERS then
950 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
951 p_api_name => l_api_name,
952 p_pkg_name => G_PKG_NAME,
953 p_exc_name => 'OTHERS',
954 x_msg_count => x_msg_count,
955 x_msg_data => x_msg_data,
956 p_api_type => G_API_TYPE);
957
958 END process_line_rule_group_rules;
959
960 FUNCTION get_rg_party_roles_id(
961 p_api_version IN NUMBER,
962 p_init_msg_list IN VARCHAR2,
963 x_return_status OUT NOCOPY VARCHAR2,
964 x_msg_count OUT NOCOPY NUMBER,
965 x_msg_data OUT NOCOPY VARCHAR2,
966 p_chr_id IN NUMBER,
967 p_line_id IN NUMBER,
968 p_rgp_id IN NUMBER,
969 p_cpl_id IN NUMBER,
970 p_rrd_id IN NUMBER)
971 RETURN OKC_RG_PARTY_ROLES.ID%TYPE IS
972 -- rule_group_id NUMBER := null;
973 rmp_id NUMBER := null;
974 -- l_rgr_rec rgr_rec_type := p_rgr_rec;
975 cursor RMP_CSR( P_CHR_ID IN NUMBER,P_RGP_ID IN NUMBER, P_CPL_ID IN NUMBER, P_RRD_ID IN NUMBER) is
976 SELECT ID FROM OKC_RG_PARTY_ROLES
977 WHERE DNZ_CHR_ID = P_CHR_ID AND
978 RGP_ID = P_RGP_ID AND
979 CPL_ID = P_CPL_ID AND
980 RRD_ID = P_RRD_ID;
981 BEGIN
982 open RMP_CSR(p_chr_id,p_rgp_id,p_cpl_id,p_rrd_id);
983 fetch RMP_CSR into rmp_id;
984 if(RMP_CSR%NOTFOUND) then
985 close RMP_CSR;
986 -- x_return_status := OKC_API.G_RET_STS_ERROR;
987 x_return_status := OKC_API.G_RET_STS_SUCCESS;
988 -- halt further validation of this column
989 else
990 x_return_status := OKC_API.G_RET_STS_SUCCESS;
991 end if;
992 return rmp_id;
993 END get_rg_party_roles_id;
994 FUNCTION get_rg_party_roles_rgp_id(
995 p_api_version IN NUMBER,
996 p_init_msg_list IN VARCHAR2,
997 x_return_status OUT NOCOPY VARCHAR2,
998 x_msg_count OUT NOCOPY NUMBER,
999 x_msg_data OUT NOCOPY VARCHAR2,
1000 p_chr_id IN NUMBER,
1001 p_line_id IN NUMBER,
1002 p_cpl_id IN NUMBER,
1003 p_rrd_id IN NUMBER)
1004 RETURN OKC_RULE_GROUPS_V.ID%TYPE IS
1005 -- rule_group_id NUMBER := null;
1006 rgp_id NUMBER := null;
1007 -- l_rgr_rec rgr_rec_type := p_rgr_rec;
1008 cursor RMP_CSR( P_CHR_ID IN NUMBER, P_CPL_ID IN NUMBER, P_RRD_ID IN NUMBER) is
1009 SELECT RGP_ID FROM OKC_RG_PARTY_ROLES
1010 WHERE DNZ_CHR_ID = P_CHR_ID AND
1011 CPL_ID = P_CPL_ID AND
1012 RRD_ID = P_RRD_ID;
1013 BEGIN
1014 open RMP_CSR(p_chr_id,p_cpl_id,p_rrd_id);
1015 fetch RMP_CSR into rgp_id;
1016 if(RMP_CSR%NOTFOUND) then
1017 close RMP_CSR;
1018 -- x_return_status := OKC_API.G_RET_STS_ERROR;
1019 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1020 -- halt further validation of this column
1021 else
1022 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1023 end if;
1024 return rgp_id;
1025 END get_rg_party_roles_rgp_id;
1026 PROCEDURE process_party_rule_group_rules(
1027 p_api_version IN NUMBER,
1028 p_init_msg_list IN VARCHAR2,
1029 x_return_status OUT NOCOPY VARCHAR2,
1030 x_msg_count OUT NOCOPY NUMBER,
1031 x_msg_data OUT NOCOPY VARCHAR2,
1032 p_chr_id IN NUMBER,
1033 p_line_id IN NUMBER,
1034 p_cpl_id IN NUMBER,
1035 p_rrd_id IN NUMBER,
1036 p_rgr_rec IN rgr_rec_type) IS
1037 lx_rgr_rec rgr_rec_type := p_rgr_rec;
1038 l_rmpv_rec rmpv_rec_type;
1039 lx_rmpv_rec rmpv_rec_type;
1040 rmp_id OKC_RG_PARTY_ROLES.ID%TYPE := null;
1041 rule_group_id NUMBER := null;
1042 BEGIN
1043 rule_group_id := get_rg_party_roles_rgp_id(
1044 p_api_version => p_api_version,
1045 p_init_msg_list => p_init_msg_list,
1046 x_return_status => x_return_status,
1047 x_msg_count => x_msg_count,
1048 x_msg_data => x_msg_data,
1049 p_chr_id => p_chr_id,
1050 p_line_id => p_line_id,
1051 p_cpl_id => p_cpl_id,
1052 p_rrd_id => p_rrd_id);
1053 if(rule_group_id is null or rule_group_id = OKC_API.G_MISS_NUM) then
1054 create_hdr_rule_group_rules(
1055 p_api_version => p_api_version,
1056 p_init_msg_list => p_init_msg_list,
1057 x_return_status => x_return_status,
1058 x_msg_count => x_msg_count,
1059 x_msg_data => x_msg_data,
1060 p_chr_id => p_chr_id,
1061 p_rule_group_id => null,
1062 px_rgr_rec => lx_rgr_rec);
1063
1064 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1065 RAISE OKC_API.G_EXCEPTION_ERROR;
1066 END IF;
1067
1068 l_rmpv_rec.rgp_id := lx_rgr_rec.rgp_id;
1069 l_rmpv_rec.rrd_id := p_rrd_id;
1070 l_rmpv_rec.cpl_id := p_cpl_id;
1071 l_rmpv_rec.dnz_chr_id := p_chr_id;
1072 OKL_RULE_PUB.create_rg_mode_pty_role(
1073 p_api_version => p_api_version,
1074 p_init_msg_list => p_init_msg_list,
1075 x_return_status => x_return_status,
1076 x_msg_count => x_msg_count,
1077 x_msg_data => x_msg_data,
1078 p_rmpv_rec => l_rmpv_rec,
1079 x_rmpv_rec => lx_rmpv_rec);
1080
1081 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1082 RAISE OKC_API.G_EXCEPTION_ERROR;
1083 END IF;
1084
1085
1086 elsif(lx_rgr_rec.rule_id is null or lx_rgr_rec.rule_id = OKC_API.G_MISS_NUM) then
1087 rule_group_id := get_rg_party_roles_rgp_id(
1088 p_api_version => p_api_version,
1089 p_init_msg_list => p_init_msg_list,
1090 x_return_status => x_return_status,
1091 x_msg_count => x_msg_count,
1092 x_msg_data => x_msg_data,
1093 p_chr_id => p_chr_id,
1094 p_line_id => p_line_id,
1095 p_cpl_id => p_cpl_id,
1096 p_rrd_id => p_rrd_id);
1097 create_hdr_rule_group_rules(
1098 p_api_version => p_api_version,
1099 p_init_msg_list => p_init_msg_list,
1100 x_return_status => x_return_status,
1101 x_msg_count => x_msg_count,
1102 x_msg_data => x_msg_data,
1103 p_chr_id => p_chr_id,
1104 p_rule_group_id => rule_group_id,
1105 px_rgr_rec => lx_rgr_rec);
1106 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1107 RAISE OKC_API.G_EXCEPTION_ERROR;
1108 END IF;
1109 end if;
1110 update_rule_rec(
1111 p_api_version => p_api_version,
1112 p_init_msg_list => p_init_msg_list,
1113 x_return_status => x_return_status,
1114 x_msg_count => x_msg_count,
1115 x_msg_data => x_msg_data,
1116 p_rgr_rec => lx_rgr_rec);
1117
1118 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1119 RAISE OKC_API.G_EXCEPTION_ERROR;
1120 END IF;
1121 EXCEPTION
1122 when OKC_API.G_EXCEPTION_ERROR then
1123 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1124 p_api_name => l_api_name,
1125 p_pkg_name => G_PKG_NAME,
1126 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
1127 x_msg_count => x_msg_count,
1128 x_msg_data => x_msg_data,
1129 p_api_type => G_API_TYPE);
1130
1131 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
1132 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1133 p_api_name => l_api_name,
1134 p_pkg_name => G_PKG_NAME,
1135 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
1136 x_msg_count => x_msg_count,
1137 x_msg_data => x_msg_data,
1138 p_api_type => G_API_TYPE);
1139
1140 when OTHERS then
1141 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1142 p_api_name => l_api_name,
1143 p_pkg_name => G_PKG_NAME,
1144 p_exc_name => 'OTHERS',
1145 x_msg_count => x_msg_count,
1146 x_msg_data => x_msg_data,
1147 p_api_type => G_API_TYPE);
1148
1149 END process_party_rule_group_rules;
1150
1151 PROCEDURE process_template_rule(
1152 p_api_version IN NUMBER,
1153 p_init_msg_list IN VARCHAR2,
1154 x_return_status OUT NOCOPY VARCHAR2,
1155 x_msg_count OUT NOCOPY NUMBER,
1156 x_msg_data OUT NOCOPY VARCHAR2,
1157 p_rgr_rec IN rgr_rec_type,
1158 px_rgr_rec OUT NOCOPY rgr_out_rec_type) IS
1159 l_rgr_rec rgr_rec_type := p_rgr_rec;
1160 l_rulv_rec rulv_rec_type := null;
1161 lx_rulv_rec rulv_rec_type := null;
1162 BEGIN
1163 if(l_rgr_rec.rule_id is null or l_rgr_rec.rule_id = OKC_API.G_MISS_NUM or l_rgr_rec.rule_id = '') then
1164 migrate_rec(p_rgr_rec => p_rgr_rec,
1165 x_rulv_rec => l_rulv_rec);
1166 l_rulv_rec.dnz_chr_id := null;
1167 l_rulv_rec.rgp_id := null;
1168 l_rulv_rec.std_template_yn := 'Y';
1169 l_rulv_rec.template_yn := 'Y';
1170 OKL_RULE_PUB.create_rule(
1171 p_api_version => p_api_version,
1172 p_init_msg_list => p_init_msg_list,
1173 x_return_status => x_return_status,
1174 x_msg_count => x_msg_count,
1175 x_msg_data => x_msg_data,
1176 p_rulv_rec => l_rulv_rec,
1177 x_rulv_rec => lx_rulv_rec);
1178
1179 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1180 RAISE OKC_API.G_EXCEPTION_ERROR;
1181 END IF;
1182
1183 px_rgr_rec.new_yn := 'Y';
1184 else
1185 --l_rgr_rec.dnz_chr_id := null;
1186 --l_rgr_rec.rgp_id := null;
1187 l_rgr_rec.template_yn := 'Y';
1188 l_rgr_rec.std_template_yn := 'Y';
1189 migrate_rec(p_rgr_rec => p_rgr_rec,
1190 x_rulv_rec => l_rulv_rec);
1191 OKL_RULE_PUB.update_rule(
1192 p_api_version => p_api_version,
1193 p_init_msg_list => p_init_msg_list,
1194 x_return_status => x_return_status,
1195 x_msg_count => x_msg_count,
1196 x_msg_data => x_msg_data,
1197 p_rulv_rec => l_rulv_rec,
1198 x_rulv_rec => lx_rulv_rec);
1199
1200 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1201 RAISE OKC_API.G_EXCEPTION_ERROR;
1202 END IF;
1203
1204 px_rgr_rec.new_yn := 'N';
1205 end if;
1206 px_rgr_rec.id := lx_rulv_rec.id;
1207 px_rgr_rec.rule_code := lx_rulv_rec.rule_information_category;
1208 px_rgr_rec.rgd_code := p_rgr_rec.rgd_code;
1209
1210
1211 EXCEPTION
1212 when OKC_API.G_EXCEPTION_ERROR then
1213 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1214 p_api_name => l_api_name,
1215 p_pkg_name => G_PKG_NAME,
1216 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
1217 x_msg_count => x_msg_count,
1218 x_msg_data => x_msg_data,
1219 p_api_type => G_API_TYPE);
1220
1221 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
1222 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1223 p_api_name => l_api_name,
1224 p_pkg_name => G_PKG_NAME,
1225 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
1226 x_msg_count => x_msg_count,
1227 x_msg_data => x_msg_data,
1228 p_api_type => G_API_TYPE);
1229
1230 when OTHERS then
1231 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1232 p_api_name => l_api_name,
1233 p_pkg_name => G_PKG_NAME,
1234 p_exc_name => 'OTHERS',
1235 x_msg_count => x_msg_count,
1236 x_msg_data => x_msg_data,
1237 p_api_type => G_API_TYPE);
1238
1239
1240 END process_template_rule;
1241
1242 PROCEDURE process_rule_group_rules(
1243 p_api_version IN NUMBER,
1244 p_init_msg_list IN VARCHAR2,
1245 x_return_status OUT NOCOPY VARCHAR2,
1246 x_msg_count OUT NOCOPY NUMBER,
1247 x_msg_data OUT NOCOPY VARCHAR2,
1248 p_chr_id IN NUMBER,
1249 p_line_id IN NUMBER,
1250 p_cpl_id IN NUMBER,
1251 p_rrd_id IN NUMBER,
1252 p_rgr_tbl IN rgr_tbl_type) IS
1253 l_rgr_tbl rgr_tbl_type := p_rgr_tbl;
1254 i NUMBER := 0;
1255 process_type VARCHAR2(10) := null;
1256 l_chr_id NUMBER := p_chr_id;
1257 l_line_id NUMBER := p_line_id;
1258 l_cpl_id NUMBER := p_cpl_id;
1259 l_rrd_id NUMBER := p_rrd_id;
1260
1261 --l_api_name VARCHAR2(35) := 'RGRP_RULES_PROCESS';
1262 l_proc_name VARCHAR2(35) := 'PROCESS_RULE_GROUP_RULES';
1263 l_api_version CONSTANT VARCHAR2(30) := p_api_version;
1264
1265 x_rulv_tbl APPS.OKL_RULE_PUB.RULV_TBL_TYPE;
1266
1267 -- sjalasut: aug 30, 04 added variables to support business events. BEGIN
1268
1269 l_raise_business_event VARCHAR2(1);
1270 l_business_event_name wf_events.name%TYPE;
1271 l_terms_id okc_rule_groups_b.id%TYPE;
1272 l_parameter_list WF_PARAMETER_LIST_T;
1273 -- cursor to get the rule group id from the header.
1274 -- if the Lien Terms and Conditions are updated at the contract level, then the cle_id is null
1275 CURSOR get_header_terms_id (p_chr_id okc_k_headers_b.id%TYPE, p_rgd_code VARCHAR2) IS
1276 SELECT id
1277 FROM okc_rule_groups_b
1278 WHERE dnz_chr_id = p_chr_id AND
1279 rgd_code = p_rgd_code AND
1280 cle_id IS NULL ;
1281
1282 CURSOR get_line_style (p_line_id okc_k_lines_b.id%TYPE) IS
1283 SELECT lty_code
1284 FROM okc_k_lines_b line,
1285 okc_line_styles_b style
1286 WHERE line.lse_id = style.id
1287 AND line.id = p_line_id;
1288
1289 l_line_style okc_line_styles_b.lty_code%TYPE;
1290
1291 CURSOR get_serv_chr_from_serv(p_chr_id okc_k_headers_b.id%TYPE,
1292 p_line_id okc_k_lines_b.id%TYPE) IS
1293 SELECT rlobj.object1_id1
1294 FROM okc_k_rel_objs_v rlobj
1295 WHERE rlobj.chr_id = p_chr_id
1296 AND rlobj.cle_id = p_line_id
1297 AND rlobj.rty_code = 'OKLSRV'
1298 AND rlobj.jtot_object1_code = 'OKL_SERVICE_LINE';
1299
1300 l_service_top_line_id okc_k_lines_b.id%TYPE;
1301
1302 CURSOR get_serv_cle_from_serv (p_serv_top_line_id okc_k_lines_b.id%TYPE) IS
1303 SELECT dnz_chr_id
1304 FROM okc_k_lines_b
1305 WHERE id = p_serv_top_line_id;
1306
1307 l_serv_contract_id okc_k_headers_b.id%TYPE;
1308
1309 -- sjalasut: aug 30, 04 added variables to support business events. END
1310
1311 --sechawla 4-jun-09 6826580
1312 cursor l_inv_frmt_csr(cp_inv_frmt_code in varchar2) IS
1313 select id
1314 from okl_invoice_formats_v
1315 where name = cp_inv_frmt_code;
1316 l_inv_frmt_id number;
1317
1318 --Bug# 8652738
1319 CURSOR l_chk_rbk_csr(p_chr_id IN NUMBER) IS
1320 SELECT orig_system_source_code
1321 FROM okc_k_headers_b
1322 WHERE id = p_chr_id;
1323
1324 l_chk_rbk_rec l_chk_rbk_csr%ROWTYPE;
1325
1326 BEGIN
1327 /* sosharma 18-Apr-07 ,
1328 added code to set org context based on the chr_id for VPA, Contracts and IA.
1329 Thsi fix has been provided to support OA pages which are dynamic in nature and AMImpl is not passing the
1330 org context to the API.The fix doesnot have any other impact on functionality.
1331 Start changes
1332 */
1333 if (p_chr_id is not null and p_chr_id <> OKC_API.G_MISS_NUM) then
1334 OKC_CONTEXT.SET_OKC_ORG_CONTEXT(p_chr_id => p_chr_id);
1335 end if;
1336 /* sosharma end changes */
1337
1338 if(l_chr_id is null or l_chr_id = OKC_API.G_MISS_NUM) then
1339 l_chr_id := -1;
1340 end if;
1341 if(l_line_id is null or l_line_id = OKC_API.G_MISS_NUM) then
1342 l_line_id := -1;
1343 end if;
1344 if(l_cpl_id is null or l_cpl_id = OKC_API.G_MISS_NUM) then
1345 l_cpl_id := -1;
1346 end if;
1347 if(l_chr_id = -1 and l_cpl_id = -1 and l_line_id = -1) then
1348 process_type := 'TEMPLATE';
1349 elsif(l_cpl_id = -1 and l_line_id = -1) then
1350 process_type := 'HEADER';
1351 elsif(l_cpl_id = -1 and l_line_id <> -1) then
1352 process_type := 'LINE';
1353 elsif(l_cpl_id <> -1) then
1354 process_type := 'PARTY';
1355 end if;
1356
1357
1358 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1359 -- call START_ACTIVITY to create savepoint, check compatibility
1360 -- and initialize message list
1361 x_return_status := OKC_API.START_ACTIVITY(
1362 p_api_name => l_api_name,
1363 p_pkg_name => G_PKG_NAME,
1364 p_init_msg_list => p_init_msg_list,
1365 l_api_version => l_api_version,
1366 p_api_version => p_api_version,
1367 p_api_type => G_API_TYPE,
1368 x_return_status => x_return_status);
1369
1370 -- check if activity started successfully
1371 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
1372 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1373 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) then
1374 raise OKC_API.G_EXCEPTION_ERROR;
1375 END IF;
1376
1377 --Bug# 4959361
1378 IF (l_rgr_tbl(l_rgr_tbl.FIRST).rgd_code = 'LAPSTH' and process_type = 'LINE') OR
1379 (l_rgr_tbl(l_rgr_tbl.FIRST).rgd_code = 'LAFEXP' and process_type = 'LINE') THEN
1380
1381 OPEN get_line_style(p_line_id);
1382 FETCH get_line_style INTO l_line_style;
1383 CLOSE get_line_style;
1384
1385 IF(l_line_style IS NOT NULL AND l_line_style = 'FEE') THEN
1386 OKL_LLA_UTIL_PVT.check_line_update_allowed
1387 (p_api_version => p_api_version,
1388 p_init_msg_list => p_init_msg_list,
1389 x_return_status => x_return_status,
1390 x_msg_count => x_msg_count,
1391 x_msg_data => x_msg_data,
1392 p_cle_id => p_line_id);
1393
1394 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1395 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1396 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1397 RAISE OKL_API.G_EXCEPTION_ERROR;
1398 END IF;
1399 END IF;
1400 END IF;
1401 --Bug# 4959361
1402
1403 loop
1404 i := i + 1;
1405 if(process_type = 'HEADER') then
1406
1407 okl_am_qa_data_integrity_pvt.check_am_rule_format (
1408 x_return_status => x_return_status,
1409 p_chr_id => l_chr_id,
1410 p_rgr_rec => l_rgr_tbl(i));
1411
1412 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1413 -- RAISE OKC_API.G_EXCEPTION_ERROR;
1414 RAISE G_EXCEPTION_STOP_VALIDATION;
1415 --return;
1416 END IF;
1417 end if;
1418 exit when (i >= l_rgr_tbl.last);
1419 end loop;
1420
1421 i := 0;
1422 loop
1423 i := i + 1;
1424 /*
1425 if(process_type = 'TEMPLATE') then
1426 process_template_rule(
1427 p_api_version => p_api_version,
1428 p_init_msg_list => p_init_msg_list,
1429 x_return_status => x_return_status,
1430 x_msg_count => x_msg_count,
1431 x_msg_data => x_msg_data,
1432 p_rgr_rec => l_rgr_tbl(i));
1433 */
1434 if(process_type = 'HEADER') then
1435
1436 process_hdr_rule_group_rules(
1437 p_api_version => p_api_version,
1438 p_init_msg_list => p_init_msg_list,
1439 x_return_status => x_return_status,
1440 x_msg_count => x_msg_count,
1441 x_msg_data => x_msg_data,
1442 p_chr_id => l_chr_id,
1443 p_rgr_rec => l_rgr_tbl(i));
1444
1445 elsif(process_type = 'LINE') then
1446
1447 --2009-08-25 sechawla Bug 8657968 : begin
1448 open l_inv_frmt_csr(l_rgr_tbl(i).rule_information1);
1449 fetch l_inv_frmt_csr into l_inv_frmt_id;
1450 close l_inv_frmt_csr;
1451 if l_inv_frmt_id is not null then
1452 l_rgr_tbl(i).rule_information1 := to_char(l_inv_frmt_id);
1453 end if;
1454 --2009-08-25 sechawla Bug 8657968 : end
1455 process_line_rule_group_rules(
1456 p_api_version => p_api_version,
1457 p_init_msg_list => p_init_msg_list,
1458 x_return_status => x_return_status,
1459 x_msg_count => x_msg_count,
1460 x_msg_data => x_msg_data,
1461 p_chr_id => l_chr_id,
1462 p_line_id => l_line_id,
1463 p_rgr_rec => l_rgr_tbl(i));
1464
1465 elsif(process_type = 'PARTY') then
1466
1467 --sechawla 4-jun-09 6826580 : begin
1468 open l_inv_frmt_csr(l_rgr_tbl(i).rule_information1);
1469 fetch l_inv_frmt_csr into l_inv_frmt_id;
1470 close l_inv_frmt_csr;
1471 l_rgr_tbl(i).rule_information1 := to_char(l_inv_frmt_id);
1472 --sechawla 4-jun-09 6826580 : end
1473
1474 process_party_rule_group_rules(
1475 p_api_version => p_api_version,
1476 p_init_msg_list => p_init_msg_list,
1477 x_return_status => x_return_status,
1478 x_msg_count => x_msg_count,
1479 x_msg_data => x_msg_data,
1480 p_chr_id => l_chr_id,
1481 p_line_id => l_line_id,
1482 p_cpl_id => l_cpl_id,
1483 p_rrd_id => l_rrd_id,
1484 p_rgr_rec => l_rgr_tbl(i));
1485 end if;
1486
1487 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1488 RAISE OKC_API.G_EXCEPTION_ERROR;
1489 END IF;
1490 exit when (i >= l_rgr_tbl.last);
1491 end loop;
1492 IF (l_rgr_tbl.count > 0) THEN
1493 if(l_rgr_tbl(l_rgr_tbl.FIRST).rgd_code = 'LAIIND') then
1494
1495 OKL_LA_PAYMENTS_PVT.process_payment(
1496 p_api_version => p_api_version,
1497 p_init_msg_list => p_init_msg_list,
1498 x_return_status => x_return_status,
1499 x_msg_count => x_msg_count,
1500 x_msg_data => x_msg_data,
1501 p_chr_id => l_chr_id,
1502 p_service_fee_id => null,
1503 p_asset_id => null,
1504 p_payment_id => null,
1505 p_update_type => 'VIR_PAYMENT',
1506 x_rulv_tbl => x_rulv_tbl);
1507
1508 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
1509 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1510 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) then
1511 raise OKC_API.G_EXCEPTION_ERROR;
1512 END IF;
1513 end if;
1514
1515 --Bug# 8652738
1516 -- Do not sync property tax contract header values to contract lines
1517 -- for a rebook contract, as we do not support update of
1518 -- Property tax rules during rebook
1519
1520 l_chk_rbk_rec := NULL;
1521 OPEN l_chk_rbk_csr(p_chr_id => l_chr_id);
1522 FETCH l_chk_rbk_csr INTO l_chk_rbk_rec;
1523 CLOSE l_chk_rbk_csr;
1524
1525 IF ( NVL(l_chk_rbk_rec.orig_system_source_code, OKL_API.G_MISS_CHAR) <> 'OKL_REBOOK' ) THEN
1526
1527 if(l_rgr_tbl(l_rgr_tbl.FIRST).rgd_code = 'LAHDTX') then
1528 OKL_LA_PROPERTY_TAX_PVT.sync_contract_property_tax(
1529 p_api_version => p_api_version,
1530 p_init_msg_list => p_init_msg_list,
1531 x_return_status => x_return_status,
1532 x_msg_count => x_msg_count,
1533 x_msg_data => x_msg_data,
1534 p_chr_id => l_chr_id);
1535
1536 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
1537 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1538 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) then
1539 raise OKC_API.G_EXCEPTION_ERROR;
1540 END IF;
1541 end if;
1542
1543 END IF;
1544 --Bug# 8652738
1545
1546 --
1547 -- sjalasut. added logic to raise business events when a rule group is created
1548 --
1549 -- initialize the global variables for the business events
1550 l_raise_business_event := OKL_API.G_FALSE;
1551 l_business_event_name := NULL;
1552 l_terms_id := NULL;
1553 IF(l_rgr_tbl(l_rgr_tbl.FIRST).rgd_code = 'LAAFLG' AND process_type = 'HEADER')THEN
1554 -- raise business event for the Liens and Title for Terms and Conditions for the Contract
1555 -- set raise business event flag to true
1556 l_raise_business_event := OKL_API.G_TRUE;
1557 -- set the event name to be raised. this event name will vary for each rule group
1558 l_business_event_name := G_WF_EVT_CONTRACT_TERM_UPDATED;
1559 OPEN get_header_terms_id(p_chr_id, l_rgr_tbl(l_rgr_tbl.FIRST).rgd_code);
1560 FETCH get_header_terms_id INTO l_terms_id;
1561 CLOSE get_header_terms_id;
1562 IF(l_terms_id IS NOT NULL)THEN
1563 wf_event.AddParameterToList(G_WF_ITM_TERMS_ID, l_terms_id, l_parameter_list);
1564 END IF;
1565 ELSIF(l_rgr_tbl(l_rgr_tbl.FIRST).rgd_code = 'LAAFLG' AND process_type = 'LINE')THEN
1566 -- raise business event for Liens Title and Registration for the Assets
1567 -- set raise business event flag to true
1568 l_raise_business_event := OKL_API.G_TRUE;
1569 -- set the event name to be raised. this event name will vary for each rule group
1570 l_business_event_name := G_WF_EVT_ASSET_FILING_UPDATED;
1571 wf_event.AddParameterToList(G_WF_ITM_ASSET_ID, p_line_id, l_parameter_list);
1572 ELSIF(l_rgr_tbl(l_rgr_tbl.FIRST).rgd_code = 'LAASTX' AND process_type = 'LINE')THEN
1573 -- raise business event for tax, property tax updated.
1574 l_raise_business_event := OKL_API.G_TRUE;
1575 l_business_event_name := G_WF_EVT_ASSET_PROPTAX_UPDATED;
1576 wf_event.AddParameterToList(G_WF_ITM_ASSET_ID, p_line_id, l_parameter_list);
1577 ELSIF(l_rgr_tbl(l_rgr_tbl.FIRST).rgd_code = 'LAPSTH' and process_type = 'LINE')THEN
1578 OPEN get_line_style(p_line_id);
1579 FETCH get_line_style INTO l_line_style;
1580 CLOSE get_line_style;
1581 -- raise business event for service line update passthru
1582 IF(l_line_style IS NOT NULL AND l_line_style = 'SOLD_SERVICE')THEN
1583 l_raise_business_event := OKL_API.G_TRUE;
1584 l_business_event_name := G_WF_EVT_SERV_PASS_UPDATED;
1585 wf_event.AddParameterToList(G_WF_ITM_SERV_LINE_ID, p_line_id, l_parameter_list);
1586 -- check if the service line in context has a service contract associated with it
1587 -- if so, pass the service contract id and service contract line id as parameters
1588 OPEN get_serv_chr_from_serv(p_chr_id, p_line_id);
1589 FETCH get_serv_chr_from_serv INTO l_service_top_line_id;
1590 CLOSE get_serv_chr_from_serv;
1591 IF(l_service_top_line_id IS NOT NULL)THEN
1592 OPEN get_serv_cle_from_serv(l_service_top_line_id);
1593 FETCH get_serv_cle_from_serv INTO l_serv_contract_id;
1594 CLOSE get_serv_cle_from_serv;
1595 wf_event.AddParameterToList(G_WF_ITM_SERV_CHR_ID, l_serv_contract_id, l_parameter_list);
1596 wf_event.AddParameterToList(G_WF_ITM_SERV_CLE_ID, l_service_top_line_id, l_parameter_list);
1597 END IF;
1598 -- raise the business event for update passthrough for Fee Line
1599 ELSIF(l_line_style IS NOT NULL AND l_line_style = 'FEE')THEN
1600 l_raise_business_event := OKL_API.G_TRUE;
1601 l_business_event_name := G_WF_EVT_FEE_PASS_UPDATED;
1602 wf_event.AddParameterToList(G_WF_ITM_FEE_LINE_ID, p_line_id, l_parameter_list);
1603 END IF;
1604 ELSIF(l_rgr_tbl(l_rgr_tbl.FIRST).rgd_code = 'LAFEXP' and process_type = 'LINE')THEN
1605 OPEN get_line_style(p_line_id);
1606 FETCH get_line_style INTO l_line_style;
1607 CLOSE get_line_style;
1608 -- raise business event for service line update expense
1609 IF(l_line_style IS NOT NULL AND l_line_style = 'SOLD_SERVICE')THEN
1610 l_raise_business_event := OKL_API.G_TRUE;
1611 l_business_event_name := G_WF_EVT_SERV_FEXP_UPDATED;
1612 wf_event.AddParameterToList(G_WF_ITM_SERV_LINE_ID, p_line_id, l_parameter_list);
1613 -- check if the service line in context has a service contract associated with it
1614 -- if so, pass the service contract id and service contract line id as parameters
1615 OPEN get_serv_chr_from_serv(p_chr_id, p_line_id);
1616 FETCH get_serv_chr_from_serv INTO l_service_top_line_id;
1617 CLOSE get_serv_chr_from_serv;
1618 IF(l_service_top_line_id IS NOT NULL)THEN
1619 OPEN get_serv_cle_from_serv(l_service_top_line_id);
1620 FETCH get_serv_cle_from_serv INTO l_serv_contract_id;
1621 CLOSE get_serv_cle_from_serv;
1622 wf_event.AddParameterToList(G_WF_ITM_SERV_CHR_ID, l_serv_contract_id, l_parameter_list);
1623 wf_event.AddParameterToList(G_WF_ITM_SERV_CLE_ID, l_service_top_line_id, l_parameter_list);
1624 END IF;
1625 ELSIF(l_line_style IS NOT NULL AND l_line_style = 'FEE')THEN
1626 l_raise_business_event := OKL_API.G_TRUE;
1627 l_business_event_name := G_WF_EVT_FEE_EXP_UPDATED;
1628 wf_event.AddParameterToList(G_WF_ITM_FEE_LINE_ID, p_line_id, l_parameter_list);
1629 END IF;
1630 END IF;
1631 END IF;
1632
1633 -- check if the business event needs to be raised
1634 IF(l_raise_business_event = OKL_API.G_TRUE AND l_business_event_name IS NOT NULL AND
1635 OKL_LLA_UTIL_PVT.is_lease_contract(p_chr_id)= OKL_API.G_TRUE)THEN
1636 -- since contract id is called as 'CONTRACT_ID' for all the above events, it is being
1637 -- added to the parameter list here, than duplicating it in all the above if conditions
1638
1639 wf_event.AddParameterToList(G_WF_ITM_CONTRACT_ID, p_chr_id, l_parameter_list);
1640 raise_business_event(p_api_version => p_api_version,
1641 p_init_msg_list => p_init_msg_list,
1642 x_return_status => x_return_status,
1643 x_msg_count => x_msg_count,
1644 x_msg_data => x_msg_data,
1645 p_event_name => l_business_event_name,
1646 p_event_param_list => l_parameter_list
1647 );
1648 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1649 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1650 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1651 RAISE OKL_API.G_EXCEPTION_ERROR;
1652 END IF;
1653 END IF;
1654
1655 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
1656 x_msg_data => x_msg_data);
1657
1658
1659 EXCEPTION
1660 when OKC_API.G_EXCEPTION_ERROR then
1661
1662 --sechawla 4-jun-09 6826580 : begin
1663 IF l_inv_frmt_csr%ISOPEN THEN
1664 CLOSE l_inv_frmt_csr;
1665 END IF;
1666
1667 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1668 p_api_name => l_api_name,
1669 p_pkg_name => G_PKG_NAME,
1670 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
1671 x_msg_count => x_msg_count,
1672 x_msg_data => x_msg_data,
1673 p_api_type => G_API_TYPE);
1674
1675 when G_EXCEPTION_STOP_VALIDATION then
1676 --sechawla 4-jun-09 6826580 : begin
1677 IF l_inv_frmt_csr%ISOPEN THEN
1678 CLOSE l_inv_frmt_csr;
1679 END IF;
1680 null;
1681
1682 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
1683
1684 --sechawla 4-jun-09 6826580 : begin
1685 IF l_inv_frmt_csr%ISOPEN THEN
1686 CLOSE l_inv_frmt_csr;
1687 END IF;
1688
1689 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1690 p_api_name => l_api_name,
1691 p_pkg_name => G_PKG_NAME,
1692 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
1693 x_msg_count => x_msg_count,
1694 x_msg_data => x_msg_data,
1695 p_api_type => G_API_TYPE);
1696
1697 when OTHERS then
1698
1699 --sechawla 4-jun-09 6826580 : begin
1700 IF l_inv_frmt_csr%ISOPEN THEN
1701 CLOSE l_inv_frmt_csr;
1702 END IF;
1703
1704 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1705 p_api_name => l_api_name,
1706 p_pkg_name => G_PKG_NAME,
1707 p_exc_name => 'OTHERS',
1708 x_msg_count => x_msg_count,
1709 x_msg_data => x_msg_data,
1710 p_api_type => G_API_TYPE);
1711 end process_rule_group_rules;
1712
1713 PROCEDURE process_template_rules(
1714 p_api_version IN NUMBER,
1715 p_init_msg_list IN VARCHAR2,
1716 x_return_status OUT NOCOPY VARCHAR2,
1717 x_msg_count OUT NOCOPY NUMBER,
1718 x_msg_data OUT NOCOPY VARCHAR2,
1719 p_id IN NUMBER,
1720 p_rgr_tbl IN rgr_tbl_type,
1721 x_rgr_tbl OUT NOCOPY rgr_out_tbl_type) IS
1722 l_rgr_tbl rgr_tbl_type := p_rgr_tbl;
1723 lx_rgr_tbl rgr_out_tbl_type := x_rgr_tbl;
1724 i NUMBER := 0;
1725 l_id NUMBER := p_id;
1726
1727 --l_api_name VARCHAR2(35) := 'RGRP_RULES_PROCESS';
1728 l_proc_name VARCHAR2(35) := 'PROCESS_TEMPLATE_RULES';
1729 l_api_version CONSTANT VARCHAR2(30) := p_api_version;
1730
1731 BEGIN
1732
1733 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1734 -- call START_ACTIVITY to create savepoint, check compatibility
1735 -- and initialize message list
1736 x_return_status := OKC_API.START_ACTIVITY(
1737 p_api_name => l_api_name,
1738 p_pkg_name => G_PKG_NAME,
1739 p_init_msg_list => p_init_msg_list,
1740 l_api_version => l_api_version,
1741 p_api_version => p_api_version,
1742 p_api_type => G_API_TYPE,
1743 x_return_status => x_return_status);
1744
1745 -- check if activity started successfully
1746 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
1747 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1748 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) then
1749 raise OKC_API.G_EXCEPTION_ERROR;
1750 END IF;
1751
1752 loop
1753 i := i + 1;
1754 process_template_rule(
1755 p_api_version => p_api_version,
1756 p_init_msg_list => p_init_msg_list,
1757 x_return_status => x_return_status,
1758 x_msg_count => x_msg_count,
1759 x_msg_data => x_msg_data,
1760 p_rgr_rec => l_rgr_tbl(i),
1761 px_rgr_rec => lx_rgr_tbl(i));
1762
1763 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1764 RAISE OKC_API.G_EXCEPTION_ERROR;
1765 END IF;
1766
1767 exit when (i >= l_rgr_tbl.last);
1768 end loop;
1769 x_rgr_tbl := lx_rgr_tbl;
1770
1771
1772 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
1773 x_msg_data => x_msg_data);
1774
1775 EXCEPTION
1776 when OKC_API.G_EXCEPTION_ERROR then
1777 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1778 p_api_name => l_api_name,
1779 p_pkg_name => G_PKG_NAME,
1780 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
1781 x_msg_count => x_msg_count,
1782 x_msg_data => x_msg_data,
1783 p_api_type => G_API_TYPE);
1784
1785 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
1786 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1787 p_api_name => l_api_name,
1788 p_pkg_name => G_PKG_NAME,
1789 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
1790 x_msg_count => x_msg_count,
1791 x_msg_data => x_msg_data,
1792 p_api_type => G_API_TYPE);
1793
1794 when OTHERS then
1795 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1796 p_api_name => l_api_name,
1797 p_pkg_name => G_PKG_NAME,
1798 p_exc_name => 'OTHERS',
1799 x_msg_count => x_msg_count,
1800 x_msg_data => x_msg_data,
1801 p_api_type => G_API_TYPE);
1802
1803 end process_template_rules;
1804
1805 END OKL_RGRP_RULES_PROCESS_PVT;