Supplier Site Contacts Conversion Script

CREATE OR REPLACE PACKAGE BODY xxbr_ap_suppliers_pkg
AS
   PROCEDURE xxbr_ap_supp_cv_main (p_action IN VARCHAR2)
   IS
   BEGIN
      -- Call validate procedure to validate the Supplier information
      IF p_action = 'validate'
      THEN
         xxbr_ap_supp_cv_validate;
      ELSE
         NULL;
      END IF;

      -- Call Loaded procedure to load the data into Supplier interface table
      IF p_action = 'load'
      THEN
         xxbr_ap_supp_cv_load;
      ELSE
         NULL;
      END IF;
   END xxbr_ap_supp_cv_main;

   -- End Main Procedure XXBR_AP_SUPP_CV_MAIN For Supplier
   PROCEDURE xxbr_ap_supp_cv_validate
   IS
      -- Variable declaration
      l_i_accepted                 NUMBER                                := 0;
      l_i_rejected                 NUMBER                                := 0;
      l_ic_accepted                NUMBER                                := 0;
      l_ic_rejected                NUMBER                                := 0;
      l_sqlcode                    NUMBER;
      l_sqlerrm                    VARCHAR2 (1000);
      l_return_status              VARCHAR2 (1);
      l_i_err_msg                  VARCHAR2 (7000);
      l_count                      NUMBER                                := 0;
      ln_vendor_id                 NUMBER;
      lv_supplier_type             VARCHAR2 (1000);
      lv_invoice_currency          VARCHAR2 (3);
      lv_payment_currency          VARCHAR2 (3);
      lv_terms_name                VARCHAR2 (100);
      ln_term_id                   NUMBER;
      ln_set_of_books_id           NUMBER;
      ln_employee_id               NUMBER;
      lv_ship_to_location_id       NUMBER;
      ln_vat_code                  VARCHAR2 (200);
      lv_terms_date_basis          VARCHAR2 (50);
      lv_var_registration_number   NUMBER;
      lv_match_option              VARCHAR2 (50);
      l_final_error_msg            VARCHAR2 (100);
      lv_error_code                VARCHAR2 (100);
      -- Variable declaration for Bank Validation
      p_bank_country_code          hz_parties.country%TYPE;
      p_bank_name                  hz_parties.party_name%TYPE;
      p_bank_number                hz_organization_profiles.bank_or_branch_number%TYPE;
      x_bank_id                    NUMBER;
      x_end_date                   DATE;
      ln_bank_id                   NUMBER;
      -- Variable declaration for Bank Branch Validation
      x_branch_id                  NUMBER;
      p_bank_id                    NUMBER;
      p_branch_name                hz_parties.party_name%TYPE;
      p_branch_number              hz_organization_profiles.bank_or_branch_number%TYPE;
      -- Variable declaration for Bank Account Validation
      p_ext_bank_acct_rec          iby_ext_bankacct_pub.extbankacct_rec_type;
      l_dup_acct_id                NUMBER;
      l_dup_start_date             DATE;
      l_dup_end_date               DATE;
      x_response                   iby_fndcpt_common_pub.result_rec_type;
      x_return_status              VARCHAR2 (1000);
      x_msg_count                  NUMBER;
      x_msg_data                   VARCHAR2 (1000);
      l_bank_account_num_len       NUMBER;

      CURSOR ap_supplier_cur
      IS
         SELECT *
           FROM xxbr_ap_supp_cv_stg
          WHERE process_flag = 'N' AND rec_type IN ('D', 'd');

      supplier_tab                 xxbr_ap_supp_cv_stg%ROWTYPE;
   BEGIN
      FOR pick_data IN ap_supplier_cur
      LOOP
         l_return_status := 'Y';
         l_i_err_msg := NULL;
         l_final_error_msg := NULL;
         lv_error_code := NULL;
         x_branch_id := NULL;

/*+-----------------------------------------------------+
-- ======= Validation for Supplier Name PR05 ===========
+-------------------------------------------------------+ */
         BEGIN
            SELECT COUNT (vendor_id)
              INTO ln_vendor_id
              FROM apps.ap_suppliers
             WHERE (UPPER (vendor_name) IN TRIM (UPPER (pick_data.vendor_name))
                   );

            IF ln_vendor_id <> 0
            THEN
               l_return_status := 'E';
               l_i_err_msg :=
                           pick_data.vendor_name || ' Vendor already exists.';
               lv_error_code := 'Error-PR05';
            END IF;
         EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
               ln_vendor_id := NULL;
            WHEN OTHERS
            THEN
               l_return_status := 'E';
               l_sqlcode := SQLCODE;
               l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
         END;

         IF l_return_status = 'Y'
         THEN
            BEGIN
               UPDATE xxbr_ap_supp_cv_stg
                  SET process_flag = 'V',
                      record_status = 'Validated'
                WHERE pk_supplier = pick_data.pk_supplier;
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_sqlcode := SQLCODE;
                  l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
            END;
         ELSE
            BEGIN
               l_final_error_msg := 'Y';

               UPDATE xxbr_ap_supp_cv_stg
                  SET error_message = l_i_err_msg,
                      process_flag = 'E',
                      record_status = 'Failure',
                      ERROR_CODE = lv_error_code
                WHERE pk_supplier = pick_data.pk_supplier;
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_sqlcode := SQLCODE;
                  l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
            END;
         END IF;

/*+-------------------------------------------------------+
-- ========= End of Validation for Supplier Name =========
+--------------------------------------------------------+ */

         /*+------------------------------------------------------+
---Start of Validation for Classification Supplier type PR07---
+---------------------------------------------------------+*/
         BEGIN
            SELECT lookup_code
              INTO lv_supplier_type
              FROM apps.fnd_lookup_values
             WHERE UPPER (lookup_code) = TRIM (UPPER (pick_data.vendor_type))
               AND lookup_type = 'VENDOR TYPE'
               AND LANGUAGE = USERENV ('LANG');
         EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
               l_return_status := 'E';
               l_i_err_msg :=
                     l_i_err_msg
                  || ''
                  || 'Vendor Type '
                  || pick_data.vendor_type
                  || ' Not Valid for '
                  || pick_data.vendor_type;
               lv_error_code := lv_error_code || '' || 'Error-PR07';
            WHEN OTHERS
            THEN
               l_return_status := 'E';
               l_sqlcode := SQLCODE;
               l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
         END;

         IF l_return_status = 'Y'
         THEN
            BEGIN
               UPDATE xxbr_ap_supp_cv_stg
                  SET process_flag = 'V',
                      record_status = 'Validated'
                WHERE pk_supplier = pick_data.pk_supplier;
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_sqlcode := SQLCODE;
                  l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
            END;
         ELSE
            BEGIN
               l_final_error_msg := 'Y';

               UPDATE xxbr_ap_supp_cv_stg
                  SET error_message = l_i_err_msg,
                      process_flag = 'E',
                      record_status = 'Failure',
                      ERROR_CODE = lv_error_code
                WHERE pk_supplier = pick_data.pk_supplier;
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_sqlcode := SQLCODE;
                  l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
            END;
         END IF;

/*+------------------------------------------------------+
-- ========= End of Validation for Supplier Type =========
+---------------------------------------------------------+ */

         /*+-------------------------------------------------------+
-----Start of Validation for Invoice Currency PR08--------
+----------------------------------------------------------+*/
         BEGIN
            IF pick_data.invoice_currency IS NOT NULL
            THEN
               SELECT currency_code
                 INTO lv_invoice_currency
                 FROM apps.fnd_currencies
                WHERE currency_code = pick_data.invoice_currency;
            END IF;
         EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
               l_return_status := 'E';
               l_i_err_msg :=
                     l_i_err_msg
                  || ''
                  || 'Invoice Currency '
                  || pick_data.invoice_currency
                  || ' Not Valid for '
                  || pick_data.vendor_name;
               lv_error_code := lv_error_code || '' || 'Error-PR08';
            WHEN OTHERS
            THEN
               l_return_status := 'E';
               l_sqlcode := SQLCODE;
               l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
         END;

         IF l_return_status = 'Y'
         THEN
            BEGIN
               UPDATE xxbr_ap_supp_cv_stg
                  SET process_flag = 'V',
                      record_status = 'Validated'
                WHERE pk_supplier = pick_data.pk_supplier;
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_sqlcode := SQLCODE;
                  l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
            END;
         ELSE
            BEGIN
               l_final_error_msg := 'Y';

               UPDATE xxbr_ap_supp_cv_stg
                  SET error_message = l_i_err_msg,
                      process_flag = 'E',
                      record_status = 'Failure',
                      ERROR_CODE = lv_error_code
                WHERE pk_supplier = pick_data.pk_supplier;
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_sqlcode := SQLCODE;
                  l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
            END;
         END IF;

/*+---------------------------------------------------------+
-- ====== End of Validation for Invoice Currency ==========
+----------------------------------------------------------+ */

         /*+--------------------------------------------------------+
------Start of Validation for Payment Currency PR09---------
+------------------------------------------------------------+*/
         BEGIN
            IF pick_data.payment_currency IS NOT NULL
            THEN
               SELECT currency_code
                 INTO lv_payment_currency
                 FROM apps.fnd_currencies
                WHERE currency_code = pick_data.payment_currency;
            END IF;
         EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
               l_return_status := 'E';
               l_i_err_msg :=
                     l_i_err_msg
                  || ''
                  || 'Payment Currency '
                  || pick_data.payment_currency
                  || ' Not Valid for '
                  || pick_data.vendor_name;
               lv_error_code := lv_error_code || '' || 'Error-PR09';
            WHEN OTHERS
            THEN
               l_return_status := 'E';
               l_sqlcode := SQLCODE;
               l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
         END;

         IF l_return_status = 'Y'
         THEN
            BEGIN
               UPDATE xxbr_ap_supp_cv_stg
                  SET process_flag = 'V',
                      record_status = 'Validated'
                WHERE pk_supplier = pick_data.pk_supplier;
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_sqlcode := SQLCODE;
                  l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
            END;
         ELSE
            BEGIN
               l_final_error_msg := 'Y';

               UPDATE xxbr_ap_supp_cv_stg
                  SET error_message = l_i_err_msg,
                      process_flag = 'E',
                      record_status = 'Failure',
                      ERROR_CODE = lv_error_code
                WHERE pk_supplier = pick_data.pk_supplier;
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_sqlcode := SQLCODE;
                  l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
            END;
         END IF;

/*+--------------------------------------------------------+
-- ========== End of Validation for Payment Currency ========
+--------------------------------------------------------+ */

         /*+--------------------------------------------------------+
---Start of Validation for Set of Books Name Ledger Name PR10---
+-----------------------------------------------------------+*/
         BEGIN
            IF pick_data.set_of_books_name IS NOT NULL
            THEN
               SELECT ledger_id
                 INTO ln_set_of_books_id
                 FROM gl_ledgers
                WHERE NAME = pick_data.set_of_books_name;
            END IF;
         EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
               l_return_status := 'E';
               l_i_err_msg :=
                     l_i_err_msg
                  || ''
                  || 'Set of Book '
                  || pick_data.set_of_books_name
                  || ' Not Valid for '
                  || pick_data.vendor_name;
               lv_error_code := lv_error_code || '' || 'Error-PR10';
            WHEN OTHERS
            THEN
               l_return_status := 'E';
               l_sqlcode := SQLCODE;
               l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
         END;

         IF l_return_status = 'Y'
         THEN
            BEGIN
               UPDATE xxbr_ap_supp_cv_stg
                  SET process_flag = 'V',
                      record_status = 'Validated',
                      set_of_books_id = ln_set_of_books_id
                WHERE pk_supplier = pick_data.pk_supplier;
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_sqlcode := SQLCODE;
                  l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
            END;
         ELSE
            BEGIN
               l_final_error_msg := 'Y';

               UPDATE xxbr_ap_supp_cv_stg
                  SET error_message = l_i_err_msg,
                      process_flag = 'E',
                      record_status = 'Failure',
                      ERROR_CODE = lv_error_code
                WHERE pk_supplier = pick_data.pk_supplier;
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_sqlcode := SQLCODE;
                  l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
            END;
         END IF;

/*+-------------------------------------------------------+
-- === End of Validation for Set of Books Name =========
+---------------------------------------------------------+ */

         /*+------------------------------------------------------+
-------Start of Validation for Terms Data Basis PR12------
+---------------------------------------------------------+*/
         BEGIN
            IF pick_data.terms_date_basis IS NOT NULL
            THEN
               SELECT lookup_code
                 INTO lv_terms_date_basis
                 FROM fnd_lookup_values
                WHERE lookup_type = 'TERMS DATE BASIS'
                  AND lookup_code = pick_data.terms_date_basis;
            END IF;
         EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
               l_return_status := 'E';
               l_i_err_msg :=
                     l_i_err_msg
                  || ''
                  || 'Terms Data Basis '
                  || pick_data.terms_date_basis
                  || ' Not Valid for '
                  || pick_data.vendor_name;
               lv_error_code := lv_error_code || '' || 'Error-PR12';
            WHEN OTHERS
            THEN
               l_return_status := 'E';
               l_sqlcode := SQLCODE;
               l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
         END;

         IF l_return_status = 'Y'
         THEN
            BEGIN
               UPDATE xxbr_ap_supp_cv_stg
                  SET process_flag = 'V',
                      record_status = 'Validated'
                WHERE pk_supplier = pick_data.pk_supplier;
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_sqlcode := SQLCODE;
                  l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
            END;
         ELSE
            BEGIN
               l_final_error_msg := 'Y';

               UPDATE xxbr_ap_supp_cv_stg
                  SET error_message = l_i_err_msg,
                      process_flag = 'E',
                      record_status = 'Failure',
                      ERROR_CODE = lv_error_code
                WHERE pk_supplier = pick_data.pk_supplier;
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_sqlcode := SQLCODE;
                  l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
            END;
         END IF;

