APPS.HZ_MERGE_DUP_CHECK SQL Statements

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

Line: 52

  SELECT
        FINANCIAL_PROFILE_ID
  FROM HZ_FINANCIAL_PROFILE
  WHERE party_id = p_to_fk_id
  AND (status IS NULL OR status = 'A')
  AND   TO_CHAR(ACCESS_AUTHORITY_DATE, 'DD/MM/YYYY') ||
	ACCESS_AUTHORITY_GRANTED ||
	TO_CHAR(BALANCE_AMOUNT) ||
	TO_CHAR(BALANCE_VERIFIED_ON_DATE, 'DD/MM/YYYY') ||
 	FINANCIAL_ACCOUNT_NUMBER ||
	FINANCIAL_ACCOUNT_TYPE  ||
	FINANCIAL_ORG_TYPE ||
	FINANCIAL_ORGANIZATION_NAME = (
           SELECT TO_CHAR(ACCESS_AUTHORITY_DATE, 'DD/MM/YYYY') ||
	        ACCESS_AUTHORITY_GRANTED ||
        	TO_CHAR(BALANCE_AMOUNT) ||
        	TO_CHAR(BALANCE_VERIFIED_ON_DATE, 'DD/MM/YYYY') ||
        	FINANCIAL_ACCOUNT_NUMBER ||
        	FINANCIAL_ACCOUNT_TYPE  ||
        	FINANCIAL_ORG_TYPE ||
        	FINANCIAL_ORGANIZATION_NAME
	   FROM HZ_FINANCIAL_PROFILE
  	   WHERE  financial_profile_id = p_from_id);

Line: 109

  SELECT
     CONTACT_POINT_ID
  FROM HZ_CONTACT_POINTS
  WHERE owner_table_name = p_owner_table_name
  AND owner_table_id = p_to_fk_id
  AND (status IS NULL OR status = 'A')
  AND
   CONTACT_POINT_TYPE ||
   STATUS ||
   EDI_TRANSACTION_HANDLING ||
   EDI_ID_NUMBER ||
   EDI_PAYMENT_METHOD ||
   EDI_PAYMENT_FORMAT ||
   EDI_REMITTANCE_METHOD ||
   EDI_REMITTANCE_INSTRUCTION ||
   EDI_TP_HEADER_ID ||
   EDI_ECE_TP_LOCATION_CODE ||
   EMAIL_FORMAT ||
   TO_CHAR(BEST_TIME_TO_CONTACT_START, 'DD/MM/YYYY') ||
   TO_CHAR(BEST_TIME_TO_CONTACT_END, 'DD/MM/YYYY') ||
   PHONE_CALLING_CALENDAR ||
   DECLARED_BUSINESS_PHONE_FLAG ||
   PHONE_PREFERRED_ORDER ||
   TELEPHONE_TYPE ||
   TIME_ZONE ||
   PHONE_TOUCH_TONE_TYPE_FLAG ||
   PHONE_AREA_CODE ||
   PHONE_COUNTRY_CODE ||
   PHONE_NUMBER ||
   PHONE_EXTENSION ||
   PHONE_LINE_TYPE ||
   TELEX_NUMBER ||
   WEB_TYPE ||
   DECODE (source_type,'P',actual_content_source,'S')
       = (SELECT
   		CONTACT_POINT_TYPE ||
   		STATUS ||
   		EDI_TRANSACTION_HANDLING ||
   		EDI_ID_NUMBER ||
   		EDI_PAYMENT_METHOD ||
   		EDI_PAYMENT_FORMAT ||
   		EDI_REMITTANCE_METHOD ||
   		EDI_REMITTANCE_INSTRUCTION ||
   		EDI_TP_HEADER_ID ||
   		EDI_ECE_TP_LOCATION_CODE ||
   		EMAIL_FORMAT ||
   		TO_CHAR(BEST_TIME_TO_CONTACT_START, 'DD/MM/YYYY') ||
   		TO_CHAR(BEST_TIME_TO_CONTACT_END, 'DD/MM/YYYY') ||
   		PHONE_CALLING_CALENDAR ||
   		DECLARED_BUSINESS_PHONE_FLAG ||
   		PHONE_PREFERRED_ORDER ||
   		TELEPHONE_TYPE ||
   		TIME_ZONE ||
   		PHONE_TOUCH_TONE_TYPE_FLAG ||
   		PHONE_AREA_CODE ||
   		PHONE_COUNTRY_CODE ||
   		PHONE_NUMBER ||
   		PHONE_EXTENSION ||
   		PHONE_LINE_TYPE ||
   		TELEX_NUMBER ||
   	        WEB_TYPE ||
                DECODE (source_type,'P',actual_content_source,'S')
           FROM HZ_CONTACT_POINTS
           WHERE contact_point_id = p_from_id)
   AND upper(nvl(EMAIL_ADDRESS,'NOEMAIL')) = ( --7294111 Added Upper
           SELECT upper(nvl(EMAIL_ADDRESS,'NOEMAIL')) --7294111 Added Upper
           FROM HZ_CONTACT_POINTS
           WHERE contact_point_id = p_from_id)
   AND nvl(URL, 'NOURL') = (
           SELECT nvl(URL, 'NOURL')
           FROM HZ_CONTACT_POINTS
           WHERE contact_point_id = p_from_id);

