FUNCTION employeehasrole
	(
		pempcode     VARCHAR2,
		prolecode    VARCHAR2,
		pproductcode VARCHAR2,
		pcompanyid   NUMBER
	) RETURN BOOLEAN IS
		vcount NUMBER;
	BEGIN
		--see if the employee that is passed to the function has the rolecode
		SELECT COUNT(*)
		INTO   vcount
		FROM   company_employee_role a
		WHERE  cg_rolecode = prolecode
		AND    productcode = pproductcode
		AND    companyid = pcompanyid
		AND    employeecode = pempcode
		AND    casehandle > 0;
		IF vcount > 0
		THEN
			RETURN TRUE;
		ELSE
			RETURN FALSE;
		END IF;
	END;

FUNCTION employeehasrole
(
  pempcode     VARCHAR2,
  prolecode    VARCHAR2,
  pproductcode VARCHAR2,
  pcompanyid   NUMBER
) RETURN BOOLEAN IS

  v_found NUMBER;

BEGIN

  --see if the employee that is passed to the function has the rolecode
  SELECT 1
  INTO   v_found
  FROM   company_employee_role a
  WHERE  cg_rolecode = prolecode
  AND    productcode = pproductcode
  AND    companyid = pcompanyid
  AND    employeecode = pempcode
  AND    casehandle > 0;

  RETURN TRUE;

EXCEPTION 
  WHEN no_data_found THEN
    RETURN FALSE;

  WHEN too_many_rows THEN --(assuming this is possible here)
    RETURN FALSE;

END;

  SELECT 1
  INTO   v_found
  WHERE EXISTS (SELECT null
                FROM   company_employee_role a
                WHERE  cg_rolecode = prolecode
                AND    productcode = pproductcode
                AND    companyid = pcompanyid
                AND    employeecode = pempcode
                AND    casehandle > 0);

-- Create table Total Record in this table is --695
create table LCT_WORKFLOWSTAGE_DTL
(
  LOANSTAGEID       NUMBER(8) not null,
  COMPANYID         NUMBER(8),
  LOANID            NUMBER(8),
  COLLCIFID         NUMBER(8) not null,
  APPLID            NUMBER(8),
  WORKFLOWID        NUMBER(8),
  WORKFLOWSTAGEID   NUMBER(8),
  STAGE             VARCHAR2(8 CHAR) not null,
  DISBURSALSTAGEFLG VARCHAR2(1 CHAR),
  STAGESTATUS       VARCHAR2(1 CHAR),
  STATUSDATE        DATE,
  FINANCIERID       NUMBER(8),
  PRIORITY          NUMBER(3),
  MANDATORY         VARCHAR2(1 CHAR) not null,
  REMARKS           VARCHAR2(200 CHAR),
  MAKERID           VARCHAR2(8 CHAR) not null,
  MAKEDATE          DATE not null,
  STATUS            VARCHAR2(1 CHAR) not null,
  STATUSBUSIDATE    DATE,
  CUSNOTEID         NUMBER(8),
  CG_STATUSREASON   NUMBER(8),
  SEQNO             NUMBER(8),
  STATUSENDDATE     DATE,
  EMPLOYEECODE      VARCHAR2(8 CHAR),
  SUPERVISORCODE    VARCHAR2(8 CHAR),
  APPLICATIONFLAG   VARCHAR2(2 CHAR),
  SETNO             NUMBER(8),
  ISROUTED          VARCHAR2(1 CHAR) default 'N',
  DECISION          VARCHAR2(2 CHAR),
  ACCESSSTATUS      VARCHAR2(1 CHAR),
  SERVICESTATUS     VARCHAR2(1 CHAR),
  REALLOCTYPE       VARCHAR2(1 CHAR),
  REALLOCEMPID      NUMBER(8),
  REALLOCATEDBY     NUMBER,
  REALLOCATEDATE    DATE,
  PARENTLOANSTAGEID NUMBER(8)
)
partition by hash (COMPANYID)
(
  partition SYS_P445
    tablespace ULS_TBS,
  partition SYS_P446
    tablespace ULS_TBS,
  partition SYS_P447
    tablespace ULS_TBS,
  partition SYS_P448
    tablespace ULS_TBS
);
-- Add comments to the table 


  
alter table LCT_WORKFLOWSTAGE_DTL  add constraint FK_LWD1_LOANID foreign key (LOANID)
  references LCT_LOAN_DTL (LOANID);
alter table LCT_WORKFLOWSTAGE_DTL  add constraint FK_LWD_CG_STATUSREASON foreign key (CG_STATUSREASON)
  references COMPANY_GENERIC (GENERICID);
  alter table LCT_WORKFLOWSTAGE_DTL  add constraint FK_LWD_COLLCIFID foreign key (COLLCIFID)
  references LCT_CIF_CUSTOMER (COLLCIFID);
alter table LCT_WORKFLOWSTAGE_DTL  add constraint FK_LWD_COMPANYID foreign key (COMPANYID)
  references COMPANY (COMPANYID);
-- Create/Recreate indexes 
create index CLWSD_NIDX1 on LCT_WORKFLOWSTAGE_DTL (WORKFLOWID, STAGESTATUS, COLLCIFID, EMPLOYEECODE, STATUS, LOANID);
 
create index CLWSD_NIDX2 on LCT_WORKFLOWSTAGE_DTL (COLLCIFID);

create index CLWSD_NIDX3 on LCT_WORKFLOWSTAGE_DTL (COLLCIFID, EMPLOYEECODE, COMPANYID, STAGESTATUS, APPLICATIONFLAG, STATUS, LOANID, STAGE)
;
create index INDX_1 on LCT_WORKFLOWSTAGE_DTL (LOANID, EMPLOYEECODE, STAGE, APPLICATIONFLAG);

create index INDX_LCWKSTDT_001 on LCT_WORKFLOWSTAGE_DTL (COMPANYID, EMPLOYEECODE, STAGESTATUS, STATUS);
 
create index INDX_LWD_EMPLINK on LCT_WORKFLOWSTAGE_DTL (COLLCIFID, STAGESTATUS, APPLICATIONFLAG, PARENTLOANSTAGEID);
create index NDX_LCTWD1 on LCT_WORKFLOWSTAGE_DTL (EMPLOYEECODE);

create index NDX_LCTWD2 on LCT_WORKFLOWSTAGE_DTL (LOANID);


select count(*) from lct_worklist_employee ---nera about records =31;

total record in flt_logic table :--103;