[Home] [Help]
PACKAGE BODY: APPS.ARH_CSU_PKG
Source
1 PACKAGE BODY arh_csu_pkg as
2 /* $Header: ARHCSUB.pls 120.9.12000000.2 2007/08/22 13:20:59 rmanikan ship $*/
3 --
4 --
5 --
6 -- PROCEDURE
7 -- maintain_denormalized_data
8 --
9 -- DESCRIPTION
10 -- This procedure coordinates the calling of routines to maintains
11 -- denormailized site_use data.
12 --
13 -- SCOPE - PUBLIC
14 --
15 -- EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
16 --
17 -- ARGUMENTS : IN: p_customer_id
18 -- p_address_id
19 -- p_site_use_id
20 -- p_site_use_code
21 -- p_primary_flag
22 -- OUT:
23 --
24 -- NOTES
25 --
26 --
27 --
28 --
29 FUNCTION INIT_SWITCH
30 ( p_date IN DATE,
31 p_switch IN VARCHAR2 DEFAULT 'NULL_GMISS')
32 RETURN DATE
33 IS
34 res_date date;
35 BEGIN
36 IF p_switch = 'NULL_GMISS' THEN
37 IF p_date IS NULL THEN
38 res_date := FND_API.G_MISS_DATE;
39 ELSE
40 res_date := p_date;
41 END IF;
42 ELSIF p_switch = 'GMISS_NULL' THEN
43 IF p_date = FND_API.G_MISS_DATE THEN
44 res_date := NULL;
45 ELSE
46 res_date := p_date;
47 END IF;
48 ELSE
49 res_date := TO_DATE('31/12/1800','DD/MM/RRRR');
50 END IF;
51 RETURN res_date;
52 END;
53
54 FUNCTION INIT_SWITCH
55 ( p_char IN VARCHAR2,
56 p_switch IN VARCHAR2 DEFAULT 'NULL_GMISS')
57 RETURN VARCHAR2
58 IS
59 res_char varchar2(2000);
60 BEGIN
61 IF p_switch = 'NULL_GMISS' THEN
62 IF p_char IS NULL THEN
63 return FND_API.G_MISS_CHAR;
64 ELSE
65 return p_char;
66 END IF;
67 ELSIF p_switch = 'GMISS_NULL' THEN
68 IF p_char = FND_API.G_MISS_CHAR THEN
69 return NULL;
70 ELSE
71 return p_char;
72 END IF;
73 ELSE
74 return ('INCORRECT_P_SWITCH');
75 END IF;
76 END;
77
78 FUNCTION INIT_SWITCH
79 ( p_num IN NUMBER,
80 p_switch IN VARCHAR2 DEFAULT 'NULL_GMISS')
81 RETURN NUMBER
82 IS
83 BEGIN
84 IF p_switch = 'NULL_GMISS' THEN
85 IF p_num IS NULL THEN
86 return FND_API.G_MISS_NUM;
87 ELSE
88 return p_num;
89 END IF;
90 ELSIF p_switch = 'GMISS_NULL' THEN
91 IF p_num = FND_API.G_MISS_NUM THEN
92 return NULL;
93 ELSE
94 return p_num;
95 END IF;
96 ELSE
97 return ('9999999999');
98 END IF;
99 END;
100
101
102 procedure maintain_denormalized_data ( p_customer_id in number,
103 p_address_id in number,
104 p_site_use_id in number,
105 p_site_use_code in varchar2,
106 p_status in varchar2,
107 p_primary_flag in varchar2 ) is
108 --
109 l_site_use_flag varchar2(1);
110 --
111 BEGIN
112 --
113 --
114 if ( p_site_use_code in ('BILL_TO','SHIP_TO','MARKET' )) then
115 --
116 --
117 if ( p_status = 'A' ) then
118 if ( p_primary_flag = 'Y' ) then
119 l_site_use_flag := 'P';
120 else
121 l_site_use_flag := 'Y';
122 end if;
123 else
124 l_site_use_flag := null;
125 end if;
126 --
127 arh_addr_pkg.update_site_use_flag(p_address_id => p_address_id,
128 p_site_use_code => p_site_use_code,
129 p_site_use_flag => l_site_use_flag);
130 end if;
131 --
132 end maintain_denormalized_data;
133 --
134 --
135 --
136 PROCEDURE delete_customer_alt_names(p_rowid in varchar2,
137 p_status in varchar2,
138 p_customer_id in number,
139 p_site_use_id in number
140 ) is
141 l_status varchar2(1);
142 l_lock_status number;
143 begin
144 --
145 --
146 if (
147 -- ( nvl ( fnd_profile.value('AR_ALT_NAME_SEARCH') , 'N' ) = 'Y' ) and
148 ( p_status = 'I')
149 ) then
150 --
151 select status
152 into l_status
153 from hz_cust_site_uses
154 where site_use_id = p_site_use_id;
155
156 --
157 if ( l_status = 'A' ) then
158 --
159 arp_cust_alt_match_pkg.lock_match ( p_customer_id,
160 p_site_use_id,
161 l_lock_status );
162 --
163 if ( l_lock_status = 1 ) then
164 --
165 -- Fixed bug 928111: added alt_name to call, since
166 -- not derivable from this location, I am passing NULL
167 arp_cust_alt_match_pkg.delete_match ( p_customer_id,
168 p_site_use_id, NULL );
169 --
170 end if;
171 --
172 end if;
173 --
174 end if;
175 --
176 --
177 exception
178 when OTHERS then
179 arp_standard.debug('EXCEPTION: arh_csu_pkg.delete_customer_alt_names');
180 end delete_customer_alt_names;
181 --
182 --
183 --
184 -- PROCEDURE
185 -- site_use_exists
186 --
187 -- DESCRIPTION
188 -- This procedure determins if an address has a site use of a particular
189 -- Type.
190 --
191 -- SCOPE - PUBLIC
192 --
193 -- EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
194 --
195 -- ARGUMENTS : IN:
196 -- p_address_id -
197 -- p_site_use_code - type of site use to create
198 -- OUT:
199 -- p_site_use_id - id of site use type
200 -- p_site_use_status - status of site use type
201 --
202 -- RETURNS null
203 --
204 -- NOTES
205 --
206 PROCEDURE site_use_exists( p_address_id in number,
207 p_site_use_code in varchar2,
208 p_site_use_id out nocopy number,
209 p_site_use_status out nocopy varchar2,
210 x_msg_count OUT NOCOPY number,
211 x_msg_data OUT NOCOPY VARCHAR2,
212 x_return_status OUT NOCOPY VARCHAR2
213 ) is
214 --
215 --
216 -- An address may only have one active site_use per type
217 -- However an address may have many number of inactive site_uses
218 --
219 -- The order by in the cursor ensures that we test the for active
220 -- site uses first. If there ar multiple inactive we simply return the
221 -- first we encounter.
222 --
223 cursor c_site_use is select su.site_use_id,
224 su.status
225 from hz_cust_site_uses su
226 where su.cust_acct_site_id = p_address_id
227 and su.site_use_code = p_site_use_code
228 order
229 by su.status;
230 begin
231 open c_site_use;
232 fetch c_site_use into p_site_use_id,p_site_use_status;
233 close c_site_use;
234 end site_use_exists;
235 --
236 --
237 --
238 -- PROCEDURE
239 -- update_su_status
240 --
241 -- DESCRIPTION
242 -- This procedure updates the staus of a row in hz_cust_site_uses
243 --
244 -- SCOPE - PUBLIC
245 --
246 -- EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
247 --
248 -- ARGUMENTS : IN:
249 -- p_customer_id
250 -- p_address_id
251 -- p_site_use_code
252 -- p_site_use_id - id of row to be updated
253 -- p_status - status to update row to(A,I)
254 -- OUT:
255 --
256 -- NOTES
257 --
258 --
259 --
260 --
261 PROCEDURE update_su_status ( p_customer_id in number,
262 p_address_id in number,
263 p_site_use_id in number,
264 p_site_use_code in varchar2,
265 p_status in varchar2 ) is
266 --
267 --
268 begin
269 --
270 --
271 update hz_cust_site_uses su
272 set su.status = p_status
273 where su.site_use_id = p_site_use_id;
274 --
275 --
276 if (SQL%NOTFOUND) then
277 raise NO_DATA_FOUND;
278 end if;
279 --
280 --
281 /* bug3297313 : The call to procedure maintain_denormalized_data is commented out
282 as this will be taken care in TCA API
283 */
284 /*
285 maintain_denormalized_data ( p_customer_id => p_customer_id,
286 p_address_id => p_address_id,
287 p_site_use_id => p_site_use_id,
288 p_site_use_code => p_site_use_code,
289 p_status => p_status,
290 p_primary_flag => 'N'
291 );
292 */
293 --
294 end update_su_status;
295 --
296 --
297 --
298 --
299 --
300 -- PROCEDURE
301 -- check_unique_site_use_code
302 --
303 -- DESCRIPTION
304 -- This procedure ensures validates to ensure
305 -- 1). An addres has only active site use per type.
306 -- 2). A Customer only has one primary active site use of each type
307 -- DUN
308 -- STMTS
309 -- LEGAL
310 --
311 -- SCOPE - PUBLIC
312 --
313 -- EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
314 --
315 -- ARGUMENTS : IN:
316 -- - p_site_use_id
317 -- - p_customer_id
318 -- - p_address_id,
319 -- - p_site_use_code
320 --
321 -- OUT:
322 --
323 -- NOTES
324 --
325 --
326 --
327 procedure check_unique_site_use_code( p_site_use_id in number,
328 p_customer_id in number,
329 p_address_id in number,
330 p_site_use_code in varchar2 ) is
331 --
332 --
333 dummy number;
334 begin
335 --
336 -- A customer can only have one primary active DUN,STMTS,LEGAL site use
337 --
338 if ( p_site_use_code in ('STMTS', 'DUN', 'LEGAL' ) )then
339 --
340 --
341 select count(1)
342 into dummy
343 from hz_cust_site_uses su,
344 hz_cust_acct_sites a
345 where su.cust_acct_site_ID = a.cust_acct_site_ID
346 and a.cust_account_id = p_customer_id
347 and su.site_use_code = p_site_use_code
348 and su.status = 'A'
349 and ( ( p_site_use_id is null ) or ( p_site_use_id <> site_use_id));
350 --
351 --
352 if (dummy >= 1 ) then
353 if (p_site_use_code = 'DUN' ) then
354 fnd_message.set_name('AR','AR_CUST_ONE_ACTIVE_DUN_SITE');
355 elsif ( p_site_use_code = 'LEGAL' ) then
356 fnd_message.set_name('AR','AR_CUST_ONE_ACTIVE_LEGAL_SITE');
357 elsif ( p_site_use_code = 'STMTS' ) then
358 fnd_message.set_name('AR','AR_CUST_ONE_ACTIVE_STMTS_SITE');
359 end if;
360 --
361 app_exception.raise_exception;
362 end if;
363 --
364 end if;
365 --
366 -- An address can only have one active site use of each type.
367 --
368 select count(1)
369 into dummy
370 from hz_cust_site_uses
371 where site_use_code = p_site_use_code
372 and cust_acct_site_ID = p_address_id
373 and status = 'A'
374 and ( ( p_site_use_id is null ) or site_use_id <> p_site_use_id);
375 --
376 if (dummy >= 1) then
377 fnd_message.set_name('AR','AR_CUST_ONE_ACTIVE_BS_PER_ADDR');
378 app_exception.raise_exception;
379 end if;
380 --
381 --
382 end check_unique_site_use_code;
383
384 -- PROCEDURE
385 -- check_unique_site_use_code
386 --
387 -- DESCRIPTION
388 -- This procedure ensures validates to ensure
389 -- 1). An addres has only active site use per type.
390 -- 2). A Customer only has one primary active site use of each type
391 -- DUN
392 -- STMTS
393 -- LEGAL
394 --
395 -- SCOPE - PUBLIC
396 --
397 -- EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
398 --
399 -- ARGUMENTS : IN:
400 -- - p_site_use_id
401 -- - p_customer_id
402 -- - p_address_id,
403 -- - p_site_use_code
404 --
405 -- OUT: x_return_status
406 -- x_msg_count
407 -- x_msg_data
408 -- NOTES Overloaded the method so that instead of rasing an exception, it add the mesage to FND stack, for graceful handling of error situation.
409 --
410 --
411 --
412 procedure check_unique_site_use_code( p_site_use_id in number,
413 p_customer_id in number,
414 p_address_id in number,
415 p_site_use_code in varchar2,
416 x_return_status out nocopy varchar2,
417 x_msg_count out nocopy number,
418 x_msg_data out nocopy varchar2 ) is
419 --
420 --
421 dummy number;
422 begin
423 --
424 -- A customer can only have one primary active DUN,STMTS,LEGAL site use
425 --
426 if ( p_site_use_code in ('STMTS', 'DUN', 'LEGAL' ) )then
427 --
428 --
429 select count(1)
430 into dummy
431 from hz_cust_site_uses su,
432 hz_cust_acct_sites a
433 where su.cust_acct_site_ID = a.cust_acct_site_ID
434 and a.cust_account_id = p_customer_id
435 and su.site_use_code = p_site_use_code
436 and su.status = 'A'
437 and ( ( p_site_use_id is null ) or ( p_site_use_id <> site_use_id));
438 --
439 --
440 if (dummy >= 1 ) then
441 if (p_site_use_code = 'DUN' ) then
442 fnd_message.set_name('AR','AR_CUST_ONE_ACTIVE_DUN_SITE');
443 x_msg_data := 'AR_CUST_ONE_ACTIVE_DUN_SITE';
444 elsif ( p_site_use_code = 'LEGAL' ) then
445 fnd_message.set_name('AR','AR_CUST_ONE_ACTIVE_LEGAL_SITE');
446 x_msg_data := 'AR_CUST_ONE_ACTIVE_LEGAL_SITE';
447 elsif ( p_site_use_code = 'STMTS' ) then
448 fnd_message.set_name('AR','AR_CUST_ONE_ACTIVE_STMTS_SITE');
449 x_msg_data := 'AR_CUST_ONE_ACTIVE_STMTS_SITE';
450 end if;
451 --
452 --app_exception.raise_exception;
453 FND_MSG_PUB.ADD;
454 x_return_status := FND_API.G_RET_STS_ERROR;
455 x_msg_count := 1;
456 return;
457 end if;
458 --
459 end if;
460 --
461 -- An address can only have one active site use of each type.
462 --
463 select count(1)
464 into dummy
465 from hz_cust_site_uses
466 where site_use_code = p_site_use_code
467 and cust_acct_site_ID = p_address_id
468 and status = 'A'
469 and ( ( p_site_use_id is null ) or site_use_id <> p_site_use_id);
470 --
471 if (dummy >= 1) then
472 fnd_message.set_name('AR','AR_CUST_ONE_ACTIVE_BS_PER_ADDR');
473 --app_exception.raise_exception;
474 FND_MSG_PUB.ADD;
475 x_return_status := FND_API.G_RET_STS_ERROR;
476 x_msg_count := 1;
477 x_msg_data := 'AR_CUST_ONE_ACTIVE_BS_PER_ADDR';
478 return;
479
480 end if;
481 --
482 --
483 end check_unique_site_use_code;
484
485
486 --
487 --
488 --
489 --
490 --
491 -- PROCEDURE
492 -- check_unique_location
493 --
494 -- DESCRIPTION
495 -- This procedure ensures validates to ensure rows
496 -- in hz_cust_site_uses have unique locations within
497 -- customer/site_use_code
498 --
499 -- SCOPE - PUBLIC
500 --
501 -- EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
502 --
503 -- ARGUMENTS : IN:
504 -- - p_site_use_id
505 -- - p_customer_id
506 -- - p_site_use_code
507 -- - p_location
508 --
509 -- OUT:
510 --
511 -- NOTES
512 --
513 --
514 --
515 PROCEDURE check_unique_location
516 ( p_site_use_id IN NUMBER,
517 p_customer_id IN NUMBER,
518 p_site_use_code IN VARCHAR2,
519 p_location IN VARCHAR2)
520 IS
521 dummy number;
522 --
523 BEGIN
524 --
525 --
526 -- Site Use location must be unique within customer,site_use_code
527 --
528 --Bug No : 2998504. Status check added to the where clause.
529
530 BEGIN
531
532 select 1
533 into dummy
534 from hz_cust_site_uses su,
535 hz_cust_acct_sites addr
536 where su.cust_acct_site_ID = addr.cust_acct_site_ID
537 and su.site_use_code = p_site_use_code
538 and su.location = p_location
539 and addr.cust_account_ID = p_customer_id
540 and ( ( p_site_use_id is null ) or ( site_use_id <> p_site_use_id ))
541 and su.STATUS = 'A'
542 and rownum = 1;
543 --
544 IF ( dummy >= 1 ) THEN
545 fnd_message.set_name('AR','AR_CUST_DUP_CODE_LOCATION');
546 app_exception.raise_exception;
547 END IF;
548
549 EXCEPTION
550 WHEN NO_DATA_FOUND THEN
551 NULL;
552 END;
553 --
554 --
555 END check_unique_location;
556 --
557
558 --
559 -- PROCEDURE
560 -- check_unique_location
561 --
562 -- DESCRIPTION
563 -- This procedure ensures validates to ensure rows
564 -- in hz_cust_site_uses have unique locations within
565 -- customer/site_use_code
566 --
567 -- SCOPE - PUBLIC
568 --
569 -- EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
570 --
571 -- ARGUMENTS : IN:
572 -- - p_site_use_id
573 -- - p_customer_id
574 -- - p_site_use_code
575 -- - p_location
576 --
577 -- OUT:
578 -- - x_return_status
579 -- - x_msg_count
580 -- - x_msg_data
581 -- NOTES Overloaded the method so that instead of rasing an exception, it add the mesage to FND stack, for graceful handling of error situation.
582 --
583 --
584 --
585 PROCEDURE check_unique_location
586 ( p_site_use_id IN NUMBER,
587 p_customer_id IN NUMBER,
588 p_site_use_code IN VARCHAR2,
589 p_location IN VARCHAR2,
590 x_return_status out nocopy varchar2,
591 x_msg_count out nocopy number,
592 x_msg_data out nocopy varchar2)
593 IS
594 dummy number;
595 --
596 BEGIN
597 --
598 --
599 -- Site Use location must be unique within customer,site_use_code
600 --
601 --Bug No : 2998504. Status check added to the where clause.
602 BEGIN
603
604 select 1
605 into dummy
606 from hz_cust_site_uses su,
607 hz_cust_acct_sites addr
608 where su.cust_acct_site_ID = addr.cust_acct_site_ID
609 and su.site_use_code = p_site_use_code
610 and su.location = p_location
611 and addr.cust_account_ID = p_customer_id
612 and ( ( p_site_use_id is null ) or ( site_use_id <> p_site_use_id ))
613 and su.org_id = (select org_id from hz_cust_site_uses where site_use_id=p_site_use_id) -- 6066859
614 and su.STATUS = 'A'
615 and rownum = 1;
616 --
617 IF ( dummy >= 1 ) THEN
618 fnd_message.set_name('AR','AR_CUST_DUP_CODE_LOCATION');
619 --app_exception.raise_exception;
620 FND_MSG_PUB.ADD;
621 x_return_status := FND_API.G_RET_STS_ERROR;
622 x_msg_count := 1;
623 x_msg_data := 'AR_CUST_DUP_CODE_LOCATION';
624 return;
625 END IF;
626
627 EXCEPTION
628 WHEN NO_DATA_FOUND THEN
629 NULL;
630 END;
631 --
632 --
633 END check_unique_location;
634 --
635
636
637
638 --
639 --
640 -- PROCEDURE
641 -- check_primary
642 --
643 -- DESCRIPTION
644 -- This procedure ensures that an address only has one active
645 -- site use per type.
646 --
647 -- SCOPE - PUBLIC
648 --
649 -- EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
650 --
651 -- ARGUMENTS : IN:
652 -- - p_site_use_id
653 -- - p_customer_id,
654 -- - p_site_use_code
655 --
656 -- OUT:
657 --
658 -- NOTES
659 --
660 --
661 --
662 PROCEDURE check_primary ( p_site_use_id in number, p_customer_id in number, p_site_use_code in varchar2) is
663 --
664 dummy number;
665 l_site_use_meaning varchar2(80);
666 --
667 begin
668 select count(1)
669 into dummy
670 from hz_cust_accounts cust,
671 hz_cust_acct_sites addr,
672 hz_cust_site_uses su
673 where su.cust_acct_site_ID = addr.cust_acct_site_ID
674 and addr.cust_account_ID = cust.cust_account_ID
675 and cust.cust_account_ID = p_customer_id
676 and site_use_code = p_site_use_code
677 and su.primary_flag = 'Y'
678 and su.status = 'A'
679 and ( ( p_site_use_id is null) or (site_use_id <> p_site_use_id));
680 --
681 if ( dummy >= 1 ) then
682 --
683 --
684 select meaning
685 into l_site_use_meaning
686 from ar_lookups
687 where lookup_type = 'SITE_USE_CODE'
688 and lookup_code = p_site_use_code;
689 --
690 fnd_message.set_name('AR','AR_CUST_ONE_PRIMARY_SU');
691 fnd_message.set_token('SITE_CODE',l_site_use_meaning);
692 app_exception.raise_exception;
693 end if;
694 --
695 end check_primary;
696 --
697 --
698 --
699 PROCEDURE Insert_Row(
700 X_Site_Use_Id IN OUT NOCOPY NUMBER,
701 X_Last_Update_Date DATE,
702 X_Last_Updated_By NUMBER,
703 X_Creation_Date DATE,
704 X_Created_By NUMBER,
705 X_Site_Use_Code VARCHAR2,
706 X_customer_id NUMBER,
707 X_Address_Id NUMBER,
708 X_Primary_Flag VARCHAR2,
709 X_Status VARCHAR2,
710 X_Location IN OUT NOCOPY VARCHAR2,
711 X_Last_Update_Login NUMBER,
712 X_Contact_Id NUMBER,
713 X_Bill_To_Site_Use_Id NUMBER,
714 X_Sic_Code VARCHAR2,
715 X_Payment_Term_Id NUMBER,
716 X_Gsa_Indicator VARCHAR2,
717 X_Ship_Partial VARCHAR2,
718 X_Ship_Via VARCHAR2,
719 X_Fob_Point VARCHAR2,
720 X_Order_Type_Id NUMBER,
721 X_Price_List_Id NUMBER,
722 X_Freight_Term VARCHAR2,
723 X_Warehouse_Id NUMBER,
724 X_Territory_Id NUMBER,
725 X_Tax_Code VARCHAR2,
726 X_Tax_Reference VARCHAR2,
727 X_Demand_Class_Code VARCHAR2,
728 x_inventory_location_id NUMBER,
729 x_inventory_organization_id NUMBER,
730 X_Attribute_Category VARCHAR2,
731 X_Attribute1 VARCHAR2,
732 X_Attribute2 VARCHAR2,
733 X_Attribute3 VARCHAR2,
734 X_Attribute4 VARCHAR2,
735 X_Attribute5 VARCHAR2,
736 X_Attribute6 VARCHAR2,
737 X_Attribute7 VARCHAR2,
738 X_Attribute8 VARCHAR2,
739 X_Attribute9 VARCHAR2,
740 X_Attribute10 VARCHAR2,
741 X_Attribute11 VARCHAR2,
742 X_Attribute12 VARCHAR2,
743 X_Attribute13 VARCHAR2,
744 X_Attribute14 VARCHAR2,
745 X_Attribute15 VARCHAR2,
746 X_Attribute16 VARCHAR2,
747 X_Attribute17 VARCHAR2,
748 X_Attribute18 VARCHAR2,
749 X_Attribute19 VARCHAR2,
750 X_Attribute20 VARCHAR2,
751 X_Attribute21 VARCHAR2,
752 X_Attribute22 VARCHAR2,
753 X_Attribute23 VARCHAR2,
754 X_Attribute24 VARCHAR2,
755 X_Attribute25 VARCHAR2,
756 X_Tax_Classification VARCHAR2,
757 X_Tax_Header_Level_Flag VARCHAR2,
758 X_Tax_Rounding_Rule VARCHAR2,
759 X_Global_Attribute_Category VARCHAR2,
760 X_Global_Attribute1 VARCHAR2,
761 X_Global_Attribute2 VARCHAR2,
762 X_Global_Attribute3 VARCHAR2,
763 X_Global_Attribute4 VARCHAR2,
764 X_Global_Attribute5 VARCHAR2,
765 X_Global_Attribute6 VARCHAR2,
766 X_Global_Attribute7 VARCHAR2,
767 X_Global_Attribute8 VARCHAR2,
768 X_Global_Attribute9 VARCHAR2,
769 X_Global_Attribute10 VARCHAR2,
770 X_Global_Attribute11 VARCHAR2,
771 X_Global_Attribute12 VARCHAR2,
772 X_Global_Attribute13 VARCHAR2,
773 X_Global_Attribute14 VARCHAR2,
774 X_Global_Attribute15 VARCHAR2,
775 X_Global_Attribute16 VARCHAR2,
776 X_Global_Attribute17 VARCHAR2,
777 X_Global_Attribute18 VARCHAR2,
778 X_Global_Attribute19 VARCHAR2,
779 X_Global_Attribute20 VARCHAR2,
780 X_Primary_Salesrep_Id NUMBER DEFAULT NULL,
781 X_Finchrg_Receivables_Trx_Id NUMBER DEFAULT NULL,
782 X_GL_ID_Rec NUMBER,
783 X_GL_ID_Rev NUMBER,
784 X_GL_ID_Tax NUMBER,
785 X_GL_ID_Freight NUMBER,
786 X_GL_ID_Clearing NUMBER,
787 X_GL_ID_Unbilled NUMBER,
788 X_GL_ID_Unearned NUMBER,
789 X_GL_ID_Unpaid_rec NUMBER,
790 X_GL_ID_Remittance NUMBER,
791 X_GL_ID_Factor NUMBER,
792 X_DATES_NEGATIVE_TOLERANCE NUMBER,
793 X_DATES_POSITIVE_TOLERANCE NUMBER,
794 X_DATE_TYPE_PREFERENCE VARCHAR2,
795 X_OVER_SHIPMENT_TOLERANCE NUMBER,
796 X_UNDER_SHIPMENT_TOLERANCE NUMBER,
797 X_ITEM_CROSS_REF_PREF VARCHAR2,
798 X_OVER_RETURN_TOLERANCE NUMBER,
799 X_UNDER_RETURN_TOLERANCE NUMBER,
800 X_SHIP_SETS_INCLUDE_LINES_FLAG VARCHAR2,
801 X_ARRIVALSETS_INCL_LINES_FLAG VARCHAR2,
802 X_SCHED_DATE_PUSH_FLAG VARCHAR2,
803 X_INVOICE_QUANTITY_RULE VARCHAR2,
804 x_msg_count OUT NOCOPY number,
805 x_msg_data OUT NOCOPY VARCHAR2,
806 x_return_status OUT NOCOPY VARCHAR2
807 )
808
809 IS
810
811 suse_rec hz_cust_account_site_v2pub.cust_site_use_rec_type;
812 profile_rec hz_customer_profile_v2pub.customer_profile_rec_type;
813 tmp_var VARCHAR2(2000);
814 i NUMBER;
815 tmp_var1 VARCHAR2(2000);
816
817 BEGIN
818
819
820 IF X_SITE_USE_CODE <> FND_API.G_MISS_CHAR AND X_SITE_USE_CODE IS NOT NULL THEN
821
822
823 x_return_status := FND_API.G_RET_STS_SUCCESS;
824
825
826 select hz_cust_site_uses_s.nextval into x_site_use_id from dual;
827 --
828 --
829 --
830 -- Location will be null if automatic site number = 'Y'
831 -- or form parameter :parameter.addr_mode = 'QUICK'
832 --
833 if ( x_location is null ) then
834 x_location := x_site_use_id ;
835 end if;
836 --
837 --
838
839 if ( x_status = 'A' ) then
840 --Calling overloaded check_unique_site_use_code, for graceful handling of error situation
841 check_unique_site_use_code( p_site_use_id => x_site_use_id,
842 p_customer_id => x_customer_id,
843 p_address_id => x_address_id,
844 p_site_use_code => x_site_use_code,
845 x_return_status => x_return_status,
846 x_msg_count => x_msg_count,
847 x_msg_data => x_msg_data
848 );
849 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
850 return;
851 end if;
852 end if;
853 --
854 --Calling overloaded check_unique_site_use_location, for graceful handling of error situation
855
856 check_unique_location ( p_site_use_id => x_site_use_id,
857 p_customer_id => x_customer_id,
858 p_site_use_code => x_site_use_code,
859 p_location => x_location,
860 x_return_status => x_return_status,
861 x_msg_count => x_msg_count,
862 x_msg_data => x_msg_data
863 );
864 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
865 return;
866 end if;
867 --
868 --
869 -- Bug#3170887: Commented out no longer necessary logic handled by V2 api
870 --{
871 --if (x_primary_flag = 'Y' and x_status = 'A' ) then
872 -- check_primary( p_site_use_id => x_site_use_id,
873 -- p_customer_id => x_customer_id,
874 -- p_site_use_code => x_site_use_code);
875 --end if;
876 --}
877 --
878 -- If inventory_location_id is not null insert a row into
879 -- po_location_associations
880 --
881
882 if ( x_site_use_code = 'SHIP_TO' and x_inventory_location_id is not null ) then
883 --
884 --
885 arp_clas_pkg.insert_po_loc_associations( p_inventory_location_id => x_inventory_location_id,
886 p_inventory_organization_id => x_inventory_organization_id,
887 p_customer_id => x_customer_id,
888 p_address_id => x_address_id,
889 p_site_use_id => x_site_use_id,
890 x_return_status => x_return_status,
891 x_msg_count => x_msg_count,
892 x_msg_data => x_msg_data
893 );
894 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
895 return;
896 END IF;
897 end if;
898
899
900 suse_rec.site_use_id := x_site_use_id;
901 suse_rec.cust_acct_site_id := X_Address_Id;
902 suse_rec.site_use_code := X_Site_Use_Code;
903 suse_rec.primary_flag := X_Primary_Flag;
904 suse_rec.location := X_Location;
905 suse_rec.contact_id := X_Contact_id;
906 suse_rec.Bill_To_Site_Use_Id := X_Bill_To_Site_Use_Id;
907 suse_rec.sic_code := X_Sic_Code;
908 suse_rec.payment_term_id := X_Payment_Term_Id;
909 suse_rec.Gsa_Indicator := X_Gsa_Indicator;
910 suse_rec.ship_partial := X_Ship_Partial;
911 suse_rec.ship_via := X_Ship_Via;
912 suse_rec.fob_point := X_Fob_Point;
913 suse_rec.order_type_id := X_Order_Type_Id;
914 suse_rec.price_list_id := X_Price_List_Id;
915 suse_rec.freight_term := X_Freight_Term;
916 suse_rec.warehouse_id := X_Warehouse_Id;
917 suse_rec.territory_id := X_Territory_Id;
918 suse_rec.Tax_Reference := X_Tax_Reference;
919 suse_rec.status := x_status;
920 suse_rec.tax_code := X_Tax_Code;
921 suse_rec.Demand_Class_Code := X_Demand_Class_Code;
922 suse_rec.Tax_Header_Level_Flag := X_Tax_Header_Level_Flag;
923 suse_rec.Tax_Rounding_Rule := X_Tax_Rounding_Rule;
924 suse_rec.Primary_Salesrep_Id := X_Primary_Salesrep_Id;
925 suse_rec.Finchrg_Receivables_Trx_Id := X_Finchrg_Receivables_Trx_Id;
926 suse_rec.DATES_NEGATIVE_TOLERANCE := X_DATES_NEGATIVE_TOLERANCE;
927 suse_rec.DATES_POSITIVE_TOLERANCE := X_DATES_POSITIVE_TOLERANCE;
928 suse_rec.DATE_TYPE_PREFERENCE := X_DATE_TYPE_PREFERENCE;
929 suse_rec.OVER_SHIPMENT_TOLERANCE := X_OVER_SHIPMENT_TOLERANCE;
930 suse_rec.UNDER_SHIPMENT_TOLERANCE := X_UNDER_SHIPMENT_TOLERANCE;
931 suse_rec.ITEM_CROSS_REF_PREF := X_ITEM_CROSS_REF_PREF;
932 suse_rec.SHIP_SETS_INCLUDE_LINES_FLAG := X_SHIP_SETS_INCLUDE_LINES_FLAG;
933 suse_rec.ARRIVALSETS_INCLUDE_LINES_FLAG:= X_ARRIVALSETS_INCL_LINES_FLAG;
934 suse_rec.SCHED_DATE_PUSH_FLAG := X_SCHED_DATE_PUSH_FLAG;
935 suse_rec.INVOICE_QUANTITY_RULE := X_INVOICE_QUANTITY_RULE;
936 suse_rec.OVER_RETURN_TOLERANCE := X_OVER_RETURN_TOLERANCE;
937 suse_rec.UNDER_RETURN_TOLERANCE := X_UNDER_RETURN_TOLERANCE;
938 suse_rec.GL_ID_Rec := X_GL_ID_Rec;
939 suse_rec.GL_ID_Rev := X_GL_ID_Rev;
940 suse_rec.GL_ID_Tax := X_GL_ID_Tax;
941 suse_rec.GL_ID_Freight := X_GL_ID_Freight;
942 suse_rec.GL_ID_Clearing := X_GL_ID_Clearing;
943 suse_rec.GL_ID_Unbilled := X_GL_ID_Unbilled;
944 suse_rec.GL_ID_Unearned := X_GL_ID_Unearned;
945 suse_rec.GL_ID_unpaid_rec := X_GL_ID_Unpaid_rec;
946 suse_rec.GL_ID_remittance := X_GL_ID_remittance;
947 suse_rec.GL_ID_factor := X_GL_ID_factor;
948 suse_rec.attribute_category := x_attribute_category;
949 suse_rec.attribute1 := X_Attribute1;
950 suse_rec.attribute2 := X_Attribute2;
951 suse_rec.attribute3 := X_Attribute3;
952 suse_rec.attribute4 := X_Attribute4;
953 suse_rec.attribute5 := X_Attribute5;
954 suse_rec.attribute6 := X_Attribute6;
955 suse_rec.attribute7 := X_Attribute7;
956 suse_rec.attribute8 := X_Attribute8;
957 suse_rec.attribute9 := X_Attribute9;
958 suse_rec.attribute10 := X_Attribute10;
959 suse_rec.attribute11 := X_Attribute11;
960 suse_rec.attribute12 := X_Attribute12;
961 suse_rec.attribute13 := X_Attribute13;
962 suse_rec.attribute14 := X_Attribute14;
963 suse_rec.attribute15 := X_Attribute15;
964 suse_rec.attribute16 := X_Attribute16;
965 suse_rec.attribute17 := X_Attribute17;
966 suse_rec.attribute18 := X_Attribute18;
967 suse_rec.attribute19 := X_Attribute19;
968 suse_rec.attribute20 := X_Attribute20;
969 suse_rec.attribute21 := X_Attribute21;
970 suse_rec.attribute22 := X_Attribute22;
971 suse_rec.attribute23 := X_Attribute23;
972 suse_rec.attribute24 := X_Attribute24;
973 suse_rec.attribute25 := X_Attribute25;
974 suse_rec.Global_Attribute_Category := X_Global_Attribute_Category;
975 suse_rec.Global_Attribute1 := X_Global_Attribute1;
976 suse_rec.Global_Attribute2 := X_Global_Attribute2;
977 suse_rec.Global_Attribute3 := X_Global_Attribute3;
978 suse_rec.Global_Attribute4 := X_Global_Attribute4;
979 suse_rec.Global_Attribute5 := X_Global_Attribute5;
980 suse_rec.Global_Attribute6 := X_Global_Attribute6;
981 suse_rec.Global_Attribute7 := X_Global_Attribute7;
982 suse_rec.Global_Attribute8 := X_Global_Attribute8;
983 suse_rec.Global_Attribute9 := X_Global_Attribute9;
984 suse_rec.Global_Attribute10 := X_Global_Attribute10;
985 suse_rec.Global_Attribute11 := X_Global_Attribute11;
986 suse_rec.Global_Attribute12 := X_Global_Attribute12;
987 suse_rec.Global_Attribute13 := X_Global_Attribute13;
988 suse_rec.Global_Attribute14 := X_Global_Attribute14;
989 suse_rec.Global_Attribute15 := X_Global_Attribute15;
990 suse_rec.Global_Attribute16 := X_Global_Attribute16;
991 suse_rec.Global_Attribute17 := X_Global_Attribute17;
992 suse_rec.Global_Attribute18 := X_Global_Attribute18;
993 suse_rec.Global_Attribute19 := X_Global_Attribute19;
994 suse_rec.Global_Attribute20 := X_Global_Attribute20;
995 suse_rec.tax_classification := x_tax_classification;
996 suse_rec.created_by_module := 'TCA_FORM_WRAPPER';
997
998 HZ_CUST_ACCOUNT_SITE_V2PUB.create_cust_site_use (
999 p_cust_site_use_rec => suse_rec,
1000 p_customer_profile_rec => profile_rec,
1001 p_create_profile => fnd_api.g_false,
1002 p_create_profile_amt => fnd_api.g_false,
1003 x_site_use_id => x_site_use_id,
1004 x_return_status => x_return_status,
1005 x_msg_count => x_msg_count,
1006 x_msg_data => x_msg_data );
1007
1008 IF x_msg_count > 1 THEN
1009 FOR i IN 1..x_msg_count LOOP
1010 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
1011 tmp_var1 := tmp_var1 || ' '|| tmp_var;
1012 END LOOP;
1013 x_msg_data := tmp_var1;
1014 END IF;
1015
1016 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1017 return;
1018 END IF;
1019
1020 --
1021 -- Update denormalized site_use_id's on RA_CUSTOMERS,RA_ADDRESSES
1022 --
1023 --
1024 /* bug3297313 : The call to procedure maintain_denormalized_data is commented out
1025 as this will be taken care in TCA API
1026 */
1027 /*
1028 maintain_denormalized_data(
1029 p_customer_id => x_customer_id,
1030 p_address_id => x_address_id,
1031 p_site_use_id => x_site_use_id,
1032 p_site_use_code => x_site_use_code,
1033 p_status => x_status,
1034 p_primary_flag => x_primary_flag );
1035 */
1036 --
1037 --
1038 --
1039 END IF;
1040
1041 END Insert_Row;
1042 --
1043 --
1044
1045 PROCEDURE Update_Row(
1046 X_Site_Use_Id IN OUT NOCOPY NUMBER,
1047 X_Last_Update_Date IN OUT NOCOPY DATE,
1048 X_Last_Updated_By NUMBER,
1049 X_Site_Use_Code VARCHAR2,
1050 X_customer_id NUMBER,
1051 X_Address_Id NUMBER,
1052 X_Primary_Flag VARCHAR2,
1053 X_Status VARCHAR2,
1054 X_Location VARCHAR2,
1055 X_Last_Update_Login NUMBER,
1056 X_Contact_Id NUMBER,
1057 X_Bill_To_Site_Use_Id NUMBER,
1058 X_Sic_Code VARCHAR2,
1059 X_Payment_Term_Id NUMBER,
1060 X_Gsa_Indicator VARCHAR2,
1061 X_Ship_Partial VARCHAR2,
1062 X_Ship_Via VARCHAR2,
1063 X_Fob_Point VARCHAR2,
1064 X_Order_Type_Id NUMBER,
1065 X_Price_List_Id NUMBER,
1066 X_Freight_Term VARCHAR2,
1067 X_Warehouse_Id NUMBER,
1068 X_Territory_Id NUMBER,
1069 X_Tax_Code VARCHAR2,
1070 X_Tax_Reference VARCHAR2,
1071 X_Demand_Class_Code VARCHAR2,
1072 x_inventory_location_id NUMBER,
1073 x_inventory_organization_id NUMBER,
1074 X_Attribute_Category VARCHAR2,
1075 X_Attribute1 VARCHAR2,
1076 X_Attribute2 VARCHAR2,
1077 X_Attribute3 VARCHAR2,
1078 X_Attribute4 VARCHAR2,
1079 X_Attribute5 VARCHAR2,
1080 X_Attribute6 VARCHAR2,
1081 X_Attribute7 VARCHAR2,
1082 X_Attribute8 VARCHAR2,
1083 X_Attribute9 VARCHAR2,
1084 X_Attribute10 VARCHAR2,
1085 X_Attribute11 VARCHAR2,
1086 X_Attribute12 VARCHAR2,
1087 X_Attribute13 VARCHAR2,
1088 X_Attribute14 VARCHAR2,
1089 X_Attribute15 VARCHAR2,
1090 X_Attribute16 VARCHAR2,
1091 X_Attribute17 VARCHAR2,
1092 X_Attribute18 VARCHAR2,
1093 X_Attribute19 VARCHAR2,
1094 X_Attribute20 VARCHAR2,
1095 X_Attribute21 VARCHAR2,
1096 X_Attribute22 VARCHAR2,
1097 X_Attribute23 VARCHAR2,
1098 X_Attribute24 VARCHAR2,
1099 X_Attribute25 VARCHAR2,
1100 X_Tax_Classification VARCHAR2,
1101 X_Tax_Header_Level_Flag VARCHAR2,
1102 X_Tax_Rounding_Rule VARCHAR2,
1103 X_Global_Attribute_Category VARCHAR2,
1104 X_Global_Attribute1 VARCHAR2,
1105 X_Global_Attribute2 VARCHAR2,
1106 X_Global_Attribute3 VARCHAR2,
1107 X_Global_Attribute4 VARCHAR2,
1108 X_Global_Attribute5 VARCHAR2,
1109 X_Global_Attribute6 VARCHAR2,
1110 X_Global_Attribute7 VARCHAR2,
1111 X_Global_Attribute8 VARCHAR2,
1112 X_Global_Attribute9 VARCHAR2,
1113 X_Global_Attribute10 VARCHAR2,
1114 X_Global_Attribute11 VARCHAR2,
1115 X_Global_Attribute12 VARCHAR2,
1116 X_Global_Attribute13 VARCHAR2,
1117 X_Global_Attribute14 VARCHAR2,
1118 X_Global_Attribute15 VARCHAR2,
1119 X_Global_Attribute16 VARCHAR2,
1120 X_Global_Attribute17 VARCHAR2,
1121 X_Global_Attribute18 VARCHAR2,
1122 X_Global_Attribute19 VARCHAR2,
1123 X_Global_Attribute20 VARCHAR2,
1124 X_Primary_Salesrep_Id NUMBER DEFAULT NULL,
1125 X_Finchrg_Receivables_Trx_Id NUMBER DEFAULT NULL,
1126 X_GL_ID_Rec NUMBER,
1127 X_GL_ID_Rev NUMBER,
1128 X_GL_ID_Tax NUMBER,
1129 X_GL_ID_Freight NUMBER,
1130 X_GL_ID_Clearing NUMBER,
1131 X_GL_ID_Unbilled NUMBER,
1132 X_GL_ID_Unearned NUMBER,
1133 X_GL_ID_Unpaid_rec NUMBER,
1134 X_GL_ID_Remittance NUMBER,
1135 X_GL_ID_Factor NUMBER,
1136 X_DATES_NEGATIVE_TOLERANCE NUMBER,
1137 X_DATES_POSITIVE_TOLERANCE NUMBER,
1138 X_DATE_TYPE_PREFERENCE VARCHAR2,
1139 X_OVER_SHIPMENT_TOLERANCE NUMBER,
1140 X_UNDER_SHIPMENT_TOLERANCE NUMBER,
1141 X_ITEM_CROSS_REF_PREF VARCHAR2,
1142 X_OVER_RETURN_TOLERANCE NUMBER,
1143 X_UNDER_RETURN_TOLERANCE NUMBER,
1144 X_SHIP_SETS_INCLUDE_LINES_FLAG VARCHAR2,
1145 X_ARRIVALSETS_INCL_LINES_FLAG VARCHAR2,
1146 X_SCHED_DATE_PUSH_FLAG VARCHAR2,
1147 X_INVOICE_QUANTITY_RULE VARCHAR2,
1148 x_msg_count OUT NOCOPY number,
1149 x_msg_data OUT NOCOPY VARCHAR2,
1150 x_return_status OUT NOCOPY VARCHAR2,
1151 x_object_version IN NUMBER DEFAULT -1
1152
1153 ) IS
1154 --
1155
1156 l_inventory_location_id number;
1157 suse_rec hz_cust_account_site_v2pub.cust_site_use_rec_type;
1158 profile_rec hz_customer_profile_v2pub.customer_profile_rec_type;
1159 x_date date;
1160 tmp_var VARCHAR2(2000);
1161 i number;
1162 tmp_var1 VARCHAR2(2000);
1163 ExpAcctSiteUse EXCEPTION;
1164
1165 CURSOR cu_version IS
1166 SELECT ROWID,
1167 OBJECT_VERSION_NUMBER,
1168 LAST_UPDATE_DATE
1169 FROM hz_cust_site_uses
1170 WHERE site_use_id = X_Site_Use_Id;
1171
1172 l_site_object_version_number NUMBER;
1173 l_site_use_rowid ROWID;
1174 l_site_use_last_update_date DATE;
1175 --
1176 BEGIN
1177
1178 x_return_status := FND_API.G_RET_STS_SUCCESS;
1179 --
1180 -- For Backward compatibility when Object Version Number is not entered
1181 --
1182 IF x_object_version = -1 THEN
1183 OPEN cu_version;
1184 FETCH cu_version INTO
1185 l_site_use_rowid ,
1186 l_site_object_version_number,
1187 l_site_use_last_update_date ;
1188 IF cu_version%NOTFOUND THEN
1189 FND_MESSAGE.SET_NAME('AR','HZ_API_NO_RECORD');
1190 FND_MESSAGE.SET_TOKEN('RECORD','hz_cust_site_uses');
1191 FND_MESSAGE.SET_TOKEN('ID',x_site_use_id);
1192 FND_MSG_PUB.ADD;
1193 x_return_status := FND_API.G_RET_STS_ERROR;
1194 END IF;
1195 CLOSE cu_version;
1196
1197 IF TO_CHAR(X_Last_Update_Date,'DD-MON-YYYY HH:MI:SS') <>
1198 TO_CHAR(l_site_use_last_update_date,'DD-MON-YYYY HH:MI:SS')
1199 THEN
1200 FND_MESSAGE.SET_NAME('AR', 'HZ_API_RECORD_CHANGED');
1201 FND_MESSAGE.SET_TOKEN('TABLE', 'hz_cust_site_uses');
1202 FND_MSG_PUB.ADD;
1203 x_return_status := FND_API.G_RET_STS_ERROR;
1204 END IF;
1205 ELSE
1206 l_site_object_version_number := x_object_version;
1207 END IF;
1208 --
1209 --
1210 --
1211 IF x_msg_count > 1 THEN
1212 FOR i IN 1..x_msg_count LOOP
1213 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
1214 tmp_var1 := tmp_var1 || ' '|| tmp_var;
1215 END LOOP;
1216 x_msg_data := tmp_var1;
1217 END IF;
1218
1219 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1220 return;
1221 END IF;
1222 --
1223 --
1224 -- In update case for V2 record type data need to switch from NULL to G_MISS
1225 --
1226 --
1227 --
1228 suse_rec.site_use_id := x_site_use_id;
1229 suse_rec.cust_acct_site_id := X_Address_Id;
1230 suse_rec.site_use_code := INIT_SWITCH(X_Site_Use_Code);
1231 suse_rec.primary_flag := INIT_SWITCH(X_Primary_Flag);
1232 suse_rec.location := INIT_SWITCH(X_Location);
1233 suse_rec.contact_id := INIT_SWITCH(X_Contact_id);
1234 suse_rec.Bill_To_Site_Use_Id := INIT_SWITCH(X_Bill_To_Site_Use_Id);
1235 suse_rec.sic_code := INIT_SWITCH(X_Sic_Code);
1236 suse_rec.payment_term_id := INIT_SWITCH(X_Payment_Term_Id);
1237 suse_rec.Gsa_Indicator := INIT_SWITCH(X_Gsa_Indicator);
1238 suse_rec.ship_partial := INIT_SWITCH(X_Ship_Partial);
1239 suse_rec.ship_via := INIT_SWITCH(X_Ship_Via);
1240 suse_rec.fob_point := INIT_SWITCH(X_Fob_Point);
1241 suse_rec.order_type_id := INIT_SWITCH(X_Order_Type_Id);
1242 suse_rec.price_list_id := INIT_SWITCH(X_Price_List_Id);
1243 suse_rec.freight_term := INIT_SWITCH(X_Freight_Term);
1244 suse_rec.warehouse_id := INIT_SWITCH(X_Warehouse_Id);
1245 suse_rec.territory_id := INIT_SWITCH(X_Territory_Id);
1246 suse_rec.Tax_Reference := INIT_SWITCH(X_Tax_Reference);
1247 suse_rec.status := INIT_SWITCH(x_status);
1248 suse_rec.tax_code := INIT_SWITCH(X_Tax_Code);
1249 suse_rec.Demand_Class_Code := INIT_SWITCH(X_Demand_Class_Code);
1250 suse_rec.Tax_Header_Level_Flag := INIT_SWITCH(X_Tax_Header_Level_Flag);
1251 suse_rec.Tax_Rounding_Rule := INIT_SWITCH(X_Tax_Rounding_Rule);
1252 suse_rec.Primary_Salesrep_Id := INIT_SWITCH(X_Primary_Salesrep_Id);
1253 suse_rec.Finchrg_Receivables_Trx_Id := INIT_SWITCH(X_Finchrg_Receivables_Trx_Id);
1254 suse_rec.DATES_NEGATIVE_TOLERANCE := INIT_SWITCH(X_DATES_NEGATIVE_TOLERANCE);
1255 suse_rec.DATES_POSITIVE_TOLERANCE := INIT_SWITCH(X_DATES_POSITIVE_TOLERANCE);
1256 suse_rec.DATE_TYPE_PREFERENCE := INIT_SWITCH(X_DATE_TYPE_PREFERENCE);
1257 suse_rec.OVER_SHIPMENT_TOLERANCE := INIT_SWITCH(X_OVER_SHIPMENT_TOLERANCE);
1258 suse_rec.UNDER_SHIPMENT_TOLERANCE := INIT_SWITCH(X_UNDER_SHIPMENT_TOLERANCE);
1259 suse_rec.ITEM_CROSS_REF_PREF := INIT_SWITCH(X_ITEM_CROSS_REF_PREF);
1260 suse_rec.SHIP_SETS_INCLUDE_LINES_FLAG := INIT_SWITCH(X_SHIP_SETS_INCLUDE_LINES_FLAG);
1261 suse_rec.ARRIVALSETS_INCLUDE_LINES_FLAG := INIT_SWITCH(X_ARRIVALSETS_INCL_LINES_FLAG);
1262 suse_rec.SCHED_DATE_PUSH_FLAG := INIT_SWITCH(X_SCHED_DATE_PUSH_FLAG);
1263 suse_rec.INVOICE_QUANTITY_RULE := INIT_SWITCH(X_INVOICE_QUANTITY_RULE);
1264 suse_rec.OVER_RETURN_TOLERANCE := INIT_SWITCH(X_OVER_RETURN_TOLERANCE);
1265 suse_rec.UNDER_RETURN_TOLERANCE := INIT_SWITCH(X_UNDER_RETURN_TOLERANCE);
1266 suse_rec.GL_ID_Rec := INIT_SWITCH(X_GL_ID_Rec);
1267 suse_rec.GL_ID_Rev := INIT_SWITCH(X_GL_ID_Rev);
1268 suse_rec.GL_ID_Tax := INIT_SWITCH(X_GL_ID_Tax);
1269 suse_rec.GL_ID_Freight := INIT_SWITCH(X_GL_ID_Freight);
1270 suse_rec.GL_ID_Clearing := INIT_SWITCH(X_GL_ID_Clearing);
1271 suse_rec.GL_ID_Unbilled := INIT_SWITCH(X_GL_ID_Unbilled);
1272 suse_rec.GL_ID_Unearned := INIT_SWITCH(X_GL_ID_Unearned);
1273 suse_rec.GL_ID_unpaid_rec := INIT_SWITCH(X_GL_ID_Unpaid_rec);
1274 suse_rec.GL_ID_remittance := INIT_SWITCH(X_GL_ID_remittance);
1275 suse_rec.GL_ID_factor := INIT_SWITCH(X_GL_ID_factor);
1276 suse_rec.attribute_category := INIT_SWITCH(x_attribute_category);
1277 suse_rec.attribute1 := INIT_SWITCH(X_Attribute1);
1278 suse_rec.attribute2 := INIT_SWITCH(X_Attribute2);
1279 suse_rec.attribute3 := INIT_SWITCH(X_Attribute3);
1280 suse_rec.attribute4 := INIT_SWITCH(X_Attribute4);
1281 suse_rec.attribute5 := INIT_SWITCH(X_Attribute5);
1282 suse_rec.attribute6 := INIT_SWITCH(X_Attribute6);
1283 suse_rec.attribute7 := INIT_SWITCH(X_Attribute7);
1284 suse_rec.attribute8 := INIT_SWITCH(X_Attribute8);
1285 suse_rec.attribute9 := INIT_SWITCH(X_Attribute9);
1286 suse_rec.attribute10 := INIT_SWITCH(X_Attribute10);
1287 suse_rec.attribute11 := INIT_SWITCH(X_Attribute11);
1288 suse_rec.attribute12 := INIT_SWITCH(X_Attribute12);
1289 suse_rec.attribute13 := INIT_SWITCH(X_Attribute13);
1290 suse_rec.attribute14 := INIT_SWITCH(X_Attribute14);
1291 suse_rec.attribute15 := INIT_SWITCH(X_Attribute15);
1292 suse_rec.attribute16 := INIT_SWITCH(X_Attribute16);
1293 suse_rec.attribute17 := INIT_SWITCH(X_Attribute17);
1294 suse_rec.attribute18 := INIT_SWITCH(X_Attribute18);
1295 suse_rec.attribute19 := INIT_SWITCH(X_Attribute19);
1296 suse_rec.attribute20 := INIT_SWITCH(X_Attribute20);
1297 suse_rec.attribute21 := INIT_SWITCH(X_Attribute21);
1298 suse_rec.attribute22 := INIT_SWITCH(X_Attribute22);
1299 suse_rec.attribute23 := INIT_SWITCH(X_Attribute23);
1300 suse_rec.attribute24 := INIT_SWITCH(X_Attribute24);
1301 suse_rec.attribute25 := INIT_SWITCH(X_Attribute25);
1302 suse_rec.Global_Attribute_Category := INIT_SWITCH(X_Global_Attribute_Category);
1303 suse_rec.Global_Attribute1 := INIT_SWITCH(X_Global_Attribute1);
1304 suse_rec.Global_Attribute2 := INIT_SWITCH(X_Global_Attribute2);
1305 suse_rec.Global_Attribute3 := INIT_SWITCH(X_Global_Attribute3);
1306 suse_rec.Global_Attribute4 := INIT_SWITCH(X_Global_Attribute4);
1307 suse_rec.Global_Attribute5 := INIT_SWITCH(X_Global_Attribute5);
1308 suse_rec.Global_Attribute6 := INIT_SWITCH(X_Global_Attribute6);
1309 suse_rec.Global_Attribute7 := INIT_SWITCH(X_Global_Attribute7);
1310 suse_rec.Global_Attribute8 := INIT_SWITCH(X_Global_Attribute8);
1311 suse_rec.Global_Attribute9 := INIT_SWITCH(X_Global_Attribute9);
1312 suse_rec.Global_Attribute10 := INIT_SWITCH(X_Global_Attribute10);
1313 suse_rec.Global_Attribute11 := INIT_SWITCH(X_Global_Attribute11);
1314 suse_rec.Global_Attribute12 := INIT_SWITCH(X_Global_Attribute12);
1315 suse_rec.Global_Attribute13 := INIT_SWITCH(X_Global_Attribute13);
1316 suse_rec.Global_Attribute14 := INIT_SWITCH(X_Global_Attribute14);
1317 suse_rec.Global_Attribute15 := INIT_SWITCH(X_Global_Attribute15);
1318 suse_rec.Global_Attribute16 := INIT_SWITCH(X_Global_Attribute16);
1319 suse_rec.Global_Attribute17 := INIT_SWITCH(X_Global_Attribute17);
1320 suse_rec.Global_Attribute18 := INIT_SWITCH(X_Global_Attribute18);
1321 suse_rec.Global_Attribute19 := INIT_SWITCH(X_Global_Attribute19);
1322 suse_rec.Global_Attribute20 := INIT_SWITCH(X_Global_Attribute20);
1323 suse_rec.tax_classification := INIT_SWITCH(x_tax_classification);
1324
1325 if ( x_status = 'A' ) then
1326 --Calling overloaded check_unique_site_use_code, for graceful handling of error situation
1327 check_unique_site_use_code( p_site_use_id => x_site_use_id,
1328 p_customer_id => x_customer_id,
1329 p_address_id => x_address_id,
1330 p_site_use_code => x_site_use_code,
1331 x_return_status => x_return_status,
1332 x_msg_count => x_msg_count,
1333 x_msg_data => x_msg_data
1334 );
1335 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
1336 return;
1337 end if;
1338 end if;
1339 --
1340 --Calling oveloaded check_uniqute_location for graceful handling of error situation
1341 check_unique_location ( p_site_use_id => x_site_use_id,
1342 p_customer_id => x_customer_id,
1343 p_site_use_code => x_site_use_code,
1344 p_location => x_location,
1345 x_return_status => x_return_status,
1346 x_msg_count => x_msg_count,
1347 x_msg_data => x_msg_data
1348 );
1349 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
1350 return;
1351 end if;
1352 --
1353 --
1354 -- Bug#3170887: Commented out no longer necessary logic handled by V2 api
1355 --{
1356 --if ( x_primary_flag = 'Y' and x_status = 'A' ) then
1357 --check_primary( p_site_use_id => x_site_use_id,
1358 -- p_customer_id => x_customer_id,
1359 -- p_site_use_code => x_site_use_code );
1360 --end if;
1361 --}
1362 --
1363 -- If the current SHIP_TO site is associated with a different inventory location
1364 -- delete the row in po_location_associations and insert a new row.
1365 --
1366 if ( x_site_use_code = 'SHIP_TO' ) then
1367 --
1368 arp_clas_pkg.update_po_loc_associations(
1369 p_site_use_id => x_site_use_id,
1370 p_address_id => x_address_id,
1371 p_customer_id => x_customer_id,
1372 p_inventory_organization_id => x_inventory_organization_id,
1373 p_inventory_location_id => x_inventory_location_id,
1374 x_return_status => x_return_status,
1375 x_msg_count => x_msg_count,
1376 x_msg_data => x_msg_data
1377 );
1378 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1379 return;
1380 END IF;
1381 --
1382 end if;
1383 --
1384 --
1385 --
1386 -- call V2 API
1387 HZ_CUST_ACCOUNT_SITE_V2PUB.update_cust_site_use (
1388 p_cust_site_use_rec => suse_rec,
1389 p_object_version_number => l_site_object_version_number,
1390 x_return_status => x_return_status,
1391 x_msg_count => x_msg_count,
1392 x_msg_data => x_msg_data
1393 );
1394
1395 IF x_msg_count > 1 THEN
1396 FOR i IN 1..x_msg_count LOOP
1397 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
1398 tmp_var1 := tmp_var1 || ' '|| tmp_var;
1399 END LOOP;
1400 x_msg_data := tmp_var1;
1401 END IF;
1402
1403 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1404 return;
1405 END IF;
1406 --
1407 -- Backward compatibility
1408 --
1409 SELECT last_update_date
1410 into x_last_update_date
1411 from hz_cust_site_uses
1412 where site_use_id = x_site_use_id;
1413 --
1414 --
1415 --
1416 /* bug3297313 : The call to procedure maintain_denormalized_data is commented out
1417 as this will be taken care in TCA API
1418 */
1419 /*
1420 maintain_denormalized_data ( p_customer_id => x_customer_id,
1421 p_address_id => x_address_id,
1422 p_site_use_id => x_site_use_id,
1423 p_site_use_code => x_site_use_code,
1424 p_status => x_status,
1425 p_primary_flag => x_primary_flag );
1426 */
1427 --
1428 END Update_Row;
1429 --
1430 --
1431 END arh_csu_pkg;