Line: 183

  SELECT ACTUAL_CONTENT_SOURCE
  FROM HZ_CONTACT_POINTS
  WHERE contact_point_id = p_from_id;

Line: 205

  SELECT orig_system_type INTO l_cont_source_type
    FROM HZ_ORIG_SYSTEMS_B WHERE orig_system = l_cont_source;

Line: 240

  SELECT
    REFERENCE_ID
  FROM HZ_REFERENCES
  WHERE referenced_party_id = p_to_fk_id
  AND (status IS NULL OR status = 'A')
  AND
    TO_CHAR(COMMENTING_PARTY_ID) ||
    EXTERNAL_ACCOUNT_NUMBER ||
    TO_CHAR(REFERENCE_DATE, 'DD/MM/YYYY') =
       (SELECT
		TO_CHAR(COMMENTING_PARTY_ID) ||
    		EXTERNAL_ACCOUNT_NUMBER ||
    		TO_CHAR(REFERENCE_DATE, 'DD/MM/YYYY')
	FROM HZ_REFERENCES
	WHERE reference_id = p_from_id);

Line: 290

  SELECT
    CERTIFICATION_ID
  FROM HZ_CERTIFICATIONS
  WHERE party_id = p_to_fk_id
  AND (status IS NULL OR status = 'A')
  AND
    CERTIFICATION_NAME ||
    CURRENT_STATUS ||
    TO_CHAR(EXPIRES_ON_DATE, 'DD/MM/YYYY') ||
    GRADE ||
    ISSUED_BY_AUTHORITY ||
    TO_CHAR(ISSUED_ON_DATE, 'DD/MM/YYYY') =
	(SELECT
		CERTIFICATION_NAME ||
    		CURRENT_STATUS ||
    		TO_CHAR(EXPIRES_ON_DATE, 'DD/MM/YYYY') ||
    		GRADE ||
    		ISSUED_BY_AUTHORITY ||
    		TO_CHAR(ISSUED_ON_DATE, 'DD/MM/YYYY')
  	FROM HZ_CERTIFICATIONS
	WHERE certification_id = p_from_id);

Line: 346

  SELECT
    CREDIT_RATING_ID
  FROM HZ_CREDIT_RATINGS
  WHERE party_id = p_to_fk_id
  AND
    TO_CHAR(RATED_AS_OF_DATE, 'DD/MM/YYYY') ||
    RATING_ORGANIZATION ||
    DECODE (source_type,'P',actual_content_source,'S')  =
	(SELECT
    		TO_CHAR(RATED_AS_OF_DATE, 'DD/MM/YYYY') ||
    		RATING_ORGANIZATION ||
              DECODE (source_type,'P',actual_content_source,'S') FROM HZ_CREDIT_RATINGS
  	WHERE credit_rating_id = p_from_id);

Line: 361

  SELECT ACTUAL_CONTENT_SOURCE
  FROM HZ_CREDIT_RATINGS
  WHERE credit_rating_id = p_from_id;

