eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.WSH_TRANSACTIONS_HISTORY_PKG SQL Statements

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

Line: 7


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;

Line: 30


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;

Line: 51


     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;

Line: 65


l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CREATE_UPDATE_TXNS_HISTORY';

Line: 77


	wsh_debug_sv.push(l_module_name,'Create_Update_Txns_History');

Line: 184


                                   p_api_name      => 'WSH_TRANSACTIONS_HISTORY_PKG.Create_Update_Txns_History',
                                   x_return_status => l_return_status);

Line: 197


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

Line: 205


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

Line: 284


	             wsh_debug_sv.log (l_module_name,'Record already exists. So Need to Update
                                                        in wsh_transactions_history');

Line: 287


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

Line: 305


			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 = 1,
				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;

Line: 389


END Create_Update_Txns_History;

Line: 402


SELECT transaction_id,
	document_type,
	document_direction,
	document_number,
	orig_document_number,
	entity_number,
	entity_type,
	trading_partner_id,
	action_type,
	transaction_status,
	ecx_message_id,
	event_name,
	event_key ,
	item_type,
	internal_control_number,
	attribute_category,
	attribute1,
	attribute2,
	attribute3,
	attribute4,
	attribute5,
	attribute6,
	attribute7,
	attribute8,
	attribute9,
	attribute10,
	attribute11,
	attribute12,
	attribute13,
	attribute14,
	attribute15
FROM wsh_transactions_history
WHERE item_type 	= p_item_type
and event_key		= p_event_key
and document_direction 	= p_direction
and document_type	= p_document_type;

Line: 507


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,
	x_return_status		OUT NOCOPY 	VARCHAR2) IS

l_txn_hist_rec 		Txns_History_Record_Type;

Line: 534


create_update_failed	exception;

Line: 587


	Create_Update_Txns_History(
	p_txns_history_rec	=> l_txn_hist_rec,
	x_txns_id		=> l_txn_id,
	x_return_status		=> l_return_status);

Line: 593


	  wsh_debug_sv.log (l_module_name, 'Return status from 	Create_Update_Txns_History', l_return_status);

Line: 597


		raise create_update_failed;

Line: 603


WHEN create_update_failed THEN
	x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;