APPS.HZ_REGISTRY_VALIDATE_BO_PVT SQL Statements

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

Line: 122

    select 'Y'
    from HZ_PARTIES
    where party_id = l_party_id
    and party_type = l_party_type;

Line: 128

    select 'Y'
    from HZ_PARTY_SITES
    where party_site_id = l_party_site_id;

Line: 133

    select 'Y'
    from HZ_CUST_ACCOUNTS
    where cust_account_id = l_acct_id;

Line: 138

    select 'Y'
    from HZ_CUST_ACCT_SITES_ALL
    where cust_acct_site_id = l_acct_site_id;

Line: 352

    p_create_or_update           IN            VARCHAR2,
    x_return_status              OUT NOCOPY    VARCHAR2,
    x_msg_count                  OUT NOCOPY    NUMBER,
    x_msg_data                   OUT NOCOPY    VARCHAR2
  ) IS
    CURSOR is_cp_valid(l_cp_id NUMBER, l_cp_type VARCHAR2) IS
    SELECT 'X'
    FROM HZ_CONTACT_POINTS
    WHERE contact_point_id = l_cp_id
    AND contact_point_type = l_cp_type;

Line: 364

    SELECT 'X'
    FROM HZ_ORG_CONTACTS
    WHERE org_contact_id = l_oc_id;

Line: 369

    SELECT 'X'
    FROM HZ_PARTIES
    WHERE party_id = l_pty_id
    AND party_type = l_pty_type
    AND status in ('A', 'I');

Line: 376

    SELECT 'X'
    FROM HZ_PARTY_SITES
    WHERE party_site_id = l_ps_id;

Line: 381

    SELECT 'X'
    FROM HZ_LOCATIONS
    WHERE location_id = l_loc_id;

Line: 386

    SELECT 'X'
    FROM HZ_CUST_ACCOUNT_ROLES
    WHERE cust_account_role_id = l_cr_id;

Line: 391

    SELECT 'X'
    FROM HZ_CUST_ACCOUNTS
    WHERE cust_account_id = l_ca_id;

Line: 396

    SELECT 'X'
    FROM HZ_CUST_ACCT_SITES
    WHERE cust_acct_site_id = l_cas_id
    AND org_id = l_org_id;

Line: 402

    SELECT 'X'
    FROM HZ_CUST_SITE_USES
    WHERE site_use_id = l_casu_id
    AND org_id = l_org_id;

Line: 433

      IF(p_create_or_update = 'C') THEN
        FND_MESSAGE.SET_NAME('AR','HZ_API_CANNOT_PASS_PK');

Line: 517

    IF(p_create_or_update = 'U') THEN
      -- if px_id pass in
      IF(px_id IS NOT NULL) THEN
        -- if px_id is invalid, raise error
        IF(l_valid_id IS NULL) THEN
          FND_MESSAGE.SET_NAME('AR','HZ_API_UPDATE_NOT_EXIST');

