DBA Data[Home] [Help]

APPS.ARP_ADDS_MINUS99 SQL Statements

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

Line: 76

   select ar_location_combinations_s.nextval + arp_standard.sequence_offset
   from dual;
Line: 104

select ar_location_values_s.nextval + arp_standard.sequence_offset
from dual;
Line: 124

|     19-Jun-00  NIPATEL  Modified the cursor to select from multi-org    |
|                         table AR_VAT_TAX_ALL using C_ORG_ID for         |
|                         Location Flexfield Sharing project.             |
| EXAMPLE                                                                 |
|                                                                         |
+-------------------------------------------------------------------------*/


CURSOR ar_location_tax_account_c (c_org_id in number) IS
select
	tax_account_id,
	INTERIM_TAX_CCID,
	ADJ_CCID,
	EDISC_CCID,
	UNEDISC_CCID,
	FINCHRG_CCID,
	ADJ_NON_REC_TAX_CCID,
	EDISC_NON_REC_TAX_CCID,
	UNEDISC_NON_REC_TAX_CCID,
	FINCHRG_NON_REC_TAX_CCID
from ar_vat_tax_all vat
where tax_type='LOCATION'
and   org_id = c_org_id
and   trunc(sysdate) between start_date and nvl(end_date, trunc(sysdate));
Line: 171

select ar_location_accounts_s.nextval + arp_standard.sequence_offset
from dual;
Line: 181

|    Used to select distinct ORG_ID from AR_SYSTEM_PARAMETERS_ALL         |
|    to create one record per ORG_ID for a new location_id in             |
|    AR_LOCATION_ACCOUNTS_ALL                                             |
|                                                                         |
| REQUIRES                                                                |
|                                                                         |
| RETURNS                                                                 |
|    Returns distinct ORG_ID from AR_SYSTEM_PARAMETERS_ALL                |
|                                                                         |
| EXCEPTIONS RAISED                                                       |
|                                                                         |
| MODIFICATION HISTORY                                                    |
|     19-Jun-00  NIPATEL  Created for Location Flexfield Sharing project. |
|                                                                         |
|                                                                         |
+-------------------------------------------------------------------------*/

-- Organization Ids -3113 and -3114 are seeded in ar_system_parameters
-- and we do not want to create records in ar_location_accounts_all table
-- for these org_id's. Hence we do not select them in the cursor.

CURSOR organization_id_c is
select nvl(org_id, -99), location_structure_id
from   ar_system_parameters_all
where  nvl(org_id, -99) not in (-3113, -3114)
and    set_of_books_id <> -1;
Line: 231

       select ar_location_rates_s.nextval + arp_standard.sequence_offset
       from dual;
Line: 248

|   the states that can be selected.                                      |
|                                                                         |
| REQUIRES                                                                |
|   location_segment_qualifier Identifies which segment this is           |
|   value               Name of Child segment, eg the city of Belmont     |
|                                                                         |
| RETURNS                                                                 |
|   Segment value for parent, EG County of "San Mateo"                    |
|                                                                         |
| EXCEPTIONS RAISED                                                       |
|   Oracle Error        If any given child( city ) has two or more        |
|                       parents with different names, and postal codes    |
|                       cannot be be used to resolve the naming conflict. |
|   NO_DATA_FOUND       If no parent can be found.                        |
|                                                                         |
| NOTES                                                                   |
|                                                                         |
| EXAMPLE                                                                 |
|                                                                         |
|                                                                         |
| MODIFICATION HISTORY                                                    |
|    22-Jan-93  Nigel Smith        Created.                               |
|    04-Mar-93  Nigel Smith        BUGFIX, find_missing_parent_in_loc,    |
|                                  now cheks parent zip range not child   |
+-------------------------------------------------------------------------*/


FUNCTION find_missing_parent_in_loc( p_location_segment_qualifier in varchar2,
                                     p_value          in varchar2,
                                     p_postal_code    in varchar2 )
         return varchar2 IS

 parent_value varchar2(60);
