APPS.ECX_TRANSACTIONS_API SQL Statements

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

Line: 18

	x_last_updated_by               Out NOCOPY     pls_integer,
	x_last_update_date              Out NOCOPY     date
	)
is
-- get data from ECX_TRANSACTIONS.
-- Bug #2183619 : Modify the cursor to add party_type
cursor c_transaction
	(
	p_transaction_type	in	varchar2,
	p_transaction_subtype	in	varchar2,
        p_party_type            in      varchar2
	)
is

Select 	TRANSACTION_ID,
	TRANSACTION_DESCRIPTION,
	CREATED_BY,
	CREATION_DATE,
	LAST_UPDATED_BY,
	LAST_UPDATE_DATE
from 	ECX_TRANSACTIONS_VL
where 	transaction_type    = p_transaction_type
and   	transaction_subtype = p_transaction_subtype
and     party_type          = p_party_type; --Bug #2183619

Line: 85

		x_last_updated_by,
		x_last_update_date;

Line: 150

i_last_updated_by		pls_integer;

Line: 151

i_last_update_date		date;

Line: 206

		x_last_updated_by		=>	i_last_updated_by,
		x_last_update_date		=>	i_last_update_date
		);

Line: 212

		select 	ecx_transactions_s.nextval
		into 	x_transaction_id
		from 	dual;

Line: 218

			i_last_updated_by :=1;

Line: 220

			i_last_updated_by :=0;

Line: 223

                /* Call the table handler API for insertion of data
                   into ecx_transactions_b and ecx_transactions_tl tables*/

                ECX_TRANSACTIONS_PKG.INSERT_ROW
                (
                   x_rowid                   =>   i_rowid,
                   x_transaction_id          =>   x_transaction_id ,
                   x_transaction_type        =>   upper(p_transaction_type),
                   x_transaction_subtype     =>   upper(p_transaction_subtype),
                   x_party_type              =>   p_party_type,
                   x_transaction_description =>   p_transaction_description,
                   x_admin_user              =>   p_admin_user,
                   x_creation_date           =>   sysdate,
                   x_created_by              =>   i_last_updated_by,
                   x_last_update_date        =>   sysdate,
                   x_last_updated_by         =>   i_last_updated_by,
                   x_last_update_login       =>   0);

Line: 271

 This Update_Transaction API is used to update an existing transaction description in the ECX_TRANSACTIONS table.
**/
procedure update_transaction
	(
	x_return_status	 		Out NOCOPY pls_integer,
	x_msg	 	 		Out NOCOPY varchar2,
	p_transaction_id	 	In	   Pls_integer,
	p_transaction_type	 	In	   Varchar2,
	p_transaction_subtype	 	In	   Varchar2,
	p_party_type	 		In	   Varchar2,
	p_transaction_description	In	   Varchar2,
	p_owner				in	   varchar2
	)
is
i_last_updated_by	pls_integer;

Line: 297

	-- update description in ECX_TRANSACTIONS.
	-- We cannot update the Primary Key of the Entity.Only description should be updated.

		if p_owner = 'SEED'
		then
			i_last_updated_by :=1;

Line: 304

			i_last_updated_by :=0;

Line: 307

	Update  ecx_transactions_b
	set 	last_updated_by         = i_last_updated_by,
		last_update_date        = sysdate
	Where transaction_id = p_transaction_id;

Line: 315

                x_msg := ecx_debug.getTranslatedMessage('ECX_NO_ROWS_UPDATED',
                                                        'p_table',
                                                        'ECX_TRANSACTIONS_B',
                                                        'p_param_name',
                                                        'Transaction ID',
                                                         'p_param_id',
                                                         p_transaction_id);

Line: 326

      Update ecx_transactions_tl
	set 	transaction_description = p_transaction_description,
		last_updated_by         = i_last_updated_by,
		last_update_date        = sysdate,
                source_lang             = userenv('LANG')
	Where transaction_id  = p_transaction_id and
              userenv('LANG') in (language, source_lang);

Line: 337

      x_msg := ecx_debug.getTranslatedMessage('ECX_NO_TRANS_ROWS_UPDATED',
                                              'p_table',
                                              'ECX_TRANSACTIONS_TL',
                                              'p_param_name',
                                              'Transaction ID',
                                              'p_param_id',
                                              'p_transaction_id');

Line: 351

end update_transaction;

