[Home] [Help]
PACKAGE BODY: APPS.BOM_BOM_REVISION_UTIL
Source
4 --
1 PACKAGE BODY Bom_Bom_Revision_Util AS
2 /* $Header: BOMUREVB.pls 120.2 2010/06/01 23:29:57 vbrobbey ship $ */
3 /****************************************************************************
5 -- Copyright (c) 1996 Oracle Corporation, Redwood Shores, CA, USA
6 -- All rights reserved.
7 --
8 -- FILENAME
9 --
10 -- BOMUREVB.pls
11 --
12 -- DESCRIPTION
13 --
14 -- Body of package Bom_Bom_Revision_Util
15 --
16 -- NOTES
17 --
18 -- HISTORY
19 --
23
20 -- 30-JUL-99 Rahul Chitko Initial Creation
21 --
22 ****************************************************************************/
24
25 PROCEDURE Insert_Row
26 ( p_bom_revision_rec IN Bom_Bo_Pub.Bom_Revision_Rec_Type
27 , p_bom_rev_Unexp_Rec IN Bom_Bo_Pub.Bom_Rev_Unexposed_Rec_Type
28 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
29 , x_Return_Status IN OUT NOCOPY VARCHAR2
30 )
31 IS
32 l_language_code VARCHAR2(3);
33 l_revision_id NUMBER;
34 l_user_id NUMBER := FND_GLOBAL.User_Id;
35 l_login_id NUMBER := FND_GLOBAL.Login_Id;
36 BEGIN
37 --bug:3254815 Update request id, prog id, prog appl id and prog update date.
38 INSERT INTO MTL_ITEM_REVISIONS_B(
39 inventory_item_id,
40 organization_id,
41 revision,
42 revision_label,
43 revision_reason,
44 last_update_date,
45 last_updated_by,
46 creation_date,
47 created_by,
48 last_update_login,
49 implementation_date,
50 effectivity_date,
51 attribute_category,
52 attribute1,
53 attribute2,
54 attribute3,
55 attribute4,
56 attribute5,
57 attribute6,
58 attribute7,
59 attribute8,
60 attribute9,
61 attribute10,
62 attribute11,
63 attribute12,
64 attribute13,
65 attribute14,
66 attribute15,
67 --description
68 revision_id,
69 object_version_number,
70 request_id,
71 program_id,
72 program_application_id,
73 program_update_date
74 ) VALUES (
75 p_bom_rev_unexp_rec.assembly_item_id,
76 p_bom_rev_unexp_rec.Organization_Id,
77 p_bom_revision_rec.Revision,
78 --* Added Nvl condition for Bug #3573457
79 Nvl(p_bom_revision_rec.Revision_label,p_bom_revision_rec.Revision),
80 p_bom_revision_rec.Revision_reason,
81 SYSDATE,
82 Bom_Globals.Get_User_Id,
83 SYSDATE,
84 Bom_Globals.Get_User_Id,
85 Bom_Globals.Get_User_Id,
86 NVL(p_bom_revision_rec.start_effective_date,sysdate),/*impl date*/--bug:4242412 Replace NULL effectivity date by SYSDATE
87 NVL(p_bom_revision_rec.start_effective_date,sysdate),/*eff. date*/--bug:4242412 Replace NULL effectivity date by SYSDATE
88 p_bom_revision_rec.Attribute_Category,
89 p_bom_revision_rec.Attribute1,
90 p_bom_revision_rec.Attribute2,
91 p_bom_revision_rec.Attribute3,
92 p_bom_revision_rec.Attribute4,
93 p_bom_revision_rec.Attribute5,
94 p_bom_revision_rec.Attribute6,
95 p_bom_revision_rec.Attribute7,
96 p_bom_revision_rec.Attribute8,
97 p_bom_revision_rec.Attribute9,
98 p_bom_revision_rec.Attribute10,
99 p_bom_revision_rec.Attribute11,
100 p_bom_revision_rec.Attribute12,
101 p_bom_revision_rec.Attribute13,
102 p_bom_revision_rec.Attribute14,
103 p_bom_revision_rec.Attribute15,
104 --p_bom_revision_rec.Description
105 mtl_item_revisions_b_s.NEXTVAL,
106 1,
107 Fnd_Global.Conc_Request_Id,
108 Fnd_Global.Conc_Program_Id,
109 Fnd_Global.Prog_Appl_Id,
110 SYSDATE
111 ) RETURNING revision_id INTO l_revision_id;
112
113 SELECT userenv('LANG') INTO l_language_code FROM dual;
114
115 -- Insert into TL table
116
117 INSERT INTO mtl_item_revisions_TL
118 ( Inventory_Item_Id
119 , Organization_Id
120 , Revision_id
121 , Language
122 , Source_Lang
123 , Created_By
124 , Creation_Date
125 , Last_Updated_By
126 , Last_Update_Date
127 , Last_Update_Login
128 , Description
129 )
130 SELECT p_bom_rev_unexp_rec.assembly_item_id
131 , p_bom_rev_unexp_rec.organization_id
132 , l_revision_id
133 , lang.language_code
134 , l_language_code
135 , l_user_Id
136 , sysdate
137 , l_user_Id
138 , sysdate
139 , l_login_Id
140 , p_bom_revision_rec.description
141 FROM FND_LANGUAGES lang
142 WHERE lang.installed_flag in ('I', 'B');
143
144 /*START OF CODE CHANGES FOR CALLING THE BUSINESS EVENT BUG 9734515*/
145 BEGIN
146 INV_ITEM_EVENTS_PVT.Raise_Events(
147 p_event_name => 'EGO_WF_WRAPPER_PVT.G_REV_CHANGE_EVENT'
148 ,p_dml_type => 'CREATE'
149 ,p_inventory_item_id => p_bom_rev_unexp_rec.assembly_item_id
150 ,p_organization_id => p_bom_rev_unexp_rec.organization_id
151 ,p_revision_id => l_revision_id);
152
153 EXCEPTION
154 WHEN OTHERS THEN
155 NULL;
156 END;
160
157 /*END OF CODE CHANGES FOR CALLING THE BUSINESS EVENT BUG 9734515*/
158
159 x_return_status := FND_API.G_RET_STS_SUCCESS;
161 EXCEPTION
162 WHEN OTHERS THEN
163 Error_Handler.Add_Error_Token
164 ( p_message_name => NULL
165 , p_message_text => 'Error Inserting record: ' ||
166 SQLERRM
167 , x_mesg_token_tbl => x_mesg_token_tbl
168 );
169 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
170 END Insert_Row;
171
172 PROCEDURE Update_Row
173 ( p_bom_revision_rec IN Bom_Bo_Pub.Bom_Revision_Rec_Type
177 )
174 , p_bom_rev_Unexp_Rec IN Bom_Bo_Pub.Bom_Rev_Unexposed_Rec_Type
175 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
176 , x_Return_Status IN OUT NOCOPY VARCHAR2
178 IS
179 l_language_code VARCHAR2(3);
180 l_revision_id NUMBER;
181 l_user_id NUMBER := FND_GLOBAL.User_Id;
182 l_login_id NUMBER := FND_GLOBAL.Login_Id;
183 BEGIN
184 UPDATE Mtl_Item_Revisions_b
185 SET --description = p_bom_revision_rec.Description,
186 effectivity_date = NVL(p_bom_revision_rec.start_effective_date,sysdate),--bug:4242412 Replace NULL effectivity date by SYSDATE
187 revision_label= p_bom_revision_rec.revision_label,
188 revision_reason= p_bom_revision_rec.revision_reason,
189 last_update_date = SYSDATE,
190 last_update_login = Bom_Globals.Get_User_Id,
191 last_updated_by = Bom_Globals.Get_User_Id,
192 Attribute_Category = p_bom_revision_rec.Attribute_Category,
193 Attribute1 = p_bom_revision_rec.Attribute1,
194 Attribute2 = p_bom_revision_rec.Attribute2,
195 Attribute3 = p_bom_revision_rec.Attribute3,
196 Attribute4 = p_bom_revision_rec.Attribute4,
197 Attribute5 = p_bom_revision_rec.Attribute5,
198 Attribute6 = p_bom_revision_rec.Attribute6,
199 Attribute7 = p_bom_revision_rec.Attribute7,
200 Attribute8 = p_bom_revision_rec.Attribute8,
201 Attribute9 = p_bom_revision_rec.Attribute9,
202 Attribute10 = p_bom_revision_rec.Attribute10,
203 Attribute11 = p_bom_revision_rec.Attribute11,
204 Attribute12 = p_bom_revision_rec.Attribute12,
205 Attribute13 = p_bom_revision_rec.Attribute13,
206 Attribute14 = p_bom_revision_rec.Attribute14,
207 Attribute15 = p_bom_revision_rec.Attribute15,
208 object_version_number = object_version_number + 1,
209 request_id = Fnd_Global.Conc_Request_Id,
210 program_id = Fnd_Global.Conc_Program_Id,
211 program_application_id = Fnd_Global.Prog_Appl_Id,
212 program_update_date = SYSDATE
213 WHERE revision = p_bom_revision_rec.revision
214 AND inventory_item_id = p_bom_rev_unexp_rec.assembly_item_id
215 AND organization_id = p_bom_rev_unexp_rec.organization_id
216 RETURNING revision_id INTO l_revision_id;
217 -- Added conditions for bug 1888688.
218
219 SELECT userenv('LANG') INTO l_language_code FROM dual;
220
221 -- Update the description in the TL table
222 --
223 UPDATE mtl_item_revisions_TL
224 SET description = decode(p_bom_Revision_rec.description, null, description, p_bom_Revision_rec.description)
225 , last_updated_by = l_user_Id
226 , last_update_date = sysdate
227 WHERE revision_id = l_revision_id
228 AND LANGUAGE = l_language_code;
229
230 /*START OF CODE CHANGES FOR CALLING THE BUSINESS EVENT BUG 9734515*/
231 BEGIN
232 INV_ITEM_EVENTS_PVT.Raise_Events(
233 p_event_name => 'EGO_WF_WRAPPER_PVT.G_REV_CHANGE_EVENT'
234 ,p_dml_type => 'UPDATE'
235 ,p_inventory_item_id => p_bom_rev_unexp_rec.assembly_item_id
236 ,p_organization_id => p_bom_rev_unexp_rec.organization_id
237 ,p_revision_id => l_revision_id);
238
239 EXCEPTION
240 WHEN OTHERS THEN
241 NULL;
242 END;
243 /*END OF CODE CHANGES FOR CALLING THE BUSINESS EVENT BUG 9734515*/
244
245 x_return_status := FND_API.G_RET_STS_SUCCESS;
246
247 EXCEPTION
248 WHEN OTHERS THEN
249 Error_Handler.Add_Error_Token
250 ( p_message_name => NULL
251 , p_message_text => 'Error Updating record: ' ||
252 SQLERRM
253 , x_mesg_token_tbl => x_mesg_token_tbl
254 );
258
255 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
256
257 END Update_Row;
259 PROCEDURE Perform_Writes
260 ( p_bom_revision_rec IN Bom_Bo_Pub.Bom_Revision_Rec_Type
261 , p_bom_rev_Unexp_Rec IN Bom_Bo_Pub.Bom_Rev_Unexposed_Rec_Type
262 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
263 , x_Return_Status IN OUT NOCOPY VARCHAR2
264 )
265 IS
266 l_return_status VARCHAR2(1);
267 l_mesg_token_tbl Error_Handler.Mesg_Token_Tbl_Type;
268 BEGIN
269 IF p_bom_revision_rec.transaction_type = Bom_Globals.G_OPR_CREATE
270 THEN
271 Insert_Row
272 ( p_bom_revision_rec => p_bom_revision_rec
273 , p_bom_rev_unexp_rec => p_bom_rev_unexp_rec
274 , x_return_status => l_return_Status
275 , x_mesg_token_tbl => l_mesg_token_tbl
276 );
277
278 ELSIF p_bom_revision_rec.transaction_type = Bom_Globals.G_OPR_UPDATE
279 THEN
280 Update_Row
281 ( p_bom_revision_rec => p_bom_revision_rec
282 , p_bom_rev_unexp_rec => p_bom_rev_unexp_rec
283 , x_return_status => l_return_Status
284 , x_mesg_token_tbl => l_mesg_token_tbl
285 );
286
287 /* DELETES FOR REVISIONS IS NOT ALLOWD HENCE THERE IS NO DELETE */
288 END IF;
289
290 x_return_status := l_return_status;
291 x_mesg_token_tbl := l_mesg_token_tbl;
292 END;
293
294
295 PROCEDURE Query_Row
296 ( p_revision IN VARCHAR2
297 , p_assembly_item_id IN NUMBER
298 , p_organization_id IN NUMBER
299 , x_bom_revision_rec IN OUT NOCOPY Bom_Bo_Pub.Bom_Revision_Rec_Type
300 , x_bom_rev_unexp_rec IN OUT NOCOPY Bom_Bo_Pub.Bom_Rev_Unexposed_Rec_Type
301 , x_return_status IN OUT NOCOPY VARCHAR
302 )
303 IS
304 BEGIN
305 SELECT
306 inventory_item_id,
307 organization_id,
308 revision,
309 revision_label,
310 revision_reason,
311 effectivity_date,
312 attribute_category,
313 attribute1,
314 attribute2,
315 attribute3,
316 attribute4,
317 attribute5,
318 attribute6,
319 attribute7,
320 attribute8,
321 attribute9,
322 attribute10,
323 attribute11,
324 attribute12,
325 attribute13,
326 attribute14,
327 attribute15,
328 description
329 INTO x_bom_rev_unexp_rec.assembly_item_id,
333 x_bom_revision_rec.Revision_reason,
330 x_bom_rev_unexp_rec.Organization_Id,
331 x_bom_revision_rec.Revision,
332 x_bom_revision_rec.Revision_label,
334 x_bom_revision_rec.start_effective_date,
335 x_bom_revision_rec.Attribute_Category,
336 x_bom_revision_rec.Attribute1,
337 x_bom_revision_rec.Attribute2,
338 x_bom_revision_rec.Attribute3,
339 x_bom_revision_rec.Attribute4,
340 x_bom_revision_rec.Attribute5,
341 x_bom_revision_rec.Attribute6,
342 x_bom_revision_rec.Attribute7,
343 x_bom_revision_rec.Attribute8,
344 x_bom_revision_rec.Attribute9,
345 x_bom_revision_rec.Attribute10,
346 x_bom_revision_rec.Attribute11,
350 x_bom_revision_rec.Attribute15,
347 x_bom_revision_rec.Attribute12,
348 x_bom_revision_rec.Attribute13,
349 x_bom_revision_rec.Attribute14,
351 x_bom_revision_rec.Description
352 FROM mtl_item_revisions
353 WHERE revision = p_revision
354 AND inventory_item_id = p_assembly_item_id
355 AND organization_id = p_organization_id;
356
357 x_return_status := Bom_Globals.G_RECORD_FOUND;
358
359 EXCEPTION
360 WHEN NO_DATA_FOUND THEN
361 x_return_status := Bom_Globals.G_RECORD_NOT_FOUND;
362 WHEN OTHERS THEN
363 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
364 END Query_Row;
365
366 END Bom_Bom_Revision_Util;