Inactive Duplicate vendor contact records in R12.2.7

Overview:

I) The Duplicate data is there when CIL instance got upgraded from 11i to R12 and during upgrade process the contacts data got duplicated because of underlying data structure changes in R12 version.

II)      Oracle offers help on certain point to get the GDF which fixes one off scenario.

Inactive duplicate vendor contact records: Using below package we update Inactive Date field of PO_VENDOR_CONTACTS or AP_SUPPLIER_CONTACTS view using standard API: AP_VENDOR_PUB_PKG.UPDATE_VENODR_CONTACT 

1) Package Specification:

CREATE OR REPLACE PACKAGE APPS.xxc8837_inactive_dupl_vendor

     AS

      /************************************************************

        PURPOSE:    Package used for inactive duplicate supplier contact

        REVISIONS:

        Ver          Date                    Author             Description

        ---         -------                --------         -----------

        1.0          06-May-2019      Sunil Ingole      Inactive duplicate supplier contact in r12.

        PARAMETERS:

        INPUT:1) p_org_name              IN       VARCHAR2

                     2) p_vendor_number    IN       VARCHAR2

                     3) p_vendor_site_code IN       VARCHAR2

    ********************************************************************** */

   PROCEDURE XXPO_INACTIVE_DUPLI_VEN_CONT (

      p_errbuf             OUT      VARCHAR2,

      p_retcode            OUT      NUMBER,

      p_org_name           IN       VARCHAR2,

      p_vendor_number      IN       VARCHAR2,

      p_vendor_site_code   IN       VARCHAR2

      );

   END XXPO_INACTIVE_DUPLI_VEN_CONT;

    /

2) Package Body:

CREATE OR REPLACE PACKAGE BODY APPS.XXPO_INACTIVE_DUPLI_VEN_CONT

