[Home] [Help]
PACKAGE BODY: APPS.AP_VENDOR_SITES_PKG
Source
1 PACKAGE BODY AP_VENDOR_SITES_PKG as
2 /* $Header: apvndsib.pls 120.19.12010000.6 2008/12/29 07:57:55 mayyalas ship $ */
3 --
4 --
5 function format_address( country_code varchar2,
6 seg1 varchar2,
7 seg2 varchar2,
8 seg3 varchar2,
9 seg4 varchar2,
10 seg5 varchar2,
11 seg6 varchar2,
12 seg7 varchar2,
13 seg8 varchar2,
14 seg9 varchar2,
15 seg10 varchar2 ) return varchar2 is
16
17 address varchar2(1000);
18 begin
19 if (seg1 is not NULL ) then
20 address := address||seg1;
21 end if;
22
23 if (seg2 is not NULL ) then
24 address := address||', '||seg2;
25 end if;
26
27 if (seg3 is not NULL ) then
28 address :=address||', '||seg3;
29 end if;
30
31 if (seg4 is not NULL ) then
32 address :=address||', '||seg4;
33 end if;
34
35 if (seg5 is not NULL ) then
36 address :=address||', '||seg5;
37 end if;
38
39 if (seg6 is not NULL ) then
40 address :=address||', '||seg6;
41 end if;
42
43 if (seg7 is not NULL ) then
44 address :=address||', '||seg7;
45 end if;
46
47 if (seg8 is not NULL ) then
48 address :=address||', '||seg8;
49 end if;
50
51 if (seg9 is not NULL ) then
52 address :=address||', '||seg9;
53 end if;
54
55 if (seg10 is not NULL ) then
56 address :=address||', '||seg10;
57 end if;
58
59 return(address);
60
61 end format_address;
62
63
64 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
65 X_Vendor_Site_Id IN OUT NOCOPY NUMBER,
66 X_Last_Update_Date DATE,
67 X_Last_Updated_By NUMBER,
68 X_Vendor_Id NUMBER,
69 X_Vendor_Site_Code IN OUT NOCOPY VARCHAR2,
70 X_Last_Update_Login NUMBER,
71 X_Creation_Date DATE,
72 X_Created_By NUMBER,
73 X_Purchasing_Site_Flag VARCHAR2,
74 X_Rfq_Only_Site_Flag VARCHAR2,
75 X_Pay_Site_Flag VARCHAR2,
76 X_Attention_Ar_Flag VARCHAR2,
77 X_Address_Line1 VARCHAR2,
78 X_Address_Line2 VARCHAR2,
79 X_Address_Line3 VARCHAR2,
80 X_City VARCHAR2,
81 X_State VARCHAR2,
82 X_Zip VARCHAR2,
83 X_Province VARCHAR2,
84 X_Country VARCHAR2,
85 X_Area_Code VARCHAR2,
86 X_Phone VARCHAR2,
87 X_Customer_Num VARCHAR2,
88 X_Ship_To_Location_Id NUMBER,
89 X_Bill_To_Location_Id NUMBER,
90 X_Ship_Via_Lookup_Code VARCHAR2,
91 X_Freight_Terms_Lookup_Code VARCHAR2,
92 X_Fob_Lookup_Code VARCHAR2,
93 X_Inactive_Date DATE,
94 X_Fax VARCHAR2,
95 X_Fax_Area_Code VARCHAR2,
96 X_Telex VARCHAR2,
97 --4552701 X_Payment_Method_Lookup_Code VARCHAR2,
98 X_Bank_Account_Name VARCHAR2,
99 X_Bank_Account_Num VARCHAR2,
100 X_Bank_Num VARCHAR2,
101 X_Bank_Account_Type VARCHAR2,
102 X_Terms_Date_Basis VARCHAR2,
103 X_Current_Catalog_Num VARCHAR2,
104 -- eTax Uptake X_Vat_Code VARCHAR2,
105 X_Distribution_Set_Id NUMBER,
106 X_Accts_Pay_CCID NUMBER,
107 X_Future_Dated_Payment_CCID NUMBER,
108 X_Prepay_Code_Combination_Id NUMBER,
109 X_Pay_Group_Lookup_Code VARCHAR2,
110 X_Payment_Priority NUMBER,
111 X_Terms_Id NUMBER,
112 X_Invoice_Amount_Limit NUMBER,
113 X_Pay_Date_Basis_Lookup_Code VARCHAR2,
114 X_Always_Take_Disc_Flag VARCHAR2,
115 X_Invoice_Currency_Code VARCHAR2,
116 X_Payment_Currency_Code VARCHAR2,
117 X_Hold_All_Payments_Flag VARCHAR2,
118 X_Hold_Future_Payments_Flag VARCHAR2,
119 X_Hold_Reason VARCHAR2,
120 X_Hold_Unmatched_Invoices_Flag VARCHAR2,
121 X_Match_Option VARCHAR2,
122 X_Create_Debit_Memo_Flag VARCHAR2,
123 --4552701 X_Exclusive_Payment_Flag VARCHAR2,
124 X_Tax_Reporting_Site_Flag VARCHAR2,
125 X_Attribute_Category VARCHAR2,
126 X_Attribute1 VARCHAR2,
127 X_Attribute2 VARCHAR2,
128 X_Attribute3 VARCHAR2,
129 X_Attribute4 VARCHAR2,
130 X_Attribute5 VARCHAR2,
131 X_Attribute6 VARCHAR2,
132 X_Attribute7 VARCHAR2,
133 X_Attribute8 VARCHAR2,
134 X_Attribute9 VARCHAR2,
135 X_Attribute10 VARCHAR2,
136 X_Attribute11 VARCHAR2,
137 X_Attribute12 VARCHAR2,
138 X_Attribute13 VARCHAR2,
139 X_Attribute14 VARCHAR2,
140 X_Attribute15 VARCHAR2,
141 X_Validation_Number NUMBER,
142 X_Exclude_Freight_From_Disc VARCHAR2,
143 X_Vat_Registration_Num VARCHAR2,
144 -- eTax Uptake X_Offset_Tax_Flag VARCHAR2,
145 X_Check_Digits VARCHAR2,
146 X_Bank_Number VARCHAR2,
147 X_Address_Line4 VARCHAR2,
148 X_County VARCHAR2,
149 X_Address_Style VARCHAR2,
150 X_Language VARCHAR2,
151 X_Allow_Awt_Flag VARCHAR2,
152 X_Awt_Group_Id NUMBER,
153 X_Pay_Awt_Group_Id NUMBER,--bug6664407
154 X_pay_on_code VARCHAR2,
155 X_default_pay_site_id NUMBER,
156 X_pay_on_receipt_summary_code VARCHAR2,
157 X_Bank_Branch_Type VARCHAR2,
158 X_EDI_ID_Number VARCHAR2, --Bug 7437549
159 /* 4552701
160 X_EDI_ID_Number VARCHAR2,
161 X_EDI_Payment_Method VARCHAR2,
162 X_EDI_Payment_Format VARCHAR2,
163 X_EDI_Remittance_Method VARCHAR2,
164 X_EDI_Remittance_Instruction VARCHAR2,
165 X_EDI_transaction_handling VARCHAR2,
166 eTax Uptake
167 X_Auto_Tax_Calc_Flag VARCHAR2,
168 X_Auto_Tax_Calc_Override VARCHAR2,
169 X_Amount_Includes_Tax_Flag VARCHAR2,
170 X_AP_Tax_Rounding_Rule VARCHAR2, */
171 X_Vendor_Site_Code_Alt VARCHAR2,
172 X_Address_Lines_Alt VARCHAR2,
173 X_global_attribute_category VARCHAR2 DEFAULT NULL,
174 X_global_attribute1 VARCHAR2 DEFAULT NULL,
175 X_global_attribute2 VARCHAR2 DEFAULT NULL,
176 X_global_attribute3 VARCHAR2 DEFAULT NULL,
177 X_global_attribute4 VARCHAR2 DEFAULT NULL,
178 X_global_attribute5 VARCHAR2 DEFAULT NULL,
179 X_global_attribute6 VARCHAR2 DEFAULT NULL,
180 X_global_attribute7 VARCHAR2 DEFAULT NULL,
181 X_global_attribute8 VARCHAR2 DEFAULT NULL,
182 X_global_attribute9 VARCHAR2 DEFAULT NULL,
183 X_global_attribute10 VARCHAR2 DEFAULT NULL,
184 X_global_attribute11 VARCHAR2 DEFAULT NULL,
185 X_global_attribute12 VARCHAR2 DEFAULT NULL,
186 X_global_attribute13 VARCHAR2 DEFAULT NULL,
187 X_global_attribute14 VARCHAR2 DEFAULT NULL,
188 X_global_attribute15 VARCHAR2 DEFAULT NULL,
189 X_global_attribute16 VARCHAR2 DEFAULT NULL,
190 X_global_attribute17 VARCHAR2 DEFAULT NULL,
191 X_global_attribute18 VARCHAR2 DEFAULT NULL,
192 X_global_attribute19 VARCHAR2 DEFAULT NULL,
193 X_global_attribute20 VARCHAR2 DEFAULT NULL,
194 X_Bank_Charge_Bearer VARCHAR2 DEFAULT NULL,
195 X_Ece_Tp_Location_Code VARCHAR2 DEFAULT NULL,
196 X_Pcard_Site_Flag VARCHAR2,
197 X_Country_of_Origin_Code VARCHAR2,
198 X_calling_sequence IN VARCHAR2,
199 X_Shipping_Location_id IN NUMBER,
200 X_Supplier_Notif_Method VARCHAR2, -- New Column
201 X_Email_Address VARCHAR2, -- New Column
202 --4552701 X_Remittance_email VARCHAR2 DEFAULT NULL,
203 X_Primary_pay_site_flag VARCHAR2 DEFAULT NULL,
204 --MO Access Control
205 X_Org_ID NUMBER DEFAULT mo_global.get_current_org_id
206 ) IS
207
208 CURSOR C IS SELECT rowid
209 FROM po_vendor_sites
210 WHERE vendor_site_id = X_vendor_site_id;
211 current_calling_sequence varchar2(2000);
212 debug_info varchar2(100);
213
214
215 BEGIN
216 -- Update the calling sequence
217 --
218 current_calling_sequence := 'AP_VENDOR_SITES_PKG.INSERT_ROW<-' ||
219 X_calling_sequence;
220
221 Check_duplicate_vendor_site (x_vendor_id, x_vendor_site_code,
222 x_org_id, --MO Access Control
223 x_rowid,
224 X_calling_sequence => current_calling_sequence);
225
226 if (x_tax_reporting_site_flag = 'Y') then
227 check_multiple_tax_sites (x_vendor_id, x_vendor_site_id,
228 x_org_id, --MO Access Control
229 X_calling_sequence => current_calling_sequence);
230 end if;
231
232
233 -- Global Supplier Sites: We need to check if there exists a site for
234 -- this vendor (same vendor_id) with the exact same vendor_site_code in
235 -- any other org (it will be in other orgs only because we do not allow
236 -- duplicate vendor_site_codes in the same org) . If yes, then we
237 -- use the same vendor_site_id while creating this new site. If no,
238 -- then we hit the sequence to get a new vendor_site_id. If there are
239 -- multiple sites for this vendor with the same site_code, we take the
240 -- max() vendor_site_id.
241 /*Following piece of code commented out NOCOPY to back out NOCOPY the changes
242 made earlier (Bug 702458)
243 debug_info := 'Select vendor site id for any other site with same site_code';
244 Select max(vendor_site_id)
245 into x_vendor_site_id
246 from po_vendor_sites_all
247 where vendor_id = x_vendor_id
248 and vendor_site_code = x_vendor_site_code; */
249
250 --(Bug 702458) if (x_vendor_site_id IS NULL) then
251 debug_info := 'Select next vendor_site_id from PO_VENDOR_SITES_S sequence';
252 Select PO_VENDOR_SITES_S.NEXTVAL
253 into x_vendor_site_id
254 from sys.dual;
255 -- end if;
256
257 debug_info := 'Insert values into PO_VENDOR_SITES';
258 INSERT INTO ap_supplier_sites_all(
259 vendor_site_id,
260 last_update_date,
261 last_updated_by,
262 vendor_id,
263 vendor_site_code,
264 last_update_login,
265 creation_date,
266 created_by,
267 purchasing_site_flag,
268 rfq_only_site_flag,
269 pay_site_flag,
270 attention_ar_flag,
271 address_line1,
272 address_line2,
273 address_line3,
274 city,
275 state,
276 zip,
277 province,
278 country,
279 area_code,
280 phone,
281 customer_num,
282 ship_to_location_id,
283 bill_to_location_id,
284 ship_via_lookup_code,
285 freight_terms_lookup_code,
286 fob_lookup_code,
287 inactive_date,
288 fax,
289 fax_area_code,
290 telex,
291 bank_account_name,
292 bank_account_num,
293 bank_num,
294 bank_account_type,
295 terms_date_basis,
296 current_catalog_num,
297 distribution_set_id,
298 accts_pay_code_combination_id,
299 future_dated_payment_ccid,
300 prepay_code_combination_id,
301 pay_group_lookup_code,
302 payment_priority,
303 terms_id,
304 invoice_amount_limit,
305 pay_date_basis_lookup_code,
306 always_take_disc_flag,
307 invoice_currency_code,
308 payment_currency_code,
309 hold_all_payments_flag,
310 hold_future_payments_flag,
311 hold_reason,
312 hold_unmatched_invoices_flag,
313 match_option,
314 create_debit_memo_flag,
315 tax_reporting_site_flag,
316 attribute_category,
317 attribute1,
318 attribute2,
319 attribute3,
320 attribute4,
321 attribute5,
322 attribute6,
323 attribute7,
324 attribute8,
325 attribute9,
326 attribute10,
327 attribute11,
328 attribute12,
329 attribute13,
330 attribute14,
331 attribute15,
332 validation_number,
333 exclude_freight_from_discount,
334 vat_registration_num,
335 check_digits,
336 bank_number,
337 address_line4,
338 county,
339 address_style,
340 language,
341 allow_awt_flag,
342 awt_group_id,
343 pay_awt_group_id,--bug6664407
344 pay_on_code,
345 default_pay_site_id,
346 pay_on_receipt_summary_code,
347 Bank_Branch_Type,
348 vendor_site_code_alt,
349 address_lines_alt,
350 global_attribute_category,
351 global_attribute1,
352 global_attribute2,
353 global_attribute3,
354 global_attribute4,
355 global_attribute5,
356 global_attribute6,
357 global_attribute7,
358 global_attribute8,
359 global_attribute9,
360 global_attribute10,
361 global_attribute11,
362 global_attribute12,
363 global_attribute13,
364 global_attribute14,
365 global_attribute15,
366 global_attribute16,
367 global_attribute17,
368 global_attribute18,
369 global_attribute19,
370 global_attribute20,
371 Bank_charge_bearer,
372 Ece_Tp_Location_Code,
373 Country_of_Origin_Code,
374 Pcard_Site_Flag,
375 Supplier_Notif_Method, -- New Column
376 Email_Address, -- New Column
377 Primary_pay_site_flag ,
378 org_id, /* MO Access Control */
379 edi_id_number -- Bug 7437549
380 )
381 VALUES (
382 X_Vendor_Site_Id,
383 X_Last_Update_Date,
384 X_Last_Updated_By,
385 X_Vendor_Id,
386 X_Vendor_Site_Code,
387 X_Last_Update_Login,
388 X_Creation_Date,
389 X_Created_By,
390 X_Purchasing_Site_Flag,
391 X_Rfq_Only_Site_Flag,
392 X_Pay_Site_Flag,
393 X_Attention_Ar_Flag,
394 X_Address_Line1,
395 X_Address_Line2,
396 X_Address_Line3,
397 X_City,
398 X_State,
399 X_Zip,
400 X_Province,
401 X_Country,
402 X_Area_Code,
403 X_Phone,
404 X_Customer_Num,
405 X_Ship_To_Location_Id,
406 X_Bill_To_Location_Id,
407 X_Ship_Via_Lookup_Code,
408 X_Freight_Terms_Lookup_Code,
409 X_Fob_Lookup_Code,
410 X_Inactive_Date,
411 X_Fax,
412 X_Fax_Area_Code,
413 X_Telex,
414 X_Bank_Account_Name,
415 X_Bank_Account_Num,
416 X_Bank_Num,
417 X_Bank_Account_Type,
418 X_Terms_Date_Basis,
419 X_Current_Catalog_Num,
420 X_Distribution_Set_Id,
421 X_Accts_Pay_CCID,
422 X_Future_Dated_Payment_CCID,
423 X_Prepay_Code_Combination_Id,
424 X_Pay_Group_Lookup_Code,
425 X_Payment_Priority,
426 X_Terms_Id,
427 X_Invoice_Amount_Limit,
428 X_Pay_Date_Basis_Lookup_Code,
429 X_Always_Take_Disc_Flag,
430 X_Invoice_Currency_Code,
431 X_Payment_Currency_Code,
432 X_Hold_All_Payments_Flag,
433 X_Hold_Future_Payments_Flag,
434 X_Hold_Reason,
435 X_Hold_Unmatched_Invoices_Flag,
436 X_Match_Option,
437 X_Create_Debit_Memo_Flag,
438 X_Tax_Reporting_Site_Flag,
439 X_Attribute_Category,
440 X_Attribute1,
441 X_Attribute2,
442 X_Attribute3,
443 X_Attribute4,
444 X_Attribute5,
445 X_Attribute6,
446 X_Attribute7,
447 X_Attribute8,
448 X_Attribute9,
449 X_Attribute10,
450 X_Attribute11,
451 X_Attribute12,
452 X_Attribute13,
453 X_Attribute14,
454 X_Attribute15,
455 X_Validation_Number,
459 X_Bank_Number,
456 X_Exclude_Freight_From_Disc,
457 X_Vat_Registration_Num,
458 X_Check_Digits,
460 X_Address_Line4,
461 X_County,
462 X_Address_Style,
463 X_Language,
464 X_Allow_Awt_Flag,
465 X_Awt_Group_Id,
466 X_Pay_Awt_Group_Id,--bug6664407
467 X_pay_on_code,
468 X_default_pay_site_id,
469 X_pay_on_receipt_summary_code,
470 X_Bank_Branch_Type,
471 X_Vendor_Site_Code_Alt,
472 X_Address_Lines_Alt,
473 X_global_attribute_category,
474 X_global_attribute1,
475 X_global_attribute2,
476 X_global_attribute3,
477 X_global_attribute4,
478 X_global_attribute5,
479 X_global_attribute6,
480 X_global_attribute7,
481 X_global_attribute8,
482 X_global_attribute9,
483 X_global_attribute10,
484 X_global_attribute11,
485 X_global_attribute12,
486 X_global_attribute13,
487 X_global_attribute14,
488 X_global_attribute15,
489 X_global_attribute16,
490 X_global_attribute17,
491 X_global_attribute18,
492 X_global_attribute19,
493 X_global_attribute20,
494 X_Bank_Charge_Bearer,
495 X_Ece_Tp_Location_Code,
496 X_Country_of_Origin_Code,
497 X_Pcard_Site_Flag,
498 X_Supplier_Notif_Method, -- New Column
499 X_Email_Address, -- New Column
500 X_Primary_pay_site_flag ,
501 X_org_id, /* MO Access Control */
502 X_EDI_ID_Number -- Bug 7437549
503 );
504
505 if (X_Shipping_Location_id is not null) then
506
507 debug_info := 'Insert values into PO_LOCATION_ASSOCIATIONS';
508
509 ap_po_locn_association_pkg.insert_row( p_location_id => X_Shipping_Location_id,
510 p_vendor_id => X_Vendor_Id,
511 p_vendor_site_id => X_Vendor_Site_Id,
512 p_last_update_date => X_Last_Update_Date,
513 p_last_updated_by => X_Last_Updated_By,
514 p_last_update_login => X_Last_Update_Login,
515 p_creation_date => X_Creation_Date,
516 p_created_by => X_Created_By,
517 p_org_id => X_Org_ID); --MO Access Control
518 end if;
519
520 debug_info := 'Open cursor C';
521 OPEN C;
522 debug_info := 'Fetch cursor C';
523 FETCH C INTO X_Rowid;
524 if (C%NOTFOUND) then
525 debug_info := 'Close cursor C - DATA NOTFOUND';
526 CLOSE C;
527 Raise NO_DATA_FOUND;
528 end if;
529 debug_info := 'Close cursor C';
530 CLOSE C;
531
532 EXCEPTION
533 WHEN OTHERS THEN
534 IF (SQLCODE <> -20001) THEN
535 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
536 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
537 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
538 FND_MESSAGE.SET_TOKEN('PARAMETERS','VENDOR_SITE_ID = ' ||
539 X_Vendor_Site_Id);
540 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
541 END IF;
542 APP_EXCEPTION.RAISE_EXCEPTION;
543
544 END Insert_Row;
545
546 PROCEDURE Insert_Row(
547 p_vendor_site_rec IN AP_VENDOR_PUB_PKG.r_vendor_site_rec_type,
548 p_last_update_date IN DATE,
549 p_last_updated_by IN NUMBER,
550 p_last_update_login IN NUMBER,
551 p_creation_date IN DATE,
552 p_created_by IN NUMBER,
553 p_request_id IN NUMBER,
554 p_program_application_id IN NUMBER,
555 p_program_id IN NUMBER,
556 p_program_update_date IN DATE,
557 x_rowid OUT NOCOPY VARCHAR2,
558 x_vendor_site_id OUT NOCOPY NUMBER
559 ) IS
560
561 CURSOR C IS SELECT rowid
562 FROM po_vendor_sites
563 WHERE vendor_site_id = X_vendor_site_id;
564
565 current_calling_sequence varchar2(2000);
566 debug_info varchar2(2000);
567
568 BEGIN
569
570 debug_info := 'Select next vendor_site_id from PO_VENDOR_SITES_S sequence';
571 Select PO_VENDOR_SITES_S.NEXTVAL
572 into x_vendor_site_id
573 from sys.dual;
574 -- end if;
575
576 debug_info := 'Insert values into PO_VENDOR_SITES';
577 INSERT INTO ap_supplier_sites_all(
578 vendor_site_id,
579 last_update_date,
580 last_updated_by,
581 vendor_id,
582 vendor_site_code,
583 last_update_login,
584 creation_date,
585 created_by,
586 purchasing_site_flag,
587 rfq_only_site_flag,
588 pay_site_flag,
589 attention_ar_flag,
590 area_code,
591 phone,
592 customer_num,
593 ship_to_location_id,
594 bill_to_location_id,
595 ship_via_lookup_code,
596 freight_terms_lookup_code,
597 fob_lookup_code,
598 inactive_date,
602 terms_date_basis,
599 fax,
600 fax_area_code,
601 telex,
603 distribution_set_id,
604 accts_pay_code_combination_id,
605 future_dated_payment_ccid,
606 prepay_code_combination_id,
607 pay_group_lookup_code,
608 payment_priority,
609 terms_id,
610 invoice_amount_limit,
611 pay_date_basis_lookup_code,
612 always_take_disc_flag,
613 invoice_currency_code,
614 payment_currency_code,
615 hold_all_payments_flag,
616 hold_future_payments_flag,
617 hold_reason,
618 hold_unmatched_invoices_flag,
619 match_option,
620 create_debit_memo_flag,
621 tax_reporting_site_flag,
622 attribute_category,
623 attribute1,
624 attribute2,
625 attribute3,
626 attribute4,
627 attribute5,
628 attribute6,
629 attribute7,
630 attribute8,
631 attribute9,
632 attribute10,
633 attribute11,
634 attribute12,
635 attribute13,
636 attribute14,
637 attribute15,
638 validation_number,
639 exclude_freight_from_discount,
640 check_digits,
641 allow_awt_flag,
642 awt_group_id,
643 pay_awt_group_id,--bug6664407
644 pay_on_code,
645 default_pay_site_id,
646 pay_on_receipt_summary_code,
647 vendor_site_code_alt,
648 global_attribute_category,
649 global_attribute1,
650 global_attribute2,
651 global_attribute3,
652 global_attribute4,
653 global_attribute5,
654 global_attribute6,
655 global_attribute7,
656 global_attribute8,
657 global_attribute9,
658 global_attribute10,
659 global_attribute11,
660 global_attribute12,
661 global_attribute13,
662 global_attribute14,
663 global_attribute15,
664 global_attribute16,
665 global_attribute17,
666 global_attribute18,
667 global_attribute19,
668 global_attribute20,
669 Bank_charge_bearer,
670 Ece_Tp_Location_Code,
671 Country_of_Origin_Code,
672 Pcard_Site_Flag,
673 Supplier_Notif_Method,
674 Email_Address,
675 Primary_pay_site_flag ,
676 org_id ,
677 location_id,
678 party_site_id,
679 tolerance_id,
680 retainage_rate,
681 shipping_control,
682 services_tolerance_id,
683 gapless_inv_num_flag,
684 selling_company_identifier,
685 duns_number, --bug6388041
686 vat_code, -- Bug 6645014
687 -- Bug 7300553 Start
688 address_line1,
689 address_line2,
690 address_line3,
691 address_line4,
692 city,
693 state,
694 zip,
695 province,
696 country,
697 county,
698 address_style,
699 language,
700 address_lines_alt,
701 -- Bug 7300553 End
702 edi_id_number, -- Bug 7437549
703 OFFSET_TAX_FLAG, -- Bug#7506443
704 AUTO_TAX_CALC_FLAG -- Bug#7506443
705
706 )
707 VALUES (
708 x_Vendor_Site_Id,
709 p_Last_Update_Date,
710 p_Last_Updated_By,
711 p_vendor_site_rec.Vendor_Id,
712 p_vendor_site_rec.Vendor_Site_Code,
713 p_Last_Update_Login,
714 p_Creation_Date,
715 p_Created_By,
716 p_vendor_site_rec.Purchasing_Site_Flag,
717 p_vendor_site_rec.Rfq_Only_Site_Flag,
718 p_vendor_site_rec.Pay_Site_Flag,
719 p_vendor_site_rec.Attention_Ar_Flag,
720 p_vendor_site_rec.Area_Code,
721 p_vendor_site_rec.Phone,
722 p_vendor_site_rec.Customer_Num,
723 p_vendor_site_rec.Ship_To_Location_Id,
724 p_vendor_site_rec.Bill_To_Location_Id,
725 p_vendor_site_rec.Ship_Via_Lookup_Code,
726 p_vendor_site_rec.Freight_Terms_Lookup_Code,
727 p_vendor_site_rec.Fob_Lookup_Code,
728 p_vendor_site_rec.Inactive_Date,
729 p_vendor_site_rec.Fax,
730 p_vendor_site_rec.Fax_area_code,
731 p_vendor_site_rec.Telex,
732 p_vendor_site_rec.Terms_Date_Basis,
733 p_vendor_site_rec.Distribution_Set_Id,
734 p_vendor_site_rec.Accts_Pay_Code_Combination_ID,
735 p_vendor_site_rec.Future_Dated_Payment_CCID,
736 p_vendor_site_rec.Prepay_Code_Combination_Id,
737 p_vendor_site_rec.Pay_Group_Lookup_Code,
738 p_vendor_site_rec.Payment_Priority,
742 p_vendor_site_rec.Always_Take_Disc_Flag,
739 p_vendor_site_rec.Terms_Id,
740 p_vendor_site_rec.Invoice_Amount_Limit,
741 p_vendor_site_rec.Pay_Date_Basis_Lookup_Code,
743 p_vendor_site_rec.Invoice_Currency_Code,
744 p_vendor_site_rec.Payment_Currency_Code,
745 p_vendor_site_rec.Hold_All_Payments_Flag,
746 p_vendor_site_rec.Hold_Future_Payments_Flag,
747 p_vendor_site_rec.Hold_Reason,
748 p_vendor_site_rec.Hold_Unmatched_Invoices_Flag,
749 p_vendor_site_rec.Match_Option,
750 p_vendor_site_rec.Create_Debit_Memo_Flag,
751 p_vendor_site_rec.Tax_Reporting_Site_Flag,
752 p_vendor_site_rec.Attribute_Category,
753 p_vendor_site_rec.Attribute1,
754 p_vendor_site_rec.Attribute2,
755 p_vendor_site_rec.Attribute3,
756 p_vendor_site_rec.Attribute4,
757 p_vendor_site_rec.Attribute5,
758 p_vendor_site_rec.Attribute6,
759 p_vendor_site_rec.Attribute7,
760 p_vendor_site_rec.Attribute8,
761 p_vendor_site_rec.Attribute9,
762 p_vendor_site_rec.Attribute10,
763 p_vendor_site_rec.Attribute11,
764 p_vendor_site_rec.Attribute12,
765 p_vendor_site_rec.Attribute13,
766 p_vendor_site_rec.Attribute14,
767 p_vendor_site_rec.Attribute15,
768 p_vendor_site_rec.Validation_Number,
769 p_vendor_site_rec.Exclude_Freight_From_Discount,
770 p_vendor_site_rec.Check_Digits,
771 p_vendor_site_rec.Allow_Awt_Flag,
772 p_vendor_site_rec.Awt_Group_Id,
773 p_vendor_site_rec.Pay_Awt_Group_Id,--bug6664407
774 p_vendor_site_rec.pay_on_code,
775 p_vendor_site_rec.default_pay_site_id,
776 p_vendor_site_rec.pay_on_receipt_summary_code,
777 p_vendor_site_rec.Vendor_Site_Code_Alt,
778 p_vendor_site_rec.global_attribute_category,
779 p_vendor_site_rec.global_attribute1,
780 p_vendor_site_rec.global_attribute2,
781 p_vendor_site_rec.global_attribute3,
782 p_vendor_site_rec.global_attribute4,
783 p_vendor_site_rec.global_attribute5,
784 p_vendor_site_rec.global_attribute6,
785 p_vendor_site_rec.global_attribute7,
786 p_vendor_site_rec.global_attribute8,
787 p_vendor_site_rec.global_attribute9,
788 p_vendor_site_rec.global_attribute10,
789 p_vendor_site_rec.global_attribute11,
790 p_vendor_site_rec.global_attribute12,
791 p_vendor_site_rec.global_attribute13,
792 p_vendor_site_rec.global_attribute14,
793 p_vendor_site_rec.global_attribute15,
794 p_vendor_site_rec.global_attribute16,
795 p_vendor_site_rec.global_attribute17,
796 p_vendor_site_rec.global_attribute18,
797 p_vendor_site_rec.global_attribute19,
798 p_vendor_site_rec.global_attribute20,
799 p_vendor_site_rec.Bank_Charge_Bearer,
800 p_vendor_site_rec.Ece_Tp_Location_Code,
801 p_vendor_site_rec.Country_of_Origin_Code,
802 p_vendor_site_rec.Pcard_Site_Flag,
803 p_vendor_site_rec.Supplier_Notif_Method,
804 p_vendor_site_rec.Email_Address,
805 p_vendor_site_rec.Primary_pay_site_flag ,
806 p_vendor_site_rec.org_id,
807 p_vendor_site_rec.location_id,
808 p_vendor_site_rec.party_site_id,
809 p_vendor_site_rec.tolerance_id,
810 p_vendor_site_rec.retainage_rate,
811 p_vendor_site_rec.shipping_control,
812 p_vendor_site_rec.services_tolerance_id,
813 p_vendor_site_rec.gapless_inv_num_flag,
814 p_vendor_site_rec.selling_company_identifier,
815 p_vendor_site_rec.duns_number, --bug6388041
816 p_vendor_site_rec.vat_code, -- bug 6645014
817 -- Bug 7300553 Start
818 p_vendor_site_rec.address_line1,
819 p_vendor_site_rec.address_line2,
820 p_vendor_site_rec.address_line3,
821 p_vendor_site_rec.address_line4,
822 p_vendor_site_rec.city,
823 p_vendor_site_rec.state,
824 p_vendor_site_rec.zip,
825 p_vendor_site_rec.province,
826 p_vendor_site_rec.country,
827 p_vendor_site_rec.county,
828 p_vendor_site_rec.address_style,
829 p_vendor_site_rec.language,
830 p_vendor_site_rec.address_lines_alt,
831 -- Bug 7300553 End
832 p_vendor_site_rec.edi_id_number, -- Bug 7437549
833 p_vendor_site_rec.offset_tax_flag, -- Bug#7506443
834 p_vendor_site_rec.auto_tax_calc_flag -- Bug#7506443
835
836 );
837
838 if (p_vendor_site_rec.Shipping_Location_id is not null) then
839
840 debug_info := 'Insert values into PO_LOCATION_ASSOCIATIONS';
841
842 ap_po_locn_association_pkg.insert_row(
843 p_location_id => p_vendor_site_rec.Ship_to_Location_id,
844 p_vendor_id => p_vendor_site_rec.Vendor_Id,
845 p_vendor_site_id => x_Vendor_Site_Id,
846 p_last_update_date => p_Last_Update_Date,
847 p_last_updated_by => p_Last_Updated_By,
848 p_last_update_login => p_Last_Update_Login,
852 end if;
849 p_creation_date => p_Creation_Date,
850 p_created_by => p_Created_By,
851 p_org_id => p_vendor_site_rec.Org_ID); --MO Access Control
853
854 debug_info := 'Open cursor C';
855 OPEN C;
856 debug_info := 'Fetch cursor C';
857 FETCH C INTO x_Rowid;
858 if (C%NOTFOUND) then
859 debug_info := 'Close cursor C - DATA NOTFOUND';
860 CLOSE C;
861 Raise NO_DATA_FOUND;
862 end if;
863 debug_info := 'Close cursor C';
864 CLOSE C;
865
866 EXCEPTION
867 WHEN OTHERS THEN
868 IF (SQLCODE <> -20001) THEN
869 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
870 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
871 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
872 FND_MESSAGE.SET_TOKEN('PARAMETERS','VENDOR_SITE_ID = ' ||
873 x_Vendor_Site_Id);
874 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
875 END IF;
876 APP_EXCEPTION.RAISE_EXCEPTION;
877
878 END Insert_Row;
879
880 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
881 X_Vendor_Site_Id NUMBER,
882 X_Vendor_Id NUMBER,
883 X_Vendor_Site_Code VARCHAR2,
884 X_Purchasing_Site_Flag VARCHAR2,
885 X_Rfq_Only_Site_Flag VARCHAR2,
886 X_Pay_Site_Flag VARCHAR2,
887 X_Attention_Ar_Flag VARCHAR2,
888 X_Address_Line1 VARCHAR2,
889 X_Address_Line2 VARCHAR2,
890 X_Address_Line3 VARCHAR2,
891 X_City VARCHAR2,
892 X_State VARCHAR2,
893 X_Zip VARCHAR2,
894 X_Province VARCHAR2,
895 X_Country VARCHAR2,
896 X_Area_Code VARCHAR2,
897 X_Phone VARCHAR2,
898 X_Customer_Num VARCHAR2,
899 X_Ship_To_Location_Id NUMBER,
900 X_Bill_To_Location_Id NUMBER,
901 X_Ship_Via_Lookup_Code VARCHAR2,
902 X_Freight_Terms_Lookup_Code VARCHAR2,
903 X_Fob_Lookup_Code VARCHAR2,
904 X_Inactive_Date DATE,
905 X_Fax VARCHAR2,
906 X_Fax_Area_Code VARCHAR2,
907 X_Telex VARCHAR2,
908 --4552701 X_Payment_Method_Lookup_Code VARCHAR2,
909 X_Bank_Account_Name VARCHAR2,
910 X_Bank_Account_Num VARCHAR2,
911 X_Bank_Num VARCHAR2,
912 X_Bank_Account_Type VARCHAR2,
913 X_Terms_Date_Basis VARCHAR2,
914 X_Current_Catalog_Num VARCHAR2,
915 -- eTax Uptake X_Vat_Code VARCHAR2,
916 X_Distribution_Set_Id NUMBER,
917 X_Accts_Pay_CCID NUMBER,
918 X_Future_Dated_Payment_CCID NUMBER,
919 X_Prepay_Code_Combination_Id NUMBER,
920 X_Pay_Group_Lookup_Code VARCHAR2,
921 X_Payment_Priority NUMBER,
922 X_Terms_Id NUMBER,
923 X_Invoice_Amount_Limit NUMBER,
924 X_Pay_Date_Basis_Lookup_Code VARCHAR2,
925 X_Always_Take_Disc_Flag VARCHAR2,
926 X_Invoice_Currency_Code VARCHAR2,
927 X_Payment_Currency_Code VARCHAR2,
928 X_Hold_All_Payments_Flag VARCHAR2,
929 X_Hold_Future_Payments_Flag VARCHAR2,
930 X_Hold_Reason VARCHAR2,
931 X_Hold_Unmatched_Invoices_Flag VARCHAR2,
932 X_Match_Option VARCHAR2,
933 X_Create_Debit_Memo_Flag VARCHAR2,
934 --4552701 X_Exclusive_Payment_Flag VARCHAR2,
935 X_Tax_Reporting_Site_Flag VARCHAR2,
936 X_Attribute_Category VARCHAR2,
937 X_Attribute1 VARCHAR2,
938 X_Attribute2 VARCHAR2,
939 X_Attribute3 VARCHAR2,
940 X_Attribute4 VARCHAR2,
941 X_Attribute5 VARCHAR2,
942 X_Attribute6 VARCHAR2,
943 X_Attribute7 VARCHAR2,
944 X_Attribute8 VARCHAR2,
945 X_Attribute9 VARCHAR2,
946 X_Attribute10 VARCHAR2,
947 X_Attribute11 VARCHAR2,
951 X_Attribute15 VARCHAR2,
948 X_Attribute12 VARCHAR2,
949 X_Attribute13 VARCHAR2,
950 X_Attribute14 VARCHAR2,
952 X_Validation_Number NUMBER,
953 X_Exclude_Freight_From_Disc VARCHAR2,
954 X_Vat_Registration_Num VARCHAR2,
955 -- eTax Uptake X_Offset_Tax_Flag VARCHAR2,
956 X_Check_Digits VARCHAR2,
957 X_Bank_Number VARCHAR2,
958 X_Address_Line4 VARCHAR2,
959 X_County VARCHAR2,
960 X_Address_Style VARCHAR2,
961 X_Language VARCHAR2,
962 X_Allow_Awt_Flag VARCHAR2,
963 X_Awt_Group_Id NUMBER,
964 X_Pay_Awt_Group_Id NUMBER,--bug6664407
965 X_pay_on_code VARCHAR2,
966 X_default_pay_site_id NUMBER,
967 X_pay_on_receipt_summary_code VARCHAR2,
968 X_Bank_Branch_Type VARCHAR2,
969 X_EDI_ID_Number VARCHAR2, --Bug 7437549
970 /* 4552701
971 X_EDI_ID_Number VARCHAR2,
972 X_EDI_Payment_Method VARCHAR2,
973 X_EDI_Payment_Format VARCHAR2,
974 X_EDI_Remittance_Method VARCHAR2,
975 X_EDI_Remittance_Instruction VARCHAR2,
976 X_EDI_transaction_handling VARCHAR2,
977 eTax Uptake
978 X_Auto_Tax_Calc_Flag VARCHAR2,
979 X_Auto_Tax_Calc_Override VARCHAR2,
980 X_Amount_Includes_Tax_Flag VARCHAR2,
981 X_AP_Tax_Rounding_Rule VARCHAR2, */
982 X_Vendor_Site_Code_Alt VARCHAR2,
983 X_Address_Lines_Alt VARCHAR2,
984 X_global_attribute_category VARCHAR2 DEFAULT NULL,
985 X_global_attribute1 VARCHAR2 DEFAULT NULL,
986 X_global_attribute2 VARCHAR2 DEFAULT NULL,
987 X_global_attribute3 VARCHAR2 DEFAULT NULL,
988 X_global_attribute4 VARCHAR2 DEFAULT NULL,
989 X_global_attribute5 VARCHAR2 DEFAULT NULL,
990 X_global_attribute6 VARCHAR2 DEFAULT NULL,
991 X_global_attribute7 VARCHAR2 DEFAULT NULL,
992 X_global_attribute8 VARCHAR2 DEFAULT NULL,
993 X_global_attribute9 VARCHAR2 DEFAULT NULL,
994 X_global_attribute10 VARCHAR2 DEFAULT NULL,
995 X_global_attribute11 VARCHAR2 DEFAULT NULL,
996 X_global_attribute12 VARCHAR2 DEFAULT NULL,
997 X_global_attribute13 VARCHAR2 DEFAULT NULL,
998 X_global_attribute14 VARCHAR2 DEFAULT NULL,
999 X_global_attribute15 VARCHAR2 DEFAULT NULL,
1000 X_global_attribute16 VARCHAR2 DEFAULT NULL,
1001 X_global_attribute17 VARCHAR2 DEFAULT NULL,
1002 X_global_attribute18 VARCHAR2 DEFAULT NULL,
1003 X_global_attribute19 VARCHAR2 DEFAULT NULL,
1004 X_global_attribute20 VARCHAR2 DEFAULT NULL,
1005 X_Bank_Charge_Bearer VARCHAR2 DEFAULT NULL,
1006 X_Ece_Tp_Location_Code VARCHAR2 DEFAULT NULL,
1007 X_Pcard_Site_Flag VARCHAR2,
1008 X_Country_of_Origin_Code VARCHAR2,
1009 X_calling_sequence IN VARCHAR2,
1010 X_Shipping_Location_id IN NUMBER,
1011 X_Supplier_Notif_Method VARCHAR2, -- New Column
1012 X_Email_Address VARCHAR2, -- New Column
1013 --4552701 X_remittance_email VARCHAR2 DEFAULT NULL,
1014 X_Primary_pay_site_flag VARCHAR2 DEFAULT NULL,
1015 --MO Access Control
1016 X_org_id NUMBER DEFAULT mo_global.get_current_org_id
1017 ) IS
1018 CURSOR C IS
1019 SELECT *
1020 FROM ap_supplier_sites
1021 WHERE rowid = X_Rowid
1022 FOR UPDATE of vendor_site_id NOWAIT;
1023 Recinfo C%ROWTYPE;
1024
1025 current_calling_sequence varchar2(2000);
1026 debug_info varchar2(100);
1027 l_shipping_location_id NUMBER := NULL;
1028
1029 BEGIN
1030 -- Update the calling sequence
1031 --
1032 current_calling_sequence := 'AP_VENDOR_SITES_PKG.LOCK_ROW<-' ||
1033 X_calling_sequence;
1034 debug_info := 'Open cursor C';
1035 OPEN C;
1036 debug_info := 'Fetch cursor C';
1037 FETCH C INTO Recinfo;
1038 if (C%NOTFOUND) then
1039 debug_info := 'Close cursor C - NOTFOUND';
1040 CLOSE C;
1041 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
1042 APP_EXCEPTION.Raise_Exception;
1043 end if;
1044 debug_info := 'Close cursor C';
1045 CLOSE C;
1046
1047 -- Bug # 689472
1048
1049 debug_info := 'Getting address style from fnd_territory';
1050
1051 begin
1052 select address_style
1056 exception
1053 into recinfo.address_style
1054 from fnd_territories
1055 where territory_code = x_country;
1057 when no_data_found then
1058 recinfo.address_style := '';
1059 end;
1060
1061 if ( (Recinfo.vendor_site_id = X_Vendor_Site_Id)
1062 AND (Recinfo.vendor_id = X_Vendor_Id)
1063 AND (Recinfo.vendor_site_code = X_Vendor_Site_Code)
1064 AND ( (Recinfo.purchasing_site_flag = X_Purchasing_Site_Flag)
1065 OR ( (Recinfo.purchasing_site_flag IS NULL)
1066 AND (X_Purchasing_Site_Flag IS NULL)))
1067 AND ( (Recinfo.rfq_only_site_flag = X_Rfq_Only_Site_Flag)
1068 OR ( (Recinfo.rfq_only_site_flag IS NULL)
1069 AND (X_Rfq_Only_Site_Flag IS NULL)))
1070 AND ( (Recinfo.pay_site_flag = X_Pay_Site_Flag)
1071 OR ( (Recinfo.pay_site_flag IS NULL)
1072 AND (X_Pay_Site_Flag IS NULL)))
1073 AND ( (Recinfo.attention_ar_flag = X_Attention_Ar_Flag)
1074 OR ( (Recinfo.attention_ar_flag IS NULL)
1075 AND (X_Attention_Ar_Flag IS NULL)))
1076 AND ( (Recinfo.address_line1 = X_Address_Line1)
1077 OR ( (Recinfo.address_line1 IS NULL)
1078 AND (X_Address_Line1 IS NULL)))
1079 AND ( (Recinfo.address_line2 = X_Address_Line2)
1080 OR ( (Recinfo.address_line2 IS NULL)
1081 AND (X_Address_Line2 IS NULL)))
1082 AND ( (Recinfo.address_line3 = X_Address_Line3)
1083 OR ( (Recinfo.address_line3 IS NULL)
1084 AND (X_Address_Line3 IS NULL)))
1085 AND ( (Recinfo.city = X_City)
1086 OR ( (Recinfo.city IS NULL)
1087 AND (X_City IS NULL)))
1088 AND ( (Recinfo.state = X_State)
1089 OR ( (Recinfo.state IS NULL)
1090 AND (X_State IS NULL)))
1091 AND ( (Recinfo.zip = X_Zip)
1092 OR ( (Recinfo.zip IS NULL)
1093 AND (X_Zip IS NULL)))
1094 AND ( (Recinfo.province = X_Province)
1095 OR ( (Recinfo.province IS NULL)
1096 AND (X_Province IS NULL)))
1097 AND ( (Recinfo.country = X_Country)
1098 OR ( (Recinfo.country IS NULL)
1099 AND (X_Country IS NULL)))
1100 AND ( (Recinfo.area_code = X_Area_Code)
1101 OR ( (Recinfo.area_code IS NULL)
1102 AND (X_Area_Code IS NULL)))
1103 AND ( (Recinfo.phone = X_Phone)
1104 OR ( (Recinfo.phone IS NULL)
1105 AND (X_Phone IS NULL)))
1106 AND ( (Recinfo.customer_num = X_Customer_Num)
1107 OR ( (Recinfo.customer_num IS NULL)
1108 AND (X_Customer_Num IS NULL)))
1109 AND ( (Recinfo.ship_to_location_id = X_Ship_To_Location_Id)
1110 OR ( (Recinfo.ship_to_location_id IS NULL)
1111 AND (X_Ship_To_Location_Id IS NULL)))
1112 AND ( (Recinfo.bill_to_location_id = X_Bill_To_Location_Id)
1113 OR ( (Recinfo.bill_to_location_id IS NULL)
1114 AND (X_Bill_To_Location_Id IS NULL)))
1115 AND ( (Recinfo.ship_via_lookup_code = X_Ship_Via_Lookup_Code)
1116 OR ( (Recinfo.ship_via_lookup_code IS NULL)
1117 AND (X_Ship_Via_Lookup_Code IS NULL)))
1118 AND ( (Recinfo.freight_terms_lookup_code = X_Freight_Terms_Lookup_Code)
1119 OR ( (Recinfo.freight_terms_lookup_code IS NULL)
1120 AND (X_Freight_Terms_Lookup_Code IS NULL)))
1121 AND ( (Recinfo.fob_lookup_code = X_Fob_Lookup_Code)
1122 OR ( (Recinfo.fob_lookup_code IS NULL)
1123 AND (X_Fob_Lookup_Code IS NULL)))
1124 AND ( (Recinfo.inactive_date = X_Inactive_Date)
1125 OR ( (Recinfo.inactive_date IS NULL)
1126 AND (X_Inactive_Date IS NULL)))
1127 AND ( (Recinfo.fax = X_Fax)
1128 OR ( (Recinfo.fax IS NULL)
1129 AND (X_Fax IS NULL)))
1130 AND ( (Recinfo.fax_area_code = X_Fax_Area_Code)
1131 OR ( (Recinfo.fax_area_code IS NULL)
1132 AND (X_Fax_Area_Code IS NULL)))
1133 AND ( (Recinfo.telex = X_Telex)
1134 OR ( (Recinfo.telex IS NULL)
1135 AND (X_Telex IS NULL)))
1136 AND ( (Recinfo.bank_account_name = X_Bank_Account_Name)
1137 OR ( (Recinfo.bank_account_name IS NULL)
1138 AND (X_Bank_Account_Name IS NULL)))
1139 AND ( (Recinfo.bank_account_num = X_Bank_Account_Num)
1140 OR ( (Recinfo.bank_account_num IS NULL)
1141 AND (X_Bank_Account_Num IS NULL)))
1142 AND ( (Recinfo.bank_num = X_Bank_Num)
1143 OR ( (Recinfo.bank_num IS NULL)
1144 AND (X_Bank_Num IS NULL)))
1145 AND ( (Recinfo.bank_account_type = X_Bank_Account_Type)
1146 OR ( (Recinfo.bank_account_type IS NULL)
1147 AND (X_Bank_Account_Type IS NULL)))
1148 AND ( (Recinfo.terms_date_basis = X_Terms_Date_Basis)
1149 OR ( (Recinfo.terms_date_basis IS NULL)
1150 AND (X_Terms_Date_Basis IS NULL)))
1151 AND ( (Recinfo.current_catalog_num = X_Current_Catalog_Num)
1152 OR ( (Recinfo.current_catalog_num IS NULL)
1156 AND (X_Distribution_Set_Id IS NULL)))
1153 AND (X_Current_Catalog_Num IS NULL)))
1154 AND ( (Recinfo.distribution_set_id = X_Distribution_Set_Id)
1155 OR ( (Recinfo.distribution_set_id IS NULL)
1157 AND ( (Recinfo.accts_pay_code_combination_id = X_Accts_Pay_CCID)
1158 OR ( (Recinfo.accts_pay_code_combination_id IS NULL)
1159 AND (X_Accts_Pay_CCID IS NULL)))
1160 AND ( (Recinfo.future_dated_payment_ccid = X_Future_Dated_Payment_CCID)
1161 OR ( (Recinfo.future_dated_payment_ccid IS NULL)
1162 AND (X_Future_Dated_Payment_CCID IS NULL)))
1163 AND ( (Recinfo.prepay_code_combination_id = X_Prepay_Code_Combination_Id)
1164 OR ( (Recinfo.prepay_code_combination_id IS NULL)
1165 AND (X_Prepay_Code_Combination_Id IS NULL)))
1166 AND ( (Recinfo.pay_group_lookup_code = X_Pay_Group_Lookup_Code)
1167 OR ( (Recinfo.pay_group_lookup_code IS NULL)
1168 AND (X_Pay_Group_Lookup_Code IS NULL)))
1169 AND ( (Recinfo.payment_priority = X_Payment_Priority)
1170 OR ( (Recinfo.payment_priority IS NULL)
1171 AND (X_Payment_Priority IS NULL)))
1172 AND ( (Recinfo.terms_id = X_Terms_Id)
1173 OR ( (Recinfo.terms_id IS NULL)
1174 AND (X_Terms_Id IS NULL)))
1175 AND ( (Recinfo.invoice_amount_limit = X_Invoice_Amount_Limit)
1176 OR ( (Recinfo.invoice_amount_limit IS NULL)
1177 AND (X_Invoice_Amount_Limit IS NULL)))
1178 AND ( (Recinfo.pay_date_basis_lookup_code = X_Pay_Date_Basis_Lookup_Code)
1179 OR ( (Recinfo.pay_date_basis_lookup_code IS NULL)
1180 AND (X_Pay_Date_Basis_Lookup_Code IS NULL)))
1181 AND ( (Recinfo.always_take_disc_flag = X_Always_Take_Disc_Flag)
1182 OR ( (Recinfo.always_take_disc_flag IS NULL)
1183 AND (X_Always_Take_Disc_Flag IS NULL)))
1184 AND ( (Recinfo.invoice_currency_code = X_Invoice_Currency_Code)
1185 OR ( (Recinfo.invoice_currency_code IS NULL)
1186 AND (X_Invoice_Currency_Code IS NULL)))
1187 AND ( (Recinfo.bank_charge_bearer = X_Bank_Charge_Bearer)
1188 OR ( (Recinfo.bank_charge_bearer IS NULL)
1189 AND (X_Bank_Charge_Bearer IS NULL)))
1190 AND ( (Recinfo.Ece_Tp_Location_Code = X_Ece_Tp_Location_Code)
1191 OR ( (Recinfo.Ece_Tp_Location_Code IS NULL)
1192 AND (X_Ece_Tp_Location_Code IS NULL)))
1193 -- New Column
1194 AND ( (Recinfo.supplier_notif_method = X_Supplier_Notif_Method)
1195 OR ( (Recinfo.supplier_notif_method IS NULL)
1196 AND (X_Supplier_Notif_Method IS NULL)))
1197 AND ( (Recinfo.email_address = X_Email_Address)
1198 OR ( (Recinfo.email_address IS NULL)
1199 AND (X_Email_Address IS NULL)))
1200 AND ( (Recinfo.primary_pay_site_flag = X_Primary_pay_site_flag)
1201 OR ( (Recinfo.Primary_pay_site_flag IS NULL)
1202 AND (X_primary_pay_site_flag IS NULL)))
1203 /* MO Access Control */
1204 AND ( (Recinfo.org_id = X_org_id)
1205 OR ( (Recinfo.org_id IS NULL)
1206 AND (X_org_id IS NULL)))
1207 -- Bug 7437549
1208 AND ( (Recinfo.edi_id_number = X_EDI_ID_Number)
1209 OR ( (Recinfo.edi_id_number IS NULL)
1210 AND (X_EDI_ID_Number IS NULL)))
1211 )
1212
1213 then
1214 null;
1215 else
1216 FND_MESSAGE.Set_Name('FND','FORM_RECORD_CHANGED');
1217 APP_EXCEPTION.Raise_Exception;
1218 end if;
1219 --
1220 if (
1221
1222 ( (Recinfo.payment_currency_code = X_Payment_Currency_Code)
1223 OR ( (Recinfo.payment_currency_code IS NULL)
1224 AND (X_Payment_Currency_Code IS NULL)))
1225 AND ( (Recinfo.hold_all_payments_flag = X_Hold_All_Payments_Flag)
1226 OR ( (Recinfo.hold_all_payments_flag IS NULL)
1227 AND (X_Hold_All_Payments_Flag IS NULL)))
1228 AND ( (Recinfo.hold_future_payments_flag = X_Hold_Future_Payments_Flag)
1229 OR ( (Recinfo.hold_future_payments_flag IS NULL)
1230 AND (X_Hold_Future_Payments_Flag IS NULL)))
1231 AND ( (Recinfo.hold_reason = X_Hold_Reason)
1232 OR ( (Recinfo.hold_reason IS NULL)
1233 AND (X_Hold_Reason IS NULL)))
1234 AND ( (Recinfo.hold_unmatched_invoices_flag = X_Hold_Unmatched_Invoices_Flag)
1235 OR ( (Recinfo.hold_unmatched_invoices_flag IS NULL)
1236 AND (X_Hold_Unmatched_Invoices_Flag IS NULL)))
1237 AND ( (Recinfo.match_option = X_match_option)
1238 OR ( (Recinfo.match_option IS NULL)
1239 AND (X_match_option IS NULL)))
1240 AND ( (Recinfo.create_debit_memo_flag = X_create_debit_memo_flag)
1241 OR ( (Recinfo.create_debit_memo_flag IS NULL)
1242 AND (X_create_debit_memo_flag IS NULL)))
1243 AND ( (Recinfo.tax_reporting_site_flag = X_Tax_Reporting_Site_Flag)
1244 OR ( (Recinfo.tax_reporting_site_flag IS NULL)
1245 AND (X_Tax_Reporting_Site_Flag IS NULL)))
1246 AND ( (Recinfo.attribute_category = X_Attribute_Category)
1247 OR ( (Recinfo.attribute_category IS NULL)
1251 AND (X_Attribute1 IS NULL)))
1248 AND (X_Attribute_Category IS NULL)))
1249 AND ( (Recinfo.attribute1 = X_Attribute1)
1250 OR ( (Recinfo.attribute1 IS NULL)
1252 AND ( (Recinfo.attribute2 = X_Attribute2)
1253 OR ( (Recinfo.attribute2 IS NULL)
1254 AND (X_Attribute2 IS NULL)))
1255 AND ( (Recinfo.attribute3 = X_Attribute3)
1256 OR ( (Recinfo.attribute3 IS NULL)
1257 AND (X_Attribute3 IS NULL)))
1258 AND ( (Recinfo.attribute4 = X_Attribute4)
1259 OR ( (Recinfo.attribute4 IS NULL)
1260 AND (X_Attribute4 IS NULL)))
1261 AND ( (Recinfo.attribute5 = X_Attribute5)
1262 OR ( (Recinfo.attribute5 IS NULL)
1263 AND (X_Attribute5 IS NULL)))
1264 AND ( (Recinfo.attribute6 = X_Attribute6)
1265 OR ( (Recinfo.attribute6 IS NULL)
1266 AND (X_Attribute6 IS NULL)))
1267 AND ( (Recinfo.attribute7 = X_Attribute7)
1268 OR ( (Recinfo.attribute7 IS NULL)
1269 AND (X_Attribute7 IS NULL)))
1270 AND ( (Recinfo.attribute8 = X_Attribute8)
1271 OR ( (Recinfo.attribute8 IS NULL)
1272 AND (X_Attribute8 IS NULL)))
1273 AND ( (Recinfo.attribute9 = X_Attribute9)
1274 OR ( (Recinfo.attribute9 IS NULL)
1275 AND (X_Attribute9 IS NULL)))
1276 AND ( (Recinfo.attribute10 = X_Attribute10)
1277 OR ( (Recinfo.attribute10 IS NULL)
1278 AND (X_Attribute10 IS NULL)))
1279 AND ( (Recinfo.attribute11 = X_Attribute11)
1280 OR ( (Recinfo.attribute11 IS NULL)
1281 AND (X_Attribute11 IS NULL)))
1282 AND ( (Recinfo.attribute12 = X_Attribute12)
1283 OR ( (Recinfo.attribute12 IS NULL)
1284 AND (X_Attribute12 IS NULL)))
1285 AND ( (Recinfo.attribute13 = X_Attribute13)
1286 OR ( (Recinfo.attribute13 IS NULL)
1287 AND (X_Attribute13 IS NULL)))
1288 AND ( (Recinfo.attribute14 = X_Attribute14)
1289 OR ( (Recinfo.attribute14 IS NULL)
1290 AND (X_Attribute14 IS NULL)))
1291 AND ( (Recinfo.attribute15 = X_Attribute15)
1292 OR ( (Recinfo.attribute15 IS NULL)
1293 AND (X_Attribute15 IS NULL)))
1294 AND ( (Recinfo.validation_number = X_Validation_Number)
1295 OR ( (Recinfo.validation_number IS NULL)
1296 AND (X_Validation_Number IS NULL)))
1297 AND ( (Recinfo.exclude_freight_from_discount = X_Exclude_Freight_From_Disc)
1298 OR ( (Recinfo.exclude_freight_from_discount IS NULL)
1299 AND (X_Exclude_Freight_From_Disc IS NULL)))
1300 AND ( (Recinfo.vat_registration_num = X_Vat_Registration_Num)
1301 OR ( (Recinfo.vat_registration_num IS NULL)
1302 AND (X_Vat_Registration_Num IS NULL)))
1303 AND ( (Recinfo.check_digits = X_Check_Digits)
1304 OR ( (Recinfo.check_digits IS NULL)
1305 AND (X_Check_Digits IS NULL)))
1306 AND ( (Recinfo.bank_number = X_Bank_Number)
1307 OR ( (Recinfo.bank_number IS NULL)
1308 AND (X_Bank_Number IS NULL)))
1309 AND ( (Recinfo.address_line4 = X_Address_Line4)
1310 OR ( (Recinfo.address_line4 IS NULL)
1311 AND (X_Address_Line4 IS NULL)))
1312 AND ( (Recinfo.county = X_County)
1313 OR ( (Recinfo.county IS NULL)
1314 AND (X_County IS NULL)))
1315 AND ( (Recinfo.address_style = X_Address_Style)
1316 OR ( (Recinfo.address_style IS NULL)
1317 AND (X_Address_Style IS NULL)))
1318 AND ( (Recinfo.language = X_Language)
1319 OR ( (Recinfo.language IS NULL)
1320 AND (X_Language IS NULL)))
1321 AND ( (Recinfo.allow_awt_flag = X_Allow_Awt_Flag)
1322 OR ( (Recinfo.allow_awt_flag IS NULL)
1323 AND (X_Allow_Awt_Flag IS NULL)))
1324 AND ( (Recinfo.awt_group_id = X_Awt_Group_Id)
1325 OR ( (Recinfo.awt_group_id IS NULL)
1326 AND (X_Awt_Group_Id IS NULL)))
1327 AND ( (Recinfo.pay_awt_group_id = X_Pay_Awt_Group_Id)
1328 OR ( (Recinfo.Pay_awt_group_id IS NULL)
1329 AND (X_Pay_Awt_Group_Id IS NULL))) --bug6664407
1330 AND ( (Recinfo.pay_on_code = X_pay_on_code)
1331 OR ( (Recinfo.pay_on_code IS NULL)
1332 AND (X_pay_on_code IS NULL)))
1333 AND ( (Recinfo.default_pay_site_id = X_default_pay_site_id)
1334 OR ( (Recinfo.default_pay_site_id IS NULL)
1335 AND (X_default_pay_site_id IS NULL)))
1336 AND ( (Recinfo.pay_on_receipt_summary_code = X_pay_on_receipt_summary_code)
1337 OR ( (Recinfo.pay_on_receipt_summary_code IS NULL)
1338 AND (X_pay_on_receipt_summary_code IS NULL)))
1339 AND ( (Recinfo.Bank_Branch_Type = X_Bank_Branch_Type)
1340 OR ( (Recinfo.Bank_Branch_Type IS NULL)
1341 AND (X_Bank_Branch_Type IS NULL)))
1342 AND ( (Recinfo.vendor_site_code_alt = X_Vendor_Site_Code_Alt)
1343 OR ( (Recinfo.vendor_site_code_alt IS NULL)
1347 AND (X_Address_Lines_Alt IS NULL)))
1344 AND (X_Vendor_Site_Code_Alt IS NULL)))
1345 AND ( (Recinfo.address_lines_alt = X_Address_Lines_Alt)
1346 OR ( (Recinfo.address_lines_alt IS NULL)
1348 )
1349 then
1350 null;
1351 else
1352 FND_MESSAGE.Set_Name('FND','FORM_RECORD_CHANGED');
1353 APP_EXCEPTION.Raise_Exception;
1354 end if;
1355 --
1356 if (
1357 ( (Recinfo.global_attribute_category = X_global_attribute_category)
1358 OR ( (Recinfo.global_attribute_category IS NULL)
1359 AND (X_global_attribute_category IS NULL)))
1360 AND ( (Recinfo.global_attribute1 = X_global_attribute1)
1361 OR ( (Recinfo.global_attribute1 IS NULL)
1362 AND (X_global_attribute1 IS NULL)))
1363 AND ( (Recinfo.global_attribute2 = X_global_attribute2)
1364 OR ( (Recinfo.global_attribute2 IS NULL)
1365 AND (X_global_attribute2 IS NULL)))
1366 AND ( (Recinfo.global_attribute3 = X_global_attribute3)
1367 OR ( (Recinfo.global_attribute3 IS NULL)
1368 AND (X_global_attribute3 IS NULL)))
1369 AND ( (Recinfo.global_attribute4 = X_global_attribute4)
1370 OR ( (Recinfo.global_attribute4 IS NULL)
1371 AND (X_global_attribute4 IS NULL)))
1372 AND ( (Recinfo.global_attribute5 = X_global_attribute5)
1373 OR ( (Recinfo.global_attribute5 IS NULL)
1374 AND (X_global_attribute5 IS NULL)))
1375 AND ( (Recinfo.global_attribute6 = X_global_attribute6)
1376 OR ( (Recinfo.global_attribute6 IS NULL)
1377 AND (X_global_attribute6 IS NULL)))
1378 AND ( (Recinfo.global_attribute7 = X_global_attribute7)
1379 OR ( (Recinfo.global_attribute7 IS NULL)
1380 AND (X_global_attribute7 IS NULL)))
1381 AND ( (Recinfo.global_attribute8 = X_global_attribute8)
1382 OR ( (Recinfo.global_attribute8 IS NULL)
1383 AND (X_global_attribute8 IS NULL)))
1384 AND ( (Recinfo.global_attribute9 = X_global_attribute9)
1385 OR ( (Recinfo.global_attribute9 IS NULL)
1386 AND (X_global_attribute9 IS NULL)))
1387 AND ( (Recinfo.global_attribute10 = X_global_attribute10)
1388 OR ( (Recinfo.global_attribute10 IS NULL)
1389 AND (X_global_attribute10 IS NULL)))
1390 AND ( (Recinfo.global_attribute11 = X_global_attribute11)
1391 OR ( (Recinfo.global_attribute11 IS NULL)
1392 AND (X_global_attribute11 IS NULL)))
1393 AND ( (Recinfo.global_attribute12 = X_global_attribute12)
1394 OR ( (Recinfo.global_attribute12 IS NULL)
1395 AND (X_global_attribute12 IS NULL)))
1396 AND ( (Recinfo.global_attribute13 = X_global_attribute13)
1397 OR ( (Recinfo.global_attribute13 IS NULL)
1398 AND (X_global_attribute13 IS NULL)))
1399 AND ( (Recinfo.global_attribute14 = X_global_attribute14)
1400 OR ( (Recinfo.global_attribute14 IS NULL)
1401 AND (X_global_attribute14 IS NULL)))
1402 AND ( (Recinfo.global_attribute15 = X_global_attribute15)
1403 OR ( (Recinfo.global_attribute15 IS NULL)
1404 AND (X_global_attribute15 IS NULL)))
1405 AND ( (Recinfo.global_attribute16 = X_global_attribute16)
1406 OR ( (Recinfo.global_attribute16 IS NULL)
1407 AND (X_global_attribute16 IS NULL)))
1408 AND ( (Recinfo.global_attribute17 = X_global_attribute17)
1409 OR ( (Recinfo.global_attribute17 IS NULL)
1410 AND (X_global_attribute17 IS NULL)))
1411 AND ( (Recinfo.global_attribute18 = X_global_attribute18)
1412 OR ( (Recinfo.global_attribute18 IS NULL)
1413 AND (X_global_attribute18 IS NULL)))
1414 AND ( (Recinfo.global_attribute19 = X_global_attribute19)
1415 OR ( (Recinfo.global_attribute19 IS NULL)
1416 AND (X_global_attribute19 IS NULL)))
1417 AND ( (Recinfo.global_attribute20 = X_global_attribute20)
1418 OR ( (Recinfo.global_attribute20 IS NULL)
1419 AND (X_global_attribute20 IS NULL)))
1420 AND ( (Recinfo.Pcard_Site_Flag = X_Pcard_Site_Flag)
1421 OR ( (Recinfo.Pcard_Site_Flag IS NULL)
1422 AND (X_Pcard_Site_Flag IS NULL)))
1423 AND ( (Recinfo.Country_of_Origin_Code = X_Country_of_Origin_Code)
1424 OR ( (Recinfo.Country_of_Origin_Code IS NULL)
1425 AND (X_Country_of_Origin_Code IS NULL)))
1426 ) then
1427 return;
1428 else
1429 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
1430 APP_EXCEPTION.Raise_Exception;
1431 end if;
1432
1433 begin
1434 select location_id
1435 into l_shipping_location_id
1436 from po_location_associations
1437 where vendor_site_id = X_Vendor_Site_Id;
1438 exception
1439 when no_data_found then
1440 l_shipping_location_id := NULL;
1441 end;
1442
1443 if ( ( X_Shipping_Location_id = l_shipping_location_id )
1444 OR ( X_Shipping_Location_id is null and l_shipping_location_id is null) ) then
1445 null;
1446 else
1450
1447 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
1448 APP_EXCEPTION.Raise_Exception;
1449 end if;
1451 EXCEPTION
1452
1453 WHEN OTHERS THEN
1454 IF (SQLCODE <> -20001) THEN
1455 IF (SQLCODE = -54) THEN
1456 FND_MESSAGE.SET_NAME('SQLAP','AP_RESOURCE_BUSY');
1457 ELSE
1458 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1459 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1460 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
1461 FND_MESSAGE.SET_TOKEN('PARAMETERS','VENDOR_SITE_ID = ' ||
1462 X_Vendor_Site_Id ||
1463 ', ROWID = ' || X_Rowid );
1464 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
1465 END IF;
1466 END IF;
1467 APP_EXCEPTION.RAISE_EXCEPTION;
1468
1469 END Lock_Row;
1470
1471
1472 PROCEDURE Update_Row(X_Rowid VARCHAR2,
1473 X_Vendor_Site_Id NUMBER,
1474 X_Last_Update_Date DATE,
1475 X_Last_Updated_By NUMBER,
1476 X_Vendor_Id NUMBER,
1477 X_Vendor_Site_Code VARCHAR2,
1478 X_Last_Update_Login NUMBER,
1479 X_Creation_Date DATE,
1480 X_Created_By NUMBER,
1481 X_Purchasing_Site_Flag VARCHAR2,
1482 X_Rfq_Only_Site_Flag VARCHAR2,
1483 X_Pay_Site_Flag VARCHAR2,
1484 X_Attention_Ar_Flag VARCHAR2,
1485 X_Address_Line1 VARCHAR2,
1486 X_Address_Line2 VARCHAR2,
1487 X_Address_Line3 VARCHAR2,
1488 X_City VARCHAR2,
1489 X_State VARCHAR2,
1490 X_Zip VARCHAR2,
1491 X_Province VARCHAR2,
1492 X_Country VARCHAR2,
1493 X_Area_Code VARCHAR2,
1494 X_Phone VARCHAR2,
1495 X_Customer_Num VARCHAR2,
1496 X_Ship_To_Location_Id NUMBER,
1497 X_Bill_To_Location_Id NUMBER,
1498 X_Ship_Via_Lookup_Code VARCHAR2,
1499 X_Freight_Terms_Lookup_Code VARCHAR2,
1500 X_Fob_Lookup_Code VARCHAR2,
1501 X_Inactive_Date DATE,
1502 X_Fax VARCHAR2,
1503 X_Fax_Area_Code VARCHAR2,
1504 X_Telex VARCHAR2,
1505 --4552701 X_Payment_Method_Lookup_Code VARCHAR2,
1506 X_Bank_Account_Name VARCHAR2,
1507 X_Bank_Account_Num VARCHAR2,
1508 X_Bank_Num VARCHAR2,
1509 X_Bank_Account_Type VARCHAR2,
1510 X_Terms_Date_Basis VARCHAR2,
1511 X_Current_Catalog_Num VARCHAR2,
1512 -- eTax Uptake X_Vat_Code VARCHAR2,
1513 X_Distribution_Set_Id NUMBER,
1514 X_Accts_Pay_CCID NUMBER,
1515 X_Future_Dated_Payment_CCID NUMBER,
1516 X_Prepay_Code_Combination_Id NUMBER,
1517 X_Pay_Group_Lookup_Code VARCHAR2,
1518 X_Payment_Priority NUMBER,
1519 X_Terms_Id NUMBER,
1520 X_Invoice_Amount_Limit NUMBER,
1521 X_Pay_Date_Basis_Lookup_Code VARCHAR2,
1522 X_Always_Take_Disc_Flag VARCHAR2,
1523 X_Invoice_Currency_Code VARCHAR2,
1524 X_Payment_Currency_Code VARCHAR2,
1525 X_Hold_All_Payments_Flag VARCHAR2,
1526 X_Hold_Future_Payments_Flag VARCHAR2,
1527 X_Hold_Reason VARCHAR2,
1528 X_Hold_Unmatched_Invoices_Flag VARCHAR2,
1529 X_Match_Option VARCHAR2,
1530 X_Create_Debit_Memo_Flag VARCHAR2,
1531 --4552701 X_Exclusive_Payment_Flag VARCHAR2,
1532 X_Tax_Reporting_Site_Flag VARCHAR2,
1533 X_Attribute_Category VARCHAR2,
1534 X_Attribute1 VARCHAR2,
1535 X_Attribute2 VARCHAR2,
1536 X_Attribute3 VARCHAR2,
1537 X_Attribute4 VARCHAR2,
1538 X_Attribute5 VARCHAR2,
1539 X_Attribute6 VARCHAR2,
1540 X_Attribute7 VARCHAR2,
1541 X_Attribute8 VARCHAR2,
1542 X_Attribute9 VARCHAR2,
1543 X_Attribute10 VARCHAR2,
1547 X_Attribute14 VARCHAR2,
1544 X_Attribute11 VARCHAR2,
1545 X_Attribute12 VARCHAR2,
1546 X_Attribute13 VARCHAR2,
1548 X_Attribute15 VARCHAR2,
1549 X_Validation_Number NUMBER,
1550 X_Exclude_Freight_From_Disc VARCHAR2,
1551 X_Vat_Registration_Num VARCHAR2,
1552 -- eTax Uptake X_Offset_Tax_Flag VARCHAR2,
1553 X_Check_Digits VARCHAR2,
1554 X_Bank_Number VARCHAR2,
1555 X_Address_Line4 VARCHAR2,
1556 X_County VARCHAR2,
1557 X_Address_Style VARCHAR2,
1558 X_Language VARCHAR2,
1559 X_Allow_Awt_Flag VARCHAR2,
1560 X_Awt_Group_Id NUMBER,
1561 X_Pay_Awt_Group_Id NUMBER,--bug6664407
1562 X_pay_on_code VARCHAR2,
1563 X_default_pay_site_id NUMBER,
1564 X_pay_on_receipt_summary_code VARCHAR2,
1565 X_Bank_Branch_Type VARCHAR2,
1566 X_EDI_ID_Number VARCHAR2, --Bug 7437549
1567 /* 4552701
1568 X_EDI_ID_Number VARCHAR2,
1569 X_EDI_Payment_Method VARCHAR2,
1570 X_EDI_Payment_Format VARCHAR2,
1571 X_EDI_Remittance_Method VARCHAR2,
1572 X_EDI_Remittance_Instruction VARCHAR2,
1573 X_EDI_transaction_handling VARCHAR2,
1574 eTax Uptake
1575 X_Auto_Tax_Calc_Flag VARCHAR2,
1576 X_Auto_Tax_Calc_Override VARCHAR2,
1577 X_Amount_Includes_Tax_Flag VARCHAR2,
1578 X_AP_Tax_Rounding_Rule VARCHAR2, */
1579 X_Vendor_Site_Code_Alt VARCHAR2,
1580 X_Address_Lines_Alt VARCHAR2,
1581 X_global_attribute_category VARCHAR2 DEFAULT NULL,
1582 X_global_attribute1 VARCHAR2 DEFAULT NULL,
1583 X_global_attribute2 VARCHAR2 DEFAULT NULL,
1584 X_global_attribute3 VARCHAR2 DEFAULT NULL,
1585 X_global_attribute4 VARCHAR2 DEFAULT NULL,
1586 X_global_attribute5 VARCHAR2 DEFAULT NULL,
1587 X_global_attribute6 VARCHAR2 DEFAULT NULL,
1588 X_global_attribute7 VARCHAR2 DEFAULT NULL,
1589 X_global_attribute8 VARCHAR2 DEFAULT NULL,
1590 X_global_attribute9 VARCHAR2 DEFAULT NULL,
1591 X_global_attribute10 VARCHAR2 DEFAULT NULL,
1592 X_global_attribute11 VARCHAR2 DEFAULT NULL,
1593 X_global_attribute12 VARCHAR2 DEFAULT NULL,
1594 X_global_attribute13 VARCHAR2 DEFAULT NULL,
1595 X_global_attribute14 VARCHAR2 DEFAULT NULL,
1596 X_global_attribute15 VARCHAR2 DEFAULT NULL,
1597 X_global_attribute16 VARCHAR2 DEFAULT NULL,
1598 X_global_attribute17 VARCHAR2 DEFAULT NULL,
1599 X_global_attribute18 VARCHAR2 DEFAULT NULL,
1600 X_global_attribute19 VARCHAR2 DEFAULT NULL,
1601 X_global_attribute20 VARCHAR2 DEFAULT NULL,
1602 X_Bank_Charge_Bearer VARCHAR2 DEFAULT NULL,
1603 X_Ece_Tp_Location_Code VARCHAR2 DEFAULT NULL,
1604 X_Pcard_Site_Flag VARCHAR2,
1605 X_Country_of_Origin_Code VARCHAR2,
1606 X_calling_sequence IN VARCHAR2,
1607 X_Shipping_Location_id IN NUMBER,
1608 X_Supplier_Notif_Method VARCHAR2, -- New Column
1609 X_Email_Address VARCHAR2, -- New Column
1610 --4552701 X_Remittance_email VARCHAR2 DEFAULT NULL,
1611 X_Primary_pay_site_flag VARCHAR2 DEFAULT NULL,
1612 --MO Access Control
1613 X_Org_ID NUMBER DEFAULT mo_global.get_current_org_id
1614 ) IS
1615
1616 current_calling_sequence varchar2(2000);
1617 debug_info varchar2(100);
1618 vendor_site_old po_vendor_sites.vendor_site_code%TYPE;
1619 total_sites number;
1620
1621 BEGIN
1622 -- Update the calling sequence
1623 --
1624 current_calling_sequence := 'AP_VENDOR_SITES_PKG.UPDATE_ROW<-' ||
1625 X_calling_sequence;
1626
1627 -- Bug # 636963 Vendor site code can not be changed if same code exits in
1628 -- a different org.
1629 -- (This fix is now backed out, Bug 702458)
1630 -- Get old site name.
1631
1632 /* select vendor_site_code
1633 into vendor_site_old
1634 from po_vendor_sites
1635 where rowid = X_rowid;
1636
1637 select count(*)
1638 into total_sites
1639 from po_vendor_sites_all
1640 where vendor_site_id = X_vendor_site_id;
1641
1642 if ( total_sites > 1 and ( vendor_site_old <> X_vendor_site_code)) then
1643 fnd_message.set_name('SQLAP','AP_SHARED_SITE');
1647 Check_duplicate_vendor_site (x_vendor_id, x_vendor_site_code,
1644 app_exception.raise_exception;
1645 end if; */
1646
1648 x_org_id, --MO Access Control
1649 x_rowid,
1650 X_calling_sequence => current_calling_sequence);
1651
1652 if (x_tax_reporting_site_flag = 'Y') then
1653 check_multiple_tax_sites (x_vendor_id, x_vendor_site_id,
1654 x_org_id, --MO Access Control
1655 X_calling_sequence => current_calling_sequence);
1656 end if;
1657
1658 debug_info := 'Update PO_VENDOR_SITES';
1659 UPDATE ap_supplier_sites
1660 SET
1661 vendor_site_id = X_Vendor_Site_Id,
1662 last_update_date = X_Last_Update_Date,
1663 last_updated_by = X_Last_Updated_By,
1664 vendor_id = X_Vendor_Id,
1665 vendor_site_code = X_Vendor_Site_Code,
1666 last_update_login = X_Last_Update_Login,
1667 purchasing_site_flag = X_Purchasing_Site_Flag,
1668 rfq_only_site_flag = X_Rfq_Only_Site_Flag,
1669 pay_site_flag = X_Pay_Site_Flag,
1670 attention_ar_flag = X_Attention_Ar_Flag,
1671 address_line1 = X_Address_Line1,
1672 address_line2 = X_Address_Line2,
1673 address_line3 = X_Address_Line3,
1674 city = X_City,
1675 state = X_State,
1676 zip = X_Zip,
1677 province = X_Province,
1678 country = X_Country,
1679 area_code = X_Area_Code,
1680 phone = X_Phone,
1681 customer_num = X_Customer_Num,
1682 ship_to_location_id = X_Ship_To_Location_Id,
1683 bill_to_location_id = X_Bill_To_Location_Id,
1684 ship_via_lookup_code = X_Ship_Via_Lookup_Code,
1685 freight_terms_lookup_code = X_Freight_Terms_Lookup_Code,
1686 fob_lookup_code = X_Fob_Lookup_Code,
1687 inactive_date = X_Inactive_Date,
1688 fax = X_Fax,
1689 fax_area_code = X_Fax_Area_Code,
1690 telex = X_Telex,
1691 bank_account_name = X_Bank_Account_Name,
1692 bank_account_num = X_Bank_Account_Num,
1693 bank_num = X_Bank_Num,
1694 bank_account_type = X_Bank_Account_Type,
1695 terms_date_basis = X_Terms_Date_Basis,
1696 current_catalog_num = X_Current_Catalog_Num,
1697 distribution_set_id = X_Distribution_Set_Id,
1698 accts_pay_code_combination_id = X_Accts_Pay_CCID,
1699 future_dated_payment_ccid = X_Future_Dated_Payment_CCID,
1700 prepay_code_combination_id = X_Prepay_Code_Combination_Id,
1701 pay_group_lookup_code = X_Pay_Group_Lookup_Code,
1702 payment_priority = X_Payment_Priority,
1703 terms_id = X_Terms_Id,
1704 invoice_amount_limit = X_Invoice_Amount_Limit,
1705 pay_date_basis_lookup_code = X_Pay_Date_Basis_Lookup_Code,
1706 always_take_disc_flag = X_Always_Take_Disc_Flag,
1707 invoice_currency_code = X_Invoice_Currency_Code,
1708 payment_currency_code = X_Payment_Currency_Code,
1709 hold_all_payments_flag = X_Hold_All_Payments_Flag,
1710 hold_future_payments_flag = X_Hold_Future_Payments_Flag,
1711 hold_reason = X_Hold_Reason,
1712 hold_unmatched_invoices_flag = X_Hold_Unmatched_Invoices_Flag,
1713 match_option = X_Match_Option,
1714 create_debit_memo_flag = X_Create_Debit_Memo_Flag,
1715 tax_reporting_site_flag = X_Tax_Reporting_Site_Flag,
1716 attribute_category = X_Attribute_Category,
1717 attribute1 = X_Attribute1,
1718 attribute2 = X_Attribute2,
1719 attribute3 = X_Attribute3,
1720 attribute4 = X_Attribute4,
1721 attribute5 = X_Attribute5,
1722 attribute6 = X_Attribute6,
1723 attribute7 = X_Attribute7,
1724 attribute8 = X_Attribute8,
1725 attribute9 = X_Attribute9,
1726 attribute10 = X_Attribute10,
1727 attribute11 = X_Attribute11,
1728 attribute12 = X_Attribute12,
1729 attribute13 = X_Attribute13,
1730 attribute14 = X_Attribute14,
1731 attribute15 = X_Attribute15,
1732 validation_number = X_Validation_Number,
1733 exclude_freight_from_discount = X_Exclude_Freight_From_Disc,
1734 vat_registration_num = X_Vat_Registration_Num,
1735 check_digits = X_Check_Digits,
1736 bank_number = X_Bank_Number,
1740 language = X_Language,
1737 address_line4 = X_Address_Line4,
1738 county = X_County,
1739 address_style = X_Address_Style,
1741 allow_awt_flag = X_Allow_Awt_Flag,
1742 awt_group_id = X_Awt_Group_Id,
1743 pay_awt_group_id = X_Pay_Awt_Group_Id,--bug6664407
1744 pay_on_code = X_pay_on_code,
1745 default_pay_site_id = X_default_pay_site_id,
1746 pay_on_receipt_summary_code = X_pay_on_receipt_summary_code,
1747 Bank_Branch_Type = X_Bank_Branch_Type,
1748 vendor_site_code_alt = X_Vendor_Site_Code_Alt,
1749 address_lines_alt = X_Address_Lines_Alt,
1750 global_attribute_category = X_global_attribute_category,
1751 global_attribute1 = X_global_attribute1,
1752 global_attribute2 = X_global_attribute2,
1753 global_attribute3 = X_global_attribute3,
1754 global_attribute4 = X_global_attribute4,
1755 global_attribute5 = X_global_attribute5,
1756 global_attribute6 = X_global_attribute6,
1757 global_attribute7 = X_global_attribute7,
1758 global_attribute8 = X_global_attribute8,
1759 global_attribute9 = X_global_attribute9,
1760 global_attribute10 = X_global_attribute10,
1761 global_attribute11 = X_global_attribute11,
1762 global_attribute12 = X_global_attribute12,
1763 global_attribute13 = X_global_attribute13,
1764 global_attribute14 = X_global_attribute14,
1765 global_attribute15 = X_global_attribute15,
1766 global_attribute16 = X_global_attribute16,
1767 global_attribute17 = X_global_attribute17,
1768 global_attribute18 = X_global_attribute18,
1769 global_attribute19 = X_global_attribute19,
1770 global_attribute20 = X_global_attribute20,
1771 Bank_Charge_Bearer = X_Bank_Charge_Bearer,
1772 Ece_Tp_Location_Code = X_Ece_Tp_Location_Code,
1773 Country_of_Origin_Code = X_Country_of_Origin_Code,
1774 Pcard_Site_Flag = X_Pcard_Site_Flag,
1775 Supplier_Notif_Method = X_Supplier_Notif_Method, -- New Column
1776 Email_Address = X_Email_Address, -- New Column
1777 Primary_pay_site_flag = X_Primary_pay_site_flag,
1778 Edi_id_number = X_EDI_ID_Number -- bug 7437549
1779 WHERE rowid = X_Rowid;
1780
1781 if (SQL%NOTFOUND) then
1782 Raise NO_DATA_FOUND;
1783 end if;
1784
1785 debug_info := 'Update values in PO_LOCATION_ASSOCIATIONS';
1786 --Bug 2697177: Added the IF condition
1787 if (X_Shipping_Location_id is not null) then
1788
1789 ap_po_locn_association_pkg.update_row(p_location_id => X_Shipping_Location_id,
1790 p_vendor_id => X_Vendor_Id,
1791 p_vendor_site_id => X_Vendor_Site_Id,
1792 p_last_update_date => X_Last_Update_Date,
1793 p_last_updated_by => X_Last_Updated_By,
1794 p_last_update_login => X_Last_Update_Login,
1795 p_creation_date => X_Creation_Date,
1796 p_created_by => X_Created_By,
1797 p_org_id => X_Org_ID); --MO Access Control
1798 end if;
1799
1800 EXCEPTION
1801 WHEN OTHERS THEN
1802 IF (SQLCODE <> -20001) THEN
1803 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1804 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1805 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
1806 FND_MESSAGE.SET_TOKEN('PARAMETERS','VENDOR_SITE_ID = ' ||
1807 X_Vendor_Site_Id ||
1808 ', ROWID = ' || X_Rowid);
1809 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
1810 END IF;
1811 APP_EXCEPTION.RAISE_EXCEPTION;
1812
1813 END Update_Row;
1814
1815 PROCEDURE update_row(
1816 p_vendor_site_rec IN AP_VENDOR_PUB_PKG.r_vendor_site_rec_type,
1817 p_last_update_date IN DATE,
1818 p_last_updated_by IN NUMBER,
1819 p_last_update_login IN NUMBER,
1820 p_request_id IN NUMBER,
1821 p_program_application_id IN NUMBER,
1822 p_program_id IN NUMBER,
1823 p_program_update_date IN DATE,
1824 p_vendor_site_id IN NUMBER
1825 ) IS
1826
1827 current_calling_sequence varchar2(2000);
1828 debug_info varchar2(100);
1829
1830 BEGIN
1831
1832 debug_info := 'Update PO_VENDOR_SITES';
1833
1834 --Bug 6216082 Begins. Added the following call to IGI package
1835 --Bug 7577497 Added another parameter to the function call p_pay_tax_grp_id
1836 IF (p_vendor_site_rec.Awt_Group_Id IS NOT NULL OR
1837 p_vendor_site_rec.Pay_Awt_Group_Id IS NOT NULL) THEN
1841 p_tax_grp_id => p_vendor_site_rec.Awt_Group_Id,
1838 IGI_CIS2007_UTIL_PKG.SUPPLIER_SITE_UPDATE(
1839 p_vendor_id => p_vendor_site_rec.Vendor_Id,
1840 p_vendor_site_id => p_vendor_site_id,
1842 p_pay_tax_grp_id => p_vendor_site_rec.Pay_Awt_Group_Id
1843 );
1844 END IF;
1845 --Bug 6216082 Ends.
1846
1847 UPDATE ap_supplier_sites_all
1848 SET
1849 last_update_date = p_Last_Update_Date,
1850 last_updated_by = p_Last_Updated_By,
1851 vendor_id = p_vendor_site_rec.Vendor_Id,
1852 vendor_site_code = p_vendor_site_rec.Vendor_Site_Code,
1853 last_update_login = p_Last_Update_Login,
1854 purchasing_site_flag = p_vendor_site_rec.Purchasing_Site_Flag,
1855 rfq_only_site_flag = p_vendor_site_rec.Rfq_Only_Site_Flag,
1856 pay_site_flag = p_vendor_site_rec.Pay_Site_Flag,
1857 attention_ar_flag = p_vendor_site_rec.Attention_Ar_Flag,
1858 area_code = p_vendor_site_rec.Area_Code,
1859 phone = p_vendor_site_rec.Phone,
1860 customer_num = p_vendor_site_rec.Customer_Num,
1861 ship_to_location_id = p_vendor_site_rec.Ship_To_Location_Id,
1862 bill_to_location_id = p_vendor_site_rec.Bill_To_Location_Id,
1863 ship_via_lookup_code = p_vendor_site_rec.Ship_Via_Lookup_Code,
1864 freight_terms_lookup_code = p_vendor_site_rec.Freight_Terms_Lookup_Code,
1865 fob_lookup_code = p_vendor_site_rec.Fob_Lookup_Code,
1866 inactive_date = p_vendor_site_rec.Inactive_Date,
1867 fax = p_vendor_site_rec.Fax,
1868 fax_area_code = p_vendor_site_rec.Fax_Area_Code,
1869 telex = p_vendor_site_rec.Telex,
1870 terms_date_basis = p_vendor_site_rec.Terms_Date_Basis,
1871 distribution_set_id = p_vendor_site_rec.Distribution_Set_Id,
1872 accts_pay_code_combination_id = p_vendor_site_rec.Accts_Pay_Code_Combination_ID,
1873 future_dated_payment_ccid = p_vendor_site_rec.Future_Dated_Payment_CCID,
1874 prepay_code_combination_id = p_vendor_site_rec.Prepay_Code_Combination_Id,
1875 pay_group_lookup_code = p_vendor_site_rec.Pay_Group_Lookup_Code,
1876 payment_priority = p_vendor_site_rec.Payment_Priority,
1877 terms_id = p_vendor_site_rec.Terms_Id,
1878 invoice_amount_limit = p_vendor_site_rec.Invoice_Amount_Limit,
1879 pay_date_basis_lookup_code = p_vendor_site_rec.Pay_Date_Basis_Lookup_Code,
1880 always_take_disc_flag = p_vendor_site_rec.Always_Take_Disc_Flag,
1881 invoice_currency_code = p_vendor_site_rec.Invoice_Currency_Code,
1882 payment_currency_code = p_vendor_site_rec.Payment_Currency_Code,
1883 hold_all_payments_flag = p_vendor_site_rec.Hold_All_Payments_Flag,
1884 hold_future_payments_flag = p_vendor_site_rec.Hold_Future_Payments_Flag,
1885 hold_reason = p_vendor_site_rec.Hold_Reason,
1886 hold_unmatched_invoices_flag = p_vendor_site_rec.Hold_Unmatched_Invoices_Flag,
1887 match_option = p_vendor_site_rec.Match_Option,
1888 create_debit_memo_flag = p_vendor_site_rec.Create_Debit_Memo_Flag,
1889 tax_reporting_site_flag = p_vendor_site_rec.Tax_Reporting_Site_Flag,
1890 attribute_category = p_vendor_site_rec.Attribute_Category,
1891 attribute1 = p_vendor_site_rec.Attribute1,
1892 attribute2 = p_vendor_site_rec.Attribute2,
1893 attribute3 = p_vendor_site_rec.Attribute3,
1894 attribute4 = p_vendor_site_rec.Attribute4,
1895 attribute5 = p_vendor_site_rec.Attribute5,
1896 attribute6 = p_vendor_site_rec.Attribute6,
1897 attribute7 = p_vendor_site_rec.Attribute7,
1898 attribute8 = p_vendor_site_rec.Attribute8,
1899 attribute9 = p_vendor_site_rec.Attribute9,
1900 attribute10 = p_vendor_site_rec.Attribute10,
1901 attribute11 = p_vendor_site_rec.Attribute11,
1902 attribute12 = p_vendor_site_rec.Attribute12,
1903 attribute13 = p_vendor_site_rec.Attribute13,
1904 attribute14 = p_vendor_site_rec.Attribute14,
1905 attribute15 = p_vendor_site_rec.Attribute15,
1906 validation_number = p_vendor_site_rec.Validation_Number,
1907 exclude_freight_from_discount = p_vendor_site_rec.Exclude_Freight_From_Discount,
1908 check_digits = p_vendor_site_rec.Check_Digits,
1909 allow_awt_flag = p_vendor_site_rec.Allow_Awt_Flag,
1910 awt_group_id = p_vendor_site_rec.Awt_Group_Id,
1911 pay_awt_group_id = p_vendor_site_rec.Pay_Awt_Group_Id,--bug6664407
1912 pay_on_code = p_vendor_site_rec.pay_on_code,
1913 default_pay_site_id = p_vendor_site_rec.default_pay_site_id,
1917 global_attribute1 = p_vendor_site_rec.global_attribute1,
1914 pay_on_receipt_summary_code = p_vendor_site_rec.pay_on_receipt_summary_code,
1915 vendor_site_code_alt = p_vendor_site_rec.Vendor_Site_Code_Alt,
1916 global_attribute_category = p_vendor_site_rec.global_attribute_category,
1918 global_attribute2 = p_vendor_site_rec.global_attribute2,
1919 global_attribute3 = p_vendor_site_rec.global_attribute3,
1920 global_attribute4 = p_vendor_site_rec.global_attribute4,
1921 global_attribute5 = p_vendor_site_rec.global_attribute5,
1922 global_attribute6 = p_vendor_site_rec.global_attribute6,
1923 global_attribute7 = p_vendor_site_rec.global_attribute7,
1924 global_attribute8 = p_vendor_site_rec.global_attribute8,
1925 global_attribute9 = p_vendor_site_rec.global_attribute9,
1926 global_attribute10 = p_vendor_site_rec.global_attribute10,
1927 global_attribute11 = p_vendor_site_rec.global_attribute11,
1928 global_attribute12 = p_vendor_site_rec.global_attribute12,
1929 global_attribute13 = p_vendor_site_rec.global_attribute13,
1930 global_attribute14 = p_vendor_site_rec.global_attribute14,
1931 global_attribute15 = p_vendor_site_rec.global_attribute15,
1932 global_attribute16 = p_vendor_site_rec.global_attribute16,
1933 global_attribute17 = p_vendor_site_rec.global_attribute17,
1934 global_attribute18 = p_vendor_site_rec.global_attribute18,
1935 global_attribute19 = p_vendor_site_rec.global_attribute19,
1936 global_attribute20 = p_vendor_site_rec.global_attribute20,
1937 Bank_Charge_Bearer = p_vendor_site_rec.Bank_Charge_Bearer,
1938 Ece_Tp_Location_Code = p_vendor_site_rec.Ece_Tp_Location_Code,
1939 Country_of_Origin_Code = p_vendor_site_rec.Country_of_Origin_Code,
1940 Pcard_Site_Flag = p_vendor_site_rec.Pcard_Site_Flag,
1941 Supplier_Notif_Method = p_vendor_site_rec.Supplier_Notif_Method,
1942 Email_Address = p_vendor_site_rec.Email_Address,
1943 Primary_pay_site_flag = p_vendor_site_rec.Primary_pay_site_flag,
1944 Location_Id = p_vendor_site_rec.location_id,
1945 Party_Site_ID = p_vendor_site_rec.party_site_id,
1946 Tolerance_Id = p_vendor_site_rec.tolerance_id,
1947 Retainage_Rate = p_vendor_site_rec.retainage_rate,
1948 Shipping_Control = p_vendor_site_rec.shipping_control,
1949 services_tolerance_id = p_vendor_site_rec.services_tolerance_id,
1950 gapless_inv_num_flag = p_vendor_site_rec.gapless_inv_num_flag,
1951 selling_company_identifier = p_vendor_site_rec.selling_company_identifier,
1952 duns_number = p_vendor_site_rec.duns_number, --bug6388041
1953 -- Bug 7300553 Start
1954 address_line1 = p_vendor_site_rec.address_line1,
1955 address_line2 = p_vendor_site_rec.address_line2,
1956 address_line3 = p_vendor_site_rec.address_line3,
1957 address_line4 = p_vendor_site_rec.address_line4,
1958 city = p_vendor_site_rec.city,
1959 state = p_vendor_site_rec.state,
1960 zip = p_vendor_site_rec.zip,
1961 province = p_vendor_site_rec.province,
1962 country = p_vendor_site_rec.country,
1963 county = p_vendor_site_rec.county,
1964 address_style = p_vendor_site_rec.address_style,
1965 language = p_vendor_site_rec.language,
1966 address_lines_alt = p_vendor_site_rec.address_lines_alt,
1967 -- Bug 7300553 End
1968 edi_id_number = p_vendor_site_rec.edi_id_number -- bug 7437549
1969 WHERE vendor_site_id = p_vendor_site_id;
1970
1971 if (SQL%NOTFOUND) then
1972 Raise NO_DATA_FOUND;
1973 end if;
1974
1975 debug_info := 'Update values in PO_LOCATION_ASSOCIATIONS';
1976 if (p_vendor_site_rec.Shipping_Location_id is not null) then
1977
1978 ap_po_locn_association_pkg.update_row(
1979 p_location_id => p_vendor_site_rec.Shipping_Location_id,
1980 /* 5945837: Shipping_Location_id is stored in po_location_associations*/
1981 p_vendor_id => p_vendor_site_rec.Vendor_Id,
1982 p_vendor_site_id => P_Vendor_Site_Id,
1983 p_last_update_date => p_Last_Update_Date,
1984 p_last_updated_by => p_Last_Updated_By,
1985 p_last_update_login => p_Last_Update_Login,
1986 p_creation_date => sysdate,
1987 p_created_by => -1,
1988 p_org_id => p_vendor_site_rec.Org_ID);
1989 end if;
1990
1991 EXCEPTION
1992 WHEN OTHERS THEN
1993 IF (SQLCODE <> -20001) THEN
1994 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1995 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1999 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
1996 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
1997 FND_MESSAGE.SET_TOKEN('PARAMETERS','VENDOR_SITE_ID = ' ||
1998 p_Vendor_Site_Id );
2000 END IF;
2001 APP_EXCEPTION.RAISE_EXCEPTION;
2002
2003 END Update_Row;
2004
2005 --
2006 --
2007 procedure Check_vendor_offsets ( x_vendor_id in number,
2008 x_calling_sequence in varchar2) is
2009 l_offset_count number;
2010 current_calling_sequence varchar2(2000);
2011 debug_info varchar2(100);
2012 begin
2013 -- Update the calling sequence
2014 --
2015 current_calling_sequence := 'AP_VENDOR_SITES_PKG.CHECK_VENDOR_OFFSETS
2016 <-' ||
2017 X_calling_sequence;
2018
2019 debug_info := 'Count sites with offset';
2020 SELECT count(1)
2021 INTO l_offset_count
2022 FROM ap_supplier_sites
2023 WHERE vendor_id = x_vendor_id
2024 AND nvl(offset_tax_flag, 'N') <> 'N';
2025 --
2026 if (l_offset_count > 0 ) then
2027 fnd_message.set_name('SQLAP','AP_CLEAR_SITE_OFFSET');
2028 app_exception.raise_exception;
2029 end if;
2030 EXCEPTION
2031 WHEN OTHERS THEN
2032 IF (SQLCODE <> -20001) THEN
2033 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
2034 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
2035 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence)
2036 ;
2037 FND_MESSAGE.SET_TOKEN('PARAMETERS','VENDOR_ID = ' ||
2038 X_Vendor_Id);
2039 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
2040 END IF;
2041 APP_EXCEPTION.RAISE_EXCEPTION;
2042
2043 END CHECK_VENDOR_OFFSETS;
2044
2045 --
2046 --
2047 procedure Check_duplicate_vendor_site ( x_vendor_id in number,
2048 x_vendor_site_code in varchar2,
2049 --MO Access Control
2050 x_org_id in number
2051 DEFAULT mo_global.get_current_org_id ,
2052 x_rowid in varchar2,
2053 X_calling_sequence in varchar2) is
2054 L_Duplicate_count number;
2055 current_calling_sequence varchar2(2000);
2056 debug_info varchar2(100);
2057 begin
2058 -- Update the calling sequence
2059 --
2060 current_calling_sequence := 'AP_VENDOR_SITES_PKG.CHECK_DUPLICATE_VENDOR_SITE<-' ||
2061 X_calling_sequence;
2062
2063 debug_info := 'Count duplicates for vendor_id and site_code';
2064 SELECT count(1)
2065 INTO L_Duplicate_count
2066 FROM po_vendor_sites
2067 WHERE (rowid <> x_rowid or x_rowid IS NULL)
2068 AND vendor_id = x_vendor_id
2069 AND UPPER(vendor_site_code) = UPPER(x_vendor_site_code)
2070 AND nvl(org_id,-99) = nvl(x_org_id,-99); --MO Access Control
2071
2072 if (L_duplicate_count > 0 ) then
2073 fnd_message.set_name('SQLAP','AP_VEN_DUPLICATE_VEN_SITE');
2074 app_exception.raise_exception;
2075 end if;
2076 EXCEPTION
2077 WHEN OTHERS THEN
2078 IF (SQLCODE <> -20001) THEN
2079 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
2080 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
2081 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
2082 FND_MESSAGE.SET_TOKEN('PARAMETERS','VENDOR_ID = ' || x_vendor_id ||
2083 ', VENDOR_SITE_CODE = ' || x_vendor_site_code ||
2084 ', ROWID = ' || x_rowid);
2085 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
2086 END IF;
2087 APP_EXCEPTION.RAISE_EXCEPTION;
2088
2089 end Check_duplicate_vendor_site;
2090 --
2091 --
2092 procedure Check_Multiple_Tax_Sites( x_vendor_id in number,
2093 x_vendor_site_id in number,
2094 --MO Access Control
2095 x_org_id in number
2096 DEFAULT mo_global.get_current_org_id,
2097 X_calling_sequence in varchar2 ) is
2098 L_Multiple_count number;
2099 current_calling_sequence varchar2(2000);
2100 debug_info varchar2(100);
2101 begin
2102 -- Update the calling sequence
2103 --
2104 current_calling_sequence := 'AP_VENDOR_SITES_PKG.CHECK_MULTIPLE_TAX_SITES<-' ||
2105 X_calling_sequence;
2106 debug_info := 'Count tax reporting sites (vendor_id = ' || x_vendor_id || ')';
2107 SELECT count(1)
2108 INTO L_Multiple_count
2109 FROM ap_supplier_sites
2110 WHERE vendor_id = x_vendor_id
2111 AND tax_reporting_site_flag = 'Y'
2112 AND vendor_site_id <> nvl(x_vendor_site_id,-999)
2113 AND nvl(org_id,-99) = nvl(x_org_id,-99); --MO Access Control
2114
2115 if (L_multiple_count > 0 ) then
2116 fnd_message.set_name('SQLAP','AP_VEN_ERROR_TAX_SITE');
2117 app_exception.raise_exception;
2118 end if;
2119 EXCEPTION
2120 WHEN OTHERS THEN
2121 IF (SQLCODE <> -20001) THEN
2122 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
2123 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
2124 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
2125 FND_MESSAGE.SET_TOKEN('PARAMETERS','VENDOR_SITE_ID = ' ||
2129 END IF;
2126 X_Vendor_Site_Id ||
2127 ', VENDOR_ID = ' || x_vendor_id);
2128 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
2130 APP_EXCEPTION.RAISE_EXCEPTION;
2131
2132 end Check_Multiple_Tax_sites;
2133 --
2134 --
2135 Procedure Check_Site_Currencies( x_vendor_id in number,
2136 x_base_Currency_Code in varchar2,
2137 X_calling_sequence in varchar2) is
2138 L_overlap_count number;
2139 current_calling_sequence varchar2(2000);
2140 debug_info varchar2(100);
2141 begin
2142 -- Update the calling sequence
2143 --
2144 current_calling_sequence := 'AP_VENDOR_SITES_PKG.CHECK_SITE_CURRENCIES<-' ||
2145 X_calling_sequence;
2146
2147 debug_info := 'Count sites not in base currency for vendor_id';
2148 SELECT count(1)
2149 INTO L_overlap_count
2150 FROM po_vendor_sites
2151 WHERE vendor_id = x_vendor_id
2152 AND (invoice_currency_code <> x_Base_Currency_Code
2153 OR invoice_currency_code IS NULL);
2154
2155 if (L_overlap_count > 0 ) then
2156 fnd_message.set_name('SQLAP','AP_AWT_SITES_NOT_BASE');
2157 app_exception.raise_exception;
2158 end if;
2159 EXCEPTION
2160 WHEN OTHERS THEN
2161 IF (SQLCODE <> -20001) THEN
2162 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
2163 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
2164 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
2165 FND_MESSAGE.SET_TOKEN('PARAMETERS','VENDOR_ID = ' || X_Vendor_Id ||
2166 ', BASE_CURRENCY_CODE = ' ||
2167 x_base_currency_code);
2168 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
2169 END IF;
2170 APP_EXCEPTION.RAISE_EXCEPTION;
2171
2172 end Check_Site_Currencies;
2173 --
2174 --
2175 procedure Get_Tax_Site( x_vendor_id in number,
2176 x_vendor_site_code in out NOCOPY varchar2,
2177 --MO Access Control
2178 x_org_id in number
2179 Default mo_global.get_current_org_id ,
2180 x_calling_sequence in varchar2 ) is
2181
2182 current_calling_sequence varchar2(2000);
2183 debug_info varchar2(100);
2184 begin
2185 -- Update the calling sequence
2186 --
2187 current_calling_sequence := 'AP_VENDOR_SITES_PKG.GET_TAX_SITE<-' ||
2188 X_calling_sequence;
2189
2190 debug_info := 'Return Vendor Site ID for tax reporting site';
2191 -- use table since policy context can be set in
2192 -- the suppliers form to an OU different from the one we want to
2193 -- select the tax site
2194
2195 SELECT vendor_site_code
2196 INTO x_vendor_site_code
2197 FROM po_vendor_sites_all
2198 WHERE vendor_id = x_vendor_id
2199 AND nvl(org_id,-99) = nvl(x_org_id,-99)
2200 AND Tax_Reporting_Site_Flag = 'Y';
2201
2202 EXCEPTION
2203 WHEN NO_DATA_FOUND THEN
2204 x_vendor_site_code := '';
2205 WHEN OTHERS THEN
2206 IF (SQLCODE <> -20001) THEN
2207 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
2208 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
2209 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
2210 FND_MESSAGE.SET_TOKEN('PARAMETERS','VENDOR_ID = ' || x_vendor_id);
2211 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
2212 END IF;
2213 APP_EXCEPTION.RAISE_EXCEPTION;
2214
2215 end get_tax_Site;
2216
2217
2218 Procedure Check_State_Codes( x_vendor_id in number,
2219 x_calling_sequence in varchar2) is
2220
2221 current_calling_sequence varchar2(2000);
2222 debug_info varchar2(100);
2223 L_Return_Count number;
2224 begin
2225 -- Update the calling sequence
2226 --
2227 current_calling_sequence := 'AP_VENDOR_SITES_PKG.CHECK_STATE_CODES<-' ||
2228 X_calling_sequence;
2229
2230 debug_info := 'Check that all Site State Codes are valid for US sites';
2231
2232 SELECT count(1)
2233 INTO L_Return_Count
2234 FROM po_vendor_sites
2235 WHERE vendor_id = x_vendor_id
2236 AND country = 'US'
2237 AND nvl(state,'99') not in (SELECT region_short_name
2238 FROM AP_income_tax_regions);
2239 if (L_Return_count > 0 ) then
2240 fnd_message.set_name('SQLAP','AP_VEN_WARN_INV_STATE');
2241 app_exception.raise_exception;
2242 end if;
2243
2244 EXCEPTION
2245 WHEN OTHERS THEN
2246 IF (SQLCODE <> -20001) THEN
2247 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
2248 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
2249 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
2250 FND_MESSAGE.SET_TOKEN('PARAMETERS','VENDOR_ID = ' ||X_Vendor_Id);
2251 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
2252 END IF;
2253 APP_EXCEPTION.RAISE_EXCEPTION;
2254
2255 end Check_State_Codes;
2256 --
2257 --
2258 procedure Check_duplicate_ece_code ( x_vendor_id in number,
2259 x_ece_tp_location_code in varchar2,
2260 x_rowid in varchar2,
2261 --MO Access Control
2262 x_org_id in varchar2
2263 Default mo_global.get_current_org_id ,
2264 X_calling_sequence in varchar2) is
2265 L_Duplicate_count number;
2266 current_calling_sequence varchar2(2000);
2267 debug_info varchar2(100);
2268 begin
2269 -- Update the calling sequence
2270 --
2271 current_calling_sequence := 'AP_VENDOR_SITES_PKG.CHECK_DUPLICATE_ECE_CODE<-' ||
2272 X_calling_sequence;
2273
2274 debug_info := 'Count duplicates for vendor_id and site_code';
2275 SELECT count(1)
2276 INTO L_Duplicate_count
2277 FROM po_vendor_sites
2278 WHERE (rowid <> x_rowid or x_rowid IS NULL)
2279 AND vendor_id = x_vendor_id
2280 AND UPPER(ece_tp_location_code) = UPPER(x_ece_tp_location_code)
2281 --MO Access Control
2282 AND nvl(org_id,-99) = nvl(x_org_id,-99);
2283
2284 if (L_duplicate_count > 0 ) then
2285 fnd_message.set_name('SQLAP','AP_VEN_DUPLICATE_ECE_CODE');
2286 app_exception.raise_exception;
2287 end if;
2288 EXCEPTION
2289 WHEN OTHERS THEN
2290 IF (SQLCODE <> -20001) THEN
2291 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
2292 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
2293 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
2294 FND_MESSAGE.SET_TOKEN('PARAMETERS','VENDOR_ID = ' || x_vendor_id ||
2295 ', ECE_TP_LOCATION_CODE = ' || x_ece_tp_location_code ||
2296 ', ROWID = ' || x_rowid);
2297 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
2298 END IF;
2299 APP_EXCEPTION.RAISE_EXCEPTION;
2300
2301 end Check_duplicate_ece_code;
2302
2303
2304
2305 END AP_VENDOR_SITES_PKG;