ALTER TABLE USERMASTEREDITED 
ADD (USERLOCKED NUMBER DEFAULT 0 );
/

create or replace PROCEDURE "UDPINSERTUPDATEUSERMASTER" (
    p_userid           IN NUMBER DEFAULT 0,
    p_roleid           IN VARCHAR2 DEFAULT NULL,
    p_employeeid       IN NUMBER DEFAULT 0,
    p_username         IN VARCHAR2 DEFAULT NULL,
    p_firstname        IN VARCHAR2 DEFAULT NULL,
    p_middlename       IN VARCHAR2 DEFAULT NULL,
    p_lastname         IN VARCHAR2 DEFAULT NULL,
    p_password         IN NVARCHAR2 DEFAULT NULL,
    p_gender           IN VARCHAR2 DEFAULT NULL,
    p_birthdate        IN VARCHAR2 DEFAULT NULL,
    p_email            IN VARCHAR2 DEFAULT NULL,
    p_email2            IN VARCHAR2 DEFAULT NULL,
    p_mobilenumber     IN VARCHAR2 DEFAULT NULL,
    p_altmobilenumber  IN VARCHAR2 DEFAULT NULL,
    p_transactionlimit IN NUMBER DEFAULT 0,
    p_address          IN VARCHAR2 DEFAULT NULL,
    p_altaddress       IN VARCHAR2 DEFAULT NULL,
    p_countryid        IN NUMBER DEFAULT 0,
    p_stateid          IN NUMBER DEFAULT 0,
    p_cityid           IN NUMBER DEFAULT 0,
    p_pincode          IN VARCHAR2 DEFAULT NULL,
    p_isactive         IN VARCHAR2 DEFAULT NULL,
    p_limit            IN NUMBER DEFAULT 0,
    p_usertype         IN NUMBER DEFAULT 0,
    p_bankcorpuser     IN NUMBER DEFAULT 0,
    p_branchuser       IN NUMBER DEFAULT 0,
    p_createdby        IN NUMBER DEFAULT 0,
    p_updateby         IN NUMBER DEFAULT 0,
    p_loggedinuser     IN NUMBER DEFAULT 0,
    p_reviewstatus     IN NUMBER DEFAULT 0,
    p_FinacleUserID    IN VARCHAR2 DEFAULT NULL,
    p_AssociatedAcNo   IN VARCHAR2 DEFAULT NULL,
	p_NetBankingLoginId IN VARCHAR2 DEFAULT NULL,
    p_isMobileApp     IN NUMBER DEFAULT 0,
    p_USERLOCKED     IN NUMBER DEFAULT 0,
    v_cursor OUT SYS_REFCURSOR,
    v_xml CLOB DEFAULT NULL )
IS
  v_useridforrole NUMBER(10);
  v_tempeml       NUMBER(10);
  v_emailtemp     NUMBER(10);
  v_tempupt       NUMBER(10);
  v_tempmbl       NUMBER(10);
  v_mobiletemp    NUMBER(10);
  v_corpuser      NUMBER(10);
  v_temp1         NUMBER(10);
  v_temp2         NUMBER(10);
  v_temp3         NUMBER(10);
  p_message       VARCHAR2(100);
  p_id            NUMBER(10);
  r_oldroleids    VARCHAR2(1500);
  r_newroleids    VARCHAR2(1500);
  r_usermaster usermaster%rowtype;
  r_oldusermaster usermaster%rowtype;
  v_checkeraccess NUMBER(1, 0);
  v_oldmiddlename VARCHAR2(200) := NULL;
  v_selfchecker   NUMBER(10)    := 0;
  r_usertype      NUMBER(1)     := 0;
  v_newusrtype usermaster%rowtype;
  v_oldpassword VARCHAR2(100 BYTE);
  v_newpassword VARCHAR2(100 BYTE);
  v_OldStatus   NUMBER(10);
  v_accountid   NUMBER(18);
  v_custId      NUMBER(18) := 0;
  v_ismobileapp   NUMBER(1);
  v_oldNetBankingLoginId VARCHAR2(200) := NULL;
  m_userMaster UserMaster%ROWTYPE;
