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

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

Line: 19


FUNCTION search_delimiter_select ( p_input_str varchar2, p_start number)
  RETURN  number IS
     l_position_min		NUMBER ;

Line: 115


         IF sql_type = 'SELECT'
         THEN
            l_next := search_delimiter_select(p_input_str, l_next_prev+1 );

Line: 183


	SELECT  user
	INTO    x_userid
	FROM    sys.dual;

Line: 251


	SELECT  COUNT(*)
	INTO   	dummy
	FROM   	cn_objects
	WHERE  	name = P_NAME
	AND    	object_type = P_OBJECT_TYPE
        AND     org_id = p_org_id;   -- MOAC Change

Line: 262


		cn_objects_pkg.insert_row(
                        x_org_id                    => p_org_id,  -- MOAC Change
			x_rowid                     => x_rowid,
			x_object_id                 => p_object_id,
			x_dependency_map_complete   => 'n',
			x_name                      => p_name,
			x_description               => null,
			x_object_type               => p_object_type,
			x_repository_id             => p_repository_id,
			x_next_synchronization_date => NULL,
			x_synchronization_frequency => NULL,
			x_object_status             => 'a',
			x_object_value              => NULL );

Line: 278


		SELECT  object_id
      INTO    P_OBJECT_ID
		FROM    cn_objects
		WHERE   name        = P_NAME
		AND     object_type = P_OBJECT_TYPE
                AND     org_id      = p_org_id;  -- MOAC Change

Line: 348


	-- Delete module source code from cn_source
	-- Delete module object dependencies for this module
	cn_utils.delete_module(module_id, package_spec_id, package_body_id, p_org_id); -- MOAC Change

Line: 396


	SELECT	object_id
	INTO 		l_pkg_object_id
	FROM 		cn_objects
	WHERE 	UPPER(name) =  UPPER(p_object_name)
	AND 	object_type = 'PKS'
        AND     org_id = p_org_id;  -- MOAC Change

Line: 405


	SELECT	text BULK COLLECT
	INTO 	 	l_sqlstring
	FROM 	 	cn_source
	WHERE 	object_id = l_pkg_object_id
        AND     org_id = p_org_id -- MOAC Change
	ORDER BY source_id;

Line: 430


	SELECT	object_id
	INTO 		l_pkg_object_id
	FROM 		cn_objects
	WHERE 	UPPER(name) =  UPPER(p_object_name)
	AND 		object_type = 'PKB'
        AND     org_id = p_org_id; -- MOAC Change

Line: 439


	SELECT	text BULK COLLECT
	INTO 	 	l_sqlstring
	FROM 	 	cn_source
	WHERE 	object_id = l_pkg_object_id
        AND     org_id = p_org_id  -- MOAC Change
	ORDER BY source_id;

Line: 464


	SELECT  COUNT(ROWNUM)
	INTO    l_error_count
	FROM    user_errors
	WHERE   name = p_object_name
	AND     type IN ('PACKAGE', 'PACKAGE BODY');

Line: 594


	SELECT 	sca_rule_attribute_id,
			 	LOWER(src_column_name) src_column_name,
			 	LOWER(user_column_name) user_column_name,
			 	LOWER(trx_src_column_name) trx_src_column_name
	FROM   	cn_sca_rule_attributes
	WHERE  	transaction_source = 'CN'
        AND     org_id = p_org_id  -- MOAC Change
	ORDER BY src_column_name;

Line: 621


	SELECT  repository_id, org_id
	INTO    l_repository_id, l_org_id
	FROM    cn_repositories
       WHERE    org_id = p_org_id;   -- MOAC Change

Line: 628


	SELECT  module_id
	INTO    l_module_id
	FROM    cn_modules
	WHERE   NAME = 'Collection'
          AND   org_id = p_org_id;  -- MOAC Change

Line: 636


	SELECT  USER
	INTO    l_user
	FROM    dual;

Line: 809


	cn_utils.appendcr(l_body_code,'  SELECT	user_name');

Line: 812


	cn_utils.appendcr(l_body_code,'	 WHERE 	user_id  = (SELECT	created_by');

Line: 862


	cn_utils.appendcr(l_body_code,'			SELECT	start_id,   end_id, type');

Line: 906


	-- Procedure check_update_revenue_error Updates cn_comm_lines_api's Adjust Status to SCA_REVENUE_ERROR
	-- for all the orders / invoices that are submitted to SCA Engine and having invalid REVENUE_TYPE
	------++
cn_utils.appendcr(l_body_code,'		PROCEDURE check_reset_error_normal(');

