[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
insert into wsh_inbound_txn_history
(TRANSACTION_ID,
RECEIPT_NUMBER,
REVISION_NUMBER,
SHIPMENT_NUMBER,
TRANSACTION_TYPE,
SHIPMENT_HEADER_ID,
PARENT_SHIPMENT_HEADER_ID,
ORGANIZATION_ID,
SUPPLIER_ID,
SHIPPED_DATE,
RECEIPT_DATE,
STATUS,
MAX_RCV_TRANSACTION_ID,
CARRIER_ID,
MATCH_REVERTED_BY,
MATCHED_BY,
SHIPMENT_LINE_ID,
OBJECT_VERSION_NUMBER,
SHIP_FROM_LOCATION_ID,-- IB-Phase-2
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN)
values(wsh_inbound_txn_history_s.nextval,
x_inboundTxnHistory_recTbl.RECEIPT_NUMBER(i),
x_inboundTxnHistory_recTbl.REVISION_NUMBER(i),
x_inboundTxnHistory_recTbl.SHIPMENT_NUMBER(i),
x_inboundTxnHistory_recTbl.TRANSACTION_TYPE(i),
x_inboundTxnHistory_recTbl.SHIPMENT_HEADER_ID(i),
x_inboundTxnHistory_recTbl.PARENT_SHIPMENT_HEADER_ID(i),
x_inboundTxnHistory_recTbl.ORGANIZATION_ID(i),
x_inboundTxnHistory_recTbl.SUPPLIER_ID(i),
x_inboundTxnHistory_recTbl.SHIPPED_DATE(i),
x_inboundTxnHistory_recTbl.RECEIPT_DATE(i),
x_inboundTxnHistory_recTbl.STATUS(i),
x_inboundTxnHistory_recTbl.MAX_RCV_TRANSACTION_ID(i),
x_inboundTxnHistory_recTbl.CARRIER_ID(i),
x_inboundTxnHistory_recTbl.MATCH_REVERTED_BY(i),
x_inboundTxnHistory_recTbl.MATCHED_BY(i),
x_inboundTxnHistory_recTbl.SHIPMENT_LINE_ID(i),
1,
x_inboundTxnHistory_recTbl.SHIP_FROM_LOCATION_ID(i),-- IB-Phase-2
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID)
RETURNING transaction_id BULK COLLECT INTO x_inboundTxnHistory_recTbl.TRANSACTION_ID;
FND_MESSAGE.SET_NAME('WSH','WSH_IB_TXN_BULK_INSERT_ERROR');
insert into wsh_inbound_txn_history
(TRANSACTION_ID,
RECEIPT_NUMBER,
REVISION_NUMBER,
SHIPMENT_NUMBER,
TRANSACTION_TYPE,
SHIPMENT_HEADER_ID,
PARENT_SHIPMENT_HEADER_ID,
ORGANIZATION_ID,
SUPPLIER_ID,
SHIPPED_DATE,
RECEIPT_DATE,
STATUS,
MAX_RCV_TRANSACTION_ID,
CARRIER_ID,
MATCH_REVERTED_BY,
MATCHED_BY,
SHIPMENT_LINE_ID,
OBJECT_VERSION_NUMBER,
SHIP_FROM_LOCATION_ID, -- IB-Phase-2
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN)
values(wsh_inbound_txn_history_s.nextval,
p_txn_history_rec.RECEIPT_NUMBER,
p_txn_history_rec.REVISION_NUMBER,
p_txn_history_rec.SHIPMENT_NUMBER,
p_txn_history_rec.TRANSACTION_TYPE,
p_txn_history_rec.SHIPMENT_HEADER_ID,
p_txn_history_rec.PARENT_SHIPMENT_HEADER_ID,
p_txn_history_rec.ORGANIZATION_ID,
p_txn_history_rec.SUPPLIER_ID,
p_txn_history_rec.SHIPPED_DATE,
p_txn_history_rec.RECEIPT_DATE,
p_txn_history_rec.STATUS,
p_txn_history_rec.MAX_RCV_TRANSACTION_ID,
p_txn_history_rec.CARRIER_ID,
p_txn_history_rec.MATCH_REVERTED_BY,
p_txn_history_rec.MATCHED_BY,
p_txn_history_rec.SHIPMENT_LINE_ID,
1,
p_txn_history_rec.SHIP_FROM_LOCATION_ID, -- IB-Phase-2
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID)
RETURNING transaction_id into x_txn_id;
PROCEDURE update_txn_history (
p_txn_history_rec IN ib_txn_history_rec_type,
x_return_status OUT NOCOPY VARCHAR2
)
IS
--{
--}
--
l_debug_on BOOLEAN;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_TXN_HISTORY';
update wsh_inbound_txn_history
set RECEIPT_NUMBER = DECODE
(
p_txn_history_rec.RECEIPT_NUMBER,
FND_API.G_MISS_CHAR,NULL,
NULL,RECEIPT_NUMBER,
p_txn_history_rec.RECEIPT_NUMBER),
REVISION_NUMBER = DECODE
(
p_txn_history_rec.REVISION_NUMBER,
FND_API.G_MISS_CHAR,NULL,
NULL,REVISION_NUMBER,
p_txn_history_rec.REVISION_NUMBER),
SHIPMENT_NUMBER = DECODE
(
p_txn_history_rec.SHIPMENT_NUMBER,
FND_API.G_MISS_CHAR,NULL,
NULL,SHIPMENT_NUMBER,
p_txn_history_rec.SHIPMENT_NUMBER),
TRANSACTION_TYPE = DECODE
(
p_txn_history_rec.TRANSACTION_TYPE,
FND_API.G_MISS_CHAR,NULL,
NULL,TRANSACTION_TYPE,
p_txn_history_rec.TRANSACTION_TYPE),
SHIPMENT_HEADER_ID = DECODE
(
p_txn_history_rec.SHIPMENT_HEADER_ID,
FND_API.G_MISS_NUM,NULL,
NULL,SHIPMENT_HEADER_ID,
p_txn_history_rec.SHIPMENT_HEADER_ID),
PARENT_SHIPMENT_HEADER_ID = DECODE
(
p_txn_history_rec.PARENT_SHIPMENT_HEADER_ID,
FND_API.G_MISS_NUM,NULL,
NULL,PARENT_SHIPMENT_HEADER_ID,
p_txn_history_rec.PARENT_SHIPMENT_HEADER_ID),
ORGANIZATION_ID = DECODE
(
p_txn_history_rec.ORGANIZATION_ID,
FND_API.G_MISS_NUM,NULL,
NULL,ORGANIZATION_ID,
p_txn_history_rec.ORGANIZATION_ID),
SUPPLIER_ID = DECODE
(
p_txn_history_rec.SUPPLIER_ID,
FND_API.G_MISS_NUM,NULL,
NULL,SUPPLIER_ID,
p_txn_history_rec.SUPPLIER_ID),
SHIPPED_DATE = DECODE
(
p_txn_history_rec.SHIPPED_DATE,
FND_API.G_MISS_DATE,NULL,
NULL,SHIPPED_DATE,
p_txn_history_rec.SHIPPED_DATE),
RECEIPT_DATE = DECODE
(
p_txn_history_rec.RECEIPT_DATE,
FND_API.G_MISS_DATE,NULL,
NULL,RECEIPT_DATE,
p_txn_history_rec.RECEIPT_DATE),
STATUS = DECODE
(
p_txn_history_rec.STATUS,
FND_API.G_MISS_CHAR,NULL,
NULL,STATUS,
p_txn_history_rec.STATUS),
MAX_RCV_TRANSACTION_ID = DECODE
(
p_txn_history_rec.MAX_RCV_TRANSACTION_ID,
FND_API.G_MISS_NUM,NULL,
NULL,MAX_RCV_TRANSACTION_ID,
p_txn_history_rec.MAX_RCV_TRANSACTION_ID),
CARRIER_ID = DECODE
(
p_txn_history_rec.CARRIER_ID,
FND_API.G_MISS_NUM,NULL,
NULL,CARRIER_ID,
p_txn_history_rec.CARRIER_ID),
MATCH_REVERTED_BY = DECODE
(
p_txn_history_rec.MATCH_REVERTED_BY,
FND_API.G_MISS_NUM,NULL,
NULL,MATCH_REVERTED_BY,
p_txn_history_rec.MATCH_REVERTED_BY),
MATCHED_BY = DECODE
(
p_txn_history_rec.MATCHED_BY,
FND_API.G_MISS_NUM,NULL,
NULL,MATCHED_BY,
p_txn_history_rec.MATCHED_BY),
SHIPMENT_LINE_ID = DECODE
(
p_txn_history_rec.SHIPMENT_LINE_ID,
FND_API.G_MISS_NUM,NULL,
NULL,SHIPMENT_LINE_ID,
p_txn_history_rec.SHIPMENT_LINE_ID),
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
-- { IB-Phase-2
SHIP_FROM_LOCATION_ID = DECODE
(
p_txn_history_rec.SHIP_FROM_LOCATION_ID,
FND_API.G_MISS_NUM,NULL,
NULL,SHIP_FROM_LOCATION_ID,
p_txn_history_rec.SHIP_FROM_LOCATION_ID),
-- } IB-Phase-2
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
where TRANSACTION_ID = p_txn_history_rec.transaction_id;
wsh_util_core.default_handler('WSH_INBOUND_TXN_HISTORY_PKG.UPDATE_TXN_HISTORY');
END update_txn_history;
PROCEDURE delete_txn_history (
p_transaction_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2
)
IS
--{
--}
--
l_debug_on BOOLEAN;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'DELETE_TXN_HISTORY';
delete from wsh_inbound_txn_history
where transaction_id = p_transaction_id;
wsh_util_core.default_handler('WSH_INBOUND_TXN_HISTORY_PKG.DELETE_TXN_HISTORY');
END delete_txn_history;
select TRANSACTION_ID,
RECEIPT_NUMBER,
REVISION_NUMBER,
SHIPMENT_NUMBER,
TRANSACTION_TYPE,
SHIPMENT_HEADER_ID,
PARENT_SHIPMENT_HEADER_ID,
ORGANIZATION_ID,
SUPPLIER_ID,
SHIPPED_DATE,
RECEIPT_DATE,
STATUS,
MAX_RCV_TRANSACTION_ID,
CARRIER_ID,
MATCH_REVERTED_BY,
MATCHED_BY,
SHIPMENT_LINE_ID,
OBJECT_VERSION_NUMBER,
SHIP_FROM_LOCATION_ID -- IB-Phase-2
from wsh_inbound_txn_history
where transaction_id = p_transaction_id
or (p_transaction_id is null
AND shipment_header_id = p_shipment_header_id
AND transaction_type = p_transaction_type);
select transaction_id
from wsh_inbound_txn_history
where transaction_type not in ('RECEIPT', 'ASN')
and shipment_header_id = p_shipment_header_id;
delete from wsh_inbound_txn_history
where transaction_id = l_txn_id_tab(i);
update_txn_history (
p_txn_history_rec => l_txn_history_rec,
x_return_status => l_return_status);
update_txn_history (
p_txn_history_rec => l_txn_history_rec,
x_return_status => l_return_status);
select 1
from wsh_inbound_txn_history
where transaction_type not in ('RECEIPT', 'ASN')
and shipment_header_id = p_shipment_header_id;
DELETE wsh_inbound_txn_history
WHERE transaction_type not in (C_ASN, C_RECEIPT)
AND shipment_header_id = p_shipment_header_id
AND max_rcv_transaction_id <= l_txn_id;
WSH_DEBUG_SV.log(l_module_name,'Number of Records deleted from transaction history',SQL%ROWCOUNT);
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit update_txn_history',WSH_DEBUG_SV.C_PROC_LEVEL);
update_txn_history
(
p_txn_history_rec => l_txn_history_rec,
x_return_status => l_return_status
);
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit update_txn_history:receipt-asn',WSH_DEBUG_SV.C_PROC_LEVEL);
update_txn_history
(
p_txn_history_rec => l_Receipttxn_history_rec,
x_return_status => l_return_status
);
SELECT TRANSACTION_ID,
RECEIPT_NUMBER,
REVISION_NUMBER,
SHIPMENT_NUMBER,
TRANSACTION_TYPE,
SHIPMENT_HEADER_ID,
PARENT_SHIPMENT_HEADER_ID,
ORGANIZATION_ID,
SUPPLIER_ID,
SHIPPED_DATE,
RECEIPT_DATE,
STATUS,
MAX_RCV_TRANSACTION_ID,
CARRIER_ID,
MATCH_REVERTED_BY,
MATCHED_BY,
SHIPMENT_LINE_ID,
OBJECT_VERSION_NUMBER,
SHIP_FROM_LOCATION_ID -- IB-Phase-2
FROM wsh_inbound_txn_history
WHERE shipment_header_id = p_shipment_header_id
AND transaction_type = p_transaction_type
FOR UPDATE OF STATUS NOWAIT;
SELECT 1
FROM wsh_inbound_txn_history
WHERE transaction_id = p_transaction_id
FOR UPDATE OF STATUS NOWAIT;
FND_MESSAGE.SET_NAME('WSH','WSH_IB_TXN_UPDATE_ERROR');
SELECT meaning,
description
FROM FND_LOOKUP_VALUES_VL
WHERE lookup_code = p_lookupCode
AND lookup_type = p_lookupType;