1 PACKAGE BODY per_jp_contact_relationships AS
2 /* $Header: pejpcrlh.pkb 115.2 2002/05/30 23:09:00 pkm ship $ */
3 -- ----------------------------------------------------------------------------
4 -- |----------------------------< chk_si_itax_flag >--------------------------|
5 -- ----------------------------------------------------------------------------
6 -- {Start Of Comments}
7 --
8 -- Description:
9 -- This procedure verifies there is only 1 row that has 'Y' in si_itax_flag
10 -- for a person and a contact person.
11 --
12 -- Prerequisites:
13 -- None.
14 --
15 -- In Parameters:
16 -- Name Reqd Type Description
17 -- p_contact_relationship_id Yes NUMBER Contact Releationship ID.
18 -- p_person_id Yes NUMBER Person ID.
19 -- p_contact_person_id Yes NUMBER Contact Person ID.
20 -- p_cont_information1 Yes VARCHAR2 Social Insurance and Income
21 -- Tax.
22 --
23 -- Post Success:
24 -- Process continues.
25 --
26 -- Post Failure:
27 -- An application error will be raised and process is terminated.
28 --
29 -- Access Status:
30 -- Internal Table Handler Use Only.
31 --
32 -- {End Of Comments}
33 --
34 PROCEDURE chk_si_itax_flag(
35 p_contact_relationship_id NUMBER DEFAULT NULL,
36 p_person_id NUMBER,
37 p_contact_person_id NUMBER,
38 p_cont_information1 VARCHAR2) IS
39 --
40 CURSOR cel_parent_row_exists(
41 p_contact_relationship_id NUMBER,
42 p_person_id NUMBER,
43 p_contact_person_id NUMBER) IS
44 --
45 SELECT 'Y' FROM per_contact_relationships
46 WHERE person_id = p_person_id
47 AND contact_person_id = p_contact_person_id
48 AND (p_contact_relationship_id IS NULL
49 OR (p_contact_relationship_id IS NOT NULL
50 AND contact_relationship_id <> p_contact_relationship_id))
51 AND cont_information1 = 'Y';
52 --
53 l_dummy VARCHAR2(1);
54 --
55 BEGIN
56 --
57 IF p_cont_information1 = 'Y' THEN
58 --
59 OPEN cel_parent_row_exists(p_contact_relationship_id, p_person_id, p_contact_person_id);
60 FETCH cel_parent_row_exists INTO l_dummy;
61 --
62 IF cel_parent_row_exists%FOUND THEN
63 --
64 CLOSE cel_parent_row_exists;
65 --
66 fnd_message.set_name(
67 application => 'PER',
68 name => 'PER_JP_CON_DUP_SI_ITAX_FLAG');
69 --
70 fnd_message.raise_error;
71 --
72 END IF;
73 --
74 CLOSE cel_parent_row_exists;
75 --
76 END IF;
77 --
78 END chk_si_itax_flag;
79 --
80 END per_jp_contact_relationships;