[Home] [Help]
PACKAGE BODY: APPS.ARH_ADDR_PKG
Source
1 PACKAGE BODY arh_addr_pkg as
2 /* $Header: ARHADDRB.pls 120.20.12000000.2 2007/07/03 11:38:13 nemani ship $*/
3
4 -- Local specification
5
6 --{BUG#4037614
7 ----------------------------
8 -- This procedure if the updation of a physical location is not allowed
9 -- and the location has been updated.
10 -- -> system option setting
11 -- -> the data of location really updated
12 -- -> transaction has been printed with this location
13 ----------------------------
14 PROCEDURE check_printed_trx
15 (p_location_id IN NUMBER,
16 p_location_rec IN hz_location_v2pub.location_rec_type,
17 x_return_status IN OUT NOCOPY VARCHAR2,
18 x_msg_data IN OUT NOCOPY VARCHAR2,
19 x_msg_count IN OUT NOCOPY NUMBER);
20 --}
21
22 --{BUG#4058639
23 -- Take the location_id to return
24 -- the existing location data in x_exist_loc_rec
25 -- if the location does not exist x_exist_loc_rec is empty record
26 -- x_loc_updated
27 -- return 'Y' if the data in p_location_rec is different to the existing location
28 -- return 'N' if the data in p_location_rec is equal to the existing location
29 -- return 'X' if the no existing location found
30 PROCEDURE compare_location_existing
31 (p_location_id IN NUMBER,
32 p_location_rec IN hz_location_v2pub.location_rec_type,
33 x_exist_loc_rec IN OUT NOCOPY hz_location_v2pub.location_rec_type,
34 x_loc_updated IN OUT NOCOPY VARCHAR2);
35
36 ar_miss_char VARCHAR2(1) := '}';
37
38 ar_null_char VARCHAR2(1) := '{';
39 --}
40 /*--------------------------------------------------------------------+
41 PUBLIC FUNCTION
42 format_address
43
44 DESCRIPTION
45 This function returns a sigle string of concatenated address
46 segments. The segments and their display order may vary according
47 to a given address format. Line breaks are inserted in order for the
48 segments to be allocated inside the given box dimension.
49
50 If the box size is not big enough to contain all the required
51 segment together with segment joint characters(spaces/commas),
52 or the box width is not long enough to contain any segment,
53 then the function truncates the string to provide the possible output.
54
55 REQUIRES
56 address_style : address format style
57 address1 : address line 1
58 address2 : address line 2
59 address3 : address line 3
60 address4 : address line 4
61 city : name of city
62 county : name of county
63 state : name of state
64 province : name of province
65 postal_code : postal code
66 territory_short_name : territory short name
67
68 OPTIONAL REQUIRES
69 country_code : country code
70 customer_name : customer name
71 first_name : contact first name
72 last_name : contact last name
73 mail_stop : mailing informatioin
74 default_country_code : default country code
75 default_country_desc : default territory short name
76 print_home_country_flag : flag to control home county printing
77 print_default_attn_flag : flag to control default attention message
78 width NUMBER : address box width
79 height_min : address box minimum height
80 height_max : address box maximum height
81
82 RETURN
83 formatted address string
84
85 +--------------------------------------------------------------------*/
86 FUNCTION format_address( address_style IN VARCHAR2,
87 address1 IN VARCHAR2,
88 address2 IN VARCHAR2,
89 address3 IN VARCHAR2,
90 address4 IN VARCHAR2,
91 city IN VARCHAR2,
92 county IN VARCHAR2,
93 state IN VARCHAR2,
94 province IN VARCHAR2,
95 postal_code IN VARCHAR2,
96 territory_short_name IN VARCHAR2,
97 country_code IN VARCHAR2 default NULL,
98 customer_name IN VARCHAR2 default NULL,
99 first_name IN VARCHAR2 default NULL,
100 last_name IN VARCHAR2 default NULL,
101 mail_stop IN VARCHAR2 default NULL,
102 default_country_code IN VARCHAR2,
103 default_country_desc IN VARCHAR2,
104 print_home_country_flag IN VARCHAR2,
105 print_default_attn_flag IN VARCHAR2,
106 width IN NUMBER,
107 height_min IN NUMBER,
108 height_max IN NUMBER
109 )return VARCHAR2 IS
110
111 l_fmt_bkwd_compatible VARCHAR2(10) :='Y';
112 l_formatted_address VARCHAR2(2000);
113 l_formatted_lines_cnt NUMBER;
114 l_formatted_address_tbl hz_format_pub.string_tbl_type;
115 l_return_status VARCHAR2(1);
116 l_msg_count NUMBER;
117 l_msg_data VARCHAR2(2000);
118
119 BEGIN
120
121 l_fmt_bkwd_compatible := FND_PROFILE.VALUE('HZ_FMT_BKWD_COMPATIBLE');
122 IF l_fmt_bkwd_compatible = 'Y' THEN
123 return( arxtw_format_address( address_style,
124 address1,
125 address2,
126 address3,
127 address4,
128 city,
129 county,
130 state,
131 province,
132 postal_code,
133 territory_short_name ) );
134 ELSE
135
136 hz_format_pub.format_address (
137 p_line_break => ', ',
138 p_from_territory_code => 'x', -- force country short name be displayed
139 p_address_line_1 => address1,
140 p_address_line_2 => address2,
141 p_address_line_3 => address3,
142 p_address_line_4 => address4,
143 p_city => city,
144 p_postal_code => postal_code,
145 p_state => state,
146 p_province => province,
147 p_county => county,
148 p_country => country_code,
149 -- output parameters
150 x_return_status => l_return_status,
151 x_msg_count => l_msg_count,
152 x_msg_data => l_msg_data,
153 x_formatted_address => l_formatted_address,
154 x_formatted_lines_cnt => l_formatted_lines_cnt,
155 x_formatted_address_tbl => l_formatted_address_tbl
156 );
157
158 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
159 return l_formatted_address;
160 ELSE
161 return( arxtw_format_address( address_style,
162 address1,
163 address2,
164 address3,
165 address4,
166 city,
167 county,
168 state,
169 province,
170 postal_code,
171 territory_short_name ) );
172 END IF;
173 END IF;
174
175
176 END format_address;
177
178
179
180 FUNCTION arxtw_format_address( address_style IN VARCHAR2,
181 address1 IN VARCHAR2,
182 address2 IN VARCHAR2,
183 address3 IN VARCHAR2,
184 address4 IN VARCHAR2,
185 city IN VARCHAR2,
186 county IN VARCHAR2,
187 state IN VARCHAR2,
188 province IN VARCHAR2,
189 postal_code IN VARCHAR2,
190 territory_short_name IN VARCHAR2
191 )return VARCHAR2 IS
192 l_address varchar2(1000);
193 BEGIN
194 --
195 -- ra addresses.address1 is a NOT NULL field.
196 --
197 l_address := address1;
198
199 IF ( address2 IS NOT NULL ) THEN
200 l_address := l_address || ', ' || address2;
201 END IF;
202
203 IF ( address3 IS NOT NULL ) THEN
204 l_address := l_address || ', ' || address3;
205 END IF;
206
207 IF ( address4 IS NOT NULL ) THEN
208 l_address := l_address || ', ' || address4;
209 END IF;
210
211 IF ( city IS NOT NULL ) THEN
212 l_address := l_address || ', ' || city;
213 END IF;
214
215 IF ( county IS NOT NULL ) THEN
216 l_address := l_address || ', ' || county;
217 END IF;
218
219 IF ( state IS NOT NULL ) THEN
220 l_address := l_address || ', ' || state;
221 END IF;
222
223 IF ( province IS NOT NULL ) THEN
224 l_address := l_address || ', ' || province;
225 END IF;
226
227 IF ( postal_code IS NOT NULL ) THEN
228 l_address := l_address || ', ' || postal_code;
229 END IF;
230
231 IF ( territory_short_name IS NOT NULL ) THEN
232 l_address := l_address || ', ' || territory_short_name;
233 END IF;
234
235 RETURN( l_address );
236 END arxtw_format_address;
237
238
239
240 FUNCTION format_last_address_line(p_address_style varchar2,
241 p_address3 varchar2,
242 p_address4 varchar2,
243 p_city varchar2,
244 p_county varchar2,
245 p_state varchar2,
246 p_province varchar2,
247 p_country varchar2,
248 p_postal_code varchar2 )
249 RETURN varchar2 IS
250
251
252 l_address varchar2(1000);
253 BEGIN
254 IF ( p_address3 IS NOT NULL )
255 THEN
256 l_address := p_address3;
257 END IF;
258
259 IF ( p_address4 IS NOT NULL )
260 THEN
261 IF (l_address IS NOT NULL)
262 THEN
263 l_address := l_address || ', ' || p_address4;
264 ELSE l_address := p_address4;
265 END IF;
266 END IF;
267
268 IF ( p_city IS NOT NULL )
269 THEN
270 IF (l_address IS NOT NULL)
271 THEN
272 l_address := l_address || ', ' || p_city;
273 ELSE l_address := p_city;
274 END IF;
275 END IF;
276
277 IF ( p_state IS NOT NULL )
278 THEN
279 IF (l_address IS NOT NULL)
280 THEN
281 l_address := l_address || ', ' || p_state;
282 ELSE l_address := p_state;
283 END IF;
284 END IF;
285
286 IF ( p_province IS NOT NULL )
287 THEN
288 IF (l_address IS NOT NULL)
289 THEN
290 l_address := l_address || ', ' || p_province;
291 ELSE l_address := p_province;
292 END IF;
293 END IF;
294
295 IF ( p_postal_code IS NOT NULL )
296 THEN
297 IF (l_address IS NOT NULL)
298 THEN
299 l_address := l_address || ' ' || p_postal_code;
300 ELSE l_address := p_postal_code;
301 END IF;
302 END IF;
303
304 IF ( p_country IS NOT NULL )
305 THEN
306 IF (l_address IS NOT NULL)
307 THEN
308 l_address := l_address || ' ' || p_country;
309 ELSE l_address := p_country;
310 END IF;
311 END IF;
312
313 RETURN(l_address);
314
315 END format_last_address_line;
316 --
317 --
318 FUNCTION INIT_SWITCH
319 ( p_date IN DATE,
320 p_switch IN VARCHAR2 DEFAULT 'NULL_GMISS')
321 RETURN DATE
322 IS
323 res_date date;
324 BEGIN
325 IF p_switch = 'NULL_GMISS' THEN
326 IF p_date IS NULL THEN
327 res_date := FND_API.G_MISS_DATE;
328 ELSE
329 res_date := p_date;
330 END IF;
331 ELSIF p_switch = 'GMISS_NULL' THEN
332 IF p_date = FND_API.G_MISS_DATE THEN
333 res_date := NULL;
334 ELSE
335 res_date := p_date;
336 END IF;
337 ELSE
338 res_date := TO_DATE('31/12/1800','DD/MM/RRRR');
339 END IF;
340 RETURN res_date;
341 END;
342
343 FUNCTION INIT_SWITCH
344 ( p_char IN VARCHAR2,
345 p_switch IN VARCHAR2 DEFAULT 'NULL_GMISS')
346 RETURN VARCHAR2
347 IS
348 res_char varchar2(2000);
349 BEGIN
350 IF p_switch = 'NULL_GMISS' THEN
351 IF p_char IS NULL THEN
352 return FND_API.G_MISS_CHAR;
353 ELSE
354 return p_char;
355 END IF;
356 ELSIF p_switch = 'GMISS_NULL' THEN
357 IF p_char = FND_API.G_MISS_CHAR THEN
358 return NULL;
359 ELSE
360 return p_char;
361 END IF;
362 ELSE
363 return ('INCORRECT_P_SWITCH');
364 END IF;
365 END;
366
367 FUNCTION INIT_SWITCH
368 ( p_num IN NUMBER,
369 p_switch IN VARCHAR2 DEFAULT 'NULL_GMISS')
370 RETURN NUMBER
371 IS
372 BEGIN
373 IF p_switch = 'NULL_GMISS' THEN
374 IF p_num IS NULL THEN
375 return FND_API.G_MISS_NUM;
376 ELSE
377 return p_num;
378 END IF;
379 ELSIF p_switch = 'GMISS_NULL' THEN
380 IF p_num = FND_API.G_MISS_NUM THEN
381 return NULL;
382 ELSE
383 return p_num;
384 END IF;
385 ELSE
386 return ('9999999999');
387 END IF;
388 END;
389 --
390 -- PROCEDURE
391 -- insert_site_use
392 --
393 -- DESCRIPTION
394 -- This procedure calls arh_csu_pkg.insert_row to create a site use for
395 -- an address
396 --
397 -- SCOPE - PRIVATE
398 --
399 -- EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
400 --
401 -- ARGUMENTS : IN:
402 -- p_address_id -
403 -- p_site_use_code - type of site use to create
404 -- OUT:
405 -- None
406 --
407 -- RETURNS : NONE
408 --
409 -- NOTES
410 --
411 -- MODIFICATION HISTORY - Created by Kevin Hudson
412 --
413 --
414 PROCEDURE insert_site_use( p_customer_id in number,
415 p_address_id in number,
416 p_site_use_code in varchar2,
417 x_msg_count OUT NOCOPY number,
418 x_msg_data OUT NOCOPY VARCHAR2,
419 x_return_status OUT NOCOPY VARCHAR2
420 ) is
421 --
422 l_rowid varchar2(18);
423 l_site_use_id number;
424 l_location varchar2(40);
425 --
426 begin
427 --
428 --
429 arh_csu_pkg.insert_row(
430 X_Site_Use_Id => l_site_use_id,
431 X_Last_Update_Date => sysdate,
432 x_Last_Updated_By => fnd_global.user_id,
433 X_Creation_Date => sysdate,
434 X_Created_By => fnd_global.user_id,
435 X_Site_Use_Code => p_site_use_code,
436 X_customer_id => p_customer_id,
437 X_Address_Id => p_address_id,
438 X_Primary_Flag => 'N',
439 X_Status => 'A',
440 X_Location => l_location,
441 X_Last_Update_Login => fnd_global.Login_id,
442 X_Contact_Id => null,
443 X_Bill_To_Site_Use_Id => null,
444 X_Sic_Code => null,
445 X_Payment_Term_Id => null,
446 X_Gsa_Indicator => null,
447 X_Ship_Partial => null,
448 X_Ship_Via => null,
449 X_Fob_Point => null,
450 X_Order_Type_Id => null,
451 X_Price_List_Id => null,
452 X_Freight_Term => null,
453 X_Warehouse_Id => null,
454 X_Territory_Id => null,
455 X_Tax_Code => null,
456 X_Tax_Reference => null,
457 X_Demand_Class_Code => null,
458 x_inventory_location_id => null,
459 x_inventory_organization_id => null,
460 X_Attribute_Category => null,
461 X_Attribute1 => null,
462 X_Attribute2 => null,
463 X_Attribute3 => null,
464 X_Attribute4 => null,
465 X_Attribute5 => null,
466 X_Attribute6 => null,
467 X_Attribute7 => null,
468 X_Attribute8 => null,
469 X_Attribute9 => null,
470 X_Attribute10 => null,
471 X_Attribute11 => null,
472 X_Attribute12 => null,
473 X_Attribute13 => null,
474 X_Attribute14 => null,
475 X_Attribute15 => null,
476 X_Attribute16 => null,
477 X_Attribute17 => null,
478 X_Attribute18 => null,
479 X_Attribute19 => null,
480 X_Attribute20 => null,
481 X_Attribute21 => null,
482 X_Attribute22 => null,
483 X_Attribute23 => null,
484 X_Attribute24 => null,
485 X_Attribute25 => null,
486 X_Tax_Classification => null,
487 X_Tax_Header_Level_Flag => null,
488 X_Tax_Rounding_Rule => null,
489 X_Global_Attribute_Category => null,
490 X_Global_Attribute1 => null,
491 X_Global_Attribute2 => null,
492 X_Global_Attribute3 => null,
493 X_Global_Attribute4 => null,
494 X_Global_Attribute5 => null,
495 X_Global_Attribute6 => null,
496 X_Global_Attribute7 => null,
497 X_Global_Attribute8 => null,
498 X_Global_Attribute9 => null,
499 X_Global_Attribute10 => null,
500 X_Global_Attribute11 => null,
501 X_Global_Attribute12 => null,
502 X_Global_Attribute13 => null,
503 X_Global_Attribute14 => null,
504 X_Global_Attribute15 => null,
505 X_Global_Attribute16 => null,
506 X_Global_Attribute17 => null,
507 X_Global_Attribute18 => null,
508 X_Global_Attribute19 => null,
509 X_Global_Attribute20 => null,
510 X_Primary_Salesrep_Id => null,
511 X_Finchrg_Receivables_Trx_Id => null,
512 X_GL_ID_Rec => null,
513 X_GL_ID_Rev => null,
514 X_GL_ID_Tax => null,
515 X_GL_ID_Freight => null,
516 X_GL_ID_Clearing => null,
517 X_GL_ID_Unbilled => null,
518 X_GL_ID_Unearned => null,
519 X_GL_ID_Unpaid_rec => null,
520 X_GL_ID_Remittance => null,
521 X_GL_ID_Factor => null,
522 X_DATES_NEGATIVE_TOLERANCE => null,
523 X_DATES_POSITIVE_TOLERANCE => null,
524 X_DATE_TYPE_PREFERENCE => null,
525 X_OVER_SHIPMENT_TOLERANCE => null,
526 X_UNDER_SHIPMENT_TOLERANCE => null,
527 X_ITEM_CROSS_REF_PREF => null,
528 X_OVER_RETURN_TOLERANCE => null,
529 X_UNDER_RETURN_TOLERANCE => null,
530 X_SHIP_SETS_INCLUDE_LINES_FLAG => null,
531 X_ARRIVALSETS_INCL_LINES_FLAG => null,
532 X_SCHED_DATE_PUSH_FLAG => null,
533 X_INVOICE_QUANTITY_RULE => null,
534 x_msg_count => x_msg_count,
535 x_msg_data => x_msg_data,
536 x_return_status => x_return_status
537
538 );
539 end insert_site_use;
540 --
541 -- PROCEDURE
542 -- manage_site_use
543 --
544 -- DESCRIPTION
545 -- This procedure manages the update/creations of site uses
546 --
547 -- SCOPE - PRIVATE
548 --
549 -- EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
550 --
551 -- ARGUMENTS : IN: p_custoemr_id
552 -- p_address_id
553 -- p_site_use_flag - Y = create/activate a site use of this type
554 -- N = Inactive a site use ods this type if it exists
555 -- p_site_use_code - type of site use
556 -- OUT:
557 -- None
558 --
559 -- RETURNS : NONE
560 --
561 -- NOTES
562 --
563 -- MODIFICATION HISTORY - Created by Kevin Hudson
564 --
565 -- 07/25/01 Suresh P Bug No : 1348423 Changed the call
566 -- from arp_csu_pkg.update_su_status
567 -- to arh_csu_pkg.update_su_status .
568
569 Procedure manage_site_use ( p_customer_id in number,
570 p_address_id in number,
571 p_site_use_flag in varchar2,
572 p_site_use_code in varchar2,
573 x_msg_count OUT NOCOPY NUMBER,
574 x_msg_data OUT NOCOPY VARCHAR2,
575 x_return_status OUT NOCOPY VARCHAR2
576 ) is
577 l_site_use_id number;
578 l_site_use_status varchar2(1);
579 begin
580 --
581 arh_csu_pkg.site_use_exists( p_address_id => p_address_id,
582 p_site_use_code => p_site_use_code,
583 p_site_use_id => l_site_use_id,
584 p_site_use_status => l_site_use_status,
585 x_msg_count => x_msg_count,
586 x_msg_data => x_msg_data,
587 x_return_status => x_return_status
588 );
589 --
590 --
591 --
592 if ( p_site_use_flag = 'Y' ) then
593 --
594 --
595 --
596 if ( l_site_use_status = 'A') then
597 null;
598 elsif ( l_site_use_status = 'I') then
599 -- Bug Fix : 1348423
600 arh_csu_pkg.update_su_status(p_customer_id => p_customer_id,
601 p_address_id => p_address_id,
602 p_site_use_id => l_site_use_id,
603 p_site_use_code => p_site_use_code,
604 p_status =>'A');
605 else
606 null;
607 insert_site_use(p_customer_id, p_address_id,p_site_use_code,x_msg_count,
608 x_msg_data,x_return_status);
609
610 end if;
611 --
612 --
613 elsif ( p_site_use_flag = 'N' ) then
614 if ( l_site_use_status = 'A' ) then
615 -- Bug Fix : 1348423
616 arh_csu_pkg.update_su_status(p_customer_id => p_customer_id,
617 p_address_id => p_address_id,
618 p_site_use_id => l_site_use_id,
619 p_site_use_code => p_site_use_code,
620 p_status => 'I');
621 elsif ( l_site_use_status = 'I' ) then
622 null;
623 else
624 null;
625 end if;
626 end if;
627
628 --
629 --
630
631 end manage_site_use;
632 --
633 -- PROCEDURE
634 -- update_site_use_flag
635 --
636 -- DESCRIPTION
637 -- This procedure updates the denormalized site_use flags
638 -- on address. It should only be called from arp_csu_pkg.
639 --
640 -- SCOPE - PUBLIC
641 --
642 -- EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
643 --
644 -- ARGUMENTS : IN:
645 -- p_address_id -
646 -- p_site_use_code -
647 -- p_site_use_flag - Y = Active
648 -- P - Active and Primary
649 -- null - Inactive
650 -- OUT:
651 -- None
652 --
653 -- RETURNS : NONE
654 --
655 -- NOTES
656 --
657 -- MODIFICATION HISTORY - Created by Kevin Hudson
658 --
659 --
660 procedure update_site_use_flag ( p_address_id in number,
661 p_site_use_code in varchar2,
662 p_site_use_flag in varchar2 ) is
663 --
664 --
665 begin
666 --
667 -- The procedure is only callled if one of the flags requires updating
668 -- therfore the if then else logic has been pushed into the sql
669 --
670 update hz_cust_acct_sites
671 set bill_to_flag = decode(p_site_use_code,
672 'BILL_TO',p_site_use_flag,
673 bill_to_flag),
674 ship_to_flag = decode(p_site_use_code,
675 'SHIP_TO',p_site_use_flag,
676 ship_to_flag),
677 market_flag = decode(p_site_use_code,
678 'MARKET',p_site_use_flag,
679 market_flag)
680 where cust_acct_site_id = p_address_id;
681 --
682 if (SQL%NOTFOUND) then
683 raise NO_DATA_FOUND;
684 end if;
685 --
686 --
687 end update_site_use_flag;
688 --
689 --
690 --
691 procedure identifying_address_flag(x_party_id in number ) is
692
693 begin
694 update hz_party_sites set identifying_address_flag = 'N'
695 where party_id = x_party_id and identifying_address_flag = 'Y';
696
697 end identifying_address_flag;
698 --
699 --
700 -- PROCEDURE
701 -- check_unique_orig_system_ref
702 --
703 -- DESCRIPTION
704 -- This procedure checks that the orig_system_reference of an address
705 -- is unique
706 --
707 -- SCOPE - PUBLIC
708 --
709 -- EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
710 --
711 -- ARGUMENTS : IN: p_address_id
712 -- p_orig_system_reference
713 -- OUT:
714 -- None
715 --
716 -- RETURNS : NONE
717 -- The system does not allow update of orig_system_reference
718 --
719 -- NOTES
720 --
721 -- MODIFICATION HISTORY - Created by Kevin Hudson
722 --
723 --
724 procedure check_unique_orig_system_ref(p_orig_system_reference in varchar2) is
725 --
726 dummy number;
727 --
728 begin
729
730 --Bug Fix:1956757,hz_locations table is replaced by hz_cust_acct_sites in the following select statement.
731 select count(1)
732 into dummy
733 from hz_cust_acct_sites addr
734 where addr.orig_system_reference = p_orig_system_reference;
735 --
736 --
737 if ( dummy >=1 ) then
738 fnd_message.set_name('AR','AR_CUST_ADDR_REF_EXISTS');
739 app_exception.raise_exception;
740 end if;
741 --
742
743 end check_unique_orig_system_ref;
744 --
745 --
746 PROCEDURE delete_customer_alt_names(p_rowid in varchar2,
747 p_status in varchar2,
748 p_customer_id in number,
749 p_address_id in number ) is
750 l_status varchar2(1);
751 l_lock_status number;
752 l_site_use_id number;
753 begin
754 --
755 --
756 if (
757 -- ( nvl ( fnd_profile.value('AR_ALT_NAME_SEARCH') , 'N' ) = 'Y' ) and
758 ( p_status = 'I')
759 ) then
760 --
761 select status
762 into l_status
763 from hz_cust_acct_sites
764 where rowid = p_rowid;
765 --
766 if ( l_status = 'A' ) then
767 --
768 select site_use_id
769 into l_site_use_id
770 from hz_cust_site_uses
771 where cust_acct_site_id = p_address_id
772 and site_use_code = 'BILL_TO'
773 and status = 'A';
774
775 --
776 arp_cust_alt_match_pkg.lock_match ( p_customer_id, l_site_use_id ,
777 l_lock_status );
778 --
779 if ( l_lock_status = 1 ) then
780 --
781 -- Bug 928111: added additional parameter alt_name to
782 -- since it is not derivable passing as null.
783 arp_cust_alt_match_pkg.delete_match ( p_customer_id, l_site_use_id , NULL) ;
784 --
785 end if;
786 --
787 end if;
788 --
789 end if;
790 --
791 --
792 exception
793 when OTHERS then
794 arp_standard.debug('EXCEPTION: arh_addr_pkg.delete_customer_alt_names');
795 end delete_customer_alt_names;
796 --
797 --
798 --
799 PROCEDURE insert_row (
800 X_Address_Id IN OUT NOCOPY NUMBER,
801 X_Last_Update_Date DATE,
802 X_Last_Updated_By NUMBER,
803 X_Creation_Date DATE,
804 X_Created_By NUMBER,
805 X_Status VARCHAR2,
806 X_Orig_System_Reference IN OUT NOCOPY VARCHAR2,
807 X_Country VARCHAR2,
808 X_Address1 VARCHAR2,
809 X_Address2 VARCHAR2,
810 X_Address3 VARCHAR2,
811 X_Address4 VARCHAR2,
812 X_City VARCHAR2,
813 X_Postal_Code VARCHAR2,
814 X_State VARCHAR2,
815 X_Province VARCHAR2,
816 X_County VARCHAR2,
817 X_Last_Update_Login NUMBER,
818 X_Address_Key VARCHAR2,
819 X_Language VARCHAR2,
820 X_Attribute_Category VARCHAR2,
821 X_Attribute1 VARCHAR2,
822 X_Attribute2 VARCHAR2,
823 X_Attribute3 VARCHAR2,
824 X_Attribute4 VARCHAR2,
825 X_Attribute5 VARCHAR2,
826 X_Attribute6 VARCHAR2,
827 X_Attribute7 VARCHAR2,
828 X_Attribute8 VARCHAR2,
829 X_Attribute9 VARCHAR2,
830 X_Attribute10 VARCHAR2,
831 X_Attribute11 VARCHAR2,
832 X_Attribute12 VARCHAR2,
833 X_Attribute13 VARCHAR2,
834 X_Attribute14 VARCHAR2,
835 X_Attribute15 VARCHAR2,
836 X_Attribute16 VARCHAR2,
837 X_Attribute17 VARCHAR2,
838 X_Attribute18 VARCHAR2,
839 X_Attribute19 VARCHAR2,
840 X_Attribute20 VARCHAR2,
841 X_Address_warning out NOCOPY boolean,
842 X_Address_Lines_Phonetic VARCHAR2,
843 X_Global_Attribute_Category VARCHAR2,
844 X_Global_Attribute1 VARCHAR2,
845 X_Global_Attribute2 VARCHAR2,
846 X_Global_Attribute3 VARCHAR2,
847 X_Global_Attribute4 VARCHAR2,
848 X_Global_Attribute5 VARCHAR2,
849 X_Global_Attribute6 VARCHAR2,
850 X_Global_Attribute7 VARCHAR2,
851 X_Global_Attribute8 VARCHAR2,
852 X_Global_Attribute9 VARCHAR2,
853 X_Global_Attribute10 VARCHAR2,
854 X_Global_Attribute11 VARCHAR2,
855 X_Global_Attribute12 VARCHAR2,
856 X_Global_Attribute13 VARCHAR2,
857 X_Global_Attribute14 VARCHAR2,
858 X_Global_Attribute15 VARCHAR2,
859 X_Global_Attribute16 VARCHAR2,
860 X_Global_Attribute17 VARCHAR2,
861 X_Global_Attribute18 VARCHAR2,
862 X_Global_Attribute19 VARCHAR2,
863 X_Global_Attribute20 VARCHAR2,
864 X_Party_site_id IN OUT NOCOPY NUMBER,
865 X_Party_id NUMBER,
866 X_Location_id IN OUT NOCOPY NUMBER,
867 X_Party_Site_Number IN OUT NOCOPY VARCHAR2,
868 X_Identifying_address_flag VARCHAR2,
869 X_Cust_acct_site_id in out NOCOPY NUMBER,
870 X_Cust_account_id NUMBER,
871 X_su_Bill_To_Flag VARCHAR2,
872 X_su_Ship_To_Flag VARCHAR2,
873 X_su_Market_Flag VARCHAR2,
874 X_su_stmt_flag VARCHAR2,
875 X_su_dun_flag VARCHAR2,
876 X_su_legal_flag VARCHAR2,
877 X_Customer_Category VARCHAR2,
878 X_Key_Account_Flag VARCHAR2,
879 X_Territory_id NUMBER,
880 X_ece_tp_location_code VARCHAR2,
881 x_address_mode VARCHAR2,
882 x_territory VARCHAR2,
883 x_translated_customer_name VARCHAR2,
884 x_sales_tax_geo_code VARCHAR2,
885 x_sale_tax_inside_city_limits VARCHAR2,
886 x_ADDRESSEE VARCHAR2,
887 x_shared_party_site IN VARCHAR2,
888 x_update_account_site IN VARCHAR2,
889 x_create_location_party_site IN VARCHAR2,
890 x_msg_count OUT NOCOPY NUMBER,
891 x_msg_data OUT NOCOPY VARCHAR2,
892 x_return_status OUT NOCOPY VARCHAR2,
893 --Bug#2689667 {
894 x_description IN VARCHAR2 DEFAULT NULL,
895 x_short_description IN VARCHAR2 DEFAULT NULL,
896 x_floor IN VARCHAR2 DEFAULT NULL,
897 x_house_number IN VARCHAR2 DEFAULT NULL,
898 x_location_directions IN VARCHAR2 DEFAULT NULL,
899 x_postal_plus4_code IN VARCHAR2 DEFAULT NULL,
900 x_po_box_number IN VARCHAR2 DEFAULT NULL,
901 x_street IN VARCHAR2 DEFAULT NULL,
902 x_street_number IN VARCHAR2 DEFAULT NULL,
903 x_street_suffix IN VARCHAR2 DEFAULT NULL,
904 x_suite IN VARCHAR2 DEFAULT NULL,
905 --}
906 /*Bug 3976386 MOAC changes*/
907 X_ORG_ID IN NUMBER DEFAULT NULL
908
909 )
910
911
912 IS
913
914 location_rec hz_location_v2pub.location_rec_type;
915 psite_rec hz_party_site_v2pub.party_site_rec_type;
916 asite_rec hz_cust_account_site_v2pub.cust_acct_site_rec_type;
917 tmp_var VARCHAR2(2000);
918 i number;
919 x_loc_id number;
920 tmp_var1 VARCHAR2(2000);
921 i_create_location VARCHAR2(1) := 'Y';
922 l_count NUMBER;
923 invalid_location EXCEPTION;
924 pragma EXCEPTION_INIT(invalid_location, -20000);
925
926 BEGIN
927
928 ---------------------------------------------------
929 -- Preparation of the flag used by the real process
930 ---------------------------------------------------
931 -- Party Site and Related Location
932 --
933 IF x_shared_party_site = 'N' THEN
934 IF x_location_id is null THEN
935 l_count := 1;
936
937 WHILE l_count > 0 loop
938 select hr_locations_s.nextval into x_location_id from dual;
939
940 select count(*) into l_count
941 from hz_locations
942 where location_id = x_location_id;
943
944 END LOOP;
945 ELSE
946 i_create_location := 'N';
947 END IF;
948 END IF;
949 --
950 IF x_shared_party_site = 'Y' THEN
951 i_create_location := 'N';
952 END IF;
953 --
954 --
955 IF ( x_orig_system_reference is null ) THEN
956 x_orig_system_reference := x_address_id;
957 END IF;
958 --
959 --
960 IF x_create_location_party_site = 'Y' THEN
961
962 l_count := 1;
963
964 WHILE l_count > 0 loop
965 select hr_locations_s.nextval into x_location_id from dual;
966
967 select count(*) into l_count
968 from hz_locations
969 where location_id = x_location_id;
970
971 END LOOP;
972 i_create_location := 'Y';
973 END IF;
974 --
975 IF x_create_location_party_site = 'N' THEN
976 check_unique_orig_system_ref(p_orig_system_reference => x_orig_system_reference);
977 --
978 check_unique_edi_location(p_edi_location => X_ece_tp_location_code,
979 p_customer_id => x_cust_account_id,
980 p_orig_system_reference => x_orig_system_reference
981 );
982 END IF;
983 --
984 ---------------------------------
985 -- Fill the required Record types
986 ---------------------------------
987 -- Locations REC TYPE
988 --
989 location_rec.location_id := X_location_Id;
990 location_rec.orig_system_reference := x_location_id ;
991 location_rec.country := X_Country;
992 location_rec.address1 := X_Address1;
993 location_rec.address2 := X_Address2;
994 location_rec.address3 := X_Address3;
995 location_rec.address4 := X_Address4;
996 location_rec.city := X_City;
997 location_rec.postal_code := X_Postal_Code;
998 location_rec.state := X_State;
999 location_rec.province := X_Province;
1000 location_rec.county := X_County;
1001 location_rec.address_key := X_Address_Key;
1002 location_rec.language := X_Language;
1003 location_rec.sales_tax_geocode := x_sales_tax_geo_code;
1004 location_rec.sales_tax_inside_city_limits := x_sale_tax_inside_city_limits;
1005 location_rec.address_lines_phonetic := X_Address_Lines_Phonetic;
1006 --Start bug fix 2910364, Commented the following lines of code
1007 -- location_rec.Attribute_Category := X_Attribute_Category;
1008 -- location_rec.attribute1 := X_Attribute1;
1009 -- location_rec.attribute2 := X_Attribute2;
1010 -- location_rec.attribute3 := X_Attribute3;
1011 -- location_rec.attribute4 := X_Attribute4;
1012 -- location_rec.attribute5 := X_Attribute5;
1013 -- location_rec.attribute6 := X_Attribute6;
1014 -- location_rec.attribute7 := X_Attribute7;
1015 -- location_rec.attribute8 := X_Attribute8;
1016 -- location_rec.attribute9 := X_Attribute9;
1017 -- location_rec.attribute10 := X_Attribute10;
1018 -- location_rec.attribute11 := X_Attribute11;
1019 -- location_rec.attribute12 := X_Attribute12;
1020 -- location_rec.attribute13 := X_Attribute13;
1021 -- location_rec.attribute14 := X_Attribute14;
1022 -- location_rec.attribute15 := X_Attribute15;
1023 -- location_rec.attribute16 := X_Attribute16;
1024 -- location_rec.attribute17 := X_Attribute17;
1025 -- location_rec.attribute18 := X_Attribute18;
1026 -- location_rec.attribute19 := X_Attribute19;
1027 -- location_rec.attribute20 := X_Attribute20;
1028 --End bug fix 2910364
1029 --Bug#2689667 {
1030 location_rec.description := x_description;
1031 location_rec.short_description := x_short_description;
1032 location_rec.location_directions := x_location_directions;
1033 location_rec.postal_plus4_code := x_postal_plus4_code;
1034 --{HYU BUG#5209119
1035 -- location_rec.floor := x_floor;
1036 -- location_rec.house_number := x_house_number;
1037 -- location_rec.po_box_number := x_po_box_number;
1038 -- location_rec.street := x_street;
1039 -- location_rec.street_number := x_street_number;
1040 -- location_rec.street_suffix := x_street_suffix;
1041 -- location_rec.suite := x_suite;
1042 --}
1043 location_rec.created_by_module := 'TCA_FORM_WRAPPER';
1044 --
1045 -- Party Site REC TYPE
1046 --
1047 psite_rec.party_site_id := X_Party_site_id;
1048 psite_rec.party_id := X_Party_id;
1049 psite_rec.location_id := x_location_id;
1050 psite_rec.party_site_number := x_party_site_number;
1051 psite_rec.orig_system_reference := x_party_site_id;
1052 psite_rec.identifying_address_flag := x_identifying_address_flag;
1053 --{HYUBUG#5209119
1054 --psite_rec.language := x_language;
1055 --}
1056 psite_rec.status := x_status;
1057 --Start bug fix 2910364, Commented the following lines of code
1058 -- psite_rec.Attribute_Category := X_Attribute_Category;
1059 -- psite_rec.attribute1 := X_Attribute1;
1060 -- psite_rec.attribute2 := X_Attribute2;
1061 -- psite_rec.attribute3 := X_Attribute3;
1062 -- psite_rec.attribute4 := X_Attribute4;
1063 -- psite_rec.attribute5 := X_Attribute5;
1064 -- psite_rec.attribute6 := X_Attribute6;
1065 -- psite_rec.attribute7 := X_Attribute7;
1066 -- psite_rec.attribute8 := X_Attribute8;
1067 -- psite_rec.attribute9 := X_Attribute9;
1068 -- psite_rec.attribute10 := X_Attribute10;
1069 -- psite_rec.attribute11 := X_Attribute11;
1070 -- psite_rec.attribute12 := X_Attribute12;
1071 -- psite_rec.attribute13 := X_Attribute13;
1072 -- psite_rec.attribute14 := X_Attribute14;
1073 -- psite_rec.attribute15 := X_Attribute15;
1074 -- psite_rec.attribute16 := X_Attribute16;
1075 -- psite_rec.attribute17 := X_Attribute17;
1076 -- psite_rec.attribute18 := X_Attribute18;
1077 -- psite_rec.attribute19 := X_Attribute19;
1078 -- psite_rec.attribute20 := X_Attribute20;
1079 --End bug fix 2910364
1080 psite_rec.ADDRESSEE := X_ADDRESSEE;
1081 psite_rec.created_by_module := 'TCA_FORM_WRAPPER';
1082 --
1083 -- Customer Account Site REC TYPE
1084 --
1085 asite_rec.cust_acct_site_id := X_Cust_acct_site_id;
1086 asite_rec.cust_account_id := X_Cust_account_id;
1087 asite_rec.party_site_id := X_Party_site_id;
1088 -- fix for bug 1449356
1089 -- odified to pass X_Orig_System_Reference instead of X_Cust_acct_site_id
1090 -- so that if user passed reference no, that will be saved.
1091 -- previously - asite_rec.orig_system_reference := X_Cust_acct_site_id;
1092 asite_rec.orig_system_reference := X_Orig_System_Reference;
1093 asite_rec.status := x_status;
1094 asite_rec.customer_category_code:= X_Customer_Category;
1095
1096 -- asite_rec.language := X_language;
1097
1098 asite_rec.key_account_flag := X_Key_Account_Flag;
1099 asite_rec.territory_id := X_Territory_id;
1100 asite_rec.Attribute_Category := X_Attribute_Category;
1101 asite_rec.attribute1 := X_Attribute1;
1102 asite_rec.attribute2 := X_Attribute2;
1103 asite_rec.attribute3 := X_Attribute3;
1104 asite_rec.attribute4 := X_Attribute4;
1105 asite_rec.attribute5 := X_Attribute5;
1106 asite_rec.attribute6 := X_Attribute6;
1107 asite_rec.attribute7 := X_Attribute7;
1108 asite_rec.attribute8 := X_Attribute8;
1109 asite_rec.attribute9 := X_Attribute9;
1110 asite_rec.attribute10 := X_Attribute10;
1111 asite_rec.attribute11 := X_Attribute11;
1112 asite_rec.attribute12 := X_Attribute12;
1113 asite_rec.attribute13 := X_Attribute13;
1114 asite_rec.attribute14 := X_Attribute14;
1115 asite_rec.attribute15 := X_Attribute15;
1116 asite_rec.attribute16 := X_Attribute16;
1117 asite_rec.attribute17 := X_Attribute17;
1118 asite_rec.attribute18 := X_Attribute18;
1119 asite_rec.attribute19 := X_Attribute19;
1120 asite_rec.attribute20 := X_Attribute20;
1121 asite_rec.Global_Attribute_Category := X_Global_Attribute_Category;
1122 asite_rec.Global_Attribute1 := X_Global_Attribute1;
1123 asite_rec.Global_Attribute2 := X_Global_Attribute2;
1124 asite_rec.Global_Attribute3 := X_Global_Attribute3;
1125 asite_rec.Global_Attribute4 := X_Global_Attribute4;
1126 asite_rec.Global_Attribute5 := X_Global_Attribute5;
1127 asite_rec.Global_Attribute6 := X_Global_Attribute6;
1128 asite_rec.Global_Attribute7 := X_Global_Attribute7;
1129 asite_rec.Global_Attribute8 := X_Global_Attribute8;
1130 asite_rec.Global_Attribute9 := X_Global_Attribute9;
1131 asite_rec.Global_Attribute10 := X_Global_Attribute10;
1132 asite_rec.Global_Attribute11 := X_Global_Attribute11;
1133 asite_rec.Global_Attribute12 := X_Global_Attribute12;
1134 asite_rec.Global_Attribute13 := X_Global_Attribute13;
1135 asite_rec.Global_Attribute14 := X_Global_Attribute14;
1136 asite_rec.Global_Attribute15 := X_Global_Attribute15;
1137 asite_rec.Global_Attribute16 := X_Global_Attribute16;
1138 asite_rec.Global_Attribute17 := X_Global_Attribute17;
1139 asite_rec.Global_Attribute18 := X_Global_Attribute18;
1140 asite_rec.Global_Attribute19 := X_Global_Attribute19;
1141 asite_rec.Global_Attribute20 := X_Global_Attribute20;
1142 asite_rec.ece_tp_location_code := X_ece_tp_location_code;
1143 asite_rec.territory := X_Territory;
1144 asite_rec.translated_customer_name := X_Translated_Customer_Name;
1145 asite_rec.created_by_module := 'TCA_FORM_WRAPPER';
1146 /*Bug 3976386 MOAC changes*/
1147 asite_rec.ORG_ID := X_ORG_ID;
1148 --
1149 --
1150 --
1151 IF x_create_location_party_site = 'Y' THEN
1152 psite_rec.party_site_id := null;
1153 psite_rec.orig_system_reference := null;
1154 psite_rec.party_site_number := null;
1155 END IF;
1156
1157 --
1158 --
1159 -- { Party Site and Related Location creation process
1160 --
1161 IF i_create_location = 'Y' THEN
1162
1163 HZ_LOCATION_V2PUB.create_location (
1164 p_location_rec => location_rec,
1165 x_location_id => x_location_id,
1166 x_return_status => x_return_status,
1167 x_msg_count => x_msg_count,
1168 x_msg_data => x_msg_data );
1169
1170 IF x_msg_count > 1 THEN
1171 FOR i IN 1..x_msg_count LOOP
1172 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
1173 tmp_var1 := tmp_var1 || ' '|| tmp_var;
1174 END LOOP;
1175 x_msg_data := tmp_var1;
1176 END IF;
1177
1178 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1179 return;
1180 END IF;
1181
1182
1183 HZ_PARTY_SITE_V2PUB.create_party_site (
1184 p_party_site_rec => psite_rec,
1185 x_party_site_id => x_party_site_id,
1186 x_party_site_number => x_party_site_number,
1187 x_return_status => x_return_status,
1188 x_msg_count => x_msg_count,
1189 x_msg_data => x_msg_data );
1190
1191 IF x_msg_count > 1 THEN
1192 FOR i IN 1..x_msg_count LOOP
1193 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
1194 tmp_var1 := tmp_var1 || ' '|| tmp_var;
1195 END LOOP;
1196 x_msg_data := tmp_var1;
1197 END IF;
1198
1199 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1200 return;
1201 END IF;
1202
1203
1204 END IF; -- if i_create_location = 'Y'
1205 -- }
1206
1207
1208 --
1209 -- Tax Assignment based on party site
1210 -- {
1211 IF x_update_account_site = 'Y' THEN
1212
1213 HZ_TAX_ASSIGNMENT_V2PUB.create_loc_assignment(
1214 p_location_id => x_location_id,
1215 p_lock_flag => FND_API.G_TRUE,
1216 p_created_by_module => 'FORM-WRAPPER',
1217 p_application_id => -222,
1218 x_return_status => x_return_status,
1219 x_msg_count => x_msg_count,
1220 x_msg_data => x_msg_data,
1221 x_loc_id => x_loc_id );
1222
1223 IF x_msg_count > 1 THEN
1224 FOR i IN 1..x_msg_count LOOP
1225 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
1226 tmp_var1 := tmp_var1 || ' '|| tmp_var;
1227 END LOOP;
1228 x_msg_data := tmp_var1;
1229 END IF;
1230
1231 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1232 return;
1233 END IF;
1234
1235
1236 UPDATE hz_cust_acct_sites
1237 SET party_site_id = x_party_site_id
1238 WHERE cust_acct_site_id = x_cust_acct_site_id;
1239
1240 END IF;
1241 -- }
1242
1243
1244 IF i_create_location = 'Y' THEN
1245 asite_rec.party_site_id := X_Party_site_id;
1246 END IF;
1247
1248 --
1249 -- Customer Account Site can either be related to a party site or new
1250 -- {
1251 IF x_update_account_site = 'N' THEN
1252
1253 HZ_CUST_ACCOUNT_SITE_V2PUB.create_cust_acct_site (
1254 p_cust_acct_site_rec => asite_rec,
1255 x_cust_acct_site_id => x_cust_acct_site_id,
1256 x_return_status => x_return_status,
1257 x_msg_count => x_msg_count,
1258 x_msg_data => x_msg_data );
1259
1260 IF x_msg_count > 1 THEN
1261 FOR i IN 1..x_msg_count LOOP
1262 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
1263 tmp_var1 := tmp_var1 || ' '|| tmp_var;
1264 END LOOP;
1265 x_msg_data := tmp_var1;
1266 END IF;
1267
1268 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1269 return;
1270 END IF;
1271
1272
1273 x_address_id := x_cust_acct_site_id;
1274
1275 END IF;
1276 --}
1277
1278 arp_standard.debug('ARHADDRB:Insert Row: After Insert into hz_cust_Acct_sites');
1279
1280 IF ( x_address_mode = 'QUICK' ) THEN
1281 IF ( x_su_bill_to_flag = 'Y' ) THEN
1282 insert_site_use (X_Cust_account_id,x_address_id,'BILL_TO',x_msg_count,x_msg_data, x_return_status);
1283 END IF;
1284 --
1285 arp_standard.debug('After insert_site_use bill_to call ');
1286 --
1287 IF ( x_su_ship_to_flag = 'Y' ) THEN
1288 insert_site_use (X_Cust_account_id,x_address_id,'SHIP_TO',x_msg_count,x_msg_data, x_return_status);
1289 END IF;
1290 --
1291 arp_standard.debug('After insert_site_use ship_to call ');
1292 --
1293 IF ( x_su_market_flag = 'Y' ) THEN
1294 insert_site_use (X_Cust_account_id,x_address_id,'MARKET',x_msg_count,x_msg_data, x_return_status);
1295 END IF;
1296 --
1297 arp_standard.debug('After insert_site_use market call ');
1298 --
1299 IF ( x_su_stmt_flag = 'Y' ) THEN
1300 insert_site_use (X_Cust_account_id,x_address_id,'STMTS',x_msg_count,x_msg_data, x_return_status);
1301 END IF;
1302 --
1303 arp_standard.debug('After insert_site_use stmts call ');
1304 --
1305 IF ( x_su_dun_flag = 'Y' ) THEN
1306 insert_site_use (X_Cust_account_id,x_address_id,'DUN',x_msg_count,x_msg_data, x_return_status);
1307 END IF;
1308 --
1309 arp_standard.debug('After insert_site_use dun call ');
1310 --
1311 IF ( x_su_legal_flag = 'Y' ) THEN
1312 insert_site_use (X_Cust_account_id,x_address_id,'LEGAL',x_msg_count,x_msg_data, x_return_status);
1313 END IF;
1314 --
1315 arp_standard.debug('After insert_site_use legal call ');
1316 --
1317 ELSIF ( x_address_mode = 'STANDARD' ) then
1318 null;
1319 ELSE
1320 app_exception.invalid_argument('arp_addr_pkg.Insert_Row', 'x_address_mode', x_address_mode);
1321 END IF;
1322 --
1323 --
1324 IF ( arp_standard.sysparm.address_validation = 'WARN' ) THEN
1325 x_address_warning := arp_adds.location_segment_inserted;
1326 END IF;
1327 --
1328 --
1329 arp_standard.debug('AROADDRB:Insert Row: END');
1330 --
1331 END Insert_Row;
1332
1333
1334 PROCEDURE update_row (
1335 X_Address_Id NUMBER,
1336 X_Last_Update_Date IN OUT NOCOPY DATE,
1337 X_party_site_Last_Update_Date IN OUT NOCOPY DATE,
1338 X_loc_Last_Update_Date IN OUT NOCOPY DATE,
1339 X_Last_Updated_By NUMBER,
1340 X_Status VARCHAR2,
1341 X_Orig_System_Reference VARCHAR2,
1342 X_Country VARCHAR2,
1343 X_Address1 VARCHAR2,
1344 X_Address2 VARCHAR2,
1345 X_Address3 VARCHAR2,
1346 X_Address4 VARCHAR2,
1347 X_City VARCHAR2,
1348 X_Postal_Code VARCHAR2,
1349 X_State VARCHAR2,
1350 X_Province VARCHAR2,
1351 X_County VARCHAR2,
1352 X_Last_Update_Login NUMBER,
1353 X_Address_Key VARCHAR2,
1354 X_Language VARCHAR2,
1355 X_Attribute_Category VARCHAR2,
1356 X_Attribute1 VARCHAR2,
1357 X_Attribute2 VARCHAR2,
1358 X_Attribute3 VARCHAR2,
1359 X_Attribute4 VARCHAR2,
1360 X_Attribute5 VARCHAR2,
1361 X_Attribute6 VARCHAR2,
1362 X_Attribute7 VARCHAR2,
1363 X_Attribute8 VARCHAR2,
1364 X_Attribute9 VARCHAR2,
1365 X_Attribute10 VARCHAR2,
1366 X_Attribute11 VARCHAR2,
1367 X_Attribute12 VARCHAR2,
1368 X_Attribute13 VARCHAR2,
1369 X_Attribute14 VARCHAR2,
1370 X_Attribute15 VARCHAR2,
1371 X_Attribute16 VARCHAR2,
1372 X_Attribute17 VARCHAR2,
1373 X_Attribute18 VARCHAR2,
1374 X_Attribute19 VARCHAR2,
1375 X_Attribute20 VARCHAR2,
1376 X_Address_warning OUT NOCOPY BOOLEAN,
1377 X_Address_Lines_Phonetic VARCHAR2,
1378 X_Global_Attribute_Category VARCHAR2,
1379 X_Global_Attribute1 VARCHAR2,
1380 X_Global_Attribute2 VARCHAR2,
1381 X_Global_Attribute3 VARCHAR2,
1382 X_Global_Attribute4 VARCHAR2,
1383 X_Global_Attribute5 VARCHAR2,
1384 X_Global_Attribute6 VARCHAR2,
1385 X_Global_Attribute7 VARCHAR2,
1386 X_Global_Attribute8 VARCHAR2,
1387 X_Global_Attribute9 VARCHAR2,
1388 X_Global_Attribute10 VARCHAR2,
1389 X_Global_Attribute11 VARCHAR2,
1390 X_Global_Attribute12 VARCHAR2,
1391 X_Global_Attribute13 VARCHAR2,
1392 X_Global_Attribute14 VARCHAR2,
1393 X_Global_Attribute15 VARCHAR2,
1394 X_Global_Attribute16 VARCHAR2,
1395 X_Global_Attribute17 VARCHAR2,
1396 X_Global_Attribute18 VARCHAR2,
1397 X_Global_Attribute19 VARCHAR2,
1398 X_Global_Attribute20 VARCHAR2,
1399 X_Party_site_id NUMBER,
1400 X_Party_id NUMBER,
1401 X_Location_id NUMBER,
1402 X_Party_Site_Number VARCHAR2,
1403 X_Identifying_address_flag VARCHAR2,
1404 X_Cust_acct_site_id NUMBER,
1405 X_Cust_account_id NUMBER,
1406 X_su_Bill_To_Flag VARCHAR2,
1407 X_su_Ship_To_Flag VARCHAR2,
1408 X_su_Market_Flag VARCHAR2,
1409 X_su_stmt_flag VARCHAR2,
1410 X_su_dun_flag VARCHAR2,
1411 X_su_legal_flag VARCHAR2,
1412 X_Customer_Category VARCHAR2,
1413 X_Key_Account_Flag VARCHAR2,
1414 X_Territory_id NUMBER,
1415 X_ece_tp_location_code VARCHAR2,
1416 x_address_mode VARCHAR2,
1417 X_Territory VARCHAR2,
1418 X_Translated_Customer_Name VARCHAR2,
1419 X_Sales_Tax_Geocode VARCHAR2,
1420 X_Sales_Tax_Inside_City_Limits VARCHAR2,
1421 x_ADDRESSEE VARCHAR2,
1422 x_msg_count OUT NOCOPY NUMBER,
1423 x_msg_data OUT NOCOPY VARCHAR2,
1424 x_return_status OUT NOCOPY VARCHAR2,
1425 x_object_version IN NUMBER,
1426 x_object_version_party_site IN NUMBER,
1427 x_object_version_location IN NUMBER,
1428 --Bug#2689667 {
1429 x_description IN VARCHAR2 DEFAULT NULL,
1430 x_short_description IN VARCHAR2 DEFAULT NULL,
1431 x_floor IN VARCHAR2 DEFAULT NULL,
1432 x_house_number IN VARCHAR2 DEFAULT NULL,
1433 x_location_directions IN VARCHAR2 DEFAULT NULL,
1434 x_postal_plus4_code IN VARCHAR2 DEFAULT NULL,
1435 x_po_box_number IN VARCHAR2 DEFAULT NULL,
1436 x_street IN VARCHAR2 DEFAULT NULL,
1437 x_street_number IN VARCHAR2 DEFAULT NULL,
1438 x_street_suffix IN VARCHAR2 DEFAULT NULL,
1439 x_suite IN VARCHAR2 DEFAULT NULL,
1440 --}
1441 /*Bug 3976386 MOAC changes*/
1442 X_ORG_ID IN NUMBER DEFAULT NULL
1443 )
1444
1445 IS
1446 --
1447 --
1448 l_site_use_id number;
1449 l_site_use_status VARCHAR2(1);
1450 location_rec hz_location_v2pub.location_rec_type;
1451 psite_rec hz_party_site_v2pub.party_site_rec_type;
1452 asite_rec hz_cust_account_site_v2pub.cust_acct_site_rec_type;
1453 tmp_var VARCHAR2(2000);
1454 i number;
1455 tmp_var1 VARCHAR2(2000);
1456 --
1457 --
1458 CURSOR cu_version_location IS
1459 SELECT ROWID,
1460 OBJECT_VERSION_NUMBER,
1461 LAST_UPDATE_DATE
1462 FROM HZ_LOCATIONS
1463 WHERE LOCATION_ID = x_location_id;
1464
1465 CURSOR cu_version_party_site IS
1466 SELECT ROWID,
1467 OBJECT_VERSION_NUMBER,
1468 LAST_UPDATE_DATE
1469 FROM HZ_PARTY_SITES
1470 WHERE PARTY_SITE_ID = X_Party_site_id;
1471
1472 CURSOR cu_version_account_site IS
1473 SELECT ROWID,
1474 OBJECT_VERSION_NUMBER,
1475 LAST_UPDATE_DATE
1476 FROM HZ_CUST_ACCT_SITES
1477 WHERE CUST_ACCT_SITE_ID = X_address_id;
1478
1479 l_object_version_number NUMBER;
1480 l_rowid ROWID;
1481 l_last_update_date DATE;
1482 l_object_version_location NUMBER;
1483 l_object_version_party_site NUMBER;
1484 l_object_version_account_site NUMBER;
1485 --
1486 --
1487 invalid_location EXCEPTION;
1488 pragma exception_init(invalid_location, -20000);
1489 --
1490 --
1491 BEGIN
1492
1493 x_return_status := FND_API.G_RET_STS_SUCCESS;
1494
1495 -----------------------------------------------------------------------
1496 --{ For Backward compatibility when Object Version Number is not entered
1497 -----------------------------------------------------------------------
1498 -----------
1499 -- Location
1500 -----------
1501 IF x_object_version_location = -1 THEN
1502
1503 OPEN cu_version_location;
1504 FETCH cu_version_location INTO
1505 l_rowid ,
1506 l_object_version_number,
1507 l_last_update_date ;
1508 IF cu_version_location%NOTFOUND THEN
1509 FND_MESSAGE.SET_NAME('AR','HZ_API_NO_RECORD');
1510 FND_MESSAGE.SET_TOKEN('RECORD','hz_locations');
1511 FND_MESSAGE.SET_TOKEN('ID',x_location_id);
1512 FND_MSG_PUB.ADD;
1513 x_return_status := FND_API.G_RET_STS_ERROR;
1514 ELSE
1515 l_object_version_location := l_object_version_number;
1516 END IF;
1517 CLOSE cu_version_location;
1518 --
1519 IF TO_CHAR(X_loc_Last_Update_Date,'DD-MON-YYYY HH:MI:SS') <>
1520 TO_CHAR(l_last_update_date,'DD-MON-YYYY HH:MI:SS')
1521 THEN
1522 FND_MESSAGE.SET_NAME('AR', 'HZ_API_RECORD_CHANGED');
1523 FND_MESSAGE.SET_TOKEN('TABLE', 'hz_locations');
1524 FND_MSG_PUB.ADD;
1525 x_return_status := FND_API.G_RET_STS_ERROR;
1526 END IF;
1527 ELSE
1528 l_object_version_location := x_object_version_location;
1529 END IF;
1530 --
1531 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1532 RAISE FND_API.G_EXC_ERROR;
1533 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1534 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1535 END IF;
1536
1537 -------------
1538 -- Party Site
1539 --------------
1540 IF x_object_version_party_site = -1 THEN
1541 OPEN cu_version_party_site;
1542 FETCH cu_version_party_site INTO
1543 l_rowid ,
1544 l_object_version_number,
1545 l_last_update_date ;
1546 IF cu_version_party_site%NOTFOUND THEN
1547 FND_MESSAGE.SET_NAME('AR','HZ_API_NO_RECORD');
1548 FND_MESSAGE.SET_TOKEN('RECORD','hz_party_sites');
1549 FND_MESSAGE.SET_TOKEN('ID',x_party_site_id);
1550 FND_MSG_PUB.ADD;
1551 x_return_status := FND_API.G_RET_STS_ERROR;
1552 ELSE
1553 l_object_version_party_site := l_object_version_number;
1554 END IF;
1555 CLOSE cu_version_party_site;
1556 --
1557 IF TO_CHAR(X_party_site_Last_Update_Date,'DD-MON-YYYY HH:MI:SS') <>
1558 TO_CHAR(l_last_update_date,'DD-MON-YYYY HH:MI:SS')
1559 THEN
1560 FND_MESSAGE.SET_NAME('AR', 'HZ_API_RECORD_CHANGED');
1561 FND_MESSAGE.SET_TOKEN('TABLE', 'hz_party_sites');
1562 FND_MSG_PUB.ADD;
1563 x_return_status := FND_API.G_RET_STS_ERROR;
1564 END IF;
1565 ELSE
1566 l_object_version_party_site := x_object_version_party_site;
1567 END IF;
1568 --
1569
1570 IF x_msg_count > 1 THEN
1571 FOR i IN 1..x_msg_count LOOP
1572 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
1573 tmp_var1 := tmp_var1 || ' '|| tmp_var;
1574 END LOOP;
1575 x_msg_data := tmp_var1;
1576 END IF;
1577
1578 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1579 return;
1580 END IF;
1581
1582
1583 ---------------
1584 -- Account Site
1585 ---------------
1586 IF x_object_version = -1 THEN
1587
1588 OPEN cu_version_account_site;
1589 FETCH cu_version_account_site INTO
1590 l_rowid ,
1591 l_object_version_number,
1592 l_last_update_date ;
1593 IF cu_version_account_site%NOTFOUND THEN
1594 FND_MESSAGE.SET_NAME('AR','HZ_API_NO_RECORD');
1595 FND_MESSAGE.SET_TOKEN('RECORD','hz_cust_acct_sites');
1596 FND_MESSAGE.SET_TOKEN('ID',x_cust_acct_site_id);
1597 FND_MSG_PUB.ADD;
1598 x_return_status := FND_API.G_RET_STS_ERROR;
1599 ELSE
1600 l_object_version_account_site := l_object_version_number;
1601 END IF;
1602 CLOSE cu_version_account_site;
1603 --
1604 IF TO_CHAR(X_Last_Update_Date,'DD-MON-YYYY HH:MI:SS') <>
1605 TO_CHAR(l_last_update_date,'DD-MON-YYYY HH:MI:SS')
1606 THEN
1607 FND_MESSAGE.SET_NAME('AR', 'HZ_API_RECORD_CHANGED');
1608 FND_MESSAGE.SET_TOKEN('TABLE', 'hz_account_sites');
1609 FND_MSG_PUB.ADD;
1610 x_return_status := FND_API.G_RET_STS_ERROR;
1611 END IF;
1612 ELSE
1613 l_object_version_account_site := x_object_version;
1614 END IF;
1615 --
1616 IF x_msg_count > 1 THEN
1617 FOR i IN 1..x_msg_count LOOP
1618 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
1619 tmp_var1 := tmp_var1 || ' '|| tmp_var;
1620 END LOOP;
1621 x_msg_data := tmp_var1;
1622 END IF;
1623
1624 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1625 return;
1626 END IF;
1627
1628
1629 -----------------------------------------------------------------------
1630 --} For Backward compatibility when Object Version Number is not entered
1631 -----------------------------------------------------------------------
1632
1633 check_unique_edi_location(p_edi_location => X_ece_tp_location_code,
1634 p_customer_id => x_cust_account_id,
1635 p_orig_system_reference => x_orig_system_reference
1636 );
1637
1638 --------------------------
1639 -- Location V2 Record Type
1640 --------------------------
1641 location_rec.location_id := X_location_Id;
1642 location_rec.country := INIT_SWITCH(X_Country);
1643 location_rec.address1 := INIT_SWITCH(X_Address1);
1644 location_rec.address2 := INIT_SWITCH(X_Address2);
1645 location_rec.address3 := INIT_SWITCH(X_Address3);
1646 location_rec.address4 := INIT_SWITCH(X_Address4);
1647 location_rec.city := INIT_SWITCH(X_City);
1648 location_rec.postal_code := INIT_SWITCH(X_Postal_Code);
1649 location_rec.state := INIT_SWITCH(X_State);
1650 location_rec.province := INIT_SWITCH(X_Province);
1651 location_rec.county := INIT_SWITCH(X_County);
1652 location_rec.address_key := INIT_SWITCH(X_Address_Key);
1653 location_rec.language := INIT_SWITCH(X_Language);
1654 location_rec.address_lines_phonetic := INIT_SWITCH(X_Address_Lines_Phonetic);
1655 --Start bug fix 2910364, Commented the following lines of code
1656 -- location_rec.Attribute_Category := INIT_SWITCH(X_Attribute_Category);
1657 -- location_rec.attribute1 := INIT_SWITCH(X_Attribute1);
1658 -- location_rec.attribute2 := INIT_SWITCH(X_Attribute2);
1659 -- location_rec.attribute3 := INIT_SWITCH(X_Attribute3);
1660 -- location_rec.attribute4 := INIT_SWITCH(X_Attribute4);
1661 -- location_rec.attribute5 := INIT_SWITCH(X_Attribute5);
1662 -- location_rec.attribute6 := INIT_SWITCH(X_Attribute6);
1663 -- location_rec.attribute7 := INIT_SWITCH(X_Attribute7);
1664 -- location_rec.attribute8 := INIT_SWITCH(X_Attribute8);
1665 -- location_rec.attribute9 := INIT_SWITCH(X_Attribute9);
1666 -- location_rec.attribute10 := INIT_SWITCH(X_Attribute10);
1667 -- location_rec.attribute11 := INIT_SWITCH(X_Attribute11);
1668 -- location_rec.attribute12 := INIT_SWITCH(X_Attribute12);
1669 -- location_rec.attribute13 := INIT_SWITCH(X_Attribute13);
1670 -- location_rec.attribute14 := INIT_SWITCH(X_Attribute14);
1671 -- location_rec.attribute15 := INIT_SWITCH(X_Attribute15);
1672 -- location_rec.attribute16 := INIT_SWITCH(X_Attribute16);
1673 -- location_rec.attribute17 := INIT_SWITCH(X_Attribute17);
1674 -- location_rec.attribute18 := INIT_SWITCH(X_Attribute18);
1675 -- location_rec.attribute19 := INIT_SWITCH(X_Attribute19);
1676 -- location_rec.attribute20 := INIT_SWITCH(X_Attribute20);
1677 --End bug fix 2910364
1678 --Bug#2689667 {
1679 location_rec.description := INIT_SWITCH(x_description);
1680 location_rec.short_description := INIT_SWITCH(x_short_description);
1681 -- location_rec.floor := INIT_SWITCH(x_floor);
1682 -- location_rec.house_number := INIT_SWITCH(x_house_number) ;
1683 location_rec.location_directions := INIT_SWITCH(x_location_directions) ;
1684 location_rec.postal_plus4_code := INIT_SWITCH(x_postal_plus4_code) ;
1685 -- location_rec.po_box_number := INIT_SWITCH(x_po_box_number) ;
1686 -- location_rec.street := INIT_SWITCH(x_street);
1687 -- location_rec.street_number := INIT_SWITCH(x_street_number);
1688 -- location_rec.street_suffix := INIT_SWITCH(x_street_suffix) ;
1689 -- location_rec.suite := INIT_SWITCH(x_suite) ;
1690 --}
1691 -- Bug - 1330693. Added the 1 line so that sales_tax_inside_city_limits is populated
1692 -- Bug - 1433433. Added the 1 line so that geocode is populated
1693 location_rec.Sales_Tax_Inside_City_Limits := INIT_SWITCH(X_Sales_Tax_Inside_City_Limits);
1694 location_rec.Sales_Tax_Geocode := INIT_SWITCH(X_Sales_Tax_Geocode);
1695
1696
1697 --{BUG#4037614
1698 check_printed_trx
1699 (p_location_id => X_location_Id,
1700 p_location_rec => location_rec,
1701 x_return_status => x_return_status,
1702 x_msg_data => x_msg_data,
1703 x_msg_count => x_msg_count);
1704
1705 IF x_msg_count > 1 THEN
1706 FOR i IN 1..x_msg_count LOOP
1707 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
1708 tmp_var1 := tmp_var1 || ' '|| tmp_var;
1709 END LOOP;
1710 x_msg_data := tmp_var1;
1711 END IF;
1712 --}
1713
1714 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1715 return;
1716 END IF;
1717 --}
1718
1719 ----------------------------
1720 -- Party Site V2 Record Type
1721 ----------------------------
1722 psite_rec.party_site_id := INIT_SWITCH(X_Party_site_id);
1723 -- psite_rec.party_id := X_Party_id;
1724 -- psite_rec.location_id := x_location_id;
1725 psite_rec.identifying_address_flag := INIT_SWITCH(x_identifying_address_flag);
1726
1727 --{HYU evaluation bug#5209119
1728 -- psite_rec.language := INIT_SWITCH(x_language);
1729 --}
1730 --Bug #1402584 de-activating a cust_acct_site from the customer standard form also de-activates the corresponding party_site.Hence commenting the psite_rec.status.
1731 --psite_rec.status := x_status;
1732 --Start bug fix 2910364, Commented the following lines of code
1733 -- psite_rec.Attribute_Category := INIT_SWITCH(X_Attribute_Category);
1734 -- psite_rec.attribute1 := INIT_SWITCH(X_Attribute1);
1735 -- psite_rec.attribute2 := INIT_SWITCH(X_Attribute2);
1736 -- psite_rec.attribute3 := INIT_SWITCH(X_Attribute3);
1737 -- psite_rec.attribute4 := INIT_SWITCH(X_Attribute4);
1738 -- psite_rec.attribute5 := INIT_SWITCH(X_Attribute5);
1739 -- psite_rec.attribute6 := INIT_SWITCH(X_Attribute6);
1740 -- psite_rec.attribute7 := INIT_SWITCH(X_Attribute7);
1741 -- psite_rec.attribute8 := INIT_SWITCH(X_Attribute8);
1742 -- psite_rec.attribute9 := INIT_SWITCH(X_Attribute9);
1743 -- psite_rec.attribute10 := INIT_SWITCH(X_Attribute10);
1744 -- psite_rec.attribute11 := INIT_SWITCH(X_Attribute11);
1745 -- psite_rec.attribute12 := INIT_SWITCH(X_Attribute12);
1746 -- psite_rec.attribute13 := INIT_SWITCH(X_Attribute13);
1747 -- psite_rec.attribute14 := INIT_SWITCH(X_Attribute14);
1748 -- psite_rec.attribute15 := INIT_SWITCH(X_Attribute15);
1749 -- psite_rec.attribute16 := INIT_SWITCH(X_Attribute16);
1750 -- psite_rec.attribute17 := INIT_SWITCH(X_Attribute17);
1751 -- psite_rec.attribute18 := INIT_SWITCH(X_Attribute18);
1752 -- psite_rec.attribute19 := INIT_SWITCH(X_Attribute19);
1753 -- psite_rec.attribute20 := INIT_SWITCH(X_Attribute20);
1754 --End bug fix 2910364
1755 psite_rec.ADDRESSEE := INIT_SWITCH(X_ADDRESSEE);
1756
1757 -----------------------------------
1758 -- Cust Account Site V2 Record Type
1759 -----------------------------------
1760 asite_rec.cust_acct_site_id := INIT_SWITCH(X_Cust_acct_site_id);
1761 -- asite_rec.cust_account_id := X_Cust_account_id;
1762 asite_rec.party_site_id := INIT_SWITCH(X_Party_site_id);
1763 asite_rec.status := INIT_SWITCH(x_status);
1764 -- asite_rec.bill_to_flag := X_su_Bill_To_Flag;
1765 -- asite_rec.market_flag := X_su_Market_Flag;
1766 -- asite_rec.ship_to_flag := X_su_Ship_To_Flag;
1767 asite_rec.customer_category_code := INIT_SWITCH(X_Customer_Category);
1768 --{HYU BUG#5209119
1769 -- asite_rec.language := INIT_SWITCH(X_language);
1770 --}
1771 asite_rec.key_account_flag := INIT_SWITCH(X_Key_Account_Flag);
1772 asite_rec.territory_id := INIT_SWITCH(X_Territory_id);
1773 asite_rec.Attribute_Category := INIT_SWITCH(X_Attribute_Category);
1774 asite_rec.attribute1 := INIT_SWITCH(X_Attribute1);
1775 asite_rec.attribute2 := INIT_SWITCH(X_Attribute2);
1776 asite_rec.attribute3 := INIT_SWITCH(X_Attribute3);
1777 asite_rec.attribute4 := INIT_SWITCH(X_Attribute4);
1778 asite_rec.attribute5 := INIT_SWITCH(X_Attribute5);
1779 asite_rec.attribute6 := INIT_SWITCH(X_Attribute6);
1780 asite_rec.attribute7 := INIT_SWITCH(X_Attribute7);
1781 asite_rec.attribute8 := INIT_SWITCH(X_Attribute8);
1782 asite_rec.attribute9 := INIT_SWITCH(X_Attribute9);
1783 asite_rec.attribute10 := INIT_SWITCH(X_Attribute10);
1784 asite_rec.attribute11 := INIT_SWITCH(X_Attribute11);
1785 asite_rec.attribute12 := INIT_SWITCH(X_Attribute12);
1786 asite_rec.attribute13 := INIT_SWITCH(X_Attribute13);
1787 asite_rec.attribute14 := INIT_SWITCH(X_Attribute14);
1788 asite_rec.attribute15 := INIT_SWITCH(X_Attribute15);
1789 asite_rec.attribute16 := INIT_SWITCH(X_Attribute16);
1790 asite_rec.attribute17 := INIT_SWITCH(X_Attribute17);
1791 asite_rec.attribute18 := INIT_SWITCH(X_Attribute18);
1792 asite_rec.attribute19 := INIT_SWITCH(X_Attribute19);
1793 asite_rec.attribute20 := INIT_SWITCH(X_Attribute20);
1794 asite_rec.Global_Attribute_Category := INIT_SWITCH(X_Global_Attribute_Category);
1795 asite_rec.Global_Attribute1 := INIT_SWITCH(X_Global_Attribute1);
1796 asite_rec.Global_Attribute2 := INIT_SWITCH(X_Global_Attribute2);
1797 asite_rec.Global_Attribute3 := INIT_SWITCH(X_Global_Attribute3);
1798 asite_rec.Global_Attribute4 := INIT_SWITCH(X_Global_Attribute4);
1799 asite_rec.Global_Attribute5 := INIT_SWITCH(X_Global_Attribute5);
1800 asite_rec.Global_Attribute6 := INIT_SWITCH(X_Global_Attribute6);
1801 asite_rec.Global_Attribute7 := INIT_SWITCH(X_Global_Attribute7);
1802 asite_rec.Global_Attribute8 := INIT_SWITCH(X_Global_Attribute8);
1803 asite_rec.Global_Attribute9 := INIT_SWITCH(X_Global_Attribute9);
1804 asite_rec.Global_Attribute10 := INIT_SWITCH(X_Global_Attribute10);
1805 asite_rec.Global_Attribute11 := INIT_SWITCH(X_Global_Attribute11);
1806 asite_rec.Global_Attribute12 := INIT_SWITCH(X_Global_Attribute12);
1807 asite_rec.Global_Attribute13 := INIT_SWITCH(X_Global_Attribute13);
1808 asite_rec.Global_Attribute14 := INIT_SWITCH(X_Global_Attribute14);
1809 asite_rec.Global_Attribute15 := INIT_SWITCH(X_Global_Attribute15);
1810 asite_rec.Global_Attribute16 := INIT_SWITCH(X_Global_Attribute16);
1811 asite_rec.Global_Attribute17 := INIT_SWITCH(X_Global_Attribute17);
1812 asite_rec.Global_Attribute18 := INIT_SWITCH(X_Global_Attribute18);
1813 asite_rec.Global_Attribute19 := INIT_SWITCH(X_Global_Attribute19);
1814 asite_rec.Global_Attribute20 := INIT_SWITCH(X_Global_Attribute20);
1815 asite_rec.ece_tp_location_code := INIT_SWITCH(X_ece_tp_location_code);
1816 asite_rec.territory := INIT_SWITCH(X_Territory);
1817 asite_rec.translated_customer_name := INIT_SWITCH(X_Translated_Customer_Name);
1818 /*Bug 3976386 MOAC changes*/
1819 asite_rec.ORG_ID := INIT_SWITCH(X_ORG_ID);
1820 ------------------
1821 -- Location update
1822 ------------------
1823 HZ_LOCATION_V2PUB.update_location (
1824 p_location_rec => location_rec,
1825 p_object_version_number => l_object_version_location,
1826 x_return_status => x_return_status,
1827 x_msg_count => x_msg_count,
1828 x_msg_data => x_msg_data );
1829
1830 IF x_msg_count > 1 THEN
1831 FOR i IN 1..x_msg_count LOOP
1832 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
1833 tmp_var1 := tmp_var1 || ' '|| tmp_var;
1834 END LOOP;
1835 x_msg_data := tmp_var1;
1836 END IF;
1837
1838 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1839 return;
1840 END IF;
1841
1842 SELECT last_update_date
1843 INTO x_loc_last_update_date
1844 FROM hz_locations
1845 WHERE location_id = x_location_id;
1846
1847 --------------------
1848 -- Party_Site update
1849 --------------------
1850 HZ_PARTY_SITE_V2PUB.update_party_site (
1851 p_party_site_rec => psite_rec,
1852 p_object_version_number => l_object_version_party_site,
1853 x_return_status => x_return_status,
1854 x_msg_count => x_msg_count,
1855 x_msg_data => x_msg_data );
1856
1857 IF x_msg_count > 1 THEN
1858 FOR i IN 1..x_msg_count LOOP
1859 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
1860 tmp_var1 := tmp_var1 || ' '|| tmp_var;
1861 END LOOP;
1862 x_msg_data := tmp_var1;
1863 END IF;
1864
1865 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1866 return;
1867 END IF;
1868
1869 SELECT last_update_date
1870 INTO x_party_site_last_update_date
1871 FROM hz_party_sites
1872 WHERE party_site_id = x_party_site_id;
1873
1874 ----------------------
1875 -- Account_Site update
1876 ----------------------
1877 HZ_CUST_ACCOUNT_SITE_V2PUB.update_cust_acct_site (
1878 p_cust_acct_site_rec => asite_rec,
1879 p_object_version_number => l_object_version_account_site,
1880 x_return_status => x_return_status,
1881 x_msg_count => x_msg_count,
1882 x_msg_data => x_msg_data );
1883
1884 IF x_msg_count > 1 THEN
1885 FOR i IN 1..x_msg_count LOOP
1886 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
1887 tmp_var1 := tmp_var1 || ' '|| tmp_var;
1888 END LOOP;
1889 x_msg_data := tmp_var1;
1890 END IF;
1891
1892 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1893 return;
1894 END IF;
1895
1896 SELECT last_update_date
1897 INTO x_last_update_date
1898 FROM hz_cust_acct_sites
1899 WHERE cust_acct_site_id = x_address_id;
1900
1901 --
1902 --
1903 -- Update the site use rows if running in QUICK_MODE
1904 --
1905 --
1906 IF ( x_address_mode = 'QUICK' ) THEN
1907 manage_site_use(p_customer_id => X_Cust_account_id,
1908 p_address_id => x_address_id,
1909 p_site_use_flag => x_su_bill_to_flag,
1910 p_site_use_code => 'BILL_TO',
1911 x_msg_count => x_msg_count,
1912 x_msg_data => x_msg_data,
1913 x_return_status => x_return_status );
1914 --
1915 manage_site_use(p_customer_id => X_Cust_account_id,
1916 p_address_id => x_address_id,
1917 p_site_use_flag => x_su_ship_to_flag,
1918 p_site_use_code => 'SHIP_TO',
1919 x_msg_count => x_msg_count,
1920 x_msg_data => x_msg_data,
1921 x_return_status => x_return_status );
1922 --
1923 manage_site_use(p_customer_id => X_Cust_account_id,
1924 p_address_id => x_address_id,
1925 p_site_use_flag => x_su_market_flag,
1926 p_site_use_code => 'MARKET',
1927 x_msg_count => x_msg_count,
1928 x_msg_data => x_msg_data,
1929 x_return_status => x_return_status );
1930 --
1931 manage_site_use(p_customer_id => X_Cust_account_id,
1932 p_address_id => x_address_id,
1933 p_site_use_flag => x_su_stmt_flag,
1934 p_site_use_code => 'STMTS',
1935 x_msg_count => x_msg_count,
1936 x_msg_data => x_msg_data,
1937 x_return_status => x_return_status);
1938 --
1939 manage_site_use(p_customer_id => X_Cust_account_id,
1940 p_address_id => x_address_id,
1941 p_site_use_flag => x_su_dun_flag,
1942 p_site_use_code => 'DUN',
1943 x_msg_count => x_msg_count,
1944 x_msg_data => x_msg_data,
1945 x_return_status => x_return_status);
1946 --
1947 manage_site_use(p_customer_id => X_Cust_account_id,
1948 p_address_id => x_address_id,
1949 p_site_use_flag => x_su_legal_flag,
1950 p_site_use_code => 'LEGAL',
1951 x_msg_count => x_msg_count,
1952 x_msg_data => x_msg_data,
1953 x_return_status => x_return_status);
1954
1955 ELSIF ( x_address_mode = 'STANDARD' ) THEN
1956 null;
1957 ELSE
1958 app_exception.invalid_argument('arp_addr_pkg.Insert_Row', 'x_address_mode',
1959 x_address_mode);
1960
1961 END IF;
1962 --
1963 --
1964 IF ( arp_standard.sysparm.address_validation = 'WARN' ) THEN
1965 x_address_warning := arp_adds.location_segment_inserted;
1966 END IF;
1967 --
1968 --
1969 END Update_Row;
1970
1971 --
1972 -----------------------------------------------------------------------------
1973 -- If a location_id is populated for an address record, it means the setup
1974 -- for tax is of type Sales Tax
1975 -----------------------------------------------------------------------------
1976 --
1977 FUNCTION location_exists (p_address_id IN Number
1978 ) return BOOLEAN is
1979 --
1980 location_identifier number;
1981 --
1982 BEGIN
1983
1984 select loc_assign.loc_id
1985 into location_identifier
1986 from hz_cust_acct_sites acct_site,
1987 hz_party_sites party_site,
1988 hz_locations loc,
1989 hz_loc_assignments loc_assign
1990 where acct_site.party_site_id = party_site.party_site_id
1991 and loc.location_id = party_site.location_id
1992 and loc.location_id = loc_assign.location_id
1993 and nvl(acct_site.org_id,-99) = nvl(loc_assign.org_id, -99)
1994 and acct_site.cust_acct_site_id = p_address_id;
1995
1996 if location_identifier is NULL then
1997 RETURN (FALSE);
1998 else
1999 RETURN (TRUE);
2000 end if;
2001 --
2002 END location_exists;
2003 --
2004 ---------------------------------------------------------------------------------------
2005 -- Receivable will not let you modify a customer address if:
2006 --
2007 -- (a) You have disabled: "Allow Change to Printed Transactions" (In AR System Options)
2008 -- Note: Using this option, you protect the invoice from direct and indirect changes
2009 --
2010 -- AND
2011 --
2012 -- (b) At least one PRINTED OR POSTED OR APPLIED transaction exists for this bill-to
2013 -- or ship-to site in Receivables and that transaction has Tax lines (Automatic
2014 -- or Manually entered).
2015 -- Reason: manual tax lines are audited in the same way that automatic tax lines
2016 -- are
2017 ---------------------------------------------------------------------------------------
2018 --
2019 FUNCTION transaction_exists (p_address_id IN number,
2020 p_customer_id IN number)
2021 return BOOLEAN is
2022 --
2023 invoice_count varchar2(1);
2024 check_value varchar2(1);
2025 --
2026 cursor ship_to_site is
2027 select site_use_id
2028 from hz_cust_site_uses
2029 where cust_acct_site_id = p_address_id
2030 and site_use_code = 'SHIP_TO';
2031 --
2032 cursor bill_to_site is
2033 select site_use_id
2034 from hz_cust_site_uses
2035 where cust_acct_site_id = p_address_id
2036 and site_use_code = 'BILL_TO';
2037 BEGIN
2038
2039 --
2040 BEGIN
2041
2042 -- check the flag: allow change to printed transaction is set to 'Y' or 'N'
2043 -- If it is set to 'Y', exit the function and customers are able to update the address
2044 -- If it is set to 'N', then check whether printed or posted transaction exits for
2045 -- bill-to or ship-to site,and the transaction has been applied
2046 -- and the transaction has any txa lines, if yes for above conditions, restrict update
2047 -- for customers on address component..
2048
2049 -- Note : This FUNCTION could have lived without customer_id argument i.e site_use_id
2050 -- was enough but then it would have meant to create two new indexes on :
2051 -- bill_to_site_use_id and ship_to_site_use_id. We have avoided this by using
2052 -- site_use_id (bill/ship) in conjunction with customer_id.
2053
2054 BEGIN
2055 select change_printed_invoice_flag
2056 into check_value
2057 from ar_system_parameters_all
2058 where org_id =
2059 (select org_id from hz_cust_acct_sites_all where cust_acct_site_id = p_address_id);
2060
2061 --Bug fix 2183072 Handled the exception.
2062 EXCEPTION
2063 WHEN NO_DATA_FOUND THEN
2064 FND_MESSAGE.SET_NAME( 'AR','AR_NO_ROW_IN_SYSTEM_PARAMETERS');
2065 FND_MSG_PUB.ADD;
2066 END ;
2067
2068 if check_value = 'Y' then
2069 RETURN (FALSE);
2070 end if;
2071 --
2072 for i in ship_to_site
2073 loop
2074 --
2075 select 'x'
2076 into invoice_count
2077 from dual
2078 where exists ( select 'y'
2079 from ra_cust_trx_types ctt,
2080 ra_customer_trx_lines ctl,
2081 ra_customer_trx ct
2082 where ct.cust_trx_type_id = ctt.cust_trx_type_id
2083 and ct.customer_trx_id = ctl.customer_trx_id
2084 and 'TAX' = ctl.line_type
2085 and (
2086 'Y' = arpt_sql_func_util.get_posted_flag
2087 ( ct.customer_trx_id,
2088 ctt.post_to_gl,
2089 ct.complete_flag
2090 ) -- posted_flag
2091 OR 'Y' = arpt_sql_func_util.get_activity_flag
2092 ( ct.customer_trx_id,
2093 ctt.accounting_affect_flag,
2094 ct.complete_flag,
2095 ctt.type,
2096 ct.initial_customer_trx_id,
2097 ct.previous_customer_trx_id
2098 ) -- activity_flag
2099 OR DECODE(ct.printing_last_printed,
2100 NULL,'N', 'Y') = 'Y'
2101 )
2102 and ct.ship_to_customer_id = p_customer_id
2103 and ct.ship_to_site_use_id = i.site_use_id
2104 );
2105
2106 RETURN (TRUE);
2107 exit;
2108
2109 end loop;
2110
2111 if invoice_count is NULL then
2112 --
2113 for j in bill_to_site
2114 loop
2115 select 'x'
2116 into invoice_count
2117 from dual
2118 where exists ( select 'y'
2119 from ra_cust_trx_types ctt,
2120 ra_customer_trx_lines ctl,
2121 ra_customer_trx ct
2122 where ct.cust_trx_type_id = ctt.cust_trx_type_id
2123 and ct.customer_trx_id = ctl.customer_trx_id
2124 and 'TAX' = ctl.line_type
2125 and (
2126 'Y' = arpt_sql_func_util.get_posted_flag
2127 ( ct.customer_trx_id,
2128 ctt.post_to_gl,
2129 ct.complete_flag
2130 ) -- posted_flag
2131 OR 'Y' = arpt_sql_func_util.get_activity_flag
2132 ( ct.customer_trx_id,
2133 ctt.accounting_affect_flag,
2134 ct.complete_flag,
2135 ctt.type,
2136 ct.initial_customer_trx_id,
2137 ct.previous_customer_trx_id
2138 ) -- activity_flag
2139 OR DECODE(ct.printing_last_printed,
2140 NULL,'N', 'Y') = 'Y'
2141 )
2142 and ct.bill_to_customer_id = p_customer_id
2143 and ct.bill_to_site_use_id = j.site_use_id
2144 );
2145
2146 RETURN (TRUE);
2147 exit;
2148
2149 end loop;
2150 --
2151 end if;
2152 EXCEPTION
2153 WHEN NO_DATA_FOUND THEN
2154 RETURN (FALSE);
2155 --
2156 END;
2157
2158 --
2159 if invoice_count is NULL then
2160 RETURN (FALSE);
2161 end if;
2162 --
2163 END transaction_exists;
2164 --
2165
2166 /* New Function created so that we check for transactions in all orgs */
2167 FUNCTION transaction_morg_exists
2168 (p_address_id IN number,
2169 p_customer_id IN number)
2170 RETURN BOOLEAN
2171 IS
2172 --
2173 invoice_count varchar2(1);
2174 check_value varchar2(1);
2175 --
2176 --{BUG#4037614 the tables accessed need to be all on site uses
2177 cursor ship_to_site is
2178 select site_use_id
2179 from hz_cust_site_uses_all
2180 where cust_acct_site_id = p_address_id
2181 and site_use_code = 'SHIP_TO';
2182 --
2183 cursor bill_to_site is
2184 select site_use_id
2185 from hz_cust_site_uses_all
2186 where cust_acct_site_id = p_address_id
2187 and site_use_code = 'BILL_TO';
2188 BEGIN
2189
2190 arp_debug.debug('transaction_morg_exists +');
2191 arp_debug.debug(' p_customer_id:'||p_customer_id);
2192 --
2193 BEGIN
2194
2195 -- check the flag: allow change to printed transaction is set to 'Y' or 'N'
2196 -- If it is set to 'Y', exit the function and customers are able to update the address
2197 -- If it is set to 'N', then check whether printed or posted transaction exits for
2198 -- bill-to or ship-to site,and the transaction has been applied
2199 -- and the transaction has any txa lines, if yes for above conditions, restrict update
2200 -- for customers on address component..
2201
2202 -- Note : This FUNCTION could have lived without customer_id argument i.e site_use_id
2203 -- was enough but then it would have meant to create two new indexes on :
2204 -- bill_to_site_use_id and ship_to_site_use_id. We have avoided this by using
2205 -- site_use_id (bill/ship) in conjunction with customer_id.
2206
2207 BEGIN
2208 arp_debug.debug('transaction_morg_exists.p_address_id: ' || p_address_id);
2209 select change_printed_invoice_flag
2210 into check_value
2211 from ar_system_parameters_all
2212 where org_id =
2213 (select org_id from hz_cust_acct_sites_all where cust_acct_site_id = p_address_id);
2214
2215 --Bug fix 2183072 Handled the exception.
2216 EXCEPTION
2217 WHEN NO_DATA_FOUND THEN
2218 FND_MESSAGE.SET_NAME( 'AR','AR_NO_ROW_IN_SYSTEM_PARAMETERS');
2219 FND_MSG_PUB.ADD;
2220 arp_debug.debug(' EXCEPTION AR_NO_ROW_IN_SYSTEM_PARAMETERS in transaction_morg_exists ');
2221 END ;
2222 arp_debug.debug(' check_value :'||check_value);
2223
2224 IF check_value = 'Y' THEN
2225 arp_debug.debug('transaction_morg_exists - RESULT : FALSE');
2226 RETURN (FALSE);
2227 END IF;
2228 --
2229 for i in ship_to_site
2230 loop
2231 --
2232 BEGIN
2233 arp_debug.debug(' cursor SHIP_TO_SITE site_use_id retrieved:'||i.site_use_id);
2234 select 'x'
2235 into invoice_count
2236 from dual
2237 where exists ( select 'y'
2238 from ra_cust_trx_types_all ctt,
2239 ra_customer_trx_lines_all ctl,
2240 ra_customer_trx_all ct
2241 where ct.cust_trx_type_id = ctt.cust_trx_type_id
2242 and nvl(ct.org_id, -99) = nvl(ctt.org_id, -99)
2243 and ct.customer_trx_id = ctl.customer_trx_id
2244 and 'TAX' = ctl.line_type
2245 and (
2246 'Y' = arpt_sql_func_util.get_posted_flag
2247 ( ct.customer_trx_id,
2248 ctt.post_to_gl,
2249 ct.complete_flag
2250 ) -- posted_flag
2251 OR 'Y' = arpt_sql_func_util.get_activity_flag
2252 ( ct.customer_trx_id,
2253 ctt.accounting_affect_flag,
2254 ct.complete_flag,
2255 ctt.type,
2256 ct.initial_customer_trx_id,
2257 ct.previous_customer_trx_id
2258 ) -- activity_flag
2259 OR DECODE(ct.printing_last_printed,
2260 NULL,'N', 'Y') = 'Y'
2261 )
2262 and ct.ship_to_customer_id = p_customer_id
2263 and ct.ship_to_site_use_id = i.site_use_id
2264 );
2265
2266 arp_debug.debug(' find existing data meeting existing of trx with posted or with activity or printed for ship_to site');
2267 arp_debug.debug('transaction_morg_exists RESULT : TRUE -');
2268 RETURN (TRUE);
2269 exit;
2270 --Bug Fix :1552964
2271 EXCEPTION
2272 when NO_DATA_FOUND then
2273 null;
2274 END;
2275 end loop;
2276
2277 if invoice_count is NULL then
2278 --
2279 for j in bill_to_site
2280
2281 loop
2282 arp_debug.debug(' cursor BILL_TO_SITE site_use_id retrieved:'||j.site_use_id);
2283 select 'x'
2284 into invoice_count
2285 from dual
2286 where exists ( select 'y'
2287 from ra_cust_trx_types_all ctt,
2288 ra_customer_trx_lines_all ctl,
2289 ra_customer_trx_all ct
2290 where ct.cust_trx_type_id = ctt.cust_trx_type_id
2291 and nvl(ct.org_id,-99) = nvl(ctt.org_id, -99)
2292 and ct.customer_trx_id = ctl.customer_trx_id
2293 and 'TAX' = ctl.line_type
2294 and (
2295 'Y' = arpt_sql_func_util.get_posted_flag
2296 ( ct.customer_trx_id,
2297 ctt.post_to_gl,
2298 ct.complete_flag
2299 ) -- posted_flag
2300 OR 'Y' = arpt_sql_func_util.get_activity_flag
2301 ( ct.customer_trx_id,
2302 ctt.accounting_affect_flag,
2303 ct.complete_flag,
2304 ctt.type,
2305 ct.initial_customer_trx_id,
2306 ct.previous_customer_trx_id
2307 ) -- activity_flag
2308 OR DECODE(ct.printing_last_printed,
2309 NULL,'N', 'Y') = 'Y'
2310 )
2311 and ct.bill_to_customer_id = p_customer_id
2312 and ct.bill_to_site_use_id = j.site_use_id
2313 );
2314
2315 arp_debug.debug(' find existing data meeting existing of trx with posted or with activity or printed for bill_to site');
2316 arp_debug.debug('transaction_morg_exists RESULT : TRUE -');
2317 RETURN (TRUE);
2318 exit;
2319
2320 end loop;
2321 --
2322 end if;
2323 EXCEPTION
2324 WHEN NO_DATA_FOUND THEN
2325 arp_debug.debug('No Ship To or Bill To SITE with posted trx or trx with activity or printed invoice');
2326 arp_debug.debug('transaction_morg_exists RESULT : FALSE -');
2327 RETURN (FALSE);
2328 --
2329 END;
2330
2331 --
2332 if invoice_count is NULL then
2333 arp_debug.debug('No Ship To or Bill To SITE found');
2334 arp_debug.debug('transaction_morg_exists RESULT : FALSE -');
2335 RETURN (FALSE);
2336 end if;
2337 --
2338 END transaction_morg_exists;
2339
2340
2341
2342 --{BUG#4037614
2343 ----------------------------
2344 -- This procedure if the updation of a physical location is not allowed
2345 -- and the location has been updated.
2346 -- -> system option setting
2347 -- -> the data of location really updated
2348 -- -> transaction has been printed with this location
2349 ----------------------------
2350 PROCEDURE check_printed_trx
2351 (p_location_id IN NUMBER,
2352 p_location_rec IN hz_location_v2pub.location_rec_type,
2353 x_return_status IN OUT NOCOPY VARCHAR2,
2354 x_msg_data IN OUT NOCOPY VARCHAR2,
2355 x_msg_count IN OUT NOCOPY NUMBER)
2356 IS
2357 l_customer_id NUMBER;
2358 l_address_id NUMBER;
2359 l_org_id number;
2360 --
2361 invoice_count VARCHAR2(1);
2362 check_value VARCHAR2(1);
2363
2364 -- find all possible customer accounts which use this location
2365 CURSOR c_cust_addrs IS
2366 SELECT acct_site.cust_account_id,
2367 acct_site.cust_acct_site_id
2368 FROM hz_locations loc,
2369 hz_party_sites party_site,
2370 hz_cust_acct_sites_all acct_site
2371 WHERE loc.location_id = party_site.location_id
2372 AND party_site.party_site_id = acct_site.party_site_id
2373 AND loc.location_id = p_location_id;
2374 --
2375 l_exist_location hz_location_v2pub.location_rec_type;
2376 --
2377 /*Bug 4605384*/
2378 /*
2379 CURSOR c_check IS
2380 SELECT change_printed_invoice_flag
2381 FROM ar_system_parameters;
2382 */
2383 --
2384 CURSOR c_org_for_loc IS
2385 select distinct acct_site.org_id
2386 from hz_locations loc,
2387 hz_party_sites party_site,
2388 hz_cust_acct_sites_all acct_site
2389 where loc.location_id = party_site.location_id
2390 and party_site.party_site_id = acct_site.party_site_id
2391 and loc.location_id = p_location_id;
2392
2393 loc_modified VARCHAR2(1) := 'N';
2394 AR_NO_ROW_IN_SYSTEM_PARAMETERS EXCEPTION;
2395 --
2396 BEGIN
2397 --Check system option
2398 /*commented for bug 4605384*/
2399 /*
2400 OPEN c_check;
2401 FETCH c_check INTO check_value;
2402 IF c_check%NOTFOUND THEN
2403 RAISE AR_NO_ROW_IN_SYSTEM_PARAMETERS;
2404 END IF;
2405 CLOSE c_check;
2406 */
2407 /*start Bug 4605384*/
2408 open c_org_for_loc;
2409 LOOP
2410 fetch c_org_for_loc into l_org_id;
2411 exit when c_org_for_loc%NOTFOUND;
2412 BEGIN
2413 select change_printed_invoice_flag
2414 into check_value
2415 from ar_system_parameters_all where org_id=l_org_id;
2416
2417 EXCEPTION
2418 WHEN NO_DATA_FOUND THEN
2419 close c_org_for_loc;
2420 RAISE AR_NO_ROW_IN_SYSTEM_PARAMETERS;
2421 END ;
2422
2423 if check_value = 'N' then
2424 exit;
2425 end if;
2426 END LOOP;
2427 close c_org_for_loc;
2428 /*End bug 4605384*/
2429
2430 arp_debug.debug(' check_value:'||check_value);
2431 -- if check_value = 'Y' no processing required user can update addresses
2432
2433 IF check_value = 'N' THEN
2434
2435 --{BUG#4058539
2436 compare_location_existing
2437 (p_location_id => p_location_id,
2438 p_location_rec => p_location_rec,
2439 x_exist_loc_rec => l_exist_location,
2440 x_loc_updated => loc_modified );
2441 --}
2442 --
2443 arp_debug.debug(' loc_modified:'||loc_modified);
2444
2445 -- any transaction printed with this location
2446 IF loc_modified = 'Y' THEN
2447 --
2448 OPEN c_cust_addrs;
2449 LOOP
2450 FETCH c_cust_addrs INTO l_customer_id,
2451 l_address_id;
2452 EXIT WHEN c_cust_addrs%NOTFOUND;
2453
2454 -- changed to call transaction_morg_exists
2455 IF transaction_morg_exists(l_address_id,l_customer_id) THEN
2456 -- transaction existing printed
2457 x_return_status := fnd_api.g_ret_sts_error;
2458 FND_MESSAGE.SET_NAME( 'AR','4600');
2459 FND_MSG_PUB.ADD;
2460 FND_MSG_PUB.Count_And_Get(
2461 p_encoded => FND_API.G_FALSE,
2462 p_count => x_msg_count,
2463 p_data => x_msg_data);
2464 EXIT;
2465 END IF;
2466
2467 END LOOP;
2468 CLOSE c_cust_addrs ;
2469
2470 IF c_cust_addrs%ISOPEN THEN
2471 CLOSE c_cust_addrs;
2472 END IF;
2473 END IF;
2474 END IF;
2475 --
2476 EXCEPTION
2477 WHEN AR_NO_ROW_IN_SYSTEM_PARAMETERS THEN
2478 IF c_org_for_loc%ISOPEN THEN CLOSE c_org_for_loc; END IF;
2479 x_return_status := fnd_api.g_ret_sts_error;
2480 FND_MESSAGE.SET_NAME( 'AR','AR_NO_ROW_IN_SYSTEM_PARAMETERS');
2481 FND_MSG_PUB.ADD;
2482 FND_MSG_PUB.Count_And_Get(
2483 p_encoded => FND_API.G_FALSE,
2484 p_count => x_msg_count,
2485 p_data => x_msg_data);
2486 WHEN OTHERS THEN
2487 IF c_org_for_loc%ISOPEN THEN CLOSE c_org_for_loc; END IF;
2488 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2489 FND_MESSAGE.SET_NAME( 'FND','FND_GENERIC_MESSAGE');
2490 FND_MESSAGE.SET_TOKEN('MESSAGE',SQLERRM);
2491 FND_MSG_PUB.ADD;
2492 FND_MSG_PUB.Count_And_Get(
2493 p_encoded => FND_API.G_FALSE,
2494 p_count => x_msg_count,
2495 p_data => x_msg_data);
2496 END check_printed_trx;
2497
2498
2499
2500 -- This is a overloaded procedure for check_printed_trx
2501 -- bug#4058539
2502 -- this procedure check_printed_trx
2503 -- Return in the input parameters the existing location data if location exist
2504 -- Return in the x_printed_trx_loc_modified
2505 -- * 'N' : No violation to transaction about the printed invoice on location update
2506 -- * 'Y' : Violation to transaction about the printed invoice on location update
2507 -- The x_return_status is set to error only other error are detected
2508 --
2509 PROCEDURE check_printed_trx
2510 (p_location_id IN NUMBER,
2511 x_Country IN OUT NOCOPY VARCHAR2,
2512 x_Address1 IN OUT NOCOPY VARCHAR2,
2513 x_Address2 IN OUT NOCOPY VARCHAR2,
2514 x_Address3 IN OUT NOCOPY VARCHAR2,
2515 x_Address4 IN OUT NOCOPY VARCHAR2,
2516 x_City IN OUT NOCOPY VARCHAR2,
2517 x_Postal_Code IN OUT NOCOPY VARCHAR2,
2518 x_State IN OUT NOCOPY VARCHAR2,
2519 x_Province IN OUT NOCOPY VARCHAR2,
2520 x_County IN OUT NOCOPY VARCHAR2,
2521 x_description IN OUT NOCOPY VARCHAR2,
2522 x_short_description IN OUT NOCOPY VARCHAR2,
2523 x_floor IN OUT NOCOPY VARCHAR2,
2524 x_house_number IN OUT NOCOPY VARCHAR2,
2525 x_location_directions IN OUT NOCOPY VARCHAR2,
2526 x_postal_plus4_code IN OUT NOCOPY VARCHAR2,
2527 x_po_box_number IN OUT NOCOPY VARCHAR2,
2528 x_street IN OUT NOCOPY VARCHAR2,
2529 x_street_number IN OUT NOCOPY VARCHAR2,
2530 x_street_suffix IN OUT NOCOPY VARCHAR2,
2531 x_suite IN OUT NOCOPY VARCHAR2,
2532 x_Language IN OUT NOCOPY VARCHAR2,
2533 x_Address_Lines_Phonetic IN OUT NOCOPY VARCHAR2,
2534 x_Sales_Tax_Geocode IN OUT NOCOPY VARCHAR2,
2535 x_Sales_Tax_Inside_City_Limits IN OUT NOCOPY VARCHAR2,
2536 --
2537 x_printed_trx_loc_modified IN OUT NOCOPY VARCHAR2,
2538 x_return_status IN OUT NOCOPY VARCHAR2,
2539 x_msg_data IN OUT NOCOPY VARCHAR2,
2540 x_msg_count IN OUT NOCOPY NUMBER)
2541 IS
2542 l_customer_id NUMBER;
2543 l_address_id NUMBER;
2544 l_org_id NUMBER;
2545 --
2546 invoice_count VARCHAR2(1);
2547 check_value VARCHAR2(1);
2548
2549 -- find all possible customer accounts which use this location
2550 CURSOR c_cust_addrs IS
2551 SELECT acct_site.cust_account_id,
2552 acct_site.cust_acct_site_id
2553 FROM hz_locations loc,
2554 hz_party_sites party_site,
2555 hz_cust_acct_sites_all acct_site
2556 WHERE loc.location_id = party_site.location_id
2557 AND party_site.party_site_id = acct_site.party_site_id
2558 AND loc.location_id = p_location_id;
2559 --
2560 l_exist_location hz_location_v2pub.location_rec_type;
2561 --
2562 /*Bug 4605384*/
2563 /*
2564 CURSOR c_check IS
2565 SELECT change_printed_invoice_flag
2566 FROM ar_system_parameters;
2567 */
2568 CURSOR c_org_for_loc IS
2569 select distinct acct_site.org_id
2570 from hz_locations loc,
2571 hz_party_sites party_site,
2572 hz_cust_acct_sites_all acct_site
2573 where loc.location_id = party_site.location_id
2574 and party_site.party_site_id = acct_site.party_site_id
2575 and loc.location_id = p_location_id;
2576 --
2577 loc_modified VARCHAR2(1) := 'N';
2578 AR_NO_ROW_IN_SYSTEM_PARAMETERS EXCEPTION;
2579 --
2580 l_location_rec hz_location_v2pub.location_rec_type;
2581 tmp_var VARCHAR2(2000);
2582 tmp_var1 VARCHAR2(2000);
2583 BEGIN
2584 arp_standard.debug('check_printed_trx +');
2585 x_return_status := FND_API.G_RET_STS_SUCCESS;
2586 x_printed_trx_loc_modified := 'N';
2587 --Check system option
2588 /* Commented for bug 4605384*/
2589 /*
2590 OPEN c_check;
2591 FETCH c_check INTO check_value;
2592 IF c_check%NOTFOUND THEN
2593 RAISE AR_NO_ROW_IN_SYSTEM_PARAMETERS;
2594 END IF;
2595 CLOSE c_check;
2596 */
2597 /*start Bug 4605384*/
2598 open c_org_for_loc;
2599 LOOP
2600 fetch c_org_for_loc into l_org_id;
2601 exit when c_org_for_loc%NOTFOUND;
2602 BEGIN
2603 select change_printed_invoice_flag
2604 into check_value
2605 from ar_system_parameters_all where org_id=l_org_id;
2606
2607 EXCEPTION
2608 WHEN NO_DATA_FOUND THEN
2609 close c_org_for_loc;
2610 RAISE AR_NO_ROW_IN_SYSTEM_PARAMETERS;
2611 END ;
2612
2613 if check_value = 'N' then
2614 exit;
2615 end if;
2616 END LOOP;
2617 close c_org_for_loc;
2618 /*End bug 4605384*/
2619 arp_standard.debug(' check_value:'||check_value);
2620 -- if check_value = 'Y' no processing required user can update addresses
2621
2622 IF check_value = 'N' THEN
2623
2624 compare_location_existing
2625 (p_location_id => p_location_id,
2626 x_Country => x_country,
2627 x_Address1 => x_address1,
2628 x_Address2 => x_address2,
2629 x_Address3 => x_address3,
2630 x_Address4 => x_address4,
2631 x_City => x_city,
2632 x_Postal_Code => x_postal_code,
2633 x_State => x_state,
2634 x_Province => x_province,
2635 x_County => x_county,
2636 x_description => x_description,
2637 x_short_description => x_short_description,
2638 x_floor => x_floor,
2639 x_house_number => x_house_number,
2640 x_location_directions => x_location_directions,
2641 x_postal_plus4_code => x_postal_plus4_code,
2642 x_po_box_number => x_po_box_number,
2643 x_street => x_street,
2644 x_street_number => x_street_number,
2645 x_street_suffix => x_street_suffix,
2646 x_suite => x_suite,
2647 x_Language => x_language,
2648 x_Address_Lines_Phonetic => x_Address_Lines_Phonetic,
2649 x_Sales_Tax_Geocode => x_Sales_Tax_Geocode,
2650 x_Sales_Tax_Inside_City_Limits => x_Sales_Tax_Inside_City_Limits,
2651 x_loc_updated => loc_modified);
2652 --}
2653 --
2654 arp_standard.debug(' loc_modified:'||loc_modified);
2655
2656 -- any transaction printed with this location
2657 IF loc_modified = 'Y' THEN
2658 --
2659 OPEN c_cust_addrs;
2660 LOOP
2661 FETCH c_cust_addrs INTO l_customer_id,
2662 l_address_id;
2663 EXIT WHEN c_cust_addrs%NOTFOUND;
2664
2665 /* changed to call transaction_morg_exists */
2666 IF transaction_morg_exists(l_address_id,l_customer_id) THEN
2667 -- transaction existing printed
2668 x_printed_trx_loc_modified := 'Y';
2669 EXIT;
2670 END IF;
2671
2672 END LOOP;
2673 CLOSE c_cust_addrs ;
2674
2675 IF c_cust_addrs%ISOPEN THEN
2676 CLOSE c_cust_addrs;
2677 END IF;
2678 END IF;
2679 END IF;
2680 arp_standard.debug('check_printed_trx -');
2681 --
2682 EXCEPTION
2683 WHEN AR_NO_ROW_IN_SYSTEM_PARAMETERS THEN
2684 IF c_org_for_loc%ISOPEN THEN CLOSE c_org_for_loc; END IF;
2685 x_return_status := fnd_api.g_ret_sts_error;
2686 FND_MESSAGE.SET_NAME( 'AR','AR_NO_ROW_IN_SYSTEM_PARAMETERS');
2687 FND_MSG_PUB.ADD;
2688 FND_MSG_PUB.Count_And_Get(
2689 p_encoded => FND_API.G_FALSE,
2690 p_count => x_msg_count,
2691 p_data => x_msg_data);
2692 arp_standard.debug('EXCEPTION AR_NO_ROW_IN_SYSTEM_PARAMETERS in check_printed_trx');
2693 IF x_msg_count > 1 THEN
2694 FOR i IN 1..x_msg_count LOOP
2695 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
2696 tmp_var1 := tmp_var1 || ' '|| tmp_var;
2697 END LOOP;
2698 x_msg_data := tmp_var1;
2699 END IF;
2700 WHEN OTHERS THEN
2701 IF c_org_for_loc%ISOPEN THEN CLOSE c_org_for_loc; END IF;
2702 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2703 FND_MESSAGE.SET_NAME( 'FND','FND_GENERIC_MESSAGE');
2704 FND_MESSAGE.SET_TOKEN('MESSAGE',SQLERRM);
2705 FND_MSG_PUB.ADD;
2706 FND_MSG_PUB.Count_And_Get(
2707 p_encoded => FND_API.G_FALSE,
2708 p_count => x_msg_count,
2709 p_data => x_msg_data);
2710 arp_standard.debug('EXCEPTION OTHERS in check_printed_trx :'||SQLERRM);
2711 IF x_msg_count > 1 THEN
2712 FOR i IN 1..x_msg_count LOOP
2713 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
2714 tmp_var1 := tmp_var1 || ' '|| tmp_var;
2715 END LOOP;
2716 x_msg_data := tmp_var1;
2717 END IF;
2718 END check_printed_trx;
2719
2720
2721 PROCEDURE check_addr_modif_allowed
2722 (p_location_id IN NUMBER,
2723 x_loc_modif_allowed IN OUT NOCOPY VARCHAR2,
2724 x_return_status IN OUT NOCOPY VARCHAR2,
2725 x_msg_data IN OUT NOCOPY VARCHAR2,
2726 x_msg_count IN OUT NOCOPY NUMBER)
2727 IS
2728 l_customer_id NUMBER;
2729 l_address_id NUMBER;
2730 l_org_id NUMBER;
2731 --
2732 invoice_count VARCHAR2(1);
2733 check_value VARCHAR2(1);
2734
2735 -- find all possible customer accounts which use this location
2736 CURSOR c_cust_addrs IS
2737 SELECT acct_site.cust_account_id,
2738 acct_site.cust_acct_site_id
2739 FROM hz_locations loc,
2740 hz_party_sites party_site,
2741 hz_cust_acct_sites_all acct_site
2742 WHERE loc.location_id = party_site.location_id
2743 AND party_site.party_site_id = acct_site.party_site_id
2744 AND loc.location_id = p_location_id;
2745 --
2746 l_exist_location hz_location_v2pub.location_rec_type;
2747 --
2748 /*Bug 4605384 Changed as per consistency for changes allowed in address*/
2749 /*
2750 CURSOR c_check IS
2751 SELECT change_printed_invoice_flag
2752 FROM ar_system_parameters;
2753 */
2754 CURSOR c_org_for_loc IS
2755 select distinct acct_site.org_id
2756 from hz_locations loc,
2757 hz_party_sites party_site,
2758 hz_cust_acct_sites_all acct_site
2759 where loc.location_id = party_site.location_id
2760 and party_site.party_site_id = acct_site.party_site_id
2761 and loc.location_id = p_location_id;
2762
2763 --
2764 loc_modified VARCHAR2(1) := 'N';
2765 AR_NO_ROW_IN_SYSTEM_PARAMETERS EXCEPTION;
2766 --
2767 l_location_rec hz_location_v2pub.location_rec_type;
2768 tmp_var VARCHAR2(2000);
2769 tmp_var1 VARCHAR2(2000);
2770 BEGIN
2771 arp_standard.debug('check_addr_modif_allowed +');
2772 x_return_status := FND_API.G_RET_STS_SUCCESS;
2773 x_loc_modif_allowed := 'Y';
2774 --Check system option
2775 /*Bug 4605384 as per consistency check for modification of address allowed*/
2776 /*
2777 OPEN c_check;
2778 FETCH c_check INTO check_value;
2779 IF c_check%NOTFOUND THEN
2780 RAISE AR_NO_ROW_IN_SYSTEM_PARAMETERS;
2781 END IF;
2782 CLOSE c_check;
2783 */
2784 /*start Bug 4605384*/
2785 open c_org_for_loc;
2786 LOOP
2787 fetch c_org_for_loc into l_org_id;
2788 exit when c_org_for_loc%NOTFOUND;
2789 BEGIN
2790 select change_printed_invoice_flag
2791 into check_value
2792 from ar_system_parameters_all where org_id=l_org_id;
2793
2794 EXCEPTION
2795 WHEN NO_DATA_FOUND THEN
2796 close c_org_for_loc;
2797 RAISE AR_NO_ROW_IN_SYSTEM_PARAMETERS;
2798 END ;
2799
2800 if check_value = 'N' then
2801 exit;
2802 end if;
2803 END LOOP;
2804 close c_org_for_loc;
2805 /*End bug 4605384*/
2806
2807 arp_standard.debug(' check_value:'||check_value);
2808 -- if check_value = 'Y' no processing required user can update addresses
2809
2810 IF check_value = 'N' THEN
2811
2812 --
2813 OPEN c_cust_addrs;
2814 LOOP
2815 FETCH c_cust_addrs INTO l_customer_id,
2816 l_address_id;
2817 EXIT WHEN c_cust_addrs%NOTFOUND;
2818
2819 /* changed to call transaction_morg_exists */
2820 IF transaction_morg_exists(l_address_id,l_customer_id) THEN
2821 -- transaction existing printed
2822 x_loc_modif_allowed := 'N';
2823 EXIT;
2824 END IF;
2825
2826 END LOOP;
2827 CLOSE c_cust_addrs ;
2828
2829 IF c_cust_addrs%ISOPEN THEN
2830 CLOSE c_cust_addrs;
2831 END IF;
2832
2833 END IF;
2834 arp_standard.debug('check_addr_modif_allowed -');
2835 --
2836 EXCEPTION
2837 WHEN AR_NO_ROW_IN_SYSTEM_PARAMETERS THEN
2838 IF c_org_for_loc%ISOPEN THEN CLOSE c_org_for_loc; END IF;
2839 x_return_status := fnd_api.g_ret_sts_error;
2840 FND_MESSAGE.SET_NAME( 'AR','AR_NO_ROW_IN_SYSTEM_PARAMETERS');
2841 FND_MSG_PUB.ADD;
2842 FND_MSG_PUB.Count_And_Get(
2843 p_encoded => FND_API.G_FALSE,
2844 p_count => x_msg_count,
2845 p_data => x_msg_data);
2846 arp_standard.debug('EXCEPTION AR_NO_ROW_IN_SYSTEM_PARAMETERS in check_addr_modif_allowed');
2847 IF x_msg_count > 1 THEN
2848 FOR i IN 1..x_msg_count LOOP
2849 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
2850 tmp_var1 := tmp_var1 || ' '|| tmp_var;
2851 END LOOP;
2852 x_msg_data := tmp_var1;
2853 END IF;
2854 WHEN OTHERS THEN
2855 IF c_org_for_loc%ISOPEN THEN CLOSE c_org_for_loc; END IF;
2856 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2857 FND_MESSAGE.SET_NAME( 'FND','FND_GENERIC_MESSAGE');
2858 FND_MESSAGE.SET_TOKEN('MESSAGE',SQLERRM);
2859 FND_MSG_PUB.ADD;
2860 FND_MSG_PUB.Count_And_Get(
2861 p_encoded => FND_API.G_FALSE,
2862 p_count => x_msg_count,
2863 p_data => x_msg_data);
2864 arp_standard.debug('EXCEPTION OTHERS in check_addr_modif_allowed :'||SQLERRM);
2865 IF x_msg_count > 1 THEN
2866 FOR i IN 1..x_msg_count LOOP
2867 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
2868 tmp_var1 := tmp_var1 || ' '|| tmp_var;
2869 END LOOP;
2870 x_msg_data := tmp_var1;
2871 END IF;
2872 END check_addr_modif_allowed;
2873
2874 --}
2875
2876
2877 --{BUG#4058639
2878 -- Take the location_id to return
2879 -- the existing location data in x_exist_loc_rec the column
2880 -- * in the case column to compare between existing and entered are alike
2881 -- the column set to fnd_api.g_miss_char
2882 -- * in the case column to compare between existing and entered are different
2883 -- the column contents the existing value
2884 -- if the location does not exist x_exist_loc_rec is empty record
2885 -- x_loc_updated
2886 -- return 'Y' if the data in p_location_rec is different to the existing location
2887 -- return 'N' if the data in p_location_rec is equal to the existing location
2888 -- return 'X' if the no existing location found
2889 PROCEDURE compare_location_existing
2890 (p_location_id IN NUMBER,
2891 p_location_rec IN hz_location_v2pub.location_rec_type,
2892 x_exist_loc_rec IN OUT NOCOPY hz_location_v2pub.location_rec_type,
2893 x_loc_updated IN OUT NOCOPY VARCHAR2)
2894 IS
2895 CURSOR c_exist_loc IS
2896 SELECT loc.country ,
2897 loc.address1 ,
2898 loc.address2 ,
2899 loc.address3 ,
2900 loc.address4 ,
2901 loc.city ,
2902 loc.postal_code ,
2903 loc.state ,
2904 loc.province ,
2905 loc.county ,
2906 loc.language ,
2907 loc.address_lines_phonetic,
2908 loc.description ,
2909 loc.short_description ,
2910 loc.floor ,
2911 loc.house_number,
2912 loc.location_directions,
2913 loc.postal_plus4_code,
2914 loc.po_box_number,
2915 loc.street ,
2916 loc.street_number,
2917 loc.street_suffix,
2918 loc.suite ,
2919 loc.Sales_Tax_Inside_City_Limits,
2920 loc.Sales_Tax_Geocode
2921 FROM hz_locations loc
2922 WHERE location_id = p_location_id;
2923 l_location_rec hz_location_v2pub.location_rec_type;
2924 loc_modified VARCHAR2(1) := 'N';
2925 BEGIN
2926 arp_standard.debug('compare_location_existing +');
2927 arp_standard.debug(' p_location_id :'||p_location_id);
2928 IF p_location_id IS NULL THEN
2929 x_loc_updated := 'X';
2930 ELSE
2931 OPEN c_exist_loc;
2932 FETCH c_exist_loc INTO l_location_rec.country ,
2933 l_location_rec.address1 ,
2934 l_location_rec.address2 ,
2935 l_location_rec.address3 ,
2936 l_location_rec.address4 ,
2937 l_location_rec.city ,
2938 l_location_rec.postal_code ,
2939 l_location_rec.state ,
2940 l_location_rec.province ,
2941 l_location_rec.county ,
2942 l_location_rec.language ,
2943 l_location_rec.address_lines_phonetic,
2944 l_location_rec.description ,
2945 l_location_rec.short_description ,
2946 l_location_rec.floor ,
2947 l_location_rec.house_number,
2948 l_location_rec.location_directions,
2949 l_location_rec.postal_plus4_code,
2950 l_location_rec.po_box_number,
2951 l_location_rec.street ,
2952 l_location_rec.street_number,
2953 l_location_rec.street_suffix,
2954 l_location_rec.suite ,
2955 l_location_rec.Sales_Tax_Inside_City_Limits,
2956 l_location_rec.Sales_Tax_Geocode;
2957 IF c_exist_loc%FOUND THEN
2958 arp_standard.debug(' p_location_rec.country :'||p_location_rec.country);
2959 arp_standard.debug(' l_location_rec.country :'||l_location_rec.country);
2960 IF NVL(p_location_rec.country,fnd_api.g_miss_char) <> NVL(l_location_rec.country,fnd_api.g_miss_char) THEN
2961 loc_modified := 'Y';
2962 ELSE
2963 l_location_rec.country := ar_miss_char;
2964 END IF;
2965 arp_standard.debug(' p_location_rec.address1 :'||p_location_rec.address1);
2966 arp_standard.debug(' l_location_rec.address1 :'||l_location_rec.address1);
2967 IF NVL(p_location_rec.address1,fnd_api.g_miss_char) <> NVL(l_location_rec.address1,fnd_api.g_miss_char) THEN
2968 loc_modified := 'Y';
2969 ELSE
2970 l_location_rec.address1 := ar_miss_char;
2971 END IF;
2972 arp_standard.debug(' p_location_rec.address2 :'||p_location_rec.address2);
2973 arp_standard.debug(' l_location_rec.address2 :'||l_location_rec.address2);
2974 IF NVL(p_location_rec.address2,fnd_api.g_miss_char) <> NVL(l_location_rec.address2,fnd_api.g_miss_char) THEN
2975 loc_modified := 'Y';
2976 ELSE
2977 l_location_rec.address2 := ar_miss_char;
2978 END IF;
2979 arp_standard.debug(' p_location_rec.address3 :'||p_location_rec.address3);
2980 arp_standard.debug(' l_location_rec.address3 :'||l_location_rec.address3);
2981 IF NVL(p_location_rec.address3,fnd_api.g_miss_char) <> NVL(l_location_rec.address3,fnd_api.g_miss_char) THEN
2982 loc_modified := 'Y';
2983 ELSE
2984 l_location_rec.address3 := ar_miss_char;
2985 END IF;
2986 arp_standard.debug(' p_location_rec.address4 :'||p_location_rec.address4);
2987 arp_standard.debug(' l_location_rec.address4 :'||l_location_rec.address4);
2988 IF NVL(p_location_rec.address4,fnd_api.g_miss_char) <> NVL(l_location_rec.address4,fnd_api.g_miss_char) THEN
2989 loc_modified := 'Y';
2990 ELSE
2991 l_location_rec.address4 := ar_miss_char;
2992 END IF;
2993 arp_standard.debug(' p_location_rec.city :'||p_location_rec.city);
2994 arp_standard.debug(' l_location_rec.city :'||l_location_rec.city);
2995 IF NVL(p_location_rec.city,fnd_api.g_miss_char) <> NVL(l_location_rec.city,fnd_api.g_miss_char) THEN
2996 loc_modified := 'Y';
2997 ELSE
2998 l_location_rec.city := ar_miss_char;
2999 END IF;
3000 arp_standard.debug(' p_location_rec.postal_code :'||p_location_rec.postal_code);
3001 arp_standard.debug(' l_location_rec.postal_code :'||l_location_rec.postal_code);
3002 IF NVL(p_location_rec.postal_code,fnd_api.g_miss_char) <> NVL(l_location_rec.postal_code,fnd_api.g_miss_char) THEN
3003 loc_modified := 'Y';
3004 ELSE
3005 l_location_rec.postal_code := ar_miss_char;
3006 END IF;
3007 arp_standard.debug(' p_location_rec.state :'||p_location_rec.state);
3008 arp_standard.debug(' l_location_rec.state :'||l_location_rec.state);
3009 IF NVL(p_location_rec.state,fnd_api.g_miss_char) <> NVL(l_location_rec.state,fnd_api.g_miss_char) THEN
3010 loc_modified := 'Y';
3011 ELSE
3012 l_location_rec.state := ar_miss_char;
3013 END IF;
3014 arp_standard.debug(' p_location_rec.province :'||p_location_rec.province);
3015 arp_standard.debug(' l_location_rec.province :'||l_location_rec.province);
3016 IF NVL(p_location_rec.province,fnd_api.g_miss_char) <> NVL(l_location_rec.province,fnd_api.g_miss_char) THEN
3017 loc_modified := 'Y';
3018 ELSE
3019 l_location_rec.province := ar_miss_char;
3020 END IF;
3021 arp_standard.debug(' p_location_rec.county :'||p_location_rec.county);
3022 arp_standard.debug(' l_location_rec.county :'||l_location_rec.county);
3023 IF NVL(p_location_rec.county,fnd_api.g_miss_char) <> NVL(l_location_rec.county,fnd_api.g_miss_char) THEN
3024 loc_modified := 'Y';
3025 ELSE
3026 l_location_rec.county := ar_miss_char;
3027 END IF;
3028 arp_standard.debug(' p_location_rec.language :'||p_location_rec.language);
3029 arp_standard.debug(' l_location_rec.language :'||l_location_rec.language);
3030 IF NVL(p_location_rec.language,fnd_api.g_miss_char) <> NVL(l_location_rec.language,fnd_api.g_miss_char) THEN
3031 loc_modified := 'Y';
3032 ELSE
3033 l_location_rec.language := ar_miss_char;
3034 END IF;
3035 arp_standard.debug(' p_location_rec.address_lines_phonetic :'||p_location_rec.address_lines_phonetic);
3036 arp_standard.debug(' l_location_rec.address_lines_phonetic :'||l_location_rec.address_lines_phonetic);
3037 IF NVL(p_location_rec.address_lines_phonetic,fnd_api.g_miss_char) <> NVL(l_location_rec.address_lines_phonetic,fnd_api.g_miss_char) THEN
3038 loc_modified := 'Y';
3039 ELSE
3040 l_location_rec.address_lines_phonetic:= ar_miss_char;
3041 END IF;
3042 arp_standard.debug(' p_location_rec.description :'||p_location_rec.description);
3043 arp_standard.debug(' l_location_rec.description :'||l_location_rec.description);
3044 IF NVL(p_location_rec.description,fnd_api.g_miss_char) <> NVL(l_location_rec.description,fnd_api.g_miss_char) THEN
3045 loc_modified := 'Y';
3046 ELSE
3047 l_location_rec.description:= ar_miss_char;
3048 END IF;
3049 arp_standard.debug(' p_location_rec.short_description :'||p_location_rec.short_description);
3050 arp_standard.debug(' l_location_rec.short_description :'||l_location_rec.short_description);
3051 IF NVL(p_location_rec.short_description,fnd_api.g_miss_char) <> NVL(l_location_rec.short_description,fnd_api.g_miss_char) THEN
3052 loc_modified := 'Y';
3053 ELSE
3054 l_location_rec.short_description:= ar_miss_char;
3055 END IF;
3056 arp_standard.debug(' p_location_rec.floor :'||p_location_rec.floor);
3057 arp_standard.debug(' l_location_rec.floor :'||l_location_rec.floor);
3058 IF NVL(p_location_rec.floor,fnd_api.g_miss_char) <> NVL(l_location_rec.floor,fnd_api.g_miss_char) THEN
3059 loc_modified := 'Y';
3060 ELSE
3061 l_location_rec.floor:= ar_miss_char;
3062 END IF;
3063 arp_standard.debug(' p_location_rec.house_number :'||p_location_rec.house_number);
3064 arp_standard.debug(' l_location_rec.house_number :'||l_location_rec.house_number);
3065 IF NVL(p_location_rec.house_number,fnd_api.g_miss_char)<> NVL(l_location_rec.house_number,fnd_api.g_miss_char) THEN
3066 loc_modified := 'Y';
3067 ELSE
3068 l_location_rec.house_number:= ar_miss_char;
3069 END IF;
3070 arp_standard.debug(' p_location_rec.location_directions :'||p_location_rec.location_directions);
3071 arp_standard.debug(' l_location_rec.location_directions :'||l_location_rec.location_directions);
3072 IF NVL(p_location_rec.location_directions,fnd_api.g_miss_char) <> NVL(l_location_rec.location_directions,fnd_api.g_miss_char) THEN
3073 loc_modified := 'Y';
3074 ELSE
3075 l_location_rec.location_directions:= ar_miss_char;
3076 END IF;
3077 arp_standard.debug(' p_location_rec.postal_plus4_code :'||p_location_rec.postal_plus4_code);
3078 arp_standard.debug(' l_location_rec.postal_plus4_code :'||l_location_rec.postal_plus4_code);
3079 IF NVL(p_location_rec.postal_plus4_code,fnd_api.g_miss_char) <> NVL(l_location_rec.postal_plus4_code,fnd_api.g_miss_char) THEN
3080 loc_modified := 'Y';
3081 ELSE
3082 l_location_rec.postal_plus4_code:= ar_miss_char;
3083 END IF;
3084 arp_standard.debug(' p_location_rec.po_box_number :'||p_location_rec.po_box_number);
3085 arp_standard.debug(' l_location_rec.po_box_number :'||l_location_rec.po_box_number);
3086
3087 IF NVL(p_location_rec.po_box_number,fnd_api.g_miss_char) <> NVL(l_location_rec.po_box_number,fnd_api.g_miss_char) THEN
3088 loc_modified := 'Y';
3089 ELSE
3090 l_location_rec.po_box_number:= ar_miss_char;
3091 END IF;
3092
3093 arp_standard.debug(' p_location_rec.street :'||p_location_rec.street);
3094 arp_standard.debug(' l_location_rec.street :'||l_location_rec.street);
3095 IF NVL(p_location_rec.street,fnd_api.g_miss_char) <> NVL(l_location_rec.street,fnd_api.g_miss_char) THEN
3096 loc_modified := 'Y';
3097 ELSE
3098 l_location_rec.street:= ar_miss_char;
3099 END IF;
3100 arp_standard.debug(' p_location_rec.street_number :'||p_location_rec.street_number);
3101 arp_standard.debug(' l_location_rec.street_number :'||l_location_rec.street_number);
3102 IF NVL(p_location_rec.street_number,fnd_api.g_miss_char) <> NVL(l_location_rec.street_number,fnd_api.g_miss_char) THEN
3103 loc_modified := 'Y';
3104 ELSE
3105 l_location_rec.street_number:= ar_miss_char;
3106 END IF;
3107 arp_standard.debug(' p_location_rec.street_suffix :'||p_location_rec.street_suffix);
3108 arp_standard.debug(' l_location_rec.street_suffix :'||l_location_rec.street_suffix);
3109 IF NVL(p_location_rec.street_suffix,fnd_api.g_miss_char) <> NVL(l_location_rec.street_suffix,fnd_api.g_miss_char) THEN
3110 loc_modified := 'Y';
3111 ELSE
3112 l_location_rec.street_suffix := ar_miss_char;
3113 END IF;
3114 arp_standard.debug(' p_location_rec.suite :'||p_location_rec.suite);
3115 arp_standard.debug(' l_location_rec.suite :'||l_location_rec.suite);
3116 IF NVL(p_location_rec.suite,fnd_api.g_miss_char) <> NVL(l_location_rec.suite,fnd_api.g_miss_char) THEN
3117 loc_modified := 'Y';
3118 ELSE
3119 l_location_rec.suite:= ar_miss_char;
3120 END IF;
3121 ELSE
3122 loc_modified := 'X';
3123 END IF;
3124 CLOSE c_exist_loc;
3125 x_loc_updated := loc_modified;
3126 x_exist_loc_rec := l_location_rec;
3127 arp_standard.debug(' x_loc_updated :'||x_loc_updated);
3128 END IF;
3129 arp_standard.debug('compare_location_existing -');
3130 EXCEPTION
3131 WHEN OTHERS THEN
3132 IF c_exist_loc%ISOPEN THEN CLOSE c_exist_loc; END IF;
3133 arp_standard.debug('EXCEPTION OTHERS compare_location_existing :'||SQLERRM);
3134 RAISE;
3135 END compare_location_existing;
3136
3137 --
3138 -- This procedure compare_location_existing overloaded
3139 -- return x_loc_updated
3140 -- 'Y' if the location data is different to the existing one
3141 -- 'N' if the location data is the same as to the existing one
3142 -- 'X' if the p_location_id is no hitting any existing location
3143 -- In the case of
3144 -- x_loc_updated = 'Y', all the x_<attribute_name> will return the existing data
3145 -- otherwise they stay the same as inputs
3146 --
3147 PROCEDURE compare_location_existing
3148 (p_location_id IN NUMBER,
3149 x_Country IN OUT NOCOPY VARCHAR2,
3150 x_Address1 IN OUT NOCOPY VARCHAR2,
3151 x_Address2 IN OUT NOCOPY VARCHAR2,
3152 x_Address3 IN OUT NOCOPY VARCHAR2,
3153 x_Address4 IN OUT NOCOPY VARCHAR2,
3154 x_City IN OUT NOCOPY VARCHAR2,
3155 x_Postal_Code IN OUT NOCOPY VARCHAR2,
3156 x_State IN OUT NOCOPY VARCHAR2,
3157 x_Province IN OUT NOCOPY VARCHAR2,
3158 x_County IN OUT NOCOPY VARCHAR2,
3159 x_description IN OUT NOCOPY VARCHAR2,
3160 x_short_description IN OUT NOCOPY VARCHAR2,
3161 x_floor IN OUT NOCOPY VARCHAR2,
3162 x_house_number IN OUT NOCOPY VARCHAR2,
3163 x_location_directions IN OUT NOCOPY VARCHAR2,
3164 x_postal_plus4_code IN OUT NOCOPY VARCHAR2,
3165 x_po_box_number IN OUT NOCOPY VARCHAR2,
3166 x_street IN OUT NOCOPY VARCHAR2,
3167 x_street_number IN OUT NOCOPY VARCHAR2,
3168 x_street_suffix IN OUT NOCOPY VARCHAR2,
3169 x_suite IN OUT NOCOPY VARCHAR2,
3170 x_Language IN OUT NOCOPY VARCHAR2,
3171 x_Address_Lines_Phonetic IN OUT NOCOPY VARCHAR2,
3172 x_Sales_Tax_Geocode IN OUT NOCOPY VARCHAR2,
3173 x_Sales_Tax_Inside_City_Limits IN OUT NOCOPY VARCHAR2,
3174 --
3175 x_loc_updated IN OUT NOCOPY VARCHAR2)
3176 IS
3177 l_location_rec hz_location_v2pub.location_rec_type;
3178 l_exist_loc_rec hz_location_v2pub.location_rec_type;
3179 BEGIN
3180 arp_standard.debug('compare_location_existing overload +');
3181
3182 arp_standard.debug('IN VALUES + ');
3183 arp_standard.debug(' x_Country :'|| x_country);
3184 arp_standard.debug(' x_Address1 :'|| x_address1);
3185 arp_standard.debug(' x_Address2 :'|| x_address2);
3186 arp_standard.debug(' x_Address3 :'|| x_address3);
3187 arp_standard.debug(' x_Address4 :'|| x_address4);
3188 arp_standard.debug(' x_City :'|| x_city);
3189 arp_standard.debug(' x_Postal_Code :'|| x_postal_code);
3190 arp_standard.debug(' x_State :'|| x_state);
3191 arp_standard.debug(' x_Province :'|| x_province);
3192 arp_standard.debug(' x_County :'|| x_county);
3193 arp_standard.debug(' x_description :'|| x_description);
3194 arp_standard.debug(' x_short_description :'|| x_short_description);
3195 arp_standard.debug(' x_floor :'|| x_floor);
3196 arp_standard.debug(' x_house_number :'|| x_house_number);
3197 arp_standard.debug(' x_location_directions :'|| x_location_directions);
3198 arp_standard.debug(' x_postal_plus4_code :'|| x_postal_plus4_code);
3199 arp_standard.debug(' x_po_box_number :'|| x_po_box_number);
3200 arp_standard.debug(' x_street :'|| x_street);
3201 arp_standard.debug(' x_street_number :'|| x_street_number);
3202 arp_standard.debug(' x_street_suffix :'|| x_street_suffix);
3203 arp_standard.debug(' x_suite :'|| x_suite);
3204 arp_standard.debug(' x_Language :'|| x_language);
3205 arp_standard.debug(' x_Address_Lines_Phonetic :'|| x_Address_Lines_Phonetic);
3206 arp_standard.debug(' x_Sales_Tax_Geocode :'|| x_sales_tax_geocode);
3207 arp_standard.debug(' x_Sales_Tax_Inside_City_Limits :'|| x_Sales_Tax_Inside_City_Limits);
3208 arp_standard.debug('IN VALUES - ');
3209
3210
3211 l_location_rec.Country := x_country;
3212 l_location_rec.Address1 := x_address1;
3213 l_location_rec.Address2 := x_address2;
3214 l_location_rec.Address3 := x_address3;
3215 l_location_rec.Address4 := x_address4;
3216 l_location_rec.City := x_city;
3217 l_location_rec.Postal_Code := x_postal_code;
3218 l_location_rec.State := x_state;
3219 l_location_rec.Province := x_province;
3220 l_location_rec.County := x_county;
3221 l_location_rec.description := x_description;
3222 l_location_rec.short_description := x_short_description;
3223 l_location_rec.floor := x_floor;
3224 l_location_rec.house_number := x_house_number;
3225 l_location_rec.location_directions := x_location_directions;
3226 l_location_rec.postal_plus4_code := x_postal_plus4_code;
3227 l_location_rec.po_box_number := x_po_box_number;
3228 l_location_rec.street := x_street;
3229 l_location_rec.street_number := x_street_number;
3230 l_location_rec.street_suffix := x_street_suffix;
3231 l_location_rec.suite := x_suite;
3232 l_location_rec.Language := x_language;
3233 l_location_rec.Address_Lines_Phonetic := x_Address_Lines_Phonetic;
3234 l_location_rec.Sales_Tax_Geocode := x_Sales_Tax_Geocode;
3235 l_location_rec.Sales_Tax_Inside_City_Limits := x_Sales_Tax_Inside_City_Limits;
3236
3237 compare_location_existing
3238 (p_location_id => p_location_id,
3239 p_location_rec => l_location_rec,
3240 x_exist_loc_rec => l_exist_loc_rec,
3241 x_loc_updated => x_loc_updated);
3242
3243 IF x_loc_updated = 'Y' THEN
3244 x_country := l_exist_loc_rec.Country;
3245 x_address1 := l_exist_loc_rec.Address1;
3246 x_address2 := l_exist_loc_rec.Address2;
3247 x_address3 := l_exist_loc_rec.Address3;
3248 x_address4 := l_exist_loc_rec.Address4;
3249 x_city := l_exist_loc_rec.City;
3250 x_postal_code := l_exist_loc_rec.Postal_Code;
3251 x_state := l_exist_loc_rec.State;
3252 x_province := l_exist_loc_rec.Province;
3253 x_county := l_exist_loc_rec.County;
3254 x_description := l_exist_loc_rec.description;
3255 x_short_description := l_exist_loc_rec.short_description;
3256 x_floor := l_exist_loc_rec.floor;
3257 x_house_number := l_exist_loc_rec.house_number;
3258 x_location_directions := l_exist_loc_rec.location_directions;
3259 x_postal_plus4_code := l_exist_loc_rec.postal_plus4_code;
3260 x_po_box_number := l_exist_loc_rec.po_box_number;
3261 x_street := l_exist_loc_rec.street;
3262 x_street_number := l_exist_loc_rec.street_number;
3263 x_street_suffix := l_exist_loc_rec.street_suffix;
3264 x_suite := l_exist_loc_rec.suite;
3265 x_language := l_exist_loc_rec.Language;
3266 x_Address_Lines_Phonetic := l_exist_loc_rec.Address_Lines_Phonetic;
3267 x_Sales_Tax_Geocode := l_exist_loc_rec.Sales_Tax_Geocode;
3268 x_Sales_Tax_Inside_City_Limits := l_exist_loc_rec.Sales_Tax_Inside_City_Limits;
3269 END IF;
3270 arp_standard.debug('OUT VALUES + ');
3271 arp_standard.debug(' x_Country :'|| x_country);
3272 arp_standard.debug(' x_Address1 :'|| x_address1);
3273 arp_standard.debug(' x_Address2 :'|| x_address2);
3274 arp_standard.debug(' x_Address3 :'|| x_address3);
3275 arp_standard.debug(' x_Address4 :'|| x_address4);
3276 arp_standard.debug(' x_City :'|| x_city);
3277 arp_standard.debug(' x_Postal_Code :'|| x_postal_code);
3278 arp_standard.debug(' x_State :'|| x_state);
3279 arp_standard.debug(' x_Province :'|| x_province);
3280 arp_standard.debug(' x_County :'|| x_county);
3281 arp_standard.debug(' x_description :'|| x_description);
3282 arp_standard.debug(' x_short_description :'|| x_short_description);
3283 arp_standard.debug(' x_floor :'|| x_floor);
3284 arp_standard.debug(' x_house_number :'|| x_house_number);
3285 arp_standard.debug(' x_location_directions :'|| x_location_directions);
3286 arp_standard.debug(' x_postal_plus4_code :'|| x_postal_plus4_code);
3287 arp_standard.debug(' x_po_box_number :'|| x_po_box_number);
3288 arp_standard.debug(' x_street :'|| x_street);
3289 arp_standard.debug(' x_street_number :'|| x_street_number);
3290 arp_standard.debug(' x_street_suffix :'|| x_street_suffix);
3291 arp_standard.debug(' x_suite :'|| x_suite);
3292 arp_standard.debug(' x_Language :'|| x_language);
3293 arp_standard.debug(' x_Address_Lines_Phonetic :'|| x_Address_Lines_Phonetic);
3294 arp_standard.debug(' x_Sales_Tax_Geocode :'|| x_sales_tax_geocode);
3295 arp_standard.debug(' x_Sales_Tax_Inside_City_Limits :'|| x_Sales_Tax_Inside_City_Limits);
3296 arp_standard.debug('OUT VALUES - ');
3297
3298 arp_standard.debug('compare_location_existing overload -');
3299 END compare_location_existing;
3300
3301
3302 PROCEDURE get_location_data
3303 (p_location_id IN NUMBER,
3304 x_Country IN OUT NOCOPY VARCHAR2,
3305 x_Address1 IN OUT NOCOPY VARCHAR2,
3306 x_Address2 IN OUT NOCOPY VARCHAR2,
3307 x_Address3 IN OUT NOCOPY VARCHAR2,
3308 x_Address4 IN OUT NOCOPY VARCHAR2,
3309 x_City IN OUT NOCOPY VARCHAR2,
3310 x_Postal_Code IN OUT NOCOPY VARCHAR2,
3311 x_State IN OUT NOCOPY VARCHAR2,
3312 x_Province IN OUT NOCOPY VARCHAR2,
3313 x_County IN OUT NOCOPY VARCHAR2,
3314 x_description IN OUT NOCOPY VARCHAR2,
3315 x_short_description IN OUT NOCOPY VARCHAR2,
3316 x_floor IN OUT NOCOPY VARCHAR2,
3317 x_house_number IN OUT NOCOPY VARCHAR2,
3318 x_location_directions IN OUT NOCOPY VARCHAR2,
3319 x_postal_plus4_code IN OUT NOCOPY VARCHAR2,
3320 x_po_box_number IN OUT NOCOPY VARCHAR2,
3321 x_street IN OUT NOCOPY VARCHAR2,
3322 x_street_number IN OUT NOCOPY VARCHAR2,
3323 x_street_suffix IN OUT NOCOPY VARCHAR2,
3324 x_suite IN OUT NOCOPY VARCHAR2,
3325 x_Address_Key IN OUT NOCOPY VARCHAR2,
3326 x_Language IN OUT NOCOPY VARCHAR2,
3327 x_Address_Lines_Phonetic IN OUT NOCOPY VARCHAR2,
3328 x_Sales_Tax_Geocode IN OUT NOCOPY VARCHAR2,
3329 x_Sales_Tax_Inside_City_Limits IN OUT NOCOPY VARCHAR2,
3330 --
3331 x_return_status IN OUT NOCOPY VARCHAR2,
3332 x_msg_data IN OUT NOCOPY VARCHAR2,
3333 x_msg_count IN OUT NOCOPY NUMBER)
3334 IS
3335 l_exist_loc_rec hz_location_v2pub.LOCATION_REC_TYPE;
3336 tmp_var VARCHAR2(2000);
3337 tmp_var1 VARCHAR2(2000);
3338 BEGIN
3339 hz_location_v2pub.get_location_rec(
3340 p_location_id => p_location_id,
3341 x_location_rec => l_exist_loc_rec,
3342 x_return_status => x_return_status,
3343 x_msg_count => x_msg_count,
3344 x_msg_data => x_msg_data);
3345
3346 IF x_msg_count > 1 THEN
3347 FOR i IN 1..x_msg_count LOOP
3348 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
3349 tmp_var1 := tmp_var1 || ' '|| tmp_var;
3350 END LOOP;
3351 x_msg_data := tmp_var1;
3352 END IF;
3353
3354 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3355 return;
3356 END IF;
3357
3358 x_country := l_exist_loc_rec.Country;
3359 x_address1 := l_exist_loc_rec.Address1;
3360 x_address2 := l_exist_loc_rec.Address2;
3361 x_address3 := l_exist_loc_rec.Address3;
3362 x_address4 := l_exist_loc_rec.Address4;
3363 x_city := l_exist_loc_rec.City;
3364 x_postal_code := l_exist_loc_rec.Postal_Code;
3365 x_state := l_exist_loc_rec.State;
3366 x_province := l_exist_loc_rec.Province;
3367 x_county := l_exist_loc_rec.County;
3368 x_description := l_exist_loc_rec.description;
3369 x_short_description := l_exist_loc_rec.short_description;
3370 x_floor := l_exist_loc_rec.floor;
3371 x_house_number := l_exist_loc_rec.house_number;
3372 x_location_directions := l_exist_loc_rec.location_directions;
3373 x_postal_plus4_code := l_exist_loc_rec.postal_plus4_code;
3374 x_po_box_number := l_exist_loc_rec.po_box_number;
3375 x_street := l_exist_loc_rec.street;
3376 x_street_number := l_exist_loc_rec.street_number;
3377 x_street_suffix := l_exist_loc_rec.street_suffix;
3378 x_suite := l_exist_loc_rec.suite;
3379 x_address_key := l_exist_loc_rec.Address_Key;
3380 x_language := l_exist_loc_rec.Language;
3381 x_Address_Lines_Phonetic := l_exist_loc_rec.Address_Lines_Phonetic;
3382 x_Sales_Tax_Geocode := l_exist_loc_rec.Sales_Tax_Geocode;
3383 x_Sales_Tax_Inside_City_Limits := l_exist_loc_rec.Sales_Tax_Inside_City_Limits;
3384
3385 END get_location_data;
3386
3387
3388 FUNCTION the_ar_miss_char RETURN VARCHAR2 IS
3389 BEGIN
3390 RETURN ar_miss_char;
3391 END;
3392
3393 FUNCTION the_ar_null_char RETURN VARCHAR2 IS
3394 BEGIN
3395 RETURN ar_null_char;
3396 END;
3397
3398 --}
3399
3400 FUNCTION check_tran_for_all_accts(p_location_id in number
3401 ) return BOOLEAN
3402 IS
3403 l_customer_id number;
3404 l_address_id number;
3405 l_org_id number;
3406
3407 --
3408 invoice_count varchar2(1);
3409 check_value varchar2(1);
3410
3411 -- find all possible customer accounts which use this location
3412 cursor c_cust_addrs is
3413 select acct_site.cust_account_id, acct_site.cust_acct_site_id
3414 from hz_locations loc,
3415 hz_party_sites party_site,
3416 hz_cust_acct_sites_all acct_site
3417 where loc.location_id = party_site.location_id
3418 and party_site.party_site_id = acct_site.party_site_id
3419 and loc.location_id = p_location_id;
3420 /*bug 4605384 cursor for loc*/
3421 cursor c_org_for_loc is
3422 select distinct acct_site.org_id
3423 from hz_locations loc,
3424 hz_party_sites party_site,
3425 hz_cust_acct_sites_all acct_site
3426 where loc.location_id = party_site.location_id
3427 and party_site.party_site_id = acct_site.party_site_id
3428 and loc.location_id = p_location_id;
3429 BEGIN
3430
3431
3432 BEGIN
3433
3434 -- check the flag: allow change to printed transaction is set to 'Y' or 'N'
3435 -- If it is set to 'Y', exit the function and customers are able to update the address
3436 -- If it is set to 'N', then check whether printed or posted transaction exits for
3437 -- bill-to or ship-to site,and the transaction has been applied
3438 -- and the transaction has any txa lines, if yes for above conditions, restrict update
3439 -- for customers on address component..
3440
3441 -- Note : This FUNCTION could have lived without customer_id argument i.e site_use_id
3442 -- was enough but then it would have meant to create two new indexes on :
3443 -- bill_to_site_use_id and ship_to_site_use_id. We have avoided this by using
3444 -- site_use_id (bill/ship) in conjunction with customer_id.
3445
3446
3447 /*start Bug 4605384*/
3448 open c_org_for_loc;
3449 LOOP
3450 fetch c_org_for_loc into l_org_id;
3451 exit when c_org_for_loc%NOTFOUND;
3452 BEGIN
3453 select change_printed_invoice_flag
3454 into check_value
3455 from ar_system_parameters_all where org_id=l_org_id;
3456
3457 --Bug fix 2183072 Handled the exception.
3458 EXCEPTION
3459 WHEN NO_DATA_FOUND THEN
3460 FND_MESSAGE.SET_NAME( 'AR','AR_NO_ROW_IN_SYSTEM_PARAMETERS');
3461 FND_MSG_PUB.ADD;
3462 END ;
3463
3464 if check_value = 'N' then
3465 exit;
3466 end if;
3467 END LOOP;
3468 --
3469 close c_org_for_loc;
3470 if check_value='Y' then
3471 return(FALSE);
3472 end if;
3473 /*End bug 4605384*/
3474 open c_cust_addrs;
3475 LOOP
3476 fetch c_cust_addrs into
3477 l_customer_id,
3478 l_address_id;
3479 exit when c_cust_addrs%notfound;
3480
3481 /* changed to call transaction_morg_exists */
3482 if transaction_morg_exists(l_address_id,l_customer_id) then
3483 return (TRUE); -- null; -- transaction exists
3484 else -- transaction exists for the location
3485 return (FALSE); -- transaction does not exists
3486 end if;
3487
3488 END LOOP;
3489 close c_cust_addrs ;
3490
3491 -- return (TRUE);
3492 return (FALSE); -- transaction does not exists
3493
3494 EXCEPTION
3495 WHEN NO_DATA_FOUND THEN
3496 NULL;
3497 END;
3498
3499 END check_tran_for_all_accts;
3500
3501
3502 ----------------------------------------------------------------------------------
3503 -- The procedure is called from insert_row/update_row. A check is made for the
3504 -- existance of edi location for all the addresses of the customer. The business
3505 -- rule is, "THE EDI LOCATION SHOULD BE UNIQUE FOR A CUSTOMER". Insert and Update
3506 -- are rejected with an error message if a duplicate is provided. Release-11
3507 -- change for EDI.
3508 ----------------------------------------------------------------------------------
3509 --
3510 procedure check_unique_edi_location(p_edi_location in varchar2,
3511 p_customer_id in number,
3512 p_orig_system_reference in varchar2) is
3513 dummy number;
3514 --
3515 begin
3516
3517 select count(1)
3518 into dummy
3519 from hz_cust_acct_sites addr
3520 where addr.cust_account_id = p_customer_id
3521 and addr.ece_tp_location_code = p_edi_location
3522 and addr.orig_system_reference <> p_orig_system_reference;
3523 --
3524 if ( dummy >=1 ) then
3525 fnd_message.set_name('AR','AR_CUST_ADDR_EDI_LOC_EXISTS');
3526 app_exception.raise_exception;
3527 end if;
3528 --
3529 end check_unique_edi_location;
3530 --
3531
3532 END arh_addr_pkg;