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

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

Line: 3


debug_mode_on_insert BOOLEAN := FALSE;

Line: 4


debug_mode_on_select BOOLEAN := FALSE;

Line: 21


PROCEDURE select_clause(
            cTransaction_Type       IN VARCHAR2,
            cCommunication_Method   IN VARCHAR2,
            cInterface_Table        IN VARCHAR2,
            p_source_tbl            IN ece_flatfile_pvt.Interface_tbl_type,
            cSelect_string          OUT NOCOPY VARCHAR2,
            cFrom_string            OUT NOCOPY VARCHAR2,
            cWhere_string           OUT NOCOPY VARCHAR2) IS
   xProgress		VARCHAR2(30);

Line: 32


   cSelect_stmt		VARCHAR2(32000) := 'SELECT ';

Line: 44


   EC_DEBUG.PUSH('ece_extract_utils_pub.select_clause');

Line: 85


       cSelect_stmt :=  cSelect_stmt || ' ' || cWord1 || nvl(p_source_tbl(i).base_column_Name,'NULL') || cWord2 || ',';

Line: 107


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

Line: 113


   if (debug_mode_on_select) then
      declare
         stmt_1		varchar2(2000) := substrb(RTRIM(cSelect_stmt, ','), 1, 2000);

Line: 116


         stmt_2		varchar2(2000) := substrb(RTRIM(cSelect_stmt, ','), 2001, 2000);

Line: 117


         stmt_3		varchar2(2000) := substrb(RTRIM(cSelect_stmt, ','), 4001, 2000);

Line: 124


         insert into ece_error (creation_date, run_id, line_id, text)
		values( sysdate, 76451, ece_error_s.nextval, stmt_1);

Line: 126


         insert into ece_error (creation_date, run_id, line_id, text)
                values( sysdate, 76451, ece_error_s.nextval, stmt_2);

Line: 128


         insert into ece_error (creation_date, run_id, line_id, text)
                values( sysdate, 76451, ece_error_s.nextval, stmt_3);

Line: 133


   EC_DEBUG.POP('ece_extract_utils_pub.select_clause');

