[Home] [Help]
PACKAGE BODY: APPS.IGS_GR_VENUE_ADDR_PKG
Source
1 package body IGS_GR_VENUE_ADDR_PKG as
2 /* $Header: IGSGI20B.pls 120.1 2005/09/16 07:04:29 appldev ship $ */
3 PROCEDURE INSERT_ROW (
4 x_rowid IN OUT NOCOPY VARCHAR2,
5 x_location_venue_addr_id IN OUT NOCOPY NUMBER,
6 x_location_id IN OUT NOCOPY NUMBER,
7 x_location_venue_cd IN VARCHAR2,
8 x_start_dt IN DATE,
9 x_end_dt IN DATE,
10 x_country IN VARCHAR2,
11 x_address_style IN VARCHAR2,
12 x_addr_line_1 IN VARCHAR2,
13 x_addr_line_2 IN VARCHAR2,
14 x_addr_line_3 IN VARCHAR2,
15 x_addr_line_4 IN VARCHAR2,
16 x_date_last_verified IN DATE,
17 x_correspondence IN VARCHAR2,
18 x_city IN VARCHAR2,
19 x_state IN VARCHAR2,
20 x_province IN VARCHAR2,
21 x_county IN VARCHAR2,
22 x_postal_code IN VARCHAR2,
23 x_address_lines_phonetic IN VARCHAR2,
24 x_delivery_point_code IN VARCHAR2,
25 x_other_details_1 IN VARCHAR2,
26 x_other_details_2 IN VARCHAR2,
27 x_other_details_3 IN VARCHAR2,
28 x_source_type IN VARCHAR2,
29 x_contact_person IN VARCHAR2 default NULL,
30 x_msg_data OUT NOCOPY VARCHAR2,
31 X_MODE IN VARCHAR2 DEFAULT 'R'
32 ) AS
33
34 p_location_rec_insert HZ_LOCATION_V2PUB.location_rec_type;
35
36 l_return_status VARCHAR2(100);
37 l_msg_count NUMBER;
38 l_msg_data VARCHAR2(1000);
39 l_location_id NUMBER;
40 l_loc_id NUMBER;
41 l_location_venue_addr_id NUMBER := x_location_venue_addr_id;
42
43 lv_rowid VARCHAR2(25);
44 tmp_var1 VARCHAR2(2000);
45 tmp_var VARCHAR2(2000);
46
47 BEGIN
48 p_location_rec_insert.country := x_country;
49 p_location_rec_insert.address_style := x_address_style;
50 p_location_rec_insert.address1 := x_addr_line_1;
51 p_location_rec_insert.address2 := x_addr_line_2;
52 p_location_rec_insert.address3 := x_addr_line_3;
53 p_location_rec_insert.address4 := x_addr_line_4;
54 p_location_rec_insert.city := x_city;
55 p_location_rec_insert.state := x_state;
56 p_location_rec_insert.province := x_province;
57 p_location_rec_insert.county := x_county;
58 p_location_rec_insert.postal_code := x_postal_code;
59 p_location_rec_insert.address_lines_phonetic := x_address_lines_phonetic;
60 p_location_rec_insert.address_effective_date := x_start_dt;
61 p_location_rec_insert.address_expiration_date := x_end_dt;
62 p_location_rec_insert.created_by_module := 'IGS';
63 p_location_rec_insert.content_source_type := 'USER_ENTERED';
64
65
66 /* Hz_Location_Pub.CREATE_LOCATION(
67 p_api_version => 1.0,
68 p_init_msg_list => FND_API.G_TRUE,
69 p_location_rec => p_location_rec_insert,
70 x_return_status => l_return_status,
71 x_msg_count => l_msg_count,
72 x_msg_data=> l_msg_data,
73 x_location_id => l_location_id
74 ); */
75
76
77 HZ_LOCATION_V2PUB.create_location(
78 p_init_msg_list => FND_API.G_FALSE,
79 p_location_rec => p_location_rec_insert,
80 x_location_id => l_location_id ,
81 x_return_status => l_return_status,
82 x_msg_count => l_msg_count,
83 x_msg_data => l_msg_data
84 );
85
86
87 IF l_return_status IN ('E', 'U') THEN
88
89 IF l_msg_count > 1 THEN
90 FOR i IN 1..l_msg_count LOOP
91 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
92 tmp_var1 := tmp_var1 || ' '|| tmp_var;
93 END LOOP;
94 x_msg_data := tmp_var1;
95 END IF;
96 RETURN;
97 ELSIF l_return_status = 'S' THEN
98
99 HZ_TAX_ASSIGNMENT_V2PUB.CREATE_LOC_ASSIGNMENT(
100 p_location_id => l_location_id,
101 p_lock_flag =>FND_API.G_FALSE,
102 p_created_by_module => 'IGS',
103 x_return_status =>l_return_status,
104 x_msg_count => l_msg_count,
105 x_msg_data =>l_msg_data,
106 x_loc_id => l_loc_id,
107 p_application_id =>8405
108 );
109
110 x_location_id := l_location_id;
111
112 IF l_return_status IN ('E', 'U') THEN
113 IF l_msg_count > 1 THEN
114 FOR i IN 1..l_msg_count LOOP
115 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
116 tmp_var1 := tmp_var1 || ' '|| tmp_var;
117 END LOOP;
118 x_msg_data := tmp_var1;
119 END IF;
120 RETURN;
121 ELSIF l_return_status = 'S' THEN
122
123
124 IGS_PE_HZ_LOCATIONS_PKG.INSERT_ROW(
125 x_rowid => lv_rowid,
126 x_location_id => l_location_id,
127 x_other_details_1 => x_other_details_1,
128 x_other_details_2 => x_other_details_2,
129 x_other_details_3 => x_other_details_3,
130 -- x_correspondence => x_correspondence,
131 x_date_last_verified => x_date_last_verified,
132 x_contact_person => x_contact_person
133 );
134 IGS_AD_LOCVENUE_ADDR_PKG.INSERT_ROW(
135 x_rowid => x_rowid,
136 x_location_venue_addr_id => l_location_venue_addr_id,
137 x_location_id => l_location_id,
138 x_location_venue_cd => x_location_venue_cd,
139 x_source_type => x_source_type,
140 x_identifying_address_flag => NVL(x_correspondence,'N')
141 );
142 x_location_venue_addr_id := l_location_venue_addr_id;
143 END IF;
144 END IF;
145
146 END INSERT_ROW;
147
148 PROCEDURE UPDATE_ROW (
149 x_rowid IN VARCHAR2,
150 x_location_venue_addr_id IN NUMBER,
151 x_location_id IN NUMBER,
152 x_location_venue_cd IN VARCHAR2,
153 x_start_dt IN DATE,
154 x_end_dt IN DATE,
155 x_country IN VARCHAR2,
156 x_address_style IN VARCHAR2,
157 x_addr_line_1 IN VARCHAR2,
158 x_addr_line_2 IN VARCHAR2,
159 x_addr_line_3 IN VARCHAR2,
160 x_addr_line_4 IN VARCHAR2,
161 x_date_last_verified IN DATE,
162 x_correspondence IN VARCHAR2,
163 x_city IN VARCHAR2,
164 x_state IN VARCHAR2,
165 x_province IN VARCHAR2,
166 x_county IN VARCHAR2,
167 x_postal_code IN VARCHAR2,
168 x_address_lines_phonetic IN VARCHAR2,
169 x_delivery_point_code IN VARCHAR2,
170 x_other_details_1 IN VARCHAR2,
171 x_other_details_2 IN VARCHAR2,
172 x_other_details_3 IN VARCHAR2,
173 x_source_type IN VARCHAR2,
174 x_contact_person IN VARCHAR2 default NULL,
175 x_msg_data OUT NOCOPY VARCHAR2,
176 X_MODE IN VARCHAR2 DEFAULT 'R'
177 ) AS
178
179 p_location_rec_update Hz_Location_v2Pub.location_rec_type;
180
181 l_return_status VARCHAR2(100);
182 l_msg_count NUMBER;
183 l_msg_data VARCHAR2(1000);
184 l_location_id NUMBER := x_location_id;
185 l_loc_id NUMBER;
186 l_last_update_date DATE;
187 lv_rowid VARCHAR2(25);
188
189 tmp_var1 VARCHAR2(2000);
190 tmp_var VARCHAR2(2000);
191
192 CURSOR c_loc IS
193 SELECT OBJECT_VERSION_NUMBER
194 FROM hz_locations
195 WHERE location_id = x_location_id;
196
197 loc_rec c_loc%ROWTYPE;
198
199 BEGIN
200 p_location_rec_update.location_id := x_location_id;
201 p_location_rec_update.country := x_country;
202 p_location_rec_update.address_style := NVL(x_address_style,FND_API.G_MISS_CHAR);
203 p_location_rec_update.address1 := x_addr_line_1;
204 p_location_rec_update.address2 := NVL(x_addr_line_2,FND_API.G_MISS_CHAR);
205 p_location_rec_update.address3 := NVL(x_addr_line_3,FND_API.G_MISS_CHAR);
206 p_location_rec_update.address4 := NVL(x_addr_line_4,FND_API.G_MISS_CHAR);
207 p_location_rec_update.city := NVL(x_city,FND_API.G_MISS_CHAR);
208 p_location_rec_update.state := NVL(x_state,FND_API.G_MISS_CHAR);
209 p_location_rec_update.province := NVL(x_province,FND_API.G_MISS_CHAR);
210 p_location_rec_update.county := NVL(x_county,FND_API.G_MISS_CHAR);
211 p_location_rec_update.postal_code := NVL(x_postal_code,FND_API.G_MISS_CHAR);
212 p_location_rec_update.address_lines_phonetic := NVL(x_address_lines_phonetic,FND_API.G_MISS_CHAR);
213 p_location_rec_update.address_effective_date := NVL(x_start_dt,FND_API.G_MISS_DATE);
214 p_location_rec_update.address_expiration_date := NVL(x_end_dt,FND_API.G_MISS_DATE);
215
216 OPEN c_loc;
217 FETCH c_loc INTO loc_rec;
218 CLOSE c_loc;
219
220 HZ_LOCATION_V2PUB.update_location(
221 p_location_rec => p_location_rec_update,
222 p_object_version_number => loc_rec.object_version_number,
223 x_return_status => l_return_status,
224 x_msg_count => l_msg_count,
225 x_msg_data=> l_msg_data
226 );
227
228 IF l_return_status IN ('E', 'U') THEN
229
230 IF l_msg_count > 1 THEN
231 FOR i IN 1..l_msg_count LOOP
232 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
233 tmp_var1 := tmp_var1 || ' '|| tmp_var;
234 END LOOP;
235 x_msg_data := tmp_var1;
236 END IF;
237 RETURN;
238 ELSIF l_return_status = 'S' THEN
239
240 HZ_TAX_ASSIGNMENT_V2PUB.UPDATE_LOC_ASSIGNMENT(
241 p_location_id => l_location_id,
242 p_lock_flag =>FND_API.G_FALSE,
243 p_created_by_module => 'IGS',
244 x_return_status =>l_return_status,
245 x_msg_count => l_msg_count,
246 x_msg_data => l_msg_data,
247 x_loc_id => l_loc_id,
248 p_application_id =>8405
249 --p_lock_flag
250 );
251
252 IF l_return_status IN ('E', 'U') THEN
253
254 IF l_msg_count > 1 THEN
255 FOR i IN 1..l_msg_count LOOP
256 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
257 tmp_var1 := tmp_var1 || ' '|| tmp_var;
258 END LOOP;
259 x_msg_data := tmp_var1;
260 END IF;
261 RETURN;
262 ELSIF l_return_status = 'S' THEN
263
264 SELECT ROWID INTO lv_rowid FROM IGS_PE_HZ_LOCATIONS WHERE location_id = x_location_id;
265
266 IGS_PE_HZ_LOCATIONS_PKG.UPDATE_ROW(
267 x_rowid => lv_rowid,
268 x_location_id => x_location_id,
269 x_other_details_1 => x_other_details_1,
270 x_other_details_2 => x_other_details_2,
271 x_other_details_3 => x_other_details_3,
272 -- x_correspondence => x_correspondence,
273 x_date_last_verified => x_date_last_verified,
274 x_contact_person => x_contact_person
275 );
276
277 IGS_AD_LOCVENUE_ADDR_PKG.UPDATE_ROW(
278 x_rowid => x_rowid,
279 x_location_venue_addr_id => x_location_venue_addr_id,
280 x_location_id => x_location_id,
281 x_location_venue_cd => x_location_venue_cd,
282 x_source_type => x_source_type,
283 x_identifying_address_flag => NVL(x_correspondence,'N')
284 );
285 END IF;
286 END IF;
287 END UPDATE_ROW;
288
289 end IGS_GR_VENUE_ADDR_PKG;