BEGIN

  BEGIN  
    SELECT * INTO r_oldusermaster FROM usermaster WHERE userid = p_userid;
  EXCEPTION 
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('-');
  END;


  INSERT INTO UserAccountMappingTmp
    (USERID,CUSTOMERID,ACCOUNTID,STATUS
    )
  SELECT USERID ,
    CUSTOMERID,
    ACCOUNTID,
    STATUS
  FROM XMLTable('/rows/AssociatedAcNoCustomer' PASSING XMLTYPE(v_xml) COLUMNS USERID NUMBER(10) path 'UserId', CUSTOMERID NUMBER(10) path 'CustomerId', ACCOUNTID NUMBER(10) path 'AccountId', STATUS VARCHAR(1) path 'Status' );
--  IF( p_usertype = 2 ) THEN
--    SELECT CUSTOMERID INTO v_custId FROM UserAccountMappingTmp WHERE ROWNUM = 1;
--  END IF;
  --TO CHECK CHECKER ACCESS
  SELECT MAX(checker)
  INTO v_checkeraccess
  FROM userrole ur
  JOIN rolesmodule rm
  ON ur.roleid    = rm.roleid
  WHERE ur.userid = p_loggedinuser
  AND moduleid    = 4;
  IF ( p_userid  <> 0) THEN
    SELECT usertype INTO r_usertype FROM usermaster WHERE userid = p_userid;
    DELETE
    FROM usercustomer
    WHERE ucuserid   = p_userid
    AND ((p_usertype = 1
    AND r_usertype  != 1)
    OR (p_usertype   = 2)
    OR (p_usertype   = 3));
     INSERT INTO DEBUGSP VALUES ('inserted 3333'); 
    --DELETE FROM usercustomer WHERE ucuserid = p_userid and ((p_usertype = 1 AND r_usertype != 1) or (p_usertype = 3));
    INSERT
    INTO usercustomer
      (

        uccustomerid, ucuserid,
        uccreatedby,
        uccreateddate
      )
    SELECT 
      3707 uccustomerid, p_userid ucuserid,
      p_userid uccreatedby,
      systimestamp uccreateddate
    FROM customermaster
    WHERE p_usertype = 1
    AND r_usertype  != 1
    UNION ALL
    SELECT  3707 ,p_userid,p_userid,systimestamp FROM dual WHERE p_usertype = 2
    UNION ALL
    SELECT 
     Distinct customerid,p_userid,
      p_userid,
      systimestamp
    FROM accountmaster
    WHERE branchid      = p_branchuser
    AND p_usertype      = 3;
    IF(v_checkerAccess !=0 AND p_ReviewStatus IN(2,5,8,3)) THEN
      SELECT listagg(rolename, ', ')
      INTO r_oldroleids
      FROM userrole t2
      JOIN roles r
      ON r.roleid           = t2.roleid
      WHERE t2.userid       = p_userid
      AND userpriviouserole = 1;
      SELECT listagg(rolename, ', ')
      INTO r_newroleids
      FROM userrole t2
      JOIN roles r
      ON r.roleid           = t2.roleid
      WHERE t2.userid       = p_userid
      AND userpriviouserole = 0;
    END IF;
    UPDATE userrole SET userpriviouserole = 1 WHERE userid = p_userid;
    v_useridforrole := p_userid;
    --when maker edited the approved record update IsLastApproved flag to 1
    SELECT isactive
    INTO v_OldStatus
    FROM UserMaster
    WHERE UserId = p_userid;
    UPDATE UserMaster
    SET islastapproved = 1
    WHERE UserId       = p_userid
    AND v_OldStatus    = 1
    AND p_isactive     = 2;
    UPDATE usermasteredited
    SET firstname             = p_firstname,
      middlename              = p_middlename,
      lastname                = p_lastname,
      gender                  = p_gender,
      birthdate               = p_birthdate,
      email                   = p_email,
      Email2                  = p_email2,
      mobilenumber            = p_mobilenumber,
      altmobilenumber         = p_altmobilenumber,
      address                 = p_address,
      alternateaddress        = p_altaddress,
      countryid               = p_countryid,
      stateid                 = p_stateid,
      cityid                  = p_cityid,
      pincode                 = p_pincode,
      isactive                = p_isactive,
      limit                   = p_limit,
      usertype                = p_usertype,
      bankcorpuser            = NVL(p_bankcorpuser, v_custId),
      branchuser              = p_branchuser,
      updatedby               = p_updateby,
      updateddate             = sysdate,
      transactionlimit        = p_transactionlimit,
      reviewstatus            = p_reviewstatus,
      FINACLEUSERID           = p_FinacleUserID,
      ASSOCIATEDACCOUNTNUMBER = p_AssociatedAcNo,
	  NETBANKINGLOGINID  =  p_NetBankingLoginId,
      ismobileapp            = p_isMobileApp,
      USERLOCKED  =  p_USERLOCKED,
      --,password = (case when p_ReviewStatus not in(1,4) or nvl(p_password,'') = '' then password else to_char(p_password) end)
      password   = NVL(p_password,password)
    WHERE userid = p_userid;
    --reject
    IF(v_checkerAccess !=0 AND p_ReviewStatus IN(3,6,9)) THEN
      SELECT * INTO m_userMaster FROM usermaster WHERE userid = p_userid;
      SELECT * INTO v_newusrtype FROM usermaster WHERE userid = p_userid;
      DELETE
      FROM usercustomer
      WHERE ucuserid             = p_userid
      AND v_newusrtype.usertype IN (1,2,3);
      INSERT INTO DEBUGSP VALUES ('inserted 2222'); 
      --delete from usercustomer where ucuserid = p_userid and v_newusrtype.usertype in (1,3);
      INSERT
      INTO usercustomer
        (       
          uccustomerid, 
          ucuserid,
          uccreatedby,
          uccreateddate
        )
      SELECT  3707 ccustomerid,
      p_userid ucuserid,
        p_userid uccreatedby,
        systimestamp uccreateddate
      FROM customermaster
      WHERE v_newusrtype.usertype = 1
      UNION ALL
      SELECT 
        v_newusrtype.bankcorpuser,p_userid,
        p_userid,
        systimestamp
      FROM dual
      WHERE v_newusrtype.usertype = 2
      UNION ALL
      SELECT Distinct customerid,p_userid,
        p_userid,
        systimestamp
      FROM accountmaster
      WHERE branchid            = v_newusrtype.branchuser
      AND v_newusrtype.usertype = 3;
      UPDATE usermaster
      SET reviewstatus = p_ReviewStatus,
        isactive       = (
        CASE
          WHEN m_userMaster.islastapproved = 1
          THEN 1
          ELSE 0
        END),
        updatedby   = p_updateby,
        updateddate = sysdate
      WHERE userid  = p_userid;
      UPDATE usermasteredited ue2
      SET
        (
          firstname,
          middlename,
          lastname,
          gender,
          birthdate,
          email,
          mobilenumber,
          altmobilenumber,
          address,
          alternateaddress,
          countryid,
          stateid,
          cityid,
          pincode,
          limit,
          usertype,
          bankcorpuser,
          branchuser,
          updatedby,
          updateddate,
          transactionlimit,
          email2,
          isactive,
          reviewstatus,
          password,
		  NETBANKINGLOGINID,
          ismobileapp
        )
        =
        (SELECT firstname,
          middlename,
          lastname,
          gender,
          birthdate,
          email,
          mobilenumber,
          altmobilenumber,
          address,
          alternateaddress,
          countryid,
          stateid,
          cityid,
          pincode,
          limit,
          usertype,
          bankcorpuser,
          branchuser,
          updatedby,
          updateddate,
          transactionlimit,
          email2,
          (
          CASE
            WHEN m_userMaster.islastapproved = 1
            THEN 1
            ELSE 0
          END),
          6,
          password,
		  NETBANKINGLOGINID,
          ismobileapp
        FROM usermaster ue1
        WHERE ue1.userid = ue2.userid
        )
      WHERE userid = p_userid;
      SELECT password
      INTO v_newpassword
      FROM usermasteredited
      WHERE userid = p_userid;
      SELECT password INTO v_oldpassword FROM usermaster WHERE userid = p_userid;
      UPDATE usermasteredited
      SET password        = v_oldpassword
      WHERE userid        = p_userid
      AND v_oldpassword  != v_newpassword
      AND p_reviewstatus IN (3,9);
      p_message          := 'User details Rejected Successfully';
      DELETE FROM userrole WHERE userid = p_userid AND userpriviouserole = 1;
      DELETE FROM UserAccountMapping WHERE status = 'A' AND USERID = p_userid;
      UPDATE UserAccountMapping
      SET status   = 'C'
      WHERE status = 'D'
      AND USERID   = p_userid;
    END IF;
    --approve
    IF(v_checkerAccess !=0 AND p_ReviewStatus IN(2,5,8)) THEN
      UPDATE usermaster
      SET firstname             = p_firstname,
        middlename              = p_middlename,
        lastname                = p_lastname,
        gender                  = p_gender,
        birthdate               = p_birthdate,
        email                   = p_email,
        mobilenumber            = p_mobilenumber,
        altmobilenumber         = p_altmobilenumber,
        address                 = p_address,
        alternateaddress        = p_altaddress,
        countryid               = p_countryid,
        stateid                 = p_stateid,
        cityid                  = p_cityid,
        pincode                 = p_pincode,
        isactive                = p_isactive,
        limit                   = p_limit,
        usertype                = p_usertype,
        bankcorpuser            = NVL(p_bankcorpuser, v_custId),
        branchuser              = p_branchuser,
        updatedby               = p_updateby,
        updateddate             = sysdate,
        transactionlimit        = p_transactionlimit,
        reviewstatus            = p_reviewstatus,
        FINACLEUSERID           = p_FinacleUserID,
        Email2                  = p_email2,
        ASSOCIATEDACCOUNTNUMBER = p_AssociatedAcNo,
		NETBANKINGLOGINID = p_NetBankingLoginId,
        ismobileapp      = p_isMobileApp  ,
        USERLOCKED  =  p_USERLOCKED
      WHERE userid              = p_userid;
      UPDATE usermaster
      SET password =
        (SELECT password FROM usermasteredited WHERE userid = p_userid
        )
      WHERE userid = p_userid;
      UPDATE usermaster
      SET islastapproved = 0
      WHERE userid       = p_userid
      AND p_isactive     = 1;
      DELETE FROM userrole WHERE userid = p_userid AND userpriviouserole = 1;
      p_message := 'User details Approved Successfully';
      DELETE FROM UserAccountMapping WHERE status = 'D' AND USERID = p_userid;
      UPDATE UserAccountMapping SET status = 'C' WHERE USERID = p_userid;
    END IF;
    --edit
    IF p_ReviewStatus IN(1,4) THEN
      UPDATE usermaster
      SET reviewstatus = p_reviewstatus,
        isactive       = p_isactive,
        updatedby      = p_updateby,
        updateddate    = sysdate
      WHERE userid     = p_userid;
      p_message       := 'User information has been sent test to checker for review';
      INSERT INTO DEBUGSP VALUES ('inserted - 1');
      DELETE FROM UserAccountMapping WHERE USERID = p_userid;
      INSERT INTO UserAccountMapping
        (USERID,CUSTOMERID,ACCOUNTID,STATUS
        )
      SELECT p_userid,
        CUSTOMERID,
        ACCOUNTID,
        STATUS
      FROM UserAccountMappingTmp
      WHERE USERID = p_userid;
    END IF;
    INSERT INTO DEBUGSP VALUES
      ('inserted'
      );
    --audit trail
