[Home] [Help]
16:
17: -- This is the global cache table used to store the default routing ID for supplies
18: -- based on the item, org, and vendor. We do not want to include supply lines with a
19: -- routing ID of 3 = Direct. The available routing ID's are as follows:
20: -- 1. Standard 2. Inspect 3. Direct
21: TYPE routing_id_tb IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
22: g_item_routing_id_tb routing_id_tb;
23: g_org_routing_id_tb routing_id_tb;
24: g_vendor_routing_id_tb routing_id_tb;
304:
305: EXCEPTION
306: WHEN OTHERS THEN
307: -- If an exception occurs, just return a value of 1 to indicate
308: -- Standard routing as the default.
309: RETURN 1;
310: END get_default_routing_id;
311: -- {{ }}
312: -- {{******************** End get_default_routing_id ********************}}
549: -- and decide where to goto next.
550: x_error_code := 'UOM';
551: RAISE FND_API.G_EXC_ERROR;
552: END IF;
553: -- Round the converted quantity to the standard precision
554: l_atd_wdd_qty := ROUND(l_conversion_rate * l_atd_qty, l_conversion_precision);
555: IF (l_debug = 1) THEN
556: print_debug(p_log_prefix || 'ATD qty in WDD UOM: => ' || l_atd_wdd_qty || ' ' ||
557: l_demand_uom_code);
570: -- and decide where to goto next.
571: x_error_code := 'UOM';
572: RAISE FND_API.G_EXC_ERROR;
573: END IF;
574: -- Round the converted quantity to the standard precision
575: l_atd_wdd_qty2 := ROUND(l_conversion_rate * l_atd_qty, l_conversion_precision);
576: ELSE
577: -- Secondary WDD UOM code is NULL
578: l_atd_wdd_qty2 := NULL;
830: -- and decide where to goto next.
831: x_error_code := 'UOM';
832: RAISE FND_API.G_EXC_ERROR;
833: END IF;
834: -- Round the converted quantity to the standard precision
835: l_atd_rsv_qty := ROUND(l_conversion_rate * l_atd_qty, l_conversion_precision);
836: IF (l_debug = 1) THEN
837: print_debug(p_log_prefix || 'ATD qty in RSV UOM: => ' || l_atd_rsv_qty || ' ' ||
838: l_rsv_uom_code);
851: -- and decide where to goto next.
852: x_error_code := 'UOM';
853: RAISE FND_API.G_EXC_ERROR;
854: END IF;
855: -- Round the converted quantity to the standard precision
856: l_atd_rsv_qty2 := ROUND(l_conversion_rate * l_atd_qty, l_conversion_precision);
857: ELSE
858: -- Secondary RSV UOM code is NULL
859: l_atd_rsv_qty2 := NULL;
1086: -- and decide where to goto next.
1087: x_error_code := 'UOM';
1088: RAISE FND_API.G_EXC_ERROR;
1089: END IF;
1090: -- Round the converted quantity to the standard precision
1091: l_atd_mol_qty2 := ROUND(l_conversion_rate * l_atd_qty, l_conversion_precision);
1092: ELSE
1093: -- Secondary RSV UOM code is NULL
1094: l_atd_mol_qty2 := NULL;
1745: MIN(pod.task_id) AS task_id,
1746: NULL AS lpn_id
1747: FROM po_headers_all poh, po_lines_all pol, po_line_locations_all poll,
1748: po_distributions_all pod, po_line_types plt, mtl_units_of_measure muom
1749: WHERE poh.type_lookup_code IN ('STANDARD','PLANNED','BLANKET','CONTRACT')
1750: AND NVL(poh.cancel_flag, 'N') IN ('N', 'I')
1751: AND NVL(poh.closed_code, 'OPEN') NOT IN ('FINALLY CLOSED','CLOSED FOR RECEIVING','CLOSED')
1752: AND pol.po_header_id = poh.po_header_id
1753: AND poh.po_header_id = poll.po_header_id
1761: AND poll.unit_meas_lookup_code = muom.unit_of_measure
1762: AND NVL(poll.approved_flag, 'N') = 'Y'
1763: AND NVL(poll.cancel_flag, 'N') = 'N'
1764: AND NVL(poll.closed_code, 'OPEN') NOT IN ('FINALLY CLOSED','CLOSED FOR RECEIVING','CLOSED')
1765: AND poll.shipment_type IN ('STANDARD', 'BLANKET', 'SCHEDULED')
1766: AND poll.ship_to_organization_id = l_organization_id
1767: AND poll.quantity > NVL(poll.quantity_received, 0)
1768: AND NVL(poll.receiving_routing_id,
1769: WMS_Xdock_Pegging_Pub.get_default_routing_id(l_organization_id,
2364: l_mol_lpn_id NUMBER;
2365:
2366: -- Conversion rate when converting quantity values to different UOMs
2367: l_conversion_rate NUMBER;
2368: -- The standard Inventory precision is to 5 decimal places. Define this in a local constant
2369: -- in case this needs to be changed later on. Variable is used to round off converted values
2370: -- to this precision level.
2371: l_conversion_precision CONSTANT NUMBER := 5;
2372:
2651: -- Set the savepoint
2652: SAVEPOINT Planned_Cross_Dock_sp;
2653: l_progress := '10';
2654:
2655: -- Standard Call to check for call compatibility
2656: IF NOT fnd_api.Compatible_API_Call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
2657: IF (l_debug = 1) THEN
2658: print_debug('FND_API version not compatible!');
2659: END IF;
3781: ROLLBACK TO Existing_Reservation_sp;
3782: -- Process the next existing reservation record.
3783: GOTO next_reservation;
3784: END IF;
3785: -- Round the converted quantity to the standard precision
3786: l_wdd_txn_qty := ROUND(l_conversion_rate * l_demand_qty, l_conversion_precision);
3787: IF (l_debug = 1) THEN
3788: print_debug('2.4 - WDD qty: =====> ' || l_demand_qty || ' ' || l_demand_uom_code);
3789: print_debug('2.4 - WDD txn qty: => ' || l_wdd_txn_qty || ' ' || l_supply_uom_code);
3804: ROLLBACK TO Existing_Reservation_sp;
3805: -- Process the next existing reservation record.
3806: GOTO next_reservation;
3807: END IF;
3808: -- Round the converted quantity to the standard precision
3809: l_rsv_txn_qty := ROUND(l_conversion_rate * l_rsv_qty, l_conversion_precision);
3810: IF (l_debug = 1) THEN
3811: print_debug('2.4 - RSV qty: =====> ' || l_rsv_qty || ' ' || l_rsv_uom_code);
3812: print_debug('2.4 - RSV txn qty: => ' || l_rsv_txn_qty || ' ' || l_supply_uom_code);
3870: ROLLBACK TO Existing_Reservation_sp;
3871: -- Process the next existing reservation record.
3872: GOTO next_reservation;
3873: END IF;
3874: -- Round the converted quantity to the standard precision
3875: l_atd_prim_qty := ROUND(l_conversion_rate * l_atd_qty, l_conversion_precision);
3876: IF (l_debug = 1) THEN
3877: print_debug('2.4 - ATD qty in primary UOM: => ' || l_atd_prim_qty || ' ' ||
3878: l_primary_uom_code);
4265: print_debug('2.6 - Error while obtaining Primary UOM conversion rate for WDD');
4266: END IF;
4267: GOTO next_record;
4268: END IF;
4269: -- Round the converted quantity to the standard precision
4270: l_demand_atr_qty := ROUND(l_conversion_rate * l_demand_atr_prim_qty, l_conversion_precision);
4271: IF (l_debug = 1) THEN
4272: print_debug('2.6 - Available qty to reserve for demand: ' || l_demand_atr_qty ||
4273: ' ' || l_demand_uom_code);
4859: -- table can therefore be a sparsely populated table after running custom logic.
4860: l_shopping_basket_tb.DELETE(i);
4861: GOTO next_custom_supply;
4862: END IF;
4863: -- Round the converted quantity to the standard precision
4864: l_supply_atr_qty := ROUND(l_conversion_rate * l_supply_atr_prim_qty, l_conversion_precision);
4865: IF (l_debug = 1) THEN
4866: print_debug('3.6 - Supply line ATR qty: ' || l_supply_atr_qty || ' ' ||
4867: l_supply_uom_code);
5354: -- Rollback any db changes that might have occurred (currently none).
5355: ROLLBACK TO Crossdock_Supply_sp;
5356: GOTO next_supply;
5357: END IF;
5358: -- Round the converted quantity to the standard precision
5359: l_supply_atr_qty := ROUND(l_conversion_rate * l_supply_atr_prim_qty, l_conversion_precision);
5360:
5361: -- Set the reservable quantity field for the current supply line
5362: -- in the shopping basket table.
5403: -- Rollback any db changes that might have occurred (currently none).
5404: ROLLBACK TO Crossdock_Supply_sp;
5405: GOTO next_supply;
5406: END IF;
5407: -- Round the converted quantity to the standard precision
5408: l_wdd_atr_txn_qty := ROUND(l_conversion_rate * l_demand_atr_qty, l_conversion_precision);
5409: l_wdd_txn_qty := ROUND(l_conversion_rate * l_demand_qty, l_conversion_precision);
5410: IF (l_debug = 1) THEN
5411: print_debug('4.3 - WDD ATR txn qty: => ' || l_wdd_atr_txn_qty || ' ' || l_supply_uom_code);
5512: -- Bug 5608611: Use quantity from demand document where possible
5513: IF (l_atd_qty = l_wdd_atr_txn_qty AND l_demand_uom_code = l_primary_uom_code) THEN
5514: l_atd_prim_qty := l_demand_atr_qty;
5515: ELSE
5516: -- Round the converted quantity to the standard precision
5517: l_atd_prim_qty := ROUND(l_conversion_rate * l_atd_qty, l_conversion_precision);
5518: END IF;
5519: IF (l_debug = 1) THEN
5520: print_debug('4.3 - ATD qty in primary UOM: => ' || l_atd_prim_qty || ' ' ||
6124: IF (l_debug = 1) AND SQL%FOUND THEN
6125: print_debug('5.5 - Cleared the temp table wms_xdock_pegging_gtmp');
6126: END IF;
6127:
6128: -- Standard call to commit
6129: IF fnd_api.To_Boolean(p_commit) THEN
6130: COMMIT;
6131: END IF;
6132: l_progress := '620';
6632: l_demand_src_code NUMBER;
6633:
6634: -- Conversion rate when converting quantity values to different UOMs
6635: l_conversion_rate NUMBER;
6636: -- The standard Inventory precision is to 5 decimal places. Define this in a local constant
6637: -- in case this needs to be changed later on. Variable is used to round off converted values
6638: -- to this precision level.
6639: l_conversion_precision CONSTANT NUMBER := 5;
6640:
7532: print_debug('2.1 - Error while obtaining UOM conversion rate for RSV qty');
7533: END IF;
7534: GOTO next_reservation;
7535: END IF;
7536: -- Round the converted quantity to the standard precision
7537: l_wip_xdock_qty := ROUND(l_conversion_rate * l_wip_xdock_prim_qty, l_conversion_precision);
7538: l_rsv_qty := l_rsv_qty - l_wip_xdock_qty;
7539: IF (l_debug = 1) THEN
7540: print_debug('2.1 - Adjusted RSV Qty: ======> ' || l_rsv_qty || ' ' ||
7556: print_debug('2.1 - Error while obtaining UOM conversion rate for RSV qty2');
7557: END IF;
7558: GOTO next_reservation;
7559: END IF;
7560: -- Round the converted quantity to the standard precision
7561: l_wip_xdock_qty2 := ROUND(l_conversion_rate * l_wip_xdock_prim_qty, l_conversion_precision);
7562: l_rsv_qty2 := l_rsv_qty2 - l_wip_xdock_qty2;
7563: IF (l_debug = 1) THEN
7564: print_debug('2.1 - Adjusted RSV Qty2: =====> ' || l_rsv_qty2 || ' ' ||
7802: ROLLBACK TO Reserved_WDD_sp;
7803: -- Process the next existing reserved WDD.
7804: GOTO next_reserved_wdd;
7805: END IF;
7806: -- Round the converted quantity to the standard precision
7807: l_wdd_txn_qty := ROUND(l_conversion_rate * l_demand_qty, l_conversion_precision);
7808: IF (l_debug = 1) THEN
7809: print_debug('2.4 - WDD qty: =====> ' || l_demand_qty || ' ' || l_demand_uom_code);
7810: print_debug('2.4 - WDD txn qty: => ' || l_wdd_txn_qty || ' ' || l_supply_uom_code);
7825: ROLLBACK TO Reserved_WDD_sp;
7826: -- Process the next existing reserved WDD.
7827: GOTO next_reserved_wdd;
7828: END IF;
7829: -- Round the converted quantity to the standard precision
7830: l_rsv_txn_qty := ROUND(l_conversion_rate * l_rsv_qty, l_conversion_precision);
7831: IF (l_debug = 1) THEN
7832: print_debug('2.4 - RSV qty: =====> ' || l_rsv_qty || ' ' || l_rsv_uom_code);
7833: print_debug('2.4 - RSV txn qty: => ' || l_rsv_txn_qty || ' ' || l_supply_uom_code);
7878: ROLLBACK TO Reserved_WDD_sp;
7879: -- Process the next existing reserved WDD.
7880: GOTO next_reserved_wdd;
7881: END IF;
7882: -- Round the converted quantity to the standard precision
7883: l_atd_prim_qty := ROUND(l_conversion_rate * l_atd_qty, l_conversion_precision);
7884: IF (l_debug = 1) THEN
7885: print_debug('2.4 - ATD qty in primary UOM: => ' || l_atd_prim_qty || ' ' ||
7886: l_primary_uom_code);
8208: print_debug('2.5 - Error while obtaining Primary UOM conversion rate for WDD');
8209: END IF;
8210: GOTO end_crossdock;
8211: END IF;
8212: -- Round the converted quantity to the standard precision
8213: l_supply_atr_qty := ROUND(l_conversion_rate * l_supply_atr_prim_qty, l_conversion_precision);
8214: IF (l_debug = 1) THEN
8215: print_debug('2.5 - Available qty to reserve for supply: ' || l_supply_atr_qty ||
8216: ' ' || l_supply_uom_code);
8836: -- table can therefore be a sparsely populated table after running custom logic.
8837: l_shopping_basket_tb.DELETE(i);
8838: GOTO next_custom_demand;
8839: END IF;
8840: -- Round the converted quantity to the standard precision
8841: l_demand_atr_qty := ROUND(l_conversion_rate * l_demand_atr_prim_qty, l_conversion_precision);
8842: IF (l_debug = 1) THEN
8843: print_debug('3.5 - Demand line ATR qty: ' || l_demand_atr_qty || ' ' ||
8844: l_demand_uom_code);
9156: -- Rollback any db changes that might have occurred (currently none).
9157: ROLLBACK TO Crossdock_Demand_sp;
9158: GOTO next_demand;
9159: END IF;
9160: -- Round the converted quantity to the standard precision
9161: l_demand_atr_qty := ROUND(l_conversion_rate * l_demand_atr_prim_qty, l_conversion_precision);
9162:
9163: -- Set the reservable quantity field for the current demand line
9164: -- in the shopping basket table.
9202: -- Rollback any db changes that might have occurred (currently none).
9203: ROLLBACK TO Crossdock_Demand_sp;
9204: GOTO next_demand;
9205: END IF;
9206: -- Round the converted quantity to the standard precision
9207: l_wdd_atr_txn_qty := ROUND(l_conversion_rate * l_demand_atr_qty, l_conversion_precision);
9208: l_wdd_txn_qty := ROUND(l_conversion_rate * l_demand_qty, l_conversion_precision);
9209: IF (l_debug = 1) THEN
9210: print_debug('4.3 - WDD/WIP ATR txn qty: => ' || l_wdd_atr_txn_qty || ' ' || l_supply_uom_code);
9259: -- Bug 5608611: Use quantity from demand document where possible
9260: IF (l_atd_qty = l_wdd_atr_txn_qty AND l_demand_uom_code = l_primary_uom_code) THEN
9261: l_atd_prim_qty := l_demand_atr_qty;
9262: ELSE
9263: -- Round the converted quantity to the standard precision
9264: l_atd_prim_qty := ROUND(l_conversion_rate * l_atd_qty, l_conversion_precision);
9265: END IF;
9266: IF (l_debug = 1) THEN
9267: print_debug('4.3 - ATD qty in primary UOM: => ' || l_atd_prim_qty || ' ' ||