APPS.ZX_PARTY_MERGE_PKG SQL Statements

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

Line: 46

  l_update_reg_from_date        DATE;

Line: 47

  l_update_reg_to_date          DATE;

Line: 61

    select registration_id, registration_number, effective_from, effective_to,
           LEGAL_LOCATION_ID, REGISTRATION_SOURCE_CODE, REGISTRATION_REASON_CODE,
           REP_TAX_AUTHORITY_ID, COLL_TAX_AUTHORITY_ID
    from   zx_registrations reg, zx_party_tax_profile prof
    where  reg.PARTY_TAX_PROFILE_ID = prof.PARTY_TAX_PROFILE_ID
    and    prof.party_id = p_fk_id;

Line: 135

             l_update_reg_from_date := l_to_start_date;

Line: 137

             l_update_reg_from_date := l_from_start_date;

Line: 141

             l_update_reg_to_date := null;

Line: 143

             l_update_reg_to_date := l_from_end_date;

Line: 145

             l_update_reg_to_date := l_to_end_date;

Line: 168

          update zx_registrations
	         set merged_to_registration_id   = l_registration_id_to,
                              effective_to       = sysdate,
	                      last_update_date   = sysdate,
	                      last_updated_by    = g_user_id,
	                      last_update_login  = g_login_id,
                	   object_version_number = object_version_number+1
                           where registration_id = l_registration_id_from;

Line: 177

           update zx_registrations
	          set effective_from             = l_update_reg_from_date,
                      effective_to               = l_update_reg_to_date,
                      LEGAL_LOCATION_ID          = l_location_id_to,
                      REGISTRATION_SOURCE_CODE   = l_reg_src_code_to,
                      REGISTRATION_REASON_CODE   = l_reg_reason_code_to,
                      REP_TAX_AUTHORITY_ID       = l_rep_tax_auth_id_to,
                      COLL_TAX_AUTHORITY_ID      = l_coll_tax_auth_id_to,
                      last_update_date           = sysdate,
	              last_updated_by            = g_user_id,
	              last_update_login          = g_login_id,
      		     object_version_number       = object_version_number+1
                   where registration_id         = l_registration_id_to;

Line: 192

            arp_message.set_name('AR','AR_ROWS_UPDATED');

Line: 235

    select Party_Tax_Profile_id
    from   zx_party_tax_profile prof
    where  prof.party_id = p_fk_id
      and  prof.party_type_code = 'THIRD_PARTY';

Line: 244

    select  code_assignment_id, class_category, class_code, END_DATE_ACTIVE
    from    hz_code_assignments
    where   owner_table_name = 'ZX_PARTY_TAX_PROFILE'
    and     owner_table_id = p_ptp_id
    and     NVL(END_DATE_ACTIVE,SYSDATE) >= sysdate;

Line: 254

    select  distinct class_category, class_code, END_DATE_ACTIVE
    from    hz_code_assignments
    where   owner_table_name = 'ZX_PARTY_TAX_PROFILE'
    and     owner_table_id = p_ptp_id
    and     NVL(END_DATE_ACTIVE,SYSDATE) >= NVL(p_end_date,SYSDATE)
    and     class_category = p_class_category
    and     class_code = p_class_code
    group by class_category, class_code, END_DATE_ACTIVE;

Line: 310

      update zx_party_tax_profile
	 set merged_to_ptp_id   = l_ptp_id_to,
         MERGED_STATUS_CODE      = 'MERGED',
	     last_update_date   = sysdate,
	     last_updated_by    = g_user_id,
	     last_update_login  = g_login_id,
             object_version_number = object_version_number+1
       where Party_Tax_Profile_id = l_ptp_id_from;

Line: 320

       arp_message.set_name('AR','AR_ROWS_UPDATED');

Line: 332

            update hz_code_assignments
	    set owner_table_id     = l_ptp_id_to,
	    last_update_date       = sysdate,
	    last_updated_by        = g_user_id,
	    last_update_login      = g_login_id,
 	    object_version_number  = object_version_number+1
            where code_assignment_id = code_assig.code_assignment_id;

Line: 406

     update zx_registrations
	 set TAX_AUTHORITY_ID   = p_ptp_id_to,
	     last_update_date   = sysdate,
	     last_updated_by    = g_user_id,
	     last_update_login  = g_login_id,
		object_version_number = object_version_number+1
      where TAX_AUTHORITY_ID = p_ptp_id_from;

Line: 414

     update zx_registrations
	 set REP_TAX_AUTHORITY_ID   = p_ptp_id_to,
	     last_update_date   = sysdate,
	     last_updated_by    = g_user_id,
	     last_update_login  = g_login_id,
		object_version_number = object_version_number+1
      where REP_TAX_AUTHORITY_ID = p_ptp_id_from;

Line: 422

     update zx_registrations
	 set COLL_TAX_AUTHORITY_ID   = p_ptp_id_to,
	     last_update_date   = sysdate,
	     last_updated_by    = g_user_id,
	     last_update_login  = g_login_id,
		object_version_number = object_version_number+1
      where COLL_TAX_AUTHORITY_ID = p_ptp_id_from;

