[Home] [Help]
PACKAGE BODY: APPS.HZ_ACT_UTIL_PUB
Source
1 PACKAGE BODY HZ_ACT_UTIL_PUB AS
2 /* $Header: ARHACUIB.pls 120.1 2005/05/25 14:59:39 dmmehta noship $ */
3
4 function get_active_act_site_use(
5 p_act_site_id IN NUMBER) RETURN VARCHAR2 IS
6
7 l_site_use_purpose VARCHAR2(100);
8 l_top5_site_use_purposes VARCHAR2(2000) := '';
9 l_count NUMBER := 0;
10
11 cursor c_site_use_purposes (l_act_site_id IN NUMBER) is
12 select al.MEANING
13 from hz_cust_acct_sites asite,
14 hz_cust_site_uses asu,
15 ar_lookups al
16 where asite.cust_acct_site_id = l_act_site_id
17 and asu.status = 'A'
18 and asu.cust_acct_site_id = asite.cust_acct_site_id
19 and al.lookup_type = 'SITE_USE_CODE'
20 and al.lookup_code = asu.site_use_code
21 order by primary_flag DESC;
22
23 BEGIN
24
25 OPEN c_site_use_purposes(p_act_site_id);
26 LOOP
27 FETCH c_site_use_purposes INTO l_site_use_purpose;
28
29 IF c_site_use_purposes%NOTFOUND THEN
30 EXIT;
31 END IF;
32
33
34 IF l_count = 5 THEN
35 l_top5_site_use_purposes := concat(l_top5_site_use_purposes, ', ... ');
36 EXIT;
37 END IF;
38
39 IF l_top5_site_use_purposes is not null THEN
40 l_top5_site_use_purposes := concat(l_top5_site_use_purposes, ', ');
41 END IF;
42
43 l_top5_site_use_purposes := concat(l_top5_site_use_purposes, l_site_use_purpose);
44
45 l_count := l_count + 1;
46 END LOOP;
47 CLOSE c_site_use_purposes;
48
49 RETURN l_top5_site_use_purposes;
50
51
52 end get_active_act_site_use;
53
54 function get_all_act_site_use(
55 p_act_site_id IN NUMBER) RETURN VARCHAR2 IS
56
57 l_site_use_purpose VARCHAR2(100);
58 l_top5_site_use_purposes VARCHAR2(2000) := '';
59 l_count NUMBER := 0;
60
61 cursor c_site_use_purposes (l_act_site_id IN NUMBER) is
62 select al.MEANING
63 from hz_cust_acct_sites asite,
64 hz_cust_site_uses asu,
65 ar_lookups al
66 where asite.cust_acct_site_id = l_act_site_id
67 and asu.cust_acct_site_id = asite.cust_acct_site_id
68 and al.lookup_type = 'SITE_USE_CODE'
69 and al.lookup_code = asu.site_use_code
70 order by primary_flag DESC, asu.status ASC ;
71
72 BEGIN
73
74 OPEN c_site_use_purposes(p_act_site_id);
75 LOOP
76 FETCH c_site_use_purposes INTO l_site_use_purpose;
77
78 IF c_site_use_purposes%NOTFOUND THEN
79 EXIT;
80 END IF;
81
82
83 IF l_count = 5 THEN
84 l_top5_site_use_purposes := concat(l_top5_site_use_purposes, ', ...');
85 EXIT;
86 END IF;
87
88 IF l_top5_site_use_purposes is not null THEN
89 l_top5_site_use_purposes := concat(l_top5_site_use_purposes, ', ');
90 END IF;
91
92 l_top5_site_use_purposes := concat(l_top5_site_use_purposes, l_site_use_purpose);
93
94 l_count := l_count + 1;
95 END LOOP;
96 CLOSE c_site_use_purposes;
97
98 RETURN l_top5_site_use_purposes;
99
100 end get_all_act_site_use;
101
102 function get_location_id (
103 p_act_site_id IN NUMBER) RETURN NUMBER IS
104
105 l_location_id HZ_PARTY_SITES.location_id%type := NULL;
106 begin
107
108 select ps.location_id into l_location_id
109 from hz_party_sites ps, hz_cust_acct_sites asite
110 where ps.party_site_id = asite.party_site_id
111 and asite.cust_acct_site_id = p_act_site_id;
112
113 RETURN l_location_id;
114
115 exception
116 when no_data_found then
117 return null;
118 when others then return null;
119
120
121 end get_location_id;
122
123 function get_act_contact_roles(
124 p_cust_account_role_id IN NUMBER) RETURN VARCHAR2 IS
125
126 l_contact_role VARCHAR2(100);
127 l_top5_contact_roles VARCHAR2(2000) := '';
128 l_count NUMBER := 0;
129
130 -- Account CPUI : Modify to get roles for INACTIVE contact also
131 cursor c_get_act_contact_roles (l_cust_account_role_id IN NUMBER) is
132 select al.MEANING
133 from --hz_cust_account_roles roles,
134 hz_role_responsibility role_type,
135 ar_lookups al
136 where role_type.cust_account_role_id = l_cust_account_role_id
137 -- and roles.cust_account_role_id = role_type.cust_account_role_id
138 and al.lookup_type = 'SITE_USE_CODE'
139 and al.lookup_code = role_type.responsibility_type
140 order by role_type.primary_flag DESC;
141
142 BEGIN
143
144 OPEN c_get_act_contact_roles(p_cust_account_role_id);
145 LOOP
146 FETCH c_get_act_contact_roles INTO l_contact_role;
147
148 IF c_get_act_contact_roles%NOTFOUND THEN
149 EXIT;
150 END IF;
151
152
153 IF l_count = 5 THEN
154 l_top5_contact_roles := concat(l_top5_contact_roles, ', ...');
155 EXIT;
156 END IF;
157
158 IF l_top5_contact_roles is not null THEN
159 l_top5_contact_roles := concat(l_top5_contact_roles, ', ');
160 END IF;
161
162 l_top5_contact_roles := concat(l_top5_contact_roles, l_contact_role);
163
164 l_count := l_count + 1;
165 END LOOP;
166 CLOSE c_get_act_contact_roles;
167
168 RETURN l_top5_contact_roles;
169
170
171 end get_act_contact_roles;
172
173
174 END HZ_ACT_UTIL_PUB;