Scribd
Upload
63 views14 pages

Data Load Verification Procedure

This document defines stored procedures and tables used to load and verify data in Oracle databases. It creates tables to store logs and summaries of data loads and interfaces. It also defines a procedure called SP_DATA_LOAD_VERIFY_DATA that is used to validate and load data according to rules defined in other interface tables, cleaning logs and targets before loading new data.

Uploaded by

hari prasad
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
63 views14 pages

Data Load Verification Procedure

This document defines stored procedures and tables used to load and verify data in Oracle databases. It creates tables to store logs and summaries of data loads and interfaces. It also defines a procedure called SP_DATA_LOAD_VERIFY_DATA that is used to validate and load data according to rules defined in other interface tables, cleaning logs and targets before loading new data.

Uploaded by

hari prasad
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 14

set define off;

create table SYS_LOAD_DATA_VERIFY_SUM( INTERFACE_ID VARCHAR2(150) ,


LINE_NO NUMBER(5) ,
ERROR_MESSAGE VARCHAR2(4000) ,
ERROR_QUERY VARCHAR2(4000) ,
COMMENTS VARCHAR2(4000) ,
CREATED_BY VARCHAR2(30) ,
CREATED_DATE DATE ,
CHANGED_BY VARCHAR2(30) ,
CHANGED_DATE DATE );

create table SYS_LOAD_INTERFACE_SUMMARY(


INTERFACE_ID VARCHAR2(150) ,
LINE_NO NUMBER(5) ,
SUMMARY_TYPE
VARCHAR2(4000) ,
SUMMARY_RESULTS
VARCHAR2(4000) ,
NOTES
VARCHAR2(4000) ,
CREATED_BY VARCHAR2(30)
,
CREATED_DATE DATE
,
CHANGED_BY VARCHAR2(30)
,
CHANGED_DATE DATE );

set define off;

create or replace PROCEDURE SP_DATA_LOAD_VERIFY_DATA(P_INTERFACE_ID VARCHAR2,


P_USER_ID VARCHAR2 DEFAULT 'DATA_LOAD')
IS
--PRAGMA AUTONOMOUS_TRANSACTION;
CURSOR C2 IS SELECT *
FROM SYS_LOAD_INTERFACE_RULES
WHERE INTERFACE_ID =P_INTERFACE_ID
AND NVL(ACTIVE_FLAG,'N')='Y'
ORDER BY FIELD_NAME,RULE_EXECUTION_SEQ;

CURSOR C3 IS SELECT *
FROM SYS_LOAD_INTERFACE_DETAIL
WHERE INTERFACE_ID=P_INTERFACE_ID
ORDER BY LINE_NO;

CURSOR C4 IS SELECT *
FROM SYS_LOAD_INTERFACE_POST_SQLS
WHERE INTERFACE_ID=P_INTERFACE_ID
ORDER BY LINE_NO;

CURSOR C5 IS SELECT *
FROM SYS_LOAD_INTERFACE_SQLS
WHERE INTERFACE_ID=P_INTERFACE_ID
ORDER BY LINE_NO;
CURSOR C6 IS SELECT A.DESTINATION_TABLE_NAME,
A.DESTINATION_FIELD_NAME ,
A.DESTINATION_TYPE ,
B.SOURCE_TABLE_NAME ,
A.SOURCE_FIELD_NAME ,
A.SOURCE_FIELD_NAME_FOR_CLEANUP,
NVL(A.NON_DATABASE_SOURCE_COLUMN,'N') AS
NON_DATABASE_SOURCE_COLUMN

FROM SYS_LOAD_INTERFACE_DETAIL A,
SYS_LOAD_INTERFACE_HEADER B
WHERE A.INTERFACE_ID=P_INTERFACE_ID
AND A.INTERFACE_ID = B.INTERFACE_ID
AND A.DESTINATION_TYPE IN ('N')
ORDER BY LINE_NO;

CURSOR C7 IS SELECT A.DESTINATION_TABLE_NAME,


A.DESTINATION_FIELD_NAME ,
A.DESTINATION_FIELD_VALUE,
A.DESTINATION_TYPE ,
B.SOURCE_TABLE_NAME ,
A.SOURCE_FIELD_NAME ,
A.SOURCE_FIELD_NAME_FOR_CLEANUP,
A.FORMAT_MODEL,
NVL(A.NON_DATABASE_SOURCE_COLUMN,'N') AS
NON_DATABASE_SOURCE_COLUMN,
A.FORMAT_MODEL_DEFAULT_VALUE
FROM SYS_LOAD_INTERFACE_DETAIL A,
SYS_LOAD_INTERFACE_HEADER B
WHERE A.INTERFACE_ID=P_INTERFACE_ID
AND A.INTERFACE_ID = B.INTERFACE_ID
AND A.DESTINATION_TYPE IN ('D')
ORDER BY LINE_NO;

