[Home] [Help]
PACKAGE BODY: APPS.EAM_MATERIAL_ALLOCQTY_PKG
Source
1 PACKAGE BODY EAM_MATERIAL_ALLOCQTY_PKG as
2 /* $Header: EAMMRALB.pls 120.5.12020000.2 2012/07/05 10:44:52 vpasupur ship $ */
3
4 --This function returns allocated quantity by querying from inv. tables
5 FUNCTION allocated_quantity(p_wip_entity_id IN NUMBER,
6 p_operation_seq_num IN NUMBER,
7 p_organization_id IN NUMBER,
8 p_inventory_item_id IN NUMBER)
9 return NUMBER
10 IS
11 l_quantity_allocated NUMBER;
12 l_line_status NUMBER;
13 l_move_order_type NUMBER;
14 BEGIN
15 l_line_status := INV_GLOBALS.G_TO_STATUS_PREAPPROVED;
16 l_move_order_type := 5;
17
18 begin
19 select sum(nvl(mtrl.quantity_detailed,0) - nvl(mtrl.quantity_delivered,0))
20 into l_quantity_allocated
21 from MTL_TXN_REQUEST_LINES mtrl,MTL_TXN_REQUEST_HEADERS mtrh
22 where
23 mtrl.TXN_SOURCE_ID = p_wip_entity_id and
24 mtrl.TXN_SOURCE_LINE_ID = p_operation_seq_num and
25 mtrl.organization_id = p_organization_id and
26 mtrl.INVENTORY_ITEM_ID = p_inventory_item_id and
27 -- preapproved status or open lines
28 mtrl.line_status = l_line_status
29 and mtrl.header_id = mtrh.header_id
30 and mtrh.move_order_type=l_move_order_type
31 group by mtrl.organization_id, mtrl.TXN_SOURCE_ID,
32 mtrl.TXN_SOURCE_LINE_ID, mtrl.INVENTORY_ITEM_ID;
33 exception
34 when NO_DATA_FOUND then
35 l_quantity_allocated := 0;
36 end;
37
38 return l_quantity_allocated;
39 END allocated_quantity;
40
41 --This function returns open quantity.If open qty is less than 0,it returns 0
42 FUNCTION open_quantity(p_wip_entity_id IN NUMBER,
43 p_operation_seq_num IN NUMBER,
44 p_organization_id IN NUMBER,
45 p_inventory_item_id IN NUMBER,
46 p_required_quantity IN NUMBER,
47 p_quantity_issued IN NUMBER)
48 return NUMBER
49 IS
50 l_quantity_allocated NUMBER;
51 l_open_quantity NUMBER;
52 l_line_status NUMBER;
53 l_move_order_type NUMBER;
54 BEGIN
55
56 l_line_status := INV_GLOBALS.G_TO_STATUS_PREAPPROVED;
57 l_move_order_type := 5;
58
59 begin
60 select sum(nvl(mtrl.quantity_detailed,0) - nvl(mtrl.quantity_delivered,0))
61 into l_quantity_allocated
62 from MTL_TXN_REQUEST_LINES mtrl,MTL_TXN_REQUEST_HEADERS mtrh
63 where
64 mtrl.TXN_SOURCE_ID = p_wip_entity_id and
65 mtrl.TXN_SOURCE_LINE_ID = p_operation_seq_num and
66 mtrl.organization_id = p_organization_id and
67 mtrl.INVENTORY_ITEM_ID = p_inventory_item_id and
68 -- preapproved status or open lines
69 mtrl.line_status =l_line_status
70 and mtrl.header_id = mtrh.header_id
71 and mtrh.move_order_type=l_move_order_type
72 group by mtrl.organization_id, mtrl.TXN_SOURCE_ID,
73 mtrl.TXN_SOURCE_LINE_ID, mtrl.INVENTORY_ITEM_ID;
74 exception
75 when NO_DATA_FOUND then
76 l_quantity_allocated := 0;
77 end;
78
79 l_open_quantity := p_required_quantity-NVL(p_quantity_issued,0)-l_quantity_allocated;
80
81 IF(l_open_quantity<0) THEN
82 l_open_quantity := 0;
83 END IF;
84
85 return l_open_quantity;
86
87 END open_quantity;
88
89 --This will call the function allocated_quantity to find the quantity allocated
90 PROCEDURE quantity_allocated(p_wip_entity_id IN NUMBER,
91 p_operation_seq_num IN NUMBER,
92 p_organization_id IN NUMBER,
93 p_inventory_item_id IN NUMBER,
94 x_quantity_allocated OUT NOCOPY NUMBER)
95 IS
96 BEGIN
97
98 x_quantity_allocated := allocated_quantity(p_wip_entity_id => p_wip_entity_id,
99 p_operation_Seq_num => p_operation_seq_num,
100 p_organization_id => p_organization_id,
101 p_inventory_item_id => p_inventory_item_id);
102
103 END quantity_allocated;
104
105 --This will call Eam_Common_Utilties_Pvt.Get_OnHand_Quant to find the on_hand_qty and available quantity
106 --for an inventory item
107 PROCEDURE get_onhand_avail_quant(p_organization_id IN NUMBER,
108 p_inventory_item_id IN NUMBER,
109 p_subinventory_code IN VARCHAR2 DEFAULT NULL, --12.1 source sub project
110 x_onhand_quant OUT NOCOPY NUMBER,
111 x_avail_quant OUT NOCOPY NUMBER)
112 IS
113 CURSOR get_material_details(organization_id NUMBER,inventory_item_id NUMBER) IS
114 SELECT
115 mtlbkfv.lot_control_code,
116 mtlbkfv.serial_number_control_code,
117 mtlbkfv.revision_qty_control_code
118 FROM mtl_system_items_b_kfv mtlbkfv
119 WHERE mtlbkfv.organization_id = p_organization_id
120 AND mtlbkfv.inventory_item_id = p_inventory_item_id;
121
122 l_is_revision_control BOOLEAN;
123 l_is_lot_control BOOLEAN;
124 l_is_serial_control BOOLEAN;
125 l_qoh NUMBER;
126 l_rqoh NUMBER;
127 l_qr NUMBER;
128 l_qs NUMBER;
129 l_att NUMBER;
130 l_atr NUMBER;
131 l_return_status VARCHAR2(1);
132 l_msg_count NUMBER;
133 l_msg_data VARCHAR2(1000);
134 X_QOH_PROFILE_VALUE NUMBER; --bug 6263104
135 BEGIN -- for FP reconcilation begin changes for 6263104
136
137 BEGIN
138 X_QOH_PROFILE_VALUE := TO_NUMBER(FND_PROFILE.VALUE('EAM_REQUIREMENT_QOH_OPTION'));
139
140 IF (X_QOH_PROFILE_VALUE IS NULL)
141 THEN
142 X_QOH_PROFILE_VALUE := 1;
143 END IF;
144
145
146 FOR p_materials_csr IN get_material_details(p_organization_id,p_inventory_item_id)
147 LOOP
148 IF (p_materials_csr.revision_qty_control_code = 2) THEN
149 l_is_revision_control:=TRUE;
150 ELSE
151 l_is_revision_control:=FALSE;
152 END IF;
153
154 IF (p_materials_csr.lot_control_code = 2) THEN
155 l_is_lot_control:=TRUE;
156 ELSE
157 l_is_lot_control:=FALSE;
158 END IF;
159
160 IF (p_materials_csr.serial_number_control_code = 1) THEN
161 l_is_serial_control:=FALSE;
162 ELSE
163 l_is_serial_control:=TRUE;
164 END IF;
165
166 END LOOP;
167
168 IF X_QOH_PROFILE_VALUE = 1 THEN --for FP reconcilation end changes for 6263104
169 inv_quantity_tree_pub.clear_quantity_cache;
170 INV_QUANTITY_TREE_PUB.QUERY_QUANTITIES
171 ( p_api_version_number => 1.0
172 , p_init_msg_lst => FND_API.G_TRUE
173 , x_return_status => l_return_status
174 , x_msg_count => l_msg_count
175 , x_msg_data => l_msg_data
176 , p_organization_id => p_organization_id
177 , p_inventory_item_id => p_inventory_item_id
178 , p_tree_mode => 2 --available to transact
179 , p_is_revision_control => l_is_revision_control
180 , p_is_lot_control => l_is_lot_control
181 , p_is_serial_control => l_is_serial_control
182 , p_revision => NULL
183 , p_lot_number => NULL
184 , p_subinventory_code => p_subinventory_code
185 , p_locator_id => NULL
186 , p_onhand_source => inv_quantity_tree_pvt.g_all_subs
187 , x_qoh => l_qoh
188 , x_rqoh => l_rqoh
189 , x_qr => l_qr
190 , x_qs => l_qs
191 , x_att => l_att
192 , x_atr => l_atr
193 );
194 ELSE
195 INV_QUANTITY_TREE_PUB.QUERY_QUANTITIES
196 ( p_api_version_number => 1.0
197 , p_init_msg_lst => FND_API.G_TRUE
198 , x_return_status => l_return_status
199 , x_msg_count => l_msg_count
200 , x_msg_data => l_msg_data
201 , p_organization_id => p_organization_id
202 , p_inventory_item_id => p_inventory_item_id
203 , p_tree_mode => 2 --available to transact
204 , p_is_revision_control => l_is_revision_control
205 , p_is_lot_control => l_is_lot_control
206 , p_is_serial_control => l_is_serial_control
207 , p_revision => NULL
208 , p_lot_number => NULL
209 , p_subinventory_code => p_subinventory_code
210 , p_locator_id => NULL
211 , p_onhand_source => inv_quantity_tree_pvt.g_nettable_only
212 , x_qoh => l_qoh
213 , x_rqoh => l_rqoh
214 , x_qr => l_qr
215 , x_qs => l_qs
216 , x_att => l_att
217 , x_atr => l_atr
218 );
219 END IF;
220
221 IF(l_return_status <> 'S') THEN
222 x_avail_quant := 0;
223 x_onhand_quant := 0;
224 RETURN;
225 END IF;
226
227 EXCEPTION
228 WHEN OTHERS THEN
229 x_avail_quant := 0;
230 x_onhand_quant := 0;
231 RETURN;
232 END;
233
234 x_avail_quant := l_att;
235 x_onhand_quant := l_qoh;
236
237 END get_onhand_avail_quant;
238
239
240 END EAM_MATERIAL_ALLOCQTY_PKG;