[Home] [Help]
PACKAGE BODY: APPS.BOM_OPRESOURCE_PVT
Source
1 PACKAGE BODY Bom_OpResource_Pvt AS
2 -- $Header: BOMVRESB.pls 120.2.12010000.2 2008/11/14 16:45:18 snandana ship $
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):='Bom_OpResource_Pvt';
5 g_event constant number := 1;
6 g_yes constant number := 1;
7 g_no constant number := 2;
8
9 PROCEDURE AssignResource(
10 p_api_version IN NUMBER,
11 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
12 p_commit IN VARCHAR2 := FND_API.G_FALSE,
13 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
14 x_return_status IN OUT NOCOPY VARCHAR2,
15 x_msg_count IN OUT NOCOPY NUMBER,
16 x_msg_data IN OUT NOCOPY VARCHAR2,
17 p_resource_rec IN RESOURCE_REC_TYPE := G_MISS_RESOURCE_REC,
18 x_resource_rec IN OUT NOCOPY RESOURCE_REC_TYPE
19 ) is
20 l_api_name CONSTANT VARCHAR2(30) := 'AssignResource';
21 l_api_version CONSTANT NUMBER := 1.0;
22 l_resource_rec RESOURCE_REC_TYPE;
23 l_ret_code NUMBER;
24 l_err_text varchar2(2000);
25 cursor l_assy_csr(P_RtgSeqId number) is
26 Select assembly_item_id,
27 organization_id,
28 alternate_routing_designator
29 From bom_operational_routings
30 Where routing_sequence_id = P_RtgSeqId;
31 cursor l_parameter_csr(P_Code varchar2) is
32 Select organization_id
33 From mtl_parameters
34 Where organization_code = P_Code;
35 cursor l_routing_csr(P_AssyItemId number, P_OrgId number,
36 P_Alternate varchar2) is
37 select routing_sequence_id
38 from bom_operational_routings
39 where organization_id = P_OrgId
40 and assembly_item_id = P_AssyItemId
41 and nvl(alternate_routing_designator, 'Primary Alternate') =
42 nvl(P_Alternate, 'Primary Alternate');
43 cursor l_event_csr(P_OpSeqId number) is
44 Select operation_type,
45 reference_flag
46 From bom_operation_sequences
47 Where operation_sequence_id = P_OpSeqId;
48 l_EventFound BOOLEAN := FALSE;
49 cursor l_oper_csr(P_RtgSeqId number, P_SeqNum number, P_EffDate Date)
50 is
51 select operation_sequence_id
52 from bom_operation_sequences
53 where routing_sequence_id = P_RtgSeqId
54 and operation_seq_num = P_SeqNum
55 /* Bug # 1376700 */
56 and trunc(effectivity_date) = trunc(P_EffDate)
57 and nvl(operation_type, g_event) = g_event
58 and nvl(reference_flag, g_no) = g_no;
59 cursor l_NonOpResource_csr(P_Code varchar2, P_OrgId number) is
60 select resource_id
61 from bom_resources
62 where resource_code = P_Code
63 and organization_id = P_OrgId;
64 cursor l_Activity_csr(P_Code varchar2, P_OrgId number) is
65 select activity_id
66 from cst_activities
67 where activity = P_Code
68 and nvl(organization_id, P_OrgId) = P_OrgId;
69 cursor l_defaults_csr(P_OpSeqId number, P_ResourceId number) is
70 select br.default_basis_type,
71 br.default_activity_id,
72 decode(bd.location_id,
73 NULL, decode(br.AUTOCHARGE_TYPE,
74 NULL, 2,
75 3, 2,
76 4, 2,
77 br.AUTOCHARGE_TYPE),
78 nvl(br.AUTOCHARGE_TYPE, 2)) default_autocharge,
79 br.standard_rate_flag
80 from bom_resources br,
81 bom_departments bd,
82 bom_operation_sequences bos
83 where br.resource_id = P_ResourceId
84 and bos.operation_sequence_id = P_OpSeqId
85 and bd.department_id = bos.department_id;
86
87 BEGIN
88 -- Standard call to check for call compatibility.
89 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
90 G_PKG_NAME) THEN
91 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
92 END IF;
93 -- Initialize message list if p_init_msg_list is set to TRUE.
94 IF FND_API.to_Boolean(p_init_msg_list) THEN
95 FND_MSG_PUB.initialize;
96 END IF;
97 -- Initialize API return status to success
98 x_return_status := FND_API.G_RET_STS_SUCCESS;
99
100 -- API body
101 l_resource_rec := p_resource_rec;
102
103 -- get routing info
104 If nvl(l_resource_rec.routing_sequence_id, FND_API.G_MISS_NUM) <>
105 FND_API.G_MISS_NUM then
106 For l_assy_rec in l_assy_csr(
107 P_RtgSeqId => l_resource_rec.routing_sequence_id) loop
108 l_resource_rec.assembly_item_id := l_assy_rec.assembly_item_id;
109 l_resource_rec.organization_id := l_assy_rec.organization_id;
110 l_resource_rec.alternate_routing_designator :=
111 l_assy_rec.alternate_routing_designator;
112 End loop;
113 End if; -- check routing
114
115 -- set organization id
116
117 If nvl(l_resource_rec.organization_code, FND_API.G_MISS_CHAR) <>
118 FND_API.G_MISS_CHAR then
119 l_resource_rec.organization_id := null;
120 For l_parameter_rec in l_parameter_csr(
121 P_Code => l_resource_rec.organization_code) loop
122 l_resource_rec.organization_id := l_parameter_rec.organization_id;
123 End loop;
124 If l_resource_rec.organization_id is null then
125 Fnd_Message.Set_Name('BOM', 'BOM_ORG_ID_MISSING');
126 FND_MSG_PUB.Add;
127 raise FND_API.G_EXC_ERROR;
128 End if; -- invalid org
129 End if; -- organization code
130
131 -- get assembly item number
132 If nvl(l_resource_rec.Assembly_Item_Number, FND_API.G_MISS_CHAR) <>
133 FND_API.G_MISS_CHAR then
134 l_ret_code := INVPUOPI.mtl_pr_trans_prod_item(
135 org_id => l_resource_rec.organization_id,
136 item_number_in => l_resource_rec.assembly_item_number,
137 item_id_out => l_resource_rec.assembly_item_id,
138 err_text => l_err_text);
139 if l_ret_code <> 0 then
140 Fnd_Message.Set_Name('BOM', 'BOM_ASSY_ITEM_MISSING');
141 FND_MSG_PUB.Add;
142 raise FND_API.G_EXC_ERROR;
143 end if; -- parse failed
144 end if; -- assembly item number
145
146 -- null routing sequence id
147 If nvl(l_resource_rec.routing_sequence_id, FND_API.G_MISS_NUM) =
148 FND_API.G_MISS_NUM then
149 If l_resource_rec.alternate_routing_designator = FND_API.G_MISS_CHAR then
150 l_resource_rec.alternate_routing_designator := null;
151 End if;
152 For l_routing_rec in l_routing_csr(
153 P_AssyItemId => l_resource_rec.assembly_item_id,
154 P_OrgId => l_resource_rec.organization_id,
155 P_Alternate => l_resource_rec.alternate_routing_designator) loop
156 l_resource_rec.routing_sequence_id :=
157 l_routing_rec.routing_sequence_id;
158 End loop;
159 End if; -- get routing sequence id
160
161 -- Can only be associated with non-referenced operations of type Event
162 If nvl(l_resource_rec.operation_sequence_id, Fnd_Api.G_Miss_Num) <>
163 Fnd_Api.G_Miss_Num then
164 l_EventFound := FALSE;
165 For l_event_rec in l_event_csr(
166 P_OpSeqId => l_resource_rec.operation_sequence_id) loop
167 l_EventFound := TRUE;
168 If l_event_rec.reference_flag = g_yes or
169 l_event_rec.operation_type <> g_event then
170 Fnd_Message.Set_Name('BOM', 'BOM_EVENT_RESOURCE');
171 FND_MSG_PUB.Add;
172 Raise FND_API.G_EXC_ERROR;
173 End if;
174 End loop;
175 If not l_EventFound then
176 Fnd_Message.Set_Name('BOM', 'BOM_OP_SEQ_INVALID');
177 FND_MSG_PUB.Add;
178 Raise FND_API.G_EXC_ERROR;
179 End if; -- invalid op seq id
180 End if; -- non-referenced event?
181
182 -- Get operation sequence id
183 If nvl(l_resource_rec.operation_sequence_id, Fnd_Api.G_Miss_Num) =
184 Fnd_Api.G_Miss_Num then
185 For l_operation_rec in l_oper_csr(
186 P_RtgSeqId => l_resource_rec.routing_sequence_id,
187 P_SeqNum => l_resource_rec.operation_seq_num,
188 P_EffDate => l_resource_rec.effectivity_date) loop
189 l_resource_rec.operation_sequence_id :=
190 l_operation_rec.operation_sequence_id;
191 End loop;
192 End if; -- get operation
193 If nvl(l_resource_rec.operation_sequence_id, Fnd_Api.G_Miss_Num) =
194 Fnd_Api.G_Miss_Num then
195 -- operation not found (must also be non-referenced event)
196 Fnd_Message.Set_Name('BOM', 'BOM_OP_SEQ_INVALID');
197 FND_MSG_PUB.Add;
198 Raise FND_API.G_EXC_ERROR;
199 End if;
200
201 -- Get resource_id
202 If nvl(l_resource_rec.resource_code, Fnd_Api.G_Miss_Char) <>
203 Fnd_Api.G_Miss_Char then
204 l_resource_rec.resource_id := null;
205 For l_SetUpResource_rec in l_NonOpResource_csr(
206 P_Code => l_resource_rec.resource_code,
207 P_OrgId =>l_resource_rec.organization_id) loop
208 l_resource_rec.resource_id := l_SetUpResource_rec.resource_id;
209 End loop;
210 If l_resource_rec.resource_id is null then
211 Fnd_Message.Set_Name('BOM', 'BOM_RESOURCE_ID_MISSING');
212 FND_MSG_PUB.Add;
213 Raise FND_API.G_EXC_ERROR;
214 End if; -- invalid resource
215 End if; -- get resource id
216
217 -- Get activity_id
218 If nvl(l_resource_rec.activity, Fnd_Api.G_Miss_Char) <>
219 Fnd_Api.G_Miss_Char then
220 l_resource_rec.activity_id := null;
221 For l_activity_rec in l_Activity_csr(
222 P_Code => l_resource_rec.activity,
223 P_OrgId => l_resource_rec.organization_id) loop
224 l_resource_rec.activity_id := l_activity_rec.activity_id;
225 End loop;
226 If l_resource_rec.activity_id is null then
227 Fnd_Message.Set_Name('BOM', 'BOM_ACTIVITY_INVALID');
228 FND_MSG_PUB.Add;
229 Raise FND_API.G_EXC_ERROR;
230 End if; -- invalid activity
231 End if; -- get activity id
232
233 -- usage rates
234 /* Bug 1349028 */
235 If (l_resource_rec.usage_rate_or_amount_inverse is null) or
236 ((l_resource_rec.usage_rate_or_amount_inverse = Fnd_Api.G_Miss_Num) and
237 (l_resource_rec.usage_rate_or_amount <> Fnd_Api.G_Miss_Num)) then
238 If l_resource_rec.usage_rate_or_amount = 0 then
239 l_resource_rec.usage_rate_or_amount_inverse := 0;
240 Else
241 -- Bug 1533214
242 l_resource_rec.usage_rate_or_amount := round (l_resource_rec.usage_rate_or_amount,G_round_off_val); /* Bug 7322996 */
243 l_resource_rec.usage_rate_or_amount_inverse :=
244 1 / l_resource_rec.usage_rate_or_amount;
245 End if;
246 End if; -- null inverse
247
248 If (l_resource_rec.usage_rate_or_amount is null) or
249 ((l_resource_rec.usage_rate_or_amount = Fnd_Api.G_Miss_Num) and
250 (l_resource_rec.usage_rate_or_amount_inverse <> Fnd_Api.G_Miss_Num)) then
251 If l_resource_rec.usage_rate_or_amount_inverse = 0 then
252 l_resource_rec.usage_rate_or_amount := 0;
253 Else
254 -- Bug 1533214
255 l_resource_rec.usage_rate_or_amount_inverse := round (l_resource_rec.usage_rate_or_amount_inverse ,G_round_off_val); /* Bug 7322996 */
256 l_resource_rec.usage_rate_or_amount :=
257 1 / l_resource_rec.usage_rate_or_amount_inverse;
258 End if;
259 End if; -- null usage rate
260
261 -- Get Basis and Autocharge defaults
262 For l_defaults_rec in l_defaults_csr(
263 P_OpSeqId => l_resource_rec.operation_sequence_id,
264 P_ResourceId => l_resource_rec.resource_id) loop
265 If l_resource_rec.basis_type is null then
266 l_resource_rec.basis_type := nvl(l_defaults_rec.default_basis_type,1);
267 End if;
268 If l_resource_rec.activity_id is null then
269 l_resource_rec.activity_id := l_defaults_rec.default_activity_id;
270 End if;
271 If l_resource_rec.autocharge_type is null then
272 l_resource_rec.autocharge_type := l_defaults_rec.default_autocharge;
273 End if;
274 If l_resource_rec.standard_rate_flag is null then
275 l_resource_rec.standard_rate_flag := l_defaults_rec.standard_rate_flag;
276 End if;
277 End loop; -- defaults
278
279 x_resource_rec := l_resource_rec;
280 -- End of API body.
281
282 -- Standard call to get message count and if count is 1, get message info.
283 FND_MSG_PUB.Count_And_Get(
284 p_count => x_msg_count,
285 p_data => x_msg_data
286 );
287 EXCEPTION
288 WHEN FND_API.G_EXC_ERROR THEN
289 x_return_status := FND_API.G_RET_STS_ERROR;
290 FND_MSG_PUB.Count_And_Get(
291 p_count => x_msg_count,
292 p_data => x_msg_data
293 );
294 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
295 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
296 FND_MSG_PUB.Count_And_Get(
297 p_count => x_msg_count,
298 p_data => x_msg_data
299 );
300 WHEN OTHERS THEN
301 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
302 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
303 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
304 END IF;
305 FND_MSG_PUB.Count_And_Get(
306 p_count => x_msg_count,
307 p_data => x_msg_data
308 );
309 END AssignResource;
310
311 PROCEDURE ValidateResource(
312 p_api_version IN NUMBER,
313 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
314 p_commit IN VARCHAR2 := FND_API.G_FALSE,
315 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
316 x_return_status IN OUT NOCOPY VARCHAR2,
317 x_msg_count IN OUT NOCOPY NUMBER,
318 x_msg_data IN OUT NOCOPY VARCHAR2,
319 p_resource_rec IN RESOURCE_REC_TYPE := G_MISS_RESOURCE_REC,
320 x_resource_rec IN OUT NOCOPY RESOURCE_REC_TYPE
321 ) is
322 l_api_name CONSTANT VARCHAR2(30) := 'ValidateResource';
323 l_api_version CONSTANT NUMBER := 1.0;
324 l_resource_rec RESOURCE_REC_TYPE;
325 l_return_status VARCHAR(1);
326 l_msg_count NUMBER;
327 l_msg_data VARCHAR(2000);
328 --<BUGFIX :1746461 Begin>
329 rate_invalid NUMBER;
330 --<BUGFIX :1746461 End>
331 cursor l_operation_csr(P_OpSeqId number) is
332 Select 'x' dummy
333 From dual
334 Where not exists(
335 select null
336 from bom_operation_sequences
337 where operation_sequence_id = P_OpSeqId
338 and nvl(operation_type, g_event) = g_event
339 and nvl(reference_flag, g_no) = g_no);
340 cursor l_resource_csr(P_OpSeqId number, P_ResourceId number) is
341 select 'x' dummy
342 from dual
343 where not exists (
344 select null
345 from bom_resources br,
346 bom_department_resources bdr,
347 bom_operation_sequences bos
348 where br.resource_id = P_ResourceId
349 and bos.operation_sequence_id = P_OpSeqId
350 and nvl(br.disable_date, bos.effectivity_date + 1)
351 > bos.effectivity_date
352 and bdr.department_id = bos.department_id
353 and bdr.resource_id = br.resource_id);
354 cursor l_DeptRes_csr(P_ResourceId number, P_OpSeqId number) is
355 select bdr.AVAILABLE_24_HOURS_FLAG,
356 bd.location_id
357 from bom_department_resources bdr,
358 bom_departments bd,
359 bom_operation_sequences bos
360 where bdr.resource_id = P_ResourceId
361 and bos.operation_sequence_id = P_OpSeqId
362 and bdr.department_id = bos.department_id
363 and bdr.department_id = bd.department_id;
364 cursor l_activity_csr(P_ActivityId number, P_OpSeqId number) is
365 Select 'x' dummy
366 From dual
367 Where not exists(
368 select null
369 from cst_activities ca,
370 bom_operation_sequences bos,
371 bom_operational_routings bor
372 where ca.activity_id = P_ActivityId
376 and nvl(ca.organization_id, bor.organization_id)
373 and bos.operation_sequence_id = P_OpSeqId
374 and bos.routing_sequence_id =
375 bor.routing_sequence_id
377 = bor.organization_id
378 and nvl(ca.disable_date, bos.effectivity_date + 1)
379 > bos.effectivity_date);
380 cursor l_DupResource_csr(P_OpSeqId number, P_OldSeqNum number,
381 P_NewSeqNum number) is
382 Select 'x' dummy
383 From dual
384 Where exists(
385 Select null
386 from bom_operation_resources bor
387 where bor.operation_sequence_id = P_OpSeqId
388 and bor.resource_seq_num = P_NewSeqNum
389 and (bor.resource_seq_num <> P_OldSeqNum
390 or P_OldSeqNum is null));
391 l_HourUomCode VARCHAR2(3);
392 l_HourUomClass VARCHAR2(10);
393 l_ResUomCode VARCHAR2(3);
394 l_ResUomClass VARCHAR2(10);
395 cursor l_uom_csr(P_ResourceId number) is
396 Select unit_of_measure
397 From bom_resources
398 Where resource_id = P_ResourceId;
399 cursor l_class_csr(P_Code varchar2) is
400 Select uom_class
401 From mtl_units_of_measure
402 Where uom_code = P_Code;
403 cursor l_conversion_csr is
404 Select 'x' dummy
405 From dual
406 where not exists(
407 select null
408 from mtl_uom_conversions a,
409 mtl_uom_conversions b
410 where a.uom_code = l_ResUomCode
411 and a.uom_class = l_ResUomClass
412 and a.inventory_item_id = 0
413 and nvl(a.disable_date, sysdate + 1) > sysdate
414 and b.uom_code = l_HourUomCode
415 and b.inventory_item_id = 0
416 and b.uom_class = a.uom_class);
417 cursor l_schedule_csr(P_OpSeqId number, P_SeqNum number,
418 P_SchedType number) is
419 Select 'x' dummy
420 From dual
421 Where exists(
422 select null
423 from bom_operation_resources bor
424 where operation_sequence_id = P_OpSeqId
425 and (resource_seq_num <> P_SeqNum or
426 P_SeqNum is null)
427 and schedule_flag = P_SchedType);
428 l_Prior constant number := 3;
429 l_Next constant number := 4;
430 l_POReceipt constant number := 3;
431 l_POMove constant number := 4;
432 cursor l_pomove_csr(P_OpSeqId number, P_SeqNum number) is
433 select 'x' dummy
434 from dual
435 where exists(
436 select null
437 from bom_operation_resources
438 where operation_sequence_id = P_OpSeqId
439 and (P_SeqNum is null or
440 resource_seq_num <> P_SeqNum)
441 and autocharge_type = l_POMove);
442 cursor l_CheckLocation_csr(P_OpSeqId number) is
443 select 1 dummy
444 from dual
445 where not exists(
446 select 'no dept loc'
447 from bom_departments bd,
448 bom_operation_sequences bos
449 where bos.operation_sequence_id = P_OpSeqId
450 and bd.department_id = bos.department_id
451 and bd.location_id is not null);
452 BEGIN
453 -- Standard call to check for call compatibility.
454 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
455 G_PKG_NAME) THEN
456 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
457 END IF;
458 -- Initialize message list if p_init_msg_list is set to TRUE.
459 IF FND_API.to_Boolean(p_init_msg_list) THEN
460 FND_MSG_PUB.initialize;
461 END IF;
462 -- Initialize API return status to success
463 x_return_status := FND_API.G_RET_STS_SUCCESS;
464
465 -- API body
466 l_resource_rec := p_resource_rec;
467
468 If p_validation_level = FND_API.G_VALID_LEVEL_FULL then
469 AssignResource(
470 p_api_version => 1,
471 p_init_msg_list => p_init_msg_list,
472 p_commit => p_commit,
473 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
474 x_return_status => l_return_status,
475 x_msg_count => l_msg_count,
476 x_msg_data => l_msg_data,
477 p_resource_rec => l_resource_rec,
478 x_resource_rec => l_resource_rec);
479 If l_return_status = FND_API.G_RET_STS_ERROR then
480 Raise FND_API.G_EXC_ERROR;
481 Elsif l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
482 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
483 End if; -- error
484 End if; -- assign values
485
486 -- verify for existence of operation seq id
487 For l_operation_rec in l_operation_csr(
488 P_OpSeqId => l_resource_rec.operation_sequence_id) loop
489 Fnd_Message.Set_Name('BOM', 'BOM_OP_SEQ_INVALID');
490 FND_MSG_PUB.Add;
491 Raise FND_API.G_EXC_ERROR;
492 End loop;
493
494 -- check for null resource seq num
495 If l_resource_rec.new_resource_seq_num is null then
496 Fnd_Message.Set_Name('BOM', 'BOM_NULL_RESOURCE_SEQ_NUM');
497 FND_MSG_PUB.Add;
498 Raise FND_API.G_EXC_ERROR;
502 For l_DupResource_rec in l_DupResource_csr(
499 End if;
500
501 -- check for duplicate resource seq num/operation sequence id
503 P_OpSeqId => l_resource_rec.operation_sequence_id,
504 P_OldSeqNum => l_resource_rec.resource_seq_num,
505 P_NewSeqNum => l_resource_rec.new_resource_seq_num) loop
506 Fnd_Message.Set_Name('BOM', 'BOM_DUPLICATE_RES_NUM');
507 FND_MSG_PUB.Add;
508 Raise FND_API.G_EXC_ERROR;
509 End loop;
510
511 -- validate resource exists and is enabled and belongs to dept
512 For l_InvalidResource_rec in l_resource_csr(
513 P_OpSeqId => l_resource_rec.operation_sequence_id,
514 P_ResourceId => l_resource_rec.resource_id) loop
515 Fnd_Message.Set_Name('BOM', 'BOM_DEPT_RES_INVALID');
516 FND_MSG_PUB.Add;
517 Raise FND_API.G_EXC_ERROR;
518 End loop;
519
520 -- Check if resource is available 24 hours
521
522 If l_resource_rec.assigned_units is null then
523 l_resource_rec.assigned_units := 1;
524 End if;
525 For l_DeptRes_rec in l_DeptRes_csr(
526 P_ResourceId => l_resource_rec.resource_id,
527 P_OpSeqId => l_resource_rec.operation_sequence_id) loop
528 If l_DeptRes_rec.available_24_hours_flag = g_yes then
529 l_resource_rec.assigned_units := 1;
530 End if;
531 End loop;
532
533 -- verify activity is enabled
534 if l_resource_rec.activity_id is not null then
535 For l_activity_rec in l_activity_csr(
536 P_ActivityId => l_resource_rec.activity_id,
537 P_OpSeqId => l_resource_rec.operation_sequence_id) loop
538 Fnd_Message.Set_Name('BOM', 'BOM_ACTIVITY_ID_INVALID');
539 FND_MSG_PUB.Add;
540 Raise FND_API.G_EXC_ERROR;
541 End loop;
542 End if;
543
544 -- get units of measure and uom classes
545 l_HourUomCode := Fnd_Profile.Value('BOM:HOUR_UOM_CODE');
546 For l_class_rec in l_class_csr(P_Code => l_HourUomCode) loop
547 l_HourUomClass := l_class_rec.uom_class;
548 End loop;
549
550 For l_uom_rec in l_uom_csr(P_ResourceId => l_resource_rec.resource_id) loop
551 l_ResUomCode := l_uom_rec.unit_of_measure;
552 End loop;
553 For l_class_rec in l_class_csr(P_Code => l_ResUomCode) loop
554 l_ResUomClass := l_class_rec.uom_class;
555 End loop;
556
557 -- Schedule must be No if:
558 -- 1) resource uom <> hour uom code (if they're the same, class would be
559 -- same.
560 -- 2) res uom class <> hour uom class and
561 -- 3) no conversion between resource uom and hour uom
562 If l_resource_rec.schedule_flag <> g_no then
563 If l_HourUomClass <> l_ResUomClass then
564 Fnd_Message.Set_Name('BOM', 'BOM_OP_RES_SCHED_NO');
565 FND_MSG_PUB.Add;
566 Raise FND_API.G_EXC_ERROR;
567 End if; -- not time based class
568 For l_conversion_rec in l_conversion_csr loop
569 Fnd_Message.Set_Name('BOM', 'BOM_OP_RES_SCHED_NO');
570 FND_MSG_PUB.Add;
571 Raise FND_API.G_EXC_ERROR;
572 End loop;
573 end if; -- schedule flag
574
575 -- cannot have more than one Next or Prior scheduled resource
576 -- for an operation
577
578 If l_resource_rec.schedule_flag = l_Prior then
579 For l_Schedule_rec in l_schedule_csr(
580 P_OpSeqId => l_resource_rec.operation_sequence_id,
581 P_SeqNum => l_resource_rec.resource_seq_num,
582 P_SchedType => l_resource_rec.schedule_flag) loop
583 Fnd_Message.Set_Name('BOM', 'BOM_OP_RES_PRIOR_ERROR');
584 FND_MSG_PUB.Add;
585 Raise FND_API.G_EXC_ERROR;
586 End loop;
587 End if; -- prior
588
589 If l_resource_rec.schedule_flag = l_Next then
590 For l_Schedule_rec in l_schedule_csr(
591 P_OpSeqId => l_resource_rec.operation_sequence_id,
592 P_SeqNum => l_resource_rec.resource_seq_num,
593 P_SchedType => l_resource_rec.schedule_flag) loop
594 Fnd_Message.Set_Name('BOM', 'BOM_OP_RES_NEXT_ERROR');
595 FND_MSG_PUB.Add;
596 Raise FND_API.G_EXC_ERROR;
597 End loop;
598 End if; -- next
599
600 -- cannot have negative usage rate if one of the following is true:
601 -- 1) autocharge_type = 3 or 4
602 -- 2) res uom class = hour_uom_class
603
604 If l_resource_rec.usage_rate_or_amount < 0 then
605 If l_resource_rec.autocharge_type in (3, 4) then
606 Fnd_Message.Set_Name('BOM', 'BOM_NEGATIVE_USAGE_RATE');
607 FND_MSG_PUB.Add;
608 Raise FND_API.G_EXC_ERROR;
609 End if;
610 If l_HourUomClass = l_ResUomClass then
611 Fnd_Message.Set_Name('BOM', 'BOM_NEGATIVE_USAGE_RATE');
612 FND_MSG_PUB.Add;
613 Raise FND_API.G_EXC_ERROR;
614 End if;
615 End if; -- negative usage
616
617 -- assigned units cannot be less than or equal to .00001
618 If l_resource_rec.assigned_units <= .00001 then
619 Fnd_Message.Set_Name('BOM', 'BOM_ASSIGNED_UNIT_ERROR');
620 FND_MSG_PUB.Add;
621 Raise FND_API.G_EXC_ERROR;
622 End if;
623
624 -- check if basis type,standard rate flag, schedule flag
625 -- and autocharge type are valid
626 If (l_resource_rec.basis_type not in (1,2)) or
627 (l_resource_rec.standard_rate_flag not in (1, 2)) or
628 (l_resource_rec.schedule_flag not in (1,2,3,4)) or
629 (l_resource_rec.autocharge_type not in (1,2,3,4)) then
633 End if;
630 Fnd_Message.Set_Name('BOM', 'BOM_OP_RES_LOOKUP_ERROR');
631 FND_MSG_PUB.Add;
632 Raise FND_API.G_EXC_ERROR;
634
635 -- Only one PO move per operation
636 If l_resource_rec.autocharge_type = l_POMove then
637 For l_autocharge_rec in l_pomove_csr(
638 P_OpSeqId => l_resource_rec.operation_sequence_id,
639 P_SeqNum => l_resource_rec.resource_seq_num) loop
640 Fnd_Message.Set_Name('BOM', 'BOM_AUTOCHARGE_INVALID');
641 FND_MSG_PUB.Add;
642 Raise FND_API.G_EXC_ERROR;
643 End loop;
644 End if; -- PO Move
645
646 -- Autocharge cannot be PO Move or PO Receipt if
647 -- the department has no location
648 If l_resource_rec.autocharge_type in (l_POReceipt, l_POMove) then
649 For l_location_rec in l_CheckLocation_csr(
650 P_OpSeqId => l_resource_rec.operation_sequence_id) loop
651 Fnd_Message.Set_Name('BOM','BOM_AUTOCHARGE_INVALID');
652 FND_MSG_PUB.Add;
653 Raise FND_API.G_EXC_ERROR;
654 End loop;
655 End if;
656
657 -- Check offset percent
658 If l_resource_rec.resource_offset_percent not between 0 and 100 then
659 Fnd_Message.Set_Name('BOM', 'BOM_OFFSET_PERCENT_INVALID');
660 FND_MSG_PUB.Add;
661 Raise FND_API.G_EXC_ERROR;
662 End if;
663
664 -- Bug 2514018 Check Principle Flag
665 If l_resource_rec.principle_flag IS NOT NULL and
666 l_resource_rec.principle_flag NOT IN (1,2)
667 and l_resource_rec.principle_flag <> FND_API.G_MISS_NUM then
668 Fnd_Message.Set_Name('BOM', 'BOM_RES_PCLFLAG_INVALID');
669 FND_MSG_PUB.Add;
670 Raise FND_API.G_EXC_ERROR;
671 END IF;
672 --Bug 2514018
673
674 -- Check usage rate and usage rate inverse
675 --<BUG FIX 1746461 Begin>
676 /*
677 If l_resource_rec.usage_rate_or_amount <> 0 then
678 If round(l_resource_rec.usage_rate_or_amount, 6) <>
679 round((1 / l_resource_rec.usage_rate_or_amount_inverse), 6) then
680 Fnd_Message.Set_Name('BOM', 'BOM_USAGE_RATE_INVALID');
681 FND_MSG_PUB.Add;
682 Raise FND_API.G_EXC_ERROR;
683 End if;
684 Elsif l_resource_rec.usage_rate_or_amount = 0 then
685 If l_resource_rec.usage_rate_or_amount_inverse <> 0 then
686 Fnd_Message.Set_Name('BOM', 'BOM_USAGE_RATE_INVALID');
687 FND_MSG_PUB.Add;
688 Raise FND_API.G_EXC_ERROR;
689 End if;
690 End if;
691 */
692 -- rate_invalid is 0 when usage_rate is invalid
693 rate_invalid:=0;
694 if (l_resource_rec.usage_rate_or_amount <> 0) and
695 (l_resource_rec.usage_rate_or_amount_inverse <> 0)
696 then /* Bug 7322996 */
697 if (round(l_resource_rec.usage_rate_or_amount,G_round_off_val) <>
698 round((1/l_resource_rec.usage_rate_or_amount_inverse),G_round_off_val))
699 and
700 (round((1/l_resource_rec.usage_rate_or_amount),G_round_off_val) <>
701 round(l_resource_rec.usage_rate_or_amount_inverse,G_round_off_val))
702 then
703 rate_invalid:=0;
704 else
705 rate_invalid:=1;
706 end if;
707 elsif (l_resource_rec.usage_rate_or_amount = 0) and
708 (l_resource_rec.usage_rate_or_amount_inverse = 0)
709 then
710 rate_invalid:=1;
711 else
712 rate_invalid:=0;
713 end if;
714
715 if (rate_invalid = 0)
716 then
717 Fnd_Message.Set_Name('BOM','BOM_USAGE_RATE_INVALID');
718 FND_MSG_PUB.Add;
719 Raise FND_API.G_EXC_ERROR;
720 end if;
721 --<BUG FIX : 1746461 End>
722
723 x_resource_rec := l_resource_rec;
724 -- End of API body.
725
726 -- Standard call to get message count and if count is 1, get message info.
727 FND_MSG_PUB.Count_And_Get(
728 p_count => x_msg_count,
729 p_data => x_msg_data
730 );
731 EXCEPTION
732 WHEN FND_API.G_EXC_ERROR THEN
733 x_return_status := FND_API.G_RET_STS_ERROR;
734 FND_MSG_PUB.Count_And_Get(
735 p_count => x_msg_count,
736 p_data => x_msg_data
737 );
738 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
739 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
740 FND_MSG_PUB.Count_And_Get(
741 p_count => x_msg_count,
742 p_data => x_msg_data
743 );
744 WHEN OTHERS THEN
745 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
746 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
747 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
748 END IF;
749 FND_MSG_PUB.Count_And_Get(
750 p_count => x_msg_count,
751 p_data => x_msg_data
752 );
753 END ValidateResource;
754 PROCEDURE CreateResource(
755 p_api_version IN NUMBER,
756 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
757 p_commit IN VARCHAR2 := FND_API.G_FALSE,
758 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
759 x_return_status IN OUT NOCOPY VARCHAR2,
760 x_msg_count IN OUT NOCOPY NUMBER,
761 x_msg_data IN OUT NOCOPY VARCHAR2,
762 p_resource_rec IN RESOURCE_REC_TYPE := G_MISS_RESOURCE_REC,
763 x_resource_rec IN OUT NOCOPY RESOURCE_REC_TYPE
764 ) IS
765 l_api_name CONSTANT VARCHAR2(30) := 'CreateResource';
766 l_api_version CONSTANT NUMBER := 1.0;
770 l_msg_data VARCHAR2(2000);
767 l_resource_rec RESOURCE_REC_TYPE;
768 l_return_status VARCHAR2(1);
769 l_msg_count NUMBER;
771 l_UserId number;
772 l_LoginId number;
773 l_RequestId number;
774 l_ProgramId number;
775 l_ApplicationId number;
776 l_ProgramUpdate date;
777 BEGIN
778 -- Standard Start of API savepoint
779 SAVEPOINT CreateResource_Pvt;
780 -- Standard call to check for call compatibility.
781 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
782 G_PKG_NAME) THEN
783 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
784 END IF;
785 -- Initialize message list if p_init_msg_list is set to TRUE.
786 IF FND_API.to_Boolean(p_init_msg_list) THEN
787 FND_MSG_PUB.initialize;
788 END IF;
789 -- Initialize API return status to success
790 x_return_status := FND_API.G_RET_STS_SUCCESS;
791
792 -- API body
793 l_resource_rec := p_resource_rec;
794
795 -- initialize record
796 If l_resource_rec.operation_sequence_id = Fnd_Api.G_Miss_Num then
797 l_resource_rec.operation_sequence_id := null;
798 End if;
799
800 If nvl(l_resource_rec.new_resource_seq_num, Fnd_Api.G_Miss_Num) =
801 Fnd_Api.G_Miss_Num then
802 l_resource_rec.new_resource_seq_num := l_resource_rec.resource_seq_num;
803 End if;
804 l_resource_rec.resource_seq_num := null;
805
806 If l_resource_rec.resource_id = Fnd_Api.G_Miss_Num then
807 l_resource_rec.resource_id := null;
808 End if;
809
810 If l_resource_rec.activity_id = Fnd_Api.G_Miss_Num then
811 l_resource_rec.activity_id := null;
812 End if;
813
814 If nvl(l_resource_rec.standard_rate_flag, Fnd_Api.G_Miss_Num) =
815 Fnd_Api.G_Miss_Num then
816 --bug 2117759 standard_rate_flag will be defaulted from res definition
817 l_resource_rec.standard_rate_flag := null;
818 End if;
819
820 If nvl(l_resource_rec.assigned_units, Fnd_Api.G_Miss_Num) =
821 Fnd_Api.G_Miss_Num then
822 l_resource_rec.assigned_units := 1;
823 End if;
824
825 If (nvl(l_resource_rec.usage_rate_or_amount, Fnd_Api.G_Miss_Num) =
826 Fnd_Api.G_Miss_Num and
827 nvl(l_resource_rec.usage_rate_or_amount_inverse, Fnd_Api.G_Miss_Num) =
828 Fnd_Api.G_Miss_Num) then
829 l_resource_rec.usage_rate_or_amount := 1;
830 l_resource_rec.usage_rate_or_amount_inverse := 1;
831 End if;
832
833 If nvl(l_resource_rec.basis_type, Fnd_Api.G_Miss_Num) =
834 Fnd_Api.G_Miss_Num then
835 l_resource_rec.basis_type := null;
836 End if;
837
838 If nvl(l_resource_rec.schedule_flag, Fnd_Api.G_Miss_Num) =
839 Fnd_Api.G_Miss_Num then
840 l_resource_rec.schedule_flag := 2;
841 End if;
842
843 If l_resource_rec.resource_offset_percent = Fnd_Api.G_Miss_Num then
844 l_resource_rec.resource_offset_percent := null;
845 End if;
846
847 If l_resource_rec.autocharge_type = Fnd_Api.G_Miss_Num then
848 l_resource_rec.autocharge_type := null;
849 End if;
850
851 If l_resource_rec.attribute_category = Fnd_Api.G_Miss_Char then
852 l_resource_rec.attribute_category := null;
853 End if;
854
855 If l_resource_rec.attribute1 = Fnd_Api.G_Miss_Char then
856 l_resource_rec.attribute1 := null;
857 End if;
858
859 If l_resource_rec.attribute2 = Fnd_Api.G_Miss_Char then
860 l_resource_rec.attribute2 := null;
861 End if;
862
863 If l_resource_rec.attribute3 = Fnd_Api.G_Miss_Char then
864 l_resource_rec.attribute3 := null;
865 End if;
866
867 If l_resource_rec.attribute4 = Fnd_Api.G_Miss_Char then
868 l_resource_rec.attribute4 := null;
869 End if;
870
871 If l_resource_rec.attribute5 = Fnd_Api.G_Miss_Char then
872 l_resource_rec.attribute5 := null;
873 End if;
874
875 If l_resource_rec.attribute6 = Fnd_Api.G_Miss_Char then
876 l_resource_rec.attribute6 := null;
877 End if;
878
879 If l_resource_rec.attribute7 = Fnd_Api.G_Miss_Char then
880 l_resource_rec.attribute7 := null;
881 End if;
882
883
884 If l_resource_rec.attribute8 = Fnd_Api.G_Miss_Char then
885 l_resource_rec.attribute8 := null;
886 End if;
887
888 If l_resource_rec.attribute9 = Fnd_Api.G_Miss_Char then
889 l_resource_rec.attribute9 := null;
890 End if;
891
892 If l_resource_rec.attribute10 = Fnd_Api.G_Miss_Char then
893 l_resource_rec.attribute10 := null;
894 End if;
895
896 If l_resource_rec.attribute11 = Fnd_Api.G_Miss_Char then
897 l_resource_rec.attribute11 := null;
898 End if;
899
900 If l_resource_rec.attribute12 = Fnd_Api.G_Miss_Char then
901 l_resource_rec.attribute12 := null;
902 End if;
903
904 If l_resource_rec.attribute13 = Fnd_Api.G_Miss_Char then
905 l_resource_rec.attribute13 := null;
906 End if;
907
908 If l_resource_rec.attribute14 = Fnd_Api.G_Miss_Char then
909 l_resource_rec.attribute14 := null;
910 End if;
911
912 If l_resource_rec.attribute15 = Fnd_Api.G_Miss_Char then
913 l_resource_rec.attribute15 := null;
914 End if;
915
916 If l_resource_rec.assembly_item_id = Fnd_Api.G_Miss_Num then
920 If l_resource_rec.alternate_routing_designator = Fnd_Api.G_Miss_Char then
917 l_resource_rec.assembly_item_id := null;
918 End if;
919
921 l_resource_rec.alternate_routing_designator := null;
922 End if;
923
924 If l_resource_rec.organization_id = Fnd_Api.G_Miss_Num then
925 l_resource_rec.organization_id := null;
926 End if;
927
928 If l_resource_rec.operation_seq_num = Fnd_Api.G_Miss_Num then
929 l_resource_rec.operation_seq_num := null;
930 End if;
931
932 If l_resource_rec.effectivity_date = Fnd_Api.G_Miss_Date then
933 l_resource_rec.effectivity_date := null;
934 End if;
935
936 If l_resource_rec.routing_sequence_id = Fnd_Api.G_Miss_Num then
937 l_resource_rec.routing_sequence_id := null;
938 End if;
939
940 If l_resource_rec.organization_code = Fnd_Api.G_Miss_Char then
941 l_resource_rec.organization_code := null;
942 End if;
943
944 If l_resource_rec.assembly_item_number = Fnd_Api.G_Miss_Char then
945 l_resource_rec.assembly_item_number := null;
946 End if;
947
948 If l_resource_rec.resource_code = Fnd_Api.G_Miss_Char then
949 l_resource_rec.resource_code := null;
950 End if;
951
952 If l_resource_rec.activity = Fnd_Api.G_Miss_Char then
953 l_resource_rec.activity := null;
954 End if;
955
956 --Bug 2514018
957 if nvl(l_resource_rec.principle_flag,Fnd_Api.G_Miss_Num) =
958 Fnd_Api.G_Miss_Num then
959 l_resource_rec.principle_flag := 2;
960 End if;
961 --Bug 2514018
962
963 If p_validation_level > FND_API.G_VALID_LEVEL_NONE then
964 ValidateResource(
965 p_api_version => 1,
966 p_init_msg_list => p_init_msg_list,
967 p_commit => p_commit,
968 p_validation_level => p_validation_level,
969 x_return_status => l_return_status,
970 x_msg_count => l_msg_count,
971 x_msg_data => l_msg_data,
972 p_resource_rec => l_resource_rec,
973 x_resource_rec => l_resource_rec);
974 If l_return_status = FND_API.G_RET_STS_ERROR then
975 Raise FND_API.G_EXC_ERROR;
976 Elsif l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
977 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
978 End if; -- validation error
979 End if; -- validate before inserting
980
981 l_UserId := nvl(Fnd_Global.USER_ID, -1);
982 l_LoginId := Fnd_Global.LOGIN_ID;
983 l_RequestId := Fnd_Global.CONC_REQUEST_ID;
984 l_ProgramId := Fnd_Global.CONC_PROGRAM_ID;
985 l_ApplicationId := Fnd_Global.PROG_APPL_ID;
986 -- do not use decode because of implicit data type conversions
987 If l_RequestId is null then
988 l_ProgramUpdate := null;
989 Else
990 l_ProgramUpdate := sysdate;
991 End if;
992
993 Insert into bom_operation_resources(
994 operation_sequence_id,
995 resource_seq_num,
996 resource_id,
997 activity_id,
998 standard_rate_flag,
999 assigned_units,
1000 usage_rate_or_amount,
1001 usage_rate_or_amount_inverse,
1002 basis_type,
1003 schedule_flag,
1004 last_update_date,
1005 last_updated_by,
1006 creation_date,
1007 created_by,
1008 last_update_login,
1009 resource_offset_percent,
1010 autocharge_type,
1011 attribute_category,
1012 attribute1,
1013 attribute2,
1014 attribute3,
1015 attribute4,
1016 attribute5,
1017 attribute6,
1018 attribute7,
1019 attribute8,
1020 attribute9,
1021 attribute10,
1022 attribute11,
1023 attribute12,
1024 attribute13,
1025 attribute14,
1026 attribute15,
1027 principle_flag,
1028 schedule_seq_num,
1029 substitute_group_num,
1030 request_id,
1031 program_application_id,
1032 program_id,
1033 program_update_date)
1034 values(
1035 l_resource_rec.operation_sequence_id,
1036 l_resource_rec.new_resource_seq_num,
1037 l_resource_rec.resource_id,
1038 l_resource_rec.activity_id,
1039 l_resource_rec.standard_rate_flag,
1040 l_resource_rec.assigned_units,
1041 round(l_resource_rec.usage_rate_or_amount,G_round_off_val), /* Bug 7322996 */
1042 round(l_resource_rec.usage_rate_or_amount_inverse,G_round_off_val), /* Bug 7322996 */
1043 l_resource_rec.basis_type,
1044 l_resource_rec.schedule_flag,
1045 sysdate,
1046 l_UserId,
1047 sysdate,
1048 l_UserId,
1049 l_LoginId,
1050 l_resource_rec.resource_offset_percent,
1051 l_resource_rec.autocharge_type,
1052 l_resource_rec.attribute_category,
1053 l_resource_rec.attribute1,
1054 l_resource_rec.attribute2,
1055 l_resource_rec.attribute3,
1056 l_resource_rec.attribute4,
1057 l_resource_rec.attribute5,
1058 l_resource_rec.attribute6,
1059 l_resource_rec.attribute7,
1060 l_resource_rec.attribute8,
1061 l_resource_rec.attribute9,
1062 l_resource_rec.attribute10,
1063 l_resource_rec.attribute11,
1064 l_resource_rec.attribute12,
1065 l_resource_rec.attribute13,
1066 l_resource_rec.attribute14,
1067 l_resource_rec.attribute15,
1071 l_RequestId,
1068 l_resource_rec.principle_flag,
1069 l_resource_rec.schedule_seq_num,
1070 l_resource_rec.schedule_seq_num,
1072 l_ApplicationId,
1073 l_ProgramId,
1074 l_ProgramUpdate);
1075
1076 x_resource_rec := l_resource_rec;
1077 -- End of API body.
1078
1079 -- Standard check of p_commit.
1080 IF FND_API.To_Boolean(p_commit) THEN
1081 COMMIT WORK;
1082 END IF;
1083 -- Standard call to get message count and if count is 1, get message info.
1084 FND_MSG_PUB.Count_And_Get(
1085 p_count => x_msg_count,
1086 p_data => x_msg_data
1087 );
1088 EXCEPTION
1089 WHEN FND_API.G_EXC_ERROR THEN
1090 ROLLBACK TO CreateResource_Pvt;
1091 x_return_status := FND_API.G_RET_STS_ERROR;
1092 FND_MSG_PUB.Count_And_Get(
1093 p_count => x_msg_count,
1094 p_data => x_msg_data
1095 );
1096 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1097 ROLLBACK TO CreateResource_Pvt;
1098 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1099 FND_MSG_PUB.Count_And_Get(
1100 p_count => x_msg_count,
1101 p_data => x_msg_data
1102 );
1103 WHEN OTHERS THEN
1104 ROLLBACK TO CreateResource_Pvt;
1105 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1106 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1107 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1108 END IF;
1109 FND_MSG_PUB.Count_And_Get(
1110 p_count => x_msg_count,
1111 p_data => x_msg_data
1112 );
1113 END CreateResource;
1114 PROCEDURE UpdateResource(
1115 p_api_version IN NUMBER,
1116 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1117 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1118 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1119 x_return_status IN OUT NOCOPY VARCHAR2,
1120 x_msg_count IN OUT NOCOPY NUMBER,
1121 x_msg_data IN OUT NOCOPY VARCHAR2,
1122 p_resource_rec IN RESOURCE_REC_TYPE := G_MISS_RESOURCE_REC,
1123 x_resource_rec IN OUT NOCOPY RESOURCE_REC_TYPE
1124 ) IS
1125 l_api_name CONSTANT VARCHAR2(30) := 'UpdateResource';
1126 l_api_version CONSTANT NUMBER := 1.0;
1127 l_resource_rec RESOURCE_REC_TYPE;
1128 l_return_status VARCHAR2(1);
1129 l_msg_count NUMBER;
1130 l_msg_data VARCHAR2(2000);
1131 l_UserId NUMBER;
1132 l_LoginId NUMBER;
1133 l_RequestId NUMBER;
1134 l_ProgramId NUMBER;
1135 l_ProgramUpdate DATE;
1136 l_ApplicationId NUMBER;
1137 cursor l_ExistingOpResource_csr(P_OpSeqId number,
1138 P_SeqNum number) is
1139 Select *
1140 From bom_operation_resources bor
1141 Where bor.operation_sequence_id = P_OpSeqId
1142 And bor.resource_seq_num = P_SeqNum;
1143 l_ResourceFound BOOLEAN := false;
1144 BEGIN
1145 -- Standard Start of API savepoint
1146 SAVEPOINT UpdateResource_Pvt;
1147 -- Standard call to check for call compatibility.
1148 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
1149 G_PKG_NAME) THEN
1150 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1151 END IF;
1152 -- Initialize message list if p_init_msg_list is set to TRUE.
1153 IF FND_API.to_Boolean(p_init_msg_list) THEN
1154 FND_MSG_PUB.initialize;
1155 END IF;
1156 -- Initialize API return status to success
1157 x_return_status := FND_API.G_RET_STS_SUCCESS;
1158
1159 -- API body
1160 l_resource_rec := p_resource_rec;
1161
1162 If p_validation_level = FND_API.G_VALID_LEVEL_FULL then
1163 AssignResource(
1164 p_api_version => 1,
1165 p_init_msg_list => p_init_msg_list,
1166 p_commit => p_commit,
1167 p_validation_level => p_validation_level,
1168 x_return_status => l_return_status,
1169 x_msg_count => l_msg_count,
1170 x_msg_data => l_msg_data,
1171 p_resource_rec => l_resource_rec,
1172 x_resource_rec => l_resource_rec
1173 );
1174 If l_return_status = FND_API.G_RET_STS_ERROR then
1175 Raise FND_API.G_EXC_ERROR;
1176 Elsif l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
1177 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
1178 End if; -- assign error
1179 End If; -- assign
1180
1181
1182 -- populate unspecified values
1183
1184 l_ResourceFound := false;
1185 For l_OldResource_rec in l_ExistingOpResource_csr(
1186 P_OpSeqId => l_resource_rec.operation_sequence_id,
1187 P_SeqNum => l_resource_rec.resource_seq_num) loop
1188 l_ResourceFound := true;
1189
1190 If l_resource_rec.resource_id = Fnd_Api.G_Miss_Num then
1191 l_resource_rec.resource_id := l_OldResource_rec.resource_id;
1192 End if;
1193
1194 If l_resource_rec.activity_id = Fnd_Api.G_Miss_Num then
1195 l_resource_rec.activity_id := l_OldResource_rec.activity_id;
1196 End if;
1197
1198 If l_resource_rec.standard_rate_flag = Fnd_Api.G_Miss_Num then
1199 l_resource_rec.standard_rate_flag :=
1200 l_OldResource_rec.standard_rate_flag;
1201 End if;
1202
1203 If l_resource_rec.assigned_units = Fnd_Api.G_Miss_Num then
1204 l_resource_rec.assigned_units := l_OldResource_rec.assigned_units;
1205 End if;
1206
1210 End if;
1207 If l_resource_rec.usage_rate_or_amount = Fnd_Api.G_Miss_Num then
1208 l_resource_rec.usage_rate_or_amount :=
1209 l_OldResource_rec.usage_rate_or_amount;
1211
1212 If l_resource_rec.usage_rate_or_amount_inverse = Fnd_Api.G_Miss_Num then
1213 l_resource_rec.usage_rate_or_amount_inverse :=
1214 l_OldResource_rec.usage_rate_or_amount_inverse;
1215 End if;
1216
1217 If l_resource_rec.basis_type = Fnd_Api.G_Miss_Num then
1218 l_resource_rec.basis_type := l_OldResource_rec.basis_type;
1219 End if;
1220
1221 If l_resource_rec.schedule_flag = Fnd_Api.G_Miss_Num then
1222 l_resource_rec.schedule_flag := l_OldResource_rec.schedule_flag;
1223 End if;
1224
1225 If l_resource_rec.resource_offset_percent = Fnd_Api.G_Miss_Num then
1226 l_resource_rec.resource_offset_percent :=
1227 l_OldResource_rec.resource_offset_percent;
1228 End if;
1229
1230 If l_resource_rec.autocharge_type = Fnd_Api.G_Miss_Num then
1231 l_resource_rec.autocharge_type := l_OldResource_rec.autocharge_type;
1232 End if;
1233
1234 If l_resource_rec.attribute_category = Fnd_Api.G_Miss_Char then
1235 l_resource_rec.attribute_category :=
1236 l_OldResource_rec.attribute_category;
1237 End if;
1238
1239 If l_resource_rec.attribute1 = Fnd_Api.G_Miss_Char then
1240 l_resource_rec.attribute1 := l_OldResource_rec.attribute1;
1241 End if;
1242
1243 If l_resource_rec.attribute2 = Fnd_Api.G_Miss_Char then
1244 l_resource_rec.attribute2 := l_OldResource_rec.attribute2;
1245 End if;
1246
1247 If l_resource_rec.attribute3 = Fnd_Api.G_Miss_Char then
1248 l_resource_rec.attribute3 := l_OldResource_rec.attribute3;
1249 End if;
1250
1251 If l_resource_rec.attribute4 = Fnd_Api.G_Miss_Char then
1252 l_resource_rec.attribute4 := l_OldResource_rec.attribute4;
1253 End if;
1254
1255 If l_resource_rec.attribute5 = Fnd_Api.G_Miss_Char then
1256 l_resource_rec.attribute5 := l_OldResource_rec.attribute5;
1257 End if;
1258
1259 If l_resource_rec.attribute6 = Fnd_Api.G_Miss_Char then
1260 l_resource_rec.attribute6 := l_OldResource_rec.attribute6;
1261 End if;
1262
1263 If l_resource_rec.attribute7 = Fnd_Api.G_Miss_Char then
1264 l_resource_rec.attribute7 := l_OldResource_rec.attribute7;
1265 End if;
1266
1267 If l_resource_rec.attribute8 = Fnd_Api.G_Miss_Char then
1268 l_resource_rec.attribute8 := l_OldResource_rec.attribute8;
1269 End if;
1270
1271 If l_resource_rec.attribute9 = Fnd_Api.G_Miss_Char then
1272 l_resource_rec.attribute9 := l_OldResource_rec.attribute9;
1273 End if;
1274
1275 If l_resource_rec.attribute10 = Fnd_Api.G_Miss_Char then
1276 l_resource_rec.attribute10 := l_OldResource_rec.attribute10;
1277 End if;
1278
1279 If l_resource_rec.attribute11 = Fnd_Api.G_Miss_Char then
1280 l_resource_rec.attribute11 := l_OldResource_rec.attribute11;
1281 End if;
1282
1283 If l_resource_rec.attribute12 = Fnd_Api.G_Miss_Char then
1284 l_resource_rec.attribute12 := l_OldResource_rec.attribute12;
1285 End if;
1286
1287 If l_resource_rec.attribute13 = Fnd_Api.G_Miss_Char then
1288 l_resource_rec.attribute13 := l_OldResource_rec.attribute13;
1289 End if;
1290
1291 If l_resource_rec.attribute14 = Fnd_Api.G_Miss_Char then
1292 l_resource_rec.attribute14 := l_OldResource_rec.attribute14;
1293 End if;
1294
1295 If l_resource_rec.attribute15 = Fnd_Api.G_Miss_Char then
1296 l_resource_rec.attribute15 := l_OldResource_rec.attribute15;
1297 End if;
1298
1299 If l_resource_rec.principle_flag = Fnd_Api.G_Miss_Num then
1300 l_resource_rec.principle_flag := l_OldResource_rec.principle_flag;
1301 End if;
1302
1303 If l_resource_rec.new_resource_seq_num = Fnd_Api.G_Miss_Num then
1304 l_resource_rec.new_resource_seq_num := l_resource_rec.resource_seq_num;
1305 End if;
1306 End loop; -- get old values
1307
1308 If not l_ResourceFound then
1309 Fnd_Message.Set_Name('BOM', 'BOM_INVALID_OP_RESOURCE');
1310 FND_MSG_PUB.Add;
1311 Raise FND_API.G_EXC_ERROR;
1312 End if; -- missing op resource
1313
1314 If p_validation_level > FND_API.G_VALID_LEVEL_NONE then
1315 ValidateResource(
1316 p_api_version => 1,
1317 p_init_msg_list => p_init_msg_list,
1318 p_commit => p_commit,
1319 p_validation_level => FND_API.G_VALID_LEVEL_NONE,
1320 x_return_status => l_return_status,
1321 x_msg_count => l_msg_count,
1322 x_msg_data => l_msg_data,
1323 p_resource_rec => l_resource_rec,
1324 x_resource_rec => l_resource_rec
1325 );
1326 If l_return_status = FND_API.G_RET_STS_ERROR then
1327 Raise FND_API.G_EXC_ERROR;
1328 Elsif l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
1329 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
1330 End if; -- validation error
1331 End If; -- validation
1332
1333 -- update operation resource
1334
1335 l_UserId := nvl(Fnd_Global.USER_ID, -1);
1336 l_LoginId := Fnd_Global.LOGIN_ID;
1337 l_RequestId := Fnd_Global.CONC_REQUEST_ID;
1338 l_ProgramId := Fnd_Global.CONC_PROGRAM_ID;
1339 l_ApplicationId := Fnd_Global.PROG_APPL_ID;
1343 Else
1340 -- do not use decode because of implicit data type conversions
1341 If l_RequestId is null then
1342 l_ProgramUpdate := null;
1344 l_ProgramUpdate := sysdate;
1345 End if;
1346
1347 update bom_operation_resources set
1348 resource_seq_num = l_resource_rec.new_resource_seq_num,
1349 resource_id = l_resource_rec.resource_id,
1350 activity_id = l_resource_rec.activity_id,
1351 standard_rate_flag = l_resource_rec.standard_rate_flag,
1352 assigned_units = l_resource_rec.assigned_units,
1353 usage_rate_or_amount = round(l_resource_rec.usage_rate_or_amount,G_round_off_val), /* Bug 7322996 */
1354 usage_rate_or_amount_inverse = round(l_resource_rec.usage_rate_or_amount_inverse,G_round_off_val), /* Bug 7322996 */
1355 basis_type = l_resource_rec.basis_type,
1356 schedule_flag = l_resource_rec.schedule_flag,
1357 last_update_date = sysdate,
1358 last_updated_by = l_UserId,
1359 creation_date = nvl(creation_date,sysdate),
1360 created_by = l_UserId,
1361 last_update_login = l_LoginId,
1362 resource_offset_percent = l_resource_rec.resource_offset_percent,
1363 autocharge_type = l_resource_rec.autocharge_type,
1364 attribute_category = l_resource_rec.attribute_category,
1365 attribute1 = l_resource_rec.attribute1,
1366 attribute2 = l_resource_rec.attribute2,
1367 attribute3 = l_resource_rec.attribute3,
1368 attribute4 = l_resource_rec.attribute4,
1369 attribute5 = l_resource_rec.attribute5,
1370 attribute6 = l_resource_rec.attribute6,
1371 attribute7 = l_resource_rec.attribute7,
1372 attribute8 = l_resource_rec.attribute8,
1373 attribute9 = l_resource_rec.attribute9,
1374 attribute10 = l_resource_rec.attribute10,
1375 attribute11 = l_resource_rec.attribute11,
1376 attribute12 = l_resource_rec.attribute12,
1377 attribute13 = l_resource_rec.attribute13,
1378 attribute14 = l_resource_rec.attribute14,
1379 attribute15 = l_resource_rec.attribute15,
1380 principle_flag = l_resource_rec.principle_flag,
1381 request_id = l_RequestId,
1382 program_application_id = l_ApplicationId,
1383 program_id = l_ProgramId,
1384 program_update_date = l_ProgramUpdate
1385 where operation_sequence_id = l_resource_rec.operation_sequence_id
1386 and resource_seq_num = l_resource_rec.resource_seq_num;
1387
1388 x_resource_rec := l_resource_rec;
1389 -- End of API body.
1390
1391 -- Standard check of p_commit.
1392 IF FND_API.To_Boolean(p_commit) THEN
1393 COMMIT WORK;
1394 END IF;
1395 -- Standard call to get message count and if count is 1, get message info.
1396 FND_MSG_PUB.Count_And_Get(
1397 p_count => x_msg_count,
1398 p_data => x_msg_data
1399 );
1400 EXCEPTION
1401 WHEN FND_API.G_EXC_ERROR THEN
1402 ROLLBACK TO UpdateResource_Pvt;
1403 x_return_status := FND_API.G_RET_STS_ERROR;
1404 FND_MSG_PUB.Count_And_Get(
1405 p_count => x_msg_count,
1406 p_data => x_msg_data
1407 );
1408 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1409 ROLLBACK TO UpdateResource_Pvt;
1410 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1411 FND_MSG_PUB.Count_And_Get(
1412 p_count => x_msg_count,
1413 p_data => x_msg_data
1414 );
1415 WHEN OTHERS THEN
1416 ROLLBACK TO UpdateResource_Pvt;
1417 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1418 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1419 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1420 END IF;
1421 FND_MSG_PUB.Count_And_Get(
1422 p_count => x_msg_count,
1423 p_data => x_msg_data
1424 );
1425 END UpdateResource;
1426 PROCEDURE DeleteResource(
1427 p_api_version IN NUMBER,
1428 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1429 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1430 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1431 x_return_status IN OUT NOCOPY VARCHAR2,
1432 x_msg_count IN OUT NOCOPY NUMBER,
1433 x_msg_data IN OUT NOCOPY VARCHAR2,
1434 p_resource_rec IN RESOURCE_REC_TYPE := G_MISS_RESOURCE_REC,
1435 x_resource_rec IN OUT NOCOPY RESOURCE_REC_TYPE
1436 ) IS
1437 l_api_name CONSTANT VARCHAR2(30) := 'DeleteResource';
1438 l_api_version CONSTANT NUMBER := 1.0;
1439 l_resource_rec RESOURCE_REC_TYPE;
1440 l_return_status VARCHAR2(1);
1441 l_msg_count NUMBER;
1442 l_msg_data VARCHAR2(2000);
1443 BEGIN
1444 -- Standard Start of API savepoint
1445 SAVEPOINT DeleteResource_Pvt;
1446 -- Standard call to check for call compatibility.
1447 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
1448 G_PKG_NAME) THEN
1449 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1450 END IF;
1451 -- Initialize message list if p_init_msg_list is set to TRUE.
1452 IF FND_API.to_Boolean(p_init_msg_list) THEN
1453 FND_MSG_PUB.initialize;
1454 END IF;
1455 -- Initialize API return status to success
1456 x_return_status := FND_API.G_RET_STS_SUCCESS;
1457
1458 -- API body
1459 l_resource_rec := p_resource_rec;
1460
1461 If p_validation_level = FND_API.G_VALID_LEVEL_FULL then
1462 AssignResource(
1463 p_api_version => 1,
1464 p_init_msg_list => p_init_msg_list,
1465 p_commit => p_commit,
1466 p_validation_level => p_validation_level,
1467 x_return_status => l_return_status,
1468 x_msg_count => l_msg_count,
1469 x_msg_data => l_msg_data,
1470 p_resource_rec => l_resource_rec,
1471 x_resource_rec => l_resource_rec
1472 );
1473 If l_return_status = FND_API.G_RET_STS_ERROR then
1474 Raise FND_API.G_EXC_ERROR;
1475 Elsif l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
1476 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
1477 End if; -- assign error
1478 End If; -- assign
1479
1480 x_resource_rec := l_resource_rec;
1481
1482 delete bom_operation_resources
1483 where operation_sequence_id = l_resource_rec.operation_sequence_id
1484 and resource_seq_num = l_resource_rec.resource_seq_num;
1485
1486 If sql%notfound then
1487 Fnd_Message.Set_Name('BOM', 'BOM_INVALID_OP_RESOURCE');
1488 FND_MSG_PUB.Add;
1489 Raise FND_API.G_EXC_ERROR;
1490 End if; -- missing op resource
1491
1492 -- End of API body.
1493
1494 -- Standard check of p_commit.
1495 IF FND_API.To_Boolean(p_commit) THEN
1496 COMMIT WORK;
1497 END IF;
1498 -- Standard call to get message count and if count is 1, get message info.
1499 FND_MSG_PUB.Count_And_Get(
1500 p_count => x_msg_count,
1501 p_data => x_msg_data
1502 );
1503 EXCEPTION
1504 WHEN FND_API.G_EXC_ERROR THEN
1505 ROLLBACK TO DeleteResource_Pvt;
1506 x_return_status := FND_API.G_RET_STS_ERROR;
1507 FND_MSG_PUB.Count_And_Get(
1508 p_count => x_msg_count,
1509 p_data => x_msg_data
1510 );
1511 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1512 ROLLBACK TO DeleteResource_Pvt;
1513 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1514 FND_MSG_PUB.Count_And_Get(
1515 p_count => x_msg_count,
1516 p_data => x_msg_data
1517 );
1518 WHEN OTHERS THEN
1519 ROLLBACK TO DeleteResource_Pvt;
1520 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1521 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1522 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1523 END IF;
1524 FND_MSG_PUB.Count_And_Get(
1525 p_count => x_msg_count,
1526 p_data => x_msg_data
1527 );
1528 END DeleteResource;
1529 END Bom_OpResource_Pvt;