--       IF( p_ReviewStatus IN(5,6)) THEN
--    INSERT
--   INTO useraudithistory
--    (
--      userid ,
--      fieldName ,
--      oldValue ,
--      newValue ,
--      createdBy ,
--      createDate,
--      action
--    )
--  SELECT p_userid userid,
--    'Request Type' FIELDNAME,
--    'Edit Approval Pending' OLDVALUE,
--    CASE
--      WHEN p_reviewStatus = 5
--      THEN 'Edit Approved'
--      WHEN p_reviewStatus = 6
--      THEN 'Edit Rejected'
--      ELSE ''
--    END NEWVALUE,
--    to_number(p_loggedinuser) CREATEDBY,
--    sysdate CREATEDATE,
--    1 ACTION
-- 
--  FROM dual;
--END IF; 
  IF(v_checkerAccess !=0 AND p_ReviewStatus IN(2,5,8,3,6)) THEN
    SELECT listagg(rolename, ', ')
    INTO r_oldroleids
    FROM userrole t2
    JOIN roles r
    ON r.roleid           = t2.roleid
    WHERE t2.userid       = p_userid
    AND userpriviouserole = 1;
    SELECT listagg(rolename, ', ')
    INTO r_newroleids
    FROM userrole t2
    JOIN roles r
    ON r.roleid           = t2.roleid
    WHERE t2.userid       = p_userid
    AND userpriviouserole = 0;
    SELECT(
      CASE
        WHEN middlename IS NULL
        THEN '9'
        ELSE middlename
      END)
    INTO v_oldmiddlename
    FROM usermaster
    WHERE userid = p_userid;

    SELECT * INTO r_usermaster FROM usermaster WHERE userid = p_userid;
    INSERT
    INTO useraudithistory
      (
        userid,
        fieldname,
        oldvalue,
        newvalue,
        createdby,
        createdate,
        action
      )
   SELECT v_useridforrole useraudithistory,
     'UserRole' fieldname,
     r_oldroleids oldvalue,
      r_newroleids newvalue,
      to_number(p_updateby) createdby,
      sysdate createdate,
      1 action
    FROM dual
    WHERE r_newroleids != r_oldroleids
    UNION ALL
    SELECT p_userid userid,
      'Request Type' FIELDNAME,
      CASE
        WHEN r_oldusermaster.reviewStatus = 1 THEN 'Add Approval Pending'
        WHEN r_oldusermaster.reviewStatus = 2 THEN 'Add Approved'
        WHEN r_oldusermaster.reviewStatus = 3 THEN 'Add Rejected'
        WHEN r_oldusermaster.reviewStatus = 4 THEN 'Edit Approval Pending'
        WHEN r_oldusermaster.reviewStatus = 5 THEN 'Edit Approved'
        WHEN r_oldusermaster.reviewStatus = 6 THEN 'Edit Rejected'
        WHEN r_oldusermaster.reviewStatus = 7 THEN 'Delete Approval pending'
        WHEN r_oldusermaster.reviewStatus = 8 THEN 'Delete Approved'
        WHEN r_oldusermaster.reviewStatus = 9 THEN 'Delete Rejected'
      ELSE '' END 
            OLDVALUE,
      CASE
        WHEN p_reviewStatus = 1 THEN 'Add Approval Pending'
        WHEN p_reviewStatus = 2 THEN 'Add Approved'
        WHEN p_reviewStatus = 3 THEN 'Add Rejected'
        WHEN p_reviewStatus = 4 THEN 'Edit Approval Pending'
        WHEN p_reviewStatus = 5 THEN 'Edit Approved'
        WHEN p_reviewStatus = 6 THEN 'Edit Rejected'
        WHEN p_reviewStatus = 7 THEN 'Delete Approval pending'
        WHEN p_reviewStatus = 8 THEN 'Delete Approved'
        WHEN p_reviewStatus = 9 THEN 'Delete Rejected'
        ELSE ''
      END NEWVALUE,
      to_number(p_loggedinuser) CREATEDBY,
      sysdate CREATEDDATE,
      1 ACTION
    FROM dual
    UNION ALL
    SELECT p_userid,
      'Status',
      'Pending',
      CASE
        WHEN p_isactive = 1
        THEN 'Active'
        WHEN p_isactive = 0
        THEN 'Inactive'
        ELSE ''
      END,
      to_number(p_loggedinuser),
      sysdate,
      1
    FROM dual
    UNION ALL
    SELECT p_userid userid,
      'First Name' FIELDNAME,
      r_usermaster.firstname OLDVALUE,
      p_firstname newvalue,
      to_number(r_usermaster.createdby) CREATEDBY,
      sysdate CREATEDDATE,
      1 ACTION
    FROM dual
    WHERE r_usermaster.firstname != p_firstname
    UNION ALL
    SELECT p_userid,
      '',
      '',
      '',
      to_number(r_usermaster.createdby),
      sysdate,
      0
    FROM dual
    WHERE r_usermaster.isactive != p_isactive
    AND p_reviewstatus           = 2
    OR p_reviewstatus            = 3
    UNION ALL
    SELECT p_userid,
      '',
      '',
      '',
      to_number(r_usermaster.createdby),
      sysdate,
      2
    FROM dual
    WHERE r_usermaster.isactive != p_isactive
    AND p_reviewstatus           = 8
    OR p_reviewstatus            = 9
    UNION ALL
    SELECT p_userid,
      'Status',
      CASE
        WHEN r_usermaster.isactive = '1'
        THEN 'Active'
        WHEN r_usermaster.isactive = '0'
        THEN 'Inactive'
        WHEN r_usermaster.isactive = '2'
        THEN 'Pending'
      END,
      CASE
        WHEN ( p_reviewstatus = 2
        OR p_reviewstatus     = 5
        OR p_reviewstatus     = 6
        OR p_reviewstatus     = 9 )
        THEN 'Active'
        WHEN ( p_reviewstatus = 3
        OR p_reviewstatus     = 8 )
        THEN 'Inactive'
        WHEN ( p_reviewstatus = 1
        OR p_reviewstatus     = 3
        OR p_reviewstatus     = 4 )
        THEN 'Pending'
      END,
      to_number(r_usermaster.createdby),
      sysdate,
      1
    FROM dual
    WHERE r_usermaster.isactive != p_isactive
    UNION ALL
    SELECT p_userid,
      'Last Name',
      r_usermaster.lastname,
      p_lastname,
      to_number(r_usermaster.createdby),
      sysdate,
      1
    FROM dual
    WHERE r_usermaster.lastname != p_lastname
    UNION ALL
    SELECT p_userid,
      'Password ',
      'Password changed',
      'Password changed',
      to_number(r_usermaster.createdby),
      sysdate,
      1
    FROM dual
    WHERE p_password          != ''
    AND r_usermaster.password != p_password
    UNION ALL
    SELECT p_userid,
      'Gender',
      r_usermaster.gender,
      p_gender,
      to_number(r_usermaster.createdby),
      sysdate,
      1
    FROM dual
    WHERE r_usermaster.gender != p_gender
    UNION ALL
    SELECT p_userid,
      'Mobile Number ',
      r_usermaster.mobilenumber,
      p_mobilenumber,
      to_number(r_usermaster.createdby),
      sysdate,
      1
    FROM dual
    WHERE r_usermaster.mobilenumber != p_mobilenumber
    UNION ALL
    SELECT p_userid,
      'Other Number',
      r_usermaster.altmobilenumber,
      p_altmobilenumber,
      to_number(r_usermaster.createdby),
      sysdate,
      1
    FROM dual
    WHERE r_usermaster.altmobilenumber != p_altmobilenumber
    UNION ALL
    SELECT p_userid,
      'Address',
      r_usermaster.address,
      p_address,
      to_number(r_usermaster.createdby),
      sysdate,
      1
    FROM dual
    WHERE r_usermaster.address != p_address
    UNION ALL
    SELECT p_userid,
      'AlternateAddress',
      r_usermaster.alternateaddress,
      p_altaddress,
      to_number(r_usermaster.createdby),
      sysdate,
      1
    FROM dual
    WHERE r_usermaster.alternateaddress != p_altaddress
    UNION ALL
    SELECT p_userid,
      'CountryId',
      TO_CHAR(r_usermaster.countryid),
      TO_CHAR(p_countryid),
      to_number(r_usermaster.createdby),
      sysdate,
      1
    FROM dual
    WHERE r_usermaster.countryid != p_countryid
    UNION ALL
    SELECT p_userid,
      'StateId',
      TO_CHAR(r_usermaster.stateid),
      TO_CHAR(p_stateid),
      to_number(r_usermaster.createdby),
      sysdate,
      1
    FROM dual
    WHERE r_usermaster.stateid != p_stateid
    UNION ALL
    SELECT p_userid,
      'CityId',
      TO_CHAR(r_usermaster.cityid),
      TO_CHAR(p_cityid),
      to_number(r_usermaster.createdby),
      sysdate,
      1
    FROM dual
    WHERE r_usermaster.cityid != p_cityid
    UNION ALL
    SELECT p_userid,
      'Pincode',
      r_usermaster.pincode,
      p_pincode,
      to_number(r_usermaster.createdby),
      sysdate,
      1
    FROM dual
    WHERE r_usermaster.pincode != p_pincode
    UNION ALL
    SELECT p_userid,
      'limit',
      TO_CHAR(r_usermaster.limit),
      TO_CHAR(p_limit),
      to_number(r_usermaster.createdby),
      sysdate,
      1
    FROM dual
    WHERE r_usermaster.limit != p_limit
    UNION ALL
    SELECT p_userid,
      'User Type',
      CASE
        WHEN r_usermaster.usertype = 1
        THEN 'Bank User'
        WHEN r_usermaster.usertype = 2
        THEN 'Corporate User'
        WHEN r_usermaster.usertype = 3
        THEN 'Branch User'
      END,
      CASE
        WHEN p_usertype = 1
        THEN 'Bank User'
        WHEN p_usertype = 2
        THEN 'Corporate User'
        WHEN p_usertype = 3
        THEN 'Branch User'
      END,
      to_number(r_usermaster.createdby),
      sysdate,
      1
    FROM dual
    WHERE r_usermaster.usertype != p_usertype
    UNION ALL
    SELECT p_userid,
      'Corporate',
      (SELECT customername
      FROM customermaster
      WHERE custpkid = r_usermaster.bankcorpuser
      ),
      (SELECT customername
      FROM customermaster
      WHERE custpkid = NVL(p_bankcorpuser, v_custId)
      ),
      to_number(r_usermaster.createdby),
      sysdate,
      1
    FROM dual
    WHERE r_usermaster.bankcorpuser != NVL(p_bankcorpuser, v_custId)
    UNION ALL
    SELECT p_userid,
      'Branch ',
      (SELECT branchname
      FROM branchmaster
      WHERE branchid = r_usermaster.branchuser
      ),
      (SELECT branchname FROM branchmaster WHERE branchid = p_branchuser
      ),
      to_number(r_usermaster.createdby),
      sysdate,
      1
    FROM dual
    WHERE r_usermaster.branchuser != p_branchuser
    UNION ALL
    SELECT p_userid,
      'Transaction Limit',
      TO_CHAR(r_usermaster.transactionlimit),
      TO_CHAR(p_transactionlimit),
      to_number(r_usermaster.createdby),
      sysdate,
      1
    FROM dual
    WHERE r_usermaster.transactionlimit != p_transactionlimit
    UNION ALL
    SELECT p_userid,
      'Email Id (Username) ',
      r_usermaster.email,
      p_email,
      to_number(r_usermaster.createdby),
      sysdate,
      1
    FROM dual
    WHERE r_usermaster.email != p_email
    UNION ALL
    SELECT p_userid,
      'Birth Date',
     r_usermaster.birthdate,
      p_birthdate,
      to_number(r_usermaster.createdby),
      sysdate,
      1
    FROM dual
    WHERE r_usermaster.birthdate != p_birthdate
    UNION ALL
    SELECT p_userid,
      'Middle Name',
      r_usermaster.middlename,
      p_middlename,
      to_number(r_usermaster.createdby),
      sysdate,
      1
    FROM dual
    WHERE v_oldmiddlename != p_middlename
    UNION ALL
    SELECT p_userid,
      'Middle Name',
      r_usermaster.middlename,
      p_middlename,
      to_number(r_usermaster.createdby),
      sysdate,
      1
    FROM dual
    WHERE p_middlename  IS NULL
    AND v_oldmiddlename != '9'
	UNION ALL
    SELECT p_userid,
      'Net Banking Login Id',
      r_usermaster.NETBANKINGLOGINID,
      p_NetBankingLoginId,
      to_number(r_usermaster.createdby),
      sysdate,
      1
    FROM dual
    WHERE r_usermaster.NETBANKINGLOGINID != p_NetBankingLoginId;
  END IF;
