Home » Applications » Oracle Fusion Apps & E-Business Suite » Inactivate Party Site (Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production)
Inactivate Party Site [message #676380] Wed, 05 June 2019 16:20 Go to next message
vharish006
Messages: 67
Registered: August 2015
Location: Chicago
Member
Hi All,

My requirement was to Inactivate Party Sites which are no longer in Use. I'm trying to use the below API but somehow it keeps throwing error and not able to find the identifier it is mentioning.Any help would be appreciated.

Error Encountered:
-------------------

Return Status:U
Inactivation Failed for Party Site:1380079:The following SQL error occurred:
ORA-00904: "HZ_UTILITY_V2PUB"."PROGRAM_UPDATE_DATE": invalid identifier.
PL/SQL procedure successfully completed.















CREATE OR REPLACE PROCEDURE PQINF.xxpq_inactivate_party_site (  
   p_party_site_number      IN     VARCHAR2,  
   p_err_flag           OUT VARCHAR2,  
   p_err_msg            OUT VARCHAR2)  
AS  
   l_party_site_rec          apps.hz_party_site_v2pub.PARTY_SITE_REC_TYPE;  
   x_return_status           VARCHAR2 (2000);  
   x_msg_count               NUMBER;  
   x_msg_data                VARCHAR2 (2000);  
   l_party_site_id           NUMBER;  
   l_object_version_number   NUMBER;  
BEGIN  
   p_err_flag := 'N';  
   p_err_msg := NULL;  
  
  
   BEGIN  
      SELECT PARTY_SITE_ID, OBJECT_VERSION_NUMBER  
        INTO l_party_site_id, l_object_version_number  
        FROM apps.HZ_PARTY_SITES  
       WHERE party_site_number = p_party_site_number;  
   EXCEPTION  
      WHEN OTHERS  
      THEN  
         p_err_flag := 'E';  
         p_err_msg :=  
               'Error in getting Party Site id for Party Site number:'  
            || p_party_site_number  
            || ':'  
            || SQLERRM;  
         /*apps.fnd_file.put_line (  
            apps.fnd_file.LOG,  
               'Error in getting Party Site id for Party Site number:'  
            || p_party_site_number  
            || ':'  
            || SQLERRM);*/  
         DBMS_OUTPUT.put_line (  
               'Error in getting Party Site id for Party Site number:'  
            || p_party_site_number  
            || ':'  
            || SQLERRM);  
   END;  
  
  
   IF P_err_flag = 'N'  
   THEN  
      l_party_site_rec.PARTY_SITE_ID :=l_party_site_id ;  
      l_party_site_rec.status := 'I';  
  
  
      apps.hz_party_site_v2pub.update_party_site (  
         p_init_msg_list           => apps.FND_API.G_TRUE,  
         p_party_site_rec          => l_party_site_rec,  
         p_object_version_number   => l_object_version_number,  
         x_return_status           => x_return_status,  
         x_msg_count               => x_msg_count,  
         x_msg_data               => x_msg_data);  
  
  
      IF x_return_status = apps.fnd_api.g_ret_sts_success  
      THEN  
         p_err_flag := 'S';  
         p_err_msg := 'Party Site Inactivated';  
         COMMIT;  
      ELSE  
        /* apps.fnd_file.put_line (  
            apps.fnd_file.LOG,  
               'Inactivation Failed for Party Site:'  
            || p_party_site_number  
            || ':'  
            || x_msg_data);*/  
         DBMS_OUTPUT.put_line (  
               'Inactivation Failed for Party Site:'  
            || p_party_site_number  
            || ':'  
            || x_msg_data);  
         ROLLBACK;  
         p_err_flag := 'E';  
  
  
         IF x_msg_count > 1  
         THEN  
            FOR i IN 1 .. x_msg_count  
            LOOP  
               x_msg_data :=  
                     apps.fnd_msg_pub.get (p_msg_index => i, p_encoded => 'F')  
                  || ','  
                  || x_msg_data;  
              /* apps.fnd_file.put_line (apps.fnd_file.LOG,  
                                       (i || ') ' || x_msg_data));*/  
            END LOOP;  
         END IF;  
  
  
         p_err_msg := x_msg_data;  
      END IF;  
   END IF;  
EXCEPTION  
   WHEN OTHERS  
   THEN  
      p_err_flag := 'E';  
      p_err_msg :=  
            'Error in Procedure xxpq_inactivate_party_site:'  
         || p_party_site_number  
         || ':'  
         || SQLERRM;  
      /*apps.fnd_file.put_line (  
         apps.fnd_file.LOG,  
            'Inactivation Failed for Party Site:'  
         || p_party_site_number  
         || '-'  
         || SQLERRM  
         || '-'  
         || DBMS_UTILITY.format_error_backtrace ());*/  
      DBMS_OUTPUT.put_line (  
            'Inactivation Failed for Party Site:'  
         || p_party_site_number  
         || '-'  
         || SQLERRM  
         || '-'  
         || DBMS_UTILITY.format_error_backtrace ());  
END xxpq_inactivate_party_site;  


Using below Block trying to execute from TOAD

SET SERVEROUTPUT ON  
  
declare  
er_fl VARCHAR2(2000);  
er_ms VARCHAR2(2000);  
v_party_site_number NUMBER:=1011331;  
  
  
Begin  
PQINF.XXPQ_INACTIVATE_PARTY_SITE  
(p_party_site_number => v_party_site_number,p_err_flag=>er_fl,p_err_msg=>er_ms);  
END;  
Re: Inactivate Party Site [message #676388 is a reply to message #676380] Thu, 06 June 2019 05:52 Go to previous message
John Watson
Messages: 8023
Registered: January 2010
Location: Global Village
Senior Member
If you remove the EXCEPTION clauses then you may get a more helpful error message.
Previous Topic: Net cost of IAS to WebLogic move
Next Topic: concurrent report request with same parameters
Goto Forum:
  


Current Time: Mon Oct 21 01:08:44 CDT 2019