Line: 354

 This Delete_Transaction API is used to delete an existing transaction
 definition in the ECX_TRANSACTIONS
 table and also the external processes that are associate to it.
 This API allows users to delete a
 transaction definition by specifying the transaction id.
**/
procedure delete_transaction
	(
	x_return_status	 		Out NOCOPY pls_integer,
	x_msg	 	 		Out NOCOPY varchar2,
	p_transaction_id		In	   Pls_integer
	)
is

num    pls_integer;

Line: 387

	-- Otherwise, do the delete.

        -- make sure that is no TP using this process first.
	select  count(*)
	into    num
	from    ecx_tp_details etd,
		ecx_ext_processes eep
	where   eep.ext_process_id = etd.ext_process_id
	and	eep.transaction_id = p_transaction_id;

Line: 404

	delete from ecx_ext_processes
	where transaction_id = p_transaction_id;

Line: 408

        ECX_TRANSACTIONS_PKG.DELETE_ROW(x_transaction_id  =>  p_transaction_id);

Line: 412

      x_msg := ecx_debug.getTranslatedMessage('ECX_NO_ROWS_DELETED',
                                              'p_table', 'ECX_TRANSACTIONS',
                                              'p_param_name','Transaction ID',
                                              'p_param_id',p_transaction_id);

Line: 419

end delete_transaction;

Line: 445

	x_last_updated_by	 	Out NOCOPY pls_integer,
	x_last_update_date	 	Out NOCOPY date,
        p_ext_type                      In         Varchar2 ,
        p_ext_subtype                   In         Varchar2,
	p_standard_type			IN	   varchar2
	)
is
   num varchar2(2000);

Line: 471

                x_last_updated_by               =>      x_last_updated_by,
                x_last_update_date              =>      x_last_update_date
                );

Line: 502

		select 	standard_id
		into 	x_standard_id
		from 	ecx_standards
		where  	standard_code = p_standard
		and	standard_type = p_standard_type;

Line: 514

	-- select data from ECX_EXT_PROCESSES
	/* Bug #2183619,Modified to add check for External Type and Sub type */
	select 	EXT_PROCESS_ID,
		EXT_TYPE,
		EXT_SUBTYPE,
		QUEUE_NAME,
		CREATED_BY,
		CREATION_DATE,
		LAST_UPDATED_BY,
		LAST_UPDATE_DATE
	into 	x_ext_process_id,
		x_ext_type,
		x_ext_subtype,
		x_queue_name,
		x_created_by,
		x_creation_date,
		x_last_updated_by,
		x_last_update_date
	from 	ECX_EXT_PROCESSES
	where 	transaction_id  = x_transaction_id
	and   	standard_id     = x_standard_id
	and   	direction       = p_direction
        and     (p_ext_type is null or ext_type=p_ext_type)
        and     (p_ext_subtype is null or ext_subtype=p_ext_subtype);

Line: 582

	x_last_updated_by	 	Out NOCOPY pls_integer,
	x_last_update_date	 	Out NOCOPY date,
        p_ext_type                      In         Varchar2 ,
        p_ext_subtype                   In         Varchar2 ,
	p_standard_type			IN	   varchar2
	)
is
begin
	x_return_status := ECX_UTIL_API.G_NO_ERROR;

Line: 624

		select 	standard_id
		into 	x_standard_id
		from 	ecx_standards
		where  	standard_code = p_standard
		and	standard_type = p_standard_type;

Line: 638

	-- select data from ECX_EXT_PROCESSES
	/* Bug #2183619,Modified to add check for External Type and Sub type */
	select 	TRANSACTION_DESCRIPTION,
		EXT_PROCESS_ID,
		EXT_TYPE,
		EXT_SUBTYPE,
		QUEUE_NAME,
		eep.CREATED_BY,
		eep.CREATION_DATE,
		eep.LAST_UPDATED_BY,
		eep.LAST_UPDATE_DATE
	into 	x_transaction_description,
		x_ext_process_id,
		x_ext_type,
		x_ext_subtype,
		x_queue_name,
		x_created_by,
		x_creation_date,
		x_last_updated_by,
		x_last_update_date
	from 	ECX_EXT_PROCESSES eep,
		ECX_TRANSACTIONS_VL et
	where  	et.transaction_id	= p_transaction_id
	and	et.transaction_id      = eep.transaction_id
	and   	eep.standard_id        = x_standard_id
	and   	eep.direction          = p_direction
        and     (p_ext_type is null or eep.ext_type=p_ext_type)
        and     (p_ext_subtype is null or eep.ext_subtype=p_ext_subtype);

