[Home] [Help]
PACKAGE BODY: APPS.BOM_OPERATION_PVT
Source
1 PACKAGE BODY BOM_Operation_Pvt AS
2 -- $Header: BOMVOPRB.pls 120.2 2005/06/21 03:47:38 appldev ship $
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):='BOM_Operation_Pvt';
5 g_event constant number := 1;
6 g_process constant number := 2;
7 g_LineOp constant number := 3;
8 g_yes constant number := 1;
9 g_no constant number := 2;
10
11 PROCEDURE AssignOperation(
12 p_api_version IN NUMBER,
13 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
14 p_commit IN VARCHAR2 := FND_API.G_FALSE,
15 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
16 x_return_status IN OUT NOCOPY VARCHAR2,
17 x_msg_count IN OUT NOCOPY NUMBER,
18 x_msg_data IN OUT NOCOPY VARCHAR2,
19 p_operation_rec IN OPERATION_REC_TYPE := G_MISS_OPERATION_REC,
20 x_operation_rec IN OUT NOCOPY OPERATION_REC_TYPE
21 ) IS
22 l_api_name CONSTANT VARCHAR2(30) := 'AssignOperation';
23 l_api_version CONSTANT NUMBER := 1.0;
24 l_operation_rec OPERATION_REC_TYPE;
25 l_ret_code NUMBER;
26 l_err_text varchar2(2000);
27 g_assy_item_type number;
28 cursor l_operation_csr (P_OpSeqId number) is
29 Select bos.routing_sequence_id,
30 bos.operation_type,
31 bos.operation_seq_num
32 From bom_operation_sequences bos
33 Where operation_sequence_id = P_OpSeqId;
34 cursor l_assy_csr(P_RtgSeqId number) is
35 Select assembly_item_id,
36 organization_id,
37 alternate_routing_designator
38 From bom_operational_routings
39 Where routing_sequence_id = P_RtgSeqId;
40 cursor l_parameter_csr(P_Code varchar2) is
41 Select organization_id
42 From mtl_parameters
43 Where organization_code = P_Code;
44 cursor l_department_csr(P_code varchar2, P_OrgId number) is
45 select department_id
46 from bom_departments
47 where organization_id = P_OrgId
48 and department_code = P_Code;
49 cursor l_routing_csr(P_AssyItemId number, P_OrgId number,
50 P_Alternate varchar2) is
51 select routing_sequence_id
52 from bom_operational_routings
53 where organization_id = P_OrgId
54 and assembly_item_id = P_AssyItemId
55 and nvl(alternate_routing_designator, 'Primary Alternate') =
56 nvl(P_Alternate, 'Primary Alternate');
57
58 cursor l_StdOp_csr(P_Code varchar2, P_OpType number,
59 P_RtgSeqId number) is
60 select bso.standard_operation_id
61 from bom_standard_operations bso,
62 bom_operational_routings bor
63 where bso.organization_id = bor.organization_id
64 and nvl(bso.line_id, -1) = nvl(bor.line_id, -1)
65 and nvl(bso.operation_type, g_event) =
66 nvl(P_OpType, g_event)
67 and bso.operation_code = P_Code
68 and bor.routing_sequence_id = P_RtgSeqId;
69 BEGIN
70 -- Standard call to check for call compatibility.
71 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
72 G_PKG_NAME) THEN
73 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
74 END IF;
75 -- Initialize message list if p_init_msg_list is set to TRUE.
76 IF FND_API.to_Boolean(p_init_msg_list) THEN
77 FND_MSG_PUB.initialize;
78 END IF;
79 -- Initialize API return status to success
80 x_return_status := FND_API.G_RET_STS_SUCCESS;
81
82 -- API body
83 l_operation_rec := p_operation_rec;
84
85 If nvl(l_operation_rec.operation_sequence_id, FND_API.G_MISS_NUM) <>
86 FND_API.G_MISS_NUM then
87 For l_ExistingOp_rec in l_operation_csr(
88 P_OpSeqId => l_operation_rec.operation_sequence_id) loop
89 l_operation_rec.routing_sequence_id := l_ExistingOp_rec.routing_sequence_id;
90 l_operation_rec.operation_type := l_ExistingOp_rec.operation_type;
91 l_operation_rec.operation_seq_num := l_ExistingOp_rec.operation_seq_num;
92 End loop;
93 End if;
94
95 If nvl(l_operation_rec.routing_sequence_id, FND_API.G_MISS_NUM) <>
96 FND_API.G_MISS_NUM then
97 For l_assy_rec in l_assy_csr(
98 P_RtgSeqId => l_operation_rec.routing_sequence_id) loop
99 l_operation_rec.assembly_item_id := l_assy_rec.assembly_item_id;
100 l_operation_rec.organization_id := l_assy_rec.organization_id;
101 l_operation_rec.alternate_routing_designator :=
102 l_assy_rec.alternate_routing_designator;
103 End loop;
104 End if; -- check existing routing
105
106 -- set organization id
107
108 If nvl(l_operation_rec.organization_code, FND_API.G_MISS_CHAR) <>
109 FND_API.G_MISS_CHAR then
110 For l_parameter_rec in l_parameter_csr(
111 P_Code => l_operation_rec.organization_code) loop
112 l_operation_rec.organization_id := l_parameter_rec.organization_id;
113 End loop;
114 End if; -- organization code
115
116 if nvl(l_operation_rec.organization_id, FND_API.G_MISS_NUM) =
117 FND_API.G_MISS_NUM then
118 Fnd_Message.Set_Name('BOM', 'BOM_ORG_ID_MISSING');
119 FND_MSG_PUB.Add;
120 raise FND_API.G_EXC_ERROR;
121 end if; -- organization_id
122
123 If nvl(l_operation_rec.Assembly_Item_Number, FND_API.G_MISS_CHAR) <>
124 FND_API.G_MISS_CHAR then
125 l_ret_code := INVPUOPI.mtl_pr_trans_prod_item(
126 org_id => l_operation_rec.organization_id,
127 item_number_in => l_operation_rec.assembly_item_number,
128 item_id_out => l_operation_rec.assembly_item_id,
129 err_text => l_err_text);
130 if l_ret_code <> 0 then
131 Fnd_Message.Set_Name('BOM', 'BOM_ASSY_ITEM_MISSING');
132 FND_MSG_PUB.Add;
133 raise FND_API.G_EXC_ERROR;
134 end if; -- parse failed
135 end if; -- assembly item number
136
137 -- set department id
138
139 If nvl(l_operation_rec.department_code, Fnd_Api.G_Miss_Char) <>
140 Fnd_Api.G_Miss_Char then
141 l_operation_rec.department_id := null;
142 For l_department_rec in l_department_csr(
143 P_code => l_operation_rec.department_code,
144 P_OrgId => l_operation_rec.organization_id) loop
145 l_operation_rec.department_id := l_department_rec.department_id;
146 End loop;
147 If l_operation_rec.department_id is null then
148 Fnd_Message.Set_Name('BOM', 'BOM_DEPT_CODE_INVALID');
149 FND_MSG_PUB.Add;
150 raise FND_API.G_EXC_ERROR;
151 End if; -- invalid dept code
152 End if; -- dept code
153
154 -- Get the Assembly type from Item Id/Org ID and set option dependent flag.
155 SELECT bom_item_type
156 INTO g_assy_item_type
157 FROM MTL_SYSTEM_ITEMS
158 WHERE organization_id = l_operation_rec.organization_id
159 AND inventory_item_id = l_operation_rec.Assembly_Item_Id ;
160
161 If (nvl(l_operation_rec.option_dependent_flag,Fnd_Api.G_Miss_Num) <> Fnd_Api.G_Miss_Num) then
162 If (g_assy_item_type not in (1,2) and l_operation_rec.option_dependent_flag = 1 ) then
163 Fnd_Message.Set_Name('BOM','BOM_OP_DPTFLAG_MUST_BE_NO');
164 FND_MSG_PUB.Add;
165 Raise FND_API.G_EXC_ERROR;
166 End If;
167 Else
168 If g_assy_item_type in ( 1,2 ) then
169 l_operation_rec.option_dependent_flag := 1;
170 Else
171 l_operation_rec.option_dependent_flag := 2;
172 End If;
173 End If;
174
175 -- null routing sequence id
176 If nvl(l_operation_rec.routing_sequence_id, FND_API.G_MISS_NUM) =
177 FND_API.G_MISS_NUM then
178 If l_operation_rec.alternate_routing_designator = Fnd_Api.G_Miss_Char then
179 l_operation_rec.alternate_routing_designator := null;
180 End if;
181 For l_routing_rec in l_routing_csr(
182 P_AssyItemId => l_operation_rec.assembly_item_id,
183 P_OrgId => l_operation_rec.organization_id,
184 P_Alternate => l_operation_rec.alternate_routing_designator) loop
185 l_operation_rec.routing_sequence_id :=
186 l_routing_rec.routing_sequence_id;
187 End loop;
188 If nvl(l_operation_rec.routing_sequence_id, FND_API.G_MISS_NUM) =
189 FND_API.G_MISS_NUM then
190 Fnd_Message.Set_Name('BOM', 'BOM_RTG_SEQ_INVALID');
191 FND_MSG_PUB.Add;
192 Raise FND_API.G_EXC_ERROR;
193 End if;
194 End if; -- get routing sequence id
195
196 If l_operation_rec.operation_type = Fnd_Api.G_MISS_NUM then
197 l_operation_rec.operation_type := g_event;
198 End if;
199
200 If nvl(l_operation_rec.operation_code, Fnd_Api.G_Miss_Char) <>
201 Fnd_Api.G_Miss_Char then
202 l_operation_rec.standard_operation_id := null;
203 For l_StdOp_rec in l_StdOp_csr(
204 P_Code => l_operation_rec.operation_code,
205 P_OpType => l_operation_rec.operation_type,
206 P_RtgSeqId => l_operation_rec.routing_sequence_id) loop
207 l_operation_rec.standard_operation_id :=
208 l_StdOp_rec.standard_operation_id;
209 End loop; -- get standard operation id
210 If l_operation_rec.standard_operation_id is null then
211 Fnd_Message.Set_Name('BOM', 'BOM_STD_OP_CODE_INVALID');
212 FND_MSG_PUB.Add;
213 Raise FND_API.G_EXC_ERROR;
214 End if; -- invalid op code
215 End if; -- std op code
216
217 x_operation_rec := l_operation_rec;
218 -- End of API body.
219
220 -- Standard call to get message count and if count is 1, get message info.
221 FND_MSG_PUB.Count_And_Get(
222 p_count => x_msg_count,
223 p_data => x_msg_data
224 );
225 EXCEPTION
226 WHEN FND_API.G_EXC_ERROR THEN
227 x_return_status := FND_API.G_RET_STS_ERROR;
228 FND_MSG_PUB.Count_And_Get(
229 p_count => x_msg_count,
230 p_data => x_msg_data
231 );
232 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
233 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
234 FND_MSG_PUB.Count_And_Get(
235 p_count => x_msg_count,
236 p_data => x_msg_data
237 );
238 WHEN OTHERS THEN
239 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
240 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
241 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
242 END IF;
243 FND_MSG_PUB.Count_And_Get(
244 p_count => x_msg_count,
245 p_data => x_msg_data
246 );
247 END AssignOperation;
248
249 PROCEDURE ValidateOperation(
250 p_api_version IN NUMBER,
251 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
252 p_commit IN VARCHAR2 := FND_API.G_FALSE,
253 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
254 x_return_status IN OUT NOCOPY VARCHAR2,
255 x_msg_count IN OUT NOCOPY NUMBER,
256 x_msg_data IN OUT NOCOPY VARCHAR2,
257 p_operation_rec IN OPERATION_REC_TYPE := G_MISS_OPERATION_REC,
258 x_operation_rec IN OUT NOCOPY OPERATION_REC_TYPE
259 ) is
260 l_api_name CONSTANT VARCHAR2(30) := 'ValidateOperation';
261 l_api_version CONSTANT NUMBER := 1.0;
262 l_operation_rec OPERATION_REC_TYPE;
263 l_return_status VARCHAR(1);
264 l_msg_count NUMBER;
265 l_msg_data VARCHAR(2000);
266 cursor l_Duplicate_csr (P_OpSeqId number, P_RtgSeqId number,
267 P_OpSeqNum number, P_OpType number, P_EffDate date) is
268 Select 'x' dummy
269 From dual
270 Where exists(
271 Select null
272 From bom_operation_sequences bos
273 where bos.routing_sequence_id = P_RtgSeqId
274 and bos.effectivity_date = P_EffDate
275 and bos.operation_seq_num = P_OpSeqNum
276 and nvl(bos.operation_type, g_event) =
277 nvl(P_OpType, g_event)
278 and bos.operation_sequence_id <> P_OpSeqId);
279 cursor l_routing_csr (P_RtgSeqId number) is
280 Select 'x' dummy
281 From dual
282 Where not exists(
283 select null
284 from bom_operational_routings bor
285 where bor.routing_sequence_id = P_RtgSeqId);
286 cursor l_noncfm_csr (P_RtgSeqId number) is
287 Select 'x' dummy
288 From bom_operational_routings bor
289 Where bor.routing_sequence_id = P_RtgSeqId
290 And nvl(bor.cfm_routing_flag, 2) = 2;
291 cursor l_lbr_csr (P_RtgSeqId number) is
292 Select 'x' dummy
293 From bom_operational_routings bor
294 Where bor.routing_sequence_id = P_RtgSeqId
295 And nvl(bor.cfm_routing_flag,2) = 3;
296 cursor l_OtherOps_csr(P_RtgSeqId number, P_OpSeqId number,
297 P_OpSeqNum number, P_OpType number, P_EffDate date,
298 P_DisDate date) is
299 select 'x' dummy
300 From dual
301 Where exists (
302 select null
303 from bom_operation_sequences
304 where operation_sequence_id <> P_OpSeqId
305 and routing_sequence_id = P_RtgSeqId
306 and operation_seq_num = P_OpSeqNum
307 and nvl(operation_type, g_event) =
308 nvl(P_OpType, g_event)
309 and (effectivity_date < nvl(P_DisDate,
310 effectivity_date + 1) and
311 nvl(disable_date, P_EffDate + 1) >= P_EffDate
312 )
313 );
314 cursor l_common_csr(P_RtgSeqId number) is
315 select 'Is pointing to a common' dummy
316 from bom_operational_routings
317 where routing_sequence_id = P_RtgSeqId
318 and common_routing_sequence_id <>
319 routing_sequence_id;
320 l_PrevStdOp number := null;
321 l_PrevStdCode varchar2(4) := null;
322 l_PrevRefFlag number := null;
323 cursor l_OldOpCode_csr(P_OpSeqId number) is
324 Select bos.standard_operation_id,
325 bso.operation_code,
326 nvl(bos.reference_flag, 2) reference_flag
327 From bom_operation_sequences bos,
328 bom_standard_operations bso
329 Where operation_sequence_id = P_OpSeqId
330 And bos.standard_operation_id =
331 bso.standard_operation_id;
332 cursor l_OpResources_csr(P_OpSeqId number) is
333 Select 'x' dummy
334 From dual
335 Where exists(
336 Select null
337 From bom_operation_resources
338 Where operation_sequence_id = P_OpSeqId);
339 cursor l_attachments_csr(P_OpSeqId number) is
340 Select 'x' dummy
341 From dual
342 Where exists(
343 Select null
344 From fnd_attached_documents
345 Where pk1_value = to_char(P_OpSeqId)
346 and entity_name = 'BOM_OPERATION_SEQUENCES');
347 cursor l_StdOp_csr(P_OpType number, P_RtgSeqId number,
348 P_StdOpId number) is
349 select bso.DEPARTMENT_ID,
350 bso.MINIMUM_TRANSFER_QUANTITY,
351 bso.COUNT_POINT_TYPE,
352 bso.OPERATION_DESCRIPTION,
353 bso.BACKFLUSH_FLAG,
354 bso.OPTION_DEPENDENT_FLAG,
355 bso.ATTRIBUTE_CATEGORY,
356 bso.ATTRIBUTE1,
357 bso.ATTRIBUTE2,
358 bso.ATTRIBUTE3,
359 bso.ATTRIBUTE4,
360 bso.ATTRIBUTE5,
361 bso.ATTRIBUTE6,
362 bso.ATTRIBUTE7,
363 bso.ATTRIBUTE8,
364 bso.ATTRIBUTE9,
365 bso.ATTRIBUTE10,
366 bso.ATTRIBUTE11,
367 bso.ATTRIBUTE12,
368 bso.ATTRIBUTE13,
369 bso.ATTRIBUTE14,
370 bso.ATTRIBUTE15,
371 bso.OPERATION_YIELD_ENABLED
372 from bom_standard_operations bso,
373 bom_operational_routings bor
374 where bso.standard_operation_id = P_StdOpId
375 and bor.routing_sequence_id = P_RtgSeqId
376 and bso.organization_id = bor.organization_id
377 and nvl(bso.line_id, -1) = nvl(bor.line_id, -1)
378 and nvl(bso.operation_type, g_event) =
379 nvl(P_OpType, g_event);
380 l_StdOpFound boolean := false;
381 l_UserId number;
382 l_LoginId number;
383 l_RequestId number;
384 l_ProgramId number;
385 l_ApplicationId number;
386 l_ProgramUpdate date;
387 cursor l_dept_csr(P_RtgSeqId number, P_DeptId number,
388 P_EffDate date) is
389 select 'x' dummy
390 from dual
391 where not exists(
392 Select null
396 and bor.routing_sequence_id = P_RtgSeqId
393 from bom_departments bd,
394 bom_operational_routings bor
395 where bd.organization_id = bor.organization_id
397 and bd.department_id = P_DeptId
398 and nvl(bd.disable_date, P_EffDate+1) > P_EffDate
399 );
400 cursor l_parents_csr(P_ParentSeqId number) is
401 Select 'x' dummy
402 from bom_operation_resources
403 where operation_sequence_id = P_ParentSeqId;
404 BEGIN
405 -- Standard Start of API savepoint
406 SAVEPOINT ValidateOperation_Pvt;
407 -- Standard call to check for call compatibility.
408 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
409 G_PKG_NAME) THEN
410 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
411 END IF;
412 -- Initialize message list if p_init_msg_list is set to TRUE.
413 IF FND_API.to_Boolean(p_init_msg_list) THEN
414 FND_MSG_PUB.initialize;
415 END IF;
416 -- Initialize API return status to success
417 x_return_status := FND_API.G_RET_STS_SUCCESS;
418
419 -- API body
420 l_operation_rec := p_operation_rec;
421 If p_validation_level = FND_API.G_VALID_LEVEL_FULL then
422 AssignOperation(
423 p_api_version => 1,
424 p_init_msg_list => p_init_msg_list,
425 p_commit => p_commit,
426 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
427 x_return_status => l_return_status,
428 x_msg_count => l_msg_count,
429 x_msg_data => l_msg_data,
430 p_operation_rec => l_operation_rec,
431 x_operation_rec => l_operation_rec);
432 If l_return_status = FND_API.G_RET_STS_ERROR then
433 Raise FND_API.G_EXC_ERROR;
434 Elsif l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
435 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
436 End if; -- error
437 End if; -- assign values
438
439 -- verify operation seq num is not null
440 If l_operation_rec .operation_seq_num is null then
441 Fnd_Message.Set_Name('BOM', 'BOM_OP_SEQ_NUM_MISSING');
442 FND_MSG_PUB.Add;
443 Raise FND_API.G_EXC_ERROR;
444 End if;
445
446 -- check effective date entered
447 If l_operation_rec.new_effectivity_date is null then
448 Fnd_Message.Set_Name('BOM', 'BOM_EFF_DATE_MISSING');
449 FND_MSG_PUB.Add;
450 Raise FND_API.G_EXC_ERROR;
451 End if;
452
453 -- verify uniqueness of operation
454 For l_duplicate_rec in l_Duplicate_csr(
455 P_OpSeqId => l_operation_rec.operation_sequence_id,
456 P_RtgSeqId => l_operation_rec.routing_sequence_id,
457 P_OpSeqNum => l_operation_rec.new_operation_seq_num,
458 P_OpType => l_operation_rec.operation_type,
459 P_EffDate => l_operation_rec.new_effectivity_date) loop
460 Fnd_Message.Set_Name('BOM', 'BOM_OPERATION_DUPLICATE');
461 FND_MSG_PUB.Add;
462 Raise FND_API.G_EXC_ERROR;
463 End loop;
464
465 -- check for existence of routing
466
467 For l_routing_rec in l_routing_csr(
468 P_RtgSeqId => l_operation_rec.routing_sequence_id) loop
469 Fnd_Message.Set_Name('BOM', 'BOM_RTG_SEQ_INVALID');
470 FND_MSG_PUB.Add;
471 Raise FND_API.G_EXC_ERROR;
472 End loop; -- routing existence
473
474 -- make sure there is no overlapping operations
475
476 For l_OtherOps_rec in l_OtherOps_csr(
477 P_RtgSeqId => l_operation_rec.routing_sequence_id,
478 P_OpSeqId => l_operation_rec.operation_sequence_id,
479 P_OpSeqNum => l_operation_rec.new_operation_seq_num,
480 P_OpType => l_operation_rec.operation_type,
481 P_EffDate => l_operation_rec.new_effectivity_date,
482 P_DisDate => l_operation_rec.disable_date) loop
483 Fnd_Message.Set_Name('BOM', 'BOM_IMPL_OP_OVERLAP');
484 FND_MSG_PUB.Add;
485 Raise FND_API.G_EXC_ERROR;
486 End loop;
487
488 -- verify that the routing does not have a common. If so, it cannot have
489 -- operations
490 For l_common_rec in l_common_csr(
491 P_RtgSeqId => l_operation_rec.routing_sequence_id) loop
492 Fnd_Message.Set_Name('BOM', 'BOM_COMMON_OP');
493 FND_MSG_PUB.Add;
494 Raise FND_API.G_EXC_ERROR;
495 End loop;
496
497 -- Op Code is mandatory for Processes and Line Operations
498 If l_operation_rec.operation_type in (g_process, g_LineOp) and
499 l_operation_rec.standard_operation_id is null then
500 Fnd_Message.Set_Name('BOM', 'BOM_STD_OP_REQUIRED');
501 FND_MSG_PUB.Add;
502 Raise FND_API.G_EXC_ERROR;
503 End if; -- mandatory op code
504
505 -- can not reference null op code
506 If l_operation_rec.standard_operation_id is null then
507 l_operation_rec.reference_flag := g_no;
508 End if; -- null op code
509
510 -- Copy and reference logic
511
512 -- get previous standard operation
513 For l_OldOp_rec in l_OldOpCode_csr(
514 P_OpSeqId => l_operation_rec.operation_sequence_id) loop
515 l_PrevStdOp := l_OldOp_rec.standard_operation_id;
516 l_PrevRefFlag := l_OldOp_rec.reference_flag;
517 l_PrevStdCode := l_OldOp_rec.operation_code;
518 End loop;
519
520 -- Cannot set copied operation to referenced
521 If l_PrevRefFlag = g_no and l_operation_rec.reference_flag = g_yes then
522 Fnd_Message.Set_Name('BOM', 'BOM_COPY_REF_OPERATION');
526 End if;
523 Fnd_Message.Set_Token('OPERATION', l_PrevStdCode);
524 FND_MSG_PUB.Add;
525 Raise FND_API.G_EXC_ERROR;
527
528 -- Standard Operation has changed to not null value
529 If nvl(l_PrevStdOp, -1) <> l_operation_rec.standard_operation_id then
530 -- check resources
531 For l_resource_rec in l_OpResources_csr(
532 P_OpSeqId => l_operation_rec.operation_sequence_id) loop
533 Fnd_Message.Set_Name('BOM', 'BOM_CANNOT_COPY_STD_OP');
534 FND_MSG_PUB.Add;
535 Raise FND_API.G_EXC_ERROR;
536 End loop; -- resources exist, cannot copy
537 l_StdOpFound := false;
538 For l_StdOp_rec in l_StdOp_csr(
539 P_OpType => l_operation_rec.operation_type,
540 P_RtgSeqId => l_operation_rec.routing_sequence_id,
541 P_StdOpId => l_operation_rec.standard_operation_id) loop
542 l_StdOpFound := true;
543 l_operation_rec.department_id := nvl(l_operation_rec.department_id,l_StdOp_rec.department_id);
544 l_operation_rec.minimum_transfer_quantity :=nvl(l_operation_rec.minimum_transfer_quantity,l_StdOp_rec.minimum_transfer_quantity);
545 l_operation_rec.count_point_type := nvl(l_operation_rec.count_point_type,l_StdOp_rec.count_point_type);
546 l_operation_rec.operation_description := nvl(l_operation_rec.operation_description,l_StdOp_rec.operation_description);
547 l_operation_rec.option_dependent_flag := nvl(l_operation_rec.option_dependent_flag,l_StdOp_rec.option_dependent_flag);
548 l_operation_rec.attribute_category := nvl(l_operation_rec.attribute_category,l_StdOp_rec.attribute_category);
549 l_operation_rec.attribute1 := nvl(l_operation_rec.attribute1,l_StdOp_rec.attribute1);
550 l_operation_rec.attribute2 := nvl(l_operation_rec.attribute2,l_StdOp_rec.attribute2);
551 l_operation_rec.attribute3 := nvl(l_operation_rec.attribute3,l_StdOp_rec.attribute3);
552 l_operation_rec.attribute4 := nvl(l_operation_rec.attribute4,l_StdOp_rec.attribute4);
553 l_operation_rec.attribute5 := nvl(l_operation_rec.attribute5,l_StdOp_rec.attribute5);
554 l_operation_rec.attribute6 := nvl(l_operation_rec.attribute6,l_StdOp_rec.attribute6);
555 l_operation_rec.attribute7 := nvl(l_operation_rec.attribute7,l_StdOp_rec.attribute7);
556 l_operation_rec.attribute8 := nvl(l_operation_rec.attribute8,l_StdOp_rec.attribute8);
557 l_operation_rec.attribute9 := nvl(l_operation_rec.attribute9,l_StdOp_rec.attribute9);
558 l_operation_rec.attribute10 := nvl(l_operation_rec.attribute10,l_StdOp_rec.attribute10);
559 l_operation_rec.attribute11 := nvl(l_operation_rec.attribute11,l_StdOp_rec.attribute11);
560 l_operation_rec.attribute12 := nvl(l_operation_rec.attribute12,l_StdOp_rec.attribute12);
561 l_operation_rec.attribute13 := nvl(l_operation_rec.attribute13,l_StdOp_rec.attribute13);
562 l_operation_rec.attribute14 := nvl(l_operation_rec.attribute14,l_StdOp_rec.attribute14);
563 l_operation_rec.attribute15 := nvl(l_operation_rec.attribute15,l_StdOp_rec.attribute15);
564 l_operation_rec.backflush_flag := nvl(l_operation_rec.backflush_flag,l_StdOp_rec.backflush_flag);
565 l_operation_rec.operation_yield_enabled := nvl(l_operation_rec.operation_yield_enabled,l_StdOp_rec.operation_yield_enabled);
566 End loop; -- copy standard operation
567 If not l_StdOpFound then
568 Fnd_Message.Set_Name('BOM', 'BOM_STD_OP_ID_INVALID');
569 FND_MSG_PUB.Add;
570 Raise FND_API.G_EXC_ERROR;
571 End if; -- invalid op code
572 -- copy op resources
573 l_UserId := nvl(Fnd_Global.USER_ID, -1);
574 l_LoginId := Fnd_Global.LOGIN_ID;
575 l_RequestId := Fnd_Global.CONC_REQUEST_ID;
576 l_ProgramId := Fnd_Global.CONC_PROGRAM_ID;
577 l_ApplicationId := Fnd_Global.PROG_APPL_ID;
578 -- do not use decode because of implicit data type conversions
579 If l_RequestId is null then
580 l_ProgramUpdate := null;
581 Else
582 l_ProgramUpdate := sysdate;
583 End if;
584 Insert into bom_operation_resources(
585 operation_sequence_id,
586 resource_seq_num,
587 resource_id,
588 activity_id,
589 standard_rate_flag,
590 assigned_units,
591 usage_rate_or_amount,
592 usage_rate_or_amount_inverse,
593 basis_type,
594 schedule_flag,
595 last_update_date,
596 last_updated_by,
597 creation_date,
598 created_by,
599 last_update_login,
600 resource_offset_percent,
601 autocharge_type,
602 attribute_category,
603 attribute1,
604 attribute2,
605 attribute3,
606 attribute4,
607 attribute5,
608 attribute6,
609 attribute7,
610 attribute8,
611 attribute9,
612 attribute10,
613 attribute11,
614 attribute12,
615 attribute13,
616 attribute14,
617 attribute15,
618 request_id,
619 program_application_id,
620 program_id,
621 program_update_date)
622 Select
623 l_operation_rec.operation_sequence_id,
624 resource_seq_num,
625 resource_id,
626 activity_id,
627 standard_rate_flag,
628 assigned_units,
629 usage_rate_or_amount,
630 usage_rate_or_amount_inverse,
631 basis_type,
632 schedule_flag,
633 sysdate,
634 l_UserId,
635 sysdate,
636 l_UserId,
637 l_LoginId,
638 null,
642 attribute2,
639 autocharge_type,
640 attribute_category,
641 attribute1,
643 attribute3,
644 attribute4,
645 attribute5,
646 attribute6,
647 attribute7,
648 attribute8,
649 attribute9,
650 attribute10,
651 attribute11,
652 attribute12,
653 attribute13,
654 attribute14,
655 attribute15,
656 l_RequestId,
657 l_ApplicationId,
658 l_ProgramId,
659 l_ProgramUpdate
660 From bom_std_op_resources
661 Where standard_operation_id = l_operation_rec.standard_operation_id;
662 -- copy attachment
663 FND_ATTACHED_DOCUMENTS2_PKG.copy_attachments(
664 X_from_entity_name => 'BOM_STANDARD_OPERATIONS',
665 X_from_pk1_value => to_char(
666 l_operation_rec.standard_operation_id),
667 X_from_pk2_value => null,
668 X_from_pk3_value => null,
669 X_from_pk4_value => null,
670 X_from_pk5_value => null,
671 X_to_entity_name => 'BOM_OPERATION_SEQUENCES',
672 X_to_pk1_value => to_char(
673 l_operation_rec.operation_sequence_id),
674 X_to_pk2_value => null,
675 X_to_pk3_value => null,
676 X_to_pk4_value => null,
677 X_to_pk5_value => null,
678 X_created_by => l_UserId,
679 X_last_update_login => l_LoginId,
680 X_program_application_id => l_ApplicationId,
681 X_program_id => l_ProgramId,
682 X_request_id => l_RequestId
683 );
684 End if; -- copy
685
686 -- columns corresponding to columns in Standard Operations should not be
687 -- changed when referenced.
688 If l_operation_rec.reference_flag = g_yes then
689 For l_StdOp_rec in l_StdOp_csr(
690 P_OpType => l_operation_rec.operation_type,
691 P_RtgSeqId => l_operation_rec.routing_sequence_id,
692 P_StdOpId => l_operation_rec.standard_operation_id) loop
693 l_StdOpFound := true;
694 l_operation_rec.department_id := l_StdOp_rec.department_id;
695 l_operation_rec.minimum_transfer_quantity :=
696 l_StdOp_rec.minimum_transfer_quantity;
697 l_operation_rec.count_point_type := l_StdOp_rec.count_point_type;
698 l_operation_rec.operation_description :=
699 l_StdOp_rec.operation_description;
700 l_operation_rec.option_dependent_flag :=
701 nvl(l_operation_rec.option_dependent_flag,l_StdOp_rec.option_dependent_flag);
702 l_operation_rec.attribute_category := l_StdOp_rec.attribute_category;
703 l_operation_rec.attribute1 := l_StdOp_rec.attribute1;
704 l_operation_rec.attribute2 := l_StdOp_rec.attribute2;
705 l_operation_rec.attribute3 := l_StdOp_rec.attribute3;
706 l_operation_rec.attribute4 := l_StdOp_rec.attribute4;
707 l_operation_rec.attribute5 := l_StdOp_rec.attribute5;
708 l_operation_rec.attribute6 := l_StdOp_rec.attribute6;
709 l_operation_rec.attribute7 := l_StdOp_rec.attribute7;
710 l_operation_rec.attribute8 := l_StdOp_rec.attribute8;
711 l_operation_rec.attribute9 := l_StdOp_rec.attribute9;
712 l_operation_rec.attribute10 := l_StdOp_rec.attribute10;
713 l_operation_rec.attribute11 := l_StdOp_rec.attribute11;
714 l_operation_rec.attribute12 := l_StdOp_rec.attribute12;
715 l_operation_rec.attribute13 := l_StdOp_rec.attribute13;
716 l_operation_rec.attribute14 := l_StdOp_rec.attribute14;
717 l_operation_rec.attribute15 := l_StdOp_rec.attribute15;
718 l_operation_rec.backflush_flag := l_StdOp_rec.backflush_flag;
719 l_operation_rec.operation_yield_enabled := l_StdOp_rec.operation_yield_enabled;
720 End loop; -- copy standard operation
721 If not l_StdOpFound then
722 Fnd_Message.Set_Name('BOM', 'BOM_STD_OP_ID_INVALID');
723 FND_MSG_PUB.Add;
724 Raise FND_API.G_EXC_ERROR;
725 End if; -- invalid op code
726 End if; -- referenced
727
728 -- Validate Department
729 For l_dept_rec in l_dept_csr(
730 P_RtgSeqId => l_operation_rec.routing_sequence_id,
731 P_DeptId => l_operation_rec.department_id,
732 P_EffDate => l_operation_rec.new_effectivity_date) loop
733 Fnd_Message.Set_Name('BOM', 'BOM_DEPT_ID_INVALID');
734 FND_MSG_PUB.Add;
735 Raise FND_API.G_EXC_ERROR;
736 End loop; -- invalid department
737
738 -- validate operation details
739
740 if (l_operation_rec.minimum_transfer_quantity < 0)
741 or (l_operation_rec.new_effectivity_date > l_operation_rec.disable_date)
742 or (l_operation_rec.count_point_type not in (1,2,3))
743 or (l_operation_rec.backflush_flag not in (1,2))
744 or (l_operation_rec.option_dependent_flag not in (1,2))
745 -- BACKFLUSH_FLAG must be Yes if COUNT_POINT_TYPE is No-direct charge
746 or (l_operation_rec.count_point_type = 3 and
747 l_operation_rec.backflush_flag <> 1)
748 or (l_operation_rec.operation_lead_time_percent not between 0 and 100)
749 -- CFM attributes
750 or (l_operation_rec.net_planning_percent not between 0 and 100)
751 or (l_operation_rec.yield not between 0 and 1)
752 or (l_operation_rec.cumulative_yield not between 0 and 1)
756 then
753 or (l_operation_rec.reverse_cumulative_yield not between 0 and 1)
754 or (l_operation_rec.include_in_rollup not in (1,2))
755 or (l_operation_rec.operation_yield_enabled not in (1,2))
757 Fnd_Message.Set_Name('BOM', 'BOM_OPERATION_ERROR');
758 FND_MSG_PUB.Add;
759 Raise FND_API.G_EXC_ERROR;
760 End if; -- etc, etc, etc
761
762 -- CFM validation
763
764 For l_noncfm_rec in l_noncfm_csr (
765 P_RtgSeqId => l_operation_rec.routing_sequence_id) loop
766 l_operation_rec.process_op_seq_id := null;
767 l_operation_rec.line_op_seq_id := null;
768 l_operation_rec.yield := null;
769 l_operation_rec.cumulative_yield := null;
770 l_operation_rec.reverse_cumulative_yield := null;
771 l_operation_rec.labor_time_calc := null;
772 l_operation_rec.machine_time_calc := null;
773 l_operation_rec.total_time_calc := null;
774 l_operation_rec.labor_time_user := null;
775 l_operation_rec.machine_time_user := null;
776 l_operation_rec.total_time_user := null;
777 l_operation_rec.net_planning_percent := null;
778 /** Bug 2097667 Default value for these 2 fields should be
779 1 regardless of the routing type
780 l_operation_rec.include_in_rollup := null;
781 l_operation_rec.operation_yield_enabled := null;
782 **/
783 End loop;
784
785 For l_lbr_rec in l_lbr_csr (
786 P_RtgSeqId => l_operation_rec.routing_sequence_id) loop
787 l_operation_rec.process_op_seq_id := null;
788 l_operation_rec.line_op_seq_id := null;
789 l_operation_rec.labor_time_calc := null;
790 l_operation_rec.machine_time_calc := null;
791 l_operation_rec.total_time_calc := null;
792 l_operation_rec.labor_time_user := null;
793 l_operation_rec.machine_time_user := null;
794 l_operation_rec.total_time_user := null;
795 l_operation_rec.net_planning_percent := null;
796 End loop;
797
798 If nvl(l_operation_rec.operation_type, g_event) <> g_event and
799 (l_operation_rec.process_op_seq_id is not null or
800 l_operation_rec.line_op_seq_id is not null) then
801 Fnd_Message.Set_Name('BOM', 'BOM_PARENT_OP_NULL');
802 FND_MSG_PUB.Add;
803 Raise FND_API.G_EXC_ERROR;
804 End if; -- only events can have parents
805
806 For l_process_rec in l_parents_csr(
807 P_ParentSeqId => l_operation_rec.process_op_seq_id) loop
808 Fnd_Message.Set_Name('BOM', 'BOM_PARENT_OP_INVALID');
809 FND_MSG_PUB.Add;
810 Raise FND_API.G_EXC_ERROR;
811 End loop; -- invalid process
812
813 For l_LineOp_rec in l_parents_csr(
814 P_ParentSeqId => l_operation_rec.line_op_seq_id) loop
815 Fnd_Message.Set_Name('BOM', 'BOM_PARENT_OP_INVALID');
816 FND_MSG_PUB.Add;
817 Raise FND_API.G_EXC_ERROR;
818 End loop; -- invalid line operation
819
820 x_operation_rec := l_operation_rec;
821
822 -- End of API body.
823
824 -- Standard check of p_commit.
825 IF FND_API.To_Boolean(p_commit) THEN
826 COMMIT WORK;
827 END IF;
828
829 -- Standard call to get message count and if count is 1, get message info.
830 FND_MSG_PUB.Count_And_Get(
831 p_count => x_msg_count,
832 p_data => x_msg_data
833 );
834 EXCEPTION
835 WHEN FND_API.G_EXC_ERROR THEN
836 ROLLBACK TO ValidateOperation_Pvt;
837 x_return_status := FND_API.G_RET_STS_ERROR;
838 FND_MSG_PUB.Count_And_Get(
839 p_count => x_msg_count,
840 p_data => x_msg_data
841 );
842 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
843 ROLLBACK TO ValidateOperation_Pvt;
844 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
845 FND_MSG_PUB.Count_And_Get(
846 p_count => x_msg_count,
847 p_data => x_msg_data
848 );
849 WHEN OTHERS THEN
850 ROLLBACK TO ValidateOperation_Pvt;
851 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
852 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
853 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
854 END IF;
855 FND_MSG_PUB.Count_And_Get(
856 p_count => x_msg_count,
857 p_data => x_msg_data
858 );
859 End ValidateOperation;
860
861 PROCEDURE CreateOperation(
862 p_api_version IN NUMBER,
863 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
864 p_commit IN VARCHAR2 := FND_API.G_FALSE,
865 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
866 x_return_status IN OUT NOCOPY VARCHAR2,
867 x_msg_count IN OUT NOCOPY NUMBER,
868 x_msg_data IN OUT NOCOPY VARCHAR2,
869 p_operation_rec IN OPERATION_REC_TYPE := G_MISS_OPERATION_REC,
870 x_operation_rec IN OUT NOCOPY OPERATION_REC_TYPE
871 ) is
872 l_api_name CONSTANT VARCHAR2(30) := 'CreateOperation';
873 l_api_version CONSTANT NUMBER := 1.0;
874 l_operation_rec OPERATION_REC_TYPE;
875 l_return_status VARCHAR2(1);
876 l_msg_count NUMBER;
877 l_msg_data VARCHAR2(2000);
878 l_UserId number;
879 l_LoginId number;
880 l_RequestId number;
881 l_ProgramId number;
882 l_ApplicationId number;
883 l_ProgramUpdate date;
884 cursor l_NewOper_csr is
885 Select bom_operation_sequences_s.nextval new_op_seq_id
886 from dual;
887 BEGIN
888 -- Standard Start of API savepoint
892 G_PKG_NAME) THEN
889 SAVEPOINT CreateOperation_Pvt;
890 -- Standard call to check for call compatibility.
891 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
893 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
894 END IF;
895 -- Initialize message list if p_init_msg_list is set to TRUE.
896 IF FND_API.to_Boolean(p_init_msg_list) THEN
897 FND_MSG_PUB.initialize;
898 END IF;
899 -- Initialize API return status to success
900 x_return_status := FND_API.G_RET_STS_SUCCESS;
901
902 -- API body
903 l_operation_rec := p_operation_rec;
904
905 -- initialize record
906
907 For l_NewOper_rec in l_NewOper_csr loop
908 l_operation_rec.operation_sequence_id := l_NewOper_rec.new_op_seq_id;
909 End loop; -- new primary key
910
911 If l_operation_rec.routing_sequence_id = Fnd_Api.G_Miss_Num then
912 l_operation_rec.routing_sequence_id := null;
913 End if;
914
915 If l_operation_rec.operation_seq_num = Fnd_Api.G_Miss_Num then
916 l_operation_rec.operation_seq_num := null;
917 End if;
918
919 If nvl(l_operation_rec.new_operation_seq_num, Fnd_Api.G_Miss_Num) =
920 Fnd_Api.G_Miss_Num then
921 l_operation_rec.new_operation_seq_num := l_operation_rec.operation_seq_num;
922 End if;
923
924 If l_operation_rec.standard_operation_id = Fnd_Api.G_Miss_Num then
925 l_operation_rec.standard_operation_id := null;
926 End if;
927
928 If l_operation_rec.department_id = Fnd_Api.G_Miss_Num then
929 l_operation_rec.department_id := null;
930 End if;
931
932 If l_operation_rec.operation_lead_time_percent = Fnd_Api.G_Miss_Num then
933 l_operation_rec.operation_lead_time_percent := null;
934 End if;
935
936 If l_operation_rec.minimum_transfer_quantity = Fnd_Api.G_Miss_Num then
937 l_operation_rec.minimum_transfer_quantity := null;
938 End if;
939
940 If nvl(l_operation_rec.count_point_type, Fnd_Api.G_Miss_Num) =
941 Fnd_Api.G_Miss_Num then
942 l_operation_rec.count_point_type := 1;
943 End if;
944
945 If l_operation_rec.operation_description = Fnd_Api.G_Miss_Char then
946 l_operation_rec.operation_description := null;
947 End if;
948
949 If nvl(l_operation_rec.effectivity_date, Fnd_Api.G_Miss_Date) =
950 Fnd_Api.G_Miss_Date then
951 l_operation_rec.effectivity_date := trunc(sysdate);
952 End if;
953
954 If nvl(l_operation_rec.new_effectivity_date, Fnd_Api.G_Miss_Date) =
955 Fnd_Api.G_Miss_Date then
956 l_operation_rec.new_effectivity_date := l_operation_rec.effectivity_date;
957 End if;
958
959 If l_operation_rec.disable_date = Fnd_Api.G_Miss_Date then
960 l_operation_rec.disable_date := null;
961 End if;
962
963 If nvl(l_operation_rec.backflush_flag, Fnd_Api.G_Miss_Num) =
964 Fnd_Api.G_Miss_Num then
965 l_operation_rec.backflush_flag := g_yes;
966 End if;
967
968 If nvl(l_operation_rec.option_dependent_flag, Fnd_Api.G_Miss_Num) =
969 Fnd_Api.G_Miss_Num then
970 l_operation_rec.option_dependent_flag := g_no;
971 End if;
972
973 If l_operation_rec.attribute_category = Fnd_Api.G_Miss_Char then
974 l_operation_rec.attribute_category := null;
975 End if;
976
977 If l_operation_rec.attribute1 = Fnd_Api.G_Miss_Char then
978 l_operation_rec.attribute1 := null;
979 End if;
980
981 If l_operation_rec.attribute2 = Fnd_Api.G_Miss_Char then
982 l_operation_rec.attribute2 := null;
983 End if;
984
985 If l_operation_rec.attribute3 = Fnd_Api.G_Miss_Char then
986 l_operation_rec.attribute3 := null;
987 End if;
988
989 If l_operation_rec.attribute4 = Fnd_Api.G_Miss_Char then
990 l_operation_rec.attribute4 := null;
991 End if;
992
993 If l_operation_rec.attribute5 = Fnd_Api.G_Miss_Char then
994 l_operation_rec.attribute5 := null;
995 End if;
996
997 If l_operation_rec.attribute6 = Fnd_Api.G_Miss_Char then
998 l_operation_rec.attribute6 := null;
999 End if;
1000
1001 If l_operation_rec.attribute7 = Fnd_Api.G_Miss_Char then
1002 l_operation_rec.attribute7 := null;
1003 End if;
1004
1005 If l_operation_rec.attribute8 = Fnd_Api.G_Miss_Char then
1006 l_operation_rec.attribute8 := null;
1007 End if;
1008
1009 If l_operation_rec.attribute9 = Fnd_Api.G_Miss_Char then
1010 l_operation_rec.attribute9 := null;
1011 End if;
1012
1013 If l_operation_rec.attribute10 = Fnd_Api.G_Miss_Char then
1014 l_operation_rec.attribute10 := null;
1015 End if;
1016
1017 If l_operation_rec.attribute11 = Fnd_Api.G_Miss_Char then
1018 l_operation_rec.attribute11 := null;
1019 End if;
1020
1021 If l_operation_rec.attribute12 = Fnd_Api.G_Miss_Char then
1022 l_operation_rec.attribute12 := null;
1023 End if;
1024
1025 If l_operation_rec.attribute13 = Fnd_Api.G_Miss_Char then
1026 l_operation_rec.attribute13 := null;
1027 End if;
1028
1029 If l_operation_rec.attribute14 = Fnd_Api.G_Miss_Char then
1030 l_operation_rec.attribute14 := null;
1031 End if;
1032
1033 If l_operation_rec.attribute15 = Fnd_Api.G_Miss_Char then
1034 l_operation_rec.attribute15 := null;
1035 End if;
1039 End if;
1036
1037 If l_operation_rec.assembly_item_id = Fnd_Api.G_Miss_Num then
1038 l_operation_rec.assembly_item_id := null;
1040
1041 If l_operation_rec.organization_id = Fnd_Api.G_Miss_Num then
1042 l_operation_rec.organization_id := null;
1043 End if;
1044
1045 If l_operation_rec.alternate_routing_designator = Fnd_Api.G_Miss_Char then
1046 l_operation_rec.alternate_routing_designator := null;
1047 End if;
1048
1049 If l_operation_rec.organization_code = Fnd_Api.G_Miss_Char then
1050 l_operation_rec.organization_code := null;
1051 End if;
1052
1053 If l_operation_rec.assembly_item_number = Fnd_Api.G_Miss_Char then
1054 l_operation_rec.assembly_item_number := null;
1055 End if;
1056
1057 If l_operation_rec.department_code = Fnd_Api.G_Miss_Char then
1058 l_operation_rec.department_code := null;
1059 End if;
1060
1061 If l_operation_rec.operation_code = Fnd_Api.G_Miss_Char then
1062 l_operation_rec.operation_code := null;
1063 End if;
1064
1065 If l_operation_rec.operation_type = Fnd_Api.G_Miss_Num then
1066 l_operation_rec.operation_type := null;
1067 End if;
1068
1069 If nvl(l_operation_rec.reference_flag, Fnd_Api.G_Miss_Num) =
1070 Fnd_Api.G_Miss_Num then
1071 l_operation_rec.reference_flag := g_no;
1072 End if;
1073
1074 If l_operation_rec.process_op_seq_id = Fnd_Api.G_Miss_Num then
1075 l_operation_rec.process_op_seq_id := null;
1076 End if;
1077
1078 If l_operation_rec.line_op_seq_id = Fnd_Api.G_Miss_Num then
1079 l_operation_rec.line_op_seq_id := null;
1080 End if;
1081
1082 If l_operation_rec.yield = Fnd_Api.G_Miss_Num then
1083 l_operation_rec.yield := null;
1084 End if;
1085
1086 If l_operation_rec.cumulative_yield = Fnd_Api.G_Miss_Num then
1087 l_operation_rec.cumulative_yield := null;
1088 End if;
1089
1090 If l_operation_rec.reverse_cumulative_yield = Fnd_Api.G_Miss_Num then
1091 l_operation_rec.reverse_cumulative_yield := null;
1092 End if;
1093
1094 If l_operation_rec.labor_time_calc = Fnd_Api.G_Miss_Num then
1095 l_operation_rec.labor_time_calc := null;
1096 End if;
1097
1098 If l_operation_rec.machine_time_calc = Fnd_Api.G_Miss_Num then
1099 l_operation_rec.machine_time_calc := null;
1100 End if;
1101
1102 If l_operation_rec.total_time_calc = Fnd_Api.G_Miss_Num then
1103 l_operation_rec.total_time_calc := null;
1104 End if;
1105
1106 If l_operation_rec.labor_time_user = Fnd_Api.G_Miss_Num then
1107 l_operation_rec.labor_time_user := null;
1108 End if;
1109
1110 If l_operation_rec.machine_time_user = Fnd_Api.G_Miss_Num then
1111 l_operation_rec.machine_time_user := null;
1112 End if;
1113
1114 If l_operation_rec.total_time_user = Fnd_Api.G_Miss_Num then
1115 l_operation_rec.total_time_user := null;
1116 End if;
1117
1118 If l_operation_rec.net_planning_percent = Fnd_Api.G_Miss_Num then
1119 l_operation_rec.net_planning_percent := null;
1120 End if;
1121
1122 If nvl(l_operation_rec.include_in_rollup, Fnd_Api.G_Miss_Num) =
1123 Fnd_Api.G_Miss_Num then
1124 l_operation_rec.include_in_rollup := g_yes;
1125 End if;
1126
1127 If nvl(l_operation_rec.operation_yield_enabled, Fnd_Api.G_Miss_Num) =
1128 Fnd_Api.G_Miss_Num then
1129 l_operation_rec.operation_yield_enabled := g_yes;
1130 End if;
1131
1132 If p_validation_level > FND_API.G_VALID_LEVEL_NONE then
1133 ValidateOperation(
1134 p_api_version => 1,
1135 p_init_msg_list => p_init_msg_list,
1136 p_commit => p_commit,
1137 p_validation_level => p_validation_level,
1138 x_return_status => l_return_status,
1139 x_msg_count => l_msg_count,
1140 x_msg_data => l_msg_data,
1141 p_operation_rec => l_operation_rec,
1142 x_operation_rec => l_operation_rec);
1143 If l_return_status = FND_API.G_RET_STS_ERROR then
1144 Raise FND_API.G_EXC_ERROR;
1145 Elsif l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
1146 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
1147 End if; -- validation error
1148 End if; -- validate before inserting
1149
1150 l_UserId := nvl(Fnd_Global.USER_ID, -1);
1151 l_LoginId := Fnd_Global.LOGIN_ID;
1152 l_RequestId := Fnd_Global.CONC_REQUEST_ID;
1153 l_ProgramId := Fnd_Global.CONC_PROGRAM_ID;
1154 l_ApplicationId := Fnd_Global.PROG_APPL_ID;
1155 -- do not use decode because of implicit data type conversions
1156 If l_RequestId is null then
1157 l_ProgramUpdate := null;
1158 Else
1159 l_ProgramUpdate := sysdate;
1160 End if;
1161
1162 Insert into bom_operation_sequences(
1163 operation_sequence_id,
1164 routing_sequence_id,
1165 operation_seq_num,
1166 last_update_date,
1167 last_updated_by,
1168 creation_date,
1169 created_by,
1170 last_update_login,
1171 standard_operation_id,
1172 department_id,
1173 operation_lead_time_percent,
1174 minimum_transfer_quantity,
1175 count_point_type,
1176 operation_description,
1177 effectivity_date,
1178 disable_date,
1179 backflush_flag,
1180 option_dependent_flag,
1184 attribute3,
1181 attribute_category,
1182 attribute1,
1183 attribute2,
1185 attribute4,
1186 attribute5,
1187 attribute6,
1188 attribute7,
1189 attribute8,
1190 attribute9,
1191 attribute10,
1192 attribute11,
1193 attribute12,
1194 attribute13,
1195 attribute14,
1196 attribute15,
1197 request_id,
1198 program_application_id,
1199 program_id,
1200 program_update_date,
1201 operation_type,
1202 reference_flag,
1203 process_op_seq_id,
1204 line_op_seq_id,
1205 yield,
1206 cumulative_yield,
1207 reverse_cumulative_yield,
1208 labor_time_calc,
1209 machine_time_calc,
1210 total_time_calc,
1211 labor_time_user,
1212 machine_time_user,
1213 total_time_user,
1214 net_planning_percent,
1215 include_in_rollup,
1216 operation_yield_enabled,
1217 implementation_date)
1218 Values(
1219 l_operation_rec.operation_sequence_id,
1220 l_operation_rec.routing_sequence_id,
1221 l_operation_rec.operation_seq_num,
1222 sysdate,
1223 l_UserId,
1224 sysdate,
1225 l_UserId,
1226 l_LoginId,
1227 l_operation_rec.standard_operation_id,
1228 l_operation_rec.department_id,
1229 l_operation_rec.operation_lead_time_percent,
1230 l_operation_rec.minimum_transfer_quantity,
1231 l_operation_rec.count_point_type,
1232 l_operation_rec.operation_description,
1233 l_operation_rec.effectivity_date,
1234 l_operation_rec.disable_date,
1235 l_operation_rec.backflush_flag,
1236 l_operation_rec.option_dependent_flag,
1237 l_operation_rec.attribute_category,
1238 l_operation_rec.attribute1,
1239 l_operation_rec.attribute2,
1240 l_operation_rec.attribute3,
1241 l_operation_rec.attribute4,
1242 l_operation_rec.attribute5,
1243 l_operation_rec.attribute6,
1244 l_operation_rec.attribute7,
1245 l_operation_rec.attribute8,
1246 l_operation_rec.attribute9,
1247 l_operation_rec.attribute10,
1248 l_operation_rec.attribute11,
1249 l_operation_rec.attribute12,
1250 l_operation_rec.attribute13,
1251 l_operation_rec.attribute14,
1252 l_operation_rec.attribute15,
1253 l_RequestId,
1254 l_ApplicationId,
1255 l_ProgramId,
1256 l_ProgramUpdate,
1257 l_operation_rec.operation_type,
1258 l_operation_rec.reference_flag,
1259 l_operation_rec.process_op_seq_id,
1260 l_operation_rec.line_op_seq_id,
1261 l_operation_rec.yield,
1262 l_operation_rec.cumulative_yield,
1263 l_operation_rec.reverse_cumulative_yield,
1264 l_operation_rec.labor_time_calc,
1265 l_operation_rec.machine_time_calc,
1266 l_operation_rec.total_time_calc,
1267 l_operation_rec.labor_time_user,
1268 l_operation_rec.machine_time_user,
1269 l_operation_rec.total_time_user,
1270 l_operation_rec.net_planning_percent,
1271 l_operation_rec.include_in_rollup,
1272 l_operation_rec.operation_yield_enabled,
1273 l_operation_rec.effectivity_date) ;
1274
1275 x_operation_rec := l_operation_rec;
1276 -- End of API body.
1277
1278 -- Standard check of p_commit.
1279 IF FND_API.To_Boolean(p_commit) THEN
1280 COMMIT WORK;
1281 END IF;
1282 -- Standard call to get message count and if count is 1, get message info.
1283 FND_MSG_PUB.Count_And_Get(
1284 p_count => x_msg_count,
1285 p_data => x_msg_data
1286 );
1287 EXCEPTION
1288 WHEN FND_API.G_EXC_ERROR THEN
1289 ROLLBACK TO CreateOperation_Pvt;
1290 x_return_status := FND_API.G_RET_STS_ERROR;
1291 FND_MSG_PUB.Count_And_Get(
1292 p_count => x_msg_count,
1293 p_data => x_msg_data
1294 );
1295 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1296 ROLLBACK TO CreateOperation_Pvt;
1297 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1298 FND_MSG_PUB.Count_And_Get(
1299 p_count => x_msg_count,
1300 p_data => x_msg_data
1301 );
1302 WHEN OTHERS THEN
1303 ROLLBACK TO CreateOperation_Pvt;
1304 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1305 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1306 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1307 END IF;
1308 FND_MSG_PUB.Count_And_Get(
1309 p_count => x_msg_count,
1310 p_data => x_msg_data
1311 );
1312 End CreateOperation;
1313
1314 PROCEDURE UpdateOperation(
1315 p_api_version IN NUMBER,
1316 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1317 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1318 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1319 x_return_status IN OUT NOCOPY VARCHAR2,
1320 x_msg_count IN OUT NOCOPY NUMBER,
1321 x_msg_data IN OUT NOCOPY VARCHAR2,
1322 p_operation_rec IN OPERATION_REC_TYPE := G_MISS_OPERATION_REC,
1323 x_operation_rec IN OUT NOCOPY OPERATION_REC_TYPE
1324 ) is
1325 l_api_name CONSTANT VARCHAR2(30) := 'UpdateOperation';
1326 l_api_version CONSTANT NUMBER := 1.0;
1327 l_operation_rec OPERATION_REC_TYPE;
1328 l_return_status VARCHAR2(1);
1329 l_msg_count NUMBER;
1330 l_msg_data VARCHAR2(2000);
1331 l_UserId NUMBER;
1332 l_LoginId NUMBER;
1333 l_RequestId NUMBER;
1337 cursor l_ExistingOperation_csr(P_OpSeqId number,
1334 l_ProgramId NUMBER;
1335 l_ProgramUpdate DATE;
1336 l_ApplicationId NUMBER;
1338 P_RtgSeqId number, P_OpType number, P_SeqNum number,
1339 P_EffDate date) is
1340 Select *
1341 From bom_operation_sequences bos
1342 Where bos.operation_sequence_id = P_OpSeqId
1343 Or (bos.routing_sequence_id = P_RtgSeqId and
1344 nvl(bos.operation_type, g_event) =
1345 nvl(P_OpType, g_event) and
1346 bos.operation_seq_num = P_SeqNum and
1347 bos.effectivity_date = decode(P_OpType,
1348 g_process, bos.effectivity_date,
1349 g_LineOp, bos.effectivity_date,
1350 P_EffDate));
1351 l_OperFound BOOLEAN := false;
1352 BEGIN
1353 -- Standard Start of API savepoint
1354 SAVEPOINT UpdateOperation_Pvt;
1355 -- Standard call to check for call compatibility.
1356 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
1357 G_PKG_NAME) THEN
1358 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1359 END IF;
1360 -- Initialize message list if p_init_msg_list is set to TRUE.
1361 IF FND_API.to_Boolean(p_init_msg_list) THEN
1362 FND_MSG_PUB.initialize;
1363 END IF;
1364 -- Initialize API return status to success
1365 x_return_status := FND_API.G_RET_STS_SUCCESS;
1366
1367 -- API body
1368 l_operation_rec := p_operation_rec;
1369
1370 If p_validation_level = FND_API.G_VALID_LEVEL_FULL then
1371 AssignOperation(
1372 p_api_version => 1,
1373 p_init_msg_list => p_init_msg_list,
1374 p_commit => p_commit,
1375 p_validation_level => p_validation_level,
1376 x_return_status => l_return_status,
1377 x_msg_count => l_msg_count,
1378 x_msg_data => l_msg_data,
1379 p_operation_rec => l_operation_rec,
1380 x_operation_rec => l_operation_rec
1381 );
1382 If l_return_status = FND_API.G_RET_STS_ERROR then
1383 Raise FND_API.G_EXC_ERROR;
1384 Elsif l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
1385 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
1386 End if; -- assign error
1387 End If; -- assign
1388
1389 -- populate unspecified values
1390
1391 l_OperFound := false;
1392 For l_OldOper_rec in l_ExistingOperation_csr(
1393 P_OpSeqId => l_operation_rec.operation_sequence_id,
1394 P_RtgSeqId => l_operation_rec.routing_sequence_id,
1395 P_OpType => l_operation_rec.operation_type,
1396 P_SeqNum => l_operation_rec.operation_seq_num,
1397 P_EffDate => l_operation_rec.effectivity_date) loop
1398 l_OperFound := true;
1399
1400 -- non updatable
1401 l_operation_rec.operation_sequence_id :=
1402 l_OldOper_rec.operation_sequence_id;
1403 l_operation_rec.routing_sequence_id := l_OldOper_rec.routing_sequence_id;
1404 l_operation_rec.operation_type := l_OldOper_rec.operation_type;
1405
1406 l_operation_rec.operation_seq_num := l_OldOper_rec.operation_seq_num;
1407 If l_operation_rec.new_operation_seq_num = Fnd_Api.G_Miss_Num then
1408 l_operation_rec.new_operation_seq_num :=
1409 l_OldOper_rec.operation_seq_num;
1410 End if;
1411
1412 If l_operation_rec.standard_operation_id = Fnd_Api.G_Miss_Num then
1413 l_operation_rec.standard_operation_id :=
1414 l_OldOper_rec.standard_operation_id;
1415 End if;
1416
1417 If l_operation_rec.department_id = Fnd_Api.G_Miss_Num then
1418 l_operation_rec.department_id := l_OldOper_rec.department_id;
1419 End if;
1420
1421 If l_operation_rec.operation_lead_time_percent = Fnd_Api.G_Miss_Num then
1422 l_operation_rec.operation_lead_time_percent :=
1423 l_OldOper_rec.operation_lead_time_percent;
1424 End if;
1425
1426 If l_operation_rec.minimum_transfer_quantity = Fnd_Api.G_Miss_Num then
1427 l_operation_rec.minimum_transfer_quantity :=
1428 l_OldOper_rec.minimum_transfer_quantity;
1429 End if;
1430
1431 If l_operation_rec.count_point_type = Fnd_Api.G_Miss_Num then
1432 l_operation_rec.count_point_type := l_OldOper_rec.count_point_type;
1433 End if;
1434
1435 If l_operation_rec.operation_description = Fnd_Api.G_Miss_Char then
1436 l_operation_rec.operation_description :=
1437 l_OldOper_rec.operation_description;
1438 End if;
1439
1440 l_operation_rec.effectivity_date := l_OldOper_rec.effectivity_date;
1441 If l_operation_rec.new_effectivity_date = Fnd_Api.G_Miss_Date then
1442 l_operation_rec.new_effectivity_date :=
1443 l_OldOper_rec.effectivity_date;
1444 End if;
1445
1446 If l_operation_rec.disable_date = Fnd_Api.G_Miss_Date then
1447 l_operation_rec.disable_date := l_OldOper_rec.disable_date;
1448 End if;
1449
1450 If l_operation_rec.backflush_flag = Fnd_Api.G_Miss_Num then
1451 l_operation_rec.backflush_flag := l_OldOper_rec.backflush_flag;
1452 End if;
1453
1454 If l_operation_rec.option_dependent_flag = Fnd_Api.G_Miss_Num then
1455 l_operation_rec.option_dependent_flag :=
1456 l_OldOper_rec.option_dependent_flag;
1457 End if;
1458
1459 If l_operation_rec.attribute_category = Fnd_Api.G_Miss_Char then
1460 l_operation_rec.attribute_category := l_OldOper_rec.attribute_category;
1461 End if;
1462
1463 If l_operation_rec.attribute1 = Fnd_Api.G_Miss_Char then
1467 If l_operation_rec.attribute2 = Fnd_Api.G_Miss_Char then
1464 l_operation_rec.attribute1 := l_OldOper_rec.attribute1;
1465 End if;
1466
1468 l_operation_rec.attribute2 := l_OldOper_rec.attribute2;
1469 End if;
1470
1471 If l_operation_rec.attribute3 = Fnd_Api.G_Miss_Char then
1472 l_operation_rec.attribute3 := l_OldOper_rec.attribute3;
1473 End if;
1474
1475 If l_operation_rec.attribute4 = Fnd_Api.G_Miss_Char then
1476 l_operation_rec.attribute4 := l_OldOper_rec.attribute4;
1477 End if;
1478
1479 If l_operation_rec.attribute5 = Fnd_Api.G_Miss_Char then
1480 l_operation_rec.attribute5 := l_OldOper_rec.attribute5;
1481 End if;
1482
1483 If l_operation_rec.attribute6 = Fnd_Api.G_Miss_Char then
1484 l_operation_rec.attribute6 := l_OldOper_rec.attribute6;
1485 End if;
1486
1487 If l_operation_rec.attribute7 = Fnd_Api.G_Miss_Char then
1488 l_operation_rec.attribute7 := l_OldOper_rec.attribute7;
1489 End if;
1490
1491 If l_operation_rec.attribute8 = Fnd_Api.G_Miss_Char then
1492 l_operation_rec.attribute8 := l_OldOper_rec.attribute8;
1493 End if;
1494
1495 If l_operation_rec.attribute9 = Fnd_Api.G_Miss_Char then
1496 l_operation_rec.attribute9 := l_OldOper_rec.attribute9;
1497 End if;
1498
1499 If l_operation_rec.attribute10 = Fnd_Api.G_Miss_Char then
1500 l_operation_rec.attribute10 := l_OldOper_rec.attribute10;
1501 End if;
1502
1503 If l_operation_rec.attribute11 = Fnd_Api.G_Miss_Char then
1504 l_operation_rec.attribute11 := l_OldOper_rec.attribute11;
1505 End if;
1506
1507 If l_operation_rec.attribute12 = Fnd_Api.G_Miss_Char then
1508 l_operation_rec.attribute12 := l_OldOper_rec.attribute12;
1509 End if;
1510
1511 If l_operation_rec.attribute13 = Fnd_Api.G_Miss_Char then
1512 l_operation_rec.attribute13 := l_OldOper_rec.attribute13;
1513 End if;
1514
1515 If l_operation_rec.attribute14 = Fnd_Api.G_Miss_Char then
1516 l_operation_rec.attribute14 := l_OldOper_rec.attribute14;
1517 End if;
1518
1519 If l_operation_rec.attribute15 = Fnd_Api.G_Miss_Char then
1520 l_operation_rec.attribute15 := l_OldOper_rec.attribute15;
1521 End if;
1522
1523 If l_operation_rec.reference_flag = Fnd_Api.G_Miss_Num then
1524 l_operation_rec.reference_flag := l_OldOper_rec.reference_flag;
1525 End if;
1526
1527 If l_operation_rec.process_op_seq_id = Fnd_Api.G_Miss_Num then
1528 l_operation_rec.process_op_seq_id := l_OldOper_rec.process_op_seq_id;
1529 End if;
1530
1531 If l_operation_rec.line_op_seq_id = Fnd_Api.G_Miss_Num then
1532 l_operation_rec.line_op_seq_id := l_OldOper_rec.line_op_seq_id;
1533 End if;
1534
1535 If l_operation_rec.yield = Fnd_Api.G_Miss_Num then
1536 l_operation_rec.yield := l_OldOper_rec.yield;
1537 End if;
1538
1539 If l_operation_rec.cumulative_yield = Fnd_Api.G_Miss_Num then
1540 l_operation_rec.cumulative_yield := l_OldOper_rec.cumulative_yield;
1541 End if;
1542
1543 If l_operation_rec.reverse_cumulative_yield = Fnd_Api.G_Miss_Num then
1544 l_operation_rec.reverse_cumulative_yield :=
1545 l_OldOper_rec.reverse_cumulative_yield;
1546 End if;
1547
1548 If l_operation_rec.labor_time_calc = Fnd_Api.G_Miss_Num then
1549 l_operation_rec.labor_time_calc := l_OldOper_rec.labor_time_calc;
1550 End if;
1551
1552 If l_operation_rec.machine_time_calc = Fnd_Api.G_Miss_Num then
1553 l_operation_rec.machine_time_calc := l_OldOper_rec.machine_time_calc;
1554 End if;
1555
1556 If l_operation_rec.total_time_calc = Fnd_Api.G_Miss_Num then
1557 l_operation_rec.total_time_calc := l_OldOper_rec.total_time_calc;
1558 End if;
1559
1560 If l_operation_rec.labor_time_user = Fnd_Api.G_Miss_Num then
1561 l_operation_rec.labor_time_user := l_OldOper_rec.labor_time_user;
1562 End if;
1563
1564 If l_operation_rec.machine_time_user = Fnd_Api.G_Miss_Num then
1565 l_operation_rec.machine_time_user := l_OldOper_rec.machine_time_user;
1566 End if;
1567
1568 If l_operation_rec.total_time_user = Fnd_Api.G_Miss_Num then
1569 l_operation_rec.total_time_user := l_OldOper_rec.total_time_user;
1570 End if;
1571
1572 If l_operation_rec.net_planning_percent = Fnd_Api.G_Miss_Num then
1573 l_operation_rec.net_planning_percent :=
1574 l_OldOper_rec.net_planning_percent;
1575 End if;
1576
1577 If l_operation_rec.include_in_rollup = Fnd_Api.G_Miss_Num then
1578 l_operation_rec.include_in_rollup := l_OldOper_rec.include_in_rollup;
1579 End if;
1580
1581 If l_operation_rec.operation_yield_enabled = Fnd_Api.G_Miss_Num then
1582 l_operation_rec.operation_yield_enabled := l_OldOper_rec.operation_yield_enabled;
1583 End if;
1584
1585 End loop; -- get old values
1586
1587 If not l_OperFound then
1588 Fnd_Message.Set_Name('BOM', 'BOM_INVALID_OPERATION');
1589 FND_MSG_PUB.Add;
1590 Raise FND_API.G_EXC_ERROR;
1591 End if; -- missing operation
1592
1593 If p_validation_level > FND_API.G_VALID_LEVEL_NONE then
1594 ValidateOperation(
1595 p_api_version => 1,
1596 p_init_msg_list => p_init_msg_list,
1597 p_commit => p_commit,
1601 x_msg_data => l_msg_data,
1598 p_validation_level => FND_API.G_VALID_LEVEL_NONE,
1599 x_return_status => l_return_status,
1600 x_msg_count => l_msg_count,
1602 p_operation_rec => l_operation_rec,
1603 x_operation_rec => l_operation_rec
1604 );
1605 If l_return_status = FND_API.G_RET_STS_ERROR then
1606 Raise FND_API.G_EXC_ERROR;
1607 Elsif l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
1608 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
1609 End if; -- validation error
1610 End If; -- validation
1611
1612 -- update operation
1613
1614 l_UserId := nvl(Fnd_Global.USER_ID, -1);
1615 l_LoginId := Fnd_Global.LOGIN_ID;
1616 l_RequestId := Fnd_Global.CONC_REQUEST_ID;
1617 l_ProgramId := Fnd_Global.CONC_PROGRAM_ID;
1618 l_ApplicationId := Fnd_Global.PROG_APPL_ID;
1619 -- do not use decode because of implicit data type conversions
1620 If l_RequestId is null then
1621 l_ProgramUpdate := null;
1622 Else
1623 l_ProgramUpdate := sysdate;
1624 End if;
1625
1626 update bom_operation_sequences set
1627 operation_seq_num = l_operation_rec.new_operation_seq_num,
1628 last_update_date = sysdate,
1629 last_updated_by = l_UserId,
1630 creation_date = sysdate,
1631 created_by = l_UserId,
1632 last_update_login = l_LoginId,
1633 standard_operation_id = l_operation_rec.standard_operation_id,
1634 department_id = l_operation_rec.department_id,
1635 operation_lead_time_percent =
1636 l_operation_rec.operation_lead_time_percent,
1637 minimum_transfer_quantity = l_operation_rec.minimum_transfer_quantity,
1638 count_point_type = l_operation_rec.count_point_type,
1639 operation_description = l_operation_rec.operation_description,
1640 effectivity_date = l_operation_rec.new_effectivity_date,
1641 disable_date = l_operation_rec.disable_date,
1642 backflush_flag = l_operation_rec.backflush_flag,
1643 option_dependent_flag = l_operation_rec.option_dependent_flag,
1644 attribute_category = l_operation_rec.attribute_category,
1645 attribute1 = l_operation_rec.attribute1,
1646 attribute2 = l_operation_rec.attribute2,
1647 attribute3 = l_operation_rec.attribute3,
1648 attribute4 = l_operation_rec.attribute4,
1649 attribute5 = l_operation_rec.attribute5,
1650 attribute6 = l_operation_rec.attribute6,
1651 attribute7 = l_operation_rec.attribute7,
1652 attribute8 = l_operation_rec.attribute8,
1653 attribute9 = l_operation_rec.attribute9,
1654 attribute10 = l_operation_rec.attribute10,
1655 attribute11 = l_operation_rec.attribute11,
1656 attribute12 = l_operation_rec.attribute12,
1657 attribute13 = l_operation_rec.attribute13,
1658 attribute14 = l_operation_rec.attribute14,
1659 attribute15 = l_operation_rec.attribute15,
1660 request_id = l_RequestId,
1661 program_application_id = l_ApplicationId,
1662 program_id = l_ProgramId,
1663 program_update_date = l_ProgramUpdate,
1664 reference_flag = l_operation_rec.reference_flag,
1665 process_op_seq_id = l_operation_rec.process_op_seq_id,
1666 line_op_seq_id = l_operation_rec.line_op_seq_id,
1667 yield = l_operation_rec.yield,
1668 cumulative_yield = l_operation_rec.cumulative_yield,
1669 reverse_cumulative_yield = l_operation_rec.reverse_cumulative_yield,
1670 labor_time_calc = l_operation_rec.labor_time_calc,
1671 machine_time_calc = l_operation_rec.machine_time_calc,
1672 total_time_calc = l_operation_rec.total_time_calc,
1673 labor_time_user = l_operation_rec.labor_time_user,
1674 machine_time_user = l_operation_rec.machine_time_user,
1675 total_time_user = l_operation_rec.total_time_user,
1676 net_planning_percent = l_operation_rec.net_planning_percent,
1677 include_in_rollup = l_operation_rec.include_in_rollup,
1678 operation_yield_enabled = l_operation_rec.operation_yield_enabled
1679 Where operation_sequence_id = l_operation_rec.operation_sequence_id
1680 Or (routing_sequence_id = l_operation_rec.routing_sequence_id
1681 and nvl(operation_type, g_event) =
1682 nvl(l_operation_rec.operation_type, g_event)
1683 and operation_seq_num = l_operation_rec.operation_seq_num
1684 and effectivity_date = decode(l_operation_rec.operation_type,
1685 g_process, effectivity_date,
1686 g_LineOp, effectivity_date,
1687 l_operation_rec.effectivity_date));
1688
1689 --bugFix 1690706 Begin
1690 UPDATE BOM_INVENTORY_COMPONENTS bic SET
1691 bic.OPERATION_LEAD_TIME_PERCENT = l_operation_rec.operation_lead_time_percent
1692 WHERE bic.OPERATION_SEQ_NUM = l_operation_rec.new_operation_seq_num
1693 and bic.BILL_SEQUENCE_ID =
1694 (select bom.BILL_SEQUENCE_ID
1695 from BOM_BILL_OF_MATERIALS bom,
1696 BOM_OPERATIONAL_ROUTINGS bor
1697 where bor.routing_sequence_id = l_operation_rec.routing_sequence_id
1698 and nvl(bor.alternate_routing_designator,'NONE') =
1699 nvl(bom.ALTERNATE_BOM_DESIGNATOR,'NONE')
1700 and bom.ASSEMBLY_ITEM_ID = bor.assembly_item_id
1701 and bom.ORGANIZATION_ID = bor.organization_id
1702 );
1703 --bugFix 1690706 End
1704 x_operation_rec := l_operation_rec;
1705 -- End of API body.
1706
1707 -- Standard check of p_commit.
1708 IF FND_API.To_Boolean( p_commit ) THEN
1709 COMMIT WORK;
1713 p_count => x_msg_count,
1710 END IF;
1711 -- Standard call to get message count and if count is 1, get message info.
1712 FND_MSG_PUB.Count_And_Get(
1714 p_data => x_msg_data
1715 );
1716 EXCEPTION
1717 WHEN FND_API.G_EXC_ERROR THEN
1718 ROLLBACK TO UpdateOperation_Pvt;
1719 x_return_status := FND_API.G_RET_STS_ERROR;
1720 FND_MSG_PUB.Count_And_Get(
1721 p_count => x_msg_count,
1722 p_data => x_msg_data
1723 );
1724 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1725 ROLLBACK TO UpdateOperation_Pvt;
1726 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1727 FND_MSG_PUB.Count_And_Get(
1728 p_count => x_msg_count,
1729 p_data => x_msg_data
1730 );
1731 WHEN OTHERS THEN
1732 ROLLBACK TO UpdateOperation_Pvt;
1733 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1734 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1735 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1736 END IF;
1737 FND_MSG_PUB.Count_And_Get(
1738 p_count => x_msg_count,
1739 p_data => x_msg_data
1740 );
1741 End UpdateOperation;
1742
1743 PROCEDURE DeleteOperation(
1744 p_api_version IN NUMBER,
1745 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1746 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1747 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1748 x_return_status IN OUT NOCOPY VARCHAR2,
1749 x_msg_count IN OUT NOCOPY NUMBER,
1750 x_msg_data IN OUT NOCOPY VARCHAR2,
1751 p_delete_group IN VARCHAR2,
1752 p_description IN VARCHAR2 := Null,
1753 p_operation_rec IN OPERATION_REC_TYPE := G_MISS_OPERATION_REC,
1754 x_operation_rec IN OUT NOCOPY OPERATION_REC_TYPE
1755 ) is
1756 l_api_name CONSTANT VARCHAR2(30) := 'DeleteOperation';
1757 l_api_version CONSTANT NUMBER := 1.0;
1758 l_operation_rec OPERATION_REC_TYPE;
1759 l_DeleteGrpSeqId number := null;
1760 l_return_status VARCHAR2(1);
1761 l_msg_count NUMBER;
1762 l_msg_data VARCHAR2(2000);
1763 l_UserId number;
1764 cursor l_ExistingOperation_csr(P_OpSeqId number,
1765 P_RtgSeqId number, P_OpType number, P_SeqNum number,
1766 P_EffDate date) is
1767 Select bos.operation_sequence_id,
1768 bor.routing_sequence_id,
1769 bor.assembly_item_id,
1770 bor.organization_id,
1771 bor.alternate_routing_designator,
1772 bor.routing_type
1773 From bom_operational_routings bor,
1774 bom_operation_sequences bos
1775 Where bor.routing_sequence_id = bos.routing_sequence_id
1776 and (bos.operation_sequence_id = P_OpSeqId
1777 Or
1778 (bos.routing_sequence_id = P_RtgSeqId and
1779 nvl(bos.operation_type, g_event) =
1780 nvl(P_OpType, g_event) and
1781 bos.operation_seq_num = P_SeqNum and
1782 bos.effectivity_date = decode(P_OpType,
1783 g_process, bos.effectivity_date,
1784 g_LineOp, bos.effectivity_date,
1785 P_EffDate))
1786 );
1787 l_OperFound BOOLEAN := false;
1788 cursor l_group_csr(P_OrgId number) is
1789 Select delete_group_sequence_id
1790 From bom_delete_groups
1791 Where delete_group_name = p_delete_group
1792 And organization_id = P_OrgId;
1793 l_operation constant number := 5; -- delete type
1794 l_ReturnCode number;
1795 BEGIN
1796 -- Standard Start of API savepoint
1797 SAVEPOINT DeleteOperation_Pvt;
1798 -- Standard call to check for call compatibility.
1799 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
1800 G_PKG_NAME) THEN
1801 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1802 END IF;
1803 -- Initialize message list if p_init_msg_list is set to TRUE.
1804 IF FND_API.to_Boolean(p_init_msg_list) THEN
1805 FND_MSG_PUB.initialize;
1806 END IF;
1807 -- Initialize API return status to success
1808 x_return_status := FND_API.G_RET_STS_SUCCESS;
1809
1810 -- API body
1811 l_operation_rec := p_operation_rec;
1812 If p_validation_level = FND_API.G_VALID_LEVEL_FULL then
1813 AssignOperation(
1814 p_api_version => 1,
1815 p_init_msg_list => p_init_msg_list,
1816 p_commit => p_commit,
1817 p_validation_level => p_validation_level,
1818 x_return_status => l_return_status,
1819 x_msg_count => l_msg_count,
1820 x_msg_data => l_msg_data,
1821 p_operation_rec => l_operation_rec,
1822 x_operation_rec => l_operation_rec
1823 );
1824 If l_return_status = FND_API.G_RET_STS_ERROR then
1825 Raise FND_API.G_EXC_ERROR;
1826 Elsif l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
1827 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
1828 End if; -- assign error
1829 End If; -- assign
1830
1831 l_DeleteGrpSeqId := null;
1832 For l_DelGrp_rec in l_group_csr(
1833 P_OrgId => l_operation_rec.organization_id) loop
1834 l_DeleteGrpSeqId := l_DelGrp_rec.delete_group_sequence_id;
1835 End loop; -- get existing delete group
1839 l_OperFound := false;
1836
1837 l_UserId := nvl(Fnd_Global.USER_ID, -1);
1838
1840 For l_OldOper_rec in l_ExistingOperation_csr(
1841 P_OpSeqId => l_operation_rec.operation_sequence_id,
1842 P_RtgSeqId => l_operation_rec.routing_sequence_id,
1843 P_OpType => l_operation_rec.operation_type,
1844 P_SeqNum => l_operation_rec.operation_seq_num,
1845 P_EffDate => l_operation_rec.effectivity_date) loop
1846 l_OperFound := true; -- old operation found
1847 l_ReturnCode := MODAL_DELETE.DELETE_MANAGER_OI(
1848 new_group_seq_id => l_DeleteGrpSeqId,
1849 name => p_delete_group,
1850 group_desc => p_description,
1851 org_id => l_OldOper_rec.organization_id,
1852 bom_or_eng => l_OldOper_rec.routing_type,
1853 del_type => l_operation,
1854 ent_bill_seq_id => null,
1855 ent_rtg_seq_id => l_OldOper_rec.routing_sequence_id,
1856 ent_inv_item_id => l_OldOper_rec.assembly_item_id,
1857 ent_alt_designator => l_OldOper_rec.alternate_routing_designator,
1858 ent_comp_seq_id => null,
1859 ent_op_seq_id => l_OldOper_rec.operation_sequence_id,
1860 user_id => l_UserId,
1861 err_text => l_msg_data
1862 );
1863 If l_ReturnCode <> 0 then
1864 Fnd_Msg_Pub.Add_Exc_Msg (
1865 p_pkg_name => 'MODAL_DELETE',
1866 p_procedure_name => 'DELETE_MANAGER_OI',
1867 p_error_text => l_msg_data
1868 );
1869 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
1870 End if; -- SQL error in modal delete
1871 End loop; -- Add to delete group
1872
1873 If not l_OperFound then
1874 Fnd_Message.Set_Name('BOM', 'BOM_INVALID_OPERATION');
1875 FND_MSG_PUB.Add;
1876 Raise FND_API.G_EXC_ERROR;
1877 End if; -- missing operation
1878
1879 x_operation_rec := l_operation_rec;
1880 -- End of API body.
1881
1882 -- Standard check of p_commit.
1883 IF FND_API.To_Boolean(p_commit) THEN
1884 COMMIT WORK;
1885 END IF;
1886 -- Standard call to get message count and if count is 1, get message info.
1887 FND_MSG_PUB.Count_And_Get(
1888 p_count => x_msg_count,
1889 p_data => x_msg_data
1890 );
1891 EXCEPTION
1892 WHEN FND_API.G_EXC_ERROR THEN
1893 ROLLBACK TO DeleteOperation_Pvt;
1894 x_return_status := FND_API.G_RET_STS_ERROR;
1895 FND_MSG_PUB.Count_And_Get(
1896 p_count => x_msg_count,
1897 p_data => x_msg_data
1898 );
1899 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1900 ROLLBACK TO DeleteOperation_Pvt;
1901 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1902 FND_MSG_PUB.Count_And_Get(
1903 p_count => x_msg_count,
1904 p_data => x_msg_data
1905 );
1906 WHEN OTHERS THEN
1907 ROLLBACK TO DeleteOperation_Pvt;
1908 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1909 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1910 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1911 END IF;
1912 FND_MSG_PUB.Count_And_Get(
1913 p_count => x_msg_count,
1914 p_data => x_msg_data
1915 );
1916 End DeleteOperation;
1917
1918 END BOM_Operation_Pvt;