/*+-------------------------------------------------------+
-- ====== End of Validation for Terms Data Basis =========
/*+-------------------------------------------------------+

/*+-------------------------------------------------------+
-- ======= Validation for VAT Registration Number PR13====
/*+-------------------------------------------------------+

         BEGIN

   IF pick_data.vat_registration_number IS NOT NULL THEN

         SELECT count(vat_registration_num)
   into lv_var_registration_number
   FROM ap_suppliers
   WHERE UPPER(vat_registration_num) = TRIM(UPPER (pick_data.vat_registration_number));

    END IF;

         IF lv_var_registration_number <> 0 then
          l_return_status := 'E';
          l_i_err_msg       := 'There is currently another supplier in the system with the same VAT Registration Number.';
    lv_error_code:= lv_error_code ||''||'Error-PR13';
         END IF;

          EXCEPTION
            WHEN NO_DATA_FOUND THEN
            lv_var_registration_number := NULL;

            WHEN OTHERS THEN
              l_return_status := 'E';
              l_sqlcode       :=SQLCODE;
              l_sqlerrm       :=SUBSTR(SQLERRM,1,1000);

          END;

          IF l_return_status='Y' THEN
            BEGIN
              UPDATE XXBR_AP_SUPP_CV_STG
                SET  process_flag       =  'V'
              ,record_status     = 'Validated'
                WHERE pk_supplier       =  pick_data.pk_supplier;
            EXCEPTION
              WHEN OTHERS THEN
                l_sqlcode      :=SQLCODE;
                l_sqlerrm      :=SUBSTR(SQLERRM,1,1000);
                END;
          ELSE
             BEGIN
    l_final_error_msg:='Y';
               UPDATE XXBR_AP_SUPP_CV_STG
               SET    error_message       =  l_i_err_msg
                     ,process_flag        =  'E'
            ,record_status    = 'Failure'
                     ,error_code          = lv_error_code
               WHERE pk_supplier          =  pick_data.pk_supplier;
             EXCEPTION
              WHEN OTHERS THEN
                l_sqlcode      :=SQLCODE;
                l_sqlerrm      :=SUBSTR(SQLERRM,1,1000);
            END;
         END IF;


/*+-------------------------------------------------------+
-- === End of Validation for VAT Registration Number ======
/*+-------------------------------------------------------+/

/*+-------------------------------------------------------+
----Start of Validation for Ship to Location Code PR14 ---
/*+-------------------------------------------------------+

BEGIN

IF pick_data.ship_to_location_code IS NOT NULL THEN

SELECT DISTINCT LOCATION_id
INTO lv_ship_to_location_id
FROM HR_LOCATIONS_ALL
WHERE SHIP_TO_SITE_FLAG ='Y'
AND LOCATION_CODE = pick_data.ship_to_location_code;

    END IF;

  EXCEPTION
            WHEN NO_DATA_FOUND THEN
              l_return_status := 'E';
              l_i_err_msg     := l_i_err_msg ||'' || 'Ship to Location Code '|| pick_data.ship_to_location_code ||
              ' Not Valid for '|| pick_data.vendor_name;
    lv_error_code:= lv_error_code ||''||'Error-PR14';

            WHEN OTHERS THEN
              l_return_status := 'E';
              l_sqlcode       :=SQLCODE;
              l_sqlerrm       :=SUBSTR(SQLERRM,1,1000);

          END;

          IF l_return_status='Y' THEN
            BEGIN
              UPDATE XXBR_AP_SUPP_CV_STG
                SET  process_flag           =  'V'
              ,record_status         = 'Validated'
     ,ship_to_location_id    = lv_ship_to_location_id
   WHERE pk_supplier            =  pick_data.pk_supplier;
            EXCEPTION
              WHEN OTHERS THEN
                l_sqlcode      :=SQLCODE;
                l_sqlerrm      :=SUBSTR(SQLERRM,1,1000);
                END;
          ELSE
             BEGIN
    l_final_error_msg:='Y';
               UPDATE XXBR_AP_SUPP_CV_STG
               SET    error_message       =  l_i_err_msg
                     ,process_flag        =  'E'
            ,record_status    = 'Failure'
                     ,error_code          = lv_error_code
               WHERE pk_supplier          =  pick_data.pk_supplier;
             EXCEPTION
              WHEN OTHERS THEN
                l_sqlcode      :=SQLCODE;
                l_sqlerrm      :=SUBSTR(SQLERRM,1,1000);
            END;
         END IF;

/*+--------------------------------------------------------+
--==== End of Validation for Ship to Location Code ==========
+---------------------------------------------------------+ */

         /*+--------------------------------------------------------+
------- Start of Validation for Match Option PR15-----------
+----------------------------------------------------------+*/
         BEGIN
            IF pick_data.match_option IS NOT NULL
            THEN
               SELECT lookup_code
                 INTO lv_match_option
                 FROM fnd_lookups
                WHERE lookup_type = 'POS_INVOICE_MATCH_OPTION'
                  AND UPPER (lookup_code) = UPPER (pick_data.match_option);
            END IF;
         EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
               l_return_status := 'E';
               l_i_err_msg :=
                     l_i_err_msg
                  || ''
                  || 'Match Option '
                  || pick_data.match_option
                  || ' Not Valid for '
                  || pick_data.vendor_name;
               lv_error_code := lv_error_code || '' || 'Error-PR15';
            WHEN OTHERS
            THEN
               l_return_status := 'E';
               l_sqlcode := SQLCODE;
               l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
         END;

         IF l_return_status = 'Y'
         THEN
            BEGIN
               UPDATE xxbr_ap_supp_cv_stg
                  SET process_flag = 'V',
                      record_status = 'Validated',
                      ship_to_location_id = lv_ship_to_location_id
                WHERE pk_supplier = pick_data.pk_supplier;
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_sqlcode := SQLCODE;
                  l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
            END;
         ELSE
            BEGIN
               l_final_error_msg := 'Y';

               UPDATE xxbr_ap_supp_cv_stg
                  SET error_message = l_i_err_msg,
                      process_flag = 'E',
                      record_status = 'Failure',
                      ERROR_CODE = lv_error_code
                WHERE pk_supplier = pick_data.pk_supplier;
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_sqlcode := SQLCODE;
                  l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
            END;
         END IF;

/*+-------------------------------------------------+
-- ========= End of Validation for Match Option ===========
+-------------------------------------------------------+ */

         /*+------------------------------------------------------+
---Start of Validation for Employee Name if Vendor_type_lookup_code = 'EMPLOYEE' PR16------
+----------------------------------------------------------+*/
         BEGIN
            IF     pick_data.vendor_type = 'EMPLOYEE'
               AND pick_data.employee_number IS NOT NULL
            THEN
               SELECT person_id
                 INTO ln_employee_id
                 FROM per_all_people_f
                WHERE employee_number = pick_data.employee_number;
            END IF;
         EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
               l_return_status := 'E';
               l_i_err_msg :=
                     l_i_err_msg
                  || ''
                  || 'Employee Number '
                  || pick_data.employee_number
                  || ' Not Valid for '
                  || pick_data.vendor_name
                  || '. Please enter valid employee number';
               lv_error_code := lv_error_code || '' || 'Error-PR16';
            WHEN OTHERS
            THEN
               l_return_status := 'E';
               l_sqlcode := SQLCODE;
               l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
         END;

         IF l_return_status = 'Y'
         THEN
            BEGIN
               UPDATE xxbr_ap_supp_cv_stg
                  SET process_flag = 'V',
                      record_status = 'Validated',
                      employee_id = ln_employee_id
                WHERE pk_supplier = pick_data.pk_supplier;
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_sqlcode := SQLCODE;
                  l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
            END;
         ELSE
            BEGIN
               l_final_error_msg := 'Y';

               UPDATE xxbr_ap_supp_cv_stg
                  SET error_message = l_i_err_msg,
                      process_flag = 'E',
                      record_status = 'Failure',
                      ERROR_CODE = lv_error_code
                WHERE pk_supplier = pick_data.pk_supplier;
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_sqlcode := SQLCODE;
                  l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
            END;
         END IF;

/*+--------------------------------------------------------+
-- ====End of Validation for Employee Name if Vendor_type_lookup_code = 'EMPLOYEE' ==
+--------------------------------------------------------+ */

         /*+-----------------------------------------------------+
----Start of Validation if Vendor_type = 'EMPLOYEE' then employee_number can not be null PR29----
+--------------------------------------------------------+*/
         BEGIN
            IF     pick_data.vendor_type = 'EMPLOYEE'
               AND pick_data.employee_number IS NULL
            THEN
               l_return_status := 'E';
               l_i_err_msg :=
                     l_i_err_msg
                  || ''
                  || 'For vendor type = EMPLOYEE, employee_number can not be null. Please enter valid employee number';
            END IF;
         EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
               l_return_status := 'E';
               l_i_err_msg :=
                     l_i_err_msg
                  || ''
                  || 'For vendor type = EMPLOYEE, employee_number can not be null. Please enter valid employee number';
               lv_error_code := lv_error_code || '' || 'Error-PR29';
            WHEN OTHERS
            THEN
               l_return_status := 'E';
               l_sqlcode := SQLCODE;
               l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
         END;

         IF l_return_status = 'Y'
         THEN
            BEGIN
               UPDATE xxbr_ap_supp_cv_stg
                  SET process_flag = 'V',
                      record_status = 'Validated',
                      employee_id = ln_employee_id
                WHERE pk_supplier = pick_data.pk_supplier;
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_sqlcode := SQLCODE;
                  l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
            END;
         ELSE
            BEGIN
               l_final_error_msg := 'Y';

               UPDATE xxbr_ap_supp_cv_stg
                  SET error_message = l_i_err_msg,
                      process_flag = 'E',
                      record_status = 'Failure',
                      ERROR_CODE = lv_error_code
                WHERE pk_supplier = pick_data.pk_supplier;
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_sqlcode := SQLCODE;
                  l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
            END;
         END IF;

/*+------------------------------------------------------+
--===End of Validation if Vendor_type = 'EMPLOYEE' then employee_number can not be null ===
+----------------------------------------------------------+ */

         /*+-------------------------------------------------------+
-----Start of Validation if Vendor_type = 'VENDOR' then employee_number should be null PR30----
+---------------------------------------------------------+*/
         BEGIN
            IF     pick_data.vendor_type = 'VENDOR'
               AND pick_data.employee_number IS NOT NULL
            THEN
               l_return_status := 'E';
               l_i_err_msg :=
                     l_i_err_msg
                  || ''
                  || 'For vendor type = VENDOR, employee_number should be null. Please do not enter employee number';
            END IF;
         EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
               l_return_status := 'E';
               l_i_err_msg :=
                     l_i_err_msg
                  || ''
                  || 'For vendor type = VENDOR, employee_number should be null. Please do not enter employee number';
               lv_error_code := lv_error_code || '' || 'Error-PR30';
            WHEN OTHERS
            THEN
               l_return_status := 'E';
               l_sqlcode := SQLCODE;
               l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
         END;

         IF l_return_status = 'Y'
         THEN
            BEGIN
               UPDATE xxbr_ap_supp_cv_stg
                  SET process_flag = 'V',
                      record_status = 'Validated',
                      employee_id = ln_employee_id
                WHERE pk_supplier = pick_data.pk_supplier;
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_sqlcode := SQLCODE;
                  l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
            END;
         ELSE
            BEGIN
               l_final_error_msg := 'Y';

               UPDATE xxbr_ap_supp_cv_stg
                  SET error_message = l_i_err_msg,
                      process_flag = 'E',
                      record_status = 'Failure',
                      ERROR_CODE = lv_error_code
                WHERE pk_supplier = pick_data.pk_supplier;
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_sqlcode := SQLCODE;
                  l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
            END;
         END IF;

/*+-----------------------------------------------------+
-- ====End of Validation if Vendor_type = 'VENDOR' then employee_number should be null ======
+--------------------------------------------------------+ */

         /*+------------------------------------------------------+
------Start of Validation for Hold Future Payments Flag PR11--
+----------------------------------------------------------+*/
         BEGIN
            IF pick_data.hold_future_payments_flag IS NOT NULL
            THEN
               IF pick_data.hold_future_payments_flag NOT IN ('Y', 'N')
               THEN
                  l_return_status := 'E';
                  l_i_err_msg :=
                        l_i_err_msg
                     || ''
                     || 'Hold Future Payments Flag '
                     || pick_data.hold_future_payments_flag
                     || ' Not Valid for '
                     || pick_data.vendor_name
                     || '. Please enter Y or N.';
                  lv_error_code := lv_error_code || '' || 'Error-PR11';
               END IF;
            END IF;

            IF l_return_status = 'Y'
            THEN
               BEGIN
                  UPDATE xxbr_ap_supp_cv_stg
                     SET process_flag = 'V',
                         record_status = 'Validated'
                   WHERE pk_supplier = pick_data.pk_supplier;
               EXCEPTION
                  WHEN OTHERS
                  THEN
                     l_sqlcode := SQLCODE;
                     l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
               END;
            ELSE
               BEGIN
                  l_final_error_msg := 'Y';

                  UPDATE xxbr_ap_supp_cv_stg
                     SET error_message = l_i_err_msg,
                         process_flag = 'E',
                         record_status = 'Failure',
                         ERROR_CODE = lv_error_code
                   WHERE pk_supplier = pick_data.pk_supplier;
               EXCEPTION
                  WHEN OTHERS
                  THEN
                     l_sqlcode := SQLCODE;
                     l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
               END;
            END IF;
         END;

/*+---------------------------------------------------------+
-- === End of Validation for Hold Future Payments Flag =====
+---------------------------------------------------------+ */

         /*+--------------------------------------------------------+
---Start of Validation for Auto Calculate Interest Flag PR17----
+----------------------------------------------------------+*/
         BEGIN
            IF pick_data.auto_calculate_interest_flag IS NOT NULL
            THEN
               IF pick_data.auto_calculate_interest_flag NOT IN ('Y', 'N')
               THEN
                  l_return_status := 'E';
                  l_i_err_msg :=
                        l_i_err_msg
                     || ''
                     || 'Auto Calculate Interest Flag '
                     || pick_data.auto_calculate_interest_flag
                     || ' Not Valid for '
                     || pick_data.vendor_name
                     || '. Please enter Y or N.';
                  lv_error_code := lv_error_code || '' || 'Error-PR17';
               END IF;
            END IF;

            IF l_return_status = 'Y'
            THEN
               BEGIN
                  UPDATE xxbr_ap_supp_cv_stg
                     SET process_flag = 'V',
                         record_status = 'Validated'
                   WHERE pk_supplier = pick_data.pk_supplier;
               EXCEPTION
                  WHEN OTHERS
                  THEN
                     l_sqlcode := SQLCODE;
                     l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
               END;
            ELSE
               BEGIN
                  l_final_error_msg := 'Y';

                  UPDATE xxbr_ap_supp_cv_stg
                     SET error_message = l_i_err_msg,
                         process_flag = 'E',
                         record_status = 'Failure',
                         ERROR_CODE = lv_error_code
                   WHERE pk_supplier = pick_data.pk_supplier;
               EXCEPTION
                  WHEN OTHERS
                  THEN
                     l_sqlcode := SQLCODE;
                     l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
               END;
            END IF;
         END;

/*+--------------------------------------------------------+
-- === End of Validation for Auto Calculate Interest Flag=====
+-----------------------------------------------------------+ */

         /*+-----------------------------------------------------+
--------Start of Validation for One Time Flag PR18---------
+---------------------------------------------------------+*/
         BEGIN
            IF pick_data.one_time_flag IS NOT NULL
            THEN
               IF pick_data.one_time_flag NOT IN ('Y', 'N')
               THEN
                  l_return_status := 'E';
                  l_i_err_msg :=
                        l_i_err_msg
                     || ''
                     || 'One Time Flag '
                     || pick_data.one_time_flag
                     || ' Not Valid for '
                     || pick_data.vendor_name
                     || '. Please enter Y or N.';
                  lv_error_code := lv_error_code || '' || 'Error-PR18';
               END IF;
            END IF;

            IF l_return_status = 'Y'
            THEN
               BEGIN
                  UPDATE xxbr_ap_supp_cv_stg
                     SET process_flag = 'V',
                         record_status = 'Validated'
                   WHERE pk_supplier = pick_data.pk_supplier;
               EXCEPTION
                  WHEN OTHERS
                  THEN
                     l_sqlcode := SQLCODE;
                     l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
               END;
            ELSE
               BEGIN
                  l_final_error_msg := 'Y';

                  UPDATE xxbr_ap_supp_cv_stg
                     SET error_message = l_i_err_msg,
                         process_flag = 'E',
                         record_status = 'Failure',
                         ERROR_CODE = lv_error_code
                   WHERE pk_supplier = pick_data.pk_supplier;
               EXCEPTION
                  WHEN OTHERS
                  THEN
                     l_sqlcode := SQLCODE;
                     l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
               END;
            END IF;
         END;

/*+--------------------------------------------------------------------------------------+
-- ================== End of Validation for One Time Flag ==============
+--------------------------------------------------------------------------------------+ */

         /*+--------------------------------------------------------------------------------------+
------------------- Start of Validation for Bank exists or not PR23------------------------
+--------------------------------------------------------------------------------------+*/
         BEGIN
            IF pick_data.bank_name IS NOT NULL
                                      --AND pick_data.bank_number IS NOT NULL
            THEN
               p_bank_country_code := 'GB';
-- This hardcoding needs to be removed and this field needs to be populated as per Supplier country
               p_bank_name := pick_data.bank_name;
               p_bank_number := pick_data.bank_number;
               ce_bank_pub.check_bank_exist (p_bank_country_code,
                                             p_bank_name,
                                             p_bank_number,
                                             x_bank_id,
                                             x_end_date
                                            );
               COMMIT;
            END IF;

            IF x_bank_id IS NULL
            THEN
               l_return_status := 'E';
               l_i_err_msg :=
                     l_i_err_msg
                  || ''
                  || 'The Bank '
                  || pick_data.bank_name
                  || ' does not exist in the system. Please create the bank first before creating ext bank account for vendor '
                  || pick_data.vendor_name;
               lv_error_code := lv_error_code || '' || 'Error-PR23';
            END IF;
         EXCEPTION
            WHEN OTHERS
            THEN
               l_return_status := 'E';
               l_sqlcode := SQLCODE;
               l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
         END;

         IF l_return_status = 'Y'
         THEN
            BEGIN
               UPDATE xxbr_ap_supp_cv_stg
                  SET process_flag = 'V',
                      record_status = 'Validated',
                      bank_id = x_bank_id
                WHERE pk_supplier = pick_data.pk_supplier;
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_sqlcode := SQLCODE;
                  l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
            END;
         ELSE
            BEGIN
               l_final_error_msg := 'Y';

               UPDATE xxbr_ap_supp_cv_stg
                  SET error_message = l_i_err_msg,
                      process_flag = 'E',
                      record_status = 'Failure',
                      ERROR_CODE = lv_error_code
                WHERE pk_supplier = pick_data.pk_supplier;
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_sqlcode := SQLCODE;
                  l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
            END;
         END IF;

         COMMIT;

