[Home] [Help]
PACKAGE BODY: APPS.EAM_GENEALOGY_IMPORT_PUB
Source
1 PACKAGE BODY EAM_GENEALOGY_IMPORT_PUB as
2 /* $Header: EAMPGEIB.pls 120.1 2006/09/27 12:09:16 kmurthy noship $ */
3 -- Start of comments
4 -- API name : import_genealogy
5 -- Type : Public
6 -- Function :
7 -- Pre-reqs : None.
8 -- Parameters :
9 -- IN p_interface_group_id IN NUMBER Required,
10 -- p_purge_option IN VARCHAR2 Optional Default = 'N'
11 --
12 -- OUT errbuf OUT VARCHAR2
13 -- retcode OUT NUMBER
14 --
15 -- Version Initial version 1.0 Himal Karmacharya
16 --
17 -- Notes : This public API imports genealogy info into
18 -- MTL_OBJECT_GENEALOGY table
19 --
20 -- End of comments
21
22
23 g_pkg_name CONSTANT VARCHAR2(30):= 'EAM_GENEALOGY_IMPORT_PUB';
24 g_msg VARCHAR2(2000):= null;
25
26 -- global variable to turn on/off debug logging.
27 G_DEBUG VARCHAR2(1) := NVL(fnd_profile.value('EAM_DEBUG'), 'N');
28
29 PROCEDURE import_genealogy(
30 errbuf OUT NOCOPY VARCHAR2,
31 retcode OUT NOCOPY NUMBER,
32 p_interface_group_id IN NUMBER,
33 p_purge_option IN VARCHAR2 := 'N'
34 ) IS
35 l_api_name CONSTANT VARCHAR2(30) := 'import_genealogy';
36 l_api_version CONSTANT NUMBER := 1.0;
37 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
38 l_conc_status BOOLEAN;
39 l_error_message VARCHAR2(2000);
40 l_error_code NUMBER;
41 l_return_status VARCHAR2(10);
42 l_msg_count NUMBER;
43 l_msg_data VARCHAR2(2000);
44 l_dummy NUMBER;
45 -- Cursor for all the records in this processing group
46
47 CURSOR genealogy_cur IS
48 SELECT *
49 FROM MTL_OBJECT_GENEALOGY_INTERFACE mogi
50 WHERE mogi.group_id = p_interface_group_id
51 AND mogi.process_status = 'R';
52
53 BEGIN
54
55 g_msg := 'Entering ' || l_full_name;
56 IF G_DEBUG = 'Y' THEN
57 fnd_file.put_line(FND_FILE.LOG, g_msg);
58 END IF;
59
60 -- Standard Start of API savepoint
61
62 SAVEPOINT import_genealogy_PUB;
63
64 -- Initialize message list
65 fnd_msg_pub.initialize;
66
67 -- Initialize API return status to success
68 l_conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('NORMAL', l_error_message);
69
70 -- API starts
71 l_error_code := 9999;
72 l_error_message := 'Unknown Exception';
73 g_msg := '';
74 IF G_DEBUG = 'Y' THEN
75 fnd_file.put_line(FND_FILE.LOG, g_msg);
76 END IF;
77
78 g_msg := '******** Starting import of Genealogy ********';
79 IF G_DEBUG = 'Y' THEN
80 fnd_file.put_line(FND_FILE.LOG, g_msg);
81 END IF;
82
83 g_msg := 'Processing interface group ' || p_interface_group_id ||
84 ' with purge option ' || p_purge_option;
85
86 IF G_DEBUG = 'Y' THEN
87 fnd_file.put_line(FND_FILE.LOG, g_msg);
88 END IF;
89
90 g_msg := 'Opening cursor for records in interface table';
91 IF G_DEBUG = 'Y' THEN
92 fnd_file.put_line(FND_FILE.LOG, g_msg);
93 END IF;
94
95 -- process each row in the cursor at a time
96
97 FOR genealogy_rec in genealogy_cur LOOP
98
99 fnd_msg_pub.initialize;
100
101 declare
102
103 incorrect_genealogy exception;
104
105 begin
106
107 -- Initialize API return status to success
108 l_return_status := fnd_api.g_ret_sts_success;
109
110
111 -- check for the item type.
112
113 begin
114 select msi.eam_item_type into l_dummy
115 from mtl_serial_numbers msn, mtl_system_items msi
116 where msn.serial_number = genealogy_rec.serial_number
117 and msi.organization_id = genealogy_rec.organization_id
118 and msi.inventory_item_id = genealogy_rec.inventory_item_id
119 and msn.inventory_item_id = msi.inventory_item_id
120 and msi.organization_id = msn.current_organization_id;
121
122 if (l_dummy <> 1 and l_dummy <> 3) then
123 FND_MESSAGE.SET_NAME('EAM', 'EAM_GEN_INVALID_ITEM_TYPE');
124 FND_MSG_PUB.ADD;
125 RAISE incorrect_genealogy;
126 end if;
127
128 exception
129 when others then
130 FND_MESSAGE.SET_NAME('EAM', 'EAM_GEN_INVALID_ITEM_TYPE');
131 FND_MSG_PUB.ADD;
132 RAISE incorrect_genealogy;
133 end;
134
135 -- if the import mode is 0 then insert a row in the MOG table
136
137 IF genealogy_rec.import_mode = 0 THEN
138
139 if genealogy_rec.start_date_active is null then
140 FND_MESSAGE.SET_NAME('EAM', 'EAM_GEN_NULL_START_DATE');
141 FND_MSG_PUB.ADD;
142 RAISE incorrect_genealogy;
143 end if;
144
145 if l_dummy = 1 then
146
147 INV_GENEALOGY_PUB.insert_genealogy(
148 p_api_version => l_api_version
149 , p_commit => fnd_api.g_true
150 , p_object_type => genealogy_rec.object_type
151 , p_parent_object_type => genealogy_rec.parent_object_type
152 , p_object_id => genealogy_rec.object_id
153 , p_object_number => genealogy_rec.serial_number
154 , p_inventory_item_id => genealogy_rec.inventory_item_id
155 , p_org_id => genealogy_rec.organization_id
156 , p_parent_object_id => genealogy_rec.parent_object_id
157 , p_parent_object_number => genealogy_rec.parent_serial_number
158 , p_parent_inventory_item_id => genealogy_rec.parent_inventory_item_id
159 , p_parent_org_id => genealogy_rec.parent_organization_id
160 , p_genealogy_origin => genealogy_rec.genealogy_origin
161 , p_genealogy_type => genealogy_rec.genealogy_type
162 , p_start_date_active => genealogy_rec.start_date_active
163 , p_end_date_active => genealogy_rec.end_date_active
164 , p_origin_txn_id => genealogy_rec.origin_txn_id
165 , p_update_txn_id => genealogy_rec.update_txn_id
166 , x_return_status => l_return_status
167 , x_msg_count => l_msg_count
168 , x_msg_data => l_msg_data);
169 else
170 wip_eam_genealogy_pvt.create_eam_genealogy(
171 p_api_version => l_api_version
172 , p_commit => fnd_api.g_true
173 , p_object_id => genealogy_rec.object_id
174 , p_serial_number => genealogy_rec.serial_number
175 , p_inventory_item_id => genealogy_rec.inventory_item_id
176 , p_organization_id => genealogy_rec.organization_id
177 , p_parent_object_id => genealogy_rec.parent_object_id
178 , p_parent_serial_number => genealogy_rec.parent_serial_number
179 , p_parent_inventory_item_id => genealogy_rec.parent_inventory_item_id
180 , p_parent_organization_id => genealogy_rec.parent_organization_id
181 , p_start_date_active => genealogy_rec.start_date_active
182 , p_end_date_active => genealogy_rec.end_date_active
183 , p_origin_txn_id => genealogy_rec.origin_txn_id
184 , p_update_txn_id => genealogy_rec.update_txn_id
185 , p_from_eam => fnd_api.g_true
186 , x_return_status => l_return_status
187 , x_msg_count => l_msg_count
188 , x_msg_data => l_msg_data);
189 end if;
190
191 -- if the import mode is 1 then update the row in MOG table
192
193 ELSIF genealogy_rec.import_mode = 1 THEN
194
195 if genealogy_rec.end_date_active is null then
196 FND_MESSAGE.SET_NAME('EAM', 'EAM_GEN_NULL_END_DATE');
197 FND_MSG_PUB.ADD;
198 RAISE incorrect_genealogy;
199 end if;
200
201 if l_dummy = 3 then
202 if genealogy_rec.end_date_active > sysdate then
203 FND_MESSAGE.SET_NAME('WIP', 'WIP_EAM_REBUILD_FUTURE_TXN');
204 FND_MSG_PUB.ADD;
205 RAISE FND_API.G_EXC_ERROR;
206 end if;
207 end if;
208
209 INV_GENEALOGY_PUB.update_genealogy(
210 p_api_version => l_api_version
211 , p_commit => fnd_api.g_true
212 , p_object_type => genealogy_rec.object_type
213 , p_object_id => genealogy_rec.object_id
214 , p_object_number => genealogy_rec.serial_number
215 , p_inventory_item_id => genealogy_rec.inventory_item_id
216 , p_org_id => genealogy_rec.organization_id
217 , p_genealogy_origin => genealogy_rec.genealogy_origin
218 , p_genealogy_type => genealogy_rec.genealogy_type
219 , p_end_date_active => genealogy_rec.end_date_active
220 , p_update_txn_id => genealogy_rec.update_txn_id
221 , x_return_status => l_return_status
222 , x_msg_count => l_msg_count
223 , x_msg_data => l_msg_data);
224
225
226 END IF;
227
228 IF l_return_status = 'E' or l_return_status = 'U' THEN
229 UPDATE MTL_OBJECT_GENEALOGY_INTERFACE mogi
230 SET mogi.process_status = 'E',
231 mogi.error_message = l_msg_data
232 WHERE mogi.group_id = p_interface_group_id and mogi.interface_header_id = genealogy_rec.interface_header_id;
233
234 ELSIF l_return_status = 'S' THEN
235 UPDATE MTL_OBJECT_GENEALOGY_INTERFACE mogi
236 SET mogi.process_status = 'S'
237 WHERE mogi.group_id = p_interface_group_id and mogi.interface_header_id = genealogy_rec.interface_header_id;
238 END IF;
239
240 exception
241 when incorrect_genealogy then
242
243 l_return_status := FND_API.G_RET_STS_ERROR ;
244
245 FND_MSG_PUB.Count_And_Get
246 (p_encoded => FND_API.G_FALSE,
247 p_count => l_msg_count,
248 p_data => l_msg_data
249 );
250
251 UPDATE MTL_OBJECT_GENEALOGY_INTERFACE mogi
252 SET mogi.process_status = 'E',
253 mogi.error_message = l_msg_data
254 WHERE mogi.group_id = p_interface_group_id and mogi.interface_header_id = genealogy_rec.interface_header_id;
255
256 when others then
257
258 l_return_status := FND_API.G_RET_STS_ERROR ;
259
260 FND_MSG_PUB.Count_And_Get
261 (p_encoded => FND_API.G_FALSE,
262 p_count => l_msg_count,
263 p_data => l_msg_data
264 );
265
266 UPDATE MTL_OBJECT_GENEALOGY_INTERFACE mogi
267 SET mogi.process_status = 'E',
268 mogi.error_message = l_msg_data
269 WHERE mogi.group_id = p_interface_group_id and mogi.interface_header_id = genealogy_rec.interface_header_id;
270
271 end;
272
273 END LOOP;
274
275 -- delete rows marked as success
276
277 IF p_purge_option = 'Y' THEN
278 DELETE FROM MTL_OBJECT_GENEALOGY_INTERFACE mogi
279 WHERE mogi.process_status = 'S' and
280 mogi.group_id = p_interface_group_id;
281 END IF;
282
283 COMMIT;
284
285 g_msg := 'Exiting ' || l_full_name;
286 IF G_DEBUG = 'Y' THEN
287 fnd_file.put_line(FND_FILE.LOG, g_msg);
288 END IF;
289
290 l_conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('NORMAL', l_error_message);
291
292
293 EXCEPTION
294
295 WHEN OTHERS THEN
296
297 ROLLBACK TO import_genealogy_PUB;
298
299 l_conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', l_error_message);
300
301 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
302 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
303 END IF;
304
305 FND_MSG_PUB.Count_And_Get
306 (p_encoded => FND_API.G_FALSE,
307 p_count => l_msg_count,
308 p_data => l_msg_data
309 );
310
311 UPDATE MTL_OBJECT_GENEALOGY_INTERFACE mogi
312 SET mogi.process_status = 'E',
313 mogi.error_message = l_msg_data,
314 mogi.error_code = 9999
315 WHERE mogi.group_id = p_interface_group_id;
316
317 IF G_DEBUG = 'Y' THEN
318 fnd_file.put_line(FND_FILE.LOG, l_msg_data);
319 END IF;
320
321 END import_genealogy;
322
323
324 END EAM_GENEALOGY_IMPORT_PUB;