The following lines contain the word 'select', 'insert', 'update' or 'delete':
| 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
l_location_segment_inserted varchar2(5);
| 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
dummy_vch varchar2(2);
| 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;
| 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
l_location_segment_inserted varchar2(5);
| 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 |
| org_id |
| 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 Added for bug 3105634 |
| |
| 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,
org_id in number ) IS
l_location_segment_inserted varchar2(5);