-4

I am completely new to Salesforce. I have a procedure (below) that takes data from 2 tables, then does some calculations and then inputs to a third table.

Can anybody help me with the basics of this?

  1. Where do I write the code.
  2. Do I need declare variables, if not how do I call the values.
  3. Can I do the calcs as below then an insert?
  4. Do I loop as before? 5 How do I pass values into the place where I write the code?

DECLARE @AnnSalary as float, @AnnSalaryYTD as float DECLARE @MonthlyAdj as float; DECLARE @Expenses as float; DECLARE @TaxFreeInd as varchar(10); DECLARE @EmpCountry as varchar(10); DECLARE @TaxFree as float; DECLARE @numb as int; DECLARE @maxemp as int; DECLARE @PAYEBAND1 as float, @PAYEBAND2 as float, @PAYEBAND3 as float; DECLARE @PAYERATE1 as float, @PAYERATE2 as float, @PAYERATE3 as float; DECLARE @NIBAND_LEL as float, @NIBAND_PT as float, @NIBAND_UEL as float; DECLARE @NIRATE_LEL as float, @NIRATE_PT as float, @NIRATE_UEL as float, @NIRATE_UPPER as float; DECLARE @FEDBAND1 as float, @FEDBAND2 as float, @FEDBAND3 as float, @FEDBAND4 as float, @FEDBAND5 as float, @FEDBAND6 as float, @FEDBAND7 as float; DECLARE @FEDRATE1 as float, @FEDRATE2 as float, @FEDRATE3 as float, @FEDRATE4 as float, @FEDRATE5 as float, @FEDRATE6 as float, @FEDRATE7 as float; DECLARE @PAYE as float, @PAYEYTD as float; DECLARE @NI as float, @NIYTD as float; DECLARE @FED as float, @FEDYTD as float; DECLARE @State as float, @StateYTD as float; DECLARE @NetPay as float;

Set @maxemp = (select max(EmployeeID) from dbo.Employees);
Set @numb = 1;