Line: 383

  SELECT orig_system_type INTO l_cont_source_type
    FROM HZ_ORIG_SYSTEMS_B WHERE orig_system = l_cont_source;

Line: 419

  SELECT
    SECURITY_ISSUED_ID
  FROM HZ_SECURITY_ISSUED
  WHERE party_id = p_to_fk_id
  AND (status IS NULL OR status = 'A')
  AND
    TO_CHAR(ESTIMATED_TOTAL_AMOUNT) ||
    TO_CHAR(STOCK_EXCHANGE_ID) ||
    SECURITY_ISSUED_CLASS ||
    SECURITY_ISSUED_NAME ||
    TOTAL_AMOUNT_IN_A_CURRENCY ||
    STOCK_TICKER_SYMBOL ||
    SECURITY_CURRENCY_CODE ||
    TO_CHAR(BEGIN_DATE,'DD/MM/YYYY') ||
    TO_CHAR(END_DATE,'DD/MM/YYYY') =
       (SELECT
	    TO_CHAR(ESTIMATED_TOTAL_AMOUNT) ||
	    TO_CHAR(STOCK_EXCHANGE_ID) ||
	    SECURITY_ISSUED_CLASS ||
	    SECURITY_ISSUED_NAME ||
	    TOTAL_AMOUNT_IN_A_CURRENCY ||
	    STOCK_TICKER_SYMBOL ||
	    SECURITY_CURRENCY_CODE ||
	    TO_CHAR(BEGIN_DATE,'DD/MM/YYYY') ||
	    TO_CHAR(END_DATE,'DD/MM/YYYY')
        FROM HZ_SECURITY_ISSUED
        WHERE   security_issued_id = p_from_id);

Line: 480

  SELECT
    FINANCIAL_REPORT_ID
  FROM HZ_FINANCIAL_REPORTS
  WHERE party_id = p_to_fk_id
  AND
    TO_CHAR(DATE_REPORT_ISSUED, 'DD/MM/YYYY') ||
    DOCUMENT_REFERENCE ||
    ISSUED_PERIOD ||
    TYPE_OF_FINANCIAL_REPORT ||
    TO_CHAR(REPORT_START_DATE, 'DD/MM/YYYY') ||
    TO_CHAR(REPORT_END_DATE, 'DD/MM/YYYY') ||
    DECODE (source_type,'P',actual_content_source,'S')  =
	(SELECT
	    TO_CHAR(DATE_REPORT_ISSUED, 'DD/MM/YYYY') ||
	    DOCUMENT_REFERENCE ||
	    ISSUED_PERIOD ||
	    TYPE_OF_FINANCIAL_REPORT ||
	    TO_CHAR(REPORT_START_DATE, 'DD/MM/YYYY') ||
	    TO_CHAR(REPORT_END_DATE, 'DD/MM/YYYY') ||
            DECODE (source_type,'P',actual_content_source,'S')
           FROM HZ_FINANCIAL_REPORTS
	WHERE financial_report_id = p_from_id);

Line: 504

  SELECT ACTUAL_CONTENT_SOURCE
  FROM HZ_FINANCIAL_REPORTS
  WHERE financial_report_id = p_from_id;

Line: 509

  SELECT financial_report_id
  FROM HZ_FINANCIAL_REPORTS
  WHERE party_id = p_to_fk_id
  AND actual_content_source = 'DNB'
  AND nvl(status, 'A') = 'A'
  AND type_of_financial_report= (SELECT type_of_financial_report
                                 FROM HZ_FINANCIAL_REPORTS
                                 WHERE financial_report_id = p_from_id);

Line: 549

  SELECT orig_system_type INTO l_cont_source_type
    FROM HZ_ORIG_SYSTEMS_B WHERE orig_system = l_cont_source;

