CREATE OR REPLACE PACKAGE BODY pkg_collworklist_tune IS
	gl_outmsg VARCHAR2(2000);
	----FUNCTION TO INSERT INTO LCT_WORKLIST_EMPLOYEE
	FUNCTION f_ins_worklist
	(
		pn_worklistid IN NUMBER,
		pn_empcode    IN NUMBER,
		pn_loanid     IN NUMBER,
		pv_status     IN VARCHAR2,
		pv_maker      IN VARCHAR2,
		pn_companyid  IN NUMBER
	) RETURN number  AS
		ln_serial lct_worklist_employee.worklistempid%TYPE;
  	BEGIN     
   		ln_serial := pkg_sequence_generation.set_sequence('LCT_WORKLIST_EMPLOYEE');
    INSERT INTO lct_worklist_employee
			(worklistempid, worklistid, employeeid, loanid, status, makerid,
			 makedate, companyid)
		VALUES
			(ln_serial, pn_worklistid, pn_empcode, pn_loanid, pv_status, pv_maker,
			 SYSDATE, pn_companyid);
   
		RETURN  1;
	EXCEPTION
		WHEN OTHERS THEN
		
			lv_sqlcode := SQLCODE;
			gl_outmsg  := fn_errconcat('-20510', fn_errconcat(lv_sqlcode), NULL);
				RETURN 0;
	END;
	-------------2.FUNCTON TO EVALUDATE CASE FOR PERTICULAR LOAN STAGE
	--a.it opens cursor containing logic string for all rule id
	--b.execute each logicstring
	--b.1. if it returns 'P' then return corresponding worklistid
	--b.2. if it returns multiple 'P' then return worklistid based on priority
	--b.3. if it doesnt return 'F' then return -1
	FUNCTION f_evalcase
	(
		pn_loanid VARCHAR2,
		pn_empid  NUMBER
	) RETURN NUMBER AS
  
    TYPE tab_companyid    IS TABLE OF lct_worklist.companyid%type  ;
    TYPE tab_worklistid   IS TABLE OF lct_worklist.worklistid%type ;
    TYPE tab_logicstring  IS TABLE OF flt_logic.logicstring%type ;
    TYPE tab_ruleid       IS TABLE OF lct_worklist.ruleid%type ;
    TYPE tab_priority     IS TABLE OF  lct_worklist.priority%type ;
    
    tv_companyid      tab_companyid;
    tv_worklistid     tab_worklistid;
    tv_logicstring    tab_logicstring;
    tv_ruleid         tab_ruleid;
    tv_priority       tab_priority ;
  
		
		ln_worklistid lct_worklist.worklistid%TYPE;
		lv_return     VARCHAR2(1);
    errors  number;
	BEGIN
  
     SELECT lw.companyid,lw.worklistid,fl.logicstring,
           lw.ruleid,	  lw.priority BULK COLLECT
     INTO  tv_companyid,tv_worklistid, tv_logicstring,
           tv_ruleid , tv_priority
         
			FROM   lct_worklist lw, flt_logic fl
			WHERE  fl.logicid = lw.ruleid
			AND    lw.status <> 'X'
			AND    fl.logicstring IS NOT NULL
			AND    lw.employeeid = pn_empid
			AND    fl.status <> 'X'
			AND    lw.companyid = gl_companyid
			ORDER  BY lw.priority;
   
		ln_worklistid := NULL;
		FOR  x IN tv_worklistid.first..tv_worklistid.count  loop--save EXCEPTIONS --  loop
			------------EXECUTE LOGICSTRING FOR LOAN STAGE ID .It will return either P or F
			BEGIN
				lv_return := pkgexecstatement.sp_execute_statement(tv_companyid(x),
																													 pn_loanid,
																													 NULL,
																													 'CO',
																													tv_logicstring(x) ,
																													 NULL,
																													 'P');
				CASE WHEN  lv_return = 'P' THEN
					ln_worklistid :=tv_worklistid(x) ;
				END CASE;
		
					lv_return     := 'F';
					ln_worklistid := NULL;
          RETURN ln_worklistid;
        END;
		END loop;
		RETURN ln_worklistid;
	EXCEPTION
		WHEN OTHERS THEN
			--added by ankur on 20/10/2007
			lv_sqlcode := SQLCODE;
			gl_outmsg  := fn_errconcat('-20511', fn_errconcat(lv_sqlcode), NULL);
			RETURN ln_worklistid;
	END;
	--------1. Procedure for worklist prioritisation for an employee
	---a.it will call evaluate function
	--b.after evaluation it will check for existing record in lct_worklist_employee table
	--c.if cheaks false then insert new record into lct_worklist_employee
	--d.if cheak turns true then delete existing stage and insert into history table
	PROCEDURE p_worklist_definition
	(
		pn_empcode   IN NUMBER,
		pv_maker     IN VARCHAR2,
		pn_companyid IN NUMBER,
		pv_out_msg   OUT VARCHAR2
	) IS
  
	ln_worklistid    lct_worklist.worklistid%TYPE;
  
    TYPE tab_lwd_loanid IS TABLE OF lct_workflowstage_dtl.loanid%type;
    tv_lwd_loanid tab_lwd_loanid;
    
    TYPE tab_lwd_stagestaus IS TABLE OF lct_workflowstage_dtl.stagestatus%type;
    tv_lwd_stagestaus tab_lwd_stagestaus;
    	
		e_parameter EXCEPTION;
    
    v_chk number(2);
     errors number;
     
    
   
	BEGIN
   	IF pn_empcode IS NULL OR pn_companyid IS NULL THEN
			RAISE e_parameter;
   	END IF;
    gl_outmsg    := NULL;
    
    ---New Changes----
    SELECT lwd.loanid ,lwd.stagestatus BULK COLLECT 
    INTO   tv_lwd_loanid ,tv_lwd_stagestaus
    FROM   lct_workflowstage_dtl lwd  
    WHERE  lwd.Stagestatus in ('P', 'D') 
    AND    status <> 'X' 
    AND    loanid IS NOT NULL
		AND    companyid=pn_companyid
		AND   lwd.employeecode=pn_empcode;
    ----New Changes---
   FOR i in 1..tv_lwd_loanid.count  loop 
        ln_worklistid := f_evalcase(tv_lwd_loanid(i), pn_empcode);
        
      FORALL j in 1..i/*tv_lwd_loanid.first..tv_lwd_loanid.last*/ SAVE EXCEPTIONS
          DELETE FROM lct_worklist_employee nologging WHERE loanid = tv_lwd_loanid(j) AND employeeid=pn_empcode;
          
				v_chk:= f_ins_worklist( ln_worklistid,
                        pn_empcode,
                        tv_lwd_loanid(i),
                        tv_lwd_stagestaus(i),
                        pv_maker,
                        pn_companyid);
        CASE WHEN  v_chk =1 then pv_out_msg := 'Success';
        ELSE  pv_out_msg :='Error';      
        END CASE;
		       
		END LOOP i;
	COMMIT;
  
 		
	EXCEPTION
 		WHEN e_parameter THEN
			pv_out_msg := fn_errconcat('-20822', NULL, NULL);
		WHEN OTHERS THEN
    
    errors := SQL%BULK_EXCEPTIONS.COUNT;
		FOR i IN 1 .. errors LOOP
			insert_error(-sql%BULK_EXCEPTIONS(i).ERROR_CODE,
									 SQLERRM(-sql%BULK_EXCEPTIONS(i).ERROR_CODE),
									 SQL%BULK_EXCEPTIONS(i).ERROR_INDEX,
									 'SP_PTP_REMINDERS',
									 'SP_PTP_REMINDERS');
			dbms_output.put_line('Error #' || i || ' at ' || 'iteration
			#' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
			dbms_output.put_line('Error message is ' ||
													 SQLERRM(-sql%BULK_EXCEPTIONS(i).ERROR_CODE));
		END LOOP;
             
			lv_sqlcode := SQLCODE;
			pv_out_msg := fn_errconcat('-20823', fn_errconcat(lv_sqlcode), NULL);
	END p_worklist_definition;
END pkg_collworklist_tune;