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