[Home] [Help]
PACKAGE BODY: APPS.HZ_PARTY_SEARCH_BO_PUB
Source
1 PACKAGE BODY HZ_PARTY_SEARCH_BO_PUB AS
2 /*$Header: ARHDBOSB.pls 120.2 2007/02/23 11:37:58 rarajend ship $ */
3
4 PROCEDURE find_party_bos (
5 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
6 p_within_os IN VARCHAR2,
7 p_rule_id IN NUMBER,
8 p_search_attr_obj IN HZ_SEARCH_ATTR_OBJ_TBL,
9 p_party_status IN VARCHAR2,
10 p_restrict_sql IN VARCHAR2,
11 p_match_type IN VARCHAR2,
12 x_search_results_obj OUT NOCOPY HZ_MATCHED_PARTY_OBJ_TBL,
13 x_return_status OUT NOCOPY VARCHAR2,
14 x_msg_count OUT NOCOPY NUMBER,
15 x_msg_data OUT NOCOPY VARCHAR2
16 )
17 IS
18 --l_search_results_obj_tbl HZ_MATCHED_PARTY_OBJ_TBL;
19 l_search_attr_obj HZ_SEARCH_ATTR_OBJ_TBL;
20 l_return_status VARCHAR2(1);
21 l_msg_count NUMBER;
22 l_msg_data VARCHAR2(2000);
23 l_search_ctx_id NUMBER;
24 l_num_matches NUMBER;
25 l_count NUMBER;
26 l_restrict_sql VARCHAR2(2000);
27 l_within_sql VARCHAR2(2000);
28 l_api_name VARCHAR2(80) := 'FIND_PARTIES';
29
30
31 -- Cursor to select all the matching parties without 'within' clause
32 CURSOR c_matched_party_tbl (l_search_ctx_id IN NUMBER) IS
33 SELECT HZ_MATCHED_PARTY_OBJ (
34 hzp.party_id
35 ,mpgt.score
36 ,hzp.party_number
37 ,hzp.party_name
38 ,hzp.party_type
39 ,hzp.address1
40 ,hzp.address2
41 ,hzp.address3
42 ,hzp.address4
43 ,hzp.city
44 ,hzp.state
45 ,hzp.country
46 ,hzp.primary_phone_country_code
47 ,hzp.primary_phone_area_code
48 ,hzp.primary_phone_number
49 ,hzp.email_address
50 ,CAST (MULTISET (
51 SELECT HZ_ORIG_SYS_REF_OBJ(
52 NULL, --P_ACTION_TYPE,
53 ORIG_SYSTEM_REF_ID,
54 ORIG_SYSTEM,
55 ORIG_SYSTEM_REFERENCE,
56 HZ_EXTRACT_BO_UTIL_PVT.get_parent_object_type(OWNER_TABLE_NAME,OWNER_TABLE_ID),
57 OWNER_TABLE_ID,
58 STATUS,
59 REASON_CODE,
60 OLD_ORIG_SYSTEM_REFERENCE,
61 START_DATE_ACTIVE,
62 END_DATE_ACTIVE,
63 PROGRAM_UPDATE_DATE,
64 CREATED_BY_MODULE,
65 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
66 CREATION_DATE,
67 LAST_UPDATE_DATE,
68 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY),
69 ATTRIBUTE_CATEGORY,
70 ATTRIBUTE1,
71 ATTRIBUTE2,
72 ATTRIBUTE3,
73 ATTRIBUTE4,
74 ATTRIBUTE5,
75 ATTRIBUTE6,
76 ATTRIBUTE7,
77 ATTRIBUTE8,
78 ATTRIBUTE9,
79 ATTRIBUTE10,
80 ATTRIBUTE11,
81 ATTRIBUTE12,
82 ATTRIBUTE13,
83 ATTRIBUTE14,
84 ATTRIBUTE15,
85 ATTRIBUTE16,
86 ATTRIBUTE17,
87 ATTRIBUTE18,
88 ATTRIBUTE19,
89 ATTRIBUTE20)
90 FROM HZ_ORIG_SYS_REFERENCES OSR
91 WHERE OSR.OWNER_TABLE_ID = HZP.PARTY_ID
92 AND OWNER_TABLE_NAME = 'HZ_PARTIES'
93 AND STATUS = 'A') AS HZ_ORIG_SYS_REF_OBJ_TBL)
94 ) AS HZ_MATCHED_PARTY_OBJ_TBL
95 FROM hz_matched_parties_gt mpgt, hz_parties hzp
96 WHERE mpgt.party_id = hzp.party_id
97 AND mpgt.search_context_id = l_search_ctx_id;
98
99 -- Cursor to select all the matching parties with 'within' clause
100 CURSOR c_matched_party_orig_tbl
101 (l_search_ctx_id IN NUMBER, l_within_os IN VARCHAR2) IS
102 SELECT HZ_MATCHED_PARTY_OBJ (
103 hzp.party_id
104 ,mpgt.score
105 ,hzp.party_number
106 ,hzp.party_name
107 ,hzp.party_type
108 ,hzp.address1
109 ,hzp.address2
110 ,hzp.address3
111 ,hzp.address4
112 ,hzp.city
113 ,hzp.state
114 ,hzp.country
115 ,hzp.primary_phone_country_code
116 ,hzp.primary_phone_area_code
117 ,hzp.primary_phone_number
118 ,hzp.email_address
119 ,CAST (MULTISET (
120 SELECT HZ_ORIG_SYS_REF_OBJ(
121 NULL, --P_ACTION_TYPE,
122 ORIG_SYSTEM_REF_ID,
123 ORIG_SYSTEM,
124 ORIG_SYSTEM_REFERENCE,
125 HZ_EXTRACT_BO_UTIL_PVT.get_parent_object_type(OWNER_TABLE_NAME,OWNER_TABLE_ID),
126 OWNER_TABLE_ID,
127 STATUS,
128 REASON_CODE,
129 OLD_ORIG_SYSTEM_REFERENCE,
130 START_DATE_ACTIVE,
131 END_DATE_ACTIVE,
132 PROGRAM_UPDATE_DATE,
133 CREATED_BY_MODULE,
134 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
135 CREATION_DATE,
136 LAST_UPDATE_DATE,
137 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY),
138 ATTRIBUTE_CATEGORY,
139 ATTRIBUTE1,
140 ATTRIBUTE2,
141 ATTRIBUTE3,
142 ATTRIBUTE4,
143 ATTRIBUTE5,
144 ATTRIBUTE6,
145 ATTRIBUTE7,
146 ATTRIBUTE8,
147 ATTRIBUTE9,
148 ATTRIBUTE10,
149 ATTRIBUTE11,
150 ATTRIBUTE12,
151 ATTRIBUTE13,
152 ATTRIBUTE14,
153 ATTRIBUTE15,
154 ATTRIBUTE16,
155 ATTRIBUTE17,
156 ATTRIBUTE18,
157 ATTRIBUTE19,
158 ATTRIBUTE20)
159 FROM HZ_ORIG_SYS_REFERENCES OSR
160 WHERE OSR.OWNER_TABLE_ID = HZP.PARTY_ID
161 AND OWNER_TABLE_NAME = 'HZ_PARTIES'
162 AND OSR.ORIG_SYSTEM = l_within_os
163 AND STATUS = 'A') AS HZ_ORIG_SYS_REF_OBJ_TBL)
164 ) AS HZ_MATCHED_PARTY_OBJ_TBL
165 FROM hz_matched_parties_gt mpgt, hz_parties hzp
166 WHERE mpgt.party_id = hzp.party_id
167 AND mpgt.search_context_id = l_search_ctx_id;
168
169 BEGIN
170
171 -- initialize API return status to success.
172 x_return_status := FND_API.G_RET_STS_SUCCESS;
173
174 -- Initialize message list if p_init_msg_list is set to TRUE
175 IF FND_API.to_Boolean(p_init_msg_list) THEN
176 FND_MSG_PUB.initialize;
177 END IF;
178
179 -- Check if the 'p_within_os' parameter passed has a value
180 IF p_within_os IS NOT NULL THEN
181 l_within_sql := ' EXISTS (SELECT 1 FROM hz_orig_sys_references o
182 WHERE o.orig_system = '''||p_within_os||'''
183 AND o.owner_table_name = ''HZ_PARTIES''
184 AND o.owner_table_id = stage.party_id)';
185
186 -- If restrict sql has a value
187 IF p_restrict_sql IS NOT NULL THEN
188 -- Concatenate the passed in restrict sql with the with clause
189 l_restrict_sql := p_restrict_sql || ' AND ' ||l_within_sql;
190 ELSE
191 -- Restrict sql is just the within clause
192 l_restrict_sql := l_within_sql;
193 END IF;
194
195 ELSE -- p_within_os is passed as null
196 l_restrict_sql := p_restrict_sql;
197 END IF;
198
199 -- Get the count of passed in search attributes
200 l_count := p_search_attr_obj.COUNT;
201
202 -- Initialze a new search object table with 20 null values
203 l_search_attr_obj := HZ_SEARCH_ATTR_OBJ_TBL();
204 FOR I IN 1..20 LOOP
205 l_search_attr_obj.extend;
206 l_search_attr_obj(i) := HZ_SEARCH_PARTY_OBJ(NULL,NULL);
207 END LOOP;
208
209 -- Loop through the count of search attributes passed in
210 -- and populate the new search object table created.
211 FOR I IN 1..l_count LOOP
212 -- If the count of passed in attributes is more than 20
213 -- exit and ignore the rest.
214 EXIT WHEN I >20;
215 l_search_attr_obj(i).ATTRIBUTE_NAME := p_search_attr_obj(i).ATTRIBUTE_NAME;
216 l_search_attr_obj(i).ATTRIBUTE_VALUE := p_search_attr_obj(i).ATTRIBUTE_VALUE;
217 -- IF one of the attribute name is Party Type and attribute value
218 -- is Person, then we need to populate and pass 'FIND_PERSONS' API.
219 IF l_search_attr_obj(i).ATTRIBUTE_NAME = 'PARTY.PARTY_TYPE' AND
220 l_search_attr_obj(i).ATTRIBUTE_VALUE = 'PERSON' THEN
221 l_api_name := 'FIND_PERSONS';
222 END IF;
223 END LOOP;
224
225 -- Call the Party Search Main API.
226 HZ_PARTY_SEARCH.call_api_dynamic_names (
227 p_init_msg_list => p_init_msg_list,
228 p_rule_id => p_rule_id,
229 p_attrib_name1 => l_search_attr_obj(1).attribute_name,
230 p_attrib_name2 => l_search_attr_obj(2).attribute_name,
231 p_attrib_name3 => l_search_attr_obj(3).attribute_name,
232 p_attrib_name4 => l_search_attr_obj(4).attribute_name,
233 p_attrib_name5 => l_search_attr_obj(5).attribute_name,
234 p_attrib_name6 => l_search_attr_obj(6).attribute_name,
235 p_attrib_name7 => l_search_attr_obj(7).attribute_name,
236 p_attrib_name8 => l_search_attr_obj(8).attribute_name,
237 p_attrib_name9 => l_search_attr_obj(9).attribute_name,
238 p_attrib_name10 => l_search_attr_obj(10).attribute_name,
239 p_attrib_name11 => l_search_attr_obj(11).attribute_name,
240 p_attrib_name12 => l_search_attr_obj(12).attribute_name,
241 p_attrib_name13 => l_search_attr_obj(13).attribute_name,
242 p_attrib_name14 => l_search_attr_obj(14).attribute_name,
243 p_attrib_name15 => l_search_attr_obj(15).attribute_name,
244 p_attrib_name16 => l_search_attr_obj(16).attribute_name,
245 p_attrib_name17 => l_search_attr_obj(17).attribute_name,
246 p_attrib_name18 => l_search_attr_obj(18).attribute_name,
247 p_attrib_name19 => l_search_attr_obj(19).attribute_name,
248 p_attrib_name20 => l_search_attr_obj(20).attribute_name,
249 p_attrib_val1 => l_search_attr_obj(1).attribute_value,
250 p_attrib_val2 => l_search_attr_obj(2).attribute_value,
251 p_attrib_val3 => l_search_attr_obj(3).attribute_value,
252 p_attrib_val4 => l_search_attr_obj(4).attribute_value,
253 p_attrib_val5 => l_search_attr_obj(5).attribute_value,
254 p_attrib_val6 => l_search_attr_obj(6).attribute_value,
255 p_attrib_val7 => l_search_attr_obj(7).attribute_value,
256 p_attrib_val8 => l_search_attr_obj(8).attribute_value,
257 p_attrib_val9 => l_search_attr_obj(9).attribute_value,
258 p_attrib_val10 => l_search_attr_obj(10).attribute_value,
259 p_attrib_val11 => l_search_attr_obj(11).attribute_value,
260 p_attrib_val12 => l_search_attr_obj(12).attribute_value,
261 p_attrib_val13 => l_search_attr_obj(13).attribute_value,
262 p_attrib_val14 => l_search_attr_obj(14).attribute_value,
263 p_attrib_val15 => l_search_attr_obj(15).attribute_value,
264 p_attrib_val16 => l_search_attr_obj(16).attribute_value,
265 p_attrib_val17 => l_search_attr_obj(17).attribute_value,
266 p_attrib_val18 => l_search_attr_obj(18).attribute_value,
267 p_attrib_val19 => l_search_attr_obj(19).attribute_value,
268 p_attrib_val20 => l_search_attr_obj(20).attribute_value,
269 p_restrict_sql => l_restrict_sql,
270 p_api_name => l_api_name,
271 p_match_type => p_match_type,
272 p_party_id => NULL,
273 p_search_merged => p_party_status,
274 x_search_ctx_id => l_search_ctx_id,
275 x_num_matches => l_num_matches,
276 x_return_status => l_return_status,
277 x_msg_count => l_msg_count,
278 x_msg_data => l_msg_data
279 );
280 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
281
282 RAISE FND_API.G_EXC_ERROR;
283 -- IF this API returns successfully then populate the results
284 -- into the output object table.
285 ELSIF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
286
287 -- check if p_within_os parameter has a value
288 IF p_within_os IS NULL THEN
289
290 OPEN c_matched_party_tbl(l_search_ctx_id);
291 -- Populate the l_search_results_obj_tbl table without 'within' clause
292 FETCH c_matched_party_tbl BULK COLLECT INTO x_search_results_obj;
293 CLOSE c_matched_party_tbl;
294
295 ELSIF p_within_os IS NOT NULL THEN
296
297 OPEN c_matched_party_orig_tbl(l_search_ctx_id, p_within_os);
298 -- Populate the l_search_results_obj_tbl table with 'within' clause
299 FETCH c_matched_party_orig_tbl BULK COLLECT INTO x_search_results_obj;
300 CLOSE c_matched_party_orig_tbl;
301
302 END IF;
303 END IF;
304 EXCEPTION
305 WHEN fnd_api.g_exc_error THEN
306 x_return_status := fnd_api.g_ret_sts_error;
307 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
308 p_count => x_msg_count,
309 p_data => x_msg_data);
310 WHEN fnd_api.g_exc_unexpected_error THEN
311 x_return_status := fnd_api.g_ret_sts_unexp_error;
312 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
313 p_count => x_msg_count,
314 p_data => x_msg_data);
315 WHEN OTHERS THEN
316 x_return_status := fnd_api.g_ret_sts_unexp_error;
317 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
318 fnd_message.set_token('ERROR' ,SQLERRM);
319 fnd_msg_pub.add;
320 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
321 p_count => x_msg_count,
322 p_data => x_msg_data);
323
324 END find_party_bos;
325
326 END HZ_PARTY_SEARCH_BO_PUB;