APPS.FTE_PO_INTEGRATION_GRP SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 99

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'
   );

Line: 128

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)) ;

Line: 155

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;

Line: 197

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;

Line: 258

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 ;

Line: 303

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;

Line: 472

  l_receipt_lines_tab.delete;

Line: 473

  x_receipt_lines_tab.delete;

Line: 488

     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));

Line: 501

        UPDATE fte_estimate_rates_del_temp
        SET RATE_AVAILABLE_FLAG ='N'
        WHERE DELIVERY_ID = l_del_table(j);

Line: 707

   l_rcv_ship_lines_table.delete;

Line: 708

   l_rcv_ship_cost_table.delete;

Line: 847

       l_rcv_ship_lines_table.delete;

Line: 848

       l_rcv_ship_cost_table.delete;

Line: 978

        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 );

Line: 993

delete from fte_estimate_rates_del_temp;

Line: 1113

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;

Line: 1189

            select uom_code into l_uom_code from MTL_UNITS_OF_MEASURE
            where UNIT_OF_MEASURE = l_uom;