1 PACKAGE BODY EAM_REBUILDS_PVT AS
2 /* $Header: EAMWERBB.pls 120.2.12000000.2 2007/03/30 14:17:10 sdandapa ship $*/
3 g_pkg_name CONSTANT VARCHAR2(30):= 'eam_rebuilds_pvt';
4
5 PROCEDURE validate_rebuild(
6 p_init_msg_list IN VARCHAR2,
7 p_validate_mode IN NUMBER,
8 p_organization_id IN NUMBER,
9 p_wip_entity_id IN NUMBER,
10 p_rebuild_item_id IN OUT NOCOPY NUMBER,
11 p_rebuild_item_name IN VARCHAR2,
12 p_rebuild_serial_number IN VARCHAR2,
13 p_rebuild_activity_id IN OUT NOCOPY NUMBER,
14 p_rebuild_activity_name IN VARCHAR2,
15 x_return_status OUT NOCOPY VARCHAR2,
16 x_msg_count OUT NOCOPY NUMBER,
17 x_msg_data OUT NOCOPY VARCHAR2
18 ) IS
19 l_dummy_num number := null;
20 l_dummy_char varchar2(2000) := null;
21 l_current_status number := null;
22 l_object_id number := null;
23 l_parent_object_id number := null;
24 l_serial_control_code number := null;
25 l_cur_organization_id number := null;
26 l_module constant varchar2(200) := 'eam.plsql.'||g_pkg_name
27 ||'.validate_rebuild';
28 l_log boolean := false;
29
30 BEGIN
31 SAVEPOINT VALIDATE_REBUILD;
32 IF FND_API.to_Boolean( p_init_msg_list ) THEN
33 FND_MSG_PUB.initialize;
34 END IF;
35
36 l_log := (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL);
37 if (l_log) then -- true only if logging enabled, avoids costly function call
38 l_log := FND_LOG.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module);
39 end if;
40 if (l_log and (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) then
41 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
42 'Start of ' || l_module || '('
43 || 'p_organization_id='|| p_organization_id || ','
44 || 'p_wip_entity_id='|| p_wip_entity_id || ','
45 || 'p_rebuild_item_id='|| p_rebuild_item_id || ','
46 || 'p_rebuild_item_name='|| p_rebuild_item_name || ','
47 || 'p_rebuild_serial_number='|| p_rebuild_serial_number ||','
48 || 'p_rebuild_activity_id='|| p_rebuild_activity_id ||','
49 || 'p_rebuild_activity_name='|| p_rebuild_activity_name
50 || ')');
51 end if;
52
53
54 x_return_status := FND_API.G_RET_STS_ERROR;
55 x_msg_count := 0;
56 if (p_organization_id is null) then
57 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
58 return;
59 end if;
60
61 --validate rebuild item
62 if (l_log and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) then
63 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module, 'Validating Rebuild Item...');
64 end if;
65 begin
66 if (p_rebuild_item_id is not null) then
67 select inventory_item_id, serial_number_control_code
68 into l_dummy_num, l_serial_control_code
69 from mtl_system_items_b msi, mtl_parameters mp
70 where msi.inventory_item_id = p_rebuild_item_id
71 and msi.organization_id = mp.organization_id
72 and mp.maint_organization_id = p_organization_id
73 and eam_item_type = 3
74 and rownum = 1;
75 elsif (p_rebuild_item_name is not null) then
76 select inventory_item_id, serial_number_control_code
77 into p_rebuild_item_id, l_serial_control_code
78 from mtl_system_items_b_kfv msi, mtl_parameters mp
79 where msi.concatenated_segments = p_rebuild_item_name
80 and msi.organization_id = mp.organization_id
81 and mp.maint_organization_id = p_organization_id
82 and eam_item_type = 3
83 and rownum = 1;
84 else
85 fnd_message.set_name('EAM', 'EAM_REBUILD_ITEM_EXCEPTION');
86 fnd_msg_pub.add;
87 FND_MSG_PUB.Count_And_Get('T', x_msg_count, x_msg_data);
88 return; --no use in continuing further with validations
89 end if;
90 exception
91 when no_data_found then
92 fnd_message.set_name('EAM', 'EAM_INVALID_REBUILD_ITEM');
93 fnd_msg_pub.add;
94 FND_MSG_PUB.Count_And_Get('T', x_msg_count, x_msg_data);
95 return;
96 end;
97
98 if (l_log and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) then
99 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
100 'Finished Validating Rebuild Item Id ' || p_rebuild_item_id);
101 end if;
102
103 --validate rebuild serial
104 if ((l_serial_control_code> 1) and p_rebuild_serial_number is not null) then
105 if (l_log and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) then
106 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
107 'Now Validating Rebuild Serial...' || p_rebuild_item_id);
108 end if;
109 if ((p_wip_entity_id is null) and (p_validate_mode <> VALIDATE_ISSUE)) then
110 -- wip entity id is required for checking the hierarchy
111 return;
112 end if;
113 begin
114 select serial_number, current_status, gen_object_id, current_organization_id
115 into l_dummy_char, l_current_status, l_object_id, l_cur_organization_id
116 from mtl_serial_numbers msn, mtl_parameters mp
117 where inventory_item_id = p_rebuild_item_id
118 and current_organization_id = mp.organization_id
119 and mp.maint_organization_id = p_organization_id
120 and serial_number = p_rebuild_serial_number;
121 --current_status: 1= predefined, 3= in stores, 4=out of stores, 5=in transit
122 if (l_current_status <> 1) then
123 if ((l_current_status <> 3) and (l_current_status <> 4))then
124 raise no_data_found; --current statuses outside of 1,3,4 not allowed
125 end if;
126 if ((l_current_status = 4) and (p_validate_mode = VALIDATE_ISSUE))then
127 raise no_data_found; --current status 4 is not ok for issue transaction
128 end if;
129 --current status 3 is ok for all transactions as per Suresh
130 --now rebuild has to be immediate child of parent work order's object
131 --only if transaction is not issue.
132 if (p_validate_mode <> VALIDATE_ISSUE) then
133
134
135 select msn.gen_object_id into l_parent_object_id
136 from wip_discrete_jobs wdj, mtl_serial_numbers msn, csi_item_instances cii
137 where wdj.wip_entity_id = p_wip_entity_id
138 and wdj.maintenance_object_id = cii.instance_id
139 and wdj.maintenance_object_type = 3
140 and cii.serial_number = msn.serial_number
141 and cii.inventory_item_id = msn.inventory_item_id
142 and cii.last_vld_organization_id = msn.current_organization_id;
143
144 select object_id into l_dummy_num
145 from mtl_object_genealogy
146 where object_id = l_object_id
147 and parent_object_id = l_parent_object_id
148 and start_date_active <= sysdate
149 and (end_date_active is null or end_date_active >= sysdate);
150 end if;
151 end if;
152 if (l_log and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) then
153 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
154 'Finished Validating Rebuild Serial');
155 end if;
156 exception
157 when no_data_found then
158 if (p_validate_mode = VALIDATE_ISSUE) then
159 fnd_message.set_name('EAM', 'EAM_INVALID_RBLD_SERIAL_ISSUE');
160 elsif (p_validate_mode = VALIDATE_REMOVE) then
161 fnd_message.set_name('EAM', 'EAM_INVALID_RBLD_SERIAL_REMOVE');
162 else
163 fnd_message.set_name('EAM', 'EAM_INVALID_RBLD_SERIAL');
164 end if;
165 fnd_message.set_token('SERIAL', p_rebuild_serial_number);
166 fnd_msg_pub.add;
167 FND_MSG_PUB.Count_And_Get('T', x_msg_count, x_msg_data);
168 return;
169 end;
170 end if;
171
172
173 --validate rebuild activity
174 if ((p_rebuild_activity_id is not null) or
175 (p_rebuild_activity_name is not null)) then
176 if (l_log and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) then
177 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
178 'Now Validating Rebuild Activity...');
179 end if;
180 -- serial mandatory if specifying activity for serialized rebuild
181 if ((p_rebuild_serial_number is null) and (l_serial_control_code > 1)) then
182 fnd_message.set_name('EAM', 'EAM_NO_SERIAL_FOR_ACTIVITY');
183 fnd_msg_pub.add;
184 FND_MSG_PUB.Count_And_Get('T', x_msg_count, x_msg_data);
185 return;
186 end if;
187
188 begin
189 if (p_rebuild_activity_id is not null) then
190 select inventory_item_id into l_dummy_num
191 from mtl_system_items_b_kfv
192 where inventory_item_id = p_rebuild_activity_id
193 and organization_id = p_organization_id
194 and eam_item_type = 2;
195 elsif (p_rebuild_activity_name is not null) then
196 select inventory_item_id into p_rebuild_activity_id
197 from mtl_system_items_b_kfv
198 where concatenated_segments = p_rebuild_activity_name
199 and organization_id = p_organization_id
200 and eam_item_type = 2;
201 if (l_log and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) then
202 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
203 'Rebuild Activity Id is ' || p_rebuild_activity_id);
204 end if;
205 if (l_serial_control_code > 1) then
206
207 select meaa.activity_association_id into l_dummy_num
208 from mtl_eam_asset_activities meaa, eam_org_maint_defaults eomd, csi_item_instances cii
209 where ( meaa.end_date_active is null or meaa.end_date_active > sysdate)
210 and (meaa.start_date_active is null or meaa.start_date_active < sysdate)
211 and meaa.asset_activity_id = p_rebuild_activity_id
212 and nvl(meaa.tmpl_flag, 'N') = 'N'
213 and eomd.organization_id = p_organization_id
214 and eomd.object_type = 60
215 and eomd.object_id = meaa.activity_association_id
216 and cii.inventory_item_id = p_rebuild_item_id
217 and cii.serial_number = p_rebuild_serial_number
218 and meaa.maintenance_object_type = 3
219 and meaa.maintenance_object_id = cii.instance_id;
220 else
221 select meaa.activity_association_id into l_dummy_num
222 from mtl_eam_asset_activities meaa, eam_org_maint_defaults eomd
223 where ( meaa.end_date_active is null or meaa.end_date_active > sysdate)
224 and (meaa.start_date_active is null or meaa.start_date_active < sysdate)
225 and meaa.asset_activity_id = p_rebuild_activity_id
226 and meaa.maintenance_object_type = 2
227 and meaa.maintenance_object_id = p_rebuild_item_id
228 and eomd.organization_id = p_organization_id
229 and eomd.object_type = 40
230 and eomd.object_id = meaa.activity_association_id;
231 end if;
232 end if;
233 if (l_log and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) then
234 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
235 'Finished Validating Rebuild Activity');
236 end if;
237 exception
238 when no_data_found then
239 fnd_message.set_name('EAM', 'EAM_INVALID_ACTIVITY');
240 fnd_msg_pub.add;
241 FND_MSG_PUB.Count_And_Get('T', x_msg_count, x_msg_data);
242 return;
243 end;
244 end if;
245
246 x_return_status := FND_API.G_RET_STS_SUCCESS;
247 FND_MSG_PUB.Count_And_Get('T', x_msg_count, x_msg_data);
248 if (l_log and (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) then
249 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module, 'End of ' || l_module);
250 end if;
251
252 EXCEPTION
253 WHEN OTHERS THEN
254 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
255 FND_MSG_PUB.Count_And_Get(
256 p_count => x_msg_count,
257 p_data => x_msg_data);
258 ROLLBACK to VALIDATE_REBUILD;
259 END validate_rebuild;
260
261 END EAM_REBUILDS_PVT;
[Home] [Help]