CREATE OR REPLACE PACKAGE pkgworkflow AS

	global_employeeid NUMBER;

	global_pcomp_appl_id NUMBER;

	global_makerid VARCHAR2(200);

	FUNCTION isemployeeblocked
	(
		vcompanyid IN NUMBER,
		v_empid    IN NUMBER,
		v_emprole  IN VARCHAR2
	) RETURN BOOLEAN;

	FUNCTION updateworkflow
	(
		pcompanyid         NUMBER,
		pcomp_appl_id      NUMBER,
		papplid            NUMBER,
		pstage             VARCHAR2,
		pdisbursalstageflg VARCHAR2,
		pfinancierid       NUMBER,
		puserid            VARCHAR2,
		pstagestatus       VARCHAR2,
		pworkflowstageid   OUT NUMBER,
		pseqno             OUT NUMBER,
		pejbname           OUT VARCHAR2,
		pworkflowid        NUMBER DEFAULT NULL,
		papplflag          VARCHAR2 DEFAULT 'O',
		prealloctype       IN NUMBER DEFAULT NULL,
		preallocempid      IN NUMBER DEFAULT NULL
	) RETURN VARCHAR2;

	/*   Fuction updateworkflow is overloaded to add new default parameter - By Anis 17.07.2006 */
	FUNCTION updateworkflow
	(
		pcompanyid         NUMBER,
		pcomp_appl_id      NUMBER,
		papplid            NUMBER,
		pstage             VARCHAR2,
		pdisbursalstageflg VARCHAR2,
		pfinancierid       NUMBER,
		puserid            VARCHAR2,
		pstagestatus       VARCHAR2,
		pworkflowstageid   OUT NUMBER,
		pseqno             OUT NUMBER,
		pejbname           OUT VARCHAR2,
		pemployeeid        IN NUMBER,
		pworkflowid        NUMBER DEFAULT NULL,
		papplflag          VARCHAR2 DEFAULT 'O',
		prealloctype       IN NUMBER DEFAULT NULL,
		preallocempid      IN NUMBER DEFAULT NULL
	) RETURN VARCHAR2;

	/*  End by Anis 17.07.2006 */
	FUNCTION workflowreappeal
	(
		pcomp_appl_id NUMBER,
		pcompanyid    NUMBER,
		preappealid   NUMBER
	) RETURN VARCHAR2;

	FUNCTION workflowreappealreverse
	(
		pcomp_appl_id NUMBER,
		pcompanyid    NUMBER,
		preappealid   NUMBER
	) RETURN VARCHAR2;

	FUNCTION roundrobinallocation
	(
		in_role_code  IN VARCHAR2,
		in_product_cd IN VARCHAR2,
		in_loc_code   IN NUMBER,
		in_company_cd IN NUMBER,
		in_stage_cd   IN VARCHAR2 DEFAULT NULL
	) RETURN VARCHAR2;

	FUNCTION workflowreversal
	(
		fromstage          IN VARCHAR2,
		tostage            IN VARCHAR2,
		in_comp_appl_id    IN NUMBER,
		in_applicationflag IN VARCHAR2 DEFAULT 'O',
		pcompanyid         IN NUMBER
	) RETURN BOOLEAN;

	FUNCTION fnunderwritsalesempallocation
	(
		in_comp_appl_id IN NUMBER,
		in_product      IN VARCHAR2,
		in_companyid    IN NUMBER,
		in_stage        IN VARCHAR2,
		in_loanstageid  IN NUMBER,
		in_role_code    IN VARCHAR2
	) RETURN VARCHAR2;

	PROCEDURE sp_initiateworkflow
	(
		in_comp_appl_id    IN NUMBER,
		in_productcode     IN VARCHAR2,
		in_prodsubtypecode IN VARCHAR2,
		in_applicationflag IN VARCHAR2,
		in_companyid       IN NUMBER,
		in_userid          IN VARCHAR2,
		out_mesg           OUT VARCHAR2
	);

	PROCEDURE sp_getworkflowid
	(
		in_comp_appl_id    IN NUMBER,
		in_productcode     IN VARCHAR2,
		in_prodsubtypecode IN VARCHAR2,
		in_applicationflag IN VARCHAR2,
		in_companyid       IN NUMBER,
		in_userid          IN VARCHAR2,
		out_mesg           OUT VARCHAR2
	);

	FUNCTION fn_tokeniser
	(
		inpstr    VARCHAR2,
		delimiter VARCHAR2 DEFAULT NULL
	) RETURN pkgglobalcursor.rsrefcursor;

	l_cnttt NUMBER := 0;

