This content has been marked as final. Show 6 replies
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)
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.