[Home] [Help]
PACKAGE BODY: APPS.AP_VENDORS_PKG
Source
1 PACKAGE BODY AP_VENDORS_PKG as
2 /* $Header: apvndhrb.pls 120.20 2011/07/05 12:34:29 lkarna 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,
399 x_Invoice_Currency_Code,
400 x_Payment_Currency_Code,
401 x_Invoice_Amount_Limit,
402 x_Hold_All_Payments_Flag,
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
558
559 debug_info := 'assign automatic Vendor number';
560
561 -- Bug 6830122. Replacing following two statements with
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 vendor_name,/*Bug 9665294 */
595 summary_flag,
596 enabled_flag,
597 last_update_login,
598 creation_date,
599 created_by,
600 employee_id,
601 validation_number,
602 vendor_type_lookup_code,
603 customer_num,
604 standard_industry_class, -- Bug 5066199
605 one_time_flag,
606 parent_vendor_id,
607 min_order_amount,
608 terms_id,
609 set_of_books_id,
610 always_take_disc_flag,
611 pay_date_basis_lookup_code,
612 pay_group_lookup_code,
613 payment_priority,
614 invoice_currency_code,
615 payment_currency_code,
616 invoice_amount_limit,
617 hold_all_payments_flag,
618 hold_future_payments_flag,
619 hold_reason,
620 individual_1099,--bug6050423
621 type_1099,
622 withholding_status_lookup_code,
623 withholding_start_date,
624 organization_type_lookup_code,
625 start_date_active,
626 end_date_active,
627 qty_rcv_tolerance,
628 minority_group_lookup_code,
629 women_owned_flag,
630 small_business_flag,
631 attribute_category,
632 attribute1,
633 attribute2,
634 attribute3,
635 attribute4,
636 attribute5,
637 hold_flag,
638 purchasing_hold_reason,
639 hold_by,
640 hold_date,
641 terms_date_basis,
642 attribute10,
643 attribute11,
644 attribute12,
645 attribute13,
646 attribute14,
647 attribute15,
648 attribute6,
649 attribute7,
650 attribute8,
651 attribute9,
652 days_early_receipt_allowed,
653 days_late_receipt_allowed,
654 enforce_ship_to_location_code,
655 federal_reportable_flag,
656 hold_unmatched_invoices_flag,
657 match_option,
658 create_debit_memo_flag,
659 inspection_required_flag,
660 receipt_required_flag,
661 receiving_routing_id,
662 state_reportable_flag,
663 tax_verification_date,
664 auto_calculate_interest_flag,
665 name_control,
666 allow_substitute_receipts_flag,
667 allow_unordered_receipts_flag,
668 receipt_days_exception_code,
669 qty_rcv_exception_code,
670 exclude_freight_from_discount,
671 tax_reporting_name,
672 awt_group_id,
673 pay_awt_group_id,--bug6664407
674 check_digits,
675 allow_awt_flag,
676
677 --start of bug 12678066
678 request_id,
679 program_application_id,
680 program_id,
681 program_update_date,
682 --end of bug 12678066
683
684 global_attribute_category,
685 global_attribute1,
686 global_attribute2,
687 global_attribute3,
688 global_attribute4,
689 global_attribute5,
690 global_attribute6,
691 global_attribute7,
692 global_attribute8,
693 global_attribute9,
694 global_attribute10,
695 global_attribute11,
696 global_attribute12,
697 global_attribute13,
698 global_attribute14,
699 global_attribute15,
700 global_attribute16,
701 global_attribute17,
702 global_attribute18,
703 global_attribute19,
704 global_attribute20,
705 bank_charge_bearer,
706 party_id,
707 parent_party_id,
708 ni_number)
709 VALUES (
710 x_Vendor_Id,
711 p_Last_Update_Date,
712 p_Last_Updated_By,
713 decode(l_supplier_numbering_method, 'AUTOMATIC', l_segment1,
714 p_vendor_rec.segment1),
715 p_vendor_rec.vendor_name, /* Bug 9665294 */
716 nvl(p_vendor_rec.summary_flag, 'N'),
717 nvl(p_vendor_rec.enabled_flag, 'Y'),
718 p_Last_Update_Login,
719 p_Creation_Date,
720 p_Created_By,
721 p_vendor_rec.Employee_Id,
722 p_vendor_rec.Validation_Number,
723 p_vendor_rec.Vendor_Type_Lookup_Code,
724 p_vendor_rec.Customer_Num,
725 p_vendor_rec.sic_code, -- Bug 5066199
726 p_vendor_rec.One_Time_Flag,
727 p_vendor_rec.Parent_Vendor_Id,
728 p_vendor_rec.Min_Order_Amount,
729 p_vendor_rec.Terms_Id,
730 p_vendor_rec.Set_Of_Books_Id,
731 p_vendor_rec.Always_Take_Disc_Flag,
732 p_vendor_rec.Pay_Date_Basis_Lookup_Code,
733 p_vendor_rec.Pay_Group_Lookup_Code,
734 p_vendor_rec.Payment_Priority,
735 p_vendor_rec.Invoice_Currency_Code,
736 p_vendor_rec.Payment_Currency_Code,
737 p_vendor_rec.Invoice_Amount_Limit,
738 p_vendor_rec.Hold_All_Payments_Flag,
739 p_vendor_rec.Hold_Future_Payments_Flag,
740 p_vendor_rec.Hold_Reason,
741 --bug6050423 starts.System inserts the taxpayer of
742 --non-employee individuals to the individual_1099 field.
743 --we donot insert any value to num_1099,becas we update
744 --it using the ap_tca_sync_pkg.sync_supplier
745 --bug6691916.commented the below decode statement and added
746 --the one below that.As per analysis,only organization type lookup
747 --code of individual or foreign individual are considered
748 --as individual suppliers
749 /*decode(UPPER(p_vendor_rec.Vendor_Type_Lookup_Code),'CONTRACTOR',
750 decode(UPPER(p_vendor_rec.Organization_Type_Lookup_Code),
751 'INDIVIDUAL',p_vendor_rec.jgzz_fiscal_code,
752 'FOREIGN INDIVIDUAL',p_vendor_rec.jgzz_fiscal_code,
753 'PARTNERSHIP',p_vendor_rec.jgzz_fiscal_code,
754 'INDIVIDUAL PARTNERSHIP',p_vendor_rec.jgzz_fiscal_code,
755 NULL),
756 NULL),*/
757 decode(UPPER(p_vendor_rec.Organization_Type_Lookup_Code),
758 'INDIVIDUAL',p_vendor_rec.jgzz_fiscal_code,
759 'FOREIGN INDIVIDUAL',p_vendor_rec.jgzz_fiscal_code,
760 NULL),
761 --bug6050423 ends
762 p_vendor_rec.Type_1099,
763 p_vendor_rec.withholding_status_Lookup_Code,
764 p_vendor_rec.Withholding_Start_Date,
765 p_vendor_rec.Organization_Type_Lookup_Code,
766 p_vendor_rec.Start_Date_Active,
767 p_vendor_rec.End_Date_Active,
768 p_vendor_rec.Qty_Rcv_Tolerance,
769 p_vendor_rec.Minority_Group_Lookup_Code,
770 p_vendor_rec.Women_Owned_Flag,
771 p_vendor_rec.Small_Business_Flag,
772 p_vendor_rec.Attribute_Category,
773 p_vendor_rec.Attribute1,
774 p_vendor_rec.Attribute2,
775 p_vendor_rec.Attribute3,
776 p_vendor_rec.Attribute4,
777 p_vendor_rec.Attribute5,
778 p_vendor_rec.Hold_Flag,
779 p_vendor_rec.Purchasing_Hold_Reason,
780 p_vendor_rec.Hold_By,
781 p_vendor_rec.Hold_Date,
782 p_vendor_rec.Terms_Date_Basis,
783 p_vendor_rec.Attribute10,
784 p_vendor_rec.Attribute11,
785 p_vendor_rec.Attribute12,
786 p_vendor_rec.Attribute13,
787 p_vendor_rec.Attribute14,
788 p_vendor_rec.Attribute15,
789 p_vendor_rec.Attribute6,
790 p_vendor_rec.Attribute7,
791 p_vendor_rec.Attribute8,
792 p_vendor_rec.Attribute9,
793 p_vendor_rec.Days_Early_Receipt_Allowed,
794 p_vendor_rec.Days_Late_Receipt_Allowed,
795 p_vendor_rec.Enforce_Ship_To_Location_Code,
796 p_vendor_rec.Federal_Reportable_Flag,
797 p_vendor_rec.Hold_Unmatched_Invoices_Flag,
798 p_vendor_rec.match_option,
799 p_vendor_rec.create_debit_memo_flag,
800 p_vendor_rec.Inspection_Required_Flag,
801 p_vendor_rec.Receipt_Required_Flag,
802 p_vendor_rec.Receiving_Routing_Id,
803 p_vendor_rec.State_Reportable_Flag,
804 p_vendor_rec.Tax_Verification_Date,
805 p_vendor_rec.Auto_Calculate_Interest_Flag,
806 p_vendor_rec.Name_Control,
807 p_vendor_rec.allow_substitute_receipts_flag,
808 p_vendor_rec.allow_unordered_receipts_flag,
809 p_vendor_rec.Receipt_Days_Exception_Code,
810 p_vendor_rec.Qty_Rcv_Exception_Code,
811 p_vendor_rec.Exclude_Freight_From_Discount,
812 p_vendor_rec.Tax_Reporting_Name,
813 p_vendor_rec.Awt_Group_Id,
814 p_vendor_rec.Pay_Awt_Group_Id,--bug6664407
815 p_vendor_rec.Check_Digits,
816 p_vendor_rec.Allow_Awt_Flag,
817
818 --start of bug 12678066
819 p_request_id,
820 p_program_application_id,
821 p_program_id,
822 p_program_update_date,
823 --end of bug 12678066
824
825 p_vendor_rec.global_attribute_category,
826 p_vendor_rec.global_attribute1,
827 p_vendor_rec.global_attribute2,
828 p_vendor_rec.global_attribute3,
829 p_vendor_rec.global_attribute4,
830 p_vendor_rec.global_attribute5,
831 p_vendor_rec.global_attribute6,
832 p_vendor_rec.global_attribute7,
833 p_vendor_rec.global_attribute8,
834 p_vendor_rec.global_attribute9,
835 p_vendor_rec.global_attribute10,
836 p_vendor_rec.global_attribute11,
837 p_vendor_rec.global_attribute12,
838 p_vendor_rec.global_attribute13,
839 p_vendor_rec.global_attribute14,
840 p_vendor_rec.global_attribute15,
841 p_vendor_rec.global_attribute16,
842 p_vendor_rec.global_attribute17,
843 p_vendor_rec.global_attribute18,
844 p_vendor_rec.global_attribute19,
845 p_vendor_rec.global_attribute20,
846 p_vendor_rec.Bank_Charge_Bearer,
847 p_vendor_rec.party_id,
848 p_vendor_rec.parent_party_id,
849 p_vendor_rec.NI_Number);
850 --
851 debug_info := 'Open cursor C';
852 OPEN C;
853 debug_info := 'Fetch cursor C';
854 FETCH C INTO x_Rowid;
855 if (C%NOTFOUND) then
856 debug_info := 'Close cursor C - NOTFOUND';
857 CLOSE C;
858 Raise NO_DATA_FOUND;
859 end if;
860 debug_info := 'Close cursor C';
861 CLOSE C;
862
863 EXCEPTION
864 WHEN OTHERS THEN
865 IF (SQLCODE <> -20001) THEN
866 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
867 FND_MSG_PUB.ADD;
868 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
869 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
870 FND_MESSAGE.SET_TOKEN('PARAMETERS','ROWID = ' || x_Rowid ||
871 ', VENDOR_ID = ' || x_vendor_Id);
872 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
873 END IF;
874 APP_EXCEPTION.RAISE_EXCEPTION;
875
876 END Insert_Row;
877 --
878 --
879 PROCEDURE Update_Row (
880 x_Rowid VARCHAR2,
881 x_Vendor_Id NUMBER,
882 x_Last_Update_Date DATE,
883 x_Last_Updated_By NUMBER,
884 x_Vendor_Name VARCHAR2,
885 x_Segment1 VARCHAR2,
886 x_Summary_Flag VARCHAR2,
887 x_Enabled_Flag VARCHAR2,
888 x_Last_Update_Login NUMBER,
889 x_Employee_Id NUMBER,
890 x_Validation_Number NUMBER,
891 x_Vendor_Type_Lookup_Code VARCHAR2,
892 x_Customer_Num VARCHAR2,
893 x_One_Time_Flag VARCHAR2,
894 x_Parent_Vendor_Id NUMBER,
895 x_Min_Order_Amount NUMBER,
896 /* x_Ship_To_Location_Id NUMBER,
897 x_Bill_To_Location_Id NUMBER,
898 x_Ship_Via_Lookup_Code VARCHAR2,
899 x_Freight_Terms_Lookup_Code VARCHAR2,
900 x_Fob_Lookup_Code VARCHAR2, */
901 x_Terms_Id NUMBER,
902 x_Set_Of_Books_Id NUMBER,
903 x_Always_Take_Disc_Flag VARCHAR2,
904 x_Pay_Date_Basis_Lookup_Code VARCHAR2,
905 x_Pay_Group_Lookup_Code VARCHAR2,
906 x_Payment_Priority NUMBER,
907 x_Invoice_Currency_Code VARCHAR2,
908 x_Payment_Currency_Code VARCHAR2,
909 x_Invoice_Amount_Limit NUMBER,
910 x_Hold_All_Payments_Flag VARCHAR2,
911 x_Hold_Future_Payments_Flag VARCHAR2,
912 x_Hold_Reason VARCHAR2,
913 /* x_Distribution_Set_Id NUMBER,
914 x_Accts_Pay_CCID NUMBER,
915 x_Future_Dated_Payment_CCID NUMBER,
916 x_Prepay_CCID NUMBER, */
917 x_Num_1099 VARCHAR2,
918 x_Type_1099 VARCHAR2,
919 x_withholding_stat_Lookup_Code VARCHAR2,
920 x_Withholding_Start_Date DATE,
921 x_Org_Type_Lookup_Code VARCHAR2,
922 -- eTax Uptake x_Vat_Code VARCHAR2,
923 x_Start_Date_Active DATE,
924 x_End_Date_Active DATE,
925 x_Qty_Rcv_Tolerance NUMBER,
926 x_Minority_Group_Lookup_Code VARCHAR2,
927 x_Bank_Account_Name VARCHAR2,
928 x_Bank_Account_Num VARCHAR2,
929 x_Bank_Num VARCHAR2,
930 x_Bank_Account_Type VARCHAR2,
931 x_Women_Owned_Flag VARCHAR2,
932 x_Small_Business_Flag VARCHAR2,
933 x_Standard_Industry_Class VARCHAR2,
934 x_Attribute_Category VARCHAR2,
935 x_Attribute1 VARCHAR2,
936 x_Attribute2 VARCHAR2,
937 x_Attribute3 VARCHAR2,
938 x_Attribute4 VARCHAR2,
939 x_Attribute5 VARCHAR2,
940 x_Hold_Flag VARCHAR2,
941 x_Purchasing_Hold_Reason VARCHAR2,
942 x_Hold_By NUMBER,
943 x_Hold_Date DATE,
944 x_Terms_Date_Basis VARCHAR2,
945 x_Price_Tolerance NUMBER,
946 x_Attribute10 VARCHAR2,
947 x_Attribute11 VARCHAR2,
948 x_Attribute12 VARCHAR2,
949 x_Attribute13 VARCHAR2,
950 x_Attribute14 VARCHAR2,
951 x_Attribute15 VARCHAR2,
952 x_Attribute6 VARCHAR2,
953 x_Attribute7 VARCHAR2,
954 x_Attribute8 VARCHAR2,
955 x_Attribute9 VARCHAR2,
956 x_Days_Early_Receipt_Allowed NUMBER,
957 x_Days_Late_Receipt_Allowed NUMBER,
958 x_Enforce_Ship_To_Loc_Code VARCHAR2,
959 x_Federal_Reportable_Flag VARCHAR2,
960 x_Hold_Unmatched_Invoices_Flag VARCHAR2,
961 x_match_option VARCHAR2,
962 x_create_debit_memo_flag VARCHAR2,
963 x_Inspection_Required_Flag VARCHAR2,
964 x_Receipt_Required_Flag VARCHAR2,
965 x_Receiving_Routing_Id NUMBER,
966 x_State_Reportable_Flag VARCHAR2,
967 x_Tax_Verification_Date DATE,
968 x_Auto_Calculate_Interest_Flag VARCHAR2,
969 x_Name_Control VARCHAR2,
970 x_Allow_Subst_Receipts_Flag VARCHAR2,
971 x_Allow_Unord_Receipts_Flag VARCHAR2,
972 x_Receipt_Days_Exception_Code VARCHAR2,
973 x_Qty_Rcv_Exception_Code VARCHAR2,
974 -- eTax Uptake x_Offset_Tax_Flag VARCHAR2,
975 x_Exclude_Freight_From_Disc VARCHAR2,
976 x_Vat_Registration_Num VARCHAR2,
977 x_Tax_Reporting_Name VARCHAR2,
978 x_Awt_Group_Id NUMBER,
979 x_Pay_Awt_Group_Id NUMBER,--bug6664407
980 x_Check_Digits VARCHAR2,
981 x_Bank_Number VARCHAR2,
982 x_Allow_Awt_Flag VARCHAR2,
983 x_Bank_Branch_Type VARCHAR2,
984 /* eTax Uptake
985 x_Auto_Tax_Calc_Flag VARCHAR2,
986 x_Auto_Tax_Calc_Override VARCHAR2,
987 x_Amount_Includes_Tax_Flag VARCHAR2,
988 x_AP_Tax_Rounding_Rule VARCHAR2, */
989 x_Vendor_Name_Alt VARCHAR2,
990 X_global_attribute_category VARCHAR2 DEFAULT NULL,
991 X_global_attribute1 VARCHAR2 DEFAULT NULL,
992 X_global_attribute2 VARCHAR2 DEFAULT NULL,
993 X_global_attribute3 VARCHAR2 DEFAULT NULL,
994 X_global_attribute4 VARCHAR2 DEFAULT NULL,
995 X_global_attribute5 VARCHAR2 DEFAULT NULL,
996 X_global_attribute6 VARCHAR2 DEFAULT NULL,
997 X_global_attribute7 VARCHAR2 DEFAULT NULL,
998 X_global_attribute8 VARCHAR2 DEFAULT NULL,
999 X_global_attribute9 VARCHAR2 DEFAULT NULL,
1000 X_global_attribute10 VARCHAR2 DEFAULT NULL,
1001 X_global_attribute11 VARCHAR2 DEFAULT NULL,
1002 X_global_attribute12 VARCHAR2 DEFAULT NULL,
1003 X_global_attribute13 VARCHAR2 DEFAULT NULL,
1004 X_global_attribute14 VARCHAR2 DEFAULT NULL,
1005 X_global_attribute15 VARCHAR2 DEFAULT NULL,
1006 X_global_attribute16 VARCHAR2 DEFAULT NULL,
1007 X_global_attribute17 VARCHAR2 DEFAULT NULL,
1008 X_global_attribute18 VARCHAR2 DEFAULT NULL,
1009 X_global_attribute19 VARCHAR2 DEFAULT NULL,
1010 X_global_attribute20 VARCHAR2 DEFAULT NULL,
1011 X_Bank_Charge_Bearer VARCHAR2 DEFAULT NULL,
1012 X_NI_Number VARCHAR2 DEFAULT NULL,
1013 X_calling_sequence IN VARCHAR2 ) IS
1014
1015 current_calling_sequence VARCHAR2(2000);
1016 debug_info VARCHAR2(100);
1017 BEGIN
1018 -- Update the calling sequence
1019 --
1020 current_calling_sequence := 'AP_VENDORS_PKG.UPDATE_ROW<-' ||
1021 X_calling_sequence;
1022 --
1023 -- error out NOCOPY if Vendor name has been assigned by another session
1024 --
1025 ap_vendors_pkg.check_unique_vendor_name( p_vendor_id => x_vendor_id,
1026 p_vendor_name => x_vendor_name,
1027 X_calling_sequence => current_calling_sequence);
1028 --
1029 -- check for duplicate vendor_numbers
1030 --
1031 ap_vendors_pkg.check_unique_vendor_number( p_vendor_id => x_vendor_id,
1032 p_vendor_number => x_segment1,
1033 X_calling_sequence => current_calling_sequence);
1034 --
1035 -- error out NOCOPY if employee has been assigned by another session
1036 --
1037 ap_vendors_pkg.Check_Duplicate_Employee(p_rowid => x_rowid,
1038 p_employee_id => x_employee_id,
1039 X_calling_sequence => current_calling_sequence);
1040
1041 debug_info := 'Update PO_VENDORS';
1042 UPDATE ap_suppliers
1043 SET
1044 vendor_id = x_Vendor_Id,
1045 last_update_date = x_Last_Update_Date,
1046 last_updated_by = x_Last_Updated_By,
1047 vendor_name = x_Vendor_Name,
1048 segment1 = x_Segment1,
1049 summary_flag = x_Summary_Flag,
1050 enabled_flag = x_Enabled_Flag,
1051 last_update_login = x_Last_Update_Login,
1052 employee_id = x_Employee_Id,
1053 validation_number = x_Validation_Number,
1054 vendor_type_lookup_code = x_Vendor_Type_Lookup_Code,
1055 customer_num = x_Customer_Num,
1056 one_time_flag = x_One_Time_Flag,
1057 parent_vendor_id = x_Parent_Vendor_Id,
1058 min_order_amount = x_Min_Order_Amount,
1059 --Bug :2809214 MOAC - Supplier Attribute Change Project
1060 /* ship_to_location_id = x_Ship_To_Location_Id,
1061 bill_to_location_id = x_Bill_To_Location_Id,
1062 ship_via_lookup_code = x_Ship_Via_Lookup_Code,
1063 freight_terms_lookup_code = x_Freight_Terms_Lookup_Code,
1064 fob_lookup_code = x_Fob_Lookup_Code, */
1065 terms_id = x_Terms_Id,
1066 set_of_books_id = x_Set_Of_Books_Id,
1067 always_take_disc_flag = x_Always_Take_Disc_Flag,
1068 pay_date_basis_lookup_code = x_Pay_Date_Basis_Lookup_Code,
1069 pay_group_lookup_code = x_Pay_Group_Lookup_Code,
1070 payment_priority = x_Payment_Priority,
1071 invoice_currency_code = x_Invoice_Currency_Code,
1072 payment_currency_code = x_Payment_Currency_Code,
1073 invoice_amount_limit = x_Invoice_Amount_Limit,
1074 hold_all_payments_flag = x_Hold_All_Payments_Flag,
1075 hold_future_payments_flag = x_Hold_Future_Payments_Flag,
1076 hold_reason = x_Hold_Reason,
1077 --Bug :2809214 MOAC - Supplier Attribute Change Project
1078 /* distribution_set_id = x_Distribution_Set_Id,
1079 accts_pay_code_combination_id = x_Accts_Pay_CCID,
1080 future_dated_payment_ccid = x_Future_Dated_Payment_CCID,
1081 prepay_code_combination_id = x_Prepay_CCID, */
1082 num_1099 = x_Num_1099,
1083 type_1099 = x_Type_1099,
1084 withholding_status_lookup_code = x_withholding_stat_Lookup_Code,
1085 withholding_start_date = x_Withholding_Start_Date,
1086 organization_type_lookup_code = x_Org_Type_Lookup_Code,
1087 start_date_active = x_Start_Date_Active,
1088 end_date_active = x_End_Date_Active,
1089 qty_rcv_tolerance = x_Qty_Rcv_Tolerance,
1090 minority_group_lookup_code = x_Minority_Group_Lookup_Code,
1091 bank_account_name = x_Bank_Account_Name,
1092 bank_account_num = x_Bank_Account_Num,
1093 bank_num = x_Bank_Num,
1094 bank_account_type = x_Bank_Account_Type,
1095 women_owned_flag = x_Women_Owned_Flag,
1096 small_business_flag = x_Small_Business_Flag,
1097 standard_industry_class = x_Standard_Industry_Class,
1098 attribute_category = x_Attribute_Category,
1099 attribute1 = x_Attribute1,
1100 attribute2 = x_Attribute2,
1101 attribute3 = x_Attribute3,
1102 attribute4 = x_Attribute4,
1103 attribute5 = x_Attribute5,
1104 hold_flag = x_Hold_Flag,
1105 purchasing_hold_reason = x_Purchasing_Hold_Reason,
1106 hold_by = x_Hold_By,
1107 hold_date = x_Hold_Date,
1108 terms_date_basis = x_Terms_Date_Basis,
1109 price_tolerance = x_Price_Tolerance,
1110 attribute10 = x_Attribute10,
1111 attribute11 = x_Attribute11,
1112 attribute12 = x_Attribute12,
1113 attribute13 = x_Attribute13,
1114 attribute14 = x_Attribute14,
1115 attribute15 = x_Attribute15,
1116 attribute6 = x_Attribute6,
1117 attribute7 = x_Attribute7,
1118 attribute8 = x_Attribute8,
1119 attribute9 = x_Attribute9,
1120 days_early_receipt_allowed = x_Days_Early_Receipt_Allowed,
1121 days_late_receipt_allowed = x_Days_Late_Receipt_Allowed,
1122 enforce_ship_to_location_code = x_Enforce_Ship_To_Loc_Code,
1123 federal_reportable_flag = x_Federal_Reportable_Flag,
1124 hold_unmatched_invoices_flag = x_Hold_Unmatched_Invoices_Flag,
1125 match_option = x_match_option,
1126 create_debit_memo_flag = x_create_debit_memo_flag,
1127 inspection_required_flag = x_Inspection_Required_Flag,
1128 receipt_required_flag = x_Receipt_Required_Flag,
1129 receiving_routing_id = x_Receiving_Routing_Id,
1130 state_reportable_flag = x_State_Reportable_Flag,
1131 tax_verification_date = x_Tax_Verification_Date,
1132 auto_calculate_interest_flag = x_Auto_Calculate_Interest_Flag,
1133 name_control = x_Name_Control,
1134 allow_substitute_receipts_flag = x_Allow_Subst_Receipts_Flag,
1135 allow_unordered_receipts_flag = x_Allow_Unord_Receipts_Flag,
1136 receipt_days_exception_code = x_Receipt_Days_Exception_Code,
1137 qty_rcv_exception_code = x_Qty_Rcv_Exception_Code,
1138 exclude_freight_from_discount = x_Exclude_Freight_From_Disc,
1139 vat_registration_num = x_Vat_Registration_Num,
1140 tax_reporting_name = x_Tax_Reporting_Name,
1141 awt_group_id = x_Awt_Group_Id,
1142 pay_awt_group_id = x_Pay_Awt_Group_Id,--bug6664407
1143 check_digits = x_Check_Digits,
1144 bank_number = x_Bank_Number,
1145 allow_awt_flag = x_Allow_Awt_Flag,
1146 bank_branch_type = x_bank_branch_type,
1147 vendor_name_alt = x_Vendor_Name_Alt,
1148 global_attribute_category = X_global_attribute_category,
1149 global_attribute1 = X_global_attribute1,
1150 global_attribute2 = X_global_attribute2,
1151 global_attribute3 = X_global_attribute3,
1152 global_attribute4 = X_global_attribute4,
1153 global_attribute5 = X_global_attribute5,
1154 global_attribute6 = X_global_attribute6,
1155 global_attribute7 = X_global_attribute7,
1156 global_attribute8 = X_global_attribute8,
1157 global_attribute9 = X_global_attribute9,
1158 global_attribute10 = X_global_attribute10,
1159 global_attribute11 = X_global_attribute11,
1160 global_attribute12 = X_global_attribute12,
1161 global_attribute13 = X_global_attribute13,
1162 global_attribute14 = X_global_attribute14,
1163 global_attribute15 = X_global_attribute15,
1164 global_attribute16 = X_global_attribute16,
1165 global_attribute17 = X_global_attribute17,
1166 global_attribute18 = X_global_attribute18,
1167 global_attribute19 = X_global_attribute19,
1168 global_attribute20 = X_global_attribute20,
1169 bank_charge_bearer = X_Bank_Charge_Bearer,
1170 --Bug :2809214 MOAC - Supplier Attribute Change Project
1171 NI_Number = X_NI_Number
1172 WHERE rowid = x_Rowid;
1173 if (SQL%NOTFOUND) then
1174 Raise NO_DATA_FOUND;
1175 end if;
1176
1177 EXCEPTION
1178 WHEN OTHERS THEN
1179 IF (SQLCODE <> -20001) THEN
1180 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1181 FND_MSG_PUB.ADD;
1182 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1183 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
1184 FND_MESSAGE.SET_TOKEN('PARAMETERS','ROWID = ' || X_Rowid ||
1185 ', VENDOR_ID = ' || x_Vendor_Id);
1186 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
1187 END IF;
1188 APP_EXCEPTION.RAISE_EXCEPTION;
1189
1190 END Update_Row;
1191
1192 PROCEDURE Update_Row(
1193 p_vendor_rec IN AP_VENDOR_PUB_PKG.r_vendor_rec_type,
1194 p_last_update_date IN DATE,
1195 p_last_updated_by IN NUMBER,
1196 p_last_update_login IN NUMBER,
1197 p_request_id IN NUMBER,
1198 p_program_application_id IN NUMBER,
1199 p_program_id IN NUMBER,
1200 p_program_update_date IN DATE,
1201 p_rowid IN VARCHAR2,
1202 p_vendor_id IN NUMBER) IS
1203
1204 current_calling_sequence VARCHAR2(2000);
1205 debug_info VARCHAR2(100);
1206 BEGIN
1207
1208 -- Bug 6216082 Begins. Added the call to IGI package.
1209 -- Bug 7577497 Added another parameter to the function call p_pay_tax_grp_id
1210 IF (p_vendor_rec.Awt_Group_Id IS NOT NULL OR
1211 p_vendor_rec.Pay_Awt_Group_Id IS NOT NULL) THEN
1212 IGI_CIS2007_UTIL_PKG.SUPPLIER_UPDATE(
1213 p_vendor_id => p_vendor_id,
1214 p_tax_grp_id => p_vendor_rec.Awt_Group_Id,
1215 p_pay_tax_grp_id => p_vendor_rec.Pay_Awt_Group_Id
1216 );
1217 END IF;
1218 -- Bug 6216082 Ends.
1219
1220 UPDATE ap_suppliers
1221 SET
1222 last_update_date = p_Last_Update_Date,
1223 last_updated_by = p_Last_Updated_By,
1224 segment1 = p_vendor_rec.Segment1,
1225 summary_flag = p_vendor_rec.Summary_Flag,
1226 enabled_flag = p_vendor_rec.Enabled_Flag,
1227 last_update_login = p_Last_Update_Login,
1228 employee_id = p_vendor_rec.Employee_Id,
1229 validation_number = p_vendor_rec.Validation_Number,
1230 vendor_type_lookup_code = p_vendor_rec.Vendor_Type_Lookup_Code,
1231 customer_num = p_vendor_rec.Customer_Num,
1232 one_time_flag = p_vendor_rec.One_Time_Flag,
1233 parent_vendor_id = p_vendor_rec.Parent_Vendor_Id,
1234 min_order_amount = p_vendor_rec.Min_Order_Amount,
1235 terms_id = p_vendor_rec.Terms_Id,
1236 set_of_books_id = p_vendor_rec.Set_Of_Books_Id,
1237 always_take_disc_flag = p_vendor_rec.Always_Take_Disc_Flag,
1238 pay_date_basis_lookup_code = p_vendor_rec.Pay_Date_Basis_Lookup_Code,
1239 pay_group_lookup_code = p_vendor_rec.Pay_Group_Lookup_Code,
1240 payment_priority = p_vendor_rec.Payment_Priority,
1241 invoice_currency_code = p_vendor_rec.Invoice_Currency_Code,
1242 payment_currency_code = p_vendor_rec.Payment_Currency_Code,
1243 invoice_amount_limit = p_vendor_rec.Invoice_Amount_Limit,
1244 hold_all_payments_flag = p_vendor_rec.Hold_All_Payments_Flag,
1245 hold_future_payments_flag = p_vendor_rec.Hold_Future_Payments_Flag,
1246 hold_reason = p_vendor_rec.Hold_Reason,
1247 --bug6050423 starts
1248 --bug6691916.commented the below assignment statement and added
1249 --the one below that.As per analysis,only organization type lookup
1250 --code of individual or foreign individual are considered
1251 --as individual suppliers
1252 /*individual_1099 = decode(UPPER(p_vendor_rec.Vendor_Type_Lookup_Code),'CONTRACTOR',
1253 decode(UPPER(p_vendor_rec.Organization_Type_Lookup_Code),
1254 'INDIVIDUAL',p_vendor_rec.jgzz_fiscal_code,
1255 'FOREIGN INDIVIDUAL',p_vendor_rec.jgzz_fiscal_code,
1256 'PARTNERSHIP',p_vendor_rec.jgzz_fiscal_code,
1257 'INDIVIDUAL PARTNERSHIP',p_vendor_rec.jgzz_fiscal_code,
1258 NULL),
1259 NULL),*/
1260 individual_1099 = decode(UPPER(p_vendor_rec.Organization_Type_Lookup_Code),
1261 'INDIVIDUAL',p_vendor_rec.jgzz_fiscal_code,
1262 'FOREIGN INDIVIDUAL',p_vendor_rec.jgzz_fiscal_code,
1263 NULL),
1264 --bug6050423 ends
1265 type_1099 = p_vendor_rec.Type_1099,
1266 withholding_status_lookup_code = p_vendor_rec.withholding_status_Lookup_Code,
1267 withholding_start_date = p_vendor_rec.Withholding_Start_Date,
1268 organization_type_lookup_code = p_vendor_rec.Organization_Type_Lookup_Code,
1269 start_date_active = p_vendor_rec.Start_Date_Active,
1270 end_date_active = p_vendor_rec.End_Date_Active,
1271 qty_rcv_tolerance = p_vendor_rec.Qty_Rcv_Tolerance,
1272 minority_group_lookup_code = p_vendor_rec.Minority_Group_Lookup_Code,
1273 women_owned_flag = p_vendor_rec.Women_Owned_Flag,
1274 small_business_flag = p_vendor_rec.Small_Business_Flag,
1275 attribute_category = p_vendor_rec.Attribute_Category,
1276 attribute1 = p_vendor_rec.Attribute1,
1277 attribute2 = p_vendor_rec.Attribute2,
1278 attribute3 = p_vendor_rec.Attribute3,
1279 attribute4 = p_vendor_rec.Attribute4,
1280 attribute5 = p_vendor_rec.Attribute5,
1281 hold_flag = p_vendor_rec.Hold_Flag,
1282 purchasing_hold_reason = p_vendor_rec.Purchasing_Hold_Reason,
1283 hold_by = p_vendor_rec.Hold_By,
1284 hold_date = p_vendor_rec.Hold_Date,
1285 terms_date_basis = p_vendor_rec.Terms_Date_Basis,
1286 attribute10 = p_vendor_rec.Attribute10,
1287 attribute11 = p_vendor_rec.Attribute11,
1288 attribute12 = p_vendor_rec.Attribute12,
1289 attribute13 = p_vendor_rec.Attribute13,
1290 attribute14 = p_vendor_rec.Attribute14,
1291 attribute15 = p_vendor_rec.Attribute15,
1292 attribute6 = p_vendor_rec.Attribute6,
1293 attribute7 = p_vendor_rec.Attribute7,
1294 attribute8 = p_vendor_rec.Attribute8,
1295 attribute9 = p_vendor_rec.Attribute9,
1296 days_early_receipt_allowed = p_vendor_rec.Days_Early_Receipt_Allowed,
1297 days_late_receipt_allowed = p_vendor_rec.Days_Late_Receipt_Allowed,
1298 enforce_ship_to_location_code = p_vendor_rec.Enforce_Ship_To_Location_Code,
1299 federal_reportable_flag = p_vendor_rec.Federal_Reportable_Flag,
1300 hold_unmatched_invoices_flag = p_vendor_rec.Hold_Unmatched_Invoices_Flag,
1301 match_option = p_vendor_rec.match_option,
1302 create_debit_memo_flag = p_vendor_rec.create_debit_memo_flag,
1303 inspection_required_flag = p_vendor_rec.Inspection_Required_Flag,
1304 receipt_required_flag = p_vendor_rec.Receipt_Required_Flag,
1305 receiving_routing_id = p_vendor_rec.Receiving_Routing_Id,
1306 state_reportable_flag = p_vendor_rec.State_Reportable_Flag,
1307 tax_verification_date = p_vendor_rec.Tax_Verification_Date,
1308 auto_calculate_interest_flag = p_vendor_rec.Auto_Calculate_Interest_Flag,
1309 name_control = p_vendor_rec.Name_Control,
1310 allow_substitute_receipts_flag = p_vendor_rec.Allow_Substitute_Receipts_Flag,
1311 allow_unordered_receipts_flag = p_vendor_rec.Allow_Unordered_Receipts_Flag,
1312 receipt_days_exception_code = p_vendor_rec.Receipt_Days_Exception_Code,
1313 qty_rcv_exception_code = p_vendor_rec.Qty_Rcv_Exception_Code,
1314 exclude_freight_from_discount = p_vendor_rec.Exclude_Freight_From_Discount,
1315 tax_reporting_name = p_vendor_rec.Tax_Reporting_Name,
1316 awt_group_id = p_vendor_rec.Awt_Group_Id,
1317 pay_awt_group_id = p_vendor_rec.Pay_Awt_Group_Id,--bug6664407
1318 check_digits = p_vendor_rec.Check_Digits,
1319 allow_awt_flag = p_vendor_rec.Allow_Awt_Flag,
1320
1321 --start of bug 12678066
1322 request_id = p_request_id,
1323 program_application_id = p_program_application_id,
1324 program_id = p_program_id,
1325 program_update_date = p_program_update_date,
1326 --end of bug 12678066
1327
1328 global_attribute_category = p_vendor_rec.global_attribute_category,
1329 global_attribute1 = p_vendor_rec.global_attribute1,
1330 global_attribute2 = p_vendor_rec.global_attribute2,
1331 global_attribute3 = p_vendor_rec.global_attribute3,
1332 global_attribute4 = p_vendor_rec.global_attribute4,
1333 global_attribute5 = p_vendor_rec.global_attribute5,
1334 global_attribute6 = p_vendor_rec.global_attribute6,
1335 global_attribute7 = p_vendor_rec.global_attribute7,
1336 global_attribute8 = p_vendor_rec.global_attribute8,
1337 global_attribute9 = p_vendor_rec.global_attribute9,
1338 global_attribute10 = p_vendor_rec.global_attribute10,
1339 global_attribute11 = p_vendor_rec.global_attribute11,
1340 global_attribute12 = p_vendor_rec.global_attribute12,
1341 global_attribute13 = p_vendor_rec.global_attribute13,
1342 global_attribute14 = p_vendor_rec.global_attribute14,
1343 global_attribute15 = p_vendor_rec.global_attribute15,
1344 global_attribute16 = p_vendor_rec.global_attribute16,
1345 global_attribute17 = p_vendor_rec.global_attribute17,
1346 global_attribute18 = p_vendor_rec.global_attribute18,
1347 global_attribute19 = p_vendor_rec.global_attribute19,
1348 global_attribute20 = p_vendor_rec.global_attribute20,
1349 bank_charge_bearer = p_vendor_rec.Bank_Charge_Bearer,
1350 NI_Number = p_vendor_rec.NI_Number,
1351 standard_industry_class = p_vendor_rec.sic_code -- 5066199
1352 WHERE vendor_id = p_vendor_id;
1353 if (SQL%NOTFOUND) then
1354 Raise NO_DATA_FOUND;
1355 end if;
1356
1357 /* Bug 5412440 */
1358 IF (p_vendor_rec.allow_awt_flag = 'N') THEN
1359 UPDATE Ap_Supplier_Sites_ALL
1360 SET allow_awt_flag = 'N',
1361 awt_group_id = NULL
1362 WHERE vendor_id = p_vendor_rec.vendor_id;
1363 END IF;
1364
1365
1366 EXCEPTION
1367 WHEN OTHERS THEN
1368 IF (SQLCODE <> -20001) THEN
1369 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1370 FND_MSG_PUB.ADD;
1371 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1372 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
1373 FND_MESSAGE.SET_TOKEN('PARAMETERS', 'VENDOR_ID = ' || p_Vendor_Id);
1374 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
1375 END IF;
1376 APP_EXCEPTION.RAISE_EXCEPTION;
1377
1378
1379 END Update_Row;
1380
1381 --
1382 --
1383 PROCEDURE Lock_Row (
1384 x_Rowid VARCHAR2,
1385 x_Vendor_Id NUMBER,
1386 x_Vendor_Name VARCHAR2,
1387 x_Segment1 VARCHAR2,
1388 x_Summary_Flag VARCHAR2,
1389 x_Enabled_Flag VARCHAR2,
1390 x_Employee_Id NUMBER,
1391 x_Validation_Number NUMBER,
1392 x_Vendor_Type_Lookup_Code VARCHAR2,
1393 x_Customer_Num VARCHAR2,
1394 x_One_Time_Flag VARCHAR2,
1395 x_Parent_Vendor_Id NUMBER,
1396 x_Min_Order_Amount NUMBER,
1397 /* x_Ship_To_Location_Id NUMBER,
1398 x_Bill_To_Location_Id NUMBER,
1399 x_Ship_Via_Lookup_Code VARCHAR2,
1400 x_Freight_Terms_Lookup_Code VARCHAR2,
1401 x_Fob_Lookup_Code VARCHAR2, */
1402 x_Terms_Id NUMBER,
1403 x_Set_Of_Books_Id NUMBER,
1404 x_Always_Take_Disc_Flag VARCHAR2,
1405 x_Pay_Date_Basis_Lookup_Code VARCHAR2,
1406 x_Pay_Group_Lookup_Code VARCHAR2,
1407 x_Payment_Priority NUMBER,
1408 x_Invoice_Currency_Code VARCHAR2,
1409 x_Payment_Currency_Code VARCHAR2,
1410 x_Invoice_Amount_Limit NUMBER,
1411 x_Hold_All_Payments_Flag VARCHAR2,
1412 x_Hold_Future_Payments_Flag VARCHAR2,
1413 x_Hold_Reason VARCHAR2,
1414 /* x_Distribution_Set_Id NUMBER,
1415 x_Accts_Pay_CCID NUMBER,
1416 x_Future_Dated_Payment_CCID NUMBER,
1417 x_Prepay_CCID NUMBER, */
1418 x_Num_1099 VARCHAR2,
1419 x_Type_1099 VARCHAR2,
1420 x_withholding_stat_Lookup_Code VARCHAR2,
1421 x_Withholding_Start_Date DATE,
1422 x_Org_Type_Lookup_Code VARCHAR2,
1423 -- eTax Uptake x_Vat_Code VARCHAR2,
1424 x_Start_Date_Active DATE,
1425 x_End_Date_Active DATE,
1426 x_Qty_Rcv_Tolerance NUMBER,
1427 x_Minority_Group_Lookup_Code VARCHAR2,
1428 x_Bank_Account_Name VARCHAR2,
1429 x_Bank_Account_Num VARCHAR2,
1430 x_Bank_Num VARCHAR2,
1431 x_Bank_Account_Type VARCHAR2,
1432 x_Women_Owned_Flag VARCHAR2,
1433 x_Small_Business_Flag VARCHAR2,
1434 x_Standard_Industry_Class VARCHAR2,
1435 x_Attribute_Category VARCHAR2,
1436 x_Attribute1 VARCHAR2,
1437 x_Attribute2 VARCHAR2,
1438 x_Attribute3 VARCHAR2,
1439 x_Attribute4 VARCHAR2,
1440 x_Attribute5 VARCHAR2,
1441 x_Hold_Flag VARCHAR2,
1442 x_Purchasing_Hold_Reason VARCHAR2,
1443 x_Hold_By NUMBER,
1444 x_Hold_Date DATE,
1445 x_Terms_Date_Basis VARCHAR2,
1446 x_Price_Tolerance NUMBER,
1447 x_Attribute10 VARCHAR2,
1448 x_Attribute11 VARCHAR2,
1449 x_Attribute12 VARCHAR2,
1450 x_Attribute13 VARCHAR2,
1451 x_Attribute14 VARCHAR2,
1452 x_Attribute15 VARCHAR2,
1453 x_Attribute6 VARCHAR2,
1454 x_Attribute7 VARCHAR2,
1455 x_Attribute8 VARCHAR2,
1456 x_Attribute9 VARCHAR2,
1457 x_Days_Early_Receipt_Allowed NUMBER,
1458 x_Days_Late_Receipt_Allowed NUMBER,
1459 x_Enforce_Ship_To_Loc_Code VARCHAR2,
1460 x_Federal_Reportable_Flag VARCHAR2,
1461 x_Hold_Unmatched_Invoices_Flag VARCHAR2,
1462 x_match_option VARCHAR2,
1463 x_create_debit_memo_flag VARCHAR2,
1464 x_Inspection_Required_Flag VARCHAR2,
1465 x_Receipt_Required_Flag VARCHAR2,
1466 x_Receiving_Routing_Id NUMBER,
1467 x_State_Reportable_Flag VARCHAR2,
1468 x_Tax_Verification_Date DATE,
1469 x_Auto_Calculate_Interest_Flag VARCHAR2,
1470 x_Name_Control VARCHAR2,
1471 x_Allow_Subst_Receipts_Flag VARCHAR2,
1472 x_Allow_Unord_Receipts_Flag VARCHAR2,
1473 x_Receipt_Days_Exception_Code VARCHAR2,
1474 x_Qty_Rcv_Exception_Code VARCHAR2,
1475 -- eTax Uptake x_Offset_Tax_Flag VARCHAR2,
1476 x_Exclude_Freight_From_Disc VARCHAR2,
1477 x_Vat_Registration_Num VARCHAR2,
1478 x_Tax_Reporting_Name VARCHAR2,
1479 x_Awt_Group_Id NUMBER,
1480 x_Pay_Awt_Group_Id NUMBER,--bug6664407
1481 x_Check_Digits VARCHAR2,
1482 x_Bank_Number VARCHAR2,
1483 x_Allow_Awt_Flag VARCHAR2,
1484 x_Bank_Branch_Type VARCHAR2,
1485 /* eTax Uptake
1486 x_Auto_Tax_Calc_Flag VARCHAR2,
1487 x_Auto_Tax_Calc_Override VARCHAR2,
1488 x_Amount_Includes_Tax_Flag VARCHAR2,
1489 x_AP_Tax_Rounding_Rule VARCHAR2, */
1490 x_Vendor_Name_Alt VARCHAR2,
1491 X_global_attribute_category VARCHAR2 DEFAULT NULL,
1492 X_global_attribute1 VARCHAR2 DEFAULT NULL,
1493 X_global_attribute2 VARCHAR2 DEFAULT NULL,
1494 X_global_attribute3 VARCHAR2 DEFAULT NULL,
1495 X_global_attribute4 VARCHAR2 DEFAULT NULL,
1496 X_global_attribute5 VARCHAR2 DEFAULT NULL,
1497 X_global_attribute6 VARCHAR2 DEFAULT NULL,
1498 X_global_attribute7 VARCHAR2 DEFAULT NULL,
1499 X_global_attribute8 VARCHAR2 DEFAULT NULL,
1500 X_global_attribute9 VARCHAR2 DEFAULT NULL,
1501 X_global_attribute10 VARCHAR2 DEFAULT NULL,
1502 X_global_attribute11 VARCHAR2 DEFAULT NULL,
1503 X_global_attribute12 VARCHAR2 DEFAULT NULL,
1504 X_global_attribute13 VARCHAR2 DEFAULT NULL,
1505 X_global_attribute14 VARCHAR2 DEFAULT NULL,
1506 X_global_attribute15 VARCHAR2 DEFAULT NULL,
1507 X_global_attribute16 VARCHAR2 DEFAULT NULL,
1508 X_global_attribute17 VARCHAR2 DEFAULT NULL,
1509 X_global_attribute18 VARCHAR2 DEFAULT NULL,
1510 X_global_attribute19 VARCHAR2 DEFAULT NULL,
1511 X_global_attribute20 VARCHAR2 DEFAULT NULL,
1512 X_Bank_Charge_Bearer VARCHAR2 DEFAULT NULL,
1513 X_NI_Number VARCHAR2 DEFAULT NULL,
1514 X_calling_sequence IN VARCHAR2 ) IS
1515
1516 CURSOR C IS
1517 SELECT *
1518 FROM ap_suppliers
1519 WHERE rowid = x_Rowid
1520 FOR UPDATE of Vendor_Id NOWAIT;
1521 Recinfo C%ROWTYPE;
1522
1523 current_calling_sequence VARCHAR2(2000);
1524 debug_info VARCHAR2(100);
1525 --
1526 BEGIN
1527 -- Update the calling sequence
1528 --
1529 current_calling_sequence := 'AP_VENDORS_PKG.LOCK_ROW<-' ||
1530 X_calling_sequence;
1531
1532 debug_info := 'Open cursor C';
1533 OPEN C;
1534 debug_info := 'Fetch cursor C';
1535 FETCH C INTO Recinfo;
1536 if (C%NOTFOUND) then
1537 debug_info := 'Close cursor C- DATA NOTFOUND';
1538 CLOSE C;
1539 FND_MESSAGE.Set_Name('FND','FORM_RECORD_DELETED');
1540 FND_MSG_PUB.ADD;
1541 APP_EXCEPTION.Raise_Exception;
1542 end if;
1543 debug_info := 'Close cursor C';
1544 CLOSE C;
1545 if (
1546 (Recinfo.vendor_id = x_Vendor_Id)
1547 AND (Recinfo.vendor_name = x_Vendor_Name)
1548 AND (Recinfo.segment1 = x_Segment1)
1549 AND (Recinfo.summary_flag = x_Summary_Flag)
1550 AND (Recinfo.enabled_flag = x_Enabled_Flag)
1551 AND ((Recinfo.employee_id = x_Employee_Id)
1552 OR ((Recinfo.employee_id IS NULL)
1553 AND (x_Employee_Id IS NULL)))
1554 AND ((Recinfo.validation_number = x_Validation_Number)
1555 OR ((Recinfo.validation_number IS NULL)
1556 AND (x_Validation_Number IS NULL)))
1557 AND ((Recinfo.vendor_type_lookup_code = x_Vendor_Type_Lookup_Code)
1558 OR ((Recinfo.vendor_type_lookup_code IS NULL)
1559 AND (x_Vendor_Type_Lookup_Code IS NULL)))
1560 AND ((Recinfo.customer_num = x_Customer_Num)
1561 OR ((Recinfo.customer_num IS NULL)
1562 AND (x_Customer_Num IS NULL)))
1563 AND ((Recinfo.one_time_flag = x_One_Time_Flag)
1564 OR ((Recinfo.one_time_flag IS NULL)
1565 AND (x_One_Time_Flag IS NULL)))
1566 AND ((Recinfo.parent_vendor_id = x_Parent_Vendor_Id)
1567 OR ((Recinfo.parent_vendor_id IS NULL)
1568 AND (x_Parent_Vendor_Id IS NULL)))
1569 AND ((Recinfo.min_order_amount = x_Min_Order_Amount)
1570 OR ((Recinfo.min_order_amount IS NULL)
1571 AND (x_Min_Order_Amount IS NULL)))
1572 --Bug :2809214 MOAC - Supplier Attribute Change Project
1573 /* AND ((Recinfo.ship_to_location_id = x_Ship_To_Location_Id)
1574 OR ((Recinfo.ship_to_location_id IS NULL)
1575 AND (x_Ship_To_Location_Id IS NULL)))
1576 AND ((Recinfo.bill_to_location_id = x_Bill_To_Location_Id)
1577 OR ((Recinfo.bill_to_location_id IS NULL)
1578 AND (x_Bill_To_Location_Id IS NULL)))
1579 AND ((Recinfo.ship_via_lookup_code = x_Ship_Via_Lookup_Code)
1580 OR ((Recinfo.ship_via_lookup_code IS NULL)
1581 AND (x_Ship_Via_Lookup_Code IS NULL)))
1582 AND ((Recinfo.freight_terms_lookup_code = x_Freight_Terms_Lookup_Code)
1583 OR ((Recinfo.freight_terms_lookup_code IS NULL)
1584 AND (x_Freight_Terms_Lookup_Code IS NULL)))
1585 AND ((Recinfo.fob_lookup_code = x_Fob_Lookup_Code)
1586 OR ((Recinfo.fob_lookup_code IS NULL)
1587 AND (x_Fob_Lookup_Code IS NULL))) */
1588 AND ((Recinfo.terms_id = x_Terms_Id)
1589 OR ( (Recinfo.terms_id IS NULL)
1590 AND (x_Terms_Id IS NULL)))
1591 AND ((Recinfo.set_of_books_id = x_Set_Of_Books_Id)
1592 OR ((Recinfo.set_of_books_id IS NULL)
1593 AND (x_Set_Of_Books_Id IS NULL)))
1594 AND ((Recinfo.always_take_disc_flag = x_Always_Take_Disc_Flag)
1595 OR ((Recinfo.always_take_disc_flag IS NULL)
1596 AND (x_Always_Take_Disc_Flag IS NULL)))
1597 AND ((Recinfo.pay_date_basis_lookup_code = x_Pay_Date_Basis_Lookup_Code)
1598 OR ((Recinfo.pay_date_basis_lookup_code IS NULL)
1599 AND (x_Pay_Date_Basis_Lookup_Code IS NULL)))
1600 AND ((Recinfo.pay_group_lookup_code = x_Pay_Group_Lookup_Code)
1601 OR ((Recinfo.pay_group_lookup_code IS NULL)
1602 AND (x_Pay_Group_Lookup_Code IS NULL)))
1603 AND ((Recinfo.payment_priority = x_Payment_Priority)
1604 OR ((Recinfo.payment_priority IS NULL)
1605 AND (x_Payment_Priority IS NULL)))
1606 AND ((Recinfo.invoice_currency_code = x_Invoice_Currency_Code)
1607 OR ((Recinfo.invoice_currency_code IS NULL)
1608 AND (x_Invoice_Currency_Code IS NULL)))
1609 AND ((Recinfo.payment_currency_code = x_Payment_Currency_Code)
1610 OR ((Recinfo.payment_currency_code IS NULL)
1611 AND (x_Payment_Currency_Code IS NULL)))
1612 AND ((Recinfo.invoice_amount_limit = x_Invoice_Amount_Limit)
1613 OR ((Recinfo.invoice_amount_limit IS NULL)
1614 AND (x_Invoice_Amount_Limit IS NULL)))
1615 AND ((Recinfo.hold_all_payments_flag = x_Hold_All_Payments_Flag)
1616 OR ((Recinfo.hold_all_payments_flag IS NULL)
1617 AND (x_Hold_All_Payments_Flag IS NULL)))
1618 AND ((Recinfo.hold_future_payments_flag = x_Hold_Future_Payments_Flag)
1619 OR ((Recinfo.hold_future_payments_flag IS NULL)
1620 AND (x_Hold_Future_Payments_Flag IS NULL)))
1621 AND ((Recinfo.hold_reason = x_Hold_Reason)
1622 OR ((Recinfo.hold_reason IS NULL)
1623 AND (x_Hold_Reason IS NULL)))
1624 --Bug :2809214 MOAC - Supplier Attribute Change Project
1625 /* AND ((Recinfo.distribution_set_id = x_Distribution_Set_Id)
1626 OR ((Recinfo.distribution_set_id IS NULL)
1627 AND (x_Distribution_Set_Id IS NULL)))
1628 AND ((Recinfo.accts_pay_code_combination_id = x_Accts_Pay_CCID)
1629 OR ((Recinfo.accts_pay_code_combination_id IS NULL)
1630 AND (x_Accts_Pay_CCID IS NULL)))
1631 AND ((Recinfo.future_dated_payment_ccid = x_Future_Dated_Payment_CCID)
1632 OR ((Recinfo.future_dated_payment_ccid IS NULL)
1633 AND (x_Future_Dated_Payment_CCID IS NULL)))
1634 AND ((Recinfo.prepay_code_combination_id = x_Prepay_CCID)
1635 OR ((Recinfo.prepay_code_combination_id IS NULL)
1636 AND (x_Prepay_CCID IS NULL))) */
1637 AND ((Recinfo.num_1099 = x_Num_1099)
1638 OR ((Recinfo.num_1099 IS NULL)
1639 AND (x_Num_1099 IS NULL)))
1640 AND ((Recinfo.type_1099 = x_Type_1099)
1641 OR ((Recinfo.type_1099 IS NULL)
1642 AND (x_Type_1099 IS NULL)))
1643 AND ((Recinfo.withholding_status_lookup_code = x_withholding_stat_Lookup_Code)
1644 OR ((Recinfo.withholding_status_lookup_code IS NULL)
1645 AND (x_withholding_stat_Lookup_Code IS NULL)))
1646 AND ((Recinfo.withholding_start_date = x_Withholding_Start_Date)
1647 OR ((Recinfo.withholding_start_date IS NULL)
1648 AND (x_Withholding_Start_Date IS NULL)))
1649 AND ((Recinfo.organization_type_lookup_code = x_Org_Type_Lookup_Code)
1650 OR ((Recinfo.organization_type_lookup_code IS NULL)
1651 AND (x_Org_Type_Lookup_Code IS NULL)))
1652 AND ((Recinfo.start_date_active = x_Start_Date_Active)
1653 OR ((Recinfo.start_date_active IS NULL)
1654 AND (x_Start_Date_Active IS NULL)))
1655 AND ((Recinfo.end_date_active = x_End_Date_Active)
1656 OR ((Recinfo.end_date_active IS NULL)
1657 AND (x_End_Date_Active IS NULL)))
1658 AND ((Recinfo.qty_rcv_tolerance = x_Qty_Rcv_Tolerance)
1659 OR ((Recinfo.qty_rcv_tolerance IS NULL)
1660 AND (x_Qty_Rcv_Tolerance IS NULL )))
1661 AND ((Recinfo.minority_group_lookup_code = x_Minority_Group_Lookup_Code)
1662 OR ((Recinfo.minority_group_lookup_code IS NULL)
1663 AND (x_Minority_Group_Lookup_Code IS NULL)))
1664 AND ((Recinfo.bank_account_name = x_Bank_Account_Name)
1665 OR ((Recinfo.bank_account_name IS NULL)
1666 AND (x_Bank_Account_Name IS NULL)))
1667 AND ((Recinfo.bank_account_num = x_Bank_Account_Num)
1668 OR ((Recinfo.bank_account_num IS NULL)
1669 AND (x_Bank_Account_Num IS NULL)))
1670 AND ((Recinfo.bank_num = x_Bank_Num)
1671 OR ((Recinfo.bank_num IS NULL)
1672 AND (x_Bank_Num IS NULL)))
1673 AND ((Recinfo.bank_account_type = x_Bank_Account_Type)
1674 OR ((Recinfo.bank_account_type IS NULL)
1675 AND (x_Bank_Account_Type IS NULL)))
1676 AND ((Recinfo.women_owned_flag = x_Women_Owned_Flag)
1677 OR ((Recinfo.women_owned_flag IS NULL)
1678 AND (x_Women_Owned_Flag IS NULL)))
1679 AND ((Recinfo.small_business_flag = x_Small_Business_Flag)
1680 OR ((Recinfo.small_business_flag IS NULL)
1681 AND (x_Small_Business_Flag IS NULL)))
1682 AND ((Recinfo.standard_industry_class = x_Standard_Industry_Class)
1683 OR ((Recinfo.standard_industry_class IS NULL)
1684 AND (x_Standard_Industry_Class IS NULL)))
1685 AND (NVL(Recinfo.Bank_Charge_Bearer,'I') = x_Bank_Charge_Bearer)
1686 --Bug :2809214 MOAC - Supplier Attribute Change Project
1687 AND ((Recinfo.NI_Number = x_NI_Number)
1688 OR ((Recinfo.NI_Number IS NULL)
1689 AND (x_NI_Number IS NULL)))
1690
1691 )
1692 then
1693 null;
1694 else
1695 FND_MESSAGE.Set_Name('FND','FORM_RECORD_CHANGED');
1696 FND_MSG_PUB.ADD;
1697 APP_EXCEPTION.Raise_Exception;
1698 end if;
1699 --
1700 if (
1701 ((Recinfo.attribute_category = x_Attribute_Category)
1702 OR ((Recinfo.attribute_category IS NULL)
1703 AND (x_Attribute_Category IS NULL)))
1704 AND ((Recinfo.attribute1 = x_Attribute1)
1705 OR ((Recinfo.attribute1 IS NULL)
1706 AND (x_Attribute1 IS NULL)))
1707 AND ((Recinfo.attribute2 = x_Attribute2)
1708 OR ((Recinfo.attribute2 IS NULL)
1709 AND (x_Attribute2 IS NULL)))
1710 AND ((Recinfo.attribute3 = x_Attribute3)
1711 OR ((Recinfo.attribute3 IS NULL)
1712 AND (x_Attribute3 IS NULL)))
1713 AND ((Recinfo.attribute4 = x_Attribute4)
1714 OR ((Recinfo.attribute4 IS NULL)
1715 AND (x_Attribute4 IS NULL)))
1716 AND ((Recinfo.attribute5 = x_Attribute5)
1717 OR ((Recinfo.attribute5 IS NULL)
1718 AND (x_Attribute5 IS NULL)))
1719 AND ((Recinfo.hold_flag = x_Hold_Flag)
1720 OR ((Recinfo.hold_flag IS NULL)
1721 AND (x_Hold_Flag IS NULL)))
1722 AND ((Recinfo.purchasing_hold_reason = x_Purchasing_Hold_Reason)
1723 OR ((Recinfo.purchasing_hold_reason IS NULL)
1724 AND (x_Purchasing_Hold_Reason IS NULL)))
1725 AND ((Recinfo.hold_by = x_Hold_By)
1726 OR ((Recinfo.hold_by IS NULL)
1727 AND (x_Hold_By IS NULL)))
1728 AND ((Recinfo.hold_date = x_Hold_Date)
1729 OR ((Recinfo.hold_date IS NULL)
1730 AND (x_Hold_Date IS NULL)))
1731 AND ((Recinfo.terms_date_basis = x_Terms_Date_Basis)
1732 OR ((Recinfo.terms_date_basis IS NULL)
1733 AND (x_Terms_Date_Basis IS NULL)))
1734 AND ((Recinfo.price_tolerance = x_Price_Tolerance)
1735 OR ((Recinfo.price_tolerance IS NULL)
1736 AND (x_Price_Tolerance IS NULL)))
1737 AND ((Recinfo.attribute10 = x_Attribute10)
1738 OR ((Recinfo.attribute10 IS NULL)
1739 AND (x_Attribute10 IS NULL)))
1740 AND ((Recinfo.attribute11 = x_Attribute11)
1741 OR ((Recinfo.attribute11 IS NULL)
1742 AND (x_Attribute11 IS NULL)))
1743 AND ((Recinfo.attribute12 = x_Attribute12)
1744 OR ((Recinfo.attribute12 IS NULL)
1745 AND (x_Attribute12 IS NULL)))
1746 AND ((Recinfo.attribute13 = x_Attribute13)
1747 OR ((Recinfo.attribute13 IS NULL)
1748 AND (x_Attribute13 IS NULL)))
1749 AND ((Recinfo.attribute14 = x_Attribute14)
1750 OR ((Recinfo.attribute14 IS NULL)
1751 AND (x_Attribute14 IS NULL)))
1752 AND ((Recinfo.attribute15 = x_Attribute15)
1753 OR ((Recinfo.attribute15 IS NULL)
1754 AND (x_Attribute15 IS NULL)))
1755 AND ((Recinfo.attribute6 = x_Attribute6)
1756 OR ((Recinfo.attribute6 IS NULL)
1757 AND (x_Attribute6 IS NULL)))
1758 AND ((Recinfo.attribute7 = x_Attribute7)
1759 OR ((Recinfo.attribute7 IS NULL)
1760 AND (x_Attribute7 IS NULL)))
1761 AND ((Recinfo.attribute8 = x_Attribute8)
1762 OR ((Recinfo.attribute8 IS NULL)
1763 AND (x_Attribute8 IS NULL)))
1764 AND ((Recinfo.attribute9 = x_Attribute9)
1765 OR ((Recinfo.attribute9 IS NULL)
1766 AND (x_Attribute9 IS NULL)))
1767 AND ((Recinfo.days_early_receipt_allowed = x_Days_Early_Receipt_Allowed)
1768 OR ((Recinfo.days_early_receipt_allowed IS NULL)
1769 AND (x_Days_Early_Receipt_Allowed IS NULL)))
1770 AND ( (Recinfo.days_late_receipt_allowed = x_Days_Late_Receipt_Allowed)
1771 OR ((Recinfo.days_late_receipt_allowed IS NULL)
1772 AND (x_Days_Late_Receipt_Allowed IS NULL)))
1773 AND ((Recinfo.enforce_ship_to_location_code = x_Enforce_Ship_To_Loc_Code)
1774 OR ((Recinfo.enforce_ship_to_location_code IS NULL)
1775 AND (x_Enforce_Ship_To_Loc_Code IS NULL)))
1776 AND ((Recinfo.federal_reportable_flag = x_Federal_Reportable_Flag)
1777 OR ((Recinfo.federal_reportable_flag IS NULL)
1778 AND (x_Federal_Reportable_Flag IS NULL)))
1779 AND ((Recinfo.hold_unmatched_invoices_flag = x_Hold_Unmatched_Invoices_Flag)
1780 OR ((Recinfo.hold_unmatched_invoices_flag IS NULL)
1781 AND (x_Hold_Unmatched_Invoices_Flag IS NULL)))
1782 AND ((Recinfo.match_option = x_match_option)
1783 OR ((Recinfo.match_option IS NULL)
1784 AND (x_match_option IS NULL)))
1785 AND ((Recinfo.create_debit_memo_flag = x_create_debit_memo_flag)
1786 OR ((Recinfo.create_debit_memo_flag IS NULL)
1787 AND (x_create_debit_memo_flag IS NULL)))
1788 AND ((Recinfo.inspection_required_flag = x_inspection_required_flag)
1789 OR ((Recinfo.inspection_required_flag IS NULL)
1790 AND (x_Inspection_Required_Flag IS NULL)))
1791 AND ((Recinfo.receipt_required_flag = x_Receipt_Required_Flag)
1792 OR ((Recinfo.receipt_required_flag IS NULL)
1793 AND (x_Receipt_Required_Flag IS NULL)))
1794 AND ((Recinfo.receiving_routing_id = x_Receiving_Routing_Id)
1795 OR ((Recinfo.receiving_routing_id IS NULL)
1796 AND (x_Receiving_Routing_Id IS NULL)))
1797 AND ((Recinfo.state_reportable_flag = x_State_Reportable_Flag)
1798 OR ((Recinfo.state_reportable_flag IS NULL)
1799 AND (x_State_Reportable_Flag IS NULL)))
1800 AND ((Recinfo.tax_verification_date = x_Tax_Verification_Date)
1801 OR ((Recinfo.tax_verification_date IS NULL)
1802 AND (x_Tax_Verification_Date IS NULL)))
1803 AND ((Recinfo.auto_calculate_interest_flag = x_Auto_Calculate_Interest_Flag)
1804 OR ((Recinfo.auto_calculate_interest_flag IS NULL)
1805 AND (x_Auto_Calculate_Interest_Flag IS NULL)))
1806 AND ((RTRIM(Recinfo.name_control) = x_Name_Control)
1807 OR ((RTRIM(Recinfo.name_control) IS NULL)
1808 AND (x_Name_Control IS NULL)))
1809 AND ((Recinfo.allow_substitute_receipts_flag = x_Allow_Subst_Receipts_Flag)
1810 OR ((Recinfo.allow_substitute_receipts_flag IS NULL)
1811 AND (x_Allow_Subst_Receipts_Flag IS NULL)))
1812 AND ((Recinfo.allow_unordered_receipts_flag = x_Allow_Unord_Receipts_Flag)
1813 OR ((Recinfo.allow_unordered_receipts_flag IS NULL)
1814 AND (x_Allow_Unord_Receipts_Flag IS NULL)))
1815 AND ((Recinfo.receipt_days_exception_code = x_Receipt_Days_Exception_Code)
1816 OR ((Recinfo.receipt_days_exception_code IS NULL)
1817 AND (x_Receipt_Days_Exception_Code IS NULL)))
1818 AND ((Recinfo.qty_rcv_exception_code = x_Qty_Rcv_Exception_Code)
1819 OR ((Recinfo.qty_rcv_exception_code IS NULL)
1820 AND (x_Qty_Rcv_Exception_Code IS NULL)))
1821 AND ((Recinfo.exclude_freight_from_discount = x_Exclude_Freight_From_Disc)
1822 OR ((Recinfo.exclude_freight_from_discount IS NULL)
1823 AND (x_Exclude_Freight_From_Disc IS NULL)))
1824 AND ((Recinfo.vat_registration_num = x_Vat_Registration_Num)
1825 OR ((Recinfo.vat_registration_num IS NULL)
1826 AND (x_Vat_Registration_Num IS NULL)))
1827 AND ((Recinfo.tax_reporting_name = x_Tax_Reporting_Name)
1828 OR ((Recinfo.tax_reporting_name IS NULL)
1829 AND (x_Tax_Reporting_Name IS NULL)))
1830 AND ((Recinfo.awt_group_id = x_Awt_Group_Id)
1831 OR ((Recinfo.awt_group_id IS NULL)
1832 AND (x_Awt_Group_Id IS NULL)))
1833 AND ((Recinfo.pay_awt_group_id = x_Pay_Awt_Group_Id)
1834 OR ((Recinfo.pay_awt_group_id IS NULL)
1835 AND (x_Pay_Awt_Group_Id IS NULL))) --bug6664407
1836 AND ((Recinfo.check_digits = x_Check_Digits)
1837 OR ((Recinfo.check_digits IS NULL)
1838 AND (x_Check_Digits IS NULL)))
1839 AND ((Recinfo.bank_number = x_Bank_Number)
1840 OR ((Recinfo.bank_number IS NULL)
1841 AND (x_Bank_Number IS NULL)))
1842 AND ((Recinfo.allow_awt_flag = x_Allow_Awt_Flag)
1843 OR ((Recinfo.allow_awt_flag IS NULL)
1844 AND (x_Allow_Awt_Flag IS NULL)))
1845 AND ((Recinfo.bank_branch_type = x_bank_branch_type)
1846 OR ((Recinfo.bank_branch_type IS NULL)
1847 AND (x_bank_branch_type IS NULL)))
1848 AND ((Recinfo.vendor_name_alt = x_Vendor_Name_Alt)
1849 OR ((Recinfo.vendor_name_alt IS NULL)
1850 AND (x_Vendor_Name_Alt IS NULL)))
1851 )
1852 then
1853 null;
1854 else
1855 FND_MESSAGE.Set_Name('FND','FORM_RECORD_CHANGED');
1856 FND_MSG_PUB.ADD;
1857 APP_EXCEPTION.Raise_Exception;
1858 end if;
1859 --
1860 if (
1861 ( (Recinfo.global_attribute_category = X_global_attribute_category)
1862 OR ( (Recinfo.global_attribute_category IS NULL)
1863 AND (X_global_attribute_category IS NULL)))
1864 AND ( (Recinfo.global_attribute1 = X_global_attribute1)
1865 OR ( (Recinfo.global_attribute1 IS NULL)
1866 AND (X_global_attribute1 IS NULL)))
1867 AND ( (Recinfo.global_attribute2 = X_global_attribute2)
1868 OR ( (Recinfo.global_attribute2 IS NULL)
1869 AND (X_global_attribute2 IS NULL)))
1870 AND ( (Recinfo.global_attribute3 = X_global_attribute3)
1871 OR ( (Recinfo.global_attribute3 IS NULL)
1872 AND (X_global_attribute3 IS NULL)))
1873 AND ( (Recinfo.global_attribute4 = X_global_attribute4)
1874 OR ( (Recinfo.global_attribute4 IS NULL)
1875 AND (X_global_attribute4 IS NULL)))
1876 AND ( (Recinfo.global_attribute5 = X_global_attribute5)
1877 OR ( (Recinfo.global_attribute5 IS NULL)
1878 AND (X_global_attribute5 IS NULL)))
1879 AND ( (Recinfo.global_attribute6 = X_global_attribute6)
1880 OR ( (Recinfo.global_attribute6 IS NULL)
1881 AND (X_global_attribute6 IS NULL)))
1882 AND ( (Recinfo.global_attribute7 = X_global_attribute7)
1883 OR ( (Recinfo.global_attribute7 IS NULL)
1884 AND (X_global_attribute7 IS NULL)))
1885 AND ( (Recinfo.global_attribute8 = X_global_attribute8)
1886 OR ( (Recinfo.global_attribute8 IS NULL)
1887 AND (X_global_attribute8 IS NULL)))
1888 AND ( (Recinfo.global_attribute9 = X_global_attribute9)
1889 OR ( (Recinfo.global_attribute9 IS NULL)
1890 AND (X_global_attribute9 IS NULL)))
1891 AND ( (Recinfo.global_attribute10 = X_global_attribute10)
1892 OR ( (Recinfo.global_attribute10 IS NULL)
1893 AND (X_global_attribute10 IS NULL)))
1894 AND ( (Recinfo.global_attribute11 = X_global_attribute11)
1895 OR ( (Recinfo.global_attribute11 IS NULL)
1896 AND (X_global_attribute11 IS NULL)))
1897 AND ( (Recinfo.global_attribute12 = X_global_attribute12)
1898 OR ( (Recinfo.global_attribute12 IS NULL)
1899 AND (X_global_attribute12 IS NULL)))
1900 AND ( (Recinfo.global_attribute13 = X_global_attribute13)
1901 OR ( (Recinfo.global_attribute13 IS NULL)
1902 AND (X_global_attribute13 IS NULL)))
1903 AND ( (Recinfo.global_attribute14 = X_global_attribute14)
1904 OR ( (Recinfo.global_attribute14 IS NULL)
1905 AND (X_global_attribute14 IS NULL)))
1906 AND ( (Recinfo.global_attribute15 = X_global_attribute15)
1907 OR ( (Recinfo.global_attribute15 IS NULL)
1908 AND (X_global_attribute15 IS NULL)))
1909 AND ( (Recinfo.global_attribute16 = X_global_attribute16)
1910 OR ( (Recinfo.global_attribute16 IS NULL)
1911 AND (X_global_attribute16 IS NULL)))
1912 AND ( (Recinfo.global_attribute17 = X_global_attribute17)
1913 OR ( (Recinfo.global_attribute17 IS NULL)
1914 AND (X_global_attribute17 IS NULL)))
1915 AND ( (Recinfo.global_attribute18 = X_global_attribute18)
1916 OR ( (Recinfo.global_attribute18 IS NULL)
1917 AND (X_global_attribute18 IS NULL)))
1918 AND ( (Recinfo.global_attribute19 = X_global_attribute19)
1919 OR ( (Recinfo.global_attribute19 IS NULL)
1920 AND (X_global_attribute19 IS NULL)))
1921 AND ( (Recinfo.global_attribute20 = X_global_attribute20)
1922 OR ( (Recinfo.global_attribute20 IS NULL)
1923 AND (X_global_attribute20 IS NULL)))
1924 )
1925 then
1926 return;
1927 else
1928 FND_MESSAGE.Set_Name('FND','FORM_RECORD_CHANGED');
1929 FND_MSG_PUB.ADD;
1930 APP_EXCEPTION.Raise_Exception;
1931 end if;
1932
1933 EXCEPTION
1934 WHEN OTHERS THEN
1935 IF (SQLCODE <> -20001) THEN
1936 IF (SQLCODE = -54) THEN
1937 FND_MESSAGE.SET_NAME('SQLAP','AP_RESOURCE_BUSY');
1938 FND_MSG_PUB.ADD;
1939 ELSE
1940 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1941 FND_MSG_PUB.ADD;
1942 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1943 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
1944 FND_MESSAGE.SET_TOKEN('PARAMETERS','ROWID = ' || X_Rowid ||
1945 ', VENDOR_ID = ' || x_Vendor_Id);
1946 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
1947 END IF;
1948 END IF;
1949 APP_EXCEPTION.RAISE_EXCEPTION;
1950
1951 END Lock_Row;
1952 --
1953 --
1954 --
1955 PROCEDURE check_unique_vendor_name ( p_vendor_id in number,
1956 p_vendor_name in varchar2,
1957 X_calling_sequence in varchar2) is
1958 L_overlap_count number;
1959
1960 current_calling_sequence VARCHAR2(2000);
1961 debug_info VARCHAR2(100);
1962 --
1963 BEGIN
1964 -- Update the calling sequence
1965 --
1966 current_calling_sequence := 'AP_VENDORS_PKG.CHECK_UNIQUE_VENDOR_NAME<-' ||
1967 X_calling_sequence;
1968
1969 debug_info := 'Count vendors with same name';
1970 SELECT count(1)
1971 INTO L_overlap_count
1972 FROM po_vendors
1973 WHERE (p_vendor_id is null OR vendor_id <> p_vendor_id)
1974 AND (vendor_name like UPPER(SUBSTR(p_vendor_name,1,2))||'%'
1975 OR vendor_name like LOWER(SUBSTR(p_vendor_name,1,2))||'%'
1976 OR vendor_name like INITCAP(SUBSTR(p_vendor_name,1,2))||'%'
1977 OR vendor_name like LOWER(SUBSTR(p_vendor_name,1,1))||
1978 UPPER(SUBSTR(p_vendor_name,2,1))||'%')
1979 AND UPPER(vendor_name) = UPPER(p_vendor_name);
1980
1981 if (L_overlap_count >= 1 ) then
1982 fnd_message.set_name('SQLAP','AP_VEN_DUPLICATE_NAME');
1983 FND_MSG_PUB.ADD;
1984 app_exception.raise_exception;
1985 end if;
1986 --
1987 --
1988 EXCEPTION
1989 WHEN OTHERS THEN
1990 IF (SQLCODE <> -20001) THEN
1991 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1992 FND_MSG_PUB.ADD;
1993 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1994 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
1995 FND_MESSAGE.SET_TOKEN('PARAMETERS', 'VENDOR_ID = ' || p_vendor_id ||
1996 ', VENDOR_NAME = ' || p_vendor_name);
1997 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
1998 END IF;
1999 APP_EXCEPTION.RAISE_EXCEPTION;
2000
2001 END check_unique_vendor_name;
2002 --
2003 --
2004 procedure check_denormalized_vendor_name ( p_vendor_id in number,
2005 p_warning_flag in out NOCOPY varchar2,
2006 X_calling_sequence in varchar2) is
2007 --
2008 -- If invoices for this vendor have been selected, warn user, old name
2009 -- will appear on check - denormalised into AP_SELECTED_INVOICES
2010 -- join to sites so that index AP_SELECTED_INVOICES_N2 can be used
2011 -- there is no index on vendor_id in AP_SELECTED_INVOICES
2012 --
2013 l_overlap_count number;
2014 current_calling_sequence VARCHAR2(2000);
2015 debug_info VARCHAR2(100);
2016 --
2017 begin
2018 -- Update the calling sequence
2019 --
2020 current_calling_sequence := 'AP_VENDORS_PKG.CHECK_DENORMALIZED_VENDOR_NAME<-' ||
2021 X_calling_sequence;
2022
2023 debug_info := 'Count overlap count for the vendor_id and site_id';
2024 SELECT count(1)
2025 INTO L_overlap_count
2026 FROM po_vendor_sites s,
2027 ap_selected_invoices i
2028 WHERE s.vendor_id = p_vendor_id
2029 AND i.vendor_site_id = s.vendor_site_id;
2030 --
2031 --
2032 if (L_overlap_count >= 1 ) then
2033 fnd_message.set_name('SQLAP','AP_VENDOR_INV_SELECTED');
2034 FND_MSG_PUB.ADD;
2035 p_warning_flag := 'W';
2036 end if;
2037 --
2038 EXCEPTION
2039 WHEN OTHERS THEN
2040 IF (SQLCODE <> -20001) THEN
2041 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
2042 FND_MSG_PUB.ADD;
2043 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
2044 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
2045 FND_MESSAGE.SET_TOKEN('PARAMETERS', 'VENDOR_ID = ' || p_vendor_id ||
2046 ', WARNING_FLAG = ' || p_warning_flag);
2047 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
2048 END IF;
2049 APP_EXCEPTION.RAISE_EXCEPTION;
2050
2051 end check_denormalized_vendor_name;
2052 --
2053 --
2054 PROCEDURE check_unique_vendor_number ( p_vendor_id in number,
2055 p_vendor_number in varchar2,
2056 X_calling_sequence in varchar2 ) is
2057 --
2058 l_overlap_count number;
2059 current_calling_sequence VARCHAR2(2000);
2060 debug_info VARCHAR2(100);
2061 --
2062 BEGIN
2063 -- Update the calling sequence
2064 --
2065 current_calling_sequence := 'AP_VENDORS_PKG.CHECK_UNIQUE_VENDOR_NUMBER<-' ||
2066 X_calling_sequence;
2067 --
2068 --
2069 debug_info := 'Count overlap for vendor number';
2070 select count(1)
2071 into l_overlap_count
2072 from po_vendors
2073 where segment1 = p_vendor_number
2074 and ( p_vendor_id IS NULL or vendor_id <> p_vendor_id );
2075 --
2076 --
2077 if (l_overlap_count = 0) then
2078 --
2079 --
2080 debug_info := 'Count overlap from po_history_vendors';
2081 select count(1)
2082 into l_overlap_count
2083 from po_history_vendors
2084 where segment1 = p_vendor_number;
2085 --
2086 --
2087 end if;
2088 --
2089 --
2090 if (L_overlap_count >= 1 ) then
2091 fnd_message.set_name('SQLAP','AP_VEN_DUPLICATE_VEN_NUM');
2092 -- Bug 6940256 udhenuko Message set needs to be added to the stack.
2093 FND_MSG_PUB.ADD;
2094 -- Bug 6940256 udhenuko End
2095 app_exception.raise_exception;
2096 end if;
2097 --
2098 --
2099 EXCEPTION
2100 WHEN OTHERS THEN
2101 IF (SQLCODE <> -20001) THEN
2102 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
2103 FND_MSG_PUB.ADD;
2104 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
2105 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
2106 FND_MESSAGE.SET_TOKEN('PARAMETERS', 'VENDOR_ID = ' || p_vendor_id ||
2107 ', VENDOR_NUMBER = ' || p_vendor_number);
2108 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
2109 END IF;
2110 APP_EXCEPTION.RAISE_EXCEPTION;
2111 --
2112 --
2113 END check_unique_vendor_number;
2114 --
2115 --
2116 PROCEDURE CHECK_SELECTED_INVOICES (x_return_count in out NOCOPY number,
2117 x_vendor_id in number,
2118 X_calling_sequence in varchar2) IS
2119 current_calling_sequence VARCHAR2(2000);
2120 debug_info VARCHAR2(100);
2121 BEGIN
2122 -- Update the calling sequence
2123 --
2124 current_calling_sequence := 'AP_VENDORS_PKG.CHECK_SELECTED_INVOICES<-' ||
2125 X_calling_sequence;
2126
2127 debug_info := 'Count from AP_selected_invoices';
2128 select count(1)
2129 into x_return_count
2130 from AP_selected_invoices
2131 where vendor_id = x_vendor_id;
2132
2133 EXCEPTION
2134 WHEN OTHERS THEN
2135 IF (SQLCODE <> -20001) THEN
2136 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
2137 FND_MSG_PUB.ADD;
2138 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
2139 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
2140 FND_MESSAGE.SET_TOKEN('PARAMETERS','RETURN_COUNT = ' ||
2141 x_return_count ||
2142 ', VENDOR_ID = ' || x_vendor_id);
2143 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
2144 END IF;
2145 APP_EXCEPTION.RAISE_EXCEPTION;
2146
2147
2148 END CHECK_SELECTED_INVOICES;
2149
2150 procedure Check_Duplicate_Employee (p_rowid in varchar2,
2151 p_employee_id in number,
2152 X_calling_sequence in varchar2) is
2153 L_Duplicate_count number;
2154 current_calling_sequence VARCHAR2(2000);
2155 debug_info VARCHAR2(100);
2156 begin
2157 -- Update the calling sequence
2158 --
2159 current_calling_sequence := 'AP_VENDORS_PKG.CHECK_DUPLICATE_EMPLOYEE<-' ||
2160 X_calling_sequence;
2161
2162 debug_info := 'Count for same employee_id';
2163 SELECT count(1)
2164 INTO L_Duplicate_Count
2165 FROM PO_VENDORS
2166 WHERE (p_rowid IS NULL OR rowid <> p_rowid)
2167 AND employee_id = p_employee_id;
2168
2169 if (L_Duplicate_count > 0 ) then
2170 fnd_message.set_name('SQLAP','AP_EMPLOYEE_ASSIGNED');
2171 FND_MSG_PUB.ADD;
2172 app_exception.raise_exception;
2173 end if;
2174
2175 EXCEPTION
2176 WHEN OTHERS THEN
2177 IF (SQLCODE <> -20001) THEN
2178 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
2179 FND_MSG_PUB.ADD;
2180 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
2181 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
2182 FND_MESSAGE.SET_TOKEN('PARAMETERS','ROWID = ' || p_rowid ||
2183 ', EMPLOYEE_ID = ' || p_employee_id);
2184 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
2185 END IF;
2186 APP_EXCEPTION.RAISE_EXCEPTION;
2187
2188 end Check_Duplicate_Employee;
2189 --
2190 --
2191 procedure Resolve_employee( x_employee_id in number,
2192 x_employee_name in out NOCOPY varchar2,
2193 x_employee_number in out NOCOPY varchar2,
2194 X_calling_sequence in varchar2) IS
2195
2196 current_calling_sequence VARCHAR2(2000);
2197 debug_info VARCHAR2(100);
2198 begin
2199
2200 -- Update the calling sequence
2201 --
2202 current_calling_sequence := 'AP_VENDORS_PKG.RESOLVE_EMPLOYEE<-' ||
2203 X_calling_sequence;
2204
2205 -- For bug 2437569. Changed the view from hr_employees to
2206 -- hr_employees_current_v. This is to retrive the record details
2207 -- of only active employees .
2208
2209 -- For bug2900352. Backing out the changes done for bug 2437569 .
2210
2211
2212 debug_info := 'Get employee name and number from HR_Employees using ID';
2213 select full_name,
2214 employee_num
2215 into x_Employee_name,
2216 x_Employee_number
2217 from hr_employees --bug: 2900352
2218 where employee_id = x_employee_id;
2219
2220 EXCEPTION
2221 WHEN NO_DATA_FOUND THEN
2222 x_employee_name := '';
2223 x_employee_number := '';
2224 WHEN OTHERS THEN
2225 IF (SQLCODE <> -20001) THEN
2226 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
2227 FND_MSG_PUB.ADD;
2228 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
2229 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
2230 FND_MESSAGE.SET_TOKEN('PARAMETERS',', EMPLOYEE_ID = ' || x_employee_id);
2231 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
2232 END IF;
2233 APP_EXCEPTION.RAISE_EXCEPTION;
2234
2235 end Resolve_employee;
2236
2237
2238 procedure get_message_text ( x_application in varchar2,
2239 x_message_name in varchar2,
2240 x_message_text in out NOCOPY varchar2) is
2241
2242 begin
2243 x_message_text := fnd_message.get_string( x_application,
2244 x_message_name);
2245 if x_message_text is null then
2246 x_message_text := x_message_name;
2247 end if;
2248
2249 end Get_Message_text;
2250
2251 /* bug6830122. Creating Autonomus transaction for
2252 automatic supplier numbering for avoiding locikng
2253 contention for product setup table */
2254
2255 FUNCTION Update_Product_Setup return number is
2256 PRAGMA AUTONOMOUS_TRANSACTION;
2257
2258 CURSOR ap_product_setup_c is
2259 SELECT next_auto_supplier_num
2260 FROM ap_product_setup
2261 FOR UPDATE OF next_auto_supplier_num;
2262
2263 l_segment1 ap_product_setup.next_auto_supplier_num%type;
2264
2265 BEGIN
2266
2267 Open ap_product_setup_c;
2268 Fetch ap_product_setup_c into l_segment1;
2269
2270 If(ap_product_setup_c%notfound) Then
2271 RAISE NO_DATA_FOUND;
2272 End if;
2273 Close ap_product_setup_c;
2274
2275 UPDATE ap_product_setup
2276 SET next_auto_supplier_num = next_auto_supplier_num + 1;
2277 commit;
2278
2279 return l_segment1;
2280
2281 END Update_Product_Setup;
2282
2283 END AP_VENDORS_PKG;