select distinct sid
  from v$mystat

SELECT  <columns>
FROM emp e, permanent_table pt
WHERE e.emp_id = pt.emp_id
AND pt.session_id = p_session_id;

PROCEDURE ExecuteBtn IS
--
VTotalRows number ;
RgId RecordGroup ;
--
--The following variables will hold the multiple values of their selcted criteria for multiselection.
--
EmpIdStr varchar2(600) ; --used to hold empid,first name,middle name,last name employeeno numbers.
EmpFrstNmeEmpNoStr varchar2(600) ; --In case of multiple frist name choice.
EmpMiddleNmeEmpNoStr varchar2(600) ; --In case of multiple middle name choice.
EmpLastNmeEmpNoStr varchar2(600) ; --In case of multiple last name choice.
EmpOUNoStr varchar2(600) ; --In case of multiple ou choice.
EmpJobNoStr varchar2(600) ; --In case of multiple jobs choice.
EmpPosNoStr varchar2(600) ; --In case of multiple position choice.
----
EmpGrpNoStr varchar2(600) ; --In case of multiple emp group choice.
EmpPaNoStr varchar2(600) ; --In case of multiple emp personnal area choice.
EmpPrAreaNoStr varchar2(600) ; --In case of multiple emp Pay roll area choice.
EmpTmaNoStr varchar2(600) ; --In case of multiple emp time management area choice.
EmpBAreaNoStr varchar2(600) ; --In case of multiple business area choice.
EmpTypeNoStr varchar2(600) ; --In case of multiple emp type area choice.
EmpStatusNoStr varchar2(600) ; --In case of multiple emp Status area choice.
EmpDeploymentNoStr varchar2(600) ; --In case of multiple emp deployment area choice.
EmpResponNoStr varchar2(600) ; --In case of multiple emp responsibility area choice.
EmpQualifNoStr varchar2(600) ; --In case of multiple emp Qualification area choice.
EmpQualifLevelNoStr varchar2(600) ; --In case of multiple emp Qualification level choice.
EmpExperienceNoStr varchar2(600) ; --In case of multiple Emperience choice.
EmpExperienceLevNoStr varchar2(600) ; --In case of multiple Emperience level choice.
EmpSkillNoStr varchar2(600) ; --In case of multiple Skill choice.
EmpSkillLevelNoStr varchar2(600) ; --In case of multiple Skill level choice.
EmpComptncyNoStr varchar2(600) ; --In case of multiple competency choice.
EmpMaritalNoStr varchar2(300) ; --In case of multiple maritallity choice.
EmpNationalityNoStr varchar2(250) ; --In case of multiple nationality choice.
EmpNicNoStr varchar2(1000) ; --In case of multiple national id card choice.
EmpSSNStr varchar2(1000) ; --In case of single SOCIAL SECURITY NUMBER id card choice.
EmpTaxNoStr varchar2(1000) ; --In case of multiple tax no choice.
--
EmpDateOfBrthFromStr varchar2(500) ; --In case of multiple birth from dates choice.
EmpDateOfBrthToStr varchar2(500) ; --In case of multiple birth To dates choice.
DateOfBrthToExistFlag varchar2(30) ; --Flag to know wether any multiple dates have been selected or not.
--
JoinDateFromStr varchar2(500) ; --In case of multiple birth from dates choice.
JoinDateToStr varchar2(500) ; --In case of multiple birth To dates choice.
JoinDateFromToExistFlag varchar2(30) ; --Flag to know wether any multiple dates have been selected or not.
-- 
begin
--the folowings are the Vield variables where different queries are being strored for processing.
-- 1 :CrieTeria.QueryStringForOrgStruct varchar2(1500) ;
-- 2 :CrieTeria.QueryStringForEmpEpStruct varchar2(1500) ;
-- 3 :CrieTeria.QueryStringForTma varchar2(1500) ;

--First initialize the where clause query string with null
:CrieTeria.QueryString := null ;
:CrieTeria.QueryStringForOrgStruct := null ;
:CrieTeria.QueryStringForEmpEpStruct := null ;
:CrieTeria.QueryStringForTma := null ;
Set_block_property('HRMPASEMPLOYEE',default_where, '') ; 
--
--QueryStringForOrgStruct variable is being used to make a string for where clause of hrmpasemployee on hrmpasemporgstruct combination.
--
:CrieTeria.QueryStringForOrgStruct := ' and EmployeeNo in(select e.employeeno from HrmpasEmployee e, HrmPasEmpOrgStruct t where t.employeeno = e.EmployeeNo and t.PlanNo = :CrieTeria.PlanNo and t.Status = 1' ; 
--
--
--QueryStringForEmpEpStruct variable is being used to make a string for where clause of hrmpasemployee on HrmPasEmpEpStruct combination.
--
:CrieTeria.QueryStringForEmpEpStruct := ' and EmployeeNo in(select e.employeeno 
from HrmpasEmployee e,
HrmPasEmpEpStruct ep 
where ep.employeeno = e.EmployeeNo 
and ep.PlanNo = :CrieTeria.PlanNo
and ep.Status = 1
and ep.co = :B0.co ' ; 
--
--
--QueryStringForTma variable is being used to make a string for where clause of hrmpasemployee on hrmpasemporgstruct combination.
--
:CrieTeria.QueryStringForTma := ' and EmployeeNo in(select e.employeeno 
from HrmpasEmployee e,
HrmPasEmpTma tm 
where tm.employeeno = e.EmployeeNo 
and tm.PlanNo = :CrieTeria.PlanNo
and tm.Status = 1' ; 