Line: 562

    ELSIF(p_create_or_update = 'C') THEN
      -- if os+osr is valid, raise error
      IF(l_ss_flag = 'Y') AND (l_count > 0) AND (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
        FND_MESSAGE.SET_NAME('AR','HZ_API_CREATE_ALREADY_EXISTS');

Line: 569

    END IF;  -- if p_create_or_update

Line: 671

    SELECT nvl(object_version_number,1), contact_level_table_id, contact_level_table
    FROM HZ_CONTACT_PREFERENCES
    WHERE contact_preference_id = l_contact_pref_id
    AND rownum = 1;

Line: 681

    SELECT nvl(object_version_number,1), contact_preference_id
    FROM HZ_CONTACT_PREFERENCES
    WHERE contact_level_table_id = l_contact_level_table_id
    AND contact_level_table = l_contact_level_table
    AND contact_type = l_contact_type
  --  AND preference_code = l_preference_code
    AND trunc(preference_start_date) = trunc(l_preference_start_date)
    AND trunc(nvl(preference_end_date,sysdate)) = trunc(nvl(l_preference_end_date,sysdate))
    AND status in ('A','I')
    AND rownum = 1;

Line: 741

    SELECT nvl(object_version_number,1), party_id
    FROM HZ_PERSON_LANGUAGE
    WHERE language_use_reference_id = l_lang_id
    AND rownum = 1;

Line: 747

    SELECT nvl(object_version_number,1), language_use_reference_id
    FROM HZ_PERSON_LANGUAGE
    WHERE party_id = l_party_id
    AND language_name = l_language_name
    AND status in ('A','I')
    AND rownum = 1;

Line: 803

    SELECT nvl(object_version_number,1), party_id
    FROM HZ_EDUCATION
    WHERE education_id = l_edu_id
    AND rownum = 1;

Line: 811

    SELECT nvl(object_version_number,1), education_id
    FROM HZ_EDUCATION
    WHERE party_id = l_party_id
    AND UPPER(ltrim(rtrim(course_major))) = UPPER(ltrim(rtrim(l_course_major)))
    AND UPPER(ltrim(rtrim(degree_received))) = UPPER(ltrim(rtrim(l_degree_received)))
    AND UPPER(ltrim(rtrim(school_attended_name))) = UPPER(ltrim(rtrim(l_school_attended_name)))
    AND status in ('A','I')
    AND rownum = 1;

Line: 865

    SELECT nvl(object_version_number,1), party_id
    FROM HZ_CITIZENSHIP
    WHERE citizenship_id = l_citizen_id
    AND rownum = 1;

Line: 871

    SELECT nvl(object_version_number,1), citizenship_id
    FROM HZ_CITIZENSHIP
    WHERE party_id = l_party_id
    AND country_code = l_country_code
    AND status in ('A','I')
    AND rownum = 1;

Line: 927

    SELECT nvl(object_version_number,1), party_id
    FROM HZ_EMPLOYMENT_HISTORY
    WHERE employment_history_id = l_emp_hist_id
    AND rownum = 1;

Line: 934

    SELECT nvl(object_version_number,1), employment_history_id
    FROM HZ_EMPLOYMENT_HISTORY
    WHERE party_id = l_party_id
    AND nvl(UPPER(ltrim(rtrim(employed_by_name_company))),-99) = nvl(UPPER(ltrim(rtrim(l_company))),-99)
    AND nvl(UPPER(ltrim(rtrim(employed_as_title))),-99) = nvl(UPPER(ltrim(rtrim(l_title))), -99)
    AND nvl(trunc(begin_date),sysdate) = nvl(trunc(l_begin_date),sysdate)
    AND status in ('A','I')
    AND rownum = 1;

Line: 988

    SELECT nvl(object_version_number,1), employment_history_id
    FROM HZ_WORK_CLASS
    WHERE work_class_id = l_work_class_id
    AND rownum = 1;

Line: 994

    SELECT nvl(object_version_number,1), work_class_id
    FROM HZ_WORK_CLASS
    WHERE employment_history_id = l_employ_hist_id
    AND UPPER(ltrim(rtrim(WORK_CLASS_NAME))) = UPPER(ltrim(rtrim(l_work_class_name)))
    AND status in ('A','I')
    AND rownum = 1;

Line: 1050

    SELECT nvl(object_version_number,1), party_id
    FROM HZ_PERSON_INTEREST
    WHERE person_interest_id = l_interest_id
    AND rownum = 1;

Line: 1058

    SELECT nvl(object_version_number,1), person_interest_id
    FROM HZ_PERSON_INTEREST
    WHERE party_id = l_party_id
    AND (
         (nvl(INTEREST_TYPE_CODE,'X') = nvl(l_interest_type_code,'X') AND
          nvl(SUB_INTEREST_TYPE_CODE,'X') = nvl(l_sub_interest_type_code,'X'))
         OR
         UPPER(ltrim(rtrim(INTEREST_NAME))) = UPPER(ltrim(rtrim(l_interest_name)))
        )
    AND status in ('A','I')
    AND rownum = 1;

Line: 1115

    SELECT nvl(object_version_number,1), party_site_id
    FROM HZ_PARTY_SITE_USES
    WHERE party_site_use_id = l_site_use_id
    AND rownum = 1;

Line: 1121

    SELECT nvl(object_version_number,1), party_site_use_id
    FROM HZ_PARTY_SITE_USES
    WHERE party_site_id = l_party_site_id
    AND site_use_type = l_site_use_type
    AND status in ('A','I')
    AND rownum = 1;

Line: 1179

    SELECT rel.object_version_number, p.object_version_number
    FROM HZ_RELATIONSHIPS rel, HZ_PARTIES p
    WHERE rel.subject_id = l_subject_id
    AND rel.object_id = l_object_id
    AND rel.relationship_id = l_rel_id
    AND rel.party_id = p.party_id
    AND rel.status in ('A','I')
    AND rownum = 1;

Line: 1190

    SELECT rel.object_version_number, p.object_version_number, rel.relationship_id
    FROM HZ_RELATIONSHIPS rel, HZ_PARTIES p
    WHERE rel.subject_id = l_subject_id
    AND rel.object_id = l_object_id
    AND rel.relationship_type = l_relationship_type
    AND rel.relationship_code = l_relationship_code
    AND sysdate between rel.start_date and nvl(rel.end_date, sysdate)
    AND rel.party_id = p.party_id
    AND rel.status in ('A','I')
    AND rownum = 1;

Line: 1240

    SELECT nvl(object_version_number,1), org_contact_id
    FROM HZ_ORG_CONTACT_ROLES
    WHERE org_contact_role_id = l_role_id
    AND rownum = 1;

Line: 1246

    SELECT nvl(object_version_number,1), org_contact_role_id
    FROM HZ_ORG_CONTACT_ROLES
    WHERE org_contact_id = l_org_contact_id
    AND role_type = l_role_type
    AND status in ('A','I')
    AND rownum = 1;

Line: 1295

    x_last_update_date           OUT NOCOPY DATE,
    x_return_status              OUT NOCOPY VARCHAR2
  ) IS
    CURSOR is_cert_id_exist(l_cert_id NUMBER) IS
    SELECT last_update_date, party_id
    FROM HZ_CERTIFICATIONS
    WHERE certification_id = l_cert_id
    AND rownum = 1;

