TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
C# Corner
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
Create or Replace Multiple Query Store Procedure inn Oracle
Pintoo Yadav
Jan 16
2015
Code
1.9
k
0
0
facebook
twitter
linkedIn
Reddit
WhatsApp
Email
Bookmark
expand
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