Line: 285

   SELECT DISTINCT v1.location_segment_value  into parent_value
   from   ar_location_values v2,
          ar_location_values v1
   WHERE  v1.location_segment_id  = v2.parent_segment_id
   and    v2.location_segment_value = rtrim(ltrim(upper(p_value)))
   and    v2.location_segment_qualifier = p_location_segment_qualifier
   and    v1.location_structure_id = arp_standard.sysparm.location_structure_id
   and    v2.location_structure_id = arp_standard.sysparm.location_structure_id;
Line: 312

      SELECT DISTINCT v1.location_segment_value  into parent_value
      from   ar_location_values v2,
      ar_location_values v1,
      ar_location_rates  r1
      WHERE  v1.location_segment_id  = v2.parent_segment_id
      and    v2.location_segment_value = ltrim(rtrim(upper(p_value)))
      and    v2.location_segment_qualifier = p_location_segment_qualifier
      and    v2.location_segment_id = r1.location_segment_id
      and    v1.location_structure_id = arp_standard.sysparm.location_structure_id
      and    v2.location_structure_id = arp_standard.sysparm.location_structure_id
      and    p_postal_code between r1.from_postal_code and r1.to_postal_code
      and    sysdate between r1.start_date and r1.end_date;
Line: 355

 |   If the given value cannot be found in the table, it is inserted       |
 |   and the new location_segment_id is returned from the sequence         |
 |   AR_LOCATION_VALUES_S.nextval                                          |
 |                                                                         |
 | REQUIRES                                                                |
 |                                                                         |
 |   location_segment_qualifier Value set name used of this segment,eg City|
 |   segment_value       The value to be found/inserted, eg Belmont        |
 |   parent_segment_id   The unique ID of the parent that owns this        |
 |                       segment.                                          |
 | OPTIONAL                                                                |
 |                                                                         |
 |   Descriptive Flexfield    Attribute_category and Descriptive Flexfield |
 |                            information, used if this calls inserts a    |
 |                            new row in ar_location_values                |
 |                                                                         |
 |   Search_Precission        If an existing locaion could not be found    |
 |                            and the supplied location is this number of  |
 |                            characters wide, then reattempt the search   |
 |                            using just this precission. Allows user to   |
 |                            manually correct truncated Cities, if the    |
 |                            data they upload through the interface table |
 |                            only supplies the first N character of a city|
 |                            name.                                        |
 |                                                                         |
 | RETURNS                                                                 |
 |   LOCATION_SEGMENT_ID for the value.                                    |
 |                                                                         |
 | EXCEPTIONS RAISED                                                       |
 |                                                                         |
 | NOTES                                                                   |
 |                                                                         |
 | EXAMPLE                                                                 |
 |                                                                         |
 | MODIFICATION HISTORY                                                    |
 |    22-Jan-93  Nigel Smith   Created.                                    |
 |    17-Feb-93  Nigel Smith   Now stores all segment values in uppercase  |
 |                             comparisons are case independent            |
 |    23-Aug-93  Nigel Smith   Added support for Descriptive Flexfields    |
 |                             used by Tax Interface program               |
 +-------------------------------------------------------------------------*/


FUNCTION
   find_location_segment_id( location_segment_qualifier    in varchar2,
                     segment_value                 in varchar2,
                     segment_description           in varchar2,
                     parent_segment_id             in number,
                     ATTRIBUTE_CATEGORY            in varchar2 default 'TRIGGER',
                     ATTRIBUTE1                    in varchar2 default null,
                     ATTRIBUTE2                    in varchar2 default null,
                     ATTRIBUTE3                    in varchar2 default null,
                     ATTRIBUTE4                    in varchar2 default null,
                     ATTRIBUTE5                    in varchar2 default null,
                     ATTRIBUTE6                    in varchar2 default null,
                     ATTRIBUTE7                    in varchar2 default null,
                     ATTRIBUTE8                    in varchar2 default null,
                     ATTRIBUTE9                    in varchar2 default null,
                     ATTRIBUTE10                   in varchar2 default null,
                     ATTRIBUTE11                   in varchar2 default null,
                     ATTRIBUTE12                   in varchar2 default null,
                     ATTRIBUTE13                   in varchar2 default null,
                     ATTRIBUTE14                   in varchar2 default null,
                     ATTRIBUTE15                   in varchar2 default null,
		     SEARCH_PRECISSION		   in number   default null )
         return number IS