Line: 588

  SELECT
    ORGANIZATION_INDICATOR_ID
  FROM HZ_ORGANIZATION_INDICATORS
  WHERE party_id = p_to_fk_id
  AND (status IS NULL OR status = 'A')
  AND
    INDICATOR ||
    TO_CHAR(START_DATE, 'DD/MM/YYYY') ||
    TO_CHAR(END_DATE, 'DD/MM/YYYY') =
	(SELECT
		INDICATOR ||
		TO_CHAR(START_DATE, 'DD/MM/YYYY') ||
		TO_CHAR(END_DATE, 'DD/MM/YYYY')
	FROM HZ_ORGANIZATION_INDICATORS
	WHERE organization_indicator_id = p_from_id)
  AND
    nvl(DESCRIPTION	, 'NODESC') =
       (SELECT nvl(DESCRIPTION, 'NODESC')
        FROM HZ_ORGANIZATION_INDICATORS
        WHERE organization_indicator_id = p_from_id);

Line: 642

  SELECT
    INDUSTRY_REFERENCE_ID
  FROM HZ_INDUSTRIAL_REFERENCE
  WHERE party_id = p_to_fk_id
  AND (status IS NULL OR status = 'A')
  AND
    INDUSTRY_REFERENCE ||
    ISSUED_BY_AUTHORITY ||
    NAME_OF_REFERENCE ||
    TO_CHAR(RECOGNIZED_AS_OF_DATE,'DD/MM/YYYY') =
	(SELECT
		INDUSTRY_REFERENCE ||
		ISSUED_BY_AUTHORITY ||
		NAME_OF_REFERENCE ||
		TO_CHAR(RECOGNIZED_AS_OF_DATE,'DD/MM/YYYY')
	FROM HZ_INDUSTRIAL_REFERENCE
	WHERE industry_reference_id = p_from_id);

Line: 693

  SELECT
    PERSON_INTEREST_ID
  FROM HZ_PERSON_INTEREST
  WHERE party_id = p_to_fk_id
  AND (status IS NULL OR status = 'A')
  AND
    INTEREST_TYPE_CODE ||
    SUB_INTEREST_TYPE_CODE =
	(SELECT
		INTEREST_TYPE_CODE ||
		SUB_INTEREST_TYPE_CODE
	FROM HZ_PERSON_INTEREST
	WHERE person_interest_id = p_from_id);

Line: 741

  SELECT
    CITIZENSHIP_ID
  FROM HZ_CITIZENSHIP
  WHERE party_id = p_to_fk_id
  AND (status IS NULL OR status = 'A')
  AND
    BIRTH_OR_SELECTED ||
    COUNTRY_CODE ||
    TO_CHAR(DATE_DISOWNED, 'DD/MM/YYYY') ||
    TO_CHAR(DATE_RECOGNIZED, 'DD/MM/YYYY') ||
    DOCUMENT_REFERENCE ||
    TO_CHAR(END_DATE, 'DD/MM/YYYY') ||
    DOCUMENT_TYPE =
	(SELECT
		BIRTH_OR_SELECTED ||
		COUNTRY_CODE ||
		TO_CHAR(DATE_DISOWNED, 'DD/MM/YYYY') ||
		TO_CHAR(DATE_RECOGNIZED, 'DD/MM/YYYY') ||
		DOCUMENT_REFERENCE ||
		TO_CHAR(END_DATE, 'DD/MM/YYYY') ||
		DOCUMENT_TYPE
	FROM HZ_CITIZENSHIP
	WHERE citizenship_id = p_from_id);

Line: 799

  SELECT
    EDUCATION_ID
  FROM HZ_EDUCATION
  WHERE party_id = p_to_fk_id
  AND (status IS NULL OR status = 'A')
  AND
    COURSE_MAJOR ||
    DEGREE_RECEIVED ||
    TO_CHAR(LAST_DATE_ATTENDED, 'DD/MM/YYYY') ||
    TO_CHAR(START_DATE_ATTENDED, 'DD/MM/YYYY') ||
    TYPE_OF_SCHOOL ||
    SCHOOL_ATTENDED_NAME ||
    SCHOOL_PARTY_ID=
	(SELECT
		COURSE_MAJOR ||
		DEGREE_RECEIVED ||
		TO_CHAR(LAST_DATE_ATTENDED, 'DD/MM/YYYY') ||
		TO_CHAR(START_DATE_ATTENDED, 'DD/MM/YYYY') ||
		TYPE_OF_SCHOOL ||
                SCHOOL_ATTENDED_NAME ||
                SCHOOL_PARTY_ID
	FROM HZ_EDUCATION
	WHERE education_id = p_from_id);