/*+--------------------------------------------------------------------------------------+
-- ====== End of Validation for Bank exists or not ============
+--------------------------------------------------------------------------------------+ */

         /*+--------------------------------------------------------------------------------------+
------- Start of Validation for Bank Branch exists or not PR22-----------
+--------------------------------------------------------------------------------------+*/
         BEGIN
            IF pick_data.bank_branch_name IS NOT NULL
            THEN
               p_bank_country_code := 'GB';
-- This hardcoding needs to be removed and this field needs to be populated as per Supplier country
               p_branch_name := pick_data.bank_branch_name;
               p_branch_number := pick_data.bank_number;

               BEGIN
                  SELECT party_id
                    INTO ln_bank_id
                    FROM hz_parties
                   WHERE party_name = pick_data.bank_name;
               EXCEPTION
                  WHEN OTHERS
                  THEN
                     NULL;
               END;

--p_bank_id := 7169;
               p_bank_id := ln_bank_id;
               ce_bank_pub.check_branch_exist (p_bank_id,
                                               p_branch_name,
                                               p_branch_number,
                                               x_branch_id,
                                               x_end_date
                                              );
               COMMIT;
            END IF;

            IF x_branch_id IS NULL
            THEN
               l_return_status := 'E';
               l_i_err_msg :=
                     l_i_err_msg
                  || ''
                  || 'The branch '
                  || pick_data.bank_branch_name
                  || ' does not exist for bank '
                  || pick_data.bank_name
                  || ' in the system. Please create the bank branch first before creating ext bank account for vendor '
                  || pick_data.vendor_name;
               lv_error_code := lv_error_code || '' || 'Error-PR22';
            END IF;
         EXCEPTION
            WHEN OTHERS
            THEN
               l_return_status := 'E';
               l_sqlcode := SQLCODE;
               l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
         END;

         IF l_return_status = 'Y'
         THEN
            BEGIN
               UPDATE xxbr_ap_supp_cv_stg
                  SET process_flag = 'V',
                      record_status = 'Validated',
                      branch_id = x_branch_id
                WHERE pk_supplier = pick_data.pk_supplier;
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_sqlcode := SQLCODE;
                  l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
            END;
         ELSE
            BEGIN
               l_final_error_msg := 'Y';

               UPDATE xxbr_ap_supp_cv_stg
                  SET error_message = l_i_err_msg,
                      process_flag = 'E',
                      record_status = 'Failure',
                      ERROR_CODE = lv_error_code
                WHERE pk_supplier = pick_data.pk_supplier;
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_sqlcode := SQLCODE;
                  l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
            END;
         END IF;

         COMMIT;

/*+--------------------------------------------------------------------------------------+
-- ==== End of Validation for Bank Branch exists or not ========
+--------------------------------------------------------------------------------------+ */

         /*+--------------------------------------------------------------------------------------+
----- Start of Validation for Bank Account Number Length PR31-----------
+--------------------------------------------------------------------------------------+*/
         BEGIN
            IF pick_data.bank_account_number IS NOT NULL
            THEN
               SELECT LENGTH (pick_data.bank_account_number)
                 INTO l_bank_account_num_len
                 FROM DUAL;

               COMMIT;
            END IF;

            IF l_bank_account_num_len > 8
            THEN
               l_return_status := 'E';
               l_i_err_msg :=
                     l_i_err_msg
                  || ''
                  || 'Please enter a maximum of 8 characters for the Account Number for Account '
                  || pick_data.bank_account_number
                  || 'for bank '
                  || pick_data.bank_name
                  || ' for vendor '
                  || pick_data.vendor_name;
               lv_error_code := lv_error_code || '' || 'Error-PR31';
            END IF;
         EXCEPTION
            WHEN OTHERS
            THEN
               l_return_status := 'E';
               l_sqlcode := SQLCODE;
               l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
         END;

         IF l_return_status = 'Y'
         THEN
            BEGIN
               UPDATE xxbr_ap_supp_cv_stg
                  SET process_flag = 'V',
                      record_status = 'Validated',
                      bank_account_id = l_dup_acct_id
                WHERE pk_supplier = pick_data.pk_supplier;
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_sqlcode := SQLCODE;
                  l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
            END;
         ELSE
            BEGIN
               l_final_error_msg := 'Y';

               UPDATE xxbr_ap_supp_cv_stg
                  SET error_message = l_i_err_msg,
                      process_flag = 'E',
                      record_status = 'Failure',
                      ERROR_CODE = lv_error_code
                WHERE pk_supplier = pick_data.pk_supplier;
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_sqlcode := SQLCODE;
                  l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
            END;
         END IF;

         COMMIT;

/*+----------------------------------------------------+
--==== End of Validation for Bank Account Number Length ====
+--------------------------------------------------------+ */

         /*+-----------------------------------------------------+
--- Start of Validation for Bank Account should not exist PR27---
+-----------------------------------------------------------+*/
         BEGIN
            IF     pick_data.bank_account_name IS NOT NULL
               AND pick_data.bank_account_number IS NOT NULL
            THEN
               p_ext_bank_acct_rec.bank_id := ln_bank_id;
               p_ext_bank_acct_rec.branch_id := x_branch_id;
               p_ext_bank_acct_rec.bank_account_name :=
                                                  pick_data.bank_account_name;
               p_ext_bank_acct_rec.bank_account_num :=
                                                pick_data.bank_account_number;
               --p_ext_bank_acct_rec.acct_owner_party_id := 7162;
               p_ext_bank_acct_rec.currency := 'GBP';
               p_ext_bank_acct_rec.country_code := 'GB';
               iby_ext_bankacct_pub.check_ext_acct_exist
                                 (p_api_version            => '1.0',
                                  p_init_msg_list          => 'T',
                                  p_ext_bank_acct_rec      => p_ext_bank_acct_rec,
                                  x_acct_id                => l_dup_acct_id,
                                  x_start_date             => l_dup_start_date,
                                  x_end_date               => l_dup_end_date,
                                  x_return_status          => x_return_status,
                                  x_msg_count              => x_msg_count,
                                  x_msg_data               => x_msg_data,
                                  x_response               => x_response
                                 );
               COMMIT;
            END IF;

            IF l_dup_acct_id IS NOT NULL
            THEN
               l_return_status := 'E';
               l_i_err_msg :=
                     l_i_err_msg
                  || ''
                  || 'The Account '
                  || pick_data.bank_account_number
                  || ' exist for bank '
                  || pick_data.bank_name
                  || ' in the system for vendor '
                  || pick_data.vendor_name;
               lv_error_code := lv_error_code || '' || 'Error-PR27';
            END IF;
         EXCEPTION
            WHEN OTHERS
            THEN
               l_return_status := 'E';
               l_sqlcode := SQLCODE;
               l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
         END;

         IF l_return_status = 'Y'
         THEN
            BEGIN
               UPDATE xxbr_ap_supp_cv_stg
                  SET process_flag = 'V',
                      record_status = 'Validated',
                      bank_account_id = l_dup_acct_id
                WHERE pk_supplier = pick_data.pk_supplier;
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_sqlcode := SQLCODE;
                  l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
            END;
         ELSE
            BEGIN
               l_final_error_msg := 'Y';

               UPDATE xxbr_ap_supp_cv_stg
                  SET error_message = l_i_err_msg,
                      process_flag = 'E',
                      record_status = 'Failure',
                      ERROR_CODE = lv_error_code
                WHERE pk_supplier = pick_data.pk_supplier;
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_sqlcode := SQLCODE;
                  l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
            END;
         END IF;

         COMMIT;

/*+--------------------------------------------------------------------------------------+
-- ============= End of Validation for Bank Account should not exist =======
+--------------------------------------------------------------------------------------+ */

         /*+--------------------------------------------------------------------------------------+
------------------- Start of Validation for Payment Terms PR06---------------
+--------------------------------------------------------------------------------------+*/
         BEGIN
            IF pick_data.terms_name IS NOT NULL
            THEN
               SELECT NAME, term_id
                 INTO lv_terms_name, ln_term_id
                 FROM apps.ap_terms_tl
                WHERE UPPER (NAME) = UPPER (pick_data.terms_name)
                  AND LANGUAGE = USERENV ('LANG');
            END IF;
         EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
               l_return_status := 'E';
               l_i_err_msg :=
                     l_i_err_msg
                  || ''
                  || 'Payment Term '
                  || pick_data.terms_name
                  || ' Not Valid for '
                  || pick_data.vendor_name;
               lv_error_code := lv_error_code || '' || 'Error-PR06';
            WHEN OTHERS
            THEN
               l_return_status := 'E';
               l_sqlcode := SQLCODE;
               l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
         END;

         IF l_return_status = 'Y'
         THEN
            BEGIN
               UPDATE xxbr_ap_supp_cv_stg
                  SET process_flag = 'V',
                      record_status = 'Validated',
                      term_id = ln_term_id
                WHERE pk_supplier = pick_data.pk_supplier;
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_sqlcode := SQLCODE;
                  l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
            END;
         ELSE
            BEGIN
               l_final_error_msg := 'Y';

               UPDATE xxbr_ap_supp_cv_stg
                  SET error_message = l_i_err_msg,
                      process_flag = 'E',
                      record_status = 'Failure',
                      ERROR_CODE = lv_error_code
                WHERE pk_supplier = pick_data.pk_supplier;
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_sqlcode := SQLCODE;
                  l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
            END;
         END IF;

-------------------------------------------------------------------------------------------------------------
         IF l_final_error_msg IS NULL
         THEN
            BEGIN
               UPDATE xxbr_ap_supp_cv_stg
                  SET process_flag = 'V',
                      record_status = 'Validated'
                --   ,term_id            = ln_term_id
               WHERE  pk_supplier = pick_data.pk_supplier;
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_sqlcode := SQLCODE;
                  l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
            END;
         ELSE
            BEGIN
               UPDATE xxbr_ap_supp_cv_stg
                  SET                 --   error_message       =  l_i_err_msg
                     process_flag = 'E',
                     record_status = 'Failure'
                --   ,error_code      = 'VE'
               WHERE  pk_supplier = pick_data.pk_supplier;
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_sqlcode := SQLCODE;
                  l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
            END;
         END IF;
------------------------------------------------------------------------------------------------------------
      END LOOP;
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line ('NO Data Found');
         COMMIT;
   END xxbr_ap_supp_cv_validate;

/*+--------------------------------------------------------------------------------------+
-- ============== End of Validation for Payment Terms ==================
+--------------------------------------------------------------------------------------+ */
   PROCEDURE xxbr_ap_supp_cv_load
   IS
      CURSOR ap_supplier_details_cur
      IS
         SELECT *
           FROM xxbr_ap_supp_cv_stg
          WHERE process_flag = 'V';

      -- Variable Declaration
      l_sqlerrm         VARCHAR2 (1000);
      l_i_err_msg       VARCHAR2 (1000);
      ln_sup_int_id     NUMBER;
      ln_user_id        NUMBER
                            := TO_NUMBER (apps.fnd_profile.VALUE ('USER_ID'));
      ld_sysdate        DATE            := SYSDATE;
--ln_resp_id := TO_NUMBER(apps.fnd_profile.value('RESP_ID'));
--ln_resp_appl_id :=TO_NUMBER(apps.fnd_profile.value('RESP_APPL_ID'));
      ln_iby_temp_ext   NUMBER;
   BEGIN
      FOR load_data IN ap_supplier_details_cur
      LOOP
         BEGIN
            l_sqlerrm := NULL;

            SELECT apps.ap_suppliers_int_s.NEXTVAL
              INTO ln_sup_int_id
              FROM DUAL;

/*+--------------------------------------------------------------------------------------+
-- =============== Inserting records into Supplier Interface Table ===========
+--------------------------------------------------------------------------------------+ */
            INSERT INTO apps.ap_suppliers_int
                        (vendor_interface_id, vendor_name,
                         vendor_name_alt, enabled_flag,
                         segment1, vendor_type_lookup_code,
                         status, invoice_currency_code,
                         payment_currency_code, terms_id,
                         party_orig_system_reference,
                         set_of_books_id,
                         hold_future_payments_flag,
                         employee_id, customer_num,
                         one_time_flag,
                         ship_to_location_id,
                         ship_to_location_code,
                         remittance_email,
                         auto_calculate_interest_flag,
--vat_code,
                         start_date_active, standard_industry_class,
                         terms_date_basis,
                         vat_registration_num,
                         tax_reporting_name,
                         match_option, last_update_date, last_updated_by,
                         creation_date, created_by
                        )
                 VALUES (ln_sup_int_id, load_data.vendor_name,
                         load_data.vendor_alternate_name, 'Y',
                         load_data.supplier_number, load_data.vendor_type,
                         'NEW', load_data.invoice_currency,
                         load_data.payment_currency, load_data.term_id,
                         load_data.supplier_number,
                         load_data.set_of_books_id,
                         load_data.hold_future_payments_flag,
                         load_data.employee_id, load_data.customer_number,
                         load_data.one_time_flag,
                         load_data.ship_to_location_id,
                         load_data.ship_to_location_code,
                         load_data.remittance_email,
                         load_data.auto_calculate_interest_flag,
--load_data.vat_code,
                         load_data.start_date_active, load_data.sic_code,
                         load_data.terms_date_basis,
                         load_data.vat_registration_number,
                         load_data.tax_reporting_name,
                         load_data.match_option, ld_sysdate, ln_user_id,
                         ld_sysdate, ln_user_id
                        );

             /*+--------------------------------------------------------------------------------------+
            -- ============== Inserting records into Supplier BANK Interface Table =======
            +--------------------------------------------------------------------------------------+ */
            SELECT iby_temp_ext_bank_accts_s.NEXTVAL
              INTO ln_iby_temp_ext
              FROM DUAL;

            INSERT INTO iby_temp_ext_bank_accts
                        (calling_app_unique_ref1, country_code, bank_id,
                         branch_id, status, temp_ext_bank_acct_id,
                         last_update_date, last_updated_by, creation_date,
                         created_by, object_version_number,
                         bank_account_name,
                         bank_account_num, currency_code
                        )             --,ext_payee_id, account_owner_party_id)
                 VALUES (ln_sup_int_id, 'GB', load_data.bank_id,
                         load_data.branch_id, 'NEW', ln_iby_temp_ext,
                         SYSDATE, 1215, SYSDATE,
                         1215, 1,
                         load_data.bank_account_name,
                         load_data.bank_account_number, 'GBP'
                        );
         EXCEPTION
            WHEN OTHERS
            THEN
               DBMS_OUTPUT.put_line ('Error Occured' || SQLERRM);
               l_sqlerrm := SQLERRM;

               --Update staging table with errors
               UPDATE xxbr_ap_supp_cv_stg
                  SET process_flag = 'E',
                      record_status = 'Error Inserting Iface table',
                      error_message = l_sqlerrm
                WHERE pk_supplier = load_data.pk_supplier;
         END;
      END LOOP;

      COMMIT;
   END xxbr_ap_supp_cv_load;

--//===============================================================
--This Procedure call Supplier Open Interface Import Program
--//================================================================
   PROCEDURE xxbr_ap_supp_cv_import
   IS
      CURSOR ap_supplier_details_cur
      IS
         SELECT *
           FROM xxbr_ap_supp_cv_stg
          WHERE process_flag = 'V';

----- Variable Declaration
      l_request_id                NUMBER;
      ln_user_id                  NUMBER;
      ln_resp_id                  NUMBER;
      ln_resp_appl_id             NUMBER;
      x_req_return_status         BOOLEAN;
      x_req_phase                 VARCHAR2 (30);
      x_req_status                VARCHAR2 (30);
      x_req_dev_phase             VARCHAR2 (30);
      x_req_dev_status            VARCHAR2 (30);
      x_req_message               VARCHAR2 (50);
      l_sqlerrm                   VARCHAR2 (2000);
      lv_vendor_id                NUMBER;
      lv_int_reject_lookup_code   VARCHAR2 (100);
      lv_int_record_count         NUMBER;
      ln_org_id                   NUMBER;
   BEGIN
      ln_user_id := TO_NUMBER (apps.fnd_profile.VALUE ('USER_ID'));
      ln_resp_id := TO_NUMBER (apps.fnd_profile.VALUE ('RESP_ID'));
      ln_resp_appl_id := TO_NUMBER (apps.fnd_profile.VALUE ('RESP_APPL_ID'));
      apps.fnd_global.apps_initialize (1215, 20640, 200);
