1 PACKAGE BODY EAM_PM_SUPPRESSIONS AS
2 /* $Header: EAMPSUPB.pls 120.1 2005/05/30 10:34:56 appldev $ */
3
4 /**
5 * This function is used to check whether a loop will be formed by adding a new
6 * suppression relation as specified by the given parameters.
7 * This should be called before the record is actually inserted into the table.
8 */
9 function check_no_loop(p_parent_assoc_id in number,
10 p_child_assoc_id in number) return boolean is
11 cursor C is
12 select child_association_id
13 from eam_suppression_relations
14 where parent_association_id = p_child_assoc_id;
15
16 x_child_id number;
17 begin
18 if ( p_child_assoc_id is null ) then
19 return true;
20 end if;
21
22 if ( p_child_assoc_id = p_parent_assoc_id ) then
23 return false;
24 end if;
25
26 -- go to child's children level to check
27 open C;
28 LOOP
29 fetch C into x_child_id;
30 EXIT WHEN ( C%NOTFOUND );
31 if ( NOT check_no_loop(p_parent_assoc_id, x_child_id) ) then
32 close C;
33 return false;
34 end if;
35 END LOOP;
36 close C;
37
38 return true;
39 end check_no_loop;
40
41
42 /**
43 * This procedure is used to check whether the suppression relationship rule is
44 * broken or not by adding one more suppression relation. The rule is that one
45 * can suppress many, but one can only be suppressed by one.
46 * This should be called before the record is actually inserted into the table.
47 */
48 function is_supp_rule_maintained(p_parent_assoc_id in number,
49 p_child_assoc_id in number) return boolean is
50 x_num number;
51 begin
52 select count(*) into x_num
53 from eam_suppression_relations sup,
54 -- eam_pm_schedulings pms,
55 mtl_eam_asset_activities eaa
56 where sup.child_association_id = p_child_assoc_id
57 -- and sup.child_association_id = pms.activity_association_id
58 and sup.child_association_id = eaa.activity_association_id
59 -- and nvl(pms.from_effective_date, sysdate-1) < sysdate
60 -- and nvl(pms.to_effective_date, sysdate+1) > sysdate
61 and nvl(eaa.start_date_active, sysdate-1) < sysdate
62 and nvl(eaa.end_date_active, sysdate+1) > sysdate;
63
64 if ( x_num <> 0 ) then
65 return false;
66 else
67 return true;
68 end if;
69
70 end is_supp_rule_maintained;
71
72 PROCEDURE instantiate_suppressions(
73 p_api_version IN NUMBER ,
74 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
75 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
76 p_validation_level IN NUMBER :=
77 FND_API.G_VALID_LEVEL_FULL ,
78 x_return_status OUT NOCOPY VARCHAR2 ,
79 x_msg_count OUT NOCOPY NUMBER ,
80 x_msg_data OUT NOCOPY VARCHAR2 ,
81 p_maintenance_object_id IN NUMBER
82 )
83 IS
84 l_api_name CONSTANT VARCHAR2(30) := 'instantiate_suppressions';
85 l_api_version CONSTANT NUMBER := 1.0;
86 i number;
87
88 -- This cursor returns all suppression templates associated with the
89 -- asset group
90 /* cursor asset_supp_csr is
91 select supp.parent_association_id, supp.child_association_id,
92 msn.inventory_item_id
93 from mtl_serial_numbers msn, eam_suppression_relations supp,
94 mtl_eam_asset_activities meaa
95 where msn.gen_object_id = p_maintenance_object_id and
96 supp.tmpl_flag = 'Y' and
97 meaa.tmpl_flag = 'Y' and
98 supp.parent_association_id = meaa.activity_association_id and
99 meaa.inventory_item_id = msn.inventory_item_id; */
100
101 cursor asset_supp_csr is
102 select supp.parent_association_id, supp.child_association_id,
103 cii.inventory_item_id
104 from csi_item_instances cii, eam_suppression_relations supp,
105 mtl_eam_asset_activities meaa
106 where cii.instance_id = p_maintenance_object_id and
107 supp.tmpl_flag = 'Y' and
108 meaa.tmpl_flag = 'Y' and
109 supp.parent_association_id = meaa.activity_association_id and
110 meaa.maintenance_object_id = cii.inventory_item_id
111 and meaa.maintenance_object_type=2;
112
113 BEGIN
114 -- Standard Start of API savepoint
115 SAVEPOINT EAM_PM_SUPPRESSIONS;
116 -- Standard call to check for call compatibility.
117 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
118 p_api_version ,
119 l_api_name ,
120 G_PKG_NAME )
121 THEN
122 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
123 END IF;
124 -- Initialize message list if p_init_msg_list is set to TRUE.
125 IF FND_API.to_Boolean( p_init_msg_list ) THEN
126 FND_MSG_PUB.initialize;
127 END IF;
128 -- Initialize API return status to success
129 x_return_status := FND_API.G_RET_STS_SUCCESS;
130
131 -- API body
132 --dbms_output.put_line('instantiating meters');
133
134 -- for loop that loops through each suppression
135 for a_row in asset_supp_csr loop
136 instantiate_suppression(a_row.parent_association_id,
137 a_row.child_association_id, p_maintenance_object_id);
138 end loop;
139 --dbms_output.put_line('end of for loop');
140 -- end if;
141
142 -- end loop;
143
144 -- End of API body.
145 -- Standard check of p_commit.
146 IF FND_API.To_Boolean( p_commit ) THEN
147 --dbms_output.put_line('committing');
148 COMMIT WORK;
149 END IF;
150 -- Standard call to get message count and if count is 1, get message info.
151 FND_MSG_PUB.Count_And_Get
152 ( p_count => x_msg_count ,
153 p_data => x_msg_data
154 );
155 EXCEPTION
156 WHEN FND_API.G_EXC_ERROR THEN
157 --dbms_output.put_line('g_exc_error');
158 ROLLBACK TO EAM_PM_SUPPRESSIONS;
159 x_return_status := FND_API.G_RET_STS_ERROR ;
160 FND_MSG_PUB.Count_And_Get
161 ( p_count => x_msg_count ,
162 p_data => x_msg_data
163 );
164 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
165 --dbms_output.put_line('unexpected error');
166 ROLLBACK TO instantiate_meters_pub;
167 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
168 FND_MSG_PUB.Count_And_Get
169 ( p_count => x_msg_count ,
170 p_data => x_msg_data
171 );
172 WHEN OTHERS THEN
173 --dbms_output.put_line('others');
174 ROLLBACK TO EAM_PM_SUPPRESSIONS;
175 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
176 IF FND_MSG_PUB.Check_Msg_Level
177 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
178 THEN
179 FND_MSG_PUB.Add_Exc_Msg
180 ( G_PKG_NAME ,
181 l_api_name
182 );
183 END IF;
184 FND_MSG_PUB.Count_And_Get
185 ( p_count => x_msg_count ,
186 p_data => x_msg_data
187 );
188
189 END instantiate_suppressions;
190
191 procedure instantiate_suppression(
192 p_parent_association_id IN NUMBER,
193 p_child_association_id IN NUMBER,
194 p_maintenance_object_id IN NUMBER) IS
195
196 l_parent_assoc_id number;
197 l_child_assoc_id number;
198 cursor supp_cursor is
199 select * from eam_suppression_relations
200 where parent_association_id = p_parent_association_id
201 and child_association_id = p_child_association_id;
202 supp_rec supp_cursor%ROWTYPE;
203 BEGIN
204 select meaa_an.activity_association_id into l_parent_assoc_id
205 from mtl_eam_asset_activities meaa_an,
206 mtl_eam_asset_activities meaa_ag
207 where meaa_ag.activity_association_id = p_parent_association_id
208 and meaa_ag.asset_activity_id = meaa_an.asset_activity_id
209 and meaa_an.maintenance_object_id = p_maintenance_object_id;
210
211 select meaa_an.activity_association_id into l_child_assoc_id
212 from mtl_eam_asset_activities meaa_an,
213 mtl_eam_asset_activities meaa_ag
214 where meaa_ag.activity_association_id = p_child_association_id
215 and meaa_ag.asset_activity_id = meaa_an.asset_activity_id
216 and meaa_an.maintenance_object_id = p_maintenance_object_id;
217
218 open supp_cursor;
219 loop
220 fetch supp_cursor into supp_rec;
221 exit when supp_cursor%NOTFOUND;
222 insert into eam_suppression_relations(
223 parent_association_id,
224 child_association_id,
225 description,
226 tmpl_flag,
227 last_update_date,
228 last_updated_by,
229 creation_date,
230 created_by,
231 last_update_login,
232 attribute_category,
233 attribute1,
234 attribute2,
235 attribute3,
236 attribute4,
237 attribute5,
238 attribute6,
239 attribute7,
240 attribute8,
241 attribute9,
242 attribute10,
243 attribute11,
244 attribute12,
245 attribute13,
246 attribute14,
247 attribute15)
248 values(
249 l_parent_assoc_id,
250 l_child_assoc_id,
251 supp_rec.description,
252 'N',
253 sysdate,
254 fnd_global.user_id,
255 sysdate,
256 fnd_global.user_id,
257 fnd_global.login_id,
258 supp_rec.attribute_category,
259 supp_rec.attribute1,
260 supp_rec.attribute2,
261 supp_rec.attribute3,
262 supp_rec.attribute4,
263 supp_rec.attribute5,
264 supp_rec.attribute6,
265 supp_rec.attribute7,
266 supp_rec.attribute8,
267 supp_rec.attribute9,
268 supp_rec.attribute10,
269 supp_rec.attribute11,
270 supp_rec.attribute12,
271 supp_rec.attribute13,
272 supp_rec.attribute14,
273 supp_rec.attribute15 );
274 end loop;
275 close supp_cursor;
276
277 end instantiate_suppression;
278
279 END eam_pm_suppressions;
280
[Home] [Help]