CURSOR S1 IS
SELECT B.COLUMN_NAME ,A.*
FROM SYS_LOAD_INTERFACE_DETAIL A,
USER_TAB_COLUMNS B
WHERE INTERFACE_ID=P_INTERFACE_ID
AND A.DESTINATION_TABLE_NAME = B.TABLE_NAME(+)
AND A.DESTINATION_FIELD_NAME = B.COLUMN_NAME(+)
ORDER BY LINE_NO;

V_NAME SD_CUSTOMERS.NAME%TYPE;
V_LOAD_HEADER SYS_LOAD_INTERFACE_HEADER%ROWTYPE;
V_SNO SYS_LOAD_INTERFACE_LOG.SNO%TYPE;
V_DEST_DATA_TYPE USER_TAB_COLUMNS.DATA_TYPE%TYPE;

V_MAX_REC NUMBER(25,5);
V_CNT NUMBER(6);
V_CAPS NUMBER;
V_LINE_NO NUMBER;
V_SQL VARCHAR2(32000);
V_SELECT VARCHAR2(32000);
V_INSERT1 VARCHAR2(32000);
V_INSERT VARCHAR2(32000);
--V_SOURCE_COLUMN VARCHAR2(4000);
V_SOURCE_FIELD_NAME VARCHAR2(4000);
V_SOURCE_FIELD_NAME_TEMP VARCHAR2(4000);
V_SOURCE_FIELD_NAME_FORCLEANUP VARCHAR2(4000);
V_SOURCE_TABLE_NAME VARCHAR2(4000);
V_FLAG VARCHAR2(500);
V_NO_OF_RECORDS NUMBER;

TABLE_ERROR EXCEPTION;
OUT_ERRORS EXCEPTION;

V_ERRORS_FOUND NUMBER(10);
V_ERRORS_FOUND_TOTAL NUMBER(10);
V_SQL_STRING VARCHAR2(1000);
V_SQL_STRING_RESULT VARCHAR2(1000);
V_ERROR_LOGS VARCHAR2(2000);
V_DESTINATION_TABLE_NAME VARCHAR2(4000);
V_FIELD_IS_CAPS VARCHAR2(10);
V_DFLT_DESTINATION_FIELD_VALUE VARCHAR2(4000);
V_DFLT_FORMAT_MODEL VARCHAR2(4000);
V_NON_DATABASE_SOURCE_COLUMN VARCHAR2(1);
BEGIN

SELECT *
INTO V_LOAD_HEADER
FROM SYS_LOAD_INTERFACE_HEADER
WHERE INTERFACE_ID = P_INTERFACE_ID;

-----------------------------------------------------------------------------------
-------------------------
------------------------------------CLEAN UP THE LOGS/TARGET
TABLE------------------------------------------

-----------------------------------------------------------------------------------
-------------------------
BEGIN
DELETE FROM SYS_LOAD_INTERFACE_LOG_DETAIL WHERE SNO IN (SELECT SNO FROM
SYS_LOAD_DATA_VERIFY_SUM WHERE INTERFACE_ID = P_INTERFACE_ID);
DELETE FROM SYS_LOAD_DATA_VERIFY_SUM WHERE INTERFACE_ID =
P_INTERFACE_ID;

EXCEPTION
WHEN OTHERS THEN NULL;
END;

V_FLAG := '1';
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE '||V_LOAD_HEADER.DESTINATION_TABLE_NAME;

EXCEPTION
WHEN OTHERS THEN
EXECUTE IMMEDIATE 'delete from '||V_LOAD_HEADER.DESTINATION_TABLE_NAME;
COMMIT;
END;

V_FLAG := '2';
DECLARE
V_AVL NUMBER;
BEGIN
SELECT COUNT(*) INTO V_AVL FROM TAB WHERE TNAME =
V_LOAD_HEADER.SOURCE_TABLE_NAME;
IF V_AVL = 0 THEN
RAISE TABLE_ERROR;
END IF;
EXCEPTION
WHEN OTHERS THEN
RAISE TABLE_ERROR;

END;
-----------------------------------------------------------------------------------
-------------------------
---------------------------------------SETUP CHECKS
--------------------------------------------------
-----------------------------------------------------------------------------------
-------------------------
-- 1
FOR X IN S1 LOOP
IF X.COLUMN_NAME IS NULL THEN
PKG_DATA_LOAD.SP_INSERT_ERROR_LOG_SUM(P_INTERFACE_ID,
'Destination Field Name : <font color= "Red"><B>'||
X.DESTINATION_FIELD_NAME||'</B></font> not Found',
V_CNT,
V_SQL);
V_ERRORS_FOUND := NVL(V_ERRORS_FOUND,0)+1;
END IF;

