[Home] [Help]
PACKAGE BODY: APPS.RRS_SITES_PKG
Source
1 PACKAGE BODY RRS_SITES_PKG AS
2 /* $Header: RRSSTPKB.pls 120.7 2006/02/02 10:25:00 pfarkade noship $ */
3
4 PROCEDURE CREATE_PROPERTY_LOCATIONS
5 (errbuf OUT NOCOPY VARCHAR2
6 ,retcode OUT NOCOPY VARCHAR2
7 ,p_batch_name IN VARCHAR2
8 ,p_org_id IN NUMBER
9 )
10 IS
11 CURSOR FAILED_RECORDS_CUR IS
12 SELECT RRS.SITE_ID
13 ,RRS.NAME
14 ,RRS.SITE_IDENTIFICATION_NUMBER
15 ,ITF.ERROR_MESSAGE
16 FROM RRS_SITES_VL RRS
17 ,PN_LOCATIONS_ITF ITF
18 WHERE ITF.BATCH_NAME = P_BATCH_NAME
19 AND RRS.SITE_ID = ITF.SITE_ID
20 AND ITF.ERROR_MESSAGE IS NOT NULL ;
21
22 CURSOR UPDATE_ADDRESS_CUR IS
23 SELECT HZ.COUNTRY
24 ,HZ.ADDRESS1
25 ,HZ.ADDRESS2
26 ,HZ.ADDRESS3
27 ,HZ.ADDRESS4
28 ,HZ.CITY
29 ,HZ.POSTAL_CODE
30 ,HZ.STATE
31 ,HZ.PROVINCE
32 ,HZ.COUNTY
33 ,HZ.ADDRESS_STYLE
34 ,PN.BATCH_NAME
35 ,PN.SITE_ID
36 FROM
37 HZ_LOCATIONS HZ
38 ,RRS_SITES_B RRS
39 ,PN_LOCATIONS_ITF PN
40 WHERE HZ.LOCATION_ID = RRS.LOCATION_ID
41 AND PN.SITE_ID = RRS.SITE_ID
42 AND PN.BATCH_NAME = p_batch_name
43 FOR UPDATE;
44
45 CURSOR UPDATE_LOCATION_ID IS
46 SELECT SITE_ID
47 ,LOCATION_ID
48 FROM PN_LOCATIONS_ITF
49 WHERE BATCH_NAME = P_BATCH_NAME
50 AND ERROR_MESSAGE IS NULL ;
51
52 l_debug_mode VARCHAR2(1);
53 l_msg_count NUMBER ;
54
55
56 BEGIN
57
58 FND_MSG_PUB.INITIALIZE ;
59 retcode := 'O' ;
60 errbuf := NULL ;
61
62 IF p_batch_name IS NULL OR p_org_id IS NULL THEN
63 FND_MESSAGE.Set_Name('RRS','RRS_INV_PARAMETER_PASSED');
64 FND_MSG_PUB.Add;
65 RAISE FND_API.G_EXC_ERROR;
66 END IF ;
67
68 FOR rec IN UPDATE_ADDRESS_CUR LOOP
69 UPDATE PN_LOCATIONS_ITF
70 SET COUNTRY = rec.COUNTRY
71 ,ADDRESS_LINE1 = rec.ADDRESS1
72 ,ADDRESS_LINE2 = rec.ADDRESS2
73 ,ADDRESS_LINE3 = rec.ADDRESS3
74 ,ADDRESS_LINE4 = rec.ADDRESS4
75 ,CITY = rec.CITY
76 ,ZIP_CODE = rec.POSTAL_CODE
77 ,STATE = rec.STATE
78 ,PROVINCE = rec.PROVINCE
79 ,COUNTY = rec.COUNTY
80 ,ADDRESS_STYLE = rec.ADDRESS_STYLE
81 WHERE CURRENT OF UPDATE_ADDRESS_CUR;
82 END LOOP ;
83
84 /*FOR rec IN UPDATE_ADDRESS_CUR LOOP
85 UPDATE PN_LOCATIONS_ITF
86 SET COUNTRY = rec.COUNTRY
87 ,ADDRESS_LINE1 = rec.ADDRESS1
88 ,ADDRESS_LINE2 = rec.ADDRESS2
89 ,ADDRESS_LINE3 = rec.ADDRESS3
90 ,ADDRESS_LINE4 = rec.ADDRESS4
91 ,CITY = rec.CITY
92 ,ZIP_CODE = rec.POSTAL_CODE
93 ,STATE = rec.STATE
94 ,PROVINCE = rec.PROVINCE
95 ,COUNTY = rec.COUNTY
96 ,ADDRESS_STYLE = rec.ADDRESS_STYLE
97 WHERE SITE_ID = rec.SITE_ID
98 AND BATCH_NAME = rec.BATCH_NAME ;
99 END LOOP ;*/
100
101 savepoint call_import ;
102
103 PN_CAD_IMPORT.IMPORT_CAD( errbuf => errbuf
104 ,retcode => retcode
105 ,p_batch_name => p_batch_name
106 ,function_flag => 'L'
107 ,p_org_id => p_org_id
108 );
109
110 FOR rec IN FAILED_RECORDS_CUR LOOP
111 FND_MESSAGE.Set_Name('RRS', 'RRS_NAME_NUMBER_ERR');
112 FND_MESSAGE.Set_Token('NAME', rec.NAME);
113 FND_MESSAGE.Set_Token('NUMBER', rec.SITE_IDENTIFICATION_NUMBER);
114 FND_MESSAGE.Set_Token('MESSAGE', rec.ERROR_MESSAGE);
115 FND_MSG_PUB.Add;
116 END LOOP ;
117
118 l_msg_count := FND_MSG_PUB.COUNT_MSG;
119
120 IF l_msg_count > 0 THEN
121 errbuf := 'E' ;
122 rollback to call_import ;
123 ELSE
124 FOR rec IN UPDATE_LOCATION_ID LOOP
125 UPDATE RRS_SITES_B RRS
126 SET RRS.PROPERTY_LOCATION_ID = rec.LOCATION_ID
127 WHERE RRS.SITE_ID = rec.SITE_ID ;
128 END LOOP ;
129 DELETE FROM PN_LOCATIONS_ITF WHERE BATCH_NAME = p_batch_name ;
130 COMMIT ;
131 END IF ;
132 EXCEPTION
133 WHEN FND_API.G_EXC_ERROR THEN
134 retcode := '2';
135 errbuf := FND_MSG_PUB.GET;
136 RETURN ;
137 WHEN OTHERS THEN
138 retcode := '2';
139 errbuf := SQLERRM;
140 RETURN ;
141 END CREATE_PROPERTY_LOCATIONS ;
142
143 PROCEDURE CREATE_PROPERTY_LOCATIONS_WRP
144 (p_batch_name IN VARCHAR2
145 ,p_org_id IN NUMBER
146 ,x_request_id OUT NOCOPY NUMBER
147 ,x_return_status OUT NOCOPY VARCHAR2
148 ,x_msg_count OUT NOCOPY NUMBER
149 ,x_msg_data OUT NOCOPY VARCHAR2
150 )
151 IS
152 l_conc_or_online VARCHAR2(30);
153 l_errbuf VARCHAR2(2000);
154 l_retcode VARCHAR2(2000);
155 BEGIN
156
157 x_msg_count := 0;
158 x_return_status := FND_API.G_RET_STS_SUCCESS;
159 l_conc_or_online := nvl(FND_PROFILE.VALUE('RRS_LOCATION_CREATION_MODE'),'ONLINE') ;
160
161 IF l_conc_or_online = 'ONLINE' THEN
162 CREATE_PROPERTY_LOCATIONS
163 (errbuf => l_errbuf
164 ,retcode => l_retcode
165 ,p_batch_name => p_batch_name
166 ,p_org_id => p_org_id
167 ) ;
168 IF l_retcode <> '0' THEN
169 Raise FND_API.G_EXC_ERROR;
170 END IF;
171 ELSE
172 CREATE_PROPERTY_LOCATIONS_CONC
173 (p_batch_name => p_batch_name
174 ,p_org_id => p_org_id
175 ,x_request_id => x_request_id
176 ,x_return_status => x_return_status
177 ,x_msg_count => x_msg_count
178 ,x_msg_data => x_msg_data
179 ) ;
180 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
181 Raise FND_API.G_EXC_ERROR;
182 END IF;
183 END IF;
184
185 EXCEPTION
186 WHEN FND_API.G_EXC_ERROR THEN
187 x_msg_count := FND_MSG_PUB.count_msg;
188 x_return_status := FND_API.G_RET_STS_ERROR;
189 WHEN OTHERS THEN
190 x_msg_count := FND_MSG_PUB.count_msg;
191 x_return_status := FND_API.G_RET_STS_ERROR;
192 END CREATE_PROPERTY_LOCATIONS_WRP ;
193
194 PROCEDURE CREATE_PROPERTY_LOCATIONS_CONC
195 (p_batch_name IN VARCHAR2
196 ,p_org_id IN NUMBER
197 ,x_request_id OUT NOCOPY NUMBER
198 ,x_return_status OUT NOCOPY VARCHAR2
199 ,x_msg_count OUT NOCOPY NUMBER
200 ,x_msg_data OUT NOCOPY VARCHAR2
201 )
202 IS
203 -- PRAGMA AUTONOMOUS_TRANSACTION;
204 BEGIN
205
206 x_msg_count := 0 ;
207 x_return_status := FND_API.G_RET_STS_SUCCESS;
208
209 x_request_id := fnd_request.submit_request
210 (
211 application => 'RRS'
212 ,program => 'RRSCREATEPL'
213 ,description => 'RRS:Create Property Location For Sites'
214 ,start_time => NULL
215 ,sub_request => false
216 ,argument1 => p_batch_name
217 ,argument2 => p_org_id -- 3671408 changed parameter value to passed IN parameter
218 );
219
220 -- Throw an error if the request could not be submitted.
221 IF x_request_id = 0 THEN
222 FND_MESSAGE.Set_Name('RRS','RRS_CON_REQUEST_FAILED');
223 FND_MSG_PUB.Add;
224 RAISE FND_API.G_EXC_ERROR;
225 END IF;
226 COMMIT ;
227
228 EXCEPTION
229 WHEN FND_API.G_EXC_ERROR THEN
230 x_msg_count := FND_MSG_PUB.COUNT_MSG;
231 x_return_status := FND_API.G_RET_STS_ERROR;
232 WHEN OTHERS THEN
233 x_return_status := 'E' ;
234 x_msg_count := FND_MSG_PUB.COUNT_MSG;
235 END CREATE_PROPERTY_LOCATIONS_CONC ;
236
237
238 PROCEDURE DELETE_TEMPLATE
239 (
240 p_site_id IN NUMBER
241 )
242 IS
243 BEGIN
244 DELETE FROM RRS_SITES_EXT_TL WHERE SITE_ID = p_site_id;
245 DELETE FROM RRS_SITES_EXT_B WHERE SITE_ID = p_site_id;
246 DELETE FROM RRS_SITE_USES WHERE SITE_ID = p_site_id;
247 DELETE FROM RRS_SITES_TL WHERE SITE_ID = p_site_id;
248 DELETE FROM RRS_SITES_B WHERE SITE_ID = p_site_id;
249 COMMIT;
250 END DELETE_TEMPLATE;
251
252 --Bug 4742710
253 PROCEDURE GET_COUNTRYCODE
254 (
255 p_location_id IN NUMBER
256 ,x_country_code OUT NOCOPY VARCHAR2
257 ,x_country_name OUT NOCOPY VARCHAR2
258 )
259 IS
260 BEGIN
261 SELECT HL.COUNTRY,FTV.TERRITORY_SHORT_NAME
262 INTO x_country_code,x_country_name
263 FROM HZ_LOCATIONS HL, FND_TERRITORIES_VL FTV
264 WHERE FTV.TERRITORY_CODE = HL.COUNTRY
265 AND HL.LOCATION_ID = p_location_id;
266 END GET_COUNTRYCODE;
267 END RRS_SITES_PKG;
268