API to Inactivate Supplier Sites which is not used for last two years

Table Script is used to capture the Vendor site and their  API updated return status

CREATE TABLE XX_TMP_VENDOR_SITES
(
   vendor_site_id      NUMBER,
   return_status       VARCHAR2 (10),
   msg_count           NUMBER,
   msg_data            VARCHAR2 (1000),
   LAST_UPDATE_DATE    DATE NOT NULL ENABLE,
   LAST_UPDATED_BY     NUMBER (15, 0) NOT NULL ENABLE,
   CREATION_DATE       DATE NOT NULL ENABLE,
   CREATED_BY          NUMBER (15, 0) NOT NULL ENABLE,
   LAST_UPDATE_LOGIN   NUMBER (15, 0)
)


--------------------------------------------------------------------------------*/

set serveroutput on size 100000000;

DECLARE
   l_vendor_site_rec   AP_VENDOR_PUB_PKG.r_vendor_site_rec_type;
   l_vendor_rec        AP_VENDOR_PUB_PKG.r_vendor_rec_type;
   x_vendor_site_id    NUMBER;
   x_vendor_id         NUMBER;
   x_msg_data          VARCHAR2 (1000);
   x_msg_count         NUMBER;
   p_count             NUMBER;
   x_return_status     VARCHAR2 (10);
   V_MSG_INDEX_OUT     NUMBER;
   L_RESP_ID           NUMBER;
   L_CNT               NUMBER;
   Lf_CNT              NUMBER;
   P_DATE              VARCHAR2 (30) := '01-MAR-17';                 --:P_DATE

   CURSOR lcu_rec
   IS
        SELECT assa1.vendor_id,
               assa1.vendor_site_id,
               aps1.SEGMENT1,
               aps1.vendor_name,
               assa1.VENDOR_SITE_CODE,
               aps1.vendor_type_lookup_code,
               assa1.inactive_date,
               org_id
          FROM ap_suppliers aps1, ap_supplier_sites_all assa1
         WHERE     1 = 1
               --and aps1.segment1 = 216
               AND aps1.VENDOR_ID = assa1.VENDOR_ID
               AND aps1.END_DATE_ACTIVE IS NULL
               AND assa1.INACTIVE_DATE IS NULL
               AND aps1.vendor_type_lookup_code NOT IN
                        ('ACCEPTANCESLC',
                         'EL_DIVISION',
                         'EMPLOYEE',
                         'GOVT_DUES',
                         'GROUP_COMPANY',
                         'HUNDI',
                         'INDIA CUSTOMS AUTHORITY ',
                         'INDIA TAX AUTHORITY',
                         'INDIA TCS AUTHORITY',
                         'INDIA TDS AUTHORITY',
                         'INDIA EXCISE AUTHORITY',
                         'INSURANCE',
                         'SALARY',
                         'STATUTORY',
                         'SUBSCRIPTION')
               AND NOT EXISTS
                     (  SELECT RCT.VENDOR_ID, RCT.VENDOR_SITE_ID
                          FROM RCV_SHIPMENT_HEADERS RCT,
                               ap_suppliers aps,
                               ap_supplier_sites_all assa
                         WHERE              --RCT.TRANSACTION_TYPE = 'RECEIVE'
                               --AND TRUNC(RCT.TRANSACTION_DATE) >='01-APR-2017'
                               RCT.CREATION_DATE >=
                                  TO_DATE ('01-MAR-2017'              --P_DATE     --- Which is not used after 01-Mar-17   --Hard coded reason
                                                        || ' 00:00:00',
                                           'DD-MM-YYYY hh24:mi:ss')
                               --                                  TO_DATE (
                               --                                     TO_CHAR ('01-MAR-2017'--P_DATE
                               --                                     , 'DD-MON-YYYY')
                               --                                     || ' 00:00:00',
                               --                                     'dd-mm-yyyy hh24:mi:ss')
                               AND RCT.VENDOR_ID = ASSA.VENDOR_ID
                               AND RCT.VENDOR_SITE_ID = ASSA.VENDOR_SITE_ID
                               AND aps.VENDOR_ID = assa.VENDOR_ID
                               --and aps.segment1 = 216
                               AND aps.vendor_id = aps1.vendor_id
                               AND ASSA.VENDOR_SITE_ID = ASSA1.VENDOR_SITE_ID
                      GROUP BY RCT.VENDOR_ID, RCT.VENDOR_SITE_ID)
      ORDER BY 1, 2;
