[Home] [Help]
PACKAGE BODY: APPS.WIP_RESERVATIONS_GRP
Source
1 PACKAGE BODY wip_reservations_grp AS
2 /* $Header: wipsogpb.pls 120.5 2006/07/07 23:58:01 kboonyap noship $ */
3
4 PROCEDURE get_available_supply_demand (
5 p_api_version_number IN NUMBER default 1.0
6 , p_init_msg_lst IN VARCHAR2
7 ,x_return_status OUT NOCOPY VARCHAR2
8 , x_msg_count OUT NOCOPY NUMBER
9 , x_msg_data OUT NOCOPY VARCHAR2
10 , x_available_quantity OUT NOCOPY NUMBER
11 , x_source_primary_uom_code OUT NOCOPY VARCHAR2
12 , x_source_uom_code OUT NOCOPY VARCHAR2
13 , p_organization_id IN NUMBER default null
14 , p_item_id IN NUMBER default null
15 , p_revision IN VARCHAR2 default null
16 , p_lot_number IN VARCHAR2 default null
17 , p_subinventory_code IN VARCHAR2 default null
18 , p_locator_id IN NUMBER default null
19 , p_supply_demand_code IN NUMBER
20 , p_supply_demand_type_id IN NUMBER
21 , p_supply_demand_header_id IN NUMBER
22 , p_supply_demand_line_id IN NUMBER
23 , p_supply_demand_line_detail IN NUMBER default FND_API.G_MISS_NUM
24 , p_lpn_id IN NUMBER default FND_API.G_MISS_NUM
25 , p_project_id IN NUMBER default null
26 , p_task_id IN NUMBER default null
27 , p_return_txn IN NUMBER default 0
28 ) IS
29 l_params wip_logger.param_tbl_t;
30 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
31 l_returnStatus VARCHAR2(1);
32 l_errMsg VARCHAR2(240);
33 l_msgCount NUMBER;
34 l_msgData VARCHAR2(2000);
35 l_crossDock_qty NUMBER;
36 BEGIN
37
38 -- write parameter value to log file
39 IF (l_logLevel <= wip_constants.trace_logging) THEN
40 l_params(1).paramName := ' p_organization_id';
41 l_params(1).paramValue := p_organization_id;
42 l_params(2).paramName := 'p_item_id';
43 l_params(2).paramValue := p_item_id;
44 l_params(3).paramName := 'p_revision';
45 l_params(3).paramValue := p_revision;
46 l_params(4).paramName := 'p_lot_number';
47 l_params(4).paramValue := p_lot_number;
48 l_params(5).paramName := 'p_subinventory_code';
49 l_params(5).paramValue := p_subinventory_code;
50 l_params(6).paramName := 'p_locator_id';
51 l_params(6).paramValue := p_locator_id;
52 l_params(7).paramName := 'p_supply_demand_code';
53 l_params(7).paramValue := p_supply_demand_code;
54 l_params(8).paramName := 'p_supply_demand_type_id';
55 l_params(8).paramValue := p_supply_demand_type_id;
56 l_params(9).paramName := 'p_supply_demand_header_id';
57 l_params(9).paramValue := p_supply_demand_header_id;
58 l_params(10).paramName := 'p_return_txn';
59 l_params(10).paramValue := p_return_txn;
60
61 wip_logger.entryPoint
62 (p_procName => 'wip_reservations_grp.get_available_supply_demand',
63 p_params => l_params,
64 x_returnStatus => l_returnStatus);
65 END IF;
66
67 x_return_status := fnd_api.g_ret_sts_success;
68
69 /* If API is called with parameter p_supply_demand_code being demand */
70 IF ( p_supply_demand_code = 1 ) THEN
71 IF ( p_return_txn = 0) THEN -- If it is normal transaction
72 SELECT
73 (WDJ.START_QUANTITY - WDJ.QUANTITY_COMPLETED - WDJ.QUANTITY_SCRAPPED) ,
74 MSIK.PRIMARY_UOM_CODE
75 INTO
76 x_available_quantity ,
77 x_source_primary_uom_code
78 FROM WIP_DISCRETE_JOBS WDJ ,
79 MTL_SYSTEM_ITEMS_KFV MSIK
80 WHERE WDJ.WIP_ENTITY_ID = p_supply_demand_header_id
81 AND WDJ.PRIMARY_ITEM_ID = MSIK.INVENTORY_ITEM_ID
82 AND WDJ.ORGANIZATION_ID = MSIK.ORGANIZATION_ID ;
83 x_source_uom_code := x_source_primary_uom_code ;
84
85 SELECT NVL(SUM(mtrl.primary_quantity),0)
86 INTO l_crossDock_qty
87 FROM mtl_txn_request_lines mtrl,
88 wms_license_plate_numbers wlpn
89 WHERE mtrl.organization_id = p_organization_id
90 AND mtrl.inventory_item_id = p_item_id
91 AND NVL(mtrl.quantity_delivered, 0) = 0
92 AND mtrl.txn_source_id = p_supply_demand_header_id
93 AND mtrl.lpn_id = wlpn.lpn_id
94 AND wlpn.lpn_context = 2 -- WIP
95 AND mtrl.line_status <> inv_globals.g_to_status_closed;
96
97 x_available_quantity := x_available_quantity + l_crossDock_qty ;
98 ELSE -- If it is return Transaction
99 SELECT
100 (WDJ.START_QUANTITY - WDJ.QUANTITY_COMPLETED - WDJ.QUANTITY_SCRAPPED) +
101 WDJ.QUANTITY_COMPLETED,
102 MSIK.PRIMARY_UOM_CODE
103 INTO
104 x_available_quantity ,
105 x_source_primary_uom_code
106 FROM WIP_DISCRETE_JOBS WDJ ,
107 MTL_SYSTEM_ITEMS_KFV MSIK
108 WHERE WDJ.WIP_ENTITY_ID = p_supply_demand_header_id
109 AND WDJ.PRIMARY_ITEM_ID = MSIK.INVENTORY_ITEM_ID
110 AND WDJ.ORGANIZATION_ID = MSIK.ORGANIZATION_ID ;
111 x_source_uom_code := x_source_primary_uom_code ;
112
113 END IF; -- End if for p_return_txn
114
115 ELSE
116 fnd_message.set_name('WIP', 'WIP_SUPPLY_SOURCE');
117 fnd_msg_pub.ADD;
118 x_return_status := fnd_api.g_ret_sts_error;
119 END IF;
120 IF (l_logLevel <= wip_constants.trace_logging) THEN
121 wip_logger.exitPoint(p_procName => 'wip_reservations_grp.get_available_supply_demand',
122 p_procReturnStatus => x_return_status,
123 p_msg => 'Success',
124 x_returnStatus => l_returnStatus);
125 END IF;
126 EXCEPTION
127 WHEN OTHERS THEN
128 x_return_status := fnd_api.g_ret_sts_unexp_error;
129 l_errMsg := 'unexpected error: ' || SQLERRM || 'SQLCODE = ' || SQLCODE;
130 IF (l_logLevel <= wip_constants.trace_logging) THEN
131 wip_logger.exitPoint(p_procName => 'wip_reservations_grp.get_available_supply_demand',
132 p_procReturnStatus => x_return_status,
133 p_msg => l_errMsg,
134 x_returnStatus => l_returnStatus);
135 END IF;
136 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
137 fnd_message.set_token('MESSAGE', l_errMsg);
138 fnd_msg_pub.add;
139
140 END get_available_supply_demand ;
141
142 PROCEDURE validate_supply_demand (
143 x_return_status OUT NOCOPY VARCHAR2
144 , x_msg_count OUT NOCOPY NUMBER
145 , x_msg_data OUT NOCOPY VARCHAR2
146 , x_valid_status OUT NOCOPY VARCHAR2
147 , p_organization_id IN NUMBER
148 , p_item_id IN NUMBER
149 , p_supply_demand_code IN NUMBER
150 , p_supply_demand_type_id IN NUMBER
151 , p_supply_demand_header_id IN NUMBER
152 , p_supply_demand_line_id IN NUMBER
153 , p_supply_demand_line_detail IN NUMBER default FND_API.G_MISS_NUM
154 , p_demand_ship_date IN DATE
155 , p_expected_receipt_date IN DATE
156 , p_api_version_number IN NUMBER default 1.0
157 ,p_init_msg_lst IN VARCHAR2
158 ) IS
159 l_params wip_logger.param_tbl_t;
160 L_logLevel NUMBER := fnd_log.g_current_runtime_level;
161 l_returnStatus VARCHAR2(1);
162 l_errMsg VARCHAR2(240);
163 l_msgCount NUMBER;
164 l_msgData VARCHAR2(2000);
165 --l_available_qty NUMBER ;
166 l_status_type NUMBER ;
167 l_crossDock_qty NUMBER;
168 BEGIN
169 -- write parameter value to log file
170 IF (l_logLevel <= wip_constants.trace_logging) THEN
171 l_params(1).paramName := ' p_organization_id';
172 l_params(1).paramValue := p_organization_id;
173 l_params(2).paramName := 'p_item_id';
174 l_params(2).paramValue := p_item_id;
175 l_params(3).paramName := 'p_supply_demand_code';
176 l_params(3).paramValue := p_supply_demand_code;
177 l_params(4).paramName := 'p_supply_demand_type_id';
178 l_params(4).paramValue := p_supply_demand_type_id;
179 l_params(5).paramName := 'p_supply_demand_header_id';
180 l_params(5).paramValue := p_supply_demand_header_id;
181
182 wip_logger.entryPoint
183 (p_procName => 'wip_reservations_grp.validate_supply_demand',
184 p_params => l_params,
185 x_returnStatus => l_returnStatus);
186 END IF;
187
188 x_return_status := fnd_api.g_ret_sts_success;
189
190 /* If API is called with parameter p_supply_demand_code being demand */
191
192 IF ( p_supply_demand_code = 1 ) then
193 /* Fixed bug 5371701. Got confirmation from Vishy that we do not have to
194 check quantity as part of this API because quantity will be checked in
195 get_available_supply_demand() procedure anyway. This API should check
196 only that the job has a valid status to be reserved against sales order.
197 The logic below does not work for return transaction because
198 quantity_completed will be updated after a call to this API. This mean
199 availableQty will always be zero if user already complete the whole
200 quantity.
201 */
202 SELECT
203 -- (WDJ.START_QUANTITY -WDJ.QUANTITY_COMPLETED - WDJ.QUANTITY_SCRAPPED) ,
204 WDJ.STATUS_TYPE
205 INTO
206 -- l_available_qty,
207 l_status_type
208 FROM WIP_DISCRETE_JOBS WDJ
209 WHERE
210 WDJ.WIP_ENTITY_ID = p_supply_demand_header_id ;
211
212 SELECT NVL(SUM(mtrl.primary_quantity),0)
213 INTO l_crossDock_qty
214 FROM mtl_txn_request_lines mtrl,
215 wms_license_plate_numbers wlpn
216 WHERE mtrl.organization_id = p_organization_id
217 AND mtrl.inventory_item_id = p_item_id
218 AND NVL(mtrl.quantity_delivered, 0) = 0
219 AND mtrl.txn_source_id = p_supply_demand_header_id
220 AND mtrl.lpn_id = wlpn.lpn_id
221 AND wlpn.lpn_context = 2 -- WIP
222 AND mtrl.line_status <> inv_globals.g_to_status_closed;
223 /*
224 l_available_qty := l_available_qty + l_crossDock_qty ;
225
226 IF l_available_qty <=0 then
227 x_valid_status := 'N' ;
228 END IF;
229 */
230 IF ( (l_status_type NOT IN
231 (WIP_CONSTANTS.UNRELEASED,
232 WIP_CONSTANTS.RELEASED,
233 WIP_CONSTANTS.HOLD,
234 WIP_CONSTANTS.COMP_CHRG)) OR
235 ((l_status_type = WIP_CONSTANTS.COMP_CHRG) AND
236 (NVL(l_crossDock_qty,0) <= 0 ) ) )
237 THEN
238 x_valid_status := 'N' ;
239 END IF;
240 ELSE
241 /* If API is called with parameter p_supply_demand_code being Supply
242 Error Out with appropriate error message */
243 fnd_message.set_name('WIP', 'WIP_SUPPLY_SOURCE');
244 fnd_msg_pub.ADD;
245 x_return_status := FND_API.G_RET_STS_ERROR;
246 END IF ; -- p_supply_demand_code if condition
247
248 -- Fixed bug 5371701. x_valid_status was not set and there is no call to
249 -- wip_logger.exitPoint().
250 x_valid_status := 'Y';
251 IF (l_logLevel <= wip_constants.trace_logging) THEN
252 wip_logger.log(p_msg => 'x_valid_status = ' || x_valid_status,
253 x_returnStatus => l_returnStatus);
254 wip_logger.exitPoint(p_procName => 'wip_reservations_grp.validate_supply_demand',
255 p_procReturnStatus => x_return_status,
256 p_msg => 'Success',
257 x_returnStatus => l_returnStatus);
258 END IF;
259 /* Need to handle exceptions and return unexpected error to the return status */
260 EXCEPTION
261 WHEN OTHERS THEN
262 x_return_status := fnd_api.g_ret_sts_unexp_error;
263 l_errMsg := 'unexpected error: ' || SQLERRM || 'SQLCODE = ' || SQLCODE;
264 IF (l_logLevel <= wip_constants.trace_logging) THEN
265 wip_logger.exitPoint(p_procName => 'wip_reservations_grp.validate_supply_demand',
266 p_procReturnStatus => x_return_status,
267 p_msg => l_errMsg,
268 x_returnStatus => l_returnStatus);
269 END IF;
270 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
271 fnd_message.set_token('MESSAGE', l_errMsg);
272 fnd_msg_pub.add;
273 END validate_supply_demand ;
274
275 END wip_reservations_grp;