Line: 927


	cn_utils.appendcr(l_body_code,'			l_api_name				CONSTANT VARCHAR2(30)	:= ''check_update_revenue_error'';');

Line: 933


	cn_utils.appendcr(l_body_code,'			SELECT	comm_lines_api_id');

Line: 948


	cn_utils.appendcr(l_body_code,'			SELECT	comm_lines_api_id');

Line: 999


	cn_utils.appendcr(l_body_code,'					UPDATE	cn_comm_lines_api');

Line: 1004


	cn_utils.appendcr(l_body_code,'							last_updated_by  = l_user_id,');

Line: 1005


	cn_utils.appendcr(l_body_code,'							last_update_date = SYSDATE');

Line: 1008


	cn_utils.appendcr(l_body_code,'					debugmsg(SQL%ROWCOUNT||'' Errored Records Of Type ORDER Are Updated To Process By SCA'');');

Line: 1025


	cn_utils.appendcr(l_body_code,'					UPDATE	cn_comm_lines_api');

Line: 1030


	cn_utils.appendcr(l_body_code,'							last_updated_by  = l_user_id,');

Line: 1031


	cn_utils.appendcr(l_body_code,'							last_update_date = SYSDATE');

Line: 1034


	cn_utils.appendcr(l_body_code,'					debugmsg(SQL%ROWCOUNT||'' Errored Records Of Type INVOICE Are Updated To Process By SCA'');');

Line: 1060


	cn_utils.appendcr(l_body_code,'		PROCEDURE check_update_revenue_error(');

Line: 1077


	cn_utils.appendcr(l_body_code,'			l_api_name				CONSTANT VARCHAR2(30)	:= ''check_update_revenue_error'';');

Line: 1082


	cn_utils.appendcr(l_body_code,'			SELECT  comm_lines_api_id');

Line: 1084


	cn_utils.appendcr(l_body_code,'				(SELECT  order_number, line_number, comm_lines_api_id');

Line: 1098


	cn_utils.appendcr(l_body_code,'				 		 (SELECT	1');

Line: 1114


	cn_utils.appendcr(l_body_code,'			SELECT  comm_lines_api_id');

Line: 1116


	cn_utils.appendcr(l_body_code,'				(SELECT  invoice_number, line_number, comm_lines_api_id');

Line: 1129


	cn_utils.appendcr(l_body_code,'				 		 (SELECT	1');

Line: 1148


	cn_utils.appendcr(l_body_code,'			SAVEPOINT check_update_revenue_error;');

Line: 1179


	cn_utils.appendcr(l_body_code,'						UPDATE	cn_comm_lines_api');

Line: 1181


	cn_utils.appendcr(l_body_code,'								last_updated_by  = l_user_id,');

Line: 1182


	cn_utils.appendcr(l_body_code,'								last_update_date = SYSDATE');

Line: 1188


	cn_utils.appendcr(l_body_code,'							debugmsg(SQL%ROWCOUNT||''Records Of Type ORDER Are Updated With Error SCA_REVENUE_ERROR'');');

Line: 1208


	cn_utils.appendcr(l_body_code,'						UPDATE	cn_comm_lines_api');

Line: 1210


	cn_utils.appendcr(l_body_code,'								last_updated_by  = l_user_id,');

Line: 1211


	cn_utils.appendcr(l_body_code,'								last_update_date = SYSDATE');

Line: 1217


	cn_utils.appendcr(l_body_code,'							debugmsg(SQL%ROWCOUNT||''Records Of Type INVOICE Are Updated With Error SCA_REVENUE_ERROR'');');

Line: 1231


	cn_utils.appendcr(l_body_code,'					ROLLBACK TO check_update_revenue_error;');

Line: 1233


	cn_utils.appendcr(l_body_code,'					debugmsg(''Unexpected Error In Procedure CHECK_UPDATE_REVENUE_ERROR''||SQLERRM);');

Line: 1239


	cn_utils.appendcr(l_body_code,'					ROLLBACK TO check_update_revenue_error;');

Line: 1240


	cn_utils.appendcr(l_body_code,'					debugmsg(''Error In Procedure CHECK_UPDATE_REVENUE_ERROR''||SQLERRM);');

Line: 1245


	cn_utils.appendcr(l_body_code,'			END check_update_revenue_error;');

Line: 1248


	-- Procedure check_update_role_error Updates cn_comm_lines_api's Adjust Status to SCA_ROLE_ERROR
	-- for all the orders / invoices that are submitted to SCA Engine and not having valid ROLE_ID
	------++

	cn_utils.appendcr(l_body_code,'		PROCEDURE check_update_role_error(');