Line: 855

  SELECT
    WORK_CLASS_ID
  FROM HZ_WORK_CLASS
  WHERE employment_history_id = p_to_fk_id
  AND (status IS NULL OR status = 'A')
  AND
    WORK_CLASS_NAME =
        (SELECT
	    WORK_CLASS_NAME
        FROM HZ_WORK_CLASS
        WHERE work_class_id = p_from_id);

Line: 900

  SELECT
    EMPLOYMENT_HISTORY_ID
  FROM HZ_EMPLOYMENT_HISTORY
  WHERE party_id = p_to_fk_id
  AND (status IS NULL OR status = 'A')
  AND
    TO_CHAR(BEGIN_DATE, 'DD/MM/YYYY') ||
    EMPLOYED_AS_TITLE ||
    EMPLOYED_BY_DIVISION_NAME ||
    EMPLOYED_BY_NAME_COMPANY ||
    TO_CHAR(END_DATE, 'DD/MM/YYYY') ||
    SUPERVISOR_NAME ||
    BRANCH ||
    MILITARY_RANK ||
    SERVED  ||
    STATION =
	(SELECT
		TO_CHAR(BEGIN_DATE, 'DD/MM/YYYY') ||
		EMPLOYED_AS_TITLE ||
		EMPLOYED_BY_DIVISION_NAME ||
		EMPLOYED_BY_NAME_COMPANY ||
		TO_CHAR(END_DATE, 'DD/MM/YYYY') ||
		SUPERVISOR_NAME ||
		BRANCH ||
		MILITARY_RANK ||
		SERVED ||
                STATION
	FROM HZ_EMPLOYMENT_HISTORY
	WHERE employment_history_id = p_from_id);

Line: 964

  SELECT LANGUAGE_USE_REFERENCE_ID
  FROM   HZ_PERSON_LANGUAGE
  WHERE party_id = p_to_fk_id
  AND (status IS NULL OR status = 'A')
  AND
    LANGUAGE_NAME =
	(SELECT
		LANGUAGE_NAME
	FROM HZ_PERSON_LANGUAGE
	WHERE language_use_reference_id = p_from_id);

Line: 1010

  SELECT
   PARTY_SITE_USE_ID
  FROM HZ_PARTY_SITE_USES
  WHERE party_site_id = p_to_fk_id
  AND (status IS NULL OR status = 'A')
  AND SITE_USE_TYPE =
         	(SELECT
		SITE_USE_TYPE
	FROM HZ_PARTY_SITE_USES
	WHERE party_site_use_id = p_from_id);

Line: 1054

  SELECT
   PARTY_SITE_ID
  FROM HZ_PARTY_SITES
  WHERE party_id = p_to_fk_id
  AND (status IS NULL OR status = 'A')
  AND LOCATION_ID = (
	SELECT LOCATION_ID
	FROM HZ_PARTY_SITES
  	WHERE party_site_id = p_from_id);

Line: 1098

  SELECT
    CODE_ASSIGNMENT_ID
  FROM HZ_CODE_ASSIGNMENTS
  WHERE owner_table_name = p_owner_table_name
  AND owner_table_id = p_to_fk_id
  --AND (status IS NULL OR status = 'A')--Commented for Bug#3016319.
  AND CLASS_CATEGORY || CLASS_CODE  = (
        SELECT  CLASS_CATEGORY ||
		CLASS_CODE
        FROM HZ_CODE_ASSIGNMENTS
        WHERE code_assignment_id = p_from_id);

Line: 1111

  SELECT CONTENT_SOURCE_TYPE
  FROM HZ_CODE_ASSIGNMENTS
  WHERE code_assignment_id = p_from_id;

Line: 1155

  SELECT
    FINANCIAL_NUMBER_ID
  FROM HZ_FINANCIAL_NUMBERS
  WHERE financial_report_id = p_to_fk_id
  AND NVL(FINANCIAL_NUMBER_NAME,'NONAME') = (
        SELECT
          NVL(FINANCIAL_NUMBER_NAME,'NONAME')
        FROM HZ_FINANCIAL_NUMBERS
        WHERE financial_number_id = p_from_id);