Line: 708

i_last_updated_by	pls_integer;

Line: 776

		select 	standard_id
		into 	I_stand_id
		from 	ecx_standards
		where  	standard_code = p_standard
		and	standard_type = p_standard_type;

Line: 794

	select 	ecx_ext_processes_s.nextval
	into 	x_ext_process_id
	from 	dual;

Line: 800

			i_last_updated_by :=1;

Line: 802

			i_last_updated_by :=0;

Line: 805

	-- Insert data into ECX_EXT_PROCESSES
	insert into ecx_ext_processes
		(
		EXT_PROCESS_ID,
		EXT_TYPE,
		EXT_SUBTYPE,
		TRANSACTION_ID,
		STANDARD_ID,
		QUEUE_NAME,
		DIRECTION,
		CREATED_BY,
		CREATION_DATE,
		LAST_UPDATED_BY,
		LAST_UPDATE_DATE
		)
	Values (
		X_ext_process_id,
		P_ext_type,
		p_ext_subtype,
		p_transaction_id,
		I_stand_id,
		p_queue_name,
		p_direction,
		i_last_updated_by,
		sysdate,
		i_last_updated_by,
		sysdate
		);

Line: 873

i_last_updated_by		pls_integer;

Line: 874

i_last_update_date		date;

Line: 891

		x_last_updated_by		=>	i_last_updated_by,
		x_last_update_date		=>	i_last_update_date
		);

Line: 920

 Update_Ext_Process API is used to update an existing external process definition in the ECX_EXT_PROCESSES table.
 This API allows users to update the ext_type, ext_subtype, standard, queue_name and direction by
 specifying the ext_process_id.
**/
procedure update_external_transaction
	(
 	x_return_status	 		Out NOCOPY pls_integer,
	x_msg	 	 		Out NOCOPY varchar2,
  	p_ext_process_id	 	In	   pls_integer,
   	p_ext_type	 		In	   Varchar2,
    	p_ext_subtype	 		In	   Varchar2,
     	p_standard	 		In	   Varchar2,
      	p_queue_name	 		In	   Varchar2,
       	p_direction	 		In	   Varchar2,
	p_owner				in	   varchar2 ,
	p_standard_type			IN	   varchar2
	)
is
I_stand_id	pls_integer;

Line: 939

i_last_updated_by	pls_integer;

Line: 1006

		select 	standard_id
		into 	I_stand_id
		from 	ecx_standards
		where  	standard_code = p_standard
		and	standard_type = p_standard_type;

Line: 1022

			i_last_updated_by :=1;

Line: 1024

			i_last_updated_by :=0;

Line: 1027

	-- update data into ECX_EXT_PROCESSES
	-- SHould we allow to update the Unqiue itself?
	update 	ECX_EXT_PROCESSES
	set 	ext_type    = p_ext_type,
		ext_subtype = p_ext_subtype,
		standard_id = I_stand_id,
		queue_name  = p_queue_name,
		direction   = p_direction,
		last_updated_by = i_last_updated_by,
		last_update_date = sysdate
	where 	ext_process_id = p_ext_process_id;

Line: 1042

		x_msg := ecx_debug.getTranslatedMessage('ECX_NO_ROWS_UPDATED',
			'p_table', 'ecx_ext_processes', 'p_key', p_ext_process_id);

Line: 1058

end update_external_transaction;

Line: 1061

This Delete_Ext_Process API is used to delete an existing external process definition in the ECX_EXT_PROCESSES table.
This API allows users to delete a process definition by specifying the ext_process_id.
**/
procedure delete_external_transaction
	(
	x_return_status	 		Out NOCOPY pls_integer,
	x_msg	 	 		Out NOCOPY varchar2,
	p_ext_process_id	 	In	   pls_integer
	)
is
num   pls_integer;

Line: 1083

	select 	count(*)
	into 	num
	from 	ecx_tp_details
	where  	ext_process_id = p_ext_process_id;

Line: 1095

	delete from ecx_ext_processes
	where ext_process_id = p_ext_process_id;

Line: 1101

		x_msg := ecx_debug.getTranslatedMessage('ECX_NO_ROWS_DELETED',
		'p_table', 'ECX_EXT_PROCESSES', 'p_key', p_ext_process_id);

Line: 1110

end delete_external_transaction;