END LOOP;

IF V_ERRORS_FOUND >0 THEN


V_ERRORS_FOUND_TOTAL := NVL(V_ERRORS_FOUND_TOTAL,0) + V_ERRORS_FOUND;
IF V_ERROR_LOGS IS NULL THEN V_ERROR_LOGS :='Data load Setup incorrect wrt
destination columns';
ELSE V_ERROR_LOGS:= V_ERROR_LOGS ||',Data
load Setup incorrect wrt Destination Field Names';
END IF;
END IF;
V_ERRORS_FOUND :=0;
V_SOURCE_FIELD_NAME_FORCLEANUP:= NULL;
-----------------------------------------------------------------------------------
-------------------------
---------------------------------------VALIDATIONS CHECKS
--------------------------------------------------
-----------------------------------------------------------------------------------
-------------------------
ZZZ_TEST('SP_VERIFY_LOAD C2 CURSOR Validation LOOP STARTS : ');
V_SOURCE_FIELD_NAME_FORCLEANUP:= NULL;
FOR J IN C2 LOOP
BEGIN
V_CNT:= 0;
BEGIN
SELECT SOURCE_FIELD_NAME,
SOURCE_TABLE_NAME,
DESTINATION_TABLE_NAME
INTO V_SOURCE_FIELD_NAME,
V_SOURCE_TABLE_NAME,
V_DESTINATION_TABLE_NAME
FROM SYS_LOAD_INTERFACE_DETAIL
WHERE INTERFACE_ID =J.INTERFACE_ID
AND DESTINATION_FIELD_NAME=J.FIELD_NAME;
EXCEPTION
WHEN NO_DATA_FOUND THEN
V_SOURCE_FIELD_NAME := NULL;
END;
BEGIN
SELECT 'Y'
INTO V_FIELD_IS_CAPS
FROM SYS_LOAD_INTERFACE_RULES
WHERE INTERFACE_ID =J.INTERFACE_ID
AND NVL(ACTIVE_FLAG,'N')='Y'
AND RULE_CATEGORY='Validation' AND RULE_TYPE='Field is caps'

AND FIELD_NAME =J.FIELD_NAME;


EXCEPTION
WHEN OTHERS THEN NULL;
V_FIELD_IS_CAPS:='N';
END;

IF J.RULE_CATEGORY='Validation' AND J.RULE_TYPE='Check Field


