[Home] [Help]
PACKAGE BODY: APPS.HZ_TAX_ASSIGNMENT_V2PUB
Source
1 PACKAGE BODY HZ_TAX_ASSIGNMENT_V2PUB AS
2 /* $Header: ARH2TASB.pls 120.20 2005/11/18 18:13:42 baianand noship $ */
3
4 --------------------------------------
5 -- declaration of private global varibles
6 --------------------------------------
7
8 --G_DEBUG BOOLEAN := FALSE;
9
10 --------------------------------------
11 -- declaration of private procedures and functions
12 --------------------------------------
13
14 /*PROCEDURE enable_debug;
15
16 PROCEDURE disable_debug;
17 */
18
19 PROCEDURE do_create_loc_assignment(
20 p_location_id IN NUMBER,
21 x_loc_id OUT NOCOPY NUMBER,
22 x_return_status IN OUT NOCOPY VARCHAR2, /* Changed from OUT NOCOPY to IN OUT*/
23 p_lock_flag IN VARCHAR2 := FND_API.G_FALSE,
24 p_created_by_module IN VARCHAR2,
25 p_application_id IN NUMBER
26 );
27
28 -- Removed following parameters since it id not used in the procedure
29 -- x_loc_id, p_created_by_module, p_application_id, x_org_id
30 -- Added p_do_addr_val, x_addr_val_status and x_addr_warn_msg for address validation.
31 PROCEDURE do_update_loc_assignment(
32 p_location_id IN NUMBER,
33 p_do_addr_val IN VARCHAR2,
34 x_addr_val_status OUT NOCOPY VARCHAR2,
35 x_addr_warn_msg OUT NOCOPY VARCHAR2,
36 x_return_status IN OUT NOCOPY VARCHAR2, /* Changed from OUT NOCOPY to IN OUT*/
37 p_lock_flag IN VARCHAR2 := FND_API.G_TRUE
38 );
39
40 --------------------------------------
41 -- private procedures and functions
42 --------------------------------------
43
44 /**
45 * PRIVATE PROCEDURE enable_debug
46 *
47 * DESCRIPTION
48 * Turn on debug mode.
49 *
50 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
51 * HZ_UTILITY_V2PUB.enable_debug
52 *
53 * MODIFICATION HISTORY
54 *
55 * 07-23-2001 Jianying Huang o Created.
56 *
57 */
58
59 /*PROCEDURE enable_debug IS
60
61 BEGIN
62
63 IF FND_PROFILE.value( 'HZ_API_FILE_DEBUG_ON' ) = 'Y' OR
64 FND_PROFILE.value( 'HZ_API_DBMS_DEBUG_ON' ) = 'Y'
65 THEN
66 HZ_UTILITY_V2PUB.enable_debug;
67 G_DEBUG := TRUE;
68 END IF;
69
70 END enable_debug;
71 */
72
73 /**
74 * PRIVATE PROCEDURE disable_debug
75 *
76 * DESCRIPTION
77 * Turn off debug mode.
78 *
79 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
80 * HZ_UTILITY_V2PUB.disable_debug
81 *
82 * MODIFICATION HISTORY
83 *
84 * 07-23-2001 Jianying Huang o Created.
85 *
86 */
87
88 /*PROCEDURE disable_debug IS
89
90 BEGIN
91
92 IF G_DEBUG THEN
93 HZ_UTILITY_V2PUB.disable_debug;
94 G_DEBUG := FALSE;
95 END IF;
96
97 END disable_debug;
98 */
99
100 /**==========================================================================+
101 | PROCEDURE
102 | do_create_loc_assignment
103 |
104 | DESCRIPTION
105 | Creates loc assignments
106 |
107 | SCOPE - PRIVATE
108 |
109 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
110 |
111 | ARGUMENTS : IN:
112 | p_location_id
113 | p_lock_flag
114 | OUT:
115 | x_loc_id
116 | IN/ OUT:
117 | x_return_status
118 |
119 | RETURNS : NONE
120 |
121 | NOTES
122 |
123 | MODIFICATION HISTORY
124 |
125 * 05-29-2003 Ramesh Ch o Bug 2800555.Initialized arp_standard.sysparm with
126 * ar_system_parameters row values.
127 * 01-30-2004 Rajib Ranjan Borah o Bug 3395521.
128 * o Modified IF clause to handle NULL
129 * o Passed address1 to address4 to update_profile_pvt
130 *
131 +===========================================================================**/
132
133 PROCEDURE do_create_loc_assignment(
134 p_location_id IN NUMBER,
135 x_loc_id OUT NOCOPY NUMBER,
136 x_return_status IN OUT NOCOPY VARCHAR2, /* Changed from OUT NOCOPY to IN OUT*/
137 p_lock_flag IN VARCHAR2 := FND_API.G_FALSE,
138 p_created_by_module IN VARCHAR2,
139 p_application_id IN NUMBER
140 ) IS
141
142 l_org_id NUMBER;
143 l_count NUMBER;
144 l_rowid ROWID := NULL;
145
146 l_is_remit_to_location VARCHAR2(1) := 'N';
147 l_return_status VARCHAR2(30);
148 l_addr_val_status VARCHAR2(30);
149 l_addr_warn_msg VARCHAR2(2000);
150 l_addr_val_level VARCHAR2(30);
151 l_msg_count NUMBER;
152 l_msg_data VARCHAR2(2000);
153
154 CURSOR c_loc (p_location_id in number) IS
155 SELECT
156 LOCATION_ID,
157 ADDRESS_STYLE,
158 COUNTRY,
159 STATE,
160 PROVINCE,
161 COUNTY,
162 CITY,
163 POSTAL_CODE,
164 POSTAL_PLUS4_CODE,
165 ATTRIBUTE1,
166 ATTRIBUTE2,
167 ATTRIBUTE3,
168 ATTRIBUTE4,
169 ATTRIBUTE5,
170 ATTRIBUTE6,
171 ATTRIBUTE7,
172 ATTRIBUTE8,
173 ATTRIBUTE9,
174 ATTRIBUTE10
175 FROM HZ_LOCATIONS WHERE LOCATION_ID = p_location_id;
176
177
178 BEGIN
179
180 -- check the required fields:
181 IF p_location_id IS NULL
182 OR
183 p_location_id = FND_API.G_MISS_NUM
184 THEN
185 FND_MESSAGE.SET_NAME('AR', 'HZ_API_MISSING_COLUMN');
186 FND_MESSAGE.SET_TOKEN('COLUMN', 'p_location_id');
187 FND_MSG_PUB.ADD;
188 RAISE FND_API.G_EXC_ERROR;
189 END IF;
190
191 -- checking whether this location is for Remit-To Address or not
192 BEGIN
193 SELECT 'Y'
194 INTO l_is_remit_to_location
195 FROM DUAL
196 WHERE EXISTS ( SELECT 1
197 FROM HZ_PARTY_SITES PS
198 WHERE PS.LOCATION_ID = p_location_id
199 AND PS.PARTY_ID = -1);
200 EXCEPTION
201 WHEN NO_DATA_FOUND THEN
202 NULL;
203 END;
204
205 IF l_is_remit_to_location <> 'Y' THEN
206 FOR l_c_loc in c_loc(p_location_id) LOOP
207 HZ_GNR_PKG.validateLoc(
208 P_LOCATION_ID => l_c_loc.LOCATION_ID,
209 P_USAGE_CODE => 'GEOGRAPHY',
210 P_ADDRESS_STYLE => l_c_loc.ADDRESS_STYLE,
211 P_COUNTRY => l_c_loc.COUNTRY,
212 P_STATE => l_c_loc.STATE,
213 P_PROVINCE => l_c_loc.PROVINCE,
214 P_COUNTY => l_c_loc.COUNTY,
215 P_CITY => l_c_loc.CITY,
216 P_POSTAL_CODE => l_c_loc.POSTAL_CODE,
217 P_POSTAL_PLUS4_CODE => l_c_loc.POSTAL_PLUS4_CODE,
218 P_ATTRIBUTE1 => l_c_loc.ATTRIBUTE1,
219 P_ATTRIBUTE2 => l_c_loc.ATTRIBUTE2,
220 P_ATTRIBUTE3 => l_c_loc.ATTRIBUTE3,
221 P_ATTRIBUTE4 => l_c_loc.ATTRIBUTE4,
222 P_ATTRIBUTE5 => l_c_loc.ATTRIBUTE5,
223 P_ATTRIBUTE6 => l_c_loc.ATTRIBUTE6,
224 P_ATTRIBUTE7 => l_c_loc.ATTRIBUTE7,
225 P_ATTRIBUTE8 => l_c_loc.ATTRIBUTE8,
226 P_ATTRIBUTE9 => l_c_loc.ATTRIBUTE9,
227 P_ATTRIBUTE10 => l_c_loc.ATTRIBUTE10,
228 P_LOCK_FLAG => p_lock_flag,
229 P_CALLED_FROM => 'VALIDATE',
230 X_ADDR_VAL_LEVEL => l_addr_val_level,
231 X_ADDR_WARN_MSG => l_addr_warn_msg,
232 X_ADDR_VAL_STATUS => l_addr_val_status,
233 X_STATUS => x_return_status);
234 END LOOP;
235 END IF;
236
237 END do_create_loc_assignment;
238
239
240 /**==========================================================================+
241 | PROCEDURE
242 | do_update_loc_assignment
243 |
244 | DESCRIPTION
245 | Updates loc assignments
246 |
247 | SCOPE - PRIVATE
248 |
249 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
250 |
251 | ARGUMENTS : IN:
252 | p_location_id
253 | p_lock_flag
254 | OUT:
255 | x_loc_id
256 | IN/ OUT:
257 | x_return_status
258 |
259 | RETURNS : NONE
260 |
261 | NOTES
262 |
263 | MODIFICATION HISTORY
264 |
265 * 05-29-2003 Ramesh Ch o Bug 2800555.Initialized arp_standard.sysparm with
266 * ar_system_parameters row values.
267 * 01-30-2004 Rajib Ranjan Borah o Bug 3395521.
268 * o Modified IF clause to handle NULL
269 * o Passed address1 to address4 to update_profile_pvt
270 * o Moved the call to update_location_pvt to outside
271 * the loop.
272 * o Updated the db_* variables to prevent unnecessary
273 * additional updates to HZ_LOCATIONS.
274 *
275 +===========================================================================**/
276 -- Removed following parameters since it id not used in the procedure
277 -- x_loc_id, p_created_by_module, p_application_id, x_org_id
278 -- Added p_do_addr_val, x_addr_val_status and x_addr_warn_msg for address validation.
279 PROCEDURE do_update_loc_assignment(
280 p_location_id IN NUMBER,
281 p_do_addr_val IN VARCHAR2,
282 x_addr_val_status OUT NOCOPY VARCHAR2,
283 x_addr_warn_msg OUT NOCOPY VARCHAR2,
284 x_return_status IN OUT NOCOPY VARCHAR2, /* Changed from OUT NOCOPY to IN OUT*/
285 p_lock_flag IN VARCHAR2 := FND_API.G_TRUE
286 ) IS
287
288 l_is_remit_to_location VARCHAR2(1) := 'N';
289 l_loc_assg_exists VARCHAR2(1) := 'N';
290 l_return_status VARCHAR2(30);
291 l_addr_val_level VARCHAR2(30);
292 l_msg_count NUMBER;
293 l_msg_data VARCHAR2(2000);
294 l_allow_update_std VARCHAR2(1);
295 l_date_validated DATE;
296 l_validation_status_code VARCHAR2(30);
297
298 l_msg_count_gnr NUMBER;
299 l_msg_data_gnr VARCHAR2(2000);
300
301 CURSOR c_loc (p_location_id in number) IS
302 SELECT
303 LOCATION_ID,
304 ADDRESS_STYLE,
305 COUNTRY,
306 STATE,
307 PROVINCE,
308 COUNTY,
309 CITY,
310 POSTAL_CODE,
311 POSTAL_PLUS4_CODE,
312 ATTRIBUTE1,
313 ATTRIBUTE2,
314 ATTRIBUTE3,
315 ATTRIBUTE4,
316 ATTRIBUTE5,
317 ATTRIBUTE6,
318 ATTRIBUTE7,
319 ATTRIBUTE8,
320 ATTRIBUTE9,
321 ATTRIBUTE10
322 FROM HZ_LOCATIONS WHERE LOCATION_ID = p_location_id;
323
324 BEGIN
325
326 -- check the required fields:
327 IF p_location_id IS NULL
328 OR
329 p_location_id = FND_API.G_MISS_NUM
330 THEN
331 FND_MESSAGE.SET_NAME('AR', 'HZ_API_MISSING_COLUMN');
332 FND_MESSAGE.SET_TOKEN('COLUMN', 'p_location_id');
333 FND_MSG_PUB.ADD;
334 RAISE FND_API.G_EXC_ERROR;
335 END IF;
336
337 BEGIN
338 SELECT DATE_VALIDATED, VALIDATION_STATUS_CODE
339 INTO l_date_validated, l_validation_status_code
340 FROM HZ_LOCATIONS
341 WHERE LOCATION_ID = p_location_id;
342 EXCEPTION
343 WHEN NO_DATA_FOUND THEN
344 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NO_RECORD');
345 FND_MESSAGE.SET_TOKEN('RECORD', 'hz_locations');
346 FND_MESSAGE.SET_TOKEN('VALUE', to_char(p_location_id));
347 FND_MSG_PUB.ADD;
348 x_return_status := FND_API.G_RET_STS_ERROR;
349 END;
350
351 -- raise error if the update location profile option is turned off and
352 -- the address has been validated before
353 l_allow_update_std := nvl(fnd_profile.value('HZ_UPDATE_STD_ADDRESS'), 'Y');
354 IF(l_allow_update_std = 'N' AND
355 l_date_validated IS NOT NULL AND
356 l_validation_status_code IS NOT NULL)
357 THEN
358 FND_MESSAGE.SET_NAME('AR', 'HZ_LOC_NO_UPDATE');
359 FND_MSG_PUB.ADD;
360 RAISE fnd_api.g_exc_error;
361 END IF;
362
363 -- checking whether this location is for Remit-To Address or not
364 BEGIN
365 SELECT 'Y'
366 INTO l_is_remit_to_location
367 FROM DUAL
368 WHERE EXISTS ( SELECT 1
369 FROM HZ_PARTY_SITES PS
370 WHERE PS.LOCATION_ID = p_location_id
371 AND PS.PARTY_ID = -1);
372 EXCEPTION
373 WHEN NO_DATA_FOUND THEN
374 NULL;
375 END;
376
377 -- checking whether this location is already validated
378 BEGIN
379 SELECT 'Y'
380 INTO l_loc_assg_exists
381 FROM DUAL
382 WHERE EXISTS ( SELECT 1
383 FROM HZ_LOC_ASSIGNMENTS
384 WHERE LOCATION_ID = p_location_id);
385 EXCEPTION
386 WHEN NO_DATA_FOUND THEN
387 NULL;
388 END;
389
390 IF l_is_remit_to_location <> 'Y' OR p_do_addr_val = 'Y' THEN
391 FOR l_c_loc in c_loc(p_location_id) LOOP
392 IF l_loc_assg_exists = 'Y' OR p_do_addr_val = 'Y' THEN
393
394 HZ_GNR_PKG.delete_gnr(
395 p_locId => p_location_id,
396 p_locTbl => 'HZ_LOCATIONS',
397 x_status => x_return_status);
398
399 HZ_GNR_PKG.validateLoc(
400 P_LOCATION_ID => l_c_loc.LOCATION_ID,
401 P_USAGE_CODE => 'GEOGRAPHY',
402 P_ADDRESS_STYLE => l_c_loc.ADDRESS_STYLE,
403 P_COUNTRY => l_c_loc.COUNTRY,
404 P_STATE => l_c_loc.STATE,
405 P_PROVINCE => l_c_loc.PROVINCE,
406 P_COUNTY => l_c_loc.COUNTY,
407 P_CITY => l_c_loc.CITY,
408 P_POSTAL_CODE => l_c_loc.POSTAL_CODE,
409 P_POSTAL_PLUS4_CODE => l_c_loc.POSTAL_PLUS4_CODE,
410 P_ATTRIBUTE1 => l_c_loc.ATTRIBUTE1,
411 P_ATTRIBUTE2 => l_c_loc.ATTRIBUTE2,
412 P_ATTRIBUTE3 => l_c_loc.ATTRIBUTE3,
413 P_ATTRIBUTE4 => l_c_loc.ATTRIBUTE4,
414 P_ATTRIBUTE5 => l_c_loc.ATTRIBUTE5,
415 P_ATTRIBUTE6 => l_c_loc.ATTRIBUTE6,
416 P_ATTRIBUTE7 => l_c_loc.ATTRIBUTE7,
417 P_ATTRIBUTE8 => l_c_loc.ATTRIBUTE8,
418 P_ATTRIBUTE9 => l_c_loc.ATTRIBUTE9,
419 P_ATTRIBUTE10 => l_c_loc.ATTRIBUTE10,
420 P_LOCK_FLAG => p_lock_flag,
421 P_CALLED_FROM => 'VALIDATE',
422 X_ADDR_VAL_LEVEL => l_addr_val_level,
423 X_ADDR_WARN_MSG => x_addr_warn_msg,
424 X_ADDR_VAL_STATUS => x_addr_val_status,
425 X_STATUS => x_return_status);
426 ELSE
427 -- Below code will execute only if there is no record in hz_loc_assignments table
428 -- process_gnr will process the GNR and return success even if there is an error
429 -- in the validation.
430 HZ_GNR_PUB.process_gnr (
431 p_location_table_name => 'HZ_LOCATIONS',
432 p_location_id => p_location_id,
433 p_call_type => 'U',
434 p_init_msg_list => 'F',
435 x_return_status => x_return_status,
436 x_msg_count => l_msg_count_gnr,
437 x_msg_data => l_msg_data_gnr);
438
439 END IF;
440 END LOOP;
441 END IF;
442
443 END do_update_loc_assignment;
444
445 --------------------------------------
446 -- public procedures and functions
447 --------------------------------------
448
449 /**
450 * PROCEDURE create_loc_assignment
451 *
452 * DESCRIPTION
453 * Creates location assignment.
454 *
455 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
456 *
457 * ARGUMENTS
458 * IN:
459 * p_init_msg_list Initialize message stack if it is set to
460 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
461 * p_location_id Location ID.
462 * p_lock_flag Lock record or not. Default is FND_API.G_FALSE.
463 * p_created_by_module Module name which creates this record.
464 * p_application_id Application ID which creates this record.
465 * IN/OUT:
466 * OUT:
467 * x_return_status Return status after the call. The status can
468 * be FND_API.G_RET_STS_SUCCESS (success),
469 * FND_API.G_RET_STS_ERROR (error),
470 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
471 * x_msg_count Number of messages in message stack.
472 * x_msg_data Message text if x_msg_count is 1.
473 * x_loc_id Location assignment ID.
474 *
475 * NOTES
476 *
477 * MODIFICATION HISTORY
478 *
479 * 07-23-2001 Indrajit Sen o Created.
480 *
481 */
482
483 PROCEDURE create_loc_assignment(
484 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
485 p_location_id IN NUMBER,
486 p_lock_flag IN VARCHAR2 :=FND_API.G_FALSE,
487 p_created_by_module IN VARCHAR2,
488 p_application_id IN NUMBER,
489 x_return_status IN OUT NOCOPY VARCHAR2,
490 x_msg_count OUT NOCOPY NUMBER,
491 x_msg_data OUT NOCOPY VARCHAR2,
492 x_loc_id OUT NOCOPY NUMBER
493 ) IS
494
495 l_location_id NUMBER := p_location_id;
496 APP_EXCEPTION EXCEPTION;
497 PRAGMA EXCEPTION_INIT(APP_EXCEPTION, -20000);
498
499 BEGIN
500
501 -- standard start of API savepoint
502 SAVEPOINT create_loc_assignment;
503
504 -- initialize message list if p_init_msg_list is set to TRUE.
505 IF FND_API.to_Boolean(p_init_msg_list) THEN
506 FND_MSG_PUB.initialize;
507 END IF;
508
509 -- initialize API return status to success.
510 x_return_status := FND_API.G_RET_STS_SUCCESS;
511
512 -- call to business logic.
513 -- populate loc_id by calling tax package.
514 do_create_loc_assignment( p_location_id,
515 x_loc_id,
516 x_return_status,
517 p_lock_flag,
518 p_created_by_module,
519 p_application_id);
520
521 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
522 RAISE FND_API.G_EXC_ERROR;
523 END IF;
524
525 -- standard call to get message count and if count is 1, get message info.
526 FND_MSG_PUB.Count_And_Get(
527 p_encoded => FND_API.G_FALSE,
528 p_count => x_msg_count,
529 p_data => x_msg_data);
530
531 EXCEPTION
532 WHEN FND_API.G_EXC_ERROR THEN
533 ROLLBACK TO create_loc_assignment;
534 x_return_status := FND_API.G_RET_STS_ERROR;
535 FND_MSG_PUB.Count_And_Get(
536 p_encoded => FND_API.G_FALSE,
537 p_count => x_msg_count,
538 p_data => x_msg_data);
539
540 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
541 ROLLBACK TO create_loc_assignment;
542 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
543 FND_MSG_PUB.Count_And_Get(
544 p_encoded => FND_API.G_FALSE,
545 p_count => x_msg_count,
546 p_data => x_msg_data);
547
548 WHEN APP_EXCEPTION THEN
549 ROLLBACK TO create_loc_assignment;
550 x_return_status := FND_API.G_RET_STS_ERROR;
551 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
552 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
553 FND_MSG_PUB.ADD;
554 FND_MSG_PUB.Count_And_Get(
555 p_encoded => FND_API.G_FALSE,
556 p_count => x_msg_count,
557 p_data => x_msg_data);
558 WHEN OTHERS THEN
559 ROLLBACK TO create_loc_assignment;
560 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
561 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
562 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
563 FND_MSG_PUB.ADD;
564 FND_MSG_PUB.Count_And_Get(
565 p_encoded => FND_API.G_FALSE,
566 p_count => x_msg_count,
567 p_data => x_msg_data);
568
569 END create_loc_assignment;
570
571 /**
572 * PROCEDURE update_loc_assignment
573 *
574 * DESCRIPTION
575 * Updates location assignment.
576 *
577 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
578 *
579 * ARGUMENTS
580 * IN:
581 * p_init_msg_list Initialize message stack if it is set to
582 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
583 * p_location_id Location ID.
584 * p_lock_flag Lock record or not. Default is FND_API.G_TRUE.
585 * p_created_by_module Module name which creates this record.
586 * p_application_id Application ID which creates this record.
587 * IN/OUT:
588 * OUT:
589 * x_return_status Return status after the call. The status can
590 * be FND_API.G_RET_STS_SUCCESS (success),
591 * FND_API.G_RET_STS_ERROR (error),
592 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
593 * x_msg_count Number of messages in message stack.
594 * x_msg_data Message text if x_msg_count is 1.
595 * x_loc_id Location assignment ID.
596 *
597 * NOTES
598 *
599 * MODIFICATION HISTORY
600 *
601 * 07-23-2001 Indrajit Sen o Created.
602 *
603 */
604
605 PROCEDURE update_loc_assignment(
606 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
607 p_location_id IN NUMBER,
608 p_lock_flag IN VARCHAR2 :=FND_API.G_TRUE,
609 p_created_by_module IN VARCHAR2,
610 p_application_id IN NUMBER,
611 x_return_status IN OUT NOCOPY VARCHAR2,
612 x_msg_count OUT NOCOPY NUMBER,
613 x_msg_data OUT NOCOPY VARCHAR2,
614 x_loc_id OUT NOCOPY NUMBER
615 ) IS
616
617 l_org_id VARCHAR2(2000);
618 BEGIN
619 update_loc_assignment(
620 p_init_msg_list,
621 p_location_id ,
622 p_lock_flag,
623 p_created_by_module,
624 p_application_id,
625 x_return_status,
626 x_msg_count,
627 x_msg_data ,
628 x_loc_id,
629 l_org_id
630 );
631
632 END update_loc_assignment;
633
634 /**
635 * PROCEDURE update_loc_assignment
636 *
637 * DESCRIPTION
638 * Updates location assignment.
639 *
640 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
641 *
642 * ARGUMENTS
643 * IN:
644 * p_init_msg_list Initialize message stack if it is set to
645 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
646 * p_location_id Location ID.
647 * p_lock_flag Lock record or not. Default is FND_API.G_TRUE.
648 * p_created_by_module Module name which creates this record.
649 * p_application_id Application ID which creates this record.
650 * IN/OUT:
651 * OUT:
652 * x_return_status Return status after the call. The status can
653 * be FND_API.G_RET_STS_SUCCESS (success),
654 * FND_API.G_RET_STS_ERROR (error),
655 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
656 * x_msg_count Number of messages in message stack.
657 * x_msg_data Message text if x_msg_count is 1.
658 * x_loc_id Location assignment ID.
659 *
660 * NOTES
661 *
662 * MODIFICATION HISTORY
663 *
664 * 09-18-2003 P.Suresh o Created.
665 *
666 */
667
668 PROCEDURE update_loc_assignment(
669 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
670 p_location_id IN NUMBER,
671 p_lock_flag IN VARCHAR2 :=FND_API.G_TRUE,
672 p_created_by_module IN VARCHAR2,
673 p_application_id IN NUMBER,
674 x_return_status IN OUT NOCOPY VARCHAR2,
675 x_msg_count OUT NOCOPY NUMBER,
676 x_msg_data OUT NOCOPY VARCHAR2,
677 x_loc_id OUT NOCOPY NUMBER ,
678 x_org_id OUT NOCOPY VARCHAR2
679 ) IS
680
681 l_location_id NUMBER := p_location_id;
682 APP_EXCEPTION EXCEPTION;
683 PRAGMA EXCEPTION_INIT(APP_EXCEPTION, -20000);
684
685 l_addr_val_status VARCHAR2(30);
686 l_addr_warn_msg VARCHAR2(2000);
687 BEGIN
688
689 -- standard start of API savepoint
690 SAVEPOINT update_loc_assignment;
691
692 -- initialize message list if p_init_msg_list is set to TRUE.
693 IF FND_API.to_Boolean(p_init_msg_list) THEN
694 FND_MSG_PUB.initialize;
695 END IF;
696
697 -- initialize API return status to success.
698 x_return_status := FND_API.G_RET_STS_SUCCESS;
699
700 -- call to business logic.
701 -- populate loc_id by calling tax package.
702 do_update_loc_assignment( l_location_id,
703 'N',
704 l_addr_val_status,
705 l_addr_warn_msg,
706 x_return_status,
707 p_lock_flag);
708
709 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
710 RAISE FND_API.G_EXC_ERROR;
711 END IF;
712
713 -- standard call to get message count and if count is 1, get message info.
714 FND_MSG_PUB.Count_And_Get(
715 p_encoded => FND_API.G_FALSE,
716 p_count => x_msg_count,
717 p_data => x_msg_data);
718 EXCEPTION
719 WHEN FND_API.G_EXC_ERROR THEN
720 ROLLBACK TO update_loc_assignment;
721 x_return_status := FND_API.G_RET_STS_ERROR;
722 FND_MSG_PUB.Count_And_Get(
723 p_encoded => FND_API.G_FALSE,
724 p_count => x_msg_count,
725 p_data => x_msg_data);
726
727 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
728 ROLLBACK TO update_loc_assignment;
729 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
730 FND_MSG_PUB.Count_And_Get(
731 p_encoded => FND_API.G_FALSE,
732 p_count => x_msg_count,
733 p_data => x_msg_data);
734
735 WHEN APP_EXCEPTION THEN
736 ROLLBACK TO update_loc_assignment;
737 x_return_status := FND_API.G_RET_STS_ERROR;
738 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
739 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
740 FND_MSG_PUB.ADD;
741 FND_MSG_PUB.Count_And_Get(
742 p_encoded => FND_API.G_FALSE,
743 p_count => x_msg_count,
744 p_data => x_msg_data);
745
746 WHEN OTHERS THEN
747 ROLLBACK TO update_loc_assignment;
748 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
749 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
750 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
751 FND_MSG_PUB.ADD;
752 FND_MSG_PUB.Count_And_Get(
753 p_encoded => FND_API.G_FALSE,
754 p_count => x_msg_count,
755 p_data => x_msg_data);
756
757 END update_loc_assignment;
758
759 /**
760 * PROCEDURE update_loc_assignment
761 *
762 * DESCRIPTION
763 * Updates location assignment(overloaded procedure with address validation).
764 *
765 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
766 *
767 * ARGUMENTS
768 * IN:
769 * p_init_msg_list Initialize message stack if it is set to
770 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
771 * p_location_id Location ID.
772 * p_lock_flag Lock record or not. Default is FND_API.G_TRUE.
773 * p_created_by_module Module name which creates this record.
774 * p_application_id Application ID which creates this record.
775 * IN/OUT:
776 * OUT:
777 * x_return_status Return status after the call. The status can
778 * be FND_API.G_RET_STS_SUCCESS (success),
779 * FND_API.G_RET_STS_ERROR (error),
780 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
781 * x_msg_count Number of messages in message stack.
782 * x_msg_data Message text if x_msg_count is 1.
783 * x_loc_id Location assignment ID.
784 *
785 * NOTES
786 *
787 * MODIFICATION HISTORY
788 *
789 * 09-18-2003 P.Suresh o Created.
790 *
791 */
792
793 PROCEDURE update_loc_assignment(
794 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
795 p_location_id IN NUMBER,
796 p_lock_flag IN VARCHAR2 := FND_API.G_TRUE,
797 p_do_addr_val IN VARCHAR2,
798 x_addr_val_status OUT NOCOPY VARCHAR2,
799 x_addr_warn_msg OUT NOCOPY VARCHAR2,
800 x_return_status IN OUT NOCOPY VARCHAR2,
801 x_msg_count OUT NOCOPY NUMBER,
802 x_msg_data OUT NOCOPY VARCHAR2
803 ) IS
804 l_location_id NUMBER := p_location_id;
805 APP_EXCEPTION EXCEPTION;
806 PRAGMA EXCEPTION_INIT(APP_EXCEPTION, -20000);
807
808 BEGIN
809
810 -- standard start of API savepoint
811 SAVEPOINT update_loc_assignment;
812
813 -- initialize message list if p_init_msg_list is set to TRUE.
814 IF FND_API.to_Boolean(p_init_msg_list) THEN
815 FND_MSG_PUB.initialize;
816 END IF;
817
818 -- initialize API return status to success.
819 x_return_status := FND_API.G_RET_STS_SUCCESS;
820
821 -- call to business logic.
822 -- populate loc_id by calling tax package.
823 do_update_loc_assignment( l_location_id,
824 p_do_addr_val,
825 x_addr_val_status,
826 x_addr_warn_msg,
827 x_return_status,
828 p_lock_flag);
829
830 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
831 RAISE FND_API.G_EXC_ERROR;
832 END IF;
833
834 -- standard call to get message count and if count is 1, get message info.
835 FND_MSG_PUB.Count_And_Get(
836 p_encoded => FND_API.G_FALSE,
837 p_count => x_msg_count,
838 p_data => x_msg_data);
839 EXCEPTION
840 WHEN FND_API.G_EXC_ERROR THEN
841 ROLLBACK TO update_loc_assignment;
842 x_return_status := FND_API.G_RET_STS_ERROR;
843 FND_MSG_PUB.Count_And_Get(
844 p_encoded => FND_API.G_FALSE,
845 p_count => x_msg_count,
846 p_data => x_msg_data);
847
848 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
849 ROLLBACK TO update_loc_assignment;
850 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
851 FND_MSG_PUB.Count_And_Get(
852 p_encoded => FND_API.G_FALSE,
853 p_count => x_msg_count,
854 p_data => x_msg_data);
855
856 WHEN APP_EXCEPTION THEN
857 ROLLBACK TO update_loc_assignment;
858 x_return_status := FND_API.G_RET_STS_ERROR;
859 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
860 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
861 FND_MSG_PUB.ADD;
862 FND_MSG_PUB.Count_And_Get(
863 p_encoded => FND_API.G_FALSE,
864 p_count => x_msg_count,
865 p_data => x_msg_data);
866
867 WHEN OTHERS THEN
868 ROLLBACK TO update_loc_assignment;
869 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
870 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
871 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
872 FND_MSG_PUB.ADD;
873 FND_MSG_PUB.Count_And_Get(
874 p_encoded => FND_API.G_FALSE,
875 p_count => x_msg_count,
876 p_data => x_msg_data);
877
878 END update_loc_assignment;
879
880 END HZ_TAX_ASSIGNMENT_V2PUB;