--apps.fnd_global.apps_initialize(1318,50554,200);
--apps.fnd_global.apps_initialize(ln_user_id,ln_resp_id,ln_resp_appl_id);
--mo_global.init('AP');

      -------------- Getting the Operating Unit while submitting the request for Supplier --------
/*BEGIN
SELECT DISTINCT org_id
INTO ln_org_id
FROM XXBR_AP_SUPP_CV_STG
WHERE ROWNUM =1;

 EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Error Occured'||SQLERRM);
   --   l_sqlerrm:=SQLERRM;
      --Update staging table with errors

END;
*/

      -- fnd_request.set_org_id(org_id => ln_org_id);
      fnd_request.set_org_id (org_id => 107);
      COMMIT;
      l_request_id :=
         fnd_request.submit_request
                            (application      => 'SQLAP',
                             program          => 'APXSUIMP',
                             description      => 'Supplier Open Interface Import',
                             start_time       => SYSDATE,
                             sub_request      => FALSE,
                             argument1        => 'NEW',
                             argument2        => 10000,
                             argument3        => 'N',
                             argument4        => 'N',
                             argument5        => 'N'
                            );
      COMMIT;
      DBMS_OUTPUT.put_line (l_request_id);

      IF l_request_id = 0
      THEN
         DBMS_OUTPUT.put_line ('Request not submitted');
      ELSE
         x_req_return_status :=
            fnd_concurrent.wait_for_request (l_request_id,
                                             20,
                                             0,
                                             x_req_phase,
                                             x_req_status,
                                             x_req_dev_phase,
                                             x_req_dev_status,
                                             x_req_message
                                            );
      END IF;

      COMMIT;

      IF x_req_return_status = TRUE
      THEN
         DBMS_OUTPUT.put_line ('Request completed successfully');

--- Checking if there are any records in the interface table with the status 'NEW'
         BEGIN
            SELECT COUNT (vendor_interface_id)
              INTO lv_int_record_count
              FROM ap_suppliers_int
             WHERE status = 'NEW';
         EXCEPTION
            WHEN OTHERS
            THEN
               DBMS_OUTPUT.put_line ('Error Occured' || SQLERRM);
               l_sqlerrm := SQLERRM;
         END;

         IF lv_int_record_count > 0
         THEN
            l_request_id :=
               fnd_request.submit_request
                            (application      => 'SQLAP',
                             program          => 'APXSUIMP',
                             description      => 'Supplier Open Interface Import',
                             start_time       => SYSDATE,
                             sub_request      => FALSE,
                             argument1        => 'NEW',
                             argument2        => 10000,
                             argument3        => 'N',
                             argument4        => 'N',
                             argument5        => 'N'
                            );
            COMMIT;
            DBMS_OUTPUT.put_line (l_request_id);
            COMMIT;
         END IF;

         IF l_request_id = 0
         THEN
            DBMS_OUTPUT.put_line ('Request not submitted');
         ELSE
            x_req_return_status :=
               fnd_concurrent.wait_for_request (l_request_id,
                                                20,
                                                0,
                                                x_req_phase,
                                                x_req_status,
                                                x_req_dev_phase,
                                                x_req_dev_status,
                                                x_req_message
                                               );
         END IF;

         COMMIT;

         IF x_req_return_status = TRUE
         THEN
            DBMS_OUTPUT.put_line ('Request completed successfully');

            BEGIN
               FOR load_data IN ap_supplier_details_cur
               LOOP
                  BEGIN
                     l_sqlerrm := NULL;

                     BEGIN
                        SELECT vendor_id
                          INTO lv_vendor_id
                          FROM ap_suppliers
                         WHERE vendor_name = load_data.vendor_name;
                     EXCEPTION
                        WHEN OTHERS
                        THEN
                           DBMS_OUTPUT.put_line ('Error Occured' || SQLERRM);
                           l_sqlerrm := SQLERRM;
                     END;

                     IF lv_vendor_id IS NOT NULL
                     THEN
                        UPDATE xxbr_ap_supp_cv_stg
                           SET process_flag = 'P',
                               record_status = 'Successfully Processed'
                         WHERE pk_supplier = load_data.pk_supplier
                           AND process_flag = 'V';
                     ELSE
                        BEGIN
                           SELECT rej.reject_lookup_code
                             INTO lv_int_reject_lookup_code
                             FROM ap_supplier_int_rejections rej,
                                  ap_suppliers_int INT
                            WHERE rej.parent_id = INT.vendor_interface_id
                              AND parent_table = 'AP_SUPPLIERS_INT'
                              AND INT.segment1 = load_data.supplier_number
                              AND INT.status NOT IN ('NEW')
                              AND ROWNUM = 1;
-- Added this condition so that when multiple errors are coming it should not go in exception;
                        EXCEPTION
                           WHEN OTHERS
                           THEN
                              DBMS_OUTPUT.put_line ('Error Occured' || SQLERRM
                                                   );
                              l_sqlerrm := SQLERRM;
                        END;

                        IF lv_int_reject_lookup_code IS NOT NULL
                        THEN
                           UPDATE xxbr_ap_supp_cv_stg
                              SET process_flag = 'E',
                                  record_status = 'Error',
                                  ERROR_CODE = 'VE',
                                  error_message = lv_int_reject_lookup_code
                            WHERE pk_supplier = load_data.pk_supplier;
                        ELSE
                           UPDATE xxbr_ap_supp_cv_stg
                              SET process_flag = 'E',
                                  record_status = 'Error',
                                  ERROR_CODE = 'VE'
                            -- ,    error_message = l_sqlerrm
                           WHERE  pk_supplier = load_data.pk_supplier;
                        END IF;
                     END IF;
                  EXCEPTION
                     WHEN OTHERS
                     THEN
                        DBMS_OUTPUT.put_line ('Error Occured' || SQLERRM);
                        l_sqlerrm := SQLERRM;

                        --Update staging table with errors
                        UPDATE xxbr_ap_supp_cv_stg
                           SET process_flag = 'E',
                               ERROR_CODE = 'VE',
                               record_status = 'Error Updating Staging tbl',
                               error_message = l_sqlerrm
                         WHERE pk_supplier = load_data.pk_supplier;
                  END;
               END LOOP;
            END;

            COMMIT;
         END IF;
      END IF;
   END xxbr_ap_supp_cv_import;

--**************************************************************************
---------------------------------- SUPPLIER SITES ------------------------------------------------
--****************************************************************************
   PROCEDURE xxbr_ap_supp_site_cv_main (p_action IN VARCHAR2)
   IS
   BEGIN
      -- Call validate procedure to validate the Supplier Sites information
      IF p_action = 'validate'
      THEN
         xxbr_ap_supp_site_cv_validate;
      ELSE
         NULL;
      END IF;

      -- Call Insert procedure to load the data into Supplier Sites interface table
      IF p_action = 'load'
      THEN
         xxbr_ap_supp_site_cv_load;
      ELSE
         NULL;
      END IF;
   END xxbr_ap_supp_site_cv_main;

--//===========================================================
--// This is validation Procedure for Supplier Site Information
--//===========================================================
   PROCEDURE xxbr_ap_supp_site_cv_validate
   IS
      --Variable declaration
      l_sqlcode                NUMBER;
      l_sqlerrm                VARCHAR2 (1000);
      l_return_status          VARCHAR2 (1);
      l_i_err_msg              VARCHAR2 (1000);
      l_count                  NUMBER                                    := 0;
      ln_vendor_id             NUMBER;
      lv_supplier_type         VARCHAR2 (1000);
      lv_invoice_currency      VARCHAR2 (3);
      lv_payment_currency      VARCHAR2 (3);
      lv_terms_name            VARCHAR2 (100);
      ln_term_id               NUMBER;
      lv_operating_unit_name   VARCHAR2 (100);
      l_org_id                 NUMBER;
      lv_dup_site              NUMBER;
      lv_err_msg               VARCHAR2 (1000);
      lv_ship_to_location_id   NUMBER;
      lv_bill_to_location_id   NUMBER;
      lv_tolerance_id          NUMBER;
      lv_match_option          VARCHAR2 (100);
      l_final_error_msg        VARCHAR2 (100);
      lv_territory_code        VARCHAR2 (100);
      lv_error_code            VARCHAR2 (1000);
      -- Variable declaration for Bank Validation
      p_bank_country_code      hz_parties.country%TYPE;
      p_bank_name              hz_parties.party_name%TYPE;
      p_bank_number            hz_organization_profiles.bank_or_branch_number%TYPE;
      x_bank_id                NUMBER;
      x_end_date               DATE;
      ln_bank_id               NUMBER;
      -- Variable declaration for Bank Branch Validation
      x_branch_id              NUMBER;
      p_bank_id                NUMBER;
      p_branch_name            hz_parties.party_name%TYPE;
      p_branch_number          hz_organization_profiles.bank_or_branch_number%TYPE;
      -- Variable declaration for Bank Account Validation
      p_ext_bank_acct_rec      iby_ext_bankacct_pub.extbankacct_rec_type;
      l_dup_acct_id            NUMBER;
      l_dup_start_date         DATE;
      l_dup_end_date           DATE;
      x_response               iby_fndcpt_common_pub.result_rec_type;
      x_return_status          VARCHAR2 (1000);
      x_msg_count              NUMBER;
      x_msg_data               VARCHAR2 (1000);
      l_bank_account_num_len   NUMBER;

      CURSOR ap_supp_sites_cur
      IS
         SELECT *
           FROM xxbr_ap_supp_site_cv_stg
          WHERE process_flag = 'N' AND rec_type IN ('D', 'd');

      supplier_sites_tab       xxbr_ap_supp_site_cv_stg%ROWTYPE;
   BEGIN
      FOR pick_data_sites IN ap_supp_sites_cur
      LOOP
         l_return_status := 'Y';
         l_i_err_msg := NULL;
         l_final_error_msg := NULL;
         lv_error_code := NULL;

/*+--------------------------------------------------------------------------------------+
-- ================== Validation for Operating Unit exists or not PR15 =======
+--------------------------------------------------------------------------------------+ */
         BEGIN
            SELECT NAME, organization_id
              INTO lv_operating_unit_name, l_org_id
              FROM apps.hr_operating_units
             WHERE NAME = (TRIM (pick_data_sites.operating_unit_name));
         EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
               l_return_status := 'E';
               l_i_err_msg :=
                     l_i_err_msg
                  || ''
                  || pick_data_sites.operating_unit_name
                  || ' Operating Unit does not exists.Please check Operating Unit Name';
               lv_error_code := lv_error_code || '' || 'Error-PR15';
            WHEN OTHERS
            THEN
               l_return_status := 'E';
               l_sqlcode := SQLCODE;
               l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
         END;

         IF l_return_status = 'Y'
         THEN
            BEGIN
               UPDATE xxbr_ap_supp_site_cv_stg
                  SET process_flag = 'V',
                      record_status = 'Validated',
                      org_id = l_org_id
                WHERE pk_supplier_site = pick_data_sites.pk_supplier_site;
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_sqlcode := SQLCODE;
                  l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
            END;
         ELSE
            BEGIN
               l_final_error_msg := 'Y';

               UPDATE xxbr_ap_supp_site_cv_stg
                  SET error_message = l_i_err_msg,
                      process_flag = 'E',
                      record_status = 'Failure',
                      ERROR_CODE = lv_error_code
                WHERE pk_supplier_site = pick_data_sites.pk_supplier_site;
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_sqlcode := SQLCODE;
                  l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
            END;
         END IF;

/*+--------------------------------------------------------------------------------------+
-- ================== End of Validation for Operating Unit exists or not  ======
+--------------------------------------------------------------------------------------+ */

         /*+--------------------------------------------------------------------------------------+
-- ================== Validation for Supplier exists or not PR18 ============
+--------------------------------------------------------------------------------------+ */
         BEGIN
            SELECT vendor_id
              INTO ln_vendor_id
              FROM apps.ap_suppliers
             WHERE (UPPER (vendor_name) IN TRIM
                                             (UPPER
                                                  (pick_data_sites.vendor_name)
                                             )
                   );
         EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
               l_return_status := 'E';
               l_i_err_msg :=
                     l_i_err_msg
                  || ''
                  || pick_data_sites.vendor_name
                  || ' Vendor does not exists.Please create supplier first before creating Supplier Site';
               lv_error_code := lv_error_code || '' || 'Error-PR18';
            WHEN OTHERS
            THEN
               l_return_status := 'E';
               l_sqlcode := SQLCODE;
               l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
         END;

         IF l_return_status = 'Y'
         THEN
            BEGIN
               UPDATE xxbr_ap_supp_site_cv_stg
                  SET process_flag = 'V',
                      record_status = 'Validated',
                      vendor_id = ln_vendor_id
                WHERE pk_supplier_site = pick_data_sites.pk_supplier_site;
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_sqlcode := SQLCODE;
                  l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
            END;
         ELSE
            BEGIN
               l_final_error_msg := 'Y';

               UPDATE xxbr_ap_supp_site_cv_stg
                  SET error_message = l_i_err_msg,
                      process_flag = 'E',
                      record_status = 'Failure',
                      ERROR_CODE = lv_error_code
                WHERE pk_supplier_site = pick_data_sites.pk_supplier_site;
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_sqlcode := SQLCODE;
                  l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
            END;
         END IF;

/*+--------------------------------------------------------------------------------------+
-- ================== End of Validation for Supplier exists or not  ===========
+--------------------------------------------------------------------------------------+ */

         /*+--------------------------------------------------------------------------------------+
------------------- Start of Validation for Bank exists or not PR24------------------------
+--------------------------------------------------------------------------------------+*/
         BEGIN
            IF pick_data_sites.bank_name IS NOT NULL
                               -- AND pick_data_sites.bank_number IS NOT NULL
            THEN
               p_bank_country_code := 'GB';
-- This hardcoding needs to be removed and this field needs to be populated as per Supplier country
               p_bank_name := pick_data_sites.bank_name;
               p_bank_number := pick_data_sites.bank_number;
               ce_bank_pub.check_bank_exist (p_bank_country_code,
                                             p_bank_name,
                                             p_bank_number,
                                             x_bank_id,
                                             x_end_date
                                            );
               COMMIT;
            END IF;

            IF x_bank_id IS NULL
            THEN
               l_return_status := 'E';
               l_i_err_msg :=
                     l_i_err_msg
                  || '|'
                  || 'The Bank '
                  || pick_data_sites.bank_name
                  || ' does not exist in the system. Please create the bank first before creating ext bank account for vendor '
                  || pick_data_sites.vendor_name;
               lv_error_code := lv_error_code || '' || 'Error-PR24';
            END IF;
         EXCEPTION
            WHEN OTHERS
            THEN
               l_return_status := 'E';
               l_sqlcode := SQLCODE;
               l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
         END;

         IF l_return_status = 'Y'
         THEN
            BEGIN
               UPDATE xxbr_ap_supp_site_cv_stg
                  SET process_flag = 'V',
                      record_status = 'Validated',
                      bank_id = x_bank_id
                WHERE pk_supplier_site = pick_data_sites.pk_supplier_site;
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_sqlcode := SQLCODE;
                  l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
            END;
         ELSE
            BEGIN
               l_final_error_msg := 'Y';

               UPDATE xxbr_ap_supp_site_cv_stg
                  SET error_message = l_i_err_msg,
                      process_flag = 'E',
                      record_status = 'Failure',
                      ERROR_CODE = lv_error_code
                WHERE pk_supplier_site = pick_data_sites.pk_supplier_site;
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_sqlcode := SQLCODE;
                  l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
            END;
         END IF;

         COMMIT;

/*+--------------------------------------------------------------------------------------+
-- ================== End of Validation for Bank exists or not ==============
+--------------------------------------------------------------------------------------+ */

         /*+--------------------------------------------------------------------------------------+
------------------- Start of Validation for Bank Branch exists or not PR23----------------
+--------------------------------------------------------------------------------------+*/
         BEGIN
            IF pick_data_sites.bank_branch_name IS NOT NULL
            THEN
               p_bank_country_code := 'GB';
