[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Create_Update_Txns_History(
p_txns_history_rec IN OUT NOCOPY Txns_History_Record_Type,
x_txns_id OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2
) IS
-- local variables
l_txns_id NUMBER;
SELECT transaction_id, transaction_status
FROM wsh_transactions_history
WHERE document_type = p_txns_history_rec.document_type AND
document_number = p_txns_history_rec.document_number AND
document_direction = p_txns_history_rec.document_direction AND
action_type = p_txns_history_rec.action_type AND
entity_number = p_txns_history_rec.entity_number AND
entity_type = p_txns_history_rec.entity_type AND
trading_partner_id = p_txns_history_rec.trading_partner_id
FOR UPDATE NOWAIT;
SELECT wnd.status_code
FROM wsh_new_deliveries wnd,
wsh_transactions_history wth
WHERE wth.document_number = v_doc_number
AND wth.entity_type = 'DLVY'
AND wth.document_type = 'SR'
AND wth.document_direction = 'O'
AND wth.action_type = 'A'
AND wth.entity_number = wnd.name
ORDER BY wth.transaction_id desc;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CREATE_UPDATE_TXNS_HISTORY';
wsh_debug_sv.push(l_module_name,'Create_Update_Txns_History');
p_api_name => 'WSH_TRANSACTIONS_HISTORY_PKG.Create_Update_Txns_History',
x_return_status => l_return_status);
-- Before Insert Check for validity of data
-- Need to validate document_direction, entity_type, action_type
-- ctd.. transaction_status, document_type
SELECT WSH_TRANSACTION_S.nextval
INTO x_transaction_id
FROM dual;
INSERT INTO wsh_transactions_history(
TRANSACTION_ID,
DOCUMENT_TYPE,
DOCUMENT_NUMBER,
ORIG_DOCUMENT_NUMBER,
DOCUMENT_DIRECTION,
TRANSACTION_STATUS,
ACTION_TYPE,
ENTITY_NUMBER,
ENTITY_TYPE,
TRADING_PARTNER_ID,
ECX_MESSAGE_ID,
EVENT_NAME,
EVENT_KEY,
ITEM_TYPE,
INTERNAL_CONTROL_NUMBER,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
--R12.1.1 STANDALONE PROJECT
DOCUMENT_REVISION,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
REQUEST_ID,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15)
VALUES( x_transaction_id,
p_txns_history_rec.document_type,
p_txns_history_rec.document_number,
p_txns_history_rec.orig_document_number,
p_txns_history_rec.document_direction,
-- k proj bmso p_txns_history_rec.transaction_status,
l_trans_status,
p_txns_history_rec.action_type,
p_txns_history_rec.entity_number,
p_txns_history_rec.entity_type,
p_txns_history_rec.trading_partner_id,
p_txns_history_rec.ECX_MESSAGE_ID,
p_txns_history_rec.EVENT_NAME,
p_txns_history_rec.EVENT_KEY,
p_txns_history_rec.ITEM_TYPE,
p_txns_history_rec.INTERNAL_CONTROL_NUMBER,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.USER_ID,
--R12.1.1 STANDALONE PROJECT
p_txns_history_rec.DOCUMENT_REVISION,
fnd_global.prog_appl_id,
fnd_global.conc_program_id,
SYSDATE,
fnd_global.conc_request_id,
p_txns_history_rec.ATTRIBUTE_CATEGORY,
p_txns_history_rec.ATTRIBUTE1,
p_txns_history_rec.ATTRIBUTE2,
p_txns_history_rec.ATTRIBUTE3,
p_txns_history_rec.ATTRIBUTE4,
p_txns_history_rec.ATTRIBUTE5,
p_txns_history_rec.ATTRIBUTE6,
p_txns_history_rec.ATTRIBUTE7,
p_txns_history_rec.ATTRIBUTE8,
p_txns_history_rec.ATTRIBUTE9,
p_txns_history_rec.ATTRIBUTE10,
p_txns_history_rec.ATTRIBUTE11,
p_txns_history_rec.ATTRIBUTE12,
p_txns_history_rec.ATTRIBUTE13,
p_txns_history_rec.ATTRIBUTE14,
p_txns_history_rec.ATTRIBUTE15);
wsh_debug_sv.log (l_module_name,'Record already exists. So Need to Update in wsh_transactions_history');
-- Record already exists. So Need to Update
-- Before Update Check for validity of status
IF(l_transaction_status = 'ST' AND p_txns_history_rec.transaction_status <> 'SC') THEN
raise invalid_status;
UPDATE wsh_transactions_history
SET entity_number = p_txns_history_rec.entity_number,
entity_type = p_txns_history_rec.entity_type,
transaction_status = p_txns_history_rec.transaction_status,
ecx_message_id = p_txns_history_rec.ecx_message_id,
event_name = p_txns_history_rec.event_name,
event_key = p_txns_history_rec.event_key,
internal_control_number = p_txns_history_rec.internal_control_number,
item_type = p_txns_history_rec.item_type,
last_update_date = SYSDATE,
last_updated_by = fnd_global.user_id,
--R12.1.1 STANDALONE PROJECT
program_application_id = fnd_global.prog_appl_id,
program_id = fnd_global.conc_program_id,
program_update_date = SYSDATE,
request_id = fnd_global.conc_request_id,
attribute_category = p_txns_history_rec.ATTRIBUTE_CATEGORY,
attribute1 = p_txns_history_rec.ATTRIBUTE1,
attribute2 = p_txns_history_rec.ATTRIBUTE2,
attribute3 = p_txns_history_rec.ATTRIBUTE3,
attribute4 = p_txns_history_rec.ATTRIBUTE4,
attribute5 = p_txns_history_rec.ATTRIBUTE5,
attribute6 = p_txns_history_rec.ATTRIBUTE6,
attribute7 = p_txns_history_rec.ATTRIBUTE7,
attribute8 = p_txns_history_rec.ATTRIBUTE8,
attribute9 = p_txns_history_rec.ATTRIBUTE9,
attribute10 = p_txns_history_rec.ATTRIBUTE10,
attribute11 = p_txns_history_rec.ATTRIBUTE11,
attribute12 = p_txns_history_rec.ATTRIBUTE12,
attribute13 = p_txns_history_rec.ATTRIBUTE13,
attribute14 = p_txns_history_rec.ATTRIBUTE14,
attribute15 = p_txns_history_rec.ATTRIBUTE15
WHERE transaction_id = l_transaction_id;
END Create_Update_Txns_History;
SELECT wth.transaction_id,
wth.document_type,
wth.document_direction,
wth.document_number,
wth.orig_document_number,
wth.entity_number,
wth.entity_type,
wth.trading_partner_id,
wth.action_type,
wth.transaction_status,
wth.ecx_message_id,
wth.event_name,
wth.event_key ,
wth.item_type,
wth.internal_control_number,
--R12.1.1 STANDALONE PROJECT
wth.document_revision,
wth.attribute_category,
wth.attribute1,
wth.attribute2,
wth.attribute3,
wth.attribute4,
wth.attribute5,
wth.attribute6,
wth.attribute7,
wth.attribute8,
wth.attribute9,
wth.attribute10,
wth.attribute11,
wth.attribute12,
wth.attribute13,
wth.attribute14,
wth.attribute15,
wndi.client_code
FROM wsh_transactions_history wth,
wsh_new_del_interface wndi
WHERE wth.item_type = p_item_type
and wth.event_key = p_event_key
and wth.document_direction = p_direction
and wth.document_type = p_document_type
and wth.entity_number = wndi.delivery_interface_id (+);
creates a txns-history record and calls the create_update_txns_history
procedure with that record
-----------------------------------------------------------------------------
*/
PROCEDURE Create_Txns_History(
p_transaction_id IN NUMBER,
p_document_type IN VARCHAR2,
p_document_direction IN VARCHAR2,
p_document_number IN VARCHAR2,
p_orig_document_number IN VARCHAR2,
p_entity_number IN VARCHAR2,
p_entity_type IN VARCHAR2,
p_trading_partner_id IN NUMBER,
p_action_type IN VARCHAR2,
p_transaction_status IN VARCHAR2,
p_ecx_message_id IN VARCHAR2,
p_event_name IN VARCHAR2,
p_event_key IN VARCHAR2,
p_item_type IN VARCHAR2,
p_internal_control_number IN VARCHAR2,
--R12.1.1 STANDALONE PROJECT
p_document_revision IN NUMBER DEFAULT NULL,
x_return_status OUT NOCOPY VARCHAR2) IS
l_txn_hist_rec Txns_History_Record_Type;
create_update_failed exception;
Create_Update_Txns_History(
p_txns_history_rec => l_txn_hist_rec,
x_txns_id => l_txn_id,
x_return_status => l_return_status);
wsh_debug_sv.log (l_module_name, 'Return status from Create_Update_Txns_History', l_return_status);
raise create_update_failed;
WHEN create_update_failed THEN
x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;