0 Replies Latest reply: Jan 10, 2013 11:59 PM by Abhilash RSS

    Unable to update the VAT_CODE column through supplier sites API

    Abhilash
      Hi,

      I'm unable to update the vat code column of the ap_supplier_sites_all table using the ap_vendor_pub_pkg.update_vendor_site API.Oracle application instance 12.1.3 and OS linux.Please find the code below.I'm able to update other feilds,but not the vat_code.Please help on this.

      Thanks,
      Abhilash


      CREATE OR REPLACE PACKAGE BODY APPS.xx_wo172304_test
      AS
      PROCEDURE xx_vat_wo172304 (
      errbuf OUT VARCHAR2
      , retcode OUT VARCHAR2
      )
      IS
      CURSOR cur_vat
      IS
      SELECT site.*
      FROM apps.ap_suppliers supp, apps.ap_supplier_sites_all site
      WHERE site.vat_code IN
      ('CZ OEUS 20', 'CZ OEUZ 20', 'CZ OJCD 20', 'CZ OT20', 'CZ-20-EDC', 'OEUS20', 'OEUZ20', 'OPP20E'
      , 'OS20', 'OT20')
      AND supp.vendor_id = site.vendor_id
      AND site.org_id IN (608, 1508, 2396, 2397)
      AND site.vendor_site_id =68154;

      l_vendor_site_rec ap_vendor_pub_pkg.r_vendor_site_rec_type;
      l_vat_code ap_supplier_sites_all.VAT_CODE%TYPE;
      l_vendor_site_id ap_supplier_sites_all.vendor_site_id%TYPE;
      x_return_status VARCHAR2 (100) := NULL;
      x_msg_data VARCHAR2 (1000) := NULL;
      x_msg_count NUMBER := NULL;
      l_error_reason VARCHAR2 (2000) := NULL;
      l_user_id           number := FND_GLOBAL.USER_ID;
      l_last_update_login           number := FND_GLOBAL.LOGIN_ID;
      l_program_application_id           number := FND_GLOBAL.prog_appl_id;
      l_program_id           number := FND_GLOBAL.conc_program_id;
      l_request_id           number := FND_GLOBAL.conc_request_id;
      BEGIN
      -- mo_global.init ('SQLAP');

      FOR rec_vat IN cur_vat LOOP
      IF rec_vat.org_id = 608 THEN
      IF rec_vat.vat_code = 'OEUS20' THEN
      l_vendor_site_rec.vat_code := 'CZ OEUS21';
      ELSIF rec_vat.vat_code = 'OEUZ20' THEN
      l_vendor_site_rec.vat_code := 'CZ OEUZ21';
      ELSIF rec_vat.vat_code = 'OPP20E' THEN
      l_vendor_site_rec.vat_code := 'CZ OPP21E';
      ELSIF rec_vat.vat_code = 'OS20' THEN
      l_vendor_site_rec.vat_code := 'CZ OS21';
      ELSIF rec_vat.vat_code = 'OT20' THEN
      l_vendor_site_rec.vat_code := 'CZ OS21';
      END IF;
      ELSIF rec_vat.org_id = 1508 THEN
      IF rec_vat.vat_code = 'CZ OJCD 20' THEN
      l_vendor_site_rec.vat_code := 'CZ OJCD21';
      ELSIF rec_vat.vat_code = 'CZ OEUS 20' THEN
      l_vendor_site_rec.vat_code := 'CZ OEUS21';
      ELSIF rec_vat.vat_code = 'CZ OEUZ 20' THEN
      l_vendor_site_rec.vat_code := 'CZ OEUZ21';
      ELSIF rec_vat.vat_code = 'CZ OT20' THEN
      l_vendor_site_rec.vat_code := 'CZ OT21';
      END IF;
      ELSIF rec_vat.org_id = 2396 THEN
      IF rec_vat.vat_code = 'CZ OEUZ 20' THEN
      l_vendor_site_rec.vat_code := 'CZ OEUZ 21';
      ELSIF rec_vat.vat_code = 'CZ OJCD 20' THEN
      l_vendor_site_rec.vat_code := 'CZ OJCD 21';
      ELSIF rec_vat.vat_code = 'CZ OT20' THEN
      l_vendor_site_rec.vat_code := 'CZ OT21';
      ELSIF rec_vat.vat_code = 'CZ-20-EDC' THEN
      l_vendor_site_rec.vat_code := 'CZ-21-EDC';
      END IF;
      ELSIF rec_vat.org_id = 2397 THEN
      IF rec_vat.vat_code = 'CZ OEUS 20' THEN
      l_vendor_site_rec.vat_code := 'CZ OEUS 21';
      ELSIF rec_vat.vat_code = 'CZ OEUZ 20' THEN
      l_vendor_site_rec.vat_code := 'CZ OEUZ 21';
      ELSIF rec_vat.vat_code = 'CZ OJCD 20' THEN
      l_vendor_site_rec.vat_code := 'CZ OJCD 21';
      ELSIF rec_vat.vat_code = 'CZ OT20' THEN
      l_vendor_site_rec.vat_code := 'CZ OT21';
      ELSIF rec_vat.vat_code = 'CZ-20-EDC' THEN
      l_vendor_site_rec.vat_code := 'CZ-21-EDC';
      END IF;
      END IF;

      l_vendor_site_id := rec_vat.vendor_site_id;
      l_vendor_site_rec.org_id := rec_vat.org_id;
      l_vendor_site_rec.vendor_id := rec_vat.vendor_id;
      --l_vendor_site_rec.vendor_site_code:='318581-MOR. KRU';
      l_vendor_site_rec.rfq_only_site_flag := 'Y';
      -- l_vendor_site_rec.last_update_date := SYSDATE;
      l_vendor_site_rec.last_updated_by := 1134;   MARTIN.ROUNDS
      -- DBMS_OUTPUT.put_line ('VAT CODE:' || l_vendor_site_rec.vat_code);
      --DBMS_OUTPUT.put_line ('Vendor Site Id:' || l_vendor_site_id);
      fnd_file.put_line (fnd_file.LOG
      , 'VAT CODE:' || l_vendor_site_rec.vat_code
      );
      fnd_file.put_line (fnd_file.LOG
      , 'Vendor ID:' || rec_vat.vendor_id
      );
      fnd_file.put_line (fnd_file.LOG
      , 'Vendor Site Id:' || l_vendor_site_id
      );
      fnd_file.put_line (fnd_file.LOG
      , 'RFQ ONLY SITE FLAG:' || l_vendor_site_rec.rfq_only_site_flag
      );
      ap_vendor_pub_pkg.update_vendor_site (p_api_version => 1
      , x_return_status => x_return_status
      , x_msg_count => x_msg_count
      , x_msg_data => x_msg_data
      , p_vendor_site_rec => l_vendor_site_rec
      , p_vendor_site_id => l_vendor_site_id
      ); --p_calling_prog     IN  VARCHAR2 DEFAULT 'NOT ISETUP'
      -- pos_vendor_pub_pkg.update_vendor_site (p_vendor_site_rec => l_vendor_site_rec
      -- , x_return_status => x_return_status
      -- , x_msg_count => x_msg_count
      -- , x_msg_data => x_msg_data
      -- );

      --     ap_vendor_sites_pkg.update_row(
      --          p_vendor_site_rec => l_vendor_site_rec,
      --          p_last_update_date => sysdate,
      --          p_last_updated_by => l_user_id,
      --          p_last_update_login => l_last_update_login,
      --          p_request_id => l_request_id ,
      --          p_program_application_id => l_program_application_id,
      --          p_program_id => l_program_id,
      --          p_program_update_date => sysdate,
      --          p_vendor_site_id => l_vendor_site_id);

      fnd_file.put_line (fnd_file.LOG
      , 'Return Status:' || x_return_status
      );


      IF x_return_status <> fnd_api.g_ret_sts_success THEN
      IF x_msg_count >= 1 THEN
      FOR i IN 1 .. x_msg_count LOOP
      IF l_error_reason IS NULL THEN
      l_error_reason :=
      l_error_reason
      || ','
      || SUBSTR (fnd_msg_pub.get (p_encoded => fnd_api.g_false)
      , 1
      , 255
      )
      || SQLERRM;
      ELSE
      l_error_reason :=
      l_error_reason
      || ','
      || SUBSTR (fnd_msg_pub.get (p_encoded => fnd_api.g_false)
      , 1
      , 255
      )
      || SQLERRM;
      END IF;

      --DBMS_OUTPUT.put_line ('Supplier Site API Error-' || l_error_reason);
      fnd_file.put_line (fnd_file.LOG
      , 'Supplier Site API Error-' || l_error_reason
      );
      END LOOP;
      END IF;
      ELSIF x_return_status='S' THEN
      --DBMS_OUTPUT.put_line ('Supplier Site API Success-' || l_error_reason);
      fnd_file.put_line (fnd_file.LOG
      , 'Supplier Site API Success-' || l_error_reason
      );
      END IF;
      END LOOP;

      COMMIT;
      EXCEPTION
      WHEN OTHERS THEN
      --DBMS_OUTPUT.put_line ('Error-' || SQLERRM);
      fnd_file.put_line (fnd_file.LOG
      , 'Error-' || SQLERRM
      );
      END xx_vat_wo172304;
      END xx_wo172304_test;