Protected by Copyscape Web Copyright Protection Software

Search This Blog

Thursday, June 20, 2013

Supplier Interface Script in Oracle apps Part 2



                                     x_party_id);
                     COMMIT;
                     DBMS_OUTPUT.
                      put_line ('X_RETURN_STATUS = ' || x_return_status);
                     DBMS_OUTPUT.
                      put_line ('X_MSG_COUNT = ' || TO_CHAR (x_msg_count));
                     DBMS_OUTPUT.put_line ('X_MSG_DATA = ' || x_msg_data);
                     DBMS_OUTPUT.
                      put_line ('X_VENDOR_ID = ' || TO_CHAR (x_vendor_id));
                     DBMS_OUTPUT.
                      put_line ('X_PARTY_ID = ' || TO_CHAR (x_party_id));
                     DBMS_OUTPUT.put_line ('');
                     fnd_file.
                      put_line (Fnd_File.LOG,
                                'X_RETURN_STATUS = ' || x_return_status);
                     fnd_file.
                      put_line (Fnd_File.LOG,
                                'X_MSG_COUNT = ' || TO_CHAR (x_msg_count));
                     fnd_file.
                      put_line (Fnd_File.LOG, 'X_MSG_DATA = ' || x_msg_data);
                     fnd_file.
                      put_line (Fnd_File.LOG,
                                'X_VENDOR_ID = ' || TO_CHAR (x_vendor_id));
                     fnd_file.
                      put_line (Fnd_File.LOG,
                                'X_PARTY_ID = ' || TO_CHAR (x_party_id));

                     COMMIT;

                     IF x_return_status = 'S'
                     THEN
                        COMMIT;
                     ELSE
                        ROLLBACK;
                     END IF;

                     IF x_msg_count > 0
                     THEN
                        FOR v_index IN 1 .. x_msg_count
                        LOOP
                           fnd_msg_pub.
                            get (p_msg_index       => v_index,
                                 p_encoded         => 'F',
                                 p_data            => x_msg_data,
                                 p_msg_index_out   => v_msg_index_out);
                           x_msg_data := SUBSTR (x_msg_data, 1, 200);
                           DBMS_OUTPUT.put_line (x_msg_data);
                        END LOOP;
                     END IF;

                     --- Updates Process records to Interface Staging table
                     UPDATE xxst_ap_supp_int_stg
                        SET status = 'P'        --, supplier_id=ln_supplier_id
                      WHERE     supplier_name = i.supplier_name
                            AND site_name = i.site_name
                            AND Batch_No = p_batch_no;

                     COMMIT;
                     count_ins_ven_records :=
                        NVL (count_ins_ven_records, 0) + 1;
                     gn_uploaded_rec := NVL (gn_uploaded_rec, 0) + 1;
                  EXCEPTION
                     WHEN OTHERS
                     THEN
                        fnd_file.
                         put_line (
                           fnd_file.LOG,
                           'ErrNo:99 Data Insertion Error in Suppilers Interface Table');
                        lc_error_message_server :=
                           'Error at the time of Data insertion into Staging table'; -- dbms.sqlerrm;
                        XXST_INT_UTILITY_PKG.
                         generate_error_log_prc (pn_batch_no,
                                                 ln_conc_req_id,
                                                 'XXST_AP_SUPP_INT_STG',
                                                 'Server Error',
                                                 'Server Error',
                                                 i.supplier_name,
                                                 '',
                                                 lc_error_message_server,
                                                 i.record_id);

                        UPDATE xxst_ap_supp_int_stg
                           SET status = 'E',
                               error_message = lc_error_message_server
                         WHERE     supplier_name = i.supplier_name
                               AND site_name = i.site_name
                               AND Batch_No = p_batch_no;

                        COMMIT;
                  END;
               END IF;
            END IF;

            lc_error_message := '';
            lc_error_message_server := '';
            lc_error_flag := '';
         END;
      END LOOP;

      COMMIT;

      --COMENTED BY SIVA ON 24-DEC-2012
      /*BEGIN
         ---- Standard Supplier Creation concurrent program
         ln_request_id1 :=
            fnd_request.submit_request ('SQLAP',
                                        'APXSUIMP',
                                        '',
                                        '',
                                        FALSE,
                                        'ALL',
                                        '1000',
                                        'N',
                                        'N',
                                        'N');
         COMMIT;

         IF fnd_concurrent.wait_for_request (request_id   => ln_request_id1,
                                             INTERVAL     => 2,
                                             MAX_WAIT     => 0,
                                             phase        => lc_phase,
                                             status       => lc_status,
                                             dev_phase    => lc_devpha,
                                             dev_status   => lc_devsta,
                                             MESSAGE      => lc_mesg)
         THEN
            COMMIT;
         END IF;
      END;*/

      ---- Supplier  site interface Request submission starts
      BEGIN
         ---- FTAL AP Supplier Site Creation Interface Program
         ln_request_id2 :=
            fnd_request.submit_request ('XXFTAL',
                                        'XXFTAL_SUPP_SITE_INT',
                                        '',
                                        '',
                                        FALSE,
                                        p_batch_no);
         COMMIT;

         IF fnd_concurrent.wait_for_request (request_id   => ln_request_id2,
                                             INTERVAL     => 2,
                                             MAX_WAIT     => 0,
                                             phase        => lc_phase,
                                             status       => lc_status,
                                             dev_phase    => lc_devpha,
                                             dev_status   => lc_devsta,
                                             MESSAGE      => lc_mesg)
         THEN
            COMMIT;
         END IF;
      END;

      ---- Supplier  Site Contact interface Request submission starts
      BEGIN
         ---- FTAL AP Supplier Site Contact Creation Interface Program
         ln_request_id3 :=
            fnd_request.submit_request ('XXFTAL',
                                        'XXFTAL_SUPP_SITE_CONT_INT',
                                        '',
                                        '',
                                        FALSE,
                                        p_batch_no);
         COMMIT;

         IF fnd_concurrent.wait_for_request (request_id   => ln_request_id3,
                                             INTERVAL     => 2,
                                             MAX_WAIT     => 0,
                                             phase        => lc_phase,
                                             status       => lc_status,
                                             dev_phase    => lc_devpha,
                                             dev_status   => lc_devsta,
                                             MESSAGE      => lc_mesg)
         THEN
            COMMIT;
         END IF;
      END;

      BEGIN
         ln_request_id4 :=
            fnd_request.submit_request ('XXFTAL',
                                        'XXFTAL_AP_SUPP_BANK_INT',
                                        '',
                                        '',
                                        FALSE,
                                        p_batch_no);
         COMMIT;

         IF fnd_concurrent.wait_for_request (request_id   => ln_request_id4,
                                             INTERVAL     => 2,
                                             MAX_WAIT     => 0,
                                             phase        => lc_phase,
                                             status       => lc_status,
                                             dev_phase    => lc_devpha,
                                             dev_status   => lc_devsta,
                                             MESSAGE      => lc_mesg)
         THEN
            COMMIT;
         END IF;
      END;

      fnd_file.
       put_line (
         fnd_file.LOG,
         ' -------------------------------------------------------------------------------------');
      fnd_file.
       put_line (fnd_file.LOG, 'Supplier Interface Log Information ');
      fnd_file.
       put_line (
         fnd_file.LOG,
         ' -------------------------------------------------------------------------------------');
      fnd_file.
       put_line (
         fnd_file.LOG,
         ' -------------------------------------------------------------------------------------');
      fnd_file.
       put_line (
         fnd_file.LOG,
         '+---------------------------------------------------------------------------+');
      fnd_file.put_line (fnd_file.LOG, ' ');
      fnd_file.
       put_line (fnd_file.LOG, '   Suppliers Interface Records');
      fnd_file.
       put_line (fnd_file.LOG, '+--------------------------------+');
      fnd_file.
       put_line (fnd_file.LOG,
                 ' Total Records Processed :' || count_new_ven_records);
      fnd_file.
       put_line (fnd_file.LOG,
                 ' Total Records Inserted  :' || count_ins_ven_records);
      fnd_file.
       put_line (
         fnd_file.LOG,
         ' Total Records Rejected  :'
         || (count_new_ven_records - count_ins_ven_records));
      fnd_file.put_line (fnd_file.LOG, ' ');

      -- Update the Audit Data Table for the processed Records
      /*UPDATE XXFTAL_INT_AUDIT_DATA
         SET count_received = gn_processed_rec,
             count_errored = gn_errored_rec,
             count_uploaded = gn_uploaded_rec
       WHERE batch_id = pn_batch_no AND conc_request_id = ln_conc_req_id;

      COMMIT;*/

      --Print The Audit and Error Information for the Processed Records
      BEGIN
         XXST_INT_UTILITY_PKG.
          PRT_AUDIT_ERROR_LOG_DATA (pn_batch_no, ln_conc_req_id);
      END;

      COMMIT;
      ---- Calling error print procedure
      ap_error_update;
      COMMIT;
   END ap_vendor_creation;

   PROCEDURE ap_error_update
   AS
      CURSOR lcur_error1
      IS
         (SELECT segment1, status, reject_code
            FROM ap_suppliers_int
           WHERE status = 'REJECTED' OR status = 'R');

      CURSOR lcur_error2
      IS
         SELECT x.segment1,
                y.vendor_id,
                z.vendor_site_id,
                z.vendor_site_code,
                z.last_name,
                z.reject_code
           FROM (SELECT segment1, vendor_id FROM po_vendors) x,
                (SELECT vendor_id, vendor_site_id FROM po_vendor_sites_all) y,
                (SELECT vendor_site_id,
                        vendor_site_code,
                        last_name,
                        reject_code
                   FROM ap_sup_site_contact_int
                  WHERE status = 'REJECTED' OR status = 'R') z
          WHERE     1 = 1
                AND x.vendor_id = y.vendor_id
                AND y.vendor_site_id = z.vendor_site_id;
   BEGIN
      FOR err1 IN lcur_error1
      LOOP
         EXIT WHEN lcur_error1%NOTFOUND;

         BEGIN
            UPDATE xxst_ap_supp_int_stg
               SET status = 'R', error_message = err1.reject_code
             WHERE supplier_name = err1.segment1;
         EXCEPTION
            WHEN OTHERS
            THEN
               NULL;
         END;
      END LOOP;
   EXCEPTION
      WHEN OTHERS
      THEN
         NULL;
   END ap_error_update;

   --------- Supplier Sites Procedure
   PROCEDURE ap_supp_sites_creation (ERRBUF          OUT VARCHAR2,
                                     RETCODE         OUT NUMBER,
                                     p_batch_no   IN     VARCHAR2)
   AS
      lc_payment_method             VARCHAR2 (30);
      ln_user_id                    NUMBER;
      ln_conc_req_id                NUMBER;
      ln_login_id                   NUMBER;
      ln_ship_to_location_id        NUMBER;
      ln_bill_to_location_id        NUMBER;
      ln_org_id                     NUMBER;
      ln_org_name                   VARCHAR2 (100);
      ln_org_id_c                   NUMBER;
      lc_lookup_type                VARCHAR2 (30);
      lc_pay_on_receipt             VARCHAR2 (50);
      ln_distribution_set_id        NUMBER;
      ln_set_of_book_id             NUMBER;
      ln_prepay_combination_id      gl_code_combinations_kfv.code_combination_id%TYPE;
      --ln_prepay_combination_id    NUMBER;
      ln_chart_of_accounts_id       NUMBER;
      ln_liability_combination_id   gl_code_combinations_kfv.code_combination_id%TYPE;
      lc_prepay_acc_segments        gl_code_combinations_kfv.concatenated_segments%TYPE;
      lc_liability_acc_segments     gl_code_combinations_kfv.concatenated_segments%TYPE;
      --ln_liability_combination_id NUMBER;
      ln_future_combination_id      NUMBER;
      lc_error_message_server       VARCHAR2 (1000);
      lc_error_message_record       VARCHAR2 (5000);
      lc_error_message              VARCHAR2 (9000);
      lc_error_flag2                VARCHAR2 (1) := 'N';
      lc_text                       VARCHAR2 (1);
      lr_vend_stg_row               xxst_ap_supp_int_stg%ROWTYPE;
      ln_supplier_id                NUMBER; -- derived from sequence AP_SUPPLIERS_INT_S
      ln_vend_category_id           NUMBER; -- Look up value from fa_categories on given values
      lc_vendor_type_lookuP_code    VARCHAR2 (15);
      ln_term_id                    NUMBER; -- term id derived from ap_terms for term name
      lc_term_name                  VARCHAR2 (50);
      lc_currency_code              VARCHAR2 (15);
      ln_location_id                NUMBER; -- Loopk up value from fa_locations on given values
      ln_last_updated_by            NUMBER; -- derived using FND_GLOBAL package
      ln_stg_vendor_count           NUMBER := 0;
      ln_base_vendor_count          NUMBER := 0;
      ln_stg_vendor_sites_count     NUMBER := 0;
      ln_base_vendor_sites_count    NUMBER := 0;
      lc_vendor_sites_flag          VARCHAR2 (1) := 'N';
      lc_vendor_creation_flag       VARCHAR2 (1) := '';
      lc_lookup_code1               VARCHAR2 (50);
      lc_lookup_code2               VARCHAR2 (50);
      l_country_code                fnd_territories_vl.territory_code%TYPE;
      ln_vendor_id                  NUMBER := 0;
      ln_vendor_interface_id        NUMBER := 0;
      ln_ven_site_flag              NUMBER := 0;
      ln_sites_pay_site             NUMBER := 0;
      ln_pay_site_flag              NUMBER := 0;
      lc_pay_site                   VARCHAR2 (1) := NULL;
      ln_supplier_name              VARCHAR2 (30);
      lc_errors_all                 VARCHAR2 (2000);
      v_request_id2                 NUMBER;
      lc_phase                      VARCHAR2 (20);
      lc_status                     VARCHAR2 (20);
      lc_devpha                     VARCHAR2 (20);
      lc_devsta                     VARCHAR2 (20);
      lc_mesg                       VARCHAR2 (20);
      ln_bef_total_Sites_count      NUMBER;
      ln_After_total_Sites_count    NUMBER;
      ln_vendor_site_id             NUMBER;
      ln_alt_site_id                NUMBER;
      lc_ship_via                   VARCHAR2 (100);
      LC_terms_date_basis           ap_lookup_codes.lookup_code%TYPE;
      lc_state_code                 VARCHAR2 (10);

      --ADDED BY XXXXX

      p_api_version                 NUMBER;
      p_init_msg_list               VARCHAR2 (200);
      p_commit                      VARCHAR2 (200);
      p_validation_level            NUMBER;
      x_return_status               VARCHAR2 (200);
      x_msg_count                   NUMBER;
      x_msg_data                    VARCHAR2 (200);
      p_vendor_site_rec             ap_vendor_pub_pkg.r_vendor_site_rec_type;
      x_vendor_site_id              NUMBER;
      x_party_site_id               NUMBER;
      x_location_id                 NUMBER;
      v_msg_index_out               NUMBER;

      --ADDED BY XXXXX
      lv_party_id                   NUMBER;
      lv_org_id                     NUMBER;
      ext_payee_tab                 iby_disbursement_setup_pub.external_payee_tab_type;
      ext_payee_id_tab              iby_disbursement_setup_pub.ext_payee_id_tab_type;
      ext_payee_create_tab          iby_disbursement_setup_pub.ext_payee_create_tab_type;
      l_ext_payee_rec               iby_disbursement_setup_pub.external_payee_rec_type;
      ext_payee_update_tab          iby_disbursement_setup_pub.ext_payee_update_tab_type;
      l_pay_return_status           VARCHAR2 (50);
      l_pay_msg_count               NUMBER;
      l_pay_msg_data                VARCHAR2 (1000);
      lv_error_reason               VARCHAR2 (2000) := NULL;
      lv_ext_payee_count            NUMBER;
      p_vendor_id                   NUMBER;
      p_vendor_site_id              NUMBER;
      p_party_site_id               NUMBER;
      p_pay_method                  VARCHAR2 (20);

      --This cursor fetches the sites details
      CURSOR lcur_supp_site
      IS
           SELECT ROWID, tkapvs.*
             FROM xxst_ap_supp_int_stg tkapvs
            WHERE     Batch_No = p_batch_no
                  AND status IS NULL
                  AND ERROR_MESSAGE IS NULL
         ORDER BY supplier_name;

      CURSOR lcur_error
      IS
         (SELECT asi.segment1,
                 assi.vendor_id,
                 assi.vendor_site_code,
                 assi.reject_code
            FROM ap_supplier_sites_int assi, ap_suppliers_int asi
           WHERE asi.vendor_interface_id = assi.vendor_interface_id
                 AND assi.status = 'REJECTED'
                 OR assi.status = 'R');
   BEGIN
      fnd_file.put_line (fnd_file.LOG, 'Enter into loop');

      --g_pn_batch_no1:=p_batch_no;
      --SELECT decode(trim(p_batch_no),'ALL',null,to_number(p_batch_no)) INTO pn_batch_no FROM DUAL;
      UPDATE xxst_ap_supp_int_stg
         SET STATUS = NULL, error_message = NULL
       WHERE batch_no = p_batch_no;

      COMMIT;
      ln_user_id := FND_PROFILE.VALUE ('USER_ID');

      IF ln_user_id IS NULL
      THEN
         ln_user_id := -1;
      END IF;

      ln_last_updated_by := FND_GLOBAL.USER_ID;
      -- To get the Conc Request ID
      ln_conc_req_id := FND_GLOBAL.CONC_REQUEST_ID;

      IF ln_conc_req_id IS NULL
      THEN
         ln_conc_req_id := -1;
      END IF;

      -- To get the Login ID
      ln_login_id := FND_GLOBAL.LOGIN_ID;

      IF ln_login_id IS NULL
      THEN
         ln_login_id := -1;
      END IF;

      -- To get the Set of Book ID
      ln_set_of_book_id := fnd_profile.VALUE ('GL_SET_OF_BKS_ID');

      -- Insert Audit Information Record in the Audit Table
     /* BEGIN
         XXST_INT_UTILITY_PKG.
          maintain_audit_data_prc (pn_batch_no,
                                   ln_conc_req_id,
                                   'XXST_AP_SUPP_INT_STG',
                                   'XXST_AP_SUPP_INT_STG',
                                   NULL,
                                   NULL,
                                   NULL);
      END;

      COMMIT;*/

      BEGIN
         ln_bef_total_Sites_count := 0;

         SELECT COUNT (*)
           INTO ln_bef_total_Sites_count
           FROM PO_VENDOR_SITES_ALL;
      EXCEPTION
         WHEN OTHERS
         THEN
            NULL;
      END;

      fnd_file.
       put_line (fnd_file.LOG, 'Enter into loopbefore' || p_batch_no);

      -- Start to Process the Cursor Records
      FOR j IN lcur_supp_site
      LOOP
         EXIT WHEN lcur_supp_site%NOTFOUND;
         ln_alt_site_id := NULL;

         BEGIN
            lc_vendor_sites_flag := 'N';
            ln_ven_site_flag := 0;
            ln_vendor_interface_id := 0;
            ln_vendor_id := 0;
            lc_error_flag2 := 'N';
            lc_errors_all := ' ';

            SELECT COUNT (*)
              INTO ln_ven_site_flag
              FROM po_vendors
             WHERE vendor_name = TRIM (j.supplier_name);
         EXCEPTION
            WHEN OTHERS
            THEN
               ln_ven_site_flag := 0;
         END;

         ------------------------------------------------------------------------------
         BEGIN
            IF NVL (ln_ven_site_flag, 0) = 0
            THEN
               lc_error_flag2 := 'Y';
               lc_error_message := 'Supplier Does not exists';
               gn_processed_rec := NVL (gn_processed_rec, 0) + 1;
               gn_errored_rec := NVL (gn_errored_rec, 0) + 1;
               lc_errors_all := lc_errors_all || '/' || lc_error_message;
               XXST_INT_UTILITY_PKG.
                generate_error_log_prc (pn_batch_no,
                                        ln_conc_req_id,
                                        'XXST_AP_SUPP_INT_STG',
                                        'SUPPLIER NUMBER',
                                        'SUPPLIER NUMBER',
                                        j.supplier_name,
                                        j.supplier_name,
                                        lc_error_message,
                                        j.record_id);

               UPDATE xxst_ap_supp_int_stg
                  SET status = 'E',
                      error_message = 'Supplier Does Not Exists'
                WHERE     supplier_name = j.supplier_name
                      AND Batch_No = j.Batch_No
                      AND site_name = j.site_name;

               COMMIT;
            END IF;
         EXCEPTION
            WHEN OTHERS
            THEN
               NULL;
         END;

         --- Getting vendor id from po_vendors for the respective site
         BEGIN
            IF NVL (ln_ven_site_flag, 0) = 1
            THEN
               SELECT vendor_id
                 INTO ln_vendor_id
                 FROM po_vendors
                WHERE UPPER (vendor_name) = UPPER (TRIM (j.supplier_name));
            /*SELECT vendor_interface_id
              INTO ln_vendor_interface_id
              FROM ap_suppliers_int
             WHERE UPPER (vendor_name) = UPPER (TRIM (j.supplier_name))
                   AND (TRIM (status) = 'PROCESSED' OR TRIM (status) = 'P');*/
            END IF;
         EXCEPTION
            WHEN OTHERS
            THEN
               NULL;
         END;

         IF NVL (ln_ven_site_flag, 0) = 1
         THEN
            BEGIN
               gn_processed_rec := NVL (gn_processed_rec, 0) + 1;
               ln_stg_vendor_sites_count := 0;
               ln_base_vendor_sites_count := 0;
               lc_vendor_sites_flag := 'N';

               BEGIN
                  SELECT COUNT (*)
                    INTO ln_stg_vendor_sites_count
                    FROM xxst_ap_supp_int_stg
                   WHERE     supplier_name = j.supplier_name
                         AND site_name = j.Site_name
                         AND Batch_No = p_batch_no;
               EXCEPTION
                  WHEN OTHERS
                  THEN
                     fnd_file.put_line (fnd_file.LOG, 'CNo:8');
                     NULL;
               END;

               BEGIN
                  SELECT COUNT (*)
                    INTO ln_base_vendor_sites_count
                    FROM po_vendor_sites_all
                   WHERE vendor_site_code = j.site_name
                         AND vendor_id = (SELECT vendor_id
                                            FROM po_vendors
                                           WHERE segment1 = j.supplier_name);
               EXCEPTION
                  WHEN OTHERS
                  THEN
                     fnd_file.put_line (fnd_file.LOG, 'CNo:9');
                     NULL;
               END;

               ---  Validation for Duplicate Suppiler Sites
               IF NVL (ln_stg_vendor_sites_count, 0) > 1
               THEN
                  lc_vendor_sites_flag := 'Y';
                  lc_error_message :=
                     '  Duplicate Supplier Sites in Staging Table';
                  lc_errors_all := lc_errors_all || '/' || lc_error_message;
                  XXST_INT_UTILITY_PKG.
                   generate_error_log_prc (pn_batch_no,
                                           ln_conc_req_id,
                                           'XXST_AP_SUPP_INT_STG',
                                           'SITE NAME',
                                           'SITE_NAME',
                                           j.supplier_name,
                                           j.site_name,
                                           lc_error_message,
                                           j.record_id);

                  UPDATE xxst_ap_supp_int_stg
                     SET status = 'E', error_message = lc_errors_all
                   WHERE     supplier_name = j.supplier_name
                         AND Batch_No = j.Batch_No
                         AND SITE_NAME = j.SITE_NAME;

                  COMMIT;
                  fnd_file.
                   put_line (
                     fnd_file.LOG,
                     'ErrNo:10 Record ID=' || j.record_id || lc_error_message);
               END IF;

               IF NVL (ln_base_vendor_sites_count, 0) >= 1
               THEN
                  gn_errored_rec := NVL (gn_errored_rec, 0) + 1;
                  lc_vendor_sites_flag := 'Y';
                  lc_error_message := '  Supplier Sites Already Exists in EBS';
                  lc_errors_all := lc_errors_all || '/' || lc_error_message;
                  XXST_INT_UTILITY_PKG.
                   generate_error_log_prc (pn_batch_no,
                                           ln_conc_req_id,
                                           'XXST_AP_SUPP_INT_STG',
                                           'SITE NAME',
                                           'SITE_NAME',
                                           j.supplier_name,
                                           j.site_name,
                                           lc_error_message,
                                           j.record_id);

                  UPDATE xxst_ap_supp_int_stg
                     SET status = 'E', error_message = lc_errors_all
                   WHERE     supplier_name = j.supplier_name
                         AND Batch_No = j.Batch_No
                         AND site_name = j.site_name;

                  COMMIT;
                  fnd_file.
                   put_line (
                     fnd_file.LOG,
                     'ErrNo:40 Record ID=' || j.record_id || lc_error_message);
               END IF;
            END;

            IF lc_vendor_sites_flag = 'N'
               AND (TRIM (j.status) != 'P' OR TRIM (j.STATUS) IS NULL)
            THEN
               count_new_sites_records := NVL (count_new_sites_records, 0) + 1;

               -----Getting Chart of Account ID value from GL Set Of Books
               BEGIN
                  SELECT chart_of_accounts_id
                    INTO ln_chart_of_accounts_id
                    FROM gl_sets_of_books
                   WHERE set_of_books_id = ln_set_of_book_id;
               EXCEPTION
                  WHEN OTHERS
                  THEN
                     lc_error_flag2 := 'Y';
                     lc_error_message := '  Invalid Chart of Account ID';
                     lc_errors_all := lc_errors_all || '/' || lc_error_message;
                     XXST_INT_UTILITY_PKG.
                      generate_error_log_prc (pn_batch_no,
                                              ln_conc_req_id,
                                              'XXST_AP_SUPP_INT_STG',
                                              'CHART OF ACCOUNTS ID',
                                              'CHART OF ACCOUNTS ID',
                                              j.supplier_name,
                                              ln_set_of_book_id,
                                              lc_error_message,
                                              j.record_id);
                     fnd_file.
                      put_line (
                        fnd_file.LOG,
                           'ErrNo:19 Record ID='
                        || j.RECORD_ID
                        || lc_error_message);
                     retcode := 2;
               END;

               -- Validation for ACCTS_PAY_CODE_COMBINATION_ID in LIABILITY CONCANTENATED (liability combination id)
               IF ln_chart_of_accounts_id IS NOT NULL
               THEN
                  lc_prepay_acc_segments := NULL;

                  begin mo_global.set_policy_context(upper('s'),22);end;

                 fnd_file.
                      put_line (
                        fnd_file.LOG,'at line 2322' );
                    


                  BEGIN
                     SELECT    gcc.segment1
                            || '.'
                            || gcc.segment2
                            || '.'
                            || gcc.segment3
                            || '.'
                            || gcc.segment4
                            || '.'
                            || TRIM (j.prepayment_account)
                            || '.'
                            || gcc.segment6
                            || '.'
                            || gcc.segment7
                            || '.'
                            || gcc.segment8
                               Prepay_ccd
                       INTO lc_prepay_acc_segments
                       FROM financials_system_parameters fsp,
                            gl_code_combinations gcc
                      WHERE 1 = 1
                            AND fsp.prepay_code_combination_id =
                                   gcc.code_combination_id(+);
                                  
                  EXCEPTION
                  WHEN OTHERS THEN
                     lc_error_flag2 := 'Y';
                     lc_error_message := '  Invalid Prepay Account ID';
                     lc_errors_all := lc_errors_all || '/' || lc_error_message;
                     XXST_INT_UTILITY_PKG.
                      generate_error_log_prc (pn_batch_no,
                                              ln_conc_req_id,
                                              'XXST_AP_SUPP_INT_STG',
                                              'PREPAY_ACCT_SEGMENT',
                                              'PREPAY_ACCT_SEGMENT',
                                              j.supplier_name,
                                              lc_prepay_acc_segments,
                                              lc_error_message,
                                              j.record_id);
                     fnd_file.
                      put_line (
                        fnd_file.LOG,
                           'ErrNo:19 Record ID='
                        || j.RECORD_ID
                        || lc_error_message);
                     retcode := 2;
                  END;

                  lc_liability_acc_segments := NULL;

                  BEGIN
                     SELECT    gcc.segment1
                            || '.'
                            || gcc.segment2
                            || '.'
                            || gcc.segment3
                            || '.'
                            || gcc.segment4
                            || '.'
                            || TRIM (j.liability_account)
                            || '.'
                            || gcc.segment6
                            || '.'
                            || gcc.segment7
                            || '.'
                            || gcc.segment8
                               Lib_ccd
                       INTO lc_liability_acc_segments
                       FROM financials_system_parameters fsp,
                            gl_code_combinations gcc
                      WHERE 1 = 1
                            AND fsp.accts_pay_code_combination_id =
                                   gcc.code_combination_id(+);
                                   EXCEPTION
                  WHEN OTHERS THEN
                     lc_error_flag2 := 'Y';
                     lc_error_message := '  Invalid Liability Account ID';
                     lc_errors_all := lc_errors_all || '/' || lc_error_message;
                     XXST_INT_UTILITY_PKG.
                      generate_error_log_prc (pn_batch_no,
                                              ln_conc_req_id,
                                              'XXST_AP_SUPP_INT_STG',
                                              'LIABILITY_ACCOUNT',
                                              'LIABILITY_ACCOUNT',
                                              j.supplier_name,
                                              lc_prepay_acc_segments,
                                              lc_error_message,
                                              j.record_id);
                     fnd_file.
                      put_line (
                        fnd_file.LOG,
                           'ErrNo:19 Record ID='
                        || j.RECORD_ID
                        || lc_error_message);
                     retcode := 2;
                  END;
                 

                  ln_prepay_combination_id := NULL;

                 fnd_file.
                      put_line (
                        fnd_file.LOG,'at line 2428' );

                  BEGIN
                     ln_liability_combination_id := NULL;
                     ln_prepay_combination_id :=
                        fnd_flex_ext.
                         get_ccid (
                           application_short_name   => 'SQLGL',
                           key_flex_code            => 'GL#',
                           structure_number         => ln_chart_of_accounts_id,
                           validation_date          => TO_CHAR (SYSDATE,
                                                                'DD-MON-YYYY'),
                           concatenated_segments    => lc_prepay_acc_segments);
                  END;

                  IF NVL (ln_prepay_combination_id, 0) = 0
                  THEN
                     lc_error_flag2 := 'Y';
                     lc_error_message :=
                        '  Invalid Prepayment concatenated id';
                     lc_errors_all := lc_errors_all || '/' || lc_error_message;
                     XXST_INT_UTILITY_PKG.
                      generate_error_log_prc (pn_batch_no,
                                              ln_conc_req_id,
                                              'XXST_AP_SUPP_INT_STG',
                                              'PREPAYMENT_CONCATENATED',
                                              'PREPAYMENT_CONCATENATED',
                                              j.supplier_name,
                                              lc_prepay_acc_segments,
                                              lc_error_message,
                                              j.record_id);

                     UPDATE xxst_ap_supp_int_stg
                        SET STATUS = 'E', ERROR_MESSAGE = lc_errors_all
                      WHERE     supplier_name = j.supplier_name
                            AND Batch_No = j.Batch_No
                            AND SITE_NAME = j.SITE_NAME;

                     -- Print the Error Message in the Log
                     fnd_file.
                      put_line (
                        fnd_file.LOG,
                           'ErrNo:20 Record ID='
                        || j.RECORD_ID
                        || lc_error_message);
                     retcode := 2;
                  END IF;

                  --to Get Expense account id
                  ln_liability_combination_id := NULL;

                  BEGIN
                     ln_liability_combination_id :=
                        fnd_flex_ext.
                         get_ccid (
                           application_short_name   => 'SQLGL',
                           key_flex_code            => 'GL#',
                           structure_number         => ln_chart_of_accounts_id,
                           validation_date          => TO_CHAR (SYSDATE,
                                                                'DD-MON-YYYY'),
                           concatenated_segments    => lc_liability_acc_segments);
                  END;

                  IF NVL (ln_liability_combination_id, 0) = 0
                  THEN
                     lc_error_flag2 := 'Y';
                     lc_error_message := '  Invalid liability concatenated id';
                     lc_errors_all := lc_errors_all || '/' || lc_error_message;
                     XXST_INT_UTILITY_PKG.
                      generate_error_log_prc (pn_batch_no,
                                              ln_conc_req_id,
                                              'XXST_AP_SUPP_INT_STG',
                                              'LIABILITY_CONCATENATED',
                                              'LIABILITY_CONCATENATED',
                                              j.supplier_name,
                                              lc_liability_acc_segments,
                                              lc_error_message,
                                              j.record_id);

                     UPDATE xxst_ap_supp_int_stg
                        SET STATUS = 'E', ERROR_MESSAGE = lc_errors_all
                      WHERE     supplier_name = j.supplier_name
                            AND Batch_No = j.Batch_No
                            AND site_name = j.site_name;

                     COMMIT;
                     -- Print the Error Message in the Log
                     fnd_file.
                      put_line (
                        fnd_file.LOG,
                           'ErrNo:20 Record ID='
                        || j.RECORD_ID
                        || lc_error_message);
                     --fnd_file.put_line(fnd_file.LOG,'Supplier Name : '||j.supplier_name||' : ' ||p_batch_no);
                     retcode := 2;
                  END IF;
               END IF;

               --Validation for Prepay code combination  field: PREPAYMENT_CONCATENATED
               --   Validation for payment term
               --   and retrieve the term_id from ap_terms
               ln_term_id := NULL;
               ----- Validation for Operating Unit
               ln_org_id := NULL;
               ln_org_name := NULL;

               IF j.operating_unit IS NULL
               THEN
                  BEGIN
                     ln_org_id := fnd_profile.VALUE ('ORG_ID');

                     SELECT name
                       INTO ln_org_name
                       FROM hr_operating_units
                      WHERE organization_id = ln_org_id;
                  EXCEPTION
                     WHEN OTHERS
                     THEN
                        NULL;
                  END;
               ELSE
                  BEGIN
                     SELECT organization_id, NAME
                       INTO ln_org_id, ln_org_name
                       FROM hr_operating_units
                      WHERE NAME = j.operating_unit;
                  EXCEPTION
                     WHEN OTHERS
                     THEN
                        lc_error_flag2 := 'Y';
                        lc_error_message := '  Operating Unit is invalid';
                        lc_errors_all :=
                           lc_errors_all || '/' || lc_error_message;
                        XXST_INT_UTILITY_PKG.
                         generate_error_log_prc (pn_batch_no,
                                                 ln_conc_req_id,
                                                 'XXST_AP_SUPP_INT_STG',
                                                 'OPERATING UNIT',
                                                 'OPERATING_UNIT',
                                                 j.supplier_name,
                                                 j.OPERATING_UNIT,
                                                 lc_error_message,
                                                 j.RECORD_ID);

                        UPDATE xxst_ap_supp_int_stg
                           SET STATUS = 'E', ERROR_MESSAGE = lc_errors_all
                         WHERE     supplier_name = j.supplier_name
                               AND Batch_No = j.Batch_No
                               AND site_name = j.site_name;

                        fnd_file.
                         put_line (
                           fnd_file.LOG,
                              'ErrNo:27 Record ID='
                           || j.RECORD_ID
                           || lc_error_message);
                        retcode := 2;
                  END;
               END IF;

               ----- Validation for Country code
               l_country_code := NULL;

               IF j.country IS NOT NULL
               THEN
                  BEGIN
                     SELECT vl.territory_code
                       INTO l_country_code
                       FROM fnd_territories_vl vl
                      WHERE UPPER (territory_short_name) =
                               UPPER (TRIM (j.country));
                  EXCEPTION
                     WHEN OTHERS
                     THEN
                        lc_error_flag2 := 'Y';
                        lc_error_message := 'Invalid Country Code';
                        lc_errors_all :=
                           lc_errors_all || '/' || lc_error_message;
                        XXST_INT_UTILITY_PKG.
                         generate_error_log_prc (pn_batch_no,
                                                 ln_conc_req_id,
                                                 'XXST_AP_SUPP_INT_STG',
                                                 'COUNTRY',
                                                 'COUNTRY',
                                                 j.supplier_name,
                                                 j.COUNTRY,
                                                 lc_error_message,
                                                 j.RECORD_ID);

                        UPDATE XXST_AP_SUPP_INT_STG
                           SET status = 'E', error_message = lc_errors_all
                         WHERE     supplier_name = j.supplier_name
                               AND Batch_No = j.Batch_No
                               AND site_name = j.site_name;

                        fnd_file.
                         put_line (
                           fnd_file.LOG,
                              'ErrNo:28 Record ID='
                           || j.RECORD_ID
                           || lc_error_message);
                        retcode := 2;
                  END;
               END IF;

               IF j.state IS NOT NULL
               THEN
                  BEGIN
                     SELECT lookup_code
                       INTO lc_state_code
                       FROM fnd_lookup_values
                      WHERE UPPER (lookup_code) = UPPER (TRIM (j.state))
                            AND LOOKUP_TYPE IN
                                   ('IN_STATES', 'XXFTAL SUPPLIER STATES');
                  EXCEPTION
                     WHEN OTHERS
                     THEN
                        lc_error_flag2 := 'Y';
                        lc_error_message := 'Invalid State Code';
                        lc_errors_all :=
                           lc_errors_all || '/' || lc_error_message;
                        XXST_INT_UTILITY_PKG.
                         generate_error_log_prc (p_batch_no,
                                                 ln_conc_req_id,
                                                 'XXST_AP_SUPP_INT_STG',
                                                 'STATE',
                                                 'STATE',
                                                 j.supplier_name,
                                                 j.state,
                                                 lc_error_message,
                                                 j.RECORD_ID);

                        UPDATE XXST_AP_SUPP_INT_STG
                           SET status = 'E', error_message = lc_errors_all
                         WHERE     supplier_name = j.supplier_name
                               AND Batch_No = j.Batch_No
                               AND site_name = j.site_name;

                        fnd_file.
                         put_line (
                           fnd_file.LOG,
                              'ErrNo:28 Record ID='
                           || j.RECORD_ID
                           || lc_error_message);
                        retcode := 2;
                  END;
               END IF;

               IF lc_error_flag2 = 'N'
               THEN
                  BEGIN                               --XXST_AP_SUPP_INT_STG
                     /* INSERT
                        INTO ap_supplier_sites_int (
                                vendor_site_code,
                                vendor_site_code_alt,
                                vendor_interface_id,
                                vendor_id,
                                purchasing_site_flag,
                                rfq_only_site_flag,
                                pay_site_flag,
                                attention_ar_flag,
                                address_line1,
                                address_lines_alt,
                                address_line2,
                                address_line3,
                                address_line4,
                                city,
                                state,
                                zip,
                                province,
                                country,
                                area_code,
                                phone,
                                ship_to_location_id,
                                ship_to_location_code,
                                bill_to_location_id,
                                bill_to_location_code,
                                ship_via_lookup_code,
                                freight_terms_lookup_code,
                                fob_lookup_code,
                                inactive_date,
                                fax,
                                fax_area_code,
                                telex,
                                payment_method_lookup_code,
                                terms_date_basis,
                                distribution_set_id,
                                distribution_set_name,
                                accts_pay_code_combination_id,
                                prepay_code_combination_id,
                                pay_group_lookup_code,
                                payment_priority,
                                terms_id,
                                terms_name,
                                invoice_amount_limit,
                                pay_date_basis_lookup_code,
                                always_take_disc_flag,
                                invoice_currency_code,
                                payment_currency_code,
                                hold_all_payments_flag,
                                hold_future_payments_flag,
                                hold_reason,
                                hold_unmatched_invoices_flag,
                                org_id,
                                operating_unit_name,
                                edi_transaction_handling,
                                edi_id_number,
                                edi_payment_method,
                                edi_payment_format,
                                edi_remittance_method,
                                bank_charge_bearer,
                                edi_remittance_instruction,
                                default_pay_site_id,
                                pay_on_code,
                                pay_on_receipt_summary_code,
                                match_option,
                                country_of_origin_code,
                                future_dated_payment_ccid,
                                create_debit_memo_flag,
                                offset_tax_flag,
                                supplier_notif_method,
                                email_address,
                                remittance_email,
                                primary_pay_site_flag,
                                import_request_id,
                                status,
                                ATTRIBUTE_CATEGORY,
                                ATTRIBUTE1,
                                ATTRIBUTE2,
                                ATTRIBUTE3,
                                ATTRIBUTE4,
                                ATTRIBUTE5,
                                ATTRIBUTE6,
                                ATTRIBUTE7,
                                ATTRIBUTE8,
                                ATTRIBUTE9,
                                ATTRIBUTE10,
                                ATTRIBUTE11,
                                ATTRIBUTE12,
                                ATTRIBUTE13,
                                ATTRIBUTE14,
                                ATTRIBUTE15,
                                last_update_date,
                                last_updated_by,
                                last_update_login,
                                creation_date,
                                created_by)
                      VALUES (
                                SUBSTR (TRIM (j.site_name), 1, 15),       --- 1
                                TRIM (j.alternate_supplier_site_if_any),  --- 2
                                ln_vendor_interface_id,                   --- 3
                                ln_vendor_id,                             --- 4
                                NULL, --j.purchasing_site,                                   --- 5
                                NULL, --(j.rfqs_only_site),--j.rfqs_only_site),'y'),         --- 6
                                NULL, --j.pay_site,                                          --- 7
                                NULL, --trim(j.attention_ar_flag),                           --- 8
                                j.address1,                               --- 9
                                j.alternate_supp_address_ifany,          --- 10
                                j.address2,                              --- 11
                                j.address3,                              --- 12
                                j.address4,                              --- 13
                                j.city,                                  --- 14
                                j.state,                                 --- 15
                                j.pin_code,                              --- 16
                                NULL, --j.province,                                          --- 17
                                l_country_code,                          --- 18
                                j.std_isd_tel_code,                      --- 19
                                j.telephone_no,                          --- 20
                                NULL, --ln_ship_to_location_id,                              --- 21
                                NULL, ----initcap(j.ship_to_location),                       --- 22
                                ln_bill_to_location_id,                  --- 23
                                NULL, ----j.bill_to_location,                                --- 24
                                DECODE (j.operating_unit,
                                        'FTALOU', 'ROAD',
                                        NULL), --trim(lc_ship_via),                                 --- 25
                                NULL, --trim(j.freight_terms),                               --- 26
                                NULL, --trim(j.fob),                                         --- 27
                                NULL, --j.inactive_date,                                     --- 28
                                j.fax_no,                                --- 29
                                j.std_isd_fax_code,                      --- 30
                                NULL,                                    --- 31
                                UPPER (lc_lookup_code2),                 --- 32
                                'Invoice', --nvl(initcap(lc_terms_date_basis),'INVOICE'),                --- 33
                                ln_distribution_set_id,                  --- 34
                                NULL, ----trim(j.distribution_control_id),                   --- 35
                                ln_liability_combination_id, --j.liability_concatenated,    --- 36
                                ln_prepay_combination_id, --j.prepayment_concatenated,   --- 37
                                NULL, --upper(j.pay_group),                                  --- 38
                                NULL, --nvl(j.payment_priority,99),                          --- 39
                                ln_term_id,                              --- 40
                                NULL, --trim(j.payment_terms),                               --- 41
                                NULL,                                    --- 42
                                NULL, --nvl(upper(j.pay_date_basis),'DUE'),                  --- 43
                                NULL, --trim(j.always_take_disc),                            --- 44
                                NULL, --nvl(trim(j.invoice_currency),'INR'),                 --- 45
                                NULL, --nvl(trim(j.payment_currency),'INR'),                 --- 46
                                NULL, --trim(j.hold_all_payments),                           --- 47
                                NULL, --trim(j.future_payments_hold),                        --- 48
                                NULL, --trim(j.hold_reason),                                 --- 49
                                NULL, --trim(j.hold_unmatched_invoices),                     --- 50
                                TRIM (j.org_id),                         --- 51
                                TRIM (j.operating_unit),                 --- 52
                                NULL, --trim(j.edi_transaction_handling),                    --- 53
                                NULL, --trim(j.edi_trading_partner_no),                      --- 54
                                NULL, --trim(j.edi_payment_method),                          --- 55
                                NULL, --trim(j.edi_payment_format),                          --- 56
                                NULL, --trim(j.edi_remittance_method),                       --- 57
                                NULL, --trim(j.bank_charge_bearer),                          --- 58
                                NULL, --trim(j.edi_remittance_instruction),                  --- 59
                                ln_alt_site_id,                          --- 60
                                'RECEIPT', --j.pay_on_code, --lc_pay_on_receipt,                  --- 61
                                'RECEIPT', --j.pay_on_receipt_code,   --                          --- 62
                                NULL, --decode(j.invoice_match_option),'Y','R','N','P',null),--- 63
                                l_country_code, -- Y- Receipt (R) , N for (P) Purchase Order.--- 64
                                ln_future_combination_id, --j.future_dt_payment_concatenated,--- 65
                                'Y', --trim(j.credit_debit_memo),                           --- 66
                                NULL,                                    --- 67
                                NULL, --trim(j.supplier_notification_method),                --- 68
                                TRIM (j.email_id),                       --- 69
                                NULL, --trim(j.remittance_mail),                             --- 70
                                NULL, --trim(j.primary_pay_site),--lc_pay_site               --- 71
                                NULL,                                    --- 72
                                'NEW', --j.status,                                           --- 73
                                NULL, --j.ATTRIBUTE_CATEGORY,                                --- 74
                                1000, --j.ATTRIBUTE1,                                        --- 75
                                NULL, --j.ATTRIBUTE2,                                        --- 75
                                NULL, --j.ATTRIBUTE3,                                        --- 76
                                NULL, --j.ATTRIBUTE4,                                        --- 77
                                NULL, --j.ATTRIBUTE5,                                        --- 78
                                NULL, --j.ATTRIBUTE6,                                        --- 79
                                TRIM (j.invoice_initiator_email), --j.ATTRIBUTE7,             --- 80
                                TRIM (j.payment_team_email), --j.ATTRIBUTE8,                  --- 81
                                TRIM (j.ifsc_no), --j.ATTRIBUTE9,                             --- 82
                                NULL, --j.ATTRIBUTE10,                                       --- 83
                                TRIM (j.ifsc_no), --j.ATTRIBUTE11,                            --- 84
                                'Y', --j.ATTRIBUTE12,                                        --- 85
                                NULL, --j.ATTRIBUTE13,                                       --- 86
                                NULL, --j.ATTRIBUTE14,                                       --- 87
                                NULL, --j.ATTRIBUTE15,                                       --- 88
                                SYSDATE,                                 --- 89
                                ln_last_updated_by,                      --- 90
                                ln_login_id,                             --- 91
                                SYSDATE,                                 --- 92
                                ln_user_id                               --- 93
                                          );
                                          */

                     --ADDED BY XXXXX

                     p_api_version := 1.0;
                     p_init_msg_list := FND_API.G_TRUE;
                     p_commit := FND_API.G_TRUE;
                     p_validation_level := FND_API.G_VALID_LEVEL_FULL;
                     x_return_status := NULL;
                     x_msg_count := NULL;
                     x_msg_data := NULL;

                     P_VENDOR_SITE_REC.vendor_site_code :=
                        SUBSTR (TRIM (j.site_name), 1, 15);
                     P_VENDOR_SITE_REC.vendor_site_code_alt :=
                        TRIM (j.alternate_supplier_site_if_any);
                     P_VENDOR_SITE_REC.vendor_interface_id := NULL;
                     P_VENDOR_SITE_REC.vendor_id := ln_vendor_id;
                     P_VENDOR_SITE_REC.purchasing_site_flag := 'Y';
                     P_VENDOR_SITE_REC.rfq_only_site_flag := 'N';
                     P_VENDOR_SITE_REC.pay_site_flag := 'Y';
                     P_VENDOR_SITE_REC.attention_ar_flag := NULL;
                     P_VENDOR_SITE_REC.address_line1 := j.address1;
                     P_VENDOR_SITE_REC.address_lines_alt :=
                        j.alternate_supp_address_ifany;
                     P_VENDOR_SITE_REC.address_line2 := j.address2;
                     P_VENDOR_SITE_REC.address_line3 := j.address3;
                     P_VENDOR_SITE_REC.address_line4 := j.address4;
                     P_VENDOR_SITE_REC.city := j.city;
                     P_VENDOR_SITE_REC.state := j.state;
                     P_VENDOR_SITE_REC.zip := j.pin_code;
                     P_VENDOR_SITE_REC.province := NULL;
                     P_VENDOR_SITE_REC.country := 'US';      --l_country_code;
                     P_VENDOR_SITE_REC.area_code := j.std_isd_tel_code;
                     P_VENDOR_SITE_REC.phone := j.telephone_no;
                     P_VENDOR_SITE_REC.ship_to_location_id := NULL;
                     P_VENDOR_SITE_REC.ship_to_location_code := NULL;
                     P_VENDOR_SITE_REC.bill_to_location_id :=
                        ln_bill_to_location_id;
                     P_VENDOR_SITE_REC.bill_to_location_code := NULL;
                     --P_VENDOR_SITE_REC.ship_via_lookup_code:=DECODE (j.operating_unit, 'FTALOU', 'ROAD', NULL);
                     P_VENDOR_SITE_REC.freight_terms_lookup_code := NULL;
                     P_VENDOR_SITE_REC.fob_lookup_code := NULL;
                     P_VENDOR_SITE_REC.inactive_date := NULL;
                     P_VENDOR_SITE_REC.fax := j.fax_no;
                     P_VENDOR_SITE_REC.fax_area_code := j.std_isd_fax_code;
                     P_VENDOR_SITE_REC.telex := NULL;
                     --P_VENDOR_SITE_REC.payment_method_lookup_code:='CHECK';
                     --P_VENDOR_SITE_REC.PAYMENT_METHOD_CODE:='CHECK';-
                     P_VENDOR_SITE_REC.terms_date_basis := 'Invoice';
                     P_VENDOR_SITE_REC.distribution_set_id :=
                        ln_distribution_set_id;
                     P_VENDOR_SITE_REC.distribution_set_name := NULL;
                     P_VENDOR_SITE_REC.accts_pay_code_combination_id :=
                        ln_liability_combination_id;
                     P_VENDOR_SITE_REC.prepay_code_combination_id :=
                        ln_prepay_combination_id;
                     P_VENDOR_SITE_REC.pay_group_lookup_code := NULL;
                     P_VENDOR_SITE_REC.payment_priority := 99;
                     P_VENDOR_SITE_REC.terms_id := ln_term_id;
                     P_VENDOR_SITE_REC.terms_name := NULL;
                     P_VENDOR_SITE_REC.invoice_amount_limit := NULL;
                     P_VENDOR_SITE_REC.pay_date_basis_lookup_code := NULL;
                     P_VENDOR_SITE_REC.always_take_disc_flag := NULL;
                     P_VENDOR_SITE_REC.invoice_currency_code := NULL;
                     P_VENDOR_SITE_REC.payment_currency_code := NULL;
                     P_VENDOR_SITE_REC.hold_all_payments_flag := NULL;
                     P_VENDOR_SITE_REC.hold_future_payments_flag := NULL;
                     P_VENDOR_SITE_REC.hold_reason := NULL;
                     P_VENDOR_SITE_REC.hold_unmatched_invoices_flag := NULL;
                     P_VENDOR_SITE_REC.org_id := TRIM (j.org_id);
                     P_VENDOR_SITE_REC.org_name := TRIM (j.operating_unit);
                     --P_VENDOR_SITE_REC.operating_unit_name:='ABC Hyderabad OU';
                     P_VENDOR_SITE_REC.edi_transaction_handling := NULL;
                     P_VENDOR_SITE_REC.edi_id_number := NULL;
                     P_VENDOR_SITE_REC.edi_payment_method := NULL;
                     P_VENDOR_SITE_REC.edi_payment_format := NULL;
                     P_VENDOR_SITE_REC.edi_remittance_method := NULL;
                     P_VENDOR_SITE_REC.bank_charge_bearer := NULL;
                     P_VENDOR_SITE_REC.edi_remittance_instruction := NULL;
                     P_VENDOR_SITE_REC.default_pay_site_id := ln_alt_site_id;
                     P_VENDOR_SITE_REC.pay_on_code := 'RECEIPT';
                     P_VENDOR_SITE_REC.pay_on_receipt_summary_code :=
                        'RECEIPT';
                     P_VENDOR_SITE_REC.match_option := NULL;
                     P_VENDOR_SITE_REC.country_of_origin_code :=
                        l_country_code;
                     P_VENDOR_SITE_REC.future_dated_payment_ccid := NULL;
                     P_VENDOR_SITE_REC.create_debit_memo_flag := 'Y';
                     P_VENDOR_SITE_REC.offset_tax_flag := NULL;
                     P_VENDOR_SITE_REC.supplier_notif_method := NULL;
                     P_VENDOR_SITE_REC.email_address := TRIM (j.email_id);
                     P_VENDOR_SITE_REC.remittance_email := NULL;
                     P_VENDOR_SITE_REC.primary_pay_site_flag := NULL;
                     -- P_VENDOR_SITE_REC.import_request_id:=NULL;
                     --P_VENDOR_SITE_REC.status:=NULL;
                     P_VENDOR_SITE_REC.ATTRIBUTE_CATEGORY := NULL;
                     P_VENDOR_SITE_REC.ATTRIBUTE1 := 1000;
                     P_VENDOR_SITE_REC.ATTRIBUTE2 := NULL;
                     P_VENDOR_SITE_REC.ATTRIBUTE3 := NULL;
                     P_VENDOR_SITE_REC.ATTRIBUTE4 := NULL;
                     P_VENDOR_SITE_REC.ATTRIBUTE5 := NULL;
                     P_VENDOR_SITE_REC.ATTRIBUTE6 := NULL;
                     P_VENDOR_SITE_REC.ATTRIBUTE7 :=
                        TRIM (j.invoice_initiator_email);
                     P_VENDOR_SITE_REC.ATTRIBUTE8 :=
                        TRIM (j.payment_team_email);
                     P_VENDOR_SITE_REC.ATTRIBUTE9 := TRIM (j.ifsc_no);
                     P_VENDOR_SITE_REC.ATTRIBUTE10 := NULL;
                     P_VENDOR_SITE_REC.ATTRIBUTE11 := TRIM (j.ifsc_no);
                     P_VENDOR_SITE_REC.ATTRIBUTE12 := NULL;
                     P_VENDOR_SITE_REC.ATTRIBUTE13 := NULL;
                     P_VENDOR_SITE_REC.ATTRIBUTE14 := NULL;
                     P_VENDOR_SITE_REC.ATTRIBUTE15 := NULL;


                     x_vendor_site_id := NULL;
                     x_party_site_id := NULL;
                     x_location_id := NULL;

                     ap_vendor_pub_pkg.
                      create_vendor_site (p_api_version,
                                          p_init_msg_list,
                                          p_commit,
                                          p_validation_level,
                                          x_return_status,
                                          x_msg_count,
                                          x_msg_data,
                                          p_vendor_site_rec,
                                          x_vendor_site_id,
                                          x_party_site_id,
                                          x_location_id);

                     IF x_return_status = 'S'
                     THEN
                        NULL;                                            -----
                        ------------
                        p_vendor_id := ln_vendor_id;        --37170;-- number;
                        p_vendor_site_id := x_vendor_site_id; --6972;-- number;
                        p_party_site_id := x_party_site_id; --224634;-- number;
                        p_pay_method := 'CHECK';              -- varchar2(20);


                        SELECT org_id
                          INTO lv_org_id
                          FROM ap_supplier_sites_all
                         WHERE vendor_site_id = p_vendor_site_id;

                        mo_global.set_policy_context ('S', lv_org_id);

                        SELECT party_id
                          INTO lv_party_id
                          FROM ap_suppliers
                         WHERE vendor_id = p_vendor_id;

                        SELECT COUNT (1)
                          INTO lv_ext_payee_count
                          FROM iby_external_payees_all payee
                         WHERE     payee.payee_party_id = lv_party_id
                               AND payee.payment_function = 'PAYABLES_DISB'
                               AND payee.party_site_id = p_party_site_id
                               AND payee.supplier_site_id = p_vendor_site_id
                               AND payee.org_id = lv_org_id
                               AND payee.org_type = 'OPERATING_UNIT';

                        IF lv_ext_payee_count = 0
                        THEN
                           l_ext_payee_rec.payee_party_id := lv_party_id;
                           l_ext_payee_rec.payee_party_site_id :=
                              p_party_site_id;
                           l_ext_payee_rec.supplier_site_id :=
                              p_vendor_site_id;
                           l_ext_payee_rec.payment_function := 'PAYABLES_DISB';
                           l_ext_payee_rec.payer_org_id := lv_org_id;
                           l_ext_payee_rec.payer_org_type := 'OPERATING_UNIT';
                           l_ext_payee_rec.default_pmt_method := p_pay_method;
                           l_ext_payee_rec.inactive_date := NULL;
                           l_ext_payee_rec.Exclusive_Pay_Flag := 'N';

                           ext_payee_tab (1) := l_ext_payee_rec;

                           IBY_DISBURSEMENT_SETUP_PUB.
                            Create_External_Payee (
                              p_api_version            => 1.0,
                              p_init_msg_list          => FND_API.G_FALSE,
                              p_ext_payee_tab          => ext_payee_tab,
                              x_return_status          => l_pay_return_status,
                              x_msg_count              => l_pay_msg_count,
                              x_msg_data               => l_pay_msg_data,
                              x_ext_payee_id_tab       => ext_payee_id_tab,
                              x_ext_payee_status_tab   => ext_payee_create_tab);

                           IF l_pay_msg_count >= 1
                           THEN
                              FOR i IN 1 .. l_pay_msg_count
                              LOOP
                                 IF lv_error_reason IS NULL
                                 THEN
                                    lv_error_reason :=
                                       SUBSTR (
                                          fnd_msg_pub.
                                           get (p_encoded => fnd_api.g_false),
                                          1,
                                          255);
                                 ELSE
                                    lv_error_reason :=
                                       lv_error_reason || ' ,'
                                       || SUBSTR (
                                             fnd_msg_pub.
                                              get (
                                                p_encoded => fnd_api.g_false),
                                             1,
                                             255);
                                 END IF;
                              END LOOP;

                              fnd_file.
                               put_line (fnd_file.LOG,
                                         'API failed: ' || lv_error_reason);
                              DBMS_OUTPUT.
                               put_line ('API failed: ' || lv_error_reason);
                           END IF;
                        ELSE
                           SELECT exclusive_payment_flag,
                                  default_payment_method_code,
                                  ece_tp_location_code,
                                  bank_charge_bearer,
                                  bank_instruction1_code,
                                  bank_instruction2_code,
                                  bank_instruction_details,
                                  payment_reason_code,
                                  payment_reason_comments,
                                  inactive_date,
                                  payment_text_message1,
                                  payment_text_message2,
                                  payment_text_message3,
                                  delivery_channel_code,
                                  payment_format_code,
                                  settlement_priority,
                                  payee_party_id,
                                  payment_function,
                                  party_site_id,
                                  supplier_site_id,
                                  org_id,
                                  org_type,
                                  ext_payee_id
                             INTO ext_payee_tab (1).exclusive_pay_flag,
                                  ext_payee_tab (1).Default_Pmt_method,
                                  ext_payee_tab (1).ece_tp_loc_code,
                                  ext_payee_tab (1).Bank_Charge_Bearer,
                                  ext_payee_tab (1).Bank_Instr1_Code,
                                  ext_payee_tab (1).Bank_Instr2_Code,
                                  ext_payee_tab (1).Bank_Instr_Detail,
                                  ext_payee_tab (1).Pay_Reason_Code,
                                  ext_payee_tab (1).Pay_Reason_Com,
                                  ext_payee_tab (1).Inactive_Date,
                                  ext_payee_tab (1).Pay_Message1,
                                  ext_payee_tab (1).Pay_Message2,
                                  ext_payee_tab (1).Pay_Message3,
                                  ext_payee_tab (1).Delivery_Channel,
                                  ext_payee_tab (1).Pmt_Format,
                                  ext_payee_tab (1).Settlement_Priority,
                                  ext_payee_tab (1).payee_party_id,
                                  ext_payee_tab (1).payment_function,
                                  ext_payee_tab (1).Payee_Party_Site_Id,
                                  ext_payee_tab (1).supplier_site_id,
                                  ext_payee_tab (1).Payer_Org_Id,
                                  ext_payee_tab (1).Payer_Org_Type,
                                  ext_payee_id_tab (1).ext_payee_id
                             FROM iby_external_payees_all payee
                            WHERE payee.payee_party_id = lv_party_id
                                  AND payee.payment_function =
                                         'PAYABLES_DISB'
                                  AND payee.party_site_id = p_party_site_id
                                  AND payee.supplier_site_id =
                                         p_vendor_site_id
                                  AND payee.org_id = lv_org_id
                                  AND payee.org_type = 'OPERATING_UNIT';

                           ext_payee_tab (1).default_pmt_method :=
                              p_pay_method;
                           --fnd_file.put_line (fnd_file.LOG, p_msg);
                           iby_disbursement_setup_pub.
                            update_external_payee (
                              p_api_version            => 1.0,
                              p_init_msg_list          => FND_API.G_TRUE,
                              p_ext_payee_tab          => ext_payee_tab,
                              p_ext_payee_id_tab       => ext_payee_id_tab,
                              x_return_status          => l_pay_return_status,
                              x_msg_count              => l_pay_msg_count,
                              x_msg_data               => l_pay_msg_data,
                              x_ext_payee_status_tab   => ext_payee_update_tab);

                           --fnd_file.put_line (fnd_file.LOG,I1.VENDOR_NAME||'-'||'VENDOR'||I1.VENDOR_SITE_CODE||'-'||'VENDOR SITE'||l_pay_return_status||'-'||l_pay_msg_count||'-'||l_pay_msg_data);
                           IF l_pay_msg_count >= 1
                           THEN
                              FOR i IN 1 .. l_pay_msg_count
                              LOOP
                                 IF lv_error_reason IS NULL
                                 THEN
                                    lv_error_reason :=
                                       SUBSTR (
                                          fnd_msg_pub.
                                           get (p_encoded => fnd_api.g_false),
                                          1,
                                          255);
                                 ELSE
                                    lv_error_reason :=
                                       lv_error_reason || ' ,'
                                       || SUBSTR (
                                             fnd_msg_pub.
                                              get (
                                                p_encoded => fnd_api.g_false),
                                             1,
                                             255);
                                 END IF;
                              END LOOP;

                              --fnd_file.put_line (fnd_file.LOG, p_msg);
                              DBMS_OUTPUT.
                               put_line ('API failed: ' || lv_error_reason);
                           END IF;
                        END IF;
                     -------------------




For Supplier Interface Script in Oracle apps Part 3 Click here


For Supplier Interface Script in Oracle apps Part 4 Click here