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

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

Line: 6


Select	ece_stage_id_s.NEXTVAL
from 	dual;

Line: 11


select	ece_document_id_s.NEXTVAL
from	dual;

Line: 18


Build and Parses the Insert Statement for insert into ece_stage for each Level.
The Cursor handles are stored in the ec_utils.g_int_levels(i).cursor_handle.
**/
procedure	parse_insert_statement
		(
		i_level			in	pls_integer
		)
is
i_Insert_Cursor		pls_integer;

Line: 27


cInsert_stmt		varchar2(32000) := 'INSERT INTO ECE_STAGE ( ';

Line: 33


ec_debug.push('EC_OUTBOUND.PARSE_INSERT_STATEMENT');

Line: 38


	cInsert_stmt := cInsert_stmt||' Stage_id, Document_Id , Transaction_type , Transaction_Level ,';

Line: 39


	cInsert_stmt := cInsert_stmt||' Line_Number , Parent_Stage_Id , Run_Id , Document_Number ,Status ,';

Line: 42


	cInsert_stmt := cInsert_stmt||' creation_date , created_by , last_update_date , last_updated_by ,';

Line: 52


			--- Build Insert Statement
			cInsert_stmt := cInsert_stmt||' '||ec_utils.g_file_tbl(i).staging_column|| ',';

Line: 58


	cInsert_stmt := RTRIM(cInsert_stmt,',')||')';

Line: 60


	cInsert_stmt := cInsert_stmt||cValue_stmt;

Line: 63


	ec_debug.pl(3,'EC','ECE_STAGE_INSERT_LEVEL','LEVEL',i_level,null);

Line: 64


	ec_debug.pl(3,cInsert_stmt);

Line: 71


	i_Insert_Cursor := dbms_sql.Open_Cursor;

Line: 74


	ec_debug.pl(3,'i_insert_cursor',i_insert_cursor);

Line: 77


	ec_utils.g_ext_levels(i_level).cursor_handle := i_insert_cursor;

Line: 80


		dbms_sql.parse(i_Insert_Cursor,cInsert_stmt,dbms_sql.native);

Line: 84


		ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_OUTBOUND.PARSE_INSERT_STATEMENT');

Line: 85


		ece_error_handling_pvt.print_parse_error (error_position,cInsert_stmt);

Line: 90


ec_debug.pop('EC_OUTBOUND.PARSE_INSERT_STATEMENT');

Line: 97


        ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_OUTBOUND.PARSE_INSERT_STATEMENT');

Line: 101


END parse_insert_statement;

Line: 104


Prepares the Select statement for the ec_views on the base Oracle Applications tables.
**/
procedure select_clause
	(
        i_level       	IN 		pls_integer,
        i_Where_string   OUT NOCOPY		VARCHAR2
	) IS
cSelect_stmt         VARCHAR2(32000) := 'SELECT ';

Line: 124


EC_DEBUG.PUSH('EC_OUTBOUND.SELECT_CLAUSE');

Line: 147


      	-- build SELECT statement
       		cSelect_stmt :=  cSelect_stmt || ' ' || cWord1 ||
			nvl(ec_utils.g_file_tbl(i).base_column_Name,'NULL') || cWord2 || ',';

Line: 156


cSelect_stmt := RTRIM(cSelect_stmt, ',');

Line: 157


i_Where_string := cSelect_stmt||' '||cFrom_stmt||' '||cWhere_Stmt;

Line: 161


EC_DEBUG.POP('EC_OUTBOUND.SELECT_CLAUSE');

Line: 166


	EC_DEBUG.PL(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_OUTBOUND.SELECT_CLAUSE');

Line: 170


END select_clause;

Line: 174


1. Select statement on the ec_views.
2. Insert statement for ece_stage table.
3. Parses and loads Custom Procedures into memory table.
4. Loads mappings required by these procedures into memory tables.
**/
procedure load_objects
is
i_counter	pls_integer :=0;

Line: 189


	select_clause
		(
		i,
		ec_utils.g_int_levels(i).sql_stmt
		);

Line: 208


	-- Parse the Select Statement for Each level
	BEGIN
		dbms_sql.parse	(
				ec_utils.g_int_levels(i).cursor_handle,
				ec_utils.g_int_levels(i).sql_stmt,
				dbms_sql.native
				);

Line: 247


	-- Parse the Insert Statement for Staging table.
	parse_insert_statement
			(
			i
			);

Line: 269


Bind the values to the Insert statement for the ece_stage table.
**/
procedure	bind_insert_statement
		(
		i_level		in	pls_integer
		)
is
i_Insert_Cursor		pls_integer := ec_utils.g_ext_levels(i_level).cursor_handle;

Line: 283


ec_debug.push('EC_OUTBOUND.BIND_INSERT_STATEMENT');

Line: 289


		dbms_sql.bind_variable (i_Insert_Cursor,'a1',to_number(ec_utils.g_ext_levels(i_level).Stage_Id));

Line: 290


		dbms_sql.bind_variable (i_Insert_Cursor,'a2',to_number(ec_utils.g_ext_levels(i_level).Document_Id));

Line: 291


		dbms_sql.bind_variable (i_Insert_Cursor,'a3',ec_utils.g_transaction_type);

Line: 292


		dbms_sql.bind_variable (i_Insert_Cursor,'a4',to_number(i_level));

Line: 293


		dbms_sql.bind_variable (i_Insert_Cursor,'a5',to_number(ec_utils.g_ext_levels(i_level).Line_Number));

Line: 294


		dbms_sql.bind_variable (i_Insert_Cursor,'a6',ec_utils.g_ext_levels(i_level).Parent_Stage_Id);

Line: 295


		dbms_sql.bind_variable (i_Insert_Cursor,'a7',ec_utils.g_run_id);

Line: 296


		dbms_sql.bind_variable (i_Insert_Cursor,'a8',ec_utils.g_ext_levels(i_level).Document_Number);

Line: 297


		dbms_sql.bind_variable (i_Insert_Cursor,'a9',i_status);

Line: 300


		dbms_sql.bind_variable (i_Insert_Cursor,'w1',sysdate);

Line: 301


		dbms_sql.bind_variable (i_Insert_Cursor,'w2',fnd_global.user_id);

Line: 302


		dbms_sql.bind_variable (i_Insert_Cursor,'w3',sysdate);

Line: 303


		dbms_sql.bind_variable (i_Insert_Cursor,'w4',fnd_global.user_id);

Line: 316


		ec_debug.pl(3,'LAST_UPDATE_DATE',sysdate);

Line: 317


		ec_debug.pl(3,'LAST_UPDATED_BY',fnd_global.user_id);

Line: 327


							i_Insert_Cursor,
							'b'||k,ins_value,
							500
							);