Line: 1305

    SELECT last_update_date, certification_id
    FROM HZ_CERTIFICATIONS
    WHERE party_id = l_party_id
    AND certification_name = l_cert_name
    AND status in ('A','I')
    AND rownum = 1;

Line: 1317

      FETCH is_cert_exist INTO x_last_update_date, px_certification_id;

Line: 1321

      FETCH is_cert_id_exist INTO x_last_update_date, l_party_id;

Line: 1352

    x_last_update_date           OUT NOCOPY DATE,
    x_return_status              OUT NOCOPY VARCHAR2
  ) IS
    CURSOR is_fin_exist(l_fin_prof_id NUMBER) IS
    SELECT last_update_date, party_id
    FROM HZ_FINANCIAL_PROFILE
    WHERE financial_profile_id = l_fin_prof_id
    AND rownum = 1;

Line: 1365

      x_last_update_date := NULL;

Line: 1368

      FETCH is_fin_exist INTO x_last_update_date, l_party_id;

Line: 1408

    SELECT nvl(object_version_number,1), owner_table_name, owner_table_id
    FROM HZ_CODE_ASSIGNMENTS
    WHERE code_assignment_id = l_code_assignment_id
    AND rownum = 1;

Line: 1415

    SELECT nvl(object_version_number,1), code_assignment_id
    FROM HZ_CODE_ASSIGNMENTS
    WHERE owner_table_name = l_owner_table_name
    AND owner_table_id = l_owner_table_id
    AND class_category = l_class_category
    AND class_code = l_class_code
    AND sysdate between start_date_active and nvl(end_date_active, sysdate)
    AND status in ('A','I')
    AND rownum = 1;

Line: 1474

    SELECT nvl(object_version_number,1)
    FROM HZ_PARTY_PREFERENCES
    WHERE party_id = l_party_id
    AND module = l_module
    AND category = l_category
    AND preference_code = l_preference_code
    AND rownum = 1;