-- This hardcoding needs to be removed and this field needs to be populated as per Supplier country
               p_branch_name := pick_data_sites.bank_branch_name;
               p_branch_number := pick_data_sites.bank_number;

               BEGIN
                  SELECT party_id
                    INTO ln_bank_id
                    FROM hz_parties
                   WHERE party_name = pick_data_sites.bank_name;
               EXCEPTION
                  WHEN OTHERS
                  THEN
                     NULL;
               END;

--p_bank_id := 7169;
               p_bank_id := ln_bank_id;
               ce_bank_pub.check_branch_exist (p_bank_id,
                                               p_branch_name,
                                               p_branch_number,
                                               x_branch_id,
                                               x_end_date
                                              );
               COMMIT;
            END IF;

            IF x_branch_id IS NULL
            THEN
               l_return_status := 'E';
               l_i_err_msg :=
                     l_i_err_msg
                  || ''
                  || 'The branch '
                  || pick_data_sites.bank_branch_name
                  || ' does not exist for bank '
                  || pick_data_sites.bank_name
                  || ' in the system. Please create the bank branch first before creating ext bank account for vendor '
                  || pick_data_sites.vendor_name;
               lv_error_code := lv_error_code || '' || 'Error-PR23';
            END IF;
         EXCEPTION
            WHEN OTHERS
            THEN
               l_return_status := 'E';
               l_sqlcode := SQLCODE;
               l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
         END;

         IF l_return_status = 'Y'
         THEN
            BEGIN
               UPDATE xxbr_ap_supp_site_cv_stg
                  SET process_flag = 'V',
                      record_status = 'Validated',
                      branch_id = x_branch_id
                WHERE pk_supplier_site = pick_data_sites.pk_supplier_site;
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_sqlcode := SQLCODE;
                  l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
            END;
         ELSE
            BEGIN
               l_final_error_msg := 'Y';

               UPDATE xxbr_ap_supp_site_cv_stg
                  SET error_message = l_i_err_msg,
                      process_flag = 'E',
                      record_status = 'Failure',
                      ERROR_CODE = lv_error_code
                WHERE pk_supplier_site = pick_data_sites.pk_supplier_site;
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_sqlcode := SQLCODE;
                  l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
            END;
         END IF;

         COMMIT;

/*+--------------------------------------------------------------------------------------+
-- =========== End of Validation for Bank Branch exists or not ==============
+--------------------------------------------------------------------------------------+ */

         /*+--------------------------------------------------------------------------------------+
------------------- Start of Validation for Bank Account Number Length PR30-------
+--------------------------------------------------------------------------------------+*/
         BEGIN
            IF pick_data_sites.bank_account_number IS NOT NULL
            THEN
               SELECT LENGTH (pick_data_sites.bank_account_number)
                 INTO l_bank_account_num_len
                 FROM DUAL;

               COMMIT;
            END IF;

            IF l_bank_account_num_len > 8
            THEN
               l_return_status := 'E';
               l_i_err_msg :=
                     l_i_err_msg
                  || ''
                  || 'Please enter a maximum of 8 characters for the Account Number for Account '
                  || pick_data_sites.bank_account_number
                  || 'for bank '
                  || pick_data_sites.bank_name
                  || ' for vendor '
                  || pick_data_sites.vendor_name;
               lv_error_code := lv_error_code || '' || 'Error-PR30';
            END IF;
         EXCEPTION
            WHEN OTHERS
            THEN
               l_return_status := 'E';
               l_sqlcode := SQLCODE;
               l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
         END;

         IF l_return_status = 'Y'
         THEN
            BEGIN
               UPDATE xxbr_ap_supp_site_cv_stg
                  SET process_flag = 'V',
                      record_status = 'Validated',
                      bank_account_id = l_dup_acct_id
                WHERE pk_supplier_site = pick_data_sites.pk_supplier_site;
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_sqlcode := SQLCODE;
                  l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
            END;
         ELSE
            BEGIN
               l_final_error_msg := 'Y';

               UPDATE xxbr_ap_supp_site_cv_stg
                  SET error_message = l_i_err_msg,
                      process_flag = 'E',
                      record_status = 'Failure',
                      ERROR_CODE = lv_error_code
                WHERE pk_supplier_site = pick_data_sites.pk_supplier_site;
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_sqlcode := SQLCODE;
                  l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
            END;
         END IF;

         COMMIT;

/*+--------------------------------------------------------------------------------------+
-- =============== End of Validation for Bank Account Number Length =======
+--------------------------------------------------------------------------------------+ */

         /*+--------------------------------------------------------------------------------------+
------------------- Start of Validation for Bank Account should not exist PR28
+--------------------------------------------------------------------------------------+*/
         BEGIN
            IF     pick_data_sites.bank_account_name IS NOT NULL
               AND pick_data_sites.bank_account_number IS NOT NULL
            THEN
               p_ext_bank_acct_rec.bank_id := ln_bank_id;
               p_ext_bank_acct_rec.branch_id := x_branch_id;
               p_ext_bank_acct_rec.bank_account_name :=
                                            pick_data_sites.bank_account_name;
               p_ext_bank_acct_rec.bank_account_num :=
                                          pick_data_sites.bank_account_number;
               --p_ext_bank_acct_rec.acct_owner_party_id := 7162;
               p_ext_bank_acct_rec.currency := 'GBP';
               p_ext_bank_acct_rec.country_code := 'GB';
               iby_ext_bankacct_pub.check_ext_acct_exist
                                 (p_api_version            => '1.0',
                                  p_init_msg_list          => 'T',
                                  p_ext_bank_acct_rec      => p_ext_bank_acct_rec,
                                  x_acct_id                => l_dup_acct_id,
                                  x_start_date             => l_dup_start_date,
                                  x_end_date               => l_dup_end_date,
                                  x_return_status          => x_return_status,
                                  x_msg_count              => x_msg_count,
                                  x_msg_data               => x_msg_data,
                                  x_response               => x_response
                                 );
               COMMIT;
            END IF;

            IF l_dup_acct_id IS NOT NULL
            THEN
               l_return_status := 'E';
               l_i_err_msg :=
                     l_i_err_msg
                  || ''
                  || 'The Account '
                  || pick_data_sites.bank_account_number
                  || ' already exist for bank '
                  || pick_data_sites.bank_name
                  || ' in the system for vendor '
                  || pick_data_sites.vendor_name;
               lv_error_code := lv_error_code || '' || 'Error-PR28';
            END IF;
         EXCEPTION
            WHEN OTHERS
            THEN
               l_return_status := 'E';
               l_sqlcode := SQLCODE;
               l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
         END;

         IF l_return_status = 'Y'
         THEN
            BEGIN
               UPDATE xxbr_ap_supp_site_cv_stg
                  SET process_flag = 'V',
                      record_status = 'Validated'
                WHERE pk_supplier_site = pick_data_sites.pk_supplier_site;
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_sqlcode := SQLCODE;
                  l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
            END;
         ELSE
            BEGIN
               l_final_error_msg := 'Y';

               UPDATE xxbr_ap_supp_site_cv_stg
                  SET error_message = l_i_err_msg,
                      process_flag = 'E',
                      record_status = 'Failure',
                      ERROR_CODE = lv_error_code,
                      bank_account_id = l_dup_acct_id
                WHERE pk_supplier_site = pick_data_sites.pk_supplier_site;
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_sqlcode := SQLCODE;
                  l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
            END;
         END IF;

         COMMIT;

/*+--------------------------------------------------------------------------------------+
-- ============= End of Validation for Bank Account should not exist ==========
+--------------------------------------------------------------------------------------+ */

         /*+--------------------------------------------------------------------------------------+
-- ============ Validation for Supplier Site Address Country  PR14============
+--------------------------------------------------------------------------------------+ */
         BEGIN
            SELECT territory_code
              INTO lv_territory_code
              FROM apps.fnd_territories
             WHERE (UPPER (territory_code) IN TRIM
                                                (UPPER
                                                      (pick_data_sites.country)
                                                )
                   );
         EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
               l_return_status := 'E';
               l_i_err_msg :=
                     l_i_err_msg
                  || ''
                  || 'Country Code '
                  || pick_data_sites.country
                  || ' Not Valid for '
                  || pick_data_sites.vendor_name;
               lv_error_code := lv_error_code || '' || 'Error-PR14';
            WHEN OTHERS
            THEN
               l_return_status := 'E';
               l_sqlcode := SQLCODE;
               l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
         END;

         IF l_return_status = 'Y'
         THEN
            BEGIN
               UPDATE xxbr_ap_supp_site_cv_stg
                  SET process_flag = 'V',
                      record_status = 'Validated'
                WHERE pk_supplier_site = pick_data_sites.pk_supplier_site;
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_sqlcode := SQLCODE;
                  l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
            END;
         ELSE
            BEGIN
               l_final_error_msg := 'Y';

               UPDATE xxbr_ap_supp_site_cv_stg
                  SET error_message = l_i_err_msg,
                      process_flag = 'E',
                      record_status = 'Failure',
                      ERROR_CODE = lv_error_code
                WHERE pk_supplier_site = pick_data_sites.pk_supplier_site;
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_sqlcode := SQLCODE;
                  l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
            END;
         END IF;

/*+--------------------------------------------------------------------------------------+
-- ============= End of Validation for Supplier Site Address Country   =========
+--------------------------------------------------------------------------------------+ */

         /*+--------------------------------------------------------------------------------------+
-------------------Start of Validation for Ship to Location Code PR31-------------------
+--------------------------------------------------------------------------------------+*/
         BEGIN
            IF pick_data_sites.ship_to_location_code IS NOT NULL
            THEN
               SELECT DISTINCT location_id
                          INTO lv_ship_to_location_id
                          FROM hr_locations_all
                         WHERE ship_to_site_flag = 'Y'
                           AND location_code =
                                         pick_data_sites.ship_to_location_code;
            END IF;
         EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
               l_return_status := 'E';
               l_i_err_msg :=
                     l_i_err_msg
                  || ''
                  || 'Ship to Location Code '
                  || pick_data_sites.ship_to_location_code
                  || ' Not Valid for '
                  || pick_data_sites.vendor_name;
               lv_error_code := lv_error_code || '' || 'Error-PR31';
            WHEN OTHERS
            THEN
               l_return_status := 'E';
               l_sqlcode := SQLCODE;
               l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
         END;

         IF l_return_status = 'Y'
         THEN
            BEGIN
               UPDATE xxbr_ap_supp_site_cv_stg
                  SET process_flag = 'V',
                      record_status = 'Validated'
                -- ,ship_to_location_id    = lv_ship_to_location_id
               WHERE  pk_supplier_site = pick_data_sites.pk_supplier_site;
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_sqlcode := SQLCODE;
                  l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
            END;
         ELSE
            BEGIN
               l_final_error_msg := 'Y';

               UPDATE xxbr_ap_supp_site_cv_stg
                  SET error_message = l_i_err_msg,
                      process_flag = 'E',
                      record_status = 'Failure',
                      ERROR_CODE = lv_error_code
                WHERE pk_supplier_site = pick_data_sites.pk_supplier_site;
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_sqlcode := SQLCODE;
                  l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
            END;
         END IF;

/*+--------------------------------------------------------------------------------------+
-- ================ End of Validation for Ship to Location Code ===========
+--------------------------------------------------------------------------------------+ */

         /*+--------------------------------------------------------------------------------------+
-------------------Start of Validation for Bill to Location Code PR32-----------------------------
+--------------------------------------------------------------------------------------+*/
         BEGIN
            IF pick_data_sites.bill_to_location_code IS NOT NULL
            THEN
               SELECT DISTINCT location_id
                          INTO lv_bill_to_location_id
                          FROM hr_locations_all
                         WHERE bill_to_site_flag = 'Y'
                           AND location_code =
                                         pick_data_sites.bill_to_location_code;
            END IF;
         EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
               l_return_status := 'E';
               l_i_err_msg :=
                     l_i_err_msg
                  || ''
                  || 'Ship to Location Code '
                  || pick_data_sites.ship_to_location_code
                  || ' Not Valid for '
                  || pick_data_sites.vendor_name;
               lv_error_code := lv_error_code || '' || 'Error-PR32';
            WHEN OTHERS
            THEN
               l_return_status := 'E';
               l_sqlcode := SQLCODE;
               l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
         END;

         IF l_return_status = 'Y'
         THEN
            BEGIN
               UPDATE xxbr_ap_supp_site_cv_stg
                  SET process_flag = 'V',
                      record_status = 'Validated'
                -- ,ship_to_location_id    = lv_ship_to_location_id
               WHERE  pk_supplier_site = pick_data_sites.pk_supplier_site;
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_sqlcode := SQLCODE;
                  l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
            END;
         ELSE
            BEGIN
               l_final_error_msg := 'Y';

               UPDATE xxbr_ap_supp_site_cv_stg
                  SET error_message = l_i_err_msg,
                      process_flag = 'E',
                      record_status = 'Failure',
                      ERROR_CODE = lv_error_code
                WHERE pk_supplier_site = pick_data_sites.pk_supplier_site;
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_sqlcode := SQLCODE;
                  l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
            END;
         END IF;

/*+--------------------------------------------------------------------------------------+
-- ================== End of Validation for Bill to Location Code ===========
+--------------------------------------------------------------------------------------+ */

         /*+--------------------------------------------------------------------------------------+
------------------- Start of Validation for Match Option PR13-----------------------------
+--------------------------------------------------------------------------------------+*/
         BEGIN
            IF pick_data_sites.match_option IS NOT NULL
            THEN
               SELECT lookup_code
                 INTO lv_match_option
                 FROM fnd_lookups
                WHERE lookup_type = 'POS_INVOICE_MATCH_OPTION'
                  AND UPPER (lookup_code) =
                                          UPPER (pick_data_sites.match_option);
            END IF;
         EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
               l_return_status := 'E';
               l_i_err_msg :=
                     l_i_err_msg
                  || ''
                  || 'Match Option '
                  || pick_data_sites.match_option
                  || ' Not Valid for '
                  || pick_data_sites.vendor_name;
               lv_error_code := lv_error_code || '' || 'Error-PR13';
            WHEN OTHERS
            THEN
               l_return_status := 'E';
               l_sqlcode := SQLCODE;
               l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
         END;

         IF l_return_status = 'Y'
         THEN
            BEGIN
               UPDATE xxbr_ap_supp_site_cv_stg
                  SET process_flag = 'V',
                      record_status = 'Validated'
                WHERE pk_supplier_site = pick_data_sites.pk_supplier_site;
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_sqlcode := SQLCODE;
                  l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
            END;
         ELSE
            BEGIN
               l_final_error_msg := 'Y';

               UPDATE xxbr_ap_supp_site_cv_stg
                  SET error_message = l_i_err_msg,
                      process_flag = 'E',
                      record_status = 'Failure',
                      ERROR_CODE = lv_error_code
                WHERE pk_supplier_site = pick_data_sites.pk_supplier_site;
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_sqlcode := SQLCODE;
                  l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
            END;
         END IF;

/*+--------------------------------------------------------------------------------------+
-- ================== End of Validation for Match Option =================
+--------------------------------------------------------------------------------------+ */

         /*+--------------------------------------------------------------------------------------+
-------------------Start of Validation for Tolerance Name PR33-----------------------------
+--------------------------------------------------------------------------------------+*/
         BEGIN
            IF pick_data_sites.tolerance_name IS NOT NULL
            THEN
               SELECT DISTINCT tolerance_id
                          INTO lv_tolerance_id
                          FROM ap_tolerance_templates
                         WHERE tolerance_type = 'GOODS'
                           AND tolerance_name = pick_data_sites.tolerance_name;
            END IF;
         EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
               l_return_status := 'E';
               l_i_err_msg :=
                     l_i_err_msg
                  || ''
                  || 'Tolerance '
                  || pick_data_sites.tolerance_name
                  || ' Not Valid for '
                  || pick_data_sites.vendor_name;
               lv_error_code := lv_error_code || '' || 'Error-PR33';
            WHEN OTHERS
            THEN
               l_return_status := 'E';
               l_sqlcode := SQLCODE;
               l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
         END;

         IF l_return_status = 'Y'
         THEN
            BEGIN
               UPDATE xxbr_ap_supp_site_cv_stg
                  SET process_flag = 'V',
                      record_status = 'Validated'
                -- ,ship_to_location_id    = lv_ship_to_location_id
               WHERE  pk_supplier_site = pick_data_sites.pk_supplier_site;
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_sqlcode := SQLCODE;
                  l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
            END;
         ELSE
            BEGIN
               l_final_error_msg := 'Y';

               UPDATE xxbr_ap_supp_site_cv_stg
                  SET error_message = l_i_err_msg,
                      process_flag = 'E',
                      record_status = 'Failure',
                      ERROR_CODE = lv_error_code
                WHERE pk_supplier_site = pick_data_sites.pk_supplier_site;
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_sqlcode := SQLCODE;
                  l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
            END;
         END IF;