/*------------------------------------------------------------------------+
 | PRIVATE CURSOR                                                         |
 |   location_value_given_parent_c                                        |
 |                                                                        |
 | DESCRIPTION                                                            |
 |   Check AR_LOCATION_VALUES for existing location_segment_id given      |
 |   parent_segment_id and location_segment_value.                        |
 |                                                                        |
 | WARNING                                                                |
 |   DO NOT MODIFY THIS CURSOR ADDING ANY SELECT COLUMNS.                 |
 |   The select does not perform any table accesses, utilising index      |
 |   only access from AR_LOCATION_VALUES_U2.                              |
 |                                                                        |
 | EXPLAIN PLAN                                                           |
 |   OPERATION              OPTIONS         OBJECT_NAME                   |
 |   ---------------------- --------------- ----------------------------- |
 |   INDEX                  RANGE SCAN      AR_LOCATION_VALUES_U2         |
 |                                                                        |
 | MODIFICATION HISTORY                                                   |
 |    22-Jan-93  Nigel Smith   Created.                                   |
 +------------------------------------------------------------------------*/


CURSOR location_value_given_parent_c( p_location_segment_qualifier    in varchar2,
                                      p_segment_value     in varchar2,
                                      p_parent_segment_id in number) is
   select location_segment_id
   from   ar_location_values
   where  location_segment_qualifier = p_location_segment_qualifier
   and    location_structure_id = arp_standard.sysparm.location_structure_id
   and    parent_segment_id = p_parent_segment_id
   and    location_segment_value = rtrim(upper(p_segment_value), ' ');
Line: 460

   select location_segment_id
   from   ar_location_values
   where  location_segment_qualifier = p_location_segment_qualifier
   and    location_structure_id = arp_standard.sysparm.location_structure_id
   and    parent_segment_id = p_parent_segment_id
   and    substr(location_segment_value,1,p_search_precission)
	= substr(rtrim(upper(p_segment_value), ' '),1,p_search_precission);
Line: 473

   select location_segment_id
   from   ar_location_values
   where  location_segment_qualifier = p_location_segment_qualifier
   and    location_structure_id = arp_standard.sysparm.location_structure_id
   and    location_segment_value = rtrim(upper(p_segment_value), ' ' );
Line: 491

   location_segment_inserted := FALSE;
Line: 587

         goto insert_new_location;
Line: 599

<>

   /*-----------------------------------------------------------------------+
    | This value has never been used before, insert it into the value sets  |
    | table and return the unique id assoicated with this value             |
    +-----------------------------------------------------------------------*/

    location_segment_inserted := TRUE;
Line: 696

|   segment_value              The value to be found/inserted, eg Belmont |
|   or:                                                                   |
|   location_segment_id        Unique id for this location                |
|                                                                         |
| RETURNS                                                                 |
|   location_segment_id                                                   |
|   location_segment_value                                                |
|   location_segment_description                                          |
|   location_segment_qualifier                                            |
|   parent_segment_id                                                     |
|                                                                         |
| EXCEPTIONS RAISED                                                       |
|                                                                         |
| NOTES                                                                   |
|                                                                         |
| EXAMPLE                                                                 |
|                                                                         |
| MODIFICATION HISTORY                                                    |
|    17-JUN-93  Nigel Smith   Created.                                    |
+-------------------------------------------------------------------------*/


procedure location_information( location_segment_id          in number,
                                location_segment_qualifier   out NOCOPY varchar2,
                                location_segment_value       out NOCOPY varchar2,
                                location_segment_description out NOCOPY varchar2,
                                parent_segment_id out NOCOPY number ) is


