---------------------------------------------------------------------------------------------
CREATE OR REPLACE PACKAGE BKDP_SEARCH_PKG AS
TYPE BKDP_Cur_GetAccts IS REF CURSOR; --RETURN BKDP_Rec_GetAccts;

PROCEDURE SP_BKDP_ACCOUNTSEARCH (param_account in
RACCOUNT.ACCOUNT%TYPE,
param_name in RACCTREL.NAME%TYPE,
param_case in RACCBKRP.CASE_NUMBER%TYPE,
param_ssn in RACCTREL.SS_NO%TYPE,
BKDP_inout OUT BKDP_Cur_GetAccts);
END BKDP_SEARCH_PKG;
/
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
CREATE OR REPLACE PACKAGE BODY BKDP_SEARCH_PKG AS

PROCEDURE SP_BKDP_ACCOUNTSEARCH
(param_account in RACCOUNT.ACCOUNT%TYPE,
param_name in RACCTREL.NAME%TYPE,
param_case in RACCBKRP.CASE_NUMBER%TYPE,
param_ssn in RACCTREL.SS_NO%TYPE,
BKDP_inout OUT BKDP_Cur_GetAccts)
as

SQLQuery varchar2(4000);
BEGIN

SQLQuery := 'Select RACCOUNT.account, RACCTREL1.NAME PRIMNAME,
BKDP_MISSINGFIELDS.LEGAL_NAME, ' ||chr(10)
||'RACCTREL1.SS_NO PRIMSSN, RACCTREL1.STATE PRIMSTATE,
RACCBKRP.CASE_NUMBER, ' ||chr(10)
||'RACCBKRP.COURT_ID, RACCTREL2.NAME CONAME, RACCTREL2.SS_NO COSSN,
RACCTREL2.STATE COSTATE ' ||chr(10)
||'FROM RACCOUNT INNER JOIN RACCTREL RACCTREL1 ON (RACCOUNT.ACCOUNT
= RACCTREL1.ACCOUNT AND RACCTREL1.REL_POS= ''1'') ' ||chr(10)
||'LEFT OUTER JOIN RACCTREL RACCTREL2 ON (RACCOUNT.ACCOUNT =
RACCTREL2.ACCOUNT AND RACCTREL2.REL_POS= ''2'') '||chr(10)
||'LEFT OUTER JOIN BKDP_MISSINGFIELDS ON RACCOUNT.ACCOUNT =
BKDP_MISSINGFIELDS.ACCOUNT '||chr(10)
||'LEFT OUTER JOIN RACCBKRP ON RACCOUNT.ACCOUNT = RACCBKRP.ACCOUNT
'||chr(10)
||'WHERE ROWNUM < 500 ';
if (param_account IS NOT NULL) then
SQLQuery := SQLQuery || 'AND RACCOUNT.ACCOUNT = ''' || param_account
|| ''' ';
end if;
if (param_name IS NOT NULL) then
SQLQuery := SQLQuery || 'and RACCTREL1.NAME_U LIKE ''' || param_name
|| '%'' ';
end if;
if (param_case IS NOT NULL) then
SQLQuery := SQLQuery || 'AND RACCBKRP.CASE_NUMBER LIKE ''' ||
param_case || '%'' ';
end if;
if (param_ssn is not null) then
SQLQuery := SQLQuery || 'AND RACCTREL1.SS_NO LIKE ''' || param_ssn ||
'%'' ';
end if;

SQLQuery := SQLQuery || 'ORDER BY RACCOUNT.ACCOUNT';

open BKDP_inout for SQLQuery;

END SP_BKDP_ACCOUNTSEARCH;

END BKDP_SEARCH_PKG;
/