Populating Fiscal Start/End dates in cost plan based on demand task

tomoldovan1 · ‎03-16-2018

Trying to figure out how to set the fiscal start/end dates on cost plan based off of Demand expected start/end dates, which is populated on-load as the task in the cost plan. This is done out of box with Resource Plans, but not for other cost plans. Want to use the expected start/end of the demand to determine the default fiscal period start/end value for the cost plan and populate it on form load. Any approaches that can help with this?

tomoldovan1 · ‎03-17-2018

Would like to simplify fiscal period portion with script include, but for now this will work. I don't see any issues past this point because I'm just populating fiscal periods. The other code that moves cost plans from demand to project should not have an issue and capex and opex total costing should calculate based on type.

Note: Specefically looking for 445 period. Not Pretty, but it works as business rule

Table: cost_plan

Display: Query BR

Condition: task starts with 'DMND'

(function executeRule(current, previous /*null when async*/) {
//Get value of Task field
g_scratchpad.task = current.task.getDisplayValue();
var gr = new GlideRecord('dmn_demand');
gr.get(current.task);
g_scratchpad.start = gr.getValue('start_date');
    //gs.log("#Start#" + g_scratchpad.start)
g_scratchpad.end = gr.getValue('requested_by');
    //gs.log("#End#" + g_scratchpad.end)

//******Get Start Fiscal Date *********//
if (current.start_fiscal_period.nil()){
            var sdat = g_scratchpad.start;
            var dat = new GlideDateTime(sdat);
            var datstr = dat.toString();
            var year = datstr.substring(0,4);
            var month = datstr.substring(5,7);
            var day = datstr.substring(8,10);
                //*** Build String to compare against fiscal dates***//
                 var dat2comp = year+month+day+"T"+"000000";
                //gs.log(dat2comp);
       //**** Query fiscal_period table to find correct period ****//
       var rec = new GlideRecord ('fiscal_period');
       rec.addQuery("start_date_time","<=", dat2comp );
       rec.addQuery("end_date_time", ">=", dat2comp);
       rec.addQuery('fiscal_type', '445period')
       rec.addQuery('open',true);
       rec.query();
       while (rec.next())
            {
             current.start_fiscal_period = rec.sys_id;
            }
     }

//******Get End Fiscal Date *********//
if (current.end_fiscal_period.nil()){
            var edat = g_scratchpad.end;
            var dat1 = new GlideDateTime(edat);
            var edatstr = dat1.toString();
            var year = edatstr.substring(0,4);
            var month = edatstr.substring(5,7);
            var day = edatstr.substring(8,10);
                //*** Build String to compare against fiscal dates***//
                 var edat2comp = year+month+day+"T"+"000000";
                //gs.log(edat2comp);
       //**** Query fiscal_period table to find correct period ****//
       var erec = new GlideRecord ('fiscal_period');
       erec.addQuery("start_date_time","<=", edat2comp );
       erec.addQuery("end_date_time", ">=", edat2comp);
       erec.addQuery('fiscal_type', '445period')
       erec.addQuery('open',true);
       erec.query();
       while (erec.next())
            {
             current.end_fiscal_period = erec.sys_id;
            }
     }

})(current, previous);