API to create party and link to the Supplier

Introduction / Issue

As part of TCA, the supplier needs to be linked to the party. This API establishes the link between the Party and the Supplier.

Why We Need to Do This / Cause of the Issue

If the link between the Party and the Supplier is broken, the supplier account becomes unusable and the details will not display properly in the UI.

How Do We Solve This

SET SERVEROUTPUT ON;
DECLARE
   l_party_site_rec      HZ_PARTY_SITE_V2PUB.PARTY_SITE_REC_TYPE;
   l_party_site_id       NUMBER;
   l_party_site_number   VARCHAR2(30);
   l_return_status       VARCHAR2(1);
   l_msg_count           NUMBER;
   l_msg_data            VARCHAR2(2000);
   lrt_vendor_site_rec                    ap_vendor_pub_pkg.r_vendor_site_rec_type;
   ln_api_version                    NUMBER := 1.0;
   x_return_status                   VARCHAR2(200);
   lv_init_msg_list                  VARCHAR2(200) := fnd_api.g_true;
   lv_msg                            VARCHAR2(4000);
   lv_commit                         VARCHAR2(200) := fnd_api.g_false;
   ln_validation_level               NUMBER := fnd_api.g_valid_level_full;
   x_msg_count                       NUMBER;
   x_msg_data                        VARCHAR2(4000);

   CURSOR C1 IS
   SELECT * FROM
   XX_SUPPLIER_SITE_BANK
   WHERE 1=1
   AND NEW_PARTY_SITE_FLAG = 'N';
BEGIN
   -- Initialize Apps Context
   fnd_global.apps_initialize
    (user_id      => 0,
     resp_id      => 12345,
     resp_appl_id => 222);
   mo_global.init('SQLAP');
   mo_global.set_policy_context('S', 123);

   FOR I IN C1 LOOP
      -- Mandatory Parameters
      l_party_site_rec.party_id                 := I.PARTY_ID; -- Existing Party ID
      l_party_site_rec.location_id              := I.NEW_LOCATION_ID; -- Existing Location ID
      l_party_site_rec.identifying_address_flag := 'Y';
      l_party_site_rec.created_by_module        := 'SUPPLIER_MIGRATION';
      l_party_site_rec.created_by_module := 'TCA_V2_API';
      l_party_site_rec.party_site_name    := i.site_name;
      l_party_site_rec.attribute_category := i.org_id;
      l_party_site_rec.attribute3 := i.supplier_number;

      HZ_PARTY_SITE_V2PUB.CREATE_PARTY_SITE
      (
         p_init_msg_list     => FND_API.G_TRUE,
         p_party_site_rec    => l_party_site_rec,
         x_party_site_id     => l_party_site_id,
         x_party_site_number => l_party_site_number,
         x_return_status     => l_return_status,
         x_msg_count         => l_msg_count,
         x_msg_data          => l_msg_data
      );

      IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
         UPDATE XX_SUPPLIER_SITE_BANK SET
         NEW_PARTY_SITE_ID = l_party_site_id,
         NEW_PARTY_SITE_NUMBER = l_party_site_number, new_party_Site_flag  = 'C'
         WHERE VENDOR_SITE_ID = I.VENDOR_SITE_ID;
         -- COMMIT
         BEGIN
            lrt_vendor_site_rec.vendor_id        := i.vendor_id;
            lrt_vendor_site_rec.party_site_id    := l_party_site_id;
            lrt_vendor_site_rec.org_id    := 123;

            AP_VENDOR_PUB_PKG.UPDATE_VENDOR_SITE(p_api_version         => ln_api_version,
            p_init_msg_list      => lv_init_msg_list,
            p_commit                 => lv_commit,
            p_validation_level  => ln_validation_level,
            x_return_status     => x_return_status,
            x_msg_count          => x_msg_count,
            x_msg_data            => x_msg_data,
            p_vendor_site_rec         => lrt_vendor_site_rec,
            p_vendor_site_id           => I.VENDOR_SITE_ID
            );

            DBMS_OUTPUT.put_line('API Status ' || x_return_status);
            FND_FILE.PUT_LINE(FND_FILE.LOG, 'API Status ' || x_return_status);

            IF x_return_status <> fnd_api.g_ret_sts_success THEN
               FOR i IN 1 .. fnd_msg_pub.count_msg LOOP
                  lv_msg := fnd_msg_pub.get(p_msg_index => i,p_encoded => fnd_api.g_false);
                  DBMS_OUTPUT.put_line('The API call failed with error ' || lv_msg);
                  FND_FILE.PUT_LINE(FND_FILE.LOG, 'The API call failed with error ' || lv_msg);
               END LOOP;
            ELSE
               DBMS_OUTPUT.put_line('Successfully Updated the Supplier details');
               FND_FILE.PUT_LINE(FND_FILE.LOG, 'Successfully Updated the Supplier details');
               --COMMIT;
            END IF;
         EXCEPTION WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('Error while updating the ap_supplier_sites');
            UPDATE XX_SUPPLIER_SITE_BANK
            SET  new_party_Site_flag = 'E', PARTY_SITE_ERROR = 'Error while updating the ap_supplier_sites'
            WHERE VENDOR_SITE_ID = I.VENDOR_SITE_ID;
         END;
      ELSE
         UPDATE XX_SUPPLIER_SITE_BANK
         SET  new_party_Site_flag = 'E', PARTY_SITE_ERROR = substr(l_msg_data,1,3999)
         WHERE VENDOR_SITE_ID = I.VENDOR_SITE_ID;
         ROLLBACK;
         DBMS_OUTPUT.PUT_LINE('Error : ' || l_msg_data);
         FOR i IN 1 .. l_msg_count LOOP
            DBMS_OUTPUT.PUT_LINE
            (FND_MSG_PUB.GET (p_msg_index => i, p_encoded   => FND_API.G_FALSE )
            );
         END LOOP;
      END IF;
   END LOOP;
END;
/

Conclusion

If there is no link between the supplier and the party, this API will help establish that link.