Line: 339


		dummy := dbms_sql.execute(i_Insert_Cursor);

Line: 342


			ec_debug.pl(3,'EC','ECE_STAGE_INSERTED',null);

Line: 345


			ec_debug.pl(0,'EC','ECE_STAGE_INSERT_FAILED','LEVEL',i_level);

Line: 346


			ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_OUTBOUND.BIND_INSERT_STATEMENT');

Line: 353


		ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_OUTBOUND.BIND_INSERT_STATEMENT');

Line: 361


ec_debug.pop('EC_OUTBOUND.BIND_INSERT_STATEMENT');

Line: 367


        ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_OUTBOUND.BIND_INSERT_STATEMENT');

Line: 371


END bind_insert_statement;

Line: 375


Inserts the data into the staging table.
**/
procedure	insert_into_stage
		(
		i_level		in	pls_integer
		)
is
i_parent_stage_id	pls_integer;

Line: 388


ec_debug.push('EC_OUTBOUND.INSERT_INTO_STAGE');

Line: 398


			-- Insert data into Stage table
			if i_level = 1
			then
				--Generate Document Id
				open 	seq_document_id;

Line: 473


			bind_insert_statement
				(
				i_level
				);

Line: 478


ec_debug.pop('EC_OUTBOUND.INSERT_INTO_STAGE');

Line: 485


        ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_OUTBOUND.INSERT_INTO_STAGE');

Line: 489


END insert_into_stage;

Line: 627


							--Insert into Staging
							insert_into_stage
									(
									ec_utils.g_int_ext_levels(k).external_level
									);

Line: 679


							--Insert into Staging
							insert_into_stage
								(
								ec_utils.g_int_ext_levels(k).external_level
								);

Line: 793


	ec_utils.g_file_tbl.DELETE;

Line: 794


	ec_utils.g_int_levels.DELETE;

Line: 795


	ec_utils.g_ext_levels.DELETE;

Line: 796


	ec_utils.g_int_ext_levels.DELETE;

Line: 797


	ec_utils.g_stage_data.DELETE;

Line: 798


	ec_utils.g_parameter_stack.DELETE;

Line: 799


	ec_utils.g_procedure_stack.DELETE;

Line: 800


	ec_utils.g_procedure_mappings.DELETE;

Line: 801


	ec_utils.g_stack_pointer.DELETE;

Line: 816


        	select  ece_output_runs_s.NEXTVAL
        	into    i_run_id
        	from    dual;

Line: 886


This file will delete all records in a staging table without using the
expense parsing of dbms_sql package.  The RUN_ID parameter is the only required parameter.
The DOCUMENT_ID parameter can be optionally used to delete one document from the staging table
at a time.
**/
procedure delete_stage_data
	(
	i_run_id		IN	number,
	i_document_id		IN	number DEFAULT NULL
	) IS
BEGIN
if ec_debug.G_debug_level >= 2 then
ec_debug.push('EC_OUTBOUND.DELETE_STAGE_DATA');

Line: 903


		ec_debug.pl(0,'EC','ECE_PARAM_MISSING','PARAMETER','I_RUN_ID', 'PROCEDURE','EC_OUTBOUND.DELETE_STAGE_DATA');

Line: 912


	Delete all indicated records from ECE_STAGE
	**/
	DELETE FROM ece_stage
	WHERE run_id = i_run_id
	AND (document_id = i_document_id OR i_document_id IS NULL);

Line: 920


		Output a warning message if no rows are deleted
		**/
		if ec_debug.G_debug_level >= 1 then
		ec_debug.pl(1,'NO rows deleted from ECE_STAGE');

Line: 927


	ec_debug.pl(3,'Number of rows deleted from ECE_STAGE',SQL%ROWCOUNT);

Line: 929


ec_debug.pop('EC_OUTBOUND.DELETE_STAGE_DATA');

Line: 935


        ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','EC_OUTBOUND.DELETE_STAGE_DATA');

Line: 939


END delete_stage_data;