[Home] [Help]
PACKAGE BODY: APPS.HZ_AIA_CUSTOM_PKG
Source
1 PACKAGE BODY HZ_AIA_CUSTOM_PKG AS
2 /* $Header: ARHAIACB.pls 120.0.12010000.3 2009/08/06 03:24:42 vsegu ship $ */
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(+)
155 ) AS CRMINTEG_HZ_MRGDTIL_OBJ_TBL ),
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
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
267 END IF;
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);
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,
272 p_msg_level=>fnd_log.level_procedure);
273 END IF;
274 WHEN OTHERS THEN
275 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
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
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
397 and mp.batch_party_id = mph.batch_party_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
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
496 and r2.subject_Type = 'PERSON' and r2.object_type = 'ORGANIZATION'
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.
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,
623 p_msg_level=>fnd_log.level_procedure);
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,
738 p_msg_level=>fnd_log.level_procedure);
739 END IF;
740 END sync_acct_update;
741
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
856 -- for each party site, set party_site_id for cust acct site under acct
857 IF(x_return_obj.organization_obj.party_site_objs IS NOT NULL AND
858 x_return_obj.organization_obj.party_site_objs.COUNT > 0) THEN
859 FOR i IN 1..x_return_obj.organization_obj.party_site_objs.COUNT LOOP
860 FOR j IN 1..l_input_acct_obj.cust_acct_site_objs.COUNT LOOP
861 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
862 l_input_acct_obj.cust_acct_site_objs(j).party_site_id := x_return_obj.organization_obj.party_site_objs(i).party_site_id;
863 END IF;
864 END LOOP;
865 END LOOP;
866 END IF;
867
868 -- for each org contact, add contact_person_id, relationship_type, relationship_code
869 -- start_date and role_type for cust acct contact under acct
870 IF(x_return_obj.organization_obj.contact_objs IS NOT NULL AND
871 x_return_obj.organization_obj.contact_objs.COUNT > 0) THEN
872 FOR i IN 1..x_return_obj.organization_obj.contact_objs.COUNT LOOP
873 FOR j IN 1..l_input_acct_obj.cust_acct_contact_objs.COUNT LOOP
874 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
875 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;
876 l_input_acct_obj.cust_acct_contact_objs(j).relationship_code := x_return_obj.organization_obj.contact_objs(i).relationship_code;
877 l_input_acct_obj.cust_acct_contact_objs(j).relationship_type := x_return_obj.organization_obj.contact_objs(i).relationship_type;
878 l_input_acct_obj.cust_acct_contact_objs(j).start_date := x_return_obj.organization_obj.contact_objs(i).start_date;
879 END IF;
880 END LOOP;
881 END LOOP;
882 END IF;
883
884 -- if organization already exists, try to get cust_acct_site_id
885 IF(p_org_cust_obj.organization_obj.organization_id IS NOT NULL AND
886 l_input_acct_obj.cust_acct_site_objs IS NOT NULL AND
887 l_input_acct_obj.cust_acct_site_objs.COUNT > 0) THEN
888 FOR k IN 1..l_input_acct_obj.cust_acct_site_objs.COUNT LOOP
889 OPEN get_cas_id(l_input_acct_obj.cust_acct_site_objs(k).party_site_id,
890 l_input_acct_obj.cust_acct_id,
891 l_input_acct_obj.cust_acct_site_objs(k).org_id);
892 FETCH get_cas_id INTO l_input_acct_obj.cust_acct_site_objs(k).cust_acct_site_id;
893 CLOSE get_cas_id;
894 IF(l_input_acct_obj.cust_acct_site_objs(k).cust_acct_site_use_objs IS NOT NULL AND
895 l_input_acct_obj.cust_acct_site_objs(k).cust_acct_site_use_objs.COUNT > 0) THEN
896 FOR l IN 1..l_input_acct_obj.cust_acct_site_objs(k).cust_acct_site_use_objs.COUNT LOOP
897 OPEN get_casu_id(l_input_acct_obj.cust_acct_site_objs(k).cust_acct_site_id,
898 l_input_acct_obj.cust_acct_site_objs(k).cust_acct_site_use_objs(l).site_use_code,
899 l_input_acct_obj.cust_acct_site_objs(k).cust_acct_site_use_objs(l).org_id);
900 FETCH get_casu_id INTO l_input_acct_obj.cust_acct_site_objs(k).cust_acct_site_use_objs(l).site_use_id;
901 CLOSE get_casu_id;
902 END LOOP;
903 END IF;
904 END LOOP;
905 END IF; --8745333
906
907 FOR l IN 1..l_input_acct_obj.cust_acct_contact_objs.COUNT LOOP
908 OPEN get_cac_id(l_input_acct_obj.cust_acct_contact_objs(l).contact_person_id,
909 l_input_acct_obj.cust_acct_id);
910 FETCH get_cac_id INTO l_input_acct_obj.cust_acct_contact_objs(l).cust_acct_contact_id;
911 CLOSE get_cac_id;
912 END LOOP;
913
914 l_org_cust_bo := HZ_ORG_CUST_BO(null,null,HZ_CUST_ACCT_BO_TBL());
915 l_org_obj := HZ_ORGANIZATION_BO.create_object(
916 p_organization_id => l_organization_id);
917 l_org_cust_bo.organization_obj := l_org_obj;
918 l_org_cust_bo.account_objs.EXTEND;
919 l_org_cust_bo.account_objs(1) := l_input_acct_obj;
920
921 UPDATE HZ_PARTIES
922 set ORG_CUST_BO_VERSION = ( SELECT BO_VERSION_NUMBER
923 FROM HZ_BUS_OBJ_DEFINITIONS
924 WHERE BUSINESS_OBJECT_CODE = 'ORG_CUST'
925 AND ENTITY_NAME = 'HZ_PARTIES'
926 AND CHILD_BO_CODE IS NULL )
927 WHERE party_id = l_organization_id;
928
929 -- call save_org_cust_bo
930 HZ_ORG_CUST_BO_PUB.save_org_cust_bo(
931 p_validate_bo_flag => p_validate_bo_flag,
932 p_org_cust_obj => l_org_cust_bo,
933 p_created_by_module => p_created_by_module,
934 p_obj_source => p_obj_source,
935 p_return_obj_flag => fnd_api.g_true,
936 x_return_status => x_return_status,
937 x_messages => x_messages,
938 x_return_obj => l_return_obj,
939 x_organization_id => l_organization_id
940 );
941
942 x_return_obj := l_return_obj;
943 x_return_obj.organization_obj := l_return_org_obj;
944 /*
945 l_obj_type := 'ORG';
946
947 -- create/update org cust acct business object then
948 HZ_CUST_ACCT_BO_PUB.save_cust_acct_bo(
949 p_validate_bo_flag => p_validate_bo_flag,
950 p_cust_acct_obj => l_input_acct_obj,
951 p_created_by_module => p_created_by_module,
952 p_obj_source => p_obj_source,
953 p_return_obj_flag => fnd_api.g_true,
954 x_return_status => x_return_status,
955 x_messages => x_messages,
956 x_return_obj => l_return_acct_obj,
957 x_cust_acct_id => l_cust_acct_id,
958 x_cust_acct_os => l_cust_acct_os,
959 x_cust_acct_osr => l_cust_acct_osr,
960 px_parent_id => l_organization_id,
961 px_parent_os => l_organization_os,
962 px_parent_osr => l_organization_osr,
963 px_parent_obj_type => l_obj_type
964 );
965 */
966 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
967 RAISE fnd_api.g_exc_error;
968 END IF;
969 /*
970 x_return_obj.account_objs.EXTEND;
971 x_return_obj.account_objs(1) := l_return_acct_obj;
972 remove_gmiss(
973 px_org_cust => x_return_obj);
974 */
975
976 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
977 hz_utility_v2pub.debug(p_message=>'sync_acct_order(-)',
978 p_prefix=>l_debug_prefix,
979 p_msg_level=>fnd_log.level_procedure);
980 END IF;
981 EXCEPTION
982 WHEN fnd_api.g_exc_error THEN
983 ROLLBACK TO do_sync_acct_order;
984
985 x_return_status := fnd_api.g_ret_sts_error;
986
987 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
988 hz_utility_v2pub.debug(p_message=>'sync_acct_order(-)',
989 p_prefix=>l_debug_prefix,
990 p_msg_level=>fnd_log.level_procedure);
991 END IF;
992 WHEN fnd_api.g_exc_unexpected_error THEN
993 ROLLBACK TO do_sync_acct_order;
994
995 x_return_status := fnd_api.g_ret_sts_error;
996
997 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
998 hz_utility_v2pub.debug(p_message=>'sync_acct_order(-)',
999 p_prefix=>l_debug_prefix,
1000 p_msg_level=>fnd_log.level_procedure);
1001 END IF;
1002 WHEN OTHERS THEN
1003 ROLLBACK TO do_sync_acct_order;
1004
1005 x_return_status := fnd_api.g_ret_sts_unexp_error;
1006
1007 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
1008 fnd_message.set_token('ERROR' ,SQLERRM);
1009 fnd_msg_pub.add;
1010
1011 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1012 hz_utility_v2pub.debug(p_message=>'sync_acct_order(-)',
1013 p_prefix=>l_debug_prefix,
1014 p_msg_level=>fnd_log.level_procedure);
1015 END IF;
1016 END sync_acct_order;
1017
1018
1019 PROCEDURE get_merge_org_custs(
1020 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1021 p_from_org_id IN NUMBER,
1022 p_to_org_id IN NUMBER,
1023 p_from_acct_id IN NUMBER,
1024 p_to_acct_id IN NUMBER,
1025 x_org_cust_objs OUT NOCOPY HZ_ORG_CUST_BO_TBL,
1026 x_return_status OUT NOCOPY VARCHAR2,
1027 x_messages OUT NOCOPY HZ_MESSAGE_OBJ_TBL
1028 ) IS
1029 l_debug_prefix VARCHAR2(30) := '';
1030 l_msg_count NUMBER;
1031 l_msg_data VARCHAR2(2000);
1032 l_org_id NUMBER;
1033 l_acct_id NUMBER;
1034 BEGIN
1035 -- initialize API return status to success.
1036 x_return_status := FND_API.G_RET_STS_SUCCESS;
1037
1038 -- Initialize message list if p_init_msg_list is set to TRUE
1039 IF FND_API.to_Boolean(p_init_msg_list) THEN
1040 FND_MSG_PUB.initialize;
1041 END IF;
1042
1043 -- Debug info.
1044 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1045 hz_utility_v2pub.debug(p_message=>'get_merge_org_custs(+)',
1046 p_prefix=>l_debug_prefix,
1047 p_msg_level=>fnd_log.level_procedure);
1048 END IF;
1049
1050 x_org_cust_objs := HZ_ORG_CUST_BO_TBL();
1051
1052 FOR i IN 1..2 LOOP
1053 -- If same from to account and same from to org
1054 -- quit the 2nd loop
1055 IF i = 2 AND p_from_org_id = p_to_org_id AND p_from_acct_id = p_to_acct_id THEN
1056 EXIT;
1057 END IF;
1058
1059 x_org_cust_objs.EXTEND;
1060 x_org_cust_objs(i) := HZ_ORG_CUST_BO(NULL, NULL, NULL);
1061 IF i = 1 THEN
1062 l_org_id := p_from_org_id;
1063 l_acct_id := p_from_acct_id;
1064 ELSE
1065 l_org_id := p_to_org_id;
1066 l_acct_id := p_to_acct_id;
1067 END IF;
1068
1069 HZ_EXTRACT_ORGANIZATION_BO_PVT.get_organization_bo(
1070 p_init_msg_list => fnd_api.g_false,
1071 p_organization_id => l_org_id,
1072 p_action_type => NULL,
1073 x_organization_obj => x_org_cust_objs(i).organization_obj,
1074 x_return_status => x_return_status,
1075 x_msg_count => l_msg_count,
1076 x_msg_data => l_msg_data);
1077
1078 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1079 RAISE FND_API.G_EXC_ERROR;
1080 END IF;
1081 /*
1082 IF(x_org_cust_obj.organization_obj IS NULL OR x_org_cust_obj.organization_obj.organization_id IS NULL)THEN
1083 fnd_message.set_name('AR', 'HZ_API_INVALID_TCA_ID');
1084 fnd_msg_pub.add;
1085 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1086 p_count => l_msg_count,
1087 p_data => l_msg_data);
1088 RAISE FND_API.G_EXC_ERROR;
1089 END IF;
1090 */
1091 HZ_EXTRACT_CUST_ACCT_BO_PVT.get_cust_acct_bos(
1092 p_init_msg_list => fnd_api.g_false,
1093 p_parent_id => l_org_id,
1094 p_cust_acct_id => l_acct_id,
1095 p_action_type => NULL,
1096 x_cust_acct_objs => x_org_cust_objs(i).account_objs,
1097 x_return_status => x_return_status,
1098 x_msg_count => l_msg_count,
1099 x_msg_data => l_msg_data);
1100
1101 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1102 RAISE FND_API.G_EXC_ERROR;
1103 END IF;
1104 END LOOP;
1105 /*
1106 IF(x_org_cust_obj.account_objs IS NULL OR x_org_cust_obj.account_objs.COUNT < 1)THEN
1107 fnd_message.set_name('AR', 'HZ_API_INVALID_TCA_ID');
1108 fnd_msg_pub.add;
1109 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1110 p_count => l_msg_count,
1111 p_data => l_msg_data);
1112 RAISE FND_API.G_EXC_ERROR;
1113 END IF;
1114 */
1115 -- Debug info.
1116 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
1117 hz_utility_v2pub.debug_return_messages(p_msg_count=>l_msg_count,
1118 p_msg_data=>l_msg_data,
1119 p_msg_type=>'WARNING',
1120 p_msg_level=>fnd_log.level_exception);
1121 END IF;
1122
1123 -- Debug info.
1124 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1125 hz_utility_v2pub.debug(p_message=>'get_merge_org_custs(-)',
1126 p_prefix=>l_debug_prefix,
1127 p_msg_level=>fnd_log.level_procedure);
1128 END IF;
1129 EXCEPTION
1130 WHEN fnd_api.g_exc_error THEN
1131 x_return_status := fnd_api.g_ret_sts_error;
1132 x_messages := HZ_PARTY_BO_PVT.return_all_messages(
1133 x_return_status => x_return_status,
1134 x_msg_count => l_msg_count,
1135 x_msg_data => l_msg_data);
1136
1137 -- Debug info.
1138 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1139 hz_utility_v2pub.debug_return_messages(p_msg_count=>l_msg_count,
1140 p_msg_data=>l_msg_data,
1141 p_msg_type=>'ERROR',
1142 p_msg_level=>fnd_log.level_error);
1143 END IF;
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 WHEN fnd_api.g_exc_unexpected_error THEN
1150 x_return_status := fnd_api.g_ret_sts_unexp_error;
1151 x_messages := HZ_PARTY_BO_PVT.return_all_messages(
1152 x_return_status => x_return_status,
1153 x_msg_count => l_msg_count,
1154 x_msg_data => l_msg_data);
1155
1156 -- Debug info.
1157 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1158 hz_utility_v2pub.debug_return_messages(p_msg_count=>l_msg_count,
1159 p_msg_data=>l_msg_data,
1160 p_msg_type=>'UNEXPECTED ERROR',
1161 p_msg_level=>fnd_log.level_error);
1162 END IF;
1163 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1164 hz_utility_v2pub.debug(p_message=>'get_merge_org_custs(-)',
1165 p_prefix=>l_debug_prefix,
1166 p_msg_level=>fnd_log.level_procedure);
1167 END IF;
1168 WHEN OTHERS THEN
1169 x_return_status := fnd_api.g_ret_sts_unexp_error;
1170 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
1171 fnd_message.set_token('ERROR' ,SQLERRM);
1172 fnd_msg_pub.add;
1173 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1174 p_count => l_msg_count,
1175 p_data => l_msg_data);
1176 x_messages := HZ_PARTY_BO_PVT.return_all_messages(
1177 x_return_status => x_return_status,
1178 x_msg_count => l_msg_count,
1179 x_msg_data => l_msg_data);
1180
1181 -- Debug info.
1182 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1183 hz_utility_v2pub.debug_return_messages(p_msg_count=>l_msg_count,
1184 p_msg_data=>l_msg_data,
1185 p_msg_type=>'SQL ERROR',
1186 p_msg_level=>fnd_log.level_error);
1187 END IF;
1188 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1189 hz_utility_v2pub.debug(p_message=>'get_merge_org_custs(-)',
1190 p_prefix=>l_debug_prefix,
1191 p_msg_level=>fnd_log.level_procedure);
1192 END IF;
1193 END get_merge_org_custs;
1194
1195 END HZ_AIA_CUSTOM_PKG;