/*+--------------------------------------------------------------------------------------+
-- ================== End of Validation for Tolerance Name ================
+--------------------------------------------------------------------------------------+ */

         /*+--------------------------------------------------------------------------------------+
-------------------Start of Validation for Gapless Invoice Number Flag PR34-----------------------------
+--------------------------------------------------------------------------------------+*/
         BEGIN
            IF pick_data_sites.gapless_inv_number_flag IS NOT NULL
            THEN
               IF pick_data_sites.gapless_inv_number_flag NOT IN ('Y', 'N')
               THEN
                  l_return_status := 'E';
                  l_i_err_msg :=
                        l_i_err_msg
                     || ''
                     || 'One Time Flag '
                     || pick_data_sites.gapless_inv_number_flag
                     || ' Not Valid for '
                     || pick_data_sites.vendor_name
                     || '. Please enter Y or N.';
                  lv_error_code := lv_error_code || '' || 'Error-PR34';
               END IF;
            END IF;

            IF l_return_status = 'Y'
            THEN
               BEGIN
                  UPDATE xxbr_ap_supp_site_cv_stg
                     SET process_flag = 'V',
                         record_status = 'Validated'
                   WHERE pk_supplier_site = pick_data_sites.pk_supplier_site;
               EXCEPTION
                  WHEN OTHERS
                  THEN
                     l_sqlcode := SQLCODE;
                     l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
               END;
            ELSE
               BEGIN
                  l_final_error_msg := 'Y';

                  UPDATE xxbr_ap_supp_site_cv_stg
                     SET error_message = l_i_err_msg,
                         process_flag = 'E',
                         record_status = 'Failure',
                         ERROR_CODE = lv_error_code
                   WHERE pk_supplier_site = pick_data_sites.pk_supplier_site;
               EXCEPTION
                  WHEN OTHERS
                  THEN
                     l_sqlcode := SQLCODE;
                     l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
               END;
            END IF;
         END;

/*+--------------------------------------------------------------------------------------+
-- ================== End of Validation for Gapless Invoice Number Flag ======
+--------------------------------------------------------------------------------------+ */

         /*+--------------------------------------------------------------------------------------+
-------------------Start of Validation for Invoice Currency PR16---------------
+--------------------------------------------------------------------------------------+*/
         BEGIN
            IF pick_data_sites.invoice_currency IS NOT NULL
            THEN
               SELECT currency_code
                 INTO lv_invoice_currency
                 FROM apps.fnd_currencies
                WHERE currency_code = pick_data_sites.invoice_currency;
            END IF;
         EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
               l_return_status := 'E';
               l_i_err_msg :=
                     l_i_err_msg
                  || ''
                  || 'Invoice Currency '
                  || pick_data_sites.invoice_currency
                  || ' Not Valid for '
                  || pick_data_sites.vendor_name;
               lv_error_code := lv_error_code || '' || 'Error-PR16';
            WHEN OTHERS
            THEN
               l_return_status := 'E';
               l_sqlcode := SQLCODE;
               l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
         END;

         IF l_return_status = 'Y'
         THEN
            BEGIN
               UPDATE xxbr_ap_supp_site_cv_stg
                  SET process_flag = 'V',
                      record_status = 'Validated'
                WHERE pk_supplier_site = pick_data_sites.pk_supplier_site;
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_sqlcode := SQLCODE;
                  l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
            END;
         ELSE
            BEGIN
               l_final_error_msg := 'Y';

               UPDATE xxbr_ap_supp_site_cv_stg
                  SET error_message = l_i_err_msg,
                      process_flag = 'E',
                      record_status = 'Failure',
                      ERROR_CODE = lv_error_code
                WHERE pk_supplier_site = pick_data_sites.pk_supplier_site;
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_sqlcode := SQLCODE;
                  l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
            END;
         END IF;

/*+--------------------------------------------------------------------------------------+
-- ======== End of Validation for Invoice Currency =======================
+--------------------------------------------------------------------------------------+ */

         /*+--------------------------------------------------------------------------------------+
-------------------Start of Validation for Payment Currency PR17---------------
+--------------------------------------------------------------------------------------+*/
         BEGIN
            IF pick_data_sites.payment_currency IS NOT NULL
            THEN
               SELECT currency_code
                 INTO lv_payment_currency
                 FROM apps.fnd_currencies
                WHERE currency_code = pick_data_sites.payment_currency;
            END IF;
         EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
               l_return_status := 'E';
               l_i_err_msg :=
                     l_i_err_msg
                  || ''
                  || 'Payment Currency '
                  || pick_data_sites.payment_currency
                  || ' Not Valid for '
                  || pick_data_sites.vendor_name;
               lv_error_code := lv_error_code || '' || 'Error-PR17';
            WHEN OTHERS
            THEN
               l_return_status := 'E';
               l_sqlcode := SQLCODE;
               l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
         END;

         IF l_return_status = 'Y'
         THEN
            BEGIN
               UPDATE xxbr_ap_supp_site_cv_stg
                  SET process_flag = 'V',
                      record_status = 'Validated'
                WHERE pk_supplier_site = pick_data_sites.pk_supplier_site;
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_sqlcode := SQLCODE;
                  l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
            END;
         ELSE
            BEGIN
               l_final_error_msg := 'Y';

               UPDATE xxbr_ap_supp_site_cv_stg
                  SET error_message = l_i_err_msg,
                      process_flag = 'E',
                      record_status = 'Failure',
                      ERROR_CODE = lv_error_code
                WHERE pk_supplier_site = pick_data_sites.pk_supplier_site;
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_sqlcode := SQLCODE;
                  l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
            END;
         END IF;

/*+--------------------------------------------------------------------------------------+
-- ========= End of Validation for Payment Currency ======================
+--------------------------------------------------------------------------------------+ */

         /*+--------------------------------------------------------------------------------------+
-------------------Start of Validation for Terms Data Basis PR10-----------------------------
+--------------------------------------------------------------------------------------+*/
         BEGIN
            IF pick_data_sites.terms_date_basis IS NOT NULL
            THEN
               SELECT lookup_code
                 INTO lv_terms_date_basis
                 FROM fnd_lookup_values
                WHERE lookup_type = 'TERMS DATE BASIS'
                  AND lookup_code = pick_data_sites.terms_date_basis;
            END IF;
         EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
               l_return_status := 'E';
               l_i_err_msg :=
                     l_i_err_msg
                  || ''
                  || 'Terms Data Basis '
                  || pick_data_sites.terms_date_basis
                  || ' Not Valid for '
                  || pick_data_sites.vendor_name;
               lv_error_code := lv_error_code || '' || 'Error-PR10';
            WHEN OTHERS
            THEN
               l_return_status := 'E';
               l_sqlcode := SQLCODE;
               l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
         END;

         IF l_return_status = 'Y'
         THEN
            BEGIN
               UPDATE xxbr_ap_supp_site_cv_stg
                  SET process_flag = 'V',
                      record_status = 'Validated'
                WHERE pk_supplier_site = pick_data_sites.pk_supplier_site;
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_sqlcode := SQLCODE;
                  l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
            END;
         ELSE
            BEGIN
               l_final_error_msg := 'Y';

               UPDATE xxbr_ap_supp_site_cv_stg
                  SET error_message = l_i_err_msg,
                      process_flag = 'E',
                      record_status = 'Failure',
                      ERROR_CODE = lv_error_code
                WHERE pk_supplier_site = pick_data_sites.pk_supplier_site;
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_sqlcode := SQLCODE;
                  l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
            END;
         END IF;

/*+--------------------------------------------------------------------------------------+
-- ================== End of Validation for Terms Data Basis ===============
+--------------------------------------------------------------------------------------+ */

         /*+--------------------------------------------------------------------------------------+
------------------- Start of Validation for Payment Terms PR08---------------
+--------------------------------------------------------------------------------------+*/
         BEGIN
            IF pick_data_sites.terms_name IS NOT NULL
            THEN
               SELECT NAME, term_id
                 INTO lv_terms_name, ln_term_id
                 FROM apps.ap_terms_tl
                WHERE UPPER (NAME) = UPPER (pick_data_sites.terms_name)
                  AND LANGUAGE = USERENV ('LANG');
            END IF;
         EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
               l_return_status := 'E';
               l_i_err_msg :=
                     l_i_err_msg
                  || ''
                  || 'Payment Term '
                  || pick_data_sites.terms_name
                  || ' Not Valid for '
                  || pick_data_sites.vendor_name;
               lv_error_code := lv_error_code || '' || 'Error-PR08';
            WHEN OTHERS
            THEN
               l_return_status := 'E';
               l_sqlcode := SQLCODE;
               l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
         END;

         IF l_return_status = 'Y'
         THEN
            BEGIN
               UPDATE xxbr_ap_supp_site_cv_stg
                  SET process_flag = 'V',
                      record_status = 'Validated',
                      term_id = ln_term_id
                WHERE pk_supplier_site = pick_data_sites.pk_supplier_site;
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_sqlcode := SQLCODE;
                  l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
            END;
         ELSE
            BEGIN
               l_final_error_msg := 'Y';

               UPDATE xxbr_ap_supp_site_cv_stg
                  SET error_message = l_i_err_msg,
                      process_flag = 'E',
                      record_status = 'Failure',
                      ERROR_CODE = lv_error_code
                WHERE pk_supplier_site = pick_data_sites.pk_supplier_site;
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_sqlcode := SQLCODE;
                  l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
            END;
         END IF;

/*+--------------------------------------------------+
-- ==== End of Validation for Payment Terms ============
+------------------------------------------------------+ */

         /*+-----------------------------------------------------+
-- ========= Validation for Supplier site PR07 ===========
+--------------------------------------------------------+ */
         BEGIN
            IF ln_vendor_id IS NOT NULL
            THEN
               SELECT COUNT (*)
                 INTO lv_dup_site
                 FROM apps.ap_supplier_sites_all
                WHERE TRIM (UPPER (vendor_site_code)) =
                               TRIM (UPPER (pick_data_sites.vendor_site_code))
                  AND vendor_id = ln_vendor_id
                  AND org_id = l_org_id;
            END IF;

            IF lv_dup_site > 0
            THEN
               l_return_status := 'E';
               l_i_err_msg :=
                     l_i_err_msg
                  || ''
                  || 'Vendor Site  '
                  || pick_data_sites.vendor_site_code
                  || ' already exists for '
                  || pick_data_sites.vendor_name;
               lv_error_code := lv_error_code || '' || 'Error-PR07';
            END IF;
         EXCEPTION
            WHEN OTHERS
            THEN
               l_return_status := 'E';
               l_sqlcode := SQLCODE;
               l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
         END;

         IF l_return_status = 'Y'
         THEN
            BEGIN
               UPDATE xxbr_ap_supp_site_cv_stg
                  SET process_flag = 'V',
                      record_status = 'Validated'
                WHERE pk_supplier_site = pick_data_sites.pk_supplier_site;
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_sqlcode := SQLCODE;
                  l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
            END;
         ELSE
            BEGIN
               l_final_error_msg := 'Y';

               UPDATE xxbr_ap_supp_site_cv_stg
                  SET error_message = l_i_err_msg,
                      process_flag = 'E',
                      record_status = 'Failure',
                      ERROR_CODE = lv_error_code
                WHERE pk_supplier_site = pick_data_sites.pk_supplier_site;
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_sqlcode := SQLCODE;
                  l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
            END;
         END IF;

-----------------------------------------------------------------------------------------------------------------
         IF l_final_error_msg IS NULL
         THEN
            BEGIN
               UPDATE xxbr_ap_supp_site_cv_stg
                  SET process_flag = 'V',
                      record_status = 'Validated'
                WHERE pk_supplier_site = pick_data_sites.pk_supplier_site;
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_sqlcode := SQLCODE;
                  l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
            END;
         ELSE
            BEGIN
               UPDATE xxbr_ap_supp_site_cv_stg
                  SET                  --  error_message       =  l_i_err_msg
                     process_flag = 'E',
                     record_status = 'Failure'
                --  ,error_code         = 'VE'
               WHERE  pk_supplier_site = pick_data_sites.pk_supplier_site;
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_sqlcode := SQLCODE;
                  l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
            END;
         END IF;
-----------------------------------------------------------------------------------------------------------------

      /*+--------------------------------------------------------------------------------------+
-- ============= End of Validation for Supplier site  ======================
+--------------------------------------------------------------------------------------+ */
      END LOOP;
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line ('NO Data Found');
         COMMIT;
   END xxbr_ap_supp_site_cv_validate;

/*+--------------------------------------------------------------------------------------+
-- =========== End of Validation for Payment Terms ======================
+--------------------------------------------------------------------------------------+ */

   --//===============================================================
--//This Procedure insert into Supplier Sites interface tables
--//===============================================================
   PROCEDURE xxbr_ap_supp_site_cv_load
   IS
      CURSOR ap_supplier_details_cur
      IS
         SELECT *
           FROM xxbr_ap_supp_site_cv_stg
          WHERE process_flag = 'V';

      -- Variable Declaration
      l_sqlerrm            VARCHAR2 (1000);
      l_i_err_msg          VARCHAR2 (1000);
      ln_sup_int_id        NUMBER;
      ln_sup_site_int_id   NUMBER;
      ln_user_id           NUMBER
                            := TO_NUMBER (apps.fnd_profile.VALUE ('USER_ID'));
      ld_sysdate           DATE            := SYSDATE;
--ln_resp_id := TO_NUMBER(apps.fnd_profile.value('RESP_ID'));
--ln_resp_appl_id :=TO_NUMBER(apps.fnd_profile.value('RESP_APPL_ID'));
      ln_iby_temp_ext      NUMBER;
   BEGIN
      FOR load_data IN ap_supplier_details_cur
      LOOP
         BEGIN
            l_sqlerrm := NULL;

            SELECT apps.ap_supplier_sites_int_s.NEXTVAL
              INTO ln_sup_site_int_id
              FROM DUAL;

            INSERT INTO apps.ap_supplier_sites_int
                        (status,
--vendor_interface_id,
                                vendor_id, vendor_site_interface_id,
                         vendor_site_code, address_line1,
                         address_line2, address_line3,
                         address_line4, city,
                         state, country, zip,
                         org_id, ship_to_location_code,
                         bill_to_location_code,
                         retainage_rate,
                         gapless_inv_num_flag,
                         invoice_currency_code,
                         payment_currency_code,
                         tolerance_name, match_option,
                         terms_name, terms_date_basis,
                         pay_site_flag,
                         primary_pay_site_flag, last_update_date,
                         last_updated_by, creation_date, created_by
                        )
                 VALUES ('NEW',
--10001,
                               load_data.vendor_id, ln_sup_site_int_id,
                         load_data.vendor_site_code, load_data.address_line1,
                         load_data.address_line2, load_data.address_line3,
                         load_data.address_line4, load_data.city,
                         load_data.county, load_data.country, load_data.zip,
                         load_data.org_id, load_data.ship_to_location_code,
                         load_data.bill_to_location_code,
                         load_data.retainage_rate,
                         load_data.gapless_inv_number_flag,
                         load_data.invoice_currency,
                         load_data.payment_currency,
                         load_data.tolerance_name, load_data.match_option,
                         load_data.terms_name, load_data.terms_date_basis,
                         load_data.pay_site_flag,
                         load_data.primary_pay_site_flag, ld_sysdate,
                         1215, ld_sysdate, 1215
                        );

             /*+--------------------------------------------------------------------------------------+
            --============ Inserting records into Supplier Site Bank Interface Table ==========
            +--------------------------------------------------------------------------------------+ */
            SELECT iby_temp_ext_bank_accts_s.NEXTVAL
              INTO ln_iby_temp_ext
              FROM DUAL;

            INSERT INTO iby_temp_ext_bank_accts
                        (calling_app_unique_ref2, country_code, bank_id,
                         branch_id, status, temp_ext_bank_acct_id,
                         last_update_date, last_updated_by, creation_date,
                         created_by, object_version_number,
                         bank_account_name,
                         bank_account_num, currency_code
                        )             --,ext_payee_id, account_owner_party_id)
                 VALUES (ln_sup_site_int_id, 'GB', load_data.bank_id,
                         load_data.branch_id, 'NEW', ln_iby_temp_ext,
                         SYSDATE, 1215, SYSDATE,
                         1215, 1,
                         load_data.bank_account_name,
                         load_data.bank_account_number, 'GBP'
                        );
         EXCEPTION
            WHEN OTHERS
            THEN
               DBMS_OUTPUT.put_line ('Error Occured' || SQLERRM);
               l_sqlerrm := SQLERRM;

               --Update staging table with errors
               UPDATE xxbr_ap_supp_site_cv_stg
                  SET process_flag = 'E',
                      record_status = 'Error Inserting Iface table',
                      error_message = l_sqlerrm
                WHERE pk_supplier_site = load_data.pk_supplier_site;
         END;
      END LOOP;

      COMMIT;
   END xxbr_ap_supp_site_cv_load;

