1 PACKAGE BODY inv_rcv_availability AS
2 /* $Header: INVRCVAB.pls 120.4 2006/06/05 22:50:31 mankuma noship $*/
3
4 PROCEDURE print_debug(p_err_msg VARCHAR2
5 ,p_module IN VARCHAR2 := ' '
6 ,p_level NUMBER := 4)
7 IS
8 l_debug NUMBER;
9 BEGIN
10 l_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
11 inv_mobile_helper_functions.tracelog
12 (p_err_msg => p_err_msg
13 ,p_module => 'inv_rcv_availability'
14 ,p_level => p_level);
15 END;
16
17 PROCEDURE get_available_supply_demand(
18 x_return_status OUT NOCOPY VARCHAR2,
19 x_msg_count OUT NOCOPY NUMBER,
20 x_msg_data OUT NOCOPY VARCHAR2,
21 x_available_quantity OUT NOCOPY NUMBER,
22 x_source_uom_code OUT NOCOPY VARCHAR2,
23 x_source_primary_uom_code OUT NOCOPY VARCHAR2,
24 p_supply_demand_code IN NUMBER,
25 p_organization_id IN NUMBER,
26 p_item_id IN NUMBER,
27 p_revision IN VARCHAR2,
28 p_lot_number IN VARCHAR2,
29 p_subinventory_code IN VARCHAR2,
30 p_locator_id IN NUMBER,
31 p_supply_demand_type_id IN NUMBER,
32 p_supply_demand_header_id IN NUMBER,
33 p_supply_demand_line_id IN NUMBER,
34 p_supply_demand_line_detail IN NUMBER,
35 p_lpn_id IN NUMBER,
36 p_project_id IN NUMBER,
37 p_task_id IN NUMBER,
38 p_api_version_number IN NUMBER,
39 p_init_msg_lst IN VARCHAR2
40 ) IS
41 l_debug NUMBER ;
42 l_progress VARCHAR2(10);
43 l_module_name VARCHAR2(30);
44
45 l_rti_primary_quantity NUMBER;
46 l_mol_primary_qty NUMBER;
47 l_supply_prim_qty NUMBER;
48
49 BEGIN
50
51 l_debug := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
52 l_progress := '10';
53 l_module_name := 'get_available_supply_demand';
54
55 IF (l_debug = 1) THEN
56 print_debug('Entering inv_rcv_availability',l_module_name,11);
57 print_debug(' p_supply_demand_code => '|| p_supply_demand_code);
58 print_debug(' p_organization_id => '|| p_organization_id);
59 print_debug(' p_item_id => '|| p_item_id);
60 print_debug(' p_revision => '|| p_revision);
61 print_debug(' p_lot_number => '|| p_lot_number);
62 print_debug(' p_subinventory_code => '|| p_subinventory_code);
63 print_debug(' p_locator_id => '|| p_locator_id);
64 print_debug(' p_supply_demand_type_id => '|| p_supply_demand_type_id);
65 print_debug(' p_supply_demand_header_id => '|| p_supply_demand_header_id);
66 print_debug(' p_supply_demand_line_id => '|| p_supply_demand_line_id);
67 print_debug(' p_supply_demand_line_detail=> '|| p_supply_demand_line_detail);
68 print_debug(' p_lpn_id => '|| p_lpn_id);
69 print_debug(' p_project_id => '|| p_project_id);
70 print_debug(' p_task_id => '|| p_task_id);
71 END IF;
72
73 x_return_status := FND_API.g_ret_sts_success;
74
75 --Query the total on hand quantity in receiving.
76
77 BEGIN
78 SELECT Nvl(SUM(rs.to_org_primary_quantity),0)
79 INTO l_supply_prim_qty
80 FROM rcv_supply rs
81 , rcv_transactions rt
82 WHERE rs.supply_source_id = rt.transaction_id
83 AND rs.supply_type_code = 'RECEIVING'
84 AND rs.to_organization_id = p_organization_id
85 AND rs.item_id = p_item_id
86
87 --10/04/05: Reservations are not created on the revision level, so
88 --when availability API is called, revision maybe NULL. In
89 --that case, don't use revision as a query criteria
90 AND Nvl(rs.item_revision,'*&@') = Nvl(p_revision,Nvl(rs.item_revision,'*&@'));
91 EXCEPTION
92 WHEN OTHERS THEN
93 IF (l_debug = 1) THEN
94 print_debug('Exception occurred at progress:'||l_progress,l_module_name,11);
95 print_debug('SQLCODE:'||SQLCODE||' SQLERRM:'||SQLERRM,l_module_name,11);
96 END IF;
97 l_supply_prim_qty := 0;
98 END;
99
100 IF (l_debug = 1) THEN
101 print_debug('l_supply_prim_qty:'||l_supply_prim_qty);
102 END IF;
103
104 l_progress := '20';
105
106 --Query the total MOL quantity which is being crossdocked to wip
107
108 BEGIN
109 SELECT Nvl(SUM((quantity-Nvl(quantity_delivered,0))*primary_quantity/quantity),0)
110 INTO l_mol_primary_qty
111 FROM mtl_txn_request_lines mtrl
112 , mtl_txn_request_headers mtrh
113 WHERE mtrh.header_id = mtrl.header_id
114 AND mtrh.move_order_type = 6
115 AND mtrl.organization_id = p_organization_id
116 AND mtrl.inventory_item_id = p_item_id
117
118 --10/04/05: Reservations are not created on the revision level, so
119 --when availability API is called, revision maybe NULL. In
120 --that case, don't use revision as a query criteria
121 AND Nvl(mtrl.revision,'@#@') = Nvl(p_revision,Nvl(mtrl.revision,'@#@'))
122 AND Nvl(mtrl.crossdock_type, -1) = 2
123 AND mtrl.line_status = 7
124 AND mtrl.quantity - Nvl(mtrl.quantity_delivered,0) > 0;
125 EXCEPTION
126 WHEN OTHERS THEN
127 IF (l_debug = 1) THEN
128 print_debug('Exception occurred at progress:'||l_progress,l_module_name,11);
129 print_debug('SQLCODE:'||SQLCODE||' SQLERRM:'||SQLERRM,l_module_name,11);
130 END IF;
131 l_mol_primary_qty := 0;
132 END;
133
134 IF (l_debug = 1) THEN
135 print_debug('l_mol_primary_qty:'||l_mol_primary_qty);
136 END IF;
137
138 --Must subtract the transaction quantity for pending
139 --deliver/return to vendor/correction transactions
140
141 BEGIN
142 SELECT Nvl(ABS(SUM(primary_quantity)),0)
143 INTO l_rti_primary_quantity
144 FROM rcv_transactions_interface rti
145 WHERE to_organization_id = p_organization_id
146 AND item_id = p_item_id
147
148 --10/04/05: Reservations are not created on the revision level, so
149 --when availability API is called, revision maybe NULL. In
150 --that case, don't use revision as a query criteria
151 AND NVL(item_revision, '@@@') = NVL(p_revision,NVL(item_revision, '@@@'))
152 AND rti.processing_status_code <> 'ERROR'
153 AND rti.transaction_status_code <> 'ERROR'
154 AND NOT exists (SELECT '1' FROM rcv_transactions rt
155 WHERE rt.interface_transaction_id = rti.interface_transaction_id)
156 AND (TRANSACTION_TYPE = 'DELIVER'
157 OR (TRANSACTION_TYPE IN ('RETURN TO VENDOR','RETURN TO CUSTOMER')
158 AND EXISTS (SELECT '1' FROM rcv_transactions rt
159 WHERE rt.transaction_id = rti.parent_transaction_id
160 AND rt.transaction_type IN ('RECEIVE','ACCEPT','REJECT','TRANSFER')))
161 OR (TRANSACTION_TYPE IN ('CORRECT')
162 AND quantity < 0
163 AND EXISTS (SELECT '1' FROM rcv_transactions rt
164 WHERE rt.transaction_id = rti.parent_transaction_id
165 AND rt.transaction_type IN ('RECEIVE')))
166 OR (TRANSACTION_TYPE IN ('CORRECT')
167 AND quantity > 0
168 AND EXISTS (SELECT '1' FROM rcv_transactions rt
169 WHERE rt.transaction_id = rti.parent_transaction_id
170 AND rt.transaction_type IN ('DELIVER'))));
171 EXCEPTION
172 WHEN OTHERS THEN
173 IF (l_debug = 1) THEN
174 print_debug('Exception occurred at progress:'||l_progress,l_module_name,11);
175 print_debug('SQLCODE:'||SQLCODE||' SQLERRM:'||SQLERRM,l_module_name,11);
176 END IF;
177 l_rti_primary_quantity := 0;
178 END;
179
180 IF (l_debug = 1) THEN
181 print_debug('l_rti_primary_quantity:'||l_rti_primary_quantity);
182 END IF;
183
184 x_available_quantity := l_supply_prim_qty - l_mol_primary_qty - l_rti_primary_quantity;
185
186 BEGIN
187 SELECT primary_uom_code
188 INTO x_source_primary_uom_code
189 FROM mtl_system_items
190 WHERE organization_id = p_organization_id
191 AND inventory_item_id = p_item_id;
192 EXCEPTION
193 WHEN OTHERS THEN
194 IF (l_debug = 1) THEN
195 print_debug('Exception occurred at progress:'||l_progress,l_module_name,11);
196 print_debug('SQLCODE:'||SQLCODE||' SQLERRM:'||SQLERRM,l_module_name,11);
197 END IF;
198 END;
199
200 x_source_uom_code := x_source_primary_uom_code;
201
202 IF (l_debug = 1) THEN
203 print_debug('Exitting get_available_supply_demand with success',l_module_name,11);
204 print_debug('x_available_quantity = '||x_available_quantity,l_module_name,11);
205 print_debug('x_source_uom_code = '||x_source_uom_code,l_module_name,11);
206 END IF;
207
208 EXCEPTION
209 WHEN OTHERS THEN
210 IF (l_debug = 1) THEN
211 print_debug('Exception occurred at progress:'||l_progress,l_module_name,11);
212 print_debug('SQLCODE:'||SQLCODE||' SQLERRM:'||SQLERRM,l_module_name,11);
213 END IF;
214 x_return_status := fnd_api.g_ret_sts_error;
215 END get_available_supply_demand;
216 END inv_rcv_availability;
[Home] [Help]