[Home] [Help]
PACKAGE BODY: APPS.XLE_CONTACT_GRP
Source
1 PACKAGE BODY XLE_CONTACT_GRP AS
2 /* $Header: xlecontb.pls 120.0 2005/10/27 14:12:43 bsilveir noship $ */
3
4 FUNCTION concat_contact_roles (p_contact_party_id IN NUMBER,
5 p_le_etb_party_id IN NUMBER)
6
7 RETURN VARCHAR2 IS
8
9 CURSOR c_get_role IS
10 SELECT lkup.meaning role_meaning
11 FROM xle_lookups lkup,
12 xle_contact_legal_roles role
13 WHERE role.lookup_code = lkup.lookup_code
14 AND role.contact_party_id = p_contact_party_id
15 AND role.le_etb_party_id = p_le_etb_party_id
16 AND role.lookup_type = 'XLE_CONTACT_ROLE'
17 AND lkup.enabled_flag = 'Y'
18 AND TRUNC(SYSDATE) BETWEEN Nvl(lkup.start_date_active,TRUNC(SYSDATE)) AND Nvl(Lkup.end_date_active,TRUNC(SYSDATE));
19
20 l_role_string VARCHAR2(2000) := NULL;
21
22 TYPE string_tbl IS TABLE OF VARCHAR2(30);
23 l_roles_tbl string_tbl;
24 l_max_rows NUMBER;
25
26
27 BEGIN
28
29 OPEN c_get_role;
30 FETCH c_get_role BULK COLLECT INTO l_roles_tbl;
31 CLOSE c_get_role;
32
33 l_max_rows := l_roles_tbl.COUNT;
34
35 FOR l_index IN 1..l_max_rows
36 LOOP
37 IF l_index > 10
38 THEN
39 EXIT;
40 END IF;
41
42 IF l_role_string IS NULL
43 THEN
44 l_role_string := l_roles_tbl(l_index);
45 ELSE
46 l_role_string := l_role_string || ', ' || l_roles_tbl(l_index);
47 END IF;
48
49 END LOOP;
50
51 RETURN l_role_string;
52 END concat_contact_roles;
53
54
55 PROCEDURE end_contact_roles (p_contact_party_id IN NUMBER,
56 p_le_etb_party_id IN NUMBER,
57 p_commit IN VARCHAR2,
58 x_return_status OUT NOCOPY VARCHAR2,
59 x_msg_count OUT NOCOPY NUMBER,
60 x_msg_data OUT NOCOPY VARCHAR2)
61 IS
62 BEGIN
63 x_return_status := FND_API.G_RET_STS_SUCCESS;
64
65 IF p_le_etb_party_id IS NULL OR p_contact_party_id IS NULL
66 THEN
67 x_return_status := FND_API.G_RET_STS_ERROR;
68 x_msg_data := 'Mandatory parameters not passed to end_contact_roles';
69 ELSE
70 UPDATE xle_contact_legal_roles
71 SET effective_to = SYSDATE
72 WHERE contact_party_id = p_contact_party_id
73 AND le_etb_party_id = p_le_etb_party_id
74 AND effective_to IS NULL;
75 END IF;
76
77 IF FND_API.To_Boolean( p_commit )
78 THEN
79 COMMIT WORK;
80 END IF;
81
82 EXCEPTION
83 WHEN OTHERS THEN
84 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
85 FND_MSG_PUB.Count_And_Get (
86 p_count => x_msg_count,
87 p_data => x_msg_data);
88
89 END end_contact_roles;
90
91
92 END XLE_CONTACT_GRP;