END;
/
CREATE OR REPLACE PACKAGE BODY pkgworkflow IS

	----------------------------------------Workflow Rejection Check Function-------------------
	-----Created and Updated By Vinay
	--- Check whether any rejection workflow has already been initiated and if not returns the "TRUE" Value

	---001 Added employee filter in Round Robin
	-- 002 If rolecode is null then employee must be null
	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 rejectionreqd
	(
		vcomp_appl_id IN NUMBER,
		vloanstageid  IN NUMBER
	) RETURN BOOLEAN AS
		ltriggeraction workflowstage.triggeraction%TYPE;
		vcount         NUMBER;
	BEGIN
		SELECT triggeraction
		INTO   ltriggeraction
		FROM   lot_workflowstage_dtl l, workflowstage w
		WHERE  l.stage = w.applstage
		AND    l.workflowid = w.workflowid
		AND    l.workflowstageid = w.workflowstageid
		AND    l.loanstageid = vloanstageid;
		SELECT COUNT(*)
		INTO   vcount
		FROM   lot_workflowstage_dtl
		WHERE  comp_appl_id = vcomp_appl_id
		AND    applicationflag = 'RJ';
		IF vcount = 0
			 AND ltriggeraction = 'Y'
		THEN
			RETURN TRUE;
		ELSE
			RETURN FALSE;
		END IF;
	EXCEPTION
		WHEN OTHERS THEN
			insert_error(SQLCODE,
									 SQLERRM,
									 vcomp_appl_id,
									 'Workflow Rejection Initiation',
									 'pkgworkflow-->UpdateWorkflow');
			RETURN FALSE;
	END rejectionreqd;

	------============================================================================
	FUNCTION allowrejection(vcomp_appl_id IN NUMBER) RETURN BOOLEAN AS
		vcountallow     NUMBER;
		vcountinitiated NUMBER;
	BEGIN
		SELECT COUNT(*)
		INTO   vcountallow
		FROM   lot_workflowstage_dtl
		WHERE  comp_appl_id = vcomp_appl_id
		AND    stage = 'REJREQ'
		AND    stagestatus = 'C'
		AND    setno IN (SELECT setno
										 FROM   lot_workflowstage_dtl
										 WHERE  comp_appl_id = vcomp_appl_id
										 AND    stage LIKE 'REJECT%'
										 AND    stagestatus = 'P'
										 AND    applicationflag = 'RJ');
		SELECT COUNT(*)
		INTO   vcountinitiated
		FROM   lot_workflowstage_dtl
		WHERE  comp_appl_id = vcomp_appl_id
		AND    applicationflag = 'RJ';
		IF vcountallow = 0
			 AND vcountinitiated > 0
		THEN
			RETURN TRUE;
		ELSE
			RETURN FALSE;
		END IF;
	EXCEPTION
		WHEN OTHERS THEN
			insert_error(SQLCODE,
									 SQLERRM,
									 vcomp_appl_id,
									 'Workflow Rejection Initiation',
									 'pkgworkflow-->UpdateWorkflow');
			RETURN FALSE;
	END allowrejection;

	-----=======================================Workflow Rejection Check Procedure==============---
	FUNCTION getparentsetno
	(
		vcomp_appl_id IN NUMBER,
		vworkflowid   IN NUMBER,
		vstage        IN VARCHAR2
	) RETURN NUMBER AS
		vsetno lot_workflowstage_dtl.setno%TYPE;
	BEGIN
		/*select setno into vsetno from lot_workflowstage_dtl a, workflowstage w where
      w.workflowid = a.workflowid and
      w.initiateworkflowid  = vworkflowid and
      a.loanstageid = (Select b.loanstageid from lot_workflowstage_dtl b where
                   comp_appl_id = vcomp_appl_id and
                   a.workflowid = b.workflowid and
                   status <> 'X' and
                   b.stagestatus = 'P' and
                   w.applstage = a.stage);
    */
		SELECT setno
		INTO   vsetno
		FROM   lot_workflowstage_dtl
		WHERE  comp_appl_id = vcomp_appl_id
		AND    stagestatus = 'P'
		AND    stage IN (SELECT applstage
										 FROM   workflowstage
										 WHERE  initiateworkflowid = vworkflowid);
		RETURN vsetno;
	EXCEPTION
		WHEN no_data_found THEN
			BEGIN
				SELECT MAX(setno)
				INTO   vsetno
				FROM   lot_workflowstage_dtl
				WHERE  comp_appl_id = vcomp_appl_id
				AND    workflowid = vworkflowid
				AND    stage = vstage
				AND    status <> 'X';
				RETURN NVL(vsetno, -100);
			EXCEPTION
				WHEN no_data_found THEN
					RETURN - 100;
			END;
	END getparentsetno;

	---------==============================================================================
	FUNCTION setparentsetno
	(
		vcomp_appl_id     IN NUMBER,
		vparentworkflowid IN NUMBER,
		vchildworkflowid  IN NUMBER,
		pcompanyid        IN NUMBER
	) RETURN BOOLEAN AS
		vseqno       lot_workflowstage_dtl.seqno%TYPE;
		vsetno       lot_workflowstage_dtl.setno%TYPE;
		vloanstageid lot_workflowstage_dtl.loanstageid%TYPE;
	BEGIN
		BEGIN
			SELECT NVL(setno, 0)
			INTO   vsetno
			FROM   lot_workflowstage_dtl lw, workflowstage wf
			WHERE  lw.comp_appl_id = vcomp_appl_id
			AND    wf.workflowid = vparentworkflowid
			AND    lw.workflowstageid = wf.workflowstageid
			AND    wf.initiateworkflowid = vchildworkflowid
			AND    --lw.stage = wf.applstage and
						 lw.seqno = wf.seqno
			AND    lw.seqno =
						 (SELECT MAX(w.seqno)
							 FROM   workflowstage w, lot_workflowstage_dtl l
							 --                and workflowstageid = (select w.workflowstageid from workflowstage w, lot_workflowstage_dtl l
							 WHERE  l.comp_appl_id = vcomp_appl_id
							 AND    w.workflowid = l.workflowid
							 AND    w.seqno < l.seqno
							 AND    w.applstage <> l.stage
							 AND    w.initiateworkflowid = vchildworkflowid
							 AND    l.workflowid = vparentworkflowid);
		EXCEPTION
			WHEN no_data_found THEN
				vsetno := 0;
		END;
		SELECT MAX(loanstageid)
		INTO   vloanstageid
		FROM   lot_workflowstage_dtl
		WHERE  comp_appl_id = vcomp_appl_id
		AND    workflowid = vparentworkflowid;
		UPDATE lot_workflowstage_dtl
		SET    setno = vsetno + 1
		WHERE  loanstageid = vloanstageid;
		--return vsetno;
		RETURN TRUE;
	EXCEPTION
		WHEN OTHERS THEN
			dbms_output.put_line(SQLERRM);
			RETURN FALSE;
	END setparentsetno;

	------=======================================================================
	-- Change No15: Added by Vinay on 09-02-2004
	-- Purpose: Following function is EmployeeBlocked will check if the employee passed to it is blocked or not. If it is blocked, it will return true else will return false
	FUNCTION isemployeeblocked
	(
		vcompanyid IN NUMBER,
		v_empid    IN NUMBER,
		v_emprole  IN VARCHAR2
	) RETURN BOOLEAN AS
		v_blocktype emp_block.blocktype%TYPE;
		v_role      company_employee_role.cg_rolecode%TYPE;
		v_count     NUMBER(3) := 0;
		CURSOR cur_empblock IS
			SELECT eb.blocktype, cer.cg_rolecode
			FROM   emp_block eb, company_employee_role cer
			WHERE  eb.comempid = cer.comempid(+)
			AND    eb.employeeid = to_number(v_empid)
			AND    to_date(to_char(get_date(vcompanyid), 'mm/dd/yyyy') ||
										 to_char(SYSDATE, 'HH24:MI:SS'),
										 'mm/dd/yyyy HH24:MI:SS') BETWEEN
						 DECODE(eb.blockfromdate,
										 NULL,
										 to_date(to_char(get_date(vcompanyid), 'mm/dd/yyyy') ||
														 to_char(eb.blockfromtime, 'HH24:MI:SS'),
														 'mm/dd/yyyy HH24:MI:SS'),
										 eb.blockfromtime) AND
						 DECODE(eb.blocktodate,
										NULL,
										to_date(to_char(get_date(vcompanyid), 'mm/dd/yyyy') ||
														to_char(eb.blocktotime, 'HH24:MI:SS'),
														'mm/dd/yyyy HH24:MI:SS'),
										eb.blocktotime)
			AND    eb.companyid = vcompanyid
			AND    eb.blockdecision = 'A'
			AND    eb.status != 'X'
			ORDER  BY eb.blocktype;
	BEGIN
		--is the employeeblocked
		SELECT COUNT(*)
		INTO   v_count
		FROM   emp_block e
		WHERE  e.employeeid = to_number(v_empid)
		AND    to_date(to_char(get_date(vcompanyid), 'mm/dd/yyyy') ||
									 to_char(SYSDATE, 'HH24:MI:SS'),
									 'mm/dd/yyyy HH24:MI:SS') BETWEEN
					 DECODE(e.blockfromdate,
									 NULL,
									 to_date(to_char(get_date(vcompanyid), 'mm/dd/yyyy') ||
													 to_char(e.blockfromtime, 'HH24:MI:SS'),
													 'mm/dd/yyyy HH24:MI:SS'),
									 e.blockfromtime) AND
					 DECODE(e.blocktodate,
									NULL,
									to_date(to_char(get_date(vcompanyid), 'mm/dd/yyyy') ||
													to_char(e.blocktotime, 'HH24:MI:SS'),
													'mm/dd/yyyy HH24:MI:SS'),
									e.blocktotime)
		AND    e.companyid = vcompanyid
		AND    e.blockdecision = 'A'
		AND    e.status != 'X';
		IF v_count = 0
		THEN
			RETURN FALSE;
		ELSE
			FOR i IN cur_empblock LOOP
				IF i.blocktype = 'C'
				THEN
					RETURN TRUE;
				ELSIF v_blocktype = 'R'
							AND v_emprole = v_role
				THEN
					RETURN TRUE;
				END IF;
			END LOOP;
		END IF;
		RETURN FALSE;
	EXCEPTION
		WHEN OTHERS THEN
			RETURN FALSE;
	END;

	-----==================================================================
	--Added by Vinay on 06-02-2004_in trying to resolve other workflow functionality
	FUNCTION fnapplflag
	(
		in_workflowid IN NUMBER,
		pcompanyid    IN NUMBER -- Added Companyid filter for Multi-company functionality [50480, 03-DEC-07]
	) RETURN VARCHAR2 AS
		vapplflag workflow.applicationflag%TYPE;
	BEGIN
		SELECT w.applicationflag
		INTO   vapplflag
		FROM   workflow w
		WHERE  w.workflowid = in_workflowid;
		RETURN vapplflag;
	END fnapplflag;

	-----===========================================================================
	--------------Function name Changed to fnUnderwritSalesEmpAllocation by Joseph on 15/4/2004 as the function will be called for SALES allocation as well
	---Parameter in_role_code added by Joseph on 15/4/2004 to distinguish between underwriting and sales allocation
	FUNCTION fnunderwritsalesempallocation
	(
		in_comp_appl_id IN NUMBER,
		in_product      IN VARCHAR2,
		in_companyid    IN NUMBER,
		in_stage        IN VARCHAR2,
		in_loanstageid  IN NUMBER,
		in_role_code    IN VARCHAR2
	) RETURN VARCHAR2 AS
		vempcode        employee.employeeid%TYPE;
		vsupervisorcode company_employee_role.supervisorcode%TYPE;
	BEGIN
		---- Namrata 29/11/2005 ----------------
		IF SUBSTR(UPPER(in_stage), 3, 5) = 'APPRV'
		THEN
			BEGIN
				SELECT DISTINCT employee_id
				INTO   vempcode
				FROM   lot_allocation_dtl lad,
							 company_generic    cg,
							 lot_allocation_hdr lah --Added by Savita @ 29042004
				WHERE  UPPER(cg.VALUE) = SUBSTR(UPPER(in_stage), 1, 2)
				AND    cg.genericid = lad.cg_hierrachylevel
				AND    lad.status != 'X'
				AND    lah.status <> 'X'
				AND    lad.comp_appl_id = in_comp_appl_id
							/** Added by Savita @ 29042004 for checking the role here also as dtl
                                                                                                                                                                                                                                table will have data for other role also*/
							---------------------------------------------------------
				AND    lad.allocation_hdr_id = lah.allocation_hdr_id
				AND    lah.allocationrole = in_role_code;
			EXCEPTION
				WHEN OTHERS THEN
					NULL;
			END;
			dbms_output.put_line('after ******************1');
			---------------------------------------------------------
		ELSE
			BEGIN
				SELECT employee_id
				INTO   vempcode
				FROM   lot_allocation_dtl lad,
							 company_generic    cg,
							 lot_allocation_hdr lah --Added by Savita @ 29042004
				WHERE  UPPER(cg.VALUE) = SUBSTR('L1APPRV', 1, 2)
				AND    cg.genericid = lad.cg_hierrachylevel
				AND    lad.status != 'X'
				AND    lah.status <> 'X'
				AND    lad.comp_appl_id = in_comp_appl_id
							/** Added by Savita @ 29042004 for checking the role here also as dtl
                                                                                                                                                                                                                                table will have data for other role also*/
							---------------------------------------------------------
				AND    lad.allocation_hdr_id = lah.allocation_hdr_id
				AND    lah.allocationrole = in_role_code;
				dbms_output.put_line('after ******************2');
			EXCEPTION
				WHEN OTHERS THEN
					NULL;
			END;
			---------------------------------------------------------
		END IF;
		------------- cg_rolecode is compared with the parameter passed instead of the hardcoding as earlier --Changed on 15/4/2004 by Joseph
		BEGIN
			SELECT supervisorcode
			INTO   vsupervisorcode
			FROM   company_employee_role
			WHERE  cg_rolecode = in_role_code
			AND    productcode = in_product
			AND    companyid = in_companyid
			AND    status <> 'X' -- added by vijayan on 22-11-2006
			AND    employeecode = vempcode;
		EXCEPTION
			WHEN OTHERS THEN
				NULL;
		END;
		dbms_output.put_line('after ******************3');
		UPDATE lot_workflowstage_dtl
		SET    employeecode   = vempcode,
					 supervisorcode = vsupervisorcode
		WHERE  loanstageid = in_loanstageid;
		dbms_output.put_line('after ******************4');
		RETURN 'S';
	EXCEPTION
		WHEN OTHERS THEN
			----Following if block added by Joseph on 15/4/2004 to distinguish between error returned during underwriting and sales allocation
			IF in_role_code = 'CR_BUYER'
			THEN
				RETURN 'Error in finding Underwriting Allocation Employee -> ' || SQLERRM;
			ELSIF in_role_code = 'SLS'
			THEN
				/*return 'Error in finding Sales Allocation Employee -> '||sqlerrm;*/
				RETURN 'S';
			END IF;
	END fnunderwritsalesempallocation;

	-----===========================================================================
	--Added by Vinay on 06-02-2004_in trying to resolve other workflow functionality
	FUNCTION fnsetno
	(
		vcomp_appl_id IN NUMBER,
		vworkflowid   IN NUMBER,
		vstage        IN VARCHAR2
	) RETURN NUMBER AS
		vsetno lot_workflowstage_dtl.setno%TYPE;
	BEGIN
		SELECT MAX(setno)
		INTO   vsetno
		FROM   lot_workflowstage_dtl
		WHERE  comp_appl_id = vcomp_appl_id
		AND    workflowid = vworkflowid
		AND    status <> 'X'
		AND    stage = vstage;
		RETURN vsetno;
	END fnsetno;

	--------=================================================================================
	---Function to check whether the passed stage is the first re-appeal stage in reappeal workflow for the passed comp_appl_id
	--Added by Vinay on 06-02-2004_in trying to resolve other workflow functionality
	FUNCTION isfirstreappealstage
	(
		pcomp_appl_id NUMBER,
		pstage        VARCHAR2
	) RETURN BOOLEAN AS
		vstage               stage.stage%TYPE;
		vwreappealworkflowno lot_reappeal_workflow.reappealworkflowno%TYPE;
	BEGIN
		SELECT MIN(b.reappealworkflowno)
		INTO   vwreappealworkflowno
		FROM   lot_reappeal_hdr a, lot_reappeal_workflow b
		WHERE  a.reappealno = b.reappealno
		AND    a.status = 'T'
		AND    b.reappealstage_yn = 'Y'
		AND    b.comp_appl_id = pcomp_appl_id;
		IF vwreappealworkflowno IS NOT NULL
		THEN
			SELECT lrw.stage
			INTO   vstage
			FROM   lot_reappeal_workflow lrw
			WHERE  lrw.reappealworkflowno = vwreappealworkflowno;
			IF vstage = pstage
			THEN
				RETURN TRUE;
			ELSE
				RETURN FALSE;
			END IF;
		ELSE
			RETURN FALSE;
		END IF;
	END isfirstreappealstage;

	---------===========================================================================
	--This function executes the stored procedure that is passed as a parameter
	FUNCTION execfunction
	(
		fnname        VARCHAR2,
		pcompanyid    VARCHAR2,
		pcomp_appl_id VARCHAR2,
		pstage        VARCHAR2,
		pststatus     VARCHAR2
	) RETURN VARCHAR2 IS
		c             NUMBER;
		n             NUMBER;
		m             VARCHAR2(2000);
		out_msg       VARCHAR2(4000);
		str1          VARCHAR2(200);
		vnoparameters NUMBER(10);
	BEGIN
		---- Archana on 27mar 2006 ---
		--- To identify how many parameters are there in procedure
		-- if it is greater than 6 then 7th parameter is status of current stage
		SELECT COUNT(*)
		INTO   vnoparameters
		FROM   user_arguments
		WHERE  object_name = fnname;
		c := dbms_sql.open_cursor;
		IF vnoparameters = 7
		THEN
			-- 7th parameter is stagestatus
			dbms_sql.parse(c,
										 ' begin ' || fnname || '( ' || pcompanyid || ', ' ||
										 pcomp_appl_id || ', ''' || pstage ||
										 ''', null, null, :out_msg,''' || pststatus ||
										 '''); end; ',
										 dbms_sql.native);
		ELSE
			dbms_sql.parse(c,
										 ' begin ' || fnname || '( ' || pcompanyid || ', ' ||
										 pcomp_appl_id || ', ''' || pstage ||
										 ''', null, null, :out_msg); end; ',
										 dbms_sql.native);
		END IF;
		dbms_sql.bind_variable(c, 'out_msg', out_msg, 2000);
		n := dbms_sql.EXECUTE(c);
		dbms_sql.variable_value(c, 'out_msg', out_msg);
		----------Namrata 14/2/2006-------------------
		insert_error(errid          => pcomp_appl_id,
								 errmsg         => ' Workflow --' || out_msg,
								 applidorcustid => NULL,
								 p_modulename   => fnname,
								 p_programname  => pstage || ' pkgworkflow');
		--IF out_msg = 'S' THEN--COMMENTED BY ABHIJIT ON 23-JAN-2008 TO HANDLE THE
		--RETURNED VALUE
		IF SUBSTR(out_msg, 1, 1) = 'S'
		THEN
			out_msg := NULL;
		END IF;
		dbms_sql.close_cursor(c);
		--     DBMS_OUTPUT.PUT_LINE(fnname || out_msg || 'ERTER');
		RETURN out_msg;
	EXCEPTION
		WHEN OTHERS THEN
			insert_error(SQLCODE,
									 SQLERRM,
									 pcomp_appl_id,
									 'Workflow --> Call function/Procedure',
									 'pkgworkflow --> Execfunction');
			RAISE_APPLICATION_ERROR(-20001, SQLERRM);
			RETURN 'xx';
	END execfunction;

	----===========================================================================
	/* Added new function for round robin allocation of employee by workflow*/
	/* Added by Vinay on 20 June 2004 */
	FUNCTION roundrobinallocation
	(
		in_role_code  IN VARCHAR2,
		in_product_cd IN VARCHAR2,
		in_loc_code   IN NUMBER,
		in_company_cd IN NUMBER,
		in_stage_cd   IN VARCHAR2 DEFAULT NULL
	) RETURN VARCHAR2 AS
		emp_list      VARCHAR2(5000) := '';
		v_cglevel     NUMBER(8);
		v_employee    NUMBER(8);
		vrowcount     NUMBER(8) := 0;
		cnt           NUMBER(4) := 0;
		l_flag        VARCHAR(2);
		vpendingcases NUMBER;
		CURSOR employee_cur IS
			SELECT cer.cg_rolecode,
						 cer.employeecode employee,
						 casehandle,
						 --cer.logicstring,--COMMENTED BY ABHIJIT ON 16042008
						 sf_return_ruledtls('R', cer.rule_id) logicstring, --ADDED BY ABHIJIT ON 16042008
						 --cer.execute_level--COMMENTED BY ABHIJIT ON 16042008
						 sf_return_ruledtls('L', cer.rule_id) execute_level --ADDED BY ABHIJIT ON 16042008
			FROM   company_employee_role cer, employee e
			WHERE  e.employeeid = cer.employeecode -- Added by nikhil
			AND    e.status = cer.status
			AND    cer.status <> 'X'
			AND    cer.cg_rolecode = in_role_code
						-- AND cer.cg_hirlevel = v_cglevel round robin will happen all the employee for this role
			AND    cer.productcode = in_product_cd
			AND    cer.companyid = in_company_cd
			ORDER  BY cer.employeecode;
	BEGIN
		SELECT genericid
		INTO   v_cglevel
		FROM   company_generic
		WHERE  key = 'HIRLEVEL'
		AND    description = 'LEVEL1'
					-- Added Companyid filter for Multi-company functionality [50480, 03-DEC-07]
		AND    sf_chk_cgcompanyid(key, companyid, in_company_cd) = 'Y';
		FOR cur_data IN employee_cur LOOP
			IF in_stage_cd IN ('FBDCSC')
			THEN
				SELECT COUNT(*)
				INTO   vrowcount
				FROM   comp_emp_area
				WHERE  employeecode = cur_data.employee
				AND    productcode = in_product_cd
				AND    companyid = in_company_cd
				AND    status <> 'X'
				AND    cg_locationcode IN
							 (SELECT bp.locationcode
								 FROM   bp bp
								 WHERE  UPPER(bp.remarks) = 'DCSC'
								 AND    bp.status <> 'X');
			ELSE
				SELECT COUNT(*)
				INTO   vrowcount
				FROM   comp_emp_area
				WHERE  employeecode = cur_data.employee
				AND    productcode = in_product_cd
				AND    cg_locationcode = in_loc_code
				AND    companyid = in_company_cd
				AND    status <> 'X';
			END IF;
			IF vrowcount > 0
			THEN
				IF (cur_data.logicstring IS NOT NULL AND
					 cur_data.execute_level IS NOT NULL)
				THEN
					BEGIN
						l_flag := pkgexecstatement.sp_execute_statement(in_company_cd,
																														global_pcomp_appl_id,
																														NULL,
																														NULL,
																														cur_data.logicstring,
																														'AP',
																														'P');
					EXCEPTION
						WHEN OTHERS THEN
							insert_error(SQLCODE,
													 SQLERRM,
													 global_pcomp_appl_id,
													 'company_employee_role rule execution',
													 'company_employee_role rule execution');
							ROLLBACK;
					END;
				ELSE
					l_flag := 'P';
				END IF;
				IF l_flag = 'P'
				THEN
					SELECT COUNT(DISTINCT c.comp_appl_id)
					INTO   vpendingcases
					FROM   lot_workflowstage_dtl a,
								 workflowstage         b,
								 lot_globalloan_t      c
					WHERE  a.status <> 'X'
					AND    a.stagestatus = 'P'
					AND    a.workflowid = b.workflowid
					AND    b.status <> 'X'
					AND    b.rolecode = cur_data.cg_rolecode
					AND    c.comp_appl_id = a.comp_appl_id
					AND    c.productcode = in_product_cd
								/*001 Added employee filter*/
					AND    a.stage = b.applstage
					AND    a.employeecode = cur_data.employee
					/*Added employee filter*/
					
					;
					IF to_number(vpendingcases) < to_number(cur_data.casehandle)
					THEN
						emp_list := emp_list || '|' || cur_data.employee;
						cnt      := cnt + 1;
					END IF;
				END IF;
			END IF;
		END LOOP;
		emp_list   := emp_list || '|';
		v_employee := pkgunderwritingallocation.fnemployeegroup(v_cglevel,
																														cnt,
																														emp_list,
																														'E',
																														in_role_code,
																														in_company_cd);
		RETURN v_employee;
	EXCEPTION
		WHEN OTHERS THEN
			dbms_output.put_line('Sql Error ' || SQLERRM);
			insert_error(SQLCODE,
									 SQLERRM,
									 NULL,
									 'Workflow RoundRobinAllocation',
									 'pkgworkflow -> RoundRobinAllocation');
			RETURN 0;
			ROLLBACK;
	END roundrobinallocation;

	-------------------------------------------------  -------------------------------------------------
	/**
  Added on 21-04-2004 for Allocation to Sales Supervisor of closing entity in stage =MANALCS.
  added prameter productcode 1 on 10-11-2006 by vijayan
  */
	FUNCTION fngetempformanualalloc
	(
		pcompapplid IN lot_workflowstage_dtl.comp_appl_id%TYPE,
		pcompanyid  IN NUMBER,
		productcd   IN VARCHAR2,
		plocationcd IN VARCHAR2,
		poutmsg     OUT VARCHAR2
	) RETURN NUMBER IS
		l_bpid lot_globalloan_t.svc_bpid%TYPE;
		l_emp  company_employee_role.employeecode%TYPE;
		/*---- ommented on 10-11-2006 by vijayan
          CURSOR cursups  IS
             SELECT cer.employeecode, e.employeename, cer.cg_rolecode,
                    cer.cg_hirlevel
               FROM sec_company_group_user sec,
                    company_employee_role cer,
                    employee e
              WHERE sec.userid = e.sec_userid
                AND e.employeecode = cer.employeecode
                --AND cer.cg_rolecode = 'SLS'
                AND cer.cg_rolecode = 'BRNMGR'
                AND fnccg_val (cer.cg_hirlevel) = 'L2'
                AND sec.bpid = l_bpid;
    ---end of comment on 10-11-2006 ----*/
		---- code added on 10-11-2006 by vijayan
		CURSOR cursups IS
			SELECT DISTINCT cer.employeecode,
											e.employeename,
											cer.cg_rolecode,
											cer.cg_hirlevel
			FROM   --- sec_company_group_user is not in use [17102007]
						 --- sec_company_group_user sec,
							company_employee_role cer,
						 comp_emp_area         area,
						 employee              e
			WHERE  /*  cer.companyid  = area.companyid -- Added Companyid filter for Multi-company functionality [50480, 03-DEC-07]
                                                                                                and    area.companyid  = e.companyid -- Added Companyid filter for Multi-company functionality [50480, 03-DEC-07]
                                                                                                and    e.companyid  = pcompanyid -- Added Companyid filter for Multi-company functionality [50480, 03-DEC-07]
                                                                                                and */ -- commencted by nikhil (Multi company changes)
			--- sec_company_group_user is not in use [17102007]
			--- sec.userid = e.sec_userid
			 e.employeecode = cer.employeecode
			 AND    cer.cg_rolecode = 'BRNMGR'
			--AND fnccg_val (cer.cg_hirlevel) = 'L2'
			 AND    e.bpid = l_bpid
			 AND    area.cg_locationcode = plocationcd
			 AND    cer.productcode = productcd
			 AND    cer.status <> 'X' -- added by vijayan on 22-11-2006
			 AND    area.status <> 'X' -- added by vijayan on 16-04-2007
			 AND    area.employeecode = cer.employeecode;
		l_cntemps NUMBER := 0;
		l_emplist VARCHAR2(2000);
	BEGIN
		--get the svc_bpid for the current application
		--find out all the employees for this BP with role=SLS and level=L2
		--do a round robin in case multiple found and return the one employee id
		SELECT svc_bpid
		INTO   l_bpid
		FROM   lot_globalloan_t t
		WHERE  t.comp_appl_id = pcompapplid
		AND    companyid = pcompanyid;
		FOR cursup IN cursups LOOP
			l_emp     := cursup.employeecode;
			l_emplist := l_emplist || '|' || cursup.employeecode;
			l_cntemps := l_cntemps + 1;
		END LOOP;
		l_emplist := l_emplist || '|';
		IF l_cntemps > 1
		THEN
			--roundrobin among these
			insert_error(pcompapplid, 'UW round robin called in WF');
			l_emp := NULL;
			l_emp := to_number(pkgunderwritingallocation.fnemployeegroup(NULL,
																																	 l_cntemps,
																																	 l_emplist,
																																	 'E',
																																	 'SALESSUP',
																																	 pcompanyid));
			RETURN l_emp; --,'N'
		ELSIF l_cntemps = 1
		THEN
			RETURN to_number(SUBSTR(l_emplist, 2, length(l_emplist) - 2));
		ELSE
			dbms_output.put_line(l_cntemps);
			insert_error(SQLCODE,
									 SQLERRM,
									 pcompapplid,
									 'Workflow Employee Allocation',
									 'No Sales Supervisor found for the SVC BP :' || l_bpid);
			poutmsg := 'Workflow Employee Allocation - No Sales Supervisor found for the SVC BP :' ||
								 l_bpid;
			RETURN 0;
		END IF;
	EXCEPTION
		WHEN OTHERS THEN
			insert_error(SQLCODE,
									 SQLERRM,
									 pcompapplid,
									 'Workflow Employee Allocation',
									 'Error Occured while finding Sales Supervisor for the SVC BP :' ||
									 l_bpid);
			poutmsg := 'Workflow Employee Allocation - Error Occured while finding Sales Supervisor for the SVC BP :' ||
								 l_bpid;
			RETURN 0;
	END fngetempformanualalloc;

	-----=====================================================================
	--Function to allocate a particular workflow stage to an employee
	FUNCTION allocateemployee
	(
		ploanstageid NUMBER,
		pcompanyid   NUMBER
	) RETURN VARCHAR2 IS
		vcompapplid      lot_workflowstage_dtl.comp_appl_id%TYPE;
		vworkflowid      lot_workflowstage_dtl.workflowid%TYPE;
		vstage           lot_workflowstage_dtl.stage%TYPE;
		vworkflowstageid lot_workflowstage_dtl.workflowstageid%TYPE;
		vempfound        BOOLEAN := FALSE;
		vseqno           workflowstage.seqno%TYPE;
		vrolecode        workflowstage.rolecode%TYPE;
		vempcode         lot_workflowstage_dtl.employeecode%TYPE;
		vprevstage       workflowstage.prevapplstage%TYPE;
		vloccode         lot_globalloan_t.cg_locationcode%TYPE;
		vprodcode        lot_globalloan_t.productcode%TYPE;
		vtmploccode      comp_emp_area.cg_locationcode%TYPE;
		vrowcount        NUMBER;
		vleastratio      NUMBER := 1;
		vleastratioemp   lot_workflowstage_dtl.employeecode%TYPE;
		vcurrratio       NUMBER;
		vpendingcases    NUMBER;
		vsupervisorcode  lot_workflowstage_dtl.supervisorcode%TYPE;
		-----------Added by Joseph on 14/4/2004 for allocation to svc emp id if the role is sales--------------
		vsvcemployeecode employee.employeecode%TYPE;
		vsrcemployeecode employee.employeecode%TYPE;
		voutmsg          VARCHAR2(2000);
		-----------End of addition on 14/4/2004--------------------------
		vunderwritingallocation VARCHAR2(500);
		psetno                  lot_workflowstage_dtl.setno%TYPE;
		v_allocationtype        VARCHAR2(100);
		v_cau_count             NUMBER; --- Namrata 28/2/2006
		vstaffofstage           workflowstage.staffofstage%TYPE;
		vstagdesc               VARCHAR2(140);
		CURSOR cur_parallel_stages IS
			SELECT rolecode, applstage
			FROM   workflowstage
			WHERE  workflowid = vworkflowid
			AND    companyid = pcompanyid
			AND    seqno = vseqno
			AND    status <> 'X' -- added by vijayan on 22-11-2006
			AND    applstage <> vstage;
		/*     Cursor cur_Prev_stages is
    Select rolecode, applstage from workflowstage where
      workflowid = vworkflowid and
      companyid = pcompanyid and
      seqno < vseqno
      order by seqno;*/ --applstage = vprevstage;
		CURSOR cur_prev_stages IS
			SELECT lw.workflowid, lw.stage, ws.rolecode
			FROM   lot_workflowstage_dtl lw, workflowstage ws
			WHERE  lw.loanstageid < ploanstageid
			AND    lw.comp_appl_id = vcompapplid
			AND    ws.applstage = lw.stage
			AND    ws.workflowid = lw.workflowid
			AND    ws.status <> 'X'
			AND    lw.status <> 'X'
			ORDER  BY loanstageid;
		CURSOR cur_emp_list IS
			SELECT employeecode,
						 casehandle,
						 cg_rolecode,
						 fnccgvalue(cg_hirlevel, 'V') hirlevel
			FROM   company_employee_role a
			WHERE  cg_rolecode = vrolecode
			AND    productcode = vprodcode
			AND    companyid = pcompanyid
			AND    a.status <> 'X'
			AND    casehandle > 0;
		-------------------Function added by Joseph on 14/4/2004 for handling cases where the role code is SALES----------
		--This function takes as input an employee code and returns the superior at level L2
		--if no superior at level L2 is found it returns null
		FUNCTION getl2levelsuperior
		(
			pempcode     VARCHAR2,
			prolecode    VARCHAR2,
			pproductcode VARCHAR2,
			pcompanyid   NUMBER,
			poutmsg      OUT VARCHAR2
		) RETURN VARCHAR2 IS
			vsupervisorcode company_employee_role.supervisorcode%TYPE;
			vlevel          company_generic.key%TYPE;
		BEGIN
			IF poutmsg IS NOT NULL
			THEN
				RETURN NULL;
			END IF;
			BEGIN
				SELECT supervisorcode, fnccgvalue(cg_hirlevel, 'V')
				INTO   vsupervisorcode, vlevel
				FROM   company_employee_role
				WHERE  cg_rolecode = prolecode
				AND    employeecode = pempcode
				AND    productcode = pproductcode
				AND    companyid = pcompanyid
				AND    status <> 'X';
			EXCEPTION
				WHEN no_data_found THEN
					vsupervisorcode := NULL;
				WHEN OTHERS THEN
					poutmsg := ' Error while fetching supervisor code : ' || SQLERRM;
					RETURN NULL;
			END;
			IF vlevel = 'L2'
			THEN
				RETURN pempcode;
			ELSIF vsupervisorcode IS NULL
			THEN
				RETURN NULL;
			ELSE
				RETURN getl2levelsuperior(vsupervisorcode,
																	prolecode,
																	pproductcode,
																	pcompanyid,
																	poutmsg);
			END IF;
		END;
	
		-------------------------End of addition on 14/4/2004----------------------------
		---------------Function added by Joseph on 19/05/2004 to see if the employee selected for allocation belongs to the role code attached to that stage
		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    status <> 'X' -- added by vijayan on 22-11-2006
			AND    casehandle > 0;
			IF vcount > 0
			THEN
				RETURN TRUE;
			ELSE
				RETURN FALSE;
			END IF;
		END;
	
		-------------------------End of addition on 19/05/2004----------------------------
	BEGIN
		--Select required columns for this loanstageid
		SELECT b.comp_appl_id,
					 b.workflowid,
					 b.stage,
					 b.workflowstageid,
					 a.seqno,
					 a.rolecode,
					 a.prevapplstage,
					 c.cg_locationcode,
					 c.productcode,
					 d.employeecode,
					 e.employeecode,
					 a.staffofstage -- Added for CGC By Anis 17.06.2006
		INTO   vcompapplid,
					 vworkflowid,
					 vstage,
					 vworkflowstageid,
					 vseqno,
					 vrolecode,
					 vprevstage,
					 vloccode,
					 vprodcode,
					 vsvcemployeecode,
					 vsrcemployeecode,
					 vstaffofstage -- Added for CGC By Anis 17.06.2006
		FROM   workflowstage         a,
					 lot_workflowstage_dtl b,
					 lot_globalloan_t      c,
					 employee              d,
					 employee              e
		WHERE  c.comp_appl_id = b.comp_appl_id
		AND    a.workflowstageid = b.workflowstageid
		AND    a.companyid = pcompanyid -- Added Companyid filter for Multi-company functionality [50480, 03-DEC-07]
		AND    c.svc_employeeid = d.employeeid(+)
		AND    c.src_employeeid = e.employeeid(+)
		AND    b.status <> 'X'
		AND    a.status <> 'X'
		AND    loanstageid = ploanstageid;
		/* New logic implemented for CGC by Anis 17.07.2006. If vstaffofstage is not null then the procedure will
    pick up the employee who had earlier worked on this stage and assign the same employee to the current
    stage  */
		IF vstaffofstage IS NOT NULL
		THEN
			BEGIN
				SELECT employeecode, supervisorcode
				INTO   vempcode, vsupervisorcode
				FROM   lot_workflowstage_dtl
				WHERE  loanstageid IN
							 (SELECT loanstageid
								FROM   lot_workflowstage_dtl
								WHERE  status <> 'X'
								AND    stage = vstaffofstage
								AND    comp_appl_id = vcompapplid);
				IF vempcode IS NOT NULL
					 AND isemployeeblocked(pcompanyid, vempcode, vrolecode) = FALSE
					 AND
					 employeehasrole(vempcode, vrolecode, vprodcode, pcompanyid) = TRUE
				THEN
					UPDATE lot_workflowstage_dtl
					SET    employeecode   = vempcode,
								 supervisorcode = vsupervisorcode
					WHERE  loanstageid = ploanstageid;
					RETURN '';
				END IF;
			EXCEPTION
				WHEN OTHERS THEN
					RETURN ' Emp may be blocked or does not have the role ' || SQLERRM;
			END;
		END IF; -- Added for CGC by Anis 17.07.2006.
		IF global_employeeid IS NOT NULL
		THEN
			-- Added for CGC by Anis 27.07.2006. Starts
			BEGIN
				SELECT supervisorcode
				INTO   vsupervisorcode
				FROM   company_employee_role
				WHERE  cg_rolecode = vrolecode
				AND    employeecode = global_employeeid
				AND    productcode = vprodcode
				AND    companyid = pcompanyid;
				IF isemployeeblocked(pcompanyid, global_employeeid, vprodcode) =
					 FALSE
					 AND employeehasrole(global_employeeid,
															 vrolecode,
															 vprodcode,
															 pcompanyid) = TRUE
				THEN
					UPDATE lot_workflowstage_dtl
					SET    employeecode   = global_employeeid,
								 supervisorcode = vsupervisorcode
					WHERE  loanstageid = ploanstageid;
					RETURN '';
				END IF;
			EXCEPTION
				WHEN OTHERS THEN
					RETURN ' Emp may be blocked or does not have the role ' || SQLERRM;
			END;
		END IF; -- Added for CGC by Anis 27.07.2006. Starts
		IF LOWER(SUBSTR(vrolecode, 1, 3)) = 'sys'
		THEN
			vempcode := '2';
			UPDATE lot_workflowstage_dtl
			SET    employeecode   = vempcode,
						 supervisorcode = vsupervisorcode
			WHERE  loanstageid = ploanstageid;
			RETURN '';
			-------Next elsif block changed to include 'SALES' role as well
		ELSIF LOWER(vrolecode) IN ('cr_buyer', 'sales')
		THEN
			insert_error(vcompapplid,
									 'reached inside the workflow empl alloc sales cond bfore call to uw allocation',
									 NULL,
									 'Price exception');
			vunderwritingallocation := fnunderwritsalesempallocation(vcompapplid,
																															 vprodcode,
																															 pcompanyid,
																															 vstage,
																															 ploanstageid,
																															 vrolecode);
			insert_error(vcompapplid,
									 'reached inside the workflow empl alloc sales cond after call to uw allocation vUnderwritingallocation= ' ||
									 vunderwritingallocation,
									 NULL,
									 'Price exception');
			IF vunderwritingallocation = 'S'
			THEN
				RETURN '';
			ELSE
				RETURN vunderwritingallocation;
			END IF;
			---------Following elsif blocks added by Joseph on 14/4/2004 for allocation to svcemployeeid/srcemployeeid as per the stage
		ELSIF (((vstage IN ('DUPAPP', 'ALLOCLD', 'MANALCS', 'FB') OR
					UPPER(vrolecode) = 'SLS') AND vstage NOT IN ('CONVLEAD')) OR
					(vstage IN ('CONVLEAD') AND UPPER(vrolecode) = 'SLS'))
					AND pcompanyid = pcompanyid
		THEN
			--         IF vstage = 'DUPAPP' THEN
			--if the stage is Duplicate Application or Duplicate Lead then assign it to the superior belonging to L2 level
			--            vsvcemployeecode := getl2levelsuperior (vsvcemployeecode, vrolecode, vprodcode, pcompanyid, voutmsg);
			------------------------------Namrata 26/12/2005--Start-----------------------------------------
			-- if the stage is DUPAPP or DUPLEAD then application will get allocated to the Branch manager of that particular location who is
			-- having role as DUPMAN assuming there will be only one branch manager for that particular location and BP
			/*--- commented by vijayan on 06-11-2006
               IF vstage = 'DUAPDESC' THEN
                  BEGIN
                       select cer.employeecode
                       into  vsvcemployeecode
                       from  company_employee_role cer , comp_emp_area ca --table comp_emp_area added by vijayan on 12-10-2006
                       where cer.cg_rolecode ='BRNMGR'
                       and   cer.productcode = vprodcode
                       and   cer.status      <> 'X'
                       and   cer.employeecode = ca.employeecode
                       and   cer.productcode  = ca.productcode
                       and   ca.cg_locationcode in (select cg_locationcode
                                                     from lot_globalloan_t lgt
                                                     where lgt.companyid = pcompanyid
                                                     and   lgt.comp_appl_id = vcompapplid );
                  exception when no_data_found then
                      voutmsg := 'Failed in Fetching Employee for DUPLEAD Stage ' || sqlerrm;
                  end;
      end of comment on 06-11-2006 -----*/
			------------------------------Namrata 26/12/2005--end-----------------------------------------
			IF voutmsg IS NOT NULL
			THEN
				RETURN voutmsg;
			END IF;
			-----------------------------Namrata 28/2/2006 start -------------------------------------------
			--   ELSIF vstage = 'FB' then
			IF vstage = 'FB'
			THEN
				BEGIN
					SELECT COUNT(caucode)
					INTO   v_cau_count
					FROM   lot_globalloan_t lgt
					WHERE  lgt.companyid = pcompanyid
					AND    lgt.comp_appl_id = vcompapplid
					AND    caucode IS NOT NULL;
				EXCEPTION
					WHEN OTHERS THEN
						v_cau_count := 0;
				END;
				IF v_cau_count <> 0
				THEN
					BEGIN
						SELECT caucode
						INTO   vsvcemployeecode
						FROM   lot_globalloan_t lgt, comp_emp_area ca
						WHERE  lgt.cg_locationcode = ca.cg_locationcode
						AND    lgt.caucode = ca.employeecode
						AND    lgt.productcode = ca.productcode
						AND    ca.status <> 'X'
						AND    ca.companyid = pcompanyid -- Added Companyid filter for Multi-company functionality [50480, 03-DEC-07]
						AND    lgt.comp_appl_id = vcompapplid
						AND    caucode IS NOT NULL;
					EXCEPTION
						WHEN no_data_found THEN
							vsvcemployeecode := NULL;
					END;
				END IF;
				-----------------------------Namrata 28/2/2006 end -------------------------------------------
				--ELSIF vstage = 'DUPLEAD'   THEN
				--  ELSIF ( vstage = 'DULEDESC' )   THEN
				------------------------------Namrata 26/12/2005--Start-----------------------------------------
				-- if the stage is DUPAPP or DUPLEAD then application will get allocated to the Branch manager of that particular location who is
				-- having role as DUPMAN assuming there will be only one branch manager for that particular location and BP
				/*--- commented by vijayan on 06-11-2006
                    begin
                         select cer.employeecode
                         into  vsvcemployeecode
                         from  company_employee_role cer , comp_emp_area ca  -- table comp_emp_area added by vijayan on 12-10-2006
                        -- where cer.cg_rolecode ='DUPMAN'
                        where cer.cg_rolecode ='BRNMGR'
                         and   cer.productcode = vprodcode
                         and   cer.status      <> 'X'
                         and   cer.employeecode = ca.employeecode
                         and   cer.productcode  = ca.productcode
                         and   ca.cg_locationcode in (select cg_locationcode
                                                       from lot_globalloan_t lgt
                                                       where lgt.companyid = pcompanyid
                                                       and   lgt.comp_appl_id = vcompapplid);
                    exception when no_data_found then
                        voutmsg := 'Failed in Fetching Employee for DUPLEAD Stage ' || sqlerrm;
                    end;
        -- end of comment on 06-11-2006 ------------*/
				------------------------------Namrata 26/12/2005--end-----------------------------------------
				-- Commented By namrata
				/*IF vsvcemployeecode IS NOT NULL  THEN
           vsvcemployeecode :=  getl2levelsuperior (
                                                     vsvcemployeecode,
                                                     vrolecode,
                                                     vprodcode,
                                                     pcompanyid,
                                                     voutmsg
                                                  );
        
           IF voutmsg IS NOT NULL   THEN
              RETURN voutmsg;
           END IF;
        ELSE
           vsvcemployeecode :=   getl2levelsuperior (
                                                       vsrcemployeecode,
                                                       vrolecode,
                                                       vprodcode,
                                                       pcompanyid,
                                                       voutmsg
                                                    );
        
           IF voutmsg IS NOT NULL     THEN
              RETURN voutmsg;
           END IF;
        END IF;*/
			ELSIF vstage IN ('ALLOCLD')
			THEN
				SELECT supervisorcode
				INTO   vsvcemployeecode
				FROM   company_employee_role
				WHERE  cg_rolecode = vrolecode
				AND    
							/*--SM--------------------------------------------------------------------*/
							--employeecode = vsvcemployeecode and
							 employeecode = vsrcemployeecode
				AND    
							/*----------------------------------------------------------------------*/
							 productcode = vprodcode
				AND    status <> 'X' -- added by vijayan on 22-11-2006
				AND    companyid = pcompanyid;
			ELSIF vstage IN ('MANALCS')
			THEN
				/*--SM----21042004 for Manaul allocation of closing staff------------------*/
				--         vsvcemployeecode :=  fngetempformanualalloc (vcompapplid, pcompanyid,voutmsg); --commented on 10-11-2006 by vijayan
				vsvcemployeecode := fngetempformanualalloc(vcompapplid,
																									 pcompanyid,
																									 vprodcode,
																									 vloccode,
																									 voutmsg);
				IF voutmsg IS NOT NULL
				THEN
					RETURN voutmsg;
				END IF;
			ELSIF vstage IN ('AUTOLDAL')
			THEN
				/* System allocation to staff */
				vsvcemployeecode := roundrobinallocation(vrolecode,
																								 vprodcode,
																								 vloccode,
																								 pcompanyid,
																								 vstage);
				UPDATE lot_globalloan_t t
				SET    svc_employeeid = vsvcemployeecode
				WHERE  companyid = pcompanyid
				AND    comp_appl_id = vcompapplid;
				/*--SM----21042004 for Manaul allocation of closing staff------------------*/
			ELSIF UPPER(vrolecode) = 'SLS'
			THEN
				--don't do anything as it has to be allocated to svc employee
				NULL;
			END IF;
			--Select supervisor code
			BEGIN
				SELECT supervisorcode
				INTO   vsupervisorcode
				FROM   company_employee_role
				WHERE  cg_rolecode = vrolecode
				AND    employeecode = vsvcemployeecode
				AND    productcode = vprodcode
				AND    status <> 'X' -- added by vijayan on 22-11-2006
				AND    companyid = pcompanyid;
			EXCEPTION
				WHEN no_data_found THEN
					vsupervisorcode := NULL;
				WHEN OTHERS THEN
					RETURN ' Error while fetching supervisor code : ' || SQLERRM;
			END;
			UPDATE lot_workflowstage_dtl
			SET    employeecode   = vsvcemployeecode,
						 supervisorcode = vsupervisorcode
			WHERE  loanstageid = ploanstageid;
			RETURN '';
			---------- End of additon on 14/4/2004 by Joseph
		ELSE
			--Search for a parallel stage
			FOR x IN cur_parallel_stages LOOP
				IF x.rolecode = vrolecode
				THEN
					--Assign this to the employee of the parallel stage
					BEGIN
						--Chnage No.:10- Added by Vinay on 06-02-2004 for handling multiple other workflow insertions
						SELECT MAX(setno)
						INTO   psetno
						FROM   lot_workflowstage_dtl
						WHERE  comp_appl_id = vcompapplid
						AND    workflowid = vworkflowid
						AND    status <> 'X'
						AND    stage = x.applstage;
						SELECT employeecode, supervisorcode
						INTO   vempcode, vsupervisorcode
						FROM   lot_workflowstage_dtl
						WHERE  comp_appl_id = vcompapplid
						AND    workflowid = vworkflowid
						AND    NVL(setno, -100) = NVL(psetno, -100)
						AND    status <> 'X'
						AND    stage = x.applstage;
					EXCEPTION
						WHEN no_data_found THEN
							vempcode := NULL;
						WHEN OTHERS THEN
							RETURN 'Error while querying lot_workflowstage_dtl for parallel stages : ' || SQLERRM;
					END;
					--if condition changed by Joseph on 19/05/2004 to see if the employee has the role attached
					IF vempcode IS NOT NULL
						 AND isemployeeblocked(pcompanyid, vempcode, vrolecode) = FALSE
						 AND
						 employeehasrole(vempcode, vrolecode, vprodcode, pcompanyid) = TRUE
					THEN
						UPDATE lot_workflowstage_dtl
						SET    employeecode   = vempcode,
									 supervisorcode = vsupervisorcode
						WHERE  loanstageid = ploanstageid;
						vempfound := TRUE;
						RETURN '';
					END IF;
				END IF;
			END LOOP;
			--See if previous stage has same role code
			FOR x IN cur_prev_stages LOOP
				IF x.rolecode = vrolecode
				THEN
					--Assign to the employee of the previous stage
					--Chnage No.:10- Added by Vinay on 06-02-2004 for handling multiple other workflow insertions
					/*Changes done by: Vinay.....Date: 19 July 2004.......
          Reason: Allocation should happen to same employee again if allocation has done earlier for the same role
                             select max(setno) into psetno from lot_workflowstage_dtl
                      where
                      comp_appl_id =  vcompapplid and
                      workflowid = vworkflowid and
                      status <> 'X' and
                      stage = x.applstage;
                             */
					psetno := getparentsetno(vcompapplid, vworkflowid, vstage);
					/*               Select employeecode, supervisorcode into vempcode, vsupervisorcode from lot_workflowstage_dtl where
                         comp_appl_id = vcompapplid and
          --               workflowid = vworkflowid and
          --                nvl(setno,-100)    = nvl(psetno,-100) and
          */
					SELECT DISTINCT employeecode, supervisorcode
					INTO   vempcode, vsupervisorcode
					FROM   lot_workflowstage_dtl lw, workflowstage ws
					WHERE  lw.loanstageid < ploanstageid
					AND    lw.comp_appl_id = vcompapplid
					AND    ws.applstage = lw.stage
					AND    lw.workflowid = x.workflowid
					AND    ws.workflowid = lw.workflowid
					AND    lw.stage = x.stage
					AND    ws.status <> 'X'
					AND    lw.status <> 'X'
					AND    rownum = 1;
					--if condition changed by Joseph on 19/05/2004 to see if the employee has the role attached
					IF vempcode IS NOT NULL
						 AND isemployeeblocked(pcompanyid, vempcode, vrolecode) = FALSE
						 AND
						 employeehasrole(vempcode, vrolecode, vprodcode, pcompanyid) = TRUE
					THEN
						UPDATE lot_workflowstage_dtl
						SET    employeecode   = vempcode,
									 supervisorcode = vsupervisorcode
						WHERE  loanstageid = ploanstageid;
						vempfound := TRUE;
						RETURN '';
					END IF;
				END IF;
			END LOOP;
			IF vrolecode IS NOT NULL
			THEN
				SELECT fnccgvalue(cg_allocation, 'V')
				INTO   v_allocationtype
				FROM   role
				WHERE  rolecode = vrolecode;
				--AND    companyid = pcompanyid;
			END IF;
			--If no assignment has taken place as yet then follow the logic of Least Load Ratio
			IF v_allocationtype = 'RRB'
			THEN
				vleastratioemp := roundrobinallocation(vrolecode,
																							 vprodcode,
																							 vloccode,
																							 pcompanyid,
																							 vstage);
			ELSIF v_allocationtype = 'LLR'
			THEN
				FOR x IN cur_emp_list LOOP
					---Hardcoding to allocate cases only to L1 level employees for Malaysia Companyid 2000
					IF pcompanyid = pcompanyid
						 AND x.hirlevel <> 'L1'
					THEN
						--if not L1 fetch next employee
						NULL;
					ELSE
						IF isemployeeblocked(pcompanyid, x.employeecode, x.cg_rolecode) =
							 FALSE
						THEN
							IF vstage IN ('FBDCSC')
							THEN
								--,'DATACHK') then
								SELECT COUNT(*)
								INTO   vrowcount
								FROM   comp_emp_area
								WHERE  employeecode = x.employeecode
								AND    productcode = vprodcode
								AND    companyid = pcompanyid
								AND    status <> 'X'
								AND    cg_locationcode IN
											 (SELECT bp.locationcode
												 FROM   bp bp
												 WHERE  UPPER(bp.remarks) = 'DCSC'
												 AND    bp.status <> 'X');
							ELSE
								SELECT COUNT(*)
								INTO   vrowcount
								FROM   comp_emp_area
								WHERE  employeecode = x.employeecode
								AND    productcode = vprodcode
								AND    cg_locationcode = vloccode
								AND    companyid = pcompanyid
								AND    status <> 'X';
							END IF;
							--If any rows are returned by the above query then this employee is eligible to process the application
							IF vrowcount > 0
							THEN
								SELECT COUNT(DISTINCT comp_appl_id)
								INTO   vpendingcases
								FROM   lot_workflowstage_dtl
								WHERE  employeecode = x.employeecode
								AND    status <> 'X'
								AND    stagestatus = 'P';
								vcurrratio := vpendingcases / x.casehandle;
								IF vcurrratio <= 1
									 AND vcurrratio < vleastratio
								THEN
									vleastratioemp := x.employeecode;
									vleastratio    := vcurrratio;
								END IF;
							END IF;
						END IF;
					END IF;
				END LOOP;
			END IF;
			IF vleastratioemp IS NOT NULL
				 AND vleastratioemp > 0
			THEN
				--Select supervisor code
				SELECT supervisorcode
				INTO   vsupervisorcode
				FROM   company_employee_role
				WHERE  cg_rolecode = vrolecode
				AND    employeecode = vleastratioemp
				AND    productcode = vprodcode
				AND    status <> 'X' -- added by vijayan on 22-11-2006
				AND    companyid = pcompanyid;
				UPDATE lot_workflowstage_dtl
				SET    employeecode   = vleastratioemp,
							 supervisorcode = vsupervisorcode
				WHERE  loanstageid = ploanstageid;
				vempfound := TRUE;
				RETURN '';
			ELSIF vrolecode IS NOT NULL
			THEN
				SELECT stagedesc INTO vstagdesc FROM stage WHERE stage = vstage;
				RETURN 'No employees found for assignment - Stage : ' || vstagdesc;
			END IF;
			--if execution reaches till here then no employee has been assigned. Let the employee code be null
			--return 'No employees found for assignment - Stage : ' || vstage;
			RETURN '';
		END IF;
	END allocateemployee;

	-----=============================================================================
	--Common procedure used to insert into lot_workflowstage_dtl
	FUNCTION insertlot_workflowstage_dtl
	(
		pcompanyid       NUMBER,
		pcomp_appl_id    NUMBER,
		papplid          NUMBER,
		pworkflowid      NUMBER,
		pworkflowstageid NUMBER,
		pstage           VARCHAR2,
		pstagestatus     VARCHAR2,
		pfinancierid     NUMBER,
		pmandatory       VARCHAR2,
		pmakerid         VARCHAR2,
		pseqno           NUMBER,
		pemployeecode    VARCHAR2,
		pallocationreq   BOOLEAN,
		pejbname         OUT VARCHAR2,
		papplflag        VARCHAR2,
		pafterreappeal   BOOLEAN DEFAULT FALSE
	) RETURN VARCHAR2 AS
		vloanstageid lot_workflowstage_dtl.loanstageid%TYPE;
		--      vspname            VARCHAR2 (255);  commented by vijayan on 21-02-2007
		vspname          stage.postcond_sp%TYPE; -- added by vijayan on 21-02-2007
		out_msg          VARCHAR2(4000);
		vsqlstmt         VARCHAR2(200);
		vseqno           lot_reappeal_workflow.seqno%TYPE;
		vreappealno      lot_reappeal_hdr.reappealno%TYPE;
		vapplicationflag workflow.applicationflag%TYPE;
		vsetno           lot_workflowstage_dtl.setno%TYPE;
		votherworkflowid workflowstage.initiateworkflowid%TYPE;
		vworkflowstageid workflowstage.workflowstageid%TYPE;
		cur              pkgglobalcursor.rsrefcursor;
		fname            VARCHAR2(255);
	BEGIN
		--See if a stage with the new sequence no. has not yet been inserted. This can happen in parallel stages of which one or more are automatic
		BEGIN
			vsetno := getparentsetno(pcomp_appl_id, pworkflowid, pstage);
			SELECT loanstageid
			INTO   vloanstageid
			FROM   lot_workflowstage_dtl
			WHERE  comp_appl_id = pcomp_appl_id
			AND    companyid = pcompanyid
			AND    workflowid = pworkflowid
			AND    NVL(setno, -100) = NVL(vsetno, -100)
			AND    status <> 'X'
			AND    stagestatus = pstagestatus
			AND    stage = pstage;
		EXCEPTION
			WHEN no_data_found THEN
				NULL;
			WHEN OTHERS THEN
				RETURN 'Error while checking for existing stage in lot_workflowstage_dtl : ' || SQLERRM;
		END;
		IF vloanstageid IS NULL
		THEN
			SELECT lotsworkflowstagecmp_seq.NEXTVAL INTO vloanstageid FROM dual;
			IF pafterreappeal = FALSE
			THEN
				IF pstage = 'AUTODRWN'
				THEN
					dbms_output.put_line(pstage || '  ' || pworkflowid || '  ' ||
															 vsetno);
				END IF;
				INSERT INTO lot_workflowstage_dtl
					(loanstageid,
					 companyid,
					 comp_appl_id,
					 applid,
					 workflowid,
					 workflowstageid,
					 stage,
					 disbursalstageflg,
					 stagestatus,
					 statusdate,
					 financierid,
					 priority,
					 mandatory,
					 remarks,
					 makerid,
					 makedate,
					 status,
					 statusbusidate,
					 cusnoteid,
					 cg_statusreason,
					 seqno,
					 statusenddate,
					 employeecode,
					 supervisorcode,
					 applicationflag,
					 setno)
				VALUES
					(vloanstageid,
					 pcompanyid,
					 pcomp_appl_id,
					 NULL,
					 pworkflowid,
					 pworkflowstageid,
					 pstage,
					 'N',
					 pstagestatus,
					 SYSDATE,
					 pfinancierid,
					 NULL,
					 pmandatory,
					 NULL,
					 pmakerid,
					 SYSDATE,
					 'A',
					 SYSDATE,
					 NULL,
					 NULL,
					 pseqno,
					 NULL,
					 pemployeecode,
					 NULL,
					 papplflag,
					 vsetno);
			ELSE
				--Insert with values as in lot_workflow_reappeal
				INSERT INTO lot_workflowstage_dtl
					(loanstageid,
					 companyid,
					 comp_appl_id,
					 applid,
					 workflowid,
					 workflowstageid,
					 stage,
					 disbursalstageflg,
					 stagestatus,
					 statusdate,
					 financierid,
					 priority,
					 mandatory,
					 remarks,
					 makerid,
					 makedate,
					 status,
					 statusbusidate,
					 cusnoteid,
					 cg_statusreason,
					 seqno,
					 statusenddate,
					 employeecode,
					 supervisorcode,
					 applicationflag,
					 setno)
					SELECT vloanstageid,
								 pcompanyid,
								 b.comp_appl_id,
								 NULL,
								 b.workflowid,
								 b.workflowstageid,
								 pstage,
								 'N',
								 b.prevstagestatus,
								 SYSDATE,
								 b.financierid,
								 NULL,
								 b.mandatory,
								 NULL,
								 pmakerid,
								 SYSDATE,
								 'A',
								 SYSDATE,
								 NULL,
								 NULL,
								 b.seqno,
								 NULL,
								 b.employeecode,
								 NULL,
								 papplflag,
								 vsetno
					FROM   lot_reappeal_hdr a, lot_reappeal_workflow b
					WHERE  a.reappealno = b.reappealno
					AND    a.status = 'T'
					AND    b.stage = pstage
					AND    b.workflowid = pworkflowid
					AND    b.comp_appl_id = pcomp_appl_id;
			END IF;
			IF pstagestatus = 'J'
			THEN
				IF rejectionreqd(pcomp_appl_id, vloanstageid)
				THEN
					out_msg := updateworkflow(pcompanyid,
																		pcomp_appl_id,
																		NULL,
																		'INITREJ',
																		NULL,
																		NULL,
																		pmakerid,
																		'C',
																		vworkflowstageid,
																		vseqno,
																		pejbname,
																		--  NULL,--employeeid added 17.07.2006 By Anis
																		NULL,
																		'RJ');
					IF out_msg IS NOT NULL
					THEN
						ROLLBACK;
						RETURN out_msg;
					END IF;
				END IF;
				IF allowrejection(pcomp_appl_id)
				THEN
					out_msg := updateworkflow(pcompanyid,
																		pcomp_appl_id,
																		NULL,
																		'REJREQ',
																		NULL,
																		NULL,
																		pmakerid,
																		'C',
																		vworkflowstageid,
																		vseqno,
																		pejbname,
																		--  NULL,--employeeid added 17.07.2006 By Anis
																		NULL,
																		'RW');
					IF out_msg IS NOT NULL
					THEN
						ROLLBACK;
						RETURN out_msg;
					END IF;
				END IF;
			END IF;
			--If allocation is required then call the allocation procedure
			IF pallocationreq = TRUE
			THEN
				BEGIN
					--Call the allocation procedure only if this stage is not going to initiate another workflow
					SELECT NVL(initiateworkflowid, 0)
					INTO   votherworkflowid
					FROM   workflowstage
					WHERE  workflowid = pworkflowid
					AND    applstage = pstage
					AND    status <> 'X';
				EXCEPTION
					WHEN OTHERS THEN
						ROLLBACK;
						RETURN 'Error while querying for otherworkflow id : ' || SQLERRM;
				END;
				IF votherworkflowid = 0
				THEN
					out_msg := allocateemployee(vloanstageid, pcompanyid);
					IF out_msg IS NOT NULL
					THEN
						ROLLBACK;
						RETURN out_msg;
					END IF;
				END IF;
			END IF;
			---Added the change by Vinay......on 4 August..
			---Reason..... Pre Condition Stored Procedure getting called when Stage is Inserted as Not REquired
			IF pafterreappeal = FALSE
				 AND pstagestatus <> 'R'
			THEN
				--See if any procedure has to be executed on insertion
				BEGIN
					SELECT precond_sp, precond_ejb
					INTO   vspname, pejbname
					FROM   stage
					WHERE  /*companyid  = pcompanyid -- Added Companyid filter for Multi-company functionality [50480, 03-DEC-07]
                                                                                                                                                           and    */
					 stage = pstage;
				EXCEPTION
					WHEN OTHERS THEN
						RETURN 'Error while fetching stored procedure name : ' || SQLERRM;
				END;
				IF vspname IS NOT NULL
				THEN
					BEGIN
						cur := fn_tokeniser(vspname, ','); --added by amit 30/01/2007
						LOOP
							FETCH cur
								INTO fname;
							EXIT WHEN cur%NOTFOUND;
							out_msg := execfunction(fname,
																			pcompanyid,
																			pcomp_appl_id,
																			pstage,
																			pstagestatus);
							IF out_msg IS NOT NULL
							THEN
								ROLLBACK;
								RETURN out_msg || ' error while executing procedure/function ' || fname;
							END IF;
						END LOOP; --added by amit
					EXCEPTION
						WHEN OTHERS THEN
							ROLLBACK;
							RETURN 'Error while excuting the stored procedurte associated with this stage : ' || fname || SQLERRM;
					END;
				END IF;
			END IF;
			--Added by Joseph on 22-02-04
			--If any pre-condition ejb is present the the status of this stage will always be pending as the java program will call the workflow back with the same
			--Stage after executing the java code
			IF pejbname IS NOT NULL
			THEN
				UPDATE lot_workflowstage_dtl
				SET    stagestatus = 'P'
				WHERE  loanstageid = vloanstageid;
			END IF;
			--End of additon on 22-02-04
		END IF;
		--Added by Joseph on 14/4/2004 to update the end date column if the stage is complete
		IF pstagestatus = 'C'
			 AND pejbname IS NULL
		THEN
			UPDATE lot_workflowstage_dtl
			SET    statusenddate = SYSDATE
			WHERE  loanstageid = vloanstageid;
		END IF;
		--end of addition on 14/4/2004 by Joseph
		RETURN '';
	END insertlot_workflowstage_dtl;

	----=====================================================================
	--function to return the employee code given the user id
	FUNCTION getempcode(puserid VARCHAR2) RETURN VARCHAR2 IS
		vempcode employee.employeecode%TYPE;
	BEGIN
		BEGIN
			SELECT employeecode
			INTO   vempcode
			FROM   employee
			WHERE  sec_userid = puserid
			AND    status != 'X';
		EXCEPTION
			WHEN OTHERS THEN
				RETURN NULL;
		END;
		RETURN vempcode;
	END getempcode;

	-----=====================================================================
	--Function will update current stage as completed after executing the stored procedure/ EJB if any associated witht the stage
	--it will also see that
	-- 1) all the previous mandatory stages are completed and
	-- 2) post condition for this stage is satisfied
	FUNCTION updatecurrentstage
	(
		pcompanyid    NUMBER,
		pcomp_appl_id NUMBER,
		pfinancierid  NUMBER,
		pworkflowid   NUMBER,
		pstage        VARCHAR2,
		puserid       VARCHAR2,
		pstagestatus  VARCHAR2,
		pejbname      OUT VARCHAR2,
		papplflag     VARCHAR2
	) RETURN VARCHAR2 AS
		vworkflowid         lot_workflowstage_dtl.workflowid%TYPE;
		vcondstatement      workflowstage.post_condition_rule%TYPE;
		vconditionstatus    VARCHAR2(1);
		vspname             company_generic.description%TYPE;
		vseqno              workflowstage.seqno%TYPE;
		vworkflowstageid    workflowstage.workflowstageid%TYPE;
		vmandatory          workflowstage.mandatory%TYPE;
		vempcode            lot_workflowstage_dtl.employeecode%TYPE;
		out_msg             VARCHAR2(4000);
		vsqlstmt            VARCHAR2(2000);
		vpost_cond_exec_lvl workflowstage.post_cond_exec_lvl%TYPE;
		vlaststageseq       workflowstage.seqno%TYPE;
		vcountpending       NUMBER;
		vprevwkid           workflowstage.workflowid%TYPE;
		vprevwkstage        workflowstage.applstage%TYPE;
		vstagestatus        lot_workflowstage_dtl.stagestatus%TYPE;
		vstagestatusparent  lot_workflowstage_dtl.stagestatus%TYPE;
		vcount              NUMBER;
		vloanstageid        lot_workflowstage_dtl.loanstageid%TYPE;
		vnoofdaysinstage    workflowstage.noofdaysinstage%TYPE;
		vstatusbusidate     lot_workflowstage_dtl.statusbusidate%TYPE;
		vservicestatus      lot_workflowstage_dtl.servicestatus%TYPE;
		vpreconrule         workflowstage.pre_condition_rule%TYPE;
		vpreconexel         workflowstage.pre_cond_exec_lvl%TYPE;
		vautomatic          workflowstage.automaticflag%TYPE;
		vrolecode           workflowstage.rolecode%TYPE;
		--Temp variables to be used as out parameters
		tmpworkflowstageid NUMBER;
		tmpseqno           NUMBER;
		tmpejbname         stage.postcond_ejb%TYPE;
		psetno             lot_workflowstage_dtl.setno%TYPE;
		rsdata             pkgglobalcursor.rsrefcursor;
		l_variable1        VARCHAR2(5000);
		l_variable2        VARCHAR2(5000);
		l_pos1             NUMBER := 1;
		l_pos2             NUMBER := 1;
		i                  NUMBER := 1;
		l_param            global_parameter.parametername%TYPE;
		v_precondrule      workflowstage.pre_condition_rule%TYPE;
		cur                pkgglobalcursor.rsrefcursor;
		fname              VARCHAR2(255);
		l_allowaudittrail  VARCHAR2(1);
	BEGIN
		--Check if previous mandatory stages are completed
		BEGIN
			--workflowid condition added to prevent any Pending workflowid getting selected  from any workflow which does not belongd to currentflow. Useful for other workflowid.
			--Added by Vinay on 06/02/2004
			SELECT MAX(setno)
			INTO   psetno
			FROM   lot_workflowstage_dtl
			WHERE  comp_appl_id = pcomp_appl_id
			AND    workflowid = pworkflowid
			AND    status <> 'X'
			AND    stage = pstage;
			SELECT NVL(MIN(workflowstageid), 0)
			INTO   vworkflowid
			FROM   lot_workflowstage_dtl l
			WHERE  seqno < (SELECT seqno
											FROM   lot_workflowstage_dtl
											WHERE  workflowid = pworkflowid
											AND    stage = pstage
											AND    NVL(setno, -100) = NVL(psetno, -100)
											AND    status <> 'X'
											AND    comp_appl_id = pcomp_appl_id)
			AND    l.workflowid =
						 (SELECT x.workflowid
							 FROM   lot_workflowstage_dtl x
							 WHERE  workflowid = pworkflowid
							 AND    stage = pstage
							 AND    NVL(setno, -100) = NVL(psetno, -100)
							 AND    status <> 'X'
							 AND    comp_appl_id = pcomp_appl_id)
			AND    mandatory = 'Y'
			AND    stagestatus = 'P'
			AND    NVL(setno, -100) = NVL(psetno, -100)
			AND    status <> 'X'
			AND    comp_appl_id = pcomp_appl_id;
		EXCEPTION
			WHEN no_data_found THEN
				NULL;
			WHEN too_many_rows THEN
				ROLLBACK;
				RETURN 'Previous mandatory stages are not yet completed';
			WHEN OTHERS THEN
				ROLLBACK;
				RETURN 'Error while checking mandatory stage completion : ' || SQLERRM;
		END;
		IF vworkflowid <> 0
		THEN
			RETURN 'Previous mandatory stages are not yet completed';
		END IF;
		-- commented by vijayan on 06-04-2007
		--neema 19/3/2007
		--  for insertion into LO audittrail tables
		BEGIN
			global_makerid    := puserid;
			l_allowaudittrail := NULL;
			BEGIN
				SELECT allowaudittrail
				INTO   l_allowaudittrail
				FROM   workflowstage
				WHERE  workflowid = pworkflowid
				AND    applstage = pstage;
			EXCEPTION
				WHEN OTHERS THEN
					l_allowaudittrail := NULL;
			END;
			IF NVL(l_allowaudittrail, 'N') = 'Y'
			THEN
				BEGIN
					sp_insert_audittrailconfig(pcompanyid,
																		 pcomp_appl_id,
																		 pstage,
																		 NULL,
																		 NULL,
																		 out_msg);
				EXCEPTION
					WHEN OTHERS THEN
						NULL;
				END;
				IF out_msg IS NOT NULL
				THEN
					ROLLBACK;
					RETURN 'Error while executing the stored procedurte associated with this stage :  ' || pstage || ' ' || out_msg;
				END IF;
			END IF;
		EXCEPTION
			WHEN OTHERS THEN
				ROLLBACK;
				RETURN 'Error while executing the stored procedurte associated with this stage : ' || fname || SQLERRM;
		END;
		--neema 19/3/2007
		-- end of comment on 06-04-2007 ---
		--check if post condition of this stage is satisfied, Also select the sequence no., mandatory and stage id which is used while inserting a new record
		--precondition rule and automatic flag is also fetched bcoz the first stage may be automatic or not req in cases where a workflow initiates another workflow
		---------Select statement changed by Joseph on 15/4/2004 to include rolecode-------------
		BEGIN
			SELECT sf_return_ruledtls('R', post_cond_rule_id) post_condition_rule,
						 seqno,
						 mandatory,
						 workflowstageid,
						 sf_return_ruledtls('L', post_cond_rule_id) post_cond_exec_lvl,
						 noofdaysinstage,
						 sf_return_ruledtls('R', pre_cond_rule_id) pre_condition_rule,
						 automaticflag,
						 sf_return_ruledtls('L', pre_cond_rule_id) pre_cond_exec_lvl,
						 rolecode
			INTO   vcondstatement,
						 vseqno,
						 vmandatory,
						 vworkflowstageid,
						 vpost_cond_exec_lvl,
						 vnoofdaysinstage,
						 vpreconrule,
						 vautomatic,
						 vpreconexel,
						 vrolecode
			FROM   workflowstage
			WHERE  workflowid = pworkflowid
			AND    applstage = pstage
			AND    status <> 'X'; --added by abhijit on 13-mar-2008
		EXCEPTION
			WHEN OTHERS THEN
				ROLLBACK;
				RETURN 'Error while fetching post_condition_rule : ' || SQLERRM || ' PSTAGE= ' || pstage || ' pworkflowid = ' || pworkflowid;
		END;
		vstagestatus := pstagestatus;
		--if this is the first stage then precondition has to be executed. Also if this is an automatic stage then the status should be set to P
		vconditionstatus := 'F';
		IF vseqno = 1
		THEN
			IF vautomatic = 'Y'
			THEN
				vstagestatus := 'C';
			ELSE
				IF vpreconrule IS NOT NULL
					 AND vstagestatus <> 'J'
				THEN
					IF INSTR(vpreconrule, '#') > 0
					THEN
						WHILE l_pos1 <> 0
									AND l_pos2 <> 0 LOOP
							BEGIN
								SELECT TRIM(SUBSTR(TRIM(vpreconrule),
																	 INSTR(TRIM(vpreconrule), '#', 1, i) + 1,
																	 INSTR(TRIM(vpreconrule), '#', 1, i + 1) -
																	 INSTR(TRIM(vpreconrule), '#', 1, i) - 1)) abc
								INTO   l_param
								FROM   dual;
							EXCEPTION
								WHEN no_data_found THEN
									NULL;
								WHEN OTHERS THEN
									RAISE_APPLICATION_ERROR(-20001, SQLERRM);
							END;
							pkgexecuteparams.sp_execparam(pcompanyid,
																						l_param,
																						pcomp_appl_id,
																						NULL,
																						NULL,
																						rsdata);
							LOOP
								FETCH rsdata
									INTO l_variable1, l_variable2;
								EXIT WHEN rsdata%NOTFOUND;
								IF l_variable2 > 0
								THEN
									BEGIN
										SELECT logicstring
										INTO   v_precondrule
										FROM   flt_logic
										WHERE  logicid = l_variable2;
									EXCEPTION
										WHEN OTHERS THEN
											insert_error(SQLCODE,
																	 SQLERRM,
																	 pcomp_appl_id,
																	 'WORKFLOW-->updatecurrentstage',
																	 'updatecurrentstage-PRECONDRULEPICKUP');
									END;
								END IF;
							END LOOP;
							vconditionstatus := pkgexecstatement.sp_execute_statement(pcompanyid,
																																				pcomp_appl_id,
																																				'',
																																				'WF',
																																				v_precondrule,
																																				'AP',
																																				'P');
							vpreconrule      := REPLACE(vpreconrule,
																					'#' || l_param || '#',
																					'''' || vconditionstatus || '''');
							l_pos1           := INSTR(TRIM(vpreconrule), '#', 1, 1);
							l_pos2           := INSTR(TRIM(vpreconrule), '#', 1, 2);
						END LOOP;
					END IF;
					vconditionstatus := pkgexecstatement.sp_execute_statement(pcompanyid,
																																		pcomp_appl_id,
																																		'',
																																		'WF',
																																		vpreconrule,
																																		'AP',
																																		'P');
					IF vconditionstatus = 'P'
					THEN
						vstagestatus := 'R';
					END IF;
				END IF;
			END IF;
		END IF;
		IF vstagestatus = 'C'
			 OR vstagestatus = 'W'
		THEN
			IF vcondstatement IS NOT NULL
			THEN
				vcondstatement   := vcondstatement;
				vconditionstatus := pkgexecstatement.sp_execute_statement(pcompanyid,
																																	pcomp_appl_id,
																																	'',
																																	'WF',
																																	vcondstatement,
																																	'AP',
																																	'P');
				IF vconditionstatus <> 'P'
				THEN
					ROLLBACK;
					RETURN 'Cannot complete the stage as exit condition is not satisfied';
				END IF;
			END IF;
			--Execute the stored procedure associated with the stage
			BEGIN
				SELECT postcond_sp, postcond_ejb
				INTO   vspname, pejbname
				FROM   stage
				WHERE  /*companyid  = pcompanyid -- Added Companyid filter for Multi-company functionality [50480, 03-DEC-07]
                                                                                                                            and    */
				 stage = pstage;
			EXCEPTION
				WHEN OTHERS THEN
					ROLLBACK;
					RETURN 'Error while fetching stored procedure name : ' || SQLERRM;
			END;
			IF vspname IS NOT NULL
			THEN
				BEGIN
					cur := fn_tokeniser(vspname, ','); --added by amit 30/01/2007
					LOOP
						FETCH cur
							INTO fname;
						EXIT WHEN cur%NOTFOUND;
						out_msg := execfunction(fname,
																		pcompanyid,
																		pcomp_appl_id,
																		pstage,
																		vstagestatus);
						IF out_msg IS NOT NULL
						THEN
							ROLLBACK;
							RETURN out_msg || 'error while executing procedure/function ' || fname;
						END IF;
					END LOOP; --added by amit
				EXCEPTION
					WHEN OTHERS THEN
						ROLLBACK;
						RETURN 'Error while executing the stored procedurte associated with this stage : ' || fname || SQLERRM;
				END;
			END IF;
		END IF;
		--if the stage is not yet present in lot_workflowstage_dtl then insert the row else update it
		BEGIN
			--Added by Vinay on 06-02-2004_in trying to resolve other workflow functionality
			IF isfirstreappealstage(pcomp_appl_id, pstage)
			THEN
				SELECT loanstageid, statusbusidate
				INTO   vloanstageid, vstatusbusidate
				FROM   lot_workflowstage_dtl
				WHERE  comp_appl_id = pcomp_appl_id
				AND    companyid = pcompanyid
				AND    workflowid = pworkflowid
				AND    NVL(setno, -100) = NVL(psetno, -100)
				AND    status <> 'X'
				AND    stage = pstage;
			ELSE
				psetno := getparentsetno(pcomp_appl_id, pworkflowid, pstage);
				--      if isfirstreappealstage(pcomp_appl_id,pstage) then
				SELECT loanstageid
				INTO   vloanstageid
				FROM   lot_workflowstage_dtl
				WHERE  comp_appl_id = pcomp_appl_id
				AND    companyid = pcompanyid
				AND    workflowid = pworkflowid
				AND    NVL(setno, -100) = NVL(psetno, -100)
				AND    status <> 'X'
				AND    stage = pstage;
			END IF;
		EXCEPTION
			WHEN no_data_found THEN
				NULL;
			WHEN OTHERS THEN
				ROLLBACK;
				RETURN 'Error while checking existence of stage : ' || SQLERRM;
		END;
		IF vloanstageid IS NULL
		THEN
			--If there are no entries for this application id in lot_workflowstage_dtl  only then call the function getempcode else let the allocation procedure allocate the case as per the logic
			SELECT COUNT(*)
			INTO   vcount
			FROM   lot_workflowstage_dtl
			WHERE  --applicationflag = nvl(papplflag,'O') and
			 comp_appl_id = pcomp_appl_id
			 AND    status <> 'X';
			--------------------Next If-Then block changed by Joseph on 15/4/2004-------------------------------
			---if the role code for this stage is null then the stage will be allocated to the user who is initiating the workflow
			IF vcount = 0
				 OR vrolecode IS NULL
			THEN
				/*002 If rolecode is null then employee must be null  */
			
				vempcode := getempcode(puserid);
			
				/*002 If rolecode is null then employee must be null  */
				out_msg := insertlot_workflowstage_dtl(pcompanyid,
																							 pcomp_appl_id,
																							 NULL,
																							 pworkflowid,
																							 vworkflowstageid,
																							 pstage,
																							 vstagestatus,
																							 pfinancierid,
																							 vmandatory,
																							 puserid,
																							 vseqno,
																							 vempcode,
																							 FALSE,
																							 pejbname,
																							 papplflag);
				IF out_msg IS NOT NULL
				THEN
					RETURN out_msg;
				END IF;
			ELSE
				out_msg := insertlot_workflowstage_dtl(pcompanyid,
																							 pcomp_appl_id,
																							 NULL,
																							 pworkflowid,
																							 vworkflowstageid,
																							 pstage,
																							 vstagestatus,
																							 pfinancierid,
																							 vmandatory,
																							 puserid,
																							 vseqno,
																							 NULL,
																							 TRUE,
																							 pejbname,
																							 papplflag);
				IF out_msg IS NOT NULL
				THEN
					RETURN out_msg;
				END IF;
			END IF;
		ELSE
			IF vstatusbusidate IS NOT NULL
				 AND vnoofdaysinstage IS NOT NULL
			THEN
				SELECT DECODE(dtdiffhours(vstatusbusidate,
																	SYSDATE,
																	vnoofdaysinstage),
											'1',
											'P',
											'0',
											'F',
											NULL)
				INTO   vservicestatus
				FROM   dual;
			END IF;
			--Update the stage
			UPDATE lot_workflowstage_dtl
			SET    stagestatus   = vstagestatus,
						 statusenddate = SYSDATE,
						 servicestatus = vservicestatus
			WHERE  loanstageid = vloanstageid;
			BEGIN
				IF vstagestatus = 'J'
				THEN
					IF rejectionreqd(pcomp_appl_id, vloanstageid)
					THEN
						out_msg := updateworkflow(pcompanyid,
																			pcomp_appl_id,
																			NULL,
																			'INITREJ',
																			NULL,
																			NULL,
																			puserid,
																			'C',
																			vworkflowstageid,
																			vseqno,
																			pejbname,
																			--         NULL, --employeeid added 17.07.2006 By Anis
																			NULL,
																			'RJ');
						IF out_msg IS NOT NULL
						THEN
							ROLLBACK;
							RETURN out_msg;
						END IF;
					END IF;
					IF allowrejection(pcomp_appl_id)
					THEN
						out_msg := updateworkflow(pcompanyid,
																			pcomp_appl_id,
																			NULL,
																			'REJREQ',
																			NULL,
																			NULL,
																			puserid,
																			'C',
																			vworkflowstageid,
																			vseqno,
																			pejbname,
																			--        NULL, --employeeid added 17.07.2006 By Anis
																			NULL,
																			'RW');
						IF out_msg IS NOT NULL
						THEN
							ROLLBACK;
							RETURN out_msg;
						END IF;
					END IF;
				END IF;
			EXCEPTION
				WHEN OTHERS THEN
					insert_error(SQLCODE,
											 SQLERRM,
											 pcomp_appl_id,
											 'Workflow Rejection Initiation',
											 'pkgworkflow-->UpdateWorkflow');
					ROLLBACK;
					RETURN 'Error occured during procedure execution : ' || SQLERRM;
			END;
		END IF;
		--if this workflowid has been generated from a parent workflow id then the status of the parent workflowid's stage should also be updated
		--See if this is the last stage of this workflowid
		SELECT MAX(seqno)
		INTO   vlaststageseq
		FROM   workflowstage
		WHERE  workflowid = pworkflowid;
		IF vlaststageseq = vseqno
		THEN
			--See that all the stages with this seq no are either complete or not required
			SELECT COUNT(loanstageid)
			INTO   vcountpending
			FROM   lot_workflowstage_dtl
			WHERE  comp_appl_id = pcomp_appl_id
			AND    workflowid = pworkflowid
			AND    status <> 'X'
			AND    (mandatory = 'Y' OR
						seqno IN (SELECT seqno
												FROM   lot_workflowstage_dtl
												WHERE  comp_appl_id = pcomp_appl_id
												AND    workflowid = pworkflowid
												AND    status <> 'X' HAVING COUNT(seqno) = 1
												GROUP  BY seqno))
			AND    stagestatus NOT IN ('C', 'R', 'W');
			--if the above count returns 0 then all the stages are completed for this workflowid
			IF vcountpending = 0
			THEN
				--Select the parent workflowid and the corresponding stage
				BEGIN
					/*                select workflowid, applstage into vprevwkid, vprevwkstage from workflowstage a where
                          initiateworkflowid  = pworkflowid and
                          applstage = (Select stage from lot_workflowstage_dtl b where
                                       comp_appl_i d = pcomp_appl_id and
                                       a.workflowid = b.workflowid and
                                       status <> 'X' and
                                       a.applstage = b.stage);
          
          */
					--Chnage No.:11- Added by Vinay on 06-02-2004 for handling multiple other workflow insertions
					SELECT workflowid, applstage
					INTO   vprevwkid, vprevwkstage
					FROM   workflowstage a
					WHERE  a.companyid = pcompanyid -- Added Companyid filter for Multi-company functionality [50480, 03-DEC-07]
					AND    initiateworkflowid = pworkflowid
					AND    applstage = (SELECT stage
															FROM   lot_workflowstage_dtl b
															WHERE  comp_appl_id = pcomp_appl_id
															AND    a.workflowid = b.workflowid
															AND    status <> 'X'
															AND    b.stagestatus = 'P'
															AND    a.applstage = b.stage);
				EXCEPTION
					WHEN no_data_found THEN
						NULL;
					WHEN OTHERS THEN
						RETURN 'Error while querying for parent workflow id : ' || SQLERRM;
				END;
				--Call the main function to update the stage status of the parent workflow id
				IF vprevwkid IS NOT NULL
				THEN
					--update the stagestatus of the parent workflowid only if it has not yet been updated
					-- Change No15: Added by Vinay on 08-02-2004
					-- Purpose: To Set the Parent Status as Complete if any of the mandatory stages are complete or if all stages of workflow get Not Required then Parent should get marked as Not Required and if any of then gets Rejected, then mark Parent as Rejected.
					SELECT COUNT(*)
					INTO   vcount
					FROM   lot_workflowstage_dtl
					WHERE  comp_appl_id = pcomp_appl_id
					AND    companyid = pcompanyid
					AND    workflowid = pworkflowid
					AND    NVL(setno, -100) = NVL(psetno, -100)
					AND    status <> 'X'
					AND    mandatory = 'Y'
					AND    stagestatus = 'J';
					IF vcount <> 0
					THEN
						--Other Workflow stage got Rejected. Mark the Parent also as Rejected
						vstagestatus := 'J';
					ELSE
						SELECT COUNT(*)
						INTO   vcount
						FROM   lot_workflowstage_dtl
						WHERE  comp_appl_id = pcomp_appl_id
						AND    companyid = pcompanyid
						AND    workflowid = pworkflowid
						AND    NVL(setno, -100) = NVL(psetno, -100)
						AND    status <> 'X'
						AND    mandatory = 'Y'
						AND    stagestatus = 'C';
						IF vcount <> 0
						THEN
							vstagestatus := 'C';
						ELSE
							SELECT COUNT(*)
							INTO   vcount
							FROM   lot_workflowstage_dtl
							WHERE  comp_appl_id = pcomp_appl_id
							AND    companyid = pcompanyid
							AND    workflowid = pworkflowid
							AND    NVL(setno, -100) = NVL(psetno, -100)
							AND    status <> 'X'
							AND    mandatory = 'Y'
							AND    stagestatus = 'W';
							IF vcount <> 0
							THEN
								vstagestatus := 'W';
							ELSE
								SELECT COUNT(*)
								INTO   vcount
								FROM   lot_workflowstage_dtl
								WHERE  comp_appl_id = pcomp_appl_id
								AND    companyid = pcompanyid
								AND    workflowid = pworkflowid
								AND    NVL(setno, -100) = NVL(psetno, -100)
								AND    status <> 'X'
								AND    mandatory = 'Y'
								AND    stagestatus = 'R';
								IF vcount <> 0
								THEN
									vstagestatus := 'R';
								ELSE
									vstagestatus := 'C';
								END IF;
							END IF;
						END IF;
					END IF;
					SELECT stagestatus
					INTO   vstagestatusparent
					FROM   lot_workflowstage_dtl
					WHERE  comp_appl_id = pcomp_appl_id
					AND    companyid = pcompanyid
					AND    workflowid = vprevwkid
					AND    status <> 'X'
					AND    stage = vprevwkstage;
					IF vstagestatusparent = 'P'
					THEN
						out_msg := updateworkflow(pcompanyid,
																			pcomp_appl_id,
																			NULL,
																			vprevwkstage,
																			NULL,
																			NULL,
																			puserid,
																			vstagestatus,
																			tmpworkflowstageid,
																			tmpseqno,
																			tmpejbname,
																			--       NULL, --employeeid added 17.07.2006 By Anis
																			NULL,
																			fnapplflag(vprevwkid,
																								 pcompanyid -- Added Companyid filter for Multi-company functionality [50480, 03-DEC-07]
																								 ));
						IF out_msg IS NOT NULL
						THEN
							ROLLBACK;
							RETURN out_msg;
						END IF;
					END IF;
				END IF;
			END IF;
		END IF;
		RETURN '';
	END updatecurrentstage;

	------===================================================================================
	--Function sees if there is a record in lot_reappeal_hdr wirh reappealstage_yn = 'N'
	--if there is such a record against this stage then return T else N. It returns the error message is soem error has occured
	FUNCTION checkreappeal
	(
		pcomp_appl_id NUMBER,
		pworkflowid   NUMBER,
		pstage        VARCHAR2,
		pprevstatus   OUT VARCHAR2
	) RETURN VARCHAR2 IS
		vreappealseqno lot_reappeal_workflow.reappealno%TYPE;
	BEGIN
		BEGIN
			SELECT a.reappealno, prevstagestatus
			INTO   vreappealseqno, pprevstatus
			FROM   lot_reappeal_hdr a, lot_reappeal_workflow b
			WHERE  a.reappealno = b.reappealno
			AND    a.status = 'T'
			AND    b.reappealstage_yn = 'N'
			AND    b.stage = pstage
			AND    b.workflowid = pworkflowid
			AND    b.comp_appl_id = pcomp_appl_id;
		EXCEPTION
			WHEN no_data_found THEN
				vreappealseqno := NULL;
			WHEN OTHERS THEN
				RETURN 'Error while checking for re-appeal : ' || SQLERRM;
		END;
		IF vreappealseqno IS NOT NULL
		THEN
			RETURN 'T';
		ELSE
			RETURN 'F';
		END IF;
	END checkreappeal;

	------=====================================================================
	FUNCTION searchworkflow
	(
		pcomp_appl_id     NUMBER,
		pcompanyid        NUMBER,
		papplflag         VARCHAR2,
		pignoresubproduct BOOLEAN,
		pelgworkflowid    OUT NUMBER
	) RETURN VARCHAR2 IS
		vproduct      lot_globalloan_t.productcode%TYPE;
		vprodsubtype  lot_globalloan_t.productsubtype%TYPE;
		vworkflowid   workflow.workflowid%TYPE;
		vconstatement global_statement.sqlstatement%TYPE;
		vquery        VARCHAR2(2000);
		TYPE curqry IS REF CURSOR;
		curworkflow      curqry;
		vconditionstatus VARCHAR2(1);
	BEGIN
		SELECT productcode, productsubtype
		INTO   vproduct, vprodsubtype
		FROM   lot_globalloan_t
		WHERE  comp_appl_id = pcomp_appl_id;
		vquery := ' SELECT a.WORKFLOWID, SQLSTATEMENT FROM WORKFLOW a, GLOBAL_STATEMENT b WHERE
					CONDITIONID = STATEMENTID (+) AND
					a.companyid = b.companyid (+) and
					a.COMPANYID = ' || pcompanyid ||
							' AND a.productcode = ''' || vproduct ||
							''' and
					a.STATUS<>''X'' AND
					nvl(b.STATUS, ''Y'') <> ''X''';
		IF vprodsubtype IS NOT NULL
			 AND pignoresubproduct = FALSE
		THEN
			vquery := vquery || ' and PRODSUBTYPECODE = ''' || vprodsubtype || '''';
		ELSE
			vquery := vquery || ' and PRODSUBTYPECODE is null';
		END IF;
		IF papplflag IS NOT NULL
		THEN
			vquery := vquery || ' and a.applicationflag = ''' || papplflag || '''';
		END IF;
		OPEN curworkflow FOR vquery;
		LOOP
			FETCH curworkflow
				INTO vworkflowid, vconstatement;
			EXIT WHEN curworkflow%NOTFOUND;
			IF NVL(vconstatement, '') <> ''
			THEN
				vconstatement := 'Select ''P'' from dual where ' || vconstatement;
				dbms_output.put_line(vconstatement);
				vconditionstatus := pkgexecstatement.sp_execute_statement(pcompanyid,
																																	pcomp_appl_id,
																																	'',
																																	'WF',
																																	vconstatement,
																																	'AP',
																																	'P');
				IF vconditionstatus = 'P'
				THEN
					--See that the eligible workflow id is not yet set bcoz if it is set then a duplicate workflow is found that satisfies the conditions
					IF pelgworkflowid IS NULL
					THEN
						pelgworkflowid := vworkflowid;
					ELSE
						RETURN '251DUPLICATE-WORKFLOW-FOUND';
					END IF;
				END IF;
			ELSE
				--See that the eligible workflow id is not yet set bcoz if it is set then a duplicate workflow is found that satisfies the conditions
				IF pelgworkflowid IS NULL
				THEN
					pelgworkflowid := vworkflowid;
				ELSE
					RETURN '251DUPLICATE-WORKFLOW-FOUND';
				END IF;
			END IF;
		END LOOP;
		CLOSE curworkflow;
		IF pelgworkflowid IS NOT NULL
		THEN
			RETURN '';
		ELSE
			vquery := ' SELECT a.WORKFLOWID, SQLSTATEMENT FROM WORKFLOW a, GLOBAL_STATEMENT b WHERE
					 CONDITIONID = STATEMENTID (+) AND
					 a.companyid = b.companyid (+) and
					 a.COMPANYID = ' || pcompanyid ||
								' AND
--                    = ''' || vproduct ||
								''' and
					 a.STATUS<>''X'' AND
					 nvl(b.STATUS, ''Y'') <> ''X''';
			vquery := vquery || ' and a.productcode is null';
			--------------
			vquery := vquery || ' and a.productcode is null';
			IF papplflag IS NOT NULL
			THEN
				vquery := vquery || ' and a.applicationflag = ''' || papplflag || '''';
			END IF;
			OPEN curworkflow FOR vquery;
			LOOP
				FETCH curworkflow
					INTO vworkflowid, vconstatement;
				EXIT WHEN curworkflow%NOTFOUND;
				IF NVL(vconstatement, '') <> ''
				THEN
					vconstatement := 'Select ''P'' from dual where ' || vconstatement;
					dbms_output.put_line(vconstatement);
					vconditionstatus := pkgexecstatement.sp_execute_statement(pcompanyid,
																																		pcomp_appl_id,
																																		'',
																																		'WF',
																																		vconstatement,
																																		'AP',
																																		'P');
					IF vconditionstatus = 'P'
					THEN
						--See that the eligible workflow id is not yet set bcoz if it is set then a duplicate workflow is found that satisfies the conditions
						IF pelgworkflowid IS NULL
						THEN
							pelgworkflowid := vworkflowid;
						ELSE
							RETURN '251DUPLICATE-WORKFLOW-FOUND';
						END IF;
					END IF;
				ELSE
					--See that the eligible workflow id is not yet set bcoz if it is set then a duplicate workflow is found that satisfies the conditions
					dbms_output.put_line('caught here ' || pelgworkflowid);
					IF pelgworkflowid IS NULL
					THEN
						pelgworkflowid := vworkflowid;
					ELSE
						RETURN '251DUPLICATE-WORKFLOW-FOUND';
					END IF;
				END IF;
			END LOOP;
			CLOSE curworkflow;
			RETURN '';
		END IF;
	END searchworkflow;

	-------=================================================================
	--Main function which will be called to update the workflow
	FUNCTION updateworkflow
	(
		pcompanyid         NUMBER,
		pcomp_appl_id      NUMBER,
		papplid            NUMBER,
		pstage             VARCHAR2,
		pdisbursalstageflg VARCHAR2,
		pfinancierid       NUMBER,
		puserid            VARCHAR2,
		pstagestatus       VARCHAR2,
		pworkflowstageid   OUT NUMBER,
		pseqno             OUT NUMBER,
		pejbname           OUT VARCHAR2,
		pworkflowid        NUMBER DEFAULT NULL,
		papplflag          VARCHAR2 DEFAULT 'O',
		prealloctype       IN NUMBER DEFAULT NULL,
		preallocempid      IN NUMBER DEFAULT NULL
	) RETURN VARCHAR2 IS
		vworkflowid workflow.workflowid%TYPE;
		--Added by Vinay for Rejected Case handling
		--Added on : 24 May 2004
		jworkflowid workflow.workflowid%TYPE;
		---------------------------------------------------------------------
		vconstatement     global_statement.sqlstatement%TYPE;
		velgworkflowid    workflow.workflowid%TYPE;
		vconditionstatus  VARCHAR2(1);
		out_msg           VARCHAR2(2000);
		vseqno            lot_workflowstage_dtl.seqno%TYPE;
		vstagestatus      lot_workflowstage_dtl.stagestatus%TYPE;
		vstagestatusparam lot_workflowstage_dtl.stagestatus%TYPE := pstagestatus;
		--Value of the parameter passed
		vfirststage         BOOLEAN := FALSE;
		vinsertnextstage    BOOLEAN := TRUE;
		vautomatic          workflowstage.automaticflag%TYPE;
		vloanstageid        lot_workflowstage_dtl.loanstageid%TYPE;
		vloanstageidhst     lot_workflowstage_dtl_hst.id%TYPE;
		vcount              NUMBER;
		vinitiateworkflowid workflowstage.initiateworkflowid%TYPE;
		vreappealseqno      lot_reappeal_workflow.seqno%TYPE;
		vreappealno         lot_reappeal_hdr.reappealno%TYPE;
		vreappealworkflowno lot_reappeal_workflow.reappealworkflowno%TYPE;
		vapplflag           workflow.applicationflag%TYPE := papplflag;
		vmandatory          workflowstage.mandatory%TYPE;
		blnreappeal         VARCHAR2(200);
		vprevstatus         lot_reappeal_workflow.prevstagestatus%TYPE;
		vsetno              lot_workflowstage_dtl.setno%TYPE;
		lapplflag           workflow.applicationflag%TYPE;
		vemployeeid         employee.employeeid%TYPE;
		v_preconrule        workflowstage.pre_condition_rule%TYPE;
		rs_data             pkgglobalcursor.rsrefcursor;
		lvariable1          VARCHAR2(2000);
		lvariable2          VARCHAR2(2000);
		l_pos1              NUMBER := 1;
		l_pos2              NUMBER := 1;
		i                   NUMBER := 1;
		l_param             global_parameter.parametername%TYPE;
		v_precondrule       workflowstage.pre_condition_rule%TYPE;
		l_cnt               NUMBER := 0;
		CURSOR curnextstage(pseqno NUMBER, pworkflowid NUMBER) IS
			SELECT applstage,
						 workflowstageid,
						 automaticflag,
						 mandatory,
						 seqno,
						 sf_return_ruledtls('R', pre_cond_rule_id) pre_condition_rule,
						 sf_return_ruledtls('L', pre_cond_rule_id) pre_cond_exec_lvl,
						 initiateworkflowid,
						 precond_ejb
			FROM   workflowstage a, stage b
			WHERE  applstage = stage
			AND    seqno = pseqno
			AND    a.status <> 'X'
			AND    workflowid = pworkflowid
			ORDER  BY automaticflag;
		CURSOR curparallelstage(curworkflowid NUMBER, curseqno NUMBER) IS
			SELECT applstage, mandatory, automaticflag
			FROM   workflowstage
			WHERE  workflowid = curworkflowid
			AND    status <> 'X'
			AND    seqno = curseqno /*and
                                                                                                                                 mandatory = 'Y' */
			;
		/*  cursor curreappealstages (preappealworkflowno number, preappealno number) is
    select COMPANYID, COMP_APPL_ID, applid, workflowid, workflowstageid,
           stage, prevstagestatus, financierid, mandatory, seqno,
           employeecode, supervisorcode, reappealstage_yn
    from lot_reappeal_workflow where
       reappealno = preappealno and
       reappealworkflowno > preappealworkflowno
    order by reappealworkflowno;*/
	BEGIN
		BEGIN
			SELECT employeeid
			INTO   vemployeeid
			FROM   employee
			WHERE  sec_userid = preallocempid
			AND    status <> 'X';
		EXCEPTION
			WHEN OTHERS THEN
				vemployeeid := NULL;
		END;
		--Check if the procedure is being called for reversals
		--it will be called for reversals if status is passed as P and record already exists in lot_workflowstage_dtl with status <> 'P'
		-- insert_error(pcomp_appl_id,'WorkFlow Start - > Company id:'|| pCompanyid ||' Comp_appl_id:'||pComp_appl_id ||' Appl id:'||pApplid||' Stage:'||pStage ||' Disbursalstageflg:'||pDisbursalstageflg||' Financierid:'||pFinancierid ||' UserID:' ||pUserID ||' StageStatus:'||pStageStatus);
		IF pstagestatus = 'P'
		THEN
			--Added by Vinay on 06-02-2004_in trying to resolve other workflow functionality
			IF isfirstreappealstage(pcomp_appl_id, pstage)
			THEN
				BEGIN
					SELECT a.workflowid, loanstageid, a.seqno, automaticflag
					INTO   vworkflowid, vloanstageid, vseqno, vautomatic
					FROM   lot_workflowstage_dtl a, workflowstage b
					WHERE  a.workflowid = b.workflowid
					AND    stage = applstage
					AND    comp_appl_id = pcomp_appl_id
					AND    stage = pstage
					AND    a.status <> 'X'
					AND    b.status <> 'X'
					AND    stagestatus <> 'P';
				EXCEPTION
					WHEN no_data_found THEN
						NULL;
					WHEN OTHERS THEN
						ROLLBACK;
						RETURN 'Error occured while query lot_workflowstage_dtl for reversals : ' || SQLERRM;
				END;
				IF vloanstageid IS NOT NULL
				THEN
					--if this is stage part of a parallel stage then return error message
					--                 vsetno := fnsetno(pcomp_appl_id,vworkflowid, );
					BEGIN
						SELECT COUNT(loanstageid)
						INTO   vcount
						FROM   lot_workflowstage_dtl
						WHERE  workflowid = vworkflowid
						AND    comp_appl_id = pcomp_appl_id
						AND    companyid = pcompanyid
						AND    status <> 'X'
						AND    seqno = vseqno;
					EXCEPTION
						WHEN OTHERS THEN
							ROLLBACK;
							RETURN 'Error occured during check parallel stage check : ' || SQLERRM;
					END;
					IF vcount > 1
					THEN
						ROLLBACK;
						RETURN 'Cannot reverse stage ' || pstage || ' as it is part of a parallel stage';
					END IF;
					-- if this stage is an automatic stage then return error message
					IF vautomatic = 'Y'
					THEN
						ROLLBACK;
						RETURN 'Cannot reverse stage ' || pstage || ' as it is an automatic stage ';
					END IF;
					--Proceed with reversals and terminate
					INSERT INTO lot_workflowstage_dtl_hst
						(id,
						 loanstageid,
						 companyid,
						 comp_appl_id,
						 applid,
						 workflowid,
						 workflowstageid,
						 stage,
						 disbursalstageflg,
						 stagestatus,
						 statusdate,
						 statusbusidate,
						 financierid,
						 priority,
						 mandatory,
						 remarks,
						 makerid,
						 makedate,
						 status,
						 cusnoteid,
						 cg_statusreason,
						 seqno,
						 statusenddate,
						 employeecode,
						 supervisorcode,
						 applicationflag,
						 setno,
						 realloctype,
						 reallocempid)
						SELECT lotsworkflowstagehstcmp_seq.NEXTVAL,
									 loanstageid,
									 companyid,
									 comp_appl_id,
									 applid,
									 workflowid,
									 workflowstageid,
									 stage,
									 disbursalstageflg,
									 stagestatus,
									 statusdate,
									 statusbusidate,
									 financierid,
									 priority,
									 mandatory,
									 remarks,
									 makerid,
									 makedate,
									 status,
									 cusnoteid,
									 cg_statusreason,
									 seqno,
									 statusenddate,
									 employeecode,
									 supervisorcode,
									 applicationflag,
									 setno,
									 realloctype,
									 vemployeeid
						FROM   lot_workflowstage_dtl
						WHERE  loanstageid > vloanstageid
						AND    comp_appl_id = pcomp_appl_id
						AND    status <> 'X'
						AND    companyid = pcompanyid;
					DELETE FROM lot_workflowstage_dtl
					WHERE  loanstageid > vloanstageid
					AND    comp_appl_id = pcomp_appl_id
					AND    companyid = pcompanyid;
					UPDATE lot_workflowstage_dtl
					SET    stagestatus = 'P'
					WHERE  loanstageid = vloanstageid;
					RETURN '';
				END IF;
			END IF;
		END IF;
		--if the procedure is not called for reversals then proceed withthe normal activity
		IF NVL(pworkflowid, 0) = 0
		THEN
			--if record already exists in lot_workflowstage_dtl for this comp_appl_id then
			--   workflow that is to be followed is known,
			--else find the workflow that will be followed
			-- Change No: 12 made by Vinay on 07-02-2004
			--Purpose : To differentiate the workflowid from Main Workflow on the basis of application flag as
			--same stagecode can be a part of different other workflows.
			BEGIN
				SELECT MIN(workflowid)
				INTO   vworkflowid
				FROM   lot_workflowstage_dtl l
				WHERE  comp_appl_id = pcomp_appl_id
				AND    NVL(l.applicationflag, 'O') = NVL(papplflag, 'O')
				AND    status <> 'X'
				AND    stage = pstage;
			EXCEPTION
				WHEN no_data_found THEN
					NULL;
				WHEN OTHERS THEN
					RETURN 'Error while querying lot_workflowstage_dtl to fetch workflow id : ' || SQLERRM;
			END;
			IF vworkflowid IS NULL
			THEN
				--Fetch the workflow id that will be followed
				vfirststage := TRUE;
				out_msg     := searchworkflow(pcomp_appl_id,
																			pcompanyid,
																			vapplflag,
																			FALSE,
																			velgworkflowid);
				IF out_msg IS NOT NULL
				THEN
					RETURN out_msg;
				END IF;
				IF velgworkflowid IS NULL
				THEN
					--if no workflow is found for the Product-sub product combination then ignore the
					--   subproduct and search for a workflow with only product
					out_msg := searchworkflow(pcomp_appl_id,
																		pcompanyid,
																		vapplflag,
																		TRUE,
																		velgworkflowid);
					IF out_msg IS NOT NULL
					THEN
						RETURN out_msg;
					END IF;
				END IF;
				--Still if no workflow is found then return error message
				IF velgworkflowid IS NULL
				THEN
					RETURN 'WORKFLOW NOT FOUND';
				END IF;
				vworkflowid := velgworkflowid;
				--End of workflow id search
			END IF;
		ELSE
			vworkflowid := pworkflowid;
			vfirststage := TRUE;
		END IF;
		--Select sequence no etc. for the current stage
		IF vfirststage = FALSE
		THEN
			BEGIN
				--Change No.:8- Added by Vinay on 06-02-2004 for handling multiple other workflow insertions
				SELECT MAX(setno)
				INTO   vsetno
				FROM   lot_workflowstage_dtl
				WHERE  comp_appl_id = pcomp_appl_id
				AND    companyid = pcompanyid
				AND    workflowid = vworkflowid
				AND    status <> 'X'
				AND    stage = pstage;
				/*            Select a.seqno, a.stagestatus, b.automaticflag, c.applicationflag, b.mandatory into vseqno, vstagestatus, vautomatic, vapplflag, vmandatory from lot_workflowstage_dtl a, workflowstage b, workflow c where
                       a.workflowid = c.workflowid and
                       a.workflowid = b.workflowid and
                       a.stage = b.applstage and
                       a.companyid = b.companyid and
                       a.comp_appl_id = pcomp_appl_id and
                       a.companyid = pcompanyid and
                       a.workflowid = vworkflowid and
                       a.status <> 'X' and
                       b.status <> 'X' and
                       a.stage =  pstage;
        */
				--DBMS_OUTPUT.put_line(pstage||'  '||vworkflowid||'  '||vsetno);
				-- COMMIT;
				SELECT a.seqno,
							 a.stagestatus,
							 b.automaticflag,
							 c.applicationflag,
							 b.mandatory
				INTO   vseqno, vstagestatus, vautomatic, vapplflag, vmandatory
				FROM   lot_workflowstage_dtl a, workflowstage b, workflow c
				WHERE  a.workflowid = c.workflowid
				AND    a.workflowid = b.workflowid
				AND    a.stage = b.applstage
				AND    a.companyid = b.companyid
				AND    a.comp_appl_id = pcomp_appl_id
				AND    a.companyid = pcompanyid
				AND    a.workflowid = vworkflowid
				AND    NVL(setno, -100) = NVL(vsetno, -100)
				AND    a.status <> 'X'
				AND    b.status <> 'X'
				AND    a.stage = pstage;
				--check if the stage has already been processed only if there is no reappeal against it
				blnreappeal := checkreappeal(pcomp_appl_id,
																		 vworkflowid,
																		 pstage,
																		 vprevstatus);
				IF blnreappeal = 'F'
				THEN
					--if the status of the current stage is not P then the stage has already been processed
					IF vstagestatus <> 'P'
					THEN
						IF vstagestatus = 'R'
						THEN
							--See if there is a row in lot_workflowstage_dtl with seqno > current sequence.
							--If such a row is found then the stage has been processed
							SELECT MAX(setno)
							INTO   vsetno
							FROM   lot_workflowstage_dtl
							WHERE  comp_appl_id = pcomp_appl_id
							AND    workflowid = vworkflowid
							AND    status <> 'X'
							AND    stage = pstage;
							SELECT COUNT(*)
							INTO   vcount
							FROM   lot_workflowstage_dtl
							WHERE  comp_appl_id = pcomp_appl_id
							AND    companyid = pcompanyid
							AND    NVL(setno, -100) = NVL(vsetno, -100)
							AND    workflowid = vworkflowid
							AND    status <> 'X'
							AND    seqno > vseqno;
							IF vcount <> 0
							THEN
								ROLLBACK;
								RETURN ' Stage ' || pstage || ' has already been processed for the application ';
							END IF;
						ELSIF vautomatic <> 'Y'
						THEN
							ROLLBACK;
							RETURN ' Stage ' || pstage || ' has already been processed for the application ';
						END IF;
					END IF;
				ELSIF blnreappeal <> 'T'
				THEN
					ROLLBACK;
					RETURN blnreappeal;
				END IF;
			EXCEPTION
				WHEN no_data_found THEN
					ROLLBACK;
					RETURN ' Record not found in lot_workflowstage_dtl ';
				WHEN OTHERS THEN
					ROLLBACK;
					RETURN ' Error while fetching stage status : ' || SQLERRM;
			END;
		ELSE
			vseqno := 1;
		END IF;
		--See if this workflow id has initiated another workflow. if so,
		--then the status of all the stages of the child workflow should be either C or R
		BEGIN
			SELECT initiateworkflowid
			INTO   vinitiateworkflowid
			FROM   workflowstage
			WHERE  workflowid = vworkflowid
			AND    status <> 'X' -- added by vijayan on 22-11-2006
			AND    applstage = pstage;
		EXCEPTION
			WHEN no_data_found THEN
				vinitiateworkflowid := NULL;
			WHEN OTHERS THEN
				ROLLBACK;
				RETURN 'Error while fetching child workflow id : ' || SQLERRM;
		END;
		SELECT COUNT(loanstageid)
		INTO   vcount
		FROM   lot_workflowstage_dtl
		WHERE  comp_appl_id = pcomp_appl_id
		AND    companyid = pcompanyid
		AND    stagestatus IN ('P', 'J', 'D', 'A')
		AND    status <> 'X'
		AND    (mandatory = 'Y' OR
					seqno IN (SELECT seqno
											FROM   lot_workflowstage_dtl
											WHERE  comp_appl_id = pcomp_appl_id
											AND    status <> 'X'
											AND    workflowid = vinitiateworkflowid HAVING
											 COUNT(seqno) = 1
											GROUP  BY seqno))
		AND    workflowid = vinitiateworkflowid;
		IF vcount <> 0
		THEN
			ROLLBACK;
			RETURN 'Cannot proceed with updation of stage ' || pstage || ' because stages of the child workflow have Pending/Rejected status';
		END IF;
		--Call the function to update the current stage as complete
		out_msg := updatecurrentstage(pcompanyid,
																	pcomp_appl_id,
																	pfinancierid,
																	vworkflowid,
																	pstage,
																	puserid,
																	pstagestatus,
																	pejbname,
																	vapplflag);
		IF out_msg IS NOT NULL
		THEN
			ROLLBACK;
			RETURN out_msg;
		END IF;
		--if it is a part of a parallel stage then next stage will be inserted only if all mandatory stages of the parallel stage are completed
		--if the current stage is a non-mandatory stage then the next stage will not be inserted
		--if a stage is marked as non-mandatory but it is a stand-alone stage (not part of a parallel stage), then it is assumed to be mandatory
		SELECT COUNT(*)
		INTO   vcount
		FROM   workflowstage
		WHERE  seqno = vseqno
		AND    workflowid = vworkflowid;
		IF vmandatory = 'N'
			 AND vcount > 1
		THEN
			vinsertnextstage := FALSE;
		ELSE
			FOR x IN curparallelstage(vworkflowid, vseqno) LOOP
				--Fetch the stagestatus from lot_workflow_dtl
				BEGIN
					--Chnage No.:8- Added by Vinay on 06-02-2004 for handling multiple other workflow insertions
					vsetno := getparentsetno(pcomp_appl_id, vworkflowid, x.applstage);
					IF vsetno IS NOT NULL
						 AND vsetno > 1
					THEN
						SELECT stagestatus
						INTO   vstagestatus
						FROM   lot_workflowstage_dtl
						WHERE  comp_appl_id = pcomp_appl_id
						AND    workflowid = vworkflowid
						AND    status <> 'X'
						AND    setno = vsetno
						AND    --           stagestatus not in ('C','R') and
									 stage = x.applstage;
					ELSE
						SELECT stagestatus
						INTO   vstagestatus
						FROM   lot_workflowstage_dtl
						WHERE  comp_appl_id = pcomp_appl_id
						AND    workflowid = vworkflowid
						AND    status <> 'X'
						AND    stage = x.applstage;
						/*SELECT stagestatus  INTO vstagestatus
             FROM lot_workflowstage_dtl A,
            (SELECT MAX(LOANSTAGEID) LOANSTAGEID
                    FROM LOT_WORKFLOWSTAGE_DTL
                    WHERE comp_appl_id =  pcomp_appl_id
                      AND workflowid = vworkflowid
                      AND status <> 'X'
                      AND stage = x.applstage) B
            WHERE A.LOANSTAGEID = B.LOANSTAGEID ;
            */
					END IF;
				EXCEPTION
					WHEN no_data_found THEN
						IF x.automaticflag = 'Y'
						THEN
							vinsertnextstage := FALSE;
							EXIT;
						END IF;
						IF x.mandatory = 'Y'
						THEN
							vinsertnextstage := FALSE;
							EXIT;
						END IF;
					WHEN OTHERS THEN
						RETURN 'Error while fetching status of parallel stage from lot_workflowstage_dtl : ' || SQLERRM;
				END;
				IF vstagestatus = 'J'
					 OR vstagestatus = 'D'
					 OR vstagestatus = 'A'
					 OR (vstagestatus = 'P' AND x.mandatory = 'Y')
				THEN
					vinsertnextstage := FALSE;
					EXIT;
				END IF;
			END LOOP;
		END IF;
		--    if nvl(pworkflowid, 0) <> 0 or pstagestatus = 'P' then
		--if all the stges for the given seq no are 'C' then reset vstagestatusparam to 'C'
		SELECT COUNT(*)
		INTO   vcount
		FROM   lot_workflowstage_dtl
		WHERE  comp_appl_id = pcomp_appl_id
		AND    workflowid = vworkflowid
		AND    seqno = vseqno
		AND    stagestatus <> 'C'
		AND    status <> 'X';
		IF vcount = 0
		THEN
			vstagestatusparam := 'C';
		END IF;
		IF vstagestatusparam = 'P'
		THEN
			vinsertnextstage := FALSE;
		END IF;
		---if all the stages for the given seq no are present in lot_workflow_stage_dtl
		IF vinsertnextstage = TRUE
			 AND
			 (pstagestatus <> 'J' OR pstagestatus <> 'A' OR pstagestatus <> 'D')
		THEN
			--Fetch the new stage
			FOR x IN curnextstage(vseqno + 1, vworkflowid) LOOP
				vconditionstatus := 'F';
				--See if there is a reappeal against this stage
				--if so then record should be inserted in lot_workflowstage_dtl with status as in lot_reapeal_workflow
				--Change No:13 :- DOne by Vinay on 07-02-2004
				--Purpose: TO update the Application Flag of the Insert Stage in LOT_WORKFLOWSTAGE_DTL with it's application to identify each different workflow seperately
				blnreappeal := checkreappeal(pcomp_appl_id,
																		 vworkflowid,
																		 x.applstage,
																		 vprevstatus);
				IF blnreappeal = 'T'
				THEN
					out_msg := insertlot_workflowstage_dtl(pcompanyid,
																								 pcomp_appl_id,
																								 papplid,
																								 vworkflowid,
																								 x.workflowstageid,
																								 x.applstage,
																								 +vprevstatus,
																								 pfinancierid,
																								 x.mandatory,
																								 puserid,
																								 x.seqno,
																								 NULL,
																								 FALSE,
																								 pejbname,
																								 NVL(papplflag,
																										 fnapplflag(vworkflowid,
																																pcompanyid -- Added Companyid filter for Multi-company functionality [50480, 03-DEC-07]
																																)),
																								 TRUE);
					IF out_msg IS NOT NULL
					THEN
						RETURN out_msg;
					END IF;
					pworkflowstageid := x.workflowstageid;
					pseqno           := x.seqno;
					--Call the update procedure again with the new stage
					out_msg := updateworkflow(pcompanyid,
																		pcomp_appl_id,
																		papplid,
																		x.applstage,
																		pdisbursalstageflg,
																		pfinancierid,
																		puserid,
																		vprevstatus,
																		pworkflowstageid,
																		pseqno,
																		pejbname,
																		NULL,
																		fnapplflag(vworkflowid,
																							 pcompanyid -- Added Companyid filter for Multi-company functionality [50480, 03-DEC-07]
																							 ));
					IF out_msg IS NOT NULL
					THEN
						RETURN out_msg;
					END IF;
					IF x.initiateworkflowid IS NOT NULL
					THEN
						--Get the 1st stage of the new workflowid and call updateworkflow with this stage and this workflow id
						IF x.initiateworkflowid > 0
						THEN
							IF setparentsetno(pcomp_appl_id,
																vworkflowid,
																x.initiateworkflowid,
																pcompanyid)
							THEN
								FOR y IN curparallelstage(x.initiateworkflowid, 1) LOOP
									blnreappeal := checkreappeal(pcomp_appl_id,
																							 x.initiateworkflowid,
																							 y.applstage,
																							 vprevstatus);
									IF blnreappeal = 'T'
									THEN
										out_msg := updateworkflow(pcompanyid,
																							pcomp_appl_id,
																							papplid,
																							y.applstage,
																							pdisbursalstageflg,
																							pfinancierid,
																							puserid,
																							'C',
																							pworkflowstageid,
																							pseqno,
																							pejbname,
																							x.initiateworkflowid,
																							fnapplflag(x.initiateworkflowid,
																												 pcompanyid -- Added Companyid filter for Multi-company functionality [50480, 03-DEC-07]
																												 ));
									ELSIF blnreappeal = 'F'
									THEN
										out_msg := updateworkflow(pcompanyid,
																							pcomp_appl_id,
																							papplid,
																							y.applstage,
																							pdisbursalstageflg,
																							pfinancierid,
																							puserid,
																							'P',
																							pworkflowstageid,
																							pseqno,
																							pejbname,
																							x.initiateworkflowid,
																							fnapplflag(x.initiateworkflowid,
																												 pcompanyid -- Added Companyid filter for Multi-company functionality [50480, 03-DEC-07]
																												 ));
									ELSE
										out_msg := blnreappeal;
									END IF;
									IF out_msg IS NOT NULL
									THEN
										RETURN out_msg;
									END IF;
								END LOOP;
							ELSE
								ROLLBACK;
								RETURN 'Parent Setno Function failed';
							END IF;
						END IF;
					END IF;
				ELSIF blnreappeal = 'F'
				THEN
					--No reppeal against this stage
					IF x.pre_condition_rule IS NOT NULL
					THEN
						v_preconrule := x.pre_condition_rule;
						IF INSTR(v_preconrule, '#') > 0
						THEN
							WHILE l_pos1 <> 0
										AND l_pos2 <> 0 LOOP
								BEGIN
									SELECT TRIM(SUBSTR(TRIM(v_preconrule),
																		 INSTR(TRIM(v_preconrule), '#', 1, i) + 1,
																		 INSTR(TRIM(v_preconrule), '#', 1, i + 1) -
																		 INSTR(TRIM(v_preconrule), '#', 1, i) - 1)) abc
									INTO   l_param
									FROM   dual;
								EXCEPTION
									WHEN no_data_found THEN
										NULL;
									WHEN OTHERS THEN
										RAISE_APPLICATION_ERROR(-20001, SQLERRM);
								END;
								pkgexecuteparams.sp_execparam(pcompanyid,
																							l_param,
																							pcomp_appl_id,
																							NULL,
																							NULL,
																							rs_data);
								LOOP
									FETCH rs_data
										INTO lvariable1, lvariable2;
									EXIT WHEN rs_data%NOTFOUND;
									IF lvariable2 > 0
									THEN
										BEGIN
											SELECT logicstring
											INTO   v_precondrule
											FROM   flt_logic
											WHERE  logicid = lvariable2;
										EXCEPTION
											WHEN OTHERS THEN
												insert_error(SQLCODE,
																		 SQLERRM,
																		 pcomp_appl_id,
																		 'WORKFLOW-->updatecurrentstage',
																		 'updatecurrentstage-PRECONDRULEPICKUP');
										END;
									END IF;
								END LOOP;
								vconditionstatus := pkgexecstatement.sp_execute_statement(pcompanyid,
																																					pcomp_appl_id,
																																					'',
																																					'WF',
																																					v_precondrule,
																																					'AP',
																																					'P');
								v_preconrule     := REPLACE(v_preconrule,
																						'#' || l_param || '#',
																						'''' || vconditionstatus || '''');
								l_pos1           := INSTR(TRIM(v_preconrule), '#', 1, 1);
								l_pos2           := INSTR(TRIM(v_preconrule), '#', 1, 2);
							END LOOP;
						END IF;
						-- code added by vijayan a on 06-04-2007
						IF x.applstage LIKE 'L%APPRV'
						THEN
							l_cnt := 0;
							SELECT COUNT(*)
							INTO   l_cnt
							FROM   lot_allocation_dtl lad
							WHERE  lad.status <> 'X'
							AND    lad.comp_appl_id = pcomp_appl_id
							AND    lad.company_id = pcompanyid
							AND    fnccgvalue(cg_hierrachylevel, 'V') =
										 TRIM(REPLACE(x.applstage, 'APPRV'));
							-- if no emp. found for this level..
							IF l_cnt = 0
							THEN
								v_preconrule := 'select ''P'' from dual';
							END IF;
						END IF;
						-- end of code on 06-04-2007
						vconditionstatus := pkgexecstatement.sp_execute_statement(pcompanyid,
																																			pcomp_appl_id,
																																			'',
																																			'WF',
																																			v_preconrule,
																																			'AP',
																																			'P');
					END IF;
					IF vconditionstatus = 'P'
					THEN
						/*                  if x.initiateworkflowid is not null then
                                 --Get the 1st stage of the new workflowid and call updateworkflow with this stage and this workflow id
                               if x.initiateworkflowid > 0   then
                                   if Setparentsetno(pComp_appl_id,vWORKFLOWID,x.initiateworkflowid) then
                                     for y in curparallelstage(x.initiateworkflowid, 1) loop
                                          out_msg := updateWorkFlow( pCompanyid,    pComp_appl_id,       pApplid,
                                                                      y.applSTAGE,   pDisbursalstageflg,  pFinancierid,
                                                                      pUserID,       'P',
                                                                      pWorkflowStageid, pseqno, pejbname,
                                                                      x.initiateworkflowid,fnapplflag(x.initiateworkflowid));
                                          if out_msg is not null then
                                             return out_msg;
                                          end if;
                                     end loop;
                                   else
                                       ROLLBACK;
                                       return 'Set Parent Setno function failed';
                                   end if;
                                 end if;
                              end if;
            */
						out_msg := insertlot_workflowstage_dtl(pcompanyid,
																									 pcomp_appl_id,
																									 papplid,
																									 vworkflowid,
																									 x.workflowstageid,
																									 x.applstage,
																									 'R',
																									 pfinancierid,
																									 x.mandatory,
																									 puserid,
																									 x.seqno,
																									 NULL,
																									 FALSE,
																									 pejbname,
																									 NVL(papplflag,
																											 fnapplflag(vworkflowid,
																																	pcompanyid -- Added Companyid filter for Multi-company functionality [50480, 03-DEC-07]
																																	)));
						IF out_msg IS NOT NULL
						THEN
							RETURN out_msg;
						END IF;
						pworkflowstageid := x.workflowstageid;
						pseqno           := x.seqno;
						--Call the update procedure again with the new stage
						out_msg := updateworkflow(pcompanyid,
																			pcomp_appl_id,
																			papplid,
																			x.applstage,
																			pdisbursalstageflg,
																			pfinancierid,
																			puserid,
																			'R',
																			pworkflowstageid,
																			pseqno,
																			pejbname,
																			NULL,
																			fnapplflag(vworkflowid,
																								 pcompanyid -- Added Companyid filter for Multi-company functionality [50480, 03-DEC-07]
																								 ));
						IF out_msg IS NOT NULL
						THEN
							RETURN out_msg;
						END IF;
					ELSIF x.automaticflag = 'Y'
					THEN
						out_msg := insertlot_workflowstage_dtl(pcompanyid,
																									 pcomp_appl_id,
																									 papplid,
																									 vworkflowid,
																									 x.workflowstageid,
																									 x.applstage,
																									 'P',
																									 pfinancierid,
																									 x.mandatory,
																									 puserid,
																									 x.seqno,
																									 NULL,
																									 FALSE,
																									 pejbname,
																									 NVL(papplflag,
																											 fnapplflag(vworkflowid,
																																	pcompanyid -- Added Companyid filter for Multi-company functionality [50480, 03-DEC-07]
																																	)));
						IF out_msg IS NOT NULL
						THEN
							RETURN out_msg;
						END IF;
						IF x.initiateworkflowid IS NOT NULL
						THEN
							--Get the 1st stage of the new workflowid and call updateworkflow with this stage and this workflow id
							IF x.initiateworkflowid > 0
							THEN
								IF setparentsetno(pcomp_appl_id,
																	vworkflowid,
																	x.initiateworkflowid,
																	pcompanyid)
								THEN
									FOR y IN curparallelstage(x.initiateworkflowid, 1) LOOP
										out_msg := updateworkflow(pcompanyid,
																							pcomp_appl_id,
																							papplid,
																							y.applstage,
																							pdisbursalstageflg,
																							pfinancierid,
																							puserid,
																							'P',
																							pworkflowstageid,
																							pseqno,
																							pejbname,
																							x.initiateworkflowid,
																							fnapplflag(x.initiateworkflowid,
																												 pcompanyid -- Added Companyid filter for Multi-company functionality [50480, 03-DEC-07]
																												 ));
										IF out_msg IS NOT NULL
										THEN
											RETURN out_msg;
										END IF;
									END LOOP;
								ELSE
									ROLLBACK;
									RETURN 'Parent Set No function failed';
								END IF;
							END IF;
						END IF;
						pworkflowstageid := x.workflowstageid;
						pseqno           := x.seqno;
						--Call the update procedure again with the new stage
						IF x.precond_ejb IS NULL
						THEN
							out_msg := updateworkflow(pcompanyid,
																				pcomp_appl_id,
																				papplid,
																				x.applstage,
																				pdisbursalstageflg,
																				pfinancierid,
																				puserid,
																				'C',
																				pworkflowstageid,
																				pseqno,
																				pejbname,
																				NULL,
																				fnapplflag(vworkflowid,
																									 pcompanyid -- Added Companyid filter for Multi-company functionality [50480, 03-DEC-07]
																									 ));
							IF out_msg IS NOT NULL
							THEN
								RETURN out_msg;
							END IF;
						END IF;
					ELSE
						out_msg := insertlot_workflowstage_dtl(pcompanyid,
																									 pcomp_appl_id,
																									 papplid,
																									 vworkflowid,
																									 x.workflowstageid,
																									 x.applstage,
																									 'P',
																									 pfinancierid,
																									 x.mandatory,
																									 puserid,
																									 x.seqno,
																									 NULL,
																									 TRUE,
																									 pejbname,
																									 NVL(papplflag,
																											 fnapplflag(vworkflowid,
																																	pcompanyid -- Added Companyid filter for Multi-company functionality [50480, 03-DEC-07]
																																	)));
						IF out_msg IS NOT NULL
						THEN
							RETURN out_msg;
						END IF;
						pworkflowstageid := x.workflowstageid;
						pseqno           := x.seqno;
						IF x.initiateworkflowid IS NOT NULL
						THEN
							--Get the 1st stage of the new workflowid and call updateworkflow with this stage and this workflow id
							IF x.initiateworkflowid > 0
							THEN
								IF setparentsetno(pcomp_appl_id,
																	vworkflowid,
																	x.initiateworkflowid,
																	pcompanyid)
								THEN
									FOR y IN curparallelstage(x.initiateworkflowid, 1) LOOP
										out_msg := updateworkflow(pcompanyid,
																							pcomp_appl_id,
																							papplid,
																							y.applstage,
																							pdisbursalstageflg,
																							pfinancierid,
																							puserid,
																							'P',
																							pworkflowstageid,
																							pseqno,
																							pejbname,
																							x.initiateworkflowid,
																							fnapplflag(x.initiateworkflowid,
																												 pcompanyid -- Added Companyid filter for Multi-company functionality [50480, 03-DEC-07]
																												 ));
										IF out_msg IS NOT NULL
										THEN
											RETURN out_msg;
										END IF;
									END LOOP;
								ELSE
									ROLLBACK;
									RETURN 'Parent Set No function failed';
								END IF;
							END IF;
						END IF;
					END IF;
				ELSE
					--Reappeal function returns error
					RETURN blnreappeal;
				END IF;
			END LOOP;
		ELSIF vinsertnextstage = FALSE
					AND pstagestatus = 'J'
		THEN
			--Changes done by: Vinay
			--Purpose: The Parent workflow stage is not getting marked as Rejected when child stage is Rejected. It's a temporary solution
			--Changes done on: 24 May 2004
			BEGIN
				SELECT loanstageid, workflowid
				INTO   vloanstageid, jworkflowid
				FROM   lot_workflowstage_dtl
				WHERE  comp_appl_id = pcomp_appl_id
				AND    stagestatus = 'P'
				AND    stage IN (SELECT applstage
												 FROM   workflowstage
												 WHERE  companyid = pcompanyid -- Added Companyid filter for Multi-company functionality [50480, 03-DEC-07]
												 AND    initiateworkflowid = vworkflowid);
				--Mark the Parent Workflow Stage as Rejected if it the stage is part of main workflow
				UPDATE lot_workflowstage_dtl
				SET    stagestatus = 'J'
				WHERE  loanstageid = vloanstageid;
				BEGIN
					IF rejectionreqd(pcomp_appl_id, vloanstageid)
					THEN
						out_msg := updateworkflow(pcompanyid,
																			pcomp_appl_id,
																			NULL,
																			'INITREJ',
																			NULL,
																			NULL,
																			puserid,
																			'C',
																			pworkflowstageid,
																			vseqno,
																			pejbname,
																			NULL,
																			'RJ');
						IF out_msg IS NOT NULL
						THEN
							ROLLBACK;
							RETURN out_msg;
						END IF;
					END IF;
					IF allowrejection(pcomp_appl_id)
					THEN
						out_msg := updateworkflow(pcompanyid,
																			pcomp_appl_id,
																			NULL,
																			'REJREQ',
																			NULL,
																			NULL,
																			puserid,
																			'C',
																			pworkflowstageid,
																			vseqno,
																			pejbname,
																			NULL,
																			'RW');
						IF out_msg IS NOT NULL
						THEN
							ROLLBACK;
							RETURN out_msg;
						END IF;
					END IF;
				EXCEPTION
					WHEN OTHERS THEN
						insert_error(SQLCODE,
												 SQLERRM,
												 pcomp_appl_id,
												 'Workflow Rejection Initiation',
												 'pkgworkflow-->UpdateWorkflow');
						ROLLBACK;
						RETURN 'Error occured during procedure execution : ' || SQLERRM;
				END;
			EXCEPTION
				WHEN OTHERS THEN
					NULL;
			END;
		END IF;
		RETURN '';
	EXCEPTION
		WHEN OTHERS THEN
			ROLLBACK;
			dbms_output.put_line('Error occured during procedure execution : ' ||
													 SQLERRM);
			RETURN 'Error occured during procedure execution : ' || SQLERRM;
	END updateworkflow;

	-------==================================================================
	/*   Fuction updateworkflow is overloaded to add new default parameter - By Anis 17.07.2006 */
	FUNCTION updateworkflow
	(
		pcompanyid         NUMBER,
		pcomp_appl_id      NUMBER,
		papplid            NUMBER,
		pstage             VARCHAR2,
		pdisbursalstageflg VARCHAR2,
		pfinancierid       NUMBER,
		puserid            VARCHAR2,
		pstagestatus       VARCHAR2,
		pworkflowstageid   OUT NUMBER,
		pseqno             OUT NUMBER,
		pejbname           OUT VARCHAR2,
		pemployeeid        IN NUMBER, -- New parameter added by Anis 17.07.2006
		pworkflowid        NUMBER DEFAULT NULL,
		papplflag          VARCHAR2 DEFAULT 'O',
		prealloctype       IN NUMBER DEFAULT NULL,
		preallocempid      IN NUMBER DEFAULT NULL
	) RETURN VARCHAR2 IS
		out_msg         VARCHAR2(2000);
		v_update_flag   BOOLEAN DEFAULT FALSE;
		v_loanstageid   lot_workflowstage_dtl.loanstageid%TYPE;
		vproductcode    lot_globalloan_t.productcode%TYPE;
		vrolecode       company_employee_role.cg_rolecode%TYPE;
		vsupervisorcode company_employee_role.supervisorcode%TYPE;
		v_count         NUMBER := 0;
	BEGIN
		global_employeeid    := pemployeeid;
		global_pcomp_appl_id := pcomp_appl_id;
		SELECT NVL(MAX(loanstageid), 0)
		INTO   v_loanstageid
		FROM   lot_workflowstage_dtl a, workflowstage b
		WHERE  a.workflowid = b.workflowid
		AND    stage = applstage
		AND    comp_appl_id = pcomp_appl_id
		AND    stage = pstage
		AND    a.status <> 'X'
		AND    b.status <> 'X';
		dbms_output.put_line('v_loanstageid :' || v_loanstageid);
		out_msg := updateworkflow(pcompanyid,
															pcomp_appl_id,
															papplid,
															pstage,
															pdisbursalstageflg,
															pfinancierid,
															puserid,
															pstagestatus,
															pworkflowstageid,
															pseqno,
															pejbname,
															NULL,
															papplflag);
		IF out_msg IS NOT NULL
		THEN
			ROLLBACK;
			RETURN out_msg;
		END IF;
		IF global_employeeid IS NOT NULL
		THEN
			SELECT COUNT(*)
			INTO   v_count
			FROM   lot_workflowstage_dtl lw, workflowstage ws
			WHERE  lw.loanstageid > v_loanstageid
			AND    lw.comp_appl_id = pcomp_appl_id
			AND    ws.applstage = lw.stage
			AND    ws.workflowid = lw.workflowid
			AND    ws.status <> 'X'
			AND    lw.status <> 'X'
			AND    lw.employeecode = global_employeeid
			AND    lw.stagestatus = 'P'; -- Only pending stages
			dbms_output.put_line('count :' || v_count);
			IF v_count > 0
			THEN
				RETURN '';
			ELSE
				ROLLBACK;
				RETURN 'Error - No stage is found that may be allocated to the passed employee.';
			END IF;
		END IF;
		RETURN '';
	EXCEPTION
		WHEN no_data_found THEN
			ROLLBACK;
			RETURN 'Exception occured in updateworkflow :' || SQLERRM;
		WHEN OTHERS THEN
			ROLLBACK;
			RETURN 'Exception occured in updateworkflow :' || SQLERRM;
	END;

	-------==================================================================
	FUNCTION workflowreappeal
	(
		pcomp_appl_id NUMBER,
		pcompanyid    NUMBER,
		preappealid   NUMBER
	) RETURN VARCHAR2 IS
		vreappealseq lot_reappeal_workflow.seqno%TYPE;
		CURSOR curreappstagestatus IS
			SELECT a.stage, a.workflowid
			FROM   lot_reappeal_workflow a
			WHERE  a.reappealno = preappealid
			AND    a.reappealstage_yn = 'Y'
			AND    a.seqno = vreappealseq;
		--pworkflowid number;
	BEGIN
		--     if psys_flag = 1 then
		--        rollback;
		--        delete from lot_reappeal_workflow where reappealno = preappealid ;
		--        delete from lot_reappeal_hdr where reappealno = preappealid ;
		--        return '' ;
		--   end if;
		BEGIN
			--Select records in lot_reappeal_workflow
			SELECT MIN(a.seqno)
			INTO   vreappealseq
			FROM   lot_reappeal_workflow a, lot_reappeal_hdr b
			WHERE  a.reappealno = b.reappealno
			AND    b.status = 'T'
			AND    a.reappealstage_yn = 'Y'
			AND    a.reappealno = preappealid;
		EXCEPTION
			WHEN OTHERS THEN
				RETURN 'Error while selecting record from lot_reappeal_hdr : ' || SQLERRM;
		END;
		--Delete all records in lot_workflowstage_dtl having seqno > this sequence no.
		BEGIN
			INSERT INTO lot_workflowstage_dtl_hst
				(id,
				 loanstageid,
				 companyid,
				 comp_appl_id,
				 applid,
				 workflowid,
				 workflowstageid,
				 stage,
				 disbursalstageflg,
				 stagestatus,
				 statusdate,
				 statusbusidate,
				 financierid,
				 priority,
				 mandatory,
				 remarks,
				 makerid,
				 makedate,
				 status,
				 cusnoteid,
				 cg_statusreason,
				 seqno,
				 statusenddate,
				 employeecode,
				 supervisorcode,
				 applicationflag,
				 setno)
				SELECT lotsworkflowstagehstcmp_seq.NEXTVAL,
							 loanstageid,
							 companyid,
							 comp_appl_id,
							 applid,
							 workflowid,
							 workflowstageid,
							 stage,
							 disbursalstageflg,
							 stagestatus,
							 statusdate,
							 statusbusidate,
							 financierid,
							 priority,
							 mandatory,
							 remarks,
							 makerid,
							 makedate,
							 status,
							 cusnoteid,
							 cg_statusreason,
							 seqno,
							 statusenddate,
							 employeecode,
							 supervisorcode,
							 applicationflag,
							 setno
				FROM   lot_workflowstage_dtl
				WHERE  seqno > vreappealseq
				AND    comp_appl_id = pcomp_appl_id
				AND    status <> 'X'
				AND    companyid = pcompanyid;
		EXCEPTION
			WHEN OTHERS THEN
				RETURN 'Error while inserting record from lot_workflowstage_dtl_hst : ' || SQLERRM;
		END;
		DELETE FROM lot_workflowstage_dtl
		WHERE  seqno > vreappealseq
		AND    comp_appl_id = pcomp_appl_id
		AND    status <> 'X'
		AND    companyid = pcompanyid;
		--Temporarily using the delete statement above instead of updating the status to 'X'
		/*        update lot_workflowstage_dtl
    set status = 'X' where
    seqno > vreappealseq and
    comp_appl_id = pcomp_appl_id and
    companyid = pcompanyid;*/
		--Update lot_workflowstage_dtl
		FOR x IN curreappstagestatus LOOP
			BEGIN
				UPDATE lot_workflowstage_dtl
				SET    stagestatus = 'P'
				WHERE  comp_appl_id = pcomp_appl_id
				AND    stage = x.stage
				AND    workflowid = x.workflowid
				AND    status <> 'X';
			EXCEPTION
				WHEN OTHERS THEN
					RETURN 'Error while updating lot_workflowstage_dtl : ' || SQLERRM;
			END;
		END LOOP;
		/*       update lot_workflowstage_dtl set
    stagestatus = 'P' where
    loanstageid = vloanstageid ;*/
		RETURN '';
	END workflowreappeal;

	------====================================================================================
	FUNCTION getrole
	(
		pstage      VARCHAR2,
		pworkflowid NUMBER
	) RETURN VARCHAR2 IS
		prole VARCHAR2(8);
	BEGIN
		SELECT rolecode
		INTO   prole
		FROM   workflowstage
		WHERE  applstage = pstage
		AND    workflowid = pworkflowid;
		RETURN prole;
	END getrole;

	-----=====================================================================================
	-- End of getRole()
	---Funtion to reverse stages when reversals are done
	FUNCTION workflowreappealreverse
	(
		pcomp_appl_id NUMBER,
		pcompanyid    NUMBER,
		preappealid   NUMBER
	) RETURN VARCHAR2 IS
		CURSOR curreappstages IS
			SELECT a.stage, a.workflowid
			FROM   lot_reappeal_workflow a
			WHERE  a.reappealno = preappealid
			AND    a.reappealstage_yn = 'Y';
		CURSOR curfirstreappealstage IS
			SELECT a.stage
			FROM   lot_reappeal_workflow a
			WHERE  a.reappealno = preappealid
			AND    a.seqno = (SELECT MIN(seqno)
												FROM   lot_reappeal_workflow
												WHERE  reappealno = preappealid);
		prole VARCHAR2(8);
	BEGIN
		--    psys_flag := 0;
		FOR x IN curreappstages LOOP
			--      prole := getRole(x.stage,x.workflowid) ;
			insert_error(1, ' first ROLE = ' || prole);
			IF x.stage = 'DDUPREQ'
			THEN
				UPDATE lot_reappeal_workflow
				SET    reappealstage_yn = 'Y'
				WHERE  comp_appl_id = pcomp_appl_id
				AND    reappealno = preappealid
				AND    stage IN ('DDUPDEDS', 'DDUPEXEC', 'DDUPCRDS', 'MAILDDUP',
								'STYMFILE', 'STYMREPT');
				/*
        'APP-DE' then
                   null;
                elsif x.stage = 'OFFER-DE'*/
			ELSIF x.stage = 'DDUPDEDS'
			THEN
				UPDATE lot_reappeal_workflow
				SET    reappealstage_yn = 'Y'
				WHERE  comp_appl_id = pcomp_appl_id
				AND    reappealno = preappealid
				AND    stage IN ('DDUPCRDS', 'MAILDDUP', 'STYMFILE', 'STYMREPT');
			ELSIF x.stage = 'DDUPCRDS'
			THEN
				UPDATE lot_reappeal_workflow
				SET    reappealstage_yn = 'Y'
				WHERE  comp_appl_id = pcomp_appl_id
				AND    reappealno = preappealid
				AND    stage IN ('MAILDDUP');
			ELSIF x.stage = 'STYMFILE'
			THEN
				UPDATE lot_reappeal_workflow
				SET    reappealstage_yn = 'Y'
				WHERE  comp_appl_id = pcomp_appl_id
				AND    reappealno = preappealid
				AND    stage IN ('STYMREPT');
			ELSIF x.stage = 'VERFTRIG'
			THEN
				UPDATE lot_reappeal_workflow
				SET    reappealstage_yn = 'Y'
				WHERE  comp_appl_id = pcomp_appl_id
				AND    reappealno = preappealid
				AND    stage IN ('FILLOGIN', 'MAILOV', 'MAILTV', 'MAILRV',
								'OVREPORT', 'RVREPORT', 'TVREPORT');
			ELSIF x.stage = 'CVALTRIG'
			THEN
				UPDATE lot_reappeal_workflow
				SET    reappealstage_yn = 'Y'
				WHERE  comp_appl_id = pcomp_appl_id
				AND    reappealno = preappealid
				AND    stage IN ('MAILCVAL');
			ELSIF x.stage = 'IBPTRIG'
			THEN
				UPDATE lot_reappeal_workflow
				SET    reappealstage_yn = 'Y'
				WHERE  comp_appl_id = pcomp_appl_id
				AND    reappealno = preappealid
				AND    stage IN ('ITR-REPT', 'PSLPREPT', 'BKSTREPT');
			ELSIF x.stage = 'PPGRULE'
			THEN
				UPDATE lot_reappeal_workflow
				SET    reappealstage_yn = 'Y'
				WHERE  comp_appl_id = pcomp_appl_id
				AND    reappealno = preappealid
				AND    stage IN ('PPG-L1', 'PPG-L2', 'PPG-L3', 'PPG-RSL2', 'PPG-FD',
								'SANCLETR', 'REJTLETR');
			ELSIF x.stage = 'PPG-FD'
			THEN
				UPDATE lot_reappeal_workflow
				SET    reappealstage_yn = 'Y'
				WHERE  comp_appl_id = pcomp_appl_id
				AND    reappealno = preappealid
				AND    stage IN ('PPG-L1', 'PPG-L2', 'PPG-L3', 'PPG-RSL2',
								'SANCLETR', 'REJTLETR');
			ELSIF x.stage = 'DPPGRULE'
			THEN
				UPDATE lot_reappeal_workflow
				SET    reappealstage_yn = 'Y'
				WHERE  comp_appl_id = pcomp_appl_id
				AND    reappealno = preappealid
				AND    stage IN
							 ('DPPG-L1', 'DPPG-L2', 'DPPG-L3', 'DPPG-RSL2', 'DPPG-FD');
			ELSIF x.stage = 'DPPG-FD'
			THEN
				UPDATE lot_reappeal_workflow
				SET    reappealstage_yn = 'Y'
				WHERE  comp_appl_id = pcomp_appl_id
				AND    reappealno = preappealid
				AND    stage IN ('DPPG-L1', 'DPPG-L2', 'DPPG-L3', 'DPPG-RSL2');
			ELSIF x.stage = 'LVTHTRIG'
			THEN
				UPDATE lot_reappeal_workflow
				SET    reappealstage_yn = 'Y'
				WHERE  comp_appl_id = pcomp_appl_id
				AND    reappealno = preappealid
				AND    stage IN
							 ('MAILLV', 'MAILTH', 'LVREPORT', 'TH1REPT', 'TH2REPT');
			ELSIF x.stage = 'INTRTRIG'
			THEN
				UPDATE lot_reappeal_workflow
				SET    reappealstage_yn = 'Y'
				WHERE  comp_appl_id = pcomp_appl_id
				AND    reappealno = preappealid
				AND    stage IN ('INTRREPT');
				--      elsif prole = 'SYSBATCH' then
				--            insert_error(1,'if SYSBATCH ROLE = ' || prole);
				--            psys_flag := 1;
				--            rollback;
				--            return 'Error cannot reverse' ;
				--      elsif prole = 'SYSPROC' then
				--            insert_error(1,'if SYSPROC ROLE = ' || prole);
				--            psys_flag := 1;
				--            rollback;
				--            return 'Error cannot reverse' ;
			END IF;
		END LOOP;
		FOR x IN curreappstages LOOP
			IF x.stage = 'DDUPREQ'
			THEN
				--           delete from dedup_result where            drm_comp_appl_id = pcomp_appl_id;
				/*           if pkgcifreversal.reappeal(pcomp_appl_id)!=1 then
          rollback;
          return 'Dedup Reversal Failed';
        end if;*/
				NULL;
			ELSIF x.stage = 'STYMFILE'
			THEN
				DELETE FROM lot_custverification_decsn
				WHERE  comp_appl_id = pcomp_appl_id
				AND    agtype IN ('SR');
			ELSIF x.stage = 'DDUPEXEC'
			THEN
				NULL;
			ELSIF x.stage = 'DDUPDEDS'
			THEN
				FOR y IN curfirstreappealstage LOOP
					IF y.stage = 'DDUPDEDS'
					THEN
						UPDATE lot_globalloan_t a
						SET    a.dedupexecute = 'Y'
						WHERE  comp_appl_id = pcomp_appl_id;
						EXIT;
					END IF;
				END LOOP;
			ELSIF x.stage = 'DDUPCRDS'
			THEN
				FOR y IN curfirstreappealstage LOOP
					IF y.stage = 'DDUPCRDS'
					THEN
						UPDATE lot_globalloan_t a
						SET    a.dedupexecute = 'Y'
						WHERE  comp_appl_id = pcomp_appl_id;
						EXIT;
					END IF;
				END LOOP;
			ELSIF x.stage = 'MAILDDUP'
			THEN
				UPDATE lot_globalloan_t l
				SET    l.maildedupflag = 'N'
				WHERE  comp_appl_id = pcomp_appl_id;
			ELSIF x.stage = 'VERFTRIG'
			THEN
				DELETE FROM lot_custverification_decsn
				WHERE  comp_appl_id = pcomp_appl_id
				AND    agtype IN ('OV', 'RV', 'TV');
				DELETE FROM lot_custverification_dtl
				WHERE  comp_appl_id = pcomp_appl_id
				AND    agtype IN ('OV', 'RV', 'TV');
			ELSIF x.stage = 'CVALTRIG'
			THEN
				DELETE FROM lot_custverification_decsn
				WHERE  comp_appl_id = pcomp_appl_id
				AND    agtype IN ('UC');
			ELSIF x.stage = 'MAILOV'
			THEN
				NULL;
			ELSIF x.stage = 'MAILRV'
			THEN
				NULL;
			ELSIF x.stage = 'MAILTV'
			THEN
				NULL;
			ELSIF x.stage = 'MAILCVAL'
			THEN
				NULL;
			ELSIF x.stage = 'OVREPORT'
			THEN
				NULL;
			ELSIF x.stage = 'RVREPORT'
			THEN
				NULL;
			ELSIF x.stage = 'TVREPORT'
			THEN
				NULL;
			ELSIF x.stage = 'CVREPORT'
			THEN
				NULL;
			ELSIF x.stage = 'FILLOGIN'
			THEN
				NULL;
			ELSIF x.stage = 'PSANCDOC'
			THEN
				NULL;
			ELSIF x.stage = 'IBPTRIG'
			THEN
				DELETE FROM lot_custverification_decsn
				WHERE  comp_appl_id = pcomp_appl_id
				AND    agtype IN ('IT', 'PV', 'BV');
				DELETE FROM lot_custverification_dtl
				WHERE  comp_appl_id = pcomp_appl_id
				AND    agtype IN ('IT', 'PV', 'BV');
			ELSIF x.stage = 'APP-DE'
			THEN
				NULL;
			ELSIF x.stage = 'OFFER-DE'
			THEN
				NULL;
			ELSIF x.stage = 'PPGRULE'
			THEN
				NULL;
			ELSIF x.stage = 'PPG-L0'
			THEN
				NULL;
			ELSIF x.stage = 'ITR-REPT'
			THEN
				NULL;
			ELSIF x.stage = 'PSLPREPT'
			THEN
				NULL;
			ELSIF x.stage = 'BKSTREPT'
			THEN
				NULL;
			ELSIF x.stage = 'PPG-L1'
			THEN
				NULL;
			ELSIF x.stage = 'PPG-L2'
			THEN
				NULL;
			ELSIF x.stage = 'PPG-RSL2'
			THEN
				NULL;
			ELSIF x.stage = 'PPG-L3'
			THEN
				NULL;
			ELSIF x.stage = 'PPG-FD'
			THEN
				NULL;
			ELSIF x.stage = 'SANCLETR'
			THEN
				NULL;
			ELSIF x.stage = 'REJTLETR'
			THEN
				NULL;
			ELSIF x.stage = 'INTRTRIG'
			THEN
				NULL;
			ELSIF x.stage = 'INTRREPT'
			THEN
				NULL;
			ELSIF x.stage = 'FILEDISB'
			THEN
				NULL;
			ELSIF x.stage = 'PDISBDOC'
			THEN
				NULL;
			ELSIF x.stage = 'SKRDOC'
			THEN
				NULL;
			ELSIF x.stage = 'LVTHTRIG'
			THEN
				DELETE FROM lot_custverification_decsn
				WHERE  comp_appl_id = pcomp_appl_id
				AND    agtype IN ('LV', 'T1', 'T2');
				DELETE FROM lot_custverification_dtl
				WHERE  comp_appl_id = pcomp_appl_id
				AND    agtype IN ('LV', 'T1', 'T2');
			ELSIF x.stage = 'MAILLV'
			THEN
				NULL;
			ELSIF x.stage = 'MAILTH'
			THEN
				NULL;
			ELSIF x.stage = 'LVREPORT'
			THEN
				NULL;
			ELSIF x.stage = 'TH1REPT'
			THEN
				NULL;
			ELSIF x.stage = 'TH2REPT'
			THEN
				NULL;
			ELSIF x.stage = 'DPPGRULE'
			THEN
				NULL;
			ELSIF x.stage = 'DPPG-L0'
			THEN
				NULL;
			ELSIF x.stage = 'DISBOFER'
			THEN
				NULL;
			ELSIF x.stage = 'DPPG-L1'
			THEN
				NULL;
			ELSIF x.stage = 'DPPG-L2'
			THEN
				NULL;
			ELSIF x.stage = 'DPPGRSL2'
			THEN
				NULL;
			ELSIF x.stage = 'DPPG-L3'
			THEN
				NULL;
			ELSIF x.stage = 'DPPG-FD'
			THEN
				NULL;
			ELSIF x.stage = 'FILEOPER'
			THEN
				NULL;
			ELSIF x.stage = 'DPAYENTR'
			THEN
				NULL;
			ELSIF x.stage = 'STYMREPT'
			THEN
				NULL;
			ELSIF x.stage = 'PROPDETL'
			THEN
				NULL;
			END IF;
			RETURN '';
		END LOOP;
	END workflowreappealreverse;

	-----================================================================================
	/* For Malaysia Workflow Reversal Functionality */
	/* Developed by Vinay */
	/* Developed on June 20, 2004 */
	FUNCTION workflowreversal
	(
		fromstage          IN VARCHAR2,
		tostage            IN VARCHAR2,
		in_comp_appl_id    IN NUMBER,
		in_applicationflag IN VARCHAR2 DEFAULT 'O',
		pcompanyid         IN NUMBER
	) RETURN BOOLEAN AS
		v_mandatory   lot_workflowstage_dtl.mandatory%TYPE;
		v_automatic   workflowstage.automaticflag%TYPE;
		v_child       workflowstage.initiateworkflowid%TYPE;
		v_loanstageid lot_workflowstage_dtl.loanstageid%TYPE;
		fpos          NUMBER(3) := 0;
		lpos          NUMBER(3) := 0;
		l_param       VARCHAR2(500) := '';
		l_paramvalue  VARCHAR2(500) := '';
		strdml        VARCHAR2(2000);
		CURSOR cur_workflow IS
			SELECT lwd.loanstageid,
						 lwd.applicationflag,
						 wf.applstage,
						 wf.initiateworkflowid,
						 lwd.setno,
						 lwd.seqno,
						 wf.mandatory,
						 wf.automaticflag,
						 s.deletetables
			FROM   lot_workflowstage_dtl lwd, workflowstage wf, stage s
			WHERE  lwd.comp_appl_id = in_comp_appl_id
			AND    lwd.workflowid = wf.workflowid
			AND    lwd.workflowstageid = wf.workflowstageid
			AND    lwd.applicationflag = in_applicationflag
			AND    s.stage = lwd.stage
			AND    lwd.seqno > (SELECT seqno
													FROM   workflowstage w
													WHERE  w.applstage = tostage
													AND    lwd.workflowid = w.workflowid)
			AND    lwd.seqno <=
						 (SELECT seqno
							 FROM   workflowstage w
							 WHERE  w.applstage = fromstage
							 AND    lwd.workflowid = w.workflowid)
			ORDER  BY lwd.seqno DESC;
	BEGIN
		BEGIN
			SELECT lwd.loanstageid
			INTO   v_loanstageid
			FROM   lot_workflowstage_dtl lwd, workflowstage wf
			WHERE  lwd.comp_appl_id = in_comp_appl_id
			AND    lwd.workflowid = wf.workflowid
			AND    lwd.workflowstageid = wf.workflowstageid
			AND    lwd.applicationflag = in_applicationflag
			AND    lwd.stagestatus = 'P'
			AND    lwd.stage = fromstage;
			IF v_loanstageid IS NULL
			THEN
				RETURN FALSE;
				ROLLBACK;
			END IF;
		EXCEPTION
			WHEN OTHERS THEN
				insert_error(SQLCODE,
										 SQLERRM,
										 in_comp_appl_id,
										 'Workflow Reversal',
										 'pkgworkflow -> workflowreversal');
				RETURN FALSE;
				ROLLBACK;
		END;
		BEGIN
			SELECT wf.initiateworkflowid, wf.mandatory, wf.automaticflag
			INTO   v_child, v_mandatory, v_automatic
			FROM   lot_workflowstage_dtl lwd, workflowstage wf
			WHERE  lwd.comp_appl_id = in_comp_appl_id
			AND    lwd.workflowid = wf.workflowid
			AND    lwd.workflowstageid = wf.workflowstageid
			AND    lwd.applicationflag = in_applicationflag
			AND    lwd.stage = tostage;
			IF v_child > 0
				 OR v_mandatory = 'N'
				 OR v_automatic = 'Y'
			THEN
				RETURN FALSE;
				ROLLBACK;
			END IF;
		EXCEPTION
			WHEN OTHERS THEN
				insert_error(SQLCODE,
										 SQLERRM,
										 in_comp_appl_id,
										 'Workflow Reversal',
										 'pkgworkflow -> workflowreversal');
				RETURN FALSE;
				ROLLBACK;
		END;
		FOR workflow_data IN cur_workflow LOOP
			UPDATE lot_workflowstage_dtl w
			SET    status = 'X'
			WHERE  w.loanstageid = workflow_data.loanstageid;
			DELETE FROM lot_workflowstage_dtl w
			WHERE  w.loanstageid = workflow_data.loanstageid;
			IF workflow_data.initiateworkflowid > 0
			THEN
				/*           update lot_workflowstage_dtl w
        set status = 'X'
        where
        w.workflowid = workflow_data.initiateworkflowid
        and   w.setno = workflow_data.setno;*/
				DELETE FROM lot_workflowstage_dtl w
				WHERE  w.workflowid = workflow_data.initiateworkflowid
				AND    w.setno = workflow_data.setno
				AND    w.comp_appl_id = in_comp_appl_id;
			END IF;
			IF SQL%ROWCOUNT > 0
			THEN
				IF workflow_data.deletetables IS NOT NULL
				THEN
					l_paramvalue := workflow_data.deletetables;
					LOOP
						EXIT WHEN l_paramvalue IS NULL;
						IF INSTR(l_paramvalue, ',') = 0
						THEN
							l_param      := l_paramvalue;
							l_paramvalue := NULL;
						ELSE
							l_param      := SUBSTR(l_paramvalue,
																		 1,
																		 INSTR(l_paramvalue, ',') - 1);
							l_paramvalue := SUBSTR(l_paramvalue,
																		 INSTR(l_paramvalue, ',') + 1,
																		 length(l_paramvalue));
						END IF;
						--                strdml:='update '|| trim(l_param)|| ' set status = ''X'' where comp_appl_id = '||in_comp_appl_id;
						strdml := 'delete from ' || TRIM(l_param) ||
											' where comp_appl_id = ' || in_comp_appl_id;
						BEGIN
							EXECUTE IMMEDIATE strdml;
						EXCEPTION
							WHEN OTHERS THEN
								insert_error(SQLCODE,
														 SQLERRM,
														 in_comp_appl_id,
														 'Workflow Reversal',
														 'pkgworkflow -> workflowreversal');
								RETURN FALSE;
								ROLLBACK;
						END;
					END LOOP;
				END IF;
			END IF;
		END LOOP;
		UPDATE lot_workflowstage_dtl l
		SET    stagestatus = 'P'
		WHERE  l.comp_appl_id = in_comp_appl_id
		AND    l.applicationflag = in_applicationflag
		AND    l.seqno = (SELECT seqno
											FROM   workflowstage w
											WHERE  w.applstage = tostage
											AND    l.workflowid = w.workflowid);
		RETURN TRUE;
	EXCEPTION
		WHEN OTHERS THEN
			insert_error(SQLCODE,
									 SQLERRM,
									 in_comp_appl_id,
									 'Workflow Reversal',
									 'pkgworkflow -> workflowreversal');
			RETURN FALSE;
			ROLLBACK;
	END workflowreversal;

	PROCEDURE sp_initiateworkflow
	(
		in_comp_appl_id    IN NUMBER,
		in_productcode     IN VARCHAR2,
		in_prodsubtypecode IN VARCHAR2,
		in_applicationflag IN VARCHAR2,
		in_companyid       IN NUMBER,
		in_userid          IN VARCHAR2,
		out_mesg           OUT VARCHAR2
	) AS
		vworkflowid workflow.workflowid%TYPE;
		--vautomaticflag     workflowstage.automaticflag%TYPE;
		pworkflowstageid workflowstage.workflowstageid%TYPE;
		pseqno           workflowstage.seqno%TYPE;
		pejbname         VARCHAR2(3000);
		v_cnt            NUMBER(2);
		v_logicstring    VARCHAR2(2000);
		v_exec_level     VARCHAR2(3);
		v_flag           VARCHAR2(2);
		v_pass_flag      NUMBER(1) := 0;
		CURSOR cur_stage(vworkflowid workflow.workflowid%TYPE) IS
			SELECT ws.workflowid,
						 ws.workflowstageid,
						 ws.applstage,
						 automaticflag,
						 ws.disbursalstageflg,
						 ws.financierid
			FROM   workflowstage ws, workflow w
			WHERE  ws.workflowid = w.workflowid
			AND    ws.workflowid = vworkflowid
			AND    seqno = 1
			AND    ws.status <> 'X';
		CURSOR cur_workflow_id(v_companyid NUMBER, v_productcode VARCHAR2, v_prodsubtypecode VARCHAR2) IS
			SELECT wf.workflowid, conditionid
			FROM   workflow wf
			WHERE  (wf.productcode = v_productcode OR wf.productcode IS NULL)
			AND    (wf.prodsubtypecode = v_prodsubtypecode OR
						wf.prodsubtypecode IS NULL)
			AND    conditionid IS NOT NULL
			AND    wf.companyid = in_companyid
			AND    wf.applicationflag = in_applicationflag
			AND    wf.status <> 'X';
	BEGIN
		BEGIN
			SELECT COUNT(*)
			INTO   v_cnt
			FROM   workflow
			WHERE  companyid = in_companyid -- Added Companyid filter for Multi-company functionality [50480, 03-DEC-07]
			AND    (productcode = in_productcode OR productcode IS NULL)
			AND    (prodsubtypecode = in_prodsubtypecode OR
						prodsubtypecode IS NULL)
			AND    conditionid IS NOT NULL
			AND    applicationflag = in_applicationflag
			AND    status <> 'X';
			IF v_cnt > 0
			THEN
				FOR rec_workflow_id IN cur_workflow_id(in_companyid,
																							 in_productcode,
																							 in_prodsubtypecode) LOOP
					BEGIN
						SELECT logicstring, execute_level
						INTO   v_logicstring, v_exec_level
						FROM   flt_logic
						WHERE  logicid = rec_workflow_id.conditionid;
					EXCEPTION
						WHEN no_data_found THEN
							IF out_mesg IS NULL
							THEN
								out_mesg := 'No Record exists in flt_logic for the given conditionid' || ' ' ||
														rec_workflow_id.conditionid;
							END IF;
							RAISE;
					END;
					v_flag := pkgexecstatement.sp_execute_statement(in_companyid,
																													in_comp_appl_id,
																													NULL,
																													NULL,
																													v_logicstring,
																													'AP',
																													'P');
					IF v_flag = 'P'
					THEN
						vworkflowid := rec_workflow_id.workflowid;
						v_pass_flag := 1;
						EXIT;
					END IF;
				END LOOP;
			END IF;
			IF v_pass_flag = 0
			THEN
				SELECT COUNT(*)
				INTO   v_cnt
				FROM   workflow
				WHERE  companyid = in_companyid -- Added Companyid filter for Multi-company functionality [50480, 03-DEC-07]
				AND    productcode = in_productcode
				AND    prodsubtypecode = in_prodsubtypecode
				AND    conditionid IS NULL
				AND    applicationflag = in_applicationflag
				AND    status <> 'X';
				IF v_cnt = 1
				THEN
					SELECT workflowid
					INTO   vworkflowid
					FROM   workflow
					WHERE  companyid = in_companyid -- Added Companyid filter for Multi-company functionality [50480, 03-DEC-07]
					AND    productcode = in_productcode
					AND    prodsubtypecode = in_prodsubtypecode
					AND    conditionid IS NULL
					AND    applicationflag = in_applicationflag
					AND    status <> 'X';
				ELSE
					BEGIN
						SELECT workflowid
						INTO   vworkflowid
						FROM   workflow
						WHERE  companyid = in_companyid -- Added Companyid filter for Multi-company functionality [50480, 03-DEC-07]
						AND    productcode = in_productcode
						AND    prodsubtypecode IS NULL
						AND    conditionid IS NULL
						AND    applicationflag = in_applicationflag
						AND    status <> 'X';
					EXCEPTION
						WHEN no_data_found THEN
							SELECT workflowid
							INTO   vworkflowid
							FROM   workflow
							WHERE  companyid = in_companyid -- Added Companyid filter for Multi-company functionality [50480, 03-DEC-07]
							AND    productcode IS NULL
							AND    prodsubtypecode IS NULL
							AND    conditionid IS NULL
							AND    applicationflag = in_applicationflag
							AND    status <> 'X';
					END;
				END IF;
			END IF;
		EXCEPTION
			WHEN OTHERS THEN
				IF out_mesg IS NULL
				THEN
					out_mesg := 'Error while fetching Data from workflow table(sp_initiateworkflow)';
				END IF;
				RAISE;
		END;
		v_pass_flag := 0;
		FOR x IN cur_stage(vworkflowid) LOOP
			IF x.automaticflag = 'Y'
			THEN
				BEGIN
					out_mesg := pkgworkflow.updateworkflow(in_companyid,
																								 in_comp_appl_id,
																								 NULL,
																								 x.applstage,
																								 x.disbursalstageflg,
																								 x.financierid,
																								 in_userid,
																								 'C',
																								 pworkflowstageid,
																								 pseqno,
																								 pejbname,
																								 vworkflowid,
																								 in_applicationflag);
				EXCEPTION
					WHEN OTHERS THEN
						ROLLBACK;
						IF out_mesg IS NULL
						THEN
							out_mesg := 'Error While executing pkgworkflow.updateworkflow in sp_initiateworkflow ' ||
													SQLERRM;
							RAISE;
						END IF;
				END;
			ELSE
				BEGIN
					-- TEMP
					--vworkflowid := NULL;
					out_mesg := pkgworkflow.updateworkflow(in_companyid,
																								 in_comp_appl_id,
																								 NULL,
																								 x.applstage,
																								 x.disbursalstageflg,
																								 x.financierid,
																								 in_userid,
																								 'P',
																								 pworkflowstageid,
																								 pseqno,
																								 pejbname,
																								 vworkflowid,
																								 in_applicationflag);
				EXCEPTION
					WHEN OTHERS THEN
						ROLLBACK;
						IF out_mesg IS NULL
						THEN
							out_mesg := 'Error While executing pkgworkflow.updateworkflow in sp_initiateworkflow ' ||
													SQLERRM;
						END IF;
						RAISE;
				END;
			END IF;
		END LOOP;
		IF out_mesg IS NULL
		THEN
			out_mesg := 'S';
		END IF;
	EXCEPTION
		WHEN OTHERS THEN
			ROLLBACK;
			IF out_mesg IS NULL
			THEN
				out_mesg := 'Error in sp_initiateworkflow procedure(sp_initiateworkflow) ' ||
										SQLERRM;
			END IF;
			RAISE;
	END sp_initiateworkflow;

	------------==============================
	/*================ To get workflowid ================*/
	PROCEDURE sp_getworkflowid
	(
		in_comp_appl_id    IN NUMBER,
		in_productcode     IN VARCHAR2,
		in_prodsubtypecode IN VARCHAR2,
		in_applicationflag IN VARCHAR2,
		in_companyid       IN NUMBER,
		in_userid          IN VARCHAR2,
		out_mesg           OUT VARCHAR2
	) AS
		vworkflowid workflow.workflowid%TYPE;
		--vautomaticflag     workflowstage.automaticflag%TYPE;
		pworkflowstageid workflowstage.workflowstageid%TYPE;
		pseqno           workflowstage.seqno%TYPE;
		pejbname         VARCHAR2(3000);
		v_cnt            NUMBER(2);
		v_logicstring    VARCHAR2(2000);
		v_exec_level     VARCHAR2(3);
		v_flag           VARCHAR2(2);
		v_pass_flag      NUMBER(1) := 0;
		CURSOR cur_workflow_id(v_companyid NUMBER, v_productcode VARCHAR2, v_prodsubtypecode VARCHAR2) IS
			SELECT wf.workflowid, conditionid
			FROM   workflow wf
			WHERE  (wf.productcode = v_productcode OR wf.productcode IS NULL)
			AND    (wf.prodsubtypecode = v_prodsubtypecode OR
						wf.prodsubtypecode IS NULL)
			AND    conditionid IS NOT NULL
			AND    wf.companyid = in_companyid
			AND    wf.applicationflag = in_applicationflag
			AND    wf.status <> 'X';
	BEGIN
		SELECT COUNT(*)
		INTO   v_cnt
		FROM   workflow
		WHERE  companyid = in_companyid -- Added Companyid filter for Multi-company functionality [50480, 03-DEC-07]
		AND    (productcode = in_productcode OR productcode IS NULL)
		AND    (prodsubtypecode = in_prodsubtypecode OR
					prodsubtypecode IS NULL)
		AND    conditionid IS NOT NULL
		AND    applicationflag = in_applicationflag
		AND    status <> 'X';
		IF v_cnt > 0
		THEN
			FOR rec_workflow_id IN cur_workflow_id(in_companyid,
																						 in_productcode,
																						 in_prodsubtypecode) LOOP
				BEGIN
					SELECT logicstring, execute_level
					INTO   v_logicstring, v_exec_level
					FROM   flt_logic
					WHERE  logicid = rec_workflow_id.conditionid;
				EXCEPTION
					WHEN no_data_found THEN
						IF out_mesg IS NULL
						THEN
							out_mesg := 'No Record exists in flt_logic for the given conditionid' || ' ' ||
													rec_workflow_id.conditionid;
						END IF;
						RAISE;
				END;
				v_flag := pkgexecstatement.sp_execute_statement(in_companyid,
																												in_comp_appl_id,
																												NULL,
																												NULL,
																												v_logicstring,
																												'AP',
																												'P');
				IF v_flag = 'P'
				THEN
					vworkflowid := rec_workflow_id.workflowid;
					v_pass_flag := 1;
					EXIT;
				END IF;
			END LOOP;
		END IF;
		IF v_pass_flag = 0
		THEN
			SELECT COUNT(*)
			INTO   v_cnt
			FROM   workflow
			WHERE  companyid = in_companyid -- Added Companyid filter for Multi-company functionality [50480, 03-DEC-07]
			AND    productcode = in_productcode
			AND    prodsubtypecode = in_prodsubtypecode
			AND    conditionid IS NULL
			AND    applicationflag = in_applicationflag
			AND    status <> 'X';
			IF v_cnt = 1
			THEN
				SELECT workflowid
				INTO   vworkflowid
				FROM   workflow
				WHERE  companyid = in_companyid -- Added Companyid filter for Multi-company functionality [50480, 03-DEC-07]
				AND    productcode = in_productcode
				AND    prodsubtypecode = in_prodsubtypecode
				AND    conditionid IS NULL
				AND    applicationflag = in_applicationflag
				AND    status <> 'X';
			ELSE
				BEGIN
					SELECT workflowid
					INTO   vworkflowid
					FROM   workflow
					WHERE  companyid = in_companyid -- Added Companyid filter for Multi-company functionality [50480, 03-DEC-07]
					AND    productcode = in_productcode
					AND    prodsubtypecode IS NULL
					AND    conditionid IS NULL
					AND    applicationflag = in_applicationflag
					AND    status <> 'X';
				EXCEPTION
					WHEN no_data_found THEN
						SELECT workflowid
						INTO   vworkflowid
						FROM   workflow
						WHERE  companyid = in_companyid -- Added Companyid filter for Multi-company functionality [50480, 03-DEC-07]
						AND    productcode IS NULL
						AND    prodsubtypecode IS NULL
						AND    conditionid IS NULL
						AND    applicationflag = in_applicationflag
						AND    status <> 'X';
				END;
			END IF;
		END IF;
		out_mesg := vworkflowid;
	EXCEPTION
		WHEN OTHERS THEN
			IF out_mesg IS NULL
			THEN
				out_mesg := 'Error while fetching Data from workflow table(sp_getworkflowid)';
			END IF;
			RAISE;
	END sp_getworkflowid;

	FUNCTION fn_tokeniser
	(
		inpstr    VARCHAR2,
		delimiter VARCHAR2 DEFAULT NULL
	) RETURN pkgglobalcursor.rsrefcursor IS
		v_list   tabletype := tabletype(); -- = strings:=strings();
		pos      NUMBER(8);
		loc      NUMBER(8);
		counter  NUMBER(8);
		out_data pkgglobalcursor.rsrefcursor;
	BEGIN
		--if the delimiter is absent in the string
		IF (NVL(INSTR(inpstr, delimiter, 1, 1), 0) = 0)
		THEN
			v_list.EXTEND;
			v_list(1) := inpstr;
		ELSE
			pos     := 1;
			counter := 0;
			LOOP
				EXIT WHEN pos > length(inpstr);
				counter := counter + 1;
				v_list.EXTEND;
				loc := NVL(INSTR(inpstr, delimiter, pos, 1), 0);
				IF loc = 0
				THEN
					v_list(counter) := ltrim(rtrim(SUBSTR(inpstr, pos)));
					EXIT;
				ELSE
					v_list(counter) := ltrim(rtrim(SUBSTR(inpstr, pos, loc - pos)));
					pos := loc + 1;
				END IF;
			END LOOP;
		END IF;
		OPEN out_data FOR
			SELECT * FROM TABLE(CAST(v_list AS tabletype));
		RETURN(out_data);
	END fn_tokeniser;

END pkgworkflow;
/