AS

  PROCEDURE inactive_dupl_ven (

    p_errbuf             OUT      VARCHAR2,

    p_retcode            OUT      NUMBER,

    p_org_name           IN       VARCHAR2,

    p_vendor_number      IN       VARCHAR2,

    p_vendor_site_code   IN       VARCHAR2)

  IS

    CURSOR lcur_check_duplicate_rec IS

     SELECT COUNT (*), povc.vendor_site_id, povc.prefix, povc.title,

      povc.first_name, povc.middle_name, povc.last_name,

   assa.org_id, assa.vendor_site_code, asup.vendor_name, asup.segment1

   -- add field here, whose declare duplicate contact record

     FROM apps.ap_suppliers asup,

      apps.ap_supplier_sites_all assa,

      apps.po_vendor_contacts povc

     WHERE asup.vendor_id = assa.vendor_id

      AND assa.vendor_site_id = povc.vendor_site_id

      AND (asup.end_date_active IS NULL OR asup.end_date_active > SYSDATE)

      AND (povc.inactive_date IS NULL OR povc.inactive_date > SYSDATE)

      AND assa.org_id = p_org_name

      AND asup.segment1 = NVL (p_vendor_number, asup.segment1)

      AND assa.vendor_site_code = NVL (p_vendor_site_code, assa.vendor_site_code)

     GROUP BY povc.vendor_site_id,

      povc.prefix,

      povc.title,

      povc.first_name,

      povc.middle_name,

      povc.last_name,

      assa.org_id,

      assa.vendor_site_code,

      asup.vendor_name,

      asup.segment1

    HAVING COUNT (*) > 1;

    --

    CURSOR lcur_cont_details (p_first_name       VARCHAR2,

       p_middle_name      VARCHAR2,

       p_last_name        VARCHAR2,

       p_prefix           VARCHAR2,

       p_title            VARCHAR2,

       p_vendor_site_id   NUMBER,

       p_org_id           NUMBER,

       p_ven_number       VARCHAR2,

       p_ven_site_code    VARCHAR2)

    IS

      SELECT DISTINCT povc.first_name, povc.middle_name, povc.last_name,

       povc.per_party_id, povc.relationship_id,

       povc.rel_party_id, povc.party_site_id,

       povc.org_contact_id, povc.org_party_site_id,

       povc.vendor_contact_id, povc.prefix, povc.title,

       povc.attribute1, povc.attribute15, povc.url,

       povc.email_address, apss.vendor_site_code,

       aps.segment1, apss.org_id, apss.vendor_site_id, aps.vendor_id

      FROM apps.po_vendor_contacts povc,

        apps.ap_suppliers aps,

        apps.ap_supplier_sites_all apss

      WHERE aps.vendor_id = apss.vendor_id

        AND apss.vendor_site_id = povc.vendor_site_id

        AND apss.org_id = p_org_id

        AND NVL (povc.first_name, 'N') = NVL (p_first_name, 'N')

        AND NVL (povc.middle_name, 'N') = NVL (p_middle_name, 'N')

        AND NVL (povc.last_name, 'N') = NVL (p_last_name, 'N')

        AND NVL (povc.prefix, 'N') = NVL (p_prefix, 'N')

        AND NVL (povc.title, 'N') = NVL (p_title, 'N')

        AND NVL (aps.segment1, 'N') = NVL (p_ven_number, 'N')

        AND NVL (apss.vendor_site_code, 'N') = NVL (p_ven_site_code, 'N')

        AND apss.vendor_site_id = p_vendor_site_id

        AND (povc.inactive_date IS NULL OR povc.inactive_date > SYSDATE);

      -- Declare Variables

      ln_cont_count                  NUMBER;

      ln_po_count                    NUMBER;

      lc_po_number                   VARCHAR2 (100);

      ln_success_record_cnt          NUMBER;

      l_chr_return_status            VARCHAR2 (2000);

      l_num_msg_count                NUMBER;

      l_chr_msg_data                 LONG;

      l_msg                          VARCHAR2 (700);

      p_api_version                  NUMBER;

      p_commit                       VARCHAR2 (200);

      x_return_status                VARCHAR2 (200);

      x_msg_count                    NUMBER;

      x_msg_data                     VARCHAR2 (200);

      lrec_vend_cont_associatn_rec   ap_vendor_pub_pkg.r_vendor_contact_rec_type;

   --

   BEGIN

    --

    FOR lreq_info IN lcur_check_duplicate_rec

    LOOP

     --

     ln_success_record_cnt := 0;

     FOR lreq_detail IN lcur_cont_details (lreq_info.first_name,

                                             lreq_info.middle_name,

                                             lreq_info.last_name,

                                             lreq_info.prefix,

                                             lreq_info.title,

                                             lreq_info.vendor_site_id,

                                             lreq_info.org_id,

                                             lreq_info.segment1,

                                             lreq_info.vendor_site_code)

    LOOP

      ln_cont_count := 0;

      ln_po_count := 0;

      lc_po_number := NULL;

      -- Check vendor contact is used or not in Existing PO.

      -- if any vendor contact already used in PO, then don't inactive that vendor contact.

      BEGIN

        SELECT COUNT (*) INTO ln_po_count

          FROM apps.po_headers_all

         WHERE vendor_contact_id = lreq_detail.vendor_contact_id

    AND vendor_id = lreq_detail.vendor_id

    AND vendor_site_id = lreq_detail.vendor_site_id

          AND (end_date_active IS NULL OR end_date_active > SYSDATE)

          AND (end_date IS NULL OR end_date > SYSDATE)

          AND authorization_status = 'APPROVED';

  

        SELECT DISTINCT segment1 INTO lc_po_number

          FROM apps.po_headers_all

         WHERE vendor_contact_id = lreq_detail.vendor_contact_id

          AND (   end_date_active IS NULL OR end_date_active > SYSDATE)

          AND (end_date IS NULL OR end_date > SYSDATE)

          AND authorization_status = 'APPROVED';

         EXCEPTION

            WHEN NO_DATA_FOUND THEN NULL;

            WHEN OTHERS THEN NULL;

         END;

         --

        BEGIN

         SELECT COUNT (*) INTO ln_cont_count

          FROM po_vendor_contacts pvc,

               ap_suppliers aps,

               ap_supplier_sites_all apss

         WHERE aps.vendor_id = apss.vendor_id

           AND apss.vendor_site_id = pvc.vendor_site_id

           AND aps.segment1 = lreq_detail.segment1

           AND (pvc.inactive_date IS NULL OR pvc.inactive_date > SYSDATE)

                AND apss.vendor_site_code = lreq_detail.vendor_site_code

                AND apss.org_id = lreq_detail.org_id

                AND NVL (UPPER (TRIM (pvc.first_name)), '~') = NVL (UPPER (TRIM (lreq_detail.first_name)), '~')

                AND NVL (UPPER (TRIM (pvc.last_name)), '~')  = NVL (UPPER (TRIM (lreq_detail.last_name)), '~')

                AND NVL (UPPER (TRIM (pvc.prefix)), '~') = NVL (UPPER (TRIM (lreq_detail.prefix)), '~')

                AND NVL (UPPER (TRIM (pvc.title)), '~') = NVL (UPPER (TRIM (lreq_detail.title)), '~')

             HAVING COUNT (*) > 1;

          EXCEPTION

             WHEN OTHERS THEN NULL;

          END;

  

        IF (ln_cont_count > 1) THEN -- INACTIVE THIS VENDOR SITES

          BEGIN

           lrec_vend_cont_associatn_rec.per_party_id := lreq_detail.per_party_id;

           lrec_vend_cont_associatn_rec.relationship_id := lreq_detail.relationship_id;

           lrec_vend_cont_associatn_rec.rel_party_id := lreq_detail.rel_party_id;

           lrec_vend_cont_associatn_rec.party_site_id := lreq_detail.party_site_id;

           lrec_vend_cont_associatn_rec.org_party_site_id := lreq_detail.org_party_site_id;

           lrec_vend_cont_associatn_rec.org_contact_id := lreq_detail.org_contact_id;

           lrec_vend_cont_associatn_rec.vendor_contact_id := lreq_detail.vendor_contact_id;

           lrec_vend_cont_associatn_rec.vendor_site_id := lreq_detail.vendor_site_id;

           lrec_vend_cont_associatn_rec.inactive_date := SYSDATE;

           lrec_vend_cont_associatn_rec.attribute1 := lreq_detail.attribute1;

           lrec_vend_cont_associatn_rec.attribute15 := lreq_detail.attribute15;

           lrec_vend_cont_associatn_rec.url := lreq_detail.url;

           lrec_vend_cont_associatn_rec.email_address := lreq_detail.email_address;

           lrec_vend_cont_associatn_rec.org_id := lreq_detail.org_id;

           lrec_vend_cont_associatn_rec.vendor_site_code := lreq_detail.vendor_site_code;

           --

           IF (ln_po_count > 0) THEN

              fnd_file.put_line (fnd_file.LOG, '---* Vendor contact ID: '

                                 || lreq_detail.vendor_contact_id

                                 || ', used in PO Number: '

                                 || lc_po_number);

           ELSE

              ap_vendor_pub_pkg.update_vendor_contact

                 (p_api_version             => 1,

                  p_commit                  => 'T',

                  p_vendor_contact_rec      => lrec_vend_cont_associatn_rec,

                  x_return_status           => l_chr_return_status,

                  x_msg_count               => l_num_msg_count,

                  x_msg_data                => l_chr_msg_data);

              COMMIT;

              --

              IF (l_chr_return_status <> fnd_api.g_ret_sts_success)

              THEN

                 FOR i IN 1 .. fnd_msg_pub.count_msg

                 LOOP

                    l_msg := fnd_msg_pub.get (p_msg_index      => i,

                             p_encoded        => fnd_api.g_false);

                 END LOOP;

              ELSE

                 fnd_file.put_line

                    (fnd_file.LOG,'The API(Inactive) call ended with SUCESSS status for First_name:'

                     || lreq_detail.first_name

                     || ', Vendor site id:'

                     || lreq_detail.vendor_site_id);

              END IF;

              --

              ln_success_record_cnt := ln_success_record_cnt + 1;

           END IF;

        EXCEPTION

          WHEN OTHERS THEN

           fnd_file.put_line (fnd_file.LOG, 'Error at inactive site:' || SQLERRM);

        END;

        END IF;

      END LOOP;

      --

      END LOOP;

   EXCEPTION

      WHEN OTHERS THEN

      fnd_file.put_line(fnd_file.LOG,'--Error in main procedure: ' || SQLERRM);

   END;

END XXPO_INACTIVE_DUPLI_VEN_CONT;

/