Line: 1193

  SELECT
    ORG_CONTACT_ROLE_ID
  FROM HZ_ORG_CONTACT_ROLES
  WHERE org_contact_id = p_to_fk_id
  AND ROLE_TYPE = (
        SELECT ROLE_TYPE
        FROM   HZ_ORG_CONTACT_ROLES
        WHERE  ORG_CONTACT_ROLE_ID = p_from_id);

Line: 1231

  SELECT
    CONTACT_PREFERENCE_ID
  FROM HZ_CONTACT_PREFERENCES
  WHERE contact_level_table = p_owner_table_name
  AND contact_level_table_id = p_to_fk_id
  AND (status IS NULL OR status = 'A')
  AND --Bug:4390508 - contact_type || preference_code || preference_topic_type ||
  		contact_type || preference_topic_type ||
        preference_topic_type_id || preference_topic_type_code =
        (SELECT
                 --Bug:4390508 - contact_type || preference_code ||
                 contact_type ||
                 preference_topic_type ||
                 preference_topic_type_id ||
                 preference_topic_type_code
         FROM HZ_CONTACT_PREFERENCES
         WHERE contact_preference_id = p_from_id)
  AND NOT (
            ( preference_end_date is not null and
             preference_end_date <= ( SELECT preference_start_date
                                      FROM HZ_CONTACT_PREFERENCES
                                      WHERE contact_preference_id = p_from_id))
            OR
            ( exists (SELECT 1
              FROM HZ_CONTACT_PREFERENCES
              WHERE contact_preference_id = p_from_id
              AND preference_end_date is not null)  AND
              preference_start_date >= ( SELECT preference_end_date
                                      FROM HZ_CONTACT_PREFERENCES
                                      WHERE contact_preference_id = p_from_id))
          )
   AND NOT (
      (
       (decode(preference_start_time_hr, null, 0, preference_start_time_hr) * 60 +
        decode(preference_start_time_mi, null, 0, preference_start_time_mi)) >
         ( select (decode(preference_end_time_hr, null, 24, preference_end_time_hr) * 60 +
                  decode (preference_end_time_mi, null, 60, preference_end_time_mi))
           from HZ_CONTACT_PREFERENCES
           WHERE contact_preference_id = p_from_id)
       ) OR (
      (decode(preference_end_time_hr, null, 24, preference_end_time_hr ) * 60 +
       decode(preference_end_time_mi, null, 60, preference_end_time_mi)) <
         (select (decode(preference_start_time_hr, null, 0, preference_start_time_hr) * 60 +
                 decode(preference_start_time_mi, null, 0, preference_start_time_mi ))
          FROM HZ_CONTACT_PREFERENCES
           WHERE contact_preference_id = p_from_id)
      )
   );

Line: 1316

  SELECT PARTY_SITE_ID
          FROM HZ_PARTY_SITES ps,
               HZ_LOCATIONS   l
         WHERE ps.party_id = p_to_fk_id
           AND ps.location_id = l.location_id
           AND (ps.status IS NULL OR ps.status = 'A')
           AND UPPER(TRIM(ADDRESS1) ||
                     TRIM(ADDRESS2) ||
                     TRIM(ADDRESS3) ||
                     TRIM(ADDRESS4) ||
                     TRIM(COUNTRY)  ||
                     TRIM(STATE)    ||
                     TRIM(CITY)     ||
                     TRIM(PROVINCE) ||
                     TRIM(COUNTY)   ||
                     TRIM(POSTAL_CODE)) =
       (SELECT UPPER(TRIM(ADDRESS1) ||
                     TRIM(ADDRESS2) ||
                     TRIM(ADDRESS3) ||
                     TRIM(ADDRESS4) ||
                     TRIM(COUNTRY)  ||
                     TRIM(STATE)    ||
                     TRIM(CITY)     ||
                     TRIM(PROVINCE) ||
                     TRIM(COUNTY)   ||
                     TRIM(POSTAL_CODE))
	 FROM HZ_LOCATIONS
	WHERE LOCATION_ID = p_from_location_id);