Line: 1517

    SELECT nvl(object_version_number,1), party_id
    FROM HZ_CREDIT_RATINGS
    WHERE credit_rating_id = l_credit_rating_id
    AND rownum = 1;

Line: 1524

    SELECT nvl(object_version_number,1), credit_rating_id
    FROM HZ_CREDIT_RATINGS
    WHERE party_id = l_party_id
    AND nvl(rating_organization,'A') = nvl(l_rating_organization,'A')
    AND trunc(nvl(rated_as_of_date,sysdate)) = trunc(nvl(l_rated_as_of_date,sysdate))
    AND status in ('A','I')
    AND rownum = 1;

Line: 1583

    SELECT nvl(object_version_number,1), party_id
    FROM HZ_FINANCIAL_REPORTS
    WHERE financial_report_id = l_fin_report_id
    AND rownum = 1;

Line: 1591

    SELECT nvl(object_version_number,1), financial_report_id
    FROM HZ_FINANCIAL_REPORTS
    WHERE party_id = l_party_id
    AND type_of_financial_report = l_type_of_fin_report
    AND document_reference = l_doc_reference
    AND (trunc(date_report_issued) = trunc(l_date_report_issued) OR
         issued_period = l_issued_period OR
         sysdate between nvl(report_start_date,sysdate) and nvl(report_end_date, sysdate))
    AND status in ('A','I')
    AND rownum = 1;

Line: 1648

    SELECT nvl(object_version_number,1), financial_report_id
    FROM HZ_FINANCIAL_NUMBERS
    WHERE financial_number_id = l_fin_number_id
    AND rownum = 1;

Line: 1654

    SELECT nvl(object_version_number,1), financial_number_id
    FROM HZ_FINANCIAL_NUMBERS
    WHERE financial_report_id = l_fin_report_id
    AND financial_number_name = l_fin_number_name
    AND status in ('A','I')
    AND rownum = 1;

Line: 1706

    SELECT nvl(object_version_number,1), cust_account_role_id
    FROM HZ_ROLE_RESPONSIBILITY
    WHERE responsibility_id = l_resp_id
    AND rownum = 1;

Line: 1712

    SELECT nvl(object_version_number,1), responsibility_id
    FROM HZ_ROLE_RESPONSIBILITY
    WHERE cust_account_role_id = l_cac_id
    AND responsibility_type = l_resp_type
    AND rownum = 1;

Line: 1765

    SELECT nvl(object_version_number,1), cust_account_id, site_use_id
    FROM HZ_CUSTOMER_PROFILES
    WHERE cust_account_profile_id = l_cust_acct_prof_id
    AND rownum = 1;

Line: 1771

    SELECT nvl(object_version_number,1), cust_account_profile_id
    FROM HZ_CUSTOMER_PROFILES
    WHERE cust_account_id = l_ca_id
    AND nvl(site_use_id, -99) = nvl(l_site_use_id, -99)
    AND profile_class_id = l_profile_class_id
    AND status in ('A','I')
    AND rownum = 1;

Line: 1826

    SELECT nvl(object_version_number,1), cust_account_profile_id
    FROM HZ_CUST_PROFILE_AMTS
    WHERE cust_acct_profile_amt_id = l_cust_prof_amt_id
    AND rownum = 1;

Line: 1832

    SELECT nvl(object_version_number,1), cust_acct_profile_amt_id
    FROM HZ_CUST_PROFILE_AMTS
    WHERE cust_account_profile_id = l_cap_id
    AND currency_code = l_currency_code
    AND rownum = 1;

Line: 1880

    SELECT nvl(object_version_number,1)
    FROM HZ_CUST_ACCT_RELATE
    WHERE cust_account_id = l_ca_id
    AND related_cust_account_id = l_rca_id
    AND status in ('A','I')
    AND rownum = 1;

Line: 1912

    x_last_update_date           OUT NOCOPY DATE
  ) IS
    CURSOR is_payment_method_exist(l_pm_id NUMBER) IS
    SELECT last_update_date
    FROM RA_CUST_RECEIPT_METHODS
    WHERE cust_receipt_method_id = l_pm_id
    AND rownum = 1;