ELSE
  --create

  Select MAX(USERID)+ 1  into v_useridforrole From Usermaster;
  INSERT
  INTO usermaster
    (
    userid,
      employeeid,
      username,
      firstname,
      middlename,
      lastname,
      password,
      gender,
      birthdate,
      email,
      email2,
      mobilenumber,
      altmobilenumber,
      transactionlimit,
      address,
      alternateaddress,
      countryid,
      stateid,
      cityid,
      pincode,
      isactive,
      createdby,
      createddate,
      limit,
      usertype,
      bankcorpuser,
      branchuser,
      reviewstatus,
      FINACLEUSERID,
      ASSOCIATEDACCOUNTNUMBER,
	  NETBANKINGLOGINID,
      ismobileapp,
      USERLOCKED 
    )
    VALUES
    (
      v_useridforrole,
      p_employeeid,
      p_username,
      p_firstname,
      p_middlename,
      p_lastname,
      p_password,
      p_gender,
      p_birthdate,
      p_email,
      p_email2,
      p_mobilenumber,
      p_altmobilenumber,
      p_transactionlimit,
      p_address,
      p_altaddress,
      p_countryid,
      p_stateid,
      p_cityid,
      p_pincode,
      p_isactive,
      p_createdby,
      sysdate,
      p_limit,
      p_usertype,
      NVL(p_bankcorpuser, v_custId),
      p_branchuser,
      p_reviewstatus,
      p_FinacleUserID,
      p_AssociatedAcNo,
	  p_NetBankingLoginId,
      p_isMobileApp,
      p_USERLOCKED
    )
  RETURNING userid
  INTO v_useridforrole;

     INSERT INTO PASSHISTORY (USERID, PASSWORD, CREATIONDATE)
    VALUES (v_useridforrole,   p_password , SYSDATE);

  INSERT
  INTO usermasteredited
    (
      userid,
      employeeid,
      username,
      firstname,
      middlename,
      lastname,
      password,
      gender,
      birthdate,
      email,
      email2,
      mobilenumber,
      altmobilenumber,
      transactionlimit,
      address,
      alternateaddress,
      countryid,
      stateid,
      cityid,
      pincode,
      isactive,
      createdby,
      createddate,
      limit,
      usertype,
      bankcorpuser,
      branchuser,
      reviewstatus,
      FINACLEUSERID,
      ASSOCIATEDACCOUNTNUMBER,
	  NETBANKINGLOGINID,
      ismobileapp,
      USERLOCKED    
    )
    VALUES
    (
      v_useridforrole,
      p_employeeid,
      p_username,
      p_firstname,
      p_middlename,
      p_lastname,
      p_password,
      p_gender,
      p_birthdate,
      p_email,
      p_email2,
      p_mobilenumber,
      p_altmobilenumber,
      p_transactionlimit,
      p_address,
      p_altaddress,
      p_countryid,
      p_stateid,
      p_cityid,
      p_pincode,
      p_isactive,
      p_createdby,
      sysdate,
      p_limit,
      p_usertype,
      NVL(p_bankcorpuser, v_custId),
      p_branchuser,
      p_reviewstatus,
      p_FinacleUserID,
      p_AssociatedAcNo,
	  p_NetBankingLoginId,
      p_isMobileApp,
      p_USERLOCKED
    );
  p_message := 'User information has been sent to checker for review';
  INSERT INTO DEBUGSP VALUES ('inserted 1111'); 
  DELETE FROM usercustomer WHERE ucuserid = p_userid AND p_usertype IN (1,2,3);
  --delete from usercustomer where ucuserid = p_userid and p_usertype in (1,3);
  INSERT
  INTO usercustomer
    (      
      uccustomerid,
      ucuserid,
      uccreatedby,
      uccreateddate
    )
  SELECT 3707 uccustomerid,
  v_useridforrole ucuserid,
    v_useridforrole uccreatedby,
    systimestamp uccreateddate
  FROM customermaster
  WHERE p_usertype = 1
  UNION ALL
  SELECT 
    NVL(p_bankcorpuser, v_custId), 
    v_useridforrole,
    v_useridforrole,
    systimestamp
  FROM dual
  WHERE p_usertype = 2
  UNION ALL
  SELECT distinct customerid,
     v_useridforrole,
    v_useridforrole,
    systimestamp
  FROM accountmaster
  WHERE branchid = p_branchuser
  AND p_usertype = 3;
  INSERT INTO UserAccountMapping
    (USERID,CUSTOMERID,ACCOUNTID,STATUS
    )
  SELECT v_useridforrole,
    CUSTOMERID,
    ACCOUNTID,
    STATUS
  FROM UserAccountMappingTmp
  WHERE status = 'A';
