[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;