Skip to main content

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('AsOnDate[1]', 'varchar(max)')
     FROM @xmlIn.nodes('/PayrollData') x(v)

    select @BonusFromDate=FromDate,@BonusToDate=ToDate from tuPay_BonusDetails where [Date]=(select max([Date]) from tuPay_BonusDetails where [Date]<=@ToDate)

    if month(dbo.IntToDate(@BonusToDate)) = month(dbo.IntToDate(@ToDate))
     begin
            
             DECLARE @BONUS_ID AS INT = 1018
             DECLARE @BONUS AS DECIMAL(18,4)=0

            DECLARE @BONUS_DED_ID AS INT = 1036
             DECLARE @BONUS_DED AS DECIMAL(18,4)=0       

                          select @BONUS=SUM(ISNULL(pdd.fD6,0))
                           FROM tPay_Payroll p       
                           INNER JOIN cuPay_PayFrequencyPeriod_Details pfd ON p.iPayFrequencyPeriodId = pfd.iPayFrequencyPeriodId       
                               AND pfd.dToDate>=@BonusFromDate and pfd.dToDate<@BonusToDate
                           INNER JOIN tuPay_Payroll_Details pd ON p.iTransId = pd.iPayrollId       
                               AND pd.iPayrollRefType = 0       
                           INNER JOIN tuPay_PayrollAttendance_Details a ON pd.iBodyId = a.iPayrollDetailsId and a.iEmpId=@EmpId      
                               AND a.iRevisionId = (       
                                   SELECT max(iRevisionId)       
                                   FROM tuPay_PayrollAttendance_Details tpd       
                                   WHERE tpd.iEmpId = a.iEmpId       
                                       AND a.iPayrollDetailsId = tpd.iPayrollDetailsId       
                                   )       
                           INNER JOIN tuPay_PayrollEarning_Details ped ON a.iBodyId = ped.iPayrollAttendanceDetailsId        
                           INNER JOIN tuPay_PayrollDeduction_Details pdd ON pdd.iPayrollAttendanceDetailsId = a.iBodyId       

        --Reading Earning Value
         --select @BONUS_DED = v.value('Value[1]','decimal(18,4)') FROM @xmlIn.nodes('/PayrollData/dDedOut/IdValuePairOfDouble[ID/text()=sql:variable("@BONUS_DED_ID")]') x(v)
         --set @BONUS = isnull(@BONUS,0) + isnull(@BONUS_DED,0)

        --Updating Earning Value        
         SET @xmlOut.modify('replace value of(/PayrollData/dEarOut/IdValuePairOfDouble[ID = sql:variable("@BONUS_ID")]/Value/text())[1] with sql:variable("@BONUS")')
     end
END

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