1 PACKAGE BODY OKL_PA_DATA_INTEGRITY AS
2 /* $Header: OKLRPAQB.pls 120.11 2007/11/21 08:38:54 rgooty noship $ */
3
4 G_TOT_RESIDU_INC_MSG CONSTANT fnd_new_messages.message_name%TYPE DEFAULT 'OKL_VN_INCORRECT_RESIDUAL';
5 G_RESIDU_NOT_POS_MSG CONSTANT fnd_new_messages.message_name%TYPE DEFAULT 'OKL_VN_INCORRECT_PERCENT';
6
7 -- this function ensures that the sum of residual share percent on the program agreement is 100%
8 -- and is distributed among the parties of the PA
9 FUNCTION validate_total_residual(p_chr_id okc_k_headers_b.id%TYPE) RETURN VARCHAR2 IS
10 -- rule group VGLRS, rule information categor VGLRSP
11 CURSOR c_get_total_share(cp_chr_id okc_k_headers_b.id%TYPE)IS
12 SELECT NVL(SUM(to_number(rules.rule_information2)),0) total_share
13 FROM okc_rules_b rules
14 ,okc_rule_groups_b rule_group
15 WHERE rule_group.id = rules.rgp_id
16 AND rules.rule_information_category = 'VGLRSP'
17 AND rule_group.rgd_code = 'VGLRS'
18 AND rule_group.dnz_chr_id = cp_chr_id;
19 lv_total_share NUMBER;
20 lv_return_status VARCHAR2(1);
21 BEGIN
22 lv_return_status := 'S';
23 OPEN c_get_total_share(cp_chr_id => p_chr_id); FETCH c_get_total_share INTO lv_total_share;
24 CLOSE c_get_total_share;
25 -- the total residual share cannot be more than 100 or less than 100. it has to be always sum up to 100
26 IF(lv_total_share > 100 OR lv_total_share < 100)THEN
27 lv_return_status := 'E';
28 okl_api.set_message(G_APP_NAME, G_TOT_RESIDU_INC_MSG);
29 END IF;
30 RETURN lv_return_status;
31 EXCEPTION WHEN OTHERS THEN
32 lv_return_status := 'E';
33 okl_api.set_message(G_APP_NAME, G_TOT_RESIDU_INC_MSG);
34 RETURN lv_return_status;
35 END validate_total_residual;
36
37 -- this function ensures that each residual share percent defined against the party in the Terms and Conditions page is a positive number
38 FUNCTION validate_residual_positive(p_chr_id okc_k_headers_b.id%TYPE) RETURN VARCHAR2 IS
39 CURSOR c_get_residual_share(cp_chr_id okc_k_headers_b.id%TYPE)IS
40 SELECT rules.rule_information2 share_percent
41 FROM okc_rules_b rules
42 ,okc_rule_groups_b rule_group
43 WHERE rule_group.id = rules.rgp_id
44 AND rules.rule_information_category = 'VGLRSP'
45 AND rule_group.rgd_code = 'VGLRS'
46 AND rule_group.dnz_chr_id = cp_chr_id;
47 lv_return_status VARCHAR2(1);
48 BEGIN
49 lv_return_status := 'S';
50 FOR each_row IN c_get_residual_share(cp_chr_id => p_chr_id) LOOP
51 IF(to_number(each_row.share_percent) < 0)THEN
52 lv_return_status := 'E';
53 okl_api.set_message(G_APP_NAME, G_RESIDU_NOT_POS_MSG);
54 END IF;
55 END LOOP;
56 RETURN lv_return_status;
57 EXCEPTION WHEN OTHERS THEN
58 lv_return_status := 'E';
59 okl_api.set_message(G_APP_NAME, G_RESIDU_NOT_POS_MSG);
60 RETURN lv_return_status;
61 END validate_residual_positive;
62
63 -- this function ensures that the pary information on the residual share terms and conditions page are valid parties on the program agreement
64 FUNCTION validate_residual_parties(p_chr_id okc_k_headers_b.id%TYPE) RETURN VARCHAR2 IS
65 lv_return_status VARCHAR2(1);
66 BEGIN
67 lv_return_status := 'S';
68 RETURN lv_return_status;
69 END validate_residual_parties;
70
71 -- This function validates, the effective dates of all the associated objects of the same type
72 -- to an agreement should not overlap.
73 FUNCTION validate_date_overlap(p_id IN NUMBER, p_chr_id IN NUMBER, p_assoc_obj_id IN NUMBER, p_start_date IN DATE, p_end_date IN DATE)
74 RETURN VARCHAR2 IS
75
76 -- Cursor to fetch the effective dates of all the associated objects of same type
77 -- keeping one object as the reference.
78 CURSOR get_assoc_rec(cp_id okl_vp_associations.id%TYPE)
79 IS
80 SELECT start_date,
81 end_date
82 ,chr_id
83 ,crs_id
84 ,assoc_object_id
85 ,assoc_object_version
86 ,assoc_object_type_code
87 FROM OKL_VP_ASSOCIATIONS
88 WHERE id = cp_id;
89 cv_get_assoc_rec get_assoc_rec%ROWTYPE;
90
91 -- find out all objects with similar id (and optional version) which on the same association and with overlap dates
92 -- assoc_object_type would not make a difference here
93 CURSOR c_get_dup_obj_assoc (cp_object_id okl_vp_associations.assoc_object_id%TYPE
94 ,cp_crs_id okl_vp_change_requests.id%TYPE
95 ,cp_start_date okl_vp_associations.start_date%TYPE
96 ,cp_end_date okl_vp_associations.end_date%TYPE
97 ,cp_object_version okl_vp_associations.assoc_object_version%TYPE
98 ,cp_id okl_vp_associations.id%TYPE
99 )IS
100 SELECT 'X'
101 FROM okl_vp_associations
102 WHERE crs_id = cp_crs_id
103 AND chr_id IS NULL
104 AND id <> cp_id
105 AND assoc_object_id = cp_object_id
106 AND (assoc_object_version = cp_object_version OR assoc_object_version IS NULL)
107 AND (
108 (trunc(start_date) BETWEEN trunc(cp_start_date) AND trunc(nvl(cp_end_date,okl_accounting_util.g_final_date))) OR
109 (trunc(cp_start_date) BETWEEN trunc(start_date) AND trunc(nvl(end_date,okl_accounting_util.g_final_date)))
110 );
111
112 CURSOR c_get_dup_obj_agr (cp_object_id okl_vp_associations.assoc_object_id%TYPE
113 ,cp_chr_id okc_k_headers_b.id%TYPE
114 ,cp_start_date okl_vp_associations.start_date%TYPE
115 ,cp_end_date okl_vp_associations.end_date%TYPE
116 ,cp_object_version okl_vp_associations.assoc_object_version%TYPE
117 ,cp_id okl_vp_associations.id%TYPE
118 )IS
119 SELECT 'X'
120 FROM okl_vp_associations
121 WHERE chr_id = cp_chr_id
122 AND crs_id IS NULL
123 AND id <> cp_id
124 AND assoc_object_id = cp_object_id
125 AND (assoc_object_version = cp_object_version OR assoc_object_version IS NULL)
126 AND (
127 (trunc(start_date) BETWEEN trunc(cp_start_date) AND trunc(nvl(cp_end_date,okl_accounting_util.g_final_date))) OR
128 (trunc(cp_start_date) BETWEEN trunc(start_date) AND trunc(nvl(end_date,okl_accounting_util.g_final_date)))
129 );
130
131 l_return_value VARCHAR2(1) ;
132 lv_dummy VARCHAR2(1);
133
134 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.okl_pa_data_integrity.validate_date_overlap';
135 l_debug_enabled VARCHAR2(10);
136
137 BEGIN
138 l_debug_enabled := okl_debug_pub.check_log_enabled;
139 IF(l_debug_enabled='Y') THEN
140 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRPAQB.pls.pls call validate_date_overlap');
141 END IF;
142
143 l_return_value := 'S';
144
145 OPEN get_assoc_rec(cp_id => p_id); FETCH get_assoc_rec INTO cv_get_assoc_rec;
146 CLOSE get_assoc_rec;
147 IF(cv_get_assoc_rec.chr_id IS NULL AND cv_get_assoc_rec.crs_id IS NOT NULL)THEN
148 -- this is the case of ASSOCIATION record
149 -- find all such associated objects on the ASSOCIATION which have overlap dates
150 OPEN c_get_dup_obj_assoc(cp_object_id => cv_get_assoc_rec.assoc_object_id
151 ,cp_crs_id => cv_get_assoc_rec.crs_id
152 ,cp_start_date => cv_get_assoc_rec.start_date
153 ,cp_end_date => cv_get_assoc_rec.end_date
154 ,cp_object_version => cv_get_assoc_rec.assoc_object_version
155 ,cp_id => p_id
156 );
157 LOOP
158 FETCH c_get_dup_obj_assoc INTO lv_dummy;
159 IF(NVL(lv_dummy,'Y') = 'X')THEN
160 l_return_value := 'E';
161 END IF;
162 EXIT WHEN (c_get_dup_obj_assoc%NOTFOUND OR l_return_value = 'E');
163 END LOOP;
164 CLOSE c_get_dup_obj_assoc;
165 ELSIF(cv_get_assoc_rec.chr_id IS NOT NULL AND cv_get_assoc_rec.crs_id IS NULL)THEN
166 -- this is the case of AGREEMENT type of change request or record on original PA
167 -- find all such objects on the change request or pa which have overlapping dates
168 OPEN c_get_dup_obj_agr(cp_object_id => cv_get_assoc_rec.assoc_object_id
169 ,cp_chr_id => cv_get_assoc_rec.chr_id
170 ,cp_start_date => cv_get_assoc_rec.start_date
171 ,cp_end_date => cv_get_assoc_rec.end_date
172 ,cp_object_version => cv_get_assoc_rec.assoc_object_version
173 ,cp_id => p_id
174 );
175 LOOP
176 FETCH c_get_dup_obj_agr INTO lv_dummy;
177 IF(NVL(lv_dummy,'Y') = 'X')THEN
178 l_return_value := 'E';
179 END IF;
180 EXIT WHEN (c_get_dup_obj_agr%NOTFOUND OR l_return_value = 'E');
181 END LOOP;
182 CLOSE c_get_dup_obj_agr;
183 END IF;
184
185 /*
186 -- For all the records found, except the reference associated object.
187 FOR each_row IN get_assoc_rec(p_id, p_chr_id, p_assoc_obj_id)
188 LOOP
189 -- If the reference objects end date and the records end date is not null then
190 -- check if reference objects start date is not between records effective date and
191 -- the records end date is not between reference effective date then return status as 'S' else as 'E'
192 IF(p_end_date IS NOT NULL)THEN
193 IF(each_row.end_date IS NOT NULL)THEN
194 IF((each_row.start_date NOT BETWEEN p_start_date AND p_end_date) AND (p_start_date NOT BETWEEN each_row.start_date AND each_row.end_date))THEN
195 l_return_value := 'S';
196 ELSE
197 l_return_value := 'E';
198 END IF;
199 -- if records end date is null, check for the reference end date should be less than the
200 -- records start date.
201 ELSE
202 IF(p_end_date < each_row.start_date) THEN
203 l_return_value := 'S';
204 ELSE
205 l_return_value := 'E';
206 END IF;
207 END IF;
208 -- If reference end date is null, check for the record end date should be less than the reference start date.
209 ELSIF(each_row.end_date IS NOT NULL)THEN
210 IF(each_row.end_date < p_start_date) THEN
211 l_return_value := 'S';
212 ELSE
213 l_return_value := 'E';
214 END IF;
215 -- If both the reference and record end date are null, return status as 'E'.
216 ELSE
217 l_return_value := 'E';
218 END IF;
219 EXIT WHEN l_return_value = 'E';
220 END LOOP;
221
222 IF(l_debug_enabled='Y') THEN
223 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRPAQB.pls.pls call validate_date_overlap');
224 END IF;
225 */
226 return l_return_value;
227
228 END validate_date_overlap;
229
230 -- This function validates the association end date should lie in between the agreements effective dates.
231 FUNCTION validate_end_date(p_assoc_end_date IN DATE, p_agrmnt_start_date IN DATE, p_agrmnt_end_date IN DATE) RETURN VARCHAR2 IS
232
233
234 l_return_value VARCHAR2(1) ;
235
236 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.okl_pa_data_integrity.validate_end_date';
237 l_debug_enabled VARCHAR2(10);
238
239 BEGIN
240
241 l_debug_enabled := okl_debug_pub.check_log_enabled;
242 IF(l_debug_enabled='Y') THEN
243 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRPAQB.pls.pls call validate_end_date');
244 END IF;
245
246 l_return_value := 'S';
247
248 --If agreement end date and the association end date are not null.
249 IF(p_agrmnt_end_date IS NOT NULL) THEN
250 IF(p_assoc_end_date IS NOT NULL) THEN
251 -- If the association end date does not lie in between the agreement effective dates, return 'E'.
252 IF(p_assoc_end_date NOT BETWEEN p_agrmnt_start_date AND p_agrmnt_end_date) THEN
253 l_return_value := 'E';
254 ELSE
255 l_return_value := 'S';
256 END IF;
257 ELSE
258 l_return_value := 'E';
259 END IF;
260 -- If agreement end date is null, check if the association end date is less than agreement start date return 'E'.
261 ELSE
262 IF(p_assoc_end_date IS NOT NULL) THEN
263 IF(p_assoc_end_date < p_agrmnt_start_date)THEN
264 l_return_value := 'E';
265 ELSE
266 l_return_value := 'S';
267 END IF;
268 ELSE
269 l_return_value := 'S';
270 END IF;
271 END IF;
272
273 IF(l_debug_enabled='Y') THEN
274 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRPAQB.pls.pls call validate_end_date');
275 END IF;
276
277 return l_return_value;
278
279 END validate_end_date;
280
281 -- This function validates for association start date should lie between agreement effective dates.
282 FUNCTION validate_start_date(p_assoc_start_date IN DATE, p_agrmnt_start_date IN DATE, p_agrmnt_end_date IN DATE) RETURN VARCHAR2 IS
283
284 l_return_value VARCHAR2(1) ;
285
286 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.okl_pa_data_integrity.validate_start_date';
287 l_debug_enabled VARCHAR2(10);
288
289 BEGIN
290
291 l_debug_enabled := okl_debug_pub.check_log_enabled;
292 IF(l_debug_enabled='Y') THEN
293 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRPAQB.pls.pls call validate_start_date');
294 END IF;
295
296 l_return_value := 'S';
297
298 -- If agreement end date is not null, check if association start date lies between agreement effective dates.
299 IF(p_agrmnt_end_date IS NOT NULL) THEN
300 IF(p_assoc_start_date NOT BETWEEN p_agrmnt_start_date AND p_agrmnt_end_date) THEN
301 l_return_value := 'E';
302 ELSE
303 l_return_value := 'S';
304 END IF;
305 -- If agreement end date is null, check if association start date is less than agreement effective dates return 'E'.
306 ELSE
307 IF(p_assoc_start_date < p_agrmnt_start_date)THEN
308 l_return_value := 'E';
309 ELSE
310 l_return_value := 'S';
311 END IF;
312 END IF;
313
314 IF(l_debug_enabled='Y') THEN
315 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRPAQB.pls.pls call validate_start_date');
316 END IF;
317
318 return l_return_value;
319
320 END validate_start_date;
321
322
323 -- THis function validates the stauts of the lease contract template or the lease application template attched to the agreement.
324 FUNCTION validate_status(p_status IN VARCHAR2) RETURN VARCHAR2 IS
325
326 l_return_value VARCHAR2(1) ;
327
328 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.okl_pa_data_integrity.validate_status';
329 l_debug_enabled VARCHAR2(10);
330
331 BEGIN
332
333 l_debug_enabled := okl_debug_pub.check_log_enabled;
334 IF(l_debug_enabled='Y') THEN
335 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRPAQB.pls.pls call validate_status');
336 END IF;
337
338 l_return_value := 'S';
339 -- if the status of lease agreement template or the lease application template attached to the agreement is not in
340 -- status "Active", return the status as 'E'.
341 IF(p_status <> 'ACTIVE')THEN
342 l_return_value := 'E';
343 ELSE
344 l_return_value := 'S';
345 END IF;
346
347 IF(l_debug_enabled='Y') THEN
348 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRPAQB.pls.pls call validate_status');
349 END IF;
350
351 return l_return_value;
352
353 END validate_status;
354
355 -- This function returns the lookup meaning of the lookup code and lookup type passed to the function.
356 FUNCTION get_lookup_meaning( p_lookup_type FND_LOOKUPS.LOOKUP_TYPE%TYPE
357 ,p_lookup_code FND_LOOKUPS.LOOKUP_CODE%TYPE)
358 RETURN VARCHAR2
359 IS
360 CURSOR fnd_lookup_csr( p_lookup_type fnd_lookups.lookup_type%type
361 ,p_lookup_code fnd_lookups.lookup_code%type)
362 IS
363 SELECT MEANING
364 FROM FND_LOOKUPS FND
365 WHERE FND.LOOKUP_TYPE = p_lookup_type
366 AND FND.LOOKUP_CODE = p_lookup_code;
367
368 l_return_value fnd_lookups.meaning%TYPE;
369
370 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.okl_pa_data_integrity.get_lookup_meaning';
371 l_debug_enabled VARCHAR2(10);
372
373 BEGIN
374 l_debug_enabled := okl_debug_pub.check_log_enabled;
375 IF(l_debug_enabled='Y') THEN
376 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRPAQB.pls.pls call get_lookup_meaning');
377 END IF;
378
379 IF ( p_lookup_type IS NOT NULL AND p_lookup_code IS NOT NULL )
380 THEN
381 OPEN fnd_lookup_csr( p_lookup_type, p_lookup_code );
382 FETCH fnd_lookup_csr INTO l_return_value;
383 CLOSE fnd_lookup_csr;
384 END IF;
385 IF(l_debug_enabled='Y') THEN
386 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRPAQB.pls.pls call evaluate_territory');
387 END IF;
388 return l_return_value;
389 END get_lookup_meaning;
390
391 -- This function validates all the records and throws an error for all the validation failures.
392 PROCEDURE check_functional_constraints( x_return_status OUT NOCOPY VARCHAR2,
393 p_chr_id IN NUMBER
394 ) IS
395
396 -- Cursor to fetch the agreement record.
397 CURSOR get_agreement_rec(cp_chr_id IN NUMBER) IS
398 SELECT start_date,
399 end_date,
400 contract_number,
401 inv_organization_id,
402 sts_code
403 FROM OKC_K_HEADERS_B
404 WHERE id = cp_chr_id;
405
406 -- Cursor to fetch lease application template record.
407 -- Manu 01-Sep-2005 Change reference_number to name
408 CURSOR get_application_template_rec(cp_id IN NUMBER, cp_version IN NUMBER)IS
409 SELECT temp.name, --temp.reference_number,
410 vers.version_status
411 FROM OKL_LEASEAPP_TMPLS temp,
412 okl_leaseapp_templ_versions_v vers
413 WHERE temp.id = cp_id
414 AND vers.leaseapp_template_id = temp.id
415 -- Manu 02-Sep-2005 version changed to version_number
416 AND vers.version_number = cp_version ;
417
418 -- Cursor to fetch the associated object details.
419 -- for normal agreement
420 CURSOR get_association_rec(cp_chr_id IN NUMBER) IS
421 SELECT vp_assoc.id,
422 vp_assoc.start_date,
423 vp_assoc.end_date,
424 vp_assoc.assoc_object_type_code,
425 vp_assoc.assoc_object_id,
426 vp_assoc.assoc_object_version
427 FROM okl_vp_associations vp_assoc
428 ,okc_k_headers_b chr
429 ,okc_statuses_b sts
430 WHERE vp_assoc.chr_id = cp_chr_id
431 AND vp_assoc.chr_id = chr.id
432 AND sts.code = chr.sts_code
433 AND sts.ste_Code = 'ENTERED'
434 UNION
435 -- for association type of change request
436 SELECT vpa.id,
437 vpa.start_date,
438 vpa.end_date,
439 vpa.assoc_object_type_code,
440 vpa.assoc_object_id,
441 vpa.assoc_object_version
442 FROM okl_vp_associations vpa,
443 okl_vp_change_requests creq
444 WHERE creq.chr_id = cp_chr_id
445 AND vpa.crs_id = creq.id
446 AND creq.status_code in ('PASSED','NEW','INCOMPLETE');
447 -- sjalasut, commented as the following sql is inconsequential
448 /*
449 UNION
450 -- for agreement type of change request
451 SELECT vpa.id,
452 vpa.start_date,
453 vpa.end_date,
454 vpa.assoc_object_type_code,
455 vpa.assoc_object_id,
456 vpa.assoc_object_version
457 FROM okl_vp_associations vpa,
458 okl_vp_change_requests creq
459 WHERE vpa.crs_id = (select crs_id from okl_k_headers where id = cp_chr_id)
460 AND vpa.crs_id = creq.id
461 AND creq.status_code in ('PASSED','NEW','INCOMPLETE')
462 */
463
464
465 -- Cursor to fetch the items name, associated to an agreement.
466 CURSOR get_item_name(p_org_id IN NUMBER, p_item_id IN NUMBER) IS
467 SELECT description
468 FROM MTL_SYSTEM_ITEMS_VL
469 WHERE organization_id = p_org_id
470 AND inventory_item_id = p_item_id;
471
472 -- Cursor to fetch the item categories name, associated to an agreement.
473 CURSOR get_item_catg_name(p_item_catg_id IN NUMBER)IS
474 SELECT CATEGORY_CONCAT_SEGS
475 FROM MTL_CATEGORIES_V
476 WHERE CATEGORY_ID = p_item_catg_id;
477
478 -- Cursor to fetch the end of terms name, associated to an agreement.
479 CURSOR get_eot_name(p_eot_id IN NUMBER)IS
480 SELECT end_of_term_name
481 FROM OKL_FE_EO_TERMS_V
482 WHERE end_of_term_id = p_eot_id;
483
484 -- Cursor to fetch the item products name, associated to an agreement.
485 CURSOR get_product_name(p_prod_id IN NUMBER)IS
486 SELECT name
487 FROM OKL_PRODUCTS
488 WHERE id = p_prod_id;
489
490 -- Cursor to fetch the parent agreement record.
491 CURSOR get_parent_rec(cp_chr_id IN NUMBER)IS
492 SELECT chrb.contract_number,
493 chrb.sts_code,
494 chrb.start_date,
495 chrb.end_date
496 FROM OKC_GOVERNANCES govb,
497 OKC_K_HEADERS_B chrb
498 WHERE govb.chr_id = cp_chr_id
499 AND govb.chr_id_referred = chrb.id;
500
501 -- Cursor to fetch the lease application template parameters.
502 -- Manu 01-Sep-2005 Change reference_number to name and sic_code to industry_code
503 -- Manu 02-Sep-2005 remove references to APPLICATION_TYPE
504 CURSOR get_la_rec(cp_assoc_id IN NUMBER) IS
505 SELECT cust_credit_classification,
506 credit_review_purpose,
507 industry_code,
508 name,
509 industry_class
510 FROM OKL_LEASEAPP_TMPLS
511 WHERE id = cp_assoc_id;
512
513 -- Cursor to find the duplicate record of lease application template with the combination of the parameters
514 -- application_type, cust_credit_classification, credit_review_purpose, sic_code.
515
516 -- Manu 01-Sep-2005 Change sic_code to industry_code
517 -- Manu 02-Sep-2005 remove references to APPLICATION_TYPE
518 CURSOR get_la_assoc_rec_param(cp_id IN NUMBER,
519 cp_object_id IN NUMBER,
520 cp_chr_id IN NUMBER,
521 cp_credit_class IN VARCHAR2,
522 cp_credit_review IN VARCHAR2,
523 cp_sic_code IN VARCHAR2,
524 cp_industry_class IN VARCHAR2,
525 cp_start_date IN DATE,
526 cp_end_date IN DATE)IS
527 SELECT lat.name
528 FROM OKL_LEASEAPP_TMPLS lat,
529 okl_vp_associations_v vpa,
530 okc_k_headers_b chr,
531 okc_statuses_b sts
532 WHERE lat.id = vpa.assoc_object_id
533 AND chr.id = vpa.chr_id
534 AND chr.sts_code = sts.code
535 AND sts.ste_code = 'ENTERED'
536 AND vpa.assoc_object_type_code = 'LA_TEMPLATE'
537 AND vpa.chr_id = cp_chr_id
538 AND lat.cust_credit_classification = cp_credit_class
539 AND lat.credit_review_purpose = cp_credit_review
540 AND NVL(lat.industry_code, OKL_API.G_MISS_CHAR) = NVL(cp_sic_code,OKL_API.G_MISS_CHAR)
541 AND NVL(lat.industry_class,OKL_API.G_MISS_CHAR) = NVL(cp_industry_class, OKL_API.G_MISS_CHAR)
542 AND vpa.id <> cp_id
543 AND vpa.assoc_object_id <> cp_object_id
544 AND (
545 (trunc(cp_start_date) BETWEEN trunc(vpa.start_date) AND TRUNC(NVL(vpa.end_date,okl_accounting_util.g_final_date))) OR
546 (trunc(NVL(cp_end_date,okl_accounting_util.g_final_date)) BETWEEN trunc(vpa.start_date) AND TRUNC(NVL(vpa.end_date,okl_accounting_util.g_final_date)))
547 )
548 UNION
549 SELECT lat.name
550 FROM OKL_LEASEAPP_TMPLS lat,
551 okl_vp_associations_v vpa,
552 okl_vp_change_requests chreq
553 WHERE lat.id = vpa.assoc_object_id
554 AND vpa.assoc_object_type_code = 'LA_TEMPLATE'
555 AND chreq.chr_id = cp_chr_id
556 AND vpa.crs_id = chreq.id
557 AND lat.cust_credit_classification = cp_credit_class
558 AND lat.credit_review_purpose = cp_credit_review
559 AND NVL(lat.industry_code, OKL_API.G_MISS_CHAR) = NVL(cp_sic_code,OKL_API.G_MISS_CHAR)
560 AND NVL(lat.industry_class,OKL_API.G_MISS_CHAR) = NVL(cp_industry_class, OKL_API.G_MISS_CHAR)
561 AND vpa.id <> cp_id
562 AND vpa.assoc_object_id <> cp_object_id
563 AND (
564 (trunc(cp_start_date) BETWEEN trunc(vpa.start_date) AND TRUNC(NVL(vpa.end_date,okl_accounting_util.g_final_date))) OR
565 (trunc(NVL(cp_end_date,okl_accounting_util.g_final_date)) BETWEEN trunc(vpa.start_date) AND TRUNC(NVL(vpa.end_date,okl_accounting_util.g_final_date)))
566 )
567 AND chreq.status_code in ('PASSED','NEW','INCOMPLETE')
568 UNION
569 SELECT lat.name
570 FROM OKL_LEASEAPP_TMPLS lat,
571 okl_vp_associations_v vpa,
572 okl_vp_change_requests chreq,
573 okl_k_headers okl
574 WHERE lat.id = vpa.assoc_object_id
575 AND vpa.assoc_object_type_code = 'LA_TEMPLATE'
576 AND okl.id = cp_chr_id
577 AND vpa.crs_id = okl.crs_id
578 AND lat.cust_credit_classification = cp_credit_class
579 AND lat.credit_review_purpose = cp_credit_review
580 AND NVL(lat.industry_code, OKL_API.G_MISS_CHAR) = NVL(cp_sic_code,OKL_API.G_MISS_CHAR)
581 AND NVL(lat.industry_class,OKL_API.G_MISS_CHAR) = NVL(cp_industry_class, OKL_API.G_MISS_CHAR)
582 AND vpa.id <> cp_id
583 AND vpa.assoc_object_id <> cp_object_id
584 AND (
585 (trunc(cp_start_date) BETWEEN trunc(vpa.start_date) AND TRUNC(NVL(vpa.end_date,okl_accounting_util.g_final_date))) OR
586 (trunc(NVL(cp_end_date,okl_accounting_util.g_final_date)) BETWEEN trunc(vpa.start_date) AND TRUNC(NVL(vpa.end_date,okl_accounting_util.g_final_date)))
587 )
588 AND chreq.id = vpa.crs_id
589 AND chreq.status_code in ('PASSED','NEW','INCOMPLETE');
590
591 CURSOR c_get_criteria_csr(cp_chr_id okc_k_headers_b.id%TYPE)IS
592 SELECT crit.effective_from_date
593 ,crit.effective_to_date
594 ,crit.match_criteria_code
595 ,cdef.crit_cat_name
596 FROM okl_fe_criteria crit
597 ,okl_fe_criteria_set cset
598 ,okl_fe_Crit_cat_def_v cdef
599 WHERE crit.criteria_set_id = cset.criteria_set_id
600 AND cdef.crit_cat_def_id = crit.crit_cat_def_id
601 AND source_id = cp_chr_id
602 AND source_object_code = 'VENDOR_PROGRAM';
603
604 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
605 l_start_date OKC_K_HEADERS_B.START_DATE%TYPE;
606 l_end_date OKC_K_HEADERS_B.END_DATE%TYPE;
607 l_agrmnt_number OKC_K_HEADERS_B.CONTRACT_NUMBER%TYPE;
608 l_inv_org_id OKC_K_HEADERS_B.INV_ORGANIZATION_ID%TYPE;
609 l_sts_code OKC_K_HEADERS_B.STS_CODE%TYPE;
610 l_assoc_start_date OKC_K_HEADERS_B.START_DATE%TYPE;
611 l_assoc_end_date OKC_K_HEADERS_B.END_DATE%TYPE;
612 l_assoc_agrmnt_number OKC_K_HEADERS_B.CONTRACT_NUMBER%TYPE;
613 l_assoc_inv_org_id OKC_K_HEADERS_B.INV_ORGANIZATION_ID%TYPE;
614 l_assoc_sts_code OKC_K_HEADERS_B.STS_CODE%TYPE;
615 l_assoc_name VARCHAR2(2000);
616 p_api_version NUMBER;
617 p_init_msg_list VARCHAR2(256) DEFAULT OKC_API.G_FALSE;
618 x_msg_count NUMBER;
619 x_msg_data VARCHAR2(256);
620 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.okl_pa_data_integrity.check_functional_constraints';
621 l_debug_enabled VARCHAR2(10);
622
623 l_dummy VARCHAR2(1);
624 -- Manu 02-Sep-2005 remove references to APPLICATION_TYPE
625 -- l_application_type OKL_LEASEAPP_TEMPLATES_V.APPLICATION_TYPE%TYPE;
626 l_credit_class OKL_LEASEAPP_TMPLS.CUST_CREDIT_CLASSIFICATION%TYPE;
627 l_credit_purpose OKL_LEASEAPP_TMPLS.CREDIT_REVIEW_PURPOSE%TYPE;
628 l_sic_code OKL_LEASEAPP_TMPLS.INDUSTRY_CODE%TYPE;
629 -- Manu 01-Sep-2005 Change REFERENCE_NUMBER to NAME
630 -- l_appl_temp_name OKL_LEASEAPP_TEMPLATES_V.REFERENCE_NUMBER%TYPE;
631 l_appl_temp_name OKL_LEASEAPP_TMPLS.NAME%TYPE;
632 lv_industry_class OKL_LEASEAPP_TMPLS.industry_class%TYPE;
633 lv_leaseapp_tmpt_name OKL_LEASEAPP_TMPLS.name%TYPE;
634 BEGIN
635
636 l_debug_enabled := okl_debug_pub.check_log_enabled;
637
638 IF(l_debug_enabled='Y') THEN
639 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRPAQB.pls.pls call get_lookup_meaning');
640 END IF;
641 -- initialize return status
642 x_return_status := OKL_API.G_RET_STS_SUCCESS;
643
644 OPEN get_agreement_rec(p_chr_id);
645 FETCH get_agreement_rec INTO l_start_date, l_end_date, l_agrmnt_number, l_inv_org_id, l_sts_code;
646 CLOSE get_agreement_rec;
647
648 -- Check the agreements effective dates should be between the parent agreements effective dates and the parent
649 -- agreement attached to the agreement must be in status "Active".
650 FOR parent_rec IN get_parent_rec(p_chr_id)
651 LOOP
652 -- Check if the agreements start date lies between the parent agreements effective dates, else throw an error.
653 l_return_status := validate_start_date(l_start_date,
654 parent_rec.start_date,
655 parent_rec.end_date
656 );
657 IF(l_return_status = 'E')THEN
658 OKL_API.set_message(G_APP_NAME,
659 'OKL_VN_AGR_INV_START_DATE',
660 'AGR_NUMBER',
661 l_agrmnt_number,
662 'PARENT_AGR_NUMBER',
663 parent_rec.contract_number
664 );
665 x_return_status := OKL_API.G_RET_STS_ERROR;
666 END IF;
667 -- Check if the agreements end date lies between the parent agreements effective dates, else throw an error.
668 l_return_status := validate_end_date(l_end_date,
669 parent_rec.start_date,
670 parent_rec.end_date
671 );
672 IF(l_return_status = 'E')THEN
673 OKL_API.set_message(G_APP_NAME,
674 'OKL_VN_AGR_INV_END_DATE',
675 'AGR_NUMBER',
676 l_agrmnt_number,
677 'PARENT_AGR_NUMBER',
678 parent_rec.contract_number
679 );
680 x_return_status := OKL_API.G_RET_STS_ERROR;
681 END IF;
682 -- Check the parent agreement must be in status "Active", else throw an error.
683 l_return_status := validate_status(parent_rec.sts_code);
684 IF(l_return_status = 'E')THEN
685 OKL_API.set_message(G_APP_NAME,
686 'OKL_VN_AGR_INV_STATUS',
687 'PARENT_AGR_NUMBER',
688 l_agrmnt_number,
689 'AGR_NUMBER',
690 parent_rec.contract_number
691 );
692 x_return_status := OKL_API.G_RET_STS_ERROR;
693 END IF;
694 END LOOP;
695
696 --For each row found in the association records, validate each record, and raise arrors, if the validation fails.
697 FOR each_row IN get_association_rec(p_chr_id)
698 LOOP
699
700 -- For lease contract template, validate that the associated templates status should be active,
701 -- The association effective dates should lie in between the agreements effective dates. If any of the validation
702 -- fails throw an error.
703 IF(each_row.assoc_object_type_code = 'LC_TEMPLATE') THEN
704 OPEN get_agreement_rec(each_row.assoc_object_id);
705 FETCH get_agreement_rec INTO l_assoc_start_date, l_assoc_end_date, l_assoc_agrmnt_number, l_assoc_inv_org_id, l_assoc_sts_code;
706 CLOSE get_agreement_rec;
707 l_return_status := validate_start_date(each_row.start_date,
708 l_start_date,
709 l_end_date
710 );
711 IF(l_return_status = 'E')THEN
712 OKL_API.set_message(G_APP_NAME,
713 'OKL_VN_INV_ASSOC_START_DATE',
714 'ASSOC_TYPE',
715 get_lookup_meaning('OKL_VP_ASSOC_OBJECT_TYPES', each_row.assoc_object_type_code),
716 'ASSOC_NAME',
717 l_assoc_agrmnt_number,
718 'AGR_NUMBER',
719 l_agrmnt_number
720 );
721 x_return_status := OKL_API.G_RET_STS_ERROR;
722 END IF;
723 l_return_status := validate_end_date(each_row.end_date,
724 l_start_date,
725 l_end_date
726 );
727 IF(l_return_status = 'E')THEN
728 OKL_API.set_message(G_APP_NAME,
729 'OKL_VN_INV_ASSOC_END_DATE',
730 'ASSOC_TYPE',
731 get_lookup_meaning('OKL_VP_ASSOC_OBJECT_TYPES', each_row.assoc_object_type_code),
732 'ASSOC_NAME',
733 l_assoc_agrmnt_number,
734 'AGR_NUMBER',
735 l_agrmnt_number
736 );
737 x_return_status := OKL_API.G_RET_STS_ERROR;
738 END IF;
739 -- l_return_status := validate_status(l_assoc_sts_code);
740 -- sjalasut, Lease Contract Temaplates are PASSED and Lease Application Templates are ACTIVE
741 -- so one common method to validate their statuses is not correct.
742 -- Bug 6642645: LC_TEMPLATE entities final status is PASSED, but not BOOKED as assumed.
743 IF((each_row.assoc_object_type_code = 'LC_TEMPLATE' AND l_assoc_sts_code <> 'PASSED') OR
744 (each_row.assoc_object_type_code = 'LA_TEMPLATE' AND l_assoc_sts_code <> 'ACTIVE')
745 )THEN
746 OKL_API.set_message(G_APP_NAME,
747 'OKL_VN_INV_ASSOC_STATUS',
748 'ASSOC_TYPE',
749 get_lookup_meaning('OKL_VP_ASSOC_OBJECT_TYPES', each_row.assoc_object_type_code),
750 'ASSOC_NAME',
751 l_assoc_agrmnt_number,
752 'AGR_NUMBER',
753 l_agrmnt_number
754 );
755 x_return_status := OKL_API.G_RET_STS_ERROR;
756 END IF;
757
758 -- For lease application template, validate that the associated templates status should be active,
759 -- The association effective dates should lie in between the agreements effective dates. If any of the validation fails throw an error.
760 ELSIF(each_row.assoc_object_type_code = 'LA_TEMPLATE') THEN
761 l_dummy := null;
762 lv_leaseapp_tmpt_name := null;
763 OPEN get_application_template_rec(each_row.assoc_object_id, each_row.assoc_object_version);
764 FETCH get_application_template_rec INTO l_assoc_agrmnt_number, l_assoc_sts_code;
765 CLOSE get_application_template_rec;
766
767 OPEN get_la_rec(each_row.assoc_object_id);
768 -- Manu 02-Sep-2005 remove references to APPLICATION_TYPE
769 FETCH get_la_rec INTO l_credit_class, l_credit_purpose, l_sic_code, l_appl_temp_name,lv_industry_class;
770 CLOSE get_la_rec;
771
772 -- Manu 02-Sep-2005 remove references to APPLICATION_TYPE
773 OPEN get_la_assoc_rec_param(each_row.id,
774 each_row.assoc_object_id,
775 p_chr_id,
776 l_credit_class,
777 l_credit_purpose,
778 l_sic_code,
779 lv_industry_class,
780 each_row.start_date,
781 each_row.end_date);
782 FETCH get_la_assoc_rec_param INTO lv_leaseapp_tmpt_name;
783 CLOSE get_la_assoc_rec_param;
784
785 IF (lv_leaseapp_tmpt_name IS NOT NULL) THEN
786 OKL_API.SET_MESSAGE( G_APP_NAME,
787 'OKL_VP_DUPLICATE_ASSOCIATION',
788 'FIRST',
789 l_appl_temp_name,
790 'SECOND',
791 l_assoc_agrmnt_number,
792 'AGR_NUMBER',
793 l_agrmnt_number
794 );
795 x_return_status := OKL_API.G_RET_STS_ERROR;
796 RAISE G_EXCEPTION_HALT_VALIDATION;
797 END IF;
798
799 l_return_status := validate_start_date(each_row.start_date,
800 l_start_date,
801 l_end_date
802 );
803 IF(l_return_status = 'E')THEN
804 OKL_API.set_message(G_APP_NAME,
805 'OKL_VN_INV_ASSOC_START_DATE',
806 'ASSOC_TYPE',
807 get_lookup_meaning('OKL_VP_ASSOC_OBJECT_TYPES', each_row.assoc_object_type_code),
808 'ASSOC_NAME',
809 l_assoc_agrmnt_number,
810 'AGR_NUMBER',
811 l_agrmnt_number
812 );
813 x_return_status := OKL_API.G_RET_STS_ERROR;
814 END IF;
815 l_return_status := validate_end_date(each_row.end_date,
816 l_start_date,
817 l_end_date
818 );
819 IF(l_return_status = 'E')THEN
820 OKL_API.set_message(G_APP_NAME,
821 'OKL_VN_INV_ASSOC_END_DATE',
822 'ASSOC_TYPE',
823 get_lookup_meaning('OKL_VP_ASSOC_OBJECT_TYPES', each_row.assoc_object_type_code),
824 'ASSOC_NAME',
825 l_assoc_agrmnt_number,
826 'AGR_NUMBER',
827 l_agrmnt_number
828 );
829 x_return_status := OKL_API.G_RET_STS_ERROR;
830 END IF;
831 l_return_status := validate_status(l_assoc_sts_code);
832 IF(l_return_status = 'E')THEN
833 OKL_API.set_message(G_APP_NAME,
834 'OKL_VN_INV_ASSOC_STATUS',
835 'ASSOC_TYPE',
836 get_lookup_meaning('OKL_VP_ASSOC_OBJECT_TYPES', each_row.assoc_object_type_code),
837 'ASSOC_NAME',
838 l_assoc_agrmnt_number,
839 'AGR_NUMBER',
840 l_agrmnt_number
841 );
842 x_return_status := OKL_API.G_RET_STS_ERROR;
843 END IF;
844
845 -- For each of the items associated to the agreement, the association dates should lie between the agreement effective dates,
846 -- For all the items of same type associated to the agreement, check no effective date of the items should be overlaping.
847 -- If any of the validation fails throw error.
848 ELSIF(each_row.assoc_object_type_code = 'LA_ITEMS')THEN
849 OPEN get_item_name(l_inv_org_id, each_row.assoc_object_id);
850 FETCH get_item_name INTO l_assoc_name;
851 CLOSE get_item_name;
852 l_return_status := validate_start_date(each_row.start_date,
853 l_start_date,
854 l_end_date
855 );
856 IF(l_return_status = 'E')THEN
857 OKL_API.set_message(G_APP_NAME,
858 'OKL_VN_INV_ITEM_START_DATE',
859 'ASSOC_NAME',
860 l_assoc_name,
861 'AGR_NUMBER',
862 l_agrmnt_number
863 );
864 x_return_status := OKL_API.G_RET_STS_ERROR;
865 END IF;
866 l_return_status := validate_end_date(each_row.end_date,
867 l_start_date,
868 l_end_date
869 );
870 IF(l_return_status = 'E')THEN
871 OKL_API.set_message(G_APP_NAME,
872 'OKL_VN_INV_ITEM_END_DATE',
873 'ASSOC_NAME',
874 l_assoc_name,
875 'AGR_NUMBER',
876 l_agrmnt_number
877 );
878 x_return_status := OKL_API.G_RET_STS_ERROR;
879 END IF;
880 l_return_status := validate_date_overlap(each_row.id,
881 p_chr_id,
882 each_row.assoc_object_id,
883 each_row.start_date,
884 each_row.end_date
885 );
886 IF(l_return_status = 'E')THEN
887 OKL_API.set_message(G_APP_NAME,
888 'OKL_VN_OVERLAP_DATE',
889 'ASSOC_TYPE',
890 get_lookup_meaning('OKL_VP_ASSOC_OBJECT_TYPES', each_row.assoc_object_type_code),
891 'AGR_NUMBER',
892 l_agrmnt_number
893 );
894 x_return_status := OKL_API.G_RET_STS_ERROR;
895 END IF;
896
897 -- For each of the item categories associated to the agreement, the association dates should lie between the agreement effective
898 -- dates. For all the item categories of same type associated to the agreement, check no effective date of the item categories
899 -- should be overlaping. If any of the validation fails throw error.
900 ELSIF(each_row.assoc_object_type_code = 'LA_ITEM_CATEGORIES')THEN
901 OPEN get_item_catg_name(each_row.assoc_object_id);
902 FETCH get_item_catg_name INTO l_assoc_name;
903 CLOSE get_item_catg_name;
904 l_return_status := validate_start_date(each_row.start_date,
905 l_start_date,
906 l_end_date
907 );
908 IF(l_return_status = 'E')THEN
909 OKL_API.set_message(G_APP_NAME,
910 'OKL_VN_INV_ITEMCAT_START_DATE',
911 'ASSOC_NAME',
912 l_assoc_name,
913 'AGR_NUMBER',
914 l_agrmnt_number
915 );
916 x_return_status := OKL_API.G_RET_STS_ERROR;
917 END IF;
918 l_return_status := validate_end_date(each_row.end_date,
919 l_start_date,
920 l_end_date
921 );
922 IF(l_return_status = 'E')THEN
923 OKL_API.set_message(G_APP_NAME,
924 'OKL_VN_INV_ITEMCAT_END_DATE',
925 'ASSOC_NAME',
926 l_assoc_name,
927 'AGR_NUMBER',
928 l_agrmnt_number
929 );
930 x_return_status := OKL_API.G_RET_STS_ERROR;
931 END IF;
932 l_return_status := validate_date_overlap(each_row.id,
933 p_chr_id,
934 each_row.assoc_object_id,
935 each_row.start_date,
936 each_row.end_date
937 );
938 IF(l_return_status = 'E')THEN
939 OKL_API.set_message(G_APP_NAME,
940 'OKL_VN_OVERLAP_DATE',
941 'ASSOC_TYPE',
942 get_lookup_meaning('OKL_VP_ASSOC_OBJECT_TYPES', each_row.assoc_object_type_code),
943 'AGR_NUMBER',
944 l_agrmnt_number
945 );
946 x_return_status := OKL_API.G_RET_STS_ERROR;
947 END IF;
948
949 -- For each of the end-of-terms associated to the agreement, the association dates should lie between the agreement effective
950 -- dates. For all the end-of-terms of same type associated to the agreement, check no effective date of the end-of-terms should
951 -- be overlaping. If any of the validation fails throw error.
952 ELSIF(each_row.assoc_object_type_code = 'LA_EOT_VALUES')THEN
953 OPEN get_eot_name(each_row.assoc_object_id);
954 FETCH get_eot_name INTO l_assoc_name;
955 CLOSE get_eot_name;
956 l_return_status := validate_start_date(each_row.start_date,
957 l_start_date,
958 l_end_date
959 );
960 IF(l_return_status = 'E')THEN
961 OKL_API.set_message(G_APP_NAME,
962 'OKL_VN_INV_EOT_START_DATE',
963 'ASSOC_NAME',
964 l_assoc_name,
965 'AGR_NUMBER',
966 l_agrmnt_number
967 );
968 x_return_status := OKL_API.G_RET_STS_ERROR;
969 END IF;
970 l_return_status := validate_end_date(each_row.end_date,
971 l_start_date,
972 l_end_date
973 );
974 IF(l_return_status = 'E')THEN
975 OKL_API.set_message(G_APP_NAME,
976 'OKL_VN_INV_EOT_END_DATE',
977 'ASSOC_NAME',
978 l_assoc_name,
979 'AGR_NUMBER',
980 l_agrmnt_number
981 );
982 x_return_status := OKL_API.G_RET_STS_ERROR;
983 END IF;
984 l_return_status := validate_date_overlap(each_row.id,
985 p_chr_id,
986 each_row.assoc_object_id,
987 each_row.start_date,
988 each_row.end_date
989 );
990 IF(l_return_status = 'E')THEN
991 OKL_API.set_message(G_APP_NAME,
992 'OKL_VN_OVERLAP_DATE',
993 'ASSOC_TYPE',
994 get_lookup_meaning('OKL_VP_ASSOC_OBJECT_TYPES', each_row.assoc_object_type_code),
995 'AGR_NUMBER',
996 l_agrmnt_number
997 );
998 x_return_status := OKL_API.G_RET_STS_ERROR;
999 END IF;
1000
1001 -- For each of the products associated to the agreement, the association dates should lie between the agreement effective
1002 -- dates. For all the products of same type associated to the agreement, check no effective date of the products should
1003 -- be overlaping. If any of the validation fails throw error.
1004 ELSIF(each_row.assoc_object_type_code = 'LA_FINANCIAL_PRODUCT')THEN
1005 OPEN get_product_name(each_row.assoc_object_id);
1006 FETCH get_product_name INTO l_assoc_name;
1007 CLOSE get_product_name;
1008 l_return_status := validate_start_date(each_row.start_date,
1009 l_start_date,
1010 l_end_date
1011 );
1012 IF(l_return_status = 'E')THEN
1013 OKL_API.set_message(G_APP_NAME,
1014 'OKL_VN_INV_PDT_START_DATE',
1015 'ASSOC_NAME',
1016 l_assoc_name,
1017 'AGR_NUMBER',
1018 l_agrmnt_number
1019 );
1020 x_return_status := OKL_API.G_RET_STS_ERROR;
1021 END IF;
1022 l_return_status := validate_end_date(each_row.end_date,
1023 l_start_date,
1024 l_end_date
1025 );
1026 IF(l_return_status = 'E')THEN
1027 OKL_API.set_message(G_APP_NAME,
1028 'OKL_VN_INV_PDT_END_DATE',
1029 'ASSOC_NAME',
1030 l_assoc_name,
1031 'AGR_NUMBER',
1032 l_agrmnt_number
1033 );
1034 x_return_status := OKL_API.G_RET_STS_ERROR;
1035 END IF;
1036 l_return_status := validate_date_overlap(each_row.id,
1037 p_chr_id,
1038 each_row.assoc_object_id,
1039 each_row.start_date,
1040 each_row.end_date
1041 );
1042 IF(l_return_status = 'E')THEN
1043 OKL_API.set_message(G_APP_NAME,
1044 'OKL_VN_OVERLAP_DATE',
1045 'ASSOC_TYPE',
1046 get_lookup_meaning('OKL_VP_ASSOC_OBJECT_TYPES', each_row.assoc_object_type_code),
1047 'AGR_NUMBER',
1048 l_agrmnt_number
1049 );
1050 x_return_status := OKL_API.G_RET_STS_ERROR;
1051 END IF;
1052 END IF;
1053 END LOOP;
1054
1055 -- sjalasut, added validations to check the effective dates of the criteria fall within the effective dates of the PA
1056 -- START of code changes
1057 FOR c_get_criteria_rec IN c_get_criteria_csr(cp_chr_id => p_chr_id) LOOP
1058 IF((TRUNC(c_get_criteria_rec.effective_from_date) NOT BETWEEN TRUNC(l_start_date) AND TRUNC(NVL(l_end_date,okl_accounting_util.g_final_date))) OR
1059 (TRUNC(NVL(c_get_criteria_rec.effective_to_date,okl_accounting_util.g_final_date)) NOT BETWEEN TRUNC(l_start_date) AND TRUNC(NVL(l_end_date,okl_accounting_util.g_final_date)))
1060 )THEN
1061 fnd_message.set_name(G_APP_NAME, 'OKL_ELIGIBILITY_CRIT_CAT');
1062 okl_api.set_message(p_app_name => G_APP_NAME
1063 ,p_msg_name => 'OKL_INVALID_EFFECTIVE_DATES'
1064 ,p_token1 => 'CRIT_CAT'
1065 ,p_token1_value => fnd_message.get
1066 ,p_token2 => 'NAME'
1067 ,p_token2_value => c_get_criteria_rec.crit_cat_name);
1068 x_return_status := OKL_API.G_RET_STS_ERROR;
1069 END IF;
1070 END LOOP;
1071 -- END of code changes
1072
1073 -- sjalasut, added more validations as part of the vendor residual share enhancement.START
1074 l_return_status := validate_residual_positive(p_chr_id => p_chr_id);
1075 IF(l_return_status = 'E')THEN
1076 x_return_status := OKL_API.G_RET_STS_ERROR;
1077 END IF;
1078
1079 l_return_status := validate_residual_parties(p_chr_id => p_chr_id);
1080 IF(l_return_status = 'E')THEN
1081 x_return_status := OKL_API.G_RET_STS_ERROR;
1082 END IF;
1083
1084 l_return_status := validate_total_residual(p_chr_id => p_chr_id);
1085 IF(l_return_status = 'E')THEN
1086 x_return_status := OKL_API.G_RET_STS_ERROR;
1087 END IF;
1088 -- sjalasut, added more validations as part of the vendor residual share enhancement.END
1089
1090 IF x_return_status = OKL_API.G_RET_STS_SUCCESS THEN
1091 OKL_API.set_message(
1092 p_app_name => G_APP_NAME,
1093 p_msg_name => G_QA_SUCCESS);
1094 END IF;
1095
1096 IF(l_debug_enabled='Y') THEN
1097 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRPAQB.pls.pls call evaluate_territory');
1098 END IF;
1099
1100 EXCEPTION
1101
1102 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1103 -- no processing necessary; validation can continue with next column
1104 NULL;
1105 WHEN OTHERS THEN
1106 -- store SQL error message on message stack
1107 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME,
1108 p_msg_name => G_UNEXPECTED_ERROR,
1109 p_token1 => G_SQLCODE_TOKEN,
1110 p_token1_value => SQLCODE,
1111 p_token2 => G_SQLERRM_TOKEN,
1112 p_token2_value => SQLERRM);
1113 -- notify caller of an error as UNEXPETED error
1114 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1115
1116 END check_functional_constraints;
1117
1118 END OKL_PA_DATA_INTEGRITY;
[Home] [Help]