WHILE @numb < @maxemp+1
BEGIN
    --Import Personal Salary Data
    Set @AnnSalary = (select Annual_Salary from tax.SalaryDetails where EmployeeID = @numb)/12;
    Set @MonthlyAdj = (select MonthlyAdjustment from tax.SalaryDetails where EmployeeID = @numb);
    Set @Expenses = (select Expenses from tax.SalaryDetails where EmployeeID = @numb);
    Set @TaxFree = (select TaxFree from tax.SalaryDetails where EmployeeID = @numb)*@TaxMonth/12;
    Set @EmpCountry = (select Country from dbo.Employees where EmployeeID = @numb)

    --Import YTD Salary
    Set @AnnSalaryYTD = (select sum(GrossPay) from tax.HistSalaryPayments where EmployeeID = @numb)
    Set @AnnSalaryYTD = CASE
    WHEN @AnnSalaryYTD is NULL then 0
    else @AnnSalaryYTD end;
    Set @NIYTD = (select sum(NI) from tax.HistSalaryPayments where EmployeeID = @numb)
    Set @NIYTD = CASE
    WHEN @NIYTD is NULL then 0
    else @NIYTD end;
    Set @PAYEYTD = (select sum(PAYE) from tax.HistSalaryPayments where EmployeeID = @numb)
    Set @PAYEYTD = CASE
    WHEN @PAYEYTD is NULL then 0
    else @PAYEYTD end;
    Set @FEDYTD = (select sum(FED) from tax.HistSalaryPayments where EmployeeID = @numb)
    Set @FEDYTD = CASE
    WHEN @FEDYTD is NULL then 0
    else @FEDYTD end;
    Set @StateYTD = (select sum(StateTax) from tax.HistSalaryPayments where EmployeeID = @numb)
    Set @StateYTD = CASE
    WHEN @StateYTD is NULL then 0
    else @StateYTD end;

    ---Calculate PAYE - these are PAYE total for the year
    Set @PAYEBAND1 = (select Limit from tax.TaxRates where TaxType = 'PAYE' and Band = 1) * @TaxMonth / 12;
    Set @PAYEBAND2 = (select Limit from tax.TaxRates where TaxType = 'PAYE' and Band = 2) * @TaxMonth / 12;
    Set @PAYEBAND3 = (select Limit from tax.TaxRates where TaxType = 'PAYE' and Band = 3) * @TaxMonth / 12;

    Set @PAYERATE1 = (select Rate from tax.TaxRates where TaxType = 'PAYE' and Band = 1);
    Set @PAYERATE2 = (select Rate from tax.TaxRates where TaxType = 'PAYE' and Band = 2);
    Set @PAYERATE3 = (select Rate from tax.TaxRates where TaxType = 'PAYE' and Band = 3);

    Set @AnnSalary = @AnnSalary + @AnnSalaryYTD + @MonthlyAdj
        -----Get Tax Free Allowance
        Set @TaxFreeInd = (select TaxClass from tax.SalaryDetails where EmployeeID = @numb)
        Set @TaxFree = 
        CASE
            when @TaxFreeInd = 'S' then ((select SingleAllowance from tax.TaxRates where TaxType = 'PAYE' and Band = 1) * @TaxMonth / 12)
            when @TaxFreeInd = 'Mmin' then ((select MinExtraMarriedAllowance from tax.TaxRates where TaxType = 'PAYE' and Band = 1) * @TaxMonth / 12)
            when @TaxFreeInd = 'Mmax' then ((select MaxExtraMarriedAllowance from tax.TaxRates where TaxType = 'PAYE' and Band = 1) * @TaxMonth / 12)
            when @TaxFreeInd = 'A' then ((select TaxFree from tax.SalaryDetails where EmployeeID = @numb) * @TaxMonth / 12) end;


        Set @PAYE = CASE
        WHEN @EmpCountry = 'USA'
            THEN 0
        WHEN @AnnSalary <  @PAYEBAND1
            THEN (@AnnSalary - @TaxFree) * @PAYEBAND1
        WHEN @AnnSalary <  @PAYEBAND2
            THEN ((@PAYEBAND1 - @TaxFree) * @PAYERATE1) + ((@AnnSalary - @PAYEBAND1) * @PAYERATE2)
            ELSE ((@PAYEBAND1 - @TaxFree) * @PAYERATE1) + ((@PAYEBAND2 - @PAYEBAND1) * @PAYERATE2) + ((@AnnSalary - @PAYEBAND2) * @PAYERATE3) end;

    ----Calculate NI
    Set @NIBAND_LEL = (select Limit from tax.TaxRates where Band = 'LEL') * @TaxMonth / 12
    Set @NIBAND_PT = (select Limit from tax.TaxRates where Band = 'LEL-PT') * @TaxMonth / 12 
    Set @NIBAND_UEL = (select Limit from tax.TaxRates where Band = 'PT-UEL') * @TaxMonth / 12

    Set @NIRATE_LEL = (select Rate from tax.TaxRates where Band = 'LEL')
    Set @NIRATE_PT = (select Rate from tax.TaxRates where Band = 'LEL-PT')
    Set @NIRATE_UEL = (select Rate from tax.TaxRates where Band = 'PT-UEL')
    Set @NIRATE_UPPER = (select Rate from tax.TaxRates where Band = 'UEL')

        Set @NI = CASE
        WHEN @EmpCountry = 'USA'
            THEN 0
        WHEN @AnnSalary <  @NIBAND_LEL
            THEN @AnnSalary * @NIRATE_LEL
        WHEN @AnnSalary <  @NIBAND_PT
            THEN (@NIRATE_LEL * @NIRATE_LEL) + ((@AnnSalary - @NIRATE_LEL) * @NIRATE_PT)
        WHEN @AnnSalary <  @NIBAND_UEL
            THEN (@NIRATE_LEL * @NIRATE_LEL) + ((@NIBAND_PT - @NIBAND_LEL) * @NIRATE_PT) + ((@AnnSalary - @NIBAND_PT) * @NIRATE_UEL)
            ELSE (@NIRATE_LEL * @NIRATE_LEL) + ((@NIBAND_PT - @NIBAND_LEL) * @NIRATE_PT) + ((@NIBAND_UEL - @NIBAND_PT) * @NIRATE_UEL) + ((@AnnSalary -@NIBAND_UEL) * @NIRATE_UPPER)  end;

    -----Calculate US Federal Tax
    Set @FEDBAND1 = (select Limit from tax.TaxRates where TaxType = 'FED' and Band = 1) * @TaxMonth / 12;
    Set @FEDBAND2 = (select Limit from tax.TaxRates where TaxType = 'FED' and Band = 2) * @TaxMonth / 12;
    Set @FEDBAND3 = (select Limit from tax.TaxRates where TaxType = 'FED' and Band = 3) * @TaxMonth / 12;
    Set @FEDBAND4 = (select Limit from tax.TaxRates where TaxType = 'FED' and Band = 4) * @TaxMonth / 12;
    Set @FEDBAND5 = (select Limit from tax.TaxRates where TaxType = 'FED' and Band = 5) * @TaxMonth / 12;
    Set @FEDBAND6 = (select Limit from tax.TaxRates where TaxType = 'FED' and Band = 6) * @TaxMonth / 12;
    Set @FEDBAND7 = (select Limit from tax.TaxRates where TaxType = 'FED' and Band = 7) * @TaxMonth / 12;

    Set @FEDRATE1 = (select Rate from tax.TaxRates where TaxType = 'FED' and Band = 1)
    Set @FEDRATE2 = (select Rate from tax.TaxRates where TaxType = 'FED' and Band = 2)
    Set @FEDRATE3 = (select Rate from tax.TaxRates where TaxType = 'FED' and Band = 3)
    Set @FEDRATE4 = (select Rate from tax.TaxRates where TaxType = 'FED' and Band = 4)
    Set @FEDRATE5 = (select Rate from tax.TaxRates where TaxType = 'FED' and Band = 5)
    Set @FEDRATE6 = (select Rate from tax.TaxRates where TaxType = 'FED' and Band = 6)
    Set @FEDRATE7 = (select Rate from tax.TaxRates where TaxType = 'FED' and Band = 7)

        Set @FED = CASE
        WHEN @EmpCountry = 'UK'
            THEN 0
        WHEN @AnnSalary <  @FEDBAND1
            THEN @AnnSalary * @FEDRATE1
        WHEN @AnnSalary <  @FEDBAND2
            THEN (@FEDBAND1 * @FEDRATE1) + ((@AnnSalary - @FEDBAND1) * @FEDRATE2)
        WHEN @AnnSalary <  @FEDBAND3
            THEN (@FEDBAND1 * @FEDRATE1) + ((@FEDBAND2 - @FEDBAND1) * @FEDRATE2) + ((@AnnSalary - @FEDBAND2) * @FEDRATE3)
        WHEN @AnnSalary <  @FEDBAND4
            THEN (@FEDBAND1 * @FEDRATE1) + ((@FEDBAND2 - @FEDBAND1) * @FEDRATE2) + ((@FEDBAND3 - @FEDBAND2) * @FEDRATE3) + ((@AnnSalary - @FEDBAND3) * @FEDRATE4)
        WHEN @AnnSalary <  @FEDBAND5
            THEN (@FEDBAND1 * @FEDRATE1) + ((@FEDBAND2 - @FEDBAND1) * @FEDRATE2) + ((@FEDBAND3 - @FEDBAND2) * @FEDRATE3) + ((@FEDBAND4 - @FEDBAND3) * @FEDRATE4) + ((@AnnSalary - @FEDBAND4) * @FEDRATE5)
        WHEN @AnnSalary <  @FEDBAND6
            THEN (@FEDBAND1 * @FEDRATE1) + ((@FEDBAND2 - @FEDBAND1) * @FEDRATE2) + ((@FEDBAND3 - @FEDBAND2) * @FEDRATE3) + ((@FEDBAND4 - @FEDBAND3) * @FEDRATE4) + ((@FEDBAND5 - @FEDBAND4) * @FEDRATE5) + ((@AnnSalary - @FEDBAND5) * @FEDRATE6)
        ELSE (@FEDBAND1 * @FEDRATE1) + ((@FEDBAND2 - @FEDBAND1) * @FEDRATE2) + ((@FEDBAND3 - @FEDBAND2) * @FEDRATE3) + ((@FEDBAND4 - @FEDBAND3) * @FEDRATE4) + ((@FEDBAND5 - @FEDBAND4) * @FEDRATE5) + ((@FEDBAND6 - @FEDBAND5) * @FEDRATE6) + ((@AnnSalary - @FEDBAND6) * @FEDRATE7) END;

    Set @NetPay = (@AnnSalary-@AnnSalaryYTD) - (@PAYE-@PAYEYTD) - (@NI-@NIYTD) - (@FED-@FEDYTD)

    INSERT into tax.HistSalaryPayments
    VALUES (@numb, @TaxYear, @TaxMonth, @AnnSalary-@AnnSalaryYTD, @PAYE-@PAYEYTD, @NI-@NIYTD, @FED-@FEDYTD, 0, @NetPay, @Expenses)

    --Set @NetPay = @AnnSalary - @PAYE - @NI - @FED

    --INSERT into tax.HistSalaryPayments
    --VALUES (@numb, @TaxYear, @TaxMonth, @AnnSalary, @PAYE, @NI, @FED, 0, @NetPay, @Expenses)

SET @numb = @numb + 1;
END
| improve this question | | | | |
4

Honestly, this is too big of a problem to define here. You need to learn how to do the basics in salesforce, and then a lot of this will begin to make sense.

Start by doing some courses in Trailhead. This will get you familiar with the tool. It sounds like you would need to then set up a couple of objects to house the data you are working with.

This level of support would probably require some support from another person. You could hire a consultant if you want or you could reach out to see if there are any upcoming admins that want exposure/experience and would be willing to work for less than a developer.

There is a lot to salesforce, sorry. But you can find lots of people willing to help and point in the right direction.

| improve this answer | | | | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.