begin

   select location_segment_value,
          location_segment_description,
          location_segment_qualifier,
          parent_segment_id
   into   location_segment_value,
          location_segment_description,
          location_segment_qualifier,
          parent_segment_id
   from   ar_location_values
   where  location_segment_id = location_information.location_segment_id;
Line: 749

   select location_segment_description,
          parent_segment_id
   into   location_segment_description,
          parent_segment_id
   from   ar_location_values v1
   where  v1.location_segment_qualifier = location_information.location_segment_qualifier
   and    v1.location_segment_value = ltrim(rtrim(upper(location_information.location_segment_value)))
   and    v1.location_structure_id = arp_standard.sysparm.location_structure_id;
Line: 762

      select max(location_segment_description), max(parent_segment_id)
      into   location_segment_description, parent_segment_id
      from   ar_location_values v1
      where  v1.location_segment_qualifier = location_information.location_segment_qualifier
      and    v1.location_structure_id = arp_standard.sysparm.location_structure_id
      and    v1.location_segment_value = ltrim(rtrim(location_information.location_segment_value));
Line: 864

 |   segment_value       The value to be inserted, eg Belmont              |
 |   parent_segment_id   The unique ID of the parent that owns this        |
 |                       segment.                                          |
 |                                                                         |
 | RETURNS                                                                 |
 |   LOCATION_SEGMENT_ID for the value.                                    |
 |                                                                         |
 | EXCEPTIONS RAISED                                                       |
 |                                                                         |
 | NOTES                                                                   |
 |                                                                         |
 | EXAMPLE                                                                 |
 |                                                                         |
 +-------------------------------------------------------------------------*/



FUNCTION ins_location_values( location_segment_qualifier     in varchar2,
                              segment_value      in varchar2,
                              segment_description in varchar2,
                              parent_segment_id  in varchar2,
                              ATTRIBUTE_CATEGORY in varchar2 default 'TRIGGER',
                              ATTRIBUTE1         in varchar2 default null,
                              ATTRIBUTE2         in varchar2 default null,
                              ATTRIBUTE3         in varchar2 default null,
                              ATTRIBUTE4         in varchar2 default null,
                              ATTRIBUTE5         in varchar2 default null,
                              ATTRIBUTE6         in varchar2 default null,
                              ATTRIBUTE7         in varchar2 default null,
                              ATTRIBUTE8         in varchar2 default null,
                              ATTRIBUTE9         in varchar2 default null,
                              ATTRIBUTE10        in varchar2 default null,
                              ATTRIBUTE11        in varchar2 default null,
                              ATTRIBUTE12        in varchar2 default null,
                              ATTRIBUTE13        in varchar2 default null,
                              ATTRIBUTE14        in varchar2 default null,
                              ATTRIBUTE15        in varchar2 default null
      ) return number IS

    location_id number;
Line: 926

   insert into ar_location_values( location_structure_id,
                                   location_segment_qualifier,
                                   location_segment_id,
                                   location_segment_value,
                                   location_segment_user_value,
                                   location_segment_description,
                                   parent_segment_id,
                                   request_id,
                                   program_application_id,
                                   program_id,
                                   program_update_date,
                                   created_by,
                                   creation_date,
                                   last_updated_by,
                                   last_update_date,
                                   last_update_login,
                                   attribute_category,
                                   attribute1,
                                   attribute2,
                                   attribute3,
                                   attribute4,
                                   attribute5,
                                   attribute6,
                                   attribute7,
                                   attribute8,
                                   attribute9,
                                   attribute10,
                                   attribute11,
                                   attribute12,
                                   attribute13,
                                   attribute14,
                                   attribute15 )
   VALUES ( arp_standard.sysparm.location_structure_id,
            location_segment_qualifier,
            location_id,
            location_segment_value,
            location_segment_user_value,
            location_segment_description,
            parent_segment_id,
            arp_standard.PROFILE.request_id,
            arp_standard.PROFILE.program_application_id,
            arp_standard.PROFILE.program_id,
            sysdate,
            arp_standard.profile.user_id,
            sysdate,
            arp_standard.profile.user_id,
            sysdate,
            arp_standard.PROFILE.last_update_login,
            attribute_category,
            attribute1,
            attribute2,
            attribute3,
            attribute4,
            attribute5,
            attribute6,
            attribute7,
            attribute8,
            attribute9,
            attribute10,
            attribute11,
            attribute12,
            attribute13,
            attribute14,
            attribute15 );
