[Home] [Help]
PACKAGE BODY: APPS.EGO_RULES_UTIL_PVT
Source
1 PACKAGE BODY Ego_Rules_Util_PVT AS
2 /* $Header: EGOVRUTB.pls 120.3 2007/06/05 19:36:07 glomeli noship $ */
3 FUNCTION Get_Comma_Separated_Bussentity(X_STR_ID NUMBER) RETURN VARCHAR2 IS
4 l_result VARCHAR2(2500);
5 CURSOR l_cur_entries IS
6 (
7 SELECT e1.USER_DATA_LEVEL_NAME
8 FROM ego_data_level_vl e1, EGO_INCLUDED_BUSINESSENTITIES e2
9 WHERE e1.attr_group_type = 'EGO_ITEMMGMT_GROUP'
10 AND e1.enable_defaulting < > 'N'
11 AND e1.DATA_LEVEL_NAME= e2.BUSINESS_ENTITY
12 AND e2.RULESET_ID=X_STR_ID
13 );
14 l_cur_var l_cur_entries%ROWTYPE;
15 BEGIN
16 OPEN l_cur_entries;
17 LOOP
18 FETCH l_cur_entries INTO l_cur_var;
19 EXIT WHEN l_cur_entries%NOTFOUND;
20 IF l_result is null THEN
21 l_result:=l_cur_var.USER_DATA_LEVEL_NAME;
22 ELSE
23 l_result:=l_result||', '||l_cur_var.USER_DATA_LEVEL_NAME;
24 END IF;
25 END LOOP;
26 CLOSE l_cur_entries;
27 RETURN l_result;
28 END Get_Comma_Separated_Bussentity ;
29
30 FUNCTION Get_Icc(X_ICC_ID NUMBER) RETURN VARCHAR2
31 IS
32 l_result VARCHAR2(2500);
33 BEGIN
34 IF X_ICC_ID is not null THEN
35 SELECT catalog_group INTO l_result FROM ego_catalog_groups_v WHERE catalog_group_id = X_ICC_ID;
36 END IF;
37 RETURN l_result;
38 END Get_Icc;
39
40 FUNCTION Get_Comma_Separated_Rulegroups(X_STR_RULEID NUMBER) RETURN VARCHAR2 IS l_result VARCHAR2(2500);
41 CURSOR l_cur_entries IS
42 (SELECT unique
43 attr_group_name
44 FROM
45 ego_rule_attribute_usages
46 WHERE
47 rule_id = X_STR_RULEID);
48 l_cur_var l_cur_entries%ROWTYPE;
49 BEGIN
50
51 OPEN l_cur_entries;
52 LOOP
53 FETCH l_cur_entries
54 INTO l_cur_var;
55 EXIT
56 WHEN l_cur_entries%NOTFOUND;
57
58 IF l_result IS NULL THEN
59 l_result := l_cur_var.attr_group_name;
60 ELSE
61 l_result := l_result || ', ' || l_cur_var.attr_group_name;
62 END IF;
63
64 END LOOP;
65
66 CLOSE l_cur_entries;
67 RETURN l_result;
68 END Get_Comma_Separated_Rulegroups;
69
70 FUNCTION Get_Comma_Separated_Ruleattrs(X_STR_RULEID NUMBER) RETURN VARCHAR2 IS l_result VARCHAR2(2500);
71 CURSOR l_cur_entries IS
72 (SELECT unique
73 attribute
74 FROM
75 ego_rule_attribute_usages
76 WHERE
77 rule_id = X_STR_RULEID);
78 l_cur_var l_cur_entries%ROWTYPE;
79 BEGIN
80
81 OPEN l_cur_entries;
82 LOOP
83 FETCH l_cur_entries
84 INTO l_cur_var;
85 EXIT WHEN l_cur_entries%NOTFOUND;
86
87 IF l_result IS NULL THEN
88 l_result := l_cur_var.attribute;
89 ELSE
90 l_result := l_result || ', ' || l_cur_var.attribute;
91 END IF;
92
93 END LOOP;
94
95 CLOSE l_cur_entries;
96 RETURN l_result;
97 END Get_Comma_Separated_Ruleattrs;
98
99 FUNCTION Get_AttributeGroupsForRuleSet(X_STR_RULESETID NUMBER) RETURN VARCHAR2 IS l_result VARCHAR2(2500);
100 CURSOR l_cur_entries IS
101 (SELECT unique
102 attr_group_name Assignedtoattrgrp
103 FROM
104 ego_rule_attribute_usages
105 WHERE
106 rule_id IN (SELECT RULE_ID FROM EGO_USER_RULES_B WHERE RULESET_ID = X_STR_RULESETID));
107 l_cur_var l_cur_entries%ROWTYPE;
108 BEGIN
109
110 OPEN l_cur_entries;
111 LOOP
112 FETCH l_cur_entries
113 INTO l_cur_var;
114 EXIT
115 WHEN l_cur_entries%NOTFOUND;
116
117 IF l_result IS NULL THEN
118 l_result := l_cur_var.Assignedtoattrgrp;
119 ELSE
120 l_result := l_result || ', ' || l_cur_var.Assignedtoattrgrp;
121 END IF;
122
123 END LOOP;
124
125 CLOSE l_cur_entries;
126 RETURN l_result;
127 END Get_AttributeGroupsForRuleSet;
128
129
130 FUNCTION Get_AttributeForRuleSet(X_STR_RULESETID NUMBER) RETURN VARCHAR2 IS l_result VARCHAR2(2500);
131 CURSOR l_cur_entries IS
132 (SELECT unique
133 ATTRIBUTE
134 FROM
135 ego_rule_attribute_usages
136 WHERE
137 rule_id IN (SELECT RULE_ID FROM EGO_USER_RULES_B WHERE RULESET_ID = X_STR_RULESETID));
138 l_cur_var l_cur_entries%ROWTYPE;
139 BEGIN
140
141 OPEN l_cur_entries;
142 LOOP
143 FETCH l_cur_entries
144 INTO l_cur_var;
145 EXIT
146 WHEN l_cur_entries%NOTFOUND;
147
148 IF l_result IS NULL THEN
149 l_result := l_cur_var.ATTRIBUTE;
150 ELSE
151 l_result := l_result || ', ' || l_cur_var.ATTRIBUTE;
152 END IF;
153
154 END LOOP;
155
156 CLOSE l_cur_entries;
157 RETURN l_result;
158 END Get_AttributeForRuleSet;
159
160 FUNCTION Get_AssignedBusinessEnitites(X_STR_RULESETID NUMBER) RETURN VARCHAR2 IS l_result VARCHAR2(2500);
161 CURSOR l_cur_entries IS
162 (
163 SELECT
164 e1.USER_DATA_LEVEL_NAME as Assignedtobusentity
165 FROM
166 ego_data_level_vl e1,
167 EGO_RULE_ASSIGNMENTS e2
168 WHERE
169 e1.attr_group_type = 'EGO_ITEMMGMT_GROUP'
170 AND
171 e1.enable_defaulting < > 'N'
172 AND
173 e1.DATA_LEVEL_NAME= e2.BUSINESS_ENTITY
174 AND
175 e2.RULESET_ID = X_STR_RULESETID
176 );
177 l_cur_var l_cur_entries%ROWTYPE;
178 BEGIN
179
180 OPEN l_cur_entries;
181 LOOP
182 FETCH l_cur_entries
183 INTO l_cur_var;
184 EXIT
185 WHEN l_cur_entries%NOTFOUND;
186
187 IF l_result IS NULL THEN
188 l_result := l_cur_var.Assignedtobusentity;
189 ELSE
190 l_result := l_result || ', ' || l_cur_var.Assignedtobusentity;
191 END IF;
192
193 END LOOP;
194
195 CLOSE l_cur_entries;
196 RETURN l_result;
197 END Get_AssignedBusinessEnitites;
198
199 FUNCTION Get_BussentityForRulesets(X_STR_RULESETID NUMBER) RETURN VARCHAR2 IS
200 l_result VARCHAR2(2500);
201 CURSOR l_cur_entries IS
202 (
203 SELECT
204 e1.USER_DATA_LEVEL_NAME as usingbusentity
205 FROM
206 ego_data_level_vl e1,
207 EGO_INCLUDED_BUSINESSENTITIES e2
208 WHERE
209 e1.attr_group_type = 'EGO_ITEMMGMT_GROUP'
210 AND
211 e1.enable_defaulting < > 'N'
212 AND
213 e1.DATA_LEVEL_NAME = e2.BUSINESS_ENTITY
214 AND
215 e2.RULESET_ID = X_STR_RULESETID
216 );
217 l_cur_var l_cur_entries%ROWTYPE;
218 BEGIN
219 OPEN l_cur_entries;
220 LOOP
221 FETCH l_cur_entries INTO l_cur_var;
222 EXIT WHEN l_cur_entries%NOTFOUND;
223 IF l_result is null THEN
224 l_result:=l_cur_var.usingbusentity;
225 ELSE
226 l_result:=l_result||', '||l_cur_var.usingbusentity;
227 END IF;
228 END LOOP;
229 CLOSE l_cur_entries;
230 RETURN l_result;
231 END Get_BussentityForRulesets ;
232
233
234 END Ego_Rules_Util_PVT;