[Home] [Help]
PACKAGE BODY: APPS.BOM_BOM_REVISION_UTIL
Source
1 PACKAGE BODY Bom_Bom_Revision_Util AS
2 /* $Header: BOMUREVB.pls 120.1 2005/08/17 03:26:54 bbpatel noship $ */
3 /****************************************************************************
4 --
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 --
20 -- 30-JUL-99 Rahul Chitko Initial Creation
21 --
22 ****************************************************************************/
23
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 x_return_status := FND_API.G_RET_STS_SUCCESS;
145
146 EXCEPTION
147 WHEN OTHERS THEN
148 Error_Handler.Add_Error_Token
149 ( p_message_name => NULL
150 , p_message_text => 'Error Inserting record: ' ||
151 SQLERRM
152 , x_mesg_token_tbl => x_mesg_token_tbl
153 );
154 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
155 END Insert_Row;
156
157 PROCEDURE Update_Row
158 ( p_bom_revision_rec IN Bom_Bo_Pub.Bom_Revision_Rec_Type
159 , p_bom_rev_Unexp_Rec IN Bom_Bo_Pub.Bom_Rev_Unexposed_Rec_Type
160 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
161 , x_Return_Status IN OUT NOCOPY VARCHAR2
162 )
163 IS
164 l_language_code VARCHAR2(3);
165 l_revision_id NUMBER;
166 l_user_id NUMBER := FND_GLOBAL.User_Id;
167 l_login_id NUMBER := FND_GLOBAL.Login_Id;
168 BEGIN
169 UPDATE Mtl_Item_Revisions_b
170 SET --description = p_bom_revision_rec.Description,
171 effectivity_date = NVL(p_bom_revision_rec.start_effective_date,sysdate),--bug:4242412 Replace NULL effectivity date by SYSDATE
172 revision_label= p_bom_revision_rec.revision_label,
173 revision_reason= p_bom_revision_rec.revision_reason,
174 last_update_date = SYSDATE,
175 last_update_login = Bom_Globals.Get_User_Id,
176 last_updated_by = Bom_Globals.Get_User_Id,
177 Attribute_Category = p_bom_revision_rec.Attribute_Category,
178 Attribute1 = p_bom_revision_rec.Attribute1,
179 Attribute2 = p_bom_revision_rec.Attribute2,
180 Attribute3 = p_bom_revision_rec.Attribute3,
181 Attribute4 = p_bom_revision_rec.Attribute4,
182 Attribute5 = p_bom_revision_rec.Attribute5,
183 Attribute6 = p_bom_revision_rec.Attribute6,
184 Attribute7 = p_bom_revision_rec.Attribute7,
185 Attribute8 = p_bom_revision_rec.Attribute8,
186 Attribute9 = p_bom_revision_rec.Attribute9,
187 Attribute10 = p_bom_revision_rec.Attribute10,
188 Attribute11 = p_bom_revision_rec.Attribute11,
189 Attribute12 = p_bom_revision_rec.Attribute12,
190 Attribute13 = p_bom_revision_rec.Attribute13,
191 Attribute14 = p_bom_revision_rec.Attribute14,
192 Attribute15 = p_bom_revision_rec.Attribute15,
193 object_version_number = object_version_number + 1,
194 request_id = Fnd_Global.Conc_Request_Id,
195 program_id = Fnd_Global.Conc_Program_Id,
196 program_application_id = Fnd_Global.Prog_Appl_Id,
197 program_update_date = SYSDATE
198 WHERE revision = p_bom_revision_rec.revision
199 AND inventory_item_id = p_bom_rev_unexp_rec.assembly_item_id
200 AND organization_id = p_bom_rev_unexp_rec.organization_id
201 RETURNING revision_id INTO l_revision_id;
202 -- Added conditions for bug 1888688.
203
204 SELECT userenv('LANG') INTO l_language_code FROM dual;
205
206 -- Update the description in the TL table
207 --
208 UPDATE mtl_item_revisions_TL
209 SET description = decode(p_bom_Revision_rec.description, null, description, p_bom_Revision_rec.description)
210 , last_updated_by = l_user_Id
211 , last_update_date = sysdate
212 WHERE revision_id = l_revision_id
213 AND LANGUAGE = l_language_code;
214
215 x_return_status := FND_API.G_RET_STS_SUCCESS;
216
217 EXCEPTION
218 WHEN OTHERS THEN
219 Error_Handler.Add_Error_Token
220 ( p_message_name => NULL
221 , p_message_text => 'Error Updating record: ' ||
222 SQLERRM
223 , x_mesg_token_tbl => x_mesg_token_tbl
224 );
225 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
226
227 END Update_Row;
228
229 PROCEDURE Perform_Writes
230 ( p_bom_revision_rec IN Bom_Bo_Pub.Bom_Revision_Rec_Type
231 , p_bom_rev_Unexp_Rec IN Bom_Bo_Pub.Bom_Rev_Unexposed_Rec_Type
232 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
233 , x_Return_Status IN OUT NOCOPY VARCHAR2
234 )
235 IS
236 l_return_status VARCHAR2(1);
237 l_mesg_token_tbl Error_Handler.Mesg_Token_Tbl_Type;
238 BEGIN
239 IF p_bom_revision_rec.transaction_type = Bom_Globals.G_OPR_CREATE
240 THEN
241 Insert_Row
242 ( p_bom_revision_rec => p_bom_revision_rec
243 , p_bom_rev_unexp_rec => p_bom_rev_unexp_rec
244 , x_return_status => l_return_Status
245 , x_mesg_token_tbl => l_mesg_token_tbl
246 );
247
248 ELSIF p_bom_revision_rec.transaction_type = Bom_Globals.G_OPR_UPDATE
249 THEN
250 Update_Row
251 ( p_bom_revision_rec => p_bom_revision_rec
252 , p_bom_rev_unexp_rec => p_bom_rev_unexp_rec
253 , x_return_status => l_return_Status
254 , x_mesg_token_tbl => l_mesg_token_tbl
255 );
256
257 /* DELETES FOR REVISIONS IS NOT ALLOWD HENCE THERE IS NO DELETE */
258 END IF;
259
260 x_return_status := l_return_status;
261 x_mesg_token_tbl := l_mesg_token_tbl;
262 END;
263
264
265 PROCEDURE Query_Row
266 ( p_revision IN VARCHAR2
267 , p_assembly_item_id IN NUMBER
268 , p_organization_id IN NUMBER
269 , x_bom_revision_rec IN OUT NOCOPY Bom_Bo_Pub.Bom_Revision_Rec_Type
270 , x_bom_rev_unexp_rec IN OUT NOCOPY Bom_Bo_Pub.Bom_Rev_Unexposed_Rec_Type
271 , x_return_status IN OUT NOCOPY VARCHAR
272 )
273 IS
274 BEGIN
275 SELECT
276 inventory_item_id,
277 organization_id,
278 revision,
279 revision_label,
280 revision_reason,
281 effectivity_date,
282 attribute_category,
283 attribute1,
284 attribute2,
285 attribute3,
286 attribute4,
287 attribute5,
288 attribute6,
289 attribute7,
290 attribute8,
291 attribute9,
292 attribute10,
293 attribute11,
294 attribute12,
295 attribute13,
296 attribute14,
297 attribute15,
298 description
299 INTO x_bom_rev_unexp_rec.assembly_item_id,
300 x_bom_rev_unexp_rec.Organization_Id,
301 x_bom_revision_rec.Revision,
302 x_bom_revision_rec.Revision_label,
303 x_bom_revision_rec.Revision_reason,
304 x_bom_revision_rec.start_effective_date,
305 x_bom_revision_rec.Attribute_Category,
306 x_bom_revision_rec.Attribute1,
307 x_bom_revision_rec.Attribute2,
308 x_bom_revision_rec.Attribute3,
309 x_bom_revision_rec.Attribute4,
310 x_bom_revision_rec.Attribute5,
311 x_bom_revision_rec.Attribute6,
312 x_bom_revision_rec.Attribute7,
313 x_bom_revision_rec.Attribute8,
314 x_bom_revision_rec.Attribute9,
315 x_bom_revision_rec.Attribute10,
316 x_bom_revision_rec.Attribute11,
317 x_bom_revision_rec.Attribute12,
318 x_bom_revision_rec.Attribute13,
319 x_bom_revision_rec.Attribute14,
320 x_bom_revision_rec.Attribute15,
321 x_bom_revision_rec.Description
322 FROM mtl_item_revisions
323 WHERE revision = p_revision
324 AND inventory_item_id = p_assembly_item_id
325 AND organization_id = p_organization_id;
326
327 x_return_status := Bom_Globals.G_RECORD_FOUND;
328
329 EXCEPTION
330 WHEN NO_DATA_FOUND THEN
331 x_return_status := Bom_Globals.G_RECORD_NOT_FOUND;
332 WHEN OTHERS THEN
333 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
334 END Query_Row;
335
336 END Bom_Bom_Revision_Util;