DBA Data[Home] [Help]

PACKAGE BODY: APPS.ASO_PARTY_INT

Source


1 PACKAGE BODY ASO_PARTY_INT as
2 /* $Header: asoiptyb.pls 120.3.12000000.2 2007/02/05 22:33:04 pkoka ship $ */
3 -- Start of Comments
4 -- Package name     : ASO_PARTY_PUB
5 -- Purpose          :
6 -- History          :
7 -- NOTE             :
8 -- Changes made : 10/15/02 By Suyog Kulkarni
9 -- Changed calls to some TCA API's as per new packages in Version 2 TCA API's.
10 -- The following procedures were modified as part of version 2 changes
11 -- 1) Create_Customer_Account
12 -- 2) Create_Acct_Site
13 -- 3) Create_Acct_Site_Use
14 -- 4) Create_Contact
15 -- 5) Create_Contact_Role
16 -- 6) Create_Org_Contact_ord
17 -- 7) Create_Party_Site_Use
18 -- 8) Create_Cust_Acct_Relationship
19 -- 9) Update_Party
20 
21 --Removed the following procedures as they are no longer being used:
22 --1) Create_Org_Contact
23 --2) Create_Contact_Points
24 --3)Create_Contact_Restriction
25 --4) Update_Party_Site
26 --5) update_Org_Contact
27 --6) Update_Contact_Points
28 --7) Update_Contact_Restriction
29 
30 -- End of Comments
31 
32 
33 G_PKG_NAME CONSTANT VARCHAR2(30):= 'ASO_PARTY_INT';
34 G_FILE_NAME CONSTANT VARCHAR2(12) := 'asoiptyb.pls';
35 
36 PROCEDURE Create_Party(
37         p_party_rec             IN      PARTY_REC_TYPE,
38         x_return_status         OUT NOCOPY /* file.sql.39 change */   VARCHAR2,
39         x_msg_count             OUT NOCOPY /* file.sql.39 change */   NUMBER,
40         x_msg_data              OUT NOCOPY /* file.sql.39 change */   VARCHAR2,
41         x_party_id              OUT NOCOPY /* file.sql.39 change */   NUMBER)
42 IS
43     l_api_name          VARCHAR2(40) := 'Create_Party';
44    -- l_organization_rec  HZ_PARTY_PUB.Organization_Rec_Type;
45    l_organization_rec  HZ_PARTY_V2PUB.Organization_Rec_Type;
46     l_party_number      NUMBER;
47     l_profile_id        NUMBER;
48    -- l_person_rec        HZ_PARTY_PUB.Person_Rec_Type;
49     l_person_rec              HZ_PARTY_V2PUB.person_rec_type;
50     --l_party_rec     HZ_PARTY_PUB.Party_Rec_Type := HZ_PARTY_PUB.G_MISS_PARTY_REC;
51     l_party_rec      HZ_PARTY_V2PUB.party_rec_type :=  HZ_PARTY_V2PUB.G_MISS_PARTY_REC;
52     l_msg_count         NUMBER;
53     l_msg_data          VARCHAR2(2000);
54 BEGIN
55     SAVEPOINT CREATE_PARTY_PVT;
56     -- Initialize API return status to SUCCESS
57     x_return_status := FND_API.G_RET_STS_SUCCESS;
58 
59 -- Initializing the created_by_module column for all the records as per
60 -- changes in version 2 api's.
61 
62    l_person_rec.Created_by_Module := 'ASO_CUSTOMER_DATA';
63    l_organization_rec.Created_by_Module := 'ASO_CUSTOMER_DATA';
64 
65   -- Column has been removed in version 2 api
66     --l_party_rec.TOTAL_NUM_OF_ORDERS := 0;
67 
68     IF p_party_rec.party_type = 'ORGANIZATION'
69                 AND p_party_rec.party_name IS NOT NULL
70                 AND p_party_rec.party_name <> FND_API.G_MISS_CHAR THEN
71         l_organization_rec.organization_name := p_party_rec.party_name;
72         l_organization_rec.curr_fy_potential_revenue := p_party_rec.curr_fy_potential_revenue;
73         l_organization_rec.employees_total  := p_party_rec.num_of_employees;
74            l_organization_rec.party_rec        := l_party_rec;
75 /*
76    The call to this api has been moved to a diff package in version 2 api
77    Original Call:		 HZ_PARTY_PUB.Create_Organization
78 */
79 
80     HZ_PARTY_V2PUB.create_organization (
81     p_init_msg_list                    => FND_API.G_FALSE,
82     p_organization_rec                 => l_organization_rec,
83     x_return_status                    => x_return_status,
84     x_msg_count                        => x_msg_count,
85     x_msg_data                         => x_msg_data,
86     x_party_id                         => x_party_id,
87     x_party_number                     => l_party_number,
88     x_profile_id                       => l_profile_id );
89 
90 IF aso_debug_pub.g_debug_flag = 'Y' THEN
91 aso_debug_pub.add('create_party:after create_org:x_party_id '||x_party_id, 1, 'N');
92 aso_debug_pub.add('create_party:after create_org:x_return_status '||x_return_status, 1, 'N');
93 END IF;
94     ELSIF p_party_rec.party_type = 'PERSON'
95                 AND p_party_rec.person_first_name IS NOT NULL
96                 AND p_party_rec.person_first_name <> FND_API.G_MISS_CHAR THEN
97 /*
98    Column Names have been changed in version 2 api's
99 
100         l_person_rec.pre_name_adjunct := p_party_rec.person_title;
101         l_person_rec.first_name := p_party_rec.person_first_name;
102         l_person_rec.middle_name := p_party_rec.person_middle_name;
103         l_person_rec.last_name := p_party_rec.person_last_name;
104 */
105 
106         l_person_rec.person_pre_name_adjunct := p_party_rec.person_title;
107         l_person_rec.person_first_name := p_party_rec.person_first_name;
108         l_person_rec.person_middle_name := p_party_rec.person_middle_name;
109         l_person_rec.person_last_name := p_party_rec.person_last_name;
110         l_person_rec.known_as  := p_party_rec.person_known_as;
111         l_person_rec.date_of_birth := p_party_rec.date_of_birth;
112         l_person_rec.personal_income := p_party_rec.personal_income;
113            l_person_rec.party_rec       := l_party_rec;
114 /*
115    The call to this api has been moved to a diff package in version 2 api
116 
117    Original Call:        HZ_PARTY_PUB.Create_Person
118 */
119 
120     HZ_PARTY_V2PUB.create_person (
121     p_init_msg_list                    => FND_API.G_FALSE,
122     p_person_rec                       => l_person_rec,
123     x_party_id                         => x_party_id,
124     x_party_number                     => l_party_number,
125     x_profile_id                       => l_profile_id,
126     x_return_status                    => x_return_status,
127     x_msg_count                        => x_msg_count,
128     x_msg_data                         => x_msg_data);
129 
130 IF aso_debug_pub.g_debug_flag = 'Y' THEN
131 aso_debug_pub.add('create_party:after create_per:x_return_status '||x_return_status, 1, 'N');
132 aso_debug_pub.add('create_party:after create_per:x_party_id '||x_party_id, 1, 'N');
133 END IF;
134     END IF;
135     if  x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
136       RAISE FND_API.G_EXC_ERROR;
137     END IF;
138 
139       EXCEPTION
140           WHEN FND_API.G_EXC_ERROR THEN
141             ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
142                    P_API_NAME => L_API_NAME
143                   ,P_PKG_NAME => G_PKG_NAME
144                   ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
145                   ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
146                   ,X_MSG_COUNT => X_MSG_COUNT
147                   ,X_MSG_DATA => X_MSG_DATA
148                   ,X_RETURN_STATUS => X_RETURN_STATUS);
149 
150           WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
151                  ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
152                    P_API_NAME => L_API_NAME
153                   ,P_PKG_NAME => G_PKG_NAME
154                   ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
155                   ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
156                   ,X_MSG_COUNT => X_MSG_COUNT
157                   ,X_MSG_DATA => X_MSG_DATA
158                   ,X_RETURN_STATUS => X_RETURN_STATUS);
159           WHEN OTHERS THEN
160                  ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
161                    P_API_NAME => L_API_NAME
162                   ,P_PKG_NAME => G_PKG_NAME
163                   ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
164                   ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
165                   ,X_MSG_COUNT => X_MSG_COUNT
166                   ,X_MSG_DATA => X_MSG_DATA
167                   ,X_RETURN_STATUS => X_RETURN_STATUS);
168 
169 END Create_Party;
170 
171 PROCEDURE Create_Party_Site(
172         p_party_site_rec        IN      PARTY_SITE_REC_TYPE,
173         x_return_status         OUT NOCOPY /* file.sql.39 change */   VARCHAR2,
174         x_party_site_id         OUT NOCOPY /* file.sql.39 change */   NUMBER,
175         x_msg_count             OUT NOCOPY /* file.sql.39 change */   NUMBER,
176         x_msg_data              OUT NOCOPY /* file.sql.39 change */   VARCHAR2)
177 IS
178 
179    /* CURSOR c_site_use_type(p_type_name VARCHAR2) IS
180         SELECT site_use_type_id FROM HZ_SITE_USE_TYPES
181         WHERE name = p_type_name;*/
182 
183     l_api_name                  VARCHAR2(40) := 'Create_Party_Site';
184     l_site_use_type_id          NUMBER;
185 --    l_location_rec              HZ_LOCATION_PUB.Location_Rec_Type;
186       l_location_rec              HZ_LOCATION_V2PUB.Location_Rec_Type;
187       l_location_id               NUMBER;
188 --    l_site_use_type_rec         HZ_PARTY_PUB.SITE_USE_TYPE_REC_TYPE;
189 
190 --  Record definitions have been moved to a diff package in version 2 api
191     --l_party_site_rec            HZ_PARTY_PUB.Party_Site_Rec_Type;
192     --l_party_site_use_rec        HZ_PARTY_PUB.Party_Site_Use_Rec_Type;
193     l_party_site_rec            HZ_PARTY_SITE_V2PUB.Party_Site_Rec_Type;
194     l_party_site_use_rec        HZ_PARTY_SITE_V2PUB.Party_Site_Use_Rec_Type;
195 
196     l_msg_count                 NUMBER;
197     l_msg_data                  VARCHAR2(2000);
198     l_party_site_use_id         NUMBER;
199     l_party_site_number         NUMBER;
200 
201 BEGIN
202     SAVEPOINT CREATE_PARTY_SITE_PVT;
203     -- Initialize API return status to SUCCESS
204     x_return_status := FND_API.G_RET_STS_SUCCESS;
205 
206 
207 -- Initializing the created_by_module column for all the records as per
208 -- changes in version 2 api's.
209 
210    l_party_site_rec.Created_by_Module := 'ASO_CUSTOMER_DATA';
211    l_party_site_use_rec.Created_by_Module := 'ASO_CUSTOMER_DATA';
212    l_location_rec.Created_by_Module := 'ASO_CUSTOMER_DATA';
213 
214 
215     l_location_rec.address1 := p_party_site_rec.location.address1;
216     l_location_rec.address2 := p_party_site_rec.location.address2;
217     l_location_rec.address3 := p_party_site_rec.location.address3;
218     l_location_rec.address4 := p_party_site_rec.location.address4;
219     l_location_rec.country      := p_party_site_rec.location.country;
220     l_location_rec.city        := p_party_site_rec.location.city;
221     l_location_rec.postal_code := p_party_site_rec.location.postal_code;
222     l_location_rec.state       := p_party_site_rec.location.state;
223     l_location_rec.province    := p_party_site_rec.location.province;
224     l_location_rec.county      := p_party_site_rec.location.county;
225 
226 
227     l_location_rec.ORIG_SYSTEM_REFERENCE := -1;
228     l_location_rec.CONTENT_SOURCE_TYPE := 'USER_ENTERED';
229 
230 /*
231  The call to this api has been moved to a diff package in version 2 api
232  Original Call:    HZ_LOCATION_PUB.Create_Location
233 
234 */
235 
236     HZ_LOCATION_V2PUB.create_location (
237     p_init_msg_list                    =>  FND_API.G_FALSE,
238     p_location_rec                      => l_location_rec,
239     x_location_id                       => l_location_id,
240     x_return_status                    => x_return_status,
241     x_msg_count                        => x_msg_count ,
242     x_msg_data                          => x_msg_data );
243 
244 IF aso_debug_pub.g_debug_flag = 'Y' THEN
245 aso_debug_pub.add('create_party_site:after create_loc:l_location_id '||l_location_id, 1, 'N');
246 aso_debug_pub.add('create_party_site:after create_loc:x_return_status '||x_return_status, 1, 'N');
247 END IF;
248      IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
249 
250         l_party_site_rec.party_id := p_party_site_rec.party_id;
251         l_party_site_rec.location_id := l_location_id;
252         l_party_site_rec.identifying_address_flag := p_party_site_rec.primary_flag;
253 
254 /*
255   The call to this api has been moved to a diff package in version 2 api
256   Original Call:      HZ_PARTY_PUB.Create_Party_Site
257 */
258 
259     HZ_PARTY_SITE_V2PUB.create_party_site (
260     p_init_msg_list                 => FND_API.G_FALSE,
261     p_party_site_rec                => l_party_site_rec,
262     x_party_site_id                 => x_party_site_id,
263     x_party_site_number             => l_party_site_number,
264     x_return_status                 => x_return_status,
265     x_msg_count                     => x_msg_count,
266     x_msg_data                      => x_msg_data );
267 
268 IF aso_debug_pub.g_debug_flag = 'Y' THEN
269 aso_debug_pub.add('create_party_site:after create_site:x_party_site_id '||x_party_site_id, 1, 'N');
270 aso_debug_pub.add('create_party_site:after create_site:x_return_status '||x_return_status, 1, 'N');
271 END IF;
272      else
273        RAISE FND_API.G_EXC_ERROR;
274      END IF;
275      IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
276 
277          l_party_site_use_rec.party_site_id := x_party_site_id;
278    --      l_party_site_use_rec.begin_date    := sysdate;
279          l_party_site_use_rec.site_use_type := p_party_site_rec.party_site_use_type;
280 
281 /*
282   The call to this api has been moved to a diff package in version 2 api
283 
284   Original Call:       HZ_PARTY_PUB.Create_Party_Site_Use
285 */
286 
287     HZ_PARTY_SITE_V2PUB.create_party_site_use (
288     p_init_msg_list                 => FND_API.G_FALSE,
289     p_party_site_use_rec            => l_party_site_use_rec,
290     x_party_site_use_id             => l_party_site_use_id,
291     x_return_status                 => x_return_status,
292     x_msg_count                     => x_msg_count,
293     x_msg_data                      => x_msg_data );
294 
295 
296 IF aso_debug_pub.g_debug_flag = 'Y' THEN
297 aso_debug_pub.add('create_party_site:after create_site_use:x_return_status '||x_return_status, 1, 'N');
298 aso_debug_pub.add('create_party_site:after create_site_use:l_party_site_use_id '||l_party_site_use_id, 1, 'N
299 ');
300 END IF;
301          IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
305       else
302             RAISE FND_API.G_EXC_ERROR;
303          END IF;
304 
306         RAISE FND_API.G_EXC_ERROR;
307       END IF;
308 
309       EXCEPTION
310           WHEN FND_API.G_EXC_ERROR THEN
311             ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
312                    P_API_NAME => L_API_NAME
313                   ,P_PKG_NAME => G_PKG_NAME
314                   ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
315                   ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
316                   ,X_MSG_COUNT => X_MSG_COUNT
317                   ,X_MSG_DATA => X_MSG_DATA
318                   ,X_RETURN_STATUS => X_RETURN_STATUS);
319           WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
320                  ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
321                    P_API_NAME => L_API_NAME
322                   ,P_PKG_NAME => G_PKG_NAME
323                   ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
324                   ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
325                   ,X_MSG_COUNT => X_MSG_COUNT
326                   ,X_MSG_DATA => X_MSG_DATA
327                   ,X_RETURN_STATUS => X_RETURN_STATUS);
328           WHEN OTHERS THEN
329                  ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
330                    P_API_NAME => L_API_NAME
331                   ,P_PKG_NAME => G_PKG_NAME
332                   ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
333                   ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
334                   ,X_MSG_COUNT => X_MSG_COUNT
335                   ,X_MSG_DATA => X_MSG_DATA
336                   ,X_RETURN_STATUS => X_RETURN_STATUS);
337 
338 END Create_Party_Site;
339 
340 PROCEDURE Update_Party(
341         p_party_rec                 IN PARTY_REC_TYPE,
342         x_return_status             OUT NOCOPY /* file.sql.39 change */   VARCHAR2,
343         x_msg_count                 OUT NOCOPY /* file.sql.39 change */   NUMBER,
344         x_msg_data                  OUT NOCOPY /* file.sql.39 change */   VARCHAR2)
345 IS
346 l_api_name         VARCHAR2(40) := 'Update_Party' ;
347 /*  The record defintions have been moved to a diff package in version 2 api.
348 l_Person_rec       HZ_PARTY_PUB.Person_rec_Type DEFAULT HZ_PARTY_PUB.G_MISS_PERSON_REC;
349 l_Organization_rec HZ_PARTY_PUB.Organization_rec_Type;
350 l_party_rec        HZ_PARTY_PUB.Party_rec_Type := HZ_PARTY_PUB.G_MISS_PARTY_REC;
351 */
352 l_Person_rec       HZ_PARTY_V2PUB.Person_rec_Type; /*  DEFAULT HZ_PARTY_V2PUB.G_MISS_PERSON_REC; */
353 l_Organization_rec HZ_PARTY_V2PUB.Organization_rec_Type;
354 l_party_rec        HZ_PARTY_V2PUB.Party_rec_Type; /* := HZ_PARTY_V2PUB.G_MISS_PARTY_REC; */
355 
356 l_Party_Id                          NUMBER;
357 
358 /* The record definition has been moved as part of version 2 api
359 l_party_rel_rec    HZ_PARTY_PUB.PARTY_REL_REC_TYPE
360                   := HZ_PARTY_PUB.G_MISS_PARTY_REL_REC;
361 */
362 
363 l_party_object_version_number      NUMBER;
364 l_object_version_number            NUMBER;
365 l_party_rel_rec    HZ_RELATIONSHIP_V2PUB.RELATIONSHIP_REC_TYPE
366                   :=  HZ_RELATIONSHIP_V2PUB.G_MISS_REL_REC;
367 l_profile_id       NUMBER;
368 l_msg_count        NUMBER;
369 l_msg_data         VARCHAR2(2000);
370 l_party_relationship_id  number;
371 
372 l_last_update_date DATE;
373 l_party_rel_last_update_date DATE;
374 begin
375    SAVEPOINT UPDATE_PARTY_PVT;
376 -- Initialize API return status to SUCCESS
377    x_return_status := FND_API.G_RET_STS_SUCCESS;
378 
379  -- Initializing the created_by_module column for all the records as per
380  -- changes in version 2 api's.
381 
382     l_person_rec.Created_by_Module := 'ASO_CUSTOMER_DATA';
383     l_organization_rec.Created_by_Module := 'ASO_CUSTOMER_DATA';
384 
385   --l_party_rec.TOTAL_NUM_OF_ORDERS := p_party_rec.TOTAL_NUM_OF_ORDERS;
386   if p_party_rec.PARTY_TYPE = 'PERSON' then
387     l_person_rec.party_rec             :=  l_party_rec;
388     IF p_party_rec.party_id <> FND_API.G_MISS_NUM THEN
389     l_person_rec.party_rec.party_id    :=  p_party_rec.party_id;
390     END IF;
391     IF p_party_rec.person_first_name <> FND_API.G_MISS_CHAR THEN
392     l_person_rec.person_FIRST_NAME            :=  p_party_rec.person_first_name;
393     END IF;
394     IF p_party_rec.person_middle_name <> FND_API.G_MISS_CHAR THEN
395     l_person_rec.person_MIDDLE_NAME           :=  p_party_rec.person_middle_name;
396     END IF;
397     IF p_party_rec.person_last_name <> FND_API.G_MISS_CHAR THEN
398     l_person_rec.person_LAST_NAME             :=  p_party_rec.person_last_name;
399     END IF;
400     IF p_party_rec.person_title <> FND_API.G_MISS_CHAR THEN
401     l_person_rec.person_TITLE                 :=  p_party_rec.person_title;
402     END IF;
403     IF p_party_rec.DATE_OF_BIRTH <> FND_API.G_MISS_DATE THEN
404     l_person_rec.DATE_OF_BIRTH         :=  p_party_rec.DATE_OF_BIRTH;
405     END IF;
406     IF p_party_rec.person_known_as <> FND_API.G_MISS_CHAR THEN
407     l_person_rec.KNOWN_AS              :=  p_party_rec.person_known_as;
408     END IF;
409     IF p_party_rec.PERSONAL_INCOME <> FND_API.G_MISS_NUM THEN
410     l_person_rec.PERSONAL_INCOME       :=  p_party_rec.PERSONAL_INCOME;
411     END IF;
412     l_last_update_date                 :=  p_party_rec.LAST_UPDATE_DATE;
413 
414 /*
415  The call to the api has been moved to a diff package in version 2 api
416  Original Call:   HZ_PARTY_PUB.update_person
417 */
418 
422         BEGIN
419 -- Getting the object version number
420 -- This is used by API to lock the object being updated
421 
423         -- Intialize the variable as it used multiple times in this procedure
424         l_party_object_version_number := FND_API.G_MISS_NUM;
425 
426         SELECT OBJECT_VERSION_NUMBER
427         INTO   l_party_object_version_number
428         FROM   HZ_PARTIES
429         WHERE  PARTY_ID = p_party_rec.party_id;
430 
431         EXCEPTION
432         WHEN OTHERS THEN
433         l_party_object_version_number := FND_API.G_MISS_NUM;
434         END;
435 
436 IF aso_debug_pub.g_debug_flag = 'Y' THEN
437 aso_debug_pub.add('update_pty:before update_per', 1, 'N');
438 aso_debug_pub.add('update_pty:object_version_number  '||l_party_object_version_number, 1, 'N');
439 END IF;
440 
441 HZ_PARTY_V2PUB.update_person (
442     p_init_msg_list                    => FND_API.G_FALSE,
443     p_person_rec                       => l_PERSON_REC,
444     p_party_object_version_number      => l_party_object_version_number,
445     x_profile_id                       => l_profile_id,
446     x_return_status                    => x_return_status,
447     x_msg_count                        => x_msg_count ,
448     x_msg_data                         => x_msg_data
449 );
450 
451 IF aso_debug_pub.g_debug_flag = 'Y' THEN
452 aso_debug_pub.add('update_pty:after update_per:x_return_status '||x_return_status, 1, 'N');
453 aso_debug_pub.add('update_pty:after update_per:p_party_rec.party_id '||p_party_rec.party_id, 1, 'N');
454 END IF;
455 
456   elsif p_party_rec.party_type =  'ORGANIZATION' THEN
457 --        AND p_party_rec.party_name IS NOT NULL
458 --        AND p_party_rec.party_name <> FND_API.G_MISS_CHAR THEN
459     l_organization_rec.party_rec         := l_party_rec;
460     l_organization_rec.party_rec.party_id   := p_party_rec.party_id;
461 --    l_organization_rec.ORGANIZATION_NAME    := p_party_rec.party_name;
462     l_organization_rec.CURR_FY_POTENTIAL_REVENUE    := p_party_rec.CURR_FY_POTENTIAL_REVENUE;
463     l_organization_rec.EMPLOYEES_TOTAL      := p_party_rec.num_of_employees;
464 
465     l_last_update_date     := p_party_rec.last_update_date;
466 
467 /*
468  The call to this api has been moved to a diff package in version 2 api
469  Original Call:   HZ_PARTY_PUB.update_organization
470 */
471 
472 -- Getting the object version number
473 -- This is used by API to lock the object being updated
474 
475         BEGIN
476         -- Intialize the variable as it used multiple times in this procedure
477         l_party_object_version_number := FND_API.G_MISS_NUM;
478 
479         SELECT OBJECT_VERSION_NUMBER
480         INTO   l_party_object_version_number
481         FROM   HZ_PARTIES
482         WHERE  PARTY_ID = p_party_rec.party_id;
483 
484         EXCEPTION
485         WHEN OTHERS THEN
486         l_party_object_version_number := FND_API.G_MISS_NUM;
487         END;
488 
489 IF aso_debug_pub.g_debug_flag = 'Y' THEN
490 aso_debug_pub.add('update_pty:before update_organization', 1, 'N');
491 aso_debug_pub.add('update_pty:object_version_number  '||l_party_object_version_number, 1, 'N');
492 END IF;
493 
494 
495 HZ_PARTY_V2PUB.update_organization (
496     p_init_msg_list                    => FND_API.G_FALSE,
497     p_organization_rec                 => l_organization_rec ,
498     p_party_object_version_number      => l_party_object_version_number,
499     x_profile_id                       => l_profile_id ,
500     x_return_status                    => x_return_status ,
501     x_msg_count                        => x_msg_count ,
502     x_msg_data                         => x_msg_data
503 );
504 
505 IF aso_debug_pub.g_debug_flag = 'Y' THEN
506 aso_debug_pub.add('update_pty:after update_org:x_return_status '||x_return_status, 1, 'N');
507 aso_debug_pub.add('update_pty:after update_org:p_party_rec.party_id '||p_party_rec.party_id, 1, 'N');
508 END IF;
509   elsif p_party_rec.party_type = 'PARTY_RELATIONSHIP' THEN
510     l_party_rel_rec.party_rec := l_party_rec;
511     l_party_rel_rec.party_rec.party_id := p_party_rec.party_id;
512     l_last_update_date                 :=  p_party_rec.LAST_UPDATE_DATE;
513 
514     SELECT last_update_date, relationship_id
515     INTO l_party_rel_last_update_date,l_party_relationship_id
516     from hz_relationships
517     where party_id = p_party_rec.party_id
518     and SUBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
519     and OBJECT_TABLE_NAME(+) = 'HZ_PARTIES';
520 
521 
522     l_party_rel_rec.relationship_id := l_party_relationship_id;
523 
524 /*
525    The call to this api has been moved to a diff package in version 2 api
526    Original Call:  HZ_PARTY_PUB.update_party_relationship
527 
528 */
529 
530 -- Getting the object version number
531 -- This is used by API to lock the object being updated
532 
533         BEGIN
534         -- Intialize the variable as it used multiple times in this procedure
535         l_party_object_version_number := FND_API.G_MISS_NUM;
536 
537         SELECT OBJECT_VERSION_NUMBER
538         INTO   l_party_object_version_number
539         FROM   HZ_PARTIES
540         WHERE  PARTY_ID = p_party_rec.party_id;
541 
542         SELECT OBJECT_VERSION_NUMBER
543         INTO   l_object_version_number
544         FROM   HZ_RELATIONSHIPS
548         EXCEPTION
545         WHERE  RELATIONSHIP_ID  = l_party_relationship_id
546         AND    DIRECTIONAL_FLAG = 'F';
547 
549         WHEN OTHERS THEN
550         l_party_object_version_number := FND_API.G_MISS_NUM;
551         l_object_version_number := FND_API.G_MISS_NUM;
552         END;
553 
554 IF aso_debug_pub.g_debug_flag = 'Y' THEN
555 aso_debug_pub.add('update_pty:before update_reltn', 1, 'N');
556 aso_debug_pub.add('update_pty:object_version_number  '||l_party_object_version_number, 1, 'N');
557 END IF;
558 
559 HZ_RELATIONSHIP_V2PUB.update_relationship (
560     p_init_msg_list               =>  FND_API.G_FALSE,
561     p_relationship_rec            => l_party_rel_rec ,
562     p_object_version_number       => l_object_version_number,
563     p_party_object_version_number => l_party_object_version_number,
564     x_return_status               => x_return_status,
565     x_msg_count                   => x_msg_count,
566     x_msg_data                    => x_msg_data
567 );
568 
569 
570 
571 IF aso_debug_pub.g_debug_flag = 'Y' THEN
572 aso_debug_pub.add('update_pty:after update_reltn:x_return_status '||x_return_status, 1, 'N');
573 aso_debug_pub.add('update_pty:after update_reltn:p_party_rec.party_id '||p_party_rec.party_id, 1, 'N');
574 END IF;
575   end if; -- end party type
576 
577   IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
578     RAISE FND_API.G_EXC_ERROR;
579   END IF;
580 
581       EXCEPTION
582           WHEN FND_API.G_EXC_ERROR THEN
583             ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
584                    P_API_NAME => L_API_NAME
585                   ,P_PKG_NAME => G_PKG_NAME
586                   ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
587                   ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
588                   ,X_MSG_COUNT => X_MSG_COUNT
589                   ,X_MSG_DATA => X_MSG_DATA
590                   ,X_RETURN_STATUS => X_RETURN_STATUS);
591 
592           WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
593            ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
594                    P_API_NAME => L_API_NAME
595                   ,P_PKG_NAME => G_PKG_NAME
596                   ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
597                   ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
598                   ,X_MSG_COUNT => X_MSG_COUNT
599                   ,X_MSG_DATA => X_MSG_DATA
600                   ,X_RETURN_STATUS => X_RETURN_STATUS);
601           WHEN OTHERS THEN
602            ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
603                    P_API_NAME => L_API_NAME
604                   ,P_PKG_NAME => G_PKG_NAME
605                   ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
606                   ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
607                   ,X_MSG_COUNT => X_MSG_COUNT
608                   ,X_MSG_DATA => X_MSG_DATA
609                   ,X_RETURN_STATUS => X_RETURN_STATUS);
610 
611   -- End of API body
612   --
613 end Update_Party;
614 
615 PROCEDURE Validate_CustAccount(
616 	p_init_msg_list		IN	VARCHAR2,
617 	p_party_id		IN	NUMBER,
618 	p_cust_account_id	IN	NUMBER,
619 	x_return_status	 OUT NOCOPY /* file.sql.39 change */   VARCHAR2,
620         x_msg_count	 OUT NOCOPY /* file.sql.39 change */   NUMBER,
621         x_msg_data	 OUT NOCOPY /* file.sql.39 change */   VARCHAR2)
622 IS
623     CURSOR C_Account IS
624 	SELECT status, account_activation_date, account_termination_date FROM HZ_CUST_ACCOUNTS
625 	WHERE cust_account_id = p_cust_account_id;
626 
627     l_api_name          VARCHAR2(40) := 'Validate_CustAccount' ;
628     l_account_status	VARCHAR2(1);
629     l_activation_date	DATE;
630     l_termination_date		DATE;
631 BEGIN
632     SAVEPOINT VALIDATE_CUSTACCOUNT_PVT;
633     -- Initialize message list if p_init_msg_list is set to TRUE.
634     IF FND_API.to_Boolean( p_init_msg_list ) THEN
635         FND_MSG_PUB.initialize;
636     END IF;
637 
638     --  Initialize API return status to success
639     x_return_status := FND_API.G_RET_STS_SUCCESS;
640 IF aso_debug_pub.g_debug_flag = 'Y' THEN
641 aso_debug_pub.add('validate cust_acct:p_cust_account_id '||p_cust_account_id, 1, 'N');
642 END IF;
643     IF (p_cust_account_id IS NOT NULL AND p_cust_account_id <> FND_API.G_MISS_NUM) THEN
644         OPEN C_Account;
645 	FETCH C_Account INTO l_account_status, l_activation_date, l_termination_date;
646         IF (C_Account%NOTFOUND OR
647 	    (sysdate NOT BETWEEN NVL(l_activation_date, sysdate) AND
648 						NVL(l_termination_date, sysdate))OR
649 			l_account_status <> 'A') THEN
650 		  x_return_status := FND_API.G_RET_STS_ERROR;
651 	       IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
652 		    FND_MESSAGE.Set_Name('ASO', 'API_INVALID_ID');
653 		    FND_MESSAGE.Set_Token('COLUMN', 'CUST_ACCOUNT', FALSE);
654 		    FND_MSG_PUB.ADD;
655 	       END IF;
656         END IF;
657 	    CLOSE C_Account;
658     END IF;
659 
660 IF aso_debug_pub.g_debug_flag = 'Y' THEN
661 aso_debug_pub.add('validate cust_acct:x_return_status '||x_return_status, 1, 'N');
662 END IF;
663   IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
664     RAISE FND_API.G_EXC_ERROR;
665   END IF;
666 
667       EXCEPTION
668           WHEN FND_API.G_EXC_ERROR THEN
669             ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
670                    P_API_NAME => L_API_NAME
671                   ,P_PKG_NAME => G_PKG_NAME
675                   ,X_MSG_DATA => X_MSG_DATA
672                   ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
673                   ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
674                   ,X_MSG_COUNT => X_MSG_COUNT
676                   ,X_RETURN_STATUS => X_RETURN_STATUS);
677 
678           WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
679 		 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
680                    P_API_NAME => L_API_NAME
681                   ,P_PKG_NAME => G_PKG_NAME
682                   ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
683                   ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
684                   ,X_MSG_COUNT => X_MSG_COUNT
685                   ,X_MSG_DATA => X_MSG_DATA
686                   ,X_RETURN_STATUS => X_RETURN_STATUS);
687           WHEN OTHERS THEN
688 		 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
689                    P_API_NAME => L_API_NAME
690                   ,P_PKG_NAME => G_PKG_NAME
691                   ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
692                   ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
693                   ,X_MSG_COUNT => X_MSG_COUNT
694                   ,X_MSG_DATA => X_MSG_DATA
695                   ,X_RETURN_STATUS => X_RETURN_STATUS);
696 
697 END Validate_CustAccount;
698 
699 
700 PROCEDURE Create_Customer_Account(
701    p_api_version       IN  NUMBER
702   ,p_init_msg_list     IN  VARCHAR2  := FND_API.g_false
703   ,p_commit            IN  VARCHAR2  := FND_API.g_false
704 --  ,p_validation_level  IN  NUMBER    := FND_API.g_valid_level_full
705   ,P_Qte_REC   IN ASO_QUOTE_PUB.Qte_Header_Rec_Type
706   ,P_Account_number IN NUMBER := FND_API.G_MISS_NUM
707   ,x_return_status     OUT NOCOPY /* file.sql.39 change */   VARCHAR2
708   ,x_msg_count         OUT NOCOPY /* file.sql.39 change */   NUMBER
709   ,x_msg_data          OUT NOCOPY /* file.sql.39 change */   VARCHAR2
710   ,x_acct_id           OUT NOCOPY /* file.sql.39 change */   NUMBER
711     )
712    IS
713   l_api_version CONSTANT NUMBER       := 1.0;
714   l_api_name    CONSTANT VARCHAR2(45) := 'Create_Customer_Account';
715 
716        CURSOR C_source_codes(l_source_code_id NUMBER) Is
717          SELECT source_code
718          FROM ams_source_codes
719          WHERE source_code_id = l_source_code_id;
720 
721        CURSOR C_party_info (l_party_id NUMBER) IS
722          SELECT party_type, party_name
723          FROM hz_parties
724          WHERE party_id = l_party_id;
725 
726        CURSOR C_acct_number IS
727          SELECT aso_account_number_s.nextval
728          FROM  dual;
729 
730        CURSOR c_party_rel_rec(l_party_id NUMBER) IS
731         SELECT object_id from
732         hz_relationships
733         where party_id = l_party_id
734         and SUBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
735         and OBJECT_TABLE_NAME(+) = 'HZ_PARTIES';
736 
737 -- The record definitions have been moved to a different package in version 2 api
738 --      account_rec	        hz_customer_accounts_pub.account_rec_type;
739         account_rec	        HZ_CUST_ACCOUNT_V2PUB.cust_account_rec_type;
740 
741 --	   person_rec              hz_party_pub.person_rec_type;
742         person_rec              HZ_PARTY_V2PUB.person_rec_type;
743 
744 --      organization_rec        hz_party_pub.organization_rec_type;
745         organization_rec        HZ_PARTY_V2PUB.organization_rec_type;
746 
747 --	   cust_profile_rec	       hz_customer_accounts_pub.cust_profile_rec_type;
748         cust_profile_rec	       HZ_CUSTOMER_PROFILE_V2PUB.customer_profile_rec_type;
749 
750 --	   p_party_rec		hz_party_pub.party_rec_type;
751         p_party_rec      HZ_PARTY_V2PUB.party_rec_type;
752 
753         l_acct_id               NUMBER;
754         l_account_number        VARCHAR2(30);
755         l_party_id              NUMBER;
756         l_party_number          VARCHAR2(30);
757         l_party_name          VARCHAR2(360);
758 	   l_profile_id		NUMBER;
759         l_return_status  VARCHAR2(1);
760         l_msg_count         NUMBER;
761         l_msg_data          VARCHAR2(2000);
762         l_gen_cust_num          VARCHAR2(3);
763         l_party_type            VARCHAR2(30);
764         customer_party_id     NUMBER;
765 
766     BEGIN
767 ---- Initialize---------------------
768 
769      SAVEPOINT CREATE_CUSTOMER_ACCOUNT_PVT;
770 
771 aso_debug_pub.g_debug_flag := nvl(fnd_profile.value('ASO_ENABLE_DEBUG'),'N');
772 
773    IF FND_API.to_boolean(p_init_msg_list) THEN
774       FND_MSG_PUB.initialize;
775    END IF;
776   IF NOT FND_API.compatible_api_call(
777          l_api_version,
778          p_api_version,
779          l_api_name,
780          g_pkg_name
781    ) THEN
782       RAISE FND_API.g_exc_unexpected_error;
783    END IF;
784 x_return_status := FND_API.g_ret_sts_success;
785 
786 -- Initializing the created_by_module column for all the records as per
787 -- changes in version 2 api's.
788 
789    account_rec.Created_by_Module := 'ASO_CUSTOMER_DATA';
790    person_rec.Created_by_Module := 'ASO_CUSTOMER_DATA';
791    organization_rec.Created_by_Module := 'ASO_CUSTOMER_DATA';
792    cust_profile_rec.Created_by_Module := 'ASO_CUSTOMER_DATA';
793 
794 -- if needed generate account_number.
795 	SELECT generate_customer_number INTO l_gen_cust_num
796 	FROM ar_system_parameters;
797 
798 -- typically should be set to 'Y' if no we will try to create a new one.
802 
799 -- however, this could error out
800 
801         IF l_gen_cust_num = 'N' and p_account_number <> FND_API.G_MISS_NUM THEN
803                account_rec.account_number := p_account_number;
804 
805         ELSIF l_gen_cust_num = 'N'
806               and ( p_account_number = FND_API.G_MISS_NUM
807               or p_account_number is null) THEN
808 
809                OPEN C_acct_number;
810                FETCH C_acct_number into  account_rec.account_number;
811                CLOSE C_acct_number;
812 
813                account_rec.account_number := 'ASO'||account_rec.account_number;
814 
815         END IF;
816 
817 -- figure OUT NOCOPY /* file.sql.39 change */ if the party is a person or an organization
818 
819      OPEN C_party_info(p_qte_rec.party_id);
820        FETCH C_party_info INTO l_party_type, l_party_name;
821        IF (C_party_info%NOTFOUND) THEN
822           IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
823               FND_MESSAGE.Set_Name('ASO', 'API_INVALID_ID');
824               FND_MESSAGE.Set_Token('COLUMN', 'PARTY ID', FALSE);
825               FND_MSG_PUB.ADD;
826           END IF;
827           raise FND_API.G_EXC_ERROR;
828        END IF;
829        CLOSE C_party_info;
830 
831 IF aso_debug_pub.g_debug_flag = 'Y' THEN
832 aso_debug_pub.add('create cust_acct:l_party_type '||l_party_type, 1, 'N');
833 aso_debug_pub.add('create cust_acct:l_party_name '||l_party_name, 1, 'N');
834 END IF;
835     IF l_party_type = 'PARTY_RELATIONSHIP' THEN
836       OPEN c_party_rel_rec(p_qte_rec.party_id);
837       FETCH c_party_rel_rec INTO customer_party_id;
838       CLOSE c_party_rel_rec;
839 
840       OPEN C_party_info(Customer_Party_id);
841 	 FETCH C_party_info INTO l_party_type, l_party_name;
842 	 CLOSE C_party_info;
843 
844     ELSE
845       customer_party_id := p_qte_rec.party_id;
846     END IF;
847 --    account_rec.cust_account_id :=null;
848 --    account_rec.status := 'I';
849 
850 account_rec.account_name := substr(l_party_name,1,240);
851 
852 -- if marketing source code is valid then pass the source code
853     IF p_qte_rec.marketing_source_code is not NULL
854       AND p_qte_rec.marketing_source_code_id <> FND_API.G_MISS_NUM THEN
855 
856          OPEN C_source_codes( p_qte_rec.marketing_source_code_id);
857          FETCH C_source_codes INTO account_rec.source_code;
858          IF (C_source_codes%NOTFOUND) THEN
859            IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
860               FND_MESSAGE.Set_Name('ASO', 'API_MISSING_INFO');
861               FND_MESSAGE.Set_Token('COLUMN', 'SOURCE CODES', FALSE);
862               FND_MSG_PUB.ADD;
863           END IF;
864         END IF;
865         CLOSE C_source_codes;
866 
867       END IF;  -- source codes
868 
869    -- will test this for patch set C
870     --account_rec.payment_term_id  := to_number(FND_PROFILE.VALUE('ASO_PAYMENT_TERM'));
871 
872 IF aso_debug_pub.g_debug_flag = 'Y' THEN
873 aso_debug_pub.add('create cust_acct:l_party_type '||l_party_type, 1, 'N');
874 aso_debug_pub.add('create cust_acct:account_rec.account_name '||account_rec.account_name, 1, 'N');
875 END IF;
876 -- if party is a person
877     IF l_party_type = 'PERSON' THEN
878 
879       person_rec.party_rec := p_party_rec;
880       person_rec.party_rec.party_id := customer_party_id;
881 /*
882       The call to create_account procedure has been moved to
883       a new package in TCA version 2 API's
884 
885      Original Call:  hz_customer_accounts_pub.create_account
886 */
887 
888       HZ_CUST_ACCOUNT_V2PUB.create_cust_account (
889       p_init_msg_list                         => FND_API.G_FALSE,
890       p_cust_account_rec                      => account_rec,
891       p_person_rec                            => person_rec,
892       p_customer_profile_rec                  => cust_profile_rec,
893       p_create_profile_amt                    => 'Y',
894       x_cust_account_id                       => l_acct_id,
895       x_account_number                        => l_account_number,
896       x_party_id                              => l_party_id	,
897       x_party_number                          => l_party_number,
898       x_profile_id                            => l_profile_id,
899       x_return_status                         => l_return_status,
900       x_msg_count                             => l_msg_count,
901       x_msg_data                              => l_msg_data );
902 
903 IF aso_debug_pub.g_debug_flag = 'Y' THEN
904 aso_debug_pub.add('create cust_acct:after create_acct:l_acct_id '||l_acct_id, 1, 'N');
905 END IF;
906 
907 -- if party is an organization
908      ELSIF l_party_type = 'ORGANIZATION' THEN
909 
910         organization_rec.party_rec := p_party_rec;
911         organization_rec.party_rec.party_id := customer_party_id;
912 /*
913       The call to create_account procedure has been moved to
914       a new package in TCA version 2 API's
915 
916       Original Call:  hz_customer_accounts_pub.create_account
917 */
918       HZ_CUST_ACCOUNT_V2PUB.create_cust_account (
919       p_init_msg_list                         => FND_API.G_FALSE,
920       p_cust_account_rec                      => account_rec,
921       p_organization_rec                      => organization_rec,
922       p_customer_profile_rec                  => cust_profile_rec,
926       x_party_id                              => l_party_id	,
923       p_create_profile_amt                    => 'Y',
924       x_cust_account_id                       => l_acct_id,
925       x_account_number                        => l_account_number,
927       x_party_number                          => l_party_number,
928       x_profile_id                            => l_profile_id,
929       x_return_status                         => l_return_status,
930       x_msg_count                             => l_msg_count,
931       x_msg_data                              => l_msg_data  );
932 
933 IF aso_debug_pub.g_debug_flag = 'Y' THEN
934 aso_debug_pub.add('create cust_acct:after create_acct:l_acct_id '||l_acct_id, 1, 'N');
935 END IF;
936     END IF;
937 
938 
939     IF l_Return_Status <> FND_API.G_RET_STS_SUCCESS THEN
940        IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
941               FND_MESSAGE.Set_Name('ASO', 'API_MISSING_ID');
942               FND_MESSAGE.Set_Token('COLUMN', 'ACCT ID', FALSE);
943               FND_MSG_PUB.ADD;
944           END IF;
945           raise FND_API.G_EXC_ERROR;
946     ELSE
947      x_acct_id  := l_acct_id;
948     END IF;
949  FND_MSG_PUB.Count_And_Get
950       (  p_count          =>   x_msg_count,
951          p_data           =>   x_msg_data
952       );
953 
954       EXCEPTION
955           WHEN FND_API.G_EXC_ERROR THEN
956               ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
957                    P_API_NAME => L_API_NAME
958                   ,P_PKG_NAME => G_PKG_NAME
959                   ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
960                   ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
961 		  ,P_SQLCODE => SQLCODE
962                   ,P_SQLERRM => SQLERRM
963                   ,X_MSG_COUNT => X_MSG_COUNT
964                   ,X_MSG_DATA => X_MSG_DATA
965                   ,X_RETURN_STATUS => X_RETURN_STATUS);
966 
967           WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
968               ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
969                    P_API_NAME => L_API_NAME
970                   ,P_PKG_NAME => G_PKG_NAME
971                   ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
972                   ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
973 		  ,P_SQLCODE => SQLCODE
974                   ,P_SQLERRM => SQLERRM
975                   ,X_MSG_COUNT => X_MSG_COUNT
976                   ,X_MSG_DATA => X_MSG_DATA
977                   ,X_RETURN_STATUS => X_RETURN_STATUS);
978 
979           WHEN OTHERS THEN
980               ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
981                    P_API_NAME => L_API_NAME
982                   ,P_PKG_NAME => G_PKG_NAME
983                   ,P_EXCEPTION_LEVEL => ASO_UTILITY_PVT.G_EXC_OTHERS
984                   ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
985 		  ,P_SQLCODE => SQLCODE
986                   ,P_SQLERRM => SQLERRM
987                   ,X_MSG_COUNT => X_MSG_COUNT
988                   ,X_MSG_DATA => X_MSG_DATA
989                   ,X_RETURN_STATUS => X_RETURN_STATUS);
990 
991  END Create_Customer_Account;
992 
993 
994 PROCEDURE Create_ACCT_SITE ( p_api_version       IN  NUMBER
995   ,p_init_msg_list     IN  VARCHAR2  := FND_API.g_false
996   ,p_commit            IN  VARCHAR2  := FND_API.g_false
997 --  ,p_validation_level  IN  NUMBER    := FND_API.g_valid_level_full
998   ,p_Cust_Account_Id NUMBER
999   ,p_Party_Site_Id NUMBER
1000   ,p_Acct_site     VARCHAR2 := 'NONE'
1001   ,x_return_status     OUT NOCOPY /* file.sql.39 change */   VARCHAR2
1002   ,x_msg_count         OUT NOCOPY /* file.sql.39 change */   NUMBER
1003   ,x_msg_data          OUT NOCOPY /* file.sql.39 change */   VARCHAR2
1004   ,x_customer_site_id  OUT NOCOPY /* file.sql.39 change */   NUMBER
1005    )
1006     IS
1007 l_api_version CONSTANT NUMBER       := 1.0;
1008 l_api_name    CONSTANT VARCHAR2(45) := 'Create_ACCT_SITE';
1009 
1010  -- acct site need not be verified
1011 
1012        CURSOR C_acct_site (account_id NUMBER, site_id NUMBER) IS
1013            SELECT cust_acct_site_id
1014            FROM hz_cust_acct_sites
1015            WHERE cust_account_id = Account_Id
1016            AND party_site_id    = Site_Id;
1017 -- The record definition has been moved to a diff. package in VERSION 2 API.
1018 --        p_acct_site_Rec           hz_customer_accounts_pub.acct_site_rec_type;
1019           p_acct_site_Rec           HZ_CUST_ACCOUNT_SITE_V2PUB.cust_acct_site_rec_type;
1020 
1021 		l_customer_site_id        NUMBER := NULL;
1022 
1023   BEGIN
1024    ---- Initialize---------------------
1025 
1026    SAVEPOINT CREATE_ACCT_SITE_PVT;
1027 
1028 
1029    IF FND_API.to_boolean(p_init_msg_list) THEN
1030       FND_MSG_PUB.initialize;
1031    END IF;
1032  IF NOT FND_API.compatible_api_call(
1033          l_api_version,
1034          p_api_version,
1035          l_api_name,
1036          g_pkg_name
1037    ) THEN
1038       RAISE FND_API.g_exc_unexpected_error;
1039    END IF;
1040     x_return_status := FND_API.g_ret_sts_success;
1041 
1042  -- Intializing created_by_module as required in version 2 api for the record structure
1043 
1044    p_acct_site_Rec.created_by_module := 'ASO_CUSTOMER_DATA';
1045 
1046 
1047     Open C_acct_site (p_cust_account_id, p_party_site_id);
1048     Fetch C_acct_site into l_customer_site_id;
1049     IF (C_acct_site%NOTFOUND) THEN
1050       l_customer_site_id := null;
1054 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1051     END IF;
1052     Close C_acct_site;
1053 
1055 aso_debug_pub.add('create acct_site:before create_site:l_customer_site_id '||l_customer_site_id, 1, 'N');
1056 END IF;
1057     IF l_customer_site_id is not NULL THEN
1058        x_customer_site_id :=  l_customer_site_id ;
1059     ELSE
1060     p_acct_site_rec.cust_account_id := P_cust_account_id;
1061     p_acct_site_rec.party_site_id   := P_party_site_id;
1062 /*
1063       The call to create_account_site procedure has been moved to
1064       a new package in TCA version 2 API's
1065 
1066     Original Call: hz_customer_accounts_pub.create_account_site
1067 */
1068 
1069     HZ_CUST_ACCOUNT_SITE_V2PUB.create_cust_acct_site (
1070     p_init_msg_list                         => FND_API.G_FALSE,
1071     p_cust_acct_site_rec                    => p_acct_site_rec,
1072     x_cust_acct_site_id                     => l_customer_site_id,
1073     x_return_status                         => x_return_status,
1074     x_msg_count                             => x_msg_count,
1075     x_msg_data                              => x_msg_data  );
1076 
1077 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1078 aso_debug_pub.add('create acct_site:after create_site:l_customer_site_id '||l_customer_site_id, 1, 'N');
1079 aso_debug_pub.add('create acct_site:after create_site:x_return_status '||x_return_status, 1, 'N');
1080 END IF;
1081     IF x_Return_Status <> FND_API.G_RET_STS_SUCCESS THEN
1082        IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1083               FND_MESSAGE.Set_Name('ASO', 'API_MISSING_ID');
1084               FND_MESSAGE.Set_Token('COLUMN', 'ACCT SITE', FALSE);
1085               FND_MSG_PUB.ADD;
1086           END IF;
1087           raise FND_API.G_EXC_ERROR;
1088     ELSE
1089            x_customer_site_id :=  l_customer_site_id ;
1090     END IF;
1091 
1092    END IF; -- customer site id not nul
1093    FND_MSG_PUB.Count_And_Get
1094       (  p_count          =>   x_msg_count,
1095          p_data           =>   x_msg_data
1096       );
1097 
1098       EXCEPTION
1099           WHEN FND_API.G_EXC_ERROR THEN
1100               ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
1101                    P_API_NAME => L_API_NAME
1102                   ,P_PKG_NAME => G_PKG_NAME
1103                   ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1104                   ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
1105 		  ,P_SQLCODE => SQLCODE
1106                   ,P_SQLERRM => SQLERRM
1107                   ,X_MSG_COUNT => X_MSG_COUNT
1108                   ,X_MSG_DATA => X_MSG_DATA
1109                   ,X_RETURN_STATUS => X_RETURN_STATUS);
1110 
1111           WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1112               ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
1113                    P_API_NAME => L_API_NAME
1114                   ,P_PKG_NAME => G_PKG_NAME
1115                   ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1116                   ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
1117 		  ,P_SQLCODE => SQLCODE
1118                   ,P_SQLERRM => SQLERRM
1119                   ,X_MSG_COUNT => X_MSG_COUNT
1120                   ,X_MSG_DATA => X_MSG_DATA
1121                   ,X_RETURN_STATUS => X_RETURN_STATUS);
1122 
1123           WHEN OTHERS THEN
1124               ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
1125                    P_API_NAME => L_API_NAME
1126                   ,P_PKG_NAME => G_PKG_NAME
1127                   ,P_EXCEPTION_LEVEL => ASO_UTILITY_PVT.G_EXC_OTHERS
1128                   ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
1129 		  ,P_SQLCODE => SQLCODE
1130                   ,P_SQLERRM => SQLERRM
1131                   ,X_MSG_COUNT => X_MSG_COUNT
1132                   ,X_MSG_DATA => X_MSG_DATA
1133                   ,X_RETURN_STATUS => X_RETURN_STATUS);
1134   END Create_acct_site;
1135 
1136 
1137 PROCEDURE Create_ACCT_SITE_USES (
1138    p_api_version       IN  NUMBER
1139   ,p_init_msg_list     IN  VARCHAR2  := FND_API.g_false
1140   ,p_commit            IN  VARCHAR2  := FND_API.g_false
1141 -- ,p_validation_level IN  NUMBER    := FND_API.g_valid_level_full
1142   ,P_Cust_Account_Id   IN  NUMBER
1143   ,P_Party_Site_Id     IN  NUMBER
1144   ,P_cust_acct_site_id IN  NUMBER    := NULL
1145   ,P_Acct_Site_type    IN  VARCHAR2  := 'NONE'
1146   ,x_cust_acct_site_id OUT NOCOPY /* file.sql.39 change */   NUMBER
1147   ,x_return_status     OUT NOCOPY /* file.sql.39 change */   VARCHAR2
1148   ,x_msg_count         OUT NOCOPY /* file.sql.39 change */   NUMBER
1149   ,x_msg_data          OUT NOCOPY /* file.sql.39 change */   VARCHAR2
1150   ,x_site_use_id  OUT NOCOPY /* file.sql.39 change */   NUMBER
1151   )
1152      IS
1153        CURSOR C_site_use(acct_site_id NUMBER, Site_type VARCHAR2) IS
1154          SELECT site_use_id
1155          FROM hz_cust_site_uses
1156          WHERE cust_acct_site_id = acct_site_id
1157          AND site_use_code = Site_type
1158          AND status = 'A';
1159 
1160        CURSOR party_site_use(l_party_site_id NUMBER, Site_type VARCHAR2) IS
1161          SELECT party_site_use_id
1162          FROM hz_party_site_uses
1163          WHERE party_site_id = l_party_site_id
1164          AND site_use_type = Site_type
1165          AND status = 'A';
1166 
1167 -- this is arbitrary. we are doing it because location is needed for site uses.
1168        CURSOR C_location(l_party_site_id NUMBER) IS
1169         Select hzl.city
1170         From hz_locations hzl,hz_party_sites hps
1174 
1171         Where hps.party_site_id = p_party_site_id
1172         And hzl.location_id = hps.location_id;
1173 
1175         l_api_version CONSTANT NUMBER       := 1.0;
1176         l_api_name    CONSTANT VARCHAR2(45) := 'Create_ACCT_SITE_USES';
1177 
1178         l_location          VARCHAR2(60);
1179 --        p_acct_site_uses_Rec  hz_customer_accounts_pub.acct_site_uses_rec_type;
1180 --    	p_cust_profile_rec    hz_customer_accounts_pub.cust_profile_rec_type;
1181 
1182 --  The above two record types have been moved to diff. packages in VERSION 2 API's.
1183 
1184         p_acct_site_uses_Rec  HZ_CUST_ACCOUNT_SITE_V2PUB.cust_site_use_rec_type;
1185     	   p_cust_profile_rec    HZ_CUSTOMER_PROFILE_V2PUB.customer_profile_rec_type;
1186 
1187         l_site_use_id             NUMBER := NULL;
1188 
1189 
1190        l_Cust_Account_Id NUMBER  := p_Cust_Account_Id ;
1191        l_Party_Site_Id   NUMBER  := P_Party_Site_Id;
1192        l_Acct_Site_type VARCHAR2(30) := P_Acct_Site_type ;
1193        lx_party_site_use_id NUMBER;
1194        l_party_site_use number;
1195 	  l_profile varchar2(1);
1196      BEGIN
1197 
1198         ---- Initialize---------------------
1199 
1200      SAVEPOINT CREATE_ACCT_SITE_USES_PVT;
1201 
1202     IF FND_API.to_boolean(p_init_msg_list) THEN
1203         FND_MSG_PUB.initialize;
1204     END IF;
1205 
1206     IF NOT FND_API.compatible_api_call(
1207          l_api_version,
1208          p_api_version,
1209          l_api_name,
1210          g_pkg_name
1211     ) THEN
1212       RAISE FND_API.g_exc_unexpected_error;
1213     END IF;
1214     x_return_status := FND_API.g_ret_sts_success;
1215 
1216  -- Intializing created_by_module as required in version 2 api for the record structure
1217 
1218     p_acct_site_uses_Rec.created_by_module := 'ASO_CUSTOMER_DATA';
1219     p_cust_profile_rec.created_by_module := 'ASO_CUSTOMER_DATA';
1220 
1221 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1222 aso_debug_pub.add('create acct_site_use:p_cust_acct_site_id '||p_cust_acct_site_id, 1, 'N');
1223 END IF;
1224 	IF (p_cust_acct_site_id IS NULL) OR
1225 	   (p_cust_acct_site_id = FND_API.G_MISS_NUM) THEN
1226            Create_ACCT_SITE(p_api_version       => 1.0
1227                         ,p_Cust_Account_Id  => l_cust_account_id
1228                         ,p_Party_Site_Id => l_party_site_id
1229                         ,p_Acct_site     => l_Acct_Site_type
1230                         ,x_return_status => x_return_status
1231                         ,x_msg_count     => x_msg_count
1232                         ,x_msg_data      =>   x_msg_data
1233                         ,x_customer_site_id  => x_cust_acct_site_id
1234                         ) ;
1235 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1236 aso_debug_pub.add('create acct_site_use:after create_site:x_cust_acct_site_id '||x_cust_acct_site_id, 1, 'N');
1237 END IF;
1238            IF x_Return_Status <> FND_API.G_RET_STS_SUCCESS THEN
1239                 raise FND_API.G_EXC_ERROR;
1240            END IF;
1241 	END IF;
1242 
1243         IF x_cust_acct_site_id is not null and x_cust_acct_site_id <> FND_API.G_MISS_NUM then
1244           p_acct_site_uses_Rec.cust_acct_site_id := x_cust_acct_site_id;
1245         ELSE
1246           p_acct_site_uses_Rec.cust_acct_site_id := p_cust_acct_site_id;
1247 	  x_cust_acct_site_id := p_cust_acct_site_id;
1248         END IF;
1249 
1250           p_acct_site_uses_Rec.site_use_code    := l_Acct_Site_type ;
1251 
1252        Open C_site_use(x_cust_acct_site_id,p_acct_site_uses_Rec.site_use_code);
1253          Fetch C_site_use into l_site_use_id;
1254          IF (C_site_use%NOTFOUND) THEN
1255               l_site_use_id := null;
1256          END IF;
1257          Close C_site_use;
1258 
1259 
1260          IF l_site_use_id is not NULL then
1261              x_site_use_id := l_site_use_id  ;
1262          ELSE
1263 
1264 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1265 aso_debug_pub.add('create acct_site_use:x_site_use_id '||x_site_use_id, 1, 'N');
1266 END IF;
1267            OPEN C_location(l_party_site_id);
1268            FETCH C_location into l_location;
1269            IF (C_location%NOTFOUND) THEN
1270               l_location := 'NO_LOCATION';
1271            END IF;
1272            CLOSE C_location;
1273 
1274 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1275 aso_debug_pub.add('create acct_site_use:l_location '||l_location, 1, 'N');
1276 END IF;
1277 
1278 
1279 --   added for bug 2291297
1280          SELECT AUTO_SITE_NUMBERING INTO l_profile
1281          FROM AR_SYSTEM_PARAMETERS;
1282 
1283      IF l_profile = 'N' then
1284            p_acct_site_uses_Rec.location := substr(l_Acct_Site_type ||' ' ||
1285 								    l_location ||' ' ||
1286 					 to_char(p_acct_site_uses_Rec.cust_acct_site_id), 1, 40) ;
1287      END IF;
1288             -- since this is the first site use rec create it as a primary
1289             /* don't flag address as primary. bug 1512188 */
1290             --    p_acct_site_uses_Rec.primary_flag := 'Y';
1291 /*
1292  The call to create_acct_site_uses has been moved to a diff. package in VERSION 2 API's.
1293 
1294   Original Call:         hz_customer_accounts_pub.create_acct_site_uses
1295 */
1296 
1297     HZ_CUST_ACCOUNT_SITE_V2PUB.create_cust_site_use (
1298     p_init_msg_list                         => FND_API.G_FALSE,
1299     p_cust_site_use_rec                     => p_acct_site_uses_rec,
1300     p_customer_profile_rec                  => p_cust_profile_rec,
1304     x_return_status                         => x_return_status,
1301     p_create_profile                        => FND_API.G_FALSE,
1302     p_create_profile_amt                    => FND_API.G_FALSE,
1303     x_site_use_id                           => l_site_use_id,
1305     x_msg_count                             => x_msg_count,
1306     x_msg_data                              => x_msg_data  );
1307 
1308 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1309 aso_debug_pub.add('create acct_site_use:create_acct_site_use:x_return_status '||x_return_status, 1, 'N');
1310 aso_debug_pub.add('create acct_site_use:create_acct_site_use:l_site_use_id '||l_site_use_id, 1, 'N');
1311 END IF;
1312 
1313            IF x_Return_Status <> FND_API.G_RET_STS_SUCCESS THEN
1314              IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1315                 FND_MESSAGE.Set_Name('ASO', 'API_MISSING_ID');
1316                 FND_MESSAGE.Set_Token('COLUMN', 'ACCT_SITE_USES', FALSE);
1317                 FND_MSG_PUB.ADD;
1318              END IF;
1319              raise FND_API.G_EXC_ERROR;
1320            ELSE
1321                 x_site_use_id := l_site_use_id;
1322            END IF;
1323          END IF; -- x_site_use not null
1324 
1325         IF (l_party_site_id IS NOT NULL AND l_party_site_id <> FND_API.G_MISS_NUM) AND
1326            (l_acct_site_type IS NOT NULL AND l_acct_site_type <> FND_API.G_MISS_CHAR) THEN
1327 
1328            OPEN party_site_use(l_party_site_id,l_acct_site_type);
1329            FETCH party_site_use into l_party_site_use;
1330            CLOSE party_site_use;
1331 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1332 aso_debug_pub.add('create acct_site_use:create_party_site_use:l_party_site_use '||l_party_site_use, 1, 'N');
1333 END IF;
1334            IF l_party_site_use = NULL OR l_party_site_use = FND_API.G_MISS_NUM then
1335               Create_Party_Site_Use(
1336                 p_api_version          => 1.0,
1337             	p_party_site_id	       => l_party_site_id,
1338                 p_party_site_use_type  => l_acct_site_type,
1339             	x_party_site_use_id	   => lx_party_site_use_id,
1340            		x_return_status        => x_return_status,
1341                 x_msg_count            => x_msg_count,
1342                 x_msg_data             => x_msg_data);
1343 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1344 aso_debug_pub.add('create acct_site_use:create_party_site_use:lx_party_site_use_id '||lx_party_site_use_id, 1, 'N');
1345 END IF;
1346            end if;
1347            IF x_Return_Status <> FND_API.G_RET_STS_SUCCESS THEN
1348                 raise FND_API.G_EXC_ERROR;
1349            END IF;
1350         END IF;
1351     FND_MSG_PUB.Count_And_Get
1352       (  p_count          =>   x_msg_count,
1353          p_data           =>   x_msg_data
1354       );
1355 
1356       EXCEPTION
1357           WHEN FND_API.G_EXC_ERROR THEN
1358               ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
1359                    P_API_NAME => L_API_NAME
1360                   ,P_PKG_NAME => G_PKG_NAME
1361                   ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1362                   ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
1363         		  ,P_SQLCODE => SQLCODE
1364                   ,P_SQLERRM => SQLERRM
1365                   ,X_MSG_COUNT => X_MSG_COUNT
1366                   ,X_MSG_DATA => X_MSG_DATA
1367                   ,X_RETURN_STATUS => X_RETURN_STATUS);
1368 
1369           WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1370               ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
1371                    P_API_NAME => L_API_NAME
1372                   ,P_PKG_NAME => G_PKG_NAME
1373                   ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1374                   ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
1375         		  ,P_SQLCODE => SQLCODE
1376                   ,P_SQLERRM => SQLERRM
1377                   ,X_MSG_COUNT => X_MSG_COUNT
1378                   ,X_MSG_DATA => X_MSG_DATA
1379                   ,X_RETURN_STATUS => X_RETURN_STATUS);
1380 
1381           WHEN OTHERS THEN
1382               ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
1383                    P_API_NAME => L_API_NAME
1384                   ,P_PKG_NAME => G_PKG_NAME
1385                   ,P_EXCEPTION_LEVEL => ASO_UTILITY_PVT.G_EXC_OTHERS
1386                   ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
1387         		  ,P_SQLCODE => SQLCODE
1388                   ,P_SQLERRM => SQLERRM
1389                   ,X_MSG_COUNT => X_MSG_COUNT
1390                   ,X_MSG_DATA => X_MSG_DATA
1391                   ,X_RETURN_STATUS => X_RETURN_STATUS);
1392    END Create_acct_site_uses;
1393 
1394 
1395 PROCEDURE Create_Contact ( p_api_version       IN  NUMBER
1396   ,p_init_msg_list     IN  VARCHAR2  := FND_API.g_false
1397   ,p_commit            IN  VARCHAR2  := FND_API.g_false
1398 --  ,p_validation_level  IN  NUMBER    := FND_API.g_valid_level_full
1399   ,p_party_id            IN  NUMBER    := NULL
1400   ,p_Org_Contact_Id      IN  NUMBER
1401   ,p_Cust_account_id     IN  NUMBER
1402   ,p_Role_type           IN       VARCHAR2 := 'CONTACT'
1403   ,p_Begin_date          IN DATE := sysdate
1404    ,x_return_status     OUT NOCOPY /* file.sql.39 change */   VARCHAR2
1405   ,x_msg_count         OUT NOCOPY /* file.sql.39 change */   NUMBER
1406   ,x_msg_data          OUT NOCOPY /* file.sql.39 change */   VARCHAR2
1407   ,x_cust_account_role_id OUT NOCOPY /* file.sql.39 change */   NUMBER
1408      )
1409     IS
1410 
1411 
1412      CURSOR C_contact (l_party_id NUMBER, l_cust_account_id NumbER,
1413                        l_role_type varchar2 )IS
1417       AND cust_account_id = l_cust_account_id
1414       SELECT cust_account_role_id
1415       FROM hz_cust_account_roles
1416       WHERE party_id = l_party_id
1418       AND role_type = l_role_type;
1419 
1420      CURSOR C_party(l_org_contact_id NUMBER) IS
1421       SELECT par.party_id
1422       FROM hz_relationships par,
1423            hz_org_contacts     org
1424       WHERE org.party_relationship_id = par.relationship_id
1425       AND org.org_contact_id  = l_org_contact_id
1426 	and par.subject_type = 'PERSON'
1427 	 and par.subject_table_name ='HZ_PARTIES';
1428 
1429       l_api_version CONSTANT NUMBER       := 1.0;
1430       l_api_name    CONSTANT VARCHAR2(45) := 'Create_Contact';
1431       l_role_type               VARCHAR2(30);
1432       l_party_id                NUMBER;
1433 --   The record definition has been moved to a new package in VERSION 2 API.
1434 --   p_cust_acct_roles_rec  hz_customer_accounts_pub.cust_acct_roles_rec_type;
1435      p_cust_acct_roles_rec  HZ_CUST_ACCOUNT_ROLE_V2PUB.cust_account_role_rec_type;
1436 
1437 	l_cust_account_role_id    NUMBER := NULL;
1438 
1439 
1440     BEGIN
1441 
1442         SAVEPOINT CREATE_CONTACT_PVT;
1443 
1444    IF FND_API.to_boolean(p_init_msg_list) THEN
1445       FND_MSG_PUB.initialize;
1446    END IF;
1447   IF NOT FND_API.compatible_api_call(
1448          l_api_version,
1449          p_api_version,
1450          l_api_name,
1451          g_pkg_name
1452    ) THEN
1453       RAISE FND_API.g_exc_unexpected_error;
1454    END IF;
1455    x_return_status := FND_API.g_ret_sts_success;
1456 
1457 -- Initializing the created_by_module column for all the records as per
1458 -- changes in version 2 api's.
1459 
1460    p_cust_acct_roles_rec.Created_by_Module := 'ASO_CUSTOMER_DATA';
1461 
1462 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1463 aso_debug_pub.add('create con:p_party_id '||p_party_id, 1, 'N');
1464 aso_debug_pub.add('create con:p_org_contact_id '||p_org_contact_id, 1, 'N');
1465 END IF;
1466    l_party_id := p_party_id;
1467    IF l_party_id is  NULL
1468      OR  l_party_id = FND_API.G_MISS_NUM THEN
1469 
1470       IF p_org_contact_id is not NULL
1471        AND p_org_contact_id <> FND_API.G_MISS_NUM THEN
1472 
1473       OPEN C_party(p_org_contact_id);
1474       FETCH C_party INTO l_party_id;
1475          IF (C_party%NOTFOUND) THEN
1476             IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1477               FND_MESSAGE.Set_Name('ASO', 'API_MISSING_ID');
1478               FND_MESSAGE.Set_Token('COLUMN', 'PARTY_ID FOR GIVEN ORG CONTACT', FALSE);
1479               FND_MSG_PUB.ADD;
1480             END IF;
1481           raise FND_API.G_EXC_ERROR;
1482          END IF;
1483       Close C_party;
1484 
1485 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1486 aso_debug_pub.add('create con:derive from org_contact:l_party_id '||l_party_id, 1, 'N');
1487 END IF;
1488 
1489      END IF;   -- org contact id is not null
1490   END IF;      -- party id is null
1491 
1492   IF (l_party_id is not NULL AND l_party_id <>  FND_API.G_MISS_NUM) THEN
1493 
1494 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1495 aso_debug_pub.add('create con:l_party_id '||l_party_id, 1, 'N');
1496 END IF;
1497  -- for now role type is always 'CONTACT'
1498      l_role_type := 'CONTACT';
1499 
1500 -- check if the contact already exists. if not create it
1501 
1502         OPEN  C_contact (l_party_id, p_cust_account_id, l_role_type);
1503         FETCH C_contact INTO  l_cust_account_role_id;
1504            IF (C_contact%NOTFOUND) THEN
1505               l_cust_account_role_id := NULL;
1506            END IF;
1507         CLOSE  C_contact;
1508 
1509 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1510 aso_debug_pub.add('create con:l_cust_account_role_id '||l_cust_account_role_id, 1, 'N');
1511 aso_debug_pub.add('create con:before create_acct_role:p_cust_account_id '||p_cust_account_id, 1, 'N');
1512 END IF;
1513 
1514        IF  l_cust_account_role_id is not NULL THEN
1515            x_cust_account_role_id:= l_cust_account_role_id;
1516        ELSE
1517 
1518            p_cust_acct_roles_rec.party_id        := l_party_id;
1519            p_cust_acct_roles_rec.cust_account_id := p_cust_account_id;
1520            p_cust_acct_roles_rec.role_type       := l_role_type;
1521 
1522 	-- The begin_date column has been deleted in record structure in version 2 api.
1523 	--      p_cust_acct_roles_rec.begin_date       := p_begin_date;
1524 
1525 /*
1526 The call to create_cust_acct_roles has been moved to a new package in version 2 api.
1527  Original Call: hz_customer_accounts_pub.create_cust_acct_roles
1528 */
1529 
1530     HZ_CUST_ACCOUNT_ROLE_V2PUB.create_cust_account_role (
1531     p_init_msg_list                         => FND_API.G_FALSE,
1532     p_cust_account_role_rec                 => p_cust_acct_roles_rec,
1533     x_cust_account_role_id                  => x_cust_account_role_id,
1534     x_return_status                         => x_return_status,
1535     x_msg_count                             => x_msg_count,
1536     x_msg_data                              => x_msg_data  );
1537 
1538 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1539 aso_debug_pub.add('create con:after create_acct_role:x_cust_account_role_id '||x_cust_account_role_id, 1, 'N');
1540 END IF;
1541              IF x_Return_Status <> FND_API.G_RET_STS_SUCCESS THEN
1542                 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1546                   FND_MSG_PUB.ADD;
1543                   THEN
1544                   FND_MESSAGE.Set_Name('ASO', 'API_MISSING_ID');
1545                   FND_MESSAGE.Set_Token('COLUMN', 'ACCT_ROLE', FALSE);
1547                 END IF;
1548                 raise FND_API.G_EXC_ERROR;
1549              ELSE
1550                 x_cust_account_role_id:= l_cust_account_role_id;
1551              END IF;
1552        END IF;
1553 
1554   END IF; -- party and org are null
1555 
1556 
1557  FND_MSG_PUB.Count_And_Get
1558       (  p_count          =>   x_msg_count,
1559          p_data           =>   x_msg_data
1560       );
1561 
1562       EXCEPTION
1563           WHEN FND_API.G_EXC_ERROR THEN
1564               ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
1565                    P_API_NAME => L_API_NAME
1566                   ,P_PKG_NAME => G_PKG_NAME
1567                   ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1568                   ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
1569 		  ,P_SQLCODE => SQLCODE
1570                   ,P_SQLERRM => SQLERRM
1571                   ,X_MSG_COUNT => X_MSG_COUNT
1572                   ,X_MSG_DATA => X_MSG_DATA
1573                   ,X_RETURN_STATUS => X_RETURN_STATUS);
1574 
1575           WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1576               ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
1577                    P_API_NAME => L_API_NAME
1578                   ,P_PKG_NAME => G_PKG_NAME
1579                   ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1580                   ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
1581 		  ,P_SQLCODE => SQLCODE
1582                   ,P_SQLERRM => SQLERRM
1583                   ,X_MSG_COUNT => X_MSG_COUNT
1584                   ,X_MSG_DATA => X_MSG_DATA
1585                   ,X_RETURN_STATUS => X_RETURN_STATUS);
1586 
1587           WHEN OTHERS THEN
1588               ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
1589                    P_API_NAME => L_API_NAME
1590                   ,P_PKG_NAME => G_PKG_NAME
1591                   ,P_EXCEPTION_LEVEL => ASO_UTILITY_PVT.G_EXC_OTHERS
1592                   ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
1593 		  ,P_SQLCODE => SQLCODE
1594                   ,P_SQLERRM => SQLERRM
1595                   ,X_MSG_COUNT => X_MSG_COUNT
1596                   ,X_MSG_DATA => X_MSG_DATA
1597                   ,X_RETURN_STATUS => X_RETURN_STATUS);
1598 
1599     END Create_Contact ;
1600 
1601 
1602 
1603 -- this procedure is called only from the aso_order_int.
1604 -- the return parameters are specific to the usage in aso_order_int
1605 
1606 
1607 PROCEDURE Create_ORG_CONTACT_ord (
1608       p_api_version       IN  NUMBER
1609   ,p_init_msg_list     IN  VARCHAR2  := FND_API.g_false
1610   ,p_commit            IN  VARCHAR2  := FND_API.g_false
1611 --  ,p_validation_level  IN  NUMBER    := FND_API.g_valid_level_full
1612   ,p_party_id NUMBER
1613   ,p_header_Party_Id NUMBER  := NULL
1614   ,p_acct_id  NUMBER         := NULL
1615   ,x_return_status     OUT NOCOPY /* file.sql.39 change */   VARCHAR2
1616   ,x_msg_count         OUT NOCOPY /* file.sql.39 change */   NUMBER
1617   ,x_msg_data          OUT NOCOPY /* file.sql.39 change */   VARCHAR2
1618   ,x_org_contact_id    OUT NOCOPY /* file.sql.39 change */   NUMBER
1619   ,x_party_id          OUT NOCOPY /* file.sql.39 change */   NUMBER
1620    )
1621      IS
1622      l_api_version CONSTANT NUMBER       := 1.0;
1623      l_api_name    CONSTANT VARCHAR2(45) := 'Create_ORG_CONTACT_ORD';
1624 
1625        Cursor C_org_contact (l_party_id NUMBER, l_header_party_id NUMBER) IS
1626         SELECT OC.org_contact_id
1627         FROM hz_org_contacts oc, hz_parties p, hz_relationships pr
1628         WHERE p.party_id = l_party_id
1629         AND p.party_id = pr.subject_id
1630         AND pr.object_id = l_header_party_id
1631         AND (pr.relationship_type = 'CONTACT'
1632            OR pr.relationship_type = 'CONTACT_OF')
1633         AND oc.party_relationship_id = pr.relationship_id;
1634 
1635         l_org_contact_id          NUMBER;
1636         l_header_party_id         NUMBER;
1637         -- p_org_contact_rec        hz_party_pub.org_contact_rec_type;
1638          p_org_contact_rec   HZ_PARTY_CONTACT_V2PUB.ORG_CONTACT_REC_TYPE;
1639          x_party_rel_id           NUMBER;
1640          x_party_number           NUMBER;
1641          l_party_type             VARCHAR2(50);
1642 
1643      BEGIN
1644         SAVEPOINT CREATE_ORG_CONTACT_ORD_PVT;
1645 
1646         IF FND_API.to_boolean(p_init_msg_list) THEN
1647               FND_MSG_PUB.initialize;
1648         END IF;
1649          IF NOT FND_API.compatible_api_call(
1650                 l_api_version,
1651                 p_api_version,
1652                 l_api_name,
1653                 g_pkg_name
1654             ) THEN
1655               RAISE FND_API.g_exc_unexpected_error;
1656          END IF;
1657          x_return_status := FND_API.g_ret_sts_success;
1658 
1659 --Initializing the created_by_module column for all the records as per
1660 -- changes in version 2 api's.
1661 
1662     p_org_contact_rec.Created_by_Module := 'ASO_CUSTOMER_DATA';
1663 
1664 
1665        x_party_id := null;
1666        x_org_contact_id := null;
1667        l_header_party_id  := p_header_party_id;
1668 
1669 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1670 aso_debug_pub.add('create org_con_ord:l_header_party_id '||l_header_party_id, 1, 'N');
1671 END IF;
1672         -- this is called if only account is passed and not the party
1676            SELECT acct.party_id, par.party_type
1673         IF l_header_party_id is NULL
1674                or l_header_party_id = FND_API.G_MISS_NUM THEN
1675              -- get the party id from acct id
1677            INTO l_header_party_id, l_party_type
1678            FROM aso_i_cust_accounts_v acct,
1679                 aso_i_parties_v       par
1680            WHERE acct.cust_account_id = p_acct_id
1681            AND      acct.party_id = par.party_id;
1682            IF (SQL%NOTFOUND) THEN
1683              IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1684               FND_MESSAGE.Set_Name('ASO', 'API_MISSING_ID');
1685               FND_MESSAGE.Set_Token('COLUMN', 'PARTY_ID', FALSE);
1686               FND_MSG_PUB.ADD;
1687              END IF;
1688              raise FND_API.G_EXC_ERROR;
1689             END IF;
1690         END IF;
1691 
1692 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1693 aso_debug_pub.add('create org_con_ord:l_header_party_id '||l_header_party_id, 1, 'N');
1694 aso_debug_pub.add('create org_con_ord:l_party_type '||l_party_type, 1, 'N');
1695 END IF;
1696 
1697 /*--- As per Edd Jentzsch suggestion we need not create an org contact for a
1698 --  B2C case.
1699 
1700     IF l_party_type = 'PERSON' THEN
1701         l_org_contact_id := NULL;
1702         x_party_id       := l_header_party_id;
1703     ELSE
1704 
1705 */
1706 
1707   -- kchervel added this if condition as a work around to avoid orders from
1708   -- failing when trying to create a relationship for itself.
1709 
1710   IF p_party_id <> l_header_party_id THEN
1711 
1712         Open C_org_contact(p_party_id, l_header_party_id);
1713         Fetch C_org_contact into l_org_contact_id;
1714         IF (C_org_contact%NOTFOUND) THEN
1715           IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1716 	      fnd_message.set_name('ASO', 'ORG_CONTACT_ID');
1717   	      FND_MSG_PUB.Add;
1718 	   END IF;
1719           -- RAISE FND_API.G_EXC_ERROR;
1720            l_org_contact_id := NULL;
1721         END IF;
1722         Close C_org_contact;
1723 
1724 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1725 aso_debug_pub.add('create org_con_ord:l_org_contact_id '||l_org_contact_id, 1, 'N');
1726 END IF;
1727 
1728    IF l_org_contact_id is NULL
1729      OR l_org_contact_id = FND_API.G_MISS_NUM THEN
1730 
1731      p_org_contact_rec.party_rel_rec.subject_id := p_party_id;
1732      p_org_contact_rec.party_rel_rec.object_id  := l_header_party_id;
1733   --   p_org_contact_rec.party_rel_rec.party_relationship_type := 'CONTACT_OF';
1734   -- In version 2 api, column has been renamed to relationship_type
1735 	p_org_contact_rec.party_rel_rec.relationship_type := 'CONTACT_OF';
1736 	p_org_contact_rec.party_rel_rec.start_date := sysdate;
1737 
1738 /*
1739 The call to Create_Org_Contact has been moved to a diff package in version 2 api.
1740   Original Call:  HZ_PARTY_PUB.create_org_contact
1741 */
1742 
1743 HZ_PARTY_CONTACT_V2PUB.create_org_contact (
1744     p_init_msg_list                    => FND_API.G_FALSE,
1745     p_org_contact_rec                  => p_org_contact_rec,
1746     x_org_contact_id                   => l_org_contact_id,
1747     x_party_rel_id                     => x_party_rel_id,
1748     x_party_id                         => x_party_id,
1749     x_party_number                     => x_party_number,
1750     x_return_status                    => x_return_status,
1751     x_msg_count                        => x_msg_count,
1752     x_msg_data                         => x_msg_data  );
1753 
1754 
1755 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1756 aso_debug_pub.add('create org_con_ord:after create_org_con:l_org_contact_id '||l_org_contact_id, 1, 'N');
1757 aso_debug_pub.add('create org_con_ord:after create_org_con:x_return_status '||x_return_status, 1, 'N');
1758 END IF;
1759          IF x_Return_Status <> FND_API.G_RET_STS_SUCCESS THEN
1760                 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1761                   THEN
1762                   FND_MESSAGE.Set_Name('ASO', 'API_MISSING_ID');
1763                   FND_MESSAGE.Set_Token('COLUMN', 'ORG_CONTACT', FALSE);
1764                   FND_MSG_PUB.ADD;
1765                 END IF;
1766                 raise FND_API.G_EXC_ERROR;
1767        END IF;
1768     END IF; -- org contact is null
1769 
1770         x_org_contact_id := l_org_contact_id;
1771 
1772     END IF; -- party id not equal to header party id
1773 
1774 --  END IF; -- for 'PERSON' type
1775 
1776 
1777  FND_MSG_PUB.Count_And_Get
1778       (  p_count          =>   x_msg_count,
1779          p_data           =>   x_msg_data
1780       );
1781 
1782       EXCEPTION
1783           WHEN FND_API.G_EXC_ERROR THEN
1784               ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
1785                    P_API_NAME => L_API_NAME
1786                   ,P_PKG_NAME => G_PKG_NAME
1787                   ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1788                   ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
1789 		  ,P_SQLCODE => SQLCODE
1790                   ,P_SQLERRM => SQLERRM
1791                   ,X_MSG_COUNT => X_MSG_COUNT
1792                   ,X_MSG_DATA => X_MSG_DATA
1793                   ,X_RETURN_STATUS => X_RETURN_STATUS);
1794 
1795           WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1796               ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
1797                    P_API_NAME => L_API_NAME
1801 		  ,P_SQLCODE => SQLCODE
1798                   ,P_PKG_NAME => G_PKG_NAME
1799                   ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1800                   ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
1802                   ,P_SQLERRM => SQLERRM
1803                   ,X_MSG_COUNT => X_MSG_COUNT
1804                   ,X_MSG_DATA => X_MSG_DATA
1805                   ,X_RETURN_STATUS => X_RETURN_STATUS);
1806 
1807           WHEN OTHERS THEN
1808               ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
1809                    P_API_NAME => L_API_NAME
1810                   ,P_PKG_NAME => G_PKG_NAME
1811                   ,P_EXCEPTION_LEVEL => ASO_UTILITY_PVT.G_EXC_OTHERS
1812                   ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
1813 		  ,P_SQLCODE => SQLCODE
1814                   ,P_SQLERRM => SQLERRM
1815                   ,X_MSG_COUNT => X_MSG_COUNT
1816                   ,X_MSG_DATA => X_MSG_DATA
1817                   ,X_RETURN_STATUS => X_RETURN_STATUS);
1818 
1819 END Create_ORG_CONTACT_ord ;
1820 
1821 
1822 PROCEDURE Create_Contact_Role ( p_api_version       IN  NUMBER
1823   ,p_init_msg_list     IN  VARCHAR2  := FND_API.g_false
1824   ,p_commit            IN  VARCHAR2  := FND_API.g_false
1825   ,p_party_id          IN NUMBER     := FND_API.G_MISS_NUM
1826   ,p_Cust_account_id     IN  NUMBER
1827   ,p_cust_account_site_id IN NUMBER  := FND_API.G_MISS_NUM
1828   ,p_Role_type           IN       VARCHAR2 := 'CONTACT'
1829   ,p_responsibility_type IN VARCHAR2 := FND_API.G_MISS_CHAR
1830   ,p_Begin_date          IN DATE := sysdate
1831   ,p_role_id            IN  NUMBER  :=  FND_API.G_MISS_NUM
1832   ,x_return_status     OUT NOCOPY /* file.sql.39 change */   VARCHAR2
1833   ,x_msg_count         OUT NOCOPY /* file.sql.39 change */   NUMBER
1834   ,x_msg_data          OUT NOCOPY /* file.sql.39 change */   VARCHAR2
1835   ,x_cust_account_role_id OUT NOCOPY /* file.sql.39 change */   NUMBER
1836 )
1837 IS
1838 l_api_version CONSTANT NUMBER       := 1.0;
1839 l_api_name    CONSTANT VARCHAR2(45) := 'Create_Contact_Role';
1840 
1841 --p_cust_acct_roles_rec  hz_customer_accounts_pub.cust_acct_roles_rec_type;
1842 --p_role_resp_rec       hz_customer_accounts_pub.role_resp_rec_type;
1843 -- The above two record definitions have been moved to a diff package in version 2 api's.
1844 p_cust_acct_roles_rec  HZ_CUST_ACCOUNT_ROLE_V2PUB.cust_account_role_rec_type;
1845 p_role_resp_rec       HZ_CUST_ACCOUNT_ROLE_V2PUB.role_responsibility_rec_type;
1846 
1847 l_responsibility_id NUMBER;
1848 
1849 CURSOR C_Get_Resp(role_id NUMBER, role_type VARCHAR2) IS
1850  SELECT responsibility_id
1851  FROM hz_role_responsibility
1852  WHERE cust_account_role_id = role_id
1853  AND responsibility_type = role_type;
1854 
1855 BEGIN
1856   SAVEPOINT CREATE_CONTACT_ROLE_PVT;
1857 
1858   IF FND_API.to_boolean(p_init_msg_list) THEN
1859     FND_MSG_PUB.initialize;
1860   END IF;
1861   IF NOT FND_API.compatible_api_call(
1862          l_api_version,
1863          p_api_version,
1864          l_api_name,
1865          g_pkg_name
1866    ) THEN
1867       RAISE FND_API.g_exc_unexpected_error;
1868    END IF;
1869    x_return_status := FND_API.g_ret_sts_success;
1870 -- Initializing the created_by_module column for all the records as per
1871 -- changes in version 2 api's.
1872 
1873    p_cust_acct_roles_rec.Created_by_Module := 'ASO_CUSTOMER_DATA';
1874    p_role_resp_rec.Created_by_Module := 'ASO_CUSTOMER_DATA';
1875 
1876 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1877 aso_debug_pub.add('create_contact_role: p_party_id: '||p_party_id,1,'N');
1878 aso_debug_pub.add('create_contact_role: p_cust_account_id: '||p_cust_account_id,1,'N');
1879 aso_debug_pub.add('create_contact_role: p_cust_account_site_id: '||p_cust_account_site_id,1,'N');
1880 aso_debug_pub.add('create_contact_role: p_role_type: '||p_role_type,1,'N');
1881 aso_debug_pub.add('create_contact_role: p_responsibility_type: '||p_responsibility_type,1,'N');
1882 aso_debug_pub.add('create_contact_role: p_begin_date: '||p_begin_date,1,'N');
1883 aso_debug_pub.add('create_contact_role: p_role_id: '||p_role_id,1,'N');
1884 END IF;
1885 
1886  IF p_role_id IS NULL OR p_role_id = FND_API.G_MISS_NUM THEN
1887    p_cust_acct_roles_rec.party_id        := p_party_id;
1888    p_cust_acct_roles_rec.cust_account_id := p_cust_account_id;
1889    p_cust_acct_roles_rec.role_type       := p_role_type;
1890 
1891 -- Begin_date column has been deleted in version 2 api record
1892 --   p_cust_acct_roles_rec.begin_date      := p_begin_date;
1893 
1894     p_cust_acct_roles_rec.cust_acct_site_id := p_cust_account_site_id;
1895 
1896 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1897 aso_debug_pub.add('before create cust acct roles',1,'N');
1898 END IF;
1899 
1900 /*
1901  The call to create_cust_acct_roles has been moved to a diff package in version 2 api.
1902   Original Call:  hz_customer_accounts_pub.create_cust_acct_roles
1903 */
1904 
1905     HZ_CUST_ACCOUNT_ROLE_V2PUB.create_cust_account_role (
1906     p_init_msg_list                         => FND_API.G_FALSE,
1907     p_cust_account_role_rec                 => p_cust_acct_roles_rec,
1908     x_cust_account_role_id                  => x_cust_account_role_id,
1909     x_return_status                         => x_return_status,
1910     x_msg_count                             => x_msg_count,
1911     x_msg_data                              => x_msg_data  );
1912 
1913 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1914 aso_debug_pub.add('create_contact_role:after create_cust_acct_role: x_return_status: '||x_return_status,1,'N');
1918      IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1915 END IF;
1916    IF x_return_status <>  FND_API.G_RET_STS_SUCCESS THEN
1917      x_cust_account_role_id := NULL;
1919        FND_MESSAGE.Set_Name('ASO', 'API_MISSING_ID');
1920        FND_MESSAGE.Set_Token('COLUMN', 'ACCT_ROLE', FALSE);
1921        FND_MSG_PUB.ADD;
1922      END IF;
1923      raise FND_API.G_EXC_ERROR;
1924    END IF;
1925   ELSE
1926     x_cust_account_role_id := p_role_id;
1927   END IF;
1928 
1929  OPEN C_Get_Resp(x_cust_account_role_id, p_responsibility_type);
1930  FETCH C_Get_Resp INTO l_responsibility_id;
1931 
1932 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1933 aso_debug_pub.add('create_contact_role:l_responsibility_id: '||l_responsibility_id,1,'N');
1934 END IF;
1935  IF C_Get_Resp%NOTFOUND THEN
1936 
1937    IF p_cust_account_site_id is not NULL AND
1938       p_cust_account_site_id <>  FND_API.G_MISS_NUM THEN
1939       p_role_resp_rec.cust_account_role_id := x_cust_account_role_id;
1940       p_role_resp_rec.responsibility_type := p_responsibility_type;
1941 
1942 /*
1943  The call to create_role_resp has been moved to a diff package in version 2 api.
1944 
1945  Original Call:      HZ_CUSTOMER_ACCOUNTS_PUB.create_role_resp
1946 */
1947 
1948 
1949     HZ_CUST_ACCOUNT_ROLE_V2PUB.create_role_responsibility (
1950     p_init_msg_list                         =>  FND_API.G_FALSE,
1951     p_role_responsibility_rec               => p_role_resp_rec,
1952     x_responsibility_id                     => l_responsibility_id,
1953     x_return_status                         => x_return_status,
1954     x_msg_count                             => x_msg_count,
1955     x_msg_data                              => x_msg_data  );
1956 
1957 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1958 aso_debug_pub.add('create_contact_role:after create_role_resp: x_return_status: '||x_return_status,1,'N');
1959 END IF;
1960      IF x_return_status <>  FND_API.G_RET_STS_SUCCESS THEN
1961        IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1962         FND_MESSAGE.Set_Name('ASO', 'API_MISSING_ID');
1963         FND_MESSAGE.Set_Token('COLUMN', 'ACCT_ROLE', FALSE);
1964         FND_MSG_PUB.ADD;
1965       END IF;
1966      raise FND_API.G_EXC_ERROR;
1967      END IF;
1968    END IF;
1969  END IF;
1970 
1971  CLOSE C_Get_Resp;
1972 
1973    FND_MSG_PUB.Count_And_Get
1974    (  p_count          =>   x_msg_count,
1975       p_data           =>   x_msg_data
1976    );
1977 
1978 EXCEPTION
1979           WHEN FND_API.G_EXC_ERROR THEN
1980               ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
1981                    P_API_NAME => L_API_NAME
1982                   ,P_PKG_NAME => G_PKG_NAME
1983                   ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1984                   ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
1985                   ,P_SQLCODE => SQLCODE
1986                   ,P_SQLERRM => SQLERRM
1987                   ,X_MSG_COUNT => X_MSG_COUNT
1988                   ,X_MSG_DATA => X_MSG_DATA
1989                   ,X_RETURN_STATUS => X_RETURN_STATUS);
1990 
1991           WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1992               ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
1993                    P_API_NAME => L_API_NAME
1994                   ,P_PKG_NAME => G_PKG_NAME
1995                   ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1996                   ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
1997                   ,P_SQLCODE => SQLCODE
1998                   ,P_SQLERRM => SQLERRM
1999                   ,X_MSG_COUNT => X_MSG_COUNT
2000                   ,X_MSG_DATA => X_MSG_DATA
2001                   ,X_RETURN_STATUS => X_RETURN_STATUS);
2002 
2003           WHEN OTHERS THEN
2004               ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
2005                    P_API_NAME => L_API_NAME
2006                   ,P_PKG_NAME => G_PKG_NAME
2007                   ,P_EXCEPTION_LEVEL => ASO_UTILITY_PVT.G_EXC_OTHERS
2008                   ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
2009                   ,P_SQLCODE => SQLCODE
2010                   ,P_SQLERRM => SQLERRM
2011                   ,X_MSG_COUNT => X_MSG_COUNT
2012                   ,X_MSG_DATA => X_MSG_DATA
2013                   ,X_RETURN_STATUS => X_RETURN_STATUS);
2014 END Create_Contact_Role;
2015 
2016 Procedure GET_ACCT_SITE_USES(
2017    p_api_version       IN  NUMBER
2018   ,p_init_msg_list     IN  VARCHAR2  := FND_API.g_false
2019   ,p_commit            IN  VARCHAR2  := FND_API.g_false
2020   ,P_Cust_Account_Id   IN  NUMBER
2021   ,P_Party_Site_Id     IN  NUMBER
2022   ,P_Acct_Site_type    IN  VARCHAR2 := 'NONE'
2023   ,x_return_status     OUT NOCOPY /* file.sql.39 change */   VARCHAR2
2024   ,x_msg_count         OUT NOCOPY /* file.sql.39 change */   NUMBER
2025   ,x_msg_data          OUT NOCOPY /* file.sql.39 change */   VARCHAR2
2026   ,x_site_use_id  OUT NOCOPY /* file.sql.39 change */   NUMBER
2027    )
2028   IS
2029 
2030   l_api_version CONSTANT NUMBER       := 1.0;
2031   l_api_name    CONSTANT VARCHAR2(45) := 'GET_ACCT_SITE_USES';
2032 
2033  BEGIN
2034    ---- Initialize---------------------
2035 
2036    IF FND_API.to_boolean(p_init_msg_list) THEN
2037       FND_MSG_PUB.initialize;
2038    END IF;
2039 
2040   IF NOT FND_API.compatible_api_call(
2041          l_api_version,
2042          p_api_version,
2043          l_api_name,
2044          g_pkg_name
2045    ) THEN
2049    x_return_status := FND_API.g_ret_sts_success;
2046       RAISE FND_API.g_exc_unexpected_error;
2047    END IF;
2048 
2050 
2051 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2052 aso_debug_pub.add('ASO_PARTY_INT.get_acct_site_use:P_Acct_Site_type: '||P_Acct_Site_type,1,'N');
2053 END IF;
2054 
2055    ASO_MAP_QUOTE_ORDER_INT.get_acct_site_uses (
2056        p_party_site_id   => P_Party_Site_Id,
2057        p_acct_site_type  => P_Acct_Site_Type,
2058        p_cust_account_id => P_Cust_Account_Id,
2059        x_return_status   => x_return_status,
2060        x_site_use_id     => x_site_use_id
2061    );
2062 
2063 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2064 aso_debug_pub.add('ASO_PARTY_INT.get_acct_site_use:x_site_use_id: '||x_site_use_id,1,'N');
2065 END IF;
2066 
2067 END GET_ACCT_SITE_USES;
2068 
2069 
2070 PROCEDURE Create_Customer_Account(
2071     p_api_version       IN      NUMBER,
2072     p_init_msg_list     IN      VARCHAR2  := FND_API.g_false,
2073     p_commit            IN      VARCHAR2  := FND_API.g_false,
2074     P_Party_id          IN      NUMBER,
2075     P_Account_number    IN      NUMBER := FND_API.G_MISS_NUM,
2076     x_return_status     OUT NOCOPY /* file.sql.39 change */   VARCHAR2,
2077     x_msg_count         OUT NOCOPY /* file.sql.39 change */   NUMBER,
2078     x_msg_data          OUT NOCOPY /* file.sql.39 change */   VARCHAR2,
2079     x_cust_acct_id      OUT NOCOPY /* file.sql.39 change */   NUMBER)
2080     IS
2081 
2082     l_api_version CONSTANT NUMBER       := 1.0;
2083     l_api_name    CONSTANT VARCHAR2(45) := 'Create_Customer_Account';
2084 
2085     CURSOR C_source_codes(l_source_code_id NUMBER) Is
2086         SELECT source_code
2087         FROM ams_source_codes
2088         WHERE source_code_id = l_source_code_id;
2089 
2090     CURSOR C_party_info (l_party_id NUMBER) IS
2091         SELECT party_type, party_name
2092         FROM hz_parties
2093         WHERE party_id = l_party_id;
2094 
2095     CURSOR C_acct_number IS
2096         SELECT aso_account_number_s.nextval
2097         FROM  dual;
2098 
2099     CURSOR c_party_rel_rec(l_party_id NUMBER) IS
2100         SELECT object_id from
2101         hz_relationships
2102         where party_id = l_party_id
2103         and object_table_name = 'HZ_PARTIES'
2104 	   and subject_type = 'PERSON'
2105 	   and subject_table_name = 'HZ_PARTIES';
2106 
2107 -- The record definitions have been moved to a different package in version 2 api
2108 --      account_rec         hz_customer_accounts_pub.account_rec_type;
2109         account_rec         HZ_CUST_ACCOUNT_V2PUB.cust_account_rec_type;
2110 
2111 --      person_rec              hz_party_pub.person_rec_type;
2112         person_rec              HZ_PARTY_V2PUB.person_rec_type;
2113 
2114 --      organization_rec        hz_party_pub.organization_rec_type;
2115         organization_rec        HZ_PARTY_V2PUB.organization_rec_type;
2116 
2117 --      cust_profile_rec        hz_customer_accounts_pub.cust_profile_rec_type;
2118         cust_profile_rec        HZ_CUSTOMER_PROFILE_V2PUB.customer_profile_rec_type;
2119 
2120 --      p_party_rec      hz_party_pub.party_rec_type;
2121         p_party_rec      HZ_PARTY_V2PUB.party_rec_type;
2122 
2123     l_acct_id          NUMBER;
2124     l_account_number   VARCHAR2(30);
2125     l_party_id         NUMBER;
2126     l_party_number     VARCHAR2(30);
2127     l_profile_id	   NUMBER;
2128     l_gen_cust_num     VARCHAR2(3);
2129     l_party_type       VARCHAR2(30);
2130     l_party_name	   VARCHAR2(360);
2131     customer_party_id  NUMBER;
2132     g_pkg_name         VARCHAR2(200) := 'CREATE_CUSTOMER_ACCOUNT';
2133 
2134 BEGIN
2135 
2136 ---- Initialize---------------------
2137     SAVEPOINT CREATE_CUSTOMER_ACCOUNT_PVT;
2138 
2139     IF FND_API.to_boolean(p_init_msg_list) THEN
2140         FND_MSG_PUB.initialize;
2141     END IF;
2142 
2143     IF NOT FND_API.compatible_api_call(
2144         l_api_version,
2145         p_api_version,
2146         l_api_name,
2147         g_pkg_name
2148     ) THEN
2149         RAISE FND_API.g_exc_unexpected_error;
2150     END IF;
2151 
2152     x_return_status := FND_API.g_ret_sts_success;
2153 
2154 -- Initializing the created_by_module column for all the records as per
2155 -- changes in version 2 api's.
2156 
2157    account_rec.Created_by_Module := 'ASO_CUSTOMER_DATA';
2158    person_rec.Created_by_Module := 'ASO_CUSTOMER_DATA';
2159    organization_rec.Created_by_Module := 'ASO_CUSTOMER_DATA';
2160    cust_profile_rec.Created_by_Module := 'ASO_CUSTOMER_DATA';
2161 
2162 
2163     -- if needed generate account_number.
2164 	SELECT generate_customer_number INTO l_gen_cust_num
2165 	FROM ar_system_parameters;
2166     -- typically should be set to 'Y' if no we will try to create a new one.
2167     -- however, this could error out
2168     IF l_gen_cust_num = 'Y' and p_account_number <> FND_API.G_MISS_NUM THEN
2169         account_rec.account_number := p_account_number;
2170     ELSIF l_gen_cust_num = 'N'
2171         and (p_account_number = FND_API.G_MISS_NUM or p_account_number is null) THEN
2172         OPEN C_acct_number;
2173             FETCH C_acct_number into account_rec.account_number;
2174         CLOSE C_acct_number;
2175         account_rec.account_number := 'ASO'||account_rec.account_number;
2176     END IF;
2177     -- figure OUT NOCOPY /* file.sql.39 change */ if the party is a person or an organization
2178 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2182         FETCH C_party_info INTO l_party_type, l_party_name;
2179 aso_debug_pub.add('create_cust_acct:P_Party_id: '||P_Party_id,1,'N');
2180 END IF;
2181     OPEN C_party_info(P_Party_id);
2183         IF (C_party_info%NOTFOUND) THEN
2184             IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2185                 FND_MESSAGE.Set_Name('ASO', 'API_INVALID_ID');
2186                 FND_MESSAGE.Set_Token('COLUMN', 'PARTY ID', FALSE);
2187                 FND_MSG_PUB.ADD;
2188             END IF;
2189             raise FND_API.G_EXC_ERROR;
2190         END IF;
2191     CLOSE C_party_info;
2192 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2193 aso_debug_pub.add('create_cust_acct:l_party_type: '||l_party_type,1,'N');
2194 END IF;
2195     -- if party is a relationship
2196     IF l_party_type = 'PARTY_RELATIONSHIP' THEN
2197         OPEN c_party_rel_rec(P_Party_id);
2198             FETCH c_party_rel_rec INTO customer_party_id;
2199         CLOSE c_party_rel_rec;
2200 
2201     OPEN C_party_info(customer_party_id);
2202     FETCH C_party_info INTO l_party_type, l_party_name;
2203         IF (C_party_info%NOTFOUND) THEN
2204             IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2205                 FND_MESSAGE.Set_Name('ASO', 'API_INVALID_ID');
2206                 FND_MESSAGE.Set_Token('COLUMN', 'PARTY ID', FALSE);
2207                 FND_MSG_PUB.ADD;
2208             END IF;
2209             raise FND_API.G_EXC_ERROR;
2210         END IF;
2211     CLOSE C_party_info;
2212 
2213     ELSE
2214         customer_party_id := P_Party_id;
2215     END IF;
2216 
2217 	account_rec.account_name := substr(l_party_name,1,240);
2218 
2219 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2220 aso_debug_pub.add('create_cust_acct:l_party_type: '||l_party_type,1,'N');
2221 aso_debug_pub.add('create_cust_acct:l_party_type: '||l_party_name,1,'N');
2222 aso_debug_pub.add('create_cust_acct:customer_party_id: '||customer_party_id,1,'N');
2223 END IF;
2224     -- if party is a person
2225     IF l_party_type = 'PERSON' THEN
2226         person_rec.party_rec := p_party_rec;
2227         person_rec.party_rec.party_id := customer_party_id;
2228 
2229 /*
2230       The call to create_account procedure has been moved to
2231       a new package in TCA version 2 API's
2232 
2233       Original Call:        hz_customer_accounts_pub.create_account
2234 
2235 */
2236 
2237 
2238       HZ_CUST_ACCOUNT_V2PUB.create_cust_account (
2239       p_init_msg_list                         => FND_API.G_FALSE,
2240       p_cust_account_rec                      => account_rec,
2241       p_person_rec                            => person_rec,
2242       p_customer_profile_rec                  => cust_profile_rec,
2243       p_create_profile_amt                    => 'Y',
2244       x_cust_account_id                       => l_acct_id,
2245       x_account_number                        => l_account_number,
2246       x_party_id                              => l_party_id ,
2247       x_party_number                          => l_party_number,
2248       x_profile_id                            => l_profile_id,
2249       x_return_status                         => x_return_status,
2250       x_msg_count                             => x_msg_count,
2251       x_msg_data                              => x_msg_data );
2252 
2253     -- if party is an organization
2254     ELSIF l_party_type = 'ORGANIZATION' THEN
2255         organization_rec.party_rec := p_party_rec;
2256         organization_rec.party_rec.party_id := customer_party_id;
2257 
2258 /*
2259       The call to create_account procedure has been moved to
2260       a new package in TCA version 2 API's
2261 
2262       Original Call:        hz_customer_accounts_pub.create_account
2263 */
2264 
2265       HZ_CUST_ACCOUNT_V2PUB.create_cust_account (
2266       p_init_msg_list                         => FND_API.G_FALSE,
2267       p_cust_account_rec                      => account_rec,
2268       p_organization_rec                      => organization_rec,
2269       p_customer_profile_rec                  => cust_profile_rec,
2270       p_create_profile_amt                    => 'Y',
2271       x_cust_account_id                       => l_acct_id,
2272       x_account_number                        => l_account_number,
2273       x_party_id                              => l_party_id ,
2274       x_party_number                          => l_party_number,
2275       x_profile_id                            => l_profile_id,
2276       x_return_status                         => x_return_status,
2277       x_msg_count                             => x_msg_count,
2278       x_msg_data                              => x_msg_data  );
2279 
2280 
2281     END IF;
2282 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2283 aso_debug_pub.add('create_cust_acct:after create_acct:l_acct_id: '||l_acct_id,1,'N');
2284 END IF;
2285     IF x_Return_Status <> FND_API.G_RET_STS_SUCCESS THEN
2286         IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2287             FND_MESSAGE.Set_Name('ASO', 'API_MISSING_ID');
2288             FND_MESSAGE.Set_Token('COLUMN', 'ACCT ID', FALSE);
2289             FND_MSG_PUB.ADD;
2290         END IF;
2291         raise FND_API.G_EXC_ERROR;
2292     ELSE
2293         x_cust_acct_id  := l_acct_id;
2294     END IF;
2295 
2296     FND_MSG_PUB.Count_And_Get(
2297         p_count          =>   x_msg_count,
2298         p_data           =>   x_msg_data);
2299 
2300     EXCEPTION
2301         WHEN FND_API.G_EXC_ERROR THEN
2305                 P_EXCEPTION_LEVEL   => FND_MSG_PUB.G_MSG_LVL_ERROR,
2302             ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
2303                 P_API_NAME          => L_API_NAME,
2304                 P_PKG_NAME          => G_PKG_NAME,
2306                 P_PACKAGE_TYPE      => ASO_UTILITY_PVT.G_PVT,
2307                 P_SQLCODE           => SQLCODE,
2308                 P_SQLERRM           => SQLERRM,
2309                 X_MSG_COUNT         => X_MSG_COUNT,
2310                 X_MSG_DATA          => X_MSG_DATA,
2311                 X_RETURN_STATUS     => X_RETURN_STATUS);
2312 
2313         WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2314             ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
2315                 P_API_NAME          => L_API_NAME,
2316                 P_PKG_NAME          => G_PKG_NAME,
2317                 P_EXCEPTION_LEVEL   => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR,
2318                 P_PACKAGE_TYPE      => ASO_UTILITY_PVT.G_PVT,
2319                 P_SQLCODE           => SQLCODE,
2320                 P_SQLERRM           => SQLERRM,
2321                 X_MSG_COUNT         => X_MSG_COUNT,
2322                 X_MSG_DATA          => X_MSG_DATA,
2323                 X_RETURN_STATUS     => X_RETURN_STATUS);
2324 
2325         WHEN OTHERS THEN
2326             ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
2327                 P_API_NAME          => L_API_NAME,
2328                 P_PKG_NAME          => G_PKG_NAME,
2329                 P_EXCEPTION_LEVEL   => ASO_UTILITY_PVT.G_EXC_OTHERS,
2330                 P_PACKAGE_TYPE      => ASO_UTILITY_PVT.G_PVT,
2331                 P_SQLCODE           => SQLCODE,
2332                 P_SQLERRM           => SQLERRM,
2333                 X_MSG_COUNT         => X_MSG_COUNT,
2334                 X_MSG_DATA          => X_MSG_DATA,
2335                 X_RETURN_STATUS     => X_RETURN_STATUS);
2336 
2337 END Create_Customer_Account;
2338 
2339 PROCEDURE Create_Cust_Acct_Relationship(
2340     p_api_version       IN      NUMBER,
2341     p_init_msg_list     IN      VARCHAR2  := FND_API.g_false,
2342     p_commit            IN      VARCHAR2  := FND_API.g_false,
2343     p_sold_to_cust_account	IN NUMBER,
2344     p_related_cust_account	IN NUMBER,
2345     p_relationship_type		IN VARCHAR2,
2346     x_return_status     OUT NOCOPY /* file.sql.39 change */   VARCHAR2,
2347     x_msg_count         OUT NOCOPY /* file.sql.39 change */   NUMBER,
2348     x_msg_data          OUT NOCOPY /* file.sql.39 change */   VARCHAR2)
2349 IS
2350 
2351 l_api_version CONSTANT NUMBER       := 1.0;
2352 l_api_name    CONSTANT VARCHAR2(45) := 'CREATE_CUST_ACCT_RELATIONSHIP';
2353 l_cust_acct_id NUMBER;
2354 -- l_create_cust_account VARCHAR2(1)
2355 --  := FND_PROFILE.Value('ASO_CREATE_CUST_ACCOUNT');
2356 --l_cust_acct_relate_rec
2357 --  hz_cust_acct_info_pub.cust_acct_relate_rec_type;
2358 -- The above record has been moved to a new package in version 2 api.
2359 l_cust_acct_relate_rec    HZ_CUST_ACCOUNT_V2PUB.cust_acct_relate_rec_type;
2360 
2361 
2362 BEGIN
2363 
2364   SAVEPOINT Create_Cust_Acct_Relationship;
2365 
2366     IF FND_API.to_boolean(p_init_msg_list) THEN
2367         FND_MSG_PUB.initialize;
2368     END IF;
2369 
2370     IF NOT FND_API.compatible_api_call(
2371         l_api_version,
2372         p_api_version,
2373         l_api_name,
2374         g_pkg_name
2375     ) THEN
2376         RAISE FND_API.g_exc_unexpected_error;
2377     END IF;
2378 
2379   x_return_status := FND_API.g_ret_sts_success;
2380 
2381 --Initializing the created_by_module column for all the records as per
2382 -- changes in version 2 api's.
2383 
2384    l_cust_acct_relate_rec.Created_by_Module := 'ASO_CUSTOMER_DATA';
2385 
2386 
2387   --setup the cust_acct_related_rec
2388   l_cust_acct_relate_rec.cust_account_id := p_related_cust_account;
2389   l_cust_acct_relate_rec.related_cust_account_id := p_sold_to_cust_account;
2390   l_cust_acct_relate_rec.relationship_type := 'ALL';
2391   l_cust_acct_relate_rec.customer_reciprocal_flag := 'N';
2392   --Fix for Bug 5855375
2393   l_cust_acct_relate_rec.bill_to_flag := 'N';
2394   l_cust_acct_relate_rec.ship_to_flag := 'N';
2395 
2396   IF aso_debug_pub.g_debug_flag = 'Y' THEN
2397   aso_debug_pub.add('p_related_cust_account = ' || p_related_cust_account, 1,'N');
2398   aso_debug_pub.add('sold_to_cust_account = ' || p_sold_to_cust_account, 1,'N');
2399   aso_debug_pub.add('relationship_type = ' || p_relationship_type,1,'N');
2400   END IF;
2401 
2402   IF p_relationship_type = 'BILL_TO' then
2403     l_cust_acct_relate_rec.bill_to_flag := 'Y';
2404   elsif p_relationship_type = 'SHIP_TO' then
2405     l_cust_acct_relate_rec.ship_to_flag := 'Y';
2406   else
2407     RAISE FND_API.G_EXC_ERROR;
2408   end if;
2409 
2410   IF aso_debug_pub.g_debug_flag = 'Y' THEN
2411   aso_debug_pub.add('enter cust1 and finish setup',1,'N');
2412   END IF;
2413 
2414 /*
2415  The call to .create_cust_acct_relate has been moved to a diff package in version 2 api
2416   Original Call:    HZ_CUST_ACCT_INFO_PUB.create_cust_acct_relate
2417 */
2418 
2419     HZ_CUST_ACCOUNT_V2PUB.create_cust_acct_relate (
2420     p_init_msg_list                         => FND_API.G_FALSE,
2421     p_cust_acct_relate_rec                  => l_cust_acct_relate_rec,
2422     x_return_status                         => x_return_status,
2423     x_msg_count                             => x_msg_count,
2424     x_msg_data                              => x_msg_data   );
2425 
2426   IF aso_debug_pub.g_debug_flag = 'Y' THEN
2430   IF x_return_status <>  FND_API.G_RET_STS_SUCCESS THEN
2427   aso_debug_pub.add('finish call create_cust_acct_relate and ' || x_return_status, 1, 'N');
2428   END IF;
2429 
2431      RAISE FND_API.G_EXC_ERROR;
2432   END IF;
2433 
2434     FND_MSG_PUB.Count_And_Get(
2435         p_count          =>   x_msg_count,
2436         p_data           =>   x_msg_data);
2437 
2438     EXCEPTION
2439         WHEN FND_API.G_EXC_ERROR THEN
2440             ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
2441                 P_API_NAME          => L_API_NAME,
2442                 P_PKG_NAME          => G_PKG_NAME,
2443                 P_EXCEPTION_LEVEL   => FND_MSG_PUB.G_MSG_LVL_ERROR,
2444                 P_PACKAGE_TYPE      => ASO_UTILITY_PVT.G_PVT,
2445                 P_SQLCODE           => SQLCODE,
2446                 P_SQLERRM           => SQLERRM,
2447                 X_MSG_COUNT         => X_MSG_COUNT,
2448                 X_MSG_DATA          => X_MSG_DATA,
2449                 X_RETURN_STATUS     => X_RETURN_STATUS);
2450 
2451         WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2452             ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
2453                 P_API_NAME          => L_API_NAME,
2454                 P_PKG_NAME          => G_PKG_NAME,
2455                 P_EXCEPTION_LEVEL   => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR,
2456                 P_PACKAGE_TYPE      => ASO_UTILITY_PVT.G_PVT,
2457                 P_SQLCODE           => SQLCODE,
2458                 P_SQLERRM           => SQLERRM,
2459                 X_MSG_COUNT         => X_MSG_COUNT,
2460                 X_MSG_DATA          => X_MSG_DATA,
2461                 X_RETURN_STATUS     => X_RETURN_STATUS);
2462 
2463         WHEN OTHERS THEN
2464             ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
2465                 P_API_NAME          => L_API_NAME,
2466                 P_PKG_NAME          => G_PKG_NAME,
2467                 P_EXCEPTION_LEVEL   => ASO_UTILITY_PVT.G_EXC_OTHERS,
2468                 P_PACKAGE_TYPE      => ASO_UTILITY_PVT.G_PVT,
2469                 P_SQLCODE           => SQLCODE,
2470                 P_SQLERRM           => SQLERRM,
2471                 X_MSG_COUNT         => X_MSG_COUNT,
2472                 X_MSG_DATA          => X_MSG_DATA,
2473                 X_RETURN_STATUS     => X_RETURN_STATUS);
2474 
2475 END Create_Cust_Acct_Relationship;
2476 
2477 
2478 
2479 PROCEDURE Create_Party_Site_Use(
2480     p_api_version          IN   NUMBER,
2481     p_init_msg_list        IN   VARCHAR2  := FND_API.g_false,
2482     p_commit               IN   VARCHAR2  := FND_API.g_false,
2483      p_party_site_id            IN NUMBER,
2484     p_party_site_use_type  IN   VARCHAR2,
2485      x_party_site_use_id    OUT NOCOPY /* file.sql.39 change */      NUMBER,
2486 	x_return_status		   OUT NOCOPY /* file.sql.39 change */   VARCHAR2,
2487     x_msg_count            OUT NOCOPY /* file.sql.39 change */   NUMBER,
2488     x_msg_data             OUT NOCOPY /* file.sql.39 change */   VARCHAR2)
2489 IS
2490 
2491     l_api_name              CONSTANT VARCHAR2(45) := 'Create_Party_Site_Use';
2492     l_api_version           CONSTANT NUMBER       := 1.0;
2493     l_party_site_use_rec  	HZ_PARTY_SITE_V2PUB.Party_Site_Use_Rec_Type;
2494 
2495 BEGIN
2496     SAVEPOINT CREATE_PARTY_SITE_USE_PVT;
2497 
2498     IF FND_API.to_boolean(p_init_msg_list) THEN
2499         FND_MSG_PUB.initialize;
2500     END IF;
2501 
2502     IF NOT FND_API.compatible_api_call(
2503         l_api_version,
2504         p_api_version,
2505         l_api_name,
2506         g_pkg_name
2507     ) THEN
2508         RAISE FND_API.g_exc_unexpected_error;
2509     END IF;
2510 
2511     -- Initialize API return status to SUCCESS
2512     x_return_status := FND_API.G_RET_STS_SUCCESS;
2513 
2514 --Initializing the created_by_module column for all the records as per
2515 -- changes in version 2 api's.
2516 
2517    l_party_site_use_rec.Created_by_Module := 'ASO_CUSTOMER_DATA';
2518 
2519 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2520 aso_debug_pub.add('create_pty_site_use:before create_pty_site_use:p_party_site_id ' || p_party_site_id, 1, 'N');
2521 aso_debug_pub.add('create_pty_site_use:before create_pty_site_use:p_party_site_use_type ' || p_party_site_use_type, 1, 'N');
2522 END IF;
2523 	 l_party_site_use_rec.party_site_id := p_party_site_id;
2524 
2525 --  Begin_date has been deleted in version 2 api.
2526 --	 l_party_site_use_rec.begin_date    := sysdate;
2527 
2528 	 l_party_site_use_rec.site_use_type := p_party_site_use_type;
2529 /*
2530 The call to Create_Party_Site_Use has been moved to diff package in version 2 api.
2531   Original Call:     HZ_PARTY_PUB.Create_Party_Site_Use
2532 */
2533 
2534     HZ_PARTY_SITE_V2PUB.create_party_site_use (
2535     p_init_msg_list                 => FND_API.G_FALSE,
2536     p_party_site_use_rec            => l_party_site_use_rec,
2537     x_party_site_use_id             => x_party_site_use_id,
2538     x_return_status                 => x_return_status,
2539     x_msg_count                     => x_msg_count,
2540     x_msg_data                      => x_msg_data  );
2541 
2542 
2543 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2544 aso_debug_pub.add('create_pty_site_use:after create_pty_site_use:x_party_site_use_id ' || x_party_site_use_id, 1, 'N');
2545 aso_debug_pub.add('create_pty_site_use:after create_pty_site_use:x_return_status ' || x_return_status, 1, 'N');
2546 END IF;
2547          IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2548             RAISE FND_API.G_EXC_ERROR;
2549          END IF;
2550 
2551       EXCEPTION
2552           WHEN FND_API.G_EXC_ERROR THEN
2553             ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
2554                    P_API_NAME => L_API_NAME
2555                   ,P_PKG_NAME => G_PKG_NAME
2556                   ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
2557                   ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
2558                   ,X_MSG_COUNT => X_MSG_COUNT
2559                   ,X_MSG_DATA => X_MSG_DATA
2560                   ,X_RETURN_STATUS => X_RETURN_STATUS);
2561 
2562           WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2563 		 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
2564                    P_API_NAME => L_API_NAME
2565                   ,P_PKG_NAME => G_PKG_NAME
2566                   ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
2567                   ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
2568                   ,X_MSG_COUNT => X_MSG_COUNT
2569                   ,X_MSG_DATA => X_MSG_DATA
2570                   ,X_RETURN_STATUS => X_RETURN_STATUS);
2571           WHEN OTHERS THEN
2572 		 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
2573                    P_API_NAME => L_API_NAME
2574                   ,P_PKG_NAME => G_PKG_NAME
2575                   ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
2576                   ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
2577                   ,X_MSG_COUNT => X_MSG_COUNT
2578                   ,X_MSG_DATA => X_MSG_DATA
2579                   ,X_RETURN_STATUS => X_RETURN_STATUS);
2580 
2581 END Create_Party_Site_Use;
2582 
2583 End ASO_PARTY_INT;