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