Line: 1921

    FETCH is_payment_method_exist INTO x_last_update_date;

Line: 1958

    SELECT owner_table_id, owner_table_name
    FROM HZ_CONTACT_POINTS
    WHERE contact_point_id = l_contact_point_id
    AND contact_point_type = l_contact_point_type;

Line: 1964

    SELECT party_id
    FROM HZ_PARTY_SITES
    WHERE party_site_id = l_ps_id;

Line: 1969

    SELECT 'X'
    FROM HZ_LOCATIONS
    WHERE location_id = l_loc_id;

Line: 1974

    SELECT 'X'
    FROM HZ_PARTIES
    WHERE party_id = l_party_id;

Line: 1979

    SELECT r.object_id
    FROM HZ_ORG_CONTACTS oc, HZ_RELATIONSHIPS r
    WHERE oc.org_contact_id = l_org_contact_id
    AND oc.party_relationship_id = r.relationship_id
    AND r.object_type = 'ORGANIZATION'
    AND r.subject_type = 'PERSON'
    AND rownum = 1;

Line: 1988

    SELECT party_id
    FROM HZ_CUST_ACCOUNTS
    WHERE cust_account_id = l_cust_acct_id;

Line: 1993

    SELECT cust_account_id
    FROM HZ_CUST_ACCT_SITES_ALL
    WHERE cust_acct_site_id = l_cust_acct_site_id;

Line: 1998

    SELECT cust_acct_site_id
    FROM HZ_CUST_SITE_USES
    WHERE site_use_id = l_site_use_id;

Line: 2003

    SELECT cust_account_id, nvl(cust_acct_site_id, -99)
    FROM HZ_CUST_ACCOUNT_ROLES
    WHERE cust_account_role_id = l_cust_acct_role_id;

Line: 2007

    l_create_update_flag       VARCHAR2(1);

Line: 2205

    x_last_update_date          OUT NOCOPY DATE,
    x_return_status              OUT NOCOPY VARCHAR2
  ) IS
    CURSOR is_usg_exist(l_party_id NUMBER, l_party_usage_code VARCHAR2 ) IS
    SELECT last_update_date
    FROM HZ_PARTY_USG_ASSIGNMENTS
    WHERE PARTY_USAGE_CODE = l_party_usage_code
	AND PARTY_ID= l_party_id
    AND rownum = 1;

Line: 2219

      x_last_update_date := NULL;

Line: 2222

      FETCH is_usg_exist INTO x_last_update_date;

Line: 2227

     x_last_update_date := NULL;

Line: 2331

      SELECT party_type FROM hz_parties
      WHERE party_id = p_parent_id;

Line: 2335

      SELECT party_type
      FROM hz_parties p, hz_cust_accounts ca
      WHERE p.party_id = ca.party_id
      AND ca.cust_account_id = p_parent_id;

Line: 4015

    SELECT d.business_object_code, d.child_bo_code, d.tca_mandated_flag,
           d.user_mandated_flag, d.root_node_flag, d.entity_name
    FROM hz_bus_obj_definitions d
    start with d.business_object_code = l_bus_obj and d.user_mandated_flag = 'Y'
    connect by prior d.child_bo_code = d.business_object_code and d.user_mandated_flag = 'Y'
    group by d.business_object_code, d.child_bo_code, d.tca_mandated_flag,
             d.user_mandated_flag, d.root_node_flag, d.entity_name;

Line: 4071

    SELECT d.business_object_code, d.child_bo_code, d.tca_mandated_flag,
           d.user_mandated_flag, d.root_node_flag, d.entity_name
    FROM hz_bus_obj_definitions d
    start with d.business_object_code in (l_phone, l_email, l_telex, l_web, l_edi, l_eft, l_sms) and d.user_mandated_flag = 'Y'
    connect by prior d.child_bo_code = d.business_object_code
                and d.user_mandated_flag = 'Y'
    group by d.business_object_code, d.child_bo_code, d.tca_mandated_flag,
             d.user_mandated_flag, d.root_node_flag, d.entity_name;