return to PRS Technologies website
This bug caused G/L budget changes to not correctly update the FRX tables in Elite/FMS.
Problem: When making Elite budget changes in Elite/FMS, particularly when deleting the budget for an entire G/L account, the corresponding changes were not correctly made to the FRX tables, causing the FRX budget reports to be out of balance.
Acutally there were a couple of problems here. Problem 1 was that the p_tran record was not getting completely populated with all the fields that were later used to delete rows from the frl_period_bal table. The delete statment looked like this:
DELETE FROM frl_period_bal WHERE
acct_id = p_tran.acct_id AND
entity_num = p_tran.entity_num AND
fiscal_year = p_tran.fiscal_year AND
fiscal_period = p_tran.fiscal_period AND
book_code = p_tran.book_code
The p_tran.entity_num and p_tran.acct_id where getting populated OK,
but the remaining fields (p_tran.fiscal_year, p_tran.fiscal_period AND p_tran.book_code)
seemed only to get populated if p_frxint.modeflag was equal to an 'A'.
Budget purges have a p_frxint.modeflag flag = "P".
So I simply populated the remaining fields in the CASE statement when p_frxint.modeflag flag = "P".
Original Code
~Line 412:
WHEN "P"
display 'going to frx_postsum 5'
IF NOT frx_postsum(p_tran.*,p_frxint.*) THEN
ROLLBACK WORK
SET LOCK MODE TO NOT WAIT
LET tran_count = 0
RETURN FALSE
END IF
END CASE
RETURN TRUE
New Code
~Line 412:
WHEN "P" # "P" is for Purge
LET p_tran.fiscal_year = p_frxint.fiscal_year
LET p_tran.fiscal_period = p_frxint.fiscal_period
CALL get_book(p_frxint.gltype) RETURNING
p_tran.book_code,book_code1,book_code2,loop_count
display 'going to frx_postsum 5'
IF NOT frx_postsum(p_tran.*,p_frxint.*) THEN
ROLLBACK WORK
SET LOCK MODE TO NOT WAIT
LET tran_count = 0
RETURN FALSE
END IF
END CASE
RETURN TRUE
Problem 2 was that the delete statement didn't work if the fiscal_period = 0. So I changed the delete statement to work correctly when fiscal_period = 0.
Original Code
~Line 492:
IF p_frxint.modeflag = "P" THEN
DELETE FROM frl_period_bal WHERE
acct_id = p_tran.acct_id AND
entity_num = p_tran.entity_num AND
fiscal_year = p_tran.fiscal_year AND
fiscal_period = p_tran.fiscal_period AND
book_code = p_tran.book_code
RETURN TRUE
END IF
New Code
~Line 492:
IF p_frxint.modeflag = "P" THEN
if p_tran.fiscal_period = 0 then
DELETE FROM frl_period_bal WHERE
acct_id = p_tran.acct_id AND
entity_num = p_tran.entity_num AND
fiscal_year = p_tran.fiscal_year AND
book_code = p_tran.book_code
else
DELETE FROM frl_period_bal WHERE
acct_id = p_tran.acct_id AND
entity_num = p_tran.entity_num AND
fiscal_year = p_tran.fiscal_year AND
fiscal_period = p_tran.fiscal_period AND
book_code = p_tran.book_code
end if
RETURN TRUE
END IF
Actually, I think the fiscal_period is ALWAYS = 0. If the user is only modifying a single period, a frxint record comes through with a fiscal_period = 0 followed by all of the periods for that year that were NOT deleted so that they can be added back.
So the ELSE in the if clause above that I wrote is probably unnecessary, but I left it in just in case.