1 PACKAGE BODY MSC_ATO_FORECAST AS -- body
2 /* $Header: MSCATOFB.pls 115.0 2003/07/10 04:10:26 pmotewar noship $ */
3
4 FUNCTION OC_COM_RT_EXISTS (
5 p_inventory_item_id IN NUMBER,
6 p_org_id IN NUMBER,
7 p_sr_instance_id IN NUMBER,
8 p_routing_sequence_id IN NUMBER,
9 p_bom_item_type IN NUMBER
10 ) RETURN NUMBER
11 IS
12 G_OPTION_CLASS NUMBER := 2;
13 G_MODEL NUMBER := 1;
14 l_parent_item_id NUMBER;
15 l_common_rout_seq_id NUMBER;
16 l_parent_item_type NUMBER;
17 SYS_YES NUMBER := 1;
18 SYS_NO NUMBER := 2;
19
20 BEGIN
21
22 /* Return FALSE if the item is not an Option Class */
23 IF (nvl(p_bom_item_type,-1) <> G_OPTION_CLASS) THEN
24 RETURN SYS_NO;
25 END IF;
26
27 /* Get the common_routing_sequence_id for given option class */
28 SELECT common_routing_sequence_id into l_common_rout_seq_id
29 from msc_routings mr
30 where mr.plan_id = -1
31 and mr.sr_instance_id = p_sr_instance_id
32 and mr.organization_id = p_org_id
33 and mr.assembly_item_id = p_inventory_item_id;
34
35 /* Return FALSE if the item does not have common routing sequence */
36 IF (nvl(l_common_rout_seq_id, -1) = -1) THEN
37 return SYS_NO;
38 END IF;
39
40 /* If common routing exists then return true only if the assembly item of this common
41 routing is an ATO model */
42 select bom_item_type into l_parent_item_type
43 from msc_system_items msi,
44 msc_routings mr
45 where mr.routing_sequence_id = l_common_rout_seq_id
46 and mr.sr_instance_id = p_sr_instance_id
47 and mr.plan_id = -1
48 and mr.organization_id = p_org_id
49 and msi.inventory_item_id = mr.assembly_item_id
50 and msi.sr_instance_id = mr.sr_instance_id
51 and msi.organization_id = mr.organization_id
52 and msi.plan_id = mr.plan_id;
53
54 IF (nvl(l_parent_item_type, -1) = G_MODEL) THEN
55 return SYS_YES;
56 ELSE
57 return SYS_NO;
58 END IF;
59
60 EXCEPTION WHEN OTHERS THEN
61 RETURN SYS_NO;
62 END OC_COM_RT_EXISTS;
63
64 END MSC_ATO_FORECAST;