Protected by Copyscape Web Copyright Protection Software

Pages

Search This Blog

Thursday, June 20, 2013

Supplier Interface Script in Oracle apps Part 3






                     END IF;

                     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_SITE_ID = ' || TO_CHAR (x_vendor_site_id));
                     DBMS_OUTPUT.
                      put_line (
                        'X_PARTY_SITE_ID = ' || TO_CHAR (x_party_site_id));
                     DBMS_OUTPUT.
                      put_line (
                        'X_LOCATION_ID = ' || TO_CHAR (x_location_id));
                     DBMS_OUTPUT.put_line ('');

                     fnd_file.
                      put_line (Fnd_File.LOG,
                                'X_RETURN_STATUS = ' || x_return_status);
                     --fnd_file. put_line ( '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_PARTY_SITE_ID = ' || TO_CHAR (x_party_site_id));
                     fnd_file.
                      put_line (
                        Fnd_File.LOG,
                        'X_LOCATION_ID = ' || TO_CHAR (x_location_id));

                     --fnd_file. put_line ( Fnd_File.OUTPUT,'X_RETURN_STATUS = ' || x_return_status);

                     UPDATE XXST_AP_SUPP_INT_STG
                        SET status = 'P', VENDOR_SITE_ID = x_vendor_site_id
                      WHERE     supplier_name = j.supplier_name
                            AND Batch_No = j.Batch_No
                            AND site_name = j.site_name;

                     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);
                           fnd_file.put_line (Fnd_File.OUTPUT, x_msg_data);
                        END LOOP;
                     END IF;

                     UPDATE XXST_AP_SUPP_INT_STG
                        SET status = 'P', supplier_id = ln_vendor_id
                      WHERE     supplier_name = j.supplier_name
                            AND Batch_No = j.Batch_No
                            AND site_name = j.site_name;

                     COMMIT;
                     gn_uploaded_rec := NVL (gn_uploaded_rec, 0) + 1;
                     count_ins_sites_records :=
                        NVL (count_ins_sites_records, 0) + 1;
                  EXCEPTION
                     WHEN OTHERS
                     THEN
                        lc_error_message_server := '  Server Error'; -- DBMS.SQLERRM;
                        fnd_file.
                         put_line (
                           fnd_file.LOG,
                           'ErrNo:29 Data Insertion error in Sites Table');
                        fnd_file.
                         put_line (fnd_file.LOG, 'SQLCODE: ' || SQLCODE);
                        fnd_file.
                         put_line (fnd_file.LOG, 'Message: ' || SQLERRM);
                  END;

                  lc_error_flag2 := '';
               END IF; --IF lc_error_flag2 IS NOT NULL AND lc_error_flag2 <> 'Y' THEN
            END IF; --IF lc_vendor_sites_flag='N' and (TRIM(j.status)!='P' OR TRIM(j.STATUS) IS NULL) then
         END IF;                            --F NVL(ln_ven_site_flag,0)=1 then

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

      COMMIT;

      --CALLING SUPPLIERS SITES CONC PRG
      /*v_request_id2 :=
         fnd_request.submit_request ('SQLAP',
                                     'APXSSIMP',
                                     '',
                                     '',
                                     FALSE,
                                     'ALL',
                                     '1000',
                                     'N',
                                     'N',
                                     'N');
      COMMIT;

      IF fnd_concurrent.wait_for_request (request_id   => v_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;*/

      FOR err IN lcur_error
      LOOP
         BEGIN
            UPDATE XXST_AP_SUPP_INT_STG
               SET status = 'R', error_message = err.reject_code
             WHERE supplier_name = err.segment1
                   AND site_name = err.vendor_site_code;
         EXCEPTION
            WHEN OTHERS
            THEN
               NULL;
         END;
      END LOOP;

      -- Update the Audit Data Table for the processed Records
     /* UPDATE XXST_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;*/

      BEGIN
         ln_after_total_Sites_count := 0;

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

      --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;

      fnd_file.
       put_line (
         fnd_file.OUTPUT,
         '-------------------------------------------------------------------------------------------------');
      COMMIT;
   END ap_supp_sites_creation;

   ---------  Supplier Contacts Procedure
   PROCEDURE ap_supp_contacts_creation (ERRBUF          OUT VARCHAR2,
                                        RETCODE         OUT NUMBER,
                                        p_batch_no   IN     VARCHAR2)
   AS
      ln_org_id_c                    NUMBER;
      lc_org_name                    VARCHAR2 (100);
      lc_contacts_flag               VARCHAR2 (2) := 'N';
      ln_contacts_flag               NUMBER := 0;
      lc_error_flag1                 VARCHAR2 (1) := 'N';
      ln_conc_req_id                 NUMBER;
      lc_error_message               VARCHAR2 (200) := '';
      ln_last_updated_by             NUMBER;
      ln_login_id                    NUMBER;
      ln_user_id                     NUMBER;
      ln_bef_total_Contact_count     NUMBER;
      ln_after_total_Contact_count   NUMBER;
      Ln_vendor_site_id              po_vendor_sites_all.vendor_site_id%TYPE;
      lc_error_message_server        VARCHAR2 (200);
      v_request_id3                  NUMBER;
      lc_phase                       VARCHAR2 (20);
      lc_status                      VARCHAR2 (20);
      lc_devpha                      VARCHAR2 (20);
      lc_devsta                      VARCHAR2 (20);
      lc_mesg                        VARCHAR2 (20);
      v_request_id5                  NUMBER;

      ln_vendor_id                   NUMBER;
      ln_party_site_id               NUMBER;

      --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_contact_rec           ap_vendor_pub_pkg.r_vendor_contact_rec_type;
      x_vendor_contact_id            NUMBER;
      x_per_party_id                 NUMBER;
      x_rel_party_id                 NUMBER;
      x_rel_id                       NUMBER;
      x_org_contact_id               NUMBER;
      x_party_site_id                NUMBER;
      v_msg_index_out                NUMBER;


      CURSOR CAPVCTAdd
      IS
         SELECT *
           FROM xxst_ap_supp_int_stg tkapcs
          WHERE     tkapcs.Batch_No = P_BATCH_NO
                AND STATUS IS NULL
                AND ERROR_MESSAGE IS NULL;

      CURSOR lcur_error2
      IS
         SELECT x.segment1,
                y.vendor_id,
                z.vendor_site_id,
                --z.vendor_site_code,
                z.last_name
           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, first_name, last_name
                   FROM xxst_ap_supp_int_stg
                  WHERE vendor_site_id IS NOT NULL) z
          WHERE     1 = 1
                AND x.vendor_id = y.vendor_id
                AND y.vendor_site_id = z.vendor_site_id;
   /*
   CURSOR CAPVCTAdd IS
   SELECT pvs.vendor_site_id,tkapcs.*
   FROM xxst_ap_supp_int_stg tkapcs,
   po_vendors pv,
   po_vendor_sites_all pvs
   WHERE pv.segment1=tkapcs.supplier_name
   AND pv.vendor_id=pvs.vendor_id
   AND tkapcs.SITE_NAME=PVS.VENDOR_SITE_CODE
   AND tkapcs.Batch_No=P_BATCH_NO
   ORDER BY tkapcs.supplier_name;*/
   BEGIN
      UPDATE xxst_ap_supp_int_stg
         SET STATUS = NULL, error_message = NULL
       WHERE batch_no = p_batch_no;

      COMMIT;

      --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;

      gn_processed_rec := 0;
      gn_errored_rec := 0;
      gn_uploaded_rec := 0;
      -- To get the User Id
      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 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 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;

      ln_bef_total_Contact_count := 0;

      /*BEGIN
         SELECT COUNT (*)
           INTO ln_bef_total_Contact_count
           FROM PO_VENDOR_CONTACTS;
      EXCEPTION
         WHEN OTHERS
         THEN
            NULL;
      END;*/

      FOR k IN CAPVCTAdd
      LOOP
         BEGIN
            gn_processed_rec := NVL (gn_processed_rec, 0) + 1;
            lc_contacts_flag := 'N';
            lc_error_flag1 := 'N';
            ln_contacts_flag := 0;
            lc_error_message := '';
            --getting vendor site id
            Ln_vendor_site_id := NULL;

            BEGIN
               SELECT vendor_id, party_site_id, vendor_site_id
                 INTO ln_vendor_id, ln_party_site_id, Ln_vendor_site_id
                 FROM ap_supplier_sites_ALL
                WHERE vendor_site_id = K.vendor_site_id;
            EXCEPTION
               WHEN OTHERS
               THEN
                  lc_error_flag1 := 'Y';
                  gn_errored_rec := gn_errored_rec + 1;
                  lc_error_message :=
                        'Supplier Site Does Not Exists'
                     || '  Vendor Number  '
                     || k.supplier_name
                     || 'Vendor Site Name  '
                     || k.SITE_NAME;
                  XXST_INT_UTILITY_PKG.
                   generate_error_log_prc (p_batch_no,
                                           ln_conc_req_id,
                                           'XXST_AP_SUPP_INT_STG',
                                           'Supplier Site',
                                           'Supplier Site',
                                           k.supplier_name,
                                           k.SITE_NAME,
                                           lc_error_message,
                                           k.RECORD_ID);

                  UPDATE XXST_AP_SUPP_INT_STG
                     SET status = 'E', error_message = lc_error_message
                   WHERE vendor_site_id = K.vendor_site_id;

                  COMMIT;
                  --WHERE record_id = k.record_id;
                  fnd_file.
                   put_line (fnd_file.LOG,
                             'ErrNo:32.0' || lc_error_message);
            --END IF;
            END;

            fnd_file.
             put_line (fnd_file.LOG,
                       'Vendor Site Id' || Ln_vendor_site_id);

            /*IF Ln_vendor_site_id IS NOT NULL
            THEN
               BEGIN
                  SELECT COUNT (*)
                    INTO ln_contacts_flag
                    FROM PO_VENDOR_CONTACTS
                   WHERE vendor_site_id = Ln_vendor_site_id
                         AND UPPER (Last_name) = UPPER (TRIM (k.last_name));
               EXCEPTION
                  WHEN OTHERS
                  THEN
                     NULL;
               END;
            END IF;*/

            IF Ln_vendor_site_id IS NULL
            THEN
               lc_error_flag1 := 'Y';
               gn_errored_rec := gn_errored_rec + 1;
               lc_error_message :=
                     'Supplier Site Does Not Exists'
                  || '  Vendor Number  '
                  || k.supplier_name
                  || 'Vendor Site Name  '
                  || k.SITE_NAME;
               XXST_INT_UTILITY_PKG.
                generate_error_log_prc (p_batch_no,
                                        ln_conc_req_id,
                                        'XXST_AP_SUPP_INT_STG',
                                        'Supplier Site',
                                        'Supplier Site',
                                        k.supplier_name,
                                        k.SITE_NAME,
                                        lc_error_message,
                                        k.RECORD_ID);

               UPDATE XXST_AP_SUPP_INT_STG
                  SET status = 'E', error_message = lc_error_message
                WHERE     supplier_name = k.supplier_name
                      AND Batch_No = k.Batch_No
                      AND SITE_NAME = k.SITE_NAME;

               COMMIT;
               --WHERE record_id = k.record_id;
               fnd_file.
                put_line (fnd_file.LOG, 'ErrNo:32' || lc_error_message);
            END IF;

            fnd_file.
             put_line (fnd_file.LOG,
                       'ln_contacts_flag' || ln_contacts_flag);

            /*IF NVL(ln_contacts_flag,0)>=1 THEN
              lc_contacts_flag:='Y';
              lc_error_flag1 := 'Y';
              gn_errored_rec:=gn_errored_rec+1;
              lc_error_message := 'Supplier Same contact already Exists';
                       XXST_INT_UTILITY_PKG.generate_error_log_prc(p_batch_no,ln_conc_req_id,
                       'XXST_AP_SUPP_INT_STG','Supplier Contact','Supplier Contact',NULL,
                       k.SITE_NAME,lc_error_message,k.RECORD_ID);
                       UPDATE XXST_AP_SUPP_INT_STG
                       SET status = 'E',
                       error_message  = lc_error_message
                       WHERE supplier_name=k.supplier_name AND Batch_No=k.Batch_No  AND SITE_NAME=k.SITE_NAME ;
                       --WHERE record_id = k.record_id;
                       fnd_file.put_line(fnd_file.LOG,'ErrNo:32'||lc_error_message);
            END IF;*/
            IF lc_contacts_flag = 'N'
            THEN
               count_new_cont_records := NVL (count_new_cont_records, 0) + 1;
               -- Validation for Operating Unit
               ln_org_id_c := NULL;
               lc_org_name := NULL;

               IF TRIM (k.operating_unit) IS NULL
               THEN
                  BEGIN
                     ln_org_id_c := fnd_profile.VALUE ('ORG_ID');

                     SELECT name
                       INTO lc_org_name
                       FROM HR_OPERATING_UNITS
                      WHERE ORGANIZATION_ID = ln_org_id_c;
                  EXCEPTION
                     WHEN OTHERS
                     THEN
                        fnd_file.put_line (fnd_file.LOG, 'CNo:31');
                        NULL;
                  END;
               ELSE
                  BEGIN
                     SELECT organization_id, NAME
                       INTO ln_org_id_c, lc_org_name
                       FROM HR_OPERATING_UNITS
                      WHERE NAME = k.OPERATING_UNIT;
                  EXCEPTION
                     WHEN OTHERS
                     THEN
                        gn_errored_rec := gn_errored_rec + 1;
                        lc_error_flag1 := 'Y';
                        lc_error_message := '  Operating Unit is invalid';
                        XXST_INT_UTILITY_PKG.
                         generate_error_log_prc (p_batch_no,
                                                 ln_conc_req_id,
                                                 'XXST_AP_SUPP_INT_STG',
                                                 'OPERATING UNIT',
                                                 'OPERATING_UNIT',
                                                 k.supplier_name,
                                                 k.OPERATING_UNIT,
                                                 lc_error_message,
                                                 k.RECORD_ID);

                        UPDATE XXST_AP_SUPP_INT_STG
                           SET status = 'E', error_message = lc_error_message
                         WHERE     supplier_name = k.supplier_name
                               AND Batch_No = k.Batch_No
                               AND SITE_NAME = k.SITE_NAME
                               AND record_id = k.record_id;

                        --WHERE record_id = k.record_id;
                        fnd_file.
                         put_line (fnd_file.LOG,
                                   'ErrNo:32' || lc_error_message);
                  END;
               END IF;

               ------ Validation for Last Name
               IF TRIM (k.last_name) IS NULL
               THEN
                  BEGIN
                     lc_error_flag1 := 'Y';
                     lc_error_message := '  Last Name cannot be null';
                     XXST_INT_UTILITY_PKG.
                      generate_error_log_prc (p_batch_no,
                                              ln_conc_req_id,
                                              'XXST_AP_SUPP_INT_STG',
                                              'LAST_NAME',
                                              'LAST_NAME',
                                              k.supplier_name,
                                              k.last_name,
                                              lc_error_message,
                                              k.record_id);

                     UPDATE XXST_AP_SUPP_INT_STG
                        SET status = 'E', error_message = lc_error_message
                      WHERE     supplier_name = k.supplier_name
                            AND Batch_No = k.Batch_No
                            AND SITE_NAME = k.SITE_NAME
                            AND record_id = k.record_id;

                     fnd_file.
                      put_line (fnd_file.LOG,
                                'ErrNo:33' || lc_error_message);
                  EXCEPTION
                     WHEN OTHERS
                     THEN
                        gn_errored_rec := gn_errored_rec + 1;
                        lc_error_flag1 := 'Y';
                        lc_error_message := '  Last Name cannot be null';
                        XXST_INT_UTILITY_PKG.
                         generate_error_log_prc (p_batch_no,
                                                 ln_conc_req_id,
                                                 'XXST_AP_SUPP_INT_STG',
                                                 'LAST_NAME',
                                                 'LAST_NAME',
                                                 k.supplier_name,
                                                 k.last_name,
                                                 lc_error_message,
                                                 k.RECORD_ID);

                        UPDATE XXST_AP_SUPP_INT_STG
                           SET STATUS = 'E', ERROR_MESSAGE = lc_error_message
                         WHERE     supplier_name = k.supplier_name
                               AND Batch_No = k.Batch_No
                               AND SITE_NAME = k.SITE_NAME
                               AND record_id = k.record_id;

                        --WHERE RECORD_ID = k.RECORD_ID;
                        fnd_file.
                         put_line (fnd_file.LOG,
                                   'ErrNo:34' || lc_error_message);
                  END;
               END IF;

               fnd_file.
                put_line (
                  fnd_file.LOG,
                     'vendor_site_code'
                  || k.SITE_NAME
                  || ':'
                  || lc_error_flag1
                  || ':'
                  || Ln_vendor_site_id);

               IF lc_error_flag1 = 'Y'
               THEN
                  gn_errored_rec := NVL (gn_errored_rec, 0) + 1;
               END IF;

               fnd_file.
                put_line (
                  fnd_file.LOG,
                     'New Address CNo:Before30'
                  || lc_error_flag1
                  || Ln_vendor_site_id);

               IF lc_error_flag1 = 'N' AND Ln_vendor_site_id IS NOT NULL
               THEN
                  fnd_file.
                   put_line (fnd_file.LOG, 'New Address CNo:After30');

                  BEGIN
                     /* INSERT
                        INTO ap_sup_site_contact_int (--vendor_site_code, -- To avoid  (ORG INFO and VENDOR_SITE_CODE mismatch) error
                                                           vendor_site_id,
                                                           org_id,
                                                           operating_unit_name,
                                                           inactive_date,
                                                           first_name,
                                                           middle_name,
                                                           last_name,
                                                           last_name_alt,
                                                           prefix,
                                                           title,
                                                           area_code,
                                                           phone,
                                                           department,
                                                           status,
                                                           email_address,
                                                           url,
                                                           alt_area_code,
                                                           alt_phone,
                                                           fax_area_code,
                                                           fax,
                                                           last_update_date,
                                                           last_updated_by,
                                                           last_update_login,
                                                           creation_date,
                                                           created_by)
                      VALUES (--trim(k.site_name),
                              ln_vendor_site_id,
                              ln_org_id_c,
                              lc_org_name,
                              NULL,                          --k.inactive_date,
                              SUBSTR (INITCAP (TRIM (k.first_name)), 1, 15),
                              NULL,                      --trim(k.middle_name),
                              SUBSTR (INITCAP (TRIM (k.last_name)), 1, 20),
                              NULL,               --initcap(trim(k.last_name)),
                              NULL,                           --trim(k.prefix),
                              NULL,                            --trim(k.title),
                              TRIM (k.std_isd_tel_code),
                              TRIM (k.telephone_no),
                              NULL,                       --trim(k.department),
                              'NEW',                          --trim(k.status),
                              TRIM (k.email_id),
                              TRIM (k.url),
                              NULL,                        --trim(k.area_code),
                              TRIM (k.mobile_no),
                              TRIM (k.std_isd_fax_code),
                              TRIM (k.fax_no),
                              SYSDATE,
                              ln_last_updated_by,
                              ln_login_id,
                              SYSDATE,
                              ln_user_id);*/


                     --ADDED BY XXXX
                     p_api_version := 1.0;
                     p_init_msg_list := 'T';
                     p_commit := 'T';
                     p_validation_level := FND_API.G_VALID_LEVEL_FULL;
                     x_return_status := NULL;
                     x_msg_count := NULL;
                     x_msg_data := NULL;
                     p_vendor_contact_rec.vendor_contact_id :=
                        po_vendor_contacts_s.NEXTVAL;
                     --DBMS_OUTPUT.put_line ('po_vendor_contacts_s.NEXTVAL = ' || po_vendor_contacts_s.NEXTVAL);
                     --fnd_file.put_line (Fnd_File.OUTPUT, 'po_vendor_contacts_s.NEXTVAL = ' || po_vendor_contacts_s.NEXTVAL);
                     P_VENDOR_CONTACT_REC.vendor_site_id := ln_vendor_site_id;
                     P_VENDOR_CONTACT_REC.org_id := ln_org_id_c;
                     P_VENDOR_CONTACT_REC.operating_unit_name := lc_org_name;
                     P_VENDOR_CONTACT_REC.inactive_date := NULL;
                     P_VENDOR_CONTACT_REC.PERSON_first_name :=
                        SUBSTR (INITCAP (TRIM (k.first_name)), 1, 15);
                     P_VENDOR_CONTACT_REC.PERSON_middle_name := NULL;
                     P_VENDOR_CONTACT_REC.PERSON_last_name :=
                        SUBSTR (INITCAP (TRIM (k.last_name)), 1, 20);
                     P_VENDOR_CONTACT_REC.PERSON_LAST_NAME_PHONETIC := NULL;
                     P_VENDOR_CONTACT_REC.prefix := NULL;
                     P_VENDOR_CONTACT_REC.PERSON_TITLE := NULL;
                     P_VENDOR_CONTACT_REC.area_code :=
                        TRIM (k.std_isd_tel_code);
                     P_VENDOR_CONTACT_REC.phone := TRIM (k.telephone_no);
                     P_VENDOR_CONTACT_REC.department := NULL;
                     ---P_VENDOR_CONTACT_REC.status:=NULL;
                     P_VENDOR_CONTACT_REC.email_address := TRIM (k.email_id);
                     P_VENDOR_CONTACT_REC.url := TRIM (k.url);
                     P_VENDOR_CONTACT_REC.alt_area_code := NULL;
                     P_VENDOR_CONTACT_REC.alt_phone := TRIM (k.mobile_no);
                     P_VENDOR_CONTACT_REC.fax_area_code :=
                        TRIM (k.std_isd_fax_code);
                     P_VENDOR_CONTACT_REC.FAX_PHONE := TRIM (k.fax_no);
                     p_vendor_contact_rec.party_site_id := ln_party_site_id;
                     --p_vendor_contact_rec.org_party_site_id := 304651;
                     p_vendor_contact_rec.VENDOR_ID := ln_vendor_id;

                     x_vendor_contact_id := NULL;
                     x_per_party_id := NULL;
                     x_rel_party_id := NULL;
                     x_rel_id := NULL;
                     x_org_contact_id := NULL;
                     x_party_site_id := NULL;
                     ap_vendor_pub_pkg.
                      create_vendor_contact (p_api_version,
                                             p_init_msg_list,
                                             p_commit,
                                             p_validation_level,
                                             x_return_status,
                                             x_msg_count,
                                             x_msg_data,
                                             p_vendor_contact_rec,
                                             x_vendor_contact_id,
                                             x_per_party_id,
                                             x_rel_party_id,
                                             x_rel_id,
                                             x_org_contact_id,
                                             x_party_site_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_CONTACT_ID = '
                        || TO_CHAR (x_vendor_contact_id));
                     DBMS_OUTPUT.
                      put_line (
                        'X_PER_PARTY_ID = ' || TO_CHAR (x_per_party_id));
                     DBMS_OUTPUT.
                      put_line (
                        'X_REL_PARTY_ID = ' || TO_CHAR (x_rel_party_id));
                     DBMS_OUTPUT.
                      put_line ('X_REL_ID = ' || TO_CHAR (x_rel_id));
                     DBMS_OUTPUT.
                      put_line (
                        'X_ORG_CONTACT_ID = ' || TO_CHAR (x_org_contact_id));
                     DBMS_OUTPUT.
                      put_line (
                        'X_PARTY_SITE_ID = ' || TO_CHAR (x_party_site_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_VENDOR_CONTACT_ID = '
                        || TO_CHAR (x_vendor_contact_id));
                     fnd_file.
                      put_line (
                        Fnd_File.LOG,
                        'X_PER_PARTY_ID = ' || TO_CHAR (x_per_party_id));
                     fnd_file.
                      put_line (
                        Fnd_File.LOG,
                        'X_REL_PARTY_ID = ' || TO_CHAR (x_rel_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;

                     UPDATE XXST_AP_SUPP_INT_STG
                        SET status = 'P'
                      WHERE     supplier_name = k.supplier_name
                            AND Batch_No = k.Batch_No
                            AND SITE_NAME = k.SITE_NAME;

                     gn_uploaded_rec := NVL (gn_uploaded_rec, 0) + 1;
                     count_ins_cont_records :=
                        NVL (count_ins_cont_records, 0) + 1;
                     COMMIT;
                  EXCEPTION
                     WHEN OTHERS
                     THEN
                        fnd_file.
                         put_line (
                           fnd_file.LOG,
                           'ErrNo:35 Data Insertion Error for Contacts');
                        lc_error_message_server := 'Error';   -- DBMS.SQLERRM;
                        XXST_INT_UTILITY_PKG.
                         generate_error_log_prc (p_batch_no,
                                                 ln_conc_req_id,
                                                 'XXST_AP_SUPP_INT_STG',
                                                 'Server Error',
                                                 'Server Error',
                                                 k.supplier_name,
                                                 '',
                                                 lc_error_message_server,
                                                 k.RECORD_ID);

                        UPDATE XXST_AP_SUPP_INT_STG
                           SET STATUS = 'E',
                               ERROR_MESSAGE = lc_error_message_server
                         WHERE     supplier_name = k.supplier_name
                               AND Batch_No = k.Batch_No
                               AND SITE_NAME = k.SITE_NAME
                               AND record_id = k.record_id;
                  END;

                  lc_error_flag1 := 'N';
               END IF;

               lc_error_flag1 := 'N';
            END IF;
         END;

         lc_error_flag1 := 'N';
      END LOOP;

      COMMIT;
      /*v_request_id3 :=
         fnd_request.submit_request ('SQLAP',
                                     'APXSCIMP',
                                     '',
                                     '',
                                     FALSE,
                                     'ALL',
                                     '1000',
                                     'N',
                                     'N',
                                     'N');
      COMMIT;

      IF fnd_concurrent.wait_for_request (request_id   => v_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;
         COMMIT;
      END IF;*/

      v_request_id5 :=
         fnd_request.submit_request ('XXFTAL',
                                     'XXFTAL_AP_SUPP_JA_IN_INT',
                                     '',
                                     '',
                                     FALSE,
                                     p_batch_no);
      COMMIT;

      /* FOR err2 IN lcur_error2
       LOOP
          BEGIN
             UPDATE XXST_AP_SUPP_INT_STG
                SET status = 'R', error_message = err2.reject_code
              WHERE     supplier_name = err2.segment1
                    AND site_name = err2.vendor_site_code
                    AND last_name = err2.last_name
                    AND Batch_No = p_batch_no;

             COMMIT;
          EXCEPTION
             WHEN OTHERS
             THEN
                NULL;
          END;
       END LOOP;*/

      -- 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 = p_batch_no AND conc_request_id = ln_conc_req_id;

      COMMIT;*/
      ln_after_total_Contact_count := 0;

      BEGIN
         SELECT COUNT (*)
           INTO ln_after_total_Contact_count
           FROM PO_VENDOR_CONTACTS;
      EXCEPTION
         WHEN OTHERS
         THEN
            NULL;
      END;

      BEGIN
         SELECT COUNT (*)
           INTO gn_errored_rec
           FROM XXST_AP_SUPP_INT_STG
          WHERE Batch_No = P_BATCH_NO AND ERROR_MESSAGE IS NOT NULL;
      EXCEPTION
         WHEN OTHERS
         THEN
            NULL;
      END;

      BEGIN
         SELECT COUNT (*)
           INTO gn_processed_rec
           FROM XXST_AP_SUPP_INT_STG
          WHERE Batch_No = P_BATCH_NO;
      EXCEPTION
         WHEN OTHERS
         THEN
            NULL;
      END;

      BEGIN
         SELECT COUNT (*)
           INTO gn_uploaded_rec
           FROM XXST_AP_SUPP_INT_STG
          WHERE Batch_No = P_BATCH_NO AND STATUS = 'P';
      EXCEPTION
         WHEN OTHERS
         THEN
            NULL;
      END;

      fnd_file.
       put_line (
         fnd_file.OUTPUT,
         '-------------------------------------------------------------------------------------------------');
      fnd_file.
       put_line (
         fnd_file.OUTPUT,
         '                                  Supplier Site Contact Conversion                                      ');
      fnd_file.
       put_line (
         fnd_file.OUTPUT,
         '-------------------------------------------------------------------------------------------------');
      fnd_file.
       put_line (
         fnd_file.OUTPUT,
         'Total Suppliers Site Contacts  Exists in EBS Before Submitting this Batch  :'
         || ln_bef_total_Contact_count);
      fnd_file.
       put_line (
         fnd_file.OUTPUT,
         'Total Suppliers Site Contacts  Exists in EBS After Submitting this Batch   :'
         || ln_after_total_Contact_count);
      fnd_file.
       put_line (
         fnd_file.OUTPUT,
         '-------------------------------------------------------------------------------------------------');
      fnd_file.
       put_line (fnd_file.OUTPUT,
                 'Total Records Processed : ' || ' ' || gn_processed_rec);
      fnd_file.
       put_line (fnd_file.OUTPUT,
                 'Total Records Rejected  : ' || ' ' || gn_errored_rec);
      fnd_file.
       put_line (fnd_file.OUTPUT,
                 'Total Records Inserted  : ' || ' ' || gn_uploaded_rec);

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

      COMMIT;
      fnd_file.
       put_line (
         fnd_file.OUTPUT,
         '-------------------------------------------------------------------------------------------------');
   END ap_supp_contacts_creation;

   ------------ Localization Supplier information
   PROCEDURE ap_localization_data_creation (
      errBuff         OUT NOCOPY VARCHAR2,
      retCode         OUT NOCOPY NUMBER,
      p_batch_no   IN            VARCHAR2)
   AS
      CURSOR lcur_ja_in
      IS
           SELECT pv.vendor_id,                         --pvsa.vendor_site_id,
                                                      --pvsa.vendor_site_code,
                   a.*
             FROM xxst_ap_supp_int_stg a,
                  ap_suppliers pv,                            --po_vendors pv,
                  ap_supplier_sites_all pvsa        --po_vendor_sites_all pvsa
            WHERE a.supplier_name = pv.vendor_name
                  AND (pv.vendor_id = pvsa.vendor_id
                       AND pvsa.vendor_site_code = a.site_name)
                  AND a.Batch_No = p_batch_no
         ORDER BY a.supplier_name;

      CURSOR lcur_ja_in1
      IS
         SELECT *
           FROM xxst_ap_supp_int_stg
          WHERE status = 'P' AND batch_no = p_batch_no;

      l_vendor_site_id             NUMBER;
      l_created_by                 fnd_user.user_id%TYPE;
      l_vendor_id                  NUMBER;
      ln_vendor_count              NUMBER;
      v_err_msg                    VARCHAR2 (9000);
      record_identi                VARCHAR2 (2000);
      lc_service_code              VARCHAR2 (30);
      lc_tax_category_list         VARCHAR2 (50);
      l_vendor_tds                 fnd_lookup_values.lookup_code%TYPE;
      lc_error_flag4               VARCHAR2 (1) := '';
      lc_error_message             VARCHAR2 (2000) := '';
      ln_login_id                  NUMBER;
      ln_conc_req_id               NUMBER;
      ln_user_id                   NUMBER;
      ln_last_updated_by           NUMBER; -- derived using FND_GLOBAL package
      ln_set_of_book_id            NUMBER;
      ln_vendor_site_flag          NUMBER := 0;
      ln_vendor_site_Num           NUMBER;
      ln_insert_flag               NUMBER := 0;
      Ln_Total_Records_received    NUMBER;
      Ln_Total_Records_Processed   NUMBER;
      Ln_Total_Records_Errored     NUMBER;
      Ln_vendor_id1                NUMBER;
      Ln_vendor_site_id1           NUMBER;
   BEGIN
      g_pn_batch_no1 := p_batch_no;
      fnd_file.
       put_line (
         fnd_file.OUTPUT,
         '=================================================================================');
      fnd_file.
       put_line (fnd_file.OUTPUT,
                 'Supplier Additional Info Conversion Details');
      fnd_file.
       put_line (
         fnd_file.OUTPUT,
         '=================================================================================');
      fnd_file.
       put_line (fnd_file.OUTPUT, 'Batch No  :' || p_batch_no);
      ln_Total_Records_received := 0;

      BEGIN
         SELECT COUNT (*)
           INTO Ln_Total_Records_received
           FROM xxst_ap_supp_int_stg tkapi,
                ap_suppliers pv,                              --po_vendors pv,
                ap_supplier_sites_all pvsa          --po_vendor_sites_all pvsa
          WHERE tkapi.supplier_name = pv.vendor_name
                AND (pv.vendor_id = pvsa.vendor_id
                     AND pvsa.vendor_site_code = tkapi.site_name)
                AND Batch_No = p_batch_no;
      EXCEPTION
         WHEN OTHERS
         THEN
            fnd_file.
             put_line (fnd_file.LOG, 'Error while Getting Total Count');
      END;

      fnd_file.
       put_line (
         fnd_file.OUTPUT,
         'Total No of Records Received ' || Ln_Total_Records_received);

      SELECT DECODE (TRIM (p_batch_no), 'ALL', NULL, TO_NUMBER (p_batch_no))
        INTO pn_batch_no
        FROM DUAL;

      l_created_by := fnd_profile.VALUE ('USER_ID');
      -- To get the User Id
      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
      FOR vendor_rec IN lcur_ja_in
      LOOP
         EXIT WHEN lcur_ja_in%NOTFOUND;
         l_vendor_site_id := 0;
         l_vendor_id := NULL;
         ln_vendor_count := NULL;
         lc_error_flag4 := '';
         lc_error_message := '';
         ln_insert_flag := 0;
         gn_processed_rec := NVL (gn_processed_rec, 0) + 1;

         BEGIN
            SELECT vendor_id
              INTO l_vendor_id
              FROM ap_suppliers                                   --po_vendors
             WHERE UPPER (vendor_name) = UPPER (vendor_rec.supplier_name);
         EXCEPTION
            WHEN OTHERS
            THEN
               lc_error_flag4 := 'Y';
               lc_error_message :=
                  ' Supplier not found ' || vendor_rec.supplier_name;
               fnd_file.
                put_line (fnd_file.LOG,
                          'ErrNo:36' || lc_error_message || SQLERRM);

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

               --WHERE RECORD_ID=vendor_rec.RECORD_ID;
               COMMIT;
         END;

         IF l_vendor_id IS NOT NULL
         THEN
            BEGIN
               ln_vendor_count := 0;

               SELECT COUNT (*)
                 INTO ln_vendor_count
                 FROM JAI_CMN_VENDOR_SITES             --ja_in_po_vendor_sites
                WHERE vendor_id = l_vendor_id AND vendor_site_id = 0; --ADDED BY XXXX
            EXCEPTION
               WHEN NO_DATA_FOUND
               THEN
                  ln_vendor_count := 0;
               WHEN OTHERS
               THEN
                  NULL;
            END;
         END IF;

         l_vendor_tds := NULL;

         IF TRIM (vendor_rec.type_of_assessee) IS NOT NULL
         THEN
            BEGIN
               SELECT lookup_code
                 INTO l_vendor_tds
                 FROM fnd_lookup_values
                WHERE UPPER (meaning) =
                         UPPER (TRIM (vendor_rec.type_of_assessee))
                      AND lookup_type = 'JAI_TDS_VENDOR_TYPE';
            EXCEPTION
               WHEN OTHERS
               THEN
                  fnd_file.put_line (fnd_file.LOG, 'CNo:40');

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

                  --WHERE record_id=vendor_rec.record_id;
                  COMMIT;
            END;
         END IF;

         IF lc_error_flag4 = 'Y'
         THEN
            gn_errored_rec := NVL (gn_errored_rec, 0) + 1;
         END IF;

         --IF NVL(ln_vendor_count,0) = 0 THEN
         IF ln_vendor_count = 0
         THEN
            BEGIN
               count_new_addl_records := NVL (count_new_addl_records, 0) + 1;

               --COMENTED BY SIVA ON 26-DEC-2012
               /* INSERT INTO ja_in_po_vendor_sites (vendor_id,
                                                   vendor_site_id,
                                                   excise_duty_range,
                                                   excise_duty_division,
                                                   excise_duty_reg_no,
                                                   excise_duty_zone,
                                                   excise_duty_region,
                                                   excise_duty_circle,
                                                   ec_code,
                                                   cst_reg_no,
                                                   st_reg_no,
                                                   approved_invoice_flag,
                                                   vendor_type,
                                                   override_flag,
                                                   vat_reg_no,
                                                   service_tax_regno,
                                                   service_type_code,
                                                   tax_category_list,
                                                   creation_date,
                                                   created_by,
                                                   last_update_date,
                                                   last_updated_by)
                     VALUES (vendor_rec.vendor_id,
                             0,                          --ln_vendor_site_id=0,
                             NULL,           --trim(vendor_rec.exc_duty_range),
                             NULL,        --trim(vendor_rec.exc_duty_division),
                             TRIM (vendor_rec.EXCISE_REGISTRATION_NO),
                             NULL,            --trim(vendor_rec.exc_duty_zone),
                             NULL,          --trim(vendor_rec.exc_duty_region),
                             NULL,          --trim(vendor_rec.exc_duty_circle),
                             NULL,                  --trim(vendor_rec.ec_code),
                             TRIM (vendor_rec.central_sales_tax_no),
                             'NA', --null,--trim(vendor_rec.lst_registration_no),
                             'Y',
                             NULL,         --trim(vendor_rec.type_of_assessee),
                             'Y',
                             TRIM (vendor_rec.value_added_tax_no),
                             TRIM (vendor_rec.service_tax_no),
                             TRIM (lc_service_code),
                             NULL,        --trim(vendor_rec.tax_category_list),
                             SYSDATE,
                             l_created_by,
                             SYSDATE,
                             l_created_by);*/

               --ADDED BY XXXXX
               INSERT INTO jai_cmn_vendor_sites (vendor_id,
                                                 vendor_site_id,
                                                 excise_duty_range,
                                                 excise_duty_division,
                                                 excise_duty_reg_no,
                                                 excise_duty_zone,
                                                 excise_duty_region,
                                                 excise_duty_circle,
                                                 ec_code,
                                                 cst_reg_no,
                                                 st_reg_no,
                                                 approved_invoice_flag,
                                                 vendor_type,
                                                 override_flag,
                                                 vat_reg_no,
                                                 service_tax_regno,
                                                 service_type_code,
                                                 tax_category_list,
                                                 creation_date,
                                                 created_by,
                                                 last_update_date,
                                                 last_updated_by)
                    VALUES (vendor_rec.vendor_id,
                            0,                          --ln_vendor_site_id=0,
                            NULL,           --trim(vendor_rec.exc_duty_range),
                            NULL,        --trim(vendor_rec.exc_duty_division),
                            TRIM (vendor_rec.EXCISE_REGISTRATION_NO),
                            NULL,            --trim(vendor_rec.exc_duty_zone),
                            NULL,          --trim(vendor_rec.exc_duty_region),
                            NULL,          --trim(vendor_rec.exc_duty_circle),
                            NULL,                  --trim(vendor_rec.ec_code),
                            TRIM (vendor_rec.central_sales_tax_no),
                            'NA', --null,--trim(vendor_rec.lst_registration_no),
                            'Y',
                            NULL,         --trim(vendor_rec.type_of_assessee),
                            'Y',
                            TRIM (vendor_rec.value_added_tax_no),
                            TRIM (vendor_rec.service_tax_no),
                            TRIM (lc_service_code),
                            NULL,        --trim(vendor_rec.tax_category_list),
                            SYSDATE,
                            l_created_by,
                            SYSDATE,
                            l_created_by);

               gn_uploaded_rec := NVL (gn_uploaded_rec, 0) + 1;
               count_ins_addl_records := NVL (count_ins_addl_records, 0) + 1;
               ln_insert_flag := ln_insert_flag + 1;

               UPDATE XXST_AP_SUPP_INT_STG
                  SET status = 'P'
                WHERE     supplier_name = vendor_rec.supplier_name
                      AND Batch_No = vendor_rec.Batch_No
                      AND site_name = vendor_rec.site_name;

               COMMIT;
            EXCEPTION
               WHEN OTHERS
               THEN
                  lc_error_flag4 := 'Y';
                  lc_error_message :=
                     ' Error Occured While inserting into JA_IN_PO_VENDOR_SITES tables  ';
                  fnd_file.
                   put_line (fnd_file.LOG,
                             'ErrNo:41' || lc_error_message || SQLERRM);
                  XXST_INT_UTILITY_PKG.
                   GENERATE_ERROR_LOG_PRC (pn_batch_no,
                                           ln_conc_req_id,
                                           'XXST_AP_SUPP_INT_STG',
                                           'JA_IN_PO_VENDOR_SITES',
                                           'JA_IN_PO_VENDOR_SITES',
                                           vendor_rec.supplier_name,
                                           vendor_rec.vendor_id,
                                           lc_error_message,
                                           vendor_rec.RECORD_ID);
                  fnd_file.
                   put_line (fnd_file.LOG, lc_error_message);
                  fnd_file.put_line (fnd_file.LOG, 'Update');

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

                  COMMIT;
            END;

            IF l_vendor_tds IS NOT NULL
            THEN
               IF vendor_rec.pan_no IS NOT NULL
                  AND LENGTH (TRIM (vendor_rec.pan_no)) = 10
               THEN
                  BEGIN
                     --COMENTED BY SIVA ON 26-DEC-2012
                     /*INSERT
                       INTO ja_in_vendor_tds_info_hdr (
                               vendor_id,
                               vendor_site_id,
                               pan_no,
                               tan_no,
                               ward_no,
                               creation_date,
                               created_by,
                               last_update_date,
                               last_updated_by,
                               last_update_login,
                               confirm_pan_flag,
                               tds_vendor_type_lookup_code)
                     VALUES (vendor_rec.vendor_id,
                             0,
                             vendor_rec.pan_no,
                             NULL,                        --vendor_rec.tan_no,
                             NULL,                       --vendor_rec.ward_no,
                             SYSDATE,
                             l_created_by,
                             SYSDATE,
                             l_created_by,
                             l_created_by,
                             'Y',
                             l_vendor_tds);*/

                     --ADDED BY XXXX
                     INSERT
                       INTO jai_ap_tds_vendor_hdrs (
                               vendor_id,
                               vendor_site_id,
                               pan_no,
                               tan_no,
                               ward_no,
                               creation_date,
                               created_by,
                               last_update_date,
                               last_updated_by,
                               last_update_login,
                               confirm_pan_flag,
                               tds_vendor_type_lookup_code)
                     VALUES (vendor_rec.vendor_id,
                             0,
                             vendor_rec.pan_no,
                             NULL,                        --vendor_rec.tan_no,
                             NULL,                       --vendor_rec.ward_no,
                             SYSDATE,
                             l_created_by,
                             SYSDATE,
                             l_created_by,
                             l_created_by,
                             'Y',
                             l_vendor_tds);

                     ln_insert_flag := ln_insert_flag + 1;

                     UPDATE XXST_AP_SUPP_INT_STG
                        SET status = 'P'
                      WHERE     supplier_name = vendor_rec.supplier_name
                            AND Batch_No = vendor_rec.Batch_No
                            AND site_name = vendor_rec.site_name;

                     COMMIT;
                  EXCEPTION
                     WHEN OTHERS
                     THEN
                        lc_error_flag4 := 'Y';
                        lc_error_message :=
                           'Error Occured While inserting into JA_IN_VENDOR_TDS_INFO_HDR tables ';
                        fnd_file.
                         put_line (fnd_file.LOG,
                                   'ErrNo:42' || lc_error_message || SQLERRM);

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

                        --WHERE record_id=vendor_rec.record_id;
                        COMMIT;
                  END;
               END IF;                         --vendor_rec.pan_no IS NOT NULL
            END IF;                            --l_vendor_tds IS NOT NULL THEN
         END IF;                          --IF NVL(ln_vendor_count,0) = 0 THEN

         IF l_vendor_id IS NOT NULL
         THEN
            BEGIN
               ln_vendor_site_num := 0;

               SELECT vendor_site_id
                 INTO ln_vendor_site_num
                 FROM jai_cmn_vendor_sites             --JA_IN_PO_VENDOR_SITES
                WHERE vendor_id = l_vendor_id
                      AND vendor_site_id = vendor_rec.vendor_site_id;
            EXCEPTION
               WHEN NO_DATA_FOUND
               THEN
                  ln_vendor_site_num := 0;
               WHEN OTHERS
               THEN
                  NULL;
            END;

            NULL;
         END IF;




For Supplier Interface Script in Oracle apps Part 4 Click here

 

No comments:

Post a Comment