Line: 1065

 last_updated_by_tab          num_tab;
Line: 1066

 last_update_date_tab         date_tab;
Line: 1070

 program_update_date_tab      date_tab;
Line: 1071

 last_update_login_tab        num_tab;
Line: 1086

   org_id_tab.delete;
Line: 1087

   loc_structure_id_tab.delete;
Line: 1089

   location_account_id_tab.delete;
Line: 1090

   location_segment_id_tab.delete;
Line: 1091

   tax_account_ccid_tab.delete;
Line: 1092

   interim_tax_ccid_tab.delete;
Line: 1093

   adj_ccid_tab.delete;
Line: 1094

   edisc_ccid_tab.delete;
Line: 1095

   unedisc_ccid_tab.delete;
Line: 1096

   finchrg_ccid_tab.delete;
Line: 1097

   adj_non_rec_tax_ccid_tab.delete;
Line: 1098

   edisc_non_rec_tax_ccid_tab.delete;
Line: 1099

   unedisc_non_rec_tax_ccid_tab.delete;
Line: 1100

   finchrg_non_rec_tax_ccid_tab.delete;
Line: 1101

   created_by_tab.delete;
Line: 1102

   creation_date_tab.delete;
Line: 1103

   last_updated_by_tab.delete;
Line: 1104

   last_update_date_tab.delete;
Line: 1105

   request_id_tab.delete;
Line: 1106

   program_application_id_tab.delete;
Line: 1107

   program_id_tab.delete;
Line: 1108

   program_update_date_tab.delete;
Line: 1109

   last_update_login_tab.delete;
Line: 1110

   organization_id_tab.delete;
Line: 1171

           last_updated_by_tab(i)          := arp_standard.profile.user_id;
Line: 1172

           last_update_date_tab(i)         := sysdate;
Line: 1177

           program_update_date_tab(i)      := sysdate;
Line: 1178

           last_update_login_tab(i)        := arp_standard.PROFILE.last_update_login;
Line: 1184

           insert into ar_location_accounts_all
                                 ( location_value_account_id,
                                   location_segment_id,
                                   tax_account_ccid,
                                   interim_tax_ccid,
                                   adj_ccid,
                                   edisc_ccid,
                                   unedisc_ccid,
                                   finchrg_ccid,
                                   adj_non_rec_tax_ccid,
                                   edisc_non_rec_tax_ccid,
                                   unedisc_non_rec_tax_ccid,
                                   finchrg_non_rec_tax_ccid,
                                   created_by,
                                   creation_date,
                                   last_updated_by,
                                   last_update_date,
                                   request_id,
                                   program_application_id,
                                   program_id,
                                   program_update_date,
                                   last_update_login,
                                   org_id)
   		VALUES
               ( location_account_id_tab(i),
            	 location_segment_id_tab(i),
                 tax_account_ccid_tab(i),
                 interim_tax_ccid_tab(i),
                 adj_ccid_tab(i),
                 edisc_ccid_tab(i),
                 unedisc_ccid_tab(i),
                 finchrg_ccid_tab(i),
                 adj_non_rec_tax_ccid_tab(i),
                 edisc_non_rec_tax_ccid_tab(i),
                 unedisc_non_rec_tax_ccid_tab(i),
                 finchrg_non_rec_tax_ccid_tab(i),
                 created_by_tab(i),
                 creation_date_tab(i),
                 last_updated_by_tab(i),
                 last_update_date_tab(i),
                 request_id_tab(i),
                 program_application_id_tab(i),
                 program_id_tab(i),
                 program_update_date_tab(i),
                 last_update_login_tab(i),
                 organization_id_tab(i) );
