ISM_Script_SourceSystem_Dim.sql

CREATE COLUMN TABLE "SAP_CONTENT#ARIBA_SPEND_ANALYSIS"."TEMP_SOURCESYSTEMDIM_FILES" (JOB_KEY varchar(20), JOB_FILE_NAME CLOB);										
CREATE COLUMN TABLE "SAP_CONTENT#ARIBA_SPEND_ANALYSIS"."TEMP_SOURCESYSTEMDIM_XML" (FILENAME varchar(20), FILECONTENT CLOB);										
										
----------------------------------------										
CREATE OR REPLACE PROCEDURE SAP_CONTENT#ARIBA_SPEND_ANALYSIS.PUT_TEMP_SOURCESYSTEMDIM_FILES (										
		IN JOB_KEY varchar(20),								
		IN JOB_FILE_NAME CLOB)								
	LANGUAGE SQLSCRIPT AS									
	BEGIN									
	DECLARE counter int;									
	DECLARE V_JFN_SOURCE CLOB;									
	DECLARE V_JFN_TARGET CLOB;									
										
	SELECT count(*) INTO counter FROM "SAP_CONTENT#ARIBA_SPEND_ANALYSIS"."TEMP_SOURCESYSTEMDIM_FILES";									
	IF :counter = 0 									
	THEN									
		INSERT INTO "SAP_CONTENT#ARIBA_SPEND_ANALYSIS"."TEMP_SOURCESYSTEMDIM_FILES" VALUES ( JOB_KEY, JOB_FILE_NAME);								
	ELSE									
		SELECT top 1 JOB_FILE_NAME INTO V_JFN_SOURCE FROM "SAP_CONTENT#ARIBA_SPEND_ANALYSIS"."TEMP_SOURCESYSTEMDIM_FILES";								
		V_JFN_TARGET  = CONCAT(:V_JFN_SOURCE, JOB_FILE_NAME);								
		Truncate table "SAP_CONTENT#ARIBA_SPEND_ANALYSIS"."TEMP_SOURCESYSTEMDIM_FILES";								
		INSERT INTO "SAP_CONTENT#ARIBA_SPEND_ANALYSIS"."TEMP_SOURCESYSTEMDIM_FILES" VALUES ( JOB_KEY, V_JFN_TARGET);								
	END IF;									
END;										
----------------------------------------										
CREATE OR REPLACE PROCEDURE SAP_CONTENT#ARIBA_SPEND_ANALYSIS.GET_TEMP_SOURCESYSTEMDIM_FILES ()										
	LANGUAGE SQLSCRIPT AS									
	BEGIN									
		SELECT "JOB_KEY","JOB_FILE_NAME" FROM "SAP_CONTENT#ARIBA_SPEND_ANALYSIS"."TEMP_SOURCESYSTEMDIM_FILES";								
END;										
----------------------------------------										
CREATE OR REPLACE PROCEDURE SAP_CONTENT#ARIBA_SPEND_ANALYSIS.TRUNC_TEMP_SOURCESYSTEMDIM_FILES ()										
	LANGUAGE SQLSCRIPT AS									
	BEGIN									
		Truncate Table "SAP_CONTENT#ARIBA_SPEND_ANALYSIS"."TEMP_SOURCESYSTEMDIM_FILES";								
END;										
----------------------------------------										
CREATE OR REPLACE PROCEDURE SAP_CONTENT#ARIBA_SPEND_ANALYSIS.PUT_TEMP_SOURCESYSTEMDIM_XML (										
		IN FILENAME varchar(20),								
		IN FILECONTENT CLOB)								
	LANGUAGE SQLSCRIPT AS									
	BEGIN									
		INSERT INTO "SAP_CONTENT#ARIBA_SPEND_ANALYSIS"."TEMP_SOURCESYSTEMDIM_XML" VALUES (								
				FILENAME,						
				FILECONTENT);						
										
		INSERT INTO "SAP_CONTENT#ARIBA_SPEND_ANALYSIS"."SourceSystemDimSystem" (								
			SELECT * FROM							
				XMLTABLE('resultset/row' PASSING						
				SAP_CONTENT#ARIBA_SPEND_ANALYSIS."TEMP_SOURCESYSTEMDIM_XML"."FILECONTENT"						
				COLUMNS						
					source_system_type		NVARCHAR(5000) 		PATH	'source_system_type',
					source_system_id		NVARCHAR(5000) 		PATH	'source_system_id',
					source_system		NVARCHAR(5000) 		PATH	'source_system'
			) as XTABLE  							
		);								
		Truncate Table "SAP_CONTENT#ARIBA_SPEND_ANALYSIS"."TEMP_SOURCESYSTEMDIM_XML"; 								
	END;									
---------------------------