---------------------------------------------------------------------------------------------------------------------------------
RgId := Find_Group('MultSelectionRecGrp') ;
VTotalRows := Get_group_row_count(RgId) ;
--
--The following loop brings records that have been choosen from multiple option.
--
for i in 1..VTotalRows loop
if Get_Group_Char_Cell('MultSelectionRecGrp.BtnNO',i) = 1 then
--If employeeid is selected then
EmpIdStr := EmpIdStr ||' OR EMPLOYEEID LIKE '''||Get_Group_Char_Cell('MultSelectionRecGrp.Id', i)||'''' ;
--
elsif Get_Group_Char_Cell('MultSelectionRecGrp.BtnNO',i) = 2 then
--If employe's name button has been selected multiple then
EmpFrstNmeEmpNoStr := EmpFrstNmeEmpNoStr ||' OR FIRSTNAME LIKE '''||Get_Group_Char_Cell('MultSelectionRecGrp.id', i)||'''' ;
--
elsif Get_Group_Char_Cell('MultSelectionRecGrp.BtnNO',i) = 3 then
--If employe Middle name has been selected multiple then
EmpMiddleNmeEmpNoStr := EmpMiddleNmeEmpNoStr ||' OR MIDDLENAME LIKE '''||Get_Group_Char_Cell('MultSelectionRecGrp.id', i)||'''' ;
--
elsif Get_Group_Char_Cell('MultSelectionRecGrp.BtnNO',i) = 4 then
--If employe Last name button has been selected for mulitple choice then
EmpLastNmeEmpNoStr := EmpLastNmeEmpNoStr ||' OR LASTNAME LIKE '''||Get_Group_Char_Cell('MultSelectionRecGrp.id', i)||'''' ;
--
elsif Get_Group_Char_Cell('MultSelectionRecGrp.BtnNO',i) = 5 then
--Orgainzational Unit
EmpOUNoStr := EmpOUNoStr ||' OR ORGUNITID LIKE '''||Get_Group_Char_Cell('MultSelectionRecGrp.id', i)||'''' ; 
--
elsif Get_Group_Char_Cell('MultSelectionRecGrp.BtnNO',i) = 6 then
--Job no selection
EmpJobNoStr := EmpJobNoStr ||' OR JobId LIKE '''||Get_Group_Char_Cell('MultSelectionRecGrp.id', i)||'''' ; 
-- 
elsif Get_Group_Char_Cell('MultSelectionRecGrp.BtnNO',i) = 7 then 
--Position id is selected
EmpPosNoStr := EmpPosNoStr ||' OR POSID LIKE '''||Get_Group_Char_Cell('MultSelectionRecGrp.id', i)||'''' ;
-- 
elsif Get_Group_Char_Cell('MultSelectionRecGrp.BtnNO',i) = 8 then 
--Employee Group
EmpGrpNoStr := EmpGrpNoStr ||' OR GROUPID LIKE '''||Get_Group_Char_Cell('MultSelectionRecGrp.id', i)||'''' ;
-- 
elsif Get_Group_Char_Cell('MultSelectionRecGrp.BtnNO',i) = 9 then 
--personnal area
EmpPaNoStr := EmpPaNoStr ||' OR PANO LIKE '''||Get_Group_Char_Cell('MultSelectionRecGrp.id', i)||'''' ;
--
elsif Get_Group_Char_Cell('MultSelectionRecGrp.BtnNO',i) = 10 then 
--Business area
EmpBAreaNoStr := EmpBAreaNoStr ||' OR BAID LIKE '''||Get_Group_Char_Cell('MultSelectionRecGrp.id', i)||'''' ;
--
elsif Get_Group_Char_Cell('MultSelectionRecGrp.BtnNO',i) = 11 then 
--Time management area
EmpTmaNoStr := EmpTmaNoStr ||' OR TMAID LIKE '''||Get_Group_Char_Cell('MultSelectionRecGrp.id', i)||'''' ; 
-- 
elsif Get_Group_Char_Cell('MultSelectionRecGrp.BtnNO',i) = 12 then 
--Pay Roll area
EmpPrAreaNoStr := EmpPrAreaNoStr ||' OR PraNo LIKE '''||Get_Group_Char_Cell('MultSelectionRecGrp.id', i)||'''' ; 
--
elsif Get_Group_Char_Cell('MultSelectionRecGrp.BtnNO',i) = 13 then 
--Employee Type
EmpTypeNoStr := EmpTypeNoStr ||' OR EMPTYPE LIKE '''||Get_Group_Char_Cell('MultSelectionRecGrp.id', i)||'''' ; 
-- 
elsif Get_Group_Char_Cell('MultSelectionRecGrp.BtnNO',i) = 14 then 
--Employee Status
EmpStatusNoStr := EmpStatusNoStr ||' OR EMPNATSTAT LIKE '''||Get_Group_Char_Cell('MultSelectionRecGrp.id', i)||'''' ; 
--
elsif Get_Group_Char_Cell('MultSelectionRecGrp.BtnNO',i) = 15 then 
--Employee Deployement
EmpDeploymentNoStr := EmpDeploymentNoStr ||' OR EMPNATSTAT LIKE '''||Get_Group_Char_Cell('MultSelectionRecGrp.id', i)||'''' ; 
--
elsif Get_Group_Char_Cell('MultSelectionRecGrp.BtnNO',i) = 17 then 
--Employee Responsibility
EmpResponNoStr := EmpResponNoStr ||' OR RESPONSIBILITYID LIKE '''||Get_Group_Char_Cell('MultSelectionRecGrp.id', i)||'''' ; 
--
elsif Get_Group_Char_Cell('MultSelectionRecGrp.BtnNO',i) = 18 then 
--Employee Qualification
EmpQualifNoStr := EmpQualifNoStr ||' OR QualificationID LIKE '''||Get_Group_Char_Cell('MultSelectionRecGrp.id', i)||'''' ; 
--
elsif Get_Group_Char_Cell('MultSelectionRecGrp.BtnNO',i) = 19 then 
--Employee Qualification level
EmpQualifLevelNoStr := EmpQualifLevelNoStr ||' OR LEVELID LIKE '''||Get_Group_Char_Cell('MultSelectionRecGrp.id', i)||'''' ; 
-- 
elsif Get_Group_Char_Cell('MultSelectionRecGrp.BtnNO',i) = 20 then 
--Employee Experiecne
EmpExperienceNoStr := EmpExperienceNoStr ||' OR EXPID LIKE '''||Get_Group_Char_Cell('MultSelectionRecGrp.id', i)||'''' ; 
--
elsif Get_Group_Char_Cell('MultSelectionRecGrp.BtnNO',i) = 21 then 
--Employee Experiecnelevel
EmpExperienceLevNoStr := EmpExperienceLevNoStr ||' OR LEVELID LIKE '''||Get_Group_Char_Cell('MultSelectionRecGrp.id', i)||'''' ; 
--
elsif Get_Group_Char_Cell('MultSelectionRecGrp.BtnNO',i) = 22 then 
--Employee Skill
EmpSkillNoStr := EmpSkillNoStr ||' OR SKILLID LIKE '''||Get_Group_Char_Cell('MultSelectionRecGrp.id', i)||'''' ; 
--
elsif Get_Group_Char_Cell('MultSelectionRecGrp.BtnNO',i) = 23 then 
--Employee Skill level
EmpSkillLevelNoStr := EmpSkillLevelNoStr ||' OR LEVELID LIKE '''||Get_Group_Char_Cell('MultSelectionRecGrp.id', i)||'''' ; 
--
elsif Get_Group_Char_Cell('MultSelectionRecGrp.BtnNO',i) = 24 then 
--Employee Compentency
EmpComptncyNoStr := EmpComptncyNoStr ||' OR COMP LIKE '''||Get_Group_Char_Cell('MultSelectionRecGrp.id', i)||'''' ; 
-- 
elsif Get_Group_Char_Cell('MultSelectionRecGrp.BtnNO',i) = 26 then 
--Employee Marital Status
EmpMaritalNoStr := EmpMaritalNoStr ||' OR MSDESC LIKE '''||Get_Group_Char_Cell('MultSelectionRecGrp.Descr', i)||'''' ; 
-- 
elsif Get_Group_Char_Cell('MultSelectionRecGrp.BtnNO',i) = 27 then 
--Employee Nationality
EmpNationalityNoStr := EmpNationalityNoStr ||','||Get_Group_Char_Cell('MultSelectionRecGrp.No', i) ; 
if EmpNationalityNoStr = ',' then
EmpNationalityNoStr := null ;
end if ; 
elsif Get_Group_Char_Cell('MultSelectionRecGrp.BtnNO',i) = 28 then 
--Employee National id card no
EmpNicNoStr := EmpNicNoStr ||' OR IDNO LIKE '''||Get_Group_Char_Cell('MultSelectionRecGrp.id', i)||'''' ;
-- 
elsif Get_Group_Char_Cell('MultSelectionRecGrp.BtnNO',i) = 34 then 
--Employee SOCIAL SECURITY NUMBER card no
EmpSSNStr := EmpSSNStr ||' OR SSNO LIKE '''||Get_Group_Char_Cell('MultSelectionRecGrp.id', i)||'''' ;
--
elsif Get_Group_Char_Cell('MultSelectionRecGrp.BtnNO',i) = 29 then 
--Employee tax no
EmpTaxNoStr := EmpTaxNoStr ||','||Get_Group_Char_Cell('MultSelectionRecGrp.No', i) ; 
if EmpTaxNoStr = ',' then
EmpTaxNoStr := null ;
end if ; 
elsif Get_Group_Char_Cell('MultSelectionRecGrp.BtnNO',i) = 30 then 
--Birth Dates flag
DateOfBrthToExistFlag := Get_Group_Char_Cell('MultSelectionRecGrp.ID', i) ;
DateOfBrthToExistFlag := DateOfBrthToExistFlag ||Get_Group_Char_Cell('MultSelectionRecGrp.descr', i) ; 
elsif Get_Group_Char_Cell('MultSelectionRecGrp.BtnNO',i) = 31 then 
--Joining dates flag
JoinDateFromToExistFlag := Get_Group_Char_Cell('MultSelectionRecGrp.ID', i) ;
JoinDateFromToExistFlag := DateOfBrthToExistFlag ||Get_Group_Char_Cell('MultSelectionRecGrp.descr', i) ; 
end if ;
--
end loop ;

---------------------------------------------------------------------------

--
--The following if structure will make a dynamic where clause string for the result block to show
--against the selected criteria or options to filter employee.
--this also first checks the which criteria user has selected to search and makes the query accordingly.
--
:CrieTeria.QueryString := 'Co = :B0.co ' ;
--The following if elsif structure check that wether male or female employees are to be sorted.
if :CrieTeria.Male = 'M' and :CrieTeria.Female <> 'F' then
:CrieTeria.QueryString := :CrieTeria.QueryString || ' AND Gender = '''||'MALE'''||'' ;
elsif :CrieTeria.Male <> 'M' and :CrieTeria.Female = 'F' then
:CrieTeria.QueryString := :CrieTeria.QueryString || ' AND Gender = '''||'FEMALE'''||'' ;
--message('Femal selected :'||:CrieTeria.QueryString) ;pause ;
elsif :CrieTeria.Male = 'M' and :CrieTeria.Female = 'F' then
:CrieTeria.QueryString := :CrieTeria.QueryString || ' AND ( Gender = '''||'MALE'||''''||' OR Gender = '''||'FEMALE'''||')' ;
end if ; -- if :CrieTeria.Male = 'M' and :CrieTeria.Female <> 'F' then
--
--The following if elsif structure check that wether active or inactive employees are to be sorted.
if :CrieTeria.Active = 1 and :CrieTeria.Inactive <> 1 then
:CrieTeria.QueryString := :CrieTeria.QueryString || ' AND Active = 1 ' ;
elsif :CrieTeria.Active <> 1 and :CrieTeria.Inactive = 1 then
:CrieTeria.QueryString := :CrieTeria.QueryString || ' AND Active = 0 ' ;
--message('Femal selected :'||:CrieTeria.QueryString) ;pause ;
elsif :CrieTeria.Active = 1 and :CrieTeria.Inactive = 1 then
:CrieTeria.QueryString := :CrieTeria.QueryString || ' AND ( Active = 1 OR Active = 0 )' ;
end if ; -- if :CrieTeria.Active = 1 and :CrieTeria.Inactive <> 1 then
----------------------------------------
if :CrieTeria.EmpID is not null 
or EmpIdStr is not null then
--if user has selected employeeno to search then
:CrieTeria.QueryString := :CrieTeria.QueryString|| ' and( Employeeid like '''||:CrieTeria.EMPid||''''||EmpIdStr||')' ;
--
end if ; --if :CrieTeria.EmployeeNo is not null .
-- 
if :CrieTeria.FIRSTNM is not null 
or EmpFrstNmeEmpNoStr is not null then
--if user has selected employee's first name to search then
:CrieTeria.QueryString := :CrieTeria.QueryString|| ' and( FIRSTNAME like '''||:CrieTeria.FIRSTNM||''''||EmpFrstNmeEmpNoStr||')' ;
--
end if ; --if :CrieTeria.FirstNm is not null then
-- 
if :CrieTeria.MIDDLENM is not null 
or EmpMiddleNmeEmpNoStr is not null then
--if user has selected employeen's middle name to search then
:CrieTeria.QueryString := :CrieTeria.QueryString|| ' and( MIDDLENAME like '''||:CrieTeria.MIDDLENM||''''||EmpMiddleNmeEmpNoStr||')' ;
--
end if ; --if :CrieTeria.MiddleNm is not null then
--
if :CrieTeria.LASTNM is not null 
or EmpLastNmeEmpNoStr is not null then
--
--if user has selected employeen's last name to search then LASTNAME
--
:CrieTeria.QueryString := :CrieTeria.QueryString|| ' and( LASTNAME like '''||:CrieTeria.LASTNM||''''||EmpLastNmeEmpNoStr||')' ;
--
end if ; --if :CrieTeria.LastNm is not null then
--

if :CrieTeria.MERITALSTATUS is not null 
or EmpMaritalNoStr is not null then --marital status
--
--if user has selected employeen's Country to search then
--
:CrieTeria.QueryString := :CrieTeria.QueryString|| ' and MsNO in(SELECT MsNO
FROM Hrmpasmaritalstatus 
where ( MSDESC like '||''''||:CrieTeria.MERITALSTATUS||''''||EmpMaritalNoStr||'))' ; 
--
end if ; --if :CrieTeria.CountryNo is not null then 
--
if :CrieTeria.CountryNo is not null 
or EmpNationalityNoStr is not null then
--
--if user has selected employeen's Nationality to search then
--
if :CrieTeria.CountryNo is null then
--
EmpNationalityNoStr := ltrim(EmpNationalityNoStr,',') ;
end if; 
:CrieTeria.QueryString := :CrieTeria.QueryString|| ' and CountryNo in( '||:CrieTeria.CountryNo||EmpNationalityNoStr||')' ;
--
end if ; --if :CrieTeria.CountryNo is not null then
--
if :CrieTeria.NicId is not null 
OR EmpNicNoStr is not null then
--
--if user has selected employeen's Nic no to search then
--
:CrieTeria.QueryString := :CrieTeria.QueryString|| ' and( IDNO like '''||:CrieTeria.NICID||''''||EmpNicNoStr||')' ;
--
end if ; --if :CrieTeria.NicId is not null then
--------------------------------------------------------------------
if :CrieTeria.SSN is not null 
OR EmpSSNStr is not null then
--if user has selected employeen's Nic no to search then
:CrieTeria.QueryString := :CrieTeria.QueryString|| ' and( SSNO like '''||:CrieTeria.SSN||''''||EmpSSNStr||')' ;
--
end if ;
----------------------------------------------------------------------
--
--
if :CrieTeria.TaxNo is not null 
or EmpTaxNoStr is not null then
--
--if user has selected employeen's TaxNo to search then
--
if :CrieTeria.TaxNo is null then
--
EmpTaxNoStr := ltrim(EmpTaxNoStr,',') ;
end if; 
:CrieTeria.QueryString := :CrieTeria.QueryString|| ' and TaxNo in( '||:CrieTeria.TaxNo||EmpTaxNoStr||')' ;
--
end if ; --if :CrieTeria.TaxNo is not null then
--
--Multiple and single date setting in the where clause----
--
if :CrieTeria.DateOfBirthFrom is not null 
or :CrieTeria.DateOfBirthTo is not null
or DateOfBrthToExistFlag is not null then
--
if DateOfBrthToExistFlag is not null
and (:CrieTeria.DateOfBirthFrom is not null 
or :CrieTeria.DateOfBirthTo is not null) then
:CrieTeria.QueryString := :CrieTeria.QueryString|| ' and DoB between '||''''||to_char(:CrieTeria.DateOfBirthFrom,'DD-MON-RRRR')||''''||' and '||''''||to_char(:CrieTeria.DateOfBirthTo,'DD-MON-RRRR')||''''||' or ' ;
elsif DateOfBrthToExistFlag is null then
-- MESSAGE('MESSAGE 3');PAUSE ;
--
:CrieTeria.QueryString := :CrieTeria.QueryString|| ' and DoB between '||''''||to_char(:CrieTeria.DateOfBirthFrom,'DD-MON-RRRR')||''''||' and '||''''||to_char(:CrieTeria.DateOfBirthTo,'DD-MON-RRRR')||'''' ;
elsif DateOfBrthToExistFlag is not null
and :CrieTeria.DateOfBirthFrom is null 
and :CrieTeria.DateOfBirthTo is null then
:CrieTeria.QueryString := :CrieTeria.QueryString|| ' and ' ;
end if ; --if DateOfBrthToExistFlag is not null then

--
for i in 1..VTotalRows loop
--
if Get_Group_Char_Cell('MultSelectionRecGrp.BtnNO',i) = 30 then 
--Employee Date of births from
EmpDateOfBrthFromStr := Get_Group_Char_Cell('MultSelectionRecGrp.ID', i) ; --FRM DATE
--Employee Date of births to
EmpDateOfBrthToStr := Get_Group_Char_Cell('MultSelectionRecGrp.DESCR', i) ; --TO DATE
--
:CrieTeria.QueryString := :CrieTeria.QueryString|| ' DoB between '||''''||to_date(EmpDateOfBrthFromStr,'DD-MM-RRRR')||''''||' and '||''''||to_date(EmpDateOfBrthToStr,'DD-MM-RRRR')||''''||' OR ' ;
end if ;
--
end loop ;
:CrieTeria.QueryString := RTRIM(:CrieTeria.QueryString,' OR ') ;
--
end if ; -- if :CrieTeria.DateOfBirthFrom is not null then
--------------------------end---of--the--Date--of birth--settings-----------------------

------------start--of--Joining--date----logic----------------------
--Multiple and single date setting in the where clause-------------------------------------
--
if :CrieTeria.JoiningDateFrom is not null 
or :CrieTeria.JoiningDateTo is not null
or JoinDateFromToExistFlag is not null then
--
if JoinDateFromToExistFlag is not null
and (:CrieTeria.JoiningDateFrom is not null 
or :CrieTeria.JoiningDateTo is not null) then
:CrieTeria.QueryString := :CrieTeria.QueryString|| ' and JOININGDATE between '||''''||to_char(:CrieTeria.JoiningDateFrom,'DD-MON-RRRR')||''''||' and '||''''||to_date(:CrieTeria.JoiningDateTo,'DD-MM-RRRR')||''''||' or ' ;
elsif JoinDateFromToExistFlag is null then
--
:CrieTeria.QueryString := :CrieTeria.QueryString|| ' and JOININGDATE between '||''''||to_char(:CrieTeria.JoiningDateFrom,'DD-MON-RRRR')||''''||' and '||''''||to_date(:CrieTeria.JoiningDateTo,'DD-MM-RRRR')||'''' ;
elsif JoinDateFromToExistFlag is not null
and :CrieTeria.JoiningDateFrom is null 
and :CrieTeria.JoiningDateTo is null then
:CrieTeria.QueryString := :CrieTeria.QueryString|| ' and ' ;
end if ; --if DateOfBrthToExistFlag is not null then
--
for i in 1..VTotalRows loop
--
if Get_Group_Char_Cell('MultSelectionRecGrp.BtnNO',i) = 31 then 
--Employee JOinig Date 
JoinDateFromStr := Get_Group_Char_Cell('MultSelectionRecGrp.ID', i) ; --FRM DATE
--Employee JOinig Date 
JoinDateToStr := Get_Group_Char_Cell('MultSelectionRecGrp.DESCR', i) ; --TO DATE
--
:CrieTeria.QueryString := :CrieTeria.QueryString|| ' JOININGDATE between '||''''||to_date(JoinDateFromStr,'DD-MM-RRRR')||''''||' and '||''''||to_date(JoinDateToStr,'DD-MM-RRRR')||''''||' OR ' ;
end if ;
--
end loop ;
:CrieTeria.QueryString := RTRIM(:CrieTeria.QueryString,' OR ') ;
--
end if ; -- if :CrieTeria.DateOfBirthFrom is not null then
------------end--of----joining---date-----------------------------------------------------
if :CrieTeria.JoiningDateFrom is not null then
--
--if user has selected employeen's TaxNo to search then
--
:CrieTeria.QueryString := :CrieTeria.QueryString|| ' and JOININGDATE between '||''''||to_date(:CrieTeria.JoiningDateFrom,'DD-MON-RRRR')||''''||' and '||''''||to_date(:CrieTeria.JoiningDateTo,'DD-MON-RRRR')||'''' ;
--
end if ; -- if :CrieTeria.JoiningDateFrom is not null then
--
if :CrieTeria.PraId is not null 
or EmpPrAreaNoStr is not null then
--
--if user has selected employeen's Pay Roll Area to search then
--
--
:CrieTeria.QueryString := :CrieTeria.QueryString|| ' and EmployeeNo in(select e.employeeno 
from HrmpasEmployee e,
HrmPasEmpPra p ,
HrmOpsPra pr
where p.employeeno = e.EmployeeNo 
and p.Pra = pr.Pra
and p.PlanNo = :CrieTeria.PlanNo
and p.Status = 1
and pr.co = :B0.co
and p.co = :B0.co 
and (pr.PraNo like '||''''||:CrieTeria.PraId||''''||EmpPrAreaNoStr||'))' ; 
--
end if ; --if :CrieTeria.PraNo is not null then
-- 
if :CrieTeria.EmpType is not null 
or EmpTypeNoStr is not null then
--
--if user has selected employee's Type to search then
--
pause ;
:CrieTeria.QueryString := :CrieTeria.QueryString|| ' and EMPLOYEENO in(select e.employeeno 
from HrmpasEmployee e,
HrmPasEmpCont t ,
HrmPasEmpTyp tp 
where t.Employeeno = e.EmployeeNo 
and t.PlanNo = :CrieTeria.PlanNo
and t.PASEMPTYP = tp.PasEmpType
and t.Status = 1
and (tp.EMPTYPE like'||''''||:CrieTeria.EmpType||''''||EmpTypeNoStr||'))' ;
--
end if ; --if :CrieTeria.PasEmpType is not null then
--
if :CrieTeria.EMPNAT is not null 
or EmpStatusNoStr is not null then
--
--if user has selected employee's Nature to search then
--
:CrieTeria.QueryString := :CrieTeria.QueryString|| ' and EMPLOYEENO in(select e.employeeno 
from HrmpasEmployee e,
HrmPasEmpNatStat N,
HrmPasEmpStat S 
where s.Employeeno = e.EmployeeNo 
and s.PlanNo = :CrieTeria.PlanNo
and s.Co = :B0.co
and n.Type = ''N'' and s.Status = ''A'' 
and s.PASEMPNAT = n.PASEMPNATSTAT
and (n.EMPNATSTAT like'||''''||:CrieTeria.EMPNAT||''''||EmpStatusNoStr||'))' ;
-- message('Values :'||:CrieTeria.PasEmpNat||EmpStatusNoStr);message(' ');
--
end if ; --if :CrieTeria.PasEmpNat is not null then
-- 
if :CrieTeria.EMPSTAT is not null 
or EmpDeploymentNoStr is not null then
--
--if user has selected employee's Deployement to search then
--

:CrieTeria.QueryString := :CrieTeria.QueryString|| ' and EMPLOYEENO in(select e.employeeno 
from HrmpasEmployee e,
HrmPasEmpNatStat N,
HrmPasEmpStat S 
where s.Employeeno = e.EmployeeNo 
and s.PlanNo = :CrieTeria.PlanNo
and s.Co = :B0.co
and n.Type = ''S''
and s.Status = ''A''
and s.PASEMPSTATUS = n.PASEMPNATSTAT
and (n.EMPNATSTAT like'||''''||:CrieTeria.EMPSTAT||''''||EmpDeploymentNoStr||'))' ;

--
end if ; --if :CrieTeria.PasEmpStat is not null then
-- 
if :CrieTeria.CSTCNTR is not null then
--
--if user has selected employee's CostCenter to search then
--
:CrieTeria.QueryString := :CrieTeria.QueryString|| ' and EMPLOYEENO in(SELECT distinct s.EMPLOYEENO FROM HrmPasEmpOrgStruct s,
HrmOpsPosPlan p,HrmOpsPosCCdtl cd,HrmOpsPosCC cc
where cd.COSTCENTER like '''||:CrieTeria.CSTCNTR||'''
and cc.Status = 1 
and s.Status = 1 
and cd.CcNo = cc.CcNo
and cc.Co = :B0.co
and s.PlanNo = :CrieTeria.PlanNo
and cc.PosPlanNo = p.PosPlanNo
and s.PosNo = p.PosNo )' ;

--
end if ; --if :CrieTeria.CostCenter is not null then
--
if :CrieTeria.ResponsibilityId is not null 
or EmpResponNoStr is not null then
--
--if user has selected employee's Responsibility to search then
-- 
:CrieTeria.QueryString := :CrieTeria.QueryString|| ' and EMPLOYEENO in(SELECT s.EmployeeNo
FROM HrmPasEmpOrgStruct s,
HrmOpsPosPlan p,
HrmOpsPosResp r,
HrmOpsRespGroupDtl d,
HRMOPSRESPONSIBILITIES rp
where s.Status = 1
and s.PlanNo = :CrieTeria.PlanNo
and r.PosPlanNo = p.PosPlanNo
and d.RESPGROUPNO = r.RESPGROUPNO
and d.RESPONSIBILITYNO = rp.RESPONSIBILITYNO
and s.PosNo = p.PosNo
and ( rp.RESPONSIBILITYID like '||''''||:CrieTeria.ResponsibilityId||''''||EmpResponNoStr||'))' ;
--
end if ; --if :CrieTeria.ResponsibilityNo is not null then
--
--
if :CrieTeria.QualificationID is not null 
or EmpQualifNoStr is not null then
--
--if user has selected employee's Qualification to search then
--

:CrieTeria.QueryString := :CrieTeria.QueryString|| ' and EMPLOYEENO in(SELECT EmployeeNo
FROM HrmPasEmpQualification eq,
HRMPASQUALIFICATION q
where eq.QualificationNo = q.QualificationNo
and eq.Status = 1 
and ( QualificationID like '||''''||:CrieTeria.QualificationID||''''||EmpQualifNoStr||'))' ; 
--
end if ; --if :CrieTeria.Qualification is not null then
--
if :CrieTeria.QUALLEVEL is not null 
or EmpQualifLevelNoStr is not null then
--
--if user has selected employee's Qualification Level to search then
--
:CrieTeria.QueryString := :CrieTeria.QueryString|| ' and EMPLOYEENO in(SELECT q.EmployeeNo
FROM HrmPasQualificationLvl l,
HrmPasEmpQualification q
where q.Status = 1 
and q.LevelNo = l.LevelNo
and ( l.LEVELID like '||''''||:CrieTeria.QUALLEVEL||''''||EmpQualifLevelNoStr||'))' ; 

--
end if ; --if :CrieTeria.QualificationLvlNo is not null then
--
if :CrieTeria.ExperienceId is not null 
or EmpExperienceNoStr is not null then
--
--if user has selected employee's Experience to search then
--
:CrieTeria.QueryString := :CrieTeria.QueryString|| ' and EMPLOYEENO in(SELECT EmployeeNo
FROM HrmPasEmpExp ex,
HrmPasExp e
where ex.ExpNo = e.ExpNo 
and ( EXPID like '||''''||:CrieTeria.ExperienceId||''''||EmpExperienceNoStr||'))' ; 

--
end if ; --if :CrieTeria.ExpNo is not null then
--
if :CrieTeria.EXPLEVELID is not null 
or EmpExperienceLevNoStr is not null then
--
--if user has selected employee's Experience Level to search then
--
:CrieTeria.QueryString := :CrieTeria.QueryString|| ' and EMPLOYEENO in(SELECT x.EmployeeNo
FROM HrmPasExpLvl l,
HrmPasEmpExp x
where x.LevelNo = l.LevelNo
and ( LEVELID like '||''''||:CrieTeria.EXPLEVELID||''''||EmpExperienceLevNoStr||'))' ; 

end if ; --if :CrieTeria.ExpLvlNo is not null then
--
if :CrieTeria.SkillID is not null 
or EmpSkillNoStr is not null then
--
--if user has selected employee's Skill to search then
--
:CrieTeria.QueryString := :CrieTeria.QueryString|| ' and EMPLOYEENO in(SELECT EmployeeNo
FROM HrmPasEmpSkill es,
HRMPASSKILL s
where s.Status = 1
and es.SkillNo = s.SkillNo
and ( SkillID like '||''''||:CrieTeria.SkillID||''''||EmpSkillNoStr||'))' ; 

end if ; --if :CrieTeria.SkillNo is not null then
--
if :CrieTeria.SKILLLEVEL is not null 
or EmpSkillLevelNoStr is not null then
--
--if user has selected employee's Experience Level to search then
--
:CrieTeria.QueryString := :CrieTeria.QueryString|| ' and EMPLOYEENO in(SELECT s.EmployeeNo
FROM HrmPasSkillLvl l,
HrmPasEmpSkill s
where s.Status = 1
and s.LevelNo = l.LevelNo
and ( LEVELID like '||''''||:CrieTeria.SKILLLEVEL||''''||EmpSkillLevelNoStr||'))' ; 
--
end if ; --if :CrieTeria.SkilllevelNo is not null then
--
if :CrieTeria.COMPETENCYID is not null
or EmpComptncyNoStr is not null then
--
--if user has selected employee's Competency to search then
-- 
:CrieTeria.QueryString := :CrieTeria.QueryString|| ' and EMPLOYEENO in(SELECT EmployeeNo
FROM HrmPasEmpCompetency cy,
HrmPesComp c
where cy.PESCOMP = c.PESCOMP 
and cy.Status = 1 
and cy.PlanNo = :CrieTeria.PlanNo 
and ( COMP like '||''''||:CrieTeria.COMPETENCYID||''''||EmpComptncyNoStr||'))' ; 

--
end if ; --if :CrieTeria.PesComp is not null then
--

------------------------HrmPasEmpOrgSstruct---------------------------------------------- 
if :CrieTeria.OuId is not null 
or EmpOUNoStr is not null then
--
--if user has selected employeen's org unit no to search then
--
:CrieTeria.QueryStringForOrgStruct := :CrieTeria.QueryStringForOrgStruct|| ' and t.OrgUnitNo in( Select OrgUnitNo from HrmOpsOrgUnit where OrgUnitId like '||''''||:CrieTeria.Ouid||''''||EmpOUNoStr||')' ; 
--
end if ; --if :CrieTeria.OuNo is not null then
--
if :CrieTeria.JobId is not null 
or EmpJobNoStr is not null then
--
--if user has selected employeen's job no to search then
--
:CrieTeria.QueryStringForOrgStruct := :CrieTeria.QueryStringForOrgStruct|| ' and t.JobNo in( Select JobNo from HrmOpsJOb where JObId like '||''''||:CrieTeria.JobId||''''||EmpJobNoStr||')' ; 
--
end if ; -- if :CrieTeria.JobNo is not null then
--
if :CrieTeria.Posid is not null 
or EmpPosNoStr is not null then
--
--if user has selected employeen's position no to search then
--
:CrieTeria.QueryStringForOrgStruct := :CrieTeria.QueryStringForOrgStruct|| ' and t.PosNo in( Select PosNo from HrmOpsPos where Posid like '||''''||:CrieTeria.PosId||''''||EmpPosNoStr||')' ;
-- --
end if ; -- if :CrieTeria.PosNo is not null then
--
-------------------------PasEmpEpstruct------------------------------------
--
--This 'QueryStringForEmpEpStruct' will set the whare clause with HrmPasEmpEpStruct.
--
if :CrieTeria.GroupId is not null 
or EmpGrpNoStr is not null then
--
--if user has selected employeen's Group no to search then
-- 
:CrieTeria.QueryStringForEmpEpStruct := :CrieTeria.QueryStringForEmpEpStruct|| ' and ep.GroupNo in( Select GroupNo from HrmPasGroupMaster where GROUPID like '||''''||:CrieTeria.GroupId||''''||EmpGrpNoStr||')' ;
--
end if ; -- if :CrieTeria.GroupNo is not null 
--
if :CrieTeria.PAID is not null 
or EmpPaNoStr is not null then
--
--if user has selected employeen's Personnal area to search then
--
:CrieTeria.QueryStringForEmpEpStruct := :CrieTeria.QueryStringForEmpEpStruct|| ' and ep.PaNo in( Select PaNo from HrmOpsPa where PAID like '||''''||:CrieTeria.PaId||''''||EmpPaNoStr||')' ;
--
end if ; -- if :CrieTeria.PaNo is not null 
--
if :CrieTeria.BaID is not null 
or EmpBAreaNoStr is not null then 
--
--if user has selected employeen's Business area to search then
--
:CrieTeria.QueryStringForEmpEpStruct := :CrieTeria.QueryStringForEmpEpStruct|| ' and ep.BaNo in( Select BaNo from HrmOpsBa where BaID like '||''''||:CrieTeria.BaId||''''||EmpBAreaNoStr||')' ;
--
end if ; -- if :CrieTeria.BaNo is not null then
--
-------------------------PasEmpEmpTma------------------------------------
-- 
if :CrieTeria.TmaNo is not null 
or EmpTmaNoStr is not null then
--
--if user has selected employeen's Time management area to search then
--
:CrieTeria.QueryStringForTma := :CrieTeria.QueryStringForTma|| ' and tm.TmaNo in( Select TmaNo from HrmOpsTma where TmaID like '||''''||:CrieTeria.TmaId||''''||EmpTmaNoStr||')' ;
--
end if ; -- if :CrieTeria.BaNo is not null then
--
-- 
------------------------------------------------------------------------------------------------------
--Note *** Following code will finally make the whole where clause and set it to Hrmpasemployee table.
--If your where clause depends upon HrmPasEmpEpStruct then add it in the if and Or structure.
------------------------------------------------------------------------------------------------------ 
--
--If one of the criteria depends on HrmpasempEpstruct then assign this statement to the main where clause string else do not.
--
if :CrieTeria.GroupID is not null 
or :CrieTeria.PaId is not null 
or :CrieTeria.BaID is not null 
or EmpGrpNoStr is not null 
or EmpPaNoStr is not null 
or EmpBAreaNoStr is not null then
--
:CrieTeria.QueryString := :CrieTeria.QueryString ||:CrieTeria.QueryStringForEmpEpStruct||')' ;
--
end if ;
--
--If one of the criteria depends on HrmPasEmpOrgStruct then assign this query clause to the main where clause 'QueryString' else do no thing.
--
if :CrieTeria.OuID is not null 
or :CrieTeria.Jobid is not null 
or :CrieTeria.PosId is not null 
or EmpOUNoStr is not null 
or EmpJobNoStr is not null 
or EmpPosNoStr is not null then
:CrieTeria.QueryString := :CrieTeria.QueryString ||:CrieTeria.QueryStringForOrgStruct||')' ;
end if ; --if :CrieTeria.OuNo is not null 
--
--
--If one of the criteria depends on HrmpasEmpTma then assign this statement to the main where clause string else do not.
--
if :CrieTeria.TmaId is not null 
or EmpTmaNoStr is not null then
--
:CrieTeria.QueryString := :CrieTeria.QueryString ||:CrieTeria.QueryStringForTma||')' ;
--
end if ;
--Now finally set the default where of HrmPasEmployee block.
:CrieTeria.QueryString := :CrieTeria.QueryString || ' Order By EmployeeId ' ;
Set_block_property('HRMPASEMPLOYEE',default_where, :CrieTeria.QueryString) ; 
go_block('HRMPASEMPLOYEE') ;
execute_query ;


end ;

PROCEDURE show_report IS

VEmpid  varchar2(32000);
VEmpid1  varchar2(32000);


PLISTID paramlist;
Path    varchar2(300);
Recs		number :=0;

	RgName  VARCHAR2(40) := 'SelectedEmps' ; 
	  RgId    RecordGroup ; 
	  GcId    GroupColumn ; 
	  RecCounter number := 1 ;
	  
			


cursor c1 is
	select p.Descr
	  from MmsProgName b, MmsPath p
	 where b.ProgName = 'PAS820'
	   and b.Type     = 'R'
	   and p.Co       = :B0.Co
	   and b.Path     = p.Path
	   and b.Co       = p.Co ;
	   
	   
	   
BEGIN
	open c1;
	fetch c1 into Path;
	close c1;
	
	/*++++++++++++++++++++++++++++++*/
/*	RgId := Find_Group(RgName); 
	go_block('HRMPASEMPLOYEE');
	First_record ;	
	
	if Not Id_Null(rgid) then
		delete_group(rgid) ;
	end if ;
	--

	 	RgId := Create_Group(RgName, GLOBAL_SCOPE);
	 	GcId := Add_Group_Column(RgId, 'EmpNo', NUMBER_COLUMN); 
	 	  	 	
	  	 	Loop
	
	  	 			 	 Add_Group_Row( RgId, RecCounter) ;
				 	 		 Set_Group_Number_Cell(GcId, RecCounter,:HrmPasEmployee.EMPLOYEENO) ;
				 	 		 RecCounter :=  RecCounter + 1;
													
			exit when :System.Last_record = 'TRUE' ;
			next_record ;  		  	  	 	 					
	  
		 	end loop ;
	
	*/
	
	/*++++++++++++++++++++++++++++++*/
	
	--
	:B0.VEmpid := null;
	
	go_block('HRMPASEMPLOYEE');
	
	first_record;
	loop -- to get the primary key of hrmpasemployees
		:B0.VEmpid := :B0.VEmpid||','||:HrmPasEmployee.EMPLOYEENO;
		Recs := Recs+1;
		exit when :system.LAST_RECORD = 'TRUE';
		next_record;
	end loop;	
	
	
	
	:B0.VEmpid := substr(:B0.VEmpid,2,length(:B0.VEmpid)); -- first is comma to eliminate it
	
	if Recs > 1000 then 
	
	--to get first 1000 employees
	
	VEmpid:=	SUBSTR(:B0.VEmpid,1,INSTR(:B0.VEmpid, ',', 1, 1000)-1);
	
	VEmpid := 'and employeeno in'||'('||VEmpid||')'; -- passing to lexical parameter
		
	--
	-- to get employees next to 1000 
		 
	VEmpid1:=	SUBSTR(:B0.VEmpid,INSTR(:B0.VEmpid, ',', 1,1000)+1,length(:B0.VEmpid));
	
	VEmpid1 := 'or employeeno in'||'('||VEmpid1||')'; -- passing to lexical parameter
	
	elsif Recs = 1000 then
		
	VEmpid:=	SUBSTR(:B0.VEmpid,1,INSTR(:B0.VEmpid, ',', 1, 1000)-1);
	
	VEmpid := 'and employeeno in'||'('||VEmpid||')'; -- passing to lexical parameter	
	
	VEmpid1 := 'or employeeno is null';
	
	elsif Recs < 1000 then
	
	VEmpid:=	SUBSTR(:B0.VEmpid,1,length(:B0.VEmpid));
	
	VEmpid := 'and employeeno in'||'('||VEmpid||')'; -- passing to lexical parameter	
	
	VEmpid1 := 'or employeeno is null' ;
	
	end if;	
	
 PListId := get_parameter_list('Details') ; 

 if not id_null(PListId) then 
		destroy_parameter_list(PListId) ;  
 end if ;   /* not id_null(PListId) then   */


 PListID := create_parameter_list('Details') ;
  
	Add_Parameter(PListID, 'PARAMFORM', TEXT_PARAMETER, 'no');
	add_Parameter(PListID, 'P_CO', text_parameter, :B0.Co) ;
  Add_Parameter(PListID, 'LPATH', text_parameter, :Global.IconPath||'Bestway.gif') ;
  Add_Parameter(PListID, 'EMPNO', TEXT_PARAMETER, VEmpid);
	Add_Parameter(PListID, 'EMPNO1', TEXT_PARAMETER,VEmpid1);	
	

  Run_Product(REPORTS, Path||'pas820.rep', ASYNCHRONOUS, RUNTIME, FILESYSTEM, PListID, NULL);
  
  
END;

SELECT USERENV('sessionid') FROM dual;