Stored Procedure With Function giving me errors in Oracle

Question

I have stored procedure and function and I am calling the function in the stored procedure in ORACLE.The function CalculateIncomeTax is what is giving me errors.In MSSQL,this type of update is possible because I have done it before.I called the function in the stored procedure.When I read around the answer I get is to use a package before I cannot use a function to update a table from another table.Please if you have any idea,tell me.The error I get is

table string.string is mutating, trigger/function may not see it Cause: A trigger (or a user defined plsql function that is referenced in this statement) attempted to look at (or modify) a table that was in the middle of being modified by the statement which fired it. Action: Rewrite the trigger (or function) so it does not read that table.

This is function

CREATE OR REPLACE function CalculateIncomeTax(periodId NVARCHAR2, 
employeeId NVARCHAR2, taxableIncome NUMBER)return NUMBER 
AS
IncomeTax NUMBER (18,4);Taxable NUMBER(18,4);            
BEGIN
SELECT SUM(CASE WHEN (taxableIncome > T.TAX_CUMMULATIVE_AMOUNT)
THEN (taxableIncome -  T.TAX_CUMMULATIVE_AMOUNT)* T.TAX_PERCENTAGE/ 100                                                        
ELSE 0.00 END )  INTO IncomeTax  
FROM TAX_LAW T  JOIN PAY_GROUP P ON P.PAY_FORMULA_ID   =T.TAX_FORMULA_ID              
JOIN PAYROLL_MASTER PP ON P.PAY_CODE =PP.PAY_PAY_GROUP_CODE        
WHERE PP.PAY_EMPLOYEE_ID = employeeId AND PP.PAY_PERIOD_CODE = periodId;         
if IncomeTax IS NULL THEN  IncomeTax :=0;     
end if;      
return IncomeTax;
end;/

This is the stored procedure

CREATE OR REPLACE PROCEDURE PROCESSPAYROLLMASTER (periodcode 
VARCHAR2) AS BEGIN         
INSERT INTO  PAYROLL_MASTER 
(       
PAY_PAYROLL_ID,PAY_EMPLOYEE_ID ,PAY_EMPLOYEE_NAME,PAY_SALARY_GRADE_CODE        
,PAY_SALARY_NOTCH_CODE,PAY_BASIC_SALARY,PAY_TOTAL_ALLOWANCE
,PAY_TOTAL_CASH_BENEFIT,PAY_MEDICAL_BENEFIT,PAY_TOTAL_BENEFIT
,PAY_TOTAL_DEDUCTION,PAY_GROSS_SALARY,PAY_TOTAL_TAXABLE,PAY_INCOME_TAX
,PAY_TAXABLE,PAY_PERIOD_CODE,PAY_BANK_CODE,PAY_BANK_NAME,PAY_BANK_ACCOUNT_NO    
,PAY_PAY_GROUP_CODE )
SELECT 
1, 
E.EMP_ID AS PAY_EMPLOYEE_ID ,
E.EMP_FIRST_NAME || ' ' || E.EMP_LAST_NAME  AS PAY_EMPLOYEE_NAME,
E.EMP_RANK_CODE,
'CODE',
(SC.SAL_MINIMUM_AMOUNT+( SN.SAL_SALARY_PERCENTAGE * 
SC.SAL_MINIMUM_AMOUNT)/100) AS PAY_BASIC_SALARY,
0,
0,
0,
0,
0,
0,
0,
0,
0,
periodcode,
'BANKCODE',
'BANKNAME',
'BANKNUMBER',
'GENERAL'
FROM EMPLOYEE E 
LEFT JOIN SALARY_SCALE  SC ON SC.SAL_RANK_CODE = E.EMP_RANK_CODE
LEFT JOIN SALARY_NOTCH SN ON  SC.SAL_ID = SN.SAL_SALARYSCALE_ID
WHERE E.EMP_RANK_CODE = SC.SAL_RANK_CODE AND E.EMP_STATUS=2;

CALCULATEALLOWANCE(v_payrollId,periodcode);
CALCULATECASHBENEFITS(v_payrollId,periodcode);
CALCULATEDEDUCTIONS(v_payrollId,periodcode);

-- UPDATE PAYROLL PAY_INCOME_TAX
   UPDATE PAYROLL_MASTER PM  SET  PM.PAY_INCOME_TAX = CalculateIncomeTax(PM.PAY_PERIOD_CODE,PM.PAY_EMPLOYEE_ID,PM.PAY_TOTAL_TAXABLE) WHERE PM.PAY_PAYROLL_ID = v_payrollId;

 UPDATE PAYROLL_PROCESS set PAY_CANCELLED = 1 WHERE PAY_PAY_GROUP_CODE='GENERAL' AND PAY_PERIOD_CODE=periodcode
 AND PAY_ID<>v_payrollId;


COMMIT;  
END ;
/

Answer 1

The function is querying the same table you are updating, which is what the error is reporting. As it happens you are not changing the value of the column you're querying, but Oracle doesn't check to that level - not least because there could be, for instance, a trigger that has less obvious side-effects.

The best solution really would be to not have to update at all, and to calculate and set all the value as part of the original insert, by joining to all the relevant tables. But you are already calling other procedures which are, presumably, updating some of the values you're inserting as zeros, including pay_total_taxable.

Unless you're able to reevaluate those as well, you may be stuck with doing a further update. In which case, you could remove the reference to the payroll_master table from the function and instead pass in the relevant data.

I think this is equivalent, though with out the table structures, sample data and what the other procedures are doing it's hard to be sure (so this is untested, obviously):

create or replace function calculateincometax (
  p_periodid nvarchar2,
  p_employeeid nvarchar2,
  p_paypaygroupcode payroll_master.pay_pay_group_code%type,
  p_taxableincome number
) return number as
  l_incometax number(18, 4);
begin
  select coalesce(sum(case when p_taxableincome > t.tax_cummulative_amount
    then (taxableincome - t.tax_cummulative_amount) * t.tax_percentage / 100
    else 0 end), 0)
  into l_incometax
  from tax_law t
  join pay_group p
  on p.pay_formula_id = t.tax_formula_id
  where p.pay_code = p_paypaygroupcode;

  return l_incometax;
end;
/

and then include the extra argument in your call:

  update payroll_master pm
  set pm.pay_income_tax = calculateincometax(pm.pay_period_code, pm.pay_employee_id,
      pm.pay_pay_group_code, pm.pay_total_taxable)
  where pm.pay_payroll_id = v_payrollid;

Although v_payrollid isn't defined in what you've shown, so even that isn't entirely clear.

I've also modified the function argument and local variable names with prefixes to remove potential ambiguity (which you seem to do by removing underscores from the names), removed the unused variable, and added a coalesce() call in place of the separate null check. Those things aren't directly relevant to the approach though.