[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
last_update_date_tbl WSH_BULK_TYPES_GRP.date_Nested_Tab_Type := WSH_BULK_TYPES_GRP.date_Nested_Tab_Type()
);
SELECT WSH_INBOUND_TXN_MATCH_KEY_S.NEXTVAL
FROM DUAL;
WSH_DEBUG_SV.logmsg(l_module_name,'UPDATE WSH_INBOUND_TXN_HISTORY');
UPDATE WSH_INBOUND_TXN_HISTORY
SET STATUS = NVL(l_headerStatus,STATUS),
OBJECT_VERSION_NUMBER = NVL(l_headerObjectVersionNumber,OBJECT_VERSION_NUMBER),
MAX_RCV_TRANSACTION_ID = NVL(l_maxRCVTransactionId,MAX_RCV_TRANSACTION_ID),
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
WHERE TRANSACTION_ID = p_headerTransactionId;
FND_MESSAGE.SET_NAME('WSH','WSH_IB_TXN_UPDATE_ERROR');
PROCEDURE insertTransactionHistory
(
p_transactionType IN VARCHAR2,
p_ReceiptAgainstASN IN VARCHAR2,
p_autonomous IN BOOLEAN,
p_index IN NUMBER,
p_line_rec IN OE_WSH_BULK_GRP.Line_rec_type,
p_ship_from_location_id IN NUMBER, -- IB-Phase-2
x_transactionId OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2
)
IS
--{
l_num_warnings NUMBER := 0;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'INSERTTRANSACTIONHISTORY';
wsh_util_core.default_handler('WSH_IB_TXN_MATCH_PKG.insertTransactionHistory');
END insertTransactionHistory;
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_IB_TXN_MATCH_PKG.INSERTTRANSACTIONHISTORY',WSH_DEBUG_SV.C_PROC_LEVEL);
WSH_IB_TXN_MATCH_PKG.insertTransactionHistory
(
p_transactionType => p_transactionType,
p_ReceiptAgainstASN => p_ReceiptAgainstASN,
p_autonomous => TRUE,
p_index => p_line_rec.shipment_line_id.FIRST,
p_line_rec => p_line_rec,
p_ship_from_location_id => p_ship_from_location_id, -- IB-Phase-2
x_transactionId => x_transactionId,
x_return_status => l_return_status
);
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_INBOUND_TXN_HISTORY_PKG.UPDATE_TXN_HISTORY',WSH_DEBUG_SV.C_PROC_LEVEL);
WSH_INBOUND_TXN_HISTORY_PKG.update_txn_history
(
p_txn_history_rec => x_parentTxnHistoryRec,
x_return_status => l_return_status
);
PROCEDURE processReceiptHeaderUpdate
(
x_parentTxnHistoryRec IN OUT NOCOPY WSH_INBOUND_TXN_HISTORY_PKG.ib_txn_history_rec_type,
x_return_status OUT NOCOPY VARCHAR2
)
IS
--{
l_num_warnings NUMBER := 0;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'PROCESSRECEIPTHEADERUPDATE';
wsh_util_core.default_handler('WSH_IB_TXN_MATCH_PKG.processReceiptHeaderUpdate');
END processReceiptHeaderUpdate;
x_lineRecTbl.last_update_date_tbl.EXTEND(p_extendBy);
x_matchedLineRecTbl.last_update_date_tab.EXTEND(p_extendBy);
x_matchedLineRecTbl.last_update_date_tab(p_destinationIndex) := x_matchedLineRecTbl.last_update_date_tab(p_sourceIndex);
SELECT 'DROPOFF' leg_type,
NVL(wts1.actual_departure_date,wts1.planned_departure_date) planned_departure_date,
NVL(wts.actual_arrival_date, wts.planned_arrival_date) planned_arrival_date,
wt.carrier_id,
NVL(wnd.ITINERARY_COMPLETE,'N') ITINERARY_COMPLETE
FROM wsh_delivery_legs wdl,
wsh_new_deliveries wnd,
wsh_trip_stops wts,
wsh_trip_stops wts1,
wsh_trips wt
WHERE wnd.delivery_id = p_deliveryId
AND wdl.delivery_id = wnd.delivery_id
AND wdl.drop_off_stop_id = wts.stop_id
AND wts.stop_location_id = wnd.ultimate_dropoff_location_id
AND wts.trip_id = wt.trip_id
AND wdl.pick_up_stop_id = wts1.stop_id
UNION ALL
SELECT 'PICKUP' leg_type,
NVL(wts.actual_departure_date,wts.planned_departure_date) planned_departure_date,
NVL(wts1.actual_arrival_Date, wts1.planned_arrival_date) planned_arrival_date,
wt.carrier_id,
NVL(wnd.ITINERARY_COMPLETE,'N') ITINERARY_COMPLETE
FROM wsh_delivery_legs wdl,
wsh_new_deliveries wnd,
wsh_trip_stops wts,
wsh_trip_stops wts1,
wsh_trips wt
WHERE wnd.delivery_id = p_deliveryId
AND wdl.delivery_id = wnd.delivery_id
AND wdl.pick_up_stop_id = wts.stop_id
AND wts.stop_location_id = wnd.initial_pickup_location_id
AND wts.trip_id = wt.trip_id
AND wdl.drop_off_stop_id = wts1.stop_id
AND p_transactionType = WSH_INBOUND_TXN_HISTORY_PKG.C_ASN
ORDER BY 1 ASC; --- order by leg_type--dropoff first then pickup
SELECT
NVL(wnd.initial_pickup_date,wnd.earliest_pickup_date) initial_pickup_date,
NVL(wnd.ultimate_dropoff_date,wnd.latest_Dropoff_date) ultimate_dropoff_date,
wnd.carrier_id,
NVL(wnd.ITINERARY_COMPLETE,'N') ITINERARY_COMPLETE,
wdl.delivery_leg_id
FROM wsh_new_deliveries wnd,
wsh_delivery_legs wdl
WHERE wnd.delivery_id = p_deliveryId
AND wnd.delivery_id = wdl.delivery_id (+);
SELECT
party_name
FROM hz_parties
WHERE party_id = p_carrierId;
SELECT wdd.delivery_detail_id,
wdd.requested_quantity,
wdd.picked_quantity,
wdd.shipped_quantity,
wdd.received_quantity,
wdd.returned_quantity,
wdd.requested_quantity2,
wdd.picked_quantity2,
wdd.shipped_quantity2,
wdd.received_quantity2,
wdd.returned_quantity2,
wdd.ship_from_location_id,
wdd.earliest_dropoff_date,
wnd.delivery_id,
wdd.rcv_shipment_line_id,
wdd.requested_quantity_uom,
wdd.requested_quantity_uom2,
wdd.released_status,
wdd.src_requested_quantity,
wdd.src_requested_quantity2,
wdd.last_update_date
FROM wsh_delivery_details wdd,
wsh_delivery_assignments_v wda,
wsh_new_deliveries wnd
WHERE wdd.source_code = 'PO'
AND NVL(wdd.line_direction,'O') NOT IN ('O','IO')
AND wdd.source_header_id = p_source_header_id
AND wdd.source_line_id = p_source_line_id
AND wdd.po_shipment_line_id = p_po_shipment_line_id
AND wdd.released_status = p_released_status
AND wdd.delivery_detail_id = wda.delivery_detail_id
AND wda.delivery_id = wnd.delivery_id (+)
AND (
(
p_source_blanket_Reference_id IS NULL
AND wdd.source_blanket_Reference_id IS NULL
)
OR
wdd.source_blanket_Reference_id = p_source_blanket_Reference_id
)
/* bug 3181963
AND (
(
p_shipment_header_id IS NULL
AND wnd.asn_shipment_header_id IS NULL
)
OR
wnd.asn_shipment_header_id = p_shipment_header_id
)
*/
AND (
(
p_rcvShipmentLineID IS NULL
AND wdd.rcv_Shipment_Line_ID IS NULL
)
OR
wdd.rcv_Shipment_Line_ID = p_rcvShipmentLineID
)
AND (
(
p_transactionSubType = WSH_INBOUND_TXN_HISTORY_PKG.C_RTV_CORRECTION_NEGATIVE
AND returned_quantity > 0
)
OR
p_transactionSubType <> WSH_INBOUND_TXN_HISTORY_PKG.C_RTV_CORRECTION_NEGATIVE
)
AND (
(
p_transactionSubType IN (
WSH_INBOUND_TXN_HISTORY_PKG.C_RECEIPT_CORRECTION_NEGATIVE,
WSH_INBOUND_TXN_HISTORY_PKG.C_RTV,
WSH_INBOUND_TXN_HISTORY_PKG.C_RTV_CORRECTION_POSITIVE
)
AND (received_quantity - NVL(returned_quantity,0)) > 0
)
OR
p_transactionSubType NOT IN (
WSH_INBOUND_TXN_HISTORY_PKG.C_RECEIPT_CORRECTION_NEGATIVE,
WSH_INBOUND_TXN_HISTORY_PKG.C_RTV,
WSH_INBOUND_TXN_HISTORY_PKG.C_RTV_CORRECTION_POSITIVE
)
)
-- { IB-Phase-2
AND ( p_ship_from_location_id IS NULL
OR
( wdd.ship_from_location_id = p_ship_from_location_id
OR
wdd.ship_from_location_id = WSH_UTIL_CORE.C_NULL_SF_LOCN_ID
)
)
-- } IB-Phase-2
ORDER BY DECODE
(
wdd.ship_from_location_id,
p_ship_from_location_id, -999,
WSH_UTIL_CORE.C_NULL_SF_LOCN_ID, 1E38,
wdd.ship_from_location_id
),
DECODE
(
p_orderByFlag,
-1,
wnd.delivery_id,
-- -2,
-- wnd.delivery_id,
p_orderByFlag
),
DECODE
(
p_orderByFlag,
-1,
NVL(picked_quantity,requested_quantity),
-2,
-1 * shipped_quantity,
-3,
-1 * NVL(returned_quantity,0),
-5,
NVL(returned_quantity,-1E38), --added nvl clause as NULLS LAST was commented
p_orderByFlag
) DESC, -- NULLS LAST, -- commented nulls last due to pl/sql bug in 8.1.7.4
DECODE
(
p_orderByFlag,
-3,
(received_quantity - NVL(returned_quantity,0)),
-4,
(received_quantity - NVL(returned_quantity,0)),
-5,
(received_quantity - NVL(returned_quantity,0)),
p_orderByFlag
) DESC; -- NULLS LAST; -- commented nulls last due to pl/sql bug in 8.1.7.4
SELECT requested_quantity_uom, requested_quantity_uom2,
src_requested_quantity,src_requested_quantity2
FROM wsh_delivery_details wdd
WHERE wdd.source_code = 'PO'
AND NVL(wdd.line_direction,'O') NOT IN ('O','IO')
AND wdd.source_header_id = p_source_header_id
AND wdd.source_line_id = p_source_line_id
AND wdd.po_shipment_line_id = p_po_shipment_line_id
AND (
(
p_source_blanket_Reference_id IS NULL
AND wdd.source_blanket_Reference_id IS NULL
)
OR
wdd.source_blanket_Reference_id = p_source_blanket_Reference_id
)
--AND rownum = 1;
SELECT DISTINCT initial_pickup_location_id
FROM wsh_new_deliveries
WHERE rcv_shipment_header_id = p_shipment_header_id;
SELECT 1
FROM wsh_inbound_txn_history
WHERE shipment_header_id = p_shipment_header_id
AND shipment_line_id = p_shipment_line_id
AND status = WSH_INBOUND_TXN_HISTORY_PKG.C_PENDING;
SELECT COUNT(DISTINCT ship_from_location_id)
FROM wsh_delivery_details wdd
WHERE wdd.source_code = 'PO'
AND NVL(wdd.line_direction,'O') NOT IN ('O','IO')
AND wdd.source_header_id = p_source_header_id
AND wdd.source_line_id = p_source_line_id
AND wdd.po_shipment_line_id = p_po_shipment_line_id
AND (
(
p_source_blanket_Reference_id IS NULL
AND wdd.source_blanket_Reference_id IS NULL
)
OR
wdd.source_blanket_Reference_id = p_source_blanket_Reference_id
)
AND rcv_shipment_line_id = p_shipment_line_id
AND (
(
p_transactionSubType = WSH_INBOUND_TXN_HISTORY_PKG.C_RTV_CORRECTION_NEGATIVE
AND returned_quantity > 0
)
OR
p_transactionSubType <> WSH_INBOUND_TXN_HISTORY_PKG.C_RTV_CORRECTION_NEGATIVE
)
AND (
(
p_transactionSubType IN (
WSH_INBOUND_TXN_HISTORY_PKG.C_RECEIPT_CORRECTION_NEGATIVE,
WSH_INBOUND_TXN_HISTORY_PKG.C_RTV,
WSH_INBOUND_TXN_HISTORY_PKG.C_RTV_CORRECTION_POSITIVE
)
AND (received_quantity - NVL(returned_quantity,0)) > 0
)
OR
p_transactionSubType NOT IN (
WSH_INBOUND_TXN_HISTORY_PKG.C_RECEIPT_CORRECTION_NEGATIVE,
WSH_INBOUND_TXN_HISTORY_PKG.C_RTV,
WSH_INBOUND_TXN_HISTORY_PKG.C_RTV_CORRECTION_POSITIVE
)
);
SELECT quantity_received
FROM rcv_shipment_lines
WHERE shipment_line_id = p_shipment_line_id;
SELECT 1
FROM wsh_delivery_details wdd
WHERE wdd.source_code = 'PO'
AND NVL(wdd.line_direction,'O') NOT IN ('O','IO')
AND wdd.source_header_id = p_source_header_id
AND wdd.source_line_id = p_source_line_id
AND wdd.po_shipment_line_id = p_po_shipment_line_id
AND wdd.released_status = 'C'
AND (
(
p_source_blanket_Reference_id IS NULL
AND wdd.source_blanket_Reference_id IS NULL
)
OR
wdd.source_blanket_Reference_id = p_source_blanket_Reference_id
);
SELECT wsh_location_id
FROM wsh_locations
WHERE source_location_id = p_hz_location_id
AND location_source_code = 'HZ' ;
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_IB_TXN_MATCH_PKG.INSERTTRANSACTIONHISTORY',WSH_DEBUG_SV.C_PROC_LEVEL);
WSH_IB_TXN_MATCH_PKG.insertTransactionHistory
(
p_transactionType => WSH_INBOUND_TXN_HISTORY_PKG.C_RECEIPT,
p_ReceiptAgainstASN => l_ReceiptAgainstASN,
p_autonomous => TRUE,
p_index => p_line_rec.shipment_line_id.FIRST,
p_line_rec => p_line_rec,
p_ship_from_location_id => l_trx_wsh_location_id, -- IB-Phase-2
x_transactionId => l_transactionId,
x_return_status => l_return_status
);
WSH_DEBUG_SV.logmsg(l_module_name,'UPDATE WSH_INBOUND_TXN_HISTORY-R-HDR-UPD');
UPDATE WSH_INBOUND_TXN_HISTORY
SET OBJECT_VERSION_NUMBER = NVL(OBJECT_VERSION_NUMBER,0) + 1,
SHIPMENT_NUMBER = l_shipmentNumber,
RECEIPT_NUMBER = l_receiptNumber,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
WHERE SHIPMENT_HEADER_ID = l_RCVShipmentHeaderId
AND TRANSACTION_TYPE NOT IN ('ROUTING_RESPONSE','ROUTING_REQUEST');
l_lineRecTbl.last_update_date_tbl;
l_matchedLineRecTbl.last_update_date_tab(l_end_index) := l_lineRecTbl.last_update_date_tbl(i);
WSH_DEBUG_SV.log(l_module_name,'last_update_date', l_matchedLineRecTbl.last_update_date_tab(l_end_index));
WSH_DEBUG_SV.logmsg(l_module_name,'UPDATE WSH_INBOUND_TXN_HISTORY');
UPDATE WSH_INBOUND_TXN_HISTORY
SET STATUS = l_headerStatus,
OBJECT_VERSION_NUMBER = NVL(OBJECT_VERSION_NUMBER,0) + 1,
MAX_RCV_TRANSACTION_ID = l_maxRCVTransactionId,
SHIPMENT_NUMBER = l_shipmentNumber,
RECEIPT_NUMBER = l_receiptNumber,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
WHERE TRANSACTION_ID = l_headerTransactionId;
FND_MESSAGE.SET_NAME('WSH','WSH_IB_TXN_UPDATE_ERROR');
WSH_DEBUG_SV.logmsg(l_module_name,'UPDATE WSH_INBOUND_TXN_HISTORY-ASN');
UPDATE WSH_INBOUND_TXN_HISTORY
SET OBJECT_VERSION_NUMBER = NVL(OBJECT_VERSION_NUMBER,0) + 1,
SHIPMENT_NUMBER = l_shipmentNumber,
RECEIPT_NUMBER = l_receiptNumber,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
WHERE TRANSACTION_ID = l_parentTxnHistoryRec.transaction_id;
FND_MESSAGE.SET_NAME('WSH','WSH_IB_TXN_UPDATE_ERROR');
SELECT rsl.shipment_header_id,
WSH_INBOUND_TXN_HISTORY_PKG.C_ASN txn_type,
sum(nvl(DECODE(rsh.asn_type,'ASN',quantity_shipped,'ASBN',quantity_shipped,0),0)) shp_rcv_qty,
1 max_rcv_txn_id
FROM rcv_shipment_lines rsl, rcv_shipment_headers rsh
WHERE po_header_id = p_po_header_id
AND po_release_id = nvl(p_po_release_id,po_release_id) -- bug 5639624
AND rsl.shipment_header_id = rsh.shipment_header_id
GROUP BY rsl.shipment_header_id
UNION ALL
SELECT shipment_header_id,
WSH_INBOUND_TXN_HISTORY_PKG.C_RECEIPT txn_type,
sum(nvl(quantity,0)) shp_rcv_qty,
max(transaction_id) max_rcv_txn_id
FROM rcv_transactions
WHERE po_header_id = p_po_header_id
AND po_release_id = nvl(p_po_release_id,po_release_id) -- bug 5639624
AND transaction_type IN ( 'RECEIVE','MATCH')
GROUP BY shipment_header_id
ORDER BY 1;
SELECT 1
FROM rcv_transactions
WHERE po_header_id = p_po_header_id
AND po_release_id = nvl(p_po_release_id,po_release_id) -- bug 5639624
AND shipment_header_id = p_rcv_header_id
AND transaction_type = 'MATCH'
AND rownum = 1;
select rsh.SHIPMENT_NUM,
rsh.RECEIPT_NUM,
rsh.CARRIER_ID,
rsh.EXPECTED_RECEIPT_DATE,
rsh.SHIPPED_DATE,
rsh.VENDOR_ID,
rsh.ORGANIZATION_ID,
rsh.asn_type,
wloc.wsh_location_id -- IB-Phase-2
FROM rcv_fte_headers_v rsh,
wsh_locations wloc
WHERE rsh.shipment_header_id = p_rcv_header_id
AND rsh.ship_from_location_id = wloc.source_location_id(+); -- IB-Phase-2
select 1
FROM rcv_shipment_lines rsl
WHERE rsl.shipment_header_id = p_rcv_header_id
AND rsl.shipment_line_status_code = 'CANCELLED'
AND rownum = 1;
select rsh.ship_from_location_id
FROM rcv_shipment_headers rsh
WHERE rsh.shipment_header_id = p_rcv_header_id;
WSH_DEBUG_SV.logmsg(l_module_name,'UPDATE WSH_INBOUND_TXN_HISTORY-PENDING');
UPDATE WSH_INBOUND_TXN_HISTORY
SET OBJECT_VERSION_NUMBER = NVL(OBJECT_VERSION_NUMBER,0) + 1,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
WHERE TRANSACTION_ID = l_txnHistoryRec.transaction_id;
FND_MESSAGE.SET_NAME('WSH','WSH_IB_TXN_UPDATE_ERROR');