Line: 1270


	cn_utils.appendcr(l_body_code,'				l_api_name				CONSTANT VARCHAR2(30)	:= ''check_update_role_error'';');

Line: 1297


	cn_utils.appendcr(l_body_code,'				SAVEPOINT	check_update_role_error;');

Line: 1315


	cn_utils.appendcr(l_body_code,'         		SELECT  ccla.comm_lines_api_id,');

Line: 1335


   cn_utils.appendcr(l_body_code,'         			(SELECT 1 ');

Line: 1345


	cn_utils.appendcr(l_body_code,'						UPDATE 	cn_comm_lines_api');

Line: 1347


	cn_utils.appendcr(l_body_code,'								last_updated_by  = l_user_id,');

Line: 1348


	cn_utils.appendcr(l_body_code,'								last_update_date = SYSDATE');

Line: 1351


	cn_utils.appendcr(l_body_code,'						debugmsg(SQL%ROWCOUNT||''Record(s) of Type ORDER Updated To SCA_ROLE_ERROR In CN_COMM_LINES_API'');');

Line: 1360


	cn_utils.appendcr(l_body_code,'         		SELECT  ccla.comm_lines_api_id,');

Line: 1379


   cn_utils.appendcr(l_body_code,'         			(SELECT 1 ');

Line: 1389


	cn_utils.appendcr(l_body_code,'						UPDATE 	cn_comm_lines_api');

Line: 1391


	cn_utils.appendcr(l_body_code,'									last_updated_by  = l_user_id,');

Line: 1392


	cn_utils.appendcr(l_body_code,'									last_update_date = SYSDATE');

Line: 1395


	cn_utils.appendcr(l_body_code,'			        	debugmsg(SQL%ROWCOUNT||''Record(s) of Type INVOICE Updated To SCA_ROLE_ERROR In CN_COMM_LINES_API'');');

Line: 1405


	cn_utils.appendcr(l_body_code,'				ROLLBACK TO check_update_role_error;');

Line: 1407


	cn_utils.appendcr(l_body_code,'				debugmsg(''Unexpected Error In Procedure CHECK_UPDATE_ROLE_ERROR''||SQLERRM);');

Line: 1413


	cn_utils.appendcr(l_body_code,'			    	ROLLBACK TO check_update_role_error;');

Line: 1415


	cn_utils.appendcr(l_body_code,'					debugmsg(''Unhandled Error In Procedure CHECK_UPDATE_ROLE_ERROR''||SQLERRM);');

Line: 1419


	cn_utils.appendcr(l_body_code,'		END check_update_role_error;');

Line: 1421


	-- Procedure check_update_resource_error Updates cn_comm_lines_api's Adjust Status to SCA_SRP_ERROR
	-- for all the orders / invoices that are submitted to SCA Engine and not having valid SALESREP_ID
	------++
	cn_utils.appendcr(l_body_code,'		PROCEDURE check_update_resource_error(');

Line: 1442


	cn_utils.appendcr(l_body_code,'			l_api_name	CONSTANT VARCHAR2(50)	:=	''check_update_resource_error'';');

Line: 1468


	cn_utils.appendcr(l_body_code,'			SAVEPOINT check_update_resource_error;');

Line: 1487


	cn_utils.appendcr(l_body_code,'				SELECT 	comm_lines_api_id,');

Line: 1495


	cn_utils.appendcr(l_body_code,'					(SELECT 	ccla.comm_lines_api_id');

Line: 1516


	cn_utils.appendcr(l_body_code,'						UPDATE   cn_comm_lines_api');

Line: 1518


	cn_utils.appendcr(l_body_code,'									last_updated_by  = l_user_id,');

Line: 1519


	cn_utils.appendcr(l_body_code,'									last_update_date = SYSDATE');

Line: 1522


	cn_utils.appendcr(l_body_code,'						debugmsg(SQL%ROWCOUNT||''Record(s) of Type ORDER Updated To SCA_SRP_ERROR In CN_COMM_LINES_API.'');');

Line: 1531


	cn_utils.appendcr(l_body_code,'		    	SELECT 	comm_lines_api_id,');

Line: 1539


	cn_utils.appendcr(l_body_code,'					(SELECT ccla.comm_lines_api_id');

Line: 1559


	cn_utils.appendcr(l_body_code,'						UPDATE  	cn_comm_lines_api ');