Line: 1379

   insert into ar_location_rates( location_rate_id,
                                  location_segment_id,
                                  from_postal_code,
                                  to_postal_code,
                                  start_date,
                                  end_date,
                                  tax_rate,
                                  request_id,
                                  program_application_id,
                                  program_id,
                                  program_update_date,
                                  created_by,
                                  creation_date,
                                  last_updated_by,
                                  last_update_date,
                                  last_update_login,
                                  attribute_category,
                                  attribute1,
                                  attribute2,
                                  attribute3,
                                  attribute4,
                                  attribute5,
                                  attribute6,
                                  attribute7,
                                  attribute8,
                                  attribute9,
                                  attribute10,
                                  attribute11,
                                  attribute12,
                                  attribute13,
                                  attribute14,
                                  attribute15,
				  override_structure_id,
				  override_rate1,
				  override_rate2,
				  override_rate3,
				  override_rate4,
				  override_rate5,
				  override_rate6,
				  override_rate7,
				  override_rate8,
				  override_rate9,
				  override_rate10  )
   VALUES ( location_rate_id,
            location_segment_id,
            from_postal_code,
            to_postal_code,
            start_date,
            end_date,
            tax_rate,
            arp_standard.PROFILE.request_id,
            arp_standard.PROFILE.program_application_id,
            arp_standard.PROFILE.program_id,
            sysdate,
            arp_standard.profile.user_id,
            sysdate,
            arp_standard.profile.user_id,
            sysdate,
            arp_standard.PROFILE.last_update_login,
            attribute_category,
            attribute1,
            attribute2,
            attribute3,
            attribute4,
            attribute5,
            attribute6,
            attribute7,
            attribute8,
            attribute9,
            attribute10,
            attribute11,
            attribute12,
            attribute13,
            attribute14,
            attribute15,
            override_structure_id,
            override_rate1,
            override_rate2,
            override_rate3,
            override_rate4,
            override_rate5,
            override_rate6,
            override_rate7,
            override_rate8,
            override_rate9,
            override_rate10  );
Line: 1562

   insert into ar_location_rates( location_rate_id,
                                  location_segment_id,
                                  from_postal_code,
                                  to_postal_code,
                                  start_date,
                                  end_date,
                                  tax_rate,
                                  request_id,
                                  program_application_id,
                                  program_id,
                                  program_update_date,
                                  created_by,
                                  creation_date,
                                  last_updated_by,
                                  last_update_date,
                                  last_update_login,
                                  attribute_category,
                                  attribute1,
                                  attribute2,
                                  attribute3,
                                  attribute4,
                                  attribute5,
                                  attribute6,
                                  attribute7,
                                  attribute8,
                                  attribute9,
                                  attribute10,
                                  attribute11,
                                  attribute12,
                                  attribute13,
                                  attribute14,
                                  attribute15,
				  override_structure_id,
				  override_rate1,
				  override_rate2,
				  override_rate3,
				  override_rate4,
				  override_rate5,
				  override_rate6,
				  override_rate7,
				  override_rate8,
				  override_rate9,
				  override_rate10  )
   VALUES ( ar_location_rates_s.nextval + arp_standard.sequence_offset,
            location_segment_id,
            from_postal_code,
            to_postal_code,
            start_date,
            end_date,
            tax_rate,
            arp_standard.PROFILE.request_id,
            arp_standard.PROFILE.program_application_id,
            arp_standard.PROFILE.program_id,
            sysdate,
            arp_standard.profile.user_id,
            sysdate,
            arp_standard.profile.user_id,
            sysdate,
            arp_standard.PROFILE.last_update_login,
            attribute_category,
            attribute1,
            attribute2,
            attribute3,
            attribute4,
            attribute5,
            attribute6,
            attribute7,
            attribute8,
            attribute9,
            attribute10,
            attribute11,
            attribute12,
            attribute13,
            attribute14,
            attribute15,
            override_structure_id,
            override_rate1,
            override_rate2,
            override_rate3,
            override_rate4,
            override_rate5,
            override_rate6,
            override_rate7,
            override_rate8,
            override_rate9,
            override_rate10  );
