1 package body per_kr_extra_ctr_rules as
2 /* $Header: pekrxctr.pkb 115.5 2003/05/30 06:52:02 nnaresh noship $ */
3 --
4 procedure chk_primary_ctr_flag(
5 p_contact_relationship_id in number,
6 p_person_id in number,
7 p_contact_person_id in number,
8 p_date_start in date,
9 p_date_end in date,
10 p_cont_information1 in varchar2)
11 is
12 l_exists varchar2(1);
13 cursor csr_lck is
14 select null
15 from per_people_f
16 where person_id = p_person_id
17 and effective_start_date = start_date
18 for update of person_id nowait;
19 /* Changed from static to dynamic SQL to remove dependency.
20 cursor csr_exists is
21 select 'Y'
22 from dual
23 where exists(
24 select null
25 from per_contact_relationships
26 where person_id = p_person_id
27 and contact_person_id = p_contact_person_id
28 and contact_relationship_id <> p_contact_relationship_id
29 and cont_information1 = 'Y'
30 and nvl(date_end, hr_general.end_of_time) >= nvl(p_date_start, hr_general.start_of_time)
31 and nvl(date_start, hr_general.start_of_time) <= nvl(p_date_end, hr_general.end_of_time));
32 */
33 type csr is ref cursor;
34 csr_exists csr;
35 begin
36 if p_cont_information1 = 'Y' then
37 --
38 -- Lock first person record to guarantee uniqueness.
39 --
40 open csr_lck;
41 close csr_lck;
42 --
43 -- Check whether the contact relationship is unique.
44 --
45 /* Changed from static to dynamic SQL to remove dependency.
46 open csr_exists;
47 */
48 begin
49 open csr_exists for
50 'select ''Y''
51 from dual
52 where exists(
53 select null
54 from per_contact_relationships
55 where person_id = :p_person_id
56 and contact_person_id = :p_contact_person_id
57 and contact_relationship_id <> :p_contact_relationship_id
58 and cont_information1 = ''Y''
59 and nvl(date_end, hr_general.end_of_time) >= nvl(:p_date_start, hr_general.start_of_time)
60 and nvl(date_start, hr_general.start_of_time) <= nvl(:p_date_end, hr_general.end_of_time))'
61 using p_person_id, p_contact_person_id, p_contact_relationship_id, p_date_start, p_date_end;
62 exception
63 --
64 -- Above open statement will raise error if column "CONT_INFORMATION1" does not exist.
65 -- No need to close the cursor because the cursor is not opened in case of error.
66 --
67 when others then
68 return;
69 end;
70 fetch csr_exists into l_exists;
71 if csr_exists%FOUND then
72 close csr_exists;
73 fnd_message.set_name('PAY', 'PER_KR_CTR_PRIMARY_CTR_FLAG');
74 fnd_message.raise_error;
75 end if;
76 close csr_exists;
77 end if;
78 end chk_primary_ctr_flag;
79 --
80 end per_kr_extra_ctr_rules;
[Home] [Help]