1 PACKAGE BODY IBE_CUSTOMER_ACCT_PVT AS
2 /* $Header: IBEVCACB.pls 120.4.12020000.2 2012/11/12 06:32:18 nsatyava ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IBE_CUSTOMER_ACCT_PVT';
5 l_true VARCHAR2(1) := FND_API.G_TRUE;
6
7 PROCEDURE GetPartySiteId(
8 p_api_version_number IN NUMBER,
9 p_init_msg_list IN VARCHAR2,
10 p_commit IN VARCHAR2,
11 p_party_id IN NUMBER,
12 p_site_use_type IN VARCHAR2,
13 x_party_site_id OUT NOCOPY NUMBER,
14 x_return_status OUT NOCOPY VARCHAR2,
15 x_msg_count OUT NOCOPY NUMBER,
16 x_msg_data OUT NOCOPY VARCHAR2)
17 IS
18
19 l_hr_type VARCHAR2(30) := ' ';
20
21 --l_orgId1 number := SUBSTRB(USERENV('CLIENT_INFO'),1,1);
22 --l_orgId2 number := SUBSTRB(USERENV('CLIENT_INFO'),1,10);
23
24 l_return_values varchar2(2000);
25 l_api_version NUMBER := 1.0;
26 l_api_name VARCHAR2(30) := 'CUSTACCT_GETPARTYSITEID';
27
28 -- 14763493 INTERMITTENTLY SHIP-TO ERROR WHILE CREATING RMA
29 /*CURSOR c_getOrgId
30 IS
31 SELECT NVL(TO_NUMBER(DECODE(l_orgId1,' ',NULL,l_orgId2)),-99) orgId from dual;
32 */
33
34 l_orgId NUMBER;
35
36 -- 4922991. CHECK FOR HZ_CUST_SITE_USES.STATUS='I' MAY NOT BE CORRECT IN SOME CASES
37 CURSOR C_address(c_party_id NUMBER,
38 c_site_use_type VARCHAR2,
39 c_hr_type VARCHAR2,
40 c_org_id NUMBER)
41 IS
42 SELECT DECODE(site_use_type, c_site_use_type, 1, 2) first_orderby,
43 DECODE(primary_per_type,'Y', 1, 2) second_orderby,
44 party_site_id,
45 site_use_type,
46 primary_per_type
47 FROM (
48 SELECT party_site_id, ps.site_use_type, ps.primary_per_type
49 FROM hz_party_sites_v ps, hr_organization_information hr
50 WHERE ps.party_id = c_party_id
51 AND ps.status = 'A'
52 AND nvl(ps.end_date_active, sysdate + 1) > sysdate
53 AND hr.organization_id = c_org_id
54 AND hr.org_information_context = c_hr_type
55 AND hr.org_information1 = ps.country
56 UNION
57 SELECT party_site_id, ps.site_use_type, ps.primary_per_type
58 FROM hz_party_sites_v ps
59 WHERE ps.party_id = c_party_id
60 AND ps.status = 'A'
61 AND nvl(ps.end_date_active,sysdate + 1) > sysdate
62 AND NOT EXISTS (
63 SELECT 1
64 FROM hr_organization_information hr
65 WHERE hr.organization_id = c_org_id
66 AND hr.org_information_context = c_hr_type
67 AND rownum = 1
68 )
69 ) o
70 WHERE ( NOT EXISTS (SELECT 1 FROM hz_party_site_uses psu
71 WHERE psu.party_site_id = o.party_site_id
72 AND psu.site_use_type = o.site_use_type
73 ) OR
74 EXISTS (SELECT 1 FROM hz_party_site_uses psu
75 WHERE psu.party_site_id = o.party_site_id
76 AND psu.site_use_type = o.site_use_type
77 AND psu.status = 'A')
78 )
79 AND ( NOT EXISTS (SELECT 1 FROM hz_cust_acct_sites cas
80 WHERE cas.party_site_id = o.party_site_id
81 ) OR
82 (EXISTS (SELECT 1 FROM hz_cust_acct_sites cas
83 where cas.party_site_id = o.party_site_id
84 and cas.status = 'A')
85 AND
86 ( NOT EXISTS (SELECT 1 FROM hz_cust_acct_sites cas, hz_cust_site_uses_all csu
87 WHERE csu.cust_acct_site_id = cas.cust_acct_site_id
88 AND cas.party_site_id = o.party_site_id
89 AND cas.org_id = csu.org_id
90 AND csu.site_use_code = o.site_use_type)
91 OR
92 EXISTS ( SELECT 1
93 FROM hz_cust_acct_sites_all cas, hz_cust_site_uses_all csu
94 WHERE cas.party_site_id = o.party_site_id
95 AND cas.org_id = csu.org_id
96 AND csu.cust_acct_site_id = cas.cust_acct_site_id
97 AND csu.status = 'A'
98 AND csu.site_use_code= o.site_use_type
99 )
100 )
101 )
102 )
103 ORDER BY 1,2;
104
105
106 l_address C_address%ROWTYPE;
107
108 BEGIN
109
110 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
111 Ibe_Util.debug('Begin IBE_CUSTOMER_ACCT_PVT:GetPartySiteId');
112 END IF;
113
114 -- Standard Start of API savepoint
115 SAVEPOINT CUSTACCT_GETPARTYSITEID;
116
117 -- Standard call to check for call compatibility.
118 IF NOT FND_API.Compatible_API_Call (l_api_version,
119 P_Api_Version_Number,
120 l_api_name,
121 G_PKG_NAME )
122 THEN
123 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
124 END IF;
125
126 -- Initialize message list if p_init_msg_list is set to TRUE.
127 IF FND_API.To_Boolean( p_init_msg_list ) THEN
128 FND_Msg_Pub.initialize;
129 END IF;
130
131 -- Initialize API return status to success
132 x_return_status := FND_API.G_RET_STS_SUCCESS;
133
134 -- Start OF API body --
135
136 IF (p_site_use_type = 'SHIP_TO') THEN
137 l_hr_type := 'SHIP_TO_COUNTRY';
138 END IF;
139 IF (p_site_use_type = 'BILL_TO') THEN
140 l_hr_type := 'BILL_TO_COUNTRY';
141 END IF;
142
143 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
144 Ibe_Util.debug('before calling c_address p_site_use_type:' || p_site_use_type);
145 Ibe_Util.debug('p_party_id:' || p_party_id);
146 Ibe_Util.debug('l_hr_type:' || l_hr_type);
147 Ibe_Util.debug('l_orgId:' || l_orgId);
148 END IF;
149
150 OPEN C_address(p_party_id, p_site_use_type, l_hr_type, l_orgId);
151 FETCH C_address INTO l_address;
152 x_party_site_id := l_address.party_site_id;
153 IF C_address%NOTFOUND THEN
154 x_party_site_id := -1;
155 END IF;
156 CLOSE C_address;
157
158 --
159 -- End of API body
160 --
161
162 -- Standard check for p_commit
163 IF FND_API.to_Boolean( p_commit )
164 THEN
165 COMMIT WORK;
166 END IF;
167
168 -- Standard call to get message count and if count is 1, get message info.
169 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
170 p_data => x_msg_data);
171
172 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
173 Ibe_Util.debug('End IBE_CUSTOMER_ACCT_PVT:GetPartySiteId');
174 END IF;
175
176 EXCEPTION
177 WHEN FND_API.G_EXC_ERROR THEN
178 ROLLBACK TO CUSTACCT_GETPARTYSITEID;
179 x_return_status := FND_API.G_RET_STS_ERROR;
180 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
181 p_count => x_msg_count ,
182 p_data => x_msg_data);
183 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
184 IBE_Util.Debug('Error IBE_CUSTOMER_ACCT_PVT:GetPartySiteId()'|| sqlerrm);
185 END IF;
186
187 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
188 ROLLBACK TO CUSTACCT_GETPARTYSITEID;
189 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
190 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
191 p_count => x_msg_count ,
192 p_data => x_msg_data);
193 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
194 IBE_Util.Debug('UNEXPECTEDErr IBE_CUSTOMER_ACCT_PVT:GetPartySiteId()' || sqlerrm);
195 END IF;
196
197 WHEN OTHERS THEN
198 ROLLBACK TO CUSTACCT_GETPARTYSITEID;
199 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
200 IF FND_Msg_Pub.Check_Msg_Level( FND_Msg_Pub.G_MSG_LVL_UNEXP_ERROR ) THEN
201 FND_Msg_Pub.Add_Exc_Msg(G_PKG_NAME,
202 L_API_NAME);
203 END IF;
204 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
205 p_count => x_msg_count ,
206 p_data => x_msg_data);
207 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
208 IBE_Util.Debug('OtherExc IBE_CUSTOMER_ACCT_PVT:GetPartySiteId' || sqlerrm);
209 END IF;
210
211 END GetPartySiteId;
212
213 /*
214 PROCEDURE Create_Party_Site_Use(
215 p_api_version_number IN NUMBER,
216 p_init_msg_list IN VARCHAR2,
217 p_commit IN VARCHAR2,
218 p_party_site_id IN NUMBER,
219 p_party_site_use_type IN VARCHAR2,
220 x_party_site_use_id OUT NOCOPY NUMBER,
221 x_return_status OUT NOCOPY VARCHAR2,
222 x_msg_count OUT NOCOPY NUMBER,
223 x_msg_data OUT NOCOPY VARCHAR2
224 )
225 IS
226
227 l_party_site_use_rec HZ_PARTY_SITE_V2PUB.Party_Site_Use_Rec_Type;
228 --## l_party_site_use_rec HZ_PARTY_PUB.Party_Site_Use_Rec_Type;
229 l_return_status VARCHAR2(1):=FND_API.G_RET_STS_SUCCESS;
230 l_party_site_use_id NUMBER;
231
232 l_return_values varchar2(2000);
233 l_api_version NUMBER := 1.0;
234 l_api_name VARCHAR2(30) := 'CUSTACCT_CREATEPARTYSITEUSE';
235
236 BEGIN
237
238 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
239 Ibe_Util.debug('Begin IBE_CUSTOMER_ACCT_PVT:Create_Party_Site_Use');
240 Ibe_Util.debug('create_pty_site_use:before create_pty_site_use:p_party_site_id: ' || p_party_site_id);
241 Ibe_Util.debug('create_pty_site_use:before create_pty_site_use:p_party_site_use_type: ' || p_party_site_use_type);
242 END IF;
243
244 -- Standard Start of API savepoint
245 SAVEPOINT CUSTACCT_CREATEPARTYSITEUSE;
246
247 -- Standard call to check for call compatibility.
248 IF NOT FND_API.Compatible_API_Call (l_api_version,
249 P_Api_Version_Number,
250 l_api_name,
251 G_PKG_NAME )
252 THEN
253 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
254 END IF;
255
256 -- Initialize message list if p_init_msg_list is set to TRUE.
257 IF FND_API.To_Boolean( p_init_msg_list ) THEN
258 FND_Msg_Pub.initialize;
259 END IF;
260
261 -- Initialize API return status to success
262 x_return_status := FND_API.G_RET_STS_SUCCESS;
263
264 -- Start OF API body --
265
266 l_party_site_use_rec.party_site_id := p_party_site_id;
267 l_party_site_use_rec.site_use_type := p_party_site_use_type;
268
269 HZ_PARTY_SITE_V2PUB.create_party_site_use (
270 p_init_msg_list => FND_API.G_FALSE,
271 p_party_site_use_rec => l_party_site_use_rec,
272 x_party_site_use_id => l_party_site_use_id,
273 x_return_status => x_return_status,
274 x_msg_count => x_msg_count,
275 x_msg_data => x_msg_data );
276
277
278 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
279 Ibe_Util.debug('create_pty_site_use:after create_pty_site_use:x_party_site_use_id: ' || x_party_site_use_id);
280 Ibe_Util.debug('create_pty_site_use:after create_pty_site_use:x_return_status: ' || x_return_status);
281 END IF;
282
283 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
284 x_party_site_use_id := l_party_site_use_id;
285 ELSE
286 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
287 Ibe_Util.debug('Exception in Hz Create_Party_Site_Use: '|| x_msg_data);
288 ENd IF;
289 RAISE FND_API.G_EXC_ERROR;
290 END IF;
291
292 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
293 Ibe_Util.debug('End IBE_CUSTOMER_ACCT_PVT:Create_Party_Site_Use');
294 ENd IF;
295
296 --
297 -- End of API body
298 --
299
300 -- Standard check for p_commit
301 IF FND_API.to_Boolean( p_commit )
302 THEN
303 COMMIT WORK;
304 END IF;
305
306 -- Standard call to get message count and if count is 1, get message info.
307 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
308 p_data => x_msg_data);
309
310 EXCEPTION
311 WHEN FND_API.G_EXC_ERROR THEN
312 ROLLBACK TO CUSTACCT_CREATEPARTYSITEUSE;
313 x_return_status := FND_API.G_RET_STS_ERROR;
314 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
315 p_count => x_msg_count ,
316 p_data => x_msg_data);
317 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
318 IBE_Util.Debug('Error IBE_CUSTOMER_ACCT_PVT:Create_Party_Site_Use'|| sqlerrm);
319 END IF;
320
321 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
322 ROLLBACK TO CUSTACCT_CREATEPARTYSITEUSE;
323 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
324 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
325 p_count => x_msg_count ,
326 p_data => x_msg_data);
327 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
328 IBE_Util.Debug('UNEXPECTEDErr IBE_CUSTOMER_ACCT_PVT:Create_Party_Site_Use' || sqlerrm);
329 END IF;
330
331 WHEN OTHERS THEN
332 ROLLBACK TO CUSTACCT_CREATEPARTYSITEUSE;
333 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
334 IF FND_Msg_Pub.Check_Msg_Level( FND_Msg_Pub.G_MSG_LVL_UNEXP_ERROR ) THEN
335 FND_Msg_Pub.Add_Exc_Msg(G_PKG_NAME,
336 L_API_NAME);
337 END IF;
338 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
339 p_count => x_msg_count ,
340 p_data => x_msg_data);
341 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
342 IBE_Util.Debug('OtherExc IBE_CUSTOMER_ACCT_PVT:Create_Party_Site_Use' || sqlerrm);
343 END IF;
344 END Create_Party_Site_Use;
345 */
346
347 PROCEDURE create_cust_account_role(
348 p_api_version_number IN NUMBER,
349 p_init_msg_list IN VARCHAR2,
350 p_commit IN VARCHAR2,
351 p_party_id IN NUMBER,
352 p_cust_acct_id IN NUMBER,
353 p_cust_acct_site_id IN NUMBER,
354 p_role_type IN VARCHAR2, -- this is only for gettting the respid.
355 x_cust_acct_role_id OUT NOCOPY NUMBER,
356 x_return_status OUT NOCOPY VARCHAR2,
357 x_msg_count OUT NOCOPY NUMBER,
358 x_msg_data OUT NOCOPY VARCHAR2
359 )
360 IS
361
362 --## p_cust_acct_roles_rec hz_customer_accounts_pub.cust_acct_roles_rec_type;
363 --## p_role_resp_rec hz_customer_accounts_pub.role_resp_rec_type;
364
365 -- The below two record definitions are for V2 APIs
366 p_cust_acct_roles_rec HZ_CUST_ACCOUNT_ROLE_V2PUB.cust_account_role_rec_type;
367 p_role_resp_rec HZ_CUST_ACCOUNT_ROLE_V2PUB.role_responsibility_rec_type;
368
369 l_responsibility_id NUMBER;
370
371 CURSOR C_Get_Resp(role_id NUMBER, role_type VARCHAR2) IS
372 SELECT responsibility_id
373 FROM hz_role_responsibility
374 WHERE cust_account_role_id = role_id
375 AND responsibility_type = role_type;
376
377 l_return_status VARCHAR2(1):=FND_API.G_RET_STS_SUCCESS;
378 l_msg_count NUMBER :=0;
379 l_msg_data VARCHAR2(2000):='';
380 l_api_version NUMBER := 1.0;
381 l_api_name VARCHAR2(30) := 'CUSTACCT_CREATCUSTACCTROLE';
382
383
384 BEGIN
385
386 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
387 Ibe_Util.debug('BEgin IBE_CUSTOMER_ACCT_PVT:create_cust_account_role');
388 END IF;
389
390 -- Standard Start of API savepoint
391 SAVEPOINT CUSTACCT_CREATCUSTACCTROLE;
392
393 -- Standard call to check for call compatibility.
394 IF NOT FND_API.Compatible_API_Call (l_api_version,
395 P_Api_Version_Number,
396 l_api_name,
397 G_PKG_NAME )
398 THEN
399 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
400 END IF;
401
402 -- Initialize message list if p_init_msg_list is set to TRUE.
403 IF FND_API.To_Boolean( p_init_msg_list ) THEN
404 FND_Msg_Pub.initialize;
405 END IF;
406
407 -- Initialize API return status to success
408 x_return_status := FND_API.G_RET_STS_SUCCESS;
409
410 -- Start OF API body --
411
412 p_cust_acct_roles_rec.party_id := p_party_id;
413 p_cust_acct_roles_rec.cust_account_id := p_cust_acct_id;
414 p_cust_acct_roles_rec.role_type := 'CONTACT'; -- it should be always contact
415 p_cust_acct_roles_rec.cust_acct_site_id := p_cust_acct_site_id;
416 -- Initializing the created_by_module column for all the records as per
417 -- changes in version 2 api's.
418 p_cust_acct_roles_rec.Created_by_Module := 'IBE_CUSTOMER_DATA';
419
420 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
421 IBE_Util.debug('before create cust acct roles');
422 END IF;
423
424
425 -- old TCA API call
426 -- hz_customer_accounts_pub.create_cust_acct_roles.
427
428
429 HZ_CUST_ACCOUNT_ROLE_V2PUB.create_cust_account_role (
430 p_init_msg_list => FND_API.G_FALSE,
431 p_cust_account_role_rec => p_cust_acct_roles_rec,
432 x_cust_account_role_id => x_cust_acct_role_id,
433 x_return_status => l_return_status,
434 x_msg_count => l_msg_count,
435 x_msg_data => l_msg_data );
436
437
438 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
439 IBE_Util.debug('create_contact_role:after create_cust_acct_role: x_return_status: '||l_return_status);
440 END IF;
441
442 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
443 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
444 IBE_Util.debug('Exception in HZ create_cust_account_role: '||l_msg_data);
445 END IF;
446 RAISE FND_API.G_EXC_ERROR;
447 END IF;
448
449 -- Setting Responsibility id for newly created role id.
450
451 IF (x_cust_acct_role_id is not null AND
452 x_cust_acct_role_id <> FND_API.G_MISS_NUM)
453 THEN
454
455 OPEN C_Get_Resp(x_cust_acct_role_id, p_role_type);
456 FETCH C_Get_Resp INTO l_responsibility_id;
457 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
458 IBE_Util.debug('create_contact_role l_responsibility_id: '||l_responsibility_id);
459 END IF;
460 IF C_Get_Resp%NOTFOUND THEN
461
462 IF p_cust_acct_site_id is not NULL AND
463 p_cust_acct_site_id <> FND_API.G_MISS_NUM
464 THEN
465 p_role_resp_rec.cust_account_role_id := x_cust_acct_role_id;
466 p_role_resp_rec.responsibility_type := p_role_type;
467 p_role_resp_rec.Created_by_Module := 'IBE_CUSTOMER_DATA';
468
469 -- old TCA API call
470 -- HZ_CUSTOMER_ACCOUNTS_PUB.create_role_resp.
471
472 -- version 2 API
473 HZ_CUST_ACCOUNT_ROLE_V2PUB.create_role_responsibility (
474 p_init_msg_list => FND_API.G_FALSE,
475 p_role_responsibility_rec => p_role_resp_rec,
476 x_responsibility_id => l_responsibility_id,
477 x_return_status => l_return_status,
478 x_msg_count => l_msg_count,
479 x_msg_data => l_msg_data );
480
481
482
483 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
484 IBE_Util.debug('create_contact_role:after create_role_resp: x_return_status: '||l_return_status);
485 END IF;
486
487 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
488 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
489 IBE_Util.debug('Exception in HZ create_role_responsibility: '||l_msg_data);
490 END IF;
491 RAISE FND_API.G_EXC_ERROR;
492 END IF;
493
494 END IF; --if p_cust_account_site_id is not NULL
495 END IF; -- IF C_Get_Resp%NOTFOUND THEN
496 CLOSE C_Get_Resp;
497 END IF;
498
499 --
500 -- End of API body
501 --
502
503 -- Standard check for p_commit
504 IF FND_API.to_Boolean( p_commit )
505 THEN
506 COMMIT WORK;
507 END IF;
508
509 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
510 IBE_Util.debug('End IBE_CUSTOMER_ACCT_PVT:create_cust_account_role');
511 END IF;
512
513 -- Standard call to get message count and if count is 1, get message info.
514 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
515 p_data => x_msg_data);
516
517 EXCEPTION
518
519 WHEN FND_API.G_EXC_ERROR THEN
520 ROLLBACK TO CUSTACCT_CREATCUSTACCTROLE;
521 x_return_status := FND_API.G_RET_STS_ERROR;
522 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
523 p_count => x_msg_count ,
524 p_data => x_msg_data);
525 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
526 IBE_Util.Debug('Error IBE_CUSTOMER_ACCT_PVT:create_cust_account_role()'|| sqlerrm);
527 END IF;
528
529 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
530 ROLLBACK TO CUSTACCT_CREATCUSTACCTROLE;
531 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
532 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
533 p_count => x_msg_count ,
534 p_data => x_msg_data);
535 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
536 IBE_Util.Debug('UNEXPECTEDErr IBE_CUSTOMER_ACCT_PVT:create_cust_account_role' || sqlerrm);
537 END IF;
538
539 WHEN OTHERS THEN
540 ROLLBACK TO CUSTACCT_CREATCUSTACCTROLE;
541 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
542 IF FND_Msg_Pub.Check_Msg_Level( FND_Msg_Pub.G_MSG_LVL_UNEXP_ERROR ) THEN
543 FND_Msg_Pub.Add_Exc_Msg(G_PKG_NAME,
544 L_API_NAME);
545 END IF;
546
547 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
548 p_count => x_msg_count ,
549 p_data => x_msg_data);
550 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
551 IBE_Util.Debug('OtherExc IBE_CUSTOMER_ACCT_PVT:create_cust_account_role)' || sqlerrm);
552 END IF;
553
554 ENd create_cust_account_role;
555
556
557 PROCEDURE Create_Cust_Acct_Site(
558 p_api_version_number IN NUMBER
559 ,p_init_msg_list IN VARCHAR2
560 ,p_commit IN VARCHAR2
561 ,p_partysite_id IN NUMBER
562 ,p_custacct_id IN NUMBER
563 ,p_custacct_type IN VARCHAR2
567 ,x_msg_data OUT NOCOPY VARCHAR2
564 ,x_custacct_site_id OUT NOCOPY NUMBER
565 ,x_return_status OUT NOCOPY VARCHAR2
566 ,x_msg_count OUT NOCOPY NUMBER
568 )
569 IS
570
571 CURSOR c_acct_site (account_id NUMBER, site_id NUMBER) IS
572 SELECT cust_acct_site_id
573 FROM hz_cust_acct_sites
574 WHERE cust_account_id = account_id
575 AND party_site_id = site_id;
576
577 p_acct_site_rec HZ_CUST_ACCOUNT_SITE_V2PUB.cust_acct_site_rec_type;
578 -- ## p_acct_site_rec hz_customer_accounts_pub.acct_site_rec_type;
579 l_customer_site_id NUMBER := NULL;
580 l_return_status VARCHAR2(1):=FND_API.G_RET_STS_SUCCESS;
581 l_msg_count NUMBER :=0;
582 l_msg_data VARCHAR2(2000):='';
583 l_api_version NUMBER := 1.0;
584 l_api_name VARCHAR2(30) := 'CUSTACCT_CREATCUSTACCTSITE';
585
586
587 BEGIN
588
589 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
590 IBE_Util.debug('Begin IBE_CUSTOMER_ACCT_PVT:Create_Cust_Acct_Site ');
591 IBE_Util.debug('Create_Cust_Acct_Site: '||p_custacct_type||' : ' ||p_partysite_id);
592 END IF;
593
594 -- Standard Start of API savepoint
595 SAVEPOINT CUSTACCT_CREATCUSTACCTSITE;
596
597 -- Standard call to check for call compatibility.
598 IF NOT FND_API.Compatible_API_Call (l_api_version,
599 P_Api_Version_Number,
600 l_api_name,
601 G_PKG_NAME )
602 THEN
603 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
604 END IF;
605
606 -- Initialize message list if p_init_msg_list is set to TRUE.
607 IF FND_API.To_Boolean( p_init_msg_list ) THEN
608 FND_Msg_Pub.initialize;
609 END IF;
610
611 -- Initialize API return status to success
612 x_return_status := FND_API.G_RET_STS_SUCCESS;
613
614 -- Start OF API body --
615
616 Open c_acct_site (p_custacct_id, p_partysite_id);
617 Fetch c_acct_site into l_customer_site_id;
618 IF (c_acct_site%NOTFOUND) THEN
619 l_customer_site_id := null;
620 END IF;
621 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
622 IBE_Util.debug('Create_Cust_Acct_Site l_customer_site_id: '||l_customer_site_id);
623 END IF;
624 Close c_acct_site;
625
626 IF l_customer_site_id is not NULL THEN
627 x_custacct_site_id := l_customer_site_id ;
628 ELSE
629 p_acct_site_rec.cust_account_id := p_custacct_id;
630 p_acct_site_rec.party_site_id := p_partysite_id;
631 -- Intializing created_by_module as required in version 2 api for the record structure
632 p_acct_site_rec.created_by_module := 'IBE_CUSTOMER_DATA';
633
634 -- old TCA API call
635 --hz_customer_accounts_pub.create_account_site
636
637 HZ_CUST_ACCOUNT_SITE_V2PUB.create_cust_acct_site (
638 p_init_msg_list => FND_API.G_FALSE,
639 p_cust_acct_site_rec => p_acct_site_rec,
640 x_cust_acct_site_id => l_customer_site_id,
641 x_return_status => l_return_status,
642 x_msg_count => l_msg_count,
643 x_msg_data => l_msg_data );
644
645 END IF;
646
647 IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
648 x_custacct_site_id := l_customer_site_id ;
649 else
650 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
651 IBE_Util.debug('Exception in HZ create_cust_acct_site: '||l_msg_data);
652 END IF;
653 RAISE FND_API.G_EXC_ERROR;
654 end if;
655
656 --
657 -- End of API body
658 --
659
660 -- Standard check for p_commit
661 IF FND_API.to_Boolean( p_commit )
662 THEN
663 COMMIT WORK;
664 END IF;
665
666
667 -- Standard call to get message count and if count is 1, get message info.
668 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
669 p_data => x_msg_data);
670
671
672 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
673 IBE_Util.debug('End IBE_CUSTOMER_ACCT_PVT: Create_Cust_Acct_Site custacct_site_id: '||x_custacct_site_id);
674 END IF;
675
676 EXCEPTION
677 WHEN FND_API.G_EXC_ERROR THEN
678 ROLLBACK TO CUSTACCT_CREATCUSTACCTSITE;
679 x_return_status := FND_API.G_RET_STS_ERROR;
680 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
681 p_count => x_msg_count ,
682 p_data => x_msg_data);
683 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
684 IBE_Util.Debug('Error IBE_CUSTOMER_ACCT_PVT: Create_Cust_Acct_Site()'|| sqlerrm);
685 END IF;
686
687 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
688 ROLLBACK TO CUSTACCT_CREATCUSTACCTSITE;
689 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
690 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
691 p_count => x_msg_count ,
692 p_data => x_msg_data);
693 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
694 IBE_Util.Debug('UNEXPECTEDErr IBE_CUSTOMER_ACCT_PVT: Create_Cust_Acct_Site()' || sqlerrm);
695 END IF;
696
697 WHEN OTHERS THEN
698 ROLLBACK TO CUSTACCT_CREATCUSTACCTSITE;
699 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
700 IF FND_Msg_Pub.Check_Msg_Level( FND_Msg_Pub.G_MSG_LVL_UNEXP_ERROR ) THEN
701 FND_Msg_Pub.Add_Exc_Msg(G_PKG_NAME,
702 L_API_NAME);
703 END IF;
704
708 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
705 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
706 p_count => x_msg_count ,
707 p_data => x_msg_data);
709 IBE_Util.Debug('OtherExc IBE_CUSTOMER_ACCT_PVT: Create_Cust_Acct_Site()' || sqlerrm);
710 END IF;
711
712 END Create_Cust_Acct_Site;
713
714
715 PROCEDURE Create_Cust_Acct_Site_Use (
716 p_api_version_number IN NUMBER
717 ,p_init_msg_list IN VARCHAR2
718 ,p_commit IN VARCHAR2
719 ,p_cust_account_Id IN NUMBER
720 ,p_party_site_Id IN NUMBER
721 ,p_cust_acct_site_id IN NUMBER
722 ,p_acct_site_type IN VARCHAR2
723 ,x_cust_acct_site_id OUT NOCOPY NUMBER
724 ,x_custacct_site_use_id OUT NOCOPY NUMBER
725 ,x_return_status OUT NOCOPY VARCHAR2
726 ,x_msg_count OUT NOCOPY NUMBER
727 ,x_msg_data OUT NOCOPY VARCHAR2
728 )
729 IS
730
731 l_acctsite_uses_rec HZ_CUST_ACCOUNT_SITE_V2PUB.cust_site_use_rec_type;
732 -- ## l_acctsite_uses_rec hz_customer_accounts_pub.acct_site_uses_rec_type;
733 l_cust_profile_rec HZ_CUSTOMER_PROFILE_V2PUB.customer_profile_rec_type;
734 -- ## l_cust_profile_rec hz_customer_accounts_pub.cust_profile_rec_type;
735 l_custacct_site_use_id NUMBER := NULL;
736 l_cust_acct_site_id NUMBER := NULL;
737 l_profile varchar2(1):= '';
738 l_location VARCHAR2(40):= '';
739 l_return_status VARCHAR2(1):=FND_API.G_RET_STS_SUCCESS;
740
741 l_party_site_use NUMBER := null;
742 l_party_site_id NUMBER := null;
743 lx_party_site_use_id NUMBER;
744 l_return_values varchar2(2000);
745 l_api_version NUMBER := 1.0;
746 l_api_name VARCHAR2(30) := 'CUSTACCT_CREATCUSTACCTSITEUSE';
747
748 CURSOR c_site_use(acct_site_id NUMBER, Site_type VARCHAR2) IS
749 SELECT site_use_id
750 FROM hz_cust_site_uses
751 WHERE cust_acct_site_id = acct_site_id
752 AND site_use_code = Site_type;
753
754 CURSOR c_party_site_use(l_party_site_id NUMBER, Site_type VARCHAR2) IS
755 SELECT party_site_use_id
756 FROM hz_party_site_uses
757 WHERE party_site_id = l_party_site_id
758 AND site_use_type = Site_type;
759
760 CURSOR c_location(l_party_site_id NUMBER) IS
761 Select hzl.city
762 From hz_locations hzl,hz_party_sites hps
763 Where hps.party_site_id = p_party_site_id
764 And hzl.location_id = hps.location_id;
765
766
767 BEGIN
768
769 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
770 Ibe_util.Debug('Begin IBE_CUSTOMER_ACCT_PVT:Create_Cust_Acct_Site_Use ' );
771 Ibe_util.Debug('p_acct_site_type: ' || p_acct_site_type);
772 END IF;
773
774
775 -- Standard Start of API savepoint
776 SAVEPOINT CUSTACCT_CREATCUSTACCTSITEUSE;
777
778 -- Standard call to check for call compatibility.
779 IF NOT FND_API.Compatible_API_Call (l_api_version,
780 P_Api_Version_Number,
781 l_api_name,
782 G_PKG_NAME )
783 THEN
784 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
785 END IF;
786
787 -- Initialize message list if p_init_msg_list is set to TRUE.
788 IF FND_API.To_Boolean( p_init_msg_list ) THEN
789 FND_Msg_Pub.initialize;
790 END IF;
791
792 -- Initialize API return status to success
793 x_return_status := FND_API.G_RET_STS_SUCCESS;
794
795 -- Start OF API body --
796
797 l_cust_acct_site_id := p_cust_acct_site_id;
798 l_acctsite_uses_rec.cust_acct_site_id := l_cust_acct_site_id;
799 x_cust_acct_site_id := l_cust_acct_site_id;
800
801 l_acctsite_uses_rec.site_use_code := p_acct_site_type;
802
803 Open c_site_use(l_cust_acct_site_id,p_acct_site_type);
804 Fetch c_site_use into l_custacct_site_use_id;
805 IF (c_site_use%NOTFOUND) THEN
806 l_custacct_site_use_id := null;
807 END IF;
808 Close c_site_use;
809
810 IF l_custacct_site_use_id is not NULL then
811 x_custacct_site_use_id := l_custacct_site_use_id ;
812 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
813 Ibe_util.Debug('x_custacct_site_use_id: ' || x_custacct_site_use_id);
814 END IF;
815 ELSE
816
817 OPEN c_location(p_party_site_id);
818 FETCH c_location into l_location;
819 IF (c_location%NOTFOUND) THEN
820 l_location := 'NO_LOCATION';
821 END IF;
822 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
823 Ibe_util.Debug('l_location: ' || l_location);
824 END IF;
825 CLOSE c_location;
826
827 L_profile := HZ_MO_GLOBAL_CACHE.get_auto_site_numbering(MO_GLOBAL.get_current_org_id());
828
829
830
831 IF l_profile = 'N' then
832 l_acctsite_uses_rec.location := p_acct_site_type ||' ' || l_location ||' '
833 ||to_char(l_acctsite_uses_rec.cust_acct_site_id) ;
834 END IF;
835
836 -- Intializing created_by_module as required in version 2 api for the record structure
837
838 l_acctsite_uses_rec.created_by_module := 'IBE_CUSTOMER_DATA';
839 l_cust_profile_rec.created_by_module := 'IBE_CUSTOMER_DATA';
840
841
842 -- old TCA API call
843 -- hz_customer_accounts_pub.create_acct_site_uses
847 p_init_msg_list => FND_API.G_FALSE,
844
845
846 HZ_CUST_ACCOUNT_SITE_V2PUB.create_cust_site_use (
848 p_cust_site_use_rec => l_acctsite_uses_rec,
849 p_customer_profile_rec => l_cust_profile_rec,
850 p_create_profile => FND_API.G_TRUE,
851 p_create_profile_amt => FND_API.G_TRUE,
852 x_site_use_id => l_custacct_site_use_id,
853 x_return_status => x_return_status,
854 x_msg_count => x_msg_count,
855 x_msg_data => x_msg_data );
856
857
858 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
859 Ibe_Util.debug('create_acct_site_use: x_return_status: '|| l_return_status);
860 Ibe_Util.debug('create_acct_site_use: l_custacct_site_use_id: '||l_custacct_site_use_id);
861 END IF;
862
863 IF l_Return_Status = FND_API.G_RET_STS_SUCCESS THEN
864 x_custacct_site_use_id := l_custacct_site_use_id;
865 ELSE
866 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
867 Ibe_util.Debug('Exception in HZ create_cust_site_use: '|| x_msg_data);
868 END IF;
869 RAISE FND_API.G_EXC_ERROR;
870 END IF;
871
872 END IF; -- IF x_site_use is null
873
874
875
876 /*
877 -- This call is commented out because create_cust_acct_site_use call to HZ API
878 -- takes care of creating realted party_site_use also.
879
880 OPEN c_party_site_use(p_party_site_Id,p_acct_site_type);
881 FETCH c_party_site_use into l_party_site_use;
882 CLOSE c_party_site_use;
883
884 IF (l_party_site_use = NULL OR l_party_site_use = FND_API.G_MISS_NUM)
885 then
886
887 Create_Party_Site_Use(p_party_site_id => l_party_site_id,
888 p_party_site_use_type => p_acct_site_type,
889 x_party_site_use_id => lx_party_site_use_id,
890 x_return_status => l_return_status,
891 x_msg_count => x_msg_count,
892 x_msg_data => x_msg_data
893 );
894
895 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
896 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
897 Ibe_util.Debug('Exception in HZ Create_Party_Site_Use: '|| x_msg_data);
898 END IF;
899 RAISE FND_API.G_EXC_ERROR;
900 END IF;
901 END IF;
902
903 */
904
905 --
906 -- End of API body
907 --
908
909 -- Standard check for p_commit
910 IF FND_API.to_Boolean( p_commit )
911 THEN
912 COMMIT WORK;
913 END IF;
914
915
916 -- Standard call to get message count and if count is 1, get message info.
917 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
918 p_data => x_msg_data);
919
920
921 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
922 Ibe_util.Debug('End IBE_CUSTOMER_ACCT_PVT:Create_Cust_Acct_Site_Use');
923 END IF;
924
925 EXCEPTION
926 WHEN FND_API.G_EXC_ERROR THEN
927 ROLLBACK TO CUSTACCT_CREATCUSTACCTSITEUSE;
928 x_return_status := FND_API.G_RET_STS_ERROR;
929 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
930 p_count => x_msg_count,
931 p_data => x_msg_data);
932 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
933 IBE_Util.Debug('Error IBE_CUSTOMER_ACCT_PVT:Create_Cust_Acct_Site_Use()'|| sqlerrm);
934 END IF;
935
936 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
937 ROLLBACK TO CUSTACCT_CREATCUSTACCTSITEUSE;
938 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
939 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
940 p_count => x_msg_count ,
941 p_data => x_msg_data);
942 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
943 IBE_Util.Debug('UNEXPECTEDErr IBE_CUSTOMER_ACCT_PVT:Create_Cust_Acct_Site_Use()' || sqlerrm);
944 END IF;
945
946 WHEN OTHERS THEN
947 ROLLBACK TO CUSTACCT_CREATCUSTACCTSITEUSE;
948 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
949 IF FND_Msg_Pub.Check_Msg_Level( FND_Msg_Pub.G_MSG_LVL_UNEXP_ERROR ) THEN
950 FND_Msg_Pub.Add_Exc_Msg(G_PKG_NAME,
951 L_API_NAME);
952 END IF;
953
954 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
955 p_count => x_msg_count ,
956 p_data => x_msg_data);
957 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
958 IBE_Util.Debug('OtherExc IBE_CUSTOMER_ACCT_PVT:Create_Cust_Acct_Site_Use()' || sqlerrm);
959 END IF;
960
961 END Create_Cust_Acct_Site_Use ;
962
963
964 PROCEDURE Get_Cust_Account_Site_Use(
965 p_api_version_number IN NUMBER
966 ,p_init_msg_list IN VARCHAR2
967 ,p_commit IN VARCHAR2
968 ,p_cust_acct_id IN NUMBER
969 ,p_party_id IN NUMBER
970 ,p_siteuse_type IN VARCHAR2
971 ,p_partysite_id IN NUMBER
972 ,x_siteuse_id OUT NOCOPY NUMBER
973 ,x_return_status OUT NOCOPY VARCHAR2
974 ,x_msg_count OUT NOCOPY NUMBER
975 ,x_msg_data OUT NOCOPY VARCHAR2
976 )
977 IS
978
979 CURSOR c_get_cust_site(c_cust_acct_id NUMBER,
983 select cust_acct_site_id,status
980 c_party_site_id NUMBER)
981 IS
982
984 from hz_cust_acct_sites
985 where cust_account_id = c_cust_acct_id
986 AND party_site_id = c_party_site_id;
987
988 -- changed for bug 4922991
989 CURSOR c_get_custsite_use(c_acct_site_id NUMBER,c_acct_site_type VARCHAR2) IS
990 select * from
991 ( select site_use_id, status
992 from hz_cust_site_uses
993 where cust_acct_site_id = c_acct_site_id and site_use_code = c_acct_site_type
994 order by status)
995 where rownum <2 ;
996
997
998 l_ship_to_org_id NUMBER := NULL;
999 l_invoice_to_org_id NUMBER := NULL;
1000 l_cust_acct_site_id NUMBER := NULL;
1001 lx_party_site_id NUMBER := NULL;
1002 l_cust_acct_site_status VARCHAR2(10) := '';
1003 l_site_use_id NUMBER := NULL;
1004 l_custsite_use_status VARCHAR2(10) := '';
1005 lx_cust_acct_site_id NUMBER := NULL;
1006 l_cust_siteid_notavl VARCHAR2(10) := FND_API.G_FALSE;
1007 l_cust_siteuseid_flow VARCHAR2(10) := FND_API.G_FALSE;
1008 l_return_status VARCHAR2(1):=FND_API.G_RET_STS_SUCCESS;
1009
1010 l_return_values varchar2(2000);
1011 l_api_version NUMBER := 1.0;
1012 l_api_name VARCHAR2(30) := 'CUSTACCT_CUSTACCTSITEUSE';
1013
1014 BEGIN
1015
1016 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1017 Ibe_util.Debug('Begin IBE_CUSTOMER_ACCT_PVT:Get_Cust_Account_Site_Use: ' || p_partysite_id );
1018 END IF;
1019
1020 -- Standard Start of API savepoint
1021 SAVEPOINT CUSTACCT_CUSTACCTSITEUSE;
1022
1023 -- Standard call to check for call compatibility.
1024 IF NOT FND_API.Compatible_API_Call (l_api_version,
1025 P_Api_Version_Number,
1026 l_api_name,
1027 G_PKG_NAME )
1028 THEN
1029 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1030 END IF;
1031
1032 -- Initialize message list if p_init_msg_list is set to TRUE.
1033 IF FND_API.To_Boolean( p_init_msg_list ) THEN
1034 FND_Msg_Pub.initialize;
1035 END IF;
1036
1037 -- Initialize API return status to success
1038 x_return_status := FND_API.G_RET_STS_SUCCESS;
1039
1040 -- Start OF API body --
1041
1042 -- PART I party site id fetching
1043 IF (p_partysite_id is null OR p_partysite_id = FND_API.G_MISS_NUM) THEN
1044
1045 /*** Use GetPartySiteId to use the Address rule.(primary, non primary or any valid address) ***/
1046
1047 GetPartySiteId(p_party_id => p_party_id
1048 ,p_site_use_type => p_siteuse_type
1049 ,x_party_site_id => lx_party_site_id
1050 ,x_return_status => x_return_status
1051 ,x_msg_count => x_msg_count
1052 ,x_msg_data => x_msg_data);
1053
1054 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1055 Ibe_util.Debug('Get_Cust_Account_Site_Use p_party_id: ' || p_party_id );
1056 Ibe_util.Debug('Get_Cust_Account_Site_Use p_siteuse_type: ' || p_siteuse_type );
1057 Ibe_util.Debug('Get_Cust_Account_Site_Use lx_party_site_id: ' || lx_party_site_id );
1058 END IF;
1059
1060 ELSE
1061
1062 -- This else part will be reached for B2C user, when he tries to
1063 -- change the address at header level in UI
1064 lx_party_site_id := p_partysite_id;
1065
1066 END IF;
1067
1068 IF (lx_party_site_id <> -1) THEN
1069
1070 -- PART II
1071 -- ** cust acct site id fetching **
1072 -- Use the party Site ID returned above
1073 -- and find the cust_acct_site_id
1074
1075 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1076 Ibe_util.Debug('retrieved val: ' || p_cust_acct_id||'::'||lx_party_site_id);
1077 END IF;
1078
1079 OPEN c_get_cust_site(p_cust_acct_id, lx_party_site_id);
1080 FETCH c_get_cust_site INTO l_cust_acct_site_id, l_cust_acct_site_status;
1081 IF (c_get_cust_site%NOTFOUND) THEN
1082 l_cust_siteid_notavl := FND_API.G_TRUE;
1083 END IF;
1084 CLOSE c_get_cust_site;
1085
1086 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1087 Ibe_util.Debug('cust_acct_site_id: ' || l_cust_acct_site_id);
1088 Ibe_util.Debug('cust_acct_site_status: ' || l_cust_acct_site_status||' : '||l_cust_siteid_notavl);
1089 END IF;
1090
1091 IF FND_API.to_Boolean(l_cust_siteid_notavl) THEN
1092
1093 -- No Valid Cust Acct Site Id is present
1094 -- so try to create one
1095 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1096 Ibe_util.Debug('Trying to create custacctsiteId');
1097 END IF;
1098 Create_Cust_Acct_Site(p_partysite_id => lx_party_site_Id
1099 ,p_custacct_id => p_cust_acct_Id
1100 ,p_custacct_type => p_siteuse_type
1101 ,x_custacct_site_id => l_cust_acct_site_id
1102 ,x_return_status => l_return_status
1103 ,x_msg_count => x_msg_count
1104 ,x_msg_data => x_msg_data
1105 );
1106
1107 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1108 Ibe_util.Debug('Create_Cust_Acct_Site returned id : ' || l_cust_acct_site_id);
1109 END IF;
1110 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS OR
1111 (l_cust_acct_site_id is null OR l_cust_acct_site_id = FND_API.G_MISS_NUM))
1112 THEN
1113 -- Creation Failed.
1114 FND_Message.Set_Name('IBE', 'IBE_ERR_OT_INVALID_BILLTO_ADDR');
1115 FND_Msg_Pub.Add;
1116 RAISE FND_API.G_EXC_ERROR;
1117 END IF;
1118
1119 ELSIF (l_cust_acct_site_status <> 'A') THEN -- IF c_get_cust_site%NOTFOUND
1120
1121 -- cust acct site id presents but invalid
1122 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1123 Ibe_util.Debug('Inside Invalid CustAcctSiteId Flow - Raise Exception');
1124 END IF;
1125
1126 IF p_siteuse_type = 'BILL_TO' THEN
1127 FND_MESSAGE.Set_Name('IBE','IBE_ERR_OT_INVALID_BILLTO_ADDR'); -- need error message
1128 ELSE
1129 FND_MESSAGE.Set_Name('IBE','IBE_ERR_OT_INVALID_SHIPTO_ADDR'); -- need error message
1130 END IF;
1131 FND_MSG_PUB.ADD;
1132 RAISE FND_API.G_EXC_ERROR;
1133
1134 END IF; -- c_get_cust_site%NOTFOUND
1135
1136
1137 -- PART III cust_acct_siteuse_id fetching
1138
1139 -- Use the cust_account_site_id returned above and find the cust_acct_site_use_id.
1140
1141 OPEN c_get_custsite_use(l_cust_acct_site_id,p_siteuse_type);
1142 FETCH c_get_custsite_use INTO l_site_use_id, l_custsite_use_status;
1143 IF (c_get_custsite_use%NOTFOUND) THEN
1144 l_cust_siteuseid_flow := FND_API.G_TRUE; -- custacctsiteuseid not present.
1145 END IF;
1146 CLOSE c_get_custsite_use;
1147
1148 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1149 Ibe_util.Debug('In l_site_use_id :' || l_site_use_id);
1150 Ibe_util.Debug('In l_custsite_use_status :' || l_custsite_use_status );
1151 END IF;
1152
1153 IF (FND_API.to_Boolean(l_cust_siteuseid_flow)) THEN
1154
1155 -- No valid Cust Acct Site Use Id present, so Create One.
1156 Create_Cust_Acct_Site_Use (p_Cust_Account_Id => p_cust_acct_id
1157 ,P_Party_Site_Id => lx_party_site_id
1158 ,P_cust_acct_site_id => l_cust_acct_site_id
1159 ,P_Acct_Site_type => p_siteuse_type
1160 ,x_cust_acct_site_id => lx_cust_acct_site_id
1161 ,x_custacct_site_use_id => l_site_use_id
1162 ,x_return_status => l_return_status
1163 ,x_msg_count => x_msg_count
1164 ,x_msg_data => x_msg_data
1165 );
1166
1167
1168 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS OR
1169 (l_cust_acct_site_id is null OR l_cust_acct_site_id = FND_API.G_MISS_NUM))
1170 THEN
1171 -- Creation Failed.
1172 FND_Message.Set_Name('IBE', 'IBE_ERR_OT_INVALID_BILLTO_ADDR');
1173 FND_Msg_Pub.Add;
1174 RAISE FND_API.G_EXC_ERROR;
1175 ELSE
1176 x_siteuse_id := l_site_use_id;
1177 END IF;
1178
1179
1180 ELSIF (l_custsite_use_status <> 'A') THEN
1181
1182 -- cust acct siteuse id present but invalid, so raise exception
1183 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1184 Ibe_util.Debug('Inside Invalid CustAcctSiteUseId Flow - Raise Exception');
1185 Ibe_util.Debug('nproc1 Inside Invalid CustAcctSiteUseId Flow - Raise Exception;' || l_site_use_id);
1186 Ibe_util.Debug('nproc1 Inside Invalid CustAcctSiteUseId Flow - Raise Exception:' || l_custsite_use_status);
1187 END IF;
1188
1189 IF p_siteuse_type = 'BILL_TO' THEN
1190 FND_MESSAGE.Set_Name('IBE','IBE_ERR_OT_INVALID_BILLTO_ADDR'); -- need error message
1191 ELSE
1192 FND_MESSAGE.Set_Name('IBE','IBE_ERR_OT_INVALID_SHIPTO_ADDR'); -- need error message
1193 END IF;
1194 FND_MSG_PUB.ADD;
1195 RAISE FND_API.G_EXC_ERROR;
1196
1197 ELSE
1198 -- valid cust_acct_site_use_id available, so use it
1199 x_siteuse_id := l_site_use_id;
1200 END IF; --l_cust_siteuseid_flow
1201
1202 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1203 Ibe_util.Debug('x_siteuse_id: ' || x_siteuse_id);
1204 END IF;
1205
1206 ELSE --party_site_id =-1
1207 l_return_status := FND_API.G_RET_STS_ERROR;
1208 END IF;
1209
1210 --
1211 -- End of API body
1212 --
1213
1214 -- Standard check for p_commit
1215 IF FND_API.to_Boolean( p_commit )
1216 THEN
1217 COMMIT WORK;
1218 END IF;
1219
1220
1221 -- Standard call to get message count and if count is 1, get message info.
1222 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1223 p_data => x_msg_data);
1224
1225
1226 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1227 Ibe_util.Debug('End IBE_CUSTOMER_ACCT_PVT:Get_Cust_Account_Site_Use');
1228 END IF;
1229
1230 EXCEPTION
1231 WHEN FND_API.G_EXC_ERROR THEN
1232 x_return_status := FND_API.G_RET_STS_ERROR;
1233 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
1234 p_count => x_msg_count,
1235 p_data => x_msg_data);
1236 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1237 IBE_Util.Debug('Error IBE_CUSTOMER_ACCT_PVT:Get_Cust_Account_Site_Use()'|| sqlerrm);
1238 END IF;
1239
1240 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1241 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1242 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
1243 p_count => x_msg_count,
1244 p_data => x_msg_data);
1245 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1246 IBE_Util.Debug('UnexError IBE_CUSTOMER_ACCT_PVT:Get_Cust_Account_Site_Use()'|| sqlerrm);
1247 END IF;
1248
1249 WHEN OTHERS THEN
1250 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1251 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
1252 p_count => x_msg_count,
1253 p_data => x_msg_data);
1254 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1255 IBE_Util.Debug('Others IBE_CUSTOMER_ACCT_PVT:Get_Cust_Account_Site_Use()'|| sqlerrm);
1256 END IF;
1257
1258 END Get_Cust_Account_Site_Use;
1259
1260 PROCEDURE Get_Cust_Acct_Role(
1261 p_api_version_number IN NUMBER
1262 ,p_init_msg_list IN VARCHAR2
1263 ,p_commit IN VARCHAR2
1264 ,p_party_id IN NUMBER
1265 ,p_acctsite_type IN VARCHAR2
1266 ,p_sold_to_orgid IN NUMBER
1267 ,p_custacct_siteuse_id IN NUMBER
1268 ,x_cust_acct_role_id OUT NOCOPY NUMBER
1269 ,x_return_status OUT NOCOPY VARCHAR2
1270 ,x_msg_count OUT NOCOPY NUMBER
1271 ,x_msg_data OUT NOCOPY VARCHAR2
1272 )
1273 IS
1274
1275 cursor c_cust_acct_id (lin_custacct_siteuse_id number, lin_siteuse_type varchar2)
1276 is
1277 select hca.cust_acct_site_id, hca.cust_account_id,hps.party_id
1278 from hz_cust_acct_sites hca,hz_cust_site_uses hcu,hz_party_sites hps
1279 where
1280 hcu.site_use_id = lin_custacct_siteuse_id
1281 and hcu.site_use_code = lin_siteuse_type
1282 and hcu.cust_acct_site_id = hca.cust_acct_site_id
1283 and hca.party_site_id = hps.party_site_id;
1284
1285
1286 cursor c_cust_role(lin_party_id number,lin_custacct_site_id number,lin_custacct_id number) is
1287 select a.cust_account_role_id, a.status
1288 from hz_cust_account_roles a
1289 --, hz_cust_acct_sites_all c
1290 where
1291 a.role_type = 'CONTACT'
1292 and a.party_id = lin_party_id
1293 and a.cust_account_id = lin_custacct_id
1294 and a.cust_acct_site_id = lin_custacct_site_id;
1295
1296 l_cust_role c_cust_role%rowtype;
1297 l_custacctrole_status VARCHAR2(10) := '';
1298 l_cust_acct_role_id NUMBER;
1299 l_party_site_id NUMBER;
1300 l_custacct_site_id NUMBER;
1301 l_cust_acct_id NUMBER;
1302 l_party_id NUMBER;
1306
1303 l_return_values varchar2(2000);
1304 l_api_version NUMBER := 1.0;
1305 l_api_name VARCHAR2(30) := 'CUSTACCT_GETCUSTACCTRLE';
1307
1308 BEGIN
1309
1310 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1311 Ibe_util.Debug('Begin IBE_CUSTOMER_ACCT_PVT:Get_Cust_Acct_Role');
1312 Ibe_util.Debug('Get_Cust_Acct_Role partyId, soldtoorgid: '||p_party_id||' : '|| p_sold_to_orgid);
1313 Ibe_util.Debug('Get_Cust_Acct_Role custacct siteuse id: '||p_custacct_siteuse_id);
1314 END IF;
1315
1316 -- Standard Start of API savepoint
1317 SAVEPOINT CUSTACCT_GETCUSTACCTRLE;
1318
1319 -- Standard call to check for call compatibility.
1320 IF NOT FND_API.Compatible_API_Call (l_api_version,
1321 P_Api_Version_Number,
1322 l_api_name,
1323 G_PKG_NAME )
1324 THEN
1325 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1326 END IF;
1327
1328 -- Initialize message list if p_init_msg_list is set to TRUE.
1329 IF FND_API.To_Boolean( p_init_msg_list ) THEN
1330 FND_Msg_Pub.initialize;
1331 END IF;
1332
1333 -- Initialize API return status to success
1334 x_return_status := FND_API.G_RET_STS_SUCCESS;
1335
1336 -- Start OF API body --
1337
1338 --for the available site_use_id fetch cust_acct_id.
1339
1340 open c_cust_acct_id(p_custacct_siteuse_id,p_acctsite_type);
1341 fetch c_cust_acct_id into l_custacct_site_id,l_cust_acct_id,l_party_id;
1342 close c_cust_acct_id;
1343
1344 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1345 Ibe_util.Debug('db cust_acct_id: '||l_cust_acct_id);
1346 END IF;
1347
1348 -- if(l_cust_acct_id = p_sold_to_orgid AND l_party_id = p_party_id) then
1349
1350 open c_cust_role(p_party_id,l_custacct_site_id,l_cust_acct_id);
1351 fetch c_cust_role into l_cust_role;
1352
1353 if(c_cust_role%notfound) then
1354 l_cust_acct_role_id:= NULL;
1355 elsif l_cust_role.status <> 'A'
1356 then
1357 x_cust_acct_role_id := NULL;
1358 return;
1359 else
1360 l_cust_acct_role_id := l_cust_role.cust_account_role_id;
1361 end if;
1362
1363 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1364 Ibe_util.Debug('Get_Cust_Acct_Role cust_acct_role_id: '||l_cust_acct_role_id);
1365 end if;
1366
1367 IF (l_cust_acct_role_id IS NULL or l_cust_acct_role_id = FND_API.G_MISS_NUM)
1368 THEN
1369 create_cust_account_role(p_party_id => p_party_id
1370 ,p_cust_acct_id => p_sold_to_orgid
1371 ,p_cust_acct_site_id => l_custacct_site_id
1372 ,p_role_type => 'BILL_TO'
1373 ,x_cust_acct_role_id => l_cust_acct_role_id
1374 ,x_return_status => x_return_status
1375 ,x_msg_count => x_msg_count
1376 ,x_msg_data => x_msg_data
1377 );
1378 END IF;
1379
1380 -- else
1381 -- l_cust_acct_role_id := NULL;
1382 -- end if;
1383 x_cust_acct_role_id := l_cust_acct_role_id;
1384
1385 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1386 Ibe_util.Debug('End IBE_CUSTOMER_ACCT_PVT:Get_Cust_Acct_Role: '||x_cust_acct_role_id);
1387 end if;
1388
1389 --
1390 -- End of API body
1391 --
1392
1393 -- Standard check for p_commit
1394 IF FND_API.to_Boolean( p_commit )
1395 THEN
1396 COMMIT WORK;
1397 END IF;
1398
1399
1400 -- Standard call to get message count and if count is 1, get message info.
1401 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1402 p_data => x_msg_data);
1403
1404 EXCEPTION
1405 WHEN FND_API.G_EXC_ERROR THEN
1406 ROLLBACK TO CUSTACCT_CUSTACCTSITEUSE;
1407 x_return_status := FND_API.G_RET_STS_ERROR;
1408 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
1409 p_count => x_msg_count ,
1410 p_data => x_msg_data);
1411 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1412 IBE_Util.Debug('Error IBE_CUSTOMER_ACCT_PVT:Get_Cust_Acct_Role()'|| sqlerrm);
1413 END IF;
1414
1415 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1416 ROLLBACK TO CUSTACCT_CUSTACCTSITEUSE;
1417 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1418 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
1419 p_count => x_msg_count ,
1420 p_data => x_msg_data);
1421 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1422 IBE_Util.Debug('UNEXPECTEDErr IBE_CUSTOMER_ACCT_PVT:Get_Cust_Acct_Role()' || sqlerrm);
1423 END IF;
1424
1425 WHEN OTHERS THEN
1426 ROLLBACK TO CUSTACCT_CUSTACCTSITEUSE;
1427 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1428 IF FND_Msg_Pub.Check_Msg_Level( FND_Msg_Pub.G_MSG_LVL_UNEXP_ERROR ) THEN
1429 FND_Msg_Pub.Add_Exc_Msg(G_PKG_NAME,
1430 L_API_NAME);
1431 END IF;
1432
1433 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
1434 p_count => x_msg_count ,
1435 p_data => x_msg_data);
1436 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1437 IBE_Util.Debug('OtherExc IBE_CUSTOMER_ACCT_PVT:Get_Cust_Acct_Role()' || sqlerrm);
1438 END IF;
1439
1440 END Get_Cust_Acct_Role;
1441
1442 PROCEDURE GetCustomerAcctData(
1443 p_api_version_number IN NUMBER := 1
1444 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
1445 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
1446 ,p_invoice_to_org_id IN NUMBER := FND_API.G_MISS_NUM
1447 ,p_invoice_to_contact_id IN NUMBER := FND_API.G_MISS_NUM
1448 ,p_contact_party_id IN NUMBER := FND_API.G_MISS_NUM
1449 ,p_cust_account_id IN NUMBER := FND_API.G_MISS_NUM
1450 ,x_cust_account_id OUT NOCOPY NUMBER
1451 ,x_cust_party_name OUT NOCOPY VARCHAR2
1452 ,x_cust_party_id OUT NOCOPY NUMBER
1453 ,x_cust_party_type OUT NOCOPY VARCHAR2
1454 ,x_contact_party_id OUT NOCOPY NUMBER
1455 ,x_contact_party_name OUT NOCOPY VARCHAR2
1456 ,x_contact_phone OUT NOCOPY VARCHAR2
1457 ,x_contact_email OUT NOCOPY VARCHAR2
1458 ,x_party_site_id OUT NOCOPY NUMBER
1459 ,x_partysite_status OUT NOCOPY VARCHAR2
1460 ,x_return_status OUT NOCOPY VARCHAR2
1461 ,x_msg_count OUT NOCOPY NUMBER
1462 ,x_msg_data OUT NOCOPY VARCHAR2)
1463
1464 IS
1465
1466 CURSOR c_cust_acct_det(lc_inv_to_org_id NUMBER) IS
1467 select hca.cust_account_id, hca.party_site_id, hcs.status
1468 from hz_cust_site_uses hcs,hz_cust_acct_sites hca
1469 where hcs.site_use_id = lc_inv_to_org_id and
1470 hcs.cust_acct_site_id = hca.cust_acct_site_id;
1471
1472 lc_cust_acct_det c_cust_acct_det%rowtype;
1473
1474 CURSOR c_customer_details(lc_cust_acct_id NUMBER) IS
1475 select a.party_id, a.party_name,a.party_type
1476 from hz_parties a, hz_cust_accounts b
1477 where b.party_id = a.party_id
1478 and b.cust_account_id = lc_cust_acct_id;
1479
1480 lc_customer_details c_customer_details%rowtype;
1481
1482 CURSOR c_contact_partyid(lc_inv_to_cntct_id NUMBER) IS
1483 Select Party_id
1484 from HZ_CUST_ACCOUNT_ROLES
1485 where cust_account_role_id = lc_inv_to_cntct_id;
1486
1487 lc_contact_partyid c_contact_partyid%rowtype;
1488
1489 CURSOR c_contact_partyname(lc_cntct_partyid NUMBER) IS
1490 Select party_name from HZ_PARTIES
1491 where party_type = 'PERSON' and party_id = lc_cntct_partyid
1492 union
1493 select party_name from HZ_PARTIES
1494 where party_id =
1495 (select subject_id from HZ_RELATIONSHIPS
1496 where party_id = lc_cntct_partyid and
1497 subject_type = 'PERSON' and object_type = 'ORGANIZATION');
1498
1499 lc_contact_partyname c_contact_partyname%rowtype;
1500
1501 CURSOR c_contact_details(lc_cntct_prtyId number) IS
1502 select contact_point_type, primary_flag,phone_line_type,
1503 IBE_UTIL.format_phone(phone_country_code,phone_area_code,phone_number,phone_extension)phone_number,
1504 email_address
1505 from hz_contact_points
1506 where contact_point_type in ('PHONE','EMAIL') and
1507 NVL(status, 'A') = 'A' and owner_table_name = 'HZ_PARTIES'
1508 and owner_table_id = lc_cntct_prtyId
1509 and primary_flag='Y';
1510
1511 lc_contact_details c_contact_details%rowtype;
1512
1513 l_cntct_partyid NUMBER := null;
1514 l_cntct_party_name VARCHAR2(360) := '';
1515 l_cntct_phone VARCHAR2(100) := '';
1516 l_cntct_email VARCHAR2(2000) := '';
1517 l_cust_account_id NUMBER;
1518 l_partysite_id NUMBER;
1519 l_partysite_stat VARCHAR2(1) := '';
1520
1521 l_api_version NUMBER := 1.0;
1522 l_api_name VARCHAR2(30) := 'CUSTACCT_CUSTOMERDATA';
1523
1524 BEGIN
1525
1526 -- Standard Start of API savepoint
1527 SAVEPOINT CUSTACCT_CUSTOMERDATA;
1528
1529 -- Standard call to check for call compatibility.
1530 IF NOT FND_API.Compatible_API_Call (l_api_version,
1531 P_Api_Version_Number,
1532 l_api_name,
1533 G_PKG_NAME )
1534 THEN
1535 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1536 END IF;
1537
1538 -- Initialize message list if p_init_msg_list is set to TRUE.
1539 IF FND_API.To_Boolean( p_init_msg_list ) THEN
1540 FND_Msg_Pub.initialize;
1541 END IF;
1542
1543 -- Initialize API return status to success
1544 x_return_status := FND_API.G_RET_STS_SUCCESS;
1545
1546 -- Start OF API body --
1547
1548
1549 -- PART I
1550 -- If InvoiceToOrgId is passed from UI Retrieve Cust_Acct_Id of the Order
1551 -- and Fetch the related Customer Details.
1552 -- Else if cust_acct_id is passed from UI the fetch the details directly
1553 -- CustomerName, CutsAcctId,PartySiteId would be sent back.
1554 -- If CustAcctId is coming IN then PartySiteId would not be sent back.
1555
1556 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1557 IBE_Util.Debug('IBE_CUSTOMER_ACCT_PVT:GetCustomerAcctData() -Begin');
1558 IBE_Util.Debug('p_invoice_to_org_id: ' || p_invoice_to_org_id);
1559 IBE_Util.Debug('p_invoice_to_contact_id: '||p_invoice_to_contact_id);
1560 IBE_Util.Debug('p_contact_party_id: '|| p_contact_party_id);
1561 IBE_Util.Debug('p_cust_account_id: '|| p_cust_account_id);
1562
1563 END IF;
1564
1565 if(p_invoice_to_org_id is not null AND p_invoice_to_org_id <> FND_API.G_MISS_NUM) then
1566 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1567 IBE_Util.Debug('Inside invoiceToorg id IF loop');
1568 END IF;
1569 open c_cust_acct_det(p_invoice_to_org_id);
1570 fetch c_cust_acct_det into lc_cust_acct_det;
1571 if(c_cust_acct_det%notfound) then
1572 FND_Message.Set_Name('IBE', 'IBE_ERR_OT_INVALID_CUST'); --need err msg
1573 FND_Msg_Pub.Add;
1574 RAISE FND_API.G_EXC_ERROR;
1575 else
1576 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1577 IBE_Util.Debug('Customer details available');
1578 END IF;
1579 l_cust_account_id := lc_cust_acct_det.cust_account_id;
1580 l_partysite_id := lc_cust_acct_det.party_site_id;
1581 l_partysite_stat := lc_cust_acct_det.status;
1582 end if;
1583 close c_cust_acct_det;
1584 elsif(p_cust_account_id is not null AND p_cust_account_id <> FND_API.G_MISS_NUM) then
1585 -- this flow would be reached for Contact/ Address getting changed in UI.
1586 l_cust_account_id := p_cust_account_id;
1587 l_partysite_id := null;
1588 l_partysite_stat := '';
1589 end if;
1590
1591 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1592 IBE_Util.Debug('l_cust_account_id: '||l_cust_account_id);
1593 IBE_Util.Debug('l_partysite_id: '|| l_partysite_id);
1594 IBE_Util.Debug('l_partysite_stat: '|| l_partysite_stat);
1595
1596 END IF;
1597 x_cust_account_id := l_cust_account_id;
1598 x_party_site_id := l_partysite_id;
1599 x_partysite_status := l_partysite_stat;
1600
1601 open c_customer_details(l_cust_account_id);
1602 fetch c_customer_details into lc_customer_details;
1603 if(c_customer_details%notfound) then
1604 FND_Message.Set_Name('IBE', 'IBE_ERR_OT_INVALID_CUST'); --need err msg
1605 FND_Msg_Pub.Add;
1606 RAISE FND_API.G_EXC_ERROR;
1607 else
1608 x_cust_party_id := lc_customer_details.party_id;
1609 x_cust_party_name := lc_customer_details.party_name;
1610 x_cust_party_type := lc_customer_details.party_type;
1611 end if;
1612
1613 close c_customer_details;
1614
1615 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1616 IBE_Util.Debug('x_cust_party_id: '|| x_cust_party_id);
1617 IBE_Util.Debug('x_cust_party_name: '|| x_cust_party_name);
1618 END IF;
1619 -- PART II
1620 -- Send the Party_site_Id and its status fetched above.
1621
1622
1623 -- PART III
1624 -- If InvoiceToContactId is passed Retrieve the ContactPartyId and fetch the related Contact details.
1625 -- Else if ConatctPartyId is directly passed from UI then fetch the related details.
1626 -- ContactName, phone and email would be sent back.
1627
1628 if (p_invoice_to_contact_id is not null AND p_invoice_to_contact_id <> FND_API.G_MISS_NUM) then
1629 open c_contact_partyid(p_invoice_to_contact_id);
1630 fetch c_contact_partyid into l_cntct_partyid;
1631 CLOSE c_contact_partyid;
1632
1633 elsif(p_contact_party_id is not null AND p_contact_party_id <> FND_API.G_MISS_NUM) then
1634 l_cntct_partyid := p_contact_party_id;
1635 end if; -- (p_invoice_to_contact_id <> FND_API.G_MISS_NUM
1636
1637 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1638 IBE_Util.Debug('l_cntct_partyid: '|| l_cntct_partyid);
1639 END IF;
1640
1641
1642 if (l_cntct_partyid is null OR l_cntct_partyid = FND_API.G_MISS_NUM) then
1643 x_contact_party_id := null;
1644 x_contact_party_name := '';
1645 x_contact_phone := '';
1646 x_contact_email := '';
1647
1648 else
1649 -- Now fetch the PARTYNAME for this contactpartyId.
1650 for lc_contact_partyname in c_contact_partyname(l_cntct_partyid)
1651 loop
1652 l_cntct_party_name := lc_contact_partyname.party_name;
1653 end loop;
1654
1655 -- Contact's Phone number And Email.
1656 -- If Primary Phone and Email is available that is fetched.
1657 -- Otherwise, non-primary Active phone and email would be fetched.
1658
1659 -- FETCH EMAIL and Phone
1660 open c_contact_details(l_cntct_partyid);
1661 loop
1662 fetch c_contact_details into lc_contact_details;
1663 Exit When c_contact_details%notfound;
1664 IF lc_contact_details.CONTACT_POINT_TYPE = 'EMAIL' THEN
1665 l_cntct_email := lc_contact_details.EMAIL_ADDRESS;
1666 ELSE
1667 IF (lc_contact_details.CONTACT_POINT_TYPE = 'PHONE') THEN
1668 l_cntct_phone := lc_contact_details.PHONE_NUMBER;
1669 END IF;
1670 END IF;
1671 end loop;
1672 close c_contact_details;
1673
1674 x_contact_party_id := l_cntct_partyid;
1675 x_contact_party_name := l_cntct_party_name;
1676 x_contact_phone := l_cntct_phone;
1677 x_contact_email := l_cntct_email;
1678 end if;
1679
1680 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1681 IBE_Util.Debug('l_cntct_party_name: '|| l_cntct_party_name);
1682 IBE_Util.Debug('l_cntct_phone: '|| l_cntct_phone);
1683 IBE_Util.Debug('l_cntct_email: '|| l_cntct_email);
1684 END IF;
1685
1686 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1687 IBE_Util.Debug('IBE_CUSTOMER_ACCT_PVT:GetCustomerAcctData() -END');
1688 END If;
1689
1690 EXCEPTION
1691 WHEN FND_API.G_EXC_ERROR THEN
1692 ROLLBACK TO CUSTACCT_CUSTOMERDATA;
1693 x_return_status := FND_API.G_RET_STS_ERROR;
1694 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
1695 p_count => x_msg_count ,
1696 p_data => x_msg_data);
1697 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1698 IBE_Util.Debug('Error IBE_CUSTOMER_ACCT_PVT:GetCustomerAcctData()'|| sqlerrm);
1699 END IF;
1700
1701 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1702 ROLLBACK TO CUSTACCT_CUSTOMERDATA;
1703 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1704 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
1705 p_count => x_msg_count ,
1706 p_data => x_msg_data);
1707 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1708 IBE_Util.Debug('UNEXPECTEDErr IBE_CUSTOMER_ACCT_PVT:GetCustomerAcctData()' || sqlerrm);
1709 END IF;
1710
1711 WHEN OTHERS THEN
1712 ROLLBACK TO CUSTACCT_CUSTOMERDATA;
1713 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1714 IF FND_Msg_Pub.Check_Msg_Level( FND_Msg_Pub.G_MSG_LVL_UNEXP_ERROR ) THEN
1715 FND_Msg_Pub.Add_Exc_Msg(G_PKG_NAME,
1716 L_API_NAME);
1717 END IF;
1718
1719 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
1720 p_count => x_msg_count ,
1721 p_data => x_msg_data);
1722 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1723 IBE_Util.Debug('OtherExc IBE_CUSTOMER_ACCT_PVT:GetCustomerAcctData()' || sqlerrm);
1724 END IF;
1725
1726 END GetCustomerAcctData;
1727
1728
1729 END IBE_CUSTOMER_ACCT_PVT;