CREATE OR REPLACE PACKAGE pkg_collworklist1 IS

  
  gl_companyid company_generic.companyid%TYPE; --added  for multiple company
  PROCEDURE p_worklist_definition
  (
    pn_empcode   IN NUMBER,
    pv_maker     IN VARCHAR2,
    pn_companyid IN NUMBER, --Added for multiple company
    pv_out_msg   OUT VARCHAR2
  );

  FUNCTION f_evalcase
  (
    pn_loanid IN VARCHAR2,
    pn_empid  NUMBER
  ) RETURN NUMBER;

  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 BOOLEAN;

  FUNCTION f_chkcase(pn_loanid IN NUMBER) RETURN BOOLEAN;

  FUNCTION f_dlt_worklist(pn_loanid IN NUMBER) RETURN BOOLEAN;
  
 -- FUNCTION f_BULK_ERR_CALL

  lv_sqlcode NUMBER;

  /*bulk_errors EXCEPTION;
	PRAGMA EXCEPTION_INIT(bulk_errors, -24381);*/

END pkg_collworklist1;
/
CREATE OR REPLACE PACKAGE BODY pkg_collworklist1 IS
  li_count NUMBER(3);
  gl_outmsg VARCHAR2(2000);

  -----FUNCTION TO CHECK THE CASE ALREADY EXIST IN LCT_WORKLIST_EMPLOYEE
  FUNCTION f_chkcase(pn_loanid IN NUMBER) RETURN BOOLEAN AS
  BEGIN
    SELECT COUNT(ROWID)
    INTO   li_count
    FROM   lct_worklist_employee a
    WHERE  a.loanid = pn_loanid
    AND    a.status <> 'X';
    
    case when li_count > 0 then RETURN TRUE; --If is replace with Case By Sonali
    --IF li_count > 0 THEN      RETURN TRUE;
    ELSE   RETURN FALSE;End case;
    --END IF;
  EXCEPTION
    WHEN OTHERS THEN
      lv_sqlcode := SQLCODE;
      gl_outmsg  := fn_errconcat('-20508', fn_errconcat(lv_sqlcode), NULL);
      
      RETURN FALSE;
  END;
  
  

  -------FUNCTION TO DELETE FROM LCT_WORKLIST_EMPLOYEE
  FUNCTION f_dlt_worklist(pn_loanid IN NUMBER) RETURN BOOLEAN AS
  BEGIN
    ----INSERT OLD RECORD INTO HISTORY TABLE
   /* INSERT INTO lct_worklist_employee_hst
      SELECT * FROM lct_worklist_employee WHERE loanid = pn_loanid;*/--Added by sonali For Tuning date 27/Feb/2009
    ----DELETE OLD RECORD
    DELETE FROM lct_worklist_employee nologging WHERE loanid = pn_loanid;
    RETURN f_chkcase(pn_loanid);
  EXCEPTION
    WHEN OTHERS THEN
      lv_sqlcode := SQLCODE;
      gl_outmsg  := fn_errconcat('-20509', fn_errconcat(lv_sqlcode), NULL);
      RETURN FALSE;
  END;

  ----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 BOOLEAN 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 f_chkcase(pn_loanid);
  EXCEPTION
    WHEN OTHERS THEN
      --added by ankur on 20/10/2007
      lv_sqlcode := SQLCODE;
      gl_outmsg  := fn_errconcat('-20510', fn_errconcat(lv_sqlcode), NULL);
      --      gl_outmsg := fn_errconcat('-20510', gl_outmsg);
      RETURN FALSE;
  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
    
   /* CURSOR cur_lct_worklist IS
      SELECT lw.companyid,
             lw.worklistid,
             fl.logicstring,
             lw.ruleid,
             lw.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 lct_worklist.worklistid%TYPE;
    lv_return     VARCHAR2(1);
    ----Added by Sonali For Tunning Purpose date : 27/FEb/2009
    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 ;
  ----Added by Sonali For Tunning Purpose date : 27/FEb/2009
    
  BEGIN
  
  ----Added by Sonali For Tunning Purpose date : 27/FEb/2009
      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;
      
  ----Added by Sonali For Tunning Purpose date : 27/FEb/2009
  
  
  
    ln_worklistid := NULL;
    FOR x IN tv_worklistid.first..tv_worklistid.last/*cur_lct_worklist*/ LOOP
      ------------EXECUTE LOGICSTRING FOR LOAN STAGE ID .It will return either P or F
      BEGIN
        lv_return := pkgexecstatement.sp_execute_statement( tv_companyid(x)/*x.companyid*/,
                                                           pn_loanid,
                                                           NULL,
                                                           'CO',
                                                           tv_worklistid(x),
                                                           NULL,
                                                           'P');
        IF lv_return = 'P' THEN
          ln_worklistid := tv_worklistid(x);
          /* ELSIF lv_return = 'F' THEN
          ln_worklistid := NULL;
          EXIT;*/
        END IF;
      EXCEPTION
        WHEN OTHERS THEN
          lv_return     := 'F';
          ln_worklistid := NULL;
      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
  
 --Variable Declartion---