Validation' THEN
IF V_SOURCE_FIELD_NAME IS NOT NULL THEN
IF J.REFERENCE3 IS NOT NULL THEN
IF V_LOAD_HEADER.WHERE_CONDITION IS NOT NULL THEN
V_SQL := 'SELECT COUNT(1) FROM '||
V_LOAD_HEADER.SOURCE_USER_ID||'.'||V_LOAD_HEADER.SOURCE_TABLE_NAME||' WHERE '||
V_LOAD_HEADER.WHERE_CONDITION||' AND '
|| V_SOURCE_FIELD_NAME||' IS NOT NULL ' || ' AND '
|| CASE WHEN V_FIELD_IS_CAPS ='Y' THEN ' UPPER('||
V_SOURCE_FIELD_NAME||')' ELSE V_SOURCE_FIELD_NAME END ||' NOT IN (SELECT '||
J.REFERENCE2||' FROM '||J.REFERENCE1||' WHERE '||
REPLACE(REPLACE(REPLACE ( UPPER(J.REFERENCE3 ),'" &
','' ),'.TEXT & "',''),V_DESTINATION_TABLE_NAME||'_','')||')';

ELSE
V_SQL := 'SELECT COUNT(1) FROM '||
V_LOAD_HEADER.SOURCE_USER_ID||'.'||V_LOAD_HEADER.SOURCE_TABLE_NAME||' WHERE '||
V_SOURCE_FIELD_NAME
||' IS NOT NULL ' ||' AND '
|| CASE WHEN V_FIELD_IS_CAPS ='Y' THEN ' UPPER('||
V_SOURCE_FIELD_NAME||')' ELSE V_SOURCE_FIELD_NAME END ||' NOT IN (SELECT '||
J.REFERENCE2||' FROM '||J.REFERENCE1||' WHERE '||
REPLACE(REPLACE(REPLACE ( UPPER(J.REFERENCE3 ),'" &
','' ),'.TEXT & "',''),V_DESTINATION_TABLE_NAME||'_','')||')';
END IF;
ELSE
IF V_LOAD_HEADER.WHERE_CONDITION IS NOT NULL THEN
V_SQL := 'SELECT COUNT(1) FROM '||
V_LOAD_HEADER.SOURCE_USER_ID||'.'||V_LOAD_HEADER.SOURCE_TABLE_NAME||' WHERE '||
V_LOAD_HEADER.WHERE_CONDITION||' AND '||V_SOURCE_FIELD_NAME||' IS NOT NULL '
|| ' AND '|| CASE WHEN V_FIELD_IS_CAPS ='Y' THEN '
UPPER('||V_SOURCE_FIELD_NAME||')' ELSE V_SOURCE_FIELD_NAME END ||' NOT IN (SELECT
'||J.REFERENCE2||' FROM '||J.REFERENCE1||')';
ELSE
V_SQL := 'SELECT COUNT(1) FROM '||
V_LOAD_HEADER.SOURCE_USER_ID||'.'||V_LOAD_HEADER.SOURCE_TABLE_NAME||' WHERE '||
V_SOURCE_FIELD_NAME ||' IS NOT NULL '
||' AND ' || CASE WHEN V_FIELD_IS_CAPS ='Y' THEN '
UPPER('||V_SOURCE_FIELD_NAME||')' ELSE V_SOURCE_FIELD_NAME END ||' NOT IN (SELECT
'||J.REFERENCE2||' FROM '||J.REFERENCE1||')';
END IF;
END IF;

ZZZ_TEST('SP_VERIFY_LOAD C2 Validation Loop :V_SQL '||


P_INTERFACE_ID||' -'||V_FIELD_IS_CAPS||';'||V_SQL);
EXECUTE IMMEDIATE V_SQL INTO V_CNT;
END IF;

IF V_CNT > 0 THEN


PKG_DATA_LOAD.SP_INSERT_ERROR_LOG_SUM(P_INTERFACE_ID,
'Validation failed for field <font color=
"Red"><B>'|| J.FIELD_NAME||'</B></font>',
V_CNT,V_SQL);
V_ERRORS_FOUND := NVL(V_ERRORS_FOUND,0)+1;

COMMIT;
END IF;
END IF;--VALIDATION RULE END

EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL ; ZZZ_TEST('SP_VERIFY_LOAD C2 CURSOR Validation
NDF :'||P_INTERFACE_ID||' -'||SQLERRM ||
DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
WHEN OTHERS THEN
NULL ; ZZZ_TEST('SP_VERIFY_LOAD C2 CURSOR Validation
OTH :'||P_INTERFACE_ID||' -'||SQLERRM ||
DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
END;
END LOOP;

IF V_ERRORS_FOUND >0 THEN


V_ERRORS_FOUND_TOTAL := NVL(V_ERRORS_FOUND_TOTAL,0) + V_ERRORS_FOUND;
IF V_ERROR_LOGS IS NULL THEN V_ERROR_LOGS :='Errors encountered for
Validations';
ELSE V_ERROR_LOGS:= V_ERROR_LOGS
||',Validations';
END IF;
END IF;
V_ERRORS_FOUND :=0;
V_SOURCE_FIELD_NAME_FORCLEANUP:= NULL;
-----------------------------------------------------------------------------------
-------------------------
---------------------------------------MANDATORY CHECKS
----------------------------------------------------
-----------------------------------------------------------------------------------
-------------------------

ZZZ_TEST('SP_VERIFY_LOAD C2 CURSOR Mandatory LOOP STARTS : ');

FOR J IN C2 LOOP
DECLARE
V_SOURCE_FIELD_NAME VARCHAR2(1000);
V_DESTINATION_FIELD_NAME VARCHAR2(1000);
V_DESTINATION_FIELD_VALUE VARCHAR2(1000);
BEGIN
V_CNT:= 0;
V_SQL:=NULL;

IF J.RULE_CATEGORY='Mandatory' AND J.RULE_TYPE='Mandatory' THEN


V_FLAG:= 'Begining Checks for Field Name: ' ||J.FIELD_NAME;
SELECT SOURCE_FIELD_NAME ,
SOURCE_FIELD_NAME_FOR_CLEANUP ,
DESTINATION_FIELD_NAME ,
DESTINATION_FIELD_VALUE ,
NON_DATABASE_SOURCE_COLUMN
INTO V_SOURCE_FIELD_NAME ,
V_SOURCE_FIELD_NAME_FORCLEANUP,
V_DESTINATION_FIELD_NAME ,
V_DESTINATION_FIELD_VALUE ,
V_NON_DATABASE_SOURCE_COLUMN
FROM SYS_LOAD_INTERFACE_DETAIL
WHERE INTERFACE_ID = P_INTERFACE_ID
AND DESTINATION_FIELD_NAME = J.FIELD_NAME;

IF V_SOURCE_FIELD_NAME_FORCLEANUP IS NOT NULL THEN


V_SOURCE_FIELD_NAME_TEMP:= REPLACE
(V_SOURCE_FIELD_NAME_FORCLEANUP,V_SOURCE_FIELD_NAME,'A.'||V_SOURCE_FIELD_NAME);

ELSE
V_SOURCE_FIELD_NAME_TEMP:= 'A.'||V_SOURCE_FIELD_NAME;
END IF;

if V_NON_DATABASE_SOURCE_COLUMN ='Y' then


IF V_SOURCE_FIELD_NAME_FORCLEANUP IS NOT NULL THEN
V_SOURCE_FIELD_NAME_TEMP:= V_SOURCE_FIELD_NAME_FORCLEANUP;
ELSE
V_SOURCE_FIELD_NAME_TEMP:= 'NULL';
END IF;
end if;

V_SQL := 'SELECT count(1) FROM '||V_LOAD_HEADER.SOURCE_USER_ID||'.'||


V_LOAD_HEADER.SOURCE_TABLE_NAME
||' A ,SYS_LOAD_INTERFACE_DETAIL b WHERE B.INTERFACE_ID ='||
CHR(39)||J.INTERFACE_ID||CHR(39)||
' AND nvl('||
V_SOURCE_FIELD_NAME_TEMP||',NVL2(B.DESTINATION_FIELD_VALUE,CHR(39)||
B.DESTINATION_FIELD_VALUE||CHR(39),NULL)) IS NULL '||
' AND B.DESTINATION_FIELD_NAME = '||CHR(39)||J.FIELD_NAME||CHR(39);

ZZZ_TEST('SP_VERIFY_LOAD C2 Mandatory lOOP :V_SQL '||


P_INTERFACE_ID||' -'||V_SQL);
EXECUTE IMMEDIATE V_SQL INTO V_CNT;

IF V_CNT > 0 THEN


PKG_DATA_LOAD.SP_INSERT_ERROR_LOG_SUM(P_INTERFACE_ID,'Field value
cannot be null for <font color= "Red"><B>'|| J.FIELD_NAME ||'</B></font> / Default
values not set',
V_CNT,V_SQL);
V_ERRORS_FOUND := NVL(V_ERRORS_FOUND,0)+1;
-- ZZZ_TEST('SP_VERIFY_LOAD C2 Mandatory lOOP :V_SQL
'||P_INTERFACE_ID||' -'||V_SQL);

COMMIT;
END IF;
END IF;--MANDATORY
END;
--ZZZ_TEST('SP_VERIFY_LOAD C4 OTH: '||P_INTERFACE_ID||' -'||K.EXECUTE_SQLS||
SQLERRM ||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);

END LOOP;
IF V_ERRORS_FOUND >0 THEN
V_ERRORS_FOUND_TOTAL := NVL(V_ERRORS_FOUND_TOTAL,0) + V_ERRORS_FOUND;
IF V_ERROR_LOGS IS NULL THEN V_ERROR_LOGS :=' Errors encountered
for missing Mandatory fields';
ELSE V_ERROR_LOGS:= V_ERROR_LOGS ||',missing
Mandatory fields';END IF;

END IF;
V_ERRORS_FOUND:=0;
V_SOURCE_FIELD_NAME_FORCLEANUP:= NULL;
-----------------------------------------------------------------------------------
-------------------------
--------------------------------------- IS NUMBER CHECKS
---------------------------------------------------
-----------------------------------------------------------------------------------
-------------------------

ZZZ_TEST('SP_VERIFY_LOAD C6 CURSOR Number check LOOP STARTS : ');


FOR X IN C6 LOOP
BEGIN
V_CNT:= 0;
V_SQL:=NULL;
--V_SOURCE_FIELD_NAME_FORCLEANUP :=X.SOURCE_FIELD_NAME_FOR_CLEANUP; TO
CLEAN UP THE CONDITION EVERYWHERE
IF X.SOURCE_FIELD_NAME_FOR_CLEANUP IS NOT NULL THEN
V_SQL := 'select count(1) from '||X.SOURCE_TABLE_NAME||'
where isnumber('||X.SOURCE_FIELD_NAME_FOR_CLEANUP|| ' ) =0 and '
||
X.SOURCE_FIELD_NAME_FOR_CLEANUP|| ' is not null';
ELSE
V_SQL := 'select count(1) from '||X.SOURCE_TABLE_NAME||'
where isnumber('||X.SOURCE_FIELD_NAME|| ' ) =0 and '
||
X.SOURCE_FIELD_NAME|| ' is not null';
END IF;

ZZZ_TEST('SP_VERIFY_LOAD C2 Check lOOP :V_SQL '||


P_INTERFACE_ID||' -'||V_SQL);

EXECUTE IMMEDIATE V_SQL INTO V_CNT;


IF V_CNT > 0 THEN
BEGIN
IF X.SOURCE_FIELD_NAME_FOR_CLEANUP IS NOT NULL THEN
V_SQL_STRING:= ' select listagg(check_col,'||CHR(39)||','||
CHR(39)||
') WITHIN GROUP (order by check_col)as error_data
from ( select distinct '
||X.SOURCE_FIELD_NAME_FOR_CLEANUP|| ' as
check_col from '||X.SOURCE_TABLE_NAME
||' where isnumber('||
X.SOURCE_FIELD_NAME_FOR_CLEANUP|| ' ) =0 and '
||X.SOURCE_FIELD_NAME_FOR_CLEANUP|| ' is not null
) WHERE ROWNUM <=10';
ELSE
V_SQL_STRING:= ' select listagg(check_col,'||CHR(39)||','||
CHR(39)||
') WITHIN GROUP (order by check_col)as error_data
from ( select distinct '
||X.SOURCE_FIELD_NAME|| ' as check_col from '||
X.SOURCE_TABLE_NAME
||' where isnumber('||X.SOURCE_FIELD_NAME|| ' )
=0 and '
||X.SOURCE_FIELD_NAME|| ' is not null ) WHERE
ROWNUM <=10';
END IF;
ZZZ_TEST('SP_VERIFY_LOAD C2 numeric validations
lOOP :v_sql_string '||P_INTERFACE_ID||' -'||V_SQL_STRING);
EXECUTE IMMEDIATE V_SQL_STRING INTO V_SQL_STRING_RESULT;

EXCEPTION
WHEN OTHERS THEN
NULL ;
END;

PKG_DATA_LOAD.SP_INSERT_ERROR_LOG_SUM(P_INTERFACE_ID,X.SOURCE_FIELD_NAME ||' Field


should be numeric.'||'( '||V_SQL_STRING_RESULT||' )',
V_CNT, V_SQL);
V_ERRORS_FOUND := NVL(V_ERRORS_FOUND,0)+1;

ZZZ_TEST('SP_VERIFY_LOAD C2 Validations lOOP :V_SQL


'||P_INTERFACE_ID||' -'||V_SQL);
COMMIT;
END IF; -- number field
END;

END LOOP;

IF V_ERRORS_FOUND>0 THEN
V_ERRORS_FOUND_TOTAL := NVL(V_ERRORS_FOUND_TOTAL,0) + V_ERRORS_FOUND;
IF V_ERROR_LOGS IS NULL THEN V_ERROR_LOGS :=' Errors encountered for numeric check
on data';
ELSE V_ERROR_LOGS:= V_ERROR_LOGS ||',Numeric check on data'; END IF;

END IF;
V_ERRORS_FOUND:=0;

V_SOURCE_FIELD_NAME_FORCLEANUP:= NULL;
-----------------------------------------------------------------------------------
-------------------------
--------------------------------------- IS DATE CHECKS
---------------------------------------------------
-----------------------------------------------------------------------------------
-------------------------

ZZZ_TEST('SP_VERIFY_LOAD C7 CURSOR DATE check LOOP STARTS : ');


FOR X IN C7 LOOP
BEGIN
V_CNT:= 0;
V_SQL:=NULL;
BEGIN
--V_SOURCE_FIELD_NAME_FORCLEANUP :=X.SOURCE_FIELD_NAME_FOR_CLEANUP;

IF X.NON_DATABASE_SOURCE_COLUMN ='Y' THEN


V_SQL := 'select count(1) from DUAL where ISDATE_FOR_LOADING('||
CHR(39)||X.DESTINATION_FIELD_VALUE||CHR(39)||','||CHR(39)||
X.FORMAT_MODEL_DEFAULT_VALUE||CHR(39)||' ) =0 and '
||CHR(39)||
X.DESTINATION_FIELD_VALUE||CHR(39)|| ' is not null';
ELSE
IF X.SOURCE_FIELD_NAME_FOR_CLEANUP IS NOT NULL THEN
V_SQL := 'select count(1) from '||X.SOURCE_TABLE_NAME||'
where ISDATE_FOR_LOADING('||X.SOURCE_FIELD_NAME_FOR_CLEANUP||','||CHR(39)||
X.FORMAT_MODEL||CHR(39)||' ) =0 and '
||
X.SOURCE_FIELD_NAME_FOR_CLEANUP|| ' is not null';

ELSIF X.SOURCE_FIELD_NAME IS NOT NULL THEN


V_SQL := 'select count(1) from '||X.SOURCE_TABLE_NAME||'
where ISDATE_FOR_LOADING('||X.SOURCE_FIELD_NAME||','||CHR(39)||X.FORMAT_MODEL||
CHR(39)||' ) =0 and '
||X.SOURCE_FIELD_NAME|| '
is not null';

END IF;
END IF;
END;
ZZZ_TEST('SP_VERIFY_LOAD C7 Check lOOP :V_SQL '||
P_INTERFACE_ID||' -'||V_SQL);
IF V_SQL IS NOT NULL THEN
EXECUTE IMMEDIATE V_SQL INTO V_CNT;
END IF;

IF V_CNT > 0 THEN


BEGIN
IF X.SOURCE_FIELD_NAME_FOR_CLEANUP IS NOT NULL THEN
V_SQL_STRING:= ' select listagg(check_col,'||
CHR(39)||','||CHR(39)||
') WITHIN GROUP (order by check_col) as
error_data from ( select distinct '
||X.SOURCE_FIELD_NAME_FOR_CLEANUP|| ' as
check_col from '||X.SOURCE_TABLE_NAME
||' where ISDATE_FOR_LOADING('||
X.SOURCE_FIELD_NAME_FOR_CLEANUP ||','||
CHR(39)|| X.FORMAT_MODEL||CHR(39)
|| ' ) =0 and '||
X.SOURCE_FIELD_NAME_FOR_CLEANUP
|| ' is not null ) WHERE ROWNUM <=2';

ELSE
V_SQL_STRING:= ' select listagg(check_col,'||
CHR(39)||','||CHR(39)||
') WITHIN GROUP (order by check_col) as
error_data from ( select distinct '
||X.SOURCE_FIELD_NAME|| ' as check_col
from '||X.SOURCE_TABLE_NAME
||' where ISDATE_FOR_LOADING('||
X.SOURCE_FIELD_NAME
||','||CHR(39)|| X.FORMAT_MODEL||CHR(39)

|| ' ) =0 and '||X.SOURCE_FIELD_NAME

|| ' is not null ) WHERE ROWNUM <=2';

END IF;
ZZZ_TEST('SP_VERIFY_LOAD C7 Check
lOOP :v_sql_string '||P_INTERFACE_ID||' -'||V_SQL_STRING);

EXECUTE IMMEDIATE V_SQL_STRING INTO V_SQL_STRING_RESULT;

EXCEPTION
WHEN OTHERS THEN
NULL ;
END;
PKG_DATA_LOAD.SP_INSERT_ERROR_LOG_SUM(P_INTERFACE_ID,'Use Date Format
mask <font color= "Blue">( DD-MON-YYYY accepted by default )</font> for <font
color= "Red"><B>'||X.SOURCE_FIELD_NAME ||'</B></font> ('||
V_SQL_STRING_RESULT||')',V_CNT, V_SQL );
V_ERRORS_FOUND := NVL(V_ERRORS_FOUND,0)+1;
--ZZZ_TEST('SP_VERIFY_LOAD C7 Check lOOP :V_SQL
'||P_INTERFACE_ID||' -'||V_SQL||' ;'||v_errors_found);
COMMIT;
END IF; -- number field
END;

END LOOP;
-----------------------------------------------------------------------------------
-------------------------
-----------------------------------------------------------------------------------
-------------------------
-----------------------------------------------------------------------------------
-------------------------

IF V_ERRORS_FOUND>0 THEN
V_ERRORS_FOUND_TOTAL := NVL(V_ERRORS_FOUND_TOTAL,0) + V_ERRORS_FOUND;
IF V_ERROR_LOGS IS NULL THEN V_ERROR_LOGS :=' Errors encountered for Date check on
data';
ELSE V_ERROR_LOGS:= V_ERROR_LOGS ||',Date check on data'; END IF;

END IF;
V_ERRORS_FOUND:=0;

V_SOURCE_FIELD_NAME_FORCLEANUP:= NULL;

IF V_ERROR_LOGS IS NULL THEN


V_ERROR_LOGS := 'All Good';
-----------------------------------------------------------------------------------
-------------------------
--------------------------------------- PRE SQLS
----------------------------------------------------------
-----------------------------------------------------------------------------------
-------------------------
-----------------------------------------------------------------------------------
-------------------------
--------------------------------------- INSERT
BEGINS-------------------------------------------------------
-----------------------------------------------------------------------------------
-------------------------
-----------------------------------------------------------------------------------
-------------------------
--------------------------------------- Post sql
-----------------------------------------------------------
-----------------------------------------------------------------------------------
-------------------------
-----------------------------------------------------------------------------------
-------------------------
--------------------------------------- SUMMARY
-----------------------------------------------------------
-----------------------------------------------------------------------------------
-------------------------
--else
-- Rollback; -- presqls if any
END IF;-- if V_ERROR_LOGS is null then

ZZZ_TEST('SP_VERIFY_LOAD SUMMARY : '||


P_INTERFACE_ID);

DELETE FROM SYS_LOAD_INTERFACE_SUMMARY


WHERE INTERFACE_ID=P_INTERFACE_ID
AND SUMMARY_TYPE='DATA_LOADED_INFO';

SELECT NVL(MAX(LINE_NO),0)+1
INTO V_LINE_NO
FROM SYS_LOAD_INTERFACE_SUMMARY
WHERE INTERFACE_ID=P_INTERFACE_ID
AND SUMMARY_TYPE='DATA_LOADED_INFO';

IF V_ERROR_LOGS = 'All Good' THEN

EXECUTE IMMEDIATE 'select count(*) from '|| V_LOAD_HEADER.DESTINATION_TABLE_NAME


INTO V_NO_OF_RECORDS;
V_ERROR_LOGS := '<font color= "BLUE"><B>'||V_ERROR_LOGS ||'</B></font>( '||
V_NO_OF_RECORDS||' rows inserted in '||V_LOAD_HEADER.DESTINATION_TABLE_NAME
||' )' ;
ELSE
V_ERROR_LOGS := '<font color= "Red">'||V_ERROR_LOGS ||'</font>' ;
END IF;

INSERT INTO SYS_LOAD_INTERFACE_SUMMARY( INTERFACE_ID,


LINE_NO,
SUMMARY_TYPE,
SUMMARY_RESULTS,
NOTES,
CREATED_BY,
CREATED_DATE,
CHANGED_BY,
CHANGED_DATE)
VALUES( P_INTERFACE_ID ,--INTERFACE_ID,

V_LINE_NO , --LINE_NO,
'DATA_LOADED_INFO' ,--SUMMARY_TYPE,

'Data Processed by ' || P_USER_ID || '


on ' || SYSDATE, --SUMMARY_RESULTS,
V_ERROR_LOGS , --NOTES,

P_USER_ID ,--CREATED_BY,
SYSDATE ,--CREATED_DATE,

NULL ,--CHANGED_BY,
NULL --CHANGED_DATE
);
COMMIT;

EXCEPTION
WHEN NO_DATA_FOUND THEN
PKG_DATA_LOAD.SP_INSERT_ERROR_LOG_SUM(P_INTERFACE_ID,
'Field value/Field is Missing per Setup.',
NULL,
V_FLAG||CHR(10)||V_SQL||CHR(10)||V_INSERT||'
'||V_SELECT||CHR(10) ||SQLERRM||',' ||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE );

WHEN TABLE_ERROR THEN


ZZZ_TEST('SP_VERIFY_LOAD END TABLE_ERROR:'||
P_INTERFACE_ID||' -'||SQLERRM ||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
PKG_DATA_LOAD.SP_INSERT_ERROR_LOG_SUM(P_INTERFACE_ID,
'<font color= "Red"><B>Incomplete
Setup.</B> </font>'
,NULL,
'Source Table does not Exist.'
||CHR(10)||' Pl. complete the Setups for
Loading Process.'
||CHR(10)||' Check for field with label '

||CHR(39)||'Source Table Name'||


CHR(39)||' ,on the First Tab('
||CHR(39)||'Interface Setup'||
CHR(39)||'), and in the Region: '
||CHR(39)||'Load Interface Setup'||
CHR(39) );
WHEN OUT_ERRORS THEN
--RAISE_APPLICATION_ERROR(-20702,V_SELECT||'-'||SUBSTR(SQLERRM,1,190));
ZZZ_TEST('SP_VERIFY_LOAD END OUT_ERRORS:'||
P_INTERFACE_ID||' -'||SQLERRM ||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
NULL;
WHEN OTHERS THEN
--RAISE_APPLICATION_ERROR(-20702,V_SELECT||'-'||SUBSTR(SQLERRM,1,190));
ZZZ_TEST('SP_VERIFY_LOAD END OTHERS :'||P_INTERFACE_ID||'
-'||SQLERRM ||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
PKG_DATA_LOAD.SP_INSERT_ERROR_LOG_SUM(P_INTERFACE_ID,SQLERRM||' :: '||
DBMS_UTILITY.FORMAT_ERROR_BACKTRACE,
NULL,
'V_FLAG-'||V_FLAG||CHR(10)||V_SQL||CHR(10)||
V_INSERT||' '||V_SELECT);
COMMIT;
END SP_DATA_LOAD_VERIFY_DATA;

You might also like