[Home] [Help]
PACKAGE BODY: APPS.HZ_TAX_ASSIGNMENT_V2PUB
Source
1 PACKAGE BODY HZ_TAX_ASSIGNMENT_V2PUB AS
2 /* $Header: ARH2TASB.pls 120.21 2010/05/06 11:13:47 rgokavar ship $ */
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 l_message_count NUMBER;
325 l_message_count_new NUMBER;
326 BEGIN
327
328 -- check the required fields:
329 IF p_location_id IS NULL
330 OR
331 p_location_id = FND_API.G_MISS_NUM
332 THEN
333 FND_MESSAGE.SET_NAME('AR', 'HZ_API_MISSING_COLUMN');
334 FND_MESSAGE.SET_TOKEN('COLUMN', 'p_location_id');
335 FND_MSG_PUB.ADD;
336 RAISE FND_API.G_EXC_ERROR;
337 END IF;
338
339 BEGIN
340 SELECT DATE_VALIDATED, VALIDATION_STATUS_CODE
341 INTO l_date_validated, l_validation_status_code
342 FROM HZ_LOCATIONS
343 WHERE LOCATION_ID = p_location_id;
344 EXCEPTION
345 WHEN NO_DATA_FOUND THEN
346 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NO_RECORD');
347 FND_MESSAGE.SET_TOKEN('RECORD', 'hz_locations');
348 FND_MESSAGE.SET_TOKEN('VALUE', to_char(p_location_id));
349 FND_MSG_PUB.ADD;
350 x_return_status := FND_API.G_RET_STS_ERROR;
351 END;
352
353 -- raise error if the update location profile option is turned off and
354 -- the address has been validated before
355 l_allow_update_std := nvl(fnd_profile.value('HZ_UPDATE_STD_ADDRESS'), 'Y');
356 IF(l_allow_update_std = 'N' AND
357 l_date_validated IS NOT NULL AND
358 l_validation_status_code IS NOT NULL)
359 THEN
360 FND_MESSAGE.SET_NAME('AR', 'HZ_LOC_NO_UPDATE');
361 FND_MSG_PUB.ADD;
362 RAISE fnd_api.g_exc_error;
363 END IF;
364
365 -- checking whether this location is for Remit-To Address or not
366 BEGIN
367 SELECT 'Y'
368 INTO l_is_remit_to_location
369 FROM DUAL
370 WHERE EXISTS ( SELECT 1
371 FROM HZ_PARTY_SITES PS
372 WHERE PS.LOCATION_ID = p_location_id
373 AND PS.PARTY_ID = -1);
374 EXCEPTION
375 WHEN NO_DATA_FOUND THEN
376 NULL;
377 END;
378
379 -- checking whether this location is already validated
380 BEGIN
381 SELECT 'Y'
382 INTO l_loc_assg_exists
383 FROM DUAL
384 WHERE EXISTS ( SELECT 1
385 FROM HZ_LOC_ASSIGNMENTS
386 WHERE LOCATION_ID = p_location_id);
387 EXCEPTION
388 WHEN NO_DATA_FOUND THEN
389 NULL;
390 END;
391
392 IF l_is_remit_to_location <> 'Y' OR p_do_addr_val = 'Y' THEN
393 FOR l_c_loc in c_loc(p_location_id) LOOP
394 IF l_loc_assg_exists = 'Y' OR p_do_addr_val = 'Y' THEN
395
396 HZ_GNR_PKG.delete_gnr(
397 p_locId => p_location_id,
398 p_locTbl => 'HZ_LOCATIONS',
399 x_status => x_return_status);
400
401 HZ_GNR_PKG.validateLoc(
402 P_LOCATION_ID => l_c_loc.LOCATION_ID,
403 P_USAGE_CODE => 'GEOGRAPHY',
404 P_ADDRESS_STYLE => l_c_loc.ADDRESS_STYLE,
405 P_COUNTRY => l_c_loc.COUNTRY,
406 P_STATE => l_c_loc.STATE,
407 P_PROVINCE => l_c_loc.PROVINCE,
408 P_COUNTY => l_c_loc.COUNTY,
409 P_CITY => l_c_loc.CITY,
410 P_POSTAL_CODE => l_c_loc.POSTAL_CODE,
411 P_POSTAL_PLUS4_CODE => l_c_loc.POSTAL_PLUS4_CODE,
412 P_ATTRIBUTE1 => l_c_loc.ATTRIBUTE1,
413 P_ATTRIBUTE2 => l_c_loc.ATTRIBUTE2,
414 P_ATTRIBUTE3 => l_c_loc.ATTRIBUTE3,
415 P_ATTRIBUTE4 => l_c_loc.ATTRIBUTE4,
416 P_ATTRIBUTE5 => l_c_loc.ATTRIBUTE5,
417 P_ATTRIBUTE6 => l_c_loc.ATTRIBUTE6,
418 P_ATTRIBUTE7 => l_c_loc.ATTRIBUTE7,
419 P_ATTRIBUTE8 => l_c_loc.ATTRIBUTE8,
420 P_ATTRIBUTE9 => l_c_loc.ATTRIBUTE9,
421 P_ATTRIBUTE10 => l_c_loc.ATTRIBUTE10,
422 P_LOCK_FLAG => p_lock_flag,
423 P_CALLED_FROM => 'VALIDATE',
424 X_ADDR_VAL_LEVEL => l_addr_val_level,
425 X_ADDR_WARN_MSG => x_addr_warn_msg,
426 X_ADDR_VAL_STATUS => x_addr_val_status,
427 X_STATUS => x_return_status);
428 ELSE
429 -- Below code will execute only if there is no record in hz_loc_assignments table
430 -- process_gnr will process the GNR and return success even if there is an error
431 -- in the validation.
432 l_message_count := fnd_msg_pub.count_msg();
433 HZ_GNR_PUB.process_gnr (
434 p_location_table_name => 'HZ_LOCATIONS',
435 p_location_id => p_location_id,
436 p_call_type => 'U',
437 p_init_msg_list => 'F',
438 x_return_status => x_return_status,
439 x_msg_count => l_msg_count_gnr,
440 x_msg_data => l_msg_data_gnr);
441
442 --Bug9671252
443 --When Address Validation Profile value is set NO
444 --then Process_GNR API generated messages need to be deleted.
445 IF p_do_addr_val = 'N' THEN
446 l_message_count_new := fnd_msg_pub.count_msg();
447 IF (l_message_count_new > l_message_count) THEN
448 FOR i IN REVERSE (l_message_count + 1)..l_message_count_new LOOP
449 fnd_msg_pub.delete_msg(i);
450 END LOOP;
451 END IF;
452 END IF;
453
454 END IF;
455 END LOOP;
456 END IF;
457
458 END do_update_loc_assignment;
459
460 --------------------------------------
461 -- public procedures and functions
462 --------------------------------------
463
464 /**
465 * PROCEDURE create_loc_assignment
466 *
467 * DESCRIPTION
468 * Creates location assignment.
469 *
470 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
471 *
472 * ARGUMENTS
473 * IN:
474 * p_init_msg_list Initialize message stack if it is set to
475 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
476 * p_location_id Location ID.
477 * p_lock_flag Lock record or not. Default is FND_API.G_FALSE.
478 * p_created_by_module Module name which creates this record.
479 * p_application_id Application ID which creates this record.
480 * IN/OUT:
481 * OUT:
482 * x_return_status Return status after the call. The status can
483 * be FND_API.G_RET_STS_SUCCESS (success),
484 * FND_API.G_RET_STS_ERROR (error),
485 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
486 * x_msg_count Number of messages in message stack.
487 * x_msg_data Message text if x_msg_count is 1.
488 * x_loc_id Location assignment ID.
489 *
490 * NOTES
491 *
492 * MODIFICATION HISTORY
493 *
494 * 07-23-2001 Indrajit Sen o Created.
495 *
496 */
497
498 PROCEDURE create_loc_assignment(
499 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
500 p_location_id IN NUMBER,
501 p_lock_flag IN VARCHAR2 :=FND_API.G_FALSE,
502 p_created_by_module IN VARCHAR2,
503 p_application_id IN NUMBER,
504 x_return_status IN OUT NOCOPY VARCHAR2,
505 x_msg_count OUT NOCOPY NUMBER,
506 x_msg_data OUT NOCOPY VARCHAR2,
507 x_loc_id OUT NOCOPY NUMBER
508 ) IS
509
510 l_location_id NUMBER := p_location_id;
511 APP_EXCEPTION EXCEPTION;
512 PRAGMA EXCEPTION_INIT(APP_EXCEPTION, -20000);
513
514 BEGIN
515
516 -- standard start of API savepoint
517 SAVEPOINT create_loc_assignment;
518
519 -- initialize message list if p_init_msg_list is set to TRUE.
520 IF FND_API.to_Boolean(p_init_msg_list) THEN
521 FND_MSG_PUB.initialize;
522 END IF;
523
524 -- initialize API return status to success.
525 x_return_status := FND_API.G_RET_STS_SUCCESS;
526
527 -- call to business logic.
528 -- populate loc_id by calling tax package.
529 do_create_loc_assignment( p_location_id,
530 x_loc_id,
531 x_return_status,
532 p_lock_flag,
533 p_created_by_module,
534 p_application_id);
535
536 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
537 RAISE FND_API.G_EXC_ERROR;
538 END IF;
539
540 -- standard call to get message count and if count is 1, get message info.
541 FND_MSG_PUB.Count_And_Get(
542 p_encoded => FND_API.G_FALSE,
543 p_count => x_msg_count,
544 p_data => x_msg_data);
545
546 EXCEPTION
547 WHEN FND_API.G_EXC_ERROR THEN
548 ROLLBACK TO create_loc_assignment;
549 x_return_status := FND_API.G_RET_STS_ERROR;
550 FND_MSG_PUB.Count_And_Get(
551 p_encoded => FND_API.G_FALSE,
552 p_count => x_msg_count,
553 p_data => x_msg_data);
554
555 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
556 ROLLBACK TO create_loc_assignment;
557 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
558 FND_MSG_PUB.Count_And_Get(
559 p_encoded => FND_API.G_FALSE,
560 p_count => x_msg_count,
561 p_data => x_msg_data);
562
563 WHEN APP_EXCEPTION THEN
564 ROLLBACK TO create_loc_assignment;
565 x_return_status := FND_API.G_RET_STS_ERROR;
566 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
567 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
568 FND_MSG_PUB.ADD;
569 FND_MSG_PUB.Count_And_Get(
570 p_encoded => FND_API.G_FALSE,
571 p_count => x_msg_count,
572 p_data => x_msg_data);
573 WHEN OTHERS THEN
574 ROLLBACK TO create_loc_assignment;
575 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
576 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
577 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
578 FND_MSG_PUB.ADD;
579 FND_MSG_PUB.Count_And_Get(
580 p_encoded => FND_API.G_FALSE,
581 p_count => x_msg_count,
582 p_data => x_msg_data);
583
584 END create_loc_assignment;
585
586 /**
587 * PROCEDURE update_loc_assignment
588 *
589 * DESCRIPTION
590 * Updates location assignment.
591 *
592 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
593 *
594 * ARGUMENTS
595 * IN:
596 * p_init_msg_list Initialize message stack if it is set to
597 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
598 * p_location_id Location ID.
599 * p_lock_flag Lock record or not. Default is FND_API.G_TRUE.
600 * p_created_by_module Module name which creates this record.
601 * p_application_id Application ID which creates this record.
602 * IN/OUT:
603 * OUT:
604 * x_return_status Return status after the call. The status can
605 * be FND_API.G_RET_STS_SUCCESS (success),
606 * FND_API.G_RET_STS_ERROR (error),
607 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
608 * x_msg_count Number of messages in message stack.
609 * x_msg_data Message text if x_msg_count is 1.
610 * x_loc_id Location assignment ID.
611 *
612 * NOTES
613 *
614 * MODIFICATION HISTORY
615 *
616 * 07-23-2001 Indrajit Sen o Created.
617 *
618 */
619
620 PROCEDURE update_loc_assignment(
621 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
622 p_location_id IN NUMBER,
623 p_lock_flag IN VARCHAR2 :=FND_API.G_TRUE,
624 p_created_by_module IN VARCHAR2,
625 p_application_id IN NUMBER,
626 x_return_status IN OUT NOCOPY VARCHAR2,
627 x_msg_count OUT NOCOPY NUMBER,
628 x_msg_data OUT NOCOPY VARCHAR2,
629 x_loc_id OUT NOCOPY NUMBER
630 ) IS
631
632 l_org_id VARCHAR2(2000);
633 BEGIN
634 update_loc_assignment(
635 p_init_msg_list,
636 p_location_id ,
637 p_lock_flag,
638 p_created_by_module,
639 p_application_id,
640 x_return_status,
641 x_msg_count,
642 x_msg_data ,
643 x_loc_id,
644 l_org_id
645 );
646
647 END update_loc_assignment;
648
649 /**
650 * PROCEDURE update_loc_assignment
651 *
652 * DESCRIPTION
653 * Updates location assignment.
654 *
655 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
656 *
657 * ARGUMENTS
658 * IN:
659 * p_init_msg_list Initialize message stack if it is set to
660 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
661 * p_location_id Location ID.
662 * p_lock_flag Lock record or not. Default is FND_API.G_TRUE.
663 * p_created_by_module Module name which creates this record.
664 * p_application_id Application ID which creates this record.
665 * IN/OUT:
666 * OUT:
667 * x_return_status Return status after the call. The status can
668 * be FND_API.G_RET_STS_SUCCESS (success),
669 * FND_API.G_RET_STS_ERROR (error),
670 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
671 * x_msg_count Number of messages in message stack.
672 * x_msg_data Message text if x_msg_count is 1.
673 * x_loc_id Location assignment ID.
674 *
675 * NOTES
676 *
677 * MODIFICATION HISTORY
678 *
679 * 09-18-2003 P.Suresh o Created.
680 *
681 */
682
683 PROCEDURE update_loc_assignment(
684 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
685 p_location_id IN NUMBER,
686 p_lock_flag IN VARCHAR2 :=FND_API.G_TRUE,
687 p_created_by_module IN VARCHAR2,
688 p_application_id IN NUMBER,
689 x_return_status IN OUT NOCOPY VARCHAR2,
690 x_msg_count OUT NOCOPY NUMBER,
691 x_msg_data OUT NOCOPY VARCHAR2,
692 x_loc_id OUT NOCOPY NUMBER ,
693 x_org_id OUT NOCOPY VARCHAR2
694 ) IS
695
696 l_location_id NUMBER := p_location_id;
697 APP_EXCEPTION EXCEPTION;
698 PRAGMA EXCEPTION_INIT(APP_EXCEPTION, -20000);
699
700 l_addr_val_status VARCHAR2(30);
701 l_addr_warn_msg VARCHAR2(2000);
702 BEGIN
703
704 -- standard start of API savepoint
705 SAVEPOINT update_loc_assignment;
706
707 -- initialize message list if p_init_msg_list is set to TRUE.
708 IF FND_API.to_Boolean(p_init_msg_list) THEN
709 FND_MSG_PUB.initialize;
710 END IF;
711
712 -- initialize API return status to success.
713 x_return_status := FND_API.G_RET_STS_SUCCESS;
714
715 -- call to business logic.
716 -- populate loc_id by calling tax package.
717 do_update_loc_assignment( l_location_id,
718 'N',
719 l_addr_val_status,
720 l_addr_warn_msg,
721 x_return_status,
722 p_lock_flag);
723
724 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
725 RAISE FND_API.G_EXC_ERROR;
726 END IF;
727
728 -- standard call to get message count and if count is 1, get message info.
729 FND_MSG_PUB.Count_And_Get(
730 p_encoded => FND_API.G_FALSE,
731 p_count => x_msg_count,
732 p_data => x_msg_data);
733 EXCEPTION
734 WHEN FND_API.G_EXC_ERROR THEN
735 ROLLBACK TO update_loc_assignment;
736 x_return_status := FND_API.G_RET_STS_ERROR;
737 FND_MSG_PUB.Count_And_Get(
738 p_encoded => FND_API.G_FALSE,
739 p_count => x_msg_count,
740 p_data => x_msg_data);
741
742 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
743 ROLLBACK TO update_loc_assignment;
744 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
745 FND_MSG_PUB.Count_And_Get(
746 p_encoded => FND_API.G_FALSE,
747 p_count => x_msg_count,
748 p_data => x_msg_data);
749
750 WHEN APP_EXCEPTION THEN
751 ROLLBACK TO update_loc_assignment;
752 x_return_status := FND_API.G_RET_STS_ERROR;
753 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
754 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
755 FND_MSG_PUB.ADD;
756 FND_MSG_PUB.Count_And_Get(
757 p_encoded => FND_API.G_FALSE,
758 p_count => x_msg_count,
759 p_data => x_msg_data);
760
761 WHEN OTHERS THEN
762 ROLLBACK TO update_loc_assignment;
763 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
764 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
765 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
766 FND_MSG_PUB.ADD;
767 FND_MSG_PUB.Count_And_Get(
768 p_encoded => FND_API.G_FALSE,
769 p_count => x_msg_count,
770 p_data => x_msg_data);
771
772 END update_loc_assignment;
773
774 /**
775 * PROCEDURE update_loc_assignment
776 *
777 * DESCRIPTION
778 * Updates location assignment(overloaded procedure with address validation).
779 *
780 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
781 *
782 * ARGUMENTS
783 * IN:
784 * p_init_msg_list Initialize message stack if it is set to
785 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
786 * p_location_id Location ID.
787 * p_lock_flag Lock record or not. Default is FND_API.G_TRUE.
788 * p_created_by_module Module name which creates this record.
789 * p_application_id Application ID which creates this record.
790 * IN/OUT:
791 * OUT:
792 * x_return_status Return status after the call. The status can
793 * be FND_API.G_RET_STS_SUCCESS (success),
794 * FND_API.G_RET_STS_ERROR (error),
795 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
796 * x_msg_count Number of messages in message stack.
797 * x_msg_data Message text if x_msg_count is 1.
798 * x_loc_id Location assignment ID.
799 *
800 * NOTES
801 *
802 * MODIFICATION HISTORY
803 *
804 * 09-18-2003 P.Suresh o Created.
805 *
806 */
807
808 PROCEDURE update_loc_assignment(
809 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
810 p_location_id IN NUMBER,
811 p_lock_flag IN VARCHAR2 := FND_API.G_TRUE,
812 p_do_addr_val IN VARCHAR2,
813 x_addr_val_status OUT NOCOPY VARCHAR2,
814 x_addr_warn_msg OUT NOCOPY VARCHAR2,
815 x_return_status IN OUT NOCOPY VARCHAR2,
816 x_msg_count OUT NOCOPY NUMBER,
817 x_msg_data OUT NOCOPY VARCHAR2
818 ) IS
819 l_location_id NUMBER := p_location_id;
820 APP_EXCEPTION EXCEPTION;
821 PRAGMA EXCEPTION_INIT(APP_EXCEPTION, -20000);
822
823 BEGIN
824
825 -- standard start of API savepoint
826 SAVEPOINT update_loc_assignment;
827
828 -- initialize message list if p_init_msg_list is set to TRUE.
829 IF FND_API.to_Boolean(p_init_msg_list) THEN
830 FND_MSG_PUB.initialize;
831 END IF;
832
833 -- initialize API return status to success.
834 x_return_status := FND_API.G_RET_STS_SUCCESS;
835
836 -- call to business logic.
837 -- populate loc_id by calling tax package.
838 do_update_loc_assignment( l_location_id,
839 p_do_addr_val,
840 x_addr_val_status,
841 x_addr_warn_msg,
842 x_return_status,
843 p_lock_flag);
844
845 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
846 RAISE FND_API.G_EXC_ERROR;
847 END IF;
848
849 -- standard call to get message count and if count is 1, get message info.
850 FND_MSG_PUB.Count_And_Get(
851 p_encoded => FND_API.G_FALSE,
852 p_count => x_msg_count,
853 p_data => x_msg_data);
854 EXCEPTION
855 WHEN FND_API.G_EXC_ERROR THEN
856 ROLLBACK TO update_loc_assignment;
857 x_return_status := FND_API.G_RET_STS_ERROR;
858 FND_MSG_PUB.Count_And_Get(
859 p_encoded => FND_API.G_FALSE,
860 p_count => x_msg_count,
861 p_data => x_msg_data);
862
863 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
864 ROLLBACK TO update_loc_assignment;
865 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
866 FND_MSG_PUB.Count_And_Get(
867 p_encoded => FND_API.G_FALSE,
868 p_count => x_msg_count,
869 p_data => x_msg_data);
870
871 WHEN APP_EXCEPTION THEN
872 ROLLBACK TO update_loc_assignment;
873 x_return_status := FND_API.G_RET_STS_ERROR;
874 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
875 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
876 FND_MSG_PUB.ADD;
877 FND_MSG_PUB.Count_And_Get(
878 p_encoded => FND_API.G_FALSE,
879 p_count => x_msg_count,
880 p_data => x_msg_data);
881
882 WHEN OTHERS THEN
883 ROLLBACK TO update_loc_assignment;
884 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
885 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
886 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
887 FND_MSG_PUB.ADD;
888 FND_MSG_PUB.Count_And_Get(
889 p_encoded => FND_API.G_FALSE,
890 p_count => x_msg_count,
891 p_data => x_msg_data);
892
893 END update_loc_assignment;
894
895 END HZ_TAX_ASSIGNMENT_V2PUB;