APPS.ZX_API_PRVDR_PUB SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 41

        SELECT ptp.party_tax_profile_id
         INTO  x_api_owner_id
         FROM HZ_PARTIES pty,
              ZX_PARTY_TAX_PROFILE ptp
        WHERE pty.party_name = p_srvc_prvdr_name
          AND pty.party_id = ptp.party_id
          AND ptp.provider_type_code in ('BOTH', 'SERVICE')
          AND (ptp.party_tax_profile_id =1
            OR ptp.party_tax_profile_id=2);

Line: 77

       select service_type_id
	into x_srvc_type_id
	from zx_service_types
	where SERVICE_TYPE_CODE = p_srvc_type_code
	  and SERVICE_CATEGORY_CODE = G_SRVC_CATEGORY;

Line: 92

         SELECT 1
           INTO l_exists
  		   FROM ZX_REGIMES_B
  		  WHERE tax_regime_code = p_country_code;

Line: 195

       SELECT code_combination_id
         INTO l_code_combination_id
         FROM zx_api_code_combinations
        WHERE segment_attribute1 = p_country_code
          AND segment_attribute2 = p_business_flow;

Line: 210

          SELECT 1
	      INTO l_exists
	      FROM zx_api_registrations reg,
		       zx_service_types srvc
         WHERE reg.api_owner_id = l_api_owner_id
   	       AND srvc.service_type_id = reg.service_type_id
	       AND srvc.service_type_code = p_srvc_type_code
		   AND context_ccid = l_code_combination_id;

Line: 231

        SELECT context_flex_structure_id
          INTO l_context_flex_structure
          FROM ZX_SERVICE_TYPES
         WHERE service_type_code = p_srvc_type_code
           AND service_category_code = G_SRVC_CATEGORY;

Line: 262

      INSERT INTO ZX_API_REGISTRATIONS
              (API_REGISTRATION_ID,
               API_OWNER_ID,
               PACKAGE_NAME,
               PROCEDURE_NAME,
               SERVICE_TYPE_ID,
               CONTEXT_CCID,
               CREATION_DATE,
               CREATED_BY,
               LAST_UPDATE_DATE,
               LAST_UPDATED_BY,
               LAST_UPDATE_LOGIN,
	       OBJECT_VERSION_NUMBER,
	       RECORD_TYPE_CODE)
          VALUES
               (ZX_API_REGISTRATIONS_S.nextval,
                l_api_owner_id,
                p_package_name,
                p_procedure_name,
                l_srvc_type_id,
                l_code_combination_id,
                sysdate,
                FND_GLOBAL.user_id,
                sysdate,
                FND_GLOBAL.user_id,
                FND_GLOBAL.user_id,
		1,
		'EBTAX_CREATED');

Line: 293

        SELECT status_code
          INTO l_api_status
          FROM ZX_API_OWNER_STATUSES
         WHERE api_owner_id         =  l_api_owner_id
          AND service_category_code = G_SRVC_CATEGORY;

Line: 303

          INSERT INTO ZX_API_OWNER_STATUSES
                     (api_owner_id
                    , service_category_code
                    , status_code
                    , creation_date
                    , created_by
                    , last_update_date
                    , last_updated_by
                    , last_update_login)
               VALUES(l_api_owner_id
                    , G_SRVC_CATEGORY
                    , 'NEW'
                    , sysdate
                    , fnd_global.user_id
                    , sysdate
                    , fnd_global.user_id
                    , fnd_global.user_id);

Line: 322

      /*Status record exists- update them*/
      IF l_api_status = 'DELETED' THEN
         UPDATE ZX_API_OWNER_STATUSES
           SET status_code = 'NEW'
         WHERE api_owner_id   =  l_api_owner_id
           AND service_category_code = G_SRVC_CATEGORY;

Line: 329

         UPDATE ZX_API_OWNER_STATUSES
           SET status_code = 'MODIFIED'
         WHERE api_owner_id   =  l_api_owner_id
           AND service_category_code = G_SRVC_CATEGORY;

Line: 353

 PROCEDURE delete_srvc_registration (
  p_api_version	    IN NUMBER,
  x_error_msg_tbl   OUT NOCOPY error_messages_tbl,
  x_return_status   OUT NOCOPY VARCHAR2,
  p_srvc_prvdr_name IN VARCHAR2,
  p_srvc_type_code  IN VARCHAR2,
  p_country_code    IN VARCHAR2,
  p_business_flow   IN VARCHAR2
 )  IS
  l_api_name        CONSTANT VARCHAR2(30) := 'DELETE_SRVC_REGISTRATION';

Line: 377

    SAVEPOINT delete_srvc_registration_pvt;

Line: 395

      SELECT count(*)
        INTO l_count
        FROM ZX_API_REGISTRATIONS
       WHERE api_owner_id = l_api_owner_id
         AND service_type_id = l_srvc_type_id;

Line: 409

	DELETE from ZX_API_REGISTRATIONS
	  WHERE EXISTS (SELECT *
	                  FROM zx_api_registrations reg,
					       zx_api_code_combinations cmbn,
						   zx_service_types srvc
					  WHERE reg.api_owner_id = l_api_owner_id
	                    AND srvc.service_type_id = reg.service_type_id
	                    AND srvc.service_type_code = p_srvc_type_code
	                    AND cmbn.segment_attribute1 = p_country_code
	                    AND cmbn.segment_attribute2 = p_business_flow
	                    AND reg.context_ccid = cmbn.code_combination_id
                    );

Line: 422

    /*Update the status back to zx_api_statuses table*/
    IF x_error_msg_tbl.COUNT = 0 THEN
      BEGIN
        SELECT status_code
          INTO l_api_status
          FROM ZX_API_OWNER_STATUSES
         WHERE api_owner_id         =  l_api_owner_id
           AND service_category_code = G_SRVC_CATEGORY;

Line: 438

           UPDATE ZX_API_OWNER_STATUSES
              SET status_code = 'DELETED'
            WHERE api_owner_id   =  l_api_owner_id
              AND service_category_code = G_SRVC_CATEGORY;

Line: 445

           UPDATE ZX_API_OWNER_STATUSES
              SET status_code = 'MODIFIED'
            WHERE api_owner_id   =  l_api_owner_id
              AND service_category_code = G_SRVC_CATEGORY;

Line: 459

         ROLLBACK TO insert_row_Pvt;

Line: 467

 END DELETE_SRVC_REGISTRATION;

Line: 497

       SELECT ptp.party_tax_profile_id
        INTO  l_api_owner_id
         FROM HZ_PARTIES pty,
              ZX_PARTY_TAX_PROFILE ptp
         WHERE pty.party_name = p_srvc_prvdr_name
           AND pty.party_id = ptp.party_id
           AND ptp.provider_type_code in ('BOTH', 'SERVICE')
           AND (ptp.party_tax_profile_id =1
             OR ptp.party_tax_profile_id=2);