This content has been marked as final. Show 6 replies
Hi,1 person found this helpful
Why are you using to_number function. Any how, as per my understing, l_last_day- EMP_TERM_DATE will retun number value only.
Please try without to_number function.
Thanks Avinash,for the reply!
Tried that also...
this is the error i get.
APP-FF-33003: Line 18: 'DAYS_DEDUCTION' is of type NUMBER but has DATE type assigned into it.
Cause: The data type of the expression on the right hand side of the assignment statement does not match the data type of the variable on the left hand side of the assignment, probably because of an earlier assignment to the variable of a different data type.
Action: Ensure data types of assignments match, or that variable being set has not been set before to another data type.
This is my complete formula
DEFAULT FOR EMP_TERM_DATE IS '1-JAN-4712'(date)
DEFAULT FOR PAY_PROC_PERIOD_START_DATE IS '01-JAN-1951' (date)
DEFAULT FOR PAY_PROC_PERIOD_END_DATE IS '31-DEC-4712' (date)
DEFAULT FOR C_Allowance_Balance_ASG_YTD is 0
DEFAULT FOR L_paid_amount IS 0
DEFAULT FOR Day_amount IS 0
DEFAULT FOR days_deduction IS 0
DEFAULT FOR deducted_amount IS 0
DEFAULT FOR L_last_day is '31-JAN-4712'(date)
L_msg = 'Club Allowance: '
L_last_day = TO_DATE( '31-DEC-'+TO_CHAR(PAY_PROC_PERIOD_END_DATE,'YYYY'),'DD-MON-YYYY')
Day_amount = (C_Allowance_Balance_ASG_YTD)/ 365
days_deduction = L_last_day-EMP_TERM_DATE
deducted_amount = (days_deduction*day_amount)
L_msg = L_msg||' Term Date :'||to_char(EMP_TERM_DATE)||'Days_ded: '||to_char(days_deduction)
Try this once.
days_deduction = days_between(L_last_day,EMP_TERM_DATE)
Thanks Aneesh..Thats working perfectly fine although I wonder why the first case shouldnt work!!
Good that it worked. I too have no idea why the other is not working.
Hi 874719,1 person found this helpful
Fast Formula engine and the Sql Pl/sql engine are not one and the same.
So you cannot do a subtract operation on dates.
The only option is to use formula functions, in this case DAYS_BETWEEN.
Also, there might be an issue with your code.
I guess you should use +1 in the logic, else you would get 1 less day in the calculation.
days_deduction = days_between(L_last_day,EMP_TERM_DATE) + 1
SELECT TO_DATE('05-JAN-2012') - TO_DATE('01-JAN-2012') FROM DUAL;
returns 4, when you might need 5.