Skip to main content

Organization Chart: Showing Employee Details using Stored Procedure

This option is used to get the required fields of employee details to display in the organization chart.

Below is the example for understanding the option.

1. Initially, when we open the organization chart a blank screen will be displayed on clicking the employee information option similar to the below screen.

image

2. To get more information for individual employees, run the below stored procedure from server database.

GO
/****** Object:  StoredProcedure [dbo].[pPay_Employee360]    Script Date: 13-11-2018 18:59:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
     
Create Procedure [dbo].[pPay_Employee360] (       
         @iEmployeeId INT       
         ,@iStartDate INT       
         )         
      AS            
          BEGIN       
                  
            select 2 as 'Type', '' as 'EmployeeGeneral Information', mPay_Designation.sName as 'designation' ,mPay_Position.sName as 'Position'       
                           , CONVERT(varchar,CONVERT(date,dbo.IntToDate(muPay_Employee.dDateofJoining))) as 'joining Date'       
                           from mPay_Employee       
                           inner join muPay_Employee on mPay_Employee.iMasterId=muPay_Employee.iMasterId        
                           inner join mPay_Designation on mPay_Designation.iMasterId=muPay_Employee.iDesignation       
                           inner join mPay_Position on mPay_Position.iMasterId=muPay_Employee.iPosition       
                           where mPay_Employee.iMasterId=@iEmployeeId       
         
           select 2 as 'Type','' as 'Personal Information',CONVERT(varchar,CONVERT(date,dbo.IntToDate(p.dDateofBirth))) as 'date of birth'       
                           ,n.sName as 'nationality'       
                           from mPay_Employee       
                           inner join muPay_Employee on mPay_Employee.iMasterId=muPay_Employee.iMasterId        
                           inner join muPay_Employee_PersonalInformation p on mPay_Employee.iMasterId=p.iMasterId       
                           INNER JOIN vPay_Employee vp ON vp.iMasterId = p.iMasterId       
                           inner join mPay_Nationality n on p.iNationality=n.iMasterId  where mPay_Employee.iMasterId=@iEmployeeId       
                       
           select  3 as 'Type','' as 'Attendance Information', (DATENAME(month,dbo.IntToDate(dStartDate))) as 'Month',MAX(p.fWorkingDaysHours) as 'Working Days',SUM(a.fworkedDays) as 'Worked Days'
                           FROM tPay_Payroll p       
                           INNER JOIN tuPay_Payroll_Details pd ON p.iTransId = pd.iPayrollId       
                               AND pd.iPayrollRefType = 0       
                               AND pd.iBodyId NOT IN (       
                                   SELECT isnull(iBodyId, 0)       
                                   FROM tuPay_PayrollApprovals_Details       
                                   WHERE iStatus = 6       
                                   )       
                           INNER JOIN tuPay_PayrollAttendance_Details a ON pd.iBodyId = a.iPayrollDetailsId       
                               AND a.iRevisionId = (       
                                   SELECT max(iRevisionId)       
                                   FROM tuPay_PayrollAttendance_Details tpd       
                                   WHERE tpd.iEmpId = a.iEmpId       
                                       AND a.iPayrollDetailsId = tpd.iPayrollDetailsId       
                                   )       
                           INNER JOIN vPay_Employee ON vPay_Employee.iMasterId = a.iEmpId and a.iEmpId=@iEmployeeId
                 GROUP BY a.iEmpId,dStartDate
       
  declare @p1 xml     
  declare @xmlData nvarchar(max) =N'<Params><SelectedIds>'+ CAST(@iEmployeeId as nvarchar(max))+ '</SelectedIds><StartDate>'+ CAST(@iStartDate as nvarchar(max))+ '</StartDate><EndDate>'+ CAST(@iStartDate as nvarchar(max))+ '</EndDate><LoginEmpId>0</LoginEmpId></Params>'     
  set @p1=convert(xml,@xmlData)     
                                        
     SET NOCOUNT OFF;       

  END 

3. Now go to organization hierarchy to give the query details.

Settings>>Approval>>Organization Hierarchy

Enter the stored procedure name (pPay_Employee360) in the field provided “Dashboard stored procedure name”. Refer below screenshot.

image

On saving the details, the organization chart will be refreshed with more information which are added in the query. Refer below screenshot after executing the stored procedure.

image

If you refer the query, below details are included:

EmployeeGeneral Information – designation

                                          Position

                                          joining Date

Personal Information - date of birth

                          nationality

Attendance Information – Month

                                         working Days

                                         Worked Days

All the information added in the query is displayed in the organization chart.

Note: If User wants to get few more details to be displayed in the organization chart, they can modify or add the required details in the query and execute it.

Refer below video link for better understanding:

https://drive.google.com/file/d/1X3QOmnyAFo_9R-r1Zi0eFMhWG4_ZS_-k/view?usp=sharing

Labels:

Comments

Post a Comment

Popular posts from this blog

TDS details in Payslip

TDS variables are added in the payslip to display the tax details for employees. These variables will give a clear idea to the employee of what is the taxable income, total tax per year to be deducted, tax deducted till date and the balance tax which he needs to pay. Follow the steps below. 1. Create the payroll components as mentioned below under Deductions. TDS_TotalTaxableIncome TDS_TotalTax TDS_PrevEmployerTDS TDS_TaxDeductedYTD TDS_BalanceTax 2. Go to deduction preferences, and add the TDS variable exactly with the same name under formula. Below is the screen shot for reference. 3. The values of TDS variables will be displayed in the payroll preview screen. The same will be displayed in the payslip, once these variables are mapped to the payslip. Note: 1. Priority Order: TDS variables should be placed after TDS component. Sample Payslip with TDS variable:
3 comments
Read more

Advance Leave Encashment

This option is for leave encashment for advance leaves. There are two scenarios here: 1. If payment type is selected as continuous in leave encashment screen, then the leave encashment salary will be credited through the payroll. 2. If payment type is selected as advance in leave encashment screen, then the leave encashment salary will not be credited through payroll but in the print layout, encash amount can be viewed. Settings>>Customization>>Leaves>>Leave Encashment>>Print layout Note: Employee Account and Salary Account should be mapped in the Employee master Follow the below steps for FA posting: 1. Create two account master fields in leave encashment screen as shown in the below screen. In the above two screens, two accounts are created. One is Company account in the first screen and Emp account in the second screen. Employee account is created to credit the encash amount and add these two fields in page layout of leave encashment. 2. Run below stored pro...
Post a Comment
Read more

Using Stored Procedure in the Earning/Deduction Formulas

Naming Convention : Stored Procedure Name : pPay_GetData1 Variable to use above stored procedure : fPay1 Advantages: we can assign multiple earning/deduction values using single stored procedure Eg : CREATE PROCEDURE pPay_GetData1 (      @xmlIn AS XML = NULL      ,@xmlOut AS XML = NULL OUT      ,@Result AS DECIMAL(18, 4) OUT      ) AS BEGIN      SET NOCOUNT ON;      SET @xmlOut = @xmlIn     DECLARE @EmpId AS VARCHAR(max),@PayrollRefType AS VARCHAR(100)      declare @ToDate as int=0     declare @BonusFromDate as int=0,@BonusToDate as int=0     --Retriving Employee Id      SELECT @EmpId = v.value('iEmpId[1]', 'varchar(max)')      FROM @xmlIn.nodes('/PayrollData') x(v)     --Retriving Payroll Date      SELECT @ToDate = v.value('...
Post a Comment
Read more