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.INV_TRANSACTIONS_HISTORY_PKG SQL Statements

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

Line: 10


PROCEDURE Create_Update_Txns_History(
p_txns_history_rec	IN OUT NOCOPY  Txns_History_Record_Type,
P_xml_document_id       IN  NUMBER,
x_txns_id		OUT NOCOPY 	NUMBER,
x_return_status		OUT NOCOPY 	VARCHAR2
) IS

-- local variables
l_txns_id 		NUMBER;

Line: 39


SELECT transaction_id, transaction_status
FROM MTL_TXNS_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 AND
        transaction_status = 'IP'
FOR UPDATE NOWAIT;

Line: 59


        inv_trx_util_pub.TRACE('Entering Create_Update_Txns_History', 'INV_TRANSACTIONS_HISTORY_PKG', 9);

Line: 121


			-- Before Insert Check for validity of data
			-- Need to validate document_direction, entity_type, action_type
			-- ctd.. transaction_status, document_type


			SELECT MTL_TXNS_HISTORY_S.nextval
			INTO x_transaction_id
			FROM dual;

Line: 132


                           inv_trx_util_pub.TRACE('Inserting into MTL_TXNS_HISTORY', 'INV_TRANSACTIONS_HISTORY_PKG', 9);

Line: 135


			INSERT INTO MTL_TXNS_HISTORY(
			TRANSACTION_ID,
			DOCUMENT_TYPE,
			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,
			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.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.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);

Line: 222


                           inv_trx_util_pub.TRACE('Record inserted into MTL_TXNS_HISTORY', 'INV_TRANSACTIONS_HISTORY_PKG', 9);

Line: 232


                           inv_trx_util_pub.TRACE('Record already exists. So need to update in MTL_TXNS_HISTORY', 'INV_TRANSACTIONS_HISTORY_PKG', 9);

Line: 235


			-- Record already exists. So Need to Update
			-- Before Update Check for validity of status

			IF(l_transaction_status in ('IP', 'AP') AND p_txns_history_rec.transaction_status NOT IN('ER', 'ST','IP')) THEN
				raise invalid_status;

Line: 250


			UPDATE MTL_TXNS_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,
				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;

Line: 284


                           inv_trx_util_pub.TRACE('MTL_TXNS_HISTORY record updated', 'INV_TRANSACTIONS_HISTORY_PKG', 9);

Line: 288


                        select count(*)
                        into l_dummy
                        from mtl_adjustment_sync_temp
                        where entity_id = p_txns_history_rec.entity_number;

Line: 297


                        UPDATE mtl_material_transactions
                           SET transaction_extracted = 'Y',
                               xml_document_id = p_txns_history_rec.entity_number
                         WHERE NVL(transaction_extracted, 'N') = 'P'
                           AND xml_document_id is null
                           AND transaction_id IN (select transaction_number
                                                    from mtl_adjustment_sync_temp
                                                   where entity_id = p_txns_history_rec.entity_number);

Line: 307


                           inv_trx_util_pub.TRACE('No of rows in MMT updated: '||SQL%ROWCOUNT, 'INV_TRANSACTIONS_HISTORY_PKG', 9);

Line: 311


                           inv_trx_util_pub.TRACE('MMT updated, now delete the temp table', 'INV_TRANSACTIONS_HISTORY_PKG', 9);

Line: 314


                        INV_INVENTORY_ADJUSTMENT.delete_temp_table(p_txns_history_rec.entity_number);

Line: 335


           inv_trx_util_pub.TRACE('Exiting Create_Update_Txns_History', 'INV_TRANSACTIONS_HISTORY_PKG', 9);

Line: 370


END Create_Update_Txns_History;

Line: 383


SELECT wth.transaction_id,
	wth.document_type,
	wth.document_direction,
	wth.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,
        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,
        ''
FROM MTL_TXNS_HISTORY wth
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 rownum = 1;

Line: 466


/* should be deleted, if not needed.
-----------------------------------------------------------------------------
   PROCEDURE  : Create_Txns_History
   PARAMETERS :
  DESCRIPTION : This procedure is written for use by the inbound mapping.
Since XML gateway does not support calls to procedures with record types as
parameters, we need this wrapper. This takes in the individual columns,
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,
	p_document_revision     IN     NUMBER DEFAULT NULL,
	x_return_status		OUT NOCOPY 	VARCHAR2) IS

	l_txn_hist_rec 		Txns_History_Record_Type;

Line: 500


	create_update_failed	exception;

Line: 544


	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: 554


		raise create_update_failed;

Line: 558


WHEN create_update_failed THEN
	x_return_status := rcv_error_pkg.g_ret_sts_error;

Line: 561


           inv_trx_util_pub.TRACE('create_update_failed exception has occured.', 'INV_TRANSACTIONS_HISTORY_PKG', 9);