Line: 1561


	cn_utils.appendcr(l_body_code,'									last_updated_by  = l_user_id,');

Line: 1562


	cn_utils.appendcr(l_body_code,'									last_update_date = SYSDATE');

Line: 1565


	cn_utils.appendcr(l_body_code,'						debugmsg(SQL%ROWCOUNT||''Record(s) of Type INVOICE Updated To SCA_SRP_ERROR In CN_COMM_LINES_API'');');

Line: 1575


	cn_utils.appendcr(l_body_code,'				ROLLBACK TO check_update_resource_error;');

Line: 1577


	cn_utils.appendcr(l_body_code,'				debugmsg(''Unexpected Error In Procedure CHECK_UPDATE_RESOURCE_ERROR''||SQLERRM);');

Line: 1583


	cn_utils.appendcr(l_body_code,'		    	ROLLBACK TO check_update_resource_error;');

Line: 1584


	cn_utils.appendcr(l_body_code,'				debugmsg(''Error In Procedure CHECK_UPDATE_RESOURCE_ERROR''||SQLERRM);');

Line: 1589


	cn_utils.appendcr(l_body_code,'		END check_update_resource_error;');

Line: 1591


	-- Procedure check_update_distinct_error Updates cn_comm_lines_api's Adjust Status to SCA_DISTINCT_ERROR
	-- for all the orders / invoices that are submitted to SCA Engine and not having distinct attribute values across
	-- order / invoice lines
	------++
	cn_utils.appendcr(l_body_code,'		PROCEDURE check_update_distinct_error(');

Line: 1613


	cn_utils.appendcr(l_body_code,'			l_api_name	CONSTANT VARCHAR2(30)	:= ''check_update_distinct_error'';');

Line: 1615


	cn_utils.appendcr(l_body_code,'			SAVEPOINT	check_update_distinct_error;');

Line: 1635


	cn_utils.appendcr(l_body_code,'				UPDATE  cn_comm_lines_api');

Line: 1638


	cn_utils.appendcr(l_body_code,'				         (SELECT  comm_lines_api_id, trx_type');

Line: 1642


	cn_utils.appendcr(l_body_code,'				             (SELECT  ord_no, line_no');

Line: 1644


	cn_utils.appendcr(l_body_code,'				          	  (SELECT distinct order_number ord_no,line_number line_no,');

Line: 1646


	split_long_sql(l_body_code,l_trx_src_column_name(l_loop_cntr3),'SELECT');

Line: 1667


	cn_utils.appendcr(l_body_code,'				    	debugmsg(SQL%ROWCOUNT||''Records Of Type ORDER Are Updated With Error SCA_DISTINCT_ERROR'');');

Line: 1676


	cn_utils.appendcr(l_body_code,'				UPDATE  cn_comm_lines_api');

Line: 1679


	cn_utils.appendcr(l_body_code,'				        (SELECT  comm_lines_api_id, trx_type');

Line: 1683


	cn_utils.appendcr(l_body_code,'				          (SELECT  inv_no, line_no');

Line: 1685


	cn_utils.appendcr(l_body_code,'				    	      (SELECT distinct invoice_number inv_no,line_number line_no,');

Line: 1687


	split_long_sql(l_body_code,l_trx_src_column_name(l_loop_cntr3),'SELECT');

Line: 1707


	cn_utils.appendcr(l_body_code,'				    	debugmsg(SQL%ROWCOUNT||''Records Of Type INVOICE Are Updated With Error SCA_DISTINCT_ERROR'');');

Line: 1718


	cn_utils.appendcr(l_body_code,'				ROLLBACK TO check_update_distinct_error;');

Line: 1720


	cn_utils.appendcr(l_body_code,'				debugmsg(''Unexpected Error In Procedure CHECK_UPDATE_DISTINCT_ERROR''||SQLERRM);');

Line: 1726


	cn_utils.appendcr(l_body_code,'				ROLLBACK TO check_update_distinct_error;');

Line: 1727


	cn_utils.appendcr(l_body_code,'				debugmsg(''Error In Procedure CHECK_UPDATE_DISTINCT_ERROR''||SQLERRM); ');

Line: 1732


	cn_utils.appendcr(l_body_code,'		END check_update_distinct_error;');

Line: 1752


	cn_utils.appendcr(l_body_code,'-- To Store values to update WHO Columns');

Line: 1783


	cn_utils.appendcr(l_body_code,'	    -- Variable Declarations To Hold Values Of Rows Inserted into CN_SCA_HEADERS_INTERFACE,');

