create
or
replace
procedure
GetCustomerInfo
(
CompanyID in varchar2 ,AcceptDateStart in varchar2 ,AcceptDateEnd in varchar2 ,p_cursor OUT SYS_REFCURSOR
)
is
begin
open p_cursor FOR
select agentcode as CompanyID,appntNO,appntName,appntsex,appntbirthday,appntidtype,appntidno,phone,ZipCode,postaladdress,homeaddress,companyaddress, '' ,ldoccupation.OccupationCode,( select codename from ldcode where codetype = ' occupationtype ' and code = ldoccupation.occupationtype)
from lccont,lcaddress,ldperson,ldoccupation
where managecom = CompanyID
and signdate between to_date(AcceptDateStart, ' YYYY_MM_DD ' ) and to_date(AcceptDateEnd, ' YYYY_MM_DD ' )
and lccont.appntno = ldperson.customerno
and ldperson.customerno = lcaddress.customerno
and ldperson.occupationcode = ldoccupation.occupationcode
union
select agentcode,insuredNO,insuredName,appntsex,appntbirthday,appntidtype,appntidno,phone,ZipCode,postaladdress,homeaddress,companyaddress, '' ,ldoccupation.OccupationCode,( select codename from ldcode where codetype = ' occupationtype ' and code = ldoccupation.occupationtype)
from lccont,lcaddress,ldperson,ldoccupation
where managecom = CompanyID
and signdate between to_date(AcceptDateStart, ' YYYY_MM_DD ' ) and to_date(AcceptDateEnd, ' YYYY_MM_DD ' )
and lccont.appntno = ldperson.customerno
and ldperson.customerno = lcaddress.customerno
and ldperson.occupationcode = ldoccupation.occupationcode;
end GetCustomerInfo;
(
CompanyID in varchar2 ,AcceptDateStart in varchar2 ,AcceptDateEnd in varchar2 ,p_cursor OUT SYS_REFCURSOR
)
is
begin
open p_cursor FOR
select agentcode as CompanyID,appntNO,appntName,appntsex,appntbirthday,appntidtype,appntidno,phone,ZipCode,postaladdress,homeaddress,companyaddress, '' ,ldoccupation.OccupationCode,( select codename from ldcode where codetype = ' occupationtype ' and code = ldoccupation.occupationtype)
from lccont,lcaddress,ldperson,ldoccupation
where managecom = CompanyID
and signdate between to_date(AcceptDateStart, ' YYYY_MM_DD ' ) and to_date(AcceptDateEnd, ' YYYY_MM_DD ' )
and lccont.appntno = ldperson.customerno
and ldperson.customerno = lcaddress.customerno
and ldperson.occupationcode = ldoccupation.occupationcode
union
select agentcode,insuredNO,insuredName,appntsex,appntbirthday,appntidtype,appntidno,phone,ZipCode,postaladdress,homeaddress,companyaddress, '' ,ldoccupation.OccupationCode,( select codename from ldcode where codetype = ' occupationtype ' and code = ldoccupation.occupationtype)
from lccont,lcaddress,ldperson,ldoccupation
where managecom = CompanyID
and signdate between to_date(AcceptDateStart, ' YYYY_MM_DD ' ) and to_date(AcceptDateEnd, ' YYYY_MM_DD ' )
and lccont.appntno = ldperson.customerno
and ldperson.customerno = lcaddress.customerno
and ldperson.occupationcode = ldoccupation.occupationcode;
end GetCustomerInfo;