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