BEGIN
   L_CNT := 0;
   Lf_CNT := 0;

   FOR i IN lcu_rec
   LOOP
 
   /*---------------------------------------------------------------------------------------
   MO Profile option was enabled. So Responsibility Id has been hard coded using this below query
 
    SELECT frv.responsibility_ID,
         frv.responsibility_name,
         fpov.profile_option_value org_id,
         hou.NAME
    FROM apps.fnd_profile_options_vl fpo,
         apps.fnd_responsibility_Vl frv,
         apps.fnd_profile_option_values fpov,
         apps.hr_organization_units hou
   WHERE     1 = 1      --AND hou.ORGANIZATION_ID =84
         AND fpov.profile_option_value = TO_CHAR (hou.organization_id)
         AND fpo.profile_option_id = fpov.profile_option_id
         AND fpo.user_profile_option_name = 'MO: Operating Unit'
         AND frv.responsibility_id = fpov.level_value
         AND frv.APPLICATION_ID = 200
         AND UPPER (frv.responsibility_name) LIKE '%PAYABLES%SUPER%USER'
ORDER BY frv.responsibility_name
 
 
   ----------------------------------------------------------------------------------------*/
 
 
      IF (i.ORG_ID = 83)
      THEN
         L_RESP_ID := 50653;
      ELSIF (i.ORG_ID = 84)
      THEN
         L_RESP_ID := 50654;
      ELSIF (i.ORG_ID = 86)
      THEN
         L_RESP_ID := 50656;
      ELSIF (i.ORG_ID = 85)
      THEN
         L_RESP_ID := 50655;
      ELSIF (i.ORG_ID = 82)
      THEN
         L_RESP_ID := 50657;
      ELSE
         L_RESP_ID := 55460;
      END IF;

      --      DBMS_OUTPUT.put_line ('L_RESP_ID = ' || L_RESP_ID);

      fnd_global.apps_initialize (12590, L_RESP_ID, 200);

      l_vendor_site_rec.vendor_id := i.vendor_id;               -- Supplier Id
      l_vendor_site_rec.org_id := i.org_id;               -- Operating Unit id
      x_vendor_site_id := i.vendor_site_id;           -- Site Id to be updated
      l_vendor_site_rec.last_update_date := SYSDATE;
      l_vendor_site_rec.last_updated_by := 12590;
      l_vendor_site_rec.inactive_date := SYSDATE;

      AP_VENDOR_PUB_PKG.Update_Vendor_Site (
         p_api_version       => 1,
         x_return_status     => x_return_status,
         x_msg_count         => x_msg_count,
         x_msg_data          => x_msg_data,
         p_vendor_site_rec   => l_vendor_site_rec,
         p_vendor_site_id    => x_vendor_site_id);
      COMMIT;

      --      DBMS_OUTPUT.put_line ('X_RETURN_STATUS = ' || x_return_status);
      --      DBMS_OUTPUT.put_line ('X_MSG_COUNT = ' || x_msg_count);
      --      DBMS_OUTPUT.put_line ('X_MSG_DATA = ' || x_msg_data);

      INSERT INTO XX_TMP_VENDOR_SITES
          VALUES (i.vendor_site_id,
                  x_return_status,
                  x_msg_count,
                  x_msg_data,
                  SYSDATE,
                  12590,
                  SYSDATE,
                  12590,
                  -1);

      COMMIT;

      IF (x_return_status = 'S')
      THEN
         L_CNT := L_CNT + 1;
      ELSE
         Lf_CNT := Lf_CNT + 1;
      --         DBMS_OUTPUT.put_line ('vendor_site_id = ' || i.vendor_site_id);
      END IF;


      L_RESP_ID := NULL;
   END LOOP;
END;
/

show error;