Fiscal Year Maker Constraint Errors?

[Siddartha Savara]

Hi All,

Short version - do you have fiscal year maker constraint errors, and how did you resolve?

Context and what we did at UH before

The baseline FiscalYearMaker (FYM) job includes a step that will erase all of the dates from the new fiscal year and recreate the dates in the University Date Table. Since documents may have reversal dates in the next fiscal year, the job fails with a constraint error.

Years ago we asked other institutions  how they run FYM given this issue.

Some schools drop the constraint, run the job and re-add the constraint.

Another school comments out the University Date Table from FYM and loads the table by running the job in another environment (without and documents) and copies the records into the University Date Table.

UH in the past used the second option. We comment the University Date Table from the FYM job and will run FYM in a separate instance to generate the future FY dates and then copy the dates into the University Date Table

When I reviewed the code it looks like it still tries to do the full delete, so would still have the same constraint issue for deletes.  I was wondering whether you resolved it in a different way. I feel that the whole FYM job needs to be rewritten to be robust and not have this kind of issue, but wanted to check to first see how other schools dealt with it...and/or if someone already wrote a more robust FYM job so I don't have to =)

Sid

Historical Constraint Error:

2013-02-28 21:36:01,986 [RMI TCP Connection(2)-192.168.230.112] u:/d: ERROR org.kuali.kfs.sys.batch.Job :: Exception occured executing step
org.springframework.dao.DataIntegrityViolationException: OJB operation; SQL []; ORA-02292: integrity constraint (KFSFYE.GL_ENTRY_TR13) violated - child record found
; nested exception is java.sql.SQLIntegrityConstraintViolationException: ORA-02292: integrity constraint (KFSFYE.GL_ENTRY_TR13) violated - child record found

The fiscalYearMaker is trying to delete the 2014 records from the UniversityDate table. (i'm guessing it's 2014). but there are entries in the GL_ENTRY_T that have 2014 dates in the FDOC_REVERSAL_DT column. I see dates like 12/13/2013, 12/11/2013.

[McCormick, Misty T - (sandberm)]

Hi Sid,

At UA, we remove, then re-enable the constraints to run the FYM job.  Agreed this should be reworked and fixed at some point.  After our upgrade to version 7.0 we attempted to run it without removing constraints and it crashed and burned, so we will continue with the workaround for now.  Here is our sql if you’re interested.

Ta,

Misty

 

Misty McCormick

Business Analyst Principal, UAccess Financials

UITS Enterprise Applications 

The University of Arizona

520-621-0156

 

 

BEGIN 
FOR c IN 
(select c.owner, c.table_name, c.constraint_name 
from all_constraints c inner join all_cons_columns xxx on c.owner = xxx.owner and c.constraint_name = xxx.constraint_name
where c.owner = 'KULOWNER' and c.constraint_type = 'R' And C.Status = 'ENABLED'
and r_constraint_name in 
(select constraint_name from all_constraints where owner = 'KULOWNER' and constraint_type = 'P' and table_name = 'SH_UNIV_DATE_T')
order by c.table_name, c.constraint_name) 
LOOP 
dbms_output.put_line('alter table ' || c.owner || '.' || c.table_name || ' disable constraint ' || c.constraint_name);
dbms_utility.exec_ddl_statement('alter table ' || c.owner || '.' || c.table_name || ' disable constraint ' || c.constraint_name);
END LOOP; 
END; 
/

BEGIN 
FOR c IN 
(select c.owner, c.table_name, c.constraint_name 
from all_constraints c inner join all_cons_columns xxx on c.owner = xxx.owner and c.constraint_name = xxx.constraint_name
where c.owner = 'KULOWNER' and c.constraint_type = 'R' And C.Status = 'DISABLED'
and r_constraint_name in 
(select constraint_name from all_constraints where owner = 'KULOWNER' and constraint_type = 'P' and table_name = 'SH_UNIV_DATE_T')
order by c.table_name, c.constraint_name) 
LOOP 
dbms_output.put_line('alter table ' || c.owner || '.' || c.table_name || ' enable constraint ' || c.constraint_name);
dbms_utility.exec_ddl_statement('alter table ' || c.owner || '.' || c.table_name || ' enable constraint ' || c.constraint_name);
END LOOP; 
END; 
/ 
--------------------------------------------------------------------------------------------

--
You received this message because you are subscribed to the Google Groups "Kuali Financials Technical User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to kfs.user.tec...@kuali.org.

[McCormick, Misty T - (sandberm)]

Hi Daniela,

Thank you for sharing the commit with the group.  Does Cornell plan to contribute this to Kuali base code anytime in the future?

Thank you,

Misty

 

 

