[Home] [Help]
PACKAGE BODY: APPS.BOM_RTG_REVISION_UTIL
Source
1 PACKAGE BODY BOM_Rtg_Revision_UTIL AS
2 /* $Header: BOMURRVB.pls 120.1.12000000.2 2007/04/11 10:01:23 shchandr ship $ */
3 /****************************************************************************
4 --
5 -- Copyright (c) 1996 Oracle Corporation, Redwood Shores, CA, USA
6 -- All rights reserved.
7 --
8 -- FILENAME
9 --
10 -- BOMURRVB.pls
11 --
12 -- DESCRIPTION
13 --
14 -- Body of package BOM_Rtg_Revision_UTIL
15 --
16 -- NOTES
17 --
18 -- HISTORY
19 --
20 -- 07-AUG-00 Biao Zhang Initial Creation
21 --
22 ****************************************************************************/
23 G_Pkg_Name VARCHAR2(30) := 'BOM_Rtg_Revision_UTIL';
24 g_token_tbl Error_Handler.Token_Tbl_Type;
25
26 /*********************************************************************
27 * Procedure : Query_Row
28 * Parameters IN : Assembly item id
29 * Organization Id
30 * Alternate_Rtg_Code
31 * Parameters out: Rtg revision exposed column record
32 * Rtg Revision unexposed column record
33 * Mesg token Table
34 * Return Status
35 * Purpose : Procedure will query the database record, seperate the
36 * values into exposed columns and unexposed columns and
37 * return with those records.
38 ***********************************************************************/
39 PROCEDURE Query_Row
40 ( p_assembly_item_id IN NUMBER
41 , p_organization_id IN NUMBER
42 , p_revision IN VARCHAR2
43 , x_rtg_revision_rec IN OUT NOCOPY Bom_Rtg_Pub.rtg_revision_Rec_Type
44 , x_rtg_rev_unexp_rec IN OUT NOCOPY Bom_Rtg_Pub.rtg_rev_unexposed_Rec_Type
45 , x_Return_status IN OUT NOCOPY VARCHAR2
46 )
47 IS
48 l_rtg_revision_rec Bom_Rtg_Pub.Rtg_revision_Rec_Type;
49 l_rtg_rev_unexp_rec Bom_Rtg_Pub.Rtg_rev_Unexposed_Rec_Type;
50 l_return_status VARCHAR2(1):= FND_API.G_RET_STS_SUCCESS;
51 l_dummy varchar2(10);
52 BEGIN
53 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
54 Error_Handler.Write_Debug('Revision: ' ||
55 l_rtg_revision_rec.revision );
56 END IF;
57
58 SELECT inventory_item_id
59 , organization_id
60 , process_revision
61 -- , implementation_date
62 , effectivity_date
63 , attribute_category
64 , attribute1
65 , attribute2
66 , attribute3
67 , attribute4
68 , attribute5
69 , attribute6
70 , attribute7
71 , attribute8
72 , attribute9
73 , attribute10
74 , attribute11
75 , attribute12
76 , attribute13
77 , attribute14
78 , attribute15
79 INTO l_rtg_rev_unexp_rec.assembly_item_id
80 , l_rtg_rev_unexp_rec.organization_id
81 , l_rtg_revision_rec.revision
82 , l_rtg_revision_rec.start_effective_date
83 , l_rtg_revision_rec.attribute_category
84 , l_rtg_revision_rec.attribute1
85 , l_rtg_revision_rec.attribute2
86 , l_rtg_revision_rec.attribute3
87 , l_rtg_revision_rec.attribute4
88 , l_rtg_revision_rec.attribute5
89 , l_rtg_revision_rec.attribute6
90 , l_rtg_revision_rec.attribute7
91 , l_rtg_revision_rec.attribute8
92 , l_rtg_revision_rec.attribute9
93 , l_rtg_revision_rec.attribute10
94 , l_rtg_revision_rec.attribute11
95 , l_rtg_revision_rec.attribute12
96 , l_rtg_revision_rec.attribute13
97 , l_rtg_revision_rec.attribute14
98 , l_rtg_revision_rec.attribute15
99 FROM mtl_rtg_item_revisions
100 WHERE inventory_item_id = p_assembly_item_id
101 AND organization_id = p_organization_id
102 AND process_revision = p_revision;
103
104 x_return_status := BOM_Rtg_Globals.G_RECORD_FOUND;
105 x_rtg_revision_rec := l_rtg_revision_rec;
106 x_rtg_rev_unexp_rec := l_rtg_rev_unexp_rec;
107
108 EXCEPTION
109 WHEN NO_DATA_FOUND THEN
110 x_return_status := BOM_Rtg_Globals.G_RECORD_NOT_FOUND;
111 x_rtg_revision_rec := l_rtg_revision_rec;
112 x_rtg_rev_unexp_rec := l_rtg_rev_unexp_rec;
113 WHEN OTHERS THEN
114 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
115 x_rtg_revision_rec := l_rtg_revision_rec;
116 x_rtg_rev_unexp_rec := l_rtg_rev_unexp_rec;
117
118 END Query_Row;
119
120 /********************************************************************
121 * Procedure : Insert_Row
122 * Parameters IN : rtg Revisioner exposed column record
123 * rtg Revisioner unexposed column record
124 * Parameters out: Message Token Table
125 * Return Status
126 * Purpose : Procedure will perfrom an insert into the
127 * rtg_Bill_Of_Materials table thus creating a new bill
128 *********************************************************************/
129 PROCEDURE Insert_Row
130 ( p_rtg_revision_rec IN Bom_Rtg_Pub.rtg_revision_Rec_Type
131 , p_rtg_rev_unexp_rec IN Bom_Rtg_Pub.rtg_rev_Unexposed_Rec_Type
132 , x_mesg_token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
133 , x_return_Status IN OUT NOCOPY VARCHAR2
134 )
135 IS
136 l_start_effectivity_date DATE;
137 p_implementation_date DATE;
138
139 BEGIN
140
141 /* Bug 5970070. Time stamp is supported for RTG revisions. So populate
142 the revision in l_start_effectivity_date as it is.
143 IF trunc(p_rtg_revision_rec.start_effective_date)
144 = trunc(sysdate) THEN
145 l_start_effectivity_date :=
146 to_date(to_char(sysdate,'DD-MON-YY-HH24:MI:SS'),
147 'DD-MON-YY-HH24:MI:SS');
148 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
149 Error_Handler.Write_Debug('start_effective_date :'||
150 to_char(l_start_effectivity_date,'DD-MON-YY-HH24:MI:SS'));
151 END IF;
152 ELSIF
153 trunc(p_rtg_revision_rec.start_effective_date) > trunc(sysdate) THEN
154 l_start_effectivity_date
155 := trunc(p_rtg_revision_rec.start_effective_date);
156 ELSIF nvl(bom_globals.get_caller_type,'0') = 'MIGRATION' THEN
157 l_start_effectivity_date := p_rtg_revision_rec.start_effective_date;
158 END IF; */
159
160 l_start_effectivity_date := p_rtg_revision_rec.start_effective_date; -- Bug 5970070
161
162 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
163 Error_Handler.Write_Debug('Writing rtg Revisioner rec for '
164 || p_rtg_revision_rec.assembly_item_name);
165 END IF;
166
167 --bug:3254815 Update request id, prog id, prog appl id and prog update date.
168 INSERT INTO mtl_rtg_item_revisions
169 ( inventory_item_id
170 , organization_id
171 , process_revision
172 , implementation_date
173 , effectivity_date
174 , attribute_category
175 , attribute1
176 , attribute2
177 , attribute3
178 , attribute4
179 , attribute5
180 , attribute6
181 , attribute7
182 , attribute8
183 , attribute9
184 , attribute10
185 , attribute11
186 , attribute12
187 , attribute13
188 , attribute14
189 , attribute15
190 , creation_date
191 , created_by
192 , last_update_date
193 , last_updated_by
194 , last_update_login
195 , request_id
196 , program_id
197 , program_application_id
198 , program_update_date
199 )
200 VALUES
201 ( p_rtg_rev_unexp_rec.assembly_item_id
202 , p_rtg_rev_unexp_rec.organization_id
203 , p_rtg_revision_rec.revision
204 , l_start_effectivity_date
205 , l_start_effectivity_date
206 , p_rtg_revision_rec.attribute_category
207 , p_rtg_revision_rec.attribute1
208 , p_rtg_revision_rec.attribute2
209 , p_rtg_revision_rec.attribute3
210 , p_rtg_revision_rec.attribute4
211 , p_rtg_revision_rec.attribute5
212 , p_rtg_revision_rec.attribute6
213 , p_rtg_revision_rec.attribute7
214 , p_rtg_revision_rec.attribute8
215 , p_rtg_revision_rec.attribute9
216 , p_rtg_revision_rec.attribute10
217 , p_rtg_revision_rec.attribute11
218 , p_rtg_revision_rec.attribute12
219 , p_rtg_revision_rec.attribute13
220 , p_rtg_revision_rec.attribute14
221 , p_rtg_revision_rec.attribute15
222 , SYSDATE
223 , BOM_Rtg_Globals.Get_User_Id
224 , SYSDATE
225 , BOM_Rtg_Globals.Get_User_Id
226 , BOM_Rtg_Globals.Get_User_Id
227 , Fnd_Global.Conc_Request_Id
228 , Fnd_Global.Conc_Program_Id
229 , Fnd_Global.Prog_Appl_Id
230 , SYSDATE
231 );
232 x_return_status := FND_API.G_RET_STS_SUCCESS;
233
234 EXCEPTION
235 WHEN OTHERS THEN
236 Error_Handler.Add_Error_Token
237 ( p_message_name => NULL
238 , p_message_text => G_PKG_NAME ||
239 ' :Inserting Record ' ||
240 SQLERRM
241 , x_mesg_token_Tbl => x_mesg_token_tbl
242 );
243 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
244
245 END Insert_Row;
246
247 /********************************************************************
248 * Procedure : Update_Row
249 * Parameters IN : RTG Revisioner exposed column record
250 * RTG Revisioner unexposed column record
251 * Parameters out: Message Token Table
252 * Return Status
253 * Purpose : Procedure will perfrom an Update into the
254 * rtg_Bill_Of_Materials table.
255 ********************************************************************/
256 PROCEDURE Update_Row
257 ( p_RTG_revision_rec IN Bom_Rtg_Pub.RTG_Revision_Rec_Type
258 , p_RTG_rev_unexp_rec IN Bom_Rtg_Pub.RTG_Rev_Unexposed_Rec_Type
259 , x_mesg_token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
260 , x_return_Status IN OUT NOCOPY VARCHAR2
261 )
262 IS
263 p_start_effective_date DATE;
264 p_implementation_date DATE;
265 BEGIN
266
267 --
268 -- The only fields that are updateable in RTG Revisioner are the
269 -- CTP, Priority, completion subinventory, completion_locator,
270 --
271 --
272 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
273 Error_Handler.Write_Debug('Updating routing revision '
274 || p_rtg_revision_rec.revision);
275 END IF;
276
277 UPDATE mtl_rtg_item_revisions
278 SET effectivity_date =
279 p_rtg_revision_rec.start_effective_date
280 , implementation_date =
281 p_rtg_revision_rec.start_effective_date
282 , last_update_date = SYSDATE
283 , last_updated_by = BOM_Rtg_Globals.Get_User_Id
284 , last_update_login = BOM_Rtg_Globals.Get_User_Id
285 , attribute_category =p_rtg_revision_rec.attribute_category , attribute1 = p_rtg_revision_rec.attribute1
286 , attribute2 = p_rtg_revision_rec.attribute2
287 , attribute3 = p_rtg_revision_rec.attribute3
288 , attribute4 = p_rtg_revision_rec.attribute4
289 , attribute5 = p_rtg_revision_rec.attribute5
290 , attribute6 = p_rtg_revision_rec.attribute6
291 , attribute7 = p_rtg_revision_rec.attribute7
292 , attribute8 = p_rtg_revision_rec.attribute8
293 , attribute9 = p_rtg_revision_rec.attribute9
294 , attribute10= p_rtg_revision_rec.attribute10
295 , attribute11= p_rtg_revision_rec.attribute11
296 , attribute12= p_rtg_revision_rec.attribute12
297 , attribute13= p_rtg_revision_rec.attribute13
298 , attribute14= p_rtg_revision_rec.attribute14
299 , attribute15= p_rtg_revision_rec.attribute15
300 , request_id = Fnd_Global.Conc_Request_Id
301 , program_id = Fnd_Global.Conc_Program_Id
302 , program_application_id = Fnd_Global.Prog_Appl_Id
303 , program_update_date = SYSDATE
304 WHERE inventory_item_id = p_rtg_rev_unexp_rec.assembly_item_id AND organization_id = p_rtg_rev_unexp_rec.organization_id
305 AND process_revision = p_rtg_revision_rec.revision;
306
307
308 END Update_Row;
309
310
311 /********************************************************************
312 * Procedure : Delete_Row
313 * Parameters IN : rtg Revisioner exposed column record
314 * rtg Revisioner unexposed column record
315 * Parameters out: Message Token Table
316 * Return Status
317 * Purpose : Procedure will perfrom an Delete from the
318 * rtg_Bill_Of_Materials by creating a delete Group.
319 *********************************************************************/
320 PROCEDURE Delete_Row
321 ( p_rtg_revision_rec IN Bom_Rtg_Pub.rtg_revision_Rec_Type
322 , p_rtg_rev_unexp_rec IN Bom_Rtg_Pub.rtg_rev_Unexposed_Rec_Type
323 , x_mesg_token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
324 , x_return_Status IN OUT NOCOPY VARCHAR2
325 )
326 IS
327 l_rtg_rev_unexp_rec Bom_Rtg_Pub.rtg_rev_Unexposed_Rec_Type
328 := p_rtg_rev_unexp_rec;
329
330 l_mesg_token_tbl Error_Handler.Mesg_Token_Tbl_Type;
331
332 BEGIN
333 x_return_status := FND_API.G_RET_STS_SUCCESS;
334
335 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN
336 Error_Handler.Write_Debug('Delete routing revision '
337 || p_rtg_revision_rec.revision);
338 END IF;
339
340 DELETE FROM mtl_rtg_item_revisions
341 WHERE inventory_item_id = p_rtg_rev_unexp_rec.assembly_item_id
342 AND organization_id = p_rtg_rev_unexp_rec.organization_id
343 AND process_revision = p_rtg_revision_rec.revision;
344
345 EXCEPTION
346 WHEN OTHERS THEN
347 Error_Handler.Add_Error_Token
348 ( p_Message_Name => NULL
349 , p_Message_Text => 'ERROR in Delete Routing Revision' ||
350 substr(SQLERRM, 1, 100) || ' ' ||
351 to_char(SQLCODE)
352 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
353 , x_Mesg_Token_Tbl => x_Mesg_Token_Tbl
354 );
355 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
356 x_mesg_token_tbl := l_mesg_token_tbl;
357
358 END Delete_Row;
359
360 /*********************************************************************
361 * Procedure : Perform_Writes
362 * Parameters IN : Rtg Revisioner Exposed Column Record
363 * Rtg Revisioner Unexposed column record
364 * Parameters out: Messgae Token Table
365 * Return Status
366 * Purpose : This is the only procedure that the user will have
367 * access to when he/she needs to perform any kind of
368 * writes to the bom_operational_routings.
369 *********************************************************************/
370 PROCEDURE Perform_Writes
371 ( p_rtg_revision_rec IN Bom_Rtg_Pub.rtg_revision_Rec_Type
372 , p_rtg_rev_unexp_rec IN Bom_Rtg_Pub.rtg_rev_Unexposed_Rec_Type
373 , x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
374 , x_return_status IN OUT NOCOPY VARCHAR2
375 )
376 IS
377 l_Mesg_Token_tbl Error_Handler.Mesg_Token_Tbl_Type;
378 l_return_status VARCHAR2(1):= FND_API.G_RET_STS_SUCCESS;
379 BEGIN
380 IF p_rtg_revision_rec.transaction_type =
381 BOM_Rtg_Globals.G_OPR_CREATE THEN
382 Insert_Row
383 ( p_rtg_revision_rec => p_rtg_revision_rec
384 , p_rtg_rev_unexp_rec => p_rtg_rev_unexp_rec
385 , x_mesg_token_Tbl => l_mesg_token_tbl
386 , x_return_Status => l_return_status
387 );
388 ELSIF p_rtg_revision_rec.transaction_type =
389 BOM_Rtg_Globals.G_OPR_UPDATE THEN
390 Update_Row
391 ( p_rtg_revision_rec => p_rtg_revision_rec
392 , p_rtg_rev_unexp_rec => p_rtg_rev_unexp_rec
393 , x_mesg_token_Tbl => l_mesg_token_tbl
394 , x_return_Status => l_return_status
395 );
396
397 ELSIF p_rtg_revision_rec.transaction_type =
398 BOM_Rtg_Globals.G_OPR_DELETE THEN
399 Delete_Row
400 ( p_rtg_revision_rec => p_rtg_revision_rec
401 , p_rtg_rev_unexp_rec => p_rtg_rev_unexp_rec
402 , x_mesg_token_Tbl => l_mesg_token_tbl
403 , x_return_Status => l_return_status
404 );
405 END IF;
406 x_return_status := l_return_status;
407 x_mesg_token_tbl := l_mesg_token_tbl;
408
409 END Perform_Writes;
410
411
412 END BOM_Rtg_Revision_UTIL;