1 package body per_kr_contact_rules as
2 /* $Header: pekrceiv.pkb 120.4.12020000.2 2012/07/05 02:50:39 amnaraya 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 -- Bug 8644512: Added code for Child(Other than Immediate) to enable the fields
70 if ( ( l_cont_type in ('1','2','3','4') or l_kr_cont_type in ('1','2','3','4','7')
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 -----------------------------------------------------------------------------------------------
85 -- Bug 7142612: Validating the Dependents for the Donation fields for Extra Contact Information
86 -----------------------------------------------------------------------------------------------
87 procedure enable_donation_fields(
88 p_effective_date in date
89 ,p_contact_relationship_id in number
90 ,p_cei_information14 in varchar2
91 ,p_cei_information15 in varchar2
92 ,p_information_type in varchar2)
93 is
94
95 l_itax_law varchar2(2);
96 l_cont_type varchar2(2);
97 l_kr_cont_type varchar2(2);
98
99 cursor csr_dpnt is
100 select ctr.cont_information2 itax_law,
101 nvl(ctr.cont_information11, '0') kr_cont_type,
102 decode(ctr.contact_type, 'S','3','P','1','B','5','SISTER','5',
103 'A','4','C','4','R','4','O','4','T','4','6') cont_type --Bug 12714266
104 from per_contact_relationships ctr
105 where ctr.cont_information_category = 'KR'
106 and ctr.cont_information1 = 'Y'
107 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))
108 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))
109 )
110 and ctr.contact_relationship_id = p_contact_relationship_id;
111
112 begin
113
114 IF p_information_type = 'KR_DPNT_EXPENSE_INFO' then
115 open csr_dpnt;
116 fetch csr_dpnt into l_itax_law, l_kr_cont_type, l_cont_type;
117 close csr_dpnt;
118 --
119 -- Bug 8644512: Added code for Child(Other than Immediate) to enable the fields
120 if ( ( l_cont_type in ('1','3','4','5') or l_kr_cont_type in ('3','4','7','1','5','2') ) --Bug 12714266
121 and ( l_itax_law = 'Y' )
122 ) then
123 null;
124 else
125 if ((p_cei_information14 is not null) or (p_cei_information15 is not null)) then
126 fnd_message.set_name('PAY', 'PAY_KR_YEA_CONT_DON_EXP_ENBLD');
127 fnd_message.raise_error;
128 end if;
129 end if;
130 --
131 END IF;
132 end;
133 -- End of Bug 7142612
134 end;
[Home] [Help]