From: Daniela Balmus <db...@cornell.edu>
Sent: Thursday, June 21, 2018 5:38 AM
To: McCormick, Misty T - (sandberm) <sand...@email.arizona.edu>; Siddartha Savara <sidd...@hawaii.edu>; kfs.us...@kuali.org; Kuali Financials Technical Implementation Council <kfs.te...@kuali.org>; kfs.us...@kuali.org
Subject: Re: [kfs.user.tech] Fiscal Year Maker Constraint Errors?

 

Hi Sid,

 

We've had this issue too and we have local a customization to the UniversityDateFiscalYearMaker so that it does not delete university dates when they already exist: update existing entries if in override mode, ignore existing entries if in append mode.

 

Here is the commit for the change:

https://github.com/CU-CommunityApps/cu-kfs/commit/c863f246cb3966d6e09b1695e19866164c07b032#diff-41d09eb9e959d6147ef3ef1236d70bd8

 

Please let me know if you have trouble accessing it or have any questions.

 

Thank you,

Daniela

 

 

---

--
You received this message because you are subscribed to the Google Groups "Kuali Financials Technical Implementation Council" group.
To unsubscribe from this group and stop receiving emails from it, send an email to kfs.tech.tic...@kuali.org.

[Siddartha Savara]

Thank you Misty and Daniela!

I think Daniela what you folks have (from my reading of your email and also reviewing the commit) is exactly what I was looking for! I was thinking of making it parameter-driven (e.g. - "full replace mode" or "error on duplicate mode" or "do not replace if exists mode") but the functionality as written does look to me like what we woudlw ant at UH - replace it if it exists, don't impact the constraints

Would this count as  a bug or a feature?  I feel it's a bug personally.  Maybe I'll leave that discussion for another day =)

Sid

On Thu, Jun 21, 2018 at 2:38 AM, Daniela Balmus <db...@cornell.edu> wrote:

Hi Sid,

We've had this issue too and we have local a customization to the UniversityDateFiscalYearMaker so that it does not delete university dates when they already exist: update existing entries if in override mode, ignore existing entries if in append mode.

Here is the commit for the change:

https://github.com/CU-CommunityApps/cu-kfs/commit/c863f246cb3966d6e09b1695e19866164c07b032#diff-41d09eb9e959d6147ef3ef1236d70bd8

Please let me know if you have trouble accessing it or have any questions.

Thank you,

Daniela

From: McCormick, Misty T - (sandberm) <sand...@email.arizona.edu>
Sent: Wednesday, June 20, 2018 8:24 PM

To: Siddartha Savara; kfs.us...@kuali.org; Kuali Financials Technical Implementation Council; kfs.us...@kuali.org
Subject: RE: [kfs.user.tech] Fiscal Year Maker Constraint Errors?

 

To unsubscribe from this group and stop receiving emails from it, send an email to kfs.user.tech+unsubscribe@kuali.org.

--

You received this message because you are subscribed to the Google Groups "Kuali Financials Technical Implementation Council" group.
To unsubscribe from this group and stop receiving emails from it, send an email to kfs.tech.tic+unsubscribe@kuali.org.

--
You received this message because you are subscribed to the Google Groups "Kuali Financials Technical User Group" group.

To unsubscribe from this group and stop receiving emails from it, send an email to kfs.user.tech+unsubscribe@kuali.org.

[Maribeth F. Bradberry]

Hi All,

 

Per our developer:

 

We tested two methods at UCI.

 

1)     University Date records are recreated for the next two fiscal years. We modified the University Date Fiscal Year Maker to skip the first year so that we do not violate constraints for reversal entries.

2)     Disable reversal constraints before fiscal year maker then enable them after fiscal year maker.

 

We implemented the first solution at UCI.

 

Maribeth

 

Maribeth Bradberry

Sr. Business Analyst
Kuali Financial Systems
Office of Information Technology
University of California, Irvine
(949) 824-0186
mari...@uci.edu

 

--
You received this message because you are subscribed to the Google Groups "Kuali Financials Functional User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to kfs.user.fun...@kuali.org.

[Dan Dwyer]

Good questions for our Off-TIC meeting in an hour I think.

To unsubscribe from this group and stop receiving emails from it, send an email to kfs.user.tec...@kuali.org.

--

You received this message because you are subscribed to the Google Groups "Kuali Financials Technical Implementation Council" group.

To unsubscribe from this group and stop receiving emails from it, send an email to kfs.tech.tic...@kuali.org.

--
You received this message because you are subscribed to the Google Groups "Kuali Financials Technical User Group" group.

To unsubscribe from this group and stop receiving emails from it, send an email to kfs.user.tec...@kuali.org.

 

--
You received this message because you are subscribed to the Google Groups "Kuali Financials Technical Implementation Council" group.

To unsubscribe from this group and stop receiving emails from it, send an email to kfs.tech.tic...@kuali.org.