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;