Line: 1720

   insert into ar_location_combinations( LOCATION_ID,
                                         LOCATION_STRUCTURE_ID,
                                         ENABLED_FLAG,
                                         LAST_UPDATED_BY,
                                         LAST_UPDATE_DATE,
                                         SUMMARY_FLAG,
                                         PROGRAM_APPLICATION_ID,
                                         PROGRAM_ID,
                                         PROGRAM_UPDATE_DATE,
                                         REQUEST_ID,
                                         START_DATE_ACTIVE,
                                         END_DATE_ACTIVE,
                                         LOCATION_ID_SEGMENT_1,
                                         LOCATION_ID_SEGMENT_2,
                                         LOCATION_ID_SEGMENT_3,
                                         LOCATION_ID_SEGMENT_4,
                                         LOCATION_ID_SEGMENT_5,
                                         LOCATION_ID_SEGMENT_6,
                                         LOCATION_ID_SEGMENT_7,
                                         LOCATION_ID_SEGMENT_8,
                                         LOCATION_ID_SEGMENT_9,
                                         LOCATION_ID_SEGMENT_10,
                                         CREATED_BY,
                                         CREATION_DATE)
   VALUES
   (
    LOCATION_ID,
    LOCATION_STRUCTURE_ID,
    ENABLED_FLAG,
    arp_standard.PROFILE.USER_ID,
    sysdate,
    'N',
    arp_standard.PROFILE.PROGRAM_APPLICATION_ID,
    arp_standard.PROFILE.PROGRAM_ID,
    sysdate,
    arp_standard.PROFILE.REQUEST_ID,
    START_DATE_ACTIVE,
    END_DATE_ACTIVE,
    LOCATION_ID_SEGMENT_1,
    LOCATION_ID_SEGMENT_2,
    LOCATION_ID_SEGMENT_3,
    LOCATION_ID_SEGMENT_4,
    LOCATION_ID_SEGMENT_5,
    LOCATION_ID_SEGMENT_6,
    LOCATION_ID_SEGMENT_7,
    LOCATION_ID_SEGMENT_8,
    LOCATION_ID_SEGMENT_9,
    LOCATION_ID_SEGMENT_10,
    arp_standard.PROFILE.USER_ID,
    sysdate );
Line: 1771

    location_combination_inserted := TRUE;
Line: 1819

select  v1.location_segment_id,
 v2.location_segment_id,
 v3.location_segment_id
from    ar_location_values v1,
 ar_location_values v2,
 ar_location_values v3
where   v1.location_segment_value = upper(param.STATE)
 and v2.location_segment_value = upper(param.COUNTY)
 and v3.location_segment_value = upper(param.CITY)
and     v2.parent_segment_id = v1.location_segment_id
 and v3.parent_segment_id = v2.location_segment_id
and     v1.location_structure_id = arp_standard.sysparm.location_structure_id and
v2.location_structure_id = arp_standard.sysparm.location_structure_id and
v3.location_structure_id = arp_standard.sysparm.location_structure_id
and     v1.location_segment_qualifier = 'STATE'
 and v2.location_segment_qualifier = 'COUNTY'
 and v3.location_segment_qualifier = 'CITY' ;
Line: 1848

select  rowid
from    ar_sales_tax tax
where   tax.location_id = p_location_id
and     tax.enabled_flag = 'Y'
and     not exists (
        select
        'x'
        from   ar_location_rates r1,
 ar_location_rates r2,
 ar_location_rates r3
        where  r1.location_segment_id = p_location_id_segment_1
 and r2.location_segment_id = p_location_id_segment_2
 and r3.location_segment_id = p_location_id_segment_3
        and    greatest( r1.from_postal_code,
 r2.from_postal_code,
 r3.from_postal_code ) <= least( r1.to_postal_code,
 r2.to_postal_code,
 r3.to_postal_code )
        and    greatest( r1.start_date ,
 r2.start_date ,
 r3.start_date  ) <= least( r1.end_date ,
 r2.end_date ,
 r3.end_date  )
        and    tax.location1_rate = decode( r3.override_rate1, null, nvl(r1.tax_rate,0), r3.override_rate1)
 and tax.location2_rate = decode( r3.override_rate2, null, nvl(r2.tax_rate,0), r3.override_rate2)
 and tax.location3_rate = decode( r3.override_rate3, null, nvl(r3.tax_rate,0), r3.override_rate3)
        and    tax.from_postal_code      = greatest( r1.from_postal_code,
 r2.from_postal_code,
 r3.from_postal_code )
        and    tax.to_postal_code        = least( r1.to_postal_code,
 r2.to_postal_code,
 r3.to_postal_code )
        and    tax.start_date            = greatest( r1.start_date ,
 r2.start_date ,
 r3.start_date  )
        and    tax.end_date              = least( r1.end_date ,
 r2.end_date ,
 r3.end_date  ));