END IF;
IF ( v_useridforrole > 0 ) THEN
  INSERT INTO userrole
    (userid,roleid,createdby,createdate,userpriviouserole
    )
  SELECT v_useridforrole,
    x.roleid,
    v_useridforrole,
    sysdate,
    0
  FROM XMLTABLE ( '/rows/UserRole' PASSING xmltype(p_roleid) COLUMNS roleid NUMBER(10) PATH 'RoleId') x;
END IF;
DELETE FROM UserAccountMappingTmp;
--  INSERT
--  INTO useraudithistory
--    (
--      userid,
--      fieldname,
--      oldvalue,
--      newvalue,
--      createdby,
--      createdate,
--      action
--    )
--  SELECT v_useridforrole userid,
--    'Request Type' FIELDNAME,
--    '' OLDVALUE,
--    'Add Approval Pending' NEWVALUE,
--    to_number(p_loggedinuser) CREATEDBY,
--    sysdate CREATEDDATE,
--    1 ACTION
--  FROM dual
--  UNION ALL
--  SELECT v_useridforrole,
--    'Status',
--    '',
--    'Pending',
--    to_number(p_loggedinuser),
--    sysdate,
--    1
--  FROM dual;
OPEN v_cursor FOR SELECT v_useridforrole userid,
p_message MESSAGE FROM dual;
END;

/

ALTER TABLE USERMASTER 
ADD (MPINFAILEDATTEMPTS NUMBER );

ALTER TABLE USERMASTER 
ADD (SUSPENDED NUMBER );

ALTER TABLE USERMASTER 
ADD (LASTATTEMPDATETIME DATE );

ALTER TABLE USERMASTER  
MODIFY (LASTATTEMPDATETIME TIMESTAMP );