--//==================================================================
--// This Procedure call Supplier Sites Open Interface Import Program
--//==================================================================
   PROCEDURE xxbr_ap_supp_site_cv_import
   IS
      CURSOR ap_supplier_details_cur
      IS
         SELECT *
           FROM xxbr_ap_supp_site_cv_stg
          WHERE process_flag = 'V';

----- Variable Declaration
      l_request_id                NUMBER;
      ln_user_id                  NUMBER;
      ln_resp_id                  NUMBER;
      ln_resp_appl_id             NUMBER;
      x_req_return_status         BOOLEAN;
      x_req_phase                 VARCHAR2 (30);
      x_req_status                VARCHAR2 (30);
      x_req_dev_phase             VARCHAR2 (30);
      x_req_dev_status            VARCHAR2 (30);
      x_req_message               VARCHAR2 (50);
      l_sqlerrm                   VARCHAR2 (2000);
      lv_vendor_site_id           NUMBER;
      lv_int_reject_lookup_code   VARCHAR2 (100);
      lv_int_record_count         NUMBER;
      ln_org_id                   NUMBER;
   BEGIN
      ln_user_id := TO_NUMBER (apps.fnd_profile.VALUE ('USER_ID'));
      ln_resp_id := TO_NUMBER (apps.fnd_profile.VALUE ('RESP_ID'));
      ln_resp_appl_id := TO_NUMBER (apps.fnd_profile.VALUE ('RESP_APPL_ID'));
      apps.fnd_global.apps_initialize (1215, 20640, 200);
--apps.fnd_global.apps_initialize(1318,50554,200);
--apps.fnd_global.apps_initialize(ln_user_id,ln_resp_id,ln_resp_appl_id);
--mo_global.init('AP');
      COMMIT;

---------------------- Getting the Operating Unit while submitting the request for Supplier --------
      BEGIN
         SELECT DISTINCT org_id
                    INTO ln_org_id
                    FROM xxbr_ap_supp_site_cv_stg
                   WHERE ROWNUM = 1 AND org_id IS NOT NULL;
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line ('Error Occured in getting org id' || SQLERRM
                                 );
      --   l_sqlerrm:=SQLERRM;
         --Update staging table with errors
      END;

      fnd_request.set_org_id (org_id => ln_org_id);
      COMMIT;
      l_request_id :=
         fnd_request.submit_request
                       (application      => 'SQLAP',
                        program          => 'APXSSIMP',
                        description      => 'Supplier Sites Open Interface Import',
                        start_time       => SYSDATE,
                        sub_request      => FALSE,
                        argument1        => 'NEW',
                        argument2        => 10000,
                        argument3        => 'N',
                        argument4        => 'N',
                        argument5        => 'N'
                       );
      DBMS_OUTPUT.put_line (l_request_id);
      COMMIT;

      IF l_request_id = 0
      THEN
         DBMS_OUTPUT.put_line ('Request not submitted');
      ELSE
         x_req_return_status :=
            fnd_concurrent.wait_for_request (l_request_id,
                                             20,
                                             0,
                                             x_req_phase,
                                             x_req_status,
                                             x_req_dev_phase,
                                             x_req_dev_status,
                                             x_req_message
                                            );
      END IF;

      COMMIT;

      IF x_req_return_status = TRUE
      THEN
         DBMS_OUTPUT.put_line ('Request completed successfully');

--==============================================================
--- Checking if there are any records in the interface table with the status 'NEW'
         BEGIN
            SELECT COUNT (vendor_interface_id)
              INTO lv_int_record_count
              FROM ap_supplier_sites_int
             WHERE status = 'NEW';
         EXCEPTION
            WHEN OTHERS
            THEN
               DBMS_OUTPUT.put_line ('Error Occured' || SQLERRM);
               l_sqlerrm := SQLERRM;
         END;

         IF lv_int_record_count > 0
         THEN
            l_request_id :=
               apps.fnd_request.submit_request ('SQLAP',
                                                'APXSSIMP',
                                                '',
                                                '',
                                                FALSE,
                                                'ALL',
                                                '1000',
                                                'N',
                                                'N',
                                                'N'
                                               );
            DBMS_OUTPUT.put_line (l_request_id);
            DBMS_OUTPUT.put_line (l_request_id);
            COMMIT;
         END IF;

         IF l_request_id = 0
         THEN
            DBMS_OUTPUT.put_line ('Request not submitted');
         ELSE
            x_req_return_status :=
               fnd_concurrent.wait_for_request (l_request_id,
                                                20,
                                                0,
                                                x_req_phase,
                                                x_req_status,
                                                x_req_dev_phase,
                                                x_req_dev_status,
                                                x_req_message
                                               );
         END IF;

         COMMIT;

         IF x_req_return_status = TRUE
         THEN
            DBMS_OUTPUT.put_line ('Request completed successfully');
--===============================================================
            COMMIT;

            BEGIN
               FOR load_data IN ap_supplier_details_cur
               LOOP
                  BEGIN
                     l_sqlerrm := NULL;

                     BEGIN
                        SELECT vendor_site_id
                          INTO lv_vendor_site_id
                          FROM ap_supplier_sites_all
                         WHERE vendor_id =
                                  (SELECT vendor_id
                                     FROM ap_suppliers
                                    WHERE vendor_name = load_data.vendor_name)
                           AND vendor_site_code = load_data.vendor_site_code
                           AND org_id = load_data.org_id;
                     EXCEPTION
                        WHEN OTHERS
                        THEN
                           DBMS_OUTPUT.put_line
                              (   'Error Occured in checking if site exists in system'
                               || SQLERRM
                              );
                           l_sqlerrm := SQLERRM;
                     END;

                     DBMS_OUTPUT.put_line (load_data.vendor_name);
                     DBMS_OUTPUT.put_line (load_data.vendor_site_code);
                     DBMS_OUTPUT.put_line (lv_vendor_site_id);

                     IF lv_vendor_site_id IS NOT NULL
                     THEN
                        UPDATE xxbr_ap_supp_site_cv_stg
                           SET process_flag = 'P',
                               record_status = 'Successfully Processed'
                         WHERE pk_supplier_site = load_data.pk_supplier_site;
                     ELSE
                        BEGIN
                           SELECT rej.reject_lookup_code
                             INTO lv_int_reject_lookup_code
                             FROM ap_supplier_int_rejections rej,
                                  ap_supplier_sites_int INT
                            WHERE rej.parent_id = INT.vendor_site_interface_id
                              AND parent_table = 'AP_SUPPLIER_SITES_INT'
                              AND INT.vendor_id =
                                     (SELECT DISTINCT vendor_id
                                                 FROM ap_suppliers
                                                WHERE vendor_name =
                                                         load_data.vendor_name)
                              AND INT.vendor_site_code =
                                                    load_data.vendor_site_code
                              AND INT.status NOT IN ('NEW');
                        EXCEPTION
                           WHEN OTHERS
                           THEN
                              DBMS_OUTPUT.put_line ('Error Occured' || SQLERRM
                                                   );
                              l_sqlerrm := SQLERRM;
                        END;

                        IF lv_int_reject_lookup_code IS NOT NULL
                        THEN
                           UPDATE xxbr_ap_supp_site_cv_stg
                              SET process_flag = 'E',
                                  ERROR_CODE = 'VE',
                                  record_status = 'Error',
                                  error_message = lv_int_reject_lookup_code
                            WHERE pk_supplier_site =
                                                    load_data.pk_supplier_site;
                        ELSE
                           UPDATE xxbr_ap_supp_site_cv_stg
                              SET process_flag = 'E',
                                  record_status = 'Error',
                                  ERROR_CODE = 'VE'
                            -- ,    error_message = l_sqlerrm
                           WHERE  pk_supplier_site =
                                                    load_data.pk_supplier_site;
                        END IF;
                     END IF;
                  EXCEPTION
                     WHEN OTHERS
                     THEN
                        DBMS_OUTPUT.put_line ('Error Occured' || SQLERRM);
                        l_sqlerrm := SQLERRM;

                        --Update staging table with errors
                        UPDATE xxbr_ap_supp_site_cv_stg
                           SET process_flag = 'E',
                               ERROR_CODE = 'VE',
                               record_status = 'Error Update Staging tbl',
                               error_message = l_sqlerrm
                         WHERE pk_supplier_site = load_data.pk_supplier_site;
                  END;
               END LOOP;
            END;

            COMMIT;
         END IF;
      END IF;
   END xxbr_ap_supp_site_cv_import;

--***************************************************************************
------------------------ SUPPLIER CONTACTS ----------------------------------------
--***************************************************************************
   PROCEDURE xxbr_ap_supp_cont_cv_main (p_action IN VARCHAR2)
   IS
   BEGIN
      -- Call validate procedure to validate the Supplier Sites information
      IF p_action = 'validate'
      THEN
         xxbr_ap_supp_cont_cv_validate;
      ELSE
         NULL;
      END IF;

      -- Call Insert procedure to load the data into Supplier Sites interface table
      IF p_action = 'load'
      THEN
         xxbr_ap_supp_cont_cv_load;
      ELSE
         NULL;
      END IF;
   END xxbr_ap_supp_cont_cv_main;

--//==========================================================
--// This is validation Procedure for Supplier Contacts Information
--//==========================================================
   PROCEDURE xxbr_ap_supp_cont_cv_validate
   IS
      --Variable declaration
      l_i_accepted             NUMBER                             := 0;
      l_i_rejected             NUMBER                             := 0;
      l_ic_accepted            NUMBER                             := 0;
      l_ic_rejected            NUMBER                             := 0;
      l_sqlcode                NUMBER;
      l_sqlerrm                VARCHAR2 (1000);
      l_return_status          VARCHAR2 (1);
      l_i_err_msg              VARCHAR2 (1000);
      l_count                  NUMBER                             := 0;
      ln_vendor_id             NUMBER;
      lv_vendor_site_id        NUMBER;
      lv_operating_unit_name   VARCHAR2 (100);
      l_org_id                 NUMBER;
      lv_err_msg               VARCHAR2 (1000);
      l_final_error_msg        VARCHAR2 (100);
      lv_vendor_contact_id     NUMBER;

      CURSOR ap_supp_contacts_cur
      IS
         SELECT *
           FROM xxbr_ap_supp_cont_cv_stg
          WHERE process_flag = 'N' AND rec_type IN ('D', 'd');

      supplier_contacts_tab    xxbr_ap_supp_cont_cv_stg%ROWTYPE;
   BEGIN
      FOR pick_data_contacts IN ap_supp_contacts_cur
      LOOP
         l_return_status := 'Y';
         l_i_err_msg := NULL;
         l_final_error_msg := NULL;

/*+--------------------------------------------------------------------------------------+
-- ================== Validation for Operating Unit exists or not  ===========
+--------------------------------------------------------------------------------------+ */
         BEGIN
            SELECT NAME, organization_id
              INTO lv_operating_unit_name, l_org_id
              FROM apps.hr_operating_units
             WHERE NAME = (TRIM (pick_data_contacts.operating_unit_name));
         EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
               l_return_status := 'E';
               l_i_err_msg :=
                     l_i_err_msg
                  || ' | '
                  || pick_data_contacts.operating_unit_name
                  || ' Operating Unit does not exist.Please check Operating Unit Name';
            WHEN OTHERS
            THEN
               l_return_status := 'E';
               l_sqlcode := SQLCODE;
               l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
         END;

         IF l_return_status = 'Y'
         THEN
            BEGIN
               UPDATE xxbr_ap_supp_cont_cv_stg
                  SET process_flag = 'V',
                      record_status = 'Validated',
                      org_id = l_org_id
                WHERE pk_supplier_cont_number =
                                    pick_data_contacts.pk_supplier_cont_number;
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_sqlcode := SQLCODE;
                  l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
            END;
         ELSE
            BEGIN
               l_final_error_msg := 'Y';

               UPDATE xxbr_ap_supp_cont_cv_stg
                  SET error_message = l_i_err_msg,
                      process_flag = 'E',
                      record_status = 'Failure',
                      ERROR_CODE = 'VE'
                WHERE pk_supplier_cont_number =
                                    pick_data_contacts.pk_supplier_cont_number;
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_sqlcode := SQLCODE;
                  l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
            END;
         END IF;

/*+--------------------------------------------------------------------------------------+
--============= End of Validation for Operating Unit exists or not  ========
+--------------------------------------------------------------------------------------+ */

         /*+--------------------------------------------------------------------------------------+
         -- ================== Validation for Supplier exists or not  ================
         +--------------------------------------------------------------------------------------+ */
         BEGIN
            SELECT vendor_id
              INTO ln_vendor_id
              FROM apps.ap_suppliers
             WHERE (UPPER (vendor_name) IN TRIM
                                             (UPPER
                                                 (pick_data_contacts.vendor_name
                                                 )
                                             )
                   );
         EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
               l_return_status := 'E';
               l_i_err_msg :=
                     l_i_err_msg
                  || ' | '
                  || pick_data_contacts.vendor_name
                  || ' Vendor does not exist.Please create supplier first before creating Supplier Contact';
            WHEN OTHERS
            THEN
               l_return_status := 'E';
               l_sqlcode := SQLCODE;
               l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
         END;

         IF l_return_status = 'Y'
         THEN
            BEGIN
               UPDATE xxbr_ap_supp_cont_cv_stg
                  SET process_flag = 'V',
                      record_status = 'Validated',
                      vendor_id = ln_vendor_id
                WHERE pk_supplier_cont_number =
                                    pick_data_contacts.pk_supplier_cont_number;
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_sqlcode := SQLCODE;
                  l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
            END;
         ELSE
            BEGIN
               l_final_error_msg := 'Y';

               UPDATE xxbr_ap_supp_cont_cv_stg
                  SET error_message = l_i_err_msg,
                      process_flag = 'E',
                      record_status = 'Failure',
                      ERROR_CODE = 'VE'
                WHERE pk_supplier_cont_number =
                                    pick_data_contacts.pk_supplier_cont_number;
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_sqlcode := SQLCODE;
                  l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
            END;
         END IF;

/*+--------------------------------------------------------------------------------------+
-- ============ End of Validation for Supplier exists or not  ================
+--------------------------------------------------------------------------------------+ */

         /*+--------------------------------------------------------------------------------------+
-- ================== Validation for Supplier site   ======================
+--------------------------------------------------------------------------------------+ */
         BEGIN
            IF ln_vendor_id IS NOT NULL
            THEN
               SELECT vendor_site_id
                 INTO lv_vendor_site_id
                 FROM apps.ap_supplier_sites_all
                WHERE TRIM (UPPER (vendor_site_code)) =
                            TRIM (UPPER (pick_data_contacts.vendor_site_code))
                  AND vendor_id = ln_vendor_id
                  AND org_id = l_org_id;
            END IF;
         EXCEPTION
            WHEN OTHERS
            THEN
               l_return_status := 'E';
               l_i_err_msg :=
                     l_i_err_msg
                  || ' | '
                  || 'Vendor Site  '
                  || pick_data_contacts.vendor_site_code
                  || ' does not exist for '
                  || pick_data_contacts.vendor_name;
         END;

         IF l_return_status = 'Y'
         THEN
            BEGIN
               UPDATE xxbr_ap_supp_cont_cv_stg
                  SET process_flag = 'V',
                      record_status = 'Validated',
                      vendor_site_id = lv_vendor_site_id
                WHERE pk_supplier_cont_number =
                                    pick_data_contacts.pk_supplier_cont_number;
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_sqlcode := SQLCODE;
                  l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
            END;
         ELSE
            BEGIN
               l_final_error_msg := 'Y';

               UPDATE xxbr_ap_supp_cont_cv_stg
                  SET error_message = l_i_err_msg,
                      process_flag = 'E',
                      record_status = 'Failure',
                      ERROR_CODE = 'VE'
                WHERE pk_supplier_cont_number =
                                    pick_data_contacts.pk_supplier_cont_number;
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_sqlcode := SQLCODE;
                  l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
            END;
         END IF;

