The following lines contain the word 'select', 'insert', 'update' or 'delete':
select wnd.delivery_id,'Y' RATE_AVAILABLE_FLAG, 'Y' MATCHING_FLAG
FROM WSH_NEW_DELIVERIES WND
WHERE WND.RCV_SHIPMENT_HEADER_ID = l_shipment_header_id
AND WND.RCV_SHIPMENT_HEADER_ID is not null
union
select distinct wda.DELIVERY_ID, 'Y' RATE_AVAILABLE_FLAG,'N' MATCHING_FLAG
from WSH_DELIVERY_ASSIGNMENTS_V wda,
wsh_delivery_details wdd,
rcv_shipment_lines rsl
where wda.delivery_detail_id=wdd.delivery_detail_id
and wdd.rcv_shipment_line_id is null
and rsl.shipment_header_id = l_shipment_header_id
AND rsl.po_line_location_id = wdd.PO_SHIPMENT_LINE_ID
and rsl.po_line_id = wdd.source_line_id
and rsl.po_header_id = wdd.source_header_id
and wdd.source_code = 'PO'
and wda.delivery_id is not null
and exists
(
select shipment_header_id
from wsh_inbound_txn_history
where shipment_header_id = rsl.shipment_header_id
AND status = 'PENDING_MATCHING'
AND transaction_type = 'RECEIPT'
);
select distinct tmp.delivery_id
FROM fte_estimate_rates_del_temp tmp,
WSH_DELIVERY_LEGS wdl
WHERE tmp.DELIVERY_ID = WDL.DELIVERY_ID
AND ( NVL(WDL.REPRICE_REQUIRED,'N') = 'Y'
OR NOT EXISTS
(SELECT WFC.DELIVERY_LEG_ID
FROM WSH_FREIGHT_COSTS WFC
WHERE WFC.DELIVERY_LEG_ID = WDL.DELIVERY_LEG_ID
AND WDL.DELIVERY_LEG_ID IS NOT NULL
AND WFC.LINE_TYPE_CODE = 'SUMMARY'
AND WFC.CHARGE_SOURCE_CODE= 'PRICING_ENGINE'
AND WFC.TOTAL_AMOUNT is not null)) ;
select distinct wdd.vendor_id,
wdd.ship_from_site_id vendor_site_id,
wdd.source_line_id,
wdd.po_shipment_line_id,
wdd.rcv_shipment_line_id
FROM WSH_DELIVERY_DETAILS WDD,
WSH_DELIVERY_ASSIGNMENTS_V WDA2,
fte_estimate_rates_del_temp tmp
WHERE WDA2.delivery_detail_id = wdd.delivery_detail_id
AND tmp.RATE_AVAILABLE_FLAG = 'N' and tmp.MATCHING_FLAG = 'Y'
AND wda2.delivery_id = tmp.delivery_id
UNION
select distinct wdd.vendor_id,
wdd.ship_from_site_id vendor_site_id,
wdd.source_line_id,
wdd.po_shipment_line_id,
rsl.shipment_line_id
FROM WSH_DELIVERY_DETAILS WDD,
WSH_DELIVERY_ASSIGNMENTS_V WDA,
rcv_shipment_lines rsl,
fte_estimate_rates_del_temp tmp2
WHERE WDA.delivery_detail_id = wdd.delivery_detail_id
AND rsl.po_line_location_id = wdd.PO_SHIPMENT_LINE_ID
and rsl.po_line_id = wdd.source_line_id
and wdd.source_code = 'PO'
and wda.delivery_id is not null
AND tmp2.RATE_AVAILABLE_FLAG = 'N'
AND tmp2.MATCHING_FLAG = 'N'
AND wda.delivery_id = tmp2.delivery_id;
SELECT WDD.VENDOR_ID,
WDD.SHIP_FROM_SITE_ID VENDOR_SITE_ID,
WDD.SOURCE_LINE_ID,
WDD.PO_SHIPMENT_LINE_ID,
WDD.RCV_SHIPMENT_LINE_ID,
WDD.CURRENCY_CODE PO_CURRENCY_CODE,
WFC.CURRENCY_CODE,
WDD.REQUESTED_QUANTITY_UOM,
sum(nvl(wdd.received_quantity, nvl(wdd.shipped_quantity,
NVL(wdd.picked_quantity, wdd.requested_quantity)))) TOTAL_SHIP_QTY,
SUM(TOTAL_AMOUNT) TOTAL_COST
FROM WSH_NEW_DELIVERIES WND,
WSH_DELIVERY_LEGS WDL,
WSH_FREIGHT_COSTS WFC,
WSH_DELIVERY_DETAILS WDD,
WSH_DELIVERY_ASSIGNMENTS_V WDA,
WSH_TRIPS WT,
WSH_TRIP_STOPS WTS1,
WSH_TRIP_STOPS WTS2,
fte_estimate_rates_del_temp tmp
WHERE WND.DELIVERY_ID = WDL.DELIVERY_ID
AND WDA.DELIVERY_ID = WND.DELIVERY_ID
AND WDD.DELIVERY_DETAIL_ID=WDA.DELIVERY_DETAIL_ID
AND WDL.DELIVERY_LEG_ID=WFC.DELIVERY_LEG_ID
AND WND.DELIVERY_ID = WFC.DELIVERY_ID
AND WDD.DELIVERY_DETAIL_ID=WFC.DELIVERY_DETAIL_ID
--AND WND.RCV_SHIPMENT_HEADER_ID IS NOT NULL
-- For only those deliveries has rates
AND tmp.delivery_id =WND.DELIVERY_ID
AND tmp.RATE_AVAILABLE_FLAG = 'Y'
AND WDD.CONTAINER_FLAG='N' -- only loosed items
-- For LTL, get all items including packed items, for others only loose items
-- since LTL rates are done at low level detail level as well.
AND ( WT.MODE_OF_TRANSPORT= 'LTL' or (WT.MODE_OF_TRANSPORT <> 'LTL' AND WDA.PARENT_DELIVERY_DETAIL_ID is NULL))
AND WFC.DELIVERY_DETAIL_ID IS NOT NULL -- only detail level rate
-- Line Type Code is SUMMARY for TL(TRUCK), PRICE for Non-TL
AND ((WFC.LINE_TYPE_CODE = 'SUMMARY' and WT.MODE_OF_TRANSPORT = 'TRUCK')
OR
(WFC.LINE_TYPE_CODE = 'PRICE' and WT.MODE_OF_TRANSPORT <> 'TRUCK'))
AND WFC.CHARGE_SOURCE_CODE= 'PRICING_ENGINE' -- Only FTE charge
AND WTS1.STOP_ID = WDL.PICK_UP_STOP_ID
AND WTS2.STOP_ID = WDL.DROP_OFF_STOP_ID
AND WT.TRIP_ID = WTS1.TRIP_ID
AND WT.TRIP_ID = WTS2.TRIP_ID
GROUP BY
WDD.VENDOR_ID,
WDD.SHIP_FROM_SITE_ID ,
WDD.SOURCE_LINE_ID,
WDD.PO_SHIPMENT_LINE_ID,
WDD.RCV_SHIPMENT_LINE_ID,
WDD.CURRENCY_CODE,
WFC.CURRENCY_CODE,
WDD.REQUESTED_QUANTITY_UOM;
SELECT WDD.DELIVERY_DETAIL_ID PARENT_CONTAINER_ID,
WDD.CURRENCY_CODE PO_CURRENCY_CODE,
WFC.CURRENCY_CODE,
SUM(TOTAL_AMOUNT) TOTAL_COST
FROM WSH_NEW_DELIVERIES WND,
WSH_DELIVERY_LEGS WDL,
WSH_FREIGHT_COSTS WFC,
WSH_DELIVERY_DETAILS WDD,
WSH_DELIVERY_ASSIGNMENTS_V WDA,
WSH_TRIPS WT,
WSH_TRIP_STOPS WTS1,
WSH_TRIP_STOPS WTS2,
fte_estimate_rates_del_temp tmp
WHERE WND.DELIVERY_ID = WDL.DELIVERY_ID
AND WDA.DELIVERY_ID = WND.DELIVERY_ID
AND WDD.DELIVERY_DETAIL_ID=WDA.DELIVERY_DETAIL_ID
AND WDL.DELIVERY_LEG_ID=WFC.DELIVERY_LEG_ID
AND WND.DELIVERY_ID = WFC.DELIVERY_ID
AND WDD.DELIVERY_DETAIL_ID=WFC.DELIVERY_DETAIL_ID
--AND WND.RCV_SHIPMENT_HEADER_ID IS NOT NULL
-- For only those deliveries has rates
AND tmp.delivery_id =WND.DELIVERY_ID
AND tmp.RATE_AVAILABLE_FLAG = 'Y'
AND WDD.CONTAINER_FLAG='Y' -- Only container item
AND WDA.PARENT_DELIVERY_DETAIL_ID IS NULL -- only top level items.
AND WFC.DELIVERY_DETAIL_ID IS NOT NULL -- only detail level rate
AND WFC.LINE_TYPE_CODE in ('SUMMARY','PRICE') -- Only Summary rate at the container
AND WFC.CHARGE_SOURCE_CODE= 'PRICING_ENGINE'
AND WTS1.STOP_ID = WDL.PICK_UP_STOP_ID
AND WTS2.STOP_ID = WDL.DROP_OFF_STOP_ID
AND WT.TRIP_ID = WTS1.TRIP_ID
AND WT.TRIP_ID = WTS2.TRIP_ID
-- Since LTL rates are already calculated at detail level, no need to pro-rate again at detail level.
AND WT.MODE_OF_TRANSPORT <>'LTL'
AND ((WT.MODE_OF_TRANSPORT = 'TRUCK' and WFC.LINE_TYPE_CODE = 'SUMMARY')
OR
(WT.MODE_OF_TRANSPORT <> 'TRUCK' and WFC.LINE_TYPE_CODE in ('SUMMARY','PRICE')) )
GROUP BY
WDD.DELIVERY_DETAIL_ID,
WDD.CURRENCY_CODE,
WFC.CURRENCY_CODE ;
SELECT WDD.DELIVERY_DETAIL_ID,
WDD.VENDOR_ID,
WDD.SOURCE_LINE_ID,
WDD.SHIP_FROM_SITE_ID,
WDD.PO_SHIPMENT_LINE_ID,
WDD.RCV_SHIPMENT_LINE_ID,
WDD.CURRENCY_CODE,
WDD.NET_WEIGHT,
WDD.WEIGHT_UOM_CODE
FROM WSH_DELIVERY_DETAILS WDD
WHERE
WDD.CONTAINER_FLAG='N'
AND EXISTS
(SELECT 1 FROM WSH_DELIVERY_ASSIGNMENTS_V WDA
WHERE WDD.DELIVERY_DETAIL_ID = WDA.DELIVERY_DETAIL_ID
START WITH WDA.DELIVERY_DETAIL_ID = l_parent_delivery_detail_id
CONNECT BY PRIOR WDA.DELIVERY_DETAIL_ID = WDA.PARENT_DELIVERY_DETAIL_ID)
*/
-- Since all I/B shipments are packed only one level container, no need of the above query..kept the query for future usage
-- Avoiding connect by prior usage due to performance reason and not required as of now (with current functionality)
-- Need to to pro-rate the rate from container level to the detail level based on the net qty
cursor c_get_container_contents ( l_parent_delivery_detail_id number ) IS
SELECT WDD.DELIVERY_DETAIL_ID,
WDD.VENDOR_ID,
WDD.SHIP_FROM_SITE_ID,
WDD.SOURCE_LINE_ID,
WDD.PO_SHIPMENT_LINE_ID,
WDD.RCV_SHIPMENT_LINE_ID,
WDD.CURRENCY_CODE,
WDD.INVENTORY_ITEM_ID,
WDD.NET_WEIGHT,
WDD.WEIGHT_UOM_CODE
FROM WSH_DELIVERY_DETAILS WDD,
WSH_DELIVERY_ASSIGNMENTS_V WDA
WHERE WDD.DELIVERY_DETAIL_ID = WDA.DELIVERY_DETAIL_ID
and WDA.PARENT_DELIVERY_DETAIL_ID = l_parent_delivery_detail_id;
l_receipt_lines_tab.delete;
x_receipt_lines_tab.delete;
INSERT INTO fte_estimate_rates_del_temp(DELIVERY_ID,RATE_AVAILABLE_FLAG,MATCHING_FLAG)
VALUES (l_del_table(j),l_rate_available_table(j),l_matching_table(j));
UPDATE fte_estimate_rates_del_temp
SET RATE_AVAILABLE_FLAG ='N'
WHERE DELIVERY_ID = l_del_table(j);
l_rcv_ship_lines_table.delete;
l_rcv_ship_cost_table.delete;
l_rcv_ship_lines_table.delete;
l_rcv_ship_cost_table.delete;
insert into FTE_RECEIPT_LINE_RECORDS
values
( x_receipt_lines_tab(i).VENDOR_ID,
x_receipt_lines_tab(i).VENDOR_SITE_ID,
x_receipt_lines_tab(i).RCV_SHIPMENT_LINE_ID,
x_receipt_lines_tab(i).CURRENCY_CODE,
x_receipt_lines_tab(i).TOTAL_COST,
x_receipt_lines_tab(i).RETURN_STATUS,
x_receipt_lines_tab(i).MESSAGE_TEXT );
delete from fte_estimate_rates_del_temp;
select shipment_line_id,
item_id,
quantity_received,
unit_of_measure,
0 total_cost
from rcv_shipment_lines
where shipment_header_id = l_shipment_header_id
and po_line_id = l_po_line_id
and po_line_location_id = l_po_line_loc_id;
select uom_code into l_uom_code from MTL_UNITS_OF_MEASURE
where UNIT_OF_MEASURE = l_uom;