2 Replies Latest reply on Dec 3, 2009 8:59 AM by S.PDASH

    Remove an End_Date on a Vendor

    Jason ORCL
      Hi- I have a custome interface to do Supplier adds and updates in R12.

      My issue is that once a update comes in for an already end_dated supplier, the customer wants the updates done, and the supplier reactivated.

      I have no problem making the updates, but passing null into the API does nothing to remove the end_date on the supplier.

      How can I do this? Is there an API to re-activate a supplier? Should I just pass in '31-DEC-4912' instead of null?
        • 1. Re: Remove an End_Date on a Vendor
          S.PDASH
          Hi Jason,
          Infact you asked the question and gave the clue for the answer.. :)

          Since the API you are using is not allowing you to update the Supplier end date as blank (null), better you pass a future date (say current date + 50 years )..31-DEC-2099..

          Would you mind to share which API you are using to update the supplier?? Incase you are using ap_vendor_pub_pkg.update_vendor API of R12, then we believe you can pass a null value to Supplier end date...

          Regards,
          S.P DASH
          • 2. Re: Remove an End_Date on a Vendor
            S.PDASH
            Hi Jason,
            You can try this as well...

            In Ap_VENDORS_PKG.updte_row look for end_date_active

                 UPDATE po_vendors
                      SET
                      vendor_id               =     x_Vendor_Id,
                      last_update_date          =     x_Last_Update_Date,
                      last_updated_by               =     x_Last_Updated_By,
                      vendor_name               =     x_Vendor_Name,
                      segment1               =     x_Segment1,
                      summary_flag               =     x_Summary_Flag,
                      enabled_flag               =     x_Enabled_Flag,
                      last_update_login          =     x_Last_Update_Login,
                      employee_id               =     x_Employee_Id,
                      validation_number          =     x_Validation_Number,
                      vendor_type_lookup_code          =     x_Vendor_Type_Lookup_Code,
                      customer_num               =     x_Customer_Num,
                      one_time_flag               =     x_One_Time_Flag,
                      parent_vendor_id          =      x_Parent_Vendor_Id,
                      min_order_amount          =     x_Min_Order_Amount,
                      ship_to_location_id          =     x_Ship_To_Location_Id,
                      bill_to_location_id          =     x_Bill_To_Location_Id,
                      ship_via_lookup_code          =     x_Ship_Via_Lookup_Code,
                      freight_terms_lookup_code     =     x_Freight_Terms_Lookup_Code,
                      fob_lookup_code               =     x_Fob_Lookup_Code,
                      terms_id               =     x_Terms_Id,
                      set_of_books_id               =     x_Set_Of_Books_Id,
                      always_take_disc_flag          =     x_Always_Take_Disc_Flag,
                      pay_date_basis_lookup_code     =     x_Pay_Date_Basis_Lookup_Code,
                      pay_group_lookup_code          =     x_Pay_Group_Lookup_Code,
                      payment_priority          =     x_Payment_Priority,
                      invoice_currency_code          =     x_Invoice_Currency_Code,
                      payment_currency_code          =     x_Payment_Currency_Code,
                      invoice_amount_limit          =     x_Invoice_Amount_Limit,
                      hold_all_payments_flag          =     x_Hold_All_Payments_Flag,
                      hold_future_payments_flag     =     x_Hold_Future_Payments_Flag,
                      hold_reason               =     x_Hold_Reason,
                      distribution_set_id          =     x_Distribution_Set_Id,
                      accts_pay_code_combination_id     =     x_Accts_Pay_CCID,
                      future_dated_payment_ccid     =     x_Future_Dated_Payment_CCID,
                      prepay_code_combination_id     =     x_Prepay_CCID,
            --TIN          num_1099               =     x_Num_1099,  bug5930700
            --TIN Proj enter value for num_1099 field  bug5930700
            num_1099 =
                      decode( UPPER(x_Vendor_Type_Lookup_Code),'EMPLOYEE',NULL,'CONTRACTOR',
                           decode(x_Org_Type_Lookup_Code,'INDIVIDUAL',NULL
                                               ,'FOREIGN INDIVIDUAL',NULL
                                               ,'PARTNERSHIP',NULL
                                               ,'FOREIGN PARTNERSHIP',NULL
                                               ,x_Num_1099),
                           x_Num_1099),
            --TIN Proj enter value for  individual_1099 field  bug5930700
            individual_1099 =
                      decode( UPPER(x_Vendor_Type_Lookup_Code),'EMPLOYEE',NULL,'CONTRACTOR',
                           decode(x_Org_Type_Lookup_Code,'INDIVIDUAL',x_Num_1099
                                               ,'FOREIGN INDIVIDUAL',x_Num_1099
                                               ,'PARTNERSHIP',x_Num_1099
                           ,'FOREIGN PARTNERSHIP',x_Num_1099
            ,NULL),
                           NULL),
                      type_1099               =     x_Type_1099,
                      withholding_status_lookup_code     =     x_withholding_stat_Lookup_Code,
                      withholding_start_date          =     x_Withholding_Start_Date,
                      organization_type_lookup_code     =     x_Org_Type_Lookup_Code,
                      vat_code               =     x_Vat_Code,
                      start_date_active          =     x_Start_Date_Active,
                      end_date_active          =     x_End_Date_Active,
                      qty_rcv_tolerance          =     x_Qty_Rcv_Tolerance,
                      minority_group_lookup_code     =     x_Minority_Group_Lookup_Code,
                      payment_method_lookup_code     =     x_Payment_Method_Lookup_Code,
                      bank_account_name          =     x_Bank_Account_Name,
                      bank_account_num          =     x_Bank_Account_Num,
                      bank_num               =     x_Bank_Num,
                      bank_account_type          =     x_Bank_Account_Type,
                      women_owned_flag          =     x_Women_Owned_Flag,
                      small_business_flag          =     x_Small_Business_Flag,
                      standard_industry_class          =     x_Standard_Industry_Class,
                      attribute_category          =     x_Attribute_Category,
                      attribute1               =     x_Attribute1,
                      attribute2               =     x_Attribute2,
                      attribute3               =     x_Attribute3,
                      attribute4               =     x_Attribute4,
                      attribute5               =     x_Attribute5,
                      hold_flag               =     x_Hold_Flag,
                      purchasing_hold_reason          =     x_Purchasing_Hold_Reason,
                      hold_by                    =     x_Hold_By,
                      hold_date               =     x_Hold_Date,
                      terms_date_basis          =     x_Terms_Date_Basis,
                      price_tolerance               =     x_Price_Tolerance,
                      attribute10               =     x_Attribute10,
                      attribute11               =     x_Attribute11,
                      attribute12               =     x_Attribute12,
                      attribute13               =     x_Attribute13,
                      attribute14               =     x_Attribute14,
                      attribute15               =     x_Attribute15,
                      attribute6               =     x_Attribute6,
                      attribute7               =     x_Attribute7,
                      attribute8               =     x_Attribute8,
                      attribute9               =     x_Attribute9,
                      days_early_receipt_allowed     =     x_Days_Early_Receipt_Allowed,
                      days_late_receipt_allowed     =     x_Days_Late_Receipt_Allowed,
                      enforce_ship_to_location_code     =     x_Enforce_Ship_To_Loc_Code,
                      exclusive_payment_flag          =     x_Exclusive_Payment_Flag,
                      federal_reportable_flag          =     x_Federal_Reportable_Flag,
                      hold_unmatched_invoices_flag     =     x_Hold_Unmatched_Invoices_Flag,
                      match_option               =     x_match_option,
                      create_debit_memo_flag          =     x_create_debit_memo_flag,
                      inspection_required_flag     =     x_Inspection_Required_Flag,
                      receipt_required_flag          =     x_Receipt_Required_Flag,
                      receiving_routing_id          =     x_Receiving_Routing_Id,
                      state_reportable_flag          =     x_State_Reportable_Flag,
                      tax_verification_date          =     x_Tax_Verification_Date,
                      auto_calculate_interest_flag     =     x_Auto_Calculate_Interest_Flag,
                      name_control               =     x_Name_Control,
                      allow_substitute_receipts_flag     =     x_Allow_Subst_Receipts_Flag,
                      allow_unordered_receipts_flag     =     x_Allow_Unord_Receipts_Flag,
                      receipt_days_exception_code     =     x_Receipt_Days_Exception_Code,
                      qty_rcv_exception_code          =     x_Qty_Rcv_Exception_Code,
                      offset_tax_flag               =     x_Offset_Tax_Flag,
                      exclude_freight_from_discount     =     x_Exclude_Freight_From_Disc,
                      vat_registration_num          =     x_Vat_Registration_Num,
                      tax_reporting_name          =     x_Tax_Reporting_Name,
                      awt_group_id               =     x_Awt_Group_Id,
                      check_digits               =     x_Check_Digits,
                      bank_number               =     x_Bank_Number,
                      allow_awt_flag               =     x_Allow_Awt_Flag,
                      bank_branch_type          =     x_bank_branch_type,
                      EDI_Payment_Method          =     x_EDI_Payment_Method,
                      EDI_Payment_Format          =     x_EDI_Payment_Format,
                      EDI_Remittance_Method          =     x_EDI_Remittance_Method,
                      EDI_Remittance_Instruction     =     x_EDI_Remittance_Instruction,
                      EDI_transaction_handling     =     x_EDI_transaction_handling,
                      Auto_Tax_Calc_Flag          =     x_Auto_Tax_Calc_Flag,
                      Auto_Tax_Calc_Override          =     x_Auto_Tax_Calc_Override,
                      Amount_Includes_Tax_Flag     =     x_Amount_Includes_Tax_Flag,
                      AP_Tax_Rounding_Rule          =     x_AP_Tax_Rounding_Rule,
                      vendor_name_alt               =     x_Vendor_Name_Alt,
            global_attribute_category = X_global_attribute_category,
            global_attribute1 = X_global_attribute1,
            global_attribute2 = X_global_attribute2,
            global_attribute3 = X_global_attribute3,
            global_attribute4 = X_global_attribute4,
            global_attribute5 = X_global_attribute5,
            global_attribute6 = X_global_attribute6,
            global_attribute7 = X_global_attribute7,
            global_attribute8 = X_global_attribute8,
            global_attribute9 = X_global_attribute9,
            global_attribute10 = X_global_attribute10,
            global_attribute11 = X_global_attribute11,
            global_attribute12 = X_global_attribute12,
            global_attribute13 = X_global_attribute13,
            global_attribute14 = X_global_attribute14,
            global_attribute15 = X_global_attribute15,
            global_attribute16 = X_global_attribute16,
            global_attribute17 = X_global_attribute17,
            global_attribute18 = X_global_attribute18,
            global_attribute19 = X_global_attribute19,
            global_attribute20 = X_global_attribute20,
            bank_charge_bearer = X_Bank_Charge_Bearer
                 WHERE     rowid = x_Rowid;

            Hope this will help...

            Regards,
            S.P DASH