Line: 433

     update zx_taxes_b
	 set REP_TAX_AUTHORITY_ID   = p_ptp_id_to,
	     last_update_date   = sysdate,
	     last_updated_by    = g_user_id,
	     last_update_login  = g_login_id,
		object_version_number = object_version_number+1
      where REP_TAX_AUTHORITY_ID = p_ptp_id_from;

Line: 441

     update zx_taxes_b
	 set COLL_TAX_AUTHORITY_ID   = p_ptp_id_to,
	     last_update_date   = sysdate,
	     last_updated_by    = g_user_id,
	     last_update_login  = g_login_id,
		object_version_number = object_version_number+1
      where COLL_TAX_AUTHORITY_ID = p_ptp_id_from;

Line: 452

     update zx_regimes_b
	 set REP_TAX_AUTHORITY_ID   = p_ptp_id_to,
	     last_update_date   = sysdate,
	     last_updated_by    = g_user_id,
	     last_update_login  = g_login_id,
		object_version_number = object_version_number+1
      where REP_TAX_AUTHORITY_ID = p_ptp_id_from;

Line: 460

     update zx_regimes_b
	 set COLL_TAX_AUTHORITY_ID   = p_ptp_id_to,
	     last_update_date   = sysdate,
	     last_updated_by    = g_user_id,
	     last_update_login  = g_login_id,
		object_version_number = object_version_number+1
      where COLL_TAX_AUTHORITY_ID = p_ptp_id_from;

Line: 470

     update zx_jurisdictions_b
	 set REP_TAX_AUTHORITY_ID   = p_ptp_id_to,
	     last_update_date   = sysdate,
	     last_updated_by    = g_user_id,
	     last_update_login  = g_login_id,
		object_version_number = object_version_number+1
      where REP_TAX_AUTHORITY_ID = p_ptp_id_from;

Line: 478

     update zx_jurisdictions_b
	 set COLL_TAX_AUTHORITY_ID   = p_ptp_id_to,
	     last_update_date   = sysdate,
	     last_updated_by    = g_user_id,
	     last_update_login  = g_login_id,
		object_version_number = object_version_number+1
      where COLL_TAX_AUTHORITY_ID = p_ptp_id_from;

Line: 488

     update zx_exemptions
	 set ISSUING_TAX_AUTHORITY_ID   = p_ptp_id_to,
	     last_update_date   = sysdate,
	     last_updated_by    = g_user_id,
	     last_update_login  = g_login_id,
		object_version_number = object_version_number+1
      where ISSUING_TAX_AUTHORITY_ID = p_ptp_id_from;

Line: 497

      arp_message.set_name('AR','AR_ROWS_UPDATED');

Line: 533

    select TAX_EXEMPTION_ID
          , EXEMPT_CERTIFICATE_NUMBER
          , effective_from
          , effective_to
          , EXEMPTION_TYPE_CODE
          , EXEMPTION_STATUS_CODE
          ,TAX_REGIME_CODE
    from   zx_exemptions exemp
    where  party_tax_profile_id = p_fk_id;

Line: 550

    select TAX_EXEMPTION_ID
    from   zx_exemptions exemp
    where  party_tax_profile_id = p_to_fk_id
      and  exempt_certificate_number = l_certificate_number
      and  effective_from = l_effective_from
      and  nvl(effective_to,l_effective_to) = l_effective_to
      and  exemption_type_code = l_type_code
      and  exemption_status_code = l_status_code
      and  tax_regime_code = l_tax_regime_code;

Line: 604

         update zx_exemptions
	     set --merged_to_exemption_id   = l_exemption_id_to,
             party_tax_profile_id  = p_to_fk_id,
	     last_update_date      = sysdate,
	     last_updated_by       = g_user_id,
	     last_update_login     = g_login_id,
             object_version_number = object_version_number+1
         where TAX_EXEMPTION_ID = rec_exe.tax_exemption_id;

Line: 616

       arp_message.set_name('AR','AR_ROWS_UPDATED');

Line: 663

    select PROCESS_FOR_APPLICABILITY_FLAG
    from   zx_party_tax_profile ptp
    where  ptp.party_tax_profile_id = p_ptp_id;

Line: 671

    select a.REGISTRATION_TYPE_CODE, a.REGISTRATION_NUMBER, a.ROUNDING_RULE_CODE,
            a.SELF_ASSESS_FLAG, a.INCLUSIVE_TAX_FLAG
    from zx_registrations a, zx_party_tax_profile b
    where   b.party_tax_profile_id = p_ptp_id
    and     a.party_tax_profile_id = b.party_tax_profile_id;

Line: 701

   Commenting as per Nigel Updates 3511846
  OPEN Calculate_Tax_Flag(p_ptp_id_from);

Line: 789

    select Party_Tax_Profile_id, party_type_code
    from   zx_party_tax_profile prof
    where  prof.party_id = p_fk_id
      and  prof.party_type_code = 'THIRD_PARTY';