1 package ARP_ADDS_MINUS99 AUTHID DEFINER /*NOSYNC*/ AS
2 /* $Header: ARPLXLOC.txt 115.5 2004/03/18 16:28:47 rpalani ship $ */
3
4 /*-------------------------------------------------------------------------+
5 | |
6 | PUBLIC EXCEPTIONS |
7 | |
8 +-------------------------------------------------------------------------*/
9
10
11 LOCATION_SEGMENT_NULL_VALUE EXCEPTION;
12
13
14
15 /*-------------------------------------------------------------------------+
16 | |
17 | PUBLIC TYPES |
18 | |
19 +-------------------------------------------------------------------------*/
20
21
22 TYPE LOCATION_TYPE IS RECORD
23 (
24 country varchar2(60),
25 City varchar2(60),
26 State varchar2(60),
27 County varchar2(60),
28 Province varchar2(60),
29 Postal_code varchar2(60),
30 attribute1 varchar2(150),
31 attribute2 varchar2(150),
32 attribute3 varchar2(150),
33 attribute4 varchar2(150),
34 attribute5 varchar2(150),
35 attribute6 varchar2(150),
36 attribute7 varchar2(150),
37 attribute8 varchar2(150),
38 attribute9 varchar2(150),
39 attribute10 varchar2(150),
40 location_ccid number);
41
42 /*-------------------------------------------------------------------------+
43 | |
44 | PUBLIC VARIABLES |
45 | |
46 | VARIABLE: location_segment_inserted |
47 | location_segment_inserted is true if the most recent call to |
48 | find location_segment_id inserted a new record into the table |
49 | ar_location_values |
50 | |
51 | VARIABLE: location_combination_inserted |
52 | location_combination_inserted is true if the most recent call to |
53 | set_location_ccid inserted a new record into the table |
54 | ar_location_combinations |
55 | |
56 | VARIABLE: triggers_enabled |
57 | If set to true, (default) row and statement table triggers will |
58 | fire on each of tables associated with Customer Address Entry |
59 | |
60 | VARIABLE: first_segment_qualifier |
61 | The Segment qualifier of the first segment in your Sales Tax |
62 | location Flexfield. |
63 | |
64 | VARIABLE: all_segment_qualifiers |
65 | An ordered list of qualifiers for each segment of your Sales Tax |
66 | location Flexfield. |
67 | |
68 | VARIABLE: populate_location_rates |
69 | If enabled, every time a database trigger creates a new row in the |
70 | table AR_LOCATION_VALUES a default row will automatically by created |
71 | in ar_location_rates. |
72 | |
73 +-------------------------------------------------------------------------*/
74
75
76
77 location_segment_inserted BOOLEAN := FALSE;
78 location_combination_inserted BOOLEAN := FALSE;
79
80
81 populate_location_rates BOOLEAN := FALSE;
82
83 triggers_enabled BOOLEAN := TRUE;
84
85
86 first_segment_qualifier varchar2(60);
87 last_segment_qualifier varchar2(60);
88
89 all_segment_qualifiers varchar2(512);
90
91 PROCEDURE Set_Location_CCID( Country in out NOCOPY varchar2,
92 City in out NOCOPY varchar2,
93 State in out NOCOPY varchar2,
94 County in out NOCOPY varchar2,
95 Province in out NOCOPY varchar2,
96 Postal_code in out NOCOPY varchar2,
97 attribute1 in out NOCOPY varchar2,
98 attribute2 in out NOCOPY varchar2,
99 attribute3 in out NOCOPY varchar2,
100 attribute4 in out NOCOPY varchar2,
101 attribute5 in out NOCOPY varchar2,
102 attribute6 in out NOCOPY varchar2,
103 attribute7 in out NOCOPY varchar2,
104 attribute8 in out NOCOPY varchar2,
105 attribute9 in out NOCOPY varchar2,
106 attribute10 in out NOCOPY varchar2,
107 location_ccid in out NOCOPY number,
108 address_id in number default null );
109
110 FUNCTION ins_location_combinations( location_structure_id number,
111 start_date_active date,
112 end_date_active date,
113 location_id_segment_1 number,
114 location_id_segment_2 number,
115 location_id_segment_3 number,
116 location_id_segment_4 number,
117 location_id_segment_5 number,
118 location_id_segment_6 number,
119 location_id_segment_7 number,
120 location_id_segment_8 number,
121 location_id_segment_9 number,
122 location_id_segment_10 number,
123 enabled_flag varchar2 )
124 return number;
125
126
127 FUNCTION ins_location_values( location_segment_qualifier in varchar2,
128 segment_value in varchar2,
129 segment_description in varchar2,
130 parent_segment_id in varchar2,
131 ATTRIBUTE_CATEGORY in varchar2 default 'TRIGGER',
132 ATTRIBUTE1 in varchar2 default null,
133 ATTRIBUTE2 in varchar2 default null,
134 ATTRIBUTE3 in varchar2 default null,
135 ATTRIBUTE4 in varchar2 default null,
136 ATTRIBUTE5 in varchar2 default null,
137 ATTRIBUTE6 in varchar2 default null,
138 ATTRIBUTE7 in varchar2 default null,
139 ATTRIBUTE8 in varchar2 default null,
140 ATTRIBUTE9 in varchar2 default null,
141 ATTRIBUTE10 in varchar2 default null,
142 ATTRIBUTE11 in varchar2 default null,
143 ATTRIBUTE12 in varchar2 default null,
144 ATTRIBUTE13 in varchar2 default null,
145 ATTRIBUTE14 in varchar2 default null,
146 ATTRIBUTE15 in varchar2 default null )
147 return number ;
148
149 FUNCTION ins_location_rates( location_segment_id in number,
150 from_postal_code in varchar2,
151 to_postal_code in varchar2,
152 start_date in date,
153 end_date in date,
154 tax_rate in number,
155 ATTRIBUTE_CATEGORY in varchar2 default 'TRIGGER',
156 ATTRIBUTE1 in varchar2 default null,
157 ATTRIBUTE2 in varchar2 default null,
158 ATTRIBUTE3 in varchar2 default null,
159 ATTRIBUTE4 in varchar2 default null,
160 ATTRIBUTE5 in varchar2 default null,
161 ATTRIBUTE6 in varchar2 default null,
162 ATTRIBUTE7 in varchar2 default null,
163 ATTRIBUTE8 in varchar2 default null,
164 ATTRIBUTE9 in varchar2 default null,
165 ATTRIBUTE10 in varchar2 default null,
166 ATTRIBUTE11 in varchar2 default null,
167 ATTRIBUTE12 in varchar2 default null,
168 ATTRIBUTE13 in varchar2 default null,
169 ATTRIBUTE14 in varchar2 default null,
170 ATTRIBUTE15 in varchar2 default null,
171 OVERRIDE_STRUCTURE_ID in NUMBER default null,
172 OVERRIDE_RATE1 in NUMBER default null,
173 OVERRIDE_RATE2 in NUMBER default null,
174 OVERRIDE_RATE3 in NUMBER default null,
175 OVERRIDE_RATE4 in NUMBER default null,
176 OVERRIDE_RATE5 in NUMBER default null,
177 OVERRIDE_RATE6 in NUMBER default null,
178 OVERRIDE_RATE7 in NUMBER default null,
179 OVERRIDE_RATE8 in NUMBER default null,
180 OVERRIDE_RATE9 in NUMBER default null,
181 OVERRIDE_RATE10 in NUMBER default null
182 ) return number ;
183
184
185 PROCEDURE ins_location_rates( location_segment_id in number,
186 from_postal_code in varchar2,
187 to_postal_code in varchar2,
188 start_date in date,
189 end_date in date,
190 tax_rate in number,
191 ATTRIBUTE_CATEGORY in varchar2 default 'TRIGGER',
192 ATTRIBUTE1 in varchar2 default null,
193 ATTRIBUTE2 in varchar2 default null,
194 ATTRIBUTE3 in varchar2 default null,
195 ATTRIBUTE4 in varchar2 default null,
196 ATTRIBUTE5 in varchar2 default null,
197 ATTRIBUTE6 in varchar2 default null,
198 ATTRIBUTE7 in varchar2 default null,
199 ATTRIBUTE8 in varchar2 default null,
200 ATTRIBUTE9 in varchar2 default null,
201 ATTRIBUTE10 in varchar2 default null,
202 ATTRIBUTE11 in varchar2 default null,
203 ATTRIBUTE12 in varchar2 default null,
204 ATTRIBUTE13 in varchar2 default null,
205 ATTRIBUTE14 in varchar2 default null,
206 ATTRIBUTE15 in varchar2 default null,
207 OVERRIDE_STRUCTURE_ID in NUMBER default null,
208 OVERRIDE_RATE1 in NUMBER default null,
209 OVERRIDE_RATE2 in NUMBER default null,
210 OVERRIDE_RATE3 in NUMBER default null,
211 OVERRIDE_RATE4 in NUMBER default null,
212 OVERRIDE_RATE5 in NUMBER default null,
213 OVERRIDE_RATE6 in NUMBER default null,
214 OVERRIDE_RATE7 in NUMBER default null,
215 OVERRIDE_RATE8 in NUMBER default null,
216 OVERRIDE_RATE9 in NUMBER default null,
217 OVERRIDE_RATE10 in NUMBER default null);
218
219 procedure location_information( location_segment_id in number,
220 location_segment_qualifier out NOCOPY varchar2,
221 location_segment_value out NOCOPY varchar2,
222 location_segment_description out NOCOPY varchar2,
223 parent_segment_id out NOCOPY number ) ;
224
225 procedure location_information( location_segment_qualifier in varchar2,
226 location_segment_value in varchar2,
227 location_segment_description out NOCOPY varchar2,
228 parent_segment_id out NOCOPY number ) ;
229
230
231
232 FUNCTION find_location_segment_id( location_segment_qualifier in varchar2,
233 segment_value in varchar2,
234 segment_description in varchar2,
235 parent_segment_id in number,
236 ATTRIBUTE_CATEGORY in varchar2 default 'TRIGGER',
237 ATTRIBUTE1 in varchar2 default null,
238 ATTRIBUTE2 in varchar2 default null,
239 ATTRIBUTE3 in varchar2 default null,
240 ATTRIBUTE4 in varchar2 default null,
241 ATTRIBUTE5 in varchar2 default null,
242 ATTRIBUTE6 in varchar2 default null,
243 ATTRIBUTE7 in varchar2 default null,
244 ATTRIBUTE8 in varchar2 default null,
245 ATTRIBUTE9 in varchar2 default null,
246 ATTRIBUTE10 in varchar2 default null,
247 ATTRIBUTE11 in varchar2 default null,
248 ATTRIBUTE12 in varchar2 default null,
249 ATTRIBUTE13 in varchar2 default null,
250 ATTRIBUTE14 in varchar2 default null,
251 ATTRIBUTE15 in varchar2 default null,
252 SEARCH_PRECISSION in number default null )
253 return number ;
254
255
256 function location_description( location_segment_qualifier in varchar2,
257 location_segment_value in varchar2 )
258 return varchar2;
259
260 function location_description( location_segment_id in number )
261 return varchar2 ;
262
263
264
265 function terr_short_name( territory_code in varchar2 ) return varchar2 ;
266
267 procedure enable_triggers;
268
269 procedure disable_triggers;
270
271
272 procedure return_location_defaults( from_postal_code out NOCOPY varchar2,
273 to_postal_code out NOCOPY varchar2,
274 min_start_date out NOCOPY date,
275 max_end_date out NOCOPY date );
276
277 PROCEDURE ins_location_accounts
281
278 ( location_segment_id in number,
279 location_segment_qualifier in varchar2);
280
282 END ARP_ADDS_MINUS99;