[Home] [Help]
PACKAGE BODY: APPS.WSH_SUPPLIER_PARTY
Source
1 PACKAGE BODY WSH_SUPPLIER_PARTY as
2 /*$Header: WSHSUPRB.pls 120.7.12010000.2 2008/09/18 08:58:17 sankarun ship $ */
3
4
5 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_SUPPLIER_PARTY';
6
7 --Global pl/sql table to store translated message from FND stack.
8 g_error_tbl WSH_ROUTING_REQUEST.tbl_var2000;
9
10 --Line number of Supplier Address Book.
11 g_line_number NUMBER;
12
13 --Constant to be used for calls made to TCA APIs
14 C_CREATED_BY_MODULE CONSTANT VARCHAR2(30) := 'WSH';
15
16 -- Start of comments
17 -- API name : Check_Hz_Location
18 -- Type : Private
19 -- Pre-reqs : None.
20 -- Function : API to check if location exist for given party in TCA. Api does
21 -- 1.For location code and party_id search record in hz_location,
22 -- if found api returns true and else false.
23 -- Parameters :
24 -- IN:
25 -- p_location_code IN Location Code.
26 -- P_party_id IN Party Id.
27 -- P_address1 IN Address1.
28 -- P_address2 IN Address2.
29 -- P_address3 IN Address3.
30 -- P_address4 IN Address4.
31 -- P_city IN City.
32 -- P_postal_code IN Postal Code.
33 -- P_state IN State.
34 -- P_Province IN Province.
35 -- P_county IN County.
36 -- p_country IN Country.
37 -- OUT:
38 -- x_location_id Location Id
39 -- x_party_site_id Party Site Id.
40 -- End of comments
41 FUNCTION Check_Hz_Location(
42 p_location_code IN varchar2,
43 P_party_id IN number,
44 P_address1 IN varchar2,
45 P_address2 IN varchar2,
46 P_address3 IN varchar2,
47 P_address4 IN varchar2,
48 P_city IN varchar2,
49 P_postal_code IN varchar2,
50 P_state IN varchar2,
51 P_Province IN varchar2,
52 P_county IN varchar2,
53 p_country IN varchar2,
54 x_location_id OUT NOCOPY number,
55 x_party_site_id OUT NOCOPY number) RETURN BOOLEAN IS
56
57 l_debug_on BOOLEAN;
58 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Check_Hz_Location';
59
60 --Cursor to find, if location information is exists for location code
61 --of given party.
62 CURSOR Check_location_csr IS
63 SELECT hl.location_id , hps.party_site_id
64 FROM hz_locations hl,
65 hz_party_sites hps
66 WHERE hl.country =p_country
67 AND hl.address1 =p_address1
68 AND hl.address2 = decode(p_address2,NULL,hl.address2,p_address2)
69 AND hl.address3 = decode(p_address3,NULL,hl.address3,p_address3)
70 AND hl.address4 = decode(p_address4,NULL,hl.address4,p_address4)
71 AND hl.city = decode(p_city, NULL, hl.city,p_city)
72 AND hl.postal_code = decode(p_postal_code, NULL, hl.postal_code,p_postal_code)
73 AND hl.state = decode(p_state, NULL, hl.state,p_state)
74 AND hl.Province = decode(p_Province, NULL, hl.Province,p_Province)
75 AND hl.county = decode(p_county, NULL, hl.county,p_county)
76 AND hl.location_id = hps.location_id
77 AND hps.party_id = p_party_id
78 AND hps.party_site_number=p_location_code;
79
80 BEGIN
81 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
82 IF l_debug_on IS NULL THEN
83 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
84 END IF;
85
86 IF l_debug_on THEN
87 WSH_DEBUG_SV.push(l_module_name);
88 WSH_DEBUG_SV.log(l_module_name,'p_location_code',p_location_code);
89 WSH_DEBUG_SV.log(l_module_name,'P_party_id',P_party_id);
90 WSH_DEBUG_SV.log(l_module_name,'P_address1',P_address1);
91 WSH_DEBUG_SV.log(l_module_name,'P_address2',P_address2);
92 WSH_DEBUG_SV.log(l_module_name,'P_address3',P_address3);
93 WSH_DEBUG_SV.log(l_module_name,'P_address4',P_address4);
94 WSH_DEBUG_SV.log(l_module_name,'P_city',P_city);
95 WSH_DEBUG_SV.log(l_module_name,'P_postal_code',P_postal_code);
96 WSH_DEBUG_SV.log(l_module_name,'P_state',P_state);
97 WSH_DEBUG_SV.log(l_module_name,'P_Province',P_Province);
98 WSH_DEBUG_SV.log(l_module_name,'P_county',P_county);
99 WSH_DEBUG_SV.log(l_module_name,'p_country',p_country);
100 END IF;
101
102 --Cursor to check for existing location information.
103 OPEN Check_location_csr;
104 FETCH Check_location_csr INTO x_location_id,x_party_site_id;
105
106 IF (Check_location_csr%NOTFOUND ) THEN
107 CLOSE Check_location_csr;
108 IF l_debug_on THEN
109 WSH_DEBUG_SV.pop(l_module_name,'RETURN false');
110 END IF;
111 RETURN false;
112 END IF;
113
114 CLOSE Check_location_csr;
115
116
117 IF l_debug_on THEN
118 WSH_DEBUG_SV.pop(l_module_name,'RETURN true');
119 END IF;
120 RETURN true;
121
122 EXCEPTION
123 WHEN OTHERS THEN
124 IF l_debug_on THEN
125 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||
126 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
127 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
128 END IF;
129
130 FND_MESSAGE.SET_NAME('WSH','WSH_UTIL_MESSAGE_U');
131 FND_MESSAGE.SET_TOKEN('MSG_TEXT',sqlerrm);
132 fnd_msg_pub.add;
133 RETURN false;
134
135 END Check_Hz_Location;
136
137
138 -- Start of comments
139 -- API name : Create_Hz_Party_site
140 -- Type : Private
141 -- Pre-reqs : None.
142 -- Procedure : API to create Hz Party Site for Party and Location. Api does
143 -- 1.Check for mandatory field for creating Party sites.
144 -- 2.Calls api HZ_PARTY_SITE_V2PUB.Create_Party_Site for creating Party sites.
145 -- Parameters :
146 -- IN:
147 -- P_party_id IN Party Id.
148 -- P_location_id IN Location id.
149 -- P_location_code IN Location Code.
150 -- OUT:
151 -- x_party_site_id OUT NOCOPY Party Site Id.
152 -- x_return_status OUT NOCOPY Standard to output api status.
153 -- End of comments
154 PROCEDURE Create_HZ_Party_Site(
155 P_party_id IN NUMBER,
156 P_location_id IN NUMBER,
157 P_location_code IN VARCHAR2,
158 x_party_site_id OUT NOCOPY NUMBER,
159 x_return_status OUT NOCOPY VARCHAR2) IS
160
161 l_debug_on BOOLEAN;
162 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Create_HZ_Party_Site';
163
164 l_return_status varchar2(1);
165 l_msg_count NUMBER;
166 l_msg_data varchar2(2000);
167 l_party_site_number varchar2(30);
168 l_site_rec HZ_PARTY_SITE_V2PUB.PARTY_SITE_REC_TYPE;
169 l_num_warnings number;
170 l_num_errors number;
171
172 -- Bug 7391414 Setting the profile option 'HZ GENERATE PARTY NUMBER' to Yes if it is No or Null
173 l_hz_profile_option varchar2(2);
174 l_hz_profile_set boolean;
175
176 BEGIN
177 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
178 IF l_debug_on IS NULL THEN
179 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
180 END IF;
181
182 IF l_debug_on THEN
183 WSH_DEBUG_SV.push(l_module_name);
184 WSH_DEBUG_SV.log(l_module_name,'P_party_id',P_party_id);
185 WSH_DEBUG_SV.log(l_module_name,'P_location_id',P_location_id);
186 WSH_DEBUG_SV.log(l_module_name,'P_location_code',P_location_code);
187 END IF;
188
189 IF (P_party_id IS NULL) THEN
190 FND_MESSAGE.SET_NAME('WSH','WSH_REQUIRED_FIELD_NULL');
191 FND_MESSAGE.SET_TOKEN('FIELD_NAME','P_party_id');
192 wsh_util_core.add_message(x_return_status,l_module_name);
193 raise fnd_api.g_exc_error;
194 END IF;
195
196 IF (P_location_id IS NULL) THEN
197 FND_MESSAGE.SET_NAME('WSH','WSH_REQUIRED_FIELD_NULL');
198 FND_MESSAGE.SET_TOKEN('FIELD_NAME','P_location_id');
199 wsh_util_core.add_message(x_return_status,l_module_name);
200 raise fnd_api.g_exc_error;
201 END IF;
202
203 IF (P_location_code IS NULL ) THEN
204 FND_MESSAGE.SET_NAME('WSH','WSH_REQUIRED_FIELD_NULL');
205 FND_MESSAGE.SET_TOKEN('FIELD_NAME','P_location_code');
206 wsh_util_core.add_message(x_return_status,l_module_name);
207 raise fnd_api.g_exc_error;
208 END IF;
209
210 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
211
212 --Assign the party and location information to input parameter of below HZ api.
213 l_site_rec.party_id := p_party_id;
214 l_site_rec.location_id := p_location_id;
215 l_site_rec.status := 'A'; --Active
216 l_site_rec.party_site_number := p_location_code;
217 l_site_rec.created_by_module := C_CREATED_BY_MODULE;
218
219 -- Bug 7391414 Setting the profile option 'HZ GENERATE PARTY NUMBER' to Yes if it is No or Null
220 l_hz_profile_set := false;
221 l_hz_profile_option := fnd_profile.value('HZ_GENERATE_PARTY_NUMBER');
222
223 IF (l_hz_profile_option = 'Y' or l_hz_profile_option is null ) THEN
224 IF l_debug_on THEN
225 WSH_DEBUG_SV.logmsg(l_module_name,'Setting profile option HZ_GENERATE_PARTY_SITE_NUMBER to No');
226 END IF;
227 --Since location code is used as party site number.
228 fnd_profile.put('HZ_GENERATE_PARTY_SITE_NUMBER','N');
229 l_hz_profile_set := true;
230 END IF;
231
232 HZ_PARTY_SITE_V2PUB.Create_Party_Site (
233 p_init_msg_list => FND_API.g_false,
234 p_party_site_rec => l_site_rec,
235 x_party_site_id => x_party_site_id,
236 x_party_site_number => l_party_site_number,
237 x_return_status => l_return_status,
238 x_msg_count => l_msg_count,
239 x_msg_data => l_msg_data);
240
241 -- Bug 7391414 Setting the profile option 'HZ GENERATE PARTY NUMBER' to previous value
242 IF l_hz_profile_set THEN
243 IF l_debug_on THEN
244 WSH_DEBUG_SV.logmsg(l_module_name,'Reverting the value of profile option HZ_GENERATE_PARTY_SITE_NUMBER');
245 END IF;
246 fnd_profile.put('HZ_GENERATE_PARTY_SITE_NUMBER',l_hz_profile_option);
247 END IF;
248
249 IF l_debug_on THEN
250 WSH_DEBUG_SV.log(l_module_name,'HZ_PARTY_SITE_V2PUB.Create_Party_Site l_return_status',l_return_status);
251 WSH_DEBUG_SV.log(l_module_name,'x_party_site_id',x_party_site_id);
252 WSH_DEBUG_SV.log(l_module_name,'l_party_site_number',l_party_site_number);
253 END IF;
254 wsh_util_core.api_post_call(
255 p_return_status => l_return_status,
256 x_num_warnings => l_num_warnings,
257 x_num_errors => l_num_errors);
258
259 IF l_num_errors > 0 THEN
260 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
261 ELSIF l_num_warnings > 0 THEN
262 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
263 END IF;
264
265 IF l_debug_on THEN
266 WSH_DEBUG_SV.pop(l_module_name);
267 END IF;
268 EXCEPTION
269 WHEN FND_API.G_EXC_ERROR THEN
270 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR ;
271
272 IF l_debug_on THEN
273 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
274 WSH_DEBUG_SV.logmsg(l_module_name,'l_msg_count: '||l_msg_count,WSH_DEBUG_SV.C_EXCEP_LEVEL);
275 WSH_DEBUG_SV.logmsg(l_module_name,'l_msg_data: '||l_msg_data,WSH_DEBUG_SV.C_EXCEP_LEVEL);
276 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
277 END IF;
278
279 WHEN OTHERS THEN
280 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
281
282 IF l_debug_on THEN
283 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||
284 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
285 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
286 END IF;
287
288 FND_MESSAGE.SET_NAME('WSH','WSH_UTIL_MESSAGE_U');
289 FND_MESSAGE.SET_TOKEN('MSG_TEXT',sqlerrm);
290 fnd_msg_pub.add;
291 END Create_HZ_Party_Site;
292
293
294 -- Start of comments
295 -- API name : VENDOR_PARTY_EXISTS
296 -- Type : Public
297 -- Pre-reqs : None.
298 -- Procedure: API to find Party for a Vendor. Based on input vendor_id check
299 -- for existing party in hz_relationships.
300 -- Parameters :
301 -- IN:
302 -- p_vendor_id Vendor Id
303 -- OUT:
304 -- x_party_id Party Id
305 -- RETURN Y/N
306 -- End of comments
307 FUNCTION VENDOR_PARTY_EXISTS(
308 p_vendor_id IN NUMBER,
309 x_party_id OUT NOCOPY NUMBER) RETURN VARCHAR2 -- Y for Yes, N for No
310 IS
311 CURSOR get_vendor_name IS
312 SELECT pv.vendor_name
313 FROM po_vendors pv
314 WHERE pv.vendor_id = p_vendor_id;
315
316 l_vendor_name varchar2(400);
317
318 l_debug_on BOOLEAN;
319 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'VENDOR_PARTY_EXISTS';
320 BEGIN
321 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
322 IF l_debug_on IS NULL THEN
323 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
324 END IF;
325
326 IF l_debug_on THEN
327 WSH_DEBUG_SV.push(l_module_name);
328 WSH_DEBUG_SV.log(l_module_name,'p_vendor_id',p_vendor_id);
329 END IF;
330
331 --IB-Phase-2 {
332
333 SELECT PARTY_ID INTO x_party_id
334 FROM PO_VENDORS
335 WHERE VENDOR_ID = p_vendor_id;
336
337 --
338
339 IF x_party_id IS NULL
340 THEN
341 OPEN get_vendor_name;
342 FETCH get_vendor_name INTO l_vendor_name;
343 CLOSE get_vendor_name;
344
345 FND_MESSAGE.SET_NAME('WSH','WSH_SUPPLIER_NO_PARTY');
346 FND_MESSAGE.SET_TOKEN('SUPPLIER_NAME',l_vendor_name);
347 fnd_msg_pub.add;
348 IF l_debug_on THEN
349 WSH_DEBUG_SV.pop(l_module_name);
350 END IF;
351 RETURN 'N';
352 END IF;
353 --}IB-Phase-2
354
355 IF l_debug_on THEN
356 WSH_DEBUG_SV.pop(l_module_name);
357 END IF;
358 RETURN 'Y';
359
360 EXCEPTION
361 WHEN NO_DATA_FOUND THEN
362 -- { IB-Phase-2
363 FND_MESSAGE.SET_NAME('WSH','WSH_SUPPLIER_NO_PARTY');
364 fnd_msg_pub.add;
365 -- } IB-Phase-2
366 IF l_debug_on THEN
367 WSH_DEBUG_SV.logmsg(l_module_name,'NO_DATA_FOUND exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
368 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
369 END IF;
370
371 RETURN 'N';
372
373 WHEN OTHERS THEN
374 IF l_debug_on THEN
375 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||
376 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
377 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
378 END IF;
379
380 FND_MESSAGE.SET_NAME('WSH','WSH_UTIL_MESSAGE_U');
381 FND_MESSAGE.SET_TOKEN('MSG_TEXT',sqlerrm);
382 fnd_msg_pub.add;
383 RETURN 'N';
384
385 END vendor_party_exists;
386
387
388 -- Start of comments
389 -- THIS API WILL NOT BE USED BY R12 CODE, SINCE PARTY CREATION WILL NOW NOT BE INITIATED BY WSH.
390 -- API name : create_vendor_party
391 -- Type : Public
392 -- Pre-reqs : None.
393 -- Procedure : API to create Creates a TCA party of type Organization from a PO_VENDOR,if the party doesn't already exist.
394 --
395 -- Parameters :
396 -- IN:
397 -- Vendor_id IN PK for the Vendor from which the party is being created.
398 -- p_file_fields IN Hold Supplier Address book record as passed by UI
399 -- OUT:
400 -- Return_status: Indicates outcome of function:
401 -- S: Successful, party was created and committed
402 -- E: Some validation failed, the party was not created
403 -- End of comments
404 FUNCTION create_vendor_party(
405 p_vendor_id IN NUMBER,
406 -- x_party_id OUT NUMBER,
407 x_return_status OUT NOCOPY VARCHAR2) RETURN NUMBER
408 IS
409
410 CURSOR get_vendor_name IS
411 SELECT pv.vendor_name
412 FROM po_vendors pv
413 WHERE pv.vendor_id = p_vendor_id;
414
415 --Cursor to check if code assignment is already exist for party.
416 CURSOR get_code_assignments(p_party_id NUMBER) IS
417 SELECT 'X'
418 FROM hz_code_assignments
419 WHERE owner_table_name = 'HZ_PARTIES'
420 AND owner_table_id = p_party_id
421 AND class_category = 'POS_CLASSIFICATION'
422 AND class_code = 'PROCUREMENT_ORGANIZATION'
423 AND status = 'A'
424 AND (end_date_active IS NULL OR end_date_active > SYSDATE);
425
426 l_vendor_name VARCHAR2(255);
427
428 l_party_rel_rec HZ_RELATIONSHIP_V2PUB.relationship_rec_type;
429 l_org_rec hz_party_v2pub.organization_rec_type;
430 l_ocon_rec HZ_PARTY_CONTACT_V2PUB.ORG_CONTACT_REC_TYPE;
431 l_code_assignment_rec hz_classification_v2pub.code_assignment_rec_type;
432 l_msg_count NUMBER;
433 l_msg_data VARCHAR2(3000);
434 exception_message VARCHAR2(3000);
435 l_code_assignment_id NUMBER;
436
437 l_status_class VARCHAR2(10);
438 l_return_status VARCHAR2(10);
439 l_party_number NUMBER;
440 l_profile_id NUMBER;
441 l_tmp varchar2(1);
442 l_party_relationship_id number;
443 l_contact_point_id number;
444 l_org_contact_id number;
445 l_party_id number;
446 l_rel_party_id number;
447
448
449 l_debug_on BOOLEAN;
450 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'create_vendor_party';
451 l_num_warnings number;
452 l_num_errors number;
453 BEGIN
454 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
455 IF l_debug_on IS NULL THEN
456 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
457 END IF;
458
459 IF l_debug_on THEN
460 WSH_DEBUG_SV.push(l_module_name);
461 WSH_DEBUG_SV.log(l_module_name,'p_vendor_id',p_vendor_id);
462 END IF;
463
464 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
465
466 IF (VENDOR_PARTY_EXISTS(p_vendor_id,l_party_id) <> 'Y' ) THEN --{
467
468 --Create only, if Party does not already exists.
469
470 --Validate vendor
471 OPEN get_vendor_name;
472 FETCH get_vendor_name INTO l_vendor_name;
473
474 IF (get_vendor_name%NOTFOUND ) THEN
475 CLOSE get_vendor_name;
476 raise FND_API.G_EXC_ERROR;
477 END IF;
478 CLOSE get_vendor_name;
479
480 l_org_rec.organization_name := l_vendor_name;
481 l_org_rec.created_by_module := C_CREATED_BY_MODULE;
482 l_org_rec.party_rec.status := 'A';
483
484 IF l_debug_on THEN
485 WSH_DEBUG_SV.logmsg(l_module_name,'Calling HZ_PARTY_V2PUB.Create_Organization');
486 WSH_DEBUG_SV.log(l_module_name,'l_org_rec.organization_name',l_org_rec.organization_name);
487 WSH_DEBUG_SV.log(l_module_name,'l_org_rec.created_by_module',l_org_rec.created_by_module);
488 WSH_DEBUG_SV.log(l_module_name,'l_org_rec.party_rec.status',l_org_rec.party_rec.status);
489 END IF;
490
491 --Party is created as Organization in TCA.
492 HZ_PARTY_V2PUB.Create_Organization
493 (
494 p_init_msg_list => FND_API.g_false,
495 p_organization_rec => l_org_rec,
496 x_return_status => l_return_status,
497 x_msg_count => l_msg_count,
498 x_msg_data => l_msg_data,
499 x_party_id => l_party_id,
500 x_party_number => l_party_number,
501 x_profile_id => l_profile_id
502 );
503
504 IF l_debug_on THEN
505 WSH_DEBUG_SV.log(l_module_name,'After HZ_PARTY_V2PUB.Create_Organization l_return_status',l_return_status);
506 WSH_DEBUG_SV.log(l_module_name,'x_party_id',l_party_id);
507 WSH_DEBUG_SV.log(l_module_name,'l_party_number',l_party_number);
508 WSH_DEBUG_SV.log(l_module_name,'l_profile_id',l_profile_id);
509 END IF;
510
511 wsh_util_core.api_post_call(
512 p_return_status => l_return_status,
513 x_num_warnings => l_num_warnings,
514 x_num_errors => l_num_errors);
515
516
517 --Need to create relationship between Vendor and Party.
518 --Relationship type define as 'POS_VENDOR_PARTY', Subject as Party and
519 --Object as Vendor, since relationship code used is 'PARTY_OF_VENDOR'
520 l_party_rel_rec.subject_id := l_party_id;
521 l_party_rel_rec.subject_table_name := 'HZ_PARTIES';
522 l_party_rel_rec.subject_type := 'ORGANIZATION';
523
524 l_party_rel_rec.object_id := p_vendor_id;
525 l_party_rel_rec.object_table_name := 'PO_VENDORS';
526 l_party_rel_rec.object_type := 'POS_VENDOR';
527
528 l_party_rel_rec.relationship_code := 'PARTY_OF_VENDOR';
529 l_party_rel_rec.relationship_type := 'POS_VENDOR_PARTY';
530
531 l_party_rel_rec.status := 'A';
532 l_party_rel_rec.start_date := sysdate;
533 l_party_rel_rec.created_by_module := C_CREATED_BY_MODULE;
534
535
536 IF l_debug_on THEN
537 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit HZ_PARTY_CONTACT_V2PUB.create_org_contact');
538 END IF;
539
540 HZ_RELATIONSHIP_V2PUB.create_relationship(
541 --p_init_msg_list => FND_API.g_false,
542 p_relationship_rec => l_party_rel_rec,
543 x_relationship_id => l_party_relationship_id,
544 x_party_id => l_rel_party_id,
545 x_party_number => l_party_number,
546 x_return_status => l_return_status,
547 x_msg_count => l_msg_count,
548 x_msg_data => l_msg_data );
549
550 IF l_debug_on THEN
551 WSH_DEBUG_SV.log(l_module_name,'After create_org_contact l_return_status',l_return_status);
552 WSH_DEBUG_SV.log(l_module_name,'l_party_relationship_id',l_party_relationship_id);
553 WSH_DEBUG_SV.log(l_module_name,'l_party_id',l_rel_party_id);
554 WSH_DEBUG_SV.log(l_module_name,'l_party_number',l_party_number);
555 END IF;
556
557 wsh_util_core.api_post_call(
558 p_return_status => l_return_status,
559 x_num_warnings => l_num_warnings,
560 x_num_errors => l_num_errors);
561
562
563 --Check if code assignment is already exist for party.
564 OPEN get_code_assignments(l_party_id);
565 FETCH get_code_assignments INTO l_tmp;
566 CLOSE get_code_assignments;
567
568 IF (l_tmp IS NULL ) THEN
569 --No code assignment exists, create for party.
570 --These input values are standard for Vendor and Party as define
571 --by PO receiving team.
572 l_code_assignment_rec.owner_table_name := 'HZ_PARTIES';
573 l_code_assignment_rec.owner_table_id := l_party_id;
574 l_code_assignment_rec.class_category := 'POS_CLASSIFICATION';
575 l_code_assignment_rec.class_code := 'PROCUREMENT_ORGANIZATION';
576 l_code_assignment_rec.primary_flag := 'Y';
577 l_code_assignment_rec.content_source_type := 'USER_ENTERED';
578 l_code_assignment_rec.start_date_active := SYSDATE;
579 l_code_assignment_rec.status := 'A';
580 l_code_assignment_rec.created_by_module := C_CREATED_BY_MODULE;
581 l_code_assignment_rec.application_id := 177;
582
583 IF l_debug_on THEN
584 WSH_DEBUG_SV.logmsg(l_module_name,'Calling HZ_CLASSIFICATION_V2PUB.create_code_assignment');
585 END IF;
586
587 HZ_CLASSIFICATION_V2PUB.create_code_assignment(
588 FND_API.G_FALSE,
589 l_code_assignment_rec,
590 l_return_status,
591 l_msg_count,
592 exception_message,
593 l_code_assignment_id);
594
595 IF l_debug_on THEN
596 WSH_DEBUG_SV.log(l_module_name,'After create_code_assignment l_return_status',l_return_status);
597 WSH_DEBUG_SV.log(l_module_name,'l_msg_count',l_msg_count);
598 WSH_DEBUG_SV.log(l_module_name,'exception_message',exception_message);
599 WSH_DEBUG_SV.log(l_module_name,'l_code_assignment_id',l_code_assignment_id);
600 END IF;
601
602 wsh_util_core.api_post_call(
603 p_return_status => l_return_status,
604 x_num_warnings => l_num_warnings,
605 x_num_errors => l_num_errors);
606 END IF;
607 END IF; --}
608
609 IF l_num_errors > 0 THEN
610 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
611 ELSIF l_num_warnings > 0 THEN
612 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
613 END IF;
614
615 IF l_debug_on THEN
616 WSH_DEBUG_SV.pop(l_module_name);
617 END IF;
618
619 RETURN l_party_id;
620
621 EXCEPTION
622 WHEN FND_API.G_EXC_ERROR THEN
623 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR ;
624
625 IF l_debug_on THEN
626 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
627 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
628 END IF;
629
630 RETURN null;
631
632 WHEN OTHERS THEN
633 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
634
635 IF l_debug_on THEN
636 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||
637 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
638 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
639 END IF;
640
641 FND_MESSAGE.SET_NAME('WSH','WSH_UTIL_MESSAGE_U');
642 FND_MESSAGE.SET_TOKEN('MSG_TEXT',sqlerrm);
643 fnd_msg_pub.add;
644
645 RETURN null;
646
647 END create_vendor_party;
648
649
650
651 -- Start of comments
652 -- API name : Update_Hz_Location
653 -- Type : Private
654 -- Pre-reqs : None.
655 -- Procedure: API to Update Hz location. Api first get the object_version_number
656 -- from hz_locations and this is passed to
657 -- HZ_LOCATION_V2PUB.Update_Location api along with input parameter
658 -- for update of location.
659 -- Parameters :
660 -- IN:
661 -- P_party_id IN Party Id.
662 -- P_address1 IN Address1.
663 -- P_address2 IN Address2.
664 -- P_address3 IN Address3.
665 -- P_address4 IN Address4.
666 -- P_city IN City.
667 -- P_postal_code IN Postal Code.
668 -- P_state IN State.
669 -- P_Province IN Province.
670 -- P_county IN County.
671 -- p_country IN Country.
672 -- OUT:
673 -- x_return_status OUT NOCOPY Standard to output api status.
674 -- End of comments
675 PROCEDURE Update_Hz_Location(
676 P_location_id IN number,
677 P_address1 IN varchar2,
678 P_address2 IN varchar2,
679 P_address3 IN varchar2,
680 P_address4 IN varchar2,
681 P_city IN varchar2,
682 P_postal_code IN varchar2,
683 P_state IN varchar2,
684 P_Province IN varchar2,
685 P_county IN varchar2,
686 p_country IN varchar2,
687 x_return_status OUT NOCOPY varchar2) IS
688 l_debug_on BOOLEAN;
689 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Update_Hz_Location';
690
691 --Get the object_version_number for the location and this again
692 --passed to HZ api for update.
693 CURSOR Get_Location_Object_Number(p_location_id NUMBER) IS
694 select object_version_number
695 from hz_locations
696 where location_id = p_location_id;
697
698 l_location_object_NUMBER NUMBER;
699
700 l_return_status varchar2(1);
701 l_msg_count NUMBER;
702 l_msg_data varchar2(2000);
703 l_loc_rec HZ_LOCATION_V2PUB.LOCATION_REC_TYPE;
704 l_num_warnings number;
705 l_num_errors number;
706
707 BEGIN
708 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
709 IF l_debug_on IS NULL THEN
710 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
711 END IF;
712
713 IF l_debug_on THEN
714 WSH_DEBUG_SV.push(l_module_name);
715 WSH_DEBUG_SV.log(l_module_name,'P_location_id',P_location_id);
716 WSH_DEBUG_SV.log(l_module_name,'P_address1',P_address1);
717 WSH_DEBUG_SV.log(l_module_name,'P_address2',P_address2);
718 WSH_DEBUG_SV.log(l_module_name,'P_address3',P_address3);
719 WSH_DEBUG_SV.log(l_module_name,'P_address4',P_address4);
720 WSH_DEBUG_SV.log(l_module_name,'P_city',P_city);
721 WSH_DEBUG_SV.log(l_module_name,'P_postal_code',P_postal_code);
722 WSH_DEBUG_SV.log(l_module_name,'P_state',P_state);
723 WSH_DEBUG_SV.log(l_module_name,'P_Province',P_Province);
724 WSH_DEBUG_SV.log(l_module_name,'P_county',P_county);
725 WSH_DEBUG_SV.log(l_module_name,'p_country',p_country);
726 END IF;
727
728 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
729
730 l_loc_rec.location_id := p_location_id;
731 l_loc_rec.address1 := p_address1;
732 l_loc_rec.address2 := p_address2;
733 l_loc_rec.address3 := p_address3;
734 l_loc_rec.address4 := p_address4;
735 l_loc_rec.city := p_city;
736 l_loc_rec.state := p_state;
737 l_loc_rec.postal_code := p_postal_code;
738 l_loc_rec.province := p_province;
739 l_loc_rec.country := p_country;
740 l_loc_rec.county := p_county;
741
742
743 --Get the object_version_number for the location and this again
744 --passed to HZ api for update.
745 OPEN Get_Location_Object_Number(p_location_id);
746 FETCH Get_Location_Object_Number INTO l_location_object_number;
747 CLOSE Get_Location_Object_Number;
748
749 HZ_LOCATION_V2PUB.Update_Location
750 (
751 p_init_msg_list => FND_API.G_FALSE,
752 p_location_rec => l_loc_rec,
753 p_object_version_number => l_location_object_number,
754 x_return_status => l_return_status,
755 x_msg_count => l_msg_count,
756 x_msg_data => l_msg_data
757 );
758
759 IF l_debug_on THEN
760 WSH_DEBUG_SV.log(l_module_name,'HZ_LOCATION_V2PUB.Update_Location l_return_status',l_return_status);
761 END IF;
762
763 wsh_util_core.api_post_call(
764 p_return_status => l_return_status,
765 x_num_warnings => l_num_warnings,
766 x_num_errors => l_num_errors);
767
768 IF l_num_errors > 0 THEN
769 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
770 ELSIF l_num_warnings > 0 THEN
771 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
772 END IF;
773
774 IF l_debug_on THEN
775 WSH_DEBUG_SV.pop(l_module_name);
776 END IF;
777 EXCEPTION
778 WHEN FND_API.G_EXC_ERROR THEN
779 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR ;
780
781 IF l_debug_on THEN
782 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
783 WSH_DEBUG_SV.logmsg(l_module_name,'l_msg_count: '||l_msg_count,WSH_DEBUG_SV.C_EXCEP_LEVEL);
784 WSH_DEBUG_SV.logmsg(l_module_name,'l_msg_data: '||l_msg_data,WSH_DEBUG_SV.C_EXCEP_LEVEL);
785 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
786 END IF;
787
788 WHEN OTHERS THEN
789 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
790
791 IF l_debug_on THEN
792 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||
793 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
794 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
795 END IF;
796
797 FND_MESSAGE.SET_NAME('WSH','WSH_UTIL_MESSAGE_U');
798 FND_MESSAGE.SET_TOKEN('MSG_TEXT',sqlerrm);
799 fnd_msg_pub.add;
800 END Update_Hz_Location;
801
802
803 -- Start of comments
804 -- API name : Create_Hz_Location_Party_Site
805 -- Type : Private
806 -- Pre-reqs : None.
807 -- Procedure: API to create Hz location and party site for address information and party. Api does
808 -- 1.Check for mandatory parameter for creating party site and location.
809 -- 2.Checks if location exist for party, if not then it creates location
810 -- and party site, otherwise return the location id and party site id.
811 -- Parameters :
812 -- IN:
813 -- P_party_id IN Party Id.
814 -- p_location_code IN Location Code.
815 -- P_address1 IN Address1.
816 -- P_address2 IN Address2.
817 -- P_address3 IN Address3.
818 -- P_address4 IN Address4.
819 -- P_city IN City.
820 -- P_postal_code IN Postal Code.
821 -- P_state IN State.
822 -- P_Province IN Province.
823 -- P_county IN County.
824 -- p_country IN Country.
825 -- OUT:
826 -- x_location_id OUT NOCOPY Location Id created.
827 -- x_party_site_id OUT NOCOPY Party Site Id created.
828 -- x_return_status OUT NOCOPY Standard to output api status.
829 -- End of comments
830 PROCEDURE Create_Hz_Location_Party_Site(
831 P_party_id IN number,
832 P_location_code IN varchar2,
833 P_address1 IN varchar2,
834 P_address2 IN varchar2,
835 P_address3 IN varchar2,
836 P_address4 IN varchar2,
837 P_city IN varchar2,
838 P_postal_code IN varchar2,
839 P_state IN varchar2,
840 P_Province IN varchar2,
841 P_county IN varchar2,
842 p_country IN varchar2,
843 x_location_id OUT NOCOPY number,
844 x_party_site_id OUT NOCOPY number,
845 x_return_status OUT NOCOPY varchar2) IS
846 l_debug_on BOOLEAN;
847 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Create_Hz_Location_Party_Site';
848
849 l_return_status varchar2(1);
850 l_msg_count NUMBER;
851 l_msg_data varchar2(2000);
852 l_loc_rec HZ_LOCATION_V2PUB.LOCATION_REC_TYPE;
853 l_num_warnings number;
854 l_num_errors number;
855 BEGIN
856 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
857 IF l_debug_on IS NULL THEN
858 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
859 END IF;
860
861 IF l_debug_on THEN
862 WSH_DEBUG_SV.push(l_module_name);
863 WSH_DEBUG_SV.log(l_module_name,'P_party_id',P_party_id);
864 WSH_DEBUG_SV.log(l_module_name,'p_location_code',p_location_code);
865 WSH_DEBUG_SV.log(l_module_name,'P_address1',P_address1);
866 WSH_DEBUG_SV.log(l_module_name,'P_address2',P_address2);
867 WSH_DEBUG_SV.log(l_module_name,'P_address3',P_address3);
868 WSH_DEBUG_SV.log(l_module_name,'P_address4',P_address4);
869 WSH_DEBUG_SV.log(l_module_name,'P_city',P_city);
870 WSH_DEBUG_SV.log(l_module_name,'P_postal_code',P_postal_code);
871 WSH_DEBUG_SV.log(l_module_name,'P_state',P_state);
872 WSH_DEBUG_SV.log(l_module_name,'P_Province',P_Province);
873 WSH_DEBUG_SV.log(l_module_name,'P_county',P_county);
874 WSH_DEBUG_SV.log(l_module_name,'p_country',p_country);
875 END IF;
876
877 IF (P_party_id IS NULL) THEN
878 FND_MESSAGE.SET_NAME('WSH','WSH_REQUIRED_FIELD_NULL');
879 FND_MESSAGE.SET_TOKEN('FIELD_NAME','P_party_id');
880 wsh_util_core.add_message(x_return_status,l_module_name);
881 raise fnd_api.g_exc_error;
882 END IF;
883
884 IF (P_address1 IS NULL) THEN
885 FND_MESSAGE.SET_NAME('WSH','WSH_REQUIRED_FIELD_NULL');
886 FND_MESSAGE.SET_TOKEN('FIELD_NAME','P_address1');
887 wsh_util_core.add_message(x_return_status,l_module_name);
888 raise fnd_api.g_exc_error;
889 END IF;
890
891 IF (p_country IS NULL ) THEN
892 FND_MESSAGE.SET_NAME('WSH','WSH_REQUIRED_FIELD_NULL');
893 FND_MESSAGE.SET_TOKEN('FIELD_NAME','p_country');
894 wsh_util_core.add_message(x_return_status,l_module_name);
895 raise fnd_api.g_exc_error;
896 END IF;
897
898 IF (p_location_code IS NULL ) THEN
899 FND_MESSAGE.SET_NAME('WSH','WSH_REQUIRED_FIELD_NULL');
900 FND_MESSAGE.SET_TOKEN('FIELD_NAME','p_location_code');
901 wsh_util_core.add_message(x_return_status,l_module_name);
902 raise fnd_api.g_exc_error;
903 END IF;
904
905 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
906
907 --Check if location is already created for party.
908 IF ( NOT check_HZ_location(
909 p_location_code => p_location_code,
910 p_party_id => p_party_id,
911 P_address1 => p_address1,
912 P_address2 => p_address2,
913 P_address3 => p_address3,
914 P_address4 => p_address4,
915 P_city => p_city,
916 P_postal_code => p_postal_code,
917 P_state => p_state,
918 P_province => p_province,
919 P_county => p_county,
920 p_country => p_country,
921 x_location_id => x_location_id,
922 x_party_site_id => x_party_site_id)
923 ) THEN
924
925 --No existing location found for party, create new one.
926 l_loc_rec.address1 := p_address1;
927 l_loc_rec.address2 := p_address2;
928 l_loc_rec.address3 := p_address3;
929 l_loc_rec.address4 := p_address4;
930 l_loc_rec.city := p_city;
931 l_loc_rec.state := p_state;
932 l_loc_rec.postal_code := p_postal_code;
933 l_loc_rec.province := p_province;
934 l_loc_rec.country := p_country;
935 l_loc_rec.county := p_county;
936 l_loc_rec.created_by_module := C_CREATED_BY_MODULE;
937
938 HZ_LOCATION_V2PUB.Create_Location (
939 p_init_msg_list => FND_API.G_FALSE,
940 p_location_rec => l_loc_rec,
941 x_location_id => x_location_id,
942 x_return_status => l_return_status,
943 x_msg_count => l_msg_count,
944 x_msg_data => l_msg_data);
945
946 IF l_debug_on THEN
947 WSH_DEBUG_SV.log(l_module_name,'HZ_LOCATION_V2PUB.Create_Location l_return_status',l_return_status);
948 WSH_DEBUG_SV.log(l_module_name,'x_location_id',x_location_id);
949 END IF;
950
951 wsh_util_core.api_post_call(
952 p_return_status => l_return_status,
953 x_num_warnings => l_num_warnings,
954 x_num_errors => l_num_errors);
955
956 --Once location is created, need to make relationship between location and party.
957 Create_HZ_Party_Site(
958 P_party_id => P_party_id,
959 P_location_id => x_location_id,
960 P_location_code => P_location_code,
961 x_party_site_id => x_party_site_id,
962 x_return_status => l_return_status);
963
964 IF l_debug_on THEN
965 WSH_DEBUG_SV.log(l_module_name,'Create_HZ_Party_Site l_return_status',l_return_status);
966 WSH_DEBUG_SV.log(l_module_name,'x_party_site_id',x_party_site_id);
967 END IF;
968 wsh_util_core.api_post_call(
969 p_return_status => l_return_status,
970 x_num_warnings => l_num_warnings,
971 x_num_errors => l_num_errors);
972 END IF;
973
974 IF l_num_errors > 0 THEN
975 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
976 ELSIF l_num_warnings > 0 THEN
977 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
978 END IF;
979
980 IF l_debug_on THEN
981 WSH_DEBUG_SV.pop(l_module_name);
982 END IF;
983 EXCEPTION
984 WHEN FND_API.G_EXC_ERROR THEN
985 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR ;
986
987 IF l_debug_on THEN
988 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
989 WSH_DEBUG_SV.logmsg(l_module_name,'l_msg_count: '||l_msg_count,WSH_DEBUG_SV.C_EXCEP_LEVEL);
990 WSH_DEBUG_SV.logmsg(l_module_name,'l_msg_data: '||l_msg_data,WSH_DEBUG_SV.C_EXCEP_LEVEL);
991 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
992 END IF;
993
994 WHEN OTHERS THEN
995 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
996
997 IF l_debug_on THEN
998 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||
999 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1000 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1001 END IF;
1002
1003 FND_MESSAGE.SET_NAME('WSH','WSH_UTIL_MESSAGE_U');
1004 FND_MESSAGE.SET_TOKEN('MSG_TEXT',sqlerrm);
1005 fnd_msg_pub.add;
1006 END Create_Hz_Location_Party_Site;
1007
1008
1009 -- Start of comments
1010 -- API name : Is_Valid_ISP_User
1011 -- Type : Private
1012 -- Pre-reqs : None.
1013 -- Function : API to validate ISP User for input Supplier.
1014 --
1015 -- Parameters :
1016 -- IN:
1017 -- p_user_id IN ISP User Id.
1018 -- p_supplier_name IN Supplier Name.
1019 -- OUT:
1020 -- Returns Ture/False.
1021 -- End of comments
1022 FUNCTION Is_Valid_ISP_User(p_user_id IN NUMBER,
1023 p_supplier_name IN VARCHAR2) RETURN boolean
1024 IS
1025
1026 /*
1027 *
1028 * R12 Bug 4911516 : Please see the new version of the cursor below
1029 *
1030 --Cursor to validate ISP user.The value for relationship type, subject and objects
1031 --is same as when user is created.
1032 CURSOR get_pos_user IS
1033 SELECT 1
1034 FROM hz_relationships h2,
1035 hz_parties hp,
1036 fnd_user fu
1037 WHERE h2.subject_type = 'ORGANIZATION'
1038 and h2.object_type = 'PERSON'
1039 and h2.relationship_type = 'POS_EMPLOYMENT'
1040 and h2.relationship_code = 'EMPLOYER_OF'
1041 and h2.subject_table_name = 'HZ_PARTIES'
1042 and h2.object_table_name = 'HZ_PARTIES'
1043 and h2.status = 'A'
1044 and h2.start_date <= sysdate
1045 and h2.end_date >= sysdate
1046 and h2.object_id = fu.person_party_id -- IB-PHASE-2 Vendor merge
1047 and h2.subject_id = hp.party_id
1048 and hp.party_name = p_supplier_name
1049 and fu.user_id = p_user_id
1050 and h2.subject_id IN
1051 (select owner_table_id
1052 from hz_code_assignments
1053 where owner_table_name='HZ_PARTIES'
1054 and status = 'A'
1055 and class_category ='POS_PARTICIPANT_TYPE'
1056 and class_code='VENDOR');
1057 */
1058
1059 --
1060 -- R12 Bug 4911516
1061 --
1062 CURSOR get_pos_user IS
1063 SELECT 1
1064 FROM pos_supplier_users_v pos, hz_parties hz
1065 WHERE pos.user_id = p_user_id
1066 AND pos.vendor_party_id = hz.party_id
1067 AND hz.party_name = p_supplier_name;
1068 --
1069 l_debug_on BOOLEAN;
1070 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Is_Valid_ISP_User';
1071
1072 l_status boolean:=true;
1073 l_tmp number;
1074
1075 BEGIN
1076 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1077 IF l_debug_on IS NULL THEN
1078 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1079 END IF;
1080
1081 IF l_debug_on THEN
1082 WSH_DEBUG_SV.push(l_module_name);
1083 WSH_DEBUG_SV.log(l_module_name,'p_user_id',p_user_id);
1084 WSH_DEBUG_SV.log(l_module_name, 'p_supplier_name', p_supplier_name);
1085 END IF;
1086 --
1087 --Validate the ISP user.
1088 OPEN get_pos_user;
1089 FETCH get_pos_user INTO l_tmp;
1090 IF (get_pos_user%NOTFOUND) THEN
1091 l_status:= false;
1092 END IF;
1093 CLOSE get_pos_user;
1094
1095 IF l_debug_on THEN
1096 WSH_DEBUG_SV.pop(l_module_name);
1097 END IF;
1098
1099 RETURN l_status;
1100
1101 EXCEPTION
1102 WHEN OTHERS THEN
1103 IF l_debug_on THEN
1104 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||
1105 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1106 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1107 END IF;
1108 FND_MESSAGE.SET_NAME('WSH','WSH_UTIL_MESSAGE_U');
1109 FND_MESSAGE.SET_TOKEN('MSG_TEXT',sqlerrm);
1110 fnd_msg_pub.add;
1111 raise ;
1112 END Is_Valid_ISP_User;
1113
1114
1115 -- Start of comments
1116 -- API name : Validate_Supplier
1117 -- Type : Public
1118 -- Pre-reqs : None.
1119 -- Procedure: API to Create/Validate Supplier. Api does
1120 -- 1. Check for mandatory parameters.
1121 -- 2. Validate the ISP user.
1122 -- 3. Validate Vendor.
1123 -- 4. Check for Party exist for vendor, if not create one.
1124 -- Parameters :
1125 -- IN:
1126 -- p_in_param IN Type WSH_ROUTING_REQUEST.In_param_Rec_Type,use p_in_param.caller to get the caller.
1127 -- P_supplier_name IN Supplier Name.
1128 -- OUT:
1129 -- x_vendor_id vendor id.
1130 -- x_party_id Party Id.
1131 -- x_return_status Standard to output api status.
1132 -- End of comments
1133 PROCEDURE Validate_Supplier(
1134 p_in_param IN WSH_ROUTING_REQUEST.In_param_Rec_Type,
1135 P_supplier_name IN varchar2,
1136 x_vendor_id OUT NOCOPY number,
1137 x_party_id OUT NOCOPY number,
1138 x_return_status OUT NOCOPY varchar2) IS
1139 l_debug_on BOOLEAN;
1140 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Validate_Supplier';
1141 l_return_status varchar2(1);
1142
1143
1144 CURSOR validate_vendor_csr(p_vendor_name varchar2) IS
1145 SELECT vendor_id
1146 FROM po_vendors
1147 WHERE vendor_name =ltrim(rtrim(p_vendor_name))
1148 AND (end_date_active IS NULL OR end_date_active >= SYSDATE); -- IB-phase-2 vendor merge
1149
1150 --
1151
1152 BEGIN
1153 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1154 IF l_debug_on IS NULL THEN
1155 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1156 END IF;
1157
1158 IF l_debug_on THEN
1159 WSH_DEBUG_SV.push(l_module_name);
1160 WSH_DEBUG_SV.log(l_module_name,'P_supplier_name',P_supplier_name);
1161 END IF;
1162
1163 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1164
1165
1166 --Validate the ISP user.
1167 IF (p_in_param.caller= 'ISP' ) THEN
1168 IF ( NOT Is_Valid_ISP_User(p_in_param.user_id,P_supplier_name)) THEN
1169 FND_MESSAGE.SET_NAME('WSH', 'WSH_RR_INVALID_ISP_USER');
1170 fnd_msg_pub.add;
1171 raise fnd_api.g_exc_error;
1172 END IF;
1173 END IF;
1174
1175 --Validate Vendor
1176 OPEN validate_vendor_csr(p_supplier_name);
1177 FETCH validate_vendor_csr INTO x_vendor_id;
1178
1179 IF l_debug_on THEN
1180 WSH_DEBUG_SV.log(l_module_name,'x_vendor_id',x_vendor_id);
1181 END IF;
1182
1183 IF (validate_vendor_csr%NOTFOUND) THEN
1184 CLOSE validate_vendor_csr;
1185
1186 FND_MESSAGE.SET_NAME('WSH','WSH_RR_INV_SUPPLIER');
1187 FND_MESSAGE.SET_TOKEN('SUP_NAME',P_supplier_name);
1188 fnd_msg_pub.add;
1189
1190 raise fnd_api.g_exc_error;
1191 END IF;
1192 CLOSE validate_vendor_csr;
1193
1194 IF ( WSH_SUPPLIER_PARTY.VENDOR_PARTY_EXISTS(x_vendor_id,x_party_id) = 'N' ) THEN
1195 -- { IB-Phase-2
1196 IF l_debug_on THEN
1197 WSH_DEBUG_SV.log(l_module_name,'Party does not Exist for the Vendor ');
1198 END IF;
1199 --
1200 raise fnd_api.g_exc_error;
1201 -- } IB-Phase-2
1202 END IF;
1203
1204
1205
1206 IF l_debug_on THEN
1207 WSH_DEBUG_SV.pop(l_module_name);
1208 END IF;
1209 EXCEPTION
1210 WHEN FND_API.G_EXC_ERROR THEN
1211 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR ;
1212
1213 IF l_debug_on THEN
1214 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1215 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
1216 END IF;
1217
1218 WHEN OTHERS THEN
1219 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1220
1221 IF l_debug_on THEN
1222 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||
1223 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1224 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1225 END IF;
1226
1227 FND_MESSAGE.SET_NAME('WSH','WSH_UTIL_MESSAGE_U');
1228 FND_MESSAGE.SET_TOKEN('MSG_TEXT',sqlerrm);
1229 fnd_msg_pub.add;
1230 END Validate_Supplier;
1231
1232
1233 -- Start of comments
1234 -- API name : Check_Hz_Party_site_Uses
1235 -- Type : Public
1236 -- Pre-reqs : None.
1237 -- Function : API to check if party site uses exist for given party site and uses type in TCA.
1238 -- Parameters :
1239 -- IN:
1240 -- P_party_site_id IN Party Site Id.
1241 -- P_site_use_type IN Site uses type.
1242 -- OUT:
1243 -- x_return_status OUT NOCOPY Standard to output api status.
1244 -- End of comments
1245 FUNCTION Check_Hz_Party_site_Uses(
1246 P_party_site_id IN NUMBER,
1247 P_site_use_type IN VARCHAR2,
1248 X_party_site_use_id OUT NOCOPY NUMBER) RETURN BOOLEAN IS
1249
1250 l_debug_on BOOLEAN;
1251 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Check_Hz_Party_site_Uses';
1252
1253 --Cursor to check existence of Party Site Uses.
1254 CURSOR check_party_site_uses_crs IS
1255 SELECT party_site_use_id
1256 FROM hz_party_site_uses
1257 WHERE party_site_id=p_party_site_id
1258 AND site_use_type = p_site_use_type;
1259
1260 BEGIN
1261 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1262 IF l_debug_on IS NULL THEN
1263 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1264 END IF;
1265
1266 IF l_debug_on THEN
1267 WSH_DEBUG_SV.push(l_module_name);
1268 WSH_DEBUG_SV.log(l_module_name,'P_party_site_id',P_party_site_id);
1269 WSH_DEBUG_SV.log(l_module_name,'P_site_use_type',P_site_use_type);
1270 END IF;
1271
1272 --Check existence of Party Site Uses.
1273 OPEN check_party_site_uses_crs;
1274 FETCH check_party_site_uses_crs INTO X_party_site_use_id;
1275
1276 IF (check_party_site_uses_crs%NOTFOUND ) THEN
1277 CLOSE check_party_site_uses_crs;
1278 IF l_debug_on THEN
1279 WSH_DEBUG_SV.pop(l_module_name,'RETURN false');
1280 END IF;
1281 RETURN false;
1282 END IF;
1283
1284 CLOSE check_party_site_uses_crs;
1285
1286 IF l_debug_on THEN
1287 WSH_DEBUG_SV.pop(l_module_name);
1288 END IF;
1289
1290 IF l_debug_on THEN
1291 WSH_DEBUG_SV.pop(l_module_name,'RETURN true');
1292 END IF;
1293 RETURN true;
1294
1295 EXCEPTION
1296 WHEN OTHERS THEN
1297 IF l_debug_on THEN
1298 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||
1299 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1300 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1301 END IF;
1302 FND_MESSAGE.SET_NAME('WSH','WSH_UTIL_MESSAGE_U');
1303 FND_MESSAGE.SET_TOKEN('MSG_TEXT',sqlerrm);
1304 fnd_msg_pub.add;
1305
1306 RETURN false;
1307
1308 END Check_Hz_Party_site_Uses;
1309
1310
1311
1312 -- Start of comments
1313 -- API name : Create_Hz_Party_site_Uses
1314 -- Type : Public
1315 -- Pre-reqs : None.
1316 -- Procedure : API to create Hz Party Site uses for Party Site and Uses Type.
1317 -- This API first checks if party site uses exist in TCA, if not then create it,
1318 -- otherwise return the party_site_use_id.
1319 -- Parameters :
1320 -- IN:
1321 -- P_party_site_id IN Party Site Id.
1322 -- P_site_use_type IN Site uses type.
1323 -- OUT:
1324 -- x_party_site_use_id OUT NOCOPY Party site use Id created.
1325 -- x_return_status OUT NOCOPY Standard to output api status.
1326 -- End of comments
1327 PROCEDURE Create_HZ_Party_Site_uses(
1328 P_party_site_id IN NUMBER,
1329 P_site_use_type IN VARCHAR2,
1330 x_party_site_use_id OUT NOCOPY NUMBER,
1331 x_return_status OUT NOCOPY VARCHAR2) IS
1332
1333 l_debug_on BOOLEAN;
1334 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Create_HZ_Party_Site_uses';
1335
1336 l_return_status varchar2(1);
1337 l_msg_count NUMBER;
1338 l_msg_data varchar2(2000);
1339 l_site_use_rec HZ_PARTY_SITE_V2PUB.PARTY_SITE_USE_REC_TYPE;
1340 l_num_warnings number;
1341 l_num_errors number;
1342
1343 BEGIN
1344 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1345 IF l_debug_on IS NULL THEN
1346 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1347 END IF;
1348
1349 IF l_debug_on THEN
1350 WSH_DEBUG_SV.push(l_module_name);
1351 WSH_DEBUG_SV.log(l_module_name,'P_party_site_id',P_party_site_id);
1352 WSH_DEBUG_SV.log(l_module_name,'P_site_use_type',P_site_use_type);
1353 END IF;
1354
1355 IF (P_party_site_id IS NULL) THEN
1356 FND_MESSAGE.SET_NAME('WSH','WSH_REQUIRED_FIELD_NULL');
1357 FND_MESSAGE.SET_TOKEN('FIELD_NAME','P_party_site_id');
1358 wsh_util_core.add_message(x_return_status,l_module_name);
1359 raise fnd_api.g_exc_error;
1360 END IF;
1361
1362 IF (P_site_use_type IS NULL ) THEN
1363 FND_MESSAGE.SET_NAME('WSH','WSH_REQUIRED_FIELD_NULL');
1364 FND_MESSAGE.SET_TOKEN('FIELD_NAME','P_site_use_type');
1365 wsh_util_core.add_message(x_return_status,l_module_name);
1366 raise fnd_api.g_exc_error;
1367 END IF;
1368
1369 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1370
1371 --Check if Party site uses is already created.
1372 IF ( NOT check_HZ_Party_Site_uses(
1373 P_party_site_id => p_party_site_id,
1374 P_site_use_type => P_site_use_type,
1375 x_party_site_use_id =>x_party_site_use_id)
1376 ) THEN
1377
1378 l_site_use_rec.site_use_type := p_site_use_type;
1379 l_site_use_rec.party_site_id := p_party_site_id;
1380 l_site_use_rec.created_by_module := C_CREATED_BY_MODULE;
1381
1382 HZ_PARTY_SITE_V2PUB.Create_Party_Site_Use (
1383 p_init_msg_list => FND_API.G_FALSE,
1384 p_party_site_use_rec => l_site_use_rec,
1385 x_party_site_use_id => x_party_site_use_id,
1386 x_return_status => l_return_status,
1387 x_msg_count => l_msg_count,
1388 x_msg_data => l_msg_data);
1389
1390 IF l_debug_on THEN
1391 WSH_DEBUG_SV.log(l_module_name,'HZ_PARTY_SITE_V2PUB.Create_Party_Site_Use l_return_status',l_return_status);
1392 WSH_DEBUG_SV.log(l_module_name,'x_party_site_use_id',x_party_site_use_id);
1393 END IF;
1394 wsh_util_core.api_post_call(
1395 p_return_status => l_return_status,
1396 x_num_warnings => l_num_warnings,
1397 x_num_errors => l_num_errors);
1398
1399 END IF;
1400
1401 IF l_num_errors > 0 THEN
1402 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1403 ELSIF l_num_warnings > 0 THEN
1404 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1405 END IF;
1406
1407 IF l_debug_on THEN
1408 WSH_DEBUG_SV.pop(l_module_name);
1409 END IF;
1410 EXCEPTION
1411 WHEN FND_API.G_EXC_ERROR THEN
1412 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR ;
1413
1414 IF l_debug_on THEN
1415 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1416 WSH_DEBUG_SV.logmsg(l_module_name,'l_msg_count: '||l_msg_count,WSH_DEBUG_SV.C_EXCEP_LEVEL);
1417 WSH_DEBUG_SV.logmsg(l_module_name,'l_msg_data: '||l_msg_data,WSH_DEBUG_SV.C_EXCEP_LEVEL);
1418 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
1419 END IF;
1420
1421 WHEN OTHERS THEN
1422 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1423
1424 IF l_debug_on THEN
1425 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||
1426 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1427 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1428 END IF;
1429 FND_MESSAGE.SET_NAME('WSH','WSH_UTIL_MESSAGE_U');
1430 FND_MESSAGE.SET_TOKEN('MSG_TEXT',sqlerrm);
1431 fnd_msg_pub.add;
1432
1433 END Create_HZ_Party_Site_uses;
1434
1435 -- Start of comments
1436 -- API name : Create_hz_phone_contact
1437 -- Type : Public
1438 -- Pre-reqs : None.
1439 -- Procedure: API to create Hz Contact for phone. If phone number input is not null
1440 -- call api HZ_CONTACT_POINT_V2PUB.Create_phone_Contact_Point to create phone
1441 -- contact.
1442 -- Parameters :
1443 -- IN:
1444 -- P_phone IN Phone Number.
1445 -- OUT:
1446 -- x_return_status OUT NOCOPY Standard to output api status.
1447 -- End of comments
1448 PROCEDURE Create_hz_phone_contact(
1449 P_phone IN VARCHAR2,
1450 p_owner_table_id IN NUMBER,
1451 x_return_status OUT NOCOPY VARCHAR2 )
1452 IS
1453
1454 l_return_status varchar2(100);
1455 l_msg_count number;
1456 l_msg_data varchar2(2000);
1457 l_contact_point_id number;
1458
1459 l_contact_points_rec_type hz_contact_point_v2pub.contact_point_rec_type;
1460 l_phone_rec_type hz_contact_point_v2pub.phone_rec_type;
1461
1462 l_debug_on BOOLEAN;
1463 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Create_hz_phone_contact';
1464 l_num_warnings number;
1465 l_num_errors number;
1466 BEGIN
1467
1468 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1469 IF l_debug_on IS NULL
1470 THEN
1471 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1472 END IF;
1473 --
1474 IF l_debug_on THEN
1475 WSH_DEBUG_SV.push(l_module_name);
1476 WSH_DEBUG_SV.log(l_module_name,'P_PHONE',P_PHONE);
1477 WSH_DEBUG_SV.log(l_module_name,'p_owner_table_id',p_owner_table_id);
1478 END IF;
1479
1480 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1481
1482 --Phone Number not is mandatory parameter for creating phone contact,
1483 --call api create only if it is not null.
1484 IF (p_phone IS NOT NULL) THEN
1485 l_contact_points_rec_type.owner_table_name := 'HZ_PARTIES';
1486 l_contact_points_rec_type.owner_table_id := p_owner_table_id;
1487 l_contact_points_rec_type.primary_flag := 'Y';
1488 l_contact_points_rec_type.status := 'A';
1489 l_contact_points_rec_type.created_by_module := C_CREATED_BY_MODULE;
1490 l_contact_points_rec_type.contact_point_type := 'PHONE';
1491 l_phone_rec_type.phone_number := p_phone;
1492 l_phone_rec_type.phone_line_type := 'GEN';
1493 l_contact_points_rec_type.primary_flag := 'N';
1494
1495 IF l_debug_on THEN
1496 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit HZ_CONTACT_POINT_V2PUB.Create_Contact_Point for PHONE'
1497 ,WSH_DEBUG_SV.C_PROC_LEVEL);
1498 END IF;
1499 HZ_CONTACT_POINT_V2PUB.Create_phone_Contact_Point (
1500 p_init_msg_list => FND_API.G_FALSE,
1501 p_contact_point_rec => l_contact_points_rec_type,
1502 p_phone_rec => l_phone_rec_type,
1503 x_contact_point_id => l_contact_point_id,
1504 x_return_status => l_return_status,
1505 x_msg_count => l_msg_count,
1506 x_msg_data => l_msg_data);
1507
1508 IF l_debug_on THEN
1509 WSH_DEBUG_SV.log(l_module_name,'HZ_CONTACT_POINT_V2PUB.Create_phone_Contact_Point l_contact_point_id',l_contact_point_id);
1510 WSH_DEBUG_SV.log(l_module_name,'l_contact_point_id',l_contact_point_id);
1511 END IF;
1512 wsh_util_core.api_post_call(
1513 p_return_status => l_return_status,
1514 x_num_warnings => l_num_warnings,
1515 x_num_errors => l_num_errors);
1516
1517 END IF;
1518
1519 IF l_num_errors > 0 THEN
1520 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1521 ELSIF l_num_warnings > 0 THEN
1522 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1523 END IF;
1524
1525 IF l_debug_on THEN
1526 WSH_DEBUG_SV.pop(l_module_name);
1527 END IF;
1528 EXCEPTION
1529 WHEN FND_API.G_EXC_ERROR THEN
1530 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR ;
1531
1532 FND_MESSAGE.SET_NAME('WSH', 'WSH_RR_ERROR_CR_CONTACT');
1533 fnd_msg_pub.add;
1534
1535 IF l_debug_on THEN
1536 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1537 WSH_DEBUG_SV.logmsg(l_module_name,'l_msg_count: '||l_msg_count,WSH_DEBUG_SV.C_EXCEP_LEVEL);
1538 WSH_DEBUG_SV.logmsg(l_module_name,'l_msg_data: '||l_msg_data,WSH_DEBUG_SV.C_EXCEP_LEVEL);
1539 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
1540 END IF;
1541
1542 WHEN OTHERS THEN
1543 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1544
1545 IF l_debug_on THEN
1546 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||
1547 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1548 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1549 END IF;
1550
1551 FND_MESSAGE.SET_NAME('WSH','WSH_UTIL_MESSAGE_U');
1552 FND_MESSAGE.SET_TOKEN('MSG_TEXT',sqlerrm);
1553 fnd_msg_pub.add;
1554 END Create_hz_phone_contact;
1555
1556
1557 -- Start of comments
1558 -- API name : Update_HZ_contact
1559 -- Type : Public
1560 -- Pre-reqs : None.
1561 -- Procedure: API to Update Hz Contact for party site. Api update person ,phone
1562 -- or email information by calling respective HZ api's. These api are
1563 -- only called if input value is different from existing value.
1564 -- Parameters :
1565 -- IN:
1566 -- P_person_id IN Person Id.
1567 -- P_person_name IN Person Name.
1568 -- P_phone_contact_point_id IN Person contact point id.
1569 -- P_phone IN Phone Number.
1570 -- P_email_contact_point_id IN Email Contact point id.
1571 -- P_email IN Email.
1572 -- OUT:
1573 -- x_return_status OUT NOCOPY Standard to output api status.
1574 -- End of comments
1575 PROCEDURE Update_HZ_contact(
1576 P_person_id IN NUMBER,
1577 P_person_name IN VARCHAR2,
1578 P_old_person_name IN VARCHAR2,
1579 P_phone_contact_point_id IN NUMBER,
1580 P_phone IN VARCHAR2,
1581 P_old_phone IN VARCHAR2,
1582 P_email_contact_point_id IN NUMBER,
1583 P_email IN VARCHAR2,
1584 P_old_email IN VARCHAR2,
1585 p_owner_table_id IN NUMBER,
1586 x_return_status OUT NOCOPY VARCHAR2 )
1587 IS
1588
1589 l_person_rec HZ_PARTY_V2PUB.person_rec_type;
1590 l_ocon_rec HZ_PARTY_CONTACT_V2PUB.ORG_CONTACT_REC_TYPE;
1591 l_contact_points_rec_type hz_contact_point_v2pub.contact_point_rec_type;
1592 l_email_rec_type hz_contact_point_v2pub.email_rec_type;
1593 l_phone_rec_type hz_contact_point_v2pub.phone_rec_type;
1594
1595 l_return_status varchar2(100);
1596 l_msg_count number;
1597 l_msg_data varchar2(2000);
1598 l_profile_id number;
1599 l_object_version_number number;
1600 l_contact_object_version number;
1601 l_rel_object_version number;
1602 l_party_object_version number;
1603 l_position number;
1604 l_call_procedure varchar2(100);
1605 l_cont_point_version number;
1606
1607 --Cursor to get object_version_number for party
1608 CURSOR Get_Object_Version_Number(p_person_party_id NUMBER) IS
1609 select object_version_number
1610 from hz_parties
1611 where party_id = p_person_party_id;
1612
1613 --Cursor to get object_version_number for party contact.
1614 CURSOR Get_Cont_Point_Version(p_contact_point_id NUMBER) IS
1615 select object_version_number
1616 from hz_contact_points
1617 where contact_point_id = p_contact_point_id;
1618
1619 l_person_party_id NUMBER;
1620 l_phone_contact_point_id NUMBER;
1621 l_email_contact_point_id NUMBER;
1622
1623 l_debug_on BOOLEAN;
1624 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Update_HZ_contact';
1625 l_num_warnings number;
1626 l_num_errors number;
1627
1628 BEGIN
1629 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1630 IF l_debug_on IS NULL
1631 THEN
1632 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1633 END IF;
1634 IF l_debug_on THEN
1635 WSH_DEBUG_SV.push(l_module_name);
1636 END IF;
1637
1638 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1639
1640
1641 IF (nvl(P_person_name,'#') <> nvl(p_old_person_name,'#') ) THEN --{
1642 l_person_rec.person_first_name := p_person_name;
1643 l_person_rec.party_rec.party_id := P_person_id;
1644
1645 --Person record should exist for update, version number are not changed.
1646 OPEN Get_Object_Version_Number(P_person_id);
1647 FETCH Get_Object_Version_Number INTO l_object_version_number;
1648 IF (Get_Object_Version_Number%NOTFOUND) THEN
1649 raise fnd_api.g_exc_error;
1650 END IF;
1651 CLOSE Get_Object_Version_Number ;
1652
1653 IF l_debug_on THEN
1654 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit HZ_PARTY_V2PUB.Update_Person',WSH_DEBUG_SV.C_PROC_LEVEL);
1655 END IF;
1656
1657 HZ_PARTY_V2PUB.Update_Person (
1658 p_init_msg_list => FND_API.G_FALSE,
1659 p_person_rec => l_person_rec,
1660 p_party_object_version_number => l_object_version_number,
1661 x_profile_id => l_profile_id,
1662 x_return_status => l_return_status,
1663 x_msg_count => l_msg_count,
1664 x_msg_data => l_msg_data);
1665
1666 IF l_debug_on THEN
1667 WSH_DEBUG_SV.log(l_module_name,'HZ_PARTY_V2PUB.Update_Person l_return_status',l_return_status);
1668 WSH_DEBUG_SV.log(l_module_name,'x_profile_id',l_profile_id);
1669 WSH_DEBUG_SV.log(l_module_name,'l_msg_count',l_msg_count);
1670 WSH_DEBUG_SV.log(l_module_name,'l_msg_data',l_msg_data);
1671 END IF;
1672
1673 wsh_util_core.api_post_call(
1674 p_return_status => l_return_status,
1675 x_num_warnings => l_num_warnings,
1676 x_num_errors => l_num_errors);
1677
1678 END IF; --}
1679
1680
1681 IF (nvl(P_phone,'#') <> nvl(p_old_phone,'#')) THEN --{
1682
1683 --If phone contact is not exist then create else update
1684 IF (p_phone_contact_point_id IS NULL) THEN --{
1685 Create_hz_phone_contact(
1686 P_phone => p_phone,
1687 p_owner_table_id => p_owner_table_id,
1688 x_return_status => l_return_status);
1689
1690 IF l_debug_on THEN
1691 WSH_DEBUG_SV.log(l_module_name,'Create_hz_phone_contact l_return_status',l_return_status);
1692 END IF;
1693 wsh_util_core.api_post_call(
1694 p_return_status => l_return_status,
1695 x_num_warnings => l_num_warnings,
1696 x_num_errors => l_num_errors);
1697
1698 ELSE
1699 --Update only if Phone Number is not null. This check is only required
1700 --for phone, since this is not mandatory field.
1701 IF (P_phone IS NOT NULL) THEN
1702 l_contact_points_rec_type.contact_point_id := p_phone_contact_point_id;
1703 l_phone_rec_type.phone_number := p_phone;
1704
1705 --Phone record should exist for update, version number are not changed.
1706 OPEN Get_Cont_Point_Version(p_phone_contact_point_id);
1707 FETCH Get_Cont_Point_Version INTO l_cont_point_version;
1708 IF (Get_Cont_Point_Version%NOTFOUND) THEN
1709 raise fnd_api.g_exc_error;
1710 END IF;
1711 CLOSE Get_Cont_Point_Version ;
1712
1713
1714 IF l_debug_on THEN
1715 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program Unit HZ_CONTACT_POINT_V2PUB.Update_Contact_Point for Phone',WSH_DEBUG_SV.C_PROC_LEVEL);
1716 END IF;
1717
1718 HZ_CONTACT_POINT_V2PUB.Update_Contact_Point(
1719 p_init_msg_list => FND_API.G_FALSE,
1720 p_contact_point_rec => l_contact_points_rec_type,
1721 p_phone_rec => l_phone_rec_type,
1722 p_object_version_number => l_cont_point_version,
1723 x_return_status => l_return_status,
1724 x_msg_count => l_msg_count,
1725 x_msg_data => l_msg_data );
1726
1727 IF l_debug_on THEN
1728 WSH_DEBUG_SV.log(l_module_name,'HZ_CONTACT_POINT_V2PUB.Update_Contact_Point l_return_status',l_return_status);
1729 WSH_DEBUG_SV.log(l_module_name,'l_msg_count',l_msg_count);
1730 WSH_DEBUG_SV.log(l_module_name,'l_msg_data',l_msg_data);
1731 END IF;
1732 wsh_util_core.api_post_call(
1733 p_return_status => l_return_status,
1734 x_num_warnings => l_num_warnings,
1735 x_num_errors => l_num_errors);
1736 END IF;
1737 END IF; --}
1738 END IF; --}
1739
1740
1741 IF (nvl(p_email,'#') <> nvl(p_old_email,'#') ) THEN --{
1742 l_contact_points_rec_type.contact_point_id := p_email_contact_point_id;
1743 l_email_rec_type.email_address := P_email;
1744
1745 --Email record should exist for update, version number are not changed.
1746 OPEN Get_Cont_Point_Version(p_email_contact_point_id);
1747 FETCH Get_Cont_Point_Version INTO l_cont_point_version;
1748 IF (Get_Cont_Point_Version%NOTFOUND) THEN
1749 raise fnd_api.g_exc_error;
1750 END IF;
1751 CLOSE Get_Cont_Point_Version ;
1752
1753
1754 IF l_debug_on THEN
1755 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program Unit HZ_CONTACT_POINT_V2PUB.Update_Contact_Point',WSH_DEBUG_SV.
1756 C_PROC_LEVEL);
1757 END IF;
1758
1759 HZ_CONTACT_POINT_V2PUB.Update_Contact_Point(
1760 p_init_msg_list => FND_API.G_FALSE,
1761 p_contact_point_rec => l_contact_points_rec_type,
1762 p_email_rec => l_email_rec_type,
1763 p_object_version_number => l_cont_point_version,
1764 x_return_status => l_return_status,
1765 x_msg_count => l_msg_count,
1766 x_msg_data => l_msg_data );
1767
1768 IF l_debug_on THEN
1769 WSH_DEBUG_SV.log(l_module_name,'HZ_CONTACT_POINT_V2PUB.Update_Contact_Point l_return_status',l_return_status);
1770 WSH_DEBUG_SV.log(l_module_name,'l_msg_count',l_msg_count);
1771 WSH_DEBUG_SV.log(l_module_name,'l_msg_data',l_msg_data);
1772 END IF;
1773
1774 wsh_util_core.api_post_call(
1775 p_return_status => l_return_status,
1776 x_num_warnings => l_num_warnings,
1777 x_num_errors => l_num_errors);
1778
1779 END IF; --}
1780
1781
1782 IF l_num_errors > 0 THEN
1783 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1784 ELSIF l_num_warnings > 0 THEN
1785 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1786 END IF;
1787
1788 IF l_debug_on THEN
1789 WSH_DEBUG_SV.pop(l_module_name);
1790 END IF;
1791 EXCEPTION
1792 WHEN FND_API.G_EXC_ERROR THEN
1793 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR ;
1794
1795 FND_MESSAGE.SET_NAME('WSH', 'WSH_RR_ERROR_UP_CONTACT');
1796 fnd_msg_pub.add;
1797
1798 IF l_debug_on THEN
1799 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1800 WSH_DEBUG_SV.logmsg(l_module_name,'l_msg_count: '||l_msg_count,WSH_DEBUG_SV.C_EXCEP_LEVEL);
1801 WSH_DEBUG_SV.logmsg(l_module_name,'l_msg_data: '||l_msg_data,WSH_DEBUG_SV.C_EXCEP_LEVEL);
1802 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
1803 END IF;
1804
1805 IF (Get_Object_Version_Number%ISOPEN) THEN
1806 CLOSE Get_Object_Version_Number;
1807 END IF;
1808
1809 IF (Get_Cont_Point_Version%ISOPEN) THEN
1810 CLOSE Get_Cont_Point_Version;
1811 END IF;
1812
1813 WHEN OTHERS THEN
1814 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1815
1816 IF l_debug_on THEN
1817 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||
1818 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1819 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1820 END IF;
1821
1822
1823 IF (Get_Object_Version_Number%ISOPEN) THEN
1824 CLOSE Get_Object_Version_Number;
1825 END IF;
1826
1827 IF (Get_Cont_Point_Version%ISOPEN) THEN
1828 CLOSE Get_Cont_Point_Version;
1829 END IF;
1830
1831 FND_MESSAGE.SET_NAME('WSH','WSH_UTIL_MESSAGE_U');
1832 FND_MESSAGE.SET_TOKEN('MSG_TEXT',sqlerrm);
1833 fnd_msg_pub.add;
1834
1835 END Update_HZ_contact;
1836
1837
1838
1839 -- Start of comments
1840 -- API name : Create_HZ_contact
1841 -- Type : Public
1842 -- Pre-reqs : None.
1843 -- Procedure: API to create Hz Contact for party site. This API first check if contact exists
1844 -- for party site in TCA, if not then create it.
1845 -- Parameters :
1846 -- IN:
1847 -- P_party_id IN Party Id.
1848 -- P_party_site_id IN Party Site Id.
1849 -- P_person_name IN Person Name.
1850 -- P_phone IN Phone Number.
1851 -- P_email IN Email.
1852 -- OUT:
1853 -- x_return_status OUT NOCOPY Standard to output api status.
1854 -- End of comments
1855 PROCEDURE Create_HZ_contact(
1856 P_PARTY_ID IN NUMBER,
1857 P_PARTY_SITE_ID IN NUMBER,
1858 P_PERSON_NAME IN VARCHAR2,
1859 P_phone IN VARCHAR2,
1860 P_EMAIL IN VARCHAR2,
1861 x_return_status OUT NOCOPY VARCHAR2 )
1862 IS
1863
1864 l_return_status varchar2(100);
1865 l_msg_count number;
1866 l_position number;
1867 l_call_procedure varchar2(100);
1868 l_msg_data varchar2(2000);
1869 l_party_number varchar2(100);
1870 l_profile_id number;
1871 l_relationship_id number;
1872 l_exception_msg varchar2(1000);
1873 l_party_relationship_id number;
1874 l_contact_point_id number;
1875 l_org_contact_id number;
1876 l_party_id number;
1877
1878 l_per_rec HZ_PARTY_V2PUB.person_rec_type;
1879 l_person_party_id number;
1880
1881 l_rel_rec_type HZ_RELATIONSHIP_V2PUB.RELATIONSHIP_REC_TYPE;
1882 l_ocon_rec HZ_PARTY_CONTACT_V2PUB.ORG_CONTACT_REC_TYPE;
1883 l_contact_points_rec_type hz_contact_point_v2pub.contact_point_rec_type;
1884 l_email_rec_type hz_contact_point_v2pub.email_rec_type;
1885 l_phone_rec_type hz_contact_point_v2pub.phone_rec_type;
1886
1887 l_debug_on BOOLEAN;
1888 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Create_HZ_contact';
1889 l_num_warnings number;
1890 l_num_errors number;
1891 BEGIN
1892
1893 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1894 IF l_debug_on IS NULL
1895 THEN
1896 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1897 END IF;
1898 --
1899 IF l_debug_on THEN
1900 WSH_DEBUG_SV.push(l_module_name);
1901 WSH_DEBUG_SV.log(l_module_name,'P_PARTY_ID',P_PARTY_ID);
1902 WSH_DEBUG_SV.log(l_module_name,'P_PARTY_SITE_ID',P_PARTY_SITE_ID);
1903 WSH_DEBUG_SV.log(l_module_name,'P_PERSON_NAME',P_PERSON_NAME);
1904 WSH_DEBUG_SV.log(l_module_name,'P_PHONE',P_PHONE);
1905 WSH_DEBUG_SV.log(l_module_name,'P_EMAIL',P_EMAIL);
1906 END IF;
1907
1908 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1909
1910 l_per_rec.person_first_name := P_PERSON_NAME;
1911 l_per_rec.created_by_module := C_CREATED_BY_MODULE;
1912 l_per_rec.party_rec.status := 'A';
1913
1914 IF l_debug_on THEN
1915 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit HZ_PARTY_V2PUB.Create_Person',WSH_DEBUG_SV.C_PROC_LEVEL);
1916 END IF;
1917
1918 HZ_PARTY_V2PUB.Create_Person (
1919 p_init_msg_list => FND_API.G_FALSE,
1920 p_person_rec => l_per_rec,
1921 x_party_id => l_person_party_id,
1922 x_party_number => l_party_number,
1923 x_profile_id => l_profile_id,
1924 x_return_status => l_return_status,
1925 x_msg_count => l_msg_count,
1926 x_msg_data => l_msg_data);
1927
1928 IF l_debug_on THEN
1929 WSH_DEBUG_SV.log(l_module_name,'HZ_PARTY_V2PUB.Create_Person l_return_status',l_return_status);
1930 WSH_DEBUG_SV.log(l_module_name,'l_person_party_id',l_person_party_id);
1931 WSH_DEBUG_SV.log(l_module_name,'l_party_number',l_party_number);
1932 WSH_DEBUG_SV.log(l_module_name,'l_profile_id',l_profile_id);
1933 END IF;
1934 --
1935 wsh_util_core.api_post_call(
1936 p_return_status => l_return_status,
1937 x_num_warnings => l_num_warnings,
1938 x_num_errors => l_num_errors);
1939
1940 -- Create Org Contact to related person to party.
1941 l_ocon_rec.party_rel_rec.subject_id := l_person_party_id;
1942 l_ocon_rec.party_rel_rec.subject_table_name := 'HZ_PARTIES';
1943 l_ocon_rec.party_rel_rec.subject_type := 'PERSON';
1944 l_ocon_rec.party_rel_rec.object_id := p_party_id;
1945 l_ocon_rec.party_rel_rec.object_table_name := 'HZ_PARTIES';
1946 l_ocon_rec.party_rel_rec.object_type := 'ORGANIZATION';
1947 l_ocon_rec.party_rel_rec.relationship_code := 'CONTACT_OF';
1948 l_ocon_rec.party_rel_rec.relationship_type := 'CONTACT';
1949 l_ocon_rec.party_rel_rec.status := 'A';
1950 l_ocon_rec.party_rel_rec.start_date := sysdate;
1951 l_ocon_rec.party_rel_rec.created_by_module := C_CREATED_BY_MODULE;
1952 l_ocon_rec.party_site_id := p_party_site_id;
1953 l_ocon_rec.created_by_module := C_CREATED_BY_MODULE;
1954
1955
1956 IF l_debug_on THEN
1957 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit HZ_PARTY_CONTACT_V2PUB.create_org_contact',WSH_DEBUG_SV.C_PROC_LEVEL);
1958 END IF;
1959
1960 HZ_PARTY_CONTACT_V2PUB.create_org_contact (
1961 p_org_contact_rec => l_ocon_rec,
1962 x_org_contact_id => l_org_contact_id,
1963 x_party_rel_id => l_party_relationship_id,
1964 x_party_id => l_party_id,
1965 x_party_number => l_party_number,
1966 x_return_status => l_return_status,
1967 x_msg_count => l_msg_count,
1968 x_msg_data => l_msg_data );
1969
1970 IF l_debug_on THEN
1971 WSH_DEBUG_SV.log(l_module_name,'HZ_PARTY_CONTACT_V2PUB.create_org_contact l_return_status',l_return_status);
1972 WSH_DEBUG_SV.log(l_module_name,'l_org_contact_id',l_org_contact_id);
1973 WSH_DEBUG_SV.log(l_module_name,'l_party_relationship_id',l_party_relationship_id);
1974 WSH_DEBUG_SV.log(l_module_name,'l_party_id',l_party_id);
1975 WSH_DEBUG_SV.log(l_module_name,'l_party_number',l_party_number);
1976 END IF;
1977 wsh_util_core.api_post_call(
1978 p_return_status => l_return_status,
1979 x_num_warnings => l_num_warnings,
1980 x_num_errors => l_num_errors);
1981
1982
1983 -- Create a EMAIL contact point, this related email to party.
1984 l_contact_points_rec_type.owner_table_name := 'HZ_PARTIES';
1985 l_contact_points_rec_type.owner_table_id := l_party_id;
1986 l_contact_points_rec_type.primary_flag := 'Y';
1987 l_contact_points_rec_type.status := 'A';
1988 l_contact_points_rec_type.created_by_module := C_CREATED_BY_MODULE;
1989 l_contact_points_rec_type.contact_point_type := 'EMAIL';
1990 l_email_rec_type.email_address := p_email;
1991
1992 IF l_debug_on THEN
1993 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit HZ_CONTACT_POINT_V2PUB.Create_Contact_Point for EMAIL',WSH_DEBUG_SV.C_PROC_LEVEL);
1994 END IF;
1995 HZ_CONTACT_POINT_V2PUB.Create_email_Contact_Point (
1996 p_init_msg_list => FND_API.G_FALSE,
1997 p_contact_point_rec => l_contact_points_rec_type,
1998 p_email_rec => l_email_rec_type,
1999 x_contact_point_id => l_contact_point_id,
2000 x_return_status => l_return_status,
2001 x_msg_count => l_msg_count,
2002 x_msg_data => l_msg_data);
2003
2004 IF l_debug_on THEN
2005 WSH_DEBUG_SV.log(l_module_name,'HZ_CONTACT_POINT_V2PUB.Create_email_Contact_Point l_return_status',l_return_status);
2006 WSH_DEBUG_SV.log(l_module_name,'l_contact_point_id',l_contact_point_id);
2007 END IF;
2008 wsh_util_core.api_post_call(
2009 p_return_status => l_return_status,
2010 x_num_warnings => l_num_warnings,
2011 x_num_errors => l_num_errors);
2012
2013
2014 --Phone Number is not mandatory field, so create only if passed.
2015 IF (p_phone IS NOT NULL ) THEN
2016 Create_hz_phone_contact(
2017 P_phone => p_phone,
2018 p_owner_table_id => l_party_id,
2019 x_return_status => l_return_status);
2020
2021 IF l_debug_on THEN
2022 WSH_DEBUG_SV.log(l_module_name,'Create_hz_phone_contact l_return_status',l_return_status);
2023 END IF;
2024 wsh_util_core.api_post_call(
2025 p_return_status => l_return_status,
2026 x_num_warnings => l_num_warnings,
2027 x_num_errors => l_num_errors);
2028 END IF;
2029
2030 IF l_num_errors > 0 THEN
2031 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2032 ELSIF l_num_warnings > 0 THEN
2033 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2034 END IF;
2035
2036 IF l_debug_on THEN
2037 WSH_DEBUG_SV.pop(l_module_name);
2038 END IF;
2039 EXCEPTION
2040 WHEN FND_API.G_EXC_ERROR THEN
2041 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR ;
2042
2043 FND_MESSAGE.SET_NAME('WSH', 'WSH_RR_ERROR_CR_CONTACT');
2044 fnd_msg_pub.add;
2045
2046 IF l_debug_on THEN
2047 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2048 WSH_DEBUG_SV.logmsg(l_module_name,'l_msg_count: '||l_msg_count,WSH_DEBUG_SV.C_EXCEP_LEVEL);
2049 WSH_DEBUG_SV.logmsg(l_module_name,'l_msg_data: '||l_msg_data,WSH_DEBUG_SV.C_EXCEP_LEVEL);
2050 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
2051 END IF;
2052
2053 WHEN OTHERS THEN
2054 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2055
2056 IF l_debug_on THEN
2057 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||
2058 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2059 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2060 END IF;
2061
2062 FND_MESSAGE.SET_NAME('WSH','WSH_UTIL_MESSAGE_U');
2063 FND_MESSAGE.SET_TOKEN('MSG_TEXT',sqlerrm);
2064 fnd_msg_pub.add;
2065 END Create_HZ_contact;
2066
2067
2068 -- Start of comments
2069 -- API name : Process_HZ_contact
2070 -- Type : Public
2071 -- Pre-reqs : None.
2072 -- Procedure: API to Process Hz Contact for party site. Api first check if
2073 -- contact information is already exists for party and party site. If
2074 -- exist then update the information else create new contact.
2075 -- Parameters :
2076 -- IN:
2077 -- P_party_id IN Party Id.
2078 -- P_party_site_id IN Party Site Id.
2079 -- P_person_name IN Person Name.
2080 -- P_phone IN Phone Number.
2081 -- P_email IN Email.
2082 -- OUT:
2083 -- x_return_status OUT NOCOPY Standard to output api status.
2084 -- End of comments
2085 PROCEDURE Process_HZ_contact(
2086 P_PARTY_ID IN NUMBER,
2087 P_PARTY_SITE_ID IN NUMBER,
2088 P_PERSON_NAME IN VARCHAR2,
2089 P_phone IN VARCHAR2,
2090 P_EMAIL IN VARCHAR2,
2091 x_return_status OUT NOCOPY VARCHAR2 )
2092 IS
2093
2094 l_person_rec HZ_PARTY_V2PUB.person_rec_type;
2095 l_ocon_rec HZ_PARTY_CONTACT_V2PUB.ORG_CONTACT_REC_TYPE;
2096 l_contact_points_rec_type hz_contact_point_v2pub.contact_point_rec_type;
2097 l_email_rec_type hz_contact_point_v2pub.email_rec_type;
2098 l_phone_rec_type hz_contact_point_v2pub.phone_rec_type;
2099
2100 l_return_status varchar2(100);
2101 l_msg_count number;
2102 l_msg_data varchar2(2000);
2103 l_profile_id number;
2104 l_object_version_number number;
2105 l_contact_object_version number;
2106 l_rel_object_version number;
2107 l_party_object_version number;
2108 l_position number;
2109 l_call_procedure varchar2(100);
2110 l_cont_point_version number;
2111
2112 --Cursor check for contact information already exists for party and party site.
2113 --The relationship type, relationship code, object and subject are define same
2114 --as it was created. For detail how contact information is created , please go
2115 --through create_hz_contact api.
2116 CURSOR Get_Contact_info(p_party_id NUMBER,
2117 p_party_site_id NUMBER ) IS
2118 select contact_person.party_id l_contact_person_id,
2119 contact_person.party_name shipper_name,
2120 phone_record.contact_point_id phone_contact_point_id,
2121 phone_record.phone_number phone_number,
2122 email_record.contact_point_id email_contact_point_id,
2123 email_record.email_address,
2124 email_record.owner_table_id,
2125 hrel.relationship_id, -- IB-Phase-2
2126 hrel.end_date end_date -- IB-Phase-2
2127 from hz_party_sites hps,
2128 hz_parties contact_person,
2129 hz_org_contacts supplier_contact,
2130 hz_contact_points phone_record,
2131 hz_contact_points email_record,
2132 hz_relationships hrel
2133 where hrel.subject_id = contact_person.party_id
2134 and hrel.subject_table_name = 'HZ_PARTIES'
2135 and hrel.subject_type = 'PERSON'
2136 and hrel.object_id = hps.party_id
2137 and hrel.object_table_name = 'HZ_PARTIES'
2138 and hrel.object_type = 'ORGANIZATION'
2139 and hrel.relationship_code = 'CONTACT_OF'
2140 and hrel.directional_flag = 'F'
2141 and supplier_contact.party_relationship_id =hrel.relationship_id
2142 and supplier_contact.party_site_id = hps.party_site_id
2143 and phone_record.owner_table_name(+) = 'HZ_PARTIES'
2144 and phone_record.owner_table_id(+) = hrel.party_id
2145 and phone_record.contact_point_type(+) = 'PHONE'
2146 and email_record.owner_table_name = 'HZ_PARTIES'
2147 and email_record.owner_table_id = hrel.party_id
2148 and email_record.contact_point_type = 'EMAIL'
2149 and hps.party_site_id =p_party_site_id
2150 and hps.party_id = p_party_id;
2151
2152 l_person_id NUMBER;
2153 l_person_name varchar2(240);
2154 l_phone_contact_point_id NUMBER;
2155 l_phone varchar2(40);
2156 l_email_contact_point_id NUMBER;
2157 l_email varchar2(2000);
2158 l_owner_table_id NUMBER;
2159 -- { IB-Phase-2
2160 l_relation_end_date DATE;
2161 l_relationship_id NUMBER;
2162 l_relationship_rec HZ_RELATIONSHIP_V2PUB.RELATIONSHIP_REC_TYPE;-- IB-Phase-2
2163 l_party_object_version_number NUMBER;
2164 -- } IB-Phase-2
2165
2166 l_debug_on BOOLEAN;
2167 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Process_HZ_contact';
2168 l_num_warnings number;
2169 l_num_errors number;
2170 BEGIN
2171 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2172 IF l_debug_on IS NULL
2173 THEN
2174 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2175 END IF;
2176 IF l_debug_on THEN
2177 WSH_DEBUG_SV.push(l_module_name);
2178 WSH_DEBUG_SV.log(l_module_name,'P_party_ID',P_party_ID);
2179 WSH_DEBUG_SV.log(l_module_name,'P_party_site_ID',P_party_site_ID);
2180 WSH_DEBUG_SV.log(l_module_name,'P_PERSON_NAME',P_PERSON_NAME);
2181 WSH_DEBUG_SV.log(l_module_name,'P_phone',P_phone);
2182 WSH_DEBUG_SV.log(l_module_name,'P_EMAIL',P_EMAIL);
2183 END IF;
2184
2185 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2186
2187 --See if contact information are already exists for Party and Party site.
2188 OPEN Get_Contact_info(p_party_id,p_party_site_id);
2189 FETCH Get_Contact_info
2190 INTO l_person_id, l_person_name,
2191 l_phone_contact_point_id,l_phone,
2192 l_email_contact_point_id,l_email,
2193 l_owner_table_id,
2194 l_relationship_id,
2195 l_relation_end_date; --IB-Phase-2
2196
2197 IF l_debug_on THEN
2198 WSH_DEBUG_SV.log(l_module_name,'l_person_id',l_person_id);
2199 WSH_DEBUG_SV.log(l_module_name,'l_person_name',l_person_name);
2200 WSH_DEBUG_SV.log(l_module_name,'l_phone_contact_point_id',l_phone_contact_point_id);
2201 WSH_DEBUG_SV.log(l_module_name,'l_phone',l_phone);
2202 WSH_DEBUG_SV.log(l_module_name,'l_email_contact_point_id',l_email_contact_point_id);
2203 WSH_DEBUG_SV.log(l_module_name,'l_email',l_email);
2204 END IF;
2205
2206 IF (Get_Contact_info%NOTFOUND) THEN
2207 --Create new contact info
2208 Create_HZ_contact(
2209 P_party_id => p_party_id,
2210 P_party_site_id => p_party_site_id,
2211 P_person_name => p_person_name,
2212 P_phone => p_phone,
2213 P_email => p_email,
2214 x_return_status => l_return_status);
2215
2216 IF l_debug_on THEN
2217 WSH_DEBUG_SV.log(l_module_name,'Create_HZ_contact l_return_status',l_return_status);
2218 END IF;
2219 wsh_util_core.api_post_call(
2220 p_return_status => l_return_status,
2221 x_num_warnings => l_num_warnings,
2222 x_num_errors => l_num_errors);
2223
2224 ELSE
2225
2226 --{ IB-Phase-2
2227 --Make this relation ship active, if it is not.
2228 IF l_relation_end_date < SYSDATE
2229 THEN
2230 l_relationship_rec.relationship_id := l_relationship_id;
2231 l_relationship_rec.end_date := FND_API.G_MISS_DATE;
2232
2233 HZ_RELATIONSHIP_V2PUB.update_relationship(
2234 p_init_msg_list => FND_API.g_false,
2235 p_relationship_rec => l_relationship_rec,
2236 p_object_version_number => l_object_version_number,
2237 p_party_object_version_number => l_party_object_version_number,
2238 x_return_status => l_return_status,
2239 x_msg_count => l_msg_count,
2240 x_msg_data => l_msg_data
2241 );
2242
2243 IF l_debug_on THEN
2244 WSH_DEBUG_SV.log(l_module_name,'HZ_RELATIONSHIP_V2PUB.update_relationship l_return_status',l_return_status);
2245 END IF;
2246 wsh_util_core.api_post_call(
2247 p_return_status => l_return_status,
2248 x_num_warnings => l_num_warnings,
2249 x_num_errors => l_num_errors);
2250
2251 END IF;
2252 --} IB-Phase-2
2253
2254
2255 --Upadte existing contact info
2256 Update_HZ_contact(
2257 P_person_id => l_person_id,
2258 P_person_name => p_person_name,
2259 P_old_person_name => l_person_name,
2260 P_phone_contact_point_id => l_phone_contact_point_id,
2261 P_phone => p_phone,
2262 P_old_phone => l_phone,
2263 P_email_contact_point_id => l_email_contact_point_id,
2264 P_email => p_email,
2265 P_old_email => l_email,
2266 p_owner_table_id => l_owner_table_id,
2267 x_return_status => l_return_status);
2268
2269 IF l_debug_on THEN
2270 WSH_DEBUG_SV.log(l_module_name,'Update_HZ_contact l_return_status',l_return_status);
2271 END IF;
2272 wsh_util_core.api_post_call(
2273 p_return_status => l_return_status,
2274 x_num_warnings => l_num_warnings,
2275 x_num_errors => l_num_errors);
2276 END IF;
2277 CLOSE Get_Contact_info;
2278
2279 IF l_num_errors > 0 THEN
2280 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2281 ELSIF l_num_warnings > 0 THEN
2282 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2283 END IF;
2284
2285 IF l_debug_on THEN
2286 WSH_DEBUG_SV.pop(l_module_name);
2287 END IF;
2288 EXCEPTION
2289 WHEN FND_API.G_EXC_ERROR THEN
2290 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR ;
2291
2292 IF l_debug_on THEN
2293 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2294 WSH_DEBUG_SV.logmsg(l_module_name,'l_msg_count: '||l_msg_count,WSH_DEBUG_SV.C_EXCEP_LEVEL);
2295 WSH_DEBUG_SV.logmsg(l_module_name,'l_msg_data: '||l_msg_data,WSH_DEBUG_SV.C_EXCEP_LEVEL);
2296 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
2297 END IF;
2298
2299 IF (Get_Contact_info%ISOPEN) THEN
2300 CLOSE Get_Contact_info;
2301 END IF;
2302
2303 WHEN OTHERS THEN
2304 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2305
2306 IF l_debug_on THEN
2307 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||
2308 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2309 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2310 END IF;
2311
2312 IF (Get_Contact_info%ISOPEN) THEN
2313 CLOSE Get_Contact_info;
2314 END IF;
2315
2316 FND_MESSAGE.SET_NAME('WSH','WSH_UTIL_MESSAGE_U');
2317 FND_MESSAGE.SET_TOKEN('MSG_TEXT',sqlerrm);
2318 fnd_msg_pub.add;
2319 END Process_HZ_contact;
2320
2321
2322 -- Start of comments
2323 -- API name : Update_Address
2324 -- Type : Public
2325 -- Pre-reqs : None.
2326 -- Procedure : API to update address information. This wrapper api
2327 -- calls api to update location and contact information.
2328 -- Parameters :
2329 -- IN:
2330 -- p_location_code IN Location Code.
2331 -- P_party_id IN Party Id.
2332 -- P_address1 IN Address1.
2333 -- P_address2 IN Address2.
2334 -- P_address3 IN Address3.
2335 -- P_address4 IN Address4.
2336 -- P_city IN City.
2337 -- P_postal_code IN Postal Code.
2338 -- P_state IN State.
2339 -- P_Province IN Province.
2340 -- P_county IN County.
2341 -- p_country IN Country.
2342 -- p_shipper_name IN Shipper Name
2343 -- p_phone IN Phone Number.
2344 -- p_email IN Email Address.
2345 -- OUT:
2346 -- x_return_status OUT NOCOPY Standard to output api status.
2347 -- End of comments
2348 PROCEDURE Update_Address(
2349 P_location_id IN number,
2350 P_party_id IN number,
2351 P_party_site_id IN number,
2352 P_address1 IN varchar2,
2353 P_address2 IN varchar2,
2354 P_address3 IN varchar2,
2355 P_address4 IN varchar2,
2356 P_city IN varchar2,
2357 P_postal_code IN varchar2,
2358 P_state IN varchar2,
2359 P_Province IN varchar2,
2360 P_county IN varchar2,
2361 p_country IN varchar2,
2362 p_shipper_name IN varchar2,
2363 p_phone IN varchar2,
2364 p_email IN varchar2,
2365 x_return_status OUT NOCOPY varchar2) IS
2366
2367 l_debug_on BOOLEAN;
2368 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Update_Address';
2369
2370 l_return_status varchar2(1);
2371 l_location_id NUMBER;
2372 l_num_warnings number;
2373 l_num_errors number;
2374
2375 BEGIN
2376 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2377 IF l_debug_on IS NULL THEN
2378 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2379 END IF;
2380
2381 IF l_debug_on THEN
2382 WSH_DEBUG_SV.push(l_module_name);
2383 WSH_DEBUG_SV.log(l_module_name,'p_location_id',p_location_id);
2384 WSH_DEBUG_SV.log(l_module_name,'p_party_site_id',p_party_site_id);
2385 WSH_DEBUG_SV.log(l_module_name,'P_address1',P_address1);
2386 WSH_DEBUG_SV.log(l_module_name,'P_address2',P_address2);
2387 WSH_DEBUG_SV.log(l_module_name,'P_address3',P_address3);
2388 WSH_DEBUG_SV.log(l_module_name,'P_address4',P_address4);
2389 WSH_DEBUG_SV.log(l_module_name,'P_city',P_city);
2390 WSH_DEBUG_SV.log(l_module_name,'P_postal_code',P_postal_code);
2391 WSH_DEBUG_SV.log(l_module_name,'P_state',P_state);
2392 WSH_DEBUG_SV.log(l_module_name,'P_Province',P_Province);
2393 WSH_DEBUG_SV.log(l_module_name,'P_county',P_county);
2394 WSH_DEBUG_SV.log(l_module_name,'p_country',p_country);
2395 WSH_DEBUG_SV.log(l_module_name,'p_phone',p_phone);
2396 WSH_DEBUG_SV.log(l_module_name,'p_email',p_email);
2397 END IF;
2398
2399 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2400
2401 l_location_id := p_location_id;
2402
2403 Update_Hz_Location(
2404 P_location_id => p_location_id,
2405 P_address1 => P_address1,
2406 P_address2 => P_address2,
2407 P_address3 => P_address3,
2408 P_address4 => P_address4,
2409 P_city => P_city,
2410 P_postal_code => P_postal_code,
2411 P_state => P_state,
2412 P_province => P_province,
2413 P_county => P_county,
2414 p_country => p_country,
2415 x_return_status => l_return_status);
2416
2417 IF l_debug_on THEN
2418 WSH_DEBUG_SV.log(l_module_name,'Update_Hz_Location l_return_status',l_return_status);
2419 END IF;
2420
2421 wsh_util_core.api_post_call(
2422 p_return_status => l_return_status,
2423 x_num_warnings => l_num_warnings,
2424 x_num_errors => l_num_errors);
2425
2426 --Validate new updated information.
2427 WSH_UTIL_VALIDATE.validate_location (
2428 p_location_id =>l_location_id,
2429 p_location_code =>NULL,
2430 p_caller => 'PO',
2431 x_return_status =>l_return_status);
2432
2433 IF l_debug_on THEN
2434 WSH_DEBUG_SV.log(l_module_name,' WSH_UTIL_VALIDATE.validate_location l_return_status',l_return_status);
2435 END IF;
2436 wsh_util_core.api_post_call(
2437 p_return_status => l_return_status,
2438 x_num_warnings => l_num_warnings,
2439 x_num_errors => l_num_errors);
2440
2441 --Update Contact information.
2442 Process_HZ_contact(
2443 P_party_id => p_party_id,
2444 P_party_site_id => p_party_site_id,
2445 P_person_name => p_shipper_name,
2446 P_phone => p_phone,
2447 P_email => p_email,
2448 x_return_status => l_return_status);
2449
2450 IF l_debug_on THEN
2451 WSH_DEBUG_SV.log(l_module_name,'Process_HZ_contact l_return_status',l_return_status);
2452 END IF;
2453 wsh_util_core.api_post_call(
2454 p_return_status => l_return_status,
2455 x_num_warnings => l_num_warnings,
2456 x_num_errors => l_num_errors);
2457
2458 IF l_num_errors > 0 THEN
2459 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2460 ELSIF l_num_warnings > 0 THEN
2461 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2462 END IF;
2463
2464 IF l_debug_on THEN
2465 WSH_DEBUG_SV.pop(l_module_name);
2466 END IF;
2467 EXCEPTION
2468 WHEN FND_API.G_EXC_ERROR THEN
2469 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR ;
2470
2471 FND_MESSAGE.SET_NAME('WSH', 'WSH_RR_ERROR_UP_LOC');
2472 fnd_msg_pub.add;
2473
2474 IF l_debug_on THEN
2475 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2476 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
2477 END IF;
2478
2479 WHEN OTHERS THEN
2480 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2481
2482 IF l_debug_on THEN
2483 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||
2484 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2485 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2486 END IF;
2487
2488 FND_MESSAGE.SET_NAME('WSH','WSH_UTIL_MESSAGE_U');
2489 FND_MESSAGE.SET_TOKEN('MSG_TEXT',sqlerrm);
2490 fnd_msg_pub.add;
2491 END Update_Address;
2492
2493
2494 -- Start of comments
2495 -- API name : Create_Address
2496 -- Type : Public
2497 -- Pre-reqs : None.
2498 -- Procedure : API to create address information. Api does
2499 -- 1.Create location and party site.
2500 -- 2.Validate location.
2501 -- 3.Create Party Site Uses.
2502 -- 4.Create contact information.
2503 -- Parameters :
2504 -- IN:
2505 -- p_location_code IN Location Code.
2506 -- P_party_id IN Party Id.
2507 -- P_address1 IN Address1.
2508 -- P_address2 IN Address2.
2509 -- P_address3 IN Address3.
2510 -- P_address4 IN Address4.
2511 -- P_city IN City.
2512 -- P_postal_code IN Postal Code.
2513 -- P_state IN State.
2514 -- P_Province IN Province.
2515 -- P_county IN County.
2516 -- p_country IN Country.
2517 -- p_shipper_name IN Shipper Name
2518 -- p_phone IN Phone Number.
2519 -- p_email IN Email Address.
2520 -- OUT:
2521 -- x_location_id OUT NOCOPY Location id create.
2522 -- x_party_site_id OUT NOCOPY Party Site id created.
2523 -- x_return_status OUT NOCOPY OUT NOCOPY Standard to output api status.
2524 -- End of comments
2525 PROCEDURE Create_Address(
2526 P_vendor_id IN number,
2527 P_party_id IN number,
2528 P_location_code IN varchar2,
2529 P_address1 IN varchar2,
2530 P_address2 IN varchar2,
2531 P_address3 IN varchar2,
2532 P_address4 IN varchar2,
2533 P_city IN varchar2,
2534 P_postal_code IN varchar2,
2535 P_state IN varchar2,
2536 P_Province IN varchar2,
2537 P_county IN varchar2,
2538 p_country IN varchar2,
2539 p_shipper_name IN varchar2,
2540 p_phone IN varchar2,
2541 p_email IN varchar2,
2542 x_location_id OUT NOCOPY number,
2543 x_party_site_id OUT NOCOPY number,
2544 x_return_status OUT NOCOPY varchar2) IS
2545
2546 l_debug_on BOOLEAN;
2547 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Create_Address';
2548
2549 l_return_status varchar2(1);
2550 l_party_site_id NUMBER;
2551 l_party_site_use_id NUMBER;
2552 l_num_warnings number;
2553 l_num_errors number;
2554 BEGIN
2555 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2556 IF l_debug_on IS NULL THEN
2557 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2558 END IF;
2559
2560 IF l_debug_on THEN
2561 WSH_DEBUG_SV.push(l_module_name);
2562 WSH_DEBUG_SV.log(l_module_name,'P_vendor_id',P_vendor_id);
2563 WSH_DEBUG_SV.log(l_module_name,'p_location_code',p_location_code);
2564 WSH_DEBUG_SV.log(l_module_name,'P_address1',P_address1);
2565 WSH_DEBUG_SV.log(l_module_name,'P_address2',P_address2);
2566 WSH_DEBUG_SV.log(l_module_name,'P_address3',P_address3);
2567 WSH_DEBUG_SV.log(l_module_name,'P_address4',P_address4);
2568 WSH_DEBUG_SV.log(l_module_name,'P_city',P_city);
2569 WSH_DEBUG_SV.log(l_module_name,'P_postal_code',P_postal_code);
2570 WSH_DEBUG_SV.log(l_module_name,'P_state',P_state);
2571 WSH_DEBUG_SV.log(l_module_name,'P_Province',P_Province);
2572 WSH_DEBUG_SV.log(l_module_name,'P_county',P_county);
2573 WSH_DEBUG_SV.log(l_module_name,'p_country',p_country);
2574 WSH_DEBUG_SV.log(l_module_name,'p_phone',p_phone);
2575 WSH_DEBUG_SV.log(l_module_name,'p_email',p_email);
2576 END IF;
2577
2578 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2579
2580 --Create Location and Party Site
2581 Create_Hz_Location_Party_Site(
2582 P_party_id => p_party_id,
2583 P_location_code => P_location_code||'|'||p_party_id,
2584 P_address1 => P_address1,
2585 P_address2 => P_address2,
2586 P_address3 => P_address3,
2587 P_address4 => P_address4,
2588 P_city => P_city,
2589 P_postal_code => P_postal_code,
2590 P_state => P_state,
2591 P_province => P_province,
2592 P_county => P_county,
2593 p_country => p_country,
2594 x_location_id => x_location_id,
2595 x_party_site_id => x_party_site_id,
2596 x_return_status => l_return_status);
2597
2598 IF l_debug_on THEN
2599 WSH_DEBUG_SV.log(l_module_name,'Create_Hz_Location_Party_Site l_return_status',l_return_status);
2600 WSH_DEBUG_SV.log(l_module_name,'x_location_id',x_location_id);
2601 WSH_DEBUG_SV.log(l_module_name,'x_party_site_id',x_party_site_id);
2602 END IF;
2603 wsh_util_core.api_post_call(
2604 p_return_status => l_return_status,
2605 x_num_warnings => l_num_warnings,
2606 x_num_errors => l_num_errors);
2607
2608 -- Validate location information created and transfer from HZ to WSH.
2609 WSH_UTIL_VALIDATE.validate_location (
2610 p_location_id =>x_location_id,
2611 p_location_code =>NULL,
2612 p_caller => 'PO',
2613 x_return_status =>l_return_status);
2614
2615 IF l_debug_on THEN
2616 WSH_DEBUG_SV.log(l_module_name,' WSH_UTIL_VALIDATE.validate_location l_return_status',l_return_status);
2617 END IF;
2618 wsh_util_core.api_post_call(
2619 p_return_status => l_return_status,
2620 x_num_warnings => l_num_warnings,
2621 x_num_errors => l_num_errors);
2622
2623
2624 --Create Party Site Uses
2625 Create_HZ_Party_Site_uses(
2626 p_party_site_id => x_party_site_id,
2627 p_site_use_type => 'SUPPLIER_SHIP_FROM',
2628 x_party_site_use_id => l_party_site_use_id,
2629 x_return_status => l_return_status);
2630
2631 IF l_debug_on THEN
2632 WSH_DEBUG_SV.log(l_module_name,' Create_HZ_Party_Site_uses l_return_status',l_return_status);
2633 WSH_DEBUG_SV.log(l_module_name,'l_party_site_use_id',l_party_site_use_id);
2634 END IF;
2635 wsh_util_core.api_post_call(
2636 p_return_status => l_return_status,
2637 x_num_warnings => l_num_warnings,
2638 x_num_errors => l_num_errors);
2639
2640 --Create Contact
2641 Process_HZ_contact(
2642 P_party_id => p_party_id,
2643 P_party_site_id => x_party_site_id,
2644 P_person_name => p_shipper_name,
2645 P_phone => p_phone,
2646 P_email => p_email,
2647 x_return_status => l_return_status);
2648
2649 IF l_debug_on THEN
2650 WSH_DEBUG_SV.log(l_module_name,'Create_HZ_contact PHONE l_return_status',l_return_status);
2651 END IF;
2652 wsh_util_core.api_post_call(
2653 p_return_status => l_return_status,
2654 x_num_warnings => l_num_warnings,
2655 x_num_errors => l_num_errors);
2656
2657 IF l_num_errors > 0 THEN
2658 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2659 ELSIF l_num_warnings > 0 THEN
2660 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2661 END IF;
2662
2663
2664 IF l_debug_on THEN
2665 WSH_DEBUG_SV.pop(l_module_name);
2666 END IF;
2667 EXCEPTION
2668 WHEN FND_API.G_EXC_ERROR THEN
2669 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR ;
2670
2671 FND_MESSAGE.SET_NAME('WSH', 'WSH_RR_ERROR_CR_LOC');
2672 fnd_msg_pub.add;
2673
2674 IF l_debug_on THEN
2675 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2676 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
2677 END IF;
2678
2679 WHEN OTHERS THEN
2680 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2681
2682 IF l_debug_on THEN
2683 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||
2684 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2685 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2686 END IF;
2687
2688 FND_MESSAGE.SET_NAME('WSH','WSH_UTIL_MESSAGE_U');
2689 FND_MESSAGE.SET_TOKEN('MSG_TEXT',sqlerrm);
2690 fnd_msg_pub.add;
2691 END CREATE_ADDRESS;
2692
2693
2694 -- Start of comments
2695 -- API name : Get_message
2696 -- Type : Private
2697 -- Pre-reqs : None.
2698 -- Procedure : API to get fnd stack messages and store in local message table.
2699 -- These messages are out to Supplier Address Book UI.
2700 --
2701 -- Parameters :
2702 -- IN:
2703 -- None
2704 -- OUT:
2705 -- None
2706 -- End of comments
2707 PROCEDURE Get_message IS
2708
2709 l_debug_on BOOLEAN;
2710 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Get_Message';
2711 l_msg varchar2(23767);
2712
2713 BEGIN
2714 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2715 IF l_debug_on IS NULL THEN
2716 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2717 END IF;
2718
2719 IF l_debug_on THEN
2720 WSH_DEBUG_SV.push(l_module_name);
2721 END IF;
2722
2723 FND_MESSAGE.SET_NAME('WSH','WSH_SAB_ADDRESS_ERROR');
2724 FND_MESSAGE.SET_TOKEN('LINE_NUMBER',g_line_number);
2725 fnd_msg_pub.add;
2726
2727 IF l_debug_on THEN
2728 WSH_DEBUG_SV.log(l_module_name,'Message Count:',FND_MSG_PUB.Count_Msg);
2729 END IF;
2730
2731
2732 FOR i IN 1..FND_MSG_PUB.Count_Msg LOOP
2733 l_msg := FND_MSG_PUB.get(i, FND_API.G_FALSE);
2734 IF l_debug_on THEN
2735 WSH_DEBUG_SV.log(l_module_name,'Stack Message :',l_msg);
2736 END IF;
2737
2738 g_error_tbl(g_error_tbl.count + 1) := l_msg;
2739 END LOOP;
2740
2741 IF l_debug_on THEN
2742 WSH_DEBUG_SV.log(l_module_name,'After Message Count:',FND_MSG_PUB.Count_Msg);
2743 END IF;
2744
2745 --Initialized the FND message to avoid duplicate message being
2746 --inserted to local message table.
2747 FND_MSG_PUB.initialize;
2748
2749 IF l_debug_on THEN
2750 WSH_DEBUG_SV.pop(l_module_name);
2751 END IF;
2752 EXCEPTION
2753 WHEN OTHERS THEN
2754 IF l_debug_on THEN
2755 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||
2756 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2757 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2758 END IF;
2759
2760 FND_MESSAGE.SET_NAME('WSH','WSH_UTIL_MESSAGE_U');
2761 FND_MESSAGE.SET_TOKEN('MSG_TEXT',sqlerrm);
2762 g_error_tbl(g_error_tbl.count + 1) := FND_MESSAGE.GET;
2763 END Get_message;
2764
2765
2766 -- Start of comments
2767 -- API name : Process_Address
2768 -- Type : Public
2769 -- Pre-reqs : None.
2770 -- Procedure : API to Create/Update address line information of Supplier Address book. Api does.
2771 -- 1.Validate the action code.
2772 -- 2.Check if Address information is already exists for Shipping Code
2773 -- and Supplier.
2774 -- 3.If address information is exist,validate that action code
2775 -- should not be insert 'I'. Than call api Update_address to update
2776 -- address information.
2777 -- 4.If address information is not exists ,validate that
2778 -- action code should not be update 'U'. Than call api
2779 -- Create_address to update address information.
2780 --
2781 -- Parameters :
2782 -- IN:
2783 -- p_in_param IN Hold additional parameter as passed by UI.
2784 -- p_Address IN Hold Supplier Address book record as passed by UI
2785 -- OUT:
2786 -- x_success_tbl OUT NOCOPY List of Success messages passed back to UI for display.
2787 -- x_error_tbl OUT NOCOPY List of Error messages passed back to UI for display.
2788 -- x_return_status OUT NOCOPY Standard to output api status.
2789 -- End of comments
2790 PROCEDURE Process_Address(
2791 p_in_param IN WSH_ROUTING_REQUEST.In_param_Rec_Type,
2792 p_Address IN WSH_ROUTING_REQUEST.Address_rec_type,
2793 x_success_tbl IN OUT NOCOPY WSH_FILE_MSG_TABLE,
2794 x_error_tbl IN OUT NOCOPY WSH_ROUTING_REQUEST.tbl_var2000,
2795 x_return_status IN OUT NOCOPY varchar2) IS
2796
2797 l_debug_on BOOLEAN;
2798 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Process_Address';
2799 l_return_status varchar2(1);
2800
2801 --Cursor to find existing address information in TCA.
2802 CURSOR check_location_csr(p_location_code varchar2,p_party_id number) IS
2803 SELECT ps.location_id,ps.party_site_id,ps.status -- IB-Phase-2 Vendor Merge
2804 FROM hz_party_sites ps,hz_party_site_uses psu
2805 WHERE ps.party_site_id = psu.party_site_id
2806 AND psu.site_use_type = 'SUPPLIER_SHIP_FROM'
2807 and party_site_number=p_location_code||'|'||p_party_id
2808 and party_id =p_party_id;
2809
2810 l_msg_count number:= 0;
2811 l_index number;
2812 l_num_errors number;
2813 l_num_warning number;
2814 l_tot_line number;
2815
2816 l_vendor_id number;
2817 l_party_id number;
2818 l_location_id number;
2819 l_party_site_id number;
2820 l_party_site_uses_id number;
2821 l_party_site_status varchar2(1);
2822 l_party_site_msg varchar2(1000);
2823
2824 BEGIN
2825 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2826 IF l_debug_on IS NULL THEN
2827 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2828 END IF;
2829
2830 IF l_debug_on THEN
2831 WSH_DEBUG_SV.push(l_module_name);
2832 WSH_DEBUG_SV.log(l_module_name,'P_Address.count',P_Address.supplier_name.count);
2833 WSH_DEBUG_SV.log(l_module_name,'x_error_tbl.count',x_error_tbl.count);
2834 END IF;
2835
2836 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2837 l_num_errors := 0;
2838 l_num_warning := 0;
2839 --Initilize success and error message table.
2840 x_success_tbl := WSH_FILE_MSG_TABLE();
2841 g_error_tbl.delete;
2842
2843
2844 l_tot_line := P_Address.supplier_name.count;
2845
2846 --Loop through address lines.
2847 l_index := P_Address.supplier_name.first;
2848 WHILE (l_index IS NOT NULL ) LOOP --{
2849 BEGIN
2850 g_line_number:= l_index;
2851
2852 --Check for validate action code.
2853 IF (P_Address.action(l_index) NOT IN ('I','U') ) THEN
2854 FND_MESSAGE.SET_NAME('WSH','WSH_RR_INV_ACTION');
2855 FND_MESSAGE.SET_TOKEN('LINE_NUMBER',l_index);
2856 fnd_msg_pub.add;
2857
2858 wsh_util_core.api_post_call(p_return_status =>WSH_UTIL_CORE.G_RET_STS_ERROR,
2859 x_num_warnings =>l_num_warning,
2860 x_num_errors =>l_num_errors);
2861 END IF;
2862
2863 IF (P_Address.error_flag(l_index) = 'Y' ) THEN
2864 wsh_util_core.api_post_call(p_return_status =>WSH_UTIL_CORE.G_RET_STS_ERROR,
2865 x_num_warnings =>l_num_warning,
2866 x_num_errors =>l_num_errors);
2867 END IF;
2868
2869 --Validate Supplier Information.
2870 Validate_Supplier(
2871 p_in_param => p_in_param,
2872 p_supplier_name => p_Address.supplier_name(l_index),
2873 x_vendor_id => l_vendor_id,
2874 x_party_id => l_party_id,
2875 x_return_status => l_return_status);
2876
2877 IF l_debug_on THEN
2878 WSH_DEBUG_SV.log(l_module_name,'Validate_Supplier l_return_status',l_return_status);
2879 WSH_DEBUG_SV.log(l_module_name,'l_vendor_id',l_vendor_id);
2880 WSH_DEBUG_SV.log(l_module_name,'l_party_id',l_party_id);
2881 END IF;
2882 wsh_util_core.api_post_call(p_return_status =>l_return_status,
2883 x_num_warnings =>l_num_warning,
2884 x_num_errors =>l_num_errors);
2885
2886
2887 --Find the existing address information in TCA.
2888 OPEN check_location_csr(p_Address.ship_from_code(l_index),l_party_id);
2889 FETCH check_location_csr INTO l_location_id,l_party_site_id,l_party_site_status;-- IB-phase-2
2890
2891 IF (check_location_csr%FOUND) THEN --{
2892 IF l_debug_on THEN
2893 WSH_DEBUG_SV.log(l_module_name,'Location Found action',P_Address.action(l_index));
2894 END IF;
2895
2896 --For existing record action should not be Insert.
2897 IF (P_Address.action(l_index) = 'I' ) THEN
2898 FND_MESSAGE.SET_NAME('WSH','WSH_RR_INV_ACTION');
2899 FND_MESSAGE.SET_TOKEN('LINE_NUMBER',l_index);
2900 fnd_msg_pub.add;
2901 wsh_util_core.api_post_call(p_return_status =>WSH_UTIL_CORE.G_RET_STS_ERROR,
2902 x_num_warnings =>l_num_warning,
2903 x_num_errors =>l_num_errors);
2904 END IF;
2905
2906 -- { IB-Phase-2
2907 -- If Party Site is not Active then error out, with a suitable message.
2908 IF l_party_site_status <> 'A'
2909 THEN
2910 l_party_site_msg := p_Address.ship_from_code(l_index);
2911 FND_MESSAGE.SET_NAME('WSH','WSH_INACTIVE_PARTY_SITE');
2912 FND_MESSAGE.SET_TOKEN('PARTY_SITE',l_party_site_msg);
2913 fnd_msg_pub.add;
2914 l_num_errors := l_num_errors + 1;
2915 raise FND_API.G_EXC_ERROR;
2916 END IF;
2917 -- } IB-Phase-2
2918
2919 Update_address(
2920 P_location_id => l_location_id,
2921 P_party_id => l_party_id,
2922 P_party_site_id => l_party_site_id,
2923 P_address1 => p_Address.ship_from_address1(l_index),
2924 P_address2 => p_Address.ship_from_address2(l_index),
2925 P_address3 => p_Address.ship_from_address3(l_index),
2926 P_address4 => p_Address.ship_from_address4(l_index),
2927 P_city => p_Address.ship_from_city(l_index),
2928 P_postal_code => p_Address.ship_from_postal_code(l_index),
2929 P_state => p_Address.ship_from_state(l_index),
2930 P_province => p_Address.ship_from_province(l_index),
2931 P_county => p_Address.ship_from_county(l_index),
2932 p_country => p_Address.ship_from_country(l_index),
2933 p_shipper_name => p_Address.shipper_name(l_index),
2934 p_phone => p_Address.phone(l_index),
2935 p_email => p_Address.email(l_index),
2936 x_return_status => l_return_status);
2937
2938 IF l_debug_on THEN
2939 WSH_DEBUG_SV.log(l_module_name,'Update_Address l_return_status',l_return_status);
2940 END IF;
2941
2942 wsh_util_core.api_post_call(p_return_status =>l_return_status,
2943 x_num_warnings =>l_num_warning,
2944 x_num_errors =>l_num_errors);
2945
2946 ELSE --}{
2947 IF l_debug_on THEN
2948 WSH_DEBUG_SV.log(l_module_name,'Location NOT Found action',P_Address.action(l_index));
2949 END IF;
2950 IF (P_Address.action(l_index) = 'U' ) THEN
2951
2952 --For new record action should not be Update.
2953 FND_MESSAGE.SET_NAME('WSH','WSH_RR_INV_ACTION');
2954 FND_MESSAGE.SET_TOKEN('LINE_NUMBER',l_index);
2955 fnd_msg_pub.add;
2956
2957 wsh_util_core.api_post_call(p_return_status =>WSH_UTIL_CORE.G_RET_STS_ERROR,
2958 x_num_warnings =>l_num_warning,
2959 x_num_errors =>l_num_errors);
2960 END IF;
2961
2962 Create_Address(
2963 P_vendor_id => l_vendor_id,
2964 P_party_id => l_party_id,
2965 P_location_code => p_Address.ship_from_code(l_index),
2966 P_address1 => p_Address.ship_from_address1(l_index),
2967 P_address2 => p_Address.ship_from_address2(l_index),
2968 P_address3 => p_Address.ship_from_address3(l_index),
2969 P_address4 => p_Address.ship_from_address4(l_index),
2970 P_city => p_Address.ship_from_city(l_index),
2971 P_postal_code => p_Address.ship_from_postal_code(l_index),
2972 P_state => p_Address.ship_from_state(l_index),
2973 P_province => p_Address.ship_from_province(l_index),
2974 P_county => p_Address.ship_from_county(l_index),
2975 p_country => p_Address.ship_from_country(l_index),
2976 p_shipper_name => p_Address.shipper_name(l_index),
2977 p_phone => p_Address.phone(l_index),
2978 p_email => p_Address.email(l_index),
2979 x_location_id => l_location_id,
2980 x_party_site_id => l_party_site_id,
2981 x_return_status => l_return_status);
2982
2983
2984 IF l_debug_on THEN
2985 WSH_DEBUG_SV.log(l_module_name,'Create_Address l_return_status',l_return_status);
2986 WSH_DEBUG_SV.log(l_module_name,'l_location_id',l_location_id);
2987 WSH_DEBUG_SV.log(l_module_name,'l_party_site_id',l_party_site_id);
2988 END IF;
2989
2990 wsh_util_core.api_post_call(p_return_status =>l_return_status,
2991 x_num_warnings =>l_num_warning,
2992 x_num_errors =>l_num_errors);
2993 END IF; --}
2994
2995 CLOSE check_location_csr;
2996
2997 FND_MESSAGE.SET_NAME('WSH','WSH_SAB_ADDRESS_SUCCESS');
2998 FND_MESSAGE.SET_TOKEN('LINE_NUMBER',l_index);
2999 l_msg_count := l_msg_count + 1;
3000 x_success_tbl.extend;
3001 x_success_tbl(l_msg_count):=FND_MESSAGE.Get;
3002
3003 EXCEPTION
3004 WHEN FND_API.G_EXC_ERROR THEN
3005 IF l_debug_on THEN
3006 wsh_debug_sv.log (l_module_name,'G_EXC_ERROR in the loop');
3007 END IF;
3008 get_message;
3009
3010 IF (check_location_csr%ISOPEN) THEN
3011 CLOSE check_location_csr;
3012 END IF;
3013
3014 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3015 IF l_debug_on THEN
3016 wsh_debug_sv.log (l_module_name, 'G_EXC_UNEXPECTED_ERROR in the loop');
3017 END IF;
3018 get_message;
3019
3020 IF (check_location_csr%ISOPEN) THEN
3021 CLOSE check_location_csr;
3022 END IF;
3023 END;
3024
3025 l_index := P_Address.supplier_name.next(l_index);
3026 END LOOP; --}
3027
3028
3029 IF (l_num_errors >= l_tot_line ) THEN
3030 --Error if all the lines are error.
3031 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR ;
3032 ELSIF ( (l_num_errors > 0 and l_num_errors < l_tot_line) or (l_num_warning > 0 ) ) THEN
3033 --Warning , if error line is more than one and less than total number of address line.
3034 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING ;
3035 END IF;
3036
3037
3038 --Collect all the error message. Error messages are inserted in
3039 --after all the success messages.
3040 l_index:= g_error_tbl.first;
3041 WHILE (l_index IS NOT NULL) LOOP
3042 x_error_tbl(x_error_tbl.count + 1) := g_error_tbl(l_index);
3043
3044 l_index:= g_error_tbl.next(l_index);
3045 END LOOP;
3046
3047
3048
3049 IF l_debug_on THEN
3050 WSH_DEBUG_SV.pop(l_module_name);
3051 END IF;
3052 EXCEPTION
3053 WHEN OTHERS THEN
3054 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3055
3056 IF l_debug_on THEN
3057 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||
3058 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3059 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3060 END IF;
3061
3062 FND_MESSAGE.SET_NAME('WSH','WSH_UTIL_MESSAGE_U');
3063 FND_MESSAGE.SET_TOKEN('MSG_TEXT',sqlerrm);
3064 fnd_msg_pub.add;
3065 END Process_Address;
3066
3067
3068 END WSH_SUPPLIER_PARTY;