1 package body per_kr_contact_rules as
2 /* $Header: pekrceiv.pkb 120.0.12010000.2 2008/08/06 09:15:37 ubhat ship $ */
3 l_count NUMBER;
4 l_year VARCHAR2(4);
5 --
6 procedure yea_details_exists_for_year(
7 P_CONTACT_EXTRA_INFO_ID in number,
8 P_CONTACT_RELATIONSHIP_ID in number,
9 P_INFORMATION_TYPE in varchar2,
10 P_EFFECTIVE_START_DATE in date,
11 P_EFFECTIVE_END_DATE in date)
12 is
13
14 cursor csr_check_contact_details is
15 select count(contact_extra_info_id)
16 from per_contact_extra_info_f cei
17 where cei.contact_relationship_id = P_CONTACT_RELATIONSHIP_ID
18 and cei.information_type = P_INFORMATION_TYPE
19 and to_char(P_EFFECTIVE_START_DATE,'yyyy') = to_char(cei.effective_start_date,'yyyy');
20 begin
21
22 IF P_INFORMATION_TYPE = 'KR_DPNT_EXPENSE_INFO' then
23 --
24 l_year := to_char(P_EFFECTIVE_START_DATE,'YYYY');
25 open csr_check_contact_details;
26 fetch csr_check_contact_details into l_count;
27 IF l_count >= 2 then
28 fnd_message.set_name('PAY', 'PAY_KR_YEA_CONT_EXPENSE_EXISTS');
29 fnd_message.set_token('YEAR',l_year);
30 fnd_message.raise_error;
31 end if;
32 END IF;
33 end;
34 --
35 -------------------------------------------------------------------------------
36 -- Bug 6849941: Credit Card validation checks for the Dependents
37 -------------------------------------------------------------------------------
38 procedure yea_credit_exp_allowed(
39 p_effective_date in date
40 ,p_contact_relationship_id in number
41 ,p_cei_information7 in varchar2
42 ,p_cei_information8 in varchar2
43 ,p_information_type in varchar2)
44 is
45
46 l_itax_law varchar2(2);
47 l_cont_type varchar2(2);
48 l_kr_cont_type varchar2(2);
49
50 cursor csr_dpnt is
51 select ctr.cont_information2 itax_law,
52 nvl(ctr.cont_information11, '0') kr_cont_type,
53 decode(ctr.contact_type, 'P', '1', 'S', '3', 'A', '4', 'C', '4', 'R', '4', 'O', '4', 'T', '4', '6') cont_type
54 from per_contact_relationships ctr
55 where ctr.cont_information_category = 'KR'
56 and ctr.cont_information1 = 'Y'
57 and ((ctr.cont_information9 ='D' and p_effective_date between nvl(date_start, p_effective_date) and nvl(trunc(add_months(date_end,12),'YYYY')-1,p_effective_date))
58 or (nvl(ctr.cont_information9,'XXX') <>'D' and p_effective_date between nvl(date_start, p_effective_date) and nvl(date_end, p_effective_date))
59 )
60 and ctr.contact_relationship_id = p_contact_relationship_id;
61
62 begin
63
64 IF p_information_type = 'KR_DPNT_EXPENSE_INFO' then
65 open csr_dpnt;
66 fetch csr_dpnt into l_itax_law, l_kr_cont_type, l_cont_type;
67 close csr_dpnt;
68 --
69 if ( ( l_cont_type in ('1','2','3','4')
70 or l_kr_cont_type in ('1','2','3','4')
71 )
72 and ( l_itax_law = 'Y' )
73 ) then
74 null;
75 else
76 if ((p_cei_information7 is not null) or (p_cei_information8 is not null)) then
77 fnd_message.set_name('PAY', 'PAY_KR_YEA_CONT_CARD_EXP_ENBLD');
78 fnd_message.raise_error;
79 end if;
80 end if;
81 --
82 END IF;
83 end;
84 end;