1 PACKAGE BODY HZ_POP_MRULE_SET_ATTR_V2PUB AS
2 /*$Header: ARHMSARB.pls 120.0 2005/05/25 21:08:26 achung noship $ */
3
4
5 /**
6 * PROCEDURE POP_MRULE_SET_ATTRIBUTES
7 *
8 * DESCRIPTION
9 * This procedure populates all the primary and secondary attributes
10 * of a condition match rule into the set.
11 *
12 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
13 *
14 * ARGUMENTS
15 * IN:
16 * p_mrule_set_id Match rule set id.
17 *
18 * IN/OUT:
19 * p_mrule_set_id IN NUMBER
20 * p_cond_mrule_id IN NUMBER
21 * OUT:
22 *
23 * NOTES
24 *
25 * MODIFICATION HISTORY
26 *
27 *
28 *
29 */
30 PROCEDURE update_prim_display_order(p_mrule_set_id IN NUMBER);
31 PROCEDURE update_sec_display_order(p_mrule_set_id IN NUMBER);
32
33 PROCEDURE pop_mrule_set_attributes(p_mrule_set_id IN NUMBER)
34 IS
35 CURSOR c_insert_prim IS ( SELECT p.ATTRIBUTE_ID FROM
36 (SELECT unique p1.ATTRIBUTE_ID
37 FROM HZ_MATCH_RULE_PRIMARY p1
38 WHERE p1.match_rule_id in (Select unique condition_match_rule_id
39 from hz_match_rule_conditions
40 where match_rule_set_id = p_mrule_set_id)
41 UNION
42 (SELECT unique attribute_id
43 from hz_match_rule_conditions
44 where match_rule_set_id = p_mrule_set_id
45 and attribute_id is not null
46 )
47 ) p
48 WHERE NOT EXISTS (
49 SELECT 1
50 FROM HZ_MATCH_RULE_PRIMARY p1
51 WHERE p1.match_rule_id = p_mrule_set_id
52 AND p1.ATTRIBUTE_ID = p.ATTRIBUTE_ID
53 )
54 );
55 CURSOR c_delete_prim IS SELECT primary_attribute_id
56 FROM HZ_MATCH_RULE_PRIMARY
57 WHERE match_rule_id = p_mrule_set_id
58 AND ATTRIBUTE_ID IN (SELECT unique p1.ATTRIBUTE_ID
59 FROM HZ_MATCH_RULE_PRIMARY p1
60 WHERE p1.match_rule_id = p_mrule_set_id
61 MINUS
62 (SELECT unique p1.ATTRIBUTE_ID
63 FROM HZ_MATCH_RULE_PRIMARY p1
64 WHERE p1.match_rule_id in (Select unique condition_match_rule_id
65 from hz_match_rule_conditions
66 where match_rule_set_id = p_mrule_set_id)
67 UNION
68 (SELECT unique attribute_id
69 from hz_match_rule_conditions
70 where match_rule_set_id = p_mrule_set_id
71 and attribute_id is not null
72 )
73 )
74 );
75 CURSOR c_insert_sec IS ( SELECT s1.ATTRIBUTE_ID
76 FROM HZ_MATCH_RULE_SECONDARY s1
77 WHERE s1.match_rule_id in (Select unique condition_match_rule_id
78 from hz_match_rule_conditions
79 where match_rule_set_id = p_mrule_set_id)
80 AND NOT EXISTS(SELECT 1 FROM HZ_MATCH_RULE_SECONDARY s2
81 WHERE s2.match_rule_id = p_mrule_set_id
82 AND s2.ATTRIBUTE_ID = s1.ATTRIBUTE_ID
83 )
84 );
85
86 CURSOR c_delete_sec IS SELECT secondary_attribute_id
87 FROM HZ_MATCH_RULE_SECONDARY
88 WHERE match_rule_id = p_mrule_set_id
89 AND ATTRIBUTE_ID IN (SELECT unique s1.ATTRIBUTE_ID
90 FROM HZ_MATCH_RULE_SECONDARY s1
91 WHERE s1.match_rule_id = p_mrule_set_id
92 MINUS
93 SELECT unique s1.ATTRIBUTE_ID
94 FROM HZ_MATCH_RULE_SECONDARY s1
95 WHERE s1.match_rule_id in (Select unique condition_match_rule_id
96 from hz_match_rule_conditions
97 where match_rule_set_id = p_mrule_set_id)
98 );
99
100 TYPE t_attr_id_list IS TABLE OF NUMBER index by binary_integer;
101 v_attr_id_list t_attr_id_list;
102 x_primary_attribute_id NUMBER;
103 x_secondary_attribute_id NUMBER;
104 l_object_version_number NUMBER;
105
106 BEGIN
107
108 l_object_version_number :=1;
109
110 /* Populate the primary attributes */
111
112 OPEN c_insert_prim;
113 FETCH c_insert_prim BULK COLLECT INTO v_attr_id_list;
114 CLOSE c_insert_prim;
115
116 IF v_attr_id_list.COUNT >0 THEN
117 FOR i IN v_attr_id_list.FIRST..v_attr_id_list.LAST
118 LOOP
119 HZ_MATCH_RULE_PRIMARY_PKG.INSERT_ROW(x_primary_attribute_id, --px_PRIMARY_ATTRIBUTE_ID
120 p_mrule_set_id, --p_MATCH_RULE_ID
121 v_attr_id_list(i), --p_ATTRIBUTE_ID
122 NULL, --p_ACTIVE_FLAG
123 NULL, --p_FILTER_FLAG
124 HZ_UTILITY_V2PUB.CREATED_BY, --p_CREATED_BY
125 HZ_UTILITY_V2PUB.CREATION_DATE, --p_CREATION_DATE
126 HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN, --p_LAST_UPDATE_LOGIN
127 HZ_UTILITY_V2PUB.LAST_UPDATE_DATE, --p_LAST_UPDATE_DATE
128 HZ_UTILITY_V2PUB.LAST_UPDATED_BY, --p_LAST_UPDATED_BY
129 l_object_version_number --p_OBJECT_VERSION_NUMBER
130 );
131 x_primary_attribute_id := NULL;
132 END LOOP;
133 END IF;
134
135 /* Delete the unnecessary primary attributes */
136
137 OPEN c_delete_prim;
138 FETCH c_delete_prim BULK COLLECT INTO v_attr_id_list;
139 CLOSE c_delete_prim;
140
141 IF v_attr_id_list.COUNT >0 THEN
142 FORALL i IN v_attr_id_list.FIRST..v_attr_id_list.LAST
143 DELETE FROM HZ_MATCH_RULE_PRIMARY
144 WHERE PRIMARY_ATTRIBUTE_ID = v_attr_id_list(i);
145 END IF;
146 /* Update the primary attributes display order */
147 update_prim_display_order(p_mrule_set_id);
148
149 /* Populate the secondary attributes */
150
151 OPEN c_insert_sec;
152 FETCH c_insert_sec BULK COLLECT INTO v_attr_id_list;
153 CLOSE c_insert_sec;
154
155 IF v_attr_id_list.COUNT >0 THEN
156 FOR i IN v_attr_id_list.FIRST..v_attr_id_list.LAST
157 LOOP
158 HZ_MATCH_RULE_SECONDARY_PKG.INSERT_ROW(x_secondary_attribute_id, --px_SECONDARY_ATTRIBUTE_ID
159 p_mrule_set_id, --p_MATCH_RULE_ID
160 v_attr_id_list(i), --p_ATTRIBUTE_ID
161 0, --p_SCORE
162 NULL, --p_ACTIVE_FLAG
163 HZ_UTILITY_V2PUB.CREATED_BY, --p_CREATED_BY
164 HZ_UTILITY_V2PUB.CREATION_DATE, --p_CREATION_DATE
165 HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN, --p_LAST_UPDATE_LOGIN
166 HZ_UTILITY_V2PUB.LAST_UPDATE_DATE, --p_LAST_UPDATE_DATE
167 HZ_UTILITY_V2PUB.LAST_UPDATED_BY, --p_LAST_UPDATED_BY
168 l_object_version_number --p_OBJECT_VERSION_NUMBER
169 );
170 x_secondary_attribute_id := NULL;
171 END LOOP;
172 END IF;
173
174 /* Delete the unnecessary secondary attributes */
175
176 OPEN c_delete_sec;
177 FETCH c_delete_sec BULK COLLECT INTO v_attr_id_list;
178 CLOSE c_delete_sec;
179
180 IF v_attr_id_list.COUNT >0 THEN
181 FORALL i IN v_attr_id_list.FIRST..v_attr_id_list.LAST
182 DELETE FROM HZ_MATCH_RULE_SECONDARY
183 WHERE SECONDARY_ATTRIBUTE_ID = v_attr_id_list(i);
184 END IF;
185
186 /* Update the secondary attributes display order */
187 update_sec_display_order(p_mrule_set_id);
188
189 END;
190
191 PROCEDURE update_prim_display_order(p_mrule_set_id NUMBER)
192 IS
193 l_g_miss_num NUMBER;
194 TYPE t_prim_attr_list IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
195
196 v_attr_id_list t_prim_attr_list;
197 v_attr_order_list t_prim_attr_list;
198
199 CURSOR c_prim_attr IS
200 SELECT unique attribute_id,0 display_order
201 from hz_match_rule_conditions
202 where match_rule_set_id = p_mrule_set_id
203 and attribute_id is not null
204 UNION
205 SELECT p1.ATTRIBUTE_ID,min(nvl(p1.display_order,l_g_miss_num)) display_order
206 FROM HZ_MATCH_RULE_PRIMARY p1
207 WHERE EXISTS (Select 1
208 from hz_match_rule_conditions cond1
209 where cond1.match_rule_set_id = p_mrule_set_id
210 and cond1.condition_match_rule_id = p1.match_rule_id
211 )
212 AND NOT EXISTS (SELECT 1 from hz_match_rule_conditions cond2
213 where cond2.match_rule_set_id = p_mrule_set_id
214 and cond2.attribute_id = p1.attribute_id
215 )
216 group by p1.ATTRIBUTE_ID;
217
218
219 BEGIN
220 l_g_miss_num := FND_API.G_MISS_NUM;
221
222 OPEN c_prim_attr;
223 FETCH c_prim_attr BULK COLLECT INTO v_attr_id_list,v_attr_order_list;
224 CLOSE c_prim_attr;
225 FORALL i IN v_attr_id_list.FIRST..v_attr_id_list.LAST
226 UPDATE HZ_MATCH_RULE_PRIMARY
227 SET DISPLAY_ORDER = decode(v_attr_order_list(i),l_g_miss_num,null,v_attr_order_list(i))
228 WHERE match_rule_id = p_mrule_set_id
229 AND attribute_id = v_attr_id_list(i);
230
231 END;
232
233
234 PROCEDURE update_sec_display_order(p_mrule_set_id IN NUMBER)
235 IS
236 l_g_miss_num NUMBER;
237 TYPE t_sec_attr_list IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
238 v_attr_id_list t_sec_attr_list;
239 v_attr_order_list t_sec_attr_list;
240
241
242 CURSOR c_sec_attr IS
243 SELECT s1.ATTRIBUTE_ID,min(nvl(s1.display_order,l_g_miss_num)) display_order
244 FROM HZ_MATCH_RULE_SECONDARY s1
245 WHERE EXISTS (Select 1
246 from hz_match_rule_conditions cond1
247 where cond1.match_rule_set_id = p_mrule_set_id
248 and cond1.condition_match_rule_id = s1.match_rule_id
249 )
250 group by s1.ATTRIBUTE_ID;
251
252
253 BEGIN
254 l_g_miss_num := FND_API.G_MISS_NUM;
255
256 OPEN c_sec_attr;
257 FETCH c_sec_attr BULK COLLECT INTO v_attr_id_list,v_attr_order_list;
258 CLOSE c_sec_attr;
259
260 FORALL i IN v_attr_id_list.FIRST..v_attr_id_list.LAST
261 UPDATE HZ_MATCH_RULE_SECONDARY
262 SET DISPLAY_ORDER = decode(v_attr_order_list(i),l_g_miss_num,null,v_attr_order_list(i))
263 WHERE match_rule_id = p_mrule_set_id
264 AND attribute_id = v_attr_id_list(i);
265
266
267 END;
268
269
270 END HZ_POP_MRULE_SET_ATTR_V2PUB;
271
[Home] [Help]