1 PACKAGE ARP_ADDS AS
2 /* $Header: ARPLLOCS.pls 120.6 2005/01/26 01:39:35 sachandr 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 /*** 2025060 ***/
91 sysparm AR_SYSTEM_PARAMETERS%ROWTYPE;
92 /*** 2025060 ***/
93 PROCEDURE Set_Location_CCID( Country in out NOCOPY varchar2,
94 City in out NOCOPY varchar2,
95 State in out NOCOPY varchar2,
96 County in out NOCOPY varchar2,
97 Province in out NOCOPY varchar2,
98 Postal_code in out NOCOPY varchar2,
99 attribute1 in out NOCOPY varchar2,
100 attribute2 in out NOCOPY varchar2,
101 attribute3 in out NOCOPY varchar2,
102 attribute4 in out NOCOPY varchar2,
103 attribute5 in out NOCOPY varchar2,
104 attribute6 in out NOCOPY varchar2,
105 attribute7 in out NOCOPY varchar2,
106 attribute8 in out NOCOPY varchar2,
107 attribute9 in out NOCOPY varchar2,
108 attribute10 in out NOCOPY varchar2,
109 location_ccid in out NOCOPY number,
110 address_id in number default null );
111
112 -- The following new signature is added for bug 3105634
113 PROCEDURE Set_Location_CCID( Country in out NOCOPY varchar2,
114 City in out NOCOPY varchar2,
115 State in out NOCOPY varchar2,
116 County in out NOCOPY varchar2,
117 Province in out NOCOPY varchar2,
118 Postal_code in out NOCOPY varchar2,
119 attribute1 in out NOCOPY varchar2,
120 attribute2 in out NOCOPY varchar2,
121 attribute3 in out NOCOPY varchar2,
122 attribute4 in out NOCOPY varchar2,
123 attribute5 in out NOCOPY varchar2,
124 attribute6 in out NOCOPY varchar2,
125 attribute7 in out NOCOPY varchar2,
126 attribute8 in out NOCOPY varchar2,
127 attribute9 in out NOCOPY varchar2,
128 attribute10 in out NOCOPY varchar2,
129 location_ccid in out NOCOPY number,
130 address_id in number default null,
131 org_id in number);
132
133 FUNCTION ins_location_combinations( location_structure_id number,
134 start_date_active date,
135 end_date_active date,
136 location_id_segment_1 number,
137 location_id_segment_2 number,
138 location_id_segment_3 number,
139 location_id_segment_4 number,
140 location_id_segment_5 number,
141 location_id_segment_6 number,
142 location_id_segment_7 number,
143 location_id_segment_8 number,
144 location_id_segment_9 number,
145 location_id_segment_10 number,
146 enabled_flag varchar2 )
147 return number;
148
149
150 FUNCTION ins_location_values( location_segment_qualifier in varchar2,
151 segment_value in varchar2,
152 segment_description in varchar2,
153 parent_segment_id in varchar2,
154 ATTRIBUTE_CATEGORY in varchar2 default 'TRIGGER',
155 ATTRIBUTE1 in varchar2 default null,
156 ATTRIBUTE2 in varchar2 default null,
157 ATTRIBUTE3 in varchar2 default null,
158 ATTRIBUTE4 in varchar2 default null,
159 ATTRIBUTE5 in varchar2 default null,
160 ATTRIBUTE6 in varchar2 default null,
161 ATTRIBUTE7 in varchar2 default null,
162 ATTRIBUTE8 in varchar2 default null,
163 ATTRIBUTE9 in varchar2 default null,
164 ATTRIBUTE10 in varchar2 default null,
165 ATTRIBUTE11 in varchar2 default null,
166 ATTRIBUTE12 in varchar2 default null,
167 ATTRIBUTE13 in varchar2 default null,
168 ATTRIBUTE14 in varchar2 default null,
169 ATTRIBUTE15 in varchar2 default null )
170 return number ;
171
172 FUNCTION ins_location_rates( location_segment_id in number,
173 from_postal_code in varchar2,
174 to_postal_code in varchar2,
175 start_date in date,
176 end_date in date,
177 tax_rate in number,
178 ATTRIBUTE_CATEGORY in varchar2 default 'TRIGGER',
179 ATTRIBUTE1 in varchar2 default null,
180 ATTRIBUTE2 in varchar2 default null,
181 ATTRIBUTE3 in varchar2 default null,
182 ATTRIBUTE4 in varchar2 default null,
183 ATTRIBUTE5 in varchar2 default null,
184 ATTRIBUTE6 in varchar2 default null,
185 ATTRIBUTE7 in varchar2 default null,
186 ATTRIBUTE8 in varchar2 default null,
187 ATTRIBUTE9 in varchar2 default null,
188 ATTRIBUTE10 in varchar2 default null,
189 ATTRIBUTE11 in varchar2 default null,
190 ATTRIBUTE12 in varchar2 default null,
191 ATTRIBUTE13 in varchar2 default null,
192 ATTRIBUTE14 in varchar2 default null,
193 ATTRIBUTE15 in varchar2 default null,
194 OVERRIDE_STRUCTURE_ID in NUMBER default null,
195 OVERRIDE_RATE1 in NUMBER default null,
196 OVERRIDE_RATE2 in NUMBER default null,
197 OVERRIDE_RATE3 in NUMBER default null,
198 OVERRIDE_RATE4 in NUMBER default null,
199 OVERRIDE_RATE5 in NUMBER default null,
200 OVERRIDE_RATE6 in NUMBER default null,
201 OVERRIDE_RATE7 in NUMBER default null,
202 OVERRIDE_RATE8 in NUMBER default null,
203 OVERRIDE_RATE9 in NUMBER default null,
204 OVERRIDE_RATE10 in NUMBER default null
205 ) return number ;
206
207
208 PROCEDURE ins_location_rates( location_segment_id in number,
209 from_postal_code in varchar2,
210 to_postal_code in varchar2,
211 start_date in date,
212 end_date in date,
213 tax_rate in number,
214 ATTRIBUTE_CATEGORY in varchar2 default 'TRIGGER',
215 ATTRIBUTE1 in varchar2 default null,
216 ATTRIBUTE2 in varchar2 default null,
217 ATTRIBUTE3 in varchar2 default null,
218 ATTRIBUTE4 in varchar2 default null,
219 ATTRIBUTE5 in varchar2 default null,
220 ATTRIBUTE6 in varchar2 default null,
221 ATTRIBUTE7 in varchar2 default null,
222 ATTRIBUTE8 in varchar2 default null,
223 ATTRIBUTE9 in varchar2 default null,
224 ATTRIBUTE10 in varchar2 default null,
225 ATTRIBUTE11 in varchar2 default null,
226 ATTRIBUTE12 in varchar2 default null,
227 ATTRIBUTE13 in varchar2 default null,
228 ATTRIBUTE14 in varchar2 default null,
229 ATTRIBUTE15 in varchar2 default null,
230 OVERRIDE_STRUCTURE_ID in NUMBER default null,
231 OVERRIDE_RATE1 in NUMBER default null,
232 OVERRIDE_RATE2 in NUMBER default null,
233 OVERRIDE_RATE3 in NUMBER default null,
234 OVERRIDE_RATE4 in NUMBER default null,
235 OVERRIDE_RATE5 in NUMBER default null,
236 OVERRIDE_RATE6 in NUMBER default null,
237 OVERRIDE_RATE7 in NUMBER default null,
238 OVERRIDE_RATE8 in NUMBER default null,
239 OVERRIDE_RATE9 in NUMBER default null,
240 OVERRIDE_RATE10 in NUMBER default null);
241
242 procedure location_information( location_segment_id in number,
243 location_segment_qualifier out NOCOPY varchar2,
244 location_segment_value out NOCOPY varchar2,
245 location_segment_description out NOCOPY varchar2,
246 parent_segment_id out NOCOPY number ) ;
247
248 procedure location_information( location_segment_qualifier in varchar2,
249 location_segment_value in varchar2,
250 location_segment_description out NOCOPY varchar2,
251 parent_segment_id out NOCOPY number ) ;
252
253
254
255 FUNCTION find_location_segment_id( location_segment_qualifier in varchar2,
256 segment_value in varchar2,
257 segment_description in varchar2,
258 parent_segment_id in number,
259 ATTRIBUTE_CATEGORY in varchar2 default 'TRIGGER',
260 ATTRIBUTE1 in varchar2 default null,
261 ATTRIBUTE2 in varchar2 default null,
262 ATTRIBUTE3 in varchar2 default null,
263 ATTRIBUTE4 in varchar2 default null,
264 ATTRIBUTE5 in varchar2 default null,
265 ATTRIBUTE6 in varchar2 default null,
266 ATTRIBUTE7 in varchar2 default null,
267 ATTRIBUTE8 in varchar2 default null,
268 ATTRIBUTE9 in varchar2 default null,
269 ATTRIBUTE10 in varchar2 default null,
270 ATTRIBUTE11 in varchar2 default null,
271 ATTRIBUTE12 in varchar2 default null,
272 ATTRIBUTE13 in varchar2 default null,
273 ATTRIBUTE14 in varchar2 default null,
274 ATTRIBUTE15 in varchar2 default null,
275 SEARCH_PRECISSION in number default null )
276 return number ;
277
278
279 function location_description( location_segment_qualifier in varchar2,
280 location_segment_value in varchar2 )
281 return varchar2;
282
283 function location_description( location_segment_id in number )
284 return varchar2 ;
285
286
287
291
288 function terr_short_name( territory_code in varchar2 ) return varchar2 ;
289
290 procedure enable_triggers;
292 procedure disable_triggers;
293
294
295 procedure return_location_defaults( from_postal_code out NOCOPY varchar2,
296 to_postal_code out NOCOPY varchar2,
297 min_start_date out NOCOPY date,
298 max_end_date out NOCOPY date );
299
300 /*-------------------------------------------------------------------------+
301 | PUBLIC PROCEDURE |
302 | Initialize_Global_Variables |
303 | |
304 | DESCRIPTION |
305 | |
306 | Initialize Global Variables, This routine has to be called |
307 | before to use those variables. |
308 | |
309 | EXCEPTIONS RAISED |
310 | |
311 | NOTES |
312 | |
313 | EXAMPLE |
314 | |
315 +-------------------------------------------------------------------------*/
316
317 PROCEDURE Initialize_Global_Variables;
318
319 END ARP_ADDS;