1 package body ont_icp_process_package as
2 /* $Header: ONTPROCB.pls 120.4 2005/09/28 00:38:13 shewgupt ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'ONT_ICP_PROCESS_PACKAGE' ;
5
6 --Below procedures/functions obsoleted after inventory convergence project
7 /*
8 function is_process_item(p_inventory_item_id in number,
9 p_ship_from_org_id in number) return number is
10 dummy_for_x char(1);
11 x_item_rec OE_ORDER_CACHE.item_rec_type;
12 --
13 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
14 --
15 begin
16 x_item_rec := OE_Order_Cache.Load_Item (p_inventory_item_id
17 ,p_ship_from_org_id);
18
19 IF x_item_rec.process_warehouse_flag = 'Y' AND
20 x_item_rec.dualum_ind in (0,1,2,3) THEN
21 return 1;
22 END IF;
23 return 0;
24
25 EXCEPTION
26 when others then
27 return 0;
28 end is_process_item;
29
30 procedure is_process_installed (p_return out nocopy number) is
31
32 v_return boolean;
33 v_status varchar2(100);
34 v_industry varchar2(100);
35 --
36 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
37 --
38 begin
39 v_return := fnd_installation.get(555,555,v_status,v_industry);
40 if v_return=TRUE then
41 p_return := 1;
42 else
43 p_return := 0;
44
45 end if;
46 end is_process_installed;
47
48
49 function get_itemid(p_organization_id IN NUMBER, p_inventory_item_id IN NUMBER) return number is
50
51 Cursor getitem(org_id number, item_id number) is
52 SELECT item_id
53 FROM ic_item_mst
54 WHERE delete_mark = 0
55 AND item_no in (SELECT segment1
56 FROM mtl_system_items
57 WHERE organization_id = org_id
58 AND inventory_item_id = item_id);
59
60 rItem getitem%ROWTYPE;
61 vItem number;
62
63 --
64 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
65 --
66 BEGIN
67
68 OPEN getitem(p_organization_id, p_inventory_item_id);
69 fetch getitem INTO rItem;
70 CLOSE getitem;
71
72 vItem := rItem.item_id;
73 return vItem;
74
75 EXCEPTION
76 when others then
77 return 0;
78 end get_itemid;
79
80
81 function get_lotid(p_inv_itemid IN NUMBER, p_orgid IN NUMBER, p_lot_number IN varchar2, p_sublot_number in varchar2) return number is
82
83 l_item_id ic_tran_pnd.item_id%TYPE;
84 l_lot_id ic_tran_pnd.lot_id%TYPE;
85
86 --
87 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
88 --
89 BEGIN
90
91 SELECT iim.item_id INTO l_item_id
92 FROM ic_item_mst iim,
93 mtl_system_items msi
94 WHERE msi.inventory_item_id = p_inv_itemid
95 AND msi.organization_id = p_orgid
96 AND msi.segment1 = iim.item_no;
97
98
99 IF p_lot_number IS NULL
100 THEN
101 l_lot_id := 0;
102 ELSIF p_sublot_number IS NULL
103 THEN
104 SELECT lot_id INTO l_lot_id
105 FROM ic_lots_mst
106 WHERE item_id = l_item_id
107 AND lot_no = p_lot_number;
108 ELSE
109 SELECT lot_id INTO l_lot_id
110 FROM ic_lots_mst
111 WHERE item_id = l_item_id
112 AND lot_no = p_lot_number
113 AND sublot_no = p_sublot_number;
114 END IF;
115
116 return l_lot_id;
117
118 EXCEPTION
119 when others then
120 return 0;
121 end get_lotid; */
122
123
124 --procedure added for inventory convergence project
125
126 procedure dual_uom_and_grade_control
127 (
128 p_inventory_item_id IN NUMBER ,
129 p_ship_from_org_id IN NUMBER := FND_API.G_MISS_NUM,
130 p_org_id IN NUMBER ,
131 x_dual_control_flag OUT NOCOPY VARCHAR2 ,
132 x_grade_control_flag OUT NOCOPY VARCHAR2,
133 x_wms_enabled_flag OUT NOCOPY VARCHAR2
134 )
135 IS
136 l_debug_level constant NUMBER := oe_debug_pub.g_debug_level ;
137 l_item_rec OE_Order_Cache.item_rec_type ;
138 BEGIN
139
140 /* if both inventory_item_id and ship_from_org_id are passed then
141 check id the item is dual_uom/grade controlled */
142 IF ( p_inventory_item_id IS NOT NULL and
143 p_inventory_item_id <> FND_API.G_MISS_NUM
144 --p_ship_from_org_id IS NOT NULL and
145 --p_ship_from_org_id <> FND_API.G_MISS_NUM
146 )
147 THEN
148 l_item_rec := OE_Order_Cache.Load_Item( p_key1 => p_inventory_item_id
149 ,p_key2 => p_ship_from_org_id
150 ,p_key3 => p_org_id) ;
151 if (l_debug_level >0 ) then
152 oe_debug_pub.add('Entering dual_uom_and_grade_control - tracking_quantity_ind ='||
153 l_item_rec.tracking_quantity_ind ) ;
154 end if ;
155 IF l_item_rec.tracking_quantity_ind = 'PS' THEN
156 x_dual_control_flag := 'Y' ;
157 if (l_debug_level >0 ) then
158 oe_debug_pub.add('Dual UOM control is true');
159 end if ;
160 ELSE
161 x_dual_control_flag := 'N' ;
162 if (l_debug_level >0 ) then
163 oe_debug_pub.add('Dual UOM control is false');
164 end if ;
165 END IF ;
166
167 IF l_item_rec.grade_control_flag = 'Y' THEN
168 x_grade_control_flag := 'Y' ;
169 if (l_debug_level >0 ) then
170 oe_debug_pub.add('Grade control is true');
171 end if ;
172 ELSE
173 x_grade_control_flag := 'N' ;
174 if (l_debug_level >0 ) then
175 oe_debug_pub.add('Grade control is false');
176 end if ;
177 END IF ;
178
179 x_wms_enabled_flag := l_item_rec.wms_enabled_flag ;
180 if (l_debug_level >0 ) then
181 oe_debug_pub.add('WMS Enabled Flag :'||x_wms_enabled_flag);
182 end if ;
183 END IF ;
184 EXCEPTION
185 WHEN NO_DATA_FOUND THEN
186 x_grade_control_flag := 'N' ;
187 x_dual_control_flag := 'N' ;
188 x_wms_enabled_flag := 'N' ;
189 if (l_debug_level >0 ) then
190 oe_debug_pub.add('no_data_found in dual_uom_and_grade_control');
191 end if ;
192 WHEN OTHERS THEN
193 if OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
194 then
195 OE_MSG_PUB.Add_Exc_Msg
196 ( G_PKG_NAME ,
197 'Dual_Uom_And_Grade_Control'
198 ) ;
199 end if ;
200 if (l_debug_level >0 ) then
201 oe_debug_pub.add('Others in dual_uom_and_grade_control');
202 end if ;
203 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
204
205 END dual_uom_and_grade_control ;
206
207 end ont_icp_process_package;
[Home] [Help]