1 PACKAGE BODY rcv_availability AS
2 /* $Header: RCVAVALB.pls 120.9 2006/10/16 11:47:08 srnatara noship $*/
3 TYPE wms_install_table_type IS TABLE OF BOOLEAN
4 INDEX BY BINARY_INTEGER;
5
6 g_wms_install_table wms_install_table_type;
7 g_pkg_name CONSTANT VARCHAR2(30) := 'rcv_availability';
8 g_rti_normalized VARCHAR2(1);
9
10 /*
11 normalize_interface_tables is a private helper function for
12 get_available_supply_demand in 12.0, the normalization package was
13 introduced to put all of the information in RTI into a consistent
14 state to allow one-stop shopping for writting SQL queries. This
15 process is normally started by the preprocessor. I want to take
16 advantage of this convience, but since reservations can be run at
17 any time I need to manually execute the package rather than let the
18 preprocessor execute it.
19 The process of normalizing the data can be expensive, so in order
20 to reduce the performance hit I will process the rows in the
21 interface table only once. This is useful considering that this
22 call may be made many times during planned cross docking execution.
23 */
24 PROCEDURE normalize_interface_tables IS
25 PRAGMA AUTONOMOUS_TRANSACTION;
26
27 CURSOR get_rhi_rows IS
28 SELECT *
29 FROM rcv_headers_interface
30 WHERE processing_status_code = 'PENDING';
31
32 CURSOR get_rti_rows IS
33 SELECT *
34 FROM rcv_transactions_interface
35 WHERE processing_status_code = 'PENDING';
36 BEGIN
37 IF (g_rti_normalized IS NOT NULL) THEN --because this is expensive, we only want to run it once per session
38 RETURN;
39 END IF;
40
41 g_rti_normalized := 'Y';
42
43 FOR rhi_row IN get_rhi_rows LOOP
44 BEGIN
45 rcv_default_pkg.default_header(rhi_row);
46 rcv_table_functions.update_rhi_row(rhi_row);
47 EXCEPTION
48 WHEN OTHERS THEN
49 NULL;
50 END;
51 END LOOP;
52
53 FOR rti_row IN get_rti_rows LOOP
54 IF (rti_row.validation_flag = 'Y') THEN
55 BEGIN
56 rcv_default_pkg.default_transaction(rti_row);
57 rcv_table_functions.update_rti_row(rti_row);
58 EXCEPTION
59 WHEN OTHERS THEN
60 NULL;
61 END;
62 END IF;
63 END LOOP;
64
65 COMMIT; --release locks
66 END normalize_interface_tables;
67
68 /*
69 get_available_supply_demand returns the available quantity in terms
70 of the ***PRIMARY_UNIT_OF_MEASURE*** <-VERY IMPORTANT. All
71 quantities inside this procedure are kept in terms of
72 primary_unit_of_measure. This is necessary because different
73 transaction rows in rt can be transacted in different units of
74 measure. There are two important quantities being tracked in this
75 procedure: x_rcv_quantity - this is the quantity that has already
76 been received or will be received into receiving, and
77 x_rcv_order_quantity - this is the quantity that was ordered on the
78 backing document. The value returned in x_available_quantity =
79 x_ordered_quantity - x_rcv_quantity
80 */
81 PROCEDURE get_available_supply_demand(
82 x_return_status OUT NOCOPY VARCHAR2,
83 x_msg_count OUT NOCOPY NUMBER,
84 x_msg_data OUT NOCOPY VARCHAR2,
85 x_available_quantity OUT NOCOPY NUMBER,
86 x_source_uom_code OUT NOCOPY VARCHAR2,
87 x_source_primary_uom_code OUT NOCOPY VARCHAR2,
88 p_supply_demand_code IN NUMBER,
89 p_organization_id IN NUMBER,
90 p_item_id IN NUMBER,
91 p_revision IN VARCHAR2,
92 p_lot_number IN VARCHAR2,
93 p_subinventory_code IN VARCHAR2,
94 p_locator_id IN NUMBER,
95 p_supply_demand_type_id IN NUMBER,
96 p_supply_demand_header_id IN NUMBER,
97 p_supply_demand_line_id IN NUMBER,
98 p_supply_demand_line_detail IN NUMBER,
99 p_lpn_id IN NUMBER,
100 p_project_id IN NUMBER,
101 p_task_id IN NUMBER,
102 p_api_version_number IN NUMBER,
103 p_init_msg_lst IN VARCHAR2
104 ) IS
105 l_api_version_number CONSTANT NUMBER := 1.0;
106 l_api_name CONSTANT VARCHAR2(30) := 'get_available_supply_demand';
107 l_lpn_id NUMBER;--Bug 5329067
108 x_rcv_quantity NUMBER;
109 x_rcv_order_quantity NUMBER;
110
111 CURSOR get_uom_code(
112 p_unit_of_measure mtl_units_of_measure.unit_of_measure%TYPE
113 ) IS
114 SELECT uom_code
115 FROM mtl_units_of_measure
116 WHERE unit_of_measure = p_unit_of_measure;
117
118 /*
119 The following four cursors: get_req_order, get_oe_order,
120 get_ship_order, and get_po_order are responsible for getting the
121 original order quantity from the backing doc regardless of the
122 status of the backing doc
123 */
124 CURSOR get_req_order IS
125 SELECT rl.quantity,
126 rl.item_id,
127 rl.unit_meas_lookup_code unit_of_measure,
128 si.primary_unit_of_measure
129 FROM po_requisition_lines_all rl,
130 mtl_system_items si
131 WHERE rl.requisition_line_id = p_supply_demand_line_id
132 AND si.inventory_item_id(+) = rl.item_id;
133
134 CURSOR get_oe_order IS
135 SELECT oel.ordered_quantity quantity,
136 oel.inventory_item_id item_id,
137 uom.unit_of_measure,
138 si.primary_unit_of_measure
139 FROM oe_order_lines_all oel,
140 mtl_units_of_measure uom,
141 mtl_system_items si
142 WHERE line_id = p_supply_demand_line_id
143 AND order_quantity_uom = uom_code
144 AND si.inventory_item_id(+) = oel.inventory_item_id;
145
146 CURSOR get_ship_order(
147 p_shipment_line_id IN rcv_shipment_lines.shipment_line_id%TYPE
148 ) IS
149 SELECT quantity_shipped quantity,
150 item_id,
151 unit_of_measure,
152 primary_unit_of_measure
153 FROM rcv_shipment_lines
154 WHERE shipment_line_id = p_shipment_line_id;
155
156 /* Bug. 4693257.
157 * We need to get the quantity from po_distributions
158 * since we need to match project and task ids.
159 */
160 CURSOR get_po_order(
161 p_project_id IN po_distributions_all.project_id%type,
162 p_task_id IN po_distributions_all.task_id%type) IS
163 SELECT sum(pod.quantity_ordered) quantity,
164 pol.item_id,
165 pol.unit_meas_lookup_code unit_of_measure,
166 si.primary_unit_of_measure
167 FROM po_line_locations_all pll,
168 po_lines_all pol,
169 po_distributions_all pod,
170 mtl_system_items si
171 WHERE pll.line_location_id = p_supply_demand_line_id
172 AND pll.po_line_id = pol.po_line_id
173 AND si.inventory_item_id(+) = pol.item_id
174 AND nvl(si.organization_id,pll.ship_to_organization_id) =
175 pll.ship_to_organization_id
176 and pod.line_location_id = pll.line_location_id
177 and (p_project_id is null or
178 pod.project_id= p_project_id)
179 and(p_task_id is null or
180 pod.task_id = p_task_id)
181 GROUP BY pol.item_id,
182 pol.unit_meas_lookup_code,
183 si.primary_unit_of_measure;
184
185 /*
186 the following cursors: get_rcv_req_row, get_rcv_oe_row,
187 get_rcv_ship_row, get_rcv_po_row are all the same query, but
188 with different driving where clauses. The cursor used is
189 determined by p_supply_demand_type_id. The cursors return all
190 the rows in RCV_SHIPMENT_LINES and all the receipts/+correction
191 to receipts/shipments in RCV_TRANSACTIONS_INTERFACE that apply
192 to the backing doc
193
194 The parameters p_organization_id, p_item_id, p_revision,
195 p_lot_number, p_subinventory_code, p_locator_id, p_project_id,
196 and p_task_id are NOT driving parameters. They are coded into
197 the queries in a way that the CBO will not attempt to use them,
198 instead it will always rely on the backing doc as the driving
199 column. These parameters only restrict the scope of a query,
200 they do not enlargen it.
201 */
202 CURSOR get_rcv_req_row IS
203 SELECT DECODE(rti.transaction_type,
204 'SHIP', rti.quantity,
205 'RECEIVE', rti.quantity,
206 'CORRECT', DECODE(rt.transaction_type,
207 'SHIP', rti.quantity,
208 'RECEIVE', rti.quantity,
209 0
210 ),
211 0
212 ) quantity_shipped,
213 DECODE(rti.transaction_type,
214 'DELIVER', rti.quantity,
215 'CORRECT', DECODE(rt.transaction_type,
216 'DELIVER', rti.quantity,
217 0
218 ),
219 DECODE(rti.auto_transact_code,
220 'DELIVER', rti.quantity,
221 0
222 )
223 ) quantity_delivered,
224 rti.item_id,
225 rti.unit_of_measure,
226 rti.primary_unit_of_measure,
227 rti.to_organization_id
228 FROM rcv_transactions_interface rti,
229 rcv_transactions rt
230 WHERE rti.parent_transaction_id = rt.transaction_id(+)
231 AND rti.quantity > 0
232 AND ( rti.transaction_type IN('RECEIVE', 'SHIP', 'DELIVER')
233 OR ( rti.transaction_type = 'CORRECT'
234 AND rt.transaction_type IN('RECEIVE', 'DELIVER')))
235 AND rti.processing_status_code IN('PENDING', 'RUNNING')
236 AND ( p_organization_id IS NULL
237 OR p_organization_id = rti.to_organization_id)
238 AND ( p_item_id IS NULL
239 OR p_item_id = rti.item_id)
240 AND ( p_revision IS NULL
241 OR p_revision = rti.item_revision)
242 AND ( p_subinventory_code IS NULL
243 OR p_subinventory_code = rti.subinventory)
244 AND ( p_locator_id IS NULL
245 OR p_locator_id = rti.locator_id)
246 AND ( l_lpn_id = fnd_api.g_miss_num --Bug 5329067
247 OR l_lpn_id = rti.lpn_id --Bug 5329067
248 OR ( l_lpn_id IS NULL--Bug 5329067
249 AND rti.lpn_id IS NULL))
250 AND ( p_project_id IS NULL
251 OR p_project_id = rti.project_id)
252 AND ( p_task_id IS NULL
253 OR p_task_id = rti.task_id)
254 AND rti.requisition_line_id = p_supply_demand_line_id
255 UNION ALL
256 SELECT rsl.quantity_received,
257 rsl.quantity_received - NVL(rs.quantity, 0) quantity_delivered,
258 rsl.item_id,
259 rsl.unit_of_measure,
260 rsl.primary_unit_of_measure,
261 rsl.to_organization_id
262 FROM rcv_shipment_lines rsl,
263 rcv_supply rs
264 WHERE rsl.shipment_line_id = rs.shipment_line_id(+)
265 AND ( p_organization_id IS NULL
266 OR p_organization_id = rsl.to_organization_id)
267 AND ( p_item_id IS NULL
268 OR p_item_id = rsl.item_id)
269 AND ( p_revision IS NULL
270 OR p_revision = rsl.item_revision)
271 AND ( p_subinventory_code IS NULL
272 OR p_subinventory_code = rsl.to_subinventory)
273 AND ( p_locator_id IS NULL
274 OR p_locator_id = rsl.locator_id)
275 AND ( l_lpn_id = fnd_api.g_miss_num --Bug 5329067
276 OR NVL(l_lpn_id, fnd_api.g_miss_num) IN(SELECT NVL(rt.lpn_id, fnd_api.g_miss_num) --Bug 5329067
277 FROM rcv_transactions rt
278 WHERE rt.shipment_line_id = rsl.shipment_line_id))
279 AND ( p_project_id IS NULL
280 OR p_project_id IN(SELECT rt.project_id
281 FROM rcv_transactions rt
282 WHERE rt.shipment_line_id = rsl.shipment_line_id))
283 AND ( p_task_id IS NULL
284 OR p_task_id IN(SELECT rt.task_id
285 FROM rcv_transactions rt
286 WHERE rt.shipment_line_id = rsl.shipment_line_id))
287 AND rsl.requisition_line_id = p_supply_demand_line_id;
288
289 CURSOR get_rcv_oe_row IS
290 SELECT DECODE(rti.transaction_type,
291 --'SHIP', rti.quantity,--dont count
292 'RECEIVE', rti.quantity,
293 'CORRECT', DECODE(rt.transaction_type,
294 --'SHIP', rti.quantity,--dont count
295 'RECEIVE', rti.quantity,
296 0
297 ),
298 0
299 ) quantity_shipped,
300 DECODE(rti.transaction_type,
301 'DELIVER', rti.quantity,
302 'CORRECT', DECODE(rt.transaction_type,
303 'DELIVER', rti.quantity,
304 0
305 ),
306 DECODE(rti.auto_transact_code,
307 'DELIVER', rti.quantity,
308 0
309 )
310 ) quantity_delivered,
311 rti.item_id,
312 rti.unit_of_measure,
313 rti.primary_unit_of_measure,
314 rti.to_organization_id
315 FROM rcv_transactions_interface rti,
316 rcv_transactions rt
317 WHERE rti.parent_transaction_id = rt.transaction_id(+)
318 AND rti.quantity > 0
319 AND ( rti.transaction_type IN('RECEIVE', 'SHIP', 'DELIVER')
320 OR ( rti.transaction_type = 'CORRECT'
321 AND rt.transaction_type IN('RECEIVE', 'DELIVER')))
322 AND rti.processing_status_code IN('PENDING', 'RUNNING')
323 AND ( p_organization_id IS NULL
324 OR p_organization_id = rti.to_organization_id)
325 AND ( p_item_id IS NULL
326 OR p_item_id = rti.item_id)
327 AND ( p_revision IS NULL
328 OR p_revision = rti.item_revision)
329 AND ( p_subinventory_code IS NULL
330 OR p_subinventory_code = rti.subinventory)
331 AND ( p_locator_id IS NULL
332 OR p_locator_id = rti.locator_id)
333 AND ( l_lpn_id = fnd_api.g_miss_num --Bug 5329067
334 OR l_lpn_id = rti.lpn_id --Bug 5329067
335 OR ( l_lpn_id IS NULL --Bug 5329067
336 AND rti.lpn_id IS NULL))
337 AND ( p_project_id IS NULL
338 OR p_project_id = rti.project_id)
339 AND ( p_task_id IS NULL
340 OR p_task_id = rti.task_id)
341 AND rti.oe_order_line_id = p_supply_demand_line_id
342 UNION ALL
343 SELECT rsl.quantity_received,
344 rsl.quantity_received - NVL(rs.quantity, 0) quantity_delivered,
345 rsl.item_id,
346 rsl.unit_of_measure,
347 rsl.primary_unit_of_measure,
348 rsl.to_organization_id
349 FROM rcv_shipment_lines rsl,
350 rcv_supply rs
351 WHERE rsl.shipment_line_id = rs.shipment_line_id(+)
352 AND ( p_organization_id IS NULL
353 OR p_organization_id = rsl.to_organization_id)
354 AND ( p_item_id IS NULL
355 OR p_item_id = rsl.item_id)
356 AND ( p_revision IS NULL
357 OR p_revision = rsl.item_revision)
358 AND ( p_subinventory_code IS NULL
359 OR p_subinventory_code = rsl.to_subinventory)
360 AND ( p_locator_id IS NULL
361 OR p_locator_id = rsl.locator_id)
362 AND ( l_lpn_id = fnd_api.g_miss_num --Bug 5329067
363 OR NVL(l_lpn_id, fnd_api.g_miss_num) IN(SELECT NVL(rt.lpn_id, fnd_api.g_miss_num)--Bug 5329067
364 FROM rcv_transactions rt
365 WHERE rt.shipment_line_id = rsl.shipment_line_id))
366 AND ( p_project_id IS NULL
367 OR p_project_id IN(SELECT rt.project_id
368 FROM rcv_transactions rt
369 WHERE rt.shipment_line_id = rsl.shipment_line_id))
370 AND ( p_task_id IS NULL
371 OR p_task_id IN(SELECT rt.task_id
372 FROM rcv_transactions rt
373 WHERE rt.shipment_line_id = rsl.shipment_line_id))
374 AND rsl.oe_order_line_id = p_supply_demand_line_id;
375
376 /*
377 get_rcv_ship_row is unique because I want to use it for both
378 asn's and intransit so I have made the line_id a parameter
379 */
380 /* Bug 4642399.
381 * We do not need the rti.quantity when transaction_type
382 * is SHIP since it actually gets the current shipped_qty
383 * for both ASN and Intrasit transactions.
384 */
385 CURSOR get_rcv_ship_row(
386 p_shipment_line_id IN rcv_shipment_lines.shipment_line_id%TYPE
387 ) IS
388 SELECT DECODE(rti.transaction_type,
389 --'SHIP', rti.quantity,
390 'RECEIVE', rti.quantity,
391 'CORRECT', DECODE(rt.transaction_type,
392 'SHIP', rti.quantity,
393 'RECEIVE', rti.quantity,
394 0
395 ),
396 0
397 ) quantity_shipped,
398 DECODE(rti.transaction_type,
399 'DELIVER', rti.quantity,
400 'CORRECT', DECODE(rt.transaction_type,
401 'DELIVER', rti.quantity,
402 0
403 ),
404 DECODE(rti.auto_transact_code,
405 'DELIVER', rti.quantity,
406 0
407 )
408 ) quantity_delivered,
409 rti.item_id,
410 rti.unit_of_measure,
411 rti.primary_unit_of_measure,
412 rti.to_organization_id
413 FROM rcv_transactions_interface rti,
414 rcv_transactions rt
415 WHERE rti.parent_transaction_id = rt.transaction_id(+)
416 AND rti.quantity > 0
417 AND ( rti.transaction_type IN('RECEIVE', 'SHIP', 'DELIVER')
418 OR ( rti.transaction_type = 'CORRECT'
419 AND rt.transaction_type IN('RECEIVE', 'DELIVER')))
420 AND rti.processing_status_code IN('PENDING', 'RUNNING')
421 AND ( p_organization_id IS NULL
422 OR p_organization_id = rti.to_organization_id)
423 AND ( p_item_id IS NULL
424 OR p_item_id = rti.item_id)
425 AND ( p_revision IS NULL
426 OR p_revision = rti.item_revision)
427 AND ( p_subinventory_code IS NULL
428 OR p_subinventory_code = rti.subinventory)
429 AND ( p_locator_id IS NULL
430 OR p_locator_id = rti.locator_id)
431 AND ( l_lpn_id = fnd_api.g_miss_num --Bug 5329067
432 OR l_lpn_id = rti.lpn_id--Bug 5329067
433 OR ( l_lpn_id IS NULL --Bug 5329067
434 AND rti.lpn_id IS NULL))
435 AND ( p_project_id IS NULL
436 OR p_project_id = rti.project_id)
437 AND ( p_task_id IS NULL
438 OR p_task_id = rti.task_id)
439 AND rti.shipment_line_id = p_shipment_line_id
440 UNION ALL
441 SELECT rsl.quantity_received,
442 rsl.quantity_received - NVL(rs.quantity, 0) quantity_delivered,
443 rsl.item_id,
444 rsl.unit_of_measure,
445 rsl.primary_unit_of_measure,
446 rsl.to_organization_id
447 FROM rcv_shipment_lines rsl,
448 rcv_supply rs
449 WHERE rsl.shipment_line_id = rs.shipment_line_id(+)
450 AND ( p_organization_id IS NULL
451 OR p_organization_id = rsl.to_organization_id)
452 AND ( p_item_id IS NULL
453 OR p_item_id = rsl.item_id)
454 AND ( p_revision IS NULL
455 OR p_revision = rsl.item_revision)
456 AND ( p_subinventory_code IS NULL
457 OR p_subinventory_code = rsl.to_subinventory)
458 AND ( p_locator_id IS NULL
459 OR p_locator_id = rsl.locator_id)
460 AND ( l_lpn_id = fnd_api.g_miss_num --Bug 5329067
461 OR NVL(l_lpn_id, fnd_api.g_miss_num) IN(SELECT NVL(rt.lpn_id, fnd_api.g_miss_num) --Bug 5329067
462 FROM rcv_transactions rt
463 WHERE rt.shipment_line_id = rsl.shipment_line_id))
464 AND ( p_project_id IS NULL
465 OR p_project_id IN(SELECT rt.project_id
466 FROM rcv_transactions rt
467 WHERE rt.shipment_line_id = rsl.shipment_line_id))
468 AND ( p_task_id IS NULL
469 OR p_task_id IN(SELECT rt.task_id
470 FROM rcv_transactions rt
471 WHERE rt.shipment_line_id = rsl.shipment_line_id))
472 AND rsl.shipment_line_id = p_shipment_line_id;
473
474 CURSOR get_rcv_po_row IS
475 SELECT DECODE(rti.transaction_type,
476 'SHIP', rti.quantity,
477 'RECEIVE', rti.quantity,
478 'CORRECT', DECODE(rt.transaction_type,
479 'SHIP', rti.quantity,
480 'RECEIVE', rti.quantity,
481 0
482 ),
483 'RETURN TO CUSTOMER', DECODE(rt.transaction_type,
484 'RECEIVE', -1 * rti.quantity,
485 0
486 ), --Return txn is similar as -ve Correction. So make the qty
487 --as -ve in order to calculate the available qty correctly
488 'RETURN TO VENDOR', DECODE(rt.transaction_type,
489 'RECEIVE', -1 * rti.quantity,
490 0
491 ),
492
493 0
494 )quantity_received, --Bug 5329067
495 DECODE(rti.transaction_type,
496 'DELIVER', rti.quantity,
497 'CORRECT', DECODE(rt.transaction_type,
498 'DELIVER', rti.quantity,
499 0
500 ),
501 'RETURN TO CUSTOMER', DECODE(rt.transaction_type,
502 'DELIVER', -1 * rti.quantity,
503 0
504 ), --Returns txn is similar as -ve Correction. So make the qty
505 --as -ve in order to calculate the available qty correctly
506 'RETURN TO VENDOR', DECODE(rt.transaction_type,
507 'DELIVER', -1 * rti.quantity,
508 0
509 ),
510 'RETURN TO RECEIVING', DECODE(rt.transaction_type,
511 'DELIVER', -1 * rti.quantity,
512 0
513 ),
514 DECODE(rti.auto_transact_code,
515 'DELIVER', rti.quantity,
516 0
517 )
518 ) quantity_delivered, --Bug 5329067
519 rti.item_id,
520 rti.unit_of_measure,
521 rti.primary_unit_of_measure,
522 rti.to_organization_id
523 FROM rcv_transactions_interface rti,
524 rcv_transactions rt
525 WHERE rti.parent_transaction_id = rt.transaction_id(+)
526 AND ( rti.quantity > 0
527 OR (rti.quantity < 0 AND rti.transaction_type = 'CORRECT')
528 )--Bug 5329067
529 AND ( rti.transaction_type IN('RECEIVE', 'SHIP', 'DELIVER')
530 OR ( rti.transaction_type = 'CORRECT'
531 AND rt.transaction_type IN('RECEIVE', 'DELIVER')
532 )
533 OR ( rti.transaction_type IN ('RETURN TO CUSTOMER','RETURN TO VENDOR', 'RETURN TO RECEIVING')
534 AND rt.transaction_type IN('RECEIVE', 'DELIVER')
535 )
536 )--Bug 5329067
537 AND rti.processing_status_code IN('PENDING', 'RUNNING')
538 AND ( p_organization_id IS NULL
539 OR p_organization_id = rti.to_organization_id)
540 AND ( p_item_id IS NULL
541 OR p_item_id = rti.item_id)
542 AND ( p_revision IS NULL
543 OR p_revision = rti.item_revision)
544 AND ( p_subinventory_code IS NULL
545 OR p_subinventory_code = rti.subinventory)
546 AND ( p_locator_id IS NULL
547 OR p_locator_id = rti.locator_id)
548 AND ( l_lpn_id = fnd_api.g_miss_num --Bug 5329067
549 OR l_lpn_id = rti.lpn_id --Bug 5329067
550 OR ( l_lpn_id IS NULL--Bug 5329067
551 AND rti.lpn_id IS NULL))
552 AND ( p_project_id IS NULL
553 OR p_project_id = rti.project_id)
554 AND ( p_task_id IS NULL
555 OR p_task_id = rti.task_id)
556 AND rti.po_line_location_id = p_supply_demand_line_id
557 UNION ALL
558 SELECT rsl.quantity_received quantity_received, --Bug 5329067
559 rsl.quantity_received - NVL(rs.quantity, 0) quantity_delivered,
560 rsl.item_id,
561 rsl.unit_of_measure,
562 rsl.primary_unit_of_measure,
563 rsl.to_organization_id
564 FROM rcv_shipment_lines rsl,
565 rcv_supply rs
566 WHERE rsl.shipment_line_id = rs.shipment_line_id(+)
567 AND ( p_organization_id IS NULL
568 OR p_organization_id = rsl.to_organization_id)
569 AND ( p_item_id IS NULL
570 OR p_item_id = rsl.item_id)
571 AND ( p_revision IS NULL
572 OR p_revision = rsl.item_revision)
573 AND ( p_subinventory_code IS NULL
574 OR p_subinventory_code = rsl.to_subinventory)
575 AND ( p_locator_id IS NULL
576 OR p_locator_id = rsl.locator_id)
577 AND ( l_lpn_id = fnd_api.g_miss_num --Bug 5329067
578 OR NVL(l_lpn_id, fnd_api.g_miss_num) IN(SELECT NVL(rt.lpn_id, fnd_api.g_miss_num) --Bug 5329067
579 FROM rcv_transactions rt
580 WHERE rt.shipment_line_id = rsl.shipment_line_id))
581 AND ( p_project_id IS NULL
582 OR p_project_id IN(SELECT rt.project_id
583 FROM rcv_transactions rt
584 WHERE rt.shipment_line_id = rsl.shipment_line_id))
585 AND ( p_task_id IS NULL
586 OR p_task_id IN(SELECT rt.task_id
587 FROM rcv_transactions rt
588 WHERE rt.shipment_line_id = rsl.shipment_line_id))
589 AND rsl.po_line_location_id = p_supply_demand_line_id;
590
591 x_order_row get_po_order%ROWTYPE;
592
593 /*
594 get_wms_install is a local helper function that takes an org_id
595 and memoizes it in order to increase performance because we are
596 likely to perform the same check against wms many times.
597 */
598 FUNCTION get_wms_install(
599 p_organization_id NUMBER
600 )
601 RETURN BOOLEAN IS
602 BEGIN
603 IF g_wms_install_table.EXISTS(p_organization_id) THEN
604 RETURN g_wms_install_table(p_organization_id);
605 END IF;
606
607 g_wms_install_table(p_organization_id) := wms_install.check_install(x_return_status,
608 x_msg_count,
609 x_msg_data,
610 p_organization_id
611 );
612 RETURN g_wms_install_table(p_organization_id);
613 END get_wms_install;
614
615 /*
616 update_rcv_quantity is a local helper function that takes care
617 of the house keeping to keep the code easy to read. This
618 procedure increments x_local_quantity and
619 x_local_quantity appropriately - ensuring to keep
620 everything in the primary_unit_of_measure.
621 */
622 PROCEDURE update_rcv_quantity(
623 p_row get_rcv_po_row%ROWTYPE
624 ) IS
625 x_local_quantity NUMBER;
626 BEGIN
627 x_local_quantity := 0;
628
629 IF (get_wms_install(p_row.to_organization_id)) THEN
630 po_uom_s.uom_convert(NVL(p_row.quantity_received, 0),
631 p_row.unit_of_measure,
632 p_row.item_id,
633 NVL(p_row.primary_unit_of_measure, p_row.unit_of_measure),
634 x_local_quantity
635 );
636 ELSE --non wms org: count the quantity delivered
637 po_uom_s.uom_convert(NVL(p_row.quantity_delivered, 0),
638 p_row.unit_of_measure,
639 p_row.item_id,
640 NVL(p_row.primary_unit_of_measure, p_row.unit_of_measure),
641 x_local_quantity
642 );
643 END IF;
644
645 /*
646 pessimistic logging - assume all beneficial transactions fail
647 */
648 /** Bug 5329067:
649 * x_local_quantity can hold -ve values in case of 'RETURN' txn and
650 * -ve CORRECTION txn. So commented the check for the value > 0 for
651 * the variable x_local_quantity.
652 */
653 -- IF (x_local_quantity > 0) THEN
654 x_rcv_quantity := x_rcv_quantity + x_local_quantity;
655 -- END IF;
656 EXCEPTION
657 WHEN OTHERS THEN
658 NULL;
659 END update_rcv_quantity;
660 BEGIN
661 x_return_status := fnd_api.g_ret_sts_error;
662
663 -- Standard call to check for call compatibility
664 IF NOT fnd_api.compatible_api_call(l_api_version_number,
665 p_api_version_number,
666 l_api_name,
667 g_pkg_name
668 ) THEN
669 RAISE fnd_api.g_exc_unexpected_error;
670 END IF;
671
672 -- Initialize message list.
673 IF fnd_api.to_boolean(p_init_msg_lst) THEN
674 fnd_msg_pub.initialize;
675 END IF;
676
677 IF ( p_supply_demand_code IS NULL
678 OR p_supply_demand_type_id IS NULL
679 OR p_supply_demand_header_id IS NULL
680 OR p_supply_demand_line_id IS NULL) THEN
681 fnd_message.set_name('RCV', 'RCV_INVALID_NULL');
682 fnd_msg_pub.ADD;
683 RAISE fnd_api.g_exc_error;
684 END IF;
685
686 /** Bug 5329067:
687 * If the p_lpn_id is passed as 'null' to the procedure get_available_supply_demand(), then
688 * we have to treat it same as the fnd_api.g_miss_num. As of now reservation in receiving is not
689 * maintained at the LPN level, so we can treat the 'null' value as fnd_api.g_miss_num.
690 */
691 IF p_lpn_id is null then --Bug 5329067
692 l_lpn_id := fnd_api.g_miss_num;
693 ELSE
694 l_lpn_id := p_lpn_id;
695 END IF;
696
697 normalize_interface_tables; --this makes sure we can query rti via the header and line id's
698 x_rcv_quantity := 0;
699 x_rcv_order_quantity := 0;
700
701 /*
702 The logic below first defaults x_rcv_order_quantity from the
703 backing docs. It then loops through all the rows in
704 rcv_transactions_interface (RTI) and rcv_shipment_lines (RSL).
705 If the row is in RSL then it increases the running value of
706 x_rcv_quantity by shipped_quantity for non-wms orgs and increases the value
707 of x_rcv_quantity by received_quantity for wms orgs. We know that RSL's
708 shipped_quantity and receipt_quantity are an accurate summation of all rcv_transaction
709 records for that backing doc. If the row is in RTI and the RTI
710 row is a 'RECEIVE' or a positive correction to a 'RECEIVE' then
711 it increases the running value of x_rcv_quantity for wms orgs, else if
712 the RTI row is a 'SHIP' then it increases the running value of
713 x_rcv_quantity.
714
715 This is to avoid double counting. If I have a PO for quantity
716 100, and I have already processed an ASN for quantity 50, a
717 processed receipt for quantity 10, a pending delivery for
718 quantity 5 for that receipt, and a new pending receipt for
719 quantity 3 then in a WMS installation I want to return an
720 available quantity of 87 = (100 - 10 - 3) or 50 = (100 - 50) in
721 a non-WMS installation. The deliver has already been accounted
722 for in the 'RECEIVE' so the reason for only looking at these RTI
723 types is to avoid double counting.
724 */
725 IF (p_supply_demand_type_id IN(7, 17)) THEN --internal and external req
726 OPEN get_req_order;
727 FETCH get_req_order INTO x_order_row;
728 CLOSE get_req_order;
729
730 FOR c_rcv_row IN get_rcv_req_row LOOP
731 update_rcv_quantity(c_rcv_row);
732 END LOOP;
733 ELSIF(p_supply_demand_type_id IN(2, 8, 12)) THEN --sales order, rma, and intenal order
734 OPEN get_oe_order;
735 FETCH get_oe_order INTO x_order_row;
736 CLOSE get_oe_order;
737
738 FOR c_rcv_row IN get_rcv_oe_row LOOP
739 update_rcv_quantity(c_rcv_row);
740 END LOOP;
741 ELSIF(p_supply_demand_type_id IN(26)) THEN --intransit
742 OPEN get_ship_order(p_supply_demand_line_id);
743 FETCH get_ship_order INTO x_order_row;
744 CLOSE get_ship_order;
745
746 FOR c_rcv_row IN get_rcv_ship_row(p_supply_demand_line_id) LOOP
747 update_rcv_quantity(c_rcv_row);
748 END LOOP;
749 ELSIF(p_supply_demand_type_id IN(25)) THEN --asn
750 OPEN get_ship_order(p_supply_demand_line_detail);
751 FETCH get_ship_order INTO x_order_row;
752 CLOSE get_ship_order;
753
754 FOR c_rcv_row IN get_rcv_ship_row(p_supply_demand_line_detail) LOOP
755 update_rcv_quantity(c_rcv_row);
756 END LOOP;
757 ELSE --po
758 OPEN get_po_order(p_project_id,p_task_id);
759 FETCH get_po_order INTO x_order_row;
760 CLOSE get_po_order;
761
762 FOR c_rcv_row IN get_rcv_po_row LOOP
763 update_rcv_quantity(c_rcv_row);
764 END LOOP;
765 END IF;
766
767 /* Bug 4901404: When the unit_of_measure and primary_unit_of_measure
768 are null, no need to call the uom_convert procedure and
769 we can default the value of x_available_quantity to Zero */
770
771 IF x_order_row.unit_of_measure IS NOT NULL THEN --Bug: 4901404
772 po_uom_s.uom_convert(NVL(x_order_row.quantity, 0),
773 x_order_row.unit_of_measure,
774 x_order_row.item_id,
775 NVL(x_order_row.primary_unit_of_measure, x_order_row.unit_of_measure),
776 x_rcv_order_quantity
777 );
778 x_rcv_order_quantity := NVL(x_rcv_order_quantity, 0);
779 x_available_quantity := x_rcv_order_quantity - x_rcv_quantity;
780
781 --Bug 5313645
782 --Since we are converting the available quantity to primary uom
783 --always. We should be passing the primary uom code in x_source_uom_code --Commenting out the following code.
784
785 /*OPEN get_uom_code(x_order_row.unit_of_measure);
786 FETCH get_uom_code INTO x_source_uom_code;
787 CLOSE get_uom_code;
788 */
789 OPEN get_uom_code(x_order_row.primary_unit_of_measure);
790 FETCH get_uom_code INTO x_source_primary_uom_code;
791 CLOSE get_uom_code;
792
793 x_source_uom_code := x_source_primary_uom_code;
794
795 IF (x_available_quantity IS NULL) THEN
796 fnd_message.set_name('RCV', 'RCV_UNEXPECTED_NULL');
797 fnd_msg_pub.ADD;
798 RAISE fnd_api.g_exc_error;
799 END IF;
800
801 IF (x_available_quantity < 0) THEN
802 x_available_quantity := 0;
803 END IF;
804 ELSE -- x_order_row.unit_of_measure is NULL
805 x_available_quantity := 0;
806 END IF; /* Bug:4901404 fix ends*/
807
808 x_return_status := fnd_api.g_ret_sts_success;
809 EXCEPTION
810 WHEN fnd_api.g_exc_error THEN
811 x_return_status := fnd_api.g_ret_sts_error;
812 -- Get message count and data
813 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
814 WHEN fnd_api.g_exc_unexpected_error THEN
815 x_return_status := fnd_api.g_ret_sts_unexp_error;
816 -- Get message count and data
817 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
818 WHEN OTHERS THEN
819 x_return_status := fnd_api.g_ret_sts_unexp_error;
820
821 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
822 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
823 END IF;
824
825 -- Get message count and data
826 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
827 END get_available_supply_demand;
828
829 /*
830 validate_supply_demand does a lookup against the given backing docs
831 and returns a valid flag if the document is open, approved, and
832 waiting for receipts
833 */
834 PROCEDURE validate_supply_demand(
835 x_return_status OUT NOCOPY VARCHAR2,
836 x_msg_count OUT NOCOPY NUMBER,
837 x_msg_data OUT NOCOPY VARCHAR2,
838 x_valid_status OUT NOCOPY VARCHAR2,
839 p_organization_id IN NUMBER,
840 p_item_id IN NUMBER,
841 p_supply_demand_code IN NUMBER,
842 p_supply_demand_type_id IN NUMBER,
843 p_supply_demand_header_id IN NUMBER,
844 p_supply_demand_line_id IN NUMBER,
845 p_supply_demand_line_detail IN NUMBER,
846 p_demand_ship_date IN DATE,
847 p_expected_receipt_date IN DATE,
848 p_api_version_number IN NUMBER,
849 p_init_msg_lst IN VARCHAR2
850 ) IS
851 l_api_version_number CONSTANT NUMBER := 1.0;
852 l_api_name CONSTANT VARCHAR2(30) := 'get_available_supply_demand';
853 l_lookup_code VARCHAR2(20);
854 BEGIN
855 x_return_status := fnd_api.g_ret_sts_error;
856 x_valid_status := 'N';
857
858 -- Standard call to check for call compatibility
859 IF NOT fnd_api.compatible_api_call(l_api_version_number,
860 p_api_version_number,
861 l_api_name,
862 g_pkg_name
863 ) THEN
864 RAISE fnd_api.g_exc_unexpected_error;
865 END IF;
866
867 -- Initialize message list.
868 IF fnd_api.to_boolean(p_init_msg_lst) THEN
869 fnd_msg_pub.initialize;
870 END IF;
871
872 IF ( p_supply_demand_code IS NULL
873 OR p_supply_demand_type_id IS NULL
874 OR p_supply_demand_header_id IS NULL
875 OR p_supply_demand_line_id IS NULL) THEN
876 fnd_message.set_name('RCV', 'RCV_INVALID_NULL');
877 fnd_msg_pub.ADD;
878 RAISE fnd_api.g_exc_error;
879 END IF;
880
881 /*
882 The following section will attempt to use the backing docs to
883 query if a valid open doc exists for the various source types.
884 If the query fails then the exception handler will set the valid
885 flag to N
886 */
887 BEGIN
888 IF (p_supply_demand_type_id IN(7, 17)) THEN --interanl and external req
889 IF (p_supply_demand_type_id = 17) THEN --external req
890 l_lookup_code := 'PURCHASE';
891 ELSE --internal req
892 l_lookup_code := 'INTERNAL';
893 END IF;
894
895 /* Bug 4619856.
896 * Change all the table to base tables.
897 */
898 SELECT 'Y'
899 INTO x_valid_status
900 FROM po_requisition_lines_all rl,
901 po_requisition_headers_all rh
902 WHERE rh.requisition_header_id = rl.requisition_header_id
903 AND type_lookup_code = l_lookup_code
904 AND authorization_status = 'APPROVED'
905 AND NVL(rl.cancel_flag, 'N') = 'N'
906 AND NVL(rl.closed_code, 'OPEN') = 'OPEN'
907 AND NVL(rh.closed_code, 'OPEN') = 'OPEN'
908 AND document_type_code IS NULL
909 AND destination_type_code <> 'EXPENSE'
910 AND ( p_organization_id IS NULL
911 OR p_organization_id = rl.destination_organization_id)
912 AND ( p_item_id IS NULL
913 OR p_item_id = rl.item_id)
914 AND rl.requisition_line_id = p_supply_demand_line_id
915 AND rh.requisition_header_id = p_supply_demand_header_id;
916 ELSIF(p_supply_demand_type_id IN(2, 8, 12)) THEN --sales order, rma, and internal order
917 IF (p_supply_demand_type_id = 12) THEN --rma
918 l_lookup_code := 'RETURN';
919 ELSE --sales order and internal order
920 l_lookup_code := 'ORDER';
921 END IF;
922
923 /* Bug 4619856.
924 * Change all the table to base tables.
925 */
926 SELECT 'Y'
927 INTO x_valid_status
928 FROM oe_order_lines_all
929 WHERE open_flag = 'Y'
930 AND line_category_code = l_lookup_code
931 AND ( p_organization_id IS NULL
932 OR p_organization_id = deliver_to_org_id)
933 AND ( p_item_id IS NULL
934 OR p_item_id = inventory_item_id)
935 AND line_id = p_supply_demand_line_id
936 AND header_id = p_supply_demand_header_id;
937 ELSIF(p_supply_demand_type_id IN(26)) THEN --intransit
938 SELECT 'Y'
939 INTO x_valid_status
940 FROM rcv_shipment_lines rsl,
941 rcv_shipment_headers rsh
942 WHERE shipment_line_status_code IN('EXPECTED', 'PARTIALLY RECEIVED')
943 AND rsl.shipment_header_id = rsh.shipment_header_id
944 AND ( p_organization_id IS NULL
945 OR p_organization_id = rsl.to_organization_id)
946 AND ( p_item_id IS NULL
947 OR p_item_id = rsl.item_id)
948 AND rsl.shipment_line_id = p_supply_demand_line_id
949 AND rsl.shipment_header_id = p_supply_demand_header_id;
950 ELSE --po's and asn's
951 /* Bug 4619856.
952 * Change all the table to base tables.
953 */
954 SELECT 'Y'
955 INTO x_valid_status
956 FROM po_line_locations_all pll,
957 po_lines_all pol,
958 po_headers_all poh
959 WHERE pol.po_header_id = poh.po_header_id
960 AND pll.po_line_id = pol.po_line_id
961 AND NVL(pll.approved_flag, 'N') = 'Y'
962 AND NVL(pol.closed_code, 'OPEN') <> 'FINALLY CLOSED'
963 AND NVL(pll.cancel_flag, 'N') = 'N'
964 AND pll.shipment_type IN('STANDARD', 'BLANKET', 'SCHEDULED')
965 AND ( p_organization_id IS NULL
966 OR p_organization_id = pll.ship_to_organization_id)
967 AND ( p_item_id IS NULL
968 OR p_item_id = pol.item_id)
969 AND pll.line_location_id = p_supply_demand_line_id
970 AND pol.po_header_id = p_supply_demand_header_id;
971
972 --already passed the first check for asn's, now perform the second
973 IF (p_supply_demand_type_id IN(25)) THEN --asn
974 SELECT 'Y'
975 INTO x_valid_status
976 FROM rcv_shipment_lines rsl,
977 rcv_shipment_headers rsh
978 WHERE shipment_line_status_code IN('EXPECTED', 'PARTIALLY RECEIVED')
979 AND rsl.shipment_header_id = rsh.shipment_header_id
980 AND rsl.shipment_line_id = p_supply_demand_line_detail
981 AND rsl.po_line_location_id = p_supply_demand_line_id
982 AND rsl.po_header_id = p_supply_demand_header_id;
983 END IF;
984 END IF;
985 EXCEPTION
986 WHEN OTHERS THEN
987 x_valid_status := 'N';
988 END;
989
990 x_return_status := fnd_api.g_ret_sts_success;
991 EXCEPTION
992 WHEN fnd_api.g_exc_error THEN
993 x_return_status := fnd_api.g_ret_sts_error;
994 -- Get message count and data
995 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
996 WHEN fnd_api.g_exc_unexpected_error THEN
997 x_return_status := fnd_api.g_ret_sts_unexp_error;
998 -- Get message count and data
999 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1000 WHEN OTHERS THEN
1001 x_return_status := fnd_api.g_ret_sts_unexp_error;
1002
1003 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1004 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1005 END IF;
1006
1007 -- Get message count and data
1008 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1009 END validate_supply_demand;
1010 END rcv_availability;
[Home] [Help]