TYPE tab_collcifid IS TABLE OF lct_workflowstage_dtl .collcifid%Type ;
   tv_collcifid tab_collcifid;
  
   /* --CURSOR cur_empworklist IS
      SELECT \*DISTINCT*\ collcifid BULK COLLECT
      INTO   tv_collcifid
      FROM   lct_workflowstage_dtl lwd
      WHERE  lwd.employeecode = nvl(pn_empcode, lwd.employeecode)
      AND    stagestatus IN ('P', 'D')
      AND    status <> 'X'
      AND    companyid = gl_companyid --Added for multiple company
      AND    loanid IS NOT NULL
      group by collcifid ;*/--added by Sonali For Tuning 
      
    r_cur            pkgglobalcursor.rsrefcursor;
    lr_workflowstage lct_workflowstage_dtl%ROWTYPE;
    ln_worklistid    lct_worklist.worklistid%TYPE;
    lv_filter        VARCHAR2(1000);
    
    e_parameter EXCEPTION;
    
  BEGIN
      SELECT /*DISTINCT*/ collcifid BULK COLLECT
      INTO   tv_collcifid
      FROM   lct_workflowstage_dtl lwd
      WHERE  lwd.employeecode = nvl(pn_empcode, lwd.employeecode)
      AND    stagestatus IN ('P', 'D')
      AND    status <> 'X'
      AND    companyid = gl_companyid --Added for multiple company
      AND    loanid IS NOT NULL
      group by collcifid ;--added by Sonali For Tuning 
  
  
    IF pn_empcode IS NULL OR pn_companyid IS NULL THEN
      RAISE e_parameter;
    END IF;
    gl_companyid := pn_companyid; --Added for multiple company
    gl_outmsg    := NULL;
    lv_filter    := 'Stagestatus in (''P'', ''D'')
         AND status <> ''X''
         AND companyid=' || gl_companyid ||
                    ' AND loanid IS NOT NULL';
    FOR cur_var IN  tv_collcifid.first..tv_collcifid.last/*cur_empworklist */LOOP
      OPEN r_cur FOR 'SELECT * FROM lct_workflowstage_dtl WHERE collcifid=' ||tv_collcifid(cur_var) /*cur_var.collcifid*/ || 'and ' || lv_filter;
      -------function call to evaluate case for loanstageid and return worklistid based on evaluation logic
      LOOP
        FETCH r_cur
          INTO lr_workflowstage;
        EXIT WHEN r_cur%NOTFOUND;
        ln_worklistid := f_evalcase(lr_workflowstage.loanid, pn_empcode);
        /*IF ln_worklistid IS NULL THEN
          gl_outmsg := fn_errconcat('-20525', gl_outmsg, cur_var.collcifid);
          EXIT;
        END IF;*/
        IF f_chkcase(lr_workflowstage.loanid) = FALSE THEN
          IF f_ins_worklist(ln_worklistid,
                            pn_empcode,
                            lr_workflowstage.loanid,
                            lr_workflowstage.stagestatus,
                            pv_maker,
                            lr_workflowstage.companyid) = TRUE THEN
            pv_out_msg := 'Success';
          END IF;
        ELSE
          IF f_dlt_worklist(lr_workflowstage.loanid) = FALSE THEN
            IF f_ins_worklist(ln_worklistid,
                              pn_empcode,
                              lr_workflowstage.loanid,
                              lr_workflowstage.stagestatus,
                              pv_maker,
                              lr_workflowstage.companyid) = TRUE THEN
              pv_out_msg := 'Success';
            END IF;
          END IF;
        END IF;
      END LOOP;
      CLOSE r_cur;
    END LOOP;
    /* IF instr(gl_outmsg, '20525') > 0 THEN
      pv_out_msg := gl_outmsg;
      --  ROLLBACK;
    ELSE*/
    COMMIT;
    -- END IF;
  EXCEPTION
    WHEN e_parameter THEN
      pv_out_msg := fn_errconcat('-20822', NULL, NULL);
    WHEN OTHERS THEN
      lv_sqlcode := SQLCODE;
      pv_out_msg := fn_errconcat('-20823', fn_errconcat(lv_sqlcode), NULL);
  END p_worklist_definition;

END pkg_collworklist1;
/