Line: 136


             EC_DEBUG.PL(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','ECE_EXTRACT_UTILS_PUB.Select_Clause');

Line: 154


END select_clause;

Line: 175


PROCEDURE insert_into_interface_tbl(
			iRun_id			IN NUMBER,
			cTransaction_Type	IN VARCHAR2,
			cCommunication_Method	IN VARCHAR2,
			cInterface_Table	IN VARCHAR2,
			p_source_tbl		IN ece_flatfile_pvt.Interface_tbl_type,
			p_foreign_key		IN NUMBER
)
IS

   xProgress		VARCHAR2(30);

Line: 188


   cInsert_stmt		VARCHAR2(32000) := 'INSERT INTO ';

Line: 210


   c_Insert_cur		INTEGER;

Line: 223


   EC_DEBUG.PUSH('ece_extract_utils_pub.insert_into_interface_tbl');

Line: 231


   cInsert_stmt := cInsert_stmt || ' ' || cInterface_Table || '( ';

Line: 233


   EC_DEBUG.PL(3, 'cInsert_stmt: ',cInsert_stmt);

Line: 247


        cInsert_stmt := cInsert_stmt || ' ' ||
                         p_source_tbl(i).interface_column_Name || ',';

Line: 255


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

Line: 261


   cInsert_stmt := cInsert_stmt || cValue_stmt;

Line: 263


   EC_DEBUG.PL(3, 'cInsert_stmt: ',cInsert_stmt);

Line: 267


   c_Insert_cur := dbms_sql.open_cursor;

Line: 269


    EC_DEBUG.PL(3, 'c_Insert_cur: ',c_Insert_cur);

Line: 273


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

Line: 294


           dbms_sql.bind_variable(c_Insert_cur,'b'||i,iRun_id);

Line: 299


           dbms_sql.bind_variable(c_Insert_cur,'b'||i,p_foreign_key);

Line: 336


   dbms_sql.bind_variable(c_Insert_cur,'b'||i,d_date);

Line: 348


            dbms_sql.bind_variable(c_Insert_cur,'b'||i,n_number);

Line: 352


          dbms_sql.bind_variable(c_Insert_cur,'b'||i,cValue);

Line: 445


         cInsert_stmt := cInsert_stmt || ' ' ||
			 p_source_tbl(i).interface_column_Name || ',';

Line: 456


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

Line: 462


   cInsert_stmt := cInsert_stmt || cValue_stmt;

Line: 464


   EC_DEBUG.PL(3, 'cInsert_stmt: ',cInsert_stmt);

Line: 467


   c_Insert_cur := dbms_sql.open_cursor;

Line: 469


    EC_DEBUG.PL(3, 'c_Insert_cur: ',c_Insert_cur);

Line: 472


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

Line: 476


   dummy := dbms_sql.execute(c_Insert_cur);

Line: 480


   if (debug_mode_on_insert)
   then
      declare
         stmt_1		varchar2(2000) := substrb(cInsert_stmt, 1, 2000);

Line: 484


         stmt_2		varchar2(2000) := substrb(cInsert_stmt, 2001, 2000);

Line: 485


         stmt_3		varchar2(2000) := substrb(cInsert_stmt, 4001, 2000);

Line: 486


         stmt_4		varchar2(2000) := substrb(cInsert_stmt, 6001, 2000);

Line: 487


         stmt_5		varchar2(2000) := substrb(cInsert_stmt, 8001, 2000);

Line: 496


          insert into ece_error (run_id, line_id, text) values( 478, ece_error_s.nextval, stmt_1);

Line: 497


         insert into ece_error (run_id, line_id, text) values( 478, ece_error_s.nextval, stmt_2);

Line: 498


         insert into ece_error (run_id, line_id, text) values( 478, ece_error_s.nextval, stmt_3);

Line: 499


         insert into ece_error (run_id, line_id, text) values( 478, ece_error_s.nextval, stmt_4);

Line: 500


         insert into ece_error (run_id, line_id, text) values( 478, ece_error_s.nextval, stmt_5);

Line: 506


   dbms_sql.close_cursor(c_Insert_cur);

Line: 509


   EC_DEBUG.POP('ece_extract_utils_pub.insert_into_interface_tbl');

Line: 514


             EC_DEBUG.PL(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','ECE_EXTRACT_UTILS_PUB.Insert_into_Interface_tbl');

Line: 532


END insert_into_interface_tbl;

Line: 534


PROCEDURE insert_into_prod_interface(
			p_Interface_Table	IN VARCHAR2,
			p_Insert_cur		IN OUT NOCOPY INTEGER,
			p_apps_tbl		IN ece_flatfile_pvt.Interface_tbl_type)

IS
   xProgress		VARCHAR2(30);

Line: 543


   cInsert_stmt		VARCHAR2(32000) := 'INSERT INTO ';

Line: 546


   c_Insert_cur		INTEGER ;

Line: 554


   if p_Insert_cur = 0
   then
      xProgress := 'EXTUB-30-1020';

Line: 557


      p_Insert_cur := -911;

Line: 561


if p_Insert_cur < 0
then

   xProgress := 'EXTUB-30-1040';

Line: 565


   cInsert_stmt := cInsert_stmt || ' ' || p_Interface_Table || '(';

Line: 586


         cInsert_stmt := cInsert_stmt || ' ' || p_apps_tbl(i).base_column_name || ',';

Line: 596


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

Line: 602


   cInsert_stmt := cInsert_stmt || cValue_stmt;

Line: 605


   p_Insert_cur := dbms_sql.open_cursor;

Line: 611


         stmt_1		varchar2(2000) := substrb(cInsert_stmt, 1, 2000);

Line: 612


         stmt_2		varchar2(2000) := substrb(cInsert_stmt, 2001, 2000);

Line: 613


         stmt_3		varchar2(2000) := substrb(cInsert_stmt, 4001, 2000);

Line: 614


         stmt_4		varchar2(2000) := substrb(cInsert_stmt, 6001, 2000);

Line: 615


         stmt_5		varchar2(2000) := substrb(cInsert_stmt, 8001, 2000);

Line: 617


         insert into ece_error (run_id, line_id, text) values( 78, ece_error_s.nextval, stmt_1);

Line: 618


         insert into ece_error (run_id, line_id, text) values( 78, ece_error_s.nextval, stmt_2);

Line: 619


         insert into ece_error (run_id, line_id, text) values( 78, ece_error_s.nextval, stmt_3);

Line: 620


         insert into ece_error (run_id, line_id, text) values( 78, ece_error_s.nextval, stmt_4);

Line: 621


         insert into ece_error (run_id, line_id, text) values( 78, ece_error_s.nextval, stmt_5);

Line: 628


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

Line: 633


if p_Insert_cur > 0
then

  begin
   xProgress := 'EXTUB-30-1150';

Line: 661


            dbms_sql.bind_variable(p_Insert_cur,
				'b'||k,
				d_date);

Line: 667


               insert into ece_error (run_id, line_id, text) values
                             ( 88, ece_error_s.nextval, 'b' ||k|| ' = '||d_date);

Line: 682


            dbms_sql.bind_variable(p_Insert_cur,
				'b'||k,
				n_number);

Line: 687


               insert into ece_error (run_id, line_id, text) values
                    ( 88, ece_error_s.nextval, 'b'||k|| ' ='||n_number);

Line: 692


            dbms_sql.bind_variable(p_Insert_cur,
				'b'||k,
				substrb(p_apps_tbl(k).value,
					1,
				p_apps_tbl(k).data_length));

Line: 699


               insert into ece_error (run_id, line_id, text) values
                   ( 88, ece_error_s.nextval, 'b'||k|| ' ='||p_apps_tbl(k).value);

Line: 709


   dummy := dbms_sql.execute(p_Insert_cur);

Line: 712


            EC_DEBUG.PL(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','ECE_EXTRACT_UTILS_PUB.insert_into_prod_interface');

Line: 730


END insert_into_prod_interface;

Line: 755


PROCEDURE insert_into_prod_interface_pvt(
            p_api_version_number IN       NUMBER,
            p_init_msg_list      IN       VARCHAR2 := fnd_api.g_false,
            p_simulate           IN       VARCHAR2 := fnd_api.g_false,
            p_commit             IN       VARCHAR2 := fnd_api.g_false,
            p_validation_level   IN       NUMBER   := fnd_api.g_valid_level_full,
            x_return_status      OUT NOCOPY      VARCHAR2,
            x_msg_count          OUT NOCOPY     NUMBER,
            x_msg_data           OUT NOCOPY     VARCHAR2,
            p_interface_table    IN       VARCHAR2,
            p_insert_cur         IN OUT NOCOPY  INTEGER,
            p_apps_tbl           IN       ece_flatfile_pvt.Interface_tbl_type) IS

   l_api_name           CONSTANT VARCHAR2(30)      := 'insert_into_prod_interface_pvt';

Line: 775


   cInsert_stmt                  VARCHAR2(32000)   := 'INSERT INTO ';

Line: 778


   c_Insert_cur                  INTEGER ;

Line: 785


   EC_DEBUG.PUSH('ece_extract_utils_pub.insert_into_prod_interface_pvt');

Line: 793


   EC_DEBUG.PL(3, 'p_insert_cur: ',p_insert_cur);

Line: 796


      SAVEPOINT insert_into_prod_interface_pvt;

Line: 817


      IF p_insert_cur = 0 THEN
         xProgress := 'EXTUB-30-1020';

Line: 819


         p_insert_cur := -911;

Line: 823


      IF p_insert_cur < 0 THEN
         xProgress := 'EXTUB-30-1040';

Line: 825


         cInsert_stmt := cInsert_stmt || ' ' || p_Interface_Table || '(';

Line: 827


EC_DEBUG.PL(3, 'cInsert_stmt: ',cInsert_stmt);

Line: 844


               cInsert_stmt := cInsert_stmt || ' ' || p_apps_tbl(i).base_column_name || ',';

Line: 846


EC_DEBUG.PL(3, 'cInsert_stmt: ',cInsert_stmt);

Line: 857


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

Line: 859


EC_DEBUG.PL(3, 'cInsert_stmt: ',cInsert_stmt);

Line: 867


         cInsert_stmt := cInsert_stmt || cValue_stmt;

Line: 869


EC_DEBUG.PL(3, 'cInsert_stmt: ',cInsert_stmt);

Line: 872


         p_Insert_cur := dbms_sql.open_cursor;

Line: 874


EC_DEBUG.PL(3, 'p_Insert_cur: ',p_Insert_cur);

Line: 879


               stmt_1      VARCHAR2(2000) := SUBSTR(cInsert_stmt,1,   2000);

Line: 880


               stmt_2      VARCHAR2(2000) := SUBSTR(cInsert_stmt,2001,2000);

Line: 881


               stmt_3      VARCHAR2(2000) := SUBSTR(cInsert_stmt,4001,2000);

Line: 882


               stmt_4      VARCHAR2(2000) := SUBSTR(cInsert_stmt,6001,2000);

Line: 883


               stmt_5      VARCHAR2(2000) := SUBSTR(cInsert_stmt,8001,2000);

Line: 893


               INSERT INTO ece_error(run_id,line_id,text) VALUES(78,ece_error_s.NEXTVAL,stmt_1);

Line: 894


               INSERT INTO ece_error(run_id,line_id,text) VALUES(78,ece_error_s.NEXTVAL,stmt_2);

Line: 895


               INSERT INTO ece_error(run_id,line_id,text) VALUES(78,ece_error_s.NEXTVAL,stmt_3);

Line: 896


               INSERT INTO ece_error(run_id,line_id,text) VALUES(78,ece_error_s.NEXTVAL,stmt_4);

Line: 897


               INSERT INTO ece_error(run_id,line_id,text) VALUES(78,ece_error_s.NEXTVAL,stmt_5);

Line: 905


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

Line: 909


               ROLLBACK TO insert_into_prod_interface_pvt;

Line: 912


                  cInsert_stmt);

Line: 921


      IF p_Insert_cur > 0 THEN
         BEGIN
            xProgress := 'EXTUB-30-1150';

Line: 947


                     dbms_sql.bind_variable(p_Insert_cur,'b'|| k,d_date);

Line: 950


                        INSERT INTO ece_error(run_id,line_id,text) VALUES(88,ece_error_s.NEXTVAL,'b' || k || ' = ' || d_date);

Line: 966


                     dbms_sql.bind_variable(p_Insert_cur,'b' || k,n_number);

Line: 969


                        INSERT INTO ece_error(run_id,line_id,text) VALUES(88,ece_error_s.NEXTVAL,'b' || k || ' =' || n_number);

Line: 973


                     dbms_sql.bind_variable(p_Insert_cur,'b' || k,SUBSTR(p_apps_tbl(k).value,1,p_apps_tbl(k).data_length));

Line: 976


                        INSERT INTO ece_error(run_id,line_id,text) VALUES(88,ece_error_s.NEXTVAL,'b' || k || ' =' || p_apps_tbl(k).value);

Line: 985


            dummy := dbms_sql.execute(p_Insert_cur);

Line: 989


              EC_DEBUG.PL(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','ECE_EXTRACT_UTILS_PUB.insert_into_prod_interface_pvt');

Line: 1022


         ROLLBACK TO insert_into_prod_interface_pvt;

Line: 1031


   EC_DEBUG.POP('ece_extract_utils_pub.insert_into_prod_interface_pvt');

Line: 1035


         ROLLBACK TO insert_into_prod_interface_pvt;

Line: 1042


         ROLLBACK TO insert_into_prod_interface_pvt;

Line: 1052


         ROLLBACK TO insert_into_prod_interface_pvt;

Line: 1066


   END insert_into_prod_interface_pvt;

Line: 1205


PROCEDURE ext_insert_value(
        l_plsql_tbl       IN OUT NOCOPY  ece_flatfile_pvt.Interface_tbl_type,
        p_position        IN     number,
        p_value           IN     varchar2)
IS
BEGIN
	 if EC_DEBUG.G_debug_level >= 2 then
           ec_debug.push('ECE_EXTRACT_UTILS_PUB.EXT_INSERT_VALUE');

Line: 1219


          ec_debug.pop('ECE_EXTRACT_UTILS_PUB.EXT_INSERT_VALUE');

Line: 1224


            ec_debug.pl(0,'EC','ECE_PROGRAM_ERROR','PROGRESS_LEVEL','ECE_EXTRACT_UTILS_PUB.EXT_INSERT_VALUE');

Line: 1228


end ext_insert_value;