[Home] [Help]
PACKAGE BODY: APPS.RRS_ASSETS_PKG
Source
1 PACKAGE BODY RRS_ASSETS_PKG AS
2 /* $Header: RRSASSTB.pls 120.9 2008/01/26 00:40:14 sunarang noship $ */
3
4 PROCEDURE CREATE_ASSET_INSTANCES
5 ( errbuf OUT NOCOPY VARCHAR2
6 ,retcode OUT NOCOPY VARCHAR2
7 ,p_source_instance_id IN NUMBER
8 ,p_additional_instances IN VARCHAR2
9 ,p_session_id IN VARCHAR2
10 )
11 IS
12
13 CURSOR SELECTED_SITES_CUR IS
14 SELECT SITE_ID
15 FROM RRS_SITES_INTF
16 WHERE SESSION_ID = p_session_id;
17
18 CURSOR SELECTED_SITE_DET_CUR( c_site_id IN NUMBER ) IS
19 SELECT PARTY_SITE_ID
20 FROM RRS_SITES_B
21 WHERE SITE_ID = c_site_id ;
22
23 CURSOR SUBJECT_IDS_CUR IS
24 SELECT cii.SUBJECT_ID
25 ,csi.INVENTORY_ITEM_ID
26 ,csi.LAST_VLD_ORGANIZATION_ID
27 ,LEVEL
28 FROM CSI_II_RELATIONSHIPS cii,
29 CSI_ITEM_INSTANCES csi
30 WHERE cii.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
31 AND cii.SUBJECT_ID = csi.INSTANCE_ID
32 AND TRUNC(NVL(cii.ACTIVE_START_DATE , SYSDATE)) <= TRUNC(SYSDATE)
33 AND TRUNC(NVL(cii.ACTIVE_END_DATE , SYSDATE )) >= TRUNC(SYSDATE)
34 START WITH cii.OBJECT_ID = p_source_instance_id
35 CONNECT BY PRIOR cii.SUBJECT_ID = cii.OBJECT_ID
36 ORDER BY LEVEL;
37
38 CURSOR OBJECT_ID_CURS(c_subject_id NUMBER) IS
39 SELECT cii.SUBJECT_ID OBJECT_ID
40 ,csi.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
41 FROM CSI_II_RELATIONSHIPS cii,
42 CSI_ITEM_INSTANCES csi
43 WHERE cii.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
44 AND cii.OBJECT_ID = csi.INSTANCE_ID
45 AND TRUNC(NVL(cii.ACTIVE_START_DATE , SYSDATE)) <= TRUNC(SYSDATE)
46 AND TRUNC(NVL(cii.ACTIVE_END_DATE , SYSDATE )) >= TRUNC(SYSDATE)
47 AND cii.OBJECT_ID = p_source_instance_id
48 START WITH cii.SUBJECT_ID = c_subject_id
49 CONNECT BY PRIOR cii.OBJECT_ID = cii.SUBJECT_ID ;
50
51 CURSOR LEVEL_ONE_ASSET_INSTANCES(c_party_site_id NUMBER) IS
52 SELECT CSI.INSTANCE_ID
53 ,CSI.INVENTORY_ITEM_ID
54 FROM CSI_ITEM_INSTANCES CSI
55 WHERE LOCATION_ID = c_party_site_id
56 AND NOT EXISTS (SELECT 1 FROM CSI_II_RELATIONSHIPS CII WHERE CII.SUBJECT_ID = CSI.INSTANCE_ID) ;
57
58
59
60 l_return_status VARCHAR2(1);
61 l_msg_count NUMBER;
62 l_msg_data VARCHAR2(2000);
63
64 l_instance_rec csi_datastructures_pub.instance_rec;
65 l_transaction_rec csi_datastructures_pub.transaction_rec;
66 l_instance_tbl csi_datastructures_pub.instance_tbl;
67 l_relationship_query_rec csi_datastructures_pub.relationship_query_rec;
68 l_ii_relationship_tbl csi_datastructures_pub.ii_relationship_tbl;
69 l_party_site_id rrs_sites_b.site_party_id%TYPE;
70
71
72 TYPE l_parent_child_ids_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
73
74 l_parent_child_ids_tbl l_parent_child_ids_tbl_type ;
75 l_level_one_instance_id_tbl l_parent_child_ids_tbl_type ;
76 l_inv_item_id_tbl l_parent_child_ids_tbl_type ;
77 l_tmp_tbl l_parent_child_ids_tbl_type ;
78 l_exists_flag VARCHAR2(1) := 'N';
79 l_conc_or_online VARCHAR2(30);
80 l_parent_id NUMBER ;
81
82 BEGIN
83 SAVEPOINT begin_create_assets;
84
85 l_conc_or_online := nvl(FND_PROFILE.VALUE('RRS_ASSET_CREATION_MODE'),'ONLINE') ;
86
87 FOR siterec IN SELECTED_SITES_CUR LOOP
88
89 OPEN SELECTED_SITE_DET_CUR(siterec.SITE_ID);
90 FETCH SELECTED_SITE_DET_CUR INTO l_party_site_id;
91 CLOSE SELECTED_SITE_DET_CUR;
92
93 IF p_additional_instances = 'N' THEN
94
95 OPEN LEVEL_ONE_ASSET_INSTANCES(l_party_site_id);
96 FETCH LEVEL_ONE_ASSET_INSTANCES BULK COLLECT INTO l_level_one_instance_id_tbl , l_tmp_tbl;
97 CLOSE LEVEL_ONE_ASSET_INSTANCES;
98
99 -- This is done just to ease the process of checking whether
100 -- inv_item_id already exists. Which is used in the code down
101 -- the line .
102
103 IF (nvl(l_tmp_tbl.LAST,0)> 0) THEN
104 FOR i in l_tmp_tbl.FIRST..l_tmp_tbl.LAST LOOP
105 l_inv_item_id_tbl(l_tmp_tbl(i)) := l_tmp_tbl(i);
106 END LOOP;
107 END IF ;
108
109 END IF ;
110
111 FOR rec IN SUBJECT_IDS_CUR LOOP
112
113 l_instance_rec.INSTANCE_ID := rec.SUBJECT_ID ;
114
115 IF p_additional_instances = 'N' THEN
116
117 -- If its a asset instance at level one
118 -- and its already been applied once , we
119 -- do not want to copy and its child again
120
121 l_exists_flag := 'N' ;
122 IF rec.LEVEL = 1 THEN
123 IF l_inv_item_id_tbl.EXISTS(rec.INVENTORY_ITEM_ID) THEN
124 l_exists_flag := 'Y' ;
125 END IF ;
126 ELSE
127 -- Get its parent at level one .
128 FOR rec1 in OBJECT_ID_CURS(rec.SUBJECT_ID) LOOP
129 IF l_inv_item_id_tbl.EXISTS(rec1.INVENTORY_ITEM_ID) THEN
130 l_exists_flag := 'Y' ;
131 EXIT;
132 ELSE
133 l_exists_flag := 'N' ;
134 END IF ;
135 END LOOP ;
136 END IF ;
137 /**************************************
138 BEGIN
139 SELECT 'Y'
140 INTO l_exists_flag
141 FROM DUAL
142 WHERE EXISTS ( SELECT 'Y'
143 FROM CSI_ITEM_INSTANCES csi
144 WHERE csi.location_id = l_party_site_id
145 --AND csi.instance_id = rec.subject_id Bug#4548344
146 AND csi.inventory_item_id = rec.inventory_item_id --csi.inventory_item_id
147 AND csi.last_vld_organization_id = rec.last_vld_organization_id
148 ) ;
149 --AND csi.location_id = l_party_site_id ;
150 --AND csi.instance_id = rec.subject_id Bug#4548344
151
152 EXCEPTION
153 WHEN NO_DATA_FOUND THEN
154 l_exists_flag := 'N' ;
155 WHEN OTHERS THEN
156 l_exists_flag := 'N' ;
157 END;
158 **************************************/
159 END IF;
160
161 l_instance_rec.LOCATION_ID := l_party_site_id ;
162 l_instance_rec.LOCATION_TYPE_CODE := 'HZ_PARTY_SITES';
163 l_instance_rec.SERIAL_NUMBER := null;
164
165 l_transaction_rec.source_transaction_date := SYSDATE ;
166 l_transaction_rec.transaction_type_id := 1 ;
167
168 IF l_exists_flag = 'N' THEN
169 CSI_ITEM_INSTANCE_PUB.COPY_ITEM_INSTANCE
170 (
171 p_api_version => 1.0
172 ,p_commit => fnd_api.g_false
173 ,p_init_msg_list => fnd_api.g_false
174 ,p_validation_level => fnd_api.g_valid_level_full
175 ,p_source_instance_rec => l_instance_rec
176 ,p_copy_ext_attribs => fnd_api.g_false
177 ,p_copy_org_assignments => fnd_api.g_false
178 ,p_copy_parties => fnd_api.g_false
179 ,p_copy_party_contacts => fnd_api.g_false
180 ,p_copy_accounts => fnd_api.g_false
181 ,p_copy_asset_assignments => fnd_api.g_false
182 ,p_copy_pricing_attribs => fnd_api.g_false
183 ,p_txn_rec => l_transaction_rec
184 ,x_new_instance_tbl => l_instance_tbl
185 ,x_return_status => l_return_status
186 ,x_msg_count => l_msg_count
187 ,x_msg_data => l_msg_data
188 );
189
190 l_parent_child_ids_tbl(l_instance_rec.INSTANCE_ID) := l_instance_tbl(1).INSTANCE_ID;
191 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
192 Raise FND_API.G_EXC_ERROR;
193 END IF;
194 END IF;
195 END LOOP ;
196
197
198 FOR rec IN SUBJECT_IDS_CUR LOOP
199 l_relationship_query_rec.OBJECT_ID := rec.SUBJECT_ID;
200 l_relationship_query_rec.RELATIONSHIP_TYPE_CODE := 'COMPONENT-OF';
201
202 IF l_exists_flag = 'N' THEN
203 CSI_II_RELATIONSHIPS_PUB.GET_RELATIONSHIPS
204 (
205 p_api_version => 1.0
206 ,p_commit => fnd_api.g_false
207 ,p_init_msg_list => fnd_api.g_false
208 ,p_validation_level => fnd_api.g_valid_level_full
209 ,p_relationship_query_rec => l_relationship_query_rec
210 ,p_depth => 1
211 ,p_time_stamp => SYSDATE
212 ,p_active_relationship_only => fnd_api.g_true
213 ,x_relationship_tbl => l_ii_relationship_tbl
214 ,x_return_status => l_return_status
215 ,x_msg_count => l_msg_count
216 ,x_msg_data => l_msg_data
217 );
218 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
219 Raise FND_API.G_EXC_ERROR;
220 END IF;
221
222 IF (l_ii_relationship_tbl.COUNT > 0) THEN
223 FOR i IN l_ii_relationship_tbl.FIRST..l_ii_relationship_tbl.LAST LOOP
224
225 l_ii_relationship_tbl(i).OBJECT_ID := l_parent_child_ids_tbl(l_ii_relationship_tbl(i).OBJECT_ID) ;
226 l_ii_relationship_tbl(i).SUBJECT_ID := l_parent_child_ids_tbl(l_ii_relationship_tbl(i).SUBJECT_ID) ;
227 l_ii_relationship_tbl(i).RELATIONSHIP_ID := null;
228
229 END LOOP ;
230
231 l_transaction_rec.source_transaction_date := sysdate ;
232 l_transaction_rec.transaction_type_id := 1 ;
233
234 CSI_II_RELATIONSHIPS_PUB.CREATE_RELATIONSHIP
235 (
236 p_api_version => 1.0
237 ,p_commit => fnd_api.g_false
238 ,p_init_msg_list => fnd_api.g_false
239 ,p_validation_level => fnd_api.g_valid_level_full
240 ,p_relationship_tbl => l_ii_relationship_tbl
241 ,p_txn_rec => l_transaction_rec
242 ,x_return_status => l_return_status
243 ,x_msg_count => l_msg_count
244 ,x_msg_data => l_msg_data
245 );
246 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
247 Raise FND_API.G_EXC_ERROR;
248 END IF;
249 END IF;
250 END IF; -- end l_exists_flag IF condition
251 END LOOP ; -- end SUBJECT_IDS_CUR cursor
252 END LOOP ; -- end SELECTED_SITES_CUR FOR
253
254 --IF l_conc_or_online <> 'ONLINE' THEN
255 DELETE FROM RRS_SITES_INTF WHERE SESSION_ID = p_session_id ;
256 COMMIT;
257 --END IF;
258
259 EXCEPTION
260 WHEN FND_API.G_EXC_ERROR THEN
261 retcode := '1';
262 errbuf := SQLERRM;
263 ROLLBACK TO begin_create_assets;
264 WHEN OTHERS THEN
265 retcode := '2';
266 errbuf := SQLERRM;
267 ROLLBACK TO begin_create_assets;
268
269 END CREATE_ASSET_INSTANCES;
270
271 PROCEDURE CREATE_ASSET_INSTANCES_WRP
272 ( p_source_instance_id IN NUMBER
273 ,p_additional_instances IN VARCHAR2
274 ,p_session_id IN VARCHAR2
275 ,x_request_id OUT NOCOPY NUMBER
276 ,x_return_status OUT NOCOPY VARCHAR2
277 ,x_msg_count OUT NOCOPY NUMBER
278 ,x_msg_data OUT NOCOPY VARCHAR2
279 )
280 IS
281 l_conc_or_online VARCHAR2(30);
282 l_errbuf VARCHAR2(2000);
283 l_retcode VARCHAR2(2000);
284 BEGIN
285 x_msg_count := 0;
286 x_return_status := FND_API.G_RET_STS_SUCCESS;
287 l_conc_or_online := nvl(FND_PROFILE.VALUE('RRS_ASSET_CREATION_MODE'),'ONLINE') ;
288
289
290 IF l_conc_or_online = 'ONLINE' THEN
291 CREATE_ASSET_INSTANCES
292 (errbuf => l_errbuf
293 ,retcode => l_retcode
294 ,p_source_instance_id => p_source_instance_id
295 ,p_additional_instances => p_additional_instances
296 ,p_session_id => p_session_id
297 ) ;
298 IF l_retcode <> '0' THEN
299 Raise FND_API.G_EXC_ERROR;
300 END IF;
301 ELSE
302 CREATE_ASSET_INSTANCES_CONC
303 ( p_source_instance_id => p_source_instance_id
304 ,p_additional_instances => p_additional_instances
305 ,p_session_id => p_session_id
306 ,x_request_id => x_request_id
307 ,x_return_status => x_return_status
308 ,x_msg_count => x_msg_count
309 ,x_msg_data => x_msg_data
310 ) ;
311 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
312 Raise FND_API.G_EXC_ERROR;
313 END IF;
314 END IF;
315
316 EXCEPTION
317 WHEN FND_API.G_EXC_ERROR THEN
318 x_msg_count := FND_MSG_PUB.count_msg;
319 x_return_status := FND_API.G_RET_STS_ERROR;
320 WHEN OTHERS THEN
321 x_msg_count := FND_MSG_PUB.count_msg;
322 x_return_status := FND_API.G_RET_STS_ERROR;
323 END CREATE_ASSET_INSTANCES_WRP ;
324
325
326 PROCEDURE CREATE_ASSET_INSTANCES_CONC
327 ( p_source_instance_id IN NUMBER
328 ,p_additional_instances IN VARCHAR2
329 ,p_session_id IN VARCHAR2
330 ,x_request_id OUT NOCOPY NUMBER
331 ,x_return_status OUT NOCOPY VARCHAR2
332 ,x_msg_count OUT NOCOPY NUMBER
336
333 ,x_msg_data OUT NOCOPY VARCHAR2
334 )
335 IS
337 BEGIN
338 x_msg_count := 0 ;
339 x_return_status := FND_API.G_RET_STS_SUCCESS;
340
341 x_request_id := FND_REQUEST.SUBMIT_REQUEST
342 (
343 application => 'RRS'
344 ,program => 'RRSCREATEASSETS'
345 ,description => 'RRS : Create Asset Instances For Sites'
346 ,start_time => NULL
347 ,sub_request => false
348 ,argument1 => p_source_instance_id
349 ,argument2 => p_additional_instances
350 ,argument3 => p_session_id
351 );
352
353 IF x_request_id = 0 THEN
354 FND_MESSAGE.Set_Name('RRS','RRS_CON_REQUEST_FAILED');
355 FND_MSG_PUB.Add;
356 RAISE FND_API.G_EXC_ERROR;
357 END IF;
358 COMMIT ;
359 EXCEPTION
360 WHEN FND_API.G_EXC_ERROR THEN
361 x_msg_count := FND_MSG_PUB.COUNT_MSG;
362 x_return_status := FND_API.G_RET_STS_ERROR;
363 WHEN OTHERS THEN
364 x_return_status := 'E' ;
365 x_msg_count := FND_MSG_PUB.COUNT_MSG;
366 END CREATE_ASSET_INSTANCES_CONC ;
367
368 PROCEDURE POPULATE_RRS_SITES_INTF
369 ( p_session_id IN VARCHAR2
370 ,p_site_ids IN RRS_NUMBER_TBL_TYPE DEFAULT NULL
371 ,p_created_by IN NUMBER
372 ,p_creation_date IN DATE
373 ,p_last_updated_by IN NUMBER
374 ,p_last_update_date IN DATE
375 ,p_last_update_login IN NUMBER
376 )
377 IS
378 BEGIN
379 FORALL i in 1..p_site_ids.count
380 INSERT INTO RRS_SITES_INTF
381 ( session_id
382 ,site_id
383 ,created_by
384 ,creation_date
385 ,last_updated_by
386 ,last_update_date
387 ,last_update_login )
388 values
389 ( p_session_id
390 ,p_site_ids(i)
391 ,p_created_by
392 ,p_creation_date
393 ,p_last_updated_by
394 ,p_last_update_date
395 ,p_last_update_login
396 );
397
398 END POPULATE_RRS_SITES_INTF;
399
400
401 END RRS_ASSETS_PKG;
402