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