[Home] [Help]
PACKAGE BODY: APPS.HZ_AIA_CUSTOM_PKG
Source
1 PACKAGE BODY HZ_AIA_CUSTOM_PKG AS
2 /* $Header: ARHAIACB.pls 120.0 2007/12/06 18:32:29 awu noship $ */
3 PROCEDURE remove_gmiss(
4 px_org_cust IN OUT NOCOPY HZ_ORG_CUST_BO);
5
6 PROCEDURE remove_gmiss(
7 px_org_cust IN OUT NOCOPY HZ_ORG_CUST_BO) IS
8 BEGIN
9 IF(px_org_cust.organization_obj.duns_number_c = FND_API.G_MISS_CHAR) THEN
10 px_org_cust.organization_obj.duns_number_c := NULL;
11 END IF;
12 IF(px_org_cust.organization_obj.contact_objs IS NOT NULL AND
13 px_org_cust.organization_obj.contact_objs.COUNT > 0) THEN
14 FOR i IN 1..px_org_cust.organization_obj.contact_objs.COUNT LOOP
15 IF(px_org_cust.organization_obj.contact_objs(i).job_title = FND_API.G_MISS_CHAR) THEN
16 px_org_cust.organization_obj.contact_objs(i).job_title := null;
17 END IF;
18 -- clear g_miss_char in person profile object
19 IF(px_org_cust.organization_obj.contact_objs(i).person_profile_obj IS NOT NULL) THEN
20 IF(px_org_cust.organization_obj.contact_objs(i).person_profile_obj.person_pre_name_adjunct = FND_API.G_MISS_CHAR) THEN
21 px_org_cust.organization_obj.contact_objs(i).person_profile_obj.person_pre_name_adjunct:= null;
22 END IF;
23 IF(px_org_cust.organization_obj.contact_objs(i).person_profile_obj.person_middle_name = FND_API.G_MISS_CHAR) THEN
24 px_org_cust.organization_obj.contact_objs(i).person_profile_obj.person_middle_name := null;
25 END IF;
26 IF(px_org_cust.organization_obj.contact_objs(i).person_profile_obj.person_title = FND_API.G_MISS_CHAR) THEN
27 px_org_cust.organization_obj.contact_objs(i).person_profile_obj.person_title := null;
28 END IF;
29 IF(px_org_cust.organization_obj.contact_objs(i).person_profile_obj.known_as = FND_API.G_MISS_CHAR) THEN
30 px_org_cust.organization_obj.contact_objs(i).person_profile_obj.known_as := null;
31 END IF;
32 IF(px_org_cust.organization_obj.contact_objs(i).person_profile_obj.gender = FND_API.G_MISS_CHAR) THEN
33 px_org_cust.organization_obj.contact_objs(i).person_profile_obj.gender := null;
34 END IF;
35 END IF;
36 -- clear g_miss_char in contact's email
37 IF(px_org_cust.organization_obj.contact_objs(i).email_objs IS NOT NULL AND
38 px_org_cust.organization_obj.contact_objs(i).email_objs.COUNT > 0) THEN
39 FOR j IN 1..px_org_cust.organization_obj.contact_objs(i).email_objs.COUNT LOOP
40 IF(px_org_cust.organization_obj.contact_objs(i).email_objs(j).email_address = FND_API.G_MISS_CHAR) THEN
41 px_org_cust.organization_obj.contact_objs(i).email_objs(j).email_address := NULL;
42 END IF;
43 END LOOP;
44 END IF;
45 -- clear g_miss_char in contact's web
46 IF(px_org_cust.organization_obj.contact_objs(i).web_objs IS NOT NULL AND
47 px_org_cust.organization_obj.contact_objs(i).web_objs.COUNT > 0) THEN
48 FOR j IN 1..px_org_cust.organization_obj.contact_objs(i).web_objs.COUNT LOOP
49 IF(px_org_cust.organization_obj.contact_objs(i).web_objs(j).url = FND_API.G_MISS_CHAR) THEN
50 px_org_cust.organization_obj.contact_objs(i).web_objs(j).url := NULL;
51 END IF;
52 END LOOP;
53 END IF;
54 END LOOP;
55 END IF;
56 IF(px_org_cust.organization_obj.party_site_objs IS NOT NULL AND
57 px_org_cust.organization_obj.party_site_objs.COUNT > 0) THEN
58 FOR i IN 1..px_org_cust.organization_obj.party_site_objs.COUNT LOOP
59 IF(px_org_cust.organization_obj.party_site_objs(i).location_obj IS NOT NULL) THEN
60 IF(px_org_cust.organization_obj.party_site_objs(i).location_obj.address2 = FND_API.G_MISS_CHAR) THEN
61 px_org_cust.organization_obj.party_site_objs(i).location_obj.address2 := NULL;
62 END IF;
63 IF(px_org_cust.organization_obj.party_site_objs(i).location_obj.address3 = FND_API.G_MISS_CHAR) THEN
64 px_org_cust.organization_obj.party_site_objs(i).location_obj.address3 := NULL;
65 END IF;
66 IF(px_org_cust.organization_obj.party_site_objs(i).location_obj.address4 = FND_API.G_MISS_CHAR) THEN
67 px_org_cust.organization_obj.party_site_objs(i).location_obj.address4 := NULL;
68 END IF;
69 IF(px_org_cust.organization_obj.party_site_objs(i).location_obj.city = FND_API.G_MISS_CHAR) THEN
70 px_org_cust.organization_obj.party_site_objs(i).location_obj.city := NULL;
71 END IF;
72 IF(px_org_cust.organization_obj.party_site_objs(i).location_obj.county = FND_API.G_MISS_CHAR) THEN
73 px_org_cust.organization_obj.party_site_objs(i).location_obj.county := NULL;
74 END IF;
75 IF(px_org_cust.organization_obj.party_site_objs(i).location_obj.postal_code = FND_API.G_MISS_CHAR) THEN
76 px_org_cust.organization_obj.party_site_objs(i).location_obj.postal_code := NULL;
77 END IF;
78 IF(px_org_cust.organization_obj.party_site_objs(i).location_obj.state = FND_API.G_MISS_CHAR) THEN
79 px_org_cust.organization_obj.party_site_objs(i).location_obj.state := NULL;
80 END IF;
81 IF(px_org_cust.organization_obj.party_site_objs(i).location_obj.province = FND_API.G_MISS_CHAR) THEN
82 px_org_cust.organization_obj.party_site_objs(i).location_obj.province := NULL;
83 END IF;
84 END IF;
85 END LOOP;
86 END IF;
87 IF(px_org_cust.organization_obj.email_objs IS NOT NULL AND
88 px_org_cust.organization_obj.email_objs.COUNT > 0) THEN
89 FOR i IN 1..px_org_cust.organization_obj.email_objs.COUNT LOOP
90 IF(px_org_cust.organization_obj.email_objs(i).email_address = FND_API.G_MISS_CHAR) THEN
91 px_org_cust.organization_obj.email_objs(i).email_address := NULL;
92 END IF;
93 END LOOP;
94 END IF;
95 IF(px_org_cust.organization_obj.web_objs IS NOT NULL AND
96 px_org_cust.organization_obj.web_objs.COUNT > 0) THEN
97 FOR i IN 1..px_org_cust.organization_obj.web_objs.COUNT LOOP
98 IF(px_org_cust.organization_obj.web_objs(i).url = FND_API.G_MISS_CHAR) THEN
99 px_org_cust.organization_obj.web_objs(i).url := NULL;
100 END IF;
101 END LOOP;
102 END IF;
103 IF(px_org_cust.organization_obj.duns_number_c = FND_API.G_MISS_CHAR) THEN
104 px_org_cust.organization_obj.duns_number_c := NULL;
105 END IF;
106 END remove_gmiss;
107
108 PROCEDURE log(
109 message IN VARCHAR2,
110 newline IN BOOLEAN DEFAULT TRUE);
111
112 PROCEDURE log(
113 message IN VARCHAR2,
114 newline IN BOOLEAN DEFAULT TRUE) IS
115 BEGIN
116 IF message = 'NEWLINE' THEN
117 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
118 ELSIF (newline) THEN
119 FND_FILE.put_line(fnd_file.log,message);
120 ELSE
121 FND_FILE.put_line(fnd_file.log,message);
122 END IF;
123 END log;
124
125 PROCEDURE get_acct_merge_obj(
126 p_customer_merge_header_id IN NUMBER,
127 x_account_merge_obj OUT NOCOPY CRMINTEG_HZ_MERGE_OBJ
128 ) IS
129 CURSOR account_merge_details(l_customer_merge_header_id NUMBER) IS
130 SELECT CRMINTEG_HZ_MERGE_OBJ(
131 decode(mh.customer_type, 'CUSTOMER_ORG', 'ORGANIZATION', 'CUSTOMER_PERSON', 'PERSON'),-- party_type
132 mh.duplicate_id, -- from cust_acct_id
133 ca.party_id, -- from party_id
134 null, -- from common_obj_id
135 mh.customer_id, -- to cust_acct_id
136 ca2.party_id, -- to party_id
137 null, -- to common_obj_id
138 'N', -- keep account flag
139 CAST(MULTISET(
140 SELECT CRMINTEG_HZ_MRGDTIL_OBJ(
141 'ADDRESS',
142 'N',
143 rm.duplicate_address_id, -- from cust_acct_site_id
144 cas.party_site_id, -- from party_site_id
145 null, -- from common_obj_id
146 rm.customer_address_id, -- to cust_acct_site_id
147 cas2.party_site_id, -- to party_site_id
148 null ) -- to common_obj_id
149 from RA_CUSTOMER_MERGES rm, HZ_CUST_ACCT_SITES_ALL cas, HZ_CUST_ACCT_SITES_ALL cas2
150 where rm.customer_merge_header_id = p_customer_merge_header_id
151 and rm.duplicate_address_id = cas.cust_acct_site_id(+)
152 and rm.customer_address_id = cas2.cust_acct_site_id(+)
153 group by rm.duplicate_address_id, rm.customer_address_id,
154 cas.party_site_id, cas2.party_site_id
155 ) AS CRMINTEG_HZ_MRGDTIL_OBJ_TBL ),
156 CAST(MULTISET(
157 SELECT CRMINTEG_HZ_MRGDTIL_OBJ(
158 'CONTACT',
159 'N',
160 carm.cust_account_role_id, -- from cust_acct_role_id
161 ocm.org_contact_id, -- from org_contact_id
162 null, -- from common_obj_id
163 car.cust_account_role_id, -- to cust_acct_role_id
164 oc.org_contact_id, -- to org_contact_id
165 null ) -- to common_obj_id
166 from RA_CUSTOMER_MERGES rm, HZ_CUST_ACCOUNT_ROLES_M carm, HZ_CUST_ACCOUNT_ROLES car, HZ_RELATIONSHIPS relm, HZ_RELATIONSHIPS rel, HZ_ORG_CONTACTS ocm, HZ_ORG_CONTACTS oc
167 where rm.customer_merge_header_id = p_customer_merge_header_id
168 and rm.customer_merge_header_id = carm.customer_merge_header_id(+)
169 and carm.party_id = relm.party_id(+)
170 and relm.relationship_id = ocm.party_relationship_id(+)
171 and carm.cust_account_role_id = car.cust_account_role_id(+)
172 and car.party_id = rel.party_id(+)
173 and rel.relationship_id = oc.party_relationship_id(+)
174 group by carm.cust_account_role_id, car.cust_account_role_id,
175 ocm.org_contact_id, oc.org_contact_id
176 ) AS CRMINTEG_HZ_MRGDTIL_OBJ_TBL ))
177 from RA_CUSTOMER_MERGE_HEADERS mh, HZ_CUST_ACCOUNTS ca, HZ_CUST_ACCOUNTS ca2
178 where mh.customer_merge_header_id = l_customer_merge_header_id
179 and mh.process_flag = 'Y'
180 and mh.duplicate_id = ca.cust_account_id(+)
181 and mh.customer_id = ca2.cust_account_id;
182
183 CURSOR other_ou(l_ca_id NUMBER, l_cas_id NUMBER, l_ps_id NUMBER) IS
184 SELECT 'Y'
185 FROM HZ_CUST_ACCT_SITES_ALL
186 WHERE cust_account_id = l_ca_id
187 AND cust_acct_site_id <> l_cas_id
188 AND party_site_id = l_ps_id
189 AND rownum = 1;
190
191 CURSOR is_acct_exist(l_cust_acct_id NUMBER) IS
192 SELECT decode(status, 'A', 'Y', 'N')
193 FROM HZ_CUST_ACCOUNTS
194 WHERE cust_account_id = l_cust_acct_id;
195
196 CURSOR get_deleted_acct_pid(l_cmhdr_id NUMBER, l_cust_acct_id NUMBER) IS
197 SELECT party_id
198 FROM HZ_CUST_ACCOUNTS_M
199 WHERE cust_account_id = l_cust_acct_id
200 AND customer_merge_header_id = l_cmhdr_id
201 AND rownum = 1;
202
203 CURSOR get_deleted_cs_psid(l_cmhdr_id NUMBER, l_cust_acct_site_id NUMBER) IS
204 SELECT party_site_id
205 FROM HZ_CUST_ACCT_SITES_ALL_M
206 WHERE cust_acct_site_id = l_cust_acct_site_id
207 AND customer_merge_header_id = l_cmhdr_id
208 AND rownum = 1;
209
210 l_debug_prefix VARCHAR2(30);
211 BEGIN
212 OPEN account_merge_details(p_customer_merge_header_id);
213 FETCH account_merge_details INTO x_account_merge_obj;
214 CLOSE account_merge_details;
215
216 -- set keep account flag to 'Y'
217 -- case 1
218 -- if same from and to account, then the event is merge address
219 -- case 2
220 -- if from party_id is null, then the account merge has deleted the from account already
221 -- if an account is deleted, then it means only 1 OU is associated with its acct site
222 -- therefore, this account can be removed
223 -- otherwise, keep the account
224 IF(x_account_merge_obj.from_cust_acct_id = x_account_merge_obj.to_cust_acct_id) THEN
225 x_account_merge_obj.keep_account_flag := 'Y';
226 ELSIF(x_account_merge_obj.from_party_id IS NULL) THEN
227 x_account_merge_obj.keep_account_flag := 'N';
228 -- get deleted account party_id
229 OPEN get_deleted_acct_pid(p_customer_merge_header_id, x_account_merge_obj.from_cust_acct_id);
230 FETCh get_deleted_acct_pid INTO x_account_merge_obj.from_party_id;
231 CLOSE get_deleted_acct_pid;
232 ELSIF(x_account_merge_obj.from_party_id IS NOT NULL) THEN
233 OPEN is_acct_exist(x_account_merge_obj.from_cust_acct_id);
234 FETCH is_acct_exist INTO x_account_merge_obj.keep_account_flag;
235 CLOSE is_acct_exist;
236 END IF;
237
238 -- get missing party_site_id
239 IF(x_account_merge_obj.merge_address_objs IS NOT NULL and x_account_merge_obj.merge_address_objs.COUNT > 0) THEN
240 FOR k IN 1..x_account_merge_obj.merge_address_objs.COUNT LOOP
241 IF(x_account_merge_obj.merge_address_objs(k).from_party_object_id IS NULL) THEN
242 OPEN get_deleted_cs_psid(p_customer_merge_header_id, x_account_merge_obj.merge_address_objs(k).from_acct_object_id);
243 FETCH get_deleted_cs_psid INTO x_account_merge_obj.merge_address_objs(k).from_party_object_id;
244 CLOSE get_deleted_cs_psid;
245 END IF;
246 END LOOP;
247 END IF;
248
249 -- if more than 1 org_id associated with cust acct site of the same party site
250 -- set keep_xref_flag for acct site
251 IF(x_account_merge_obj.merge_address_objs IS NOT NULL and
252 x_account_merge_obj.merge_address_objs.COUNT > 0) THEN
253 FOR i IN 1..x_account_merge_obj.merge_address_objs.COUNT LOOP
254 OPEN other_ou(x_account_merge_obj.from_cust_acct_id,
255 x_account_merge_obj.merge_address_objs(i).from_acct_object_id,
256 x_account_merge_obj.merge_address_objs(i).from_party_object_id);
257 FETCH other_ou INTO x_account_merge_obj.merge_address_objs(i).keep_xref_flag;
258 CLOSE other_ou;
259 END LOOP;
260 END IF;
261 EXCEPTION
262 WHEN fnd_api.g_exc_error THEN
263 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
264 hz_utility_v2pub.debug(p_message=>'get_acct_merge_obj(-)',
265 p_prefix=>l_debug_prefix,
266 p_msg_level=>fnd_log.level_procedure);
267 END IF;
268 WHEN fnd_api.g_exc_unexpected_error THEN
269 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
270 hz_utility_v2pub.debug(p_message=>'get_acct_merge_obj(-)',
271 p_prefix=>l_debug_prefix,
275 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
272 p_msg_level=>fnd_log.level_procedure);
273 END IF;
274 WHEN OTHERS THEN
276 fnd_message.set_token('ERROR' ,SQLERRM);
277 fnd_msg_pub.add;
278
279 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
280 hz_utility_v2pub.debug(p_message=>'get_acct_merge_obj(-)',
281 p_prefix=>l_debug_prefix,
282 p_msg_level=>fnd_log.level_procedure);
283 END IF;
284 END get_acct_merge_obj;
285
286 PROCEDURE get_related_org_cust_objs(
287 p_batch_id IN NUMBER,
288 p_merge_to_party_id IN NUMBER,
289 x_org_cust_objs OUT NOCOPY HZ_ORG_CUST_BO_TBL
290 ) IS
291 CURSOR get_related_org_cust(l_batch_id NUMBER) IS
292 SELECT distinct cr.cust_account_id, r2.object_id
293 from HZ_MERGE_BATCH mb, HZ_MERGE_PARTIES mp, HZ_MERGE_PARTY_HISTORY mph,
294 HZ_MERGE_DICTIONARY md, HZ_CUST_ACCOUNT_ROLES cr, HZ_RELATIONSHIPS r, HZ_ORG_CONTACTS oc,
295 HZ_RELATIONSHIPS r2, HZ_ORG_CONTACTS oc2
296 where mb.batch_id = l_batch_id
297 and mb.batch_id = mp.batch_id
298 and mp.merge_reason_code = 'DUPLICATE_RELN_PARTY'
299 and mp.batch_party_id = mph.batch_party_id
300 and mph.merge_dict_id = md.merge_dict_id
301 and cr.cust_account_role_id = mph.from_entity_id
302 and r.party_id = mp.from_party_id
303 and r.relationship_id = oc.party_relationship_id
304 and r.subject_Type = 'PERSON' and r.object_type = 'ORGANIZATION'
305 and r2.party_id = mp.to_party_id
306 and r2.relationship_id = oc2.party_relationship_id
307 and r2.subject_Type = 'PERSON' and r2.object_type = 'ORGANIZATION'
308 and md.entity_name = 'HZ_CUST_ACCOUNT_ROLES';
309
310 l_ca_id NUMBER;
311 l_org_id NUMBER;
312 i NUMBER;
313 l_return_status VARCHAR2(30);
314 l_msg_data VARCHAR2(2000);
315 l_msg_count NUMBER;
316 l_debug_prefix VARCHAR2(30);
317 BEGIN
318 x_org_cust_objs := HZ_ORG_CUST_BO_TBL();
319 i := 1;
320 OPEN get_related_org_cust(p_batch_id);
321 LOOP
322 FETCH get_related_org_cust INTO l_ca_id, l_org_id;
323 EXIT WHEN get_related_org_cust%NOTFOUND;
324
325 x_org_cust_objs.EXTEND;
326 x_org_cust_objs(i) := HZ_ORG_CUST_BO(null, null, null);
327
328 HZ_EXTRACT_ORGANIZATION_BO_PVT.get_organization_bo(
329 p_init_msg_list => fnd_api.g_false,
330 p_organization_id => l_org_id,
331 p_action_type => null,
332 x_organization_obj => x_org_cust_objs(i).organization_obj,
333 x_return_status => l_return_status,
334 x_msg_count => l_msg_count,
335 x_msg_data => l_msg_data);
336
337 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
338 RAISE FND_API.G_EXC_ERROR;
339 END IF;
340
341 HZ_EXTRACT_CUST_ACCT_BO_PVT.get_cust_acct_bos(
342 p_init_msg_list => fnd_api.g_false,
343 p_parent_id => l_org_id,
344 p_cust_acct_id => l_ca_id,
345 p_action_type => null,
346 x_cust_acct_objs => x_org_cust_objs(i).account_objs,
347 x_return_status => l_return_status,
348 x_msg_count => l_msg_count,
349 x_msg_data => l_msg_data);
350
351 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
352 RAISE FND_API.G_EXC_ERROR;
353 END IF;
354 i := i + 1;
355 END LOOP;
356 CLOSE get_related_org_cust;
357 EXCEPTION
358 WHEN fnd_api.g_exc_error THEN
359 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
360 hz_utility_v2pub.debug(p_message=>'get_related_org_cust_objs(-)',
361 p_prefix=>l_debug_prefix,
362 p_msg_level=>fnd_log.level_procedure);
363 END IF;
364 WHEN fnd_api.g_exc_unexpected_error THEN
365 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
366 hz_utility_v2pub.debug(p_message=>'get_related_org_cust_objs(-)',
367 p_prefix=>l_debug_prefix,
368 p_msg_level=>fnd_log.level_procedure);
369 END IF;
370 WHEN OTHERS THEN
371 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
372 fnd_message.set_token('ERROR' ,SQLERRM);
373 fnd_msg_pub.add;
374
375 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
376 hz_utility_v2pub.debug(p_message=>'get_related_org_cust_objs(-)',
377 p_prefix=>l_debug_prefix,
378 p_msg_level=>fnd_log.level_procedure);
379 END IF;
380 END get_related_org_cust_objs;
381
382 PROCEDURE get_party_merge_objs(
383 p_batch_id IN NUMBER,
384 p_merge_to_party_id IN NUMBER,
385 x_party_merge_objs OUT NOCOPY CRMINTEG_HZ_MERGE_OBJ_TBL
386 ) IS
387 CURSOR get_merge_type IS
388 SELECT party_type
389 FROM HZ_PARTIES
390 WHERE party_id = p_merge_to_party_id;
391
392 CURSOR get_merge_parties(l_batch_id NUMBER) IS
393 SELECT p.party_type, from_parent_entity_id, from_entity_id, to_parent_entity_id
397 and mp.batch_party_id = mph.batch_party_id
394 from HZ_MERGE_BATCH mb, HZ_MERGE_PARTIES mp, HZ_MERGE_PARTY_HISTORY mph, HZ_MERGE_DICTIONARY md, HZ_PARTIES p
395 where mb.batch_id = l_batch_id
396 and mb.batch_id = mp.batch_id
398 and mph.merge_dict_id = md.merge_dict_id
399 and p.party_id = mph.to_parent_entity_id
400 and md.entity_name = 'HZ_CUST_ACCOUNTS';
401
402 CURSOR get_merge_detail(l_batch_id NUMBER, l_party_type VARCHAR2, l_fpid NUMBER, l_fcaid NUMBER, l_tpid NUMBER) IS
403 SELECT CRMINTEG_HZ_MERGE_OBJ(
404 l_party_type, -- party_type
405 l_fcaid, -- from cust_acct_id
406 l_fpid, -- from party_id
407 null, -- from common_obj_id
408 l_fcaid, -- to cust_acct_id
409 l_tpid, -- to party_id
410 null, -- to common_obj_id
411 'N',
412 CAST(MULTISET(
413 SELECT CRMINTEG_HZ_MRGDTIL_OBJ(
414 'ADDRESS',
415 'N',
416 cs.cust_account_id, -- from cust_acct_id
417 mph.from_parent_entity_id, -- from party_site_id
418 null, -- from common_obj_id
419 cs.cust_account_id, -- to cust_acct_id
420 mph.to_parent_entity_id, -- to party_site_id
421 null ) -- to common_obj_id
422 from HZ_MERGE_BATCH mb, HZ_MERGE_PARTIES mp, HZ_MERGE_PARTY_HISTORY mph,
423 HZ_MERGE_DICTIONARY md, HZ_CUST_ACCT_SITES_ALL cs
424 where mb.batch_id = l_batch_id
425 and mb.batch_id = mp.batch_id
426 and mp.batch_party_id = mph.batch_party_id
427 and mph.merge_dict_id = md.merge_dict_id
428 and cs.cust_acct_site_id = mph.from_entity_id
429 and cs.cust_account_id = l_fcaid
430 and md.entity_name = 'HZ_CUST_ACCT_SITES_ALL'
431 ) AS CRMINTEG_HZ_MRGDTIL_OBJ_TBL ),
432 CAST(MULTISET(
433 SELECT CRMINTEG_HZ_MRGDTIL_OBJ(
434 'CONTACT',
435 'N',
436 cr.cust_account_id, -- from cust_acct_site_id
437 oc.org_contact_id, -- from party_site_id
438 null, -- from common_obj_id
439 cr.cust_account_id, -- to cust_acct_site_id
440 oc2.org_contact_id, -- to party_site_id
441 null ) -- to common_obj_id
442 from HZ_MERGE_BATCH mb, HZ_MERGE_PARTIES mp, HZ_MERGE_PARTY_HISTORY mph,
443 HZ_MERGE_DICTIONARY md, HZ_CUST_ACCOUNT_ROLES cr, HZ_RELATIONSHIPS r, HZ_ORG_CONTACTS oc,
444 HZ_RELATIONSHIPS r2, HZ_ORG_CONTACTS oc2
445 where mb.batch_id = l_batch_id
446 and mb.batch_id = mp.batch_id
447 and mp.batch_party_id = mph.batch_party_id
448 and mph.merge_dict_id = md.merge_dict_id
449 and cr.cust_account_role_id = mph.from_entity_id
450 and cr.cust_account_id = l_fcaid
451 and r.party_id = mph.from_parent_entity_id
452 and r.relationship_id = oc.party_relationship_id
453 and r.subject_Type = 'PERSON' and r.object_type = 'ORGANIZATION'
454 and r2.party_id = mph.to_parent_entity_id
455 and r2.relationship_id = oc2.party_relationship_id
456 and r2.subject_Type = 'PERSON' and r2.object_type = 'ORGANIZATION'
457 and md.entity_name = 'HZ_CUST_ACCOUNT_ROLES'
458 ) AS CRMINTEG_HZ_MRGDTIL_OBJ_TBL ))
459 from dual;
460
461 CURSOR get_person_merge_detail(l_batch_id NUMBER, l_party_type VARCHAR2) IS
462 SELECT CRMINTEG_HZ_MERGE_OBJ(
463 l_party_type, -- party_type
464 null, -- from cust_acct_id
465 null, -- from party_id
466 null, -- from common_obj_id
467 null, -- to cust_acct_id
468 null, -- to party_id
469 null, -- to common_obj_id
470 'N',
471 CRMINTEG_HZ_MRGDTIL_OBJ_TBL(),
472 CAST(MULTISET(
473 SELECT CRMINTEG_HZ_MRGDTIL_OBJ(
474 'CONTACT',
475 'N',
476 cr.cust_account_id, -- from cust_acct_site_id
477 oc.org_contact_id, -- from org_contact_id
478 null, -- from common_obj_id
479 cr.cust_account_id, -- to cust_acct_site_id
480 oc2.org_contact_id, -- to org_contact_id
481 r2.object_id) -- to party_id
482 from HZ_MERGE_BATCH mb, HZ_MERGE_PARTIES mp, HZ_MERGE_PARTY_HISTORY mph,
483 HZ_MERGE_DICTIONARY md, HZ_CUST_ACCOUNT_ROLES cr, HZ_RELATIONSHIPS r, HZ_ORG_CONTACTS oc,
484 HZ_RELATIONSHIPS r2, HZ_ORG_CONTACTS oc2
485 where mb.batch_id = l_batch_id
486 and mb.batch_id = mp.batch_id
487 and mp.merge_reason_code = 'DUPLICATE_RELN_PARTY'
488 and mp.batch_party_id = mph.batch_party_id
489 and mph.merge_dict_id = md.merge_dict_id
490 and cr.cust_account_role_id = mph.from_entity_id
491 and r.party_id = mp.from_party_id
492 and r.relationship_id = oc.party_relationship_id
496 and r2.subject_Type = 'PERSON' and r2.object_type = 'ORGANIZATION'
493 and r.subject_Type = 'PERSON' and r.object_type = 'ORGANIZATION'
494 and r2.party_id = mp.to_party_id
495 and r2.relationship_id = oc2.party_relationship_id
497 and md.entity_name = 'HZ_CUST_ACCOUNT_ROLES'
498 ) AS CRMINTEG_HZ_MRGDTIL_OBJ_TBL ))
499 from dual;
500
501 l_debug_prefix VARCHAR2(30);
502 l_party_type VARCHAR2(30);
503 l_from_party_id NUMBER;
504 l_from_ca_id NUMBER;
505 l_to_party_id NUMBER;
506 i NUMBER;
507 BEGIN
508 x_party_merge_objs := CRMINTEG_HZ_MERGE_OBJ_TBL();
509
510 OPEN get_merge_type;
511 FETCH get_merge_type INTO l_party_type;
512 CLOSE get_merge_type;
513
514 IF(l_party_type = 'PERSON') THEN
515 x_party_merge_objs.EXTEND;
516 OPEN get_person_merge_detail(p_batch_id, l_party_type);
517 FETCH get_person_merge_detail INTO x_party_merge_objs(1);
518 CLOSE get_person_merge_detail;
519 ELSIF(l_party_type = 'ORGANIZATION') THEN
520 i := 1;
521 OPEN get_merge_parties(p_batch_id);
522 LOOP
523 FETCH get_merge_parties INTO l_party_type, l_from_party_id, l_from_ca_id, l_to_party_id;
524 EXIT WHEN get_merge_parties%NOTFOUND;
525 x_party_merge_objs.EXTEND;
526
527 OPEN get_merge_detail(p_batch_id, l_party_type, l_from_party_id, l_from_ca_id, l_to_party_id);
528 FETCH get_merge_detail INTO x_party_merge_objs(i);
529 CLOSE get_merge_detail;
530 i := i + 1;
531 END LOOP;
532 CLOSE get_merge_parties;
533 END IF;
534 EXCEPTION
535 WHEN fnd_api.g_exc_error THEN
536 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
537 hz_utility_v2pub.debug(p_message=>'get_party_merge_objs(-)',
538 p_prefix=>l_debug_prefix,
539 p_msg_level=>fnd_log.level_procedure);
540 END IF;
541 WHEN fnd_api.g_exc_unexpected_error THEN
542 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
543 hz_utility_v2pub.debug(p_message=>'get_party_merge_objs(-)',
544 p_prefix=>l_debug_prefix,
545 p_msg_level=>fnd_log.level_procedure);
546 END IF;
547 WHEN OTHERS THEN
548 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
549 fnd_message.set_token('ERROR' ,SQLERRM);
550 fnd_msg_pub.add;
551
552 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
553 hz_utility_v2pub.debug(p_message=>'get_party_merge_objs(-)',
554 p_prefix=>l_debug_prefix,
555 p_msg_level=>fnd_log.level_procedure);
556 END IF;
557 END get_party_merge_objs;
558
559 PROCEDURE sync_acct_update(
560 p_validate_bo_flag IN VARCHAR2 := fnd_api.g_true,
561 p_org_cust_obj IN HZ_ORG_CUST_BO,
562 p_created_by_module IN VARCHAR2,
563 p_obj_source IN VARCHAR2 := null,
564 x_return_status OUT NOCOPY VARCHAR2,
565 x_messages OUT NOCOPY HZ_MESSAGE_OBJ_TBL,
566 x_return_obj OUT NOCOPY HZ_ORG_CUST_BO
567 ) IS
568 l_organization_id NUMBER;
569 l_organization_os VARCHAR2(30);
570 l_organization_osr VARCHAR2(255);
571 l_debug_prefix VARCHAR2(30);
572 l_org_obj HZ_ORGANIZATION_BO;
573 l_return_obj HZ_ORG_CUST_BO;
574 l_return_org_obj HZ_ORGANIZATION_BO;
575 l_input_acct_obj HZ_CUST_ACCT_BO;
576 l_org_cust_bo HZ_ORG_CUST_BO;
577
578 CURSOR get_cp_id(l_org_id NUMBER) IS
579 SELECT contact_point_id
580 FROM HZ_CONTACT_POINTS
581 WHERE owner_table_name = 'HZ_PARTIES'
582 AND contact_point_type = 'WEB'
583 AND owner_table_id = l_org_id
584 AND primary_flag = 'Y';
585
586 CURSOR get_occp_id(l_oc_id NUMBER) IS
587 SELECT cp.contact_point_id
588 FROM HZ_ORG_CONTACTS oc, HZ_RELATIONSHIPS r, HZ_CONTACT_POINTS cp
589 WHERE cp.owner_table_name = 'HZ_PARTIES'
590 AND cp.contact_point_type = 'EMAIL'
591 AND cp.owner_table_id = r.party_id
592 AND cp.primary_flag = 'Y'
593 AND oc.org_contact_id = l_oc_id
594 AND oc.party_relationship_id = r.relationship_id
595 AND rownum = 1;
596
597 CURSOR get_cas_id(l_ps_id NUMBER, l_ca_id NUMBER, l_org_id NUMBER) IS
598 SELECT cust_acct_site_id
599 FROM HZ_CUST_ACCT_SITES_ALL
600 WHERE cust_account_id = l_ca_id
601 AND party_site_id = l_ps_id
602 AND org_id = l_org_id
603 AND rownum = 1;
604
605 CURSOR get_casu_id(l_cas_id NUMBER, l_su_code VARCHAR2, l_org_id NUMBER) IS
606 SELECT site_use_id
607 FROM HZ_CUST_SITE_USES_ALL
608 WHERE cust_acct_site_id = l_cas_id
609 AND site_use_code = l_su_code
610 AND status = 'A'
611 AND org_id = l_org_id
612 AND rownum = 1;
613 BEGIN
614 SAVEPOINT do_sync_acct_update;
615
616 -- initialize API return status to success.
617 x_return_status := FND_API.G_RET_STS_SUCCESS;
618
619 -- Debug info.
623 p_msg_level=>fnd_log.level_procedure);
620 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
621 hz_utility_v2pub.debug(p_message=>'sync_acct_update(+)',
622 p_prefix=>l_debug_prefix,
624 END IF;
625
626 x_return_obj := HZ_ORG_CUST_BO(null, null, HZ_CUST_ACCT_BO_TBL());
627 l_org_obj := p_org_cust_obj.organization_obj;
628
629
630 -- update organization business object
631 HZ_ORGANIZATION_BO_PUB.update_organization_bo(
632 p_organization_obj => l_org_obj,
633 p_created_by_module => p_created_by_module,
634 p_obj_source => p_obj_source,
635 p_return_obj_flag => fnd_api.g_true,
636 x_return_status => x_return_status,
637 x_messages => x_messages,
638 x_return_obj => l_return_org_obj,
639 x_organization_id => l_organization_id,
640 x_organization_os => l_organization_os,
641 x_organization_osr => l_organization_osr
642 );
643
644 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
645 RAISE fnd_api.g_exc_error;
646 END IF;
647
648 l_input_acct_obj := p_org_cust_obj.account_objs(1);
649
650 -- get cust acct site and site use id
651 IF(l_input_acct_obj.cust_acct_site_objs IS NOT NULL AND
652 l_input_acct_obj.cust_acct_site_objs.COUNT > 0) THEN
653 FOR k IN 1..l_input_acct_obj.cust_acct_site_objs.COUNT LOOP
654 OPEN get_cas_id(l_input_acct_obj.cust_acct_site_objs(k).party_site_id,
655 l_input_acct_obj.cust_acct_id,
656 l_input_acct_obj.cust_acct_site_objs(k).org_id);
657 FETCH get_cas_id INTO l_input_acct_obj.cust_acct_site_objs(k).cust_acct_site_id;
658 CLOSE get_cas_id;
659 IF(l_input_acct_obj.cust_acct_site_objs(k).cust_acct_site_use_objs IS NOT NULL AND
660 l_input_acct_obj.cust_acct_site_objs(k).cust_acct_site_use_objs.COUNT > 0) THEN
661 FOR l IN 1..l_input_acct_obj.cust_acct_site_objs(k).cust_acct_site_use_objs.COUNT LOOP
662 OPEN get_casu_id(l_input_acct_obj.cust_acct_site_objs(k).cust_acct_site_id,
663 l_input_acct_obj.cust_acct_site_objs(k).cust_acct_site_use_objs(l).site_use_code,
664 l_input_acct_obj.cust_acct_site_objs(k).cust_acct_site_use_objs(l).org_id);
665 FETCH get_casu_id INTO l_input_acct_obj.cust_acct_site_objs(k).cust_acct_site_use_objs(l).site_use_id;
666 CLOSE get_casu_id;
667 END LOOP;
668 END IF;
669 END LOOP;
670 END IF;
671
672 l_org_cust_bo := HZ_ORG_CUST_BO(null,null,HZ_CUST_ACCT_BO_TBL());
673 l_org_obj := HZ_ORGANIZATION_BO.create_object(
674 p_organization_id => l_organization_id);
675 l_org_cust_bo.organization_obj := l_org_obj;
676 l_org_cust_bo.account_objs.EXTEND;
677 l_org_cust_bo.account_objs(1) := l_input_acct_obj;
678
679 -- call update_org_cust_bo
680 HZ_ORG_CUST_BO_PUB.update_org_cust_bo(
681 p_org_cust_obj => l_org_cust_bo,
682 p_created_by_module => p_created_by_module,
683 p_obj_source => p_obj_source,
684 p_return_obj_flag => fnd_api.g_true,
685 x_return_status => x_return_status,
686 x_messages => x_messages,
687 x_return_obj => l_return_obj,
688 x_organization_id => l_organization_id
689 );
690
691 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
692 RAISE fnd_api.g_exc_error;
693 END IF;
694
695 x_return_obj := l_return_obj;
696 x_return_obj.organization_obj := l_return_org_obj;
697 remove_gmiss(
698 px_org_cust => x_return_obj);
699
700 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
701 hz_utility_v2pub.debug(p_message=>'sync_acct_update(-)',
702 p_prefix=>l_debug_prefix,
703 p_msg_level=>fnd_log.level_procedure);
704 END IF;
705 EXCEPTION
706 WHEN fnd_api.g_exc_error THEN
707 ROLLBACK TO do_sync_acct_update;
708
709 x_return_status := fnd_api.g_ret_sts_error;
710
711 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
712 hz_utility_v2pub.debug(p_message=>'sync_acct_update(-)',
713 p_prefix=>l_debug_prefix,
714 p_msg_level=>fnd_log.level_procedure);
715 END IF;
716 WHEN fnd_api.g_exc_unexpected_error THEN
717 ROLLBACK TO do_sync_acct_update;
718
719 x_return_status := fnd_api.g_ret_sts_error;
720
721 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
722 hz_utility_v2pub.debug(p_message=>'sync_acct_update(-)',
723 p_prefix=>l_debug_prefix,
724 p_msg_level=>fnd_log.level_procedure);
725 END IF;
726 WHEN OTHERS THEN
727 ROLLBACK TO do_sync_acct_update;
728
729 x_return_status := fnd_api.g_ret_sts_unexp_error;
730
731 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
732 fnd_message.set_token('ERROR' ,SQLERRM);
733 fnd_msg_pub.add;
734
735 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
736 hz_utility_v2pub.debug(p_message=>'sync_acct_update(-)',
737 p_prefix=>l_debug_prefix,
741
738 p_msg_level=>fnd_log.level_procedure);
739 END IF;
740 END sync_acct_update;
742 PROCEDURE sync_acct_order(
743 p_validate_bo_flag IN VARCHAR2 := fnd_api.g_true,
744 p_org_cust_obj IN HZ_ORG_CUST_BO,
745 p_created_by_module IN VARCHAR2,
746 p_obj_source IN VARCHAR2 := null,
747 x_return_status OUT NOCOPY VARCHAR2,
748 x_messages OUT NOCOPY HZ_MESSAGE_OBJ_TBL,
749 x_return_obj OUT NOCOPY HZ_ORG_CUST_BO
750 ) IS
751 l_organization_id NUMBER;
752 l_organization_os VARCHAR2(30);
753 l_organization_osr VARCHAR2(255);
754 l_cust_acct_id NUMBER;
755 l_cust_acct_os VARCHAR2(30);
756 l_cust_acct_osr VARCHAR2(255);
757 l_obj_type VARCHAR2(30);
758 l_debug_prefix VARCHAR2(30);
759 l_input_acct_obj HZ_CUST_ACCT_BO;
760 l_org_obj HZ_ORGANIZATION_BO;
761 l_return_org_obj HZ_ORGANIZATION_BO;
762 l_return_acct_obj HZ_CUST_ACCT_BO;
763 l_dummy VARCHAR2(1) := NULL;
764 l_an_profile AR_SYSTEM_PARAMETERS.generate_customer_number%TYPE;
765 l_su_profile AR_SYSTEM_PARAMETERS.auto_site_numbering%TYPE;
766 l_org_cust_bo HZ_ORG_CUST_BO;
767 l_return_obj HZ_ORG_CUST_BO;
768
769 CURSOR get_cp_id(l_org_id NUMBER) IS
770 SELECT contact_point_id
771 FROM HZ_CONTACT_POINTS
772 WHERE owner_table_name = 'HZ_PARTIES'
773 AND contact_point_type = 'WEB'
774 AND owner_table_id = l_org_id
775 AND primary_flag = 'Y';
776
777 CURSOR get_occp_id(l_oc_id NUMBER) IS
778 SELECT cp.contact_point_id
779 FROM HZ_ORG_CONTACTS oc, HZ_RELATIONSHIPS r, HZ_CONTACT_POINTS cp
780 WHERE cp.owner_table_name = 'HZ_PARTIES'
781 AND cp.contact_point_type = 'EMAIL'
782 AND cp.owner_table_id = r.party_id
783 AND cp.primary_flag = 'Y'
784 AND oc.org_contact_id = l_oc_id
785 AND oc.party_relationship_id = r.relationship_id
786 AND rownum = 1;
787
788 CURSOR get_cas_id(l_ps_id NUMBER, l_ca_id NUMBER, l_org_id NUMBER) IS
789 SELECT cust_acct_site_id
790 FROM HZ_CUST_ACCT_SITES_ALL
791 WHERE cust_account_id = l_ca_id
792 AND party_site_id = l_ps_id
793 AND org_id = l_org_id
794 AND rownum = 1;
795
796 CURSOR get_casu_id(l_cas_id NUMBER, l_su_code VARCHAR2, l_org_id NUMBER) IS
797 SELECT site_use_id
798 FROM HZ_CUST_SITE_USES_ALL
799 WHERE cust_acct_site_id = l_cas_id
800 AND site_use_code = l_su_code
801 AND status = 'A'
802 AND org_id = l_org_id
803 AND rownum = 1;
804
805 CURSOR get_cac_id(l_per_id NUMBER, l_ca_id NUMBER) IS
806 SELECT car.cust_account_role_id
807 FROM HZ_CUST_ACCOUNT_ROLES car, HZ_RELATIONSHIPS r
808 WHERE car.cust_account_id = l_ca_id
809 AND car.party_id = r.party_id
810 AND r.subject_id = l_per_id
811 AND r.subject_type = 'PERSON'
812 AND r.object_type = 'ORGANIZATION'
813 AND car.cust_acct_site_id IS NULL
814 AND rownum = 1;
815
816 BEGIN
817 SAVEPOINT do_sync_acct_order;
818
819 -- initialize API return status to success.
820 x_return_status := FND_API.G_RET_STS_SUCCESS;
821
822 -- Debug info.
823 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
824 hz_utility_v2pub.debug(p_message=>'sync_acct_order(+)',
825 p_prefix=>l_debug_prefix,
826 p_msg_level=>fnd_log.level_procedure);
827 END IF;
828
829 x_return_obj := HZ_ORG_CUST_BO(null, null, HZ_CUST_ACCT_BO_TBL());
830 l_org_obj := p_org_cust_obj.organization_obj;
831
832 -- create/update organization business object
833 HZ_ORGANIZATION_BO_PUB.save_organization_bo(
834 p_validate_bo_flag => p_validate_bo_flag,
835 p_organization_obj => l_org_obj,
836 p_created_by_module => p_created_by_module,
837 p_obj_source => p_obj_source,
838 p_return_obj_flag => fnd_api.g_true,
839 x_return_status => x_return_status,
840 x_messages => x_messages,
841 x_return_obj => l_return_org_obj,
842 x_organization_id => l_organization_id,
843 x_organization_os => l_organization_os,
844 x_organization_osr => l_organization_osr
845 );
846
847 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
848 RAISE fnd_api.g_exc_error;
849 END IF;
850
851 x_return_obj.organization_obj := l_return_org_obj;
852
853 l_input_acct_obj := p_org_cust_obj.account_objs(1);
854
855 -- check if customer number autonumbering is off, account number is required
856 BEGIN
857 SELECT GENERATE_CUSTOMER_NUMBER, AUTO_SITE_NUMBERING INTO l_an_profile, l_su_profile
858 FROM AR_SYSTEM_PARAMETERS;
859 EXCEPTION
860 WHEN NO_DATA_FOUND THEN
861 NULL;
862 END;
863
864 -- for each party site, set party_site_id for cust acct site under acct
865 IF(x_return_obj.organization_obj.party_site_objs IS NOT NULL AND
866 x_return_obj.organization_obj.party_site_objs.COUNT > 0) THEN
870 l_input_acct_obj.cust_acct_site_objs(j).party_site_id := x_return_obj.organization_obj.party_site_objs(i).party_site_id;
867 FOR i IN 1..x_return_obj.organization_obj.party_site_objs.COUNT LOOP
868 FOR j IN 1..l_input_acct_obj.cust_acct_site_objs.COUNT LOOP
869 IF(l_input_acct_obj.cust_acct_site_objs(j).party_site_id IS NULL AND l_input_acct_obj.cust_acct_site_objs(j).common_obj_id = x_return_obj.organization_obj.party_site_objs(i).common_obj_id) THEN
871 -- if there is cust site use, check l_su_profile and location field, if null, preset the
872 -- site use id and location
873 IF(l_input_acct_obj.cust_acct_site_objs(j).cust_acct_site_use_objs IS NOT NULL AND
874 l_input_acct_obj.cust_acct_site_objs(j).cust_acct_site_use_objs.COUNT > 0 AND
875 l_su_profile = 'N') THEN
876 FOR k IN 1..l_input_acct_obj.cust_acct_site_objs(j).cust_acct_site_use_objs.COUNT LOOP
877 IF(l_input_acct_obj.cust_acct_site_objs(j).cust_acct_site_use_objs(k).location IS NULL) THEN
878 select HZ_CUST_SITE_USES_S.NEXTVAL into l_input_acct_obj.cust_acct_site_objs(j).cust_acct_site_use_objs(k).site_use_id from dual;
879 l_input_acct_obj.cust_acct_site_objs(j).cust_acct_site_use_objs(k).location := to_char(l_input_acct_obj.cust_acct_site_objs(j).cust_acct_site_use_objs(k).site_use_id);
880 END IF;
881 END LOOP;
882 END IF;
883 END IF;
884 END LOOP;
885 END LOOP;
886 END IF;
887
888 -- for each org contact, add contact_person_id, relationship_type, relationship_code
889 -- start_date and role_type for cust acct contact under acct
890 IF(x_return_obj.organization_obj.contact_objs IS NOT NULL AND
891 x_return_obj.organization_obj.contact_objs.COUNT > 0) THEN
892 FOR i IN 1..x_return_obj.organization_obj.contact_objs.COUNT LOOP
893 FOR j IN 1..l_input_acct_obj.cust_acct_contact_objs.COUNT LOOP
894 IF(l_input_acct_obj.cust_acct_contact_objs(j).contact_person_id IS NULL AND l_input_acct_obj.cust_acct_contact_objs(j).common_obj_id = x_return_obj.organization_obj.contact_objs(i).common_obj_id) THEN
895 l_input_acct_obj.cust_acct_contact_objs(j).contact_person_id := x_return_obj.organization_obj.contact_objs(i).person_profile_obj.person_id;
896 l_input_acct_obj.cust_acct_contact_objs(j).relationship_code := x_return_obj.organization_obj.contact_objs(i).relationship_code;
897 l_input_acct_obj.cust_acct_contact_objs(j).relationship_type := x_return_obj.organization_obj.contact_objs(i).relationship_type;
898 l_input_acct_obj.cust_acct_contact_objs(j).start_date := x_return_obj.organization_obj.contact_objs(i).start_date;
899 END IF;
900 END LOOP;
901 END LOOP;
902 END IF;
903
904 -- if organization already exists, try to get cust_acct_site_id
905 IF(p_org_cust_obj.organization_obj.organization_id IS NOT NULL AND
906 l_input_acct_obj.cust_acct_site_objs IS NOT NULL AND
907 l_input_acct_obj.cust_acct_site_objs.COUNT > 0) THEN
908 FOR k IN 1..l_input_acct_obj.cust_acct_site_objs.COUNT LOOP
909 OPEN get_cas_id(l_input_acct_obj.cust_acct_site_objs(k).party_site_id,
910 l_input_acct_obj.cust_acct_id,
911 l_input_acct_obj.cust_acct_site_objs(k).org_id);
912 FETCH get_cas_id INTO l_input_acct_obj.cust_acct_site_objs(k).cust_acct_site_id;
913 CLOSE get_cas_id;
914 IF(l_input_acct_obj.cust_acct_site_objs(k).cust_acct_site_use_objs IS NOT NULL AND
915 l_input_acct_obj.cust_acct_site_objs(k).cust_acct_site_use_objs.COUNT > 0) THEN
916 FOR l IN 1..l_input_acct_obj.cust_acct_site_objs(k).cust_acct_site_use_objs.COUNT LOOP
917 OPEN get_casu_id(l_input_acct_obj.cust_acct_site_objs(k).cust_acct_site_id,
918 l_input_acct_obj.cust_acct_site_objs(k).cust_acct_site_use_objs(l).site_use_code,
919 l_input_acct_obj.cust_acct_site_objs(k).cust_acct_site_use_objs(l).org_id);
920 FETCH get_casu_id INTO l_input_acct_obj.cust_acct_site_objs(k).cust_acct_site_use_objs(l).site_use_id;
921 CLOSE get_casu_id;
922 END LOOP;
923 END IF;
924 END LOOP;
925
926 FOR l IN 1..l_input_acct_obj.cust_acct_contact_objs.COUNT LOOP
927 OPEN get_cac_id(l_input_acct_obj.cust_acct_contact_objs(l).contact_person_id,
928 l_input_acct_obj.cust_acct_id);
929 FETCH get_cac_id INTO l_input_acct_obj.cust_acct_contact_objs(l).cust_acct_contact_id;
930 CLOSE get_cac_id;
931 END LOOP;
932 END IF;
933
934 l_org_cust_bo := HZ_ORG_CUST_BO(null,null,HZ_CUST_ACCT_BO_TBL());
935 l_org_obj := HZ_ORGANIZATION_BO.create_object(
936 p_organization_id => l_organization_id);
937 l_org_cust_bo.organization_obj := l_org_obj;
938 l_org_cust_bo.account_objs.EXTEND;
939 l_org_cust_bo.account_objs(1) := l_input_acct_obj;
940
941 UPDATE HZ_PARTIES
942 set ORG_CUST_BO_VERSION = ( SELECT BO_VERSION_NUMBER
943 FROM HZ_BUS_OBJ_DEFINITIONS
944 WHERE BUSINESS_OBJECT_CODE = 'ORG_CUST'
945 AND ENTITY_NAME = 'HZ_PARTIES'
946 AND CHILD_BO_CODE IS NULL )
947 WHERE party_id = l_organization_id;
948
949 -- call save_org_cust_bo
950 HZ_ORG_CUST_BO_PUB.save_org_cust_bo(
951 p_validate_bo_flag => p_validate_bo_flag,
952 p_org_cust_obj => l_org_cust_bo,
953 p_created_by_module => p_created_by_module,
957 x_messages => x_messages,
954 p_obj_source => p_obj_source,
955 p_return_obj_flag => fnd_api.g_true,
956 x_return_status => x_return_status,
958 x_return_obj => l_return_obj,
959 x_organization_id => l_organization_id
960 );
961
962 x_return_obj := l_return_obj;
963 x_return_obj.organization_obj := l_return_org_obj;
964 /*
965 l_obj_type := 'ORG';
966
967 -- create/update org cust acct business object then
968 HZ_CUST_ACCT_BO_PUB.save_cust_acct_bo(
969 p_validate_bo_flag => p_validate_bo_flag,
970 p_cust_acct_obj => l_input_acct_obj,
971 p_created_by_module => p_created_by_module,
972 p_obj_source => p_obj_source,
973 p_return_obj_flag => fnd_api.g_true,
974 x_return_status => x_return_status,
975 x_messages => x_messages,
976 x_return_obj => l_return_acct_obj,
977 x_cust_acct_id => l_cust_acct_id,
978 x_cust_acct_os => l_cust_acct_os,
979 x_cust_acct_osr => l_cust_acct_osr,
980 px_parent_id => l_organization_id,
981 px_parent_os => l_organization_os,
982 px_parent_osr => l_organization_osr,
983 px_parent_obj_type => l_obj_type
984 );
985 */
986 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
987 RAISE fnd_api.g_exc_error;
988 END IF;
989 /*
990 x_return_obj.account_objs.EXTEND;
991 x_return_obj.account_objs(1) := l_return_acct_obj;
992 remove_gmiss(
993 px_org_cust => x_return_obj);
994 */
995
996 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
997 hz_utility_v2pub.debug(p_message=>'sync_acct_order(-)',
998 p_prefix=>l_debug_prefix,
999 p_msg_level=>fnd_log.level_procedure);
1000 END IF;
1001 EXCEPTION
1002 WHEN fnd_api.g_exc_error THEN
1003 ROLLBACK TO do_sync_acct_order;
1004
1005 x_return_status := fnd_api.g_ret_sts_error;
1006
1007 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1008 hz_utility_v2pub.debug(p_message=>'sync_acct_order(-)',
1009 p_prefix=>l_debug_prefix,
1010 p_msg_level=>fnd_log.level_procedure);
1011 END IF;
1012 WHEN fnd_api.g_exc_unexpected_error THEN
1013 ROLLBACK TO do_sync_acct_order;
1014
1015 x_return_status := fnd_api.g_ret_sts_error;
1016
1017 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1018 hz_utility_v2pub.debug(p_message=>'sync_acct_order(-)',
1019 p_prefix=>l_debug_prefix,
1020 p_msg_level=>fnd_log.level_procedure);
1021 END IF;
1022 WHEN OTHERS THEN
1023 ROLLBACK TO do_sync_acct_order;
1024
1025 x_return_status := fnd_api.g_ret_sts_unexp_error;
1026
1027 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
1028 fnd_message.set_token('ERROR' ,SQLERRM);
1029 fnd_msg_pub.add;
1030
1031 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1032 hz_utility_v2pub.debug(p_message=>'sync_acct_order(-)',
1033 p_prefix=>l_debug_prefix,
1034 p_msg_level=>fnd_log.level_procedure);
1035 END IF;
1036 END sync_acct_order;
1037
1038
1039 PROCEDURE get_merge_org_custs(
1040 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1041 p_from_org_id IN NUMBER,
1042 p_to_org_id IN NUMBER,
1043 p_from_acct_id IN NUMBER,
1044 p_to_acct_id IN NUMBER,
1045 x_org_cust_objs OUT NOCOPY HZ_ORG_CUST_BO_TBL,
1046 x_return_status OUT NOCOPY VARCHAR2,
1047 x_messages OUT NOCOPY HZ_MESSAGE_OBJ_TBL
1048 ) IS
1049 l_debug_prefix VARCHAR2(30) := '';
1050 l_msg_count NUMBER;
1051 l_msg_data VARCHAR2(2000);
1052 l_org_id NUMBER;
1053 l_acct_id NUMBER;
1054 BEGIN
1055 -- initialize API return status to success.
1056 x_return_status := FND_API.G_RET_STS_SUCCESS;
1057
1058 -- Initialize message list if p_init_msg_list is set to TRUE
1059 IF FND_API.to_Boolean(p_init_msg_list) THEN
1060 FND_MSG_PUB.initialize;
1061 END IF;
1062
1063 -- Debug info.
1064 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1065 hz_utility_v2pub.debug(p_message=>'get_merge_org_custs(+)',
1066 p_prefix=>l_debug_prefix,
1067 p_msg_level=>fnd_log.level_procedure);
1068 END IF;
1069
1070 x_org_cust_objs := HZ_ORG_CUST_BO_TBL();
1071
1072 FOR i IN 1..2 LOOP
1073 -- If same from to account and same from to org
1074 -- quit the 2nd loop
1075 IF i = 2 AND p_from_org_id = p_to_org_id AND p_from_acct_id = p_to_acct_id THEN
1076 EXIT;
1077 END IF;
1078
1079 x_org_cust_objs.EXTEND;
1080 x_org_cust_objs(i) := HZ_ORG_CUST_BO(NULL, NULL, NULL);
1081 IF i = 1 THEN
1082 l_org_id := p_from_org_id;
1083 l_acct_id := p_from_acct_id;
1084 ELSE
1085 l_org_id := p_to_org_id;
1086 l_acct_id := p_to_acct_id;
1090 p_init_msg_list => fnd_api.g_false,
1087 END IF;
1088
1089 HZ_EXTRACT_ORGANIZATION_BO_PVT.get_organization_bo(
1091 p_organization_id => l_org_id,
1092 p_action_type => NULL,
1093 x_organization_obj => x_org_cust_objs(i).organization_obj,
1094 x_return_status => x_return_status,
1095 x_msg_count => l_msg_count,
1096 x_msg_data => l_msg_data);
1097
1098 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1099 RAISE FND_API.G_EXC_ERROR;
1100 END IF;
1101 /*
1102 IF(x_org_cust_obj.organization_obj IS NULL OR x_org_cust_obj.organization_obj.organization_id IS NULL)THEN
1103 fnd_message.set_name('AR', 'HZ_API_INVALID_TCA_ID');
1104 fnd_msg_pub.add;
1105 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1106 p_count => l_msg_count,
1107 p_data => l_msg_data);
1108 RAISE FND_API.G_EXC_ERROR;
1109 END IF;
1110 */
1111 HZ_EXTRACT_CUST_ACCT_BO_PVT.get_cust_acct_bos(
1112 p_init_msg_list => fnd_api.g_false,
1116 x_cust_acct_objs => x_org_cust_objs(i).account_objs,
1113 p_parent_id => l_org_id,
1114 p_cust_acct_id => l_acct_id,
1115 p_action_type => NULL,
1117 x_return_status => x_return_status,
1118 x_msg_count => l_msg_count,
1119 x_msg_data => l_msg_data);
1120
1124 END LOOP;
1121 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1122 RAISE FND_API.G_EXC_ERROR;
1123 END IF;
1125 /*
1126 IF(x_org_cust_obj.account_objs IS NULL OR x_org_cust_obj.account_objs.COUNT < 1)THEN
1127 fnd_message.set_name('AR', 'HZ_API_INVALID_TCA_ID');
1128 fnd_msg_pub.add;
1129 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1130 p_count => l_msg_count,
1131 p_data => l_msg_data);
1132 RAISE FND_API.G_EXC_ERROR;
1133 END IF;
1134 */
1135 -- Debug info.
1136 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
1137 hz_utility_v2pub.debug_return_messages(p_msg_count=>l_msg_count,
1138 p_msg_data=>l_msg_data,
1139 p_msg_type=>'WARNING',
1140 p_msg_level=>fnd_log.level_exception);
1141 END IF;
1142
1143 -- Debug info.
1144 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1145 hz_utility_v2pub.debug(p_message=>'get_merge_org_custs(-)',
1146 p_prefix=>l_debug_prefix,
1147 p_msg_level=>fnd_log.level_procedure);
1148 END IF;
1149 EXCEPTION
1150 WHEN fnd_api.g_exc_error THEN
1151 x_return_status := fnd_api.g_ret_sts_error;
1152 x_messages := HZ_PARTY_BO_PVT.return_all_messages(
1153 x_return_status => x_return_status,
1154 x_msg_count => l_msg_count,
1155 x_msg_data => l_msg_data);
1156
1157 -- Debug info.
1158 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1159 hz_utility_v2pub.debug_return_messages(p_msg_count=>l_msg_count,
1160 p_msg_data=>l_msg_data,
1161 p_msg_type=>'ERROR',
1162 p_msg_level=>fnd_log.level_error);
1163 END IF;
1164 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1165 hz_utility_v2pub.debug(p_message=>'get_merge_org_custs(-)',
1166 p_prefix=>l_debug_prefix,
1167 p_msg_level=>fnd_log.level_procedure);
1168 END IF;
1169 WHEN fnd_api.g_exc_unexpected_error THEN
1170 x_return_status := fnd_api.g_ret_sts_unexp_error;
1171 x_messages := HZ_PARTY_BO_PVT.return_all_messages(
1172 x_return_status => x_return_status,
1173 x_msg_count => l_msg_count,
1174 x_msg_data => l_msg_data);
1175
1176 -- Debug info.
1177 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1178 hz_utility_v2pub.debug_return_messages(p_msg_count=>l_msg_count,
1179 p_msg_data=>l_msg_data,
1180 p_msg_type=>'UNEXPECTED ERROR',
1181 p_msg_level=>fnd_log.level_error);
1182 END IF;
1183 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1184 hz_utility_v2pub.debug(p_message=>'get_merge_org_custs(-)',
1185 p_prefix=>l_debug_prefix,
1186 p_msg_level=>fnd_log.level_procedure);
1187 END IF;
1188 WHEN OTHERS THEN
1189 x_return_status := fnd_api.g_ret_sts_unexp_error;
1190 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
1191 fnd_message.set_token('ERROR' ,SQLERRM);
1192 fnd_msg_pub.add;
1193 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1194 p_count => l_msg_count,
1195 p_data => l_msg_data);
1196 x_messages := HZ_PARTY_BO_PVT.return_all_messages(
1197 x_return_status => x_return_status,
1198 x_msg_count => l_msg_count,
1199 x_msg_data => l_msg_data);
1200
1201 -- Debug info.
1202 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1203 hz_utility_v2pub.debug_return_messages(p_msg_count=>l_msg_count,
1204 p_msg_data=>l_msg_data,
1205 p_msg_type=>'SQL ERROR',
1206 p_msg_level=>fnd_log.level_error);
1207 END IF;
1208 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1209 hz_utility_v2pub.debug(p_message=>'get_merge_org_custs(-)',
1210 p_prefix=>l_debug_prefix,
1211 p_msg_level=>fnd_log.level_procedure);
1212 END IF;
1213 END get_merge_org_custs;
1214
1215 END HZ_AIA_CUSTOM_PKG;