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