[Home] [Help]
PACKAGE BODY: APPS.EAM_MATERIAL_ALLOCQTY_PKG
Source
1 PACKAGE BODY EAM_MATERIAL_ALLOCQTY_PKG as
2 /* $Header: EAMMRALB.pls 120.1.12010000.2 2008/11/28 06:26:33 vchidura 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 X_QOH_PROFILE_VALUE := TO_NUMBER(FND_PROFILE.VALUE('EAM_REQUIREMENT_QOH_OPTION'));
137 IF (X_QOH_PROFILE_VALUE IS NULL)
138 THEN
139 X_QOH_PROFILE_VALUE := 1;
140 END IF;
141
142 IF X_QOH_PROFILE_VALUE = 1 THEN --for FP reconcilation end changes for 6263104
143
144 BEGIN
145 FOR p_materials_csr IN get_material_details(p_organization_id,p_inventory_item_id)
146 LOOP
147 IF (p_materials_csr.revision_qty_control_code = 2) THEN
148 l_is_revision_control:=TRUE;
149 ELSE
150 l_is_revision_control:=FALSE;
151 END IF;
152
153 IF (p_materials_csr.lot_control_code = 2) THEN
154 l_is_lot_control:=TRUE;
155 ELSE
156 l_is_lot_control:=FALSE;
157 END IF;
158
159 IF (p_materials_csr.serial_number_control_code = 1) THEN
160 l_is_serial_control:=FALSE;
161 ELSE
162 l_is_serial_control:=TRUE;
163 END IF;
164
165 END LOOP;
166
167 INV_QUANTITY_TREE_PUB.QUERY_QUANTITIES
168 ( p_api_version_number => 1.0
169 , p_init_msg_lst => FND_API.G_TRUE
170 , x_return_status => l_return_status
171 , x_msg_count => l_msg_count
172 , x_msg_data => l_msg_data
173 , p_organization_id => p_organization_id
174 , p_inventory_item_id => p_inventory_item_id
175 , p_tree_mode => 2 --available to transact
176 , p_is_revision_control => l_is_revision_control
177 , p_is_lot_control => l_is_lot_control
178 , p_is_serial_control => l_is_serial_control
179 , p_revision => NULL
180 , p_lot_number => NULL
181 , p_subinventory_code => p_subinventory_code
182 , p_locator_id => NULL
183 , x_qoh => l_qoh
184 , x_rqoh => l_rqoh
185 , x_qr => l_qr
186 , x_qs => l_qs
187 , x_att => l_att
188 , x_atr => l_atr
189 );
190
191 IF(l_return_status <> 'S') THEN
192 x_avail_quant := 0;
193 x_onhand_quant := 0;
194 RETURN;
195 END IF;
196
197 EXCEPTION
198 WHEN OTHERS THEN
199 x_avail_quant := 0;
200 x_onhand_quant := 0;
201 RETURN;
202 END;
203
204 ELSE
205 -- for reconciliation begin changes for 6263104
206 SELECT NVL(SUM(QUANTITY),0)
207 into l_qoh
208 FROM MTL_SECONDARY_INVENTORIES MSS,
209 MTL_ITEM_QUANTITIES_VIEW MOQ,
210 MTL_SYSTEM_ITEMS MSI
211 WHERE MOQ.ORGANIZATION_ID = p_organization_id
212 AND MSI.ORGANIZATION_ID = p_organization_id
213 AND MSS.ORGANIZATION_ID = p_organization_id
214 AND MOQ.INVENTORY_ITEM_ID = p_inventory_item_id
215 AND MSI.INVENTORY_ITEM_ID = MOQ.INVENTORY_ITEM_ID
216 AND MSS.SECONDARY_INVENTORY_NAME = MOQ.SUBINVENTORY_CODE
217 AND MSS.AVAILABILITY_TYPE = 1;
218 END IF;
219 --for reconciliation end changes for 6263104
220
221
222 x_avail_quant := l_att;
223 x_onhand_quant := l_qoh;
224
225 END get_onhand_avail_quant;
226
227
228 END EAM_MATERIAL_ALLOCQTY_PKG;