[Home] [Help]
PACKAGE BODY: APPS.BOM_OPERATION_PVT
Source
1 PACKAGE BODY BOM_Operation_Pvt AS
2 -- $Header: BOMVOPRB.pls 120.2.12020000.3 2012/09/04 11:37:31 rambkond 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
61 from bom_standard_operations bso,
58 cursor l_StdOp_csr(P_Code varchar2, P_OpType number,
59 P_RtgSeqId number) is
60 select bso.standard_operation_id
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
193 End if;
190 Fnd_Message.Set_Name('BOM', 'BOM_RTG_SEQ_INVALID');
191 FND_MSG_PUB.Add;
192 Raise FND_API.G_EXC_ERROR;
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 <>
322 l_PrevRefFlag number := null;
319 routing_sequence_id;
320 l_PrevStdOp number := null;
321 l_PrevStdCode varchar2(4) := 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
393 from bom_departments bd,
394 bom_operational_routings bor
395 where bd.organization_id = bor.organization_id
396 and bor.routing_sequence_id = P_RtgSeqId
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;
448 Fnd_Message.Set_Name('BOM', 'BOM_EFF_DATE_MISSING');
445
446 -- check effective date entered
447 If l_operation_rec.new_effectivity_date is null then
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
491 P_RtgSeqId => l_operation_rec.routing_sequence_id) loop
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(
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');
523 Fnd_Message.Set_Token('OPERATION', l_PrevStdCode);
524 FND_MSG_PUB.Add;
525 Raise FND_API.G_EXC_ERROR;
526 End if;
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;
539 P_OpType => l_operation_rec.operation_type,
536 End loop; -- resources exist, cannot copy
537 l_StdOpFound := false;
538 For l_StdOp_rec in l_StdOp_csr(
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 schedule_seq_num, -- Bug#14550670
623 principle_flag) -- Bug#14550670
624 Select
625 l_operation_rec.operation_sequence_id,
626 resource_seq_num,
627 resource_id,
628 activity_id,
629 standard_rate_flag,
630 assigned_units,
631 usage_rate_or_amount,
632 usage_rate_or_amount_inverse,
633 basis_type,
637 sysdate,
634 schedule_flag,
635 sysdate,
636 l_UserId,
638 l_UserId,
639 l_LoginId,
640 null,
641 autocharge_type,
642 attribute_category,
643 attribute1,
644 attribute2,
645 attribute3,
646 attribute4,
647 attribute5,
648 attribute6,
649 attribute7,
650 attribute8,
651 attribute9,
652 attribute10,
653 attribute11,
654 attribute12,
655 attribute13,
656 attribute14,
657 attribute15,
658 l_RequestId,
659 l_ApplicationId,
660 l_ProgramId,
661 l_ProgramUpdate,
662 schedule_seq_num, -- Bug#14550670
663 principle_flag -- Bug#14550670
664 From bom_std_op_resources
665 Where standard_operation_id = l_operation_rec.standard_operation_id;
666 -- copy attachment
667 FND_ATTACHED_DOCUMENTS2_PKG.copy_attachments(
668 X_from_entity_name => 'BOM_STANDARD_OPERATIONS',
669 X_from_pk1_value => to_char(
670 l_operation_rec.standard_operation_id),
671 X_from_pk2_value => null,
672 X_from_pk3_value => null,
673 X_from_pk4_value => null,
674 X_from_pk5_value => null,
675 X_to_entity_name => 'BOM_OPERATION_SEQUENCES',
676 X_to_pk1_value => to_char(
677 l_operation_rec.operation_sequence_id),
678 X_to_pk2_value => null,
679 X_to_pk3_value => null,
680 X_to_pk4_value => null,
681 X_to_pk5_value => null,
682 X_created_by => l_UserId,
683 X_last_update_login => l_LoginId,
684 X_program_application_id => l_ApplicationId,
685 X_program_id => l_ProgramId,
686 X_request_id => l_RequestId
687 );
688 End if; -- copy
689
690 -- columns corresponding to columns in Standard Operations should not be
691 -- changed when referenced.
692 If l_operation_rec.reference_flag = g_yes then
693 For l_StdOp_rec in l_StdOp_csr(
694 P_OpType => l_operation_rec.operation_type,
695 P_RtgSeqId => l_operation_rec.routing_sequence_id,
696 P_StdOpId => l_operation_rec.standard_operation_id) loop
697 l_StdOpFound := true;
698 l_operation_rec.department_id := l_StdOp_rec.department_id;
699 l_operation_rec.minimum_transfer_quantity :=
700 l_StdOp_rec.minimum_transfer_quantity;
701 l_operation_rec.count_point_type := l_StdOp_rec.count_point_type;
702 l_operation_rec.operation_description :=
703 l_StdOp_rec.operation_description;
704 l_operation_rec.option_dependent_flag :=
705 nvl(l_operation_rec.option_dependent_flag,l_StdOp_rec.option_dependent_flag);
706 l_operation_rec.attribute_category := l_StdOp_rec.attribute_category;
707 l_operation_rec.attribute1 := l_StdOp_rec.attribute1;
708 l_operation_rec.attribute2 := l_StdOp_rec.attribute2;
709 l_operation_rec.attribute3 := l_StdOp_rec.attribute3;
710 l_operation_rec.attribute4 := l_StdOp_rec.attribute4;
711 l_operation_rec.attribute5 := l_StdOp_rec.attribute5;
712 l_operation_rec.attribute6 := l_StdOp_rec.attribute6;
713 l_operation_rec.attribute7 := l_StdOp_rec.attribute7;
714 l_operation_rec.attribute8 := l_StdOp_rec.attribute8;
715 l_operation_rec.attribute9 := l_StdOp_rec.attribute9;
716 l_operation_rec.attribute10 := l_StdOp_rec.attribute10;
717 l_operation_rec.attribute11 := l_StdOp_rec.attribute11;
718 l_operation_rec.attribute12 := l_StdOp_rec.attribute12;
719 l_operation_rec.attribute13 := l_StdOp_rec.attribute13;
720 l_operation_rec.attribute14 := l_StdOp_rec.attribute14;
721 l_operation_rec.attribute15 := l_StdOp_rec.attribute15;
722 l_operation_rec.backflush_flag := l_StdOp_rec.backflush_flag;
723 l_operation_rec.operation_yield_enabled := l_StdOp_rec.operation_yield_enabled;
724 End loop; -- copy standard operation
725 If not l_StdOpFound then
726 Fnd_Message.Set_Name('BOM', 'BOM_STD_OP_ID_INVALID');
727 FND_MSG_PUB.Add;
728 Raise FND_API.G_EXC_ERROR;
729 End if; -- invalid op code
730 End if; -- referenced
731
732 -- Validate Department
733 For l_dept_rec in l_dept_csr(
734 P_RtgSeqId => l_operation_rec.routing_sequence_id,
735 P_DeptId => l_operation_rec.department_id,
736 P_EffDate => l_operation_rec.new_effectivity_date) loop
737 Fnd_Message.Set_Name('BOM', 'BOM_DEPT_ID_INVALID');
738 FND_MSG_PUB.Add;
739 Raise FND_API.G_EXC_ERROR;
743
740 End loop; -- invalid department
741
742 -- validate operation details
744 if (l_operation_rec.minimum_transfer_quantity < 0)
745 or (l_operation_rec.new_effectivity_date > l_operation_rec.disable_date)
746 or (l_operation_rec.count_point_type not in (1,2,3))
747 or (l_operation_rec.backflush_flag not in (1,2))
748 or (l_operation_rec.option_dependent_flag not in (1,2))
749 -- BACKFLUSH_FLAG must be Yes if COUNT_POINT_TYPE is No-direct charge
750 or (l_operation_rec.count_point_type = 3 and
751 l_operation_rec.backflush_flag <> 1)
752 or (l_operation_rec.operation_lead_time_percent not between 0 and 100)
753 -- CFM attributes
754 or (l_operation_rec.net_planning_percent not between 0 and 100)
755 or (l_operation_rec.yield not between 0 and 1)
756 or (l_operation_rec.cumulative_yield not between 0 and 1)
757 or (l_operation_rec.reverse_cumulative_yield not between 0 and 1)
758 or (l_operation_rec.include_in_rollup not in (1,2))
759 or (l_operation_rec.operation_yield_enabled not in (1,2))
760 then
761 Fnd_Message.Set_Name('BOM', 'BOM_OPERATION_ERROR');
762 FND_MSG_PUB.Add;
763 Raise FND_API.G_EXC_ERROR;
764 End if; -- etc, etc, etc
765
766 -- CFM validation
767
768 For l_noncfm_rec in l_noncfm_csr (
769 P_RtgSeqId => l_operation_rec.routing_sequence_id) loop
770 l_operation_rec.process_op_seq_id := null;
771 l_operation_rec.line_op_seq_id := null;
772 l_operation_rec.yield := null;
773 l_operation_rec.cumulative_yield := null;
774 l_operation_rec.reverse_cumulative_yield := null;
775 l_operation_rec.labor_time_calc := null;
776 l_operation_rec.machine_time_calc := null;
777 l_operation_rec.total_time_calc := null;
778 l_operation_rec.labor_time_user := null;
779 l_operation_rec.machine_time_user := null;
780 l_operation_rec.total_time_user := null;
781 l_operation_rec.net_planning_percent := null;
782 /** Bug 2097667 Default value for these 2 fields should be
783 1 regardless of the routing type
784 l_operation_rec.include_in_rollup := null;
788
785 l_operation_rec.operation_yield_enabled := null;
786 **/
787 End loop;
789 For l_lbr_rec in l_lbr_csr (
790 P_RtgSeqId => l_operation_rec.routing_sequence_id) loop
791 l_operation_rec.process_op_seq_id := null;
792 l_operation_rec.line_op_seq_id := null;
793 l_operation_rec.labor_time_calc := null;
794 l_operation_rec.machine_time_calc := null;
795 l_operation_rec.total_time_calc := null;
796 l_operation_rec.labor_time_user := null;
797 l_operation_rec.machine_time_user := null;
798 l_operation_rec.total_time_user := null;
799 l_operation_rec.net_planning_percent := null;
800 End loop;
801
802 If nvl(l_operation_rec.operation_type, g_event) <> g_event and
803 (l_operation_rec.process_op_seq_id is not null or
804 l_operation_rec.line_op_seq_id is not null) then
805 Fnd_Message.Set_Name('BOM', 'BOM_PARENT_OP_NULL');
806 FND_MSG_PUB.Add;
807 Raise FND_API.G_EXC_ERROR;
808 End if; -- only events can have parents
809
810 For l_process_rec in l_parents_csr(
811 P_ParentSeqId => l_operation_rec.process_op_seq_id) loop
812 Fnd_Message.Set_Name('BOM', 'BOM_PARENT_OP_INVALID');
813 FND_MSG_PUB.Add;
814 Raise FND_API.G_EXC_ERROR;
815 End loop; -- invalid process
816
817 For l_LineOp_rec in l_parents_csr(
818 P_ParentSeqId => l_operation_rec.line_op_seq_id) loop
819 Fnd_Message.Set_Name('BOM', 'BOM_PARENT_OP_INVALID');
820 FND_MSG_PUB.Add;
821 Raise FND_API.G_EXC_ERROR;
822 End loop; -- invalid line operation
823
824 x_operation_rec := l_operation_rec;
825
826 -- End of API body.
827
828 -- Standard check of p_commit.
829 IF FND_API.To_Boolean(p_commit) THEN
830 COMMIT WORK;
831 END IF;
832
833 -- Standard call to get message count and if count is 1, get message info.
834 FND_MSG_PUB.Count_And_Get(
835 p_count => x_msg_count,
836 p_data => x_msg_data
837 );
838 EXCEPTION
839 WHEN FND_API.G_EXC_ERROR THEN
840 ROLLBACK TO ValidateOperation_Pvt;
841 x_return_status := FND_API.G_RET_STS_ERROR;
842 FND_MSG_PUB.Count_And_Get(
843 p_count => x_msg_count,
844 p_data => x_msg_data
845 );
846 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
847 ROLLBACK TO ValidateOperation_Pvt;
848 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
849 FND_MSG_PUB.Count_And_Get(
850 p_count => x_msg_count,
851 p_data => x_msg_data
852 );
853 WHEN OTHERS THEN
854 ROLLBACK TO ValidateOperation_Pvt;
855 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
856 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
857 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
858 END IF;
859 FND_MSG_PUB.Count_And_Get(
860 p_count => x_msg_count,
861 p_data => x_msg_data
862 );
863 End ValidateOperation;
864
865 PROCEDURE CreateOperation(
866 p_api_version IN NUMBER,
867 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
868 p_commit IN VARCHAR2 := FND_API.G_FALSE,
869 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
870 x_return_status IN OUT NOCOPY VARCHAR2,
871 x_msg_count IN OUT NOCOPY NUMBER,
872 x_msg_data IN OUT NOCOPY VARCHAR2,
873 p_operation_rec IN OPERATION_REC_TYPE := G_MISS_OPERATION_REC,
874 x_operation_rec IN OUT NOCOPY OPERATION_REC_TYPE
875 ) is
876 l_api_name CONSTANT VARCHAR2(30) := 'CreateOperation';
877 l_api_version CONSTANT NUMBER := 1.0;
878 l_operation_rec OPERATION_REC_TYPE;
879 l_return_status VARCHAR2(1);
880 l_msg_count NUMBER;
881 l_msg_data VARCHAR2(2000);
882 l_UserId number;
883 l_LoginId number;
884 l_RequestId number;
885 l_ProgramId number;
886 l_ApplicationId number;
887 l_ProgramUpdate date;
888 cursor l_NewOper_csr is
889 Select bom_operation_sequences_s.nextval new_op_seq_id
890 from dual;
891 BEGIN
892 -- Standard Start of API savepoint
893 SAVEPOINT CreateOperation_Pvt;
894 -- Standard call to check for call compatibility.
895 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
896 G_PKG_NAME) THEN
897 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
898 END IF;
899 -- Initialize message list if p_init_msg_list is set to TRUE.
900 IF FND_API.to_Boolean(p_init_msg_list) THEN
901 FND_MSG_PUB.initialize;
902 END IF;
903 -- Initialize API return status to success
904 x_return_status := FND_API.G_RET_STS_SUCCESS;
905
906 -- API body
907 l_operation_rec := p_operation_rec;
908
909 -- initialize record
910
911 For l_NewOper_rec in l_NewOper_csr loop
912 l_operation_rec.operation_sequence_id := l_NewOper_rec.new_op_seq_id;
913 End loop; -- new primary key
914
915 If l_operation_rec.routing_sequence_id = Fnd_Api.G_Miss_Num then
916 l_operation_rec.routing_sequence_id := null;
917 End if;
918
919 If l_operation_rec.operation_seq_num = Fnd_Api.G_Miss_Num then
920 l_operation_rec.operation_seq_num := null;
921 End if;
922
923 If nvl(l_operation_rec.new_operation_seq_num, Fnd_Api.G_Miss_Num) =
924 Fnd_Api.G_Miss_Num then
925 l_operation_rec.new_operation_seq_num := l_operation_rec.operation_seq_num;
926 End if;
927
928 If l_operation_rec.standard_operation_id = Fnd_Api.G_Miss_Num then
929 l_operation_rec.standard_operation_id := null;
930 End if;
931
932 If l_operation_rec.department_id = Fnd_Api.G_Miss_Num then
933 l_operation_rec.department_id := null;
934 End if;
935
939
936 If l_operation_rec.operation_lead_time_percent = Fnd_Api.G_Miss_Num then
937 l_operation_rec.operation_lead_time_percent := null;
938 End if;
940 If l_operation_rec.minimum_transfer_quantity = Fnd_Api.G_Miss_Num then
941 l_operation_rec.minimum_transfer_quantity := null;
942 End if;
943
944 If nvl(l_operation_rec.count_point_type, Fnd_Api.G_Miss_Num) =
945 Fnd_Api.G_Miss_Num then
946 l_operation_rec.count_point_type := 1;
947 End if;
948
949 If l_operation_rec.operation_description = Fnd_Api.G_Miss_Char then
950 l_operation_rec.operation_description := null;
951 End if;
952
953 If nvl(l_operation_rec.effectivity_date, Fnd_Api.G_Miss_Date) =
954 Fnd_Api.G_Miss_Date then
955 l_operation_rec.effectivity_date := trunc(sysdate);
956 End if;
957
958 If nvl(l_operation_rec.new_effectivity_date, Fnd_Api.G_Miss_Date) =
959 Fnd_Api.G_Miss_Date then
960 l_operation_rec.new_effectivity_date := l_operation_rec.effectivity_date;
964 l_operation_rec.disable_date := null;
961 End if;
962
963 If l_operation_rec.disable_date = Fnd_Api.G_Miss_Date then
965 End if;
966
967 If nvl(l_operation_rec.backflush_flag, Fnd_Api.G_Miss_Num) =
968 Fnd_Api.G_Miss_Num then
969 l_operation_rec.backflush_flag := g_yes;
970 End if;
971
972 If nvl(l_operation_rec.option_dependent_flag, Fnd_Api.G_Miss_Num) =
973 Fnd_Api.G_Miss_Num then
974 l_operation_rec.option_dependent_flag := g_no;
975 End if;
976
977 If l_operation_rec.attribute_category = Fnd_Api.G_Miss_Char then
978 l_operation_rec.attribute_category := null;
979 End if;
980
981 If l_operation_rec.attribute1 = Fnd_Api.G_Miss_Char then
982 l_operation_rec.attribute1 := null;
983 End if;
984
985 If l_operation_rec.attribute2 = Fnd_Api.G_Miss_Char then
986 l_operation_rec.attribute2 := null;
987 End if;
988
989 If l_operation_rec.attribute3 = Fnd_Api.G_Miss_Char then
990 l_operation_rec.attribute3 := null;
991 End if;
992
993 If l_operation_rec.attribute4 = Fnd_Api.G_Miss_Char then
994 l_operation_rec.attribute4 := null;
995 End if;
996
997 If l_operation_rec.attribute5 = Fnd_Api.G_Miss_Char then
998 l_operation_rec.attribute5 := null;
999 End if;
1000
1001 If l_operation_rec.attribute6 = Fnd_Api.G_Miss_Char then
1002 l_operation_rec.attribute6 := null;
1003 End if;
1004
1005 If l_operation_rec.attribute7 = Fnd_Api.G_Miss_Char then
1006 l_operation_rec.attribute7 := null;
1007 End if;
1008
1009 If l_operation_rec.attribute8 = Fnd_Api.G_Miss_Char then
1010 l_operation_rec.attribute8 := null;
1011 End if;
1012
1013 If l_operation_rec.attribute9 = Fnd_Api.G_Miss_Char then
1014 l_operation_rec.attribute9 := null;
1015 End if;
1016
1017 If l_operation_rec.attribute10 = Fnd_Api.G_Miss_Char then
1018 l_operation_rec.attribute10 := null;
1019 End if;
1020
1021 If l_operation_rec.attribute11 = Fnd_Api.G_Miss_Char then
1022 l_operation_rec.attribute11 := null;
1023 End if;
1024
1025 If l_operation_rec.attribute12 = Fnd_Api.G_Miss_Char then
1026 l_operation_rec.attribute12 := null;
1027 End if;
1028
1029 If l_operation_rec.attribute13 = Fnd_Api.G_Miss_Char then
1030 l_operation_rec.attribute13 := null;
1031 End if;
1032
1033 If l_operation_rec.attribute14 = Fnd_Api.G_Miss_Char then
1034 l_operation_rec.attribute14 := null;
1035 End if;
1036
1037 If l_operation_rec.attribute15 = Fnd_Api.G_Miss_Char then
1038 l_operation_rec.attribute15 := null;
1039 End if;
1040
1041 If l_operation_rec.assembly_item_id = Fnd_Api.G_Miss_Num then
1042 l_operation_rec.assembly_item_id := null;
1043 End if;
1044
1045 If l_operation_rec.organization_id = Fnd_Api.G_Miss_Num then
1046 l_operation_rec.organization_id := null;
1047 End if;
1048
1049 If l_operation_rec.alternate_routing_designator = Fnd_Api.G_Miss_Char then
1050 l_operation_rec.alternate_routing_designator := null;
1051 End if;
1052
1053 If l_operation_rec.organization_code = Fnd_Api.G_Miss_Char then
1054 l_operation_rec.organization_code := null;
1055 End if;
1056
1057 If l_operation_rec.assembly_item_number = Fnd_Api.G_Miss_Char then
1058 l_operation_rec.assembly_item_number := null;
1059 End if;
1060
1061 If l_operation_rec.department_code = Fnd_Api.G_Miss_Char then
1062 l_operation_rec.department_code := null;
1063 End if;
1064
1065 If l_operation_rec.operation_code = Fnd_Api.G_Miss_Char then
1066 l_operation_rec.operation_code := null;
1067 End if;
1068
1072
1069 If l_operation_rec.operation_type = Fnd_Api.G_Miss_Num then
1070 l_operation_rec.operation_type := null;
1071 End if;
1073 If nvl(l_operation_rec.reference_flag, Fnd_Api.G_Miss_Num) =
1074 Fnd_Api.G_Miss_Num then
1075 l_operation_rec.reference_flag := g_no;
1076 End if;
1077
1078 If l_operation_rec.process_op_seq_id = Fnd_Api.G_Miss_Num then
1079 l_operation_rec.process_op_seq_id := null;
1080 End if;
1081
1082 If l_operation_rec.line_op_seq_id = Fnd_Api.G_Miss_Num then
1083 l_operation_rec.line_op_seq_id := null;
1084 End if;
1085
1086 If l_operation_rec.yield = Fnd_Api.G_Miss_Num then
1087 l_operation_rec.yield := null;
1088 End if;
1089
1090 If l_operation_rec.cumulative_yield = Fnd_Api.G_Miss_Num then
1091 l_operation_rec.cumulative_yield := null;
1092 End if;
1093
1094 If l_operation_rec.reverse_cumulative_yield = Fnd_Api.G_Miss_Num then
1095 l_operation_rec.reverse_cumulative_yield := null;
1096 End if;
1097
1098 If l_operation_rec.labor_time_calc = Fnd_Api.G_Miss_Num then
1099 l_operation_rec.labor_time_calc := null;
1100 End if;
1101
1102 If l_operation_rec.machine_time_calc = Fnd_Api.G_Miss_Num then
1103 l_operation_rec.machine_time_calc := null;
1104 End if;
1105
1106 If l_operation_rec.total_time_calc = Fnd_Api.G_Miss_Num then
1107 l_operation_rec.total_time_calc := null;
1108 End if;
1109
1110 If l_operation_rec.labor_time_user = Fnd_Api.G_Miss_Num then
1111 l_operation_rec.labor_time_user := null;
1112 End if;
1113
1114 If l_operation_rec.machine_time_user = Fnd_Api.G_Miss_Num then
1115 l_operation_rec.machine_time_user := null;
1116 End if;
1117
1118 If l_operation_rec.total_time_user = Fnd_Api.G_Miss_Num then
1119 l_operation_rec.total_time_user := null;
1120 End if;
1121
1122 If l_operation_rec.net_planning_percent = Fnd_Api.G_Miss_Num then
1123 l_operation_rec.net_planning_percent := null;
1124 End if;
1125
1126 If nvl(l_operation_rec.include_in_rollup, Fnd_Api.G_Miss_Num) =
1127 Fnd_Api.G_Miss_Num then
1128 l_operation_rec.include_in_rollup := g_yes;
1129 End if;
1130
1131 If nvl(l_operation_rec.operation_yield_enabled, Fnd_Api.G_Miss_Num) =
1132 Fnd_Api.G_Miss_Num then
1133 l_operation_rec.operation_yield_enabled := g_yes;
1134 End if;
1135
1136 If p_validation_level > FND_API.G_VALID_LEVEL_NONE then
1137 ValidateOperation(
1138 p_api_version => 1,
1139 p_init_msg_list => p_init_msg_list,
1140 p_commit => p_commit,
1141 p_validation_level => p_validation_level,
1142 x_return_status => l_return_status,
1143 x_msg_count => l_msg_count,
1144 x_msg_data => l_msg_data,
1145 p_operation_rec => l_operation_rec,
1146 x_operation_rec => l_operation_rec);
1147 If l_return_status = FND_API.G_RET_STS_ERROR then
1148 Raise FND_API.G_EXC_ERROR;
1152 End if; -- validate before inserting
1149 Elsif l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
1150 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
1151 End if; -- validation error
1153
1154 l_UserId := nvl(Fnd_Global.USER_ID, -1);
1155 l_LoginId := Fnd_Global.LOGIN_ID;
1156 l_RequestId := Fnd_Global.CONC_REQUEST_ID;
1157 l_ProgramId := Fnd_Global.CONC_PROGRAM_ID;
1158 l_ApplicationId := Fnd_Global.PROG_APPL_ID;
1159 -- do not use decode because of implicit data type conversions
1160 If l_RequestId is null then
1161 l_ProgramUpdate := null;
1162 Else
1163 l_ProgramUpdate := sysdate;
1164 End if;
1165
1166 Insert into bom_operation_sequences(
1167 operation_sequence_id,
1168 routing_sequence_id,
1169 operation_seq_num,
1170 last_update_date,
1171 last_updated_by,
1172 creation_date,
1173 created_by,
1174 last_update_login,
1175 standard_operation_id,
1176 department_id,
1177 operation_lead_time_percent,
1178 minimum_transfer_quantity,
1179 count_point_type,
1180 operation_description,
1181 effectivity_date,
1182 disable_date,
1183 backflush_flag,
1184 option_dependent_flag,
1185 attribute_category,
1186 attribute1,
1187 attribute2,
1188 attribute3,
1189 attribute4,
1190 attribute5,
1191 attribute6,
1192 attribute7,
1193 attribute8,
1194 attribute9,
1195 attribute10,
1196 attribute11,
1197 attribute12,
1198 attribute13,
1199 attribute14,
1200 attribute15,
1201 request_id,
1202 program_application_id,
1203 program_id,
1207 process_op_seq_id,
1204 program_update_date,
1205 operation_type,
1206 reference_flag,
1208 line_op_seq_id,
1209 yield,
1210 cumulative_yield,
1211 reverse_cumulative_yield,
1212 labor_time_calc,
1213 machine_time_calc,
1214 total_time_calc,
1215 labor_time_user,
1216 machine_time_user,
1217 total_time_user,
1218 net_planning_percent,
1219 include_in_rollup,
1220 operation_yield_enabled,
1221 implementation_date)
1222 Values(
1223 l_operation_rec.operation_sequence_id,
1224 l_operation_rec.routing_sequence_id,
1225 l_operation_rec.operation_seq_num,
1226 sysdate,
1227 l_UserId,
1228 sysdate,
1229 l_UserId,
1230 l_LoginId,
1231 l_operation_rec.standard_operation_id,
1232 l_operation_rec.department_id,
1233 l_operation_rec.operation_lead_time_percent,
1234 l_operation_rec.minimum_transfer_quantity,
1235 l_operation_rec.count_point_type,
1236 l_operation_rec.operation_description,
1237 l_operation_rec.effectivity_date,
1238 l_operation_rec.disable_date,
1239 l_operation_rec.backflush_flag,
1240 l_operation_rec.option_dependent_flag,
1241 l_operation_rec.attribute_category,
1242 l_operation_rec.attribute1,
1243 l_operation_rec.attribute2,
1244 l_operation_rec.attribute3,
1245 l_operation_rec.attribute4,
1246 l_operation_rec.attribute5,
1247 l_operation_rec.attribute6,
1248 l_operation_rec.attribute7,
1249 l_operation_rec.attribute8,
1250 l_operation_rec.attribute9,
1251 l_operation_rec.attribute10,
1252 l_operation_rec.attribute11,
1253 l_operation_rec.attribute12,
1254 l_operation_rec.attribute13,
1255 l_operation_rec.attribute14,
1256 l_operation_rec.attribute15,
1257 l_RequestId,
1258 l_ApplicationId,
1259 l_ProgramId,
1260 l_ProgramUpdate,
1261 l_operation_rec.operation_type,
1262 l_operation_rec.reference_flag,
1263 l_operation_rec.process_op_seq_id,
1264 l_operation_rec.line_op_seq_id,
1265 l_operation_rec.yield,
1266 l_operation_rec.cumulative_yield,
1267 l_operation_rec.reverse_cumulative_yield,
1268 l_operation_rec.labor_time_calc,
1269 l_operation_rec.machine_time_calc,
1270 l_operation_rec.total_time_calc,
1271 l_operation_rec.labor_time_user,
1272 l_operation_rec.machine_time_user,
1273 l_operation_rec.total_time_user,
1274 l_operation_rec.net_planning_percent,
1275 l_operation_rec.include_in_rollup,
1276 l_operation_rec.operation_yield_enabled,
1277 l_operation_rec.effectivity_date) ;
1278
1279 x_operation_rec := l_operation_rec;
1280 -- End of API body.
1281
1282 -- Standard check of p_commit.
1283 IF FND_API.To_Boolean(p_commit) THEN
1284 COMMIT WORK;
1285 END IF;
1286 -- Standard call to get message count and if count is 1, get message info.
1287 FND_MSG_PUB.Count_And_Get(
1288 p_count => x_msg_count,
1289 p_data => x_msg_data
1290 );
1291 EXCEPTION
1292 WHEN FND_API.G_EXC_ERROR THEN
1293 ROLLBACK TO CreateOperation_Pvt;
1294 x_return_status := FND_API.G_RET_STS_ERROR;
1295 FND_MSG_PUB.Count_And_Get(
1296 p_count => x_msg_count,
1297 p_data => x_msg_data
1298 );
1299 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1300 ROLLBACK TO CreateOperation_Pvt;
1301 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1302 FND_MSG_PUB.Count_And_Get(
1303 p_count => x_msg_count,
1304 p_data => x_msg_data
1305 );
1306 WHEN OTHERS THEN
1307 ROLLBACK TO CreateOperation_Pvt;
1308 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1309 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1310 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1311 END IF;
1312 FND_MSG_PUB.Count_And_Get(
1313 p_count => x_msg_count,
1314 p_data => x_msg_data
1315 );
1316 End CreateOperation;
1317
1318 PROCEDURE UpdateOperation(
1319 p_api_version IN NUMBER,
1320 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1321 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1322 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1323 x_return_status IN OUT NOCOPY VARCHAR2,
1324 x_msg_count IN OUT NOCOPY NUMBER,
1325 x_msg_data IN OUT NOCOPY VARCHAR2,
1326 p_operation_rec IN OPERATION_REC_TYPE := G_MISS_OPERATION_REC,
1327 x_operation_rec IN OUT NOCOPY OPERATION_REC_TYPE
1328 ) is
1329 l_api_name CONSTANT VARCHAR2(30) := 'UpdateOperation';
1330 l_api_version CONSTANT NUMBER := 1.0;
1331 l_operation_rec OPERATION_REC_TYPE;
1332 l_return_status VARCHAR2(1);
1333 l_msg_count NUMBER;
1334 l_msg_data VARCHAR2(2000);
1335 l_UserId NUMBER;
1336 l_LoginId NUMBER;
1337 l_RequestId NUMBER;
1338 l_ProgramId NUMBER;
1339 l_ProgramUpdate DATE;
1340 l_ApplicationId NUMBER;
1344 Select *
1341 cursor l_ExistingOperation_csr(P_OpSeqId number,
1342 P_RtgSeqId number, P_OpType number, P_SeqNum number,
1343 P_EffDate date) is
1345 From bom_operation_sequences bos
1346 Where bos.operation_sequence_id = P_OpSeqId
1347 Or (bos.routing_sequence_id = P_RtgSeqId and
1348 nvl(bos.operation_type, g_event) =
1349 nvl(P_OpType, g_event) and
1350 bos.operation_seq_num = P_SeqNum and
1351 bos.effectivity_date = decode(P_OpType,
1352 g_process, bos.effectivity_date,
1353 g_LineOp, bos.effectivity_date,
1354 P_EffDate));
1355 l_OperFound BOOLEAN := false;
1356 BEGIN
1357 -- Standard Start of API savepoint
1358 SAVEPOINT UpdateOperation_Pvt;
1359 -- Standard call to check for call compatibility.
1360 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
1361 G_PKG_NAME) THEN
1362 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1363 END IF;
1364 -- Initialize message list if p_init_msg_list is set to TRUE.
1365 IF FND_API.to_Boolean(p_init_msg_list) THEN
1366 FND_MSG_PUB.initialize;
1367 END IF;
1368 -- Initialize API return status to success
1369 x_return_status := FND_API.G_RET_STS_SUCCESS;
1370
1371 -- API body
1372 l_operation_rec := p_operation_rec;
1373
1374 If p_validation_level = FND_API.G_VALID_LEVEL_FULL then
1375 AssignOperation(
1376 p_api_version => 1,
1377 p_init_msg_list => p_init_msg_list,
1378 p_commit => p_commit,
1379 p_validation_level => p_validation_level,
1380 x_return_status => l_return_status,
1384 x_operation_rec => l_operation_rec
1381 x_msg_count => l_msg_count,
1382 x_msg_data => l_msg_data,
1383 p_operation_rec => l_operation_rec,
1385 );
1386 If l_return_status = FND_API.G_RET_STS_ERROR then
1387 Raise FND_API.G_EXC_ERROR;
1388 Elsif l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
1389 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
1390 End if; -- assign error
1391 End If; -- assign
1392
1393 -- populate unspecified values
1394
1395 l_OperFound := false;
1396 For l_OldOper_rec in l_ExistingOperation_csr(
1397 P_OpSeqId => l_operation_rec.operation_sequence_id,
1398 P_RtgSeqId => l_operation_rec.routing_sequence_id,
1399 P_OpType => l_operation_rec.operation_type,
1400 P_SeqNum => l_operation_rec.operation_seq_num,
1401 P_EffDate => l_operation_rec.effectivity_date) loop
1402 l_OperFound := true;
1403
1404 -- non updatable
1405 l_operation_rec.operation_sequence_id :=
1406 l_OldOper_rec.operation_sequence_id;
1407 l_operation_rec.routing_sequence_id := l_OldOper_rec.routing_sequence_id;
1408 l_operation_rec.operation_type := l_OldOper_rec.operation_type;
1409
1410 l_operation_rec.operation_seq_num := l_OldOper_rec.operation_seq_num;
1411 If l_operation_rec.new_operation_seq_num = Fnd_Api.G_Miss_Num then
1412 l_operation_rec.new_operation_seq_num :=
1413 l_OldOper_rec.operation_seq_num;
1414 End if;
1415
1416 If l_operation_rec.standard_operation_id = Fnd_Api.G_Miss_Num then
1417 l_operation_rec.standard_operation_id :=
1418 l_OldOper_rec.standard_operation_id;
1419 End if;
1420
1421 If l_operation_rec.department_id = Fnd_Api.G_Miss_Num then
1422 l_operation_rec.department_id := l_OldOper_rec.department_id;
1423 End if;
1424
1425 If l_operation_rec.operation_lead_time_percent = Fnd_Api.G_Miss_Num then
1426 l_operation_rec.operation_lead_time_percent :=
1427 l_OldOper_rec.operation_lead_time_percent;
1428 End if;
1429
1430 If l_operation_rec.minimum_transfer_quantity = Fnd_Api.G_Miss_Num then
1431 l_operation_rec.minimum_transfer_quantity :=
1432 l_OldOper_rec.minimum_transfer_quantity;
1433 End if;
1434
1435 If l_operation_rec.count_point_type = Fnd_Api.G_Miss_Num then
1436 l_operation_rec.count_point_type := l_OldOper_rec.count_point_type;
1437 End if;
1438
1439 If l_operation_rec.operation_description = Fnd_Api.G_Miss_Char then
1440 l_operation_rec.operation_description :=
1441 l_OldOper_rec.operation_description;
1442 End if;
1443
1444 l_operation_rec.effectivity_date := l_OldOper_rec.effectivity_date;
1445 If l_operation_rec.new_effectivity_date = Fnd_Api.G_Miss_Date then
1446 l_operation_rec.new_effectivity_date :=
1447 l_OldOper_rec.effectivity_date;
1448 End if;
1449
1450 If l_operation_rec.disable_date = Fnd_Api.G_Miss_Date then
1451 l_operation_rec.disable_date := l_OldOper_rec.disable_date;
1452 End if;
1453
1454 If l_operation_rec.backflush_flag = Fnd_Api.G_Miss_Num then
1455 l_operation_rec.backflush_flag := l_OldOper_rec.backflush_flag;
1456 End if;
1457
1458 If l_operation_rec.option_dependent_flag = Fnd_Api.G_Miss_Num then
1459 l_operation_rec.option_dependent_flag :=
1460 l_OldOper_rec.option_dependent_flag;
1461 End if;
1462
1463 If l_operation_rec.attribute_category = Fnd_Api.G_Miss_Char then
1464 l_operation_rec.attribute_category := l_OldOper_rec.attribute_category;
1465 End if;
1466
1467 If l_operation_rec.attribute1 = Fnd_Api.G_Miss_Char then
1468 l_operation_rec.attribute1 := l_OldOper_rec.attribute1;
1469 End if;
1470
1471 If l_operation_rec.attribute2 = Fnd_Api.G_Miss_Char then
1472 l_operation_rec.attribute2 := l_OldOper_rec.attribute2;
1473 End if;
1474
1475 If l_operation_rec.attribute3 = Fnd_Api.G_Miss_Char then
1476 l_operation_rec.attribute3 := l_OldOper_rec.attribute3;
1477 End if;
1478
1479 If l_operation_rec.attribute4 = Fnd_Api.G_Miss_Char then
1480 l_operation_rec.attribute4 := l_OldOper_rec.attribute4;
1481 End if;
1482
1483 If l_operation_rec.attribute5 = Fnd_Api.G_Miss_Char then
1484 l_operation_rec.attribute5 := l_OldOper_rec.attribute5;
1485 End if;
1486
1487 If l_operation_rec.attribute6 = Fnd_Api.G_Miss_Char then
1488 l_operation_rec.attribute6 := l_OldOper_rec.attribute6;
1489 End if;
1490
1491 If l_operation_rec.attribute7 = Fnd_Api.G_Miss_Char then
1492 l_operation_rec.attribute7 := l_OldOper_rec.attribute7;
1493 End if;
1494
1495 If l_operation_rec.attribute8 = Fnd_Api.G_Miss_Char then
1496 l_operation_rec.attribute8 := l_OldOper_rec.attribute8;
1497 End if;
1498
1499 If l_operation_rec.attribute9 = Fnd_Api.G_Miss_Char then
1500 l_operation_rec.attribute9 := l_OldOper_rec.attribute9;
1501 End if;
1502
1503 If l_operation_rec.attribute10 = Fnd_Api.G_Miss_Char then
1504 l_operation_rec.attribute10 := l_OldOper_rec.attribute10;
1505 End if;
1506
1507 If l_operation_rec.attribute11 = Fnd_Api.G_Miss_Char then
1508 l_operation_rec.attribute11 := l_OldOper_rec.attribute11;
1509 End if;
1510
1511 If l_operation_rec.attribute12 = Fnd_Api.G_Miss_Char then
1512 l_operation_rec.attribute12 := l_OldOper_rec.attribute12;
1513 End if;
1514
1515 If l_operation_rec.attribute13 = Fnd_Api.G_Miss_Char then
1516 l_operation_rec.attribute13 := l_OldOper_rec.attribute13;
1517 End if;
1518
1522
1519 If l_operation_rec.attribute14 = Fnd_Api.G_Miss_Char then
1520 l_operation_rec.attribute14 := l_OldOper_rec.attribute14;
1521 End if;
1523 If l_operation_rec.attribute15 = Fnd_Api.G_Miss_Char then
1524 l_operation_rec.attribute15 := l_OldOper_rec.attribute15;
1525 End if;
1526
1527 If l_operation_rec.reference_flag = Fnd_Api.G_Miss_Num then
1528 l_operation_rec.reference_flag := l_OldOper_rec.reference_flag;
1529 End if;
1530
1531 If l_operation_rec.process_op_seq_id = Fnd_Api.G_Miss_Num then
1532 l_operation_rec.process_op_seq_id := l_OldOper_rec.process_op_seq_id;
1533 End if;
1534
1535 If l_operation_rec.line_op_seq_id = Fnd_Api.G_Miss_Num then
1536 l_operation_rec.line_op_seq_id := l_OldOper_rec.line_op_seq_id;
1537 End if;
1538
1539 If l_operation_rec.yield = Fnd_Api.G_Miss_Num then
1540 l_operation_rec.yield := l_OldOper_rec.yield;
1541 End if;
1542
1543 If l_operation_rec.cumulative_yield = Fnd_Api.G_Miss_Num then
1544 l_operation_rec.cumulative_yield := l_OldOper_rec.cumulative_yield;
1545 End if;
1546
1547 If l_operation_rec.reverse_cumulative_yield = Fnd_Api.G_Miss_Num then
1548 l_operation_rec.reverse_cumulative_yield :=
1549 l_OldOper_rec.reverse_cumulative_yield;
1550 End if;
1551
1552 If l_operation_rec.labor_time_calc = Fnd_Api.G_Miss_Num then
1553 l_operation_rec.labor_time_calc := l_OldOper_rec.labor_time_calc;
1554 End if;
1555
1556 If l_operation_rec.machine_time_calc = Fnd_Api.G_Miss_Num then
1557 l_operation_rec.machine_time_calc := l_OldOper_rec.machine_time_calc;
1558 End if;
1559
1560 If l_operation_rec.total_time_calc = Fnd_Api.G_Miss_Num then
1561 l_operation_rec.total_time_calc := l_OldOper_rec.total_time_calc;
1562 End if;
1563
1564 If l_operation_rec.labor_time_user = Fnd_Api.G_Miss_Num then
1565 l_operation_rec.labor_time_user := l_OldOper_rec.labor_time_user;
1566 End if;
1567
1568 If l_operation_rec.machine_time_user = Fnd_Api.G_Miss_Num then
1569 l_operation_rec.machine_time_user := l_OldOper_rec.machine_time_user;
1570 End if;
1571
1572 If l_operation_rec.total_time_user = Fnd_Api.G_Miss_Num then
1573 l_operation_rec.total_time_user := l_OldOper_rec.total_time_user;
1574 End if;
1575
1576 If l_operation_rec.net_planning_percent = Fnd_Api.G_Miss_Num then
1577 l_operation_rec.net_planning_percent :=
1578 l_OldOper_rec.net_planning_percent;
1579 End if;
1580
1581 If l_operation_rec.include_in_rollup = Fnd_Api.G_Miss_Num then
1582 l_operation_rec.include_in_rollup := l_OldOper_rec.include_in_rollup;
1583 End if;
1584
1585 If l_operation_rec.operation_yield_enabled = Fnd_Api.G_Miss_Num then
1586 l_operation_rec.operation_yield_enabled := l_OldOper_rec.operation_yield_enabled;
1587 End if;
1588
1589 End loop; -- get old values
1590
1591 If not l_OperFound then
1592 Fnd_Message.Set_Name('BOM', 'BOM_INVALID_OPERATION');
1593 FND_MSG_PUB.Add;
1594 Raise FND_API.G_EXC_ERROR;
1595 End if; -- missing operation
1596
1597 If p_validation_level > FND_API.G_VALID_LEVEL_NONE then
1598 ValidateOperation(
1599 p_api_version => 1,
1600 p_init_msg_list => p_init_msg_list,
1601 p_commit => p_commit,
1602 p_validation_level => FND_API.G_VALID_LEVEL_NONE,
1603 x_return_status => l_return_status,
1604 x_msg_count => l_msg_count,
1605 x_msg_data => l_msg_data,
1606 p_operation_rec => l_operation_rec,
1607 x_operation_rec => l_operation_rec
1608 );
1609 If l_return_status = FND_API.G_RET_STS_ERROR then
1610 Raise FND_API.G_EXC_ERROR;
1611 Elsif l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
1612 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
1613 End if; -- validation error
1614 End If; -- validation
1615
1616 -- update operation
1617
1618 l_UserId := nvl(Fnd_Global.USER_ID, -1);
1619 l_LoginId := Fnd_Global.LOGIN_ID;
1620 l_RequestId := Fnd_Global.CONC_REQUEST_ID;
1621 l_ProgramId := Fnd_Global.CONC_PROGRAM_ID;
1622 l_ApplicationId := Fnd_Global.PROG_APPL_ID;
1623 -- do not use decode because of implicit data type conversions
1624 If l_RequestId is null then
1625 l_ProgramUpdate := null;
1626 Else
1627 l_ProgramUpdate := sysdate;
1628 End if;
1629
1630 update bom_operation_sequences set
1631 operation_seq_num = l_operation_rec.new_operation_seq_num,
1632 last_update_date = sysdate,
1633 last_updated_by = l_UserId,
1634 creation_date = sysdate,
1635 created_by = l_UserId,
1636 last_update_login = l_LoginId,
1637 standard_operation_id = l_operation_rec.standard_operation_id,
1638 department_id = l_operation_rec.department_id,
1639 operation_lead_time_percent =
1640 l_operation_rec.operation_lead_time_percent,
1641 minimum_transfer_quantity = l_operation_rec.minimum_transfer_quantity,
1642 count_point_type = l_operation_rec.count_point_type,
1643 operation_description = l_operation_rec.operation_description,
1644 effectivity_date = l_operation_rec.new_effectivity_date,
1645 disable_date = l_operation_rec.disable_date,
1646 backflush_flag = l_operation_rec.backflush_flag,
1647 option_dependent_flag = l_operation_rec.option_dependent_flag,
1648 attribute_category = l_operation_rec.attribute_category,
1649 attribute1 = l_operation_rec.attribute1,
1650 attribute2 = l_operation_rec.attribute2,
1654 attribute6 = l_operation_rec.attribute6,
1651 attribute3 = l_operation_rec.attribute3,
1652 attribute4 = l_operation_rec.attribute4,
1653 attribute5 = l_operation_rec.attribute5,
1655 attribute7 = l_operation_rec.attribute7,
1656 attribute8 = l_operation_rec.attribute8,
1657 attribute9 = l_operation_rec.attribute9,
1658 attribute10 = l_operation_rec.attribute10,
1659 attribute11 = l_operation_rec.attribute11,
1660 attribute12 = l_operation_rec.attribute12,
1661 attribute13 = l_operation_rec.attribute13,
1662 attribute14 = l_operation_rec.attribute14,
1663 attribute15 = l_operation_rec.attribute15,
1664 request_id = l_RequestId,
1665 program_application_id = l_ApplicationId,
1666 program_id = l_ProgramId,
1667 program_update_date = l_ProgramUpdate,
1668 reference_flag = l_operation_rec.reference_flag,
1669 process_op_seq_id = l_operation_rec.process_op_seq_id,
1670 line_op_seq_id = l_operation_rec.line_op_seq_id,
1671 yield = l_operation_rec.yield,
1672 cumulative_yield = l_operation_rec.cumulative_yield,
1673 reverse_cumulative_yield = l_operation_rec.reverse_cumulative_yield,
1674 labor_time_calc = l_operation_rec.labor_time_calc,
1675 machine_time_calc = l_operation_rec.machine_time_calc,
1676 total_time_calc = l_operation_rec.total_time_calc,
1677 labor_time_user = l_operation_rec.labor_time_user,
1678 machine_time_user = l_operation_rec.machine_time_user,
1679 total_time_user = l_operation_rec.total_time_user,
1680 net_planning_percent = l_operation_rec.net_planning_percent,
1681 include_in_rollup = l_operation_rec.include_in_rollup,
1682 operation_yield_enabled = l_operation_rec.operation_yield_enabled
1683 Where operation_sequence_id = l_operation_rec.operation_sequence_id
1684 Or (routing_sequence_id = l_operation_rec.routing_sequence_id
1685 and nvl(operation_type, g_event) =
1686 nvl(l_operation_rec.operation_type, g_event)
1687 and operation_seq_num = l_operation_rec.operation_seq_num
1688 and effectivity_date = decode(l_operation_rec.operation_type,
1689 g_process, effectivity_date,
1690 g_LineOp, effectivity_date,
1691 l_operation_rec.effectivity_date));
1692
1693 --bugFix 1690706 Begin
1694 UPDATE BOM_INVENTORY_COMPONENTS bic SET
1695 bic.OPERATION_LEAD_TIME_PERCENT = l_operation_rec.operation_lead_time_percent
1696 WHERE bic.OPERATION_SEQ_NUM = l_operation_rec.new_operation_seq_num
1697 and bic.BILL_SEQUENCE_ID =
1698 (select bom.BILL_SEQUENCE_ID
1699 from BOM_BILL_OF_MATERIALS bom,
1700 BOM_OPERATIONAL_ROUTINGS bor
1701 where bor.routing_sequence_id = l_operation_rec.routing_sequence_id
1702 and nvl(bor.alternate_routing_designator,'NONE') =
1703 nvl(bom.ALTERNATE_BOM_DESIGNATOR,'NONE')
1704 and bom.ASSEMBLY_ITEM_ID = bor.assembly_item_id
1705 and bom.ORGANIZATION_ID = bor.organization_id
1706 );
1707 --bugFix 1690706 End
1708 x_operation_rec := l_operation_rec;
1709 -- End of API body.
1710
1711 -- Standard check of p_commit.
1712 IF FND_API.To_Boolean( p_commit ) THEN
1713 COMMIT WORK;
1714 END IF;
1715 -- Standard call to get message count and if count is 1, get message info.
1716 FND_MSG_PUB.Count_And_Get(
1717 p_count => x_msg_count,
1718 p_data => x_msg_data
1719 );
1720 EXCEPTION
1721 WHEN FND_API.G_EXC_ERROR THEN
1722 ROLLBACK TO UpdateOperation_Pvt;
1723 x_return_status := FND_API.G_RET_STS_ERROR;
1724 FND_MSG_PUB.Count_And_Get(
1725 p_count => x_msg_count,
1726 p_data => x_msg_data
1727 );
1728 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1729 ROLLBACK TO UpdateOperation_Pvt;
1730 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1731 FND_MSG_PUB.Count_And_Get(
1732 p_count => x_msg_count,
1733 p_data => x_msg_data
1734 );
1735 WHEN OTHERS THEN
1736 ROLLBACK TO UpdateOperation_Pvt;
1737 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1738 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1739 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1740 END IF;
1741 FND_MSG_PUB.Count_And_Get(
1742 p_count => x_msg_count,
1743 p_data => x_msg_data
1744 );
1745 End UpdateOperation;
1746
1747 PROCEDURE DeleteOperation(
1748 p_api_version IN NUMBER,
1749 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1750 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1751 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1752 x_return_status IN OUT NOCOPY VARCHAR2,
1753 x_msg_count IN OUT NOCOPY NUMBER,
1754 x_msg_data IN OUT NOCOPY VARCHAR2,
1755 p_delete_group IN VARCHAR2,
1756 p_description IN VARCHAR2 := Null,
1757 p_operation_rec IN OPERATION_REC_TYPE := G_MISS_OPERATION_REC,
1758 x_operation_rec IN OUT NOCOPY OPERATION_REC_TYPE
1759 ) is
1760 l_api_name CONSTANT VARCHAR2(30) := 'DeleteOperation';
1761 l_api_version CONSTANT NUMBER := 1.0;
1762 l_operation_rec OPERATION_REC_TYPE;
1763 l_DeleteGrpSeqId number := null;
1764 l_return_status VARCHAR2(1);
1765 l_msg_count NUMBER;
1766 l_msg_data VARCHAR2(2000);
1767 l_UserId number;
1768 cursor l_ExistingOperation_csr(P_OpSeqId number,
1769 P_RtgSeqId number, P_OpType number, P_SeqNum number,
1770 P_EffDate date) is
1771 Select bos.operation_sequence_id,
1772 bor.routing_sequence_id,
1773 bor.assembly_item_id,
1774 bor.organization_id,
1775 bor.alternate_routing_designator,
1776 bor.routing_type
1777 From bom_operational_routings bor,
1781 Or
1778 bom_operation_sequences bos
1779 Where bor.routing_sequence_id = bos.routing_sequence_id
1780 and (bos.operation_sequence_id = P_OpSeqId
1782 (bos.routing_sequence_id = P_RtgSeqId and
1783 nvl(bos.operation_type, g_event) =
1784 nvl(P_OpType, g_event) and
1785 bos.operation_seq_num = P_SeqNum and
1786 bos.effectivity_date = decode(P_OpType,
1787 g_process, bos.effectivity_date,
1788 g_LineOp, bos.effectivity_date,
1789 P_EffDate))
1790 );
1791 l_OperFound BOOLEAN := false;
1792 cursor l_group_csr(P_OrgId number) is
1793 Select delete_group_sequence_id
1794 From bom_delete_groups
1795 Where delete_group_name = p_delete_group
1796 And organization_id = P_OrgId;
1797 l_operation constant number := 5; -- delete type
1798 l_ReturnCode number;
1799 BEGIN
1800 -- Standard Start of API savepoint
1801 SAVEPOINT DeleteOperation_Pvt;
1802 -- Standard call to check for call compatibility.
1803 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
1804 G_PKG_NAME) THEN
1805 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1806 END IF;
1807 -- Initialize message list if p_init_msg_list is set to TRUE.
1808 IF FND_API.to_Boolean(p_init_msg_list) THEN
1809 FND_MSG_PUB.initialize;
1810 END IF;
1811 -- Initialize API return status to success
1812 x_return_status := FND_API.G_RET_STS_SUCCESS;
1813
1814 -- API body
1815 l_operation_rec := p_operation_rec;
1816 If p_validation_level = FND_API.G_VALID_LEVEL_FULL then
1817 AssignOperation(
1818 p_api_version => 1,
1819 p_init_msg_list => p_init_msg_list,
1820 p_commit => p_commit,
1821 p_validation_level => p_validation_level,
1822 x_return_status => l_return_status,
1823 x_msg_count => l_msg_count,
1824 x_msg_data => l_msg_data,
1825 p_operation_rec => l_operation_rec,
1826 x_operation_rec => l_operation_rec
1827 );
1828 If l_return_status = FND_API.G_RET_STS_ERROR then
1829 Raise FND_API.G_EXC_ERROR;
1830 Elsif l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
1831 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
1832 End if; -- assign error
1833 End If; -- assign
1834
1835 l_DeleteGrpSeqId := null;
1836 For l_DelGrp_rec in l_group_csr(
1837 P_OrgId => l_operation_rec.organization_id) loop
1838 l_DeleteGrpSeqId := l_DelGrp_rec.delete_group_sequence_id;
1839 End loop; -- get existing delete group
1840
1841 l_UserId := nvl(Fnd_Global.USER_ID, -1);
1842
1843 l_OperFound := false;
1844 For l_OldOper_rec in l_ExistingOperation_csr(
1845 P_OpSeqId => l_operation_rec.operation_sequence_id,
1846 P_RtgSeqId => l_operation_rec.routing_sequence_id,
1847 P_OpType => l_operation_rec.operation_type,
1848 P_SeqNum => l_operation_rec.operation_seq_num,
1849 P_EffDate => l_operation_rec.effectivity_date) loop
1850 l_OperFound := true; -- old operation found
1851 l_ReturnCode := MODAL_DELETE.DELETE_MANAGER_OI(
1852 new_group_seq_id => l_DeleteGrpSeqId,
1853 name => p_delete_group,
1854 group_desc => p_description,
1855 org_id => l_OldOper_rec.organization_id,
1856 bom_or_eng => l_OldOper_rec.routing_type,
1857 del_type => l_operation,
1858 ent_bill_seq_id => null,
1859 ent_rtg_seq_id => l_OldOper_rec.routing_sequence_id,
1860 ent_inv_item_id => l_OldOper_rec.assembly_item_id,
1861 ent_alt_designator => l_OldOper_rec.alternate_routing_designator,
1862 ent_comp_seq_id => null,
1863 ent_op_seq_id => l_OldOper_rec.operation_sequence_id,
1864 user_id => l_UserId,
1865 err_text => l_msg_data
1866 );
1867 If l_ReturnCode <> 0 then
1868 Fnd_Msg_Pub.Add_Exc_Msg (
1869 p_pkg_name => 'MODAL_DELETE',
1870 p_procedure_name => 'DELETE_MANAGER_OI',
1871 p_error_text => l_msg_data
1872 );
1873 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
1874 End if; -- SQL error in modal delete
1875 End loop; -- Add to delete group
1876
1877 If not l_OperFound then
1878 Fnd_Message.Set_Name('BOM', 'BOM_INVALID_OPERATION');
1879 FND_MSG_PUB.Add;
1880 Raise FND_API.G_EXC_ERROR;
1881 End if; -- missing operation
1882
1883 x_operation_rec := l_operation_rec;
1884 -- End of API body.
1885
1886 -- Standard check of p_commit.
1887 IF FND_API.To_Boolean(p_commit) THEN
1888 COMMIT WORK;
1889 END IF;
1890 -- Standard call to get message count and if count is 1, get message info.
1891 FND_MSG_PUB.Count_And_Get(
1892 p_count => x_msg_count,
1893 p_data => x_msg_data
1894 );
1895 EXCEPTION
1896 WHEN FND_API.G_EXC_ERROR THEN
1897 ROLLBACK TO DeleteOperation_Pvt;
1898 x_return_status := FND_API.G_RET_STS_ERROR;
1899 FND_MSG_PUB.Count_And_Get(
1900 p_count => x_msg_count,
1901 p_data => x_msg_data
1902 );
1903 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1904 ROLLBACK TO DeleteOperation_Pvt;
1905 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1906 FND_MSG_PUB.Count_And_Get(
1907 p_count => x_msg_count,
1908 p_data => x_msg_data
1909 );
1910 WHEN OTHERS THEN
1911 ROLLBACK TO DeleteOperation_Pvt;
1912 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1913 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1917 p_count => x_msg_count,
1914 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1915 END IF;
1916 FND_MSG_PUB.Count_And_Get(
1918 p_data => x_msg_data
1919 );
1920 End DeleteOperation;
1921
1922 END BOM_Operation_Pvt;