Create or Replace Multiple Query Store Procedure inn Oracle

Pintoo Yadav
Jan 16 2015
2k
0
0

CREATE OR REPLACE PROCEDURE spr_Appr_SearchData (
vNACHDEST IN VARCHAR2,
vFLAGMASTER IN VARCHAR2,
vSearchText IN VARCHAR2,
vCetegory IN VARCHAR2,
vCondition IN VARCHAR2,
cur_Out OUT SYS_REFCURSOR
)
AS
mQuery VARCHAR2(4000);
mErr_Msg VARCHAR2(200);
BEGIN
-- Show Mandate base on UMRN----
IF (vCondition = 'All' AND vCetegory= 'UMRN') THEN
mQuery := 'SELECT MANDATE_DATE, IHNO, MANDATE_ID, CUST_REFNO, SCH_REFNO, UMRN, BANK_CODE, ACNO,'||
'FREQUENCY, PERIOD, CUST_ID, TEL_NO, MOBILE_NO, MAIL_ID,B.CODE_DESC,UMRN,CUST_NAME,AC_TYPE,'||
' ACNO,AMOUNT,START_DATE,END_DATE,A.STATUS,STATUS_CODE,COLLECTION_TYPE, '||
'CUST_BANKNM FROM ' || vNACHDEST ||' A,'|| vFLAGMASTER ||' B WHERE B.ID(+)=''R'' AND A.STATUS_CODE=B.CODE(+) AND '||
' NVL(TRIM(A.STATUS),'' '') <> '' '' AND UMRN='''|| vSearchText ||'''';
OPEN cur_Out FOR
mQuery;
ELSE IF (vCondition = 'Accepted' AND vCetegory= 'UMRN') THEN
mQuery := 'SELECT MANDATE_DATE, IHNO, MANDATE_ID, CUST_REFNO, SCH_REFNO, UMRN, BANK_CODE, ACNO,'||
'FREQUENCY, PERIOD, CUST_ID, TEL_NO, MOBILE_NO, MAIL_ID,B.CODE_DESC,UMRN,CUST_NAME,AC_TYPE,'||
' ACNO,AMOUNT,START_DATE,END_DATE,A.STATUS,STATUS_CODE,COLLECTION_TYPE, '||
'CUST_BANKNM FROM ' || vNACHDEST ||' A,'|| vFLAGMASTER ||' B WHERE B.ID(+)=''R'' AND A.STATUS_CODE=B.CODE(+) AND '||
' NVL(TRIM(A.STATUS),'' '') = ''A'' AND UMRN='''|| vSearchText ||'''';
OPEN cur_Out FOR
mQuery;
ELSE IF (vCondition = 'Rejected' AND vCetegory= 'UMRN') THEN
mQuery := 'SELECT MANDATE_DATE, IHNO, MANDATE_ID, CUST_REFNO, SCH_REFNO, UMRN, BANK_CODE, ACNO,'||
'FREQUENCY, PERIOD, CUST_ID, TEL_NO, MOBILE_NO, MAIL_ID,B.CODE_DESC,UMRN,CUST_NAME,AC_TYPE,'||
' ACNO,AMOUNT,START_DATE,END_DATE,A.STATUS,STATUS_CODE,COLLECTION_TYPE, '||
'CUST_BANKNM FROM ' || vNACHDEST ||' A,'|| vFLAGMASTER ||' B WHERE B.ID(+)=''R'' AND A.STATUS_CODE=B.CODE(+) AND '||
' NVL(TRIM(A.STATUS),'' '') = ''R'' AND UMRN='''|| vSearchText ||'''';
OPEN cur_Out FOR
mQuery;
-- Show Mandate base on Cust NAME----
ELSE IF (vCondition = 'All' AND vCetegory= 'NAME') THEN
mQuery := 'SELECT MANDATE_DATE, IHNO, MANDATE_ID, CUST_REFNO, SCH_REFNO, UMRN, BANK_CODE, ACNO,FREQUENCY, '||
'PERIOD, CUST_ID, TEL_NO, MOBILE_NO, MAIL_ID,B.CODE_DESC,UMRN,CUST_NAME,AC_TYPE,ACNO,AMOUNT, '||
'START_DATE,END_DATE,STATUS,STATUS_CODE,COLLECTION_TYPE,CUST_BANKNM FROM '|| vNACHDEST ||' A, '||
''|| vFLAGMASTER ||' B WHERE B.ID(+)=''R'' AND A.STATUS_CODE=B.CODE(+) AND '||
' NVL(TRIM(STATUS),'' '') <> '' '' AND CUST_NAME LIKE ''%'|| vSearchText ||'''';
OPEN cur_Out FOR
mQuery;
ELSE IF (vCondition = 'Accepted' AND vCetegory= 'NAME' ) THEN
mQuery := 'SELECT MANDATE_DATE, IHNO, MANDATE_ID, CUST_REFNO, SCH_REFNO, UMRN, BANK_CODE, ACNO,FREQUENCY, '||
'PERIOD, CUST_ID, TEL_NO, MOBILE_NO, MAIL_ID,B.CODE_DESC,UMRN,CUST_NAME,AC_TYPE,ACNO,AMOUNT, '||
'START_DATE,END_DATE,STATUS,STATUS_CODE,COLLECTION_TYPE,CUST_BANKNM FROM '|| vNACHDEST ||' A, '||
''|| vFLAGMASTER ||' B WHERE B.ID(+)=''R'' AND A.STATUS_CODE=B.CODE(+) AND '||
' NVL(TRIM(STATUS),'' '') = ''A'' AND CUST_NAME LIKE ''%'|| vSearchText ||'''';
OPEN cur_Out FOR
mQuery;
ELSE IF (vCondition = 'Rejected' AND vCetegory= 'NAME') THEN
mQuery := 'SELECT MANDATE_DATE, IHNO, MANDATE_ID, CUST_REFNO, SCH_REFNO, UMRN, BANK_CODE, ACNO,FREQUENCY, '||
'PERIOD, CUST_ID, TEL_NO, MOBILE_NO, MAIL_ID,B.CODE_DESC,UMRN,CUST_NAME,AC_TYPE,ACNO,AMOUNT, '||
'START_DATE,END_DATE,STATUS,STATUS_CODE,COLLECTION_TYPE,CUST_BANKNM FROM '|| vNACHDEST ||' A, '||
''|| vFLAGMASTER ||' B WHERE B.ID(+)=''R'' AND A.STATUS_CODE=B.CODE(+) AND '||
' NVL(TRIM(STATUS),'' '') = ''R'' AND CUST_NAME LIKE ''%'|| vSearchText ||'''';
OPEN cur_Out FOR
mQuery;
-- Show Mandate base on ACNO----
ELSE IF (vCondition = 'All' AND vCetegory= 'ACNO' ) THEN
mQuery := 'SELECT MANDATE_DATE, IHNO, MANDATE_ID, CUST_REFNO, SCH_REFNO, UMRN, BANK_CODE, ACNO,'||
'FREQUENCY, PERIOD, CUST_ID, TEL_NO, MOBILE_NO, MAIL_ID,B.CODE_DESC,UMRN,CUST_NAME,AC_TYPE,ACNO,AMOUNT,'||
'START_DATE,END_DATE,STATUS,STATUS_CODE,COLLECTION_TYPE,CUST_BANKNM FROM '|| vNACHDEST ||' A, '||
''|| vFLAGMASTER ||' B WHERE B.ID(+)=''R'' AND A.STATUS_CODE=B.CODE(+) AND '||
' NVL(TRIM(STATUS),'' '') <> '' '' AND ACNO='''|| vSearchText ||'''';
OPEN cur_Out FOR
mQuery;
ELSE IF (vCondition = 'Accepted' AND vCetegory= 'ACNO') THEN
mQuery := 'SELECT MANDATE_DATE, IHNO, MANDATE_ID, CUST_REFNO, SCH_REFNO, UMRN, BANK_CODE, ACNO,'||
'FREQUENCY, PERIOD, CUST_ID, TEL_NO, MOBILE_NO, MAIL_ID,B.CODE_DESC,UMRN,CUST_NAME,AC_TYPE,ACNO,AMOUNT,'||
'START_DATE,END_DATE,STATUS,STATUS_CODE,COLLECTION_TYPE,CUST_BANKNM FROM '|| vNACHDEST ||' A, '||
''|| vFLAGMASTER ||' B WHERE B.ID(+)=''R'' AND A.STATUS_CODE=B.CODE(+) AND '||
' NVL(TRIM(STATUS),'' '') = ''A'' AND ACNO='''|| vSearchText ||'''';
OPEN cur_Out FOR
mQuery;
ELSE IF (vCondition = 'Rejected' AND vCetegory= 'ACNO') THEN
mQuery := 'SELECT MANDATE_DATE, IHNO, MANDATE_ID, CUST_REFNO, SCH_REFNO, UMRN, BANK_CODE, ACNO,'||
'FREQUENCY, PERIOD, CUST_ID, TEL_NO, MOBILE_NO, MAIL_ID,B.CODE_DESC,UMRN,CUST_NAME,AC_TYPE,ACNO,AMOUNT,'||
'START_DATE,END_DATE,STATUS,STATUS_CODE,COLLECTION_TYPE,CUST_BANKNM FROM '|| vNACHDEST ||' A, '||
''|| vFLAGMASTER ||' B WHERE B.ID(+)=''R'' AND A.STATUS_CODE=B.CODE(+) AND '||
' NVL(TRIM(STATUS),'' '') = ''R'' AND ACNO='''|| vSearchText ||'''';
OPEN cur_Out FOR
mQuery;
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(';SQLERRM = ' || SQLERRM);
mErr_Msg := SQLERRM;
OPEN cur_Out FOR
SELECT ';FAILED#' || mErr_Msg "Status" from dual;
ROLLBACK;
END;
/

Oracle

Up Next

Create or Replace Multiple Query Store Procedure inn Oracle