[Home] [Help]
PACKAGE BODY: APPS.AP_VENDORS_PKG
Source
1 PACKAGE BODY AP_VENDORS_PKG as
2 /* $Header: apvndhrb.pls 120.15.12010000.3 2008/12/29 07:57:16 mayyalas ship $ */
3 --
4 --
5 FUNCTION Update_Product_Setup Return Number;
6
7 PROCEDURE Insert_Row (
8 x_Rowid IN OUT NOCOPY VARCHAR2,
9 x_Vendor_Id IN OUT NOCOPY NUMBER,
10 x_Last_Update_Date DATE,
11 x_Last_Updated_By NUMBER,
12 x_Vendor_Name VARCHAR2,
13 x_Segment1 IN OUT NOCOPY VARCHAR2,
14 x_Summary_Flag VARCHAR2,
15 x_Enabled_Flag VARCHAR2,
16 x_Last_Update_Login NUMBER,
17 x_Creation_Date DATE,
18 x_Created_By NUMBER,
19 x_Employee_Id NUMBER,
20 x_Validation_Number NUMBER,
21 x_Vendor_Type_Lookup_Code VARCHAR2,
22 x_Customer_Num VARCHAR2,
23 x_One_Time_Flag VARCHAR2,
24 x_Parent_Vendor_Id NUMBER,
25 x_Min_Order_Amount NUMBER,
26 --Bug :2809214 MOAC - Supplier Attribute Change Project
27 /* x_Ship_To_Location_ID NUMBER,
28 x_Bill_To_Location_Id NUMBER,
29 x_Ship_Via_Lookup_Code VARCHAR2,
30 x_Freight_Terms_Lookup_Code VARCHAR2,
31 x_Fob_Lookup_Code VARCHAR2, */
32 x_Terms_Id NUMBER,
33 x_Set_Of_Books_Id NUMBER,
34 x_Always_Take_Disc_Flag VARCHAR2,
35 x_Pay_Date_Basis_Lookup_Code VARCHAR2,
36 x_Pay_Group_Lookup_Code VARCHAR2,
37 x_Payment_Priority NUMBER,
38 x_Invoice_Currency_Code VARCHAR2,
39 x_Payment_Currency_Code VARCHAR2,
40 x_Invoice_Amount_Limit NUMBER,
41 x_Hold_All_Payments_Flag VARCHAR2,
42 x_Hold_Future_Payments_Flag VARCHAR2,
43 x_Hold_Reason VARCHAR2,
44 --Bug :2809214 MOAC - Supplier Attribute Change Project
45 /* x_Distribution_Set_Id NUMBER,
46 x_Accts_Pay_CCID NUMBER,
47 x_Future_Dated_Payment_CCID NUMBER,
48 x_Prepay_CCID NUMBER, */
49 x_Num_1099 VARCHAR2,
50 x_Type_1099 VARCHAR2,
51 x_withholding_stat_Lookup_Code VARCHAR2,
52 x_Withholding_Start_Date DATE,
53 x_Org_Type_Lookup_Code VARCHAR2,
54 -- eTax Uptake x_Vat_Code VARCHAR2,
55 x_Start_Date_Active DATE,
56 x_End_Date_Active DATE,
57 x_Qty_Rcv_Tolerance NUMBER,
58 x_Minority_Group_Lookup_Code VARCHAR2,
59 --4552701 x_Payment_Method_Lookup_Code VARCHAR2,
60 x_Bank_Account_Name VARCHAR2,
61 x_Bank_Account_Num VARCHAR2,
62 x_Bank_Num VARCHAR2,
63 x_Bank_Account_Type VARCHAR2,
64 x_Women_Owned_Flag VARCHAR2,
65 x_Small_Business_Flag VARCHAR2,
66 x_Standard_Industry_Class VARCHAR2,
67 x_Attribute_Category VARCHAR2,
68 x_Attribute1 VARCHAR2,
69 x_Attribute2 VARCHAR2,
70 x_Attribute3 VARCHAR2,
71 x_Attribute4 VARCHAR2,
72 x_Attribute5 VARCHAR2,
73 x_Hold_Flag VARCHAR2,
74 x_Purchasing_Hold_Reason VARCHAR2,
75 x_Hold_By NUMBER,
76 x_Hold_Date DATE,
77 x_Terms_Date_Basis VARCHAR2,
78 x_Price_Tolerance NUMBER,
79 x_Attribute10 VARCHAR2,
80 x_Attribute11 VARCHAR2,
81 x_Attribute12 VARCHAR2,
82 x_Attribute13 VARCHAR2,
83 x_Attribute14 VARCHAR2,
84 x_Attribute15 VARCHAR2,
85 x_Attribute6 VARCHAR2,
86 x_Attribute7 VARCHAR2,
87 x_Attribute8 VARCHAR2,
88 x_Attribute9 VARCHAR2,
89 x_Days_Early_Receipt_Allowed NUMBER,
90 x_Days_Late_Receipt_Allowed NUMBER,
91 x_Enforce_Ship_To_Loc_Code VARCHAR2,
92 --4552701 x_Exclusive_Payment_Flag VARCHAR2,
93 x_Federal_Reportable_Flag VARCHAR2,
94 x_Hold_Unmatched_Invoices_Flag VARCHAR2,
95 x_match_option VARCHAR2,
96 x_create_debit_memo_flag VARCHAR2,
97 x_Inspection_Required_Flag VARCHAR2,
98 x_Receipt_Required_Flag VARCHAR2,
99 x_Receiving_Routing_Id NUMBER,
100 x_State_Reportable_Flag VARCHAR2,
101 x_Tax_Verification_Date DATE,
102 x_Auto_Calculate_Interest_Flag VARCHAR2,
103 x_Name_Control VARCHAR2,
104 x_Allow_Subst_Receipts_Flag VARCHAR2,
105 x_Allow_Unord_Receipts_Flag VARCHAR2,
106 x_Receipt_Days_Exception_Code VARCHAR2,
107 x_Qty_Rcv_Exception_Code VARCHAR2,
108 -- eTax Uptake x_Offset_Tax_Flag VARCHAR2,
109 x_Exclude_Freight_From_Disc VARCHAR2,
110 x_Vat_Registration_Num VARCHAR2,
111 x_Tax_Reporting_Name VARCHAR2,
112 x_Awt_Group_Id NUMBER,
113 x_Pay_Awt_Group_Id NUMBER,--bug6664407
114 x_Check_Digits VARCHAR2,
115 x_Bank_Number VARCHAR2,
116 x_Allow_Awt_Flag VARCHAR2,
117 x_Bank_Branch_Type VARCHAR2,
118 /* 4552701
119 x_EDI_Payment_Method VARCHAR2,
120 x_EDI_Payment_Format VARCHAR2,
121 x_EDI_Remittance_Method VARCHAR2,
122 x_EDI_Remittance_Instruction VARCHAR2,
123 x_EDI_transaction_handling VARCHAR2,
124 eTax Uptake
125 x_Auto_Tax_Calc_Flag VARCHAR2,
126 x_Auto_Tax_Calc_Override VARCHAR2,
127 x_Amount_Includes_Tax_Flag VARCHAR2,
128 x_AP_Tax_Rounding_Rule VARCHAR2,*/
129 x_Vendor_Name_Alt VARCHAR2,
130 X_global_attribute_category VARCHAR2 DEFAULT NULL,
131 X_global_attribute1 VARCHAR2 DEFAULT NULL,
132 X_global_attribute2 VARCHAR2 DEFAULT NULL,
133 X_global_attribute3 VARCHAR2 DEFAULT NULL,
134 X_global_attribute4 VARCHAR2 DEFAULT NULL,
135 X_global_attribute5 VARCHAR2 DEFAULT NULL,
136 X_global_attribute6 VARCHAR2 DEFAULT NULL,
137 X_global_attribute7 VARCHAR2 DEFAULT NULL,
138 X_global_attribute8 VARCHAR2 DEFAULT NULL,
139 X_global_attribute9 VARCHAR2 DEFAULT NULL,
140 X_global_attribute10 VARCHAR2 DEFAULT NULL,
141 X_global_attribute11 VARCHAR2 DEFAULT NULL,
142 X_global_attribute12 VARCHAR2 DEFAULT NULL,
143 X_global_attribute13 VARCHAR2 DEFAULT NULL,
144 X_global_attribute14 VARCHAR2 DEFAULT NULL,
145 X_global_attribute15 VARCHAR2 DEFAULT NULL,
146 X_global_attribute16 VARCHAR2 DEFAULT NULL,
147 X_global_attribute17 VARCHAR2 DEFAULT NULL,
148 X_global_attribute18 VARCHAR2 DEFAULT NULL,
149 X_global_attribute19 VARCHAR2 DEFAULT NULL,
150 X_global_attribute20 VARCHAR2 DEFAULT NULL,
151 X_Bank_Charge_Bearer VARCHAR2 DEFAULT NULL,
152 X_NI_Number VARCHAR2 DEFAULT NULL,
153 X_calling_sequence IN VARCHAR2 ) IS
154
155 CURSOR C IS
156 SELECT rowid
157 FROM po_vendors
158 WHERE vendor_id = x_Vendor_Id;
159
160 current_calling_sequence VARCHAR2(2000);
161 debug_info VARCHAR2(100);
162 l_supplier_numbering_method VARCHAR2(25);
163
164 BEGIN
165 -- Update the calling sequence
166 --
167 current_calling_sequence := 'AP_VENDORS_PKG.INSERT_ROW<-' ||
168 X_calling_sequence;
169
170 --
171 -- error out NOCOPY if Vendor name has been assigned by another session
172 --
173 ap_vendors_pkg.check_unique_vendor_name( p_vendor_id => x_vendor_id,
174 p_vendor_name => x_vendor_name,
175 X_calling_sequence => current_calling_sequence );
176 --
177 -- error out NOCOPY if employee has been assigned by another session
178 --
179 ap_vendors_pkg.Check_Duplicate_Employee(p_rowid => x_rowid,
180 p_employee_id => x_employee_id,
181 X_calling_sequence => current_calling_sequence);
182 --
183 -- assign automatic Vendor number if needed
184 --
185 --Bug :2809214 MOAC - Supplier Attribute Change Project
186 /* select user_defined_vendor_num_code
187 into l_ven_num_code
188 from financials_system_parameters; */
189
190 select supplier_numbering_method
191 into l_supplier_numbering_method
192 from ap_product_setup;
193
194 if (nvl(l_supplier_numbering_method,'AUTOMATIC') = 'AUTOMATIC') then
195
196 debug_info := 'assign automatic Vendor number';
197
198 --Bug :2809214 MOAC - Supplier Attribute Change Project
199 --Replaced the 2 SQLs with the below 2 SQLs.
200 /* UPDATE PO_UNIQUE_IDENTIFIER_CONTROL
201 SET current_max_unique_identifier = current_max_unique_identifier + 1
202 WHERE table_name = 'PO_VENDORS';
203
204 --
205 --
206 debug_info := 'Select current_max_unique_identifier';
207 SELECT current_max_unique_identifier
208 INTO x_segment1
209 FROM po_unique_identifier_control
210 WHERE table_name = 'PO_VENDORS'; */
211
212 -- Bug 6830122. Replacing following two statements with
213 -- autonomus transactions
214 /*SELECT next_auto_supplier_num
215 INTO x_segment1
216 FROM ap_product_setup ;
217
218 UPDATE ap_product_setup
219 SET next_auto_supplier_num = next_auto_supplier_num + 1;*/
220 x_segment1 := Update_Product_Setup;
221
222 end if;
223 --
224 --
225 ap_vendors_pkg.check_unique_vendor_number( p_vendor_id => x_vendor_id,
226 p_vendor_number => x_segment1,
227 X_calling_sequence => current_calling_sequence );
228 --
229 --
230 debug_info := 'Select next sequence value from PO_VENDORS_S';
231 Select PO_VENDORS_S.NEXTVAL
232 into x_vendor_id
233 from sys.dual;
234 --
235 --
236 debug_info := 'Insert into PO_VENDORS';
237 INSERT INTO ap_suppliers (
238 vendor_id,
239 last_update_date,
240 last_updated_by,
241 vendor_name,
242 segment1,
243 summary_flag,
244 enabled_flag,
245 last_update_login,
246 creation_date,
247 created_by,
248 employee_id,
249 validation_number,
250 vendor_type_lookup_code,
251 customer_num,
252 one_time_flag,
253 parent_vendor_id,
254 min_order_amount,
255 --Bug :2809214 MOAC - Supplier Attribute Change Project
256 /* ship_to_location_id,
257 bill_to_location_id,
258 ship_via_lookup_code,
259 freight_terms_lookup_code,
260 fob_lookup_code, */
261 terms_id,
262 set_of_books_id,
263 always_take_disc_flag,
264 pay_date_basis_lookup_code,
265 pay_group_lookup_code,
266 payment_priority,
267 invoice_currency_code,
268 payment_currency_code,
269 invoice_amount_limit,
270 hold_all_payments_flag,
271 hold_future_payments_flag,
272 hold_reason,
273 --Bug :2809214 MOAC - Supplier Attribute Change Project
274 /* distribution_set_id,
275 accts_pay_code_combination_id,
276 future_dated_payment_ccid,
277 prepay_code_combination_id, */
278 num_1099,
279 type_1099,
280 withholding_status_lookup_code,
281 withholding_start_date,
282 organization_type_lookup_code,
283 start_date_active,
284 end_date_active,
285 qty_rcv_tolerance,
286 minority_group_lookup_code,
287 bank_account_name,
288 bank_account_num,
289 bank_num,
290 bank_account_type,
291 women_owned_flag,
292 small_business_flag,
293 standard_industry_class,
294 attribute_category,
295 attribute1,
296 attribute2,
297 attribute3,
298 attribute4,
299 attribute5,
300 hold_flag,
301 purchasing_hold_reason,
302 hold_by,
303 hold_date,
304 terms_date_basis,
305 price_tolerance,
306 attribute10,
307 attribute11,
308 attribute12,
309 attribute13,
310 attribute14,
311 attribute15,
312 attribute6,
313 attribute7,
314 attribute8,
315 attribute9,
316 days_early_receipt_allowed,
317 days_late_receipt_allowed,
318 enforce_ship_to_location_code,
319 federal_reportable_flag,
320 hold_unmatched_invoices_flag,
321 match_option,
322 create_debit_memo_flag,
323 inspection_required_flag,
324 receipt_required_flag,
325 receiving_routing_id,
326 state_reportable_flag,
327 tax_verification_date,
328 auto_calculate_interest_flag,
329 name_control,
330 allow_substitute_receipts_flag,
331 allow_unordered_receipts_flag,
332 receipt_days_exception_code,
333 qty_rcv_exception_code,
334 exclude_freight_from_discount,
335 vat_registration_num,
336 tax_reporting_name,
337 awt_group_id,
338 pay_awt_group_id,--bug6664407
339 check_digits,
340 bank_number,
341 allow_awt_flag,
342 bank_branch_type,
343 vendor_name_alt,
344 global_attribute_category,
345 global_attribute1,
346 global_attribute2,
347 global_attribute3,
348 global_attribute4,
349 global_attribute5,
350 global_attribute6,
351 global_attribute7,
352 global_attribute8,
353 global_attribute9,
354 global_attribute10,
355 global_attribute11,
356 global_attribute12,
357 global_attribute13,
358 global_attribute14,
359 global_attribute15,
360 global_attribute16,
361 global_attribute17,
362 global_attribute18,
363 global_attribute19,
364 global_attribute20,
365 bank_charge_bearer,
366 --Bug :2809214 MOAC - Supplier Attribute Change Project
367 NI_Number)
368
369 VALUES (
370 x_Vendor_Id,
371 x_Last_Update_Date,
372 x_Last_Updated_By,
373 x_Vendor_Name,
374 x_Segment1,
375 'N',
376 'Y',
377 x_Last_Update_Login,
378 x_Creation_Date,
379 x_Created_By,
380 x_Employee_Id,
381 x_Validation_Number,
382 x_Vendor_Type_Lookup_Code,
383 x_Customer_Num,
384 x_One_Time_Flag,
385 x_Parent_Vendor_Id,
386 x_Min_Order_Amount,
387 --Bug :2809214 MOAC - Supplier Attribute Change Project
388 /* x_Ship_To_Location_Id,
389 x_Bill_To_Location_Id,
390 x_Ship_Via_Lookup_Code,
391 x_Freight_Terms_Lookup_Code,
392 x_Fob_Lookup_Code, */
393 x_Terms_Id,
394 x_Set_Of_Books_Id,
395 x_Always_Take_Disc_Flag,
396 x_Pay_Date_Basis_Lookup_Code,
397 x_Pay_Group_Lookup_Code,
398 x_Payment_Priority,
402 x_Hold_All_Payments_Flag,
399 x_Invoice_Currency_Code,
400 x_Payment_Currency_Code,
401 x_Invoice_Amount_Limit,
403 x_Hold_Future_Payments_Flag,
404 x_Hold_Reason,
405 --Bug :2809214 MOAC - Supplier Attribute Change Project
406 /* x_Distribution_Set_Id,
407 x_Accts_Pay_CCID,
408 x_Future_Dated_Payment_CCID,
409 x_Prepay_CCID, */
410 x_Num_1099,
411 x_Type_1099,
412 x_withholding_stat_Lookup_Code,
413 x_Withholding_Start_Date,
414 x_Org_Type_Lookup_Code,
415 x_Start_Date_Active,
416 x_End_Date_Active,
417 x_Qty_Rcv_Tolerance,
418 x_Minority_Group_Lookup_Code,
419 x_Bank_Account_Name,
420 x_Bank_Account_Num,
421 x_Bank_Num,
422 x_Bank_Account_Type,
423 x_Women_Owned_Flag,
424 x_Small_Business_Flag,
425 x_Standard_Industry_Class,
426 x_Attribute_Category,
427 x_Attribute1,
428 x_Attribute2,
429 x_Attribute3,
430 x_Attribute4,
431 x_Attribute5,
432 x_Hold_Flag,
433 x_Purchasing_Hold_Reason,
434 x_Hold_By,
435 x_Hold_Date,
436 x_Terms_Date_Basis,
437 x_Price_Tolerance,
438 x_Attribute10,
439 x_Attribute11,
440 x_Attribute12,
441 x_Attribute13,
442 x_Attribute14,
443 x_Attribute15,
444 x_Attribute6,
445 x_Attribute7,
446 x_Attribute8,
447 x_Attribute9,
448 x_Days_Early_Receipt_Allowed,
449 x_Days_Late_Receipt_Allowed,
450 x_Enforce_Ship_To_Loc_Code,
451 x_Federal_Reportable_Flag,
452 x_Hold_Unmatched_Invoices_Flag,
453 x_match_option,
454 x_create_debit_memo_flag,
455 x_Inspection_Required_Flag,
456 x_Receipt_Required_Flag,
457 x_Receiving_Routing_Id,
458 x_State_Reportable_Flag,
459 x_Tax_Verification_Date,
460 x_Auto_Calculate_Interest_Flag,
461 x_Name_Control,
462 x_Allow_Subst_Receipts_Flag,
463 x_Allow_Unord_Receipts_Flag,
464 x_Receipt_Days_Exception_Code,
465 x_Qty_Rcv_Exception_Code,
466 x_Exclude_Freight_From_Disc,
467 x_Vat_Registration_Num,
468 x_Tax_Reporting_Name,
469 x_Awt_Group_Id,
470 x_Pay_Awt_Group_Id,--bug6664407
471 x_Check_Digits,
472 x_Bank_Number,
473 x_Allow_Awt_Flag,
474 x_bank_branch_type,
475 x_Vendor_Name_Alt,
476 X_global_attribute_category,
477 X_global_attribute1,
478 X_global_attribute2,
479 X_global_attribute3,
480 X_global_attribute4,
481 X_global_attribute5,
482 X_global_attribute6,
483 X_global_attribute7,
484 X_global_attribute8,
485 X_global_attribute9,
486 X_global_attribute10,
487 X_global_attribute11,
488 X_global_attribute12,
489 X_global_attribute13,
490 X_global_attribute14,
491 X_global_attribute15,
492 X_global_attribute16,
493 X_global_attribute17,
494 X_global_attribute18,
495 X_global_attribute19,
496 X_global_attribute20,
497 X_Bank_Charge_Bearer,
498 --Bug :2809214 MOAC - Supplier Attribute Change Project
499 X_NI_Number);
500 --
501 debug_info := 'Open cursor C';
502 OPEN C;
503 debug_info := 'Fetch cursor C';
504 FETCH C INTO x_Rowid;
505 if (C%NOTFOUND) then
506 debug_info := 'Close cursor C - NOTFOUND';
507 CLOSE C;
508 Raise NO_DATA_FOUND;
509 end if;
510 debug_info := 'Close cursor C';
511 CLOSE C;
512
513 EXCEPTION
514 WHEN OTHERS THEN
515 IF (SQLCODE <> -20001) THEN
516 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
517 FND_MSG_PUB.ADD;
518 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
519 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
520 FND_MESSAGE.SET_TOKEN('PARAMETERS','ROWID = ' || X_Rowid ||
521 ', VENDOR_ID = ' || x_Vendor_Id);
522 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
523 END IF;
524 APP_EXCEPTION.RAISE_EXCEPTION;
525
526 END Insert_Row;
527
528 PROCEDURE Insert_Row(
529 p_vendor_rec IN AP_VENDOR_PUB_PKG.r_vendor_rec_type,
530 p_last_update_date IN DATE,
531 p_last_updated_by IN NUMBER,
532 p_last_update_login IN NUMBER,
533 p_creation_date IN DATE,
534 p_created_by IN NUMBER,
535 p_request_id IN NUMBER,
536 p_program_application_id IN NUMBER,
537 p_program_id IN NUMBER,
538 p_program_update_date IN DATE,
539 x_rowid OUT NOCOPY VARCHAR2,
540 x_vendor_id OUT NOCOPY NUMBER) IS
541
542 CURSOR C IS
543 SELECT rowid
544 FROM po_vendors
545 WHERE vendor_id = x_Vendor_Id;
546
547 current_calling_sequence VARCHAR2(2000);
548 debug_info VARCHAR2(100);
549 l_supplier_numbering_method VARCHAR2(25);
550 l_segment1 VARCHAR2(30);
551 BEGIN
552
553 select supplier_numbering_method
554 into l_supplier_numbering_method
555 from ap_product_setup;
556
557 if (nvl(l_supplier_numbering_method,'AUTOMATIC') = 'AUTOMATIC') then
561 -- Bug 6830122. Replacing following two statements with
558
559 debug_info := 'assign automatic Vendor number';
560
562 -- autonomus transactions
563
564 /*SELECT next_auto_supplier_num
565 INTO l_segment1
566 FROM ap_product_setup ;
567
568 UPDATE ap_product_setup
569 SET next_auto_supplier_num = next_auto_supplier_num + 1;*/
570 l_segment1 := Update_Product_Setup;
571
572 end if;
573
574 -- Bug 6940256 udhenuko check for duplicate vendor_numbers
575 check_unique_vendor_number( p_vendor_id => x_vendor_id,
576 p_vendor_number => l_segment1,
577 X_calling_sequence => current_calling_sequence );
578 -- Bug 6940256 End
579 --
580 --
581 debug_info := 'Select next sequence value from PO_VENDORS_S';
582 Select PO_VENDORS_S.NEXTVAL
583 into x_vendor_id
584 from sys.dual;
585 --
586 --
587
588 debug_info := 'Insert into ap_suppliers';
589 INSERT INTO ap_suppliers (
590 vendor_id,
591 last_update_date,
592 last_updated_by,
593 segment1,
594 summary_flag,
595 enabled_flag,
596 last_update_login,
597 creation_date,
598 created_by,
599 employee_id,
600 validation_number,
601 vendor_type_lookup_code,
602 customer_num,
603 standard_industry_class, -- Bug 5066199
604 one_time_flag,
605 parent_vendor_id,
606 min_order_amount,
607 terms_id,
608 set_of_books_id,
609 always_take_disc_flag,
610 pay_date_basis_lookup_code,
611 pay_group_lookup_code,
612 payment_priority,
613 invoice_currency_code,
614 payment_currency_code,
615 invoice_amount_limit,
616 hold_all_payments_flag,
617 hold_future_payments_flag,
618 hold_reason,
619 individual_1099,--bug6050423
620 type_1099,
621 withholding_status_lookup_code,
622 withholding_start_date,
623 organization_type_lookup_code,
624 start_date_active,
625 end_date_active,
626 qty_rcv_tolerance,
627 minority_group_lookup_code,
628 women_owned_flag,
629 small_business_flag,
630 attribute_category,
631 attribute1,
632 attribute2,
633 attribute3,
634 attribute4,
635 attribute5,
636 hold_flag,
637 purchasing_hold_reason,
638 hold_by,
639 hold_date,
640 terms_date_basis,
641 attribute10,
642 attribute11,
643 attribute12,
644 attribute13,
645 attribute14,
646 attribute15,
647 attribute6,
648 attribute7,
649 attribute8,
650 attribute9,
651 days_early_receipt_allowed,
652 days_late_receipt_allowed,
653 enforce_ship_to_location_code,
654 federal_reportable_flag,
655 hold_unmatched_invoices_flag,
656 match_option,
657 create_debit_memo_flag,
658 inspection_required_flag,
659 receipt_required_flag,
660 receiving_routing_id,
661 state_reportable_flag,
662 tax_verification_date,
663 auto_calculate_interest_flag,
664 name_control,
665 allow_substitute_receipts_flag,
666 allow_unordered_receipts_flag,
667 receipt_days_exception_code,
668 qty_rcv_exception_code,
669 exclude_freight_from_discount,
670 tax_reporting_name,
671 awt_group_id,
672 pay_awt_group_id,--bug6664407
673 check_digits,
674 allow_awt_flag,
675 global_attribute_category,
676 global_attribute1,
677 global_attribute2,
678 global_attribute3,
679 global_attribute4,
680 global_attribute5,
681 global_attribute6,
682 global_attribute7,
683 global_attribute8,
684 global_attribute9,
685 global_attribute10,
686 global_attribute11,
687 global_attribute12,
688 global_attribute13,
689 global_attribute14,
690 global_attribute15,
691 global_attribute16,
692 global_attribute17,
693 global_attribute18,
694 global_attribute19,
695 global_attribute20,
696 bank_charge_bearer,
697 party_id,
698 parent_party_id,
699 ni_number)
700 VALUES (
701 x_Vendor_Id,
702 p_Last_Update_Date,
703 p_Last_Updated_By,
704 decode(l_supplier_numbering_method, 'AUTOMATIC', l_segment1,
705 p_vendor_rec.segment1),
706 nvl(p_vendor_rec.summary_flag, 'N'),
707 nvl(p_vendor_rec.enabled_flag, 'Y'),
708 p_Last_Update_Login,
709 p_Creation_Date,
710 p_Created_By,
711 p_vendor_rec.Employee_Id,
712 p_vendor_rec.Validation_Number,
713 p_vendor_rec.Vendor_Type_Lookup_Code,
714 p_vendor_rec.Customer_Num,
715 p_vendor_rec.sic_code, -- Bug 5066199
716 p_vendor_rec.One_Time_Flag,
717 p_vendor_rec.Parent_Vendor_Id,
718 p_vendor_rec.Min_Order_Amount,
719 p_vendor_rec.Terms_Id,
720 p_vendor_rec.Set_Of_Books_Id,
721 p_vendor_rec.Always_Take_Disc_Flag,
722 p_vendor_rec.Pay_Date_Basis_Lookup_Code,
723 p_vendor_rec.Pay_Group_Lookup_Code,
724 p_vendor_rec.Payment_Priority,
725 p_vendor_rec.Invoice_Currency_Code,
729 p_vendor_rec.Hold_Future_Payments_Flag,
726 p_vendor_rec.Payment_Currency_Code,
727 p_vendor_rec.Invoice_Amount_Limit,
728 p_vendor_rec.Hold_All_Payments_Flag,
730 p_vendor_rec.Hold_Reason,
731 --bug6050423 starts.System inserts the taxpayer of
732 --non-employee individuals to the individual_1099 field.
733 --we donot insert any value to num_1099,becas we update
734 --it using the ap_tca_sync_pkg.sync_supplier
735 --bug6691916.commented the below decode statement and added
736 --the one below that.As per analysis,only organization type lookup
737 --code of individual or foreign individual are considered
738 --as individual suppliers
739 /*decode(UPPER(p_vendor_rec.Vendor_Type_Lookup_Code),'CONTRACTOR',
740 decode(UPPER(p_vendor_rec.Organization_Type_Lookup_Code),
741 'INDIVIDUAL',p_vendor_rec.jgzz_fiscal_code,
742 'FOREIGN INDIVIDUAL',p_vendor_rec.jgzz_fiscal_code,
743 'PARTNERSHIP',p_vendor_rec.jgzz_fiscal_code,
744 'INDIVIDUAL PARTNERSHIP',p_vendor_rec.jgzz_fiscal_code,
745 NULL),
746 NULL),*/
747 decode(UPPER(p_vendor_rec.Organization_Type_Lookup_Code),
748 'INDIVIDUAL',p_vendor_rec.jgzz_fiscal_code,
749 'FOREIGN INDIVIDUAL',p_vendor_rec.jgzz_fiscal_code,
750 NULL),
751 --bug6050423 ends
752 p_vendor_rec.Type_1099,
753 p_vendor_rec.withholding_status_Lookup_Code,
754 p_vendor_rec.Withholding_Start_Date,
755 p_vendor_rec.Organization_Type_Lookup_Code,
756 p_vendor_rec.Start_Date_Active,
757 p_vendor_rec.End_Date_Active,
758 p_vendor_rec.Qty_Rcv_Tolerance,
759 p_vendor_rec.Minority_Group_Lookup_Code,
760 p_vendor_rec.Women_Owned_Flag,
761 p_vendor_rec.Small_Business_Flag,
762 p_vendor_rec.Attribute_Category,
763 p_vendor_rec.Attribute1,
764 p_vendor_rec.Attribute2,
765 p_vendor_rec.Attribute3,
766 p_vendor_rec.Attribute4,
767 p_vendor_rec.Attribute5,
768 p_vendor_rec.Hold_Flag,
769 p_vendor_rec.Purchasing_Hold_Reason,
770 p_vendor_rec.Hold_By,
771 p_vendor_rec.Hold_Date,
772 p_vendor_rec.Terms_Date_Basis,
773 p_vendor_rec.Attribute10,
774 p_vendor_rec.Attribute11,
775 p_vendor_rec.Attribute12,
776 p_vendor_rec.Attribute13,
777 p_vendor_rec.Attribute14,
778 p_vendor_rec.Attribute15,
779 p_vendor_rec.Attribute6,
780 p_vendor_rec.Attribute7,
781 p_vendor_rec.Attribute8,
782 p_vendor_rec.Attribute9,
783 p_vendor_rec.Days_Early_Receipt_Allowed,
784 p_vendor_rec.Days_Late_Receipt_Allowed,
785 p_vendor_rec.Enforce_Ship_To_Location_Code,
786 p_vendor_rec.Federal_Reportable_Flag,
787 p_vendor_rec.Hold_Unmatched_Invoices_Flag,
788 p_vendor_rec.match_option,
789 p_vendor_rec.create_debit_memo_flag,
790 p_vendor_rec.Inspection_Required_Flag,
791 p_vendor_rec.Receipt_Required_Flag,
792 p_vendor_rec.Receiving_Routing_Id,
793 p_vendor_rec.State_Reportable_Flag,
794 p_vendor_rec.Tax_Verification_Date,
795 p_vendor_rec.Auto_Calculate_Interest_Flag,
796 p_vendor_rec.Name_Control,
797 p_vendor_rec.allow_substitute_receipts_flag,
798 p_vendor_rec.allow_unordered_receipts_flag,
799 p_vendor_rec.Receipt_Days_Exception_Code,
800 p_vendor_rec.Qty_Rcv_Exception_Code,
801 p_vendor_rec.Exclude_Freight_From_Discount,
802 p_vendor_rec.Tax_Reporting_Name,
803 p_vendor_rec.Awt_Group_Id,
804 p_vendor_rec.Pay_Awt_Group_Id,--bug6664407
805 p_vendor_rec.Check_Digits,
806 p_vendor_rec.Allow_Awt_Flag,
807 p_vendor_rec.global_attribute_category,
808 p_vendor_rec.global_attribute1,
809 p_vendor_rec.global_attribute2,
810 p_vendor_rec.global_attribute3,
811 p_vendor_rec.global_attribute4,
812 p_vendor_rec.global_attribute5,
813 p_vendor_rec.global_attribute6,
814 p_vendor_rec.global_attribute7,
815 p_vendor_rec.global_attribute8,
816 p_vendor_rec.global_attribute9,
817 p_vendor_rec.global_attribute10,
818 p_vendor_rec.global_attribute11,
819 p_vendor_rec.global_attribute12,
820 p_vendor_rec.global_attribute13,
821 p_vendor_rec.global_attribute14,
822 p_vendor_rec.global_attribute15,
823 p_vendor_rec.global_attribute16,
824 p_vendor_rec.global_attribute17,
825 p_vendor_rec.global_attribute18,
826 p_vendor_rec.global_attribute19,
827 p_vendor_rec.global_attribute20,
828 p_vendor_rec.Bank_Charge_Bearer,
829 p_vendor_rec.party_id,
830 p_vendor_rec.parent_party_id,
831 p_vendor_rec.NI_Number);
832 --
833 debug_info := 'Open cursor C';
834 OPEN C;
835 debug_info := 'Fetch cursor C';
836 FETCH C INTO x_Rowid;
837 if (C%NOTFOUND) then
838 debug_info := 'Close cursor C - NOTFOUND';
839 CLOSE C;
840 Raise NO_DATA_FOUND;
841 end if;
842 debug_info := 'Close cursor C';
843 CLOSE C;
844
845 EXCEPTION
846 WHEN OTHERS THEN
847 IF (SQLCODE <> -20001) THEN
848 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
849 FND_MSG_PUB.ADD;
850 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
854 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
851 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
852 FND_MESSAGE.SET_TOKEN('PARAMETERS','ROWID = ' || x_Rowid ||
853 ', VENDOR_ID = ' || x_vendor_Id);
855 END IF;
856 APP_EXCEPTION.RAISE_EXCEPTION;
857
858 END Insert_Row;
859 --
860 --
861 PROCEDURE Update_Row (
862 x_Rowid VARCHAR2,
863 x_Vendor_Id NUMBER,
864 x_Last_Update_Date DATE,
865 x_Last_Updated_By NUMBER,
866 x_Vendor_Name VARCHAR2,
867 x_Segment1 VARCHAR2,
868 x_Summary_Flag VARCHAR2,
869 x_Enabled_Flag VARCHAR2,
870 x_Last_Update_Login NUMBER,
871 x_Employee_Id NUMBER,
872 x_Validation_Number NUMBER,
873 x_Vendor_Type_Lookup_Code VARCHAR2,
874 x_Customer_Num VARCHAR2,
875 x_One_Time_Flag VARCHAR2,
876 x_Parent_Vendor_Id NUMBER,
877 x_Min_Order_Amount NUMBER,
878 /* x_Ship_To_Location_Id NUMBER,
879 x_Bill_To_Location_Id NUMBER,
880 x_Ship_Via_Lookup_Code VARCHAR2,
881 x_Freight_Terms_Lookup_Code VARCHAR2,
882 x_Fob_Lookup_Code VARCHAR2, */
883 x_Terms_Id NUMBER,
884 x_Set_Of_Books_Id NUMBER,
885 x_Always_Take_Disc_Flag VARCHAR2,
886 x_Pay_Date_Basis_Lookup_Code VARCHAR2,
887 x_Pay_Group_Lookup_Code VARCHAR2,
888 x_Payment_Priority NUMBER,
889 x_Invoice_Currency_Code VARCHAR2,
890 x_Payment_Currency_Code VARCHAR2,
891 x_Invoice_Amount_Limit NUMBER,
892 x_Hold_All_Payments_Flag VARCHAR2,
893 x_Hold_Future_Payments_Flag VARCHAR2,
894 x_Hold_Reason VARCHAR2,
895 /* x_Distribution_Set_Id NUMBER,
896 x_Accts_Pay_CCID NUMBER,
897 x_Future_Dated_Payment_CCID NUMBER,
898 x_Prepay_CCID NUMBER, */
899 x_Num_1099 VARCHAR2,
900 x_Type_1099 VARCHAR2,
901 x_withholding_stat_Lookup_Code VARCHAR2,
902 x_Withholding_Start_Date DATE,
903 x_Org_Type_Lookup_Code VARCHAR2,
904 -- eTax Uptake x_Vat_Code VARCHAR2,
905 x_Start_Date_Active DATE,
906 x_End_Date_Active DATE,
907 x_Qty_Rcv_Tolerance NUMBER,
908 x_Minority_Group_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_Women_Owned_Flag VARCHAR2,
914 x_Small_Business_Flag VARCHAR2,
915 x_Standard_Industry_Class VARCHAR2,
916 x_Attribute_Category VARCHAR2,
917 x_Attribute1 VARCHAR2,
918 x_Attribute2 VARCHAR2,
919 x_Attribute3 VARCHAR2,
920 x_Attribute4 VARCHAR2,
921 x_Attribute5 VARCHAR2,
922 x_Hold_Flag VARCHAR2,
923 x_Purchasing_Hold_Reason VARCHAR2,
924 x_Hold_By NUMBER,
925 x_Hold_Date DATE,
926 x_Terms_Date_Basis VARCHAR2,
927 x_Price_Tolerance NUMBER,
928 x_Attribute10 VARCHAR2,
929 x_Attribute11 VARCHAR2,
930 x_Attribute12 VARCHAR2,
931 x_Attribute13 VARCHAR2,
932 x_Attribute14 VARCHAR2,
933 x_Attribute15 VARCHAR2,
934 x_Attribute6 VARCHAR2,
935 x_Attribute7 VARCHAR2,
936 x_Attribute8 VARCHAR2,
937 x_Attribute9 VARCHAR2,
938 x_Days_Early_Receipt_Allowed NUMBER,
939 x_Days_Late_Receipt_Allowed NUMBER,
940 x_Enforce_Ship_To_Loc_Code VARCHAR2,
941 x_Federal_Reportable_Flag VARCHAR2,
942 x_Hold_Unmatched_Invoices_Flag VARCHAR2,
943 x_match_option VARCHAR2,
944 x_create_debit_memo_flag VARCHAR2,
945 x_Inspection_Required_Flag VARCHAR2,
946 x_Receipt_Required_Flag VARCHAR2,
947 x_Receiving_Routing_Id NUMBER,
948 x_State_Reportable_Flag VARCHAR2,
949 x_Tax_Verification_Date DATE,
950 x_Auto_Calculate_Interest_Flag VARCHAR2,
951 x_Name_Control VARCHAR2,
952 x_Allow_Subst_Receipts_Flag VARCHAR2,
953 x_Allow_Unord_Receipts_Flag VARCHAR2,
954 x_Receipt_Days_Exception_Code VARCHAR2,
955 x_Qty_Rcv_Exception_Code VARCHAR2,
956 -- eTax Uptake x_Offset_Tax_Flag VARCHAR2,
957 x_Exclude_Freight_From_Disc VARCHAR2,
958 x_Vat_Registration_Num VARCHAR2,
959 x_Tax_Reporting_Name VARCHAR2,
960 x_Awt_Group_Id NUMBER,
961 x_Pay_Awt_Group_Id NUMBER,--bug6664407
962 x_Check_Digits VARCHAR2,
963 x_Bank_Number VARCHAR2,
964 x_Allow_Awt_Flag VARCHAR2,
965 x_Bank_Branch_Type VARCHAR2,
966 /* eTax Uptake
967 x_Auto_Tax_Calc_Flag VARCHAR2,
968 x_Auto_Tax_Calc_Override VARCHAR2,
969 x_Amount_Includes_Tax_Flag VARCHAR2,
970 x_AP_Tax_Rounding_Rule VARCHAR2, */
971 x_Vendor_Name_Alt VARCHAR2,
972 X_global_attribute_category VARCHAR2 DEFAULT NULL,
973 X_global_attribute1 VARCHAR2 DEFAULT NULL,
974 X_global_attribute2 VARCHAR2 DEFAULT NULL,
975 X_global_attribute3 VARCHAR2 DEFAULT NULL,
976 X_global_attribute4 VARCHAR2 DEFAULT NULL,
977 X_global_attribute5 VARCHAR2 DEFAULT NULL,
978 X_global_attribute6 VARCHAR2 DEFAULT NULL,
979 X_global_attribute7 VARCHAR2 DEFAULT NULL,
980 X_global_attribute8 VARCHAR2 DEFAULT NULL,
981 X_global_attribute9 VARCHAR2 DEFAULT NULL,
985 X_global_attribute13 VARCHAR2 DEFAULT NULL,
982 X_global_attribute10 VARCHAR2 DEFAULT NULL,
983 X_global_attribute11 VARCHAR2 DEFAULT NULL,
984 X_global_attribute12 VARCHAR2 DEFAULT NULL,
986 X_global_attribute14 VARCHAR2 DEFAULT NULL,
987 X_global_attribute15 VARCHAR2 DEFAULT NULL,
988 X_global_attribute16 VARCHAR2 DEFAULT NULL,
989 X_global_attribute17 VARCHAR2 DEFAULT NULL,
990 X_global_attribute18 VARCHAR2 DEFAULT NULL,
991 X_global_attribute19 VARCHAR2 DEFAULT NULL,
992 X_global_attribute20 VARCHAR2 DEFAULT NULL,
993 X_Bank_Charge_Bearer VARCHAR2 DEFAULT NULL,
994 X_NI_Number VARCHAR2 DEFAULT NULL,
995 X_calling_sequence IN VARCHAR2 ) IS
996
997 current_calling_sequence VARCHAR2(2000);
998 debug_info VARCHAR2(100);
999 BEGIN
1000 -- Update the calling sequence
1001 --
1002 current_calling_sequence := 'AP_VENDORS_PKG.UPDATE_ROW<-' ||
1003 X_calling_sequence;
1004 --
1005 -- error out NOCOPY if Vendor name has been assigned by another session
1006 --
1007 ap_vendors_pkg.check_unique_vendor_name( p_vendor_id => x_vendor_id,
1008 p_vendor_name => x_vendor_name,
1009 X_calling_sequence => current_calling_sequence);
1010 --
1011 -- check for duplicate vendor_numbers
1012 --
1013 ap_vendors_pkg.check_unique_vendor_number( p_vendor_id => x_vendor_id,
1014 p_vendor_number => x_segment1,
1015 X_calling_sequence => current_calling_sequence);
1016 --
1017 -- error out NOCOPY if employee has been assigned by another session
1018 --
1019 ap_vendors_pkg.Check_Duplicate_Employee(p_rowid => x_rowid,
1020 p_employee_id => x_employee_id,
1021 X_calling_sequence => current_calling_sequence);
1022
1023 debug_info := 'Update PO_VENDORS';
1024 UPDATE ap_suppliers
1025 SET
1026 vendor_id = x_Vendor_Id,
1027 last_update_date = x_Last_Update_Date,
1028 last_updated_by = x_Last_Updated_By,
1029 vendor_name = x_Vendor_Name,
1030 segment1 = x_Segment1,
1031 summary_flag = x_Summary_Flag,
1032 enabled_flag = x_Enabled_Flag,
1033 last_update_login = x_Last_Update_Login,
1034 employee_id = x_Employee_Id,
1035 validation_number = x_Validation_Number,
1036 vendor_type_lookup_code = x_Vendor_Type_Lookup_Code,
1037 customer_num = x_Customer_Num,
1038 one_time_flag = x_One_Time_Flag,
1039 parent_vendor_id = x_Parent_Vendor_Id,
1040 min_order_amount = x_Min_Order_Amount,
1041 --Bug :2809214 MOAC - Supplier Attribute Change Project
1042 /* ship_to_location_id = x_Ship_To_Location_Id,
1043 bill_to_location_id = x_Bill_To_Location_Id,
1044 ship_via_lookup_code = x_Ship_Via_Lookup_Code,
1045 freight_terms_lookup_code = x_Freight_Terms_Lookup_Code,
1046 fob_lookup_code = x_Fob_Lookup_Code, */
1047 terms_id = x_Terms_Id,
1048 set_of_books_id = x_Set_Of_Books_Id,
1049 always_take_disc_flag = x_Always_Take_Disc_Flag,
1050 pay_date_basis_lookup_code = x_Pay_Date_Basis_Lookup_Code,
1051 pay_group_lookup_code = x_Pay_Group_Lookup_Code,
1052 payment_priority = x_Payment_Priority,
1053 invoice_currency_code = x_Invoice_Currency_Code,
1054 payment_currency_code = x_Payment_Currency_Code,
1055 invoice_amount_limit = x_Invoice_Amount_Limit,
1056 hold_all_payments_flag = x_Hold_All_Payments_Flag,
1057 hold_future_payments_flag = x_Hold_Future_Payments_Flag,
1058 hold_reason = x_Hold_Reason,
1059 --Bug :2809214 MOAC - Supplier Attribute Change Project
1060 /* distribution_set_id = x_Distribution_Set_Id,
1061 accts_pay_code_combination_id = x_Accts_Pay_CCID,
1062 future_dated_payment_ccid = x_Future_Dated_Payment_CCID,
1063 prepay_code_combination_id = x_Prepay_CCID, */
1064 num_1099 = x_Num_1099,
1065 type_1099 = x_Type_1099,
1066 withholding_status_lookup_code = x_withholding_stat_Lookup_Code,
1067 withholding_start_date = x_Withholding_Start_Date,
1068 organization_type_lookup_code = x_Org_Type_Lookup_Code,
1069 start_date_active = x_Start_Date_Active,
1070 end_date_active = x_End_Date_Active,
1071 qty_rcv_tolerance = x_Qty_Rcv_Tolerance,
1072 minority_group_lookup_code = x_Minority_Group_Lookup_Code,
1073 bank_account_name = x_Bank_Account_Name,
1074 bank_account_num = x_Bank_Account_Num,
1075 bank_num = x_Bank_Num,
1076 bank_account_type = x_Bank_Account_Type,
1077 women_owned_flag = x_Women_Owned_Flag,
1078 small_business_flag = x_Small_Business_Flag,
1079 standard_industry_class = x_Standard_Industry_Class,
1080 attribute_category = x_Attribute_Category,
1081 attribute1 = x_Attribute1,
1082 attribute2 = x_Attribute2,
1083 attribute3 = x_Attribute3,
1084 attribute4 = x_Attribute4,
1085 attribute5 = x_Attribute5,
1086 hold_flag = x_Hold_Flag,
1087 purchasing_hold_reason = x_Purchasing_Hold_Reason,
1088 hold_by = x_Hold_By,
1089 hold_date = x_Hold_Date,
1090 terms_date_basis = x_Terms_Date_Basis,
1091 price_tolerance = x_Price_Tolerance,
1092 attribute10 = x_Attribute10,
1093 attribute11 = x_Attribute11,
1097 attribute15 = x_Attribute15,
1094 attribute12 = x_Attribute12,
1095 attribute13 = x_Attribute13,
1096 attribute14 = x_Attribute14,
1098 attribute6 = x_Attribute6,
1099 attribute7 = x_Attribute7,
1100 attribute8 = x_Attribute8,
1101 attribute9 = x_Attribute9,
1102 days_early_receipt_allowed = x_Days_Early_Receipt_Allowed,
1103 days_late_receipt_allowed = x_Days_Late_Receipt_Allowed,
1104 enforce_ship_to_location_code = x_Enforce_Ship_To_Loc_Code,
1105 federal_reportable_flag = x_Federal_Reportable_Flag,
1106 hold_unmatched_invoices_flag = x_Hold_Unmatched_Invoices_Flag,
1107 match_option = x_match_option,
1108 create_debit_memo_flag = x_create_debit_memo_flag,
1109 inspection_required_flag = x_Inspection_Required_Flag,
1110 receipt_required_flag = x_Receipt_Required_Flag,
1111 receiving_routing_id = x_Receiving_Routing_Id,
1112 state_reportable_flag = x_State_Reportable_Flag,
1113 tax_verification_date = x_Tax_Verification_Date,
1114 auto_calculate_interest_flag = x_Auto_Calculate_Interest_Flag,
1115 name_control = x_Name_Control,
1116 allow_substitute_receipts_flag = x_Allow_Subst_Receipts_Flag,
1117 allow_unordered_receipts_flag = x_Allow_Unord_Receipts_Flag,
1118 receipt_days_exception_code = x_Receipt_Days_Exception_Code,
1119 qty_rcv_exception_code = x_Qty_Rcv_Exception_Code,
1120 exclude_freight_from_discount = x_Exclude_Freight_From_Disc,
1121 vat_registration_num = x_Vat_Registration_Num,
1122 tax_reporting_name = x_Tax_Reporting_Name,
1123 awt_group_id = x_Awt_Group_Id,
1124 pay_awt_group_id = x_Pay_Awt_Group_Id,--bug6664407
1125 check_digits = x_Check_Digits,
1126 bank_number = x_Bank_Number,
1127 allow_awt_flag = x_Allow_Awt_Flag,
1128 bank_branch_type = x_bank_branch_type,
1129 vendor_name_alt = x_Vendor_Name_Alt,
1130 global_attribute_category = X_global_attribute_category,
1131 global_attribute1 = X_global_attribute1,
1132 global_attribute2 = X_global_attribute2,
1133 global_attribute3 = X_global_attribute3,
1134 global_attribute4 = X_global_attribute4,
1135 global_attribute5 = X_global_attribute5,
1136 global_attribute6 = X_global_attribute6,
1137 global_attribute7 = X_global_attribute7,
1138 global_attribute8 = X_global_attribute8,
1139 global_attribute9 = X_global_attribute9,
1140 global_attribute10 = X_global_attribute10,
1141 global_attribute11 = X_global_attribute11,
1142 global_attribute12 = X_global_attribute12,
1143 global_attribute13 = X_global_attribute13,
1144 global_attribute14 = X_global_attribute14,
1145 global_attribute15 = X_global_attribute15,
1146 global_attribute16 = X_global_attribute16,
1147 global_attribute17 = X_global_attribute17,
1148 global_attribute18 = X_global_attribute18,
1149 global_attribute19 = X_global_attribute19,
1150 global_attribute20 = X_global_attribute20,
1151 bank_charge_bearer = X_Bank_Charge_Bearer,
1152 --Bug :2809214 MOAC - Supplier Attribute Change Project
1153 NI_Number = X_NI_Number
1154 WHERE rowid = x_Rowid;
1155 if (SQL%NOTFOUND) then
1156 Raise NO_DATA_FOUND;
1157 end if;
1158
1159 EXCEPTION
1160 WHEN OTHERS THEN
1161 IF (SQLCODE <> -20001) THEN
1162 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1163 FND_MSG_PUB.ADD;
1164 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1165 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
1166 FND_MESSAGE.SET_TOKEN('PARAMETERS','ROWID = ' || X_Rowid ||
1167 ', VENDOR_ID = ' || x_Vendor_Id);
1168 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
1169 END IF;
1170 APP_EXCEPTION.RAISE_EXCEPTION;
1171
1172 END Update_Row;
1173
1174 PROCEDURE Update_Row(
1175 p_vendor_rec IN AP_VENDOR_PUB_PKG.r_vendor_rec_type,
1176 p_last_update_date IN DATE,
1177 p_last_updated_by IN NUMBER,
1178 p_last_update_login IN NUMBER,
1179 p_request_id IN NUMBER,
1180 p_program_application_id IN NUMBER,
1181 p_program_id IN NUMBER,
1182 p_program_update_date IN DATE,
1183 p_rowid IN VARCHAR2,
1184 p_vendor_id IN NUMBER) IS
1185
1186 current_calling_sequence VARCHAR2(2000);
1187 debug_info VARCHAR2(100);
1188 BEGIN
1189
1190 -- Bug 6216082 Begins. Added the call to IGI package.
1191 -- Bug 7577497 Added another parameter to the function call p_pay_tax_grp_id
1192 IF (p_vendor_rec.Awt_Group_Id IS NOT NULL OR
1193 p_vendor_rec.Pay_Awt_Group_Id IS NOT NULL) THEN
1194 IGI_CIS2007_UTIL_PKG.SUPPLIER_UPDATE(
1195 p_vendor_id => p_vendor_id,
1196 p_tax_grp_id => p_vendor_rec.Awt_Group_Id,
1197 p_pay_tax_grp_id => p_vendor_rec.Pay_Awt_Group_Id
1198 );
1199 END IF;
1200 -- Bug 6216082 Ends.
1201
1202 UPDATE ap_suppliers
1203 SET
1207 summary_flag = p_vendor_rec.Summary_Flag,
1204 last_update_date = p_Last_Update_Date,
1205 last_updated_by = p_Last_Updated_By,
1206 segment1 = p_vendor_rec.Segment1,
1208 enabled_flag = p_vendor_rec.Enabled_Flag,
1209 last_update_login = p_Last_Update_Login,
1210 employee_id = p_vendor_rec.Employee_Id,
1211 validation_number = p_vendor_rec.Validation_Number,
1212 vendor_type_lookup_code = p_vendor_rec.Vendor_Type_Lookup_Code,
1213 customer_num = p_vendor_rec.Customer_Num,
1214 one_time_flag = p_vendor_rec.One_Time_Flag,
1215 parent_vendor_id = p_vendor_rec.Parent_Vendor_Id,
1216 min_order_amount = p_vendor_rec.Min_Order_Amount,
1217 terms_id = p_vendor_rec.Terms_Id,
1218 set_of_books_id = p_vendor_rec.Set_Of_Books_Id,
1219 always_take_disc_flag = p_vendor_rec.Always_Take_Disc_Flag,
1220 pay_date_basis_lookup_code = p_vendor_rec.Pay_Date_Basis_Lookup_Code,
1221 pay_group_lookup_code = p_vendor_rec.Pay_Group_Lookup_Code,
1222 payment_priority = p_vendor_rec.Payment_Priority,
1223 invoice_currency_code = p_vendor_rec.Invoice_Currency_Code,
1224 payment_currency_code = p_vendor_rec.Payment_Currency_Code,
1225 invoice_amount_limit = p_vendor_rec.Invoice_Amount_Limit,
1226 hold_all_payments_flag = p_vendor_rec.Hold_All_Payments_Flag,
1227 hold_future_payments_flag = p_vendor_rec.Hold_Future_Payments_Flag,
1228 hold_reason = p_vendor_rec.Hold_Reason,
1229 --bug6050423 starts
1230 --bug6691916.commented the below assignment statement and added
1231 --the one below that.As per analysis,only organization type lookup
1232 --code of individual or foreign individual are considered
1233 --as individual suppliers
1234 /*individual_1099 = decode(UPPER(p_vendor_rec.Vendor_Type_Lookup_Code),'CONTRACTOR',
1235 decode(UPPER(p_vendor_rec.Organization_Type_Lookup_Code),
1236 'INDIVIDUAL',p_vendor_rec.jgzz_fiscal_code,
1237 'FOREIGN INDIVIDUAL',p_vendor_rec.jgzz_fiscal_code,
1238 'PARTNERSHIP',p_vendor_rec.jgzz_fiscal_code,
1239 'INDIVIDUAL PARTNERSHIP',p_vendor_rec.jgzz_fiscal_code,
1240 NULL),
1241 NULL),*/
1242 individual_1099 = decode(UPPER(p_vendor_rec.Organization_Type_Lookup_Code),
1243 'INDIVIDUAL',p_vendor_rec.jgzz_fiscal_code,
1244 'FOREIGN INDIVIDUAL',p_vendor_rec.jgzz_fiscal_code,
1245 NULL),
1246 --bug6050423 ends
1247 type_1099 = p_vendor_rec.Type_1099,
1248 withholding_status_lookup_code = p_vendor_rec.withholding_status_Lookup_Code,
1249 withholding_start_date = p_vendor_rec.Withholding_Start_Date,
1250 organization_type_lookup_code = p_vendor_rec.Organization_Type_Lookup_Code,
1251 start_date_active = p_vendor_rec.Start_Date_Active,
1252 end_date_active = p_vendor_rec.End_Date_Active,
1253 qty_rcv_tolerance = p_vendor_rec.Qty_Rcv_Tolerance,
1254 minority_group_lookup_code = p_vendor_rec.Minority_Group_Lookup_Code,
1255 women_owned_flag = p_vendor_rec.Women_Owned_Flag,
1256 small_business_flag = p_vendor_rec.Small_Business_Flag,
1257 attribute_category = p_vendor_rec.Attribute_Category,
1258 attribute1 = p_vendor_rec.Attribute1,
1259 attribute2 = p_vendor_rec.Attribute2,
1260 attribute3 = p_vendor_rec.Attribute3,
1261 attribute4 = p_vendor_rec.Attribute4,
1262 attribute5 = p_vendor_rec.Attribute5,
1263 hold_flag = p_vendor_rec.Hold_Flag,
1264 purchasing_hold_reason = p_vendor_rec.Purchasing_Hold_Reason,
1265 hold_by = p_vendor_rec.Hold_By,
1266 hold_date = p_vendor_rec.Hold_Date,
1267 terms_date_basis = p_vendor_rec.Terms_Date_Basis,
1268 attribute10 = p_vendor_rec.Attribute10,
1269 attribute11 = p_vendor_rec.Attribute11,
1270 attribute12 = p_vendor_rec.Attribute12,
1271 attribute13 = p_vendor_rec.Attribute13,
1272 attribute14 = p_vendor_rec.Attribute14,
1273 attribute15 = p_vendor_rec.Attribute15,
1274 attribute6 = p_vendor_rec.Attribute6,
1275 attribute7 = p_vendor_rec.Attribute7,
1276 attribute8 = p_vendor_rec.Attribute8,
1277 attribute9 = p_vendor_rec.Attribute9,
1278 days_early_receipt_allowed = p_vendor_rec.Days_Early_Receipt_Allowed,
1279 days_late_receipt_allowed = p_vendor_rec.Days_Late_Receipt_Allowed,
1280 enforce_ship_to_location_code = p_vendor_rec.Enforce_Ship_To_Location_Code,
1281 federal_reportable_flag = p_vendor_rec.Federal_Reportable_Flag,
1282 hold_unmatched_invoices_flag = p_vendor_rec.Hold_Unmatched_Invoices_Flag,
1283 match_option = p_vendor_rec.match_option,
1284 create_debit_memo_flag = p_vendor_rec.create_debit_memo_flag,
1285 inspection_required_flag = p_vendor_rec.Inspection_Required_Flag,
1286 receipt_required_flag = p_vendor_rec.Receipt_Required_Flag,
1287 receiving_routing_id = p_vendor_rec.Receiving_Routing_Id,
1288 state_reportable_flag = p_vendor_rec.State_Reportable_Flag,
1289 tax_verification_date = p_vendor_rec.Tax_Verification_Date,
1290 auto_calculate_interest_flag = p_vendor_rec.Auto_Calculate_Interest_Flag,
1291 name_control = p_vendor_rec.Name_Control,
1292 allow_substitute_receipts_flag = p_vendor_rec.Allow_Substitute_Receipts_Flag,
1293 allow_unordered_receipts_flag = p_vendor_rec.Allow_Unordered_Receipts_Flag,
1294 receipt_days_exception_code = p_vendor_rec.Receipt_Days_Exception_Code,
1295 qty_rcv_exception_code = p_vendor_rec.Qty_Rcv_Exception_Code,
1296 exclude_freight_from_discount = p_vendor_rec.Exclude_Freight_From_Discount,
1300 check_digits = p_vendor_rec.Check_Digits,
1297 tax_reporting_name = p_vendor_rec.Tax_Reporting_Name,
1298 awt_group_id = p_vendor_rec.Awt_Group_Id,
1299 pay_awt_group_id = p_vendor_rec.Pay_Awt_Group_Id,--bug6664407
1301 allow_awt_flag = p_vendor_rec.Allow_Awt_Flag,
1302 global_attribute_category = p_vendor_rec.global_attribute_category,
1303 global_attribute1 = p_vendor_rec.global_attribute1,
1304 global_attribute2 = p_vendor_rec.global_attribute2,
1305 global_attribute3 = p_vendor_rec.global_attribute3,
1306 global_attribute4 = p_vendor_rec.global_attribute4,
1307 global_attribute5 = p_vendor_rec.global_attribute5,
1308 global_attribute6 = p_vendor_rec.global_attribute6,
1309 global_attribute7 = p_vendor_rec.global_attribute7,
1310 global_attribute8 = p_vendor_rec.global_attribute8,
1311 global_attribute9 = p_vendor_rec.global_attribute9,
1312 global_attribute10 = p_vendor_rec.global_attribute10,
1313 global_attribute11 = p_vendor_rec.global_attribute11,
1314 global_attribute12 = p_vendor_rec.global_attribute12,
1315 global_attribute13 = p_vendor_rec.global_attribute13,
1316 global_attribute14 = p_vendor_rec.global_attribute14,
1317 global_attribute15 = p_vendor_rec.global_attribute15,
1318 global_attribute16 = p_vendor_rec.global_attribute16,
1319 global_attribute17 = p_vendor_rec.global_attribute17,
1320 global_attribute18 = p_vendor_rec.global_attribute18,
1321 global_attribute19 = p_vendor_rec.global_attribute19,
1322 global_attribute20 = p_vendor_rec.global_attribute20,
1323 bank_charge_bearer = p_vendor_rec.Bank_Charge_Bearer,
1324 NI_Number = p_vendor_rec.NI_Number,
1325 standard_industry_class = p_vendor_rec.sic_code -- 5066199
1326 WHERE vendor_id = p_vendor_id;
1327 if (SQL%NOTFOUND) then
1328 Raise NO_DATA_FOUND;
1329 end if;
1330
1331 /* Bug 5412440 */
1332 IF (p_vendor_rec.allow_awt_flag = 'N') THEN
1333 UPDATE Ap_Supplier_Sites_ALL
1334 SET allow_awt_flag = 'N',
1335 awt_group_id = NULL
1336 WHERE vendor_id = p_vendor_rec.vendor_id;
1337 END IF;
1338
1339
1340 EXCEPTION
1341 WHEN OTHERS THEN
1342 IF (SQLCODE <> -20001) THEN
1343 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1344 FND_MSG_PUB.ADD;
1345 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1346 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
1347 FND_MESSAGE.SET_TOKEN('PARAMETERS', 'VENDOR_ID = ' || p_Vendor_Id);
1348 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
1349 END IF;
1350 APP_EXCEPTION.RAISE_EXCEPTION;
1351
1352
1353 END Update_Row;
1354
1355 --
1356 --
1357 PROCEDURE Lock_Row (
1358 x_Rowid VARCHAR2,
1359 x_Vendor_Id NUMBER,
1360 x_Vendor_Name VARCHAR2,
1361 x_Segment1 VARCHAR2,
1362 x_Summary_Flag VARCHAR2,
1363 x_Enabled_Flag VARCHAR2,
1364 x_Employee_Id NUMBER,
1365 x_Validation_Number NUMBER,
1366 x_Vendor_Type_Lookup_Code VARCHAR2,
1367 x_Customer_Num VARCHAR2,
1368 x_One_Time_Flag VARCHAR2,
1369 x_Parent_Vendor_Id NUMBER,
1370 x_Min_Order_Amount NUMBER,
1371 /* x_Ship_To_Location_Id NUMBER,
1372 x_Bill_To_Location_Id NUMBER,
1373 x_Ship_Via_Lookup_Code VARCHAR2,
1374 x_Freight_Terms_Lookup_Code VARCHAR2,
1375 x_Fob_Lookup_Code VARCHAR2, */
1376 x_Terms_Id NUMBER,
1377 x_Set_Of_Books_Id NUMBER,
1378 x_Always_Take_Disc_Flag VARCHAR2,
1379 x_Pay_Date_Basis_Lookup_Code VARCHAR2,
1380 x_Pay_Group_Lookup_Code VARCHAR2,
1381 x_Payment_Priority NUMBER,
1382 x_Invoice_Currency_Code VARCHAR2,
1383 x_Payment_Currency_Code VARCHAR2,
1384 x_Invoice_Amount_Limit NUMBER,
1385 x_Hold_All_Payments_Flag VARCHAR2,
1386 x_Hold_Future_Payments_Flag VARCHAR2,
1387 x_Hold_Reason VARCHAR2,
1388 /* x_Distribution_Set_Id NUMBER,
1389 x_Accts_Pay_CCID NUMBER,
1390 x_Future_Dated_Payment_CCID NUMBER,
1391 x_Prepay_CCID NUMBER, */
1392 x_Num_1099 VARCHAR2,
1393 x_Type_1099 VARCHAR2,
1394 x_withholding_stat_Lookup_Code VARCHAR2,
1395 x_Withholding_Start_Date DATE,
1396 x_Org_Type_Lookup_Code VARCHAR2,
1397 -- eTax Uptake x_Vat_Code VARCHAR2,
1398 x_Start_Date_Active DATE,
1399 x_End_Date_Active DATE,
1400 x_Qty_Rcv_Tolerance NUMBER,
1401 x_Minority_Group_Lookup_Code VARCHAR2,
1402 x_Bank_Account_Name VARCHAR2,
1403 x_Bank_Account_Num VARCHAR2,
1404 x_Bank_Num VARCHAR2,
1405 x_Bank_Account_Type VARCHAR2,
1406 x_Women_Owned_Flag VARCHAR2,
1407 x_Small_Business_Flag VARCHAR2,
1408 x_Standard_Industry_Class VARCHAR2,
1409 x_Attribute_Category VARCHAR2,
1413 x_Attribute4 VARCHAR2,
1410 x_Attribute1 VARCHAR2,
1411 x_Attribute2 VARCHAR2,
1412 x_Attribute3 VARCHAR2,
1414 x_Attribute5 VARCHAR2,
1415 x_Hold_Flag VARCHAR2,
1416 x_Purchasing_Hold_Reason VARCHAR2,
1417 x_Hold_By NUMBER,
1418 x_Hold_Date DATE,
1419 x_Terms_Date_Basis VARCHAR2,
1420 x_Price_Tolerance NUMBER,
1421 x_Attribute10 VARCHAR2,
1422 x_Attribute11 VARCHAR2,
1423 x_Attribute12 VARCHAR2,
1424 x_Attribute13 VARCHAR2,
1425 x_Attribute14 VARCHAR2,
1426 x_Attribute15 VARCHAR2,
1427 x_Attribute6 VARCHAR2,
1428 x_Attribute7 VARCHAR2,
1429 x_Attribute8 VARCHAR2,
1430 x_Attribute9 VARCHAR2,
1431 x_Days_Early_Receipt_Allowed NUMBER,
1432 x_Days_Late_Receipt_Allowed NUMBER,
1433 x_Enforce_Ship_To_Loc_Code VARCHAR2,
1434 x_Federal_Reportable_Flag VARCHAR2,
1435 x_Hold_Unmatched_Invoices_Flag VARCHAR2,
1436 x_match_option VARCHAR2,
1437 x_create_debit_memo_flag VARCHAR2,
1438 x_Inspection_Required_Flag VARCHAR2,
1439 x_Receipt_Required_Flag VARCHAR2,
1440 x_Receiving_Routing_Id NUMBER,
1441 x_State_Reportable_Flag VARCHAR2,
1442 x_Tax_Verification_Date DATE,
1443 x_Auto_Calculate_Interest_Flag VARCHAR2,
1444 x_Name_Control VARCHAR2,
1445 x_Allow_Subst_Receipts_Flag VARCHAR2,
1446 x_Allow_Unord_Receipts_Flag VARCHAR2,
1447 x_Receipt_Days_Exception_Code VARCHAR2,
1448 x_Qty_Rcv_Exception_Code VARCHAR2,
1449 -- eTax Uptake x_Offset_Tax_Flag VARCHAR2,
1450 x_Exclude_Freight_From_Disc VARCHAR2,
1451 x_Vat_Registration_Num VARCHAR2,
1452 x_Tax_Reporting_Name VARCHAR2,
1453 x_Awt_Group_Id NUMBER,
1454 x_Pay_Awt_Group_Id NUMBER,--bug6664407
1455 x_Check_Digits VARCHAR2,
1456 x_Bank_Number VARCHAR2,
1457 x_Allow_Awt_Flag VARCHAR2,
1458 x_Bank_Branch_Type VARCHAR2,
1459 /* eTax Uptake
1460 x_Auto_Tax_Calc_Flag VARCHAR2,
1461 x_Auto_Tax_Calc_Override VARCHAR2,
1462 x_Amount_Includes_Tax_Flag VARCHAR2,
1463 x_AP_Tax_Rounding_Rule VARCHAR2, */
1464 x_Vendor_Name_Alt VARCHAR2,
1465 X_global_attribute_category VARCHAR2 DEFAULT NULL,
1466 X_global_attribute1 VARCHAR2 DEFAULT NULL,
1467 X_global_attribute2 VARCHAR2 DEFAULT NULL,
1468 X_global_attribute3 VARCHAR2 DEFAULT NULL,
1469 X_global_attribute4 VARCHAR2 DEFAULT NULL,
1470 X_global_attribute5 VARCHAR2 DEFAULT NULL,
1471 X_global_attribute6 VARCHAR2 DEFAULT NULL,
1472 X_global_attribute7 VARCHAR2 DEFAULT NULL,
1473 X_global_attribute8 VARCHAR2 DEFAULT NULL,
1474 X_global_attribute9 VARCHAR2 DEFAULT NULL,
1475 X_global_attribute10 VARCHAR2 DEFAULT NULL,
1476 X_global_attribute11 VARCHAR2 DEFAULT NULL,
1477 X_global_attribute12 VARCHAR2 DEFAULT NULL,
1478 X_global_attribute13 VARCHAR2 DEFAULT NULL,
1479 X_global_attribute14 VARCHAR2 DEFAULT NULL,
1480 X_global_attribute15 VARCHAR2 DEFAULT NULL,
1481 X_global_attribute16 VARCHAR2 DEFAULT NULL,
1482 X_global_attribute17 VARCHAR2 DEFAULT NULL,
1483 X_global_attribute18 VARCHAR2 DEFAULT NULL,
1484 X_global_attribute19 VARCHAR2 DEFAULT NULL,
1485 X_global_attribute20 VARCHAR2 DEFAULT NULL,
1486 X_Bank_Charge_Bearer VARCHAR2 DEFAULT NULL,
1487 X_NI_Number VARCHAR2 DEFAULT NULL,
1488 X_calling_sequence IN VARCHAR2 ) IS
1489
1490 CURSOR C IS
1491 SELECT *
1492 FROM ap_suppliers
1493 WHERE rowid = x_Rowid
1494 FOR UPDATE of Vendor_Id NOWAIT;
1495 Recinfo C%ROWTYPE;
1496
1497 current_calling_sequence VARCHAR2(2000);
1498 debug_info VARCHAR2(100);
1499 --
1500 BEGIN
1501 -- Update the calling sequence
1502 --
1503 current_calling_sequence := 'AP_VENDORS_PKG.LOCK_ROW<-' ||
1504 X_calling_sequence;
1505
1506 debug_info := 'Open cursor C';
1507 OPEN C;
1508 debug_info := 'Fetch cursor C';
1509 FETCH C INTO Recinfo;
1510 if (C%NOTFOUND) then
1511 debug_info := 'Close cursor C- DATA NOTFOUND';
1512 CLOSE C;
1513 FND_MESSAGE.Set_Name('FND','FORM_RECORD_DELETED');
1514 FND_MSG_PUB.ADD;
1515 APP_EXCEPTION.Raise_Exception;
1516 end if;
1517 debug_info := 'Close cursor C';
1518 CLOSE C;
1519 if (
1520 (Recinfo.vendor_id = x_Vendor_Id)
1521 AND (Recinfo.vendor_name = x_Vendor_Name)
1522 AND (Recinfo.segment1 = x_Segment1)
1523 AND (Recinfo.summary_flag = x_Summary_Flag)
1524 AND (Recinfo.enabled_flag = x_Enabled_Flag)
1525 AND ((Recinfo.employee_id = x_Employee_Id)
1526 OR ((Recinfo.employee_id IS NULL)
1527 AND (x_Employee_Id IS NULL)))
1528 AND ((Recinfo.validation_number = x_Validation_Number)
1529 OR ((Recinfo.validation_number IS NULL)
1530 AND (x_Validation_Number IS NULL)))
1534 AND ((Recinfo.customer_num = x_Customer_Num)
1531 AND ((Recinfo.vendor_type_lookup_code = x_Vendor_Type_Lookup_Code)
1532 OR ((Recinfo.vendor_type_lookup_code IS NULL)
1533 AND (x_Vendor_Type_Lookup_Code IS NULL)))
1535 OR ((Recinfo.customer_num IS NULL)
1536 AND (x_Customer_Num IS NULL)))
1537 AND ((Recinfo.one_time_flag = x_One_Time_Flag)
1538 OR ((Recinfo.one_time_flag IS NULL)
1539 AND (x_One_Time_Flag IS NULL)))
1540 AND ((Recinfo.parent_vendor_id = x_Parent_Vendor_Id)
1541 OR ((Recinfo.parent_vendor_id IS NULL)
1542 AND (x_Parent_Vendor_Id IS NULL)))
1543 AND ((Recinfo.min_order_amount = x_Min_Order_Amount)
1544 OR ((Recinfo.min_order_amount IS NULL)
1545 AND (x_Min_Order_Amount IS NULL)))
1546 --Bug :2809214 MOAC - Supplier Attribute Change Project
1547 /* AND ((Recinfo.ship_to_location_id = x_Ship_To_Location_Id)
1548 OR ((Recinfo.ship_to_location_id IS NULL)
1549 AND (x_Ship_To_Location_Id IS NULL)))
1550 AND ((Recinfo.bill_to_location_id = x_Bill_To_Location_Id)
1551 OR ((Recinfo.bill_to_location_id IS NULL)
1552 AND (x_Bill_To_Location_Id IS NULL)))
1553 AND ((Recinfo.ship_via_lookup_code = x_Ship_Via_Lookup_Code)
1554 OR ((Recinfo.ship_via_lookup_code IS NULL)
1555 AND (x_Ship_Via_Lookup_Code IS NULL)))
1556 AND ((Recinfo.freight_terms_lookup_code = x_Freight_Terms_Lookup_Code)
1557 OR ((Recinfo.freight_terms_lookup_code IS NULL)
1558 AND (x_Freight_Terms_Lookup_Code IS NULL)))
1559 AND ((Recinfo.fob_lookup_code = x_Fob_Lookup_Code)
1560 OR ((Recinfo.fob_lookup_code IS NULL)
1561 AND (x_Fob_Lookup_Code IS NULL))) */
1562 AND ((Recinfo.terms_id = x_Terms_Id)
1563 OR ( (Recinfo.terms_id IS NULL)
1564 AND (x_Terms_Id IS NULL)))
1565 AND ((Recinfo.set_of_books_id = x_Set_Of_Books_Id)
1566 OR ((Recinfo.set_of_books_id IS NULL)
1567 AND (x_Set_Of_Books_Id IS NULL)))
1568 AND ((Recinfo.always_take_disc_flag = x_Always_Take_Disc_Flag)
1569 OR ((Recinfo.always_take_disc_flag IS NULL)
1570 AND (x_Always_Take_Disc_Flag IS NULL)))
1571 AND ((Recinfo.pay_date_basis_lookup_code = x_Pay_Date_Basis_Lookup_Code)
1572 OR ((Recinfo.pay_date_basis_lookup_code IS NULL)
1573 AND (x_Pay_Date_Basis_Lookup_Code IS NULL)))
1574 AND ((Recinfo.pay_group_lookup_code = x_Pay_Group_Lookup_Code)
1575 OR ((Recinfo.pay_group_lookup_code IS NULL)
1576 AND (x_Pay_Group_Lookup_Code IS NULL)))
1577 AND ((Recinfo.payment_priority = x_Payment_Priority)
1578 OR ((Recinfo.payment_priority IS NULL)
1579 AND (x_Payment_Priority IS NULL)))
1580 AND ((Recinfo.invoice_currency_code = x_Invoice_Currency_Code)
1581 OR ((Recinfo.invoice_currency_code IS NULL)
1582 AND (x_Invoice_Currency_Code IS NULL)))
1583 AND ((Recinfo.payment_currency_code = x_Payment_Currency_Code)
1584 OR ((Recinfo.payment_currency_code IS NULL)
1585 AND (x_Payment_Currency_Code IS NULL)))
1586 AND ((Recinfo.invoice_amount_limit = x_Invoice_Amount_Limit)
1587 OR ((Recinfo.invoice_amount_limit IS NULL)
1588 AND (x_Invoice_Amount_Limit IS NULL)))
1589 AND ((Recinfo.hold_all_payments_flag = x_Hold_All_Payments_Flag)
1590 OR ((Recinfo.hold_all_payments_flag IS NULL)
1591 AND (x_Hold_All_Payments_Flag IS NULL)))
1592 AND ((Recinfo.hold_future_payments_flag = x_Hold_Future_Payments_Flag)
1593 OR ((Recinfo.hold_future_payments_flag IS NULL)
1594 AND (x_Hold_Future_Payments_Flag IS NULL)))
1595 AND ((Recinfo.hold_reason = x_Hold_Reason)
1596 OR ((Recinfo.hold_reason IS NULL)
1597 AND (x_Hold_Reason IS NULL)))
1598 --Bug :2809214 MOAC - Supplier Attribute Change Project
1599 /* AND ((Recinfo.distribution_set_id = x_Distribution_Set_Id)
1600 OR ((Recinfo.distribution_set_id IS NULL)
1601 AND (x_Distribution_Set_Id IS NULL)))
1602 AND ((Recinfo.accts_pay_code_combination_id = x_Accts_Pay_CCID)
1603 OR ((Recinfo.accts_pay_code_combination_id IS NULL)
1604 AND (x_Accts_Pay_CCID IS NULL)))
1605 AND ((Recinfo.future_dated_payment_ccid = x_Future_Dated_Payment_CCID)
1606 OR ((Recinfo.future_dated_payment_ccid IS NULL)
1607 AND (x_Future_Dated_Payment_CCID IS NULL)))
1608 AND ((Recinfo.prepay_code_combination_id = x_Prepay_CCID)
1609 OR ((Recinfo.prepay_code_combination_id IS NULL)
1610 AND (x_Prepay_CCID IS NULL))) */
1611 AND ((Recinfo.num_1099 = x_Num_1099)
1612 OR ((Recinfo.num_1099 IS NULL)
1613 AND (x_Num_1099 IS NULL)))
1614 AND ((Recinfo.type_1099 = x_Type_1099)
1615 OR ((Recinfo.type_1099 IS NULL)
1616 AND (x_Type_1099 IS NULL)))
1617 AND ((Recinfo.withholding_status_lookup_code = x_withholding_stat_Lookup_Code)
1618 OR ((Recinfo.withholding_status_lookup_code IS NULL)
1619 AND (x_withholding_stat_Lookup_Code IS NULL)))
1620 AND ((Recinfo.withholding_start_date = x_Withholding_Start_Date)
1621 OR ((Recinfo.withholding_start_date IS NULL)
1622 AND (x_Withholding_Start_Date IS NULL)))
1623 AND ((Recinfo.organization_type_lookup_code = x_Org_Type_Lookup_Code)
1624 OR ((Recinfo.organization_type_lookup_code IS NULL)
1625 AND (x_Org_Type_Lookup_Code IS NULL)))
1626 AND ((Recinfo.start_date_active = x_Start_Date_Active)
1627 OR ((Recinfo.start_date_active IS NULL)
1628 AND (x_Start_Date_Active IS NULL)))
1629 AND ((Recinfo.end_date_active = x_End_Date_Active)
1630 OR ((Recinfo.end_date_active IS NULL)
1631 AND (x_End_Date_Active IS NULL)))
1632 AND ((Recinfo.qty_rcv_tolerance = x_Qty_Rcv_Tolerance)
1636 OR ((Recinfo.minority_group_lookup_code IS NULL)
1633 OR ((Recinfo.qty_rcv_tolerance IS NULL)
1634 AND (x_Qty_Rcv_Tolerance IS NULL )))
1635 AND ((Recinfo.minority_group_lookup_code = x_Minority_Group_Lookup_Code)
1637 AND (x_Minority_Group_Lookup_Code IS NULL)))
1638 AND ((Recinfo.bank_account_name = x_Bank_Account_Name)
1639 OR ((Recinfo.bank_account_name IS NULL)
1640 AND (x_Bank_Account_Name IS NULL)))
1641 AND ((Recinfo.bank_account_num = x_Bank_Account_Num)
1642 OR ((Recinfo.bank_account_num IS NULL)
1643 AND (x_Bank_Account_Num IS NULL)))
1644 AND ((Recinfo.bank_num = x_Bank_Num)
1645 OR ((Recinfo.bank_num IS NULL)
1646 AND (x_Bank_Num IS NULL)))
1647 AND ((Recinfo.bank_account_type = x_Bank_Account_Type)
1648 OR ((Recinfo.bank_account_type IS NULL)
1649 AND (x_Bank_Account_Type IS NULL)))
1650 AND ((Recinfo.women_owned_flag = x_Women_Owned_Flag)
1651 OR ((Recinfo.women_owned_flag IS NULL)
1652 AND (x_Women_Owned_Flag IS NULL)))
1653 AND ((Recinfo.small_business_flag = x_Small_Business_Flag)
1654 OR ((Recinfo.small_business_flag IS NULL)
1655 AND (x_Small_Business_Flag IS NULL)))
1656 AND ((Recinfo.standard_industry_class = x_Standard_Industry_Class)
1657 OR ((Recinfo.standard_industry_class IS NULL)
1658 AND (x_Standard_Industry_Class IS NULL)))
1659 AND (NVL(Recinfo.Bank_Charge_Bearer,'I') = x_Bank_Charge_Bearer)
1660 --Bug :2809214 MOAC - Supplier Attribute Change Project
1661 AND ((Recinfo.NI_Number = x_NI_Number)
1662 OR ((Recinfo.NI_Number IS NULL)
1663 AND (x_NI_Number IS NULL)))
1664
1665 )
1666 then
1667 null;
1668 else
1669 FND_MESSAGE.Set_Name('FND','FORM_RECORD_CHANGED');
1670 FND_MSG_PUB.ADD;
1671 APP_EXCEPTION.Raise_Exception;
1672 end if;
1673 --
1674 if (
1675 ((Recinfo.attribute_category = x_Attribute_Category)
1676 OR ((Recinfo.attribute_category IS NULL)
1677 AND (x_Attribute_Category IS NULL)))
1678 AND ((Recinfo.attribute1 = x_Attribute1)
1679 OR ((Recinfo.attribute1 IS NULL)
1680 AND (x_Attribute1 IS NULL)))
1681 AND ((Recinfo.attribute2 = x_Attribute2)
1682 OR ((Recinfo.attribute2 IS NULL)
1683 AND (x_Attribute2 IS NULL)))
1684 AND ((Recinfo.attribute3 = x_Attribute3)
1685 OR ((Recinfo.attribute3 IS NULL)
1686 AND (x_Attribute3 IS NULL)))
1687 AND ((Recinfo.attribute4 = x_Attribute4)
1688 OR ((Recinfo.attribute4 IS NULL)
1689 AND (x_Attribute4 IS NULL)))
1690 AND ((Recinfo.attribute5 = x_Attribute5)
1691 OR ((Recinfo.attribute5 IS NULL)
1692 AND (x_Attribute5 IS NULL)))
1693 AND ((Recinfo.hold_flag = x_Hold_Flag)
1694 OR ((Recinfo.hold_flag IS NULL)
1695 AND (x_Hold_Flag IS NULL)))
1696 AND ((Recinfo.purchasing_hold_reason = x_Purchasing_Hold_Reason)
1697 OR ((Recinfo.purchasing_hold_reason IS NULL)
1698 AND (x_Purchasing_Hold_Reason IS NULL)))
1699 AND ((Recinfo.hold_by = x_Hold_By)
1700 OR ((Recinfo.hold_by IS NULL)
1701 AND (x_Hold_By IS NULL)))
1702 AND ((Recinfo.hold_date = x_Hold_Date)
1703 OR ((Recinfo.hold_date IS NULL)
1704 AND (x_Hold_Date IS NULL)))
1705 AND ((Recinfo.terms_date_basis = x_Terms_Date_Basis)
1706 OR ((Recinfo.terms_date_basis IS NULL)
1707 AND (x_Terms_Date_Basis IS NULL)))
1708 AND ((Recinfo.price_tolerance = x_Price_Tolerance)
1709 OR ((Recinfo.price_tolerance IS NULL)
1710 AND (x_Price_Tolerance IS NULL)))
1711 AND ((Recinfo.attribute10 = x_Attribute10)
1712 OR ((Recinfo.attribute10 IS NULL)
1713 AND (x_Attribute10 IS NULL)))
1714 AND ((Recinfo.attribute11 = x_Attribute11)
1715 OR ((Recinfo.attribute11 IS NULL)
1716 AND (x_Attribute11 IS NULL)))
1717 AND ((Recinfo.attribute12 = x_Attribute12)
1718 OR ((Recinfo.attribute12 IS NULL)
1719 AND (x_Attribute12 IS NULL)))
1720 AND ((Recinfo.attribute13 = x_Attribute13)
1721 OR ((Recinfo.attribute13 IS NULL)
1722 AND (x_Attribute13 IS NULL)))
1723 AND ((Recinfo.attribute14 = x_Attribute14)
1724 OR ((Recinfo.attribute14 IS NULL)
1725 AND (x_Attribute14 IS NULL)))
1726 AND ((Recinfo.attribute15 = x_Attribute15)
1727 OR ((Recinfo.attribute15 IS NULL)
1728 AND (x_Attribute15 IS NULL)))
1729 AND ((Recinfo.attribute6 = x_Attribute6)
1730 OR ((Recinfo.attribute6 IS NULL)
1731 AND (x_Attribute6 IS NULL)))
1732 AND ((Recinfo.attribute7 = x_Attribute7)
1733 OR ((Recinfo.attribute7 IS NULL)
1734 AND (x_Attribute7 IS NULL)))
1735 AND ((Recinfo.attribute8 = x_Attribute8)
1736 OR ((Recinfo.attribute8 IS NULL)
1737 AND (x_Attribute8 IS NULL)))
1738 AND ((Recinfo.attribute9 = x_Attribute9)
1739 OR ((Recinfo.attribute9 IS NULL)
1740 AND (x_Attribute9 IS NULL)))
1741 AND ((Recinfo.days_early_receipt_allowed = x_Days_Early_Receipt_Allowed)
1742 OR ((Recinfo.days_early_receipt_allowed IS NULL)
1743 AND (x_Days_Early_Receipt_Allowed IS NULL)))
1744 AND ( (Recinfo.days_late_receipt_allowed = x_Days_Late_Receipt_Allowed)
1745 OR ((Recinfo.days_late_receipt_allowed IS NULL)
1746 AND (x_Days_Late_Receipt_Allowed IS NULL)))
1747 AND ((Recinfo.enforce_ship_to_location_code = x_Enforce_Ship_To_Loc_Code)
1748 OR ((Recinfo.enforce_ship_to_location_code IS NULL)
1749 AND (x_Enforce_Ship_To_Loc_Code IS NULL)))
1753 AND ((Recinfo.hold_unmatched_invoices_flag = x_Hold_Unmatched_Invoices_Flag)
1750 AND ((Recinfo.federal_reportable_flag = x_Federal_Reportable_Flag)
1751 OR ((Recinfo.federal_reportable_flag IS NULL)
1752 AND (x_Federal_Reportable_Flag IS NULL)))
1754 OR ((Recinfo.hold_unmatched_invoices_flag IS NULL)
1755 AND (x_Hold_Unmatched_Invoices_Flag IS NULL)))
1756 AND ((Recinfo.match_option = x_match_option)
1757 OR ((Recinfo.match_option IS NULL)
1758 AND (x_match_option IS NULL)))
1759 AND ((Recinfo.create_debit_memo_flag = x_create_debit_memo_flag)
1760 OR ((Recinfo.create_debit_memo_flag IS NULL)
1761 AND (x_create_debit_memo_flag IS NULL)))
1762 AND ((Recinfo.inspection_required_flag = x_inspection_required_flag)
1763 OR ((Recinfo.inspection_required_flag IS NULL)
1764 AND (x_Inspection_Required_Flag IS NULL)))
1765 AND ((Recinfo.receipt_required_flag = x_Receipt_Required_Flag)
1766 OR ((Recinfo.receipt_required_flag IS NULL)
1767 AND (x_Receipt_Required_Flag IS NULL)))
1768 AND ((Recinfo.receiving_routing_id = x_Receiving_Routing_Id)
1769 OR ((Recinfo.receiving_routing_id IS NULL)
1770 AND (x_Receiving_Routing_Id IS NULL)))
1771 AND ((Recinfo.state_reportable_flag = x_State_Reportable_Flag)
1772 OR ((Recinfo.state_reportable_flag IS NULL)
1773 AND (x_State_Reportable_Flag IS NULL)))
1774 AND ((Recinfo.tax_verification_date = x_Tax_Verification_Date)
1775 OR ((Recinfo.tax_verification_date IS NULL)
1776 AND (x_Tax_Verification_Date IS NULL)))
1777 AND ((Recinfo.auto_calculate_interest_flag = x_Auto_Calculate_Interest_Flag)
1778 OR ((Recinfo.auto_calculate_interest_flag IS NULL)
1779 AND (x_Auto_Calculate_Interest_Flag IS NULL)))
1780 AND ((RTRIM(Recinfo.name_control) = x_Name_Control)
1781 OR ((RTRIM(Recinfo.name_control) IS NULL)
1782 AND (x_Name_Control IS NULL)))
1783 AND ((Recinfo.allow_substitute_receipts_flag = x_Allow_Subst_Receipts_Flag)
1784 OR ((Recinfo.allow_substitute_receipts_flag IS NULL)
1785 AND (x_Allow_Subst_Receipts_Flag IS NULL)))
1786 AND ((Recinfo.allow_unordered_receipts_flag = x_Allow_Unord_Receipts_Flag)
1787 OR ((Recinfo.allow_unordered_receipts_flag IS NULL)
1788 AND (x_Allow_Unord_Receipts_Flag IS NULL)))
1789 AND ((Recinfo.receipt_days_exception_code = x_Receipt_Days_Exception_Code)
1790 OR ((Recinfo.receipt_days_exception_code IS NULL)
1791 AND (x_Receipt_Days_Exception_Code IS NULL)))
1792 AND ((Recinfo.qty_rcv_exception_code = x_Qty_Rcv_Exception_Code)
1793 OR ((Recinfo.qty_rcv_exception_code IS NULL)
1794 AND (x_Qty_Rcv_Exception_Code IS NULL)))
1795 AND ((Recinfo.exclude_freight_from_discount = x_Exclude_Freight_From_Disc)
1796 OR ((Recinfo.exclude_freight_from_discount IS NULL)
1797 AND (x_Exclude_Freight_From_Disc IS NULL)))
1798 AND ((Recinfo.vat_registration_num = x_Vat_Registration_Num)
1799 OR ((Recinfo.vat_registration_num IS NULL)
1800 AND (x_Vat_Registration_Num IS NULL)))
1801 AND ((Recinfo.tax_reporting_name = x_Tax_Reporting_Name)
1802 OR ((Recinfo.tax_reporting_name IS NULL)
1803 AND (x_Tax_Reporting_Name IS NULL)))
1804 AND ((Recinfo.awt_group_id = x_Awt_Group_Id)
1805 OR ((Recinfo.awt_group_id IS NULL)
1806 AND (x_Awt_Group_Id IS NULL)))
1807 AND ((Recinfo.pay_awt_group_id = x_Pay_Awt_Group_Id)
1808 OR ((Recinfo.pay_awt_group_id IS NULL)
1809 AND (x_Pay_Awt_Group_Id IS NULL))) --bug6664407
1810 AND ((Recinfo.check_digits = x_Check_Digits)
1811 OR ((Recinfo.check_digits IS NULL)
1812 AND (x_Check_Digits IS NULL)))
1813 AND ((Recinfo.bank_number = x_Bank_Number)
1814 OR ((Recinfo.bank_number IS NULL)
1815 AND (x_Bank_Number IS NULL)))
1816 AND ((Recinfo.allow_awt_flag = x_Allow_Awt_Flag)
1817 OR ((Recinfo.allow_awt_flag IS NULL)
1818 AND (x_Allow_Awt_Flag IS NULL)))
1819 AND ((Recinfo.bank_branch_type = x_bank_branch_type)
1820 OR ((Recinfo.bank_branch_type IS NULL)
1821 AND (x_bank_branch_type IS NULL)))
1822 AND ((Recinfo.vendor_name_alt = x_Vendor_Name_Alt)
1823 OR ((Recinfo.vendor_name_alt IS NULL)
1824 AND (x_Vendor_Name_Alt IS NULL)))
1825 )
1826 then
1827 null;
1828 else
1829 FND_MESSAGE.Set_Name('FND','FORM_RECORD_CHANGED');
1830 FND_MSG_PUB.ADD;
1831 APP_EXCEPTION.Raise_Exception;
1832 end if;
1833 --
1834 if (
1835 ( (Recinfo.global_attribute_category = X_global_attribute_category)
1836 OR ( (Recinfo.global_attribute_category IS NULL)
1837 AND (X_global_attribute_category IS NULL)))
1838 AND ( (Recinfo.global_attribute1 = X_global_attribute1)
1839 OR ( (Recinfo.global_attribute1 IS NULL)
1840 AND (X_global_attribute1 IS NULL)))
1841 AND ( (Recinfo.global_attribute2 = X_global_attribute2)
1842 OR ( (Recinfo.global_attribute2 IS NULL)
1843 AND (X_global_attribute2 IS NULL)))
1844 AND ( (Recinfo.global_attribute3 = X_global_attribute3)
1845 OR ( (Recinfo.global_attribute3 IS NULL)
1849 AND (X_global_attribute4 IS NULL)))
1846 AND (X_global_attribute3 IS NULL)))
1847 AND ( (Recinfo.global_attribute4 = X_global_attribute4)
1848 OR ( (Recinfo.global_attribute4 IS NULL)
1850 AND ( (Recinfo.global_attribute5 = X_global_attribute5)
1851 OR ( (Recinfo.global_attribute5 IS NULL)
1852 AND (X_global_attribute5 IS NULL)))
1853 AND ( (Recinfo.global_attribute6 = X_global_attribute6)
1854 OR ( (Recinfo.global_attribute6 IS NULL)
1855 AND (X_global_attribute6 IS NULL)))
1856 AND ( (Recinfo.global_attribute7 = X_global_attribute7)
1857 OR ( (Recinfo.global_attribute7 IS NULL)
1858 AND (X_global_attribute7 IS NULL)))
1859 AND ( (Recinfo.global_attribute8 = X_global_attribute8)
1860 OR ( (Recinfo.global_attribute8 IS NULL)
1861 AND (X_global_attribute8 IS NULL)))
1862 AND ( (Recinfo.global_attribute9 = X_global_attribute9)
1863 OR ( (Recinfo.global_attribute9 IS NULL)
1864 AND (X_global_attribute9 IS NULL)))
1865 AND ( (Recinfo.global_attribute10 = X_global_attribute10)
1866 OR ( (Recinfo.global_attribute10 IS NULL)
1867 AND (X_global_attribute10 IS NULL)))
1868 AND ( (Recinfo.global_attribute11 = X_global_attribute11)
1869 OR ( (Recinfo.global_attribute11 IS NULL)
1870 AND (X_global_attribute11 IS NULL)))
1871 AND ( (Recinfo.global_attribute12 = X_global_attribute12)
1872 OR ( (Recinfo.global_attribute12 IS NULL)
1873 AND (X_global_attribute12 IS NULL)))
1874 AND ( (Recinfo.global_attribute13 = X_global_attribute13)
1875 OR ( (Recinfo.global_attribute13 IS NULL)
1876 AND (X_global_attribute13 IS NULL)))
1877 AND ( (Recinfo.global_attribute14 = X_global_attribute14)
1878 OR ( (Recinfo.global_attribute14 IS NULL)
1879 AND (X_global_attribute14 IS NULL)))
1880 AND ( (Recinfo.global_attribute15 = X_global_attribute15)
1881 OR ( (Recinfo.global_attribute15 IS NULL)
1882 AND (X_global_attribute15 IS NULL)))
1883 AND ( (Recinfo.global_attribute16 = X_global_attribute16)
1884 OR ( (Recinfo.global_attribute16 IS NULL)
1885 AND (X_global_attribute16 IS NULL)))
1886 AND ( (Recinfo.global_attribute17 = X_global_attribute17)
1887 OR ( (Recinfo.global_attribute17 IS NULL)
1888 AND (X_global_attribute17 IS NULL)))
1889 AND ( (Recinfo.global_attribute18 = X_global_attribute18)
1890 OR ( (Recinfo.global_attribute18 IS NULL)
1891 AND (X_global_attribute18 IS NULL)))
1892 AND ( (Recinfo.global_attribute19 = X_global_attribute19)
1893 OR ( (Recinfo.global_attribute19 IS NULL)
1894 AND (X_global_attribute19 IS NULL)))
1895 AND ( (Recinfo.global_attribute20 = X_global_attribute20)
1896 OR ( (Recinfo.global_attribute20 IS NULL)
1897 AND (X_global_attribute20 IS NULL)))
1898 )
1899 then
1900 return;
1901 else
1902 FND_MESSAGE.Set_Name('FND','FORM_RECORD_CHANGED');
1903 FND_MSG_PUB.ADD;
1904 APP_EXCEPTION.Raise_Exception;
1905 end if;
1906
1907 EXCEPTION
1908 WHEN OTHERS THEN
1909 IF (SQLCODE <> -20001) THEN
1910 IF (SQLCODE = -54) THEN
1911 FND_MESSAGE.SET_NAME('SQLAP','AP_RESOURCE_BUSY');
1912 FND_MSG_PUB.ADD;
1913 ELSE
1914 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1915 FND_MSG_PUB.ADD;
1916 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1917 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
1918 FND_MESSAGE.SET_TOKEN('PARAMETERS','ROWID = ' || X_Rowid ||
1919 ', VENDOR_ID = ' || x_Vendor_Id);
1920 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
1921 END IF;
1922 END IF;
1923 APP_EXCEPTION.RAISE_EXCEPTION;
1924
1925 END Lock_Row;
1926 --
1927 --
1928 --
1929 PROCEDURE check_unique_vendor_name ( p_vendor_id in number,
1930 p_vendor_name in varchar2,
1931 X_calling_sequence in varchar2) is
1932 L_overlap_count number;
1933
1934 current_calling_sequence VARCHAR2(2000);
1935 debug_info VARCHAR2(100);
1936 --
1940 current_calling_sequence := 'AP_VENDORS_PKG.CHECK_UNIQUE_VENDOR_NAME<-' ||
1937 BEGIN
1938 -- Update the calling sequence
1939 --
1941 X_calling_sequence;
1942
1943 debug_info := 'Count vendors with same name';
1944 SELECT count(1)
1945 INTO L_overlap_count
1946 FROM po_vendors
1947 WHERE (p_vendor_id is null OR vendor_id <> p_vendor_id)
1948 AND (vendor_name like UPPER(SUBSTR(p_vendor_name,1,2))||'%'
1949 OR vendor_name like LOWER(SUBSTR(p_vendor_name,1,2))||'%'
1950 OR vendor_name like INITCAP(SUBSTR(p_vendor_name,1,2))||'%'
1951 OR vendor_name like LOWER(SUBSTR(p_vendor_name,1,1))||
1952 UPPER(SUBSTR(p_vendor_name,2,1))||'%')
1953 AND UPPER(vendor_name) = UPPER(p_vendor_name);
1954
1955 if (L_overlap_count >= 1 ) then
1956 fnd_message.set_name('SQLAP','AP_VEN_DUPLICATE_NAME');
1957 FND_MSG_PUB.ADD;
1958 app_exception.raise_exception;
1959 end if;
1960 --
1961 --
1962 EXCEPTION
1963 WHEN OTHERS THEN
1964 IF (SQLCODE <> -20001) THEN
1965 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1966 FND_MSG_PUB.ADD;
1967 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1968 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
1969 FND_MESSAGE.SET_TOKEN('PARAMETERS', 'VENDOR_ID = ' || p_vendor_id ||
1970 ', VENDOR_NAME = ' || p_vendor_name);
1971 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
1972 END IF;
1973 APP_EXCEPTION.RAISE_EXCEPTION;
1974
1975 END check_unique_vendor_name;
1976 --
1977 --
1978 procedure check_denormalized_vendor_name ( p_vendor_id in number,
1979 p_warning_flag in out NOCOPY varchar2,
1980 X_calling_sequence in varchar2) is
1981 --
1982 -- If invoices for this vendor have been selected, warn user, old name
1983 -- will appear on check - denormalised into AP_SELECTED_INVOICES
1984 -- join to sites so that index AP_SELECTED_INVOICES_N2 can be used
1985 -- there is no index on vendor_id in AP_SELECTED_INVOICES
1986 --
1987 l_overlap_count number;
1988 current_calling_sequence VARCHAR2(2000);
1989 debug_info VARCHAR2(100);
1990 --
1991 begin
1992 -- Update the calling sequence
1993 --
1994 current_calling_sequence := 'AP_VENDORS_PKG.CHECK_DENORMALIZED_VENDOR_NAME<-' ||
1995 X_calling_sequence;
1996
1997 debug_info := 'Count overlap count for the vendor_id and site_id';
1998 SELECT count(1)
1999 INTO L_overlap_count
2000 FROM po_vendor_sites s,
2001 ap_selected_invoices i
2002 WHERE s.vendor_id = p_vendor_id
2003 AND i.vendor_site_id = s.vendor_site_id;
2004 --
2005 --
2006 if (L_overlap_count >= 1 ) then
2007 fnd_message.set_name('SQLAP','AP_VENDOR_INV_SELECTED');
2008 FND_MSG_PUB.ADD;
2009 p_warning_flag := 'W';
2010 end if;
2011 --
2012 EXCEPTION
2013 WHEN OTHERS THEN
2014 IF (SQLCODE <> -20001) THEN
2015 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
2016 FND_MSG_PUB.ADD;
2017 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
2018 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
2019 FND_MESSAGE.SET_TOKEN('PARAMETERS', 'VENDOR_ID = ' || p_vendor_id ||
2020 ', WARNING_FLAG = ' || p_warning_flag);
2021 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
2022 END IF;
2023 APP_EXCEPTION.RAISE_EXCEPTION;
2024
2025 end check_denormalized_vendor_name;
2026 --
2027 --
2028 PROCEDURE check_unique_vendor_number ( p_vendor_id in number,
2029 p_vendor_number in varchar2,
2030 X_calling_sequence in varchar2 ) is
2031 --
2032 l_overlap_count number;
2033 current_calling_sequence VARCHAR2(2000);
2034 debug_info VARCHAR2(100);
2035 --
2036 BEGIN
2037 -- Update the calling sequence
2038 --
2039 current_calling_sequence := 'AP_VENDORS_PKG.CHECK_UNIQUE_VENDOR_NUMBER<-' ||
2040 X_calling_sequence;
2041 --
2042 --
2043 debug_info := 'Count overlap for vendor number';
2044 select count(1)
2045 into l_overlap_count
2046 from po_vendors
2047 where segment1 = p_vendor_number
2048 and ( p_vendor_id IS NULL or vendor_id <> p_vendor_id );
2049 --
2050 --
2051 if (l_overlap_count = 0) then
2052 --
2053 --
2054 debug_info := 'Count overlap from po_history_vendors';
2055 select count(1)
2056 into l_overlap_count
2057 from po_history_vendors
2058 where segment1 = p_vendor_number;
2059 --
2060 --
2061 end if;
2062 --
2063 --
2064 if (L_overlap_count >= 1 ) then
2065 fnd_message.set_name('SQLAP','AP_VEN_DUPLICATE_VEN_NUM');
2066 -- Bug 6940256 udhenuko Message set needs to be added to the stack.
2067 FND_MSG_PUB.ADD;
2068 -- Bug 6940256 udhenuko End
2069 app_exception.raise_exception;
2070 end if;
2071 --
2072 --
2073 EXCEPTION
2077 FND_MSG_PUB.ADD;
2074 WHEN OTHERS THEN
2075 IF (SQLCODE <> -20001) THEN
2076 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
2078 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
2079 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
2080 FND_MESSAGE.SET_TOKEN('PARAMETERS', 'VENDOR_ID = ' || p_vendor_id ||
2081 ', VENDOR_NUMBER = ' || p_vendor_number);
2082 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
2083 END IF;
2084 APP_EXCEPTION.RAISE_EXCEPTION;
2085 --
2086 --
2087 END check_unique_vendor_number;
2088 --
2089 --
2090 PROCEDURE CHECK_SELECTED_INVOICES (x_return_count in out NOCOPY number,
2091 x_vendor_id in number,
2092 X_calling_sequence in varchar2) IS
2093 current_calling_sequence VARCHAR2(2000);
2094 debug_info VARCHAR2(100);
2095 BEGIN
2096 -- Update the calling sequence
2097 --
2098 current_calling_sequence := 'AP_VENDORS_PKG.CHECK_SELECTED_INVOICES<-' ||
2099 X_calling_sequence;
2100
2101 debug_info := 'Count from AP_selected_invoices';
2102 select count(1)
2103 into x_return_count
2104 from AP_selected_invoices
2105 where vendor_id = x_vendor_id;
2106
2107 EXCEPTION
2108 WHEN OTHERS THEN
2109 IF (SQLCODE <> -20001) THEN
2110 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
2111 FND_MSG_PUB.ADD;
2112 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
2113 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
2114 FND_MESSAGE.SET_TOKEN('PARAMETERS','RETURN_COUNT = ' ||
2115 x_return_count ||
2116 ', VENDOR_ID = ' || x_vendor_id);
2117 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
2118 END IF;
2119 APP_EXCEPTION.RAISE_EXCEPTION;
2120
2121
2122 END CHECK_SELECTED_INVOICES;
2123
2124 procedure Check_Duplicate_Employee (p_rowid in varchar2,
2125 p_employee_id in number,
2126 X_calling_sequence in varchar2) is
2127 L_Duplicate_count number;
2128 current_calling_sequence VARCHAR2(2000);
2129 debug_info VARCHAR2(100);
2130 begin
2131 -- Update the calling sequence
2132 --
2133 current_calling_sequence := 'AP_VENDORS_PKG.CHECK_DUPLICATE_EMPLOYEE<-' ||
2134 X_calling_sequence;
2135
2136 debug_info := 'Count for same employee_id';
2137 SELECT count(1)
2138 INTO L_Duplicate_Count
2139 FROM PO_VENDORS
2140 WHERE (p_rowid IS NULL OR rowid <> p_rowid)
2141 AND employee_id = p_employee_id;
2142
2143 if (L_Duplicate_count > 0 ) then
2144 fnd_message.set_name('SQLAP','AP_EMPLOYEE_ASSIGNED');
2145 FND_MSG_PUB.ADD;
2146 app_exception.raise_exception;
2147 end if;
2148
2149 EXCEPTION
2150 WHEN OTHERS THEN
2151 IF (SQLCODE <> -20001) THEN
2152 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
2153 FND_MSG_PUB.ADD;
2154 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
2155 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
2156 FND_MESSAGE.SET_TOKEN('PARAMETERS','ROWID = ' || p_rowid ||
2157 ', EMPLOYEE_ID = ' || p_employee_id);
2158 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
2159 END IF;
2160 APP_EXCEPTION.RAISE_EXCEPTION;
2161
2162 end Check_Duplicate_Employee;
2163 --
2164 --
2165 procedure Resolve_employee( x_employee_id in number,
2166 x_employee_name in out NOCOPY varchar2,
2167 x_employee_number in out NOCOPY varchar2,
2168 X_calling_sequence in varchar2) IS
2169
2170 current_calling_sequence VARCHAR2(2000);
2171 debug_info VARCHAR2(100);
2172 begin
2173
2174 -- Update the calling sequence
2175 --
2176 current_calling_sequence := 'AP_VENDORS_PKG.RESOLVE_EMPLOYEE<-' ||
2177 X_calling_sequence;
2178
2179 -- For bug 2437569. Changed the view from hr_employees to
2180 -- hr_employees_current_v. This is to retrive the record details
2181 -- of only active employees .
2182
2183 -- For bug2900352. Backing out the changes done for bug 2437569 .
2184
2185
2186 debug_info := 'Get employee name and number from HR_Employees using ID';
2187 select full_name,
2188 employee_num
2189 into x_Employee_name,
2190 x_Employee_number
2191 from hr_employees --bug: 2900352
2192 where employee_id = x_employee_id;
2193
2194 EXCEPTION
2195 WHEN NO_DATA_FOUND THEN
2196 x_employee_name := '';
2197 x_employee_number := '';
2198 WHEN OTHERS THEN
2199 IF (SQLCODE <> -20001) THEN
2200 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
2201 FND_MSG_PUB.ADD;
2202 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
2203 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
2204 FND_MESSAGE.SET_TOKEN('PARAMETERS',', EMPLOYEE_ID = ' || x_employee_id);
2205 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
2206 END IF;
2207 APP_EXCEPTION.RAISE_EXCEPTION;
2208
2209 end Resolve_employee;
2210
2211
2212 procedure get_message_text ( x_application in varchar2,
2213 x_message_name in varchar2,
2214 x_message_text in out NOCOPY varchar2) is
2215
2216 begin
2217 x_message_text := fnd_message.get_string( x_application,
2218 x_message_name);
2219 if x_message_text is null then
2220 x_message_text := x_message_name;
2221 end if;
2222
2223 end Get_Message_text;
2224
2225 /* bug6830122. Creating Autonomus transaction for
2226 automatic supplier numbering for avoiding locikng
2227 contention for product setup table */
2228
2229 FUNCTION Update_Product_Setup return number is
2230 PRAGMA AUTONOMOUS_TRANSACTION;
2231
2232 CURSOR ap_product_setup_c is
2233 SELECT next_auto_supplier_num
2234 FROM ap_product_setup
2235 FOR UPDATE OF next_auto_supplier_num;
2236
2237 l_segment1 ap_product_setup.next_auto_supplier_num%type;
2238
2239 BEGIN
2240
2241 Open ap_product_setup_c;
2242 Fetch ap_product_setup_c into l_segment1;
2243
2244 If(ap_product_setup_c%notfound) Then
2245 RAISE NO_DATA_FOUND;
2246 End if;
2247 Close ap_product_setup_c;
2248
2249 UPDATE ap_product_setup
2250 SET next_auto_supplier_num = next_auto_supplier_num + 1;
2251 commit;
2252
2253 return l_segment1;
2254
2255 END Update_Product_Setup;
2256
2257 END AP_VENDORS_PKG;