Line: 1897

 select location_id
        from   ar_location_combinations cc
        where  location_structure_id = arp_standard.sysparm.location_structure_id
        and    cc.LOCATION_ID_SEGMENT_1 = p_LOCATION_ID_SEGMENT_1
 and cc.LOCATION_ID_SEGMENT_2 = p_LOCATION_ID_SEGMENT_2
 and cc.LOCATION_ID_SEGMENT_3 = p_LOCATION_ID_SEGMENT_3;
Line: 2010

      select territory_short_name into short_name from fnd_territories_vl
      where territory_code = terr_short_name.territory_code;
Line: 2047

 | In order to do this, it may be necessary to insert new items into       |
 | the tables: AR_LOCATION_VALUES and AR_LOCATION_COMBINATIONS             |
 |                                                                         |
 | REQUIRES                                                                |
 |   City                   City column of RA_ADDRESSES                    |
 |   State                  State column of RA_ADDRESSES                   |
 |   County                 County column of RA_ADDRESSES                  |
 |   Province               Province column of RA_ADDRESSES                |
 |   Country                Country column of RA_ADDRESSES                 |
 |   Postal Code            Postal Code column of RA_ADDRESSES             |
 |   DF1 .. 10              User Descriptive Flexfields 1 through 10       |
 |                                                                         |
 | RETURNS                                                                 |
 |  Location_ID             Set the Location Code Combination id           |
 |                          applicable to this address given each of the   |
 |                          segment values described above.                |
 | MODIFIES                                                                |
 |   Each of the address components may have the value NULL, even though   |
 |   it is used as part of the location flexfield. If so, this code will   |
 |   determine the missing value using the other segments and postal code  |
 |   and update this column to hold the correct value.                     |
 |                                                                         |
 | EXCEPTIONS RAISED                                                       |
 |                                                                         |
 | NOTES                                                                   |
 |                                                                         |
 | EXAMPLE                                                                 |
 |                                                                         |
 +-------------------------------------------------------------------------*/


PROCEDURE Set_Location_CCID(  Country        in out NOCOPY varchar2,
                              City           in out NOCOPY varchar2,
                              State          in out NOCOPY varchar2,
                              County         in out NOCOPY varchar2,
			      Province       in out NOCOPY varchar2,
                              Postal_code    in out NOCOPY varchar2,
                              attribute1     in out NOCOPY varchar2,
                              attribute2     in out NOCOPY varchar2,
                              attribute3     in out NOCOPY varchar2,
                              attribute4     in out NOCOPY varchar2,
                              attribute5     in out NOCOPY varchar2,
                              attribute6     in out NOCOPY varchar2,
                              attribute7     in out NOCOPY varchar2,
                              attribute8     in out NOCOPY varchar2,
                              attribute9     in out NOCOPY varchar2,
                              attribute10    in out NOCOPY varchar2,
                              location_ccid  in out NOCOPY number,
			      address_id     in number default null ) IS

        location        LOCATION_TYPE;
Line: 2124

    location_combination_inserted := FALSE;
Line: 2125

    location_segment_inserted := FALSE;
Line: 2176

             /* Find Missing Values, insert them and then generate Code Combination ID */

            for i in 0 .. 10
            loop
               location_id(i) := null;