Line: 1784


	cn_utils.appendcr(l_body_code,'	    -- CN_SCA_LINES_INTERFACE And Rows Updated Into CN_COMM_LINES_API For Each ORDER and/or INVOICE');

Line: 1796


	cn_utils.appendcr(l_body_code,'		SELECT trx_amt, comm_lines_api_id, order_number, line_number from ');

Line: 1797


	cn_utils.appendcr(l_body_code,'		(SELECT SUM(transaction_amount) trx_amt,');

Line: 1816


   cn_utils.appendcr(l_body_code,'		    (SELECT 1 FROM cn_comm_lines_api');

Line: 1824


	cn_utils.appendcr(l_body_code,'		SELECT	cshi.sca_headers_interface_id,');

Line: 1852


	cn_utils.appendcr(l_body_code,'		SELECT  comm_lines_api_id ');

Line: 1866


	cn_utils.appendcr(l_body_code,'		       (SELECT 1 FROM cn_comm_lines_api');

Line: 1874


	cn_utils.appendcr(l_body_code,'		SELECT trx_amt, comm_lines_api_id, invoice_number, line_number FROM');

Line: 1875


	cn_utils.appendcr(l_body_code,'		(SELECT SUM(transaction_amount) trx_amt,');

Line: 1893


   cn_utils.appendcr(l_body_code,'		    (SELECT 1 FROM cn_comm_lines_api');

Line: 1901


	cn_utils.appendcr(l_body_code,'		SELECT	cshi.sca_headers_interface_id,');

Line: 1928


	cn_utils.appendcr(l_body_code,'		SELECT  comm_lines_api_id');

Line: 1942


	cn_utils.appendcr(l_body_code,'		       (SELECT 1 FROM cn_comm_lines_api');

Line: 2059


	cn_utils.appendcr(l_body_code,'		check_update_revenue_error(');

Line: 2081


	cn_utils.appendcr(l_body_code,'		check_update_role_error(');

Line: 2104


	cn_utils.appendcr(l_body_code,'		check_update_resource_error(');

Line: 2127


	cn_utils.appendcr(l_body_code,'		check_update_distinct_error(');

Line: 2168


 	cn_utils.appendcr(l_body_code,' 	         INSERT INTO cn_sca_headers_interface');

Line: 2174


	split_long_sql(l_body_code,l_src_column_name(l_loop_cntr3),'SELECT');

Line: 2177


	cn_utils.appendcr(l_body_code,' 		     SELECT');

Line: 2183


	split_long_sql(l_body_code,l_trx_src_column_name(l_loop_cntr3),'SELECT');

Line: 2209


	cn_utils.appendcr(l_body_code,' 			         INSERT INTO cn_sca_lines_interface');

Line: 2225


	cn_utils.appendcr(l_body_code,'	    		UPDATE cn_comm_lines_api');

Line: 2228


	cn_utils.appendcr(l_body_code,'					last_updated_by      = '||l_user_id||',');

Line: 2229


	cn_utils.appendcr(l_body_code,'					last_update_login    = '||l_login_id||',');

Line: 2230


	cn_utils.appendcr(l_body_code,'					last_update_date     = SYSDATE');

Line: 2258


	cn_utils.appendcr(l_body_code,' 	         INSERT INTO cn_sca_headers_interface');

Line: 2264


	split_long_sql(l_body_code,l_src_column_name(l_loop_cntr3),'SELECT');

Line: 2267


	cn_utils.appendcr(l_body_code,' 		     SELECT');

Line: 2273


	split_long_sql(l_body_code,l_trx_src_column_name(l_loop_cntr3),'SELECT');

Line: 2299


	cn_utils.appendcr(l_body_code,' 		         INSERT INTO cn_sca_lines_interface');

Line: 2315


	cn_utils.appendcr(l_body_code,'	    		UPDATE cn_comm_lines_api');

Line: 2318


	cn_utils.appendcr(l_body_code,'					last_updated_by      = '||l_user_id||',');

Line: 2319


	cn_utils.appendcr(l_body_code,'					last_update_login    = '||l_login_id||',');

Line: 2320


	cn_utils.appendcr(l_body_code,'					last_update_date     = SYSDATE');

Line: 2356


	cn_utils.appendcr(l_body_code,'		debugmsg(''RECORDS INSERTED INTO****|***TRANSACTION TYPE****|****#OF RECORDS'');');

Line: 2364


	cn_utils.appendcr(l_body_code,'		debugmsg(''RECORDS UPDATED IN CN_COMM_LINES_INTERFACE WITH SCA_PENDING STATUS'');');