DBA Data[Home] [Help]

PACKAGE BODY: APPS.IBE_CUSTOMER_ACCT_PVT

Source


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;