[Home] [Help]
PACKAGE BODY: APPS.BOM_ROUTINGREVISION_PVT
Source
1 PACKAGE BODY Bom_RoutingRevision_Pvt AS
2 -- $Header: BOMVRRVB.pls 115.1 99/07/16 05:17:18 porting ship $
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'Bom_RoutingRevision_Pvt';
5
6 PROCEDURE AssignRtgRevision(
7 p_api_version IN NUMBER,
8 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
9 p_commit IN VARCHAR2 := FND_API.G_FALSE,
10 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
11 x_return_status OUT VARCHAR2,
12 x_msg_count OUT NUMBER,
13 x_msg_data OUT VARCHAR2,
14 p_RtgRevision_rec IN RTG_REVISION_REC_TYPE :=
15 G_MISS_RTG_REVISION_REC,
16 x_RtgRevision_rec OUT RTG_REVISION_REC_TYPE
17 ) IS
18 l_api_name CONSTANT VARCHAR2(30) := 'AssignRtgRevision';
19 l_api_version CONSTANT NUMBER := 1.0;
20 l_RtgRevision_rec RTG_REVISION_REC_TYPE;
21 l_ret_code NUMBER;
22 l_err_text VARCHAR2(2000);
23 cursor l_parameter_csr(P_Code varchar2) is
24 Select organization_id
25 From mtl_parameters
26 Where organization_code = P_Code;
27 BEGIN
28 -- Standard call to check for call compatibility.
29 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
30 G_PKG_NAME) THEN
31 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
32 END IF;
33 -- Initialize message list if p_init_msg_list is set to TRUE.
34 IF FND_API.to_Boolean(p_init_msg_list) THEN
35 FND_MSG_PUB.initialize;
36 END IF;
37 -- Initialize API return status to success
38 x_return_status := FND_API.G_RET_STS_SUCCESS;
39 -- API body
40 l_RtgRevision_rec := p_RtgRevision_rec;
41
42 -- set organization id
43
44 If nvl(l_RtgRevision_rec.organization_code, FND_API.G_MISS_CHAR) <>
45 FND_API.G_MISS_CHAR then
46 For l_parameter_rec in l_parameter_csr(
47 P_Code => l_RtgRevision_rec.organization_code) loop
48 l_RtgRevision_rec.organization_id := l_parameter_rec.organization_id;
49 End loop;
50 End if; -- organization code
51 If l_RtgRevision_rec.organization_id is null then
52 Fnd_Message.Set_Name('BOM', 'BOM_ORG_ID_MISSING');
53 FND_MSG_PUB.Add;
54 raise FND_API.G_EXC_ERROR;
55 End if; -- invalid org
56
57 -- set item id
58 If nvl(l_RtgRevision_rec.inventory_item_number, Fnd_Api.G_Miss_Char) <>
59 Fnd_Api.G_Miss_Char then
60 l_ret_code := INVPUOPI.mtl_pr_trans_prod_item(
61 org_id => l_RtgRevision_rec.organization_id,
62 item_number_in => l_RtgRevision_rec.inventory_item_number,
63 item_id_out => l_RtgRevision_rec.inventory_item_id,
64 err_text => l_err_text);
65 If l_ret_code <> 0 then
66 Fnd_Message.Set_Name('BOM', 'BOM_INV_ITEM_ID_MISSING');
67 FND_MSG_PUB.Add;
68 Raise FND_API.G_EXC_ERROR;
69 End if;
70 End if;
71
72 x_RtgRevision_rec := l_RtgRevision_rec;
73 -- End of API body.
74 -- Standard call to get message count and if count is 1, get message info.
75 FND_MSG_PUB.Count_And_Get(
76 p_count => x_msg_count,
77 p_data => x_msg_data
78 );
79 EXCEPTION
80 WHEN FND_API.G_EXC_ERROR THEN
81 x_return_status := FND_API.G_RET_STS_ERROR;
82 FND_MSG_PUB.Count_And_Get(
83 p_count => x_msg_count,
84 p_data => x_msg_data
85 );
86 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
87 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
88 FND_MSG_PUB.Count_And_Get(
89 p_count => x_msg_count,
90 p_data => x_msg_data
91 );
92 WHEN OTHERS THEN
93 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
94 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
95 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
96 END IF;
97 FND_MSG_PUB.Count_And_Get(
98 p_count => x_msg_count,
99 p_data => x_msg_data
100 );
101 END AssignRtgRevision;
102 PROCEDURE ValidateRtgRevision(
103 p_api_version IN NUMBER,
104 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
105 p_commit IN VARCHAR2 := FND_API.G_FALSE,
106 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
107 x_return_status OUT VARCHAR2,
108 x_msg_count OUT NUMBER,
109 x_msg_data OUT VARCHAR2,
110 p_RtgRevision_rec IN RTG_REVISION_REC_TYPE :=
111 G_MISS_RTG_REVISION_REC,
112 x_RtgRevision_rec OUT RTG_REVISION_REC_TYPE
113 ) IS
114 l_api_name CONSTANT VARCHAR2(30) := 'ValidateRtgRevision';
115 l_api_version CONSTANT NUMBER := 1.0;
116 l_RtgRevision_rec RTG_REVISION_REC_TYPE;
117 l_return_status VARCHAR2(1);
118 l_msg_count NUMBER;
119 l_msg_data VARCHAR2(2000);
120 cursor l_org_csr(P_OrgId number) is
121 Select 'x' dummy
122 From dual
123 Where not exists(
124 Select null
125 From mtl_parameters
126 where organization_id = P_OrgId);
127 cursor l_item_csr(P_ItemId number, P_OrgId number) is
128 Select 'x' dummy
129 From dual
130 Where not exists(
131 Select null
132 from mtl_system_items
133 where organization_id = P_OrgId
134 and inventory_item_id = P_ItemId);
135 cursor l_routing_csr(P_ItemId number, P_OrgId number) is
136 Select 'x' dummy
137 From dual
138 Where not exists(
139 Select null
140 from bom_operational_routings
141 where organization_id = P_OrgId
142 and assembly_item_id = P_ItemId);
143 cursor l_OtherRevs_csr(P_ItemId number, P_OrgId number,
144 P_Revision varchar2, P_EffDate date) is
145 Select 'x' dummy
146 from dual
147 Where exists(
148 Select null
149 from mtl_rtg_item_revisions
150 where inventory_item_id = P_ItemId
151 and organization_id = P_OrgId
152 and ((effectivity_date > P_EffDate and
153 process_revision < P_Revision)
154 or
155 (effectivity_date < P_EffDate and
156 process_revision > P_Revision)
157 )
158 );
159
160 BEGIN
161 -- Standard call to check for call compatibility.
162 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
163 G_PKG_NAME) THEN
164 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
165 END IF;
166 -- Initialize message list if p_init_msg_list is set to TRUE.
167 IF FND_API.to_Boolean(p_init_msg_list) THEN
168 FND_MSG_PUB.initialize;
169 END IF;
170 -- Initialize API return status to success
171 x_return_status := FND_API.G_RET_STS_SUCCESS;
172 -- API body
173 l_RtgRevision_rec := p_RtgRevision_rec;
174
175 If p_validation_level = FND_API.G_VALID_LEVEL_FULL then
176 AssignRtgRevision(
177 p_api_version => 1,
178 p_init_msg_list => p_init_msg_list,
179 p_commit => p_commit,
180 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
181 x_return_status => l_return_status,
182 x_msg_count => l_msg_count,
183 x_msg_data => l_msg_data,
184 p_RtgRevision_rec => l_RtgRevision_rec,
185 x_RtgRevision_rec => l_RtgRevision_rec);
186 If l_return_status = FND_API.G_RET_STS_ERROR then
187 Raise FND_API.G_EXC_ERROR;
188 Elsif l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
189 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
190 End if; -- error
191 End if; -- assign values
192
193 -- Check if process revision is null
194 If l_RtgRevision_rec.process_revision is null then
195 Fnd_Message.Set_Name('BOM', 'BOM_NULL_RTG_REV');
196 FND_MSG_PUB.Add;
197 Raise FND_API.G_EXC_ERROR;
198 End if;
199
200 -- Check for valid org id
201 For l_org_rec in l_org_csr(P_OrgId => l_RtgRevision_rec.organization_id) loop
202 Fnd_Message.Set_Name('BOM', 'BOM_INVALID_ORG_ID');
203 FND_MSG_PUB.Add;
204 Raise FND_API.G_EXC_ERROR;
205 End loop;
206
207 -- Check if assembly item exists
208 For l_item_rec in l_item_csr(
209 P_ItemId => l_RtgRevision_rec.inventory_item_id,
210 P_OrgId => l_RtgRevision_rec.organization_id) loop
211 Fnd_Message.Set_Name('BOM', 'BOM_INV_ITEM_INVALID');
212 FND_MSG_PUB.Add;
213 Raise FND_API.G_EXC_ERROR;
214 End loop;
215
216 -- check if a valid routing exists for this revision
217 For l_routing_rec in l_routing_csr(
218 P_ItemId => l_RtgRevision_rec.inventory_item_id,
219 P_OrgId => l_RtgRevision_rec.organization_id) loop
220 Fnd_Message.Set_Name('BOM', 'BOM_RTG_DOES_NOT_EXIST');
221 FND_MSG_PUB.Add;
222 Raise FND_API.G_EXC_ERROR;
223 End loop;
224
225 -- check for ascending order
226 For l_OtherRevs_rec in l_OtherRevs_csr(
227 P_ItemId => l_RtgRevision_rec.inventory_item_id,
228 P_OrgId => l_RtgRevision_rec.organization_id,
229 P_Revision => l_RtgRevision_rec.process_revision,
230 P_EffDate => l_RtgRevision_rec.effectivity_date) loop
231 Fnd_Message.Set_Name('BOM', 'BOM_REV_INVALID');
232 FND_MSG_PUB.Add;
233 Raise FND_API.G_EXC_ERROR;
234 End loop;
235
236 x_RtgRevision_rec := l_RtgRevision_rec;
237 -- End of API body.
238
239 -- Standard call to get message count and if count is 1, get message info.
240 FND_MSG_PUB.Count_And_Get(
241 p_count => x_msg_count,
242 p_data => x_msg_data
243 );
244 EXCEPTION
245 WHEN FND_API.G_EXC_ERROR THEN
246 x_return_status := FND_API.G_RET_STS_ERROR;
247 FND_MSG_PUB.Count_And_Get(
248 p_count => x_msg_count,
249 p_data => x_msg_data
250 );
251 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
252 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
253 FND_MSG_PUB.Count_And_Get(
254 p_count => x_msg_count,
255 p_data => x_msg_data
256 );
257
258 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
259 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
260 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
261 END IF;
262 FND_MSG_PUB.Count_And_Get(
263 p_count => x_msg_count,
264 p_data => x_msg_data
265 );
266 END ValidateRtgRevision;
267 PROCEDURE CreateRtgRevision(
268 p_api_version IN NUMBER,
269 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
270 p_commit IN VARCHAR2 := FND_API.G_FALSE,
271 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
272 x_return_status OUT VARCHAR2,
273 x_msg_count OUT NUMBER,
274 x_msg_data OUT VARCHAR2,
275 p_RtgRevision_rec IN RTG_REVISION_REC_TYPE :=
276 G_MISS_RTG_REVISION_REC,
277 x_RtgRevision_rec OUT RTG_REVISION_REC_TYPE
278 ) IS
279 l_api_name CONSTANT VARCHAR2(30) := 'CreateRtgRevision';
280 l_api_version CONSTANT NUMBER := 1.0;
281 l_RtgRevision_rec RTG_REVISION_REC_TYPE;
282 l_return_status VARCHAR2(1);
283 l_msg_count NUMBER;
284 l_msg_data VARCHAR2(2000);
285 l_UserId number;
286 l_LoginId number;
287 l_RequestId number;
288 l_ProgramId number;
289 l_ApplicationId number;
290 l_ProgramUpdate date;
291 BEGIN
292 -- Standard Start of API savepoint
293 SAVEPOINT CreateRtgRevision_Pvt;
294 -- Standard call to check for call compatibility.
295 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
296 G_PKG_NAME) THEN
297 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
298 END IF;
299 -- Initialize message list if p_init_msg_list is set to TRUE.
300 IF FND_API.to_Boolean(p_init_msg_list) THEN
301 FND_MSG_PUB.initialize;
302 END IF;
303 -- Initialize API return status to success
304 x_return_status := FND_API.G_RET_STS_SUCCESS;
305
306 -- API body
307 l_RtgRevision_rec := p_RtgRevision_rec;
308
309 -- initialize record
310 If l_RtgRevision_rec.inventory_item_id = Fnd_Api.G_Miss_Num then
311 l_RtgRevision_rec.inventory_item_id := null;
312 End if;
313
314 If l_RtgRevision_rec.inventory_item_number = Fnd_Api.G_Miss_Char then
315 l_RtgRevision_rec.inventory_item_number := null;
316 End if;
317
318 If l_RtgRevision_rec.organization_id = Fnd_Api.G_Miss_Num then
319 l_RtgRevision_rec.organization_id := null;
320 End if;
321
322 If l_RtgRevision_rec.organization_code = Fnd_Api.G_Miss_Char then
323 l_RtgRevision_rec.organization_code := null;
324 End if;
325
326 If l_RtgRevision_rec.process_revision = Fnd_Api.G_Miss_Char then
327 l_RtgRevision_rec.process_revision := null;
328 End if;
329
330 If l_RtgRevision_rec.change_notice = Fnd_Api.G_Miss_Char then
331 l_RtgRevision_rec.change_notice := null;
332 End if;
333
334 If l_RtgRevision_rec.ecn_initiation_date = Fnd_Api.G_Miss_Date then
335 l_RtgRevision_rec.ecn_initiation_date := null;
336 End if;
337
338 If l_RtgRevision_rec.implemented_serial_number = Fnd_Api.G_Miss_Char then
339 l_RtgRevision_rec.implemented_serial_number := null;
340 End if;
341
342 If nvl(l_RtgRevision_rec.effectivity_date, Fnd_Api.G_Miss_Date) =
343 Fnd_Api.G_Miss_Date then
344 l_RtgRevision_rec.effectivity_date := sysdate;
345 End if;
346 l_RtgRevision_rec.implementation_date := l_RtgRevision_rec.effectivity_date;
347
348 If l_RtgRevision_rec.attribute_category = Fnd_Api.G_Miss_Char then
349 l_RtgRevision_rec.attribute_category := null;
350 End if;
351
352 If l_RtgRevision_rec.attribute1 = Fnd_Api.G_Miss_Char then
353 l_RtgRevision_rec.attribute1 := null;
354 End if;
355
356 If l_RtgRevision_rec.attribute2 = Fnd_Api.G_Miss_Char then
357 l_RtgRevision_rec.attribute2 := null;
358 End if;
359
360 If l_RtgRevision_rec.attribute3 = Fnd_Api.G_Miss_Char then
361 l_RtgRevision_rec.attribute3 := null;
362 End if;
363
364 If l_RtgRevision_rec.attribute4 = Fnd_Api.G_Miss_Char then
365 l_RtgRevision_rec.attribute4 := null;
366 End if;
367
368 If l_RtgRevision_rec.attribute5 = Fnd_Api.G_Miss_Char then
369 l_RtgRevision_rec.attribute5 := null;
370 End if;
371
372 If l_RtgRevision_rec.attribute6 = Fnd_Api.G_Miss_Char then
373 l_RtgRevision_rec.attribute6 := null;
374 End if;
375
376 If l_RtgRevision_rec.attribute7 = Fnd_Api.G_Miss_Char then
377 l_RtgRevision_rec.attribute7 := null;
378 End if;
379
380 If l_RtgRevision_rec.attribute8 = Fnd_Api.G_Miss_Char then
381 l_RtgRevision_rec.attribute8 := null;
382 End if;
383
384 If l_RtgRevision_rec.attribute9 = Fnd_Api.G_Miss_Char then
385 l_RtgRevision_rec.attribute9 := null;
386 End if;
387
388 If l_RtgRevision_rec.attribute10 = Fnd_Api.G_Miss_Char then
389 l_RtgRevision_rec.attribute10 := null;
390 End if;
391
392 If l_RtgRevision_rec.attribute11 = Fnd_Api.G_Miss_Char then
393 l_RtgRevision_rec.attribute11 := null;
394 End if;
395
396 If l_RtgRevision_rec.attribute12 = Fnd_Api.G_Miss_Char then
397 l_RtgRevision_rec.attribute12 := null;
398 End if;
399
400 If l_RtgRevision_rec.attribute13 = Fnd_Api.G_Miss_Char then
401 l_RtgRevision_rec.attribute13 := null;
402 End if;
403
404 If l_RtgRevision_rec.attribute14 = Fnd_Api.G_Miss_Char then
405 l_RtgRevision_rec.attribute14 := null;
406 End if;
407
408 If l_RtgRevision_rec.attribute15 = Fnd_Api.G_Miss_Char then
409 l_RtgRevision_rec.attribute15 := null;
410 End if;
411
412 If p_validation_level > FND_API.G_VALID_LEVEL_NONE then
413 ValidateRtgRevision(
414 p_api_version => 1,
415 p_init_msg_list => p_init_msg_list,
416 p_commit => p_commit,
417 p_validation_level => p_validation_level,
418 x_return_status => l_return_status,
419 x_msg_count => l_msg_count,
420 x_msg_data => l_msg_data,
421 p_RtgRevision_rec => l_RtgRevision_rec,
422 x_RtgRevision_rec => l_RtgRevision_rec);
423 If l_return_status = FND_API.G_RET_STS_ERROR then
424 Raise FND_API.G_EXC_ERROR;
425 Elsif l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
426 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
427 End if; -- validation error
428 End if; -- validate before inserting
429
430 l_UserId := nvl(Fnd_Global.USER_ID, -1);
431 l_LoginId := Fnd_Global.LOGIN_ID;
432 l_RequestId := Fnd_Global.CONC_REQUEST_ID;
433 l_ProgramId := Fnd_Global.CONC_PROGRAM_ID;
434 l_ApplicationId := Fnd_Global.PROG_APPL_ID;
435 -- do not use decode because of implicit data type conversions
436 If l_RequestId is null then
437 l_ProgramUpdate := null;
438 Else
439 l_ProgramUpdate := sysdate;
440 End if;
441
442 Insert into mtl_rtg_item_revisions(
443 inventory_item_id,
444 organization_id,
445 process_revision,
446 last_update_date,
447 last_updated_by,
448 creation_date,
449 created_by,
450 last_update_login,
451 change_notice,
452 ecn_initiation_date,
453 implementation_date,
454 implemented_serial_number,
455 effectivity_date,
456 attribute_category,
457 attribute1,
458 attribute2,
459 attribute3,
460 attribute4,
461 attribute5,
462 attribute6,
463 attribute7,
464 attribute8,
465 attribute9,
466 attribute10,
467 attribute11,
468 attribute12,
469 attribute13,
470 attribute14,
471 attribute15,
472 request_id,
473 program_application_id,
474 program_id,
475 program_update_date)
476 values(
477 l_RtgRevision_rec.inventory_item_id,
478 l_RtgRevision_rec.organization_id,
479 l_RtgRevision_rec.process_revision,
480 sysdate,
481 l_UserId,
482 sysdate,
483 l_UserId,
484 l_LoginId,
485 l_RtgRevision_rec.change_notice,
486 l_RtgRevision_rec.ecn_initiation_date,
487 l_RtgRevision_rec.implementation_date,
488 l_RtgRevision_rec.implemented_serial_number,
489 l_RtgRevision_rec.effectivity_date,
490 l_RtgRevision_rec.attribute_category,
491 l_RtgRevision_rec.attribute1,
492 l_RtgRevision_rec.attribute2,
493 l_RtgRevision_rec.attribute3,
494 l_RtgRevision_rec.attribute4,
495 l_RtgRevision_rec.attribute5,
496 l_RtgRevision_rec.attribute6,
497 l_RtgRevision_rec.attribute7,
498 l_RtgRevision_rec.attribute8,
499 l_RtgRevision_rec.attribute9,
500 l_RtgRevision_rec.attribute10,
501 l_RtgRevision_rec.attribute11,
502 l_RtgRevision_rec.attribute12,
503 l_RtgRevision_rec.attribute13,
504 l_RtgRevision_rec.attribute14,
505 l_RtgRevision_rec.attribute15,
506 l_RequestId,
507 l_ApplicationId,
508 l_ProgramId,
509 l_ProgramUpdate);
510
511 x_RtgRevision_rec := l_RtgRevision_rec;
512 -- End of API body.
513
514 -- Standard check of p_commit.
515 IF FND_API.To_Boolean(p_commit) THEN
516 COMMIT WORK;
517 END IF;
518 -- Standard call to get message count and if count is 1, get message info.
519 FND_MSG_PUB.Count_And_Get(
520 p_count => x_msg_count,
521 p_data => x_msg_data
522 );
523 EXCEPTION
524 WHEN FND_API.G_EXC_ERROR THEN
525 ROLLBACK TO CreateRtgRevision_Pvt;
526 x_return_status := FND_API.G_RET_STS_ERROR;
527 FND_MSG_PUB.Count_And_Get(
528 p_count => x_msg_count,
529 p_data => x_msg_data
530 );
531 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
532 ROLLBACK TO CreateRtgRevision_Pvt;
533 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
534 FND_MSG_PUB.Count_And_Get(
535 p_count => x_msg_count,
536 p_data => x_msg_data
537 );
538 WHEN DUP_VAL_ON_INDEX then
539 ROLLBACK TO CreateRtgRevision_Pvt;
540 x_return_status := FND_API.G_RET_STS_ERROR;
541 Fnd_Message.Set_Name('BOM', 'BOM_REV_INVALID');
542 FND_MSG_PUB.Add;
543 FND_MSG_PUB.Count_And_Get(
544 p_count => x_msg_count,
545 p_data => x_msg_data
546 );
547 WHEN OTHERS THEN
548 ROLLBACK TO CreateRtgRevision_Pvt;
549 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
550 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
551 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
552 END IF;
553 FND_MSG_PUB.Count_And_Get(
554 p_count => x_msg_count,
555 p_data => x_msg_data
556 );
557 END CreateRtgRevision;
558 PROCEDURE UpdateRtgRevision(
559 p_api_version IN NUMBER,
560 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
561 p_commit IN VARCHAR2 := FND_API.G_FALSE,
562 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
563 x_return_status OUT VARCHAR2,
564 x_msg_count OUT NUMBER,
565 x_msg_data OUT VARCHAR2,
566 p_RtgRevision_rec IN RTG_REVISION_REC_TYPE :=
567 G_MISS_RTG_REVISION_REC,
568 x_RtgRevision_rec OUT RTG_REVISION_REC_TYPE
569 ) IS
570 l_api_name CONSTANT VARCHAR2(30) := 'UpdateRtgRevision';
571 l_api_version CONSTANT NUMBER := 1.0;
572 l_RtgRevision_rec RTG_REVISION_REC_TYPE;
573 l_return_status VARCHAR2(1);
574 l_msg_count NUMBER;
575 l_msg_data VARCHAR2(2000);
576 l_UserId NUMBER;
577 l_LoginId NUMBER;
578 l_RequestId NUMBER;
579 l_ProgramId NUMBER;
580 l_ProgramUpdate DATE;
581 l_ApplicationId NUMBER;
582 cursor l_ExistiongRevision_csr(P_ItemId number, P_OrgId number,
583 P_Revision varchar2) is
584 Select *
585 From mtl_rtg_item_revisions
586 Where inventory_item_id = P_ItemId
587 And organization_id = P_OrgId
588 And process_revision = P_Revision;
589 l_RowsFound boolean := false;
590 BEGIN
591 -- Standard Start of API savepoint
592 SAVEPOINT UpdateRtgRevision_Pvt;
593 -- Standard call to check for call compatibility.
594 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
595 G_PKG_NAME) THEN
596 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
597 END IF;
598 -- Initialize message list if p_init_msg_list is set to TRUE.
599 IF FND_API.to_Boolean(p_init_msg_list) THEN
600 FND_MSG_PUB.initialize;
601 END IF;
602 -- Initialize API return status to success
603 x_return_status := FND_API.G_RET_STS_SUCCESS;
604
605 -- API body
606 l_RtgRevision_rec := p_RtgRevision_rec ;
607
608 If p_validation_level = FND_API.G_VALID_LEVEL_FULL then
609 AssignRtgRevision(
610 p_api_version => 1,
611 p_init_msg_list => p_init_msg_list,
612 p_commit => p_commit,
613 p_validation_level => p_validation_level,
614 x_return_status => l_return_status,
615 x_msg_count => l_msg_count,
616 x_msg_data => l_msg_data,
617 p_RtgRevision_rec => l_RtgRevision_rec,
618 x_RtgRevision_rec => l_RtgRevision_rec
619 );
620 If l_return_status = FND_API.G_RET_STS_ERROR then
621 Raise FND_API.G_EXC_ERROR;
622 Elsif l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
623 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
624 End if; -- assign error
625 End If; -- assign
626
627 -- populate unspecified values
628 l_RowsFound := false;
629 For l_ExistingRev_rec in l_ExistiongRevision_csr(
630 P_ItemId => l_RtgRevision_rec.inventory_item_id,
631 P_OrgId => l_RtgRevision_rec.organization_id,
632 P_Revision => l_RtgRevision_rec.process_revision) loop
633 l_RowsFound := true;
634
635 If l_RtgRevision_rec.change_notice = Fnd_Api.G_Miss_Char then
636 l_RtgRevision_rec.change_notice := l_ExistingRev_rec.change_notice;
637 End if;
638
639 If l_RtgRevision_rec.ecn_initiation_date = Fnd_Api.G_Miss_Date then
640 l_RtgRevision_rec.ecn_initiation_date :=
641 l_ExistingRev_rec.ecn_initiation_date;
642 End if;
643
644 If l_RtgRevision_rec.implemented_serial_number = Fnd_Api.G_Miss_Char then
645 l_RtgRevision_rec.implemented_serial_number :=
646 l_ExistingRev_rec.implemented_serial_number;
647 End if;
648
649 If nvl(l_RtgRevision_rec.effectivity_date, Fnd_Api.G_Miss_Date) =
650 Fnd_Api.G_Miss_Date then
651 l_RtgRevision_rec.effectivity_date := l_ExistingRev_rec.effectivity_date;
652 End if;
653 l_RtgRevision_rec.implementation_date := l_RtgRevision_rec.effectivity_date;
654
655 If l_RtgRevision_rec.attribute_category = Fnd_Api.G_Miss_Char then
656 l_RtgRevision_rec.attribute_category :=
657 l_ExistingRev_rec.attribute_category;
658 End if;
659
660 If l_RtgRevision_rec.attribute1 = Fnd_Api.G_Miss_Char then
661 l_RtgRevision_rec.attribute1 := l_ExistingRev_rec.attribute1;
662 End if;
663
664 If l_RtgRevision_rec.attribute2 = Fnd_Api.G_Miss_Char then
665 l_RtgRevision_rec.attribute2 := l_ExistingRev_rec.attribute2;
666 End if;
667
668 If l_RtgRevision_rec.attribute3 = Fnd_Api.G_Miss_Char then
669 l_RtgRevision_rec.attribute3 := l_ExistingRev_rec.attribute3;
670 End if;
671
672 If l_RtgRevision_rec.attribute4 = Fnd_Api.G_Miss_Char then
673 l_RtgRevision_rec.attribute4 := l_ExistingRev_rec.attribute4;
674 End if;
675
676 If l_RtgRevision_rec.attribute5 = Fnd_Api.G_Miss_Char then
677 l_RtgRevision_rec.attribute5 := l_ExistingRev_rec.attribute5;
678 End if;
679
680 If l_RtgRevision_rec.attribute6 = Fnd_Api.G_Miss_Char then
681 l_RtgRevision_rec.attribute6 := l_ExistingRev_rec.attribute6;
682 End if;
683
684 If l_RtgRevision_rec.attribute7 = Fnd_Api.G_Miss_Char then
685 l_RtgRevision_rec.attribute7 := l_ExistingRev_rec.attribute7;
686 End if;
687
688 If l_RtgRevision_rec.attribute8 = Fnd_Api.G_Miss_Char then
689 l_RtgRevision_rec.attribute8 := l_ExistingRev_rec.attribute8;
690 End if;
691
692 If l_RtgRevision_rec.attribute9 = Fnd_Api.G_Miss_Char then
693 l_RtgRevision_rec.attribute9 := l_ExistingRev_rec.attribute9;
694 End if;
695
696 If l_RtgRevision_rec.attribute10 = Fnd_Api.G_Miss_Char then
697 l_RtgRevision_rec.attribute10 := l_ExistingRev_rec.attribute10;
698 End if;
699
700 If l_RtgRevision_rec.attribute11 = Fnd_Api.G_Miss_Char then
701 l_RtgRevision_rec.attribute11 := l_ExistingRev_rec.attribute11;
702 End if;
703
704 If l_RtgRevision_rec.attribute12 = Fnd_Api.G_Miss_Char then
705 l_RtgRevision_rec.attribute12 := l_ExistingRev_rec.attribute12;
706 End if;
707
708 If l_RtgRevision_rec.attribute13 = Fnd_Api.G_Miss_Char then
709 l_RtgRevision_rec.attribute13 := l_ExistingRev_rec.attribute13;
710 End if;
711
712 If l_RtgRevision_rec.attribute14 = Fnd_Api.G_Miss_Char then
713 l_RtgRevision_rec.attribute14 := l_ExistingRev_rec.attribute14;
714 End if;
715
716 If l_RtgRevision_rec.attribute15 = Fnd_Api.G_Miss_Char then
717 l_RtgRevision_rec.attribute15 := l_ExistingRev_rec.attribute15;
718 End if;
719 End loop; -- get old values
720 If not l_RowsFound then
721 Fnd_Message.Set_Name('BOM', 'BOM_SQL_ERR');
722 Fnd_Message.Set_Token('ENTITY', sqlerrm(100));
723 FND_MSG_PUB.Add;
724 Raise FND_API.G_EXC_ERROR;
725 End if;
726
727 If p_validation_level > FND_API.G_VALID_LEVEL_NONE then
728 ValidateRtgRevision(
729 p_api_version => 1,
730 p_init_msg_list => p_init_msg_list,
731 p_commit => p_commit,
732 p_validation_level => FND_API.G_VALID_LEVEL_NONE,
733 x_return_status => l_return_status,
734 x_msg_count => l_msg_count,
735 x_msg_data => l_msg_data,
736 p_RtgRevision_rec => l_RtgRevision_rec,
737 x_RtgRevision_rec => l_RtgRevision_rec
738 );
739 If l_return_status = FND_API.G_RET_STS_ERROR then
740 Raise FND_API.G_EXC_ERROR;
741 Elsif l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
742 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
743 End if; -- validation error
744 End If; -- validation
745
746 -- update routing revision
747
748 l_UserId := nvl(Fnd_Global.USER_ID, -1);
749 l_LoginId := Fnd_Global.LOGIN_ID;
750 l_RequestId := Fnd_Global.CONC_REQUEST_ID;
751 l_ProgramId := Fnd_Global.CONC_PROGRAM_ID;
752 l_ApplicationId := Fnd_Global.PROG_APPL_ID;
753 -- do not use decode because of implicit data type conversions
754 If l_RequestId is null then
755 l_ProgramUpdate := null;
756 Else
757 l_ProgramUpdate := sysdate;
758 End if;
759
760 update mtl_rtg_item_revisions set
761 last_update_date = sysdate,
762 last_updated_by = l_UserId,
763 creation_date = sysdate,
764 created_by = l_UserId,
765 last_update_login = l_LoginId,
766 change_notice = l_RtgRevision_rec.change_notice,
767 ecn_initiation_date = l_RtgRevision_rec.ecn_initiation_date,
768 implementation_date = l_RtgRevision_rec.implementation_date,
769 implemented_serial_number = l_RtgRevision_rec.implemented_serial_number,
770 effectivity_date = l_RtgRevision_rec.effectivity_date,
771 attribute_category = l_RtgRevision_rec.attribute_category,
772 attribute1 = l_RtgRevision_rec.attribute1,
773 attribute2 = l_RtgRevision_rec.attribute2,
774 attribute3 = l_RtgRevision_rec.attribute3,
775 attribute4 = l_RtgRevision_rec.attribute4,
776 attribute5 = l_RtgRevision_rec.attribute5,
777 attribute6 = l_RtgRevision_rec.attribute6,
778 attribute7 = l_RtgRevision_rec.attribute7,
779 attribute8 = l_RtgRevision_rec.attribute8,
780 attribute9 = l_RtgRevision_rec.attribute9,
781 attribute10 = l_RtgRevision_rec.attribute10,
782 attribute11 = l_RtgRevision_rec.attribute11,
783 attribute12 = l_RtgRevision_rec.attribute12,
784 attribute13 = l_RtgRevision_rec.attribute13,
785 attribute14 = l_RtgRevision_rec.attribute14,
786 attribute15 = l_RtgRevision_rec.attribute15,
787 request_id = l_RequestId,
788 program_application_id = l_ApplicationId,
789 program_id = l_ProgramId,
790 program_update_date = l_ProgramUpdate
791 where inventory_item_id = l_RtgRevision_rec.inventory_item_id
792 and organization_id = l_RtgRevision_rec.organization_id
793 and process_revision = l_RtgRevision_rec.process_revision;
794
795
796 x_RtgRevision_rec := l_RtgRevision_rec ;
797 -- End of API body.
798
799 -- Standard check of p_commit.
800 IF FND_API.To_Boolean(p_commit) THEN
801 COMMIT WORK;
802 END IF;
803 -- Standard call to get message count and if count is 1, get message info.
804 FND_MSG_PUB.Count_And_Get(
805 p_count => x_msg_count,
806 p_data => x_msg_data
807 );
808 EXCEPTION
809 WHEN FND_API.G_EXC_ERROR THEN
810 ROLLBACK TO UpdateRtgRevision_Pvt;
811 x_return_status := FND_API.G_RET_STS_ERROR;
812 FND_MSG_PUB.Count_And_Get(
813 p_count => x_msg_count,
814 p_data => x_msg_data
815 );
816 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
817 ROLLBACK TO UpdateRtgRevision_Pvt;
818 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
819 FND_MSG_PUB.Count_And_Get(
820 p_count => x_msg_count,
821 p_data => x_msg_data
822 );
823 WHEN OTHERS THEN
824 ROLLBACK TO UpdateRtgRevision_Pvt;
825 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
826 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
827 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
828 END IF;
829 FND_MSG_PUB.Count_And_Get(
830 p_count => x_msg_count,
831 p_data => x_msg_data
832 );
833 END UpdateRtgRevision;
834 PROCEDURE DeleteRtgRevision(
835 p_api_version IN NUMBER,
836 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
837 p_commit IN VARCHAR2 := FND_API.G_FALSE,
838 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
839 x_return_status OUT VARCHAR2,
840 x_msg_count OUT NUMBER,
841 x_msg_data OUT VARCHAR2,
842 p_RtgRevision_rec IN RTG_REVISION_REC_TYPE :=
843 G_MISS_RTG_REVISION_REC,
844 x_RtgRevision_rec OUT RTG_REVISION_REC_TYPE
845 ) IS
846 l_api_name CONSTANT VARCHAR2(30) := 'DeleteRtgRevision';
847 l_api_version CONSTANT NUMBER := 1.0;
848 l_RtgRevision_rec RTG_REVISION_REC_TYPE;
849 l_return_status VARCHAR2(1);
850 l_msg_count NUMBER;
851 l_msg_data VARCHAR2(2000);
852 cursor l_date_csr(P_ItemId number, P_OrgId number,
853 P_Rev varchar2) is
854 Select 1 dummy
855 From mtl_rtg_item_revisions mrir
856 Where mrir.inventory_item_id = P_ItemId
857 And mrir.organization_id = P_OrgId
858 And mrir.process_revision = P_Rev
859 And mrir.effectivity_date < sysdate;
860 BEGIN
861 -- Standard Start of API savepoint
862 SAVEPOINT DeleteRtgRevision_Pvt;
863 -- Standard call to check for call compatibility.
864 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
865 G_PKG_NAME) THEN
866 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
867 END IF;
868 -- Initialize message list if p_init_msg_list is set to TRUE.
869 IF FND_API.to_Boolean(p_init_msg_list) THEN
870 FND_MSG_PUB.initialize;
871 END IF;
872 -- Initialize API return status to success
873 x_return_status := FND_API.G_RET_STS_SUCCESS;
874
875 -- API body
876 l_RtgRevision_rec := p_RtgRevision_rec;
877
878 If p_validation_level = FND_API.G_VALID_LEVEL_FULL then
879 AssignRtgRevision(
880 p_api_version => 1,
881 p_init_msg_list => p_init_msg_list,
882 p_commit => p_commit,
883 p_validation_level => p_validation_level,
884 x_return_status => l_return_status,
885 x_msg_count => l_msg_count,
886 x_msg_data => l_msg_data,
887 p_RtgRevision_rec => l_RtgRevision_rec,
888 x_RtgRevision_rec => l_RtgRevision_rec
889 );
890 If l_return_status = FND_API.G_RET_STS_ERROR then
891 Raise FND_API.G_EXC_ERROR;
892 Elsif l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
893 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
894 End if; -- assign error
895 End If; -- assign
896
897 For l_date_rec in l_date_csr(
898 P_ItemId => l_RtgRevision_rec.inventory_item_id,
899 P_OrgId => l_RtgRevision_rec.organization_id,
900 P_Rev => l_RtgRevision_rec.process_revision) loop
901 Fnd_Message.Set_Name('BOM', 'BOM_CANNOT_DELETE_REVISION');
902 FND_MSG_PUB.Add;
903 Raise FND_API.G_EXC_ERROR;
904 End loop;
905
906 delete from mtl_rtg_item_revisions
907 where inventory_item_id = l_RtgRevision_rec.inventory_item_id
908 and organization_id = l_RtgRevision_rec.organization_id
909 and process_revision = l_RtgRevision_rec.process_revision;
910 If sql%NotFound then
911 Fnd_Message.Set_Name('BOM', 'BOM_SQL_ERR');
912 Fnd_Message.Set_Token('ENTITY', sqlerrm(100));
913 FND_MSG_PUB.Add;
914 Raise FND_API.G_EXC_ERROR;
915 End if;
916
917 x_RtgRevision_rec := l_RtgRevision_rec;
918 -- End of API body.
919
920 -- Standard check of p_commit.
921 IF FND_API.To_Boolean(p_commit) THEN
922 COMMIT WORK;
923 END IF;
924 -- Standard call to get message count and if count is 1, get message info.
925 FND_MSG_PUB.Count_And_Get(
926 p_count => x_msg_count,
927 p_data => x_msg_data
928 );
929 EXCEPTION
930 WHEN FND_API.G_EXC_ERROR THEN
931 ROLLBACK TO DeleteRtgRevision_Pvt;
932 x_return_status := FND_API.G_RET_STS_ERROR;
933 FND_MSG_PUB.Count_And_Get(
934 p_count => x_msg_count,
935 p_data => x_msg_data
936 );
937 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
938 ROLLBACK TO DeleteRtgRevision_Pvt;
939 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
940 FND_MSG_PUB.Count_And_Get(
941 p_count => x_msg_count,
942 p_data => x_msg_data
943 );
944 WHEN OTHERS THEN
945 ROLLBACK TO DeleteRtgRevision_Pvt;
946 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
947 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
948 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
949 END IF;
950 FND_MSG_PUB.Count_And_Get(
951 p_count => x_msg_count,
952 p_data => x_msg_data
953 );
954 END DeleteRtgRevision;
955 END Bom_RoutingRevision_Pvt;