/*+--------------------------------------------------------------------------------------+
-- ================ End of Validation for Supplier site  ==================
+--------------------------------------------------------------------------------------+ */

         /*+--------------------------------------------------------------------------------------+
-- === Validation for combination of first_name,last_name,phone,email_address   =====
+--------------------------------------------------------------------------------------+ */
/*
BEGIN

IF pick_data_contacts.last_name IS NOT NULL
THEN

SELECT party_id
INTO lv_vendor_contact_id
from
(select hp.party_id
, hp.person_first_name
, hp.person_last_name
, hcpe.email_address
, hcpp.phone_number primary_phone_number
from hz_parties hp
, fnd_user fu
, hz_relationships hzr
, hz_parties hzr_hp
, hz_party_usg_assignments hpua
, HZ_CONTACT_POINTS hcpp
, HZ_CONTACT_POINTS hcpe
where hp.party_id = hzr.subject_id
and hzr.object_id = ln_vendor_id --8110-- :1 -- party of supplier
and hzr.relationship_type = 'CONTACT'
and hzr.relationship_code = 'CONTACT_OF'
and hzr.subject_type ='PERSON'
and hzr.object_type = 'ORGANIZATION'
-- commenting for Bug 6076854 --
and (hzr.end_date is null or hzr.end_date > sysdate)
and hzr.status = 'A'
and hzr_hp.party_id = hzr.party_id
and fu.person_party_id (+) = hp.party_id
and hp.party_id not in ( select contact_party_id
                       from pos_contact_requests pcr, pos_supplier_mappings psm
                       where pcr.request_status='PENDING'
                       and psm.mapping_id = pcr.mapping_id
                       and psm.PARTY_ID = ln_vendor_id--8110--:2
                       and contact_party_id is not null )
                       and hpua.party_id = hp.party_id
                       and hpua.status_flag = 'A'
                       and hpua.party_usage_code = 'SUPPLIER_CONTACT'
                       And hcpp.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
                       And hcpp.OWNER_TABLE_ID(+) = hzr.PARTY_ID
                       And hcpp.PHONE_LINE_TYPE(+) = 'GEN'
                       And hcpp.CONTACT_POINT_TYPE(+) = 'PHONE'
                       -- BUG 8275853,8517744 AND hcpp.primary_flag(+)='Y' -- BUG 8275853,8517744
                       And hcpe.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
                       and hcpe.OWNER_TABLE_ID(+) = hzr.PARTY_ID
                       And hcpe.CONTACT_POINT_TYPE(+) = 'EMAIL'
                       -- BUG 8275853,8517744 AND hcpe.primary_flag(+)='Y' -- BUG 8275853,8517744
                       And hcpe.status (+)='A' And hcpp.status (+)='A' -- commenting for Bug 6076854 --
                       and (hpua.effective_end_date is null OR hpua.effective_end_date > sysdate))
WHERE NVL(person_first_name,'-')||NVL(person_last_name,'-')||NVL(email_address,'-')||NVL(primary_phone_number,'-')
= NVL(pick_data_contacts.first_name,'-')||NVL(pick_data_contacts.last_name,'-')||NVL(pick_data_contacts.email_address,'-')||NVL(pick_data_contacts.phone,'-') ;

END IF;
dbms_output.put_line(lv_vendor_contact_id);
dbms_output.put_line(pick_data_contacts.first_name);
dbms_output.put_line(pick_data_contacts.last_name);
dbms_output.put_line(pick_data_contacts.email_address);
dbms_output.put_line(pick_data_contacts.phone);


        EXCEPTION

  WHEN NO_DATA_FOUND
          THEN
    l_return_status := 'E';
          l_i_err_msg := l_i_err_msg || ' | ' || 'Incorrect Supplier Site Contact Info';

          WHEN OTHERS THEN
          l_return_status := 'E';
          l_i_err_msg :=SUBSTR(SQLERRM,1,1000);

          END;

          IF l_return_status='Y' THEN
            BEGIN
              UPDATE XXBR_AP_SUPP_CONT_CV_STG
                SET  process_flag           =  'V'
               ,record_status         = 'Validated'
                    ,vendor_site_id         = lv_vendor_site_id
                WHERE PK_SUPPLIER_CONT_NUMBER      =  pick_data_contacts.PK_SUPPLIER_CONT_NUMBER;
            EXCEPTION
              WHEN OTHERS THEN
                l_sqlcode      :=SQLCODE;
                l_sqlerrm      :=SUBSTR(SQLERRM,1,1000);
                END;
          ELSE
             BEGIN
     l_final_error_msg:='Y';
               UPDATE XXBR_AP_SUPP_CONT_CV_STG
               SET    error_message       =  l_i_err_msg
                      ,process_flag        =  'E'
                      ,record_status  = 'Failure'
                      ,error_code         = 'VE'
               WHERE PK_SUPPLIER_CONT_NUMBER          =  pick_data_contacts.PK_SUPPLIER_CONT_NUMBER;
             EXCEPTION
              WHEN OTHERS THEN
                l_sqlcode      :=SQLCODE;
                l_sqlerrm      :=SUBSTR(SQLERRM,1,1000);
            END;
         END IF;

*/
/*+--------------------------------------------------------------------------------------+
-- =========== End of Validation for Validation for combination of first_name,last_name,phone,email_address  =================
+--------------------------------------------------------------------------------------+ */

         /*+--------------------------------------------------------------------------------------+
-- ======= Validation for Supplier Contact Last Name   ========
+--------------------------------------------------------------------------------------+ */
         BEGIN
            IF pick_data_contacts.last_name IS NOT NULL
            THEN
               BEGIN
                  UPDATE xxbr_ap_supp_cont_cv_stg
                     SET process_flag = 'V',
                         record_status = 'Validated',
                         vendor_site_id = lv_vendor_site_id
                   WHERE pk_supplier_cont_number =
                                    pick_data_contacts.pk_supplier_cont_number;
               EXCEPTION
                  WHEN OTHERS
                  THEN
                     l_sqlcode := SQLCODE;
                     l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
               END;
            ELSE
               l_i_err_msg :=
                     l_i_err_msg
                  || ' | '
                  || 'Last Name can not be null for Vendor Contact for Vendor Site  '
                  || pick_data_contacts.vendor_site_code
                  || ' for Vendor name '
                  || pick_data_contacts.vendor_name;

               BEGIN
                  l_final_error_msg := 'Y';

                  UPDATE xxbr_ap_supp_cont_cv_stg
                     SET error_message = l_i_err_msg,
                         process_flag = 'E',
                         record_status = 'Failure',
                         ERROR_CODE = 'VE'
                   WHERE pk_supplier_cont_number =
                                    pick_data_contacts.pk_supplier_cont_number;
               EXCEPTION
                  WHEN OTHERS
                  THEN
                     l_sqlcode := SQLCODE;
                     l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
               END;
            END IF;
         EXCEPTION
            WHEN OTHERS
            THEN
               l_sqlcode := SQLCODE;
               l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
         END;

------------------------------------------------------------------------------------
         IF l_final_error_msg IS NULL
         THEN
            BEGIN
               UPDATE xxbr_ap_supp_cont_cv_stg
                  SET process_flag = 'V',
                      record_status = 'Validated'
                --      ,vendor_site_id         = lv_vendor_site_id
               WHERE  pk_supplier_cont_number =
                                    pick_data_contacts.pk_supplier_cont_number;
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_sqlcode := SQLCODE;
                  l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
            END;
         ELSE
            BEGIN
               UPDATE xxbr_ap_supp_cont_cv_stg
                  SET                 --   error_message       =  l_i_err_msg
                     process_flag = 'E',
                     record_status = 'Failure'
                --       ,error_code         = 'VE'
               WHERE  pk_supplier_cont_number =
                                    pick_data_contacts.pk_supplier_cont_number;
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_sqlcode := SQLCODE;
                  l_sqlerrm := SUBSTR (SQLERRM, 1, 1000);
            END;
         END IF;
------------------------------------------------------------------------------------
/*+--------------------------------------------------------------------------------------+
-- ======== End of Validation for Supplier Contact Last Name ===============
+--------------------------------------------------------------------------------------+ */
      END LOOP;
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line ('NO Data Found');
         COMMIT;
   END xxbr_ap_supp_cont_cv_validate;

--//=================================================================
--// This Procedure insert into Supplier Contacts interface tables
--//=================================================================
   PROCEDURE xxbr_ap_supp_cont_cv_load
   IS
      CURSOR ap_supplier_details_cur
      IS
         SELECT *
           FROM xxbr_ap_supp_cont_cv_stg
          WHERE process_flag = 'V';

      -- Variable Declaration
      l_sqlerrm            VARCHAR2 (1000);
      l_i_err_msg          VARCHAR2 (1000);
      ln_sup_int_id        NUMBER;
      ln_sup_cont_int_id   NUMBER;
      ln_user_id           NUMBER
                            := TO_NUMBER (apps.fnd_profile.VALUE ('USER_ID'));
      ld_sysdate           DATE            := SYSDATE;
--ln_resp_id := TO_NUMBER(apps.fnd_profile.value('RESP_ID'));
--ln_resp_appl_id :=TO_NUMBER(apps.fnd_profile.value('RESP_APPL_ID'));
   BEGIN
      FOR load_data IN ap_supplier_details_cur
      LOOP
         BEGIN
            l_sqlerrm := NULL;

            SELECT apps.ap_sup_site_contact_int_s.NEXTVAL
              INTO ln_sup_cont_int_id
              FROM DUAL;

            INSERT INTO apps.ap_sup_site_contact_int
                        (vendor_site_id, vendor_id,
                         org_id, vendor_contact_interface_id,
                         first_name,
                         first_name_alt, last_name,
                         contact_name_alt, prefix,
                         area_code,
                         alt_area_code,
                         phone, alt_phone,
                         fax_area_code,
                         fax, email_address,
                         mail_stop, status, last_update_date,
                         last_updated_by, creation_date, created_by
                        )
                 VALUES (load_data.vendor_site_id, load_data.vendor_id,
                         load_data.org_id, ln_sup_cont_int_id,
                         load_data.first_name,
                         load_data.first_name_alternate, load_data.last_name,
                         load_data.contact_name_alternate, load_data.prefix,
                         TRIM (load_data.area_code),
                         TRIM (load_data.alt_area_code),
                         TRIM (load_data.phone), TRIM (load_data.alt_phone),
                         TRIM (load_data.fax_area_code),
                         TRIM (load_data.fax), load_data.email_address,
                         load_data.mail_stop, 'NEW', ld_sysdate,
                         ln_user_id, ld_sysdate, ln_user_id
                        );

            COMMIT;

            BEGIN
               UPDATE xxbr_ap_supp_cont_cv_stg
                  SET process_flag = 'P',
                      record_status = 'Successfully Processed'
                WHERE pk_supplier_cont_number =
                                             load_data.pk_supplier_cont_number;
            EXCEPTION
               WHEN OTHERS
               THEN
                  DBMS_OUTPUT.put_line ('Error Occured' || SQLERRM);
                  l_sqlerrm := SQLERRM;

                  --Update staging table with errors
                  UPDATE xxbr_ap_supp_cont_cv_stg
                     SET process_flag = 'E',
                         record_status = 'Error Update staging tbl',
                         error_message = l_sqlerrm
                   WHERE pk_supplier_cont_number =
                                             load_data.pk_supplier_cont_number;
            END;
         EXCEPTION
            WHEN OTHERS
            THEN
               DBMS_OUTPUT.put_line ('Error Occured' || SQLERRM);
               l_sqlerrm := SQLERRM;

               --Update staging table with errors
               UPDATE xxbr_ap_supp_cont_cv_stg
                  SET process_flag = 'E',
                      record_status = 'Error Inserting Iface table',
                      error_message = l_sqlerrm
                WHERE pk_supplier_cont_number =
                                             load_data.pk_supplier_cont_number;
         END;
      END LOOP;

      COMMIT;
   END xxbr_ap_supp_cont_cv_load;

--//======================================================
--//This Procedure call Supplier Sites Open Interface Import Program
--//======================================================
   PROCEDURE xxbr_ap_supp_cont_cv_import
   IS
----- Variable Declaration
      l_request_id          NUMBER;
      ln_user_id            NUMBER;
      ln_resp_id            NUMBER;
      ln_resp_appl_id       NUMBER;
      ln_org_id             NUMBER;
      x_req_return_status   BOOLEAN;
      x_req_phase           VARCHAR2 (30);
      x_req_status          VARCHAR2 (30);
      x_req_dev_phase       VARCHAR2 (30);
      x_req_dev_status      VARCHAR2 (30);
      x_req_message         VARCHAR2 (50);
   BEGIN
      ln_user_id := TO_NUMBER (apps.fnd_profile.VALUE ('USER_ID'));
      ln_resp_id := TO_NUMBER (apps.fnd_profile.VALUE ('RESP_ID'));
      ln_resp_appl_id := TO_NUMBER (apps.fnd_profile.VALUE ('RESP_APPL_ID'));
      apps.fnd_global.apps_initialize (1215, 20640, 200);
      COMMIT;

--apps.fnd_global.apps_initialize(1318,50554,200);
--apps.fnd_global.apps_initialize(ln_user_id,ln_resp_id,ln_resp_appl_id);
--mo_global.init('AP');
/*L_REQUEST_ID := APPS.FND_REQUEST.SUBMIT_REQUEST
 ('SQLAP',
 'APXSCIMP',
 '',
 '',
 FALSE,
 'NEW',
 '1000',
 'N','N','N');
dbms_output.put_line(L_REQUEST_ID);
commit;
*/

      ------ Getting the Operating Unit while submitting the request for Supplier Site Contacts --------
      BEGIN
         SELECT DISTINCT org_id
                    INTO ln_org_id
                    FROM xxbr_ap_supp_cont_cv_stg
                   WHERE ROWNUM = 1 AND org_id IS NOT NULL;
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line ('Error Occured' || SQLERRM);
      --   l_sqlerrm:=SQLERRM;
         --Update staging table with errors
      END;

      fnd_request.set_org_id (org_id => ln_org_id);
      COMMIT;
      l_request_id :=
         fnd_request.submit_request
               (application      => 'SQLAP',
                program          => 'APXSCIMP',
                description      => 'Supplier Site Contacts Open Interface Import',
                start_time       => SYSDATE,
                sub_request      => FALSE,
                argument1        => 'NEW',
                argument2        => 10000,
                argument3        => 'N',
                argument4        => 'N',
                argument5        => 'N'
               );
      DBMS_OUTPUT.put_line (l_request_id);
      COMMIT;

      IF l_request_id = 0
      THEN
         DBMS_OUTPUT.put_line ('Request not submitted');
      ELSE
         x_req_return_status :=
            fnd_concurrent.wait_for_request (l_request_id,
                                             20,
                                             0,
                                             x_req_phase,
                                             x_req_status,
                                             x_req_dev_phase,
                                             x_req_dev_status,
                                             x_req_message
                                            );
      END IF;

      COMMIT;

      IF x_req_return_status = TRUE
      THEN
         DBMS_OUTPUT.put_line ('Request completed successfully');
      END IF;
   END xxbr_ap_supp_cont_cv_import;
END xxbr_ap_suppliers_pkg;


No comments:

Post a Comment

How to find all cancel Requisitions

SELECT prha . *   FROM po_Requisition_headers_all prha , po_action_history pah   WHERE      1 = 1        AND pah . object_id ...