1 PACKAGE ece_trading_partners_pub AUTHID CURRENT_USER AS
2 -- $Header: ECVNWTPS.pls 120.2 2005/09/28 07:25:44 arsriniv ship $
3
4 -- Global constants holding the package and file names to be used by
5 -- messaging routines in the case of an unexpected error.
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'ece_trading_partners_pub';
7 G_FILE_NAME CONSTANT VARCHAR2(12) := 'ECVTPXFB.pls';
8
9 -- Global constants representing Bank, Customer, Internal Location, and Supplier.
10 G_BANK CONSTANT NUMBER := 0;
11 G_CUSTOMER CONSTANT NUMBER := 1;
12 G_HR_LOCATION CONSTANT NUMBER := 2;
13 G_SUPPLIER CONSTANT NUMBER := 3;
14
15 -- Global constants representing Status Codes
16 G_NO_ERRORS CONSTANT NUMBER := 0;
17 G_INCONSISTENT_ADDR_COMP CONSTANT NUMBER := 1;
18 G_CANNOT_DERIVE_ADDR CONSTANT NUMBER := 2;
19 G_CANNOT_DERIVE_ADDR_ID CONSTANT NUMBER := 3;
20 G_INVALID_ADDR_ID CONSTANT NUMBER := 4;
21 G_INVALID_ORG_ID CONSTANT NUMBER := 5;
22 G_INVALID_PARAMETER CONSTANT NUMBER := 6;
23 G_UNEXP_ERROR CONSTANT NUMBER := 7;
24
25 -- bug 2151462
26 G_MULTIPLE_LOC_FOUND CONSTANT NUMBER := 8;
27 G_MULTIPLE_ADDR_FOUND CONSTANT NUMBER := 9;
28
29 -- Start of Comments
30 -- API name : Get_TP_Address
31 -- Type : Private.
32 -- Function : Retrieve Supplier/Customer and Address information for a TP
33 -- Pre-reqs : None.
34 -- Paramaeters :
35 -- IN : p_api_version_number IN NUMBER Required
36 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API.G_FALSE
37 -- p_simulate IN VARCHAR2 Optional Default = FND_API.G_FALSE
38 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
39 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
40 -- p_translator_code IN VARCHAR2 Required
41 -- p_location_code_ext IN VARCHAR2 Required
42 -- p_info_type IN VARCHAR2 Required
43 --
44 -- OUT : p_return_status OUT VARCHAR2(1)
45 -- p_msg_count OUT NUMBER
46 -- p_msg_data OUT VARCHAR2(2000)
47 -- p_entity_id OUT NUMBER
48 -- p_entity_address_id OUT NUMBER
49 -- .
50 -- Version : Current version 1.0
51 -- Initial version 1.0
52 --
53 -- Notes : Note text
54 --
55 --
56 -- API name : Get_TP_Address_Ref
57 -- Type : Private.
58 -- Function : Retrieve Supplier/Customer and Address information for a TP
59 -- This implementation is per request from the automotive team
60 -- Pre-reqs : None.
61 -- Paramaeters :
62 -- IN : p_api_version_number IN NUMBER Required
63 -- p_init_msg_list IN VARCHAR2 Optional
64 -- Default = FND_API.G_FALSE
65 -- p_simulate IN VARCHAR2 Optional
66 -- Default = FND_API.G_FALSE
67 -- p_commit IN VARCHAR2 Optional
68 -- Default = FND_API.G_FALSE
69 -- p_validation_level IN NUMBER Optional
70 -- Default = FND_API.G_VALID_LEVEL_FULL
71 -- p_reference_ext1 IN VARCHAR2 Required
72 -- p_reference_ext2 IN VARCHAR2 Required
73 -- p_info_type IN VARCHAR2 Required
74 --
75 -- OUT : p_return_status OUT VARCHAR2(1)
76 -- p_msg_count OUT NUMBER
77 -- p_msg_data OUT VARCHAR2(2000)
78 -- p_entity_id OUT NUMBER
79 -- p_entity_address_id OUT NUMBER
80 -- .
81 -- Version : Current version 1.0
82 -- Initial version 1.0
83 --
84 -- Notes : Note text
85 --
86 --
87 -- API name : Get_TP_Location_Code
88 -- Type : Private.
89 -- Function : Retrieve TP information for an address
90 -- Pre-reqs : None.
91 -- Paramaeters :
92 -- IN : p_api_version_number IN NUMBER Required
93 -- p_init_msg_list IN VARCHAR2 Optional
94 -- Default = FND_API.G_FALSE
95 -- p_simulate IN VARCHAR2 Optional
96 -- Default = FND_API.G_FALSE
97 -- p_commit IN VARCHAR2 Optional
98 -- Default = FND_API.G_FALSE
99 -- p_validation_level IN NUMBER Optional
100 -- Default = FND_API.G_VALID_LEVEL_FULL
101 -- p_entity_address_id IN NUMBER Required
102 -- p_info_type IN VARCHAR2 Required
103 --
104 -- OUT : p_return_status OUT VARCHAR2(1)
105 -- p_msg_count OUT NUMBER
106 -- p_msg_data OUT VARCHAR2(2000)
107 -- p_location_code_ext OUT VARCHAR2
108 -- p_reference_ext1 OUT VARCHAR2
109 -- p_reference_ext2 OUT VARCHAR2
110
111 -- .
112 -- Version : Current version 1.0
113 -- Initial version 1.0
114 --
115 -- Notes : Note text
116
117 PROCEDURE ece_get_address_wrapper(
118 p_api_version_number IN NUMBER,
119 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
120 p_simulate IN VARCHAR2 := FND_API.G_FALSE,
121 p_commit IN VARCHAR2 := FND_API.G_FALSE,
122 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
123 x_return_status OUT NOCOPY VARCHAR2,
124 x_msg_count OUT NOCOPY NUMBER,
125 x_msg_data OUT NOCOPY VARCHAR2,
126 x_status_code OUT NOCOPY NUMBER,
127 p_address_type IN NUMBER,
128 p_transaction_type IN VARCHAR2,
129 p_org_id_in IN NUMBER DEFAULT NULL,
130 p_address_id_in IN NUMBER DEFAULT NULL,
131 p_tp_location_code_in IN VARCHAR2 DEFAULT NULL,
132 p_translator_code_in IN VARCHAR2 DEFAULT NULL,
133 p_tp_location_name_in IN VARCHAR2 DEFAULT NULL,
134 p_address_line1_in IN VARCHAR2 DEFAULT NULL,
135 p_address_line2_in IN VARCHAR2 DEFAULT NULL,
136 p_address_line3_in IN VARCHAR2 DEFAULT NULL,
137 p_address_line4_in IN VARCHAR2 DEFAULT NULL,
138 p_address_line_alt_in IN VARCHAR2 DEFAULT NULL,
139 p_city_in IN VARCHAR2 DEFAULT NULL,
140 p_county_in IN VARCHAR2 DEFAULT NULL,
141 p_state_in IN VARCHAR2 DEFAULT NULL,
142 p_zip_in IN VARCHAR2 DEFAULT NULL,
143 p_province_in IN VARCHAR2 DEFAULT NULL,
144 p_country_in IN VARCHAR2 DEFAULT NULL,
145 p_region_1_in IN VARCHAR2 DEFAULT NULL,
146 p_region_2_in IN VARCHAR2 DEFAULT NULL,
147 p_region_3_in IN VARCHAR2 DEFAULT NULL,
148 x_entity_id_out OUT NOCOPY NUMBER,
149 x_org_id_out OUT NOCOPY NUMBER,
150 x_address_id_out OUT NOCOPY NUMBER,
151 x_tp_location_code_out OUT NOCOPY VARCHAR2,
152 x_translator_code_out OUT NOCOPY VARCHAR2,
153 x_tp_location_name_out OUT NOCOPY VARCHAR2,
154 x_address_line1_out OUT NOCOPY VARCHAR2,
155 x_address_line2_out OUT NOCOPY VARCHAR2,
156 x_address_line3_out OUT NOCOPY VARCHAR2,
157 x_address_line4_out OUT NOCOPY VARCHAR2,
158 x_address_line_alt_out OUT NOCOPY VARCHAR2,
159 x_city_out OUT NOCOPY VARCHAR2,
160 x_county_out OUT NOCOPY VARCHAR2,
161 x_state_out OUT NOCOPY VARCHAR2,
162 x_zip_out OUT NOCOPY VARCHAR2,
163 x_province_out OUT NOCOPY VARCHAR2,
164 x_country_out OUT NOCOPY VARCHAR2,
165 x_region_1_out OUT NOCOPY VARCHAR2,
166 x_region_2_out OUT NOCOPY VARCHAR2,
167 x_region_3_out OUT NOCOPY VARCHAR2);
168
169 PROCEDURE ece_get_address(
170 p_api_version_number IN NUMBER,
171 p_init_msg_list IN VARCHAR2 := fnd_api.G_FALSE,
172 p_simulate IN VARCHAR2 := fnd_api.G_FALSE,
173 p_commit IN VARCHAR2 := fnd_api.G_FALSE,
174 p_validation_level IN NUMBER := fnd_api.G_VALID_LEVEL_FULL,
175 x_return_status OUT NOCOPY VARCHAR2,
176 x_msg_count OUT NOCOPY NUMBER,
177 x_msg_data OUT NOCOPY VARCHAR2,
178 x_status_code OUT NOCOPY NUMBER,
179 p_precedence_code IN VARCHAR2,
180 p_address_type IN NUMBER,
181 p_transaction_type IN VARCHAR2,
182 p_org_id_in IN NUMBER DEFAULT NULL,
183 p_address_id_in IN NUMBER DEFAULT NULL,
184 p_tp_location_code_in IN VARCHAR2 DEFAULT NULL,
185 p_translator_code_in IN VARCHAR2 DEFAULT NULL,
186 p_tp_location_name_in IN VARCHAR2 DEFAULT NULL,
187 p_address_line1_in IN VARCHAR2 DEFAULT NULL,
188 p_address_line2_in IN VARCHAR2 DEFAULT NULL,
189 p_address_line3_in IN VARCHAR2 DEFAULT NULL,
190 p_address_line4_in IN VARCHAR2 DEFAULT NULL,
191 p_address_line_alt_in IN VARCHAR2 DEFAULT NULL,
192 p_city_in IN VARCHAR2 DEFAULT NULL,
193 p_county_in IN VARCHAR2 DEFAULT NULL,
194 p_state_in IN VARCHAR2 DEFAULT NULL,
195 p_zip_in IN VARCHAR2 DEFAULT NULL,
196 p_province_in IN VARCHAR2 DEFAULT NULL,
197 p_country_in IN VARCHAR2 DEFAULT NULL,
198 p_region_1_in IN VARCHAR2 DEFAULT NULL,
199 p_region_2_in IN VARCHAR2 DEFAULT NULL,
200 p_region_3_in IN VARCHAR2 DEFAULT NULL,
201 x_org_id_out OUT NOCOPY NUMBER,
202 x_address_id_out OUT NOCOPY NUMBER,
203 x_tp_location_code_out OUT NOCOPY VARCHAR2,
204 x_translator_code_out OUT NOCOPY VARCHAR2,
205 x_tp_location_name_out OUT NOCOPY VARCHAR2,
206 x_address_line1_out OUT NOCOPY VARCHAR2,
207 x_address_line2_out OUT NOCOPY VARCHAR2,
208 x_address_line3_out OUT NOCOPY VARCHAR2,
209 x_address_line4_out OUT NOCOPY VARCHAR2,
210 x_address_line_alt_out OUT NOCOPY VARCHAR2,
211 x_city_out OUT NOCOPY VARCHAR2,
212 x_county_out OUT NOCOPY VARCHAR2,
213 x_state_out OUT NOCOPY VARCHAR2,
214 x_zip_out OUT NOCOPY VARCHAR2,
215 x_province_out OUT NOCOPY VARCHAR2,
216 x_country_out OUT NOCOPY VARCHAR2,
217 x_region_1_out OUT NOCOPY VARCHAR2,
218 x_region_2_out OUT NOCOPY VARCHAR2,
219 x_region_3_out OUT NOCOPY VARCHAR2);
220
221 FUNCTION ece_compare_addresses(
222 p_address_line1_in IN VARCHAR2,
223 p_address_line2_in IN VARCHAR2,
224 p_address_line3_in IN VARCHAR2,
225 p_address_line4_in IN VARCHAR2,
226 p_address_line_alt_in IN VARCHAR2,
227 p_city_in IN VARCHAR2,
228 p_county_in IN VARCHAR2,
229 p_state_in IN VARCHAR2,
230 p_zip_in IN VARCHAR2,
231 p_province_in IN VARCHAR2,
232 p_country_in IN VARCHAR2,
233 p_region_1_in IN VARCHAR2,
234 p_region_2_in IN VARCHAR2,
235 p_region_3_in IN VARCHAR2,
236 p2_address_line1_in IN VARCHAR2,
237 p2_address_line2_in IN VARCHAR2,
238 p2_address_line3_in IN VARCHAR2,
239 p2_address_line4_in IN VARCHAR2,
240 p2_address_line_alt_in IN VARCHAR2,
241 p2_city_in IN VARCHAR2,
242 p2_county_in IN VARCHAR2,
243 p2_state_in IN VARCHAR2,
244 p2_zip_in IN VARCHAR2,
245 p2_province_in IN VARCHAR2,
246 p2_country_in IN VARCHAR2,
247 p2_region_1_in IN VARCHAR2,
248 p2_region_2_in IN VARCHAR2,
249 p2_region_3_in IN VARCHAR2) RETURN BOOLEAN;
250
251 FUNCTION scrub(
252 p_instring VARCHAR2) RETURN VARCHAR2;
253
254 --***********************************************
255 -- procedure Get_TP_Address
256 --***********************************************
257 PROCEDURE Get_TP_Address(
258 p_api_version_number IN NUMBER,
259 p_init_msg_list IN VARCHAR2 := fnd_api.G_FALSE,
260 p_simulate IN VARCHAR2 := fnd_api.G_FALSE,
261 p_commit IN VARCHAR2 := fnd_api.G_FALSE,
262 p_validation_level IN NUMBER := fnd_api.G_VALID_LEVEL_FULL,
263 p_return_status OUT NOCOPY VARCHAR2,
264 p_msg_count OUT NOCOPY NUMBER,
265 p_msg_data OUT NOCOPY VARCHAR2,
266 p_translator_code IN VARCHAR2,
267 p_location_code_ext IN VARCHAR2,
268 p_info_type IN VARCHAR2,
269 p_entity_id OUT NOCOPY NUMBER,
270 p_entity_address_id OUT NOCOPY NUMBER);
271
272 --***********************************************
273 -- procedure Get_TP_Address_Auto
274 --
275 -- Overload this procedure per request from
276 -- the automotive team
277 --***********************************************
278 PROCEDURE Get_TP_Address_Ref(
279 p_api_version_number IN NUMBER,
280 p_init_msg_list IN VARCHAR2 := fnd_api.G_FALSE,
281 p_simulate IN VARCHAR2 := fnd_api.G_FALSE,
282 p_commit IN VARCHAR2 := fnd_api.G_FALSE,
283 p_validation_level IN NUMBER := fnd_api.G_VALID_LEVEL_FULL,
284 p_return_status OUT NOCOPY VARCHAR2,
285 p_msg_count OUT NOCOPY NUMBER,
286 p_msg_data OUT NOCOPY VARCHAR2,
287 p_reference_ext1 IN VARCHAR2,
288 p_reference_ext2 IN VARCHAR2,
289 p_info_type IN VARCHAR2,
290 p_entity_id OUT NOCOPY NUMBER,
291 p_entity_address_id OUT NOCOPY NUMBER);
292
293 --***********************************************
294 -- procedure Get_TP_Location_Code
295 --***********************************************
296 PROCEDURE Get_TP_Location_Code(
297 p_api_version_number IN NUMBER,
298 p_init_msg_list IN VARCHAR2 := fnd_api.G_FALSE,
299 p_simulate IN VARCHAR2 := fnd_api.G_FALSE,
300 p_commit IN VARCHAR2 := fnd_api.G_FALSE,
301 p_validation_level IN NUMBER := fnd_api.G_VALID_LEVEL_FULL,
302 p_return_status OUT NOCOPY VARCHAR2,
303 p_msg_count OUT NOCOPY NUMBER,
304 p_msg_data OUT NOCOPY VARCHAR2,
305 p_entity_address_id IN NUMBER,
306 p_info_type IN VARCHAR2,
307 p_location_code_ext OUT NOCOPY VARCHAR2,
308 p_reference_ext1 OUT NOCOPY VARCHAR2,
309 p_reference_ext2 OUT NOCOPY VARCHAR2);
310
311 END;
312
[Home] [Help]