Create Expense lines from original Expense lines - script assistance urgently needed

[Mickey_Cegon]

We just implemented Project Portfolio Management, but had an issue after Madrid due to the change in Rate Models. We originally set Cost Plan and Cost Type on each Expense line, in order to follow our company policy of tracking expense lines at the task level. So, when a person submits a time card, two Expense lines are created, one at the time card task level, and one on the Project, which then is tied to the Time card via the Base Expense field on the Expense. We had contract help to work through getting our issue resolved, but due to an error in the script we ran, some of the Expense Lines at the Project level have the wrong Cost Plan/Cost Type. But, their matching task level Expense Line has the right Cost Plan/Cost type. 

I need help with a script to do the following:

1. Run a query on the Expense Line table to find a specific set of Expense lines that were created incorrectly.

2. On each incorrect Expense line from the query, insert a matching expense line with a new number and a negative amount, to cancel out the first expense line, but leave the first one there for audit purposes, both still in the wrong Cost Plan.

3. Using the same incorrect Expense line, insert a 2nd matching expense line, with the original amount, but with the correct Cost Plan, found by using the base_expense.cost_plan.

Results should be:

1. Original Expense line - wrong cost plan, correct $$ amount

2. New Expense line - wrong cost plan, -$$ amount

3. New Expense line - correct cost plan from base expense record, correct $$ amount

We asked for help from same contractor, but I'm not confident that the script is correctly written. Plus, it's inserting new numbers way above the numbering that we are at. Currently, we are at 52,000 or so in expense line numbering, and in my test of their script, it inserted numbers starting at 1200000, and now any expense lines entered are over that number.

Here are the two scripts. I was also wondering if the new records need to be initialized, and I don't see that being called in these scripts. And, I don't know why we couldn't combine them, since they are needing to copy the same record twice. Plus, I don't see the need to query the Cost Plan in the second script, because the Base Expense on the bad Expense Line actually has the right Cost Plan we need. 

--------------------------------------------
var gr = new GlideRecord('fm_expense_line');
gr.addQuery('sys_id','eb00910ddb723f485de642fa0b9619ee');
//gr.addencodedquery("sys_created_onON2019-07-20@javascript:gs.dateGenerate('2019-07-20','start')@javascript:gs.dateGenerate('2019-07-20','end')^cost_planLIKEExpense Internal Labor^amount>javascript:global.getCurrencyFilter('fm_expense_line','amount', 'USD;0')^base_expenseISNOTEMPTY");
gr.query();
var i = 0;
while(gr.next()){
i++;
gr.number = gs.getNextObjNumberPadded();
gr.amount = gr.amount*(-1);
gr.state = 'pending';
//gr.setWorkflow(false);
//gr.autoSysFields(false);
gr.insert();
}
gs.print('Total no. of Expense lines inserted '+i);

--------------------------------------------

var gr = new GlideRecord('fm_expense_line');
gr.addQuery('sys_id','81345dcddbb23f485de642fa0b9619ba');
//gr.addEncodedQuery("sys_created_onON2019-07-20@javascript:gs.dateGenerate('2019-07-20','start')@javascript:gs.dateGenerate('2019-07-20','end')^cost_planLIKEExpense Internal Labor^amount>javascript:global.getCurrencyFilter('fm_expense_line','amount', 'USD;0')^base_expenseISNOTEMPTY");
gr.query();
var i = 0;
while(gr.next()){
i++;
gr.number = gs.getNextObjNumberPadded();
var r_type = gr.base_expense.resource_type;
var c_plan = new GlideRecord('cost_plan');
c_plan.addQuery('task',gr.task);
c_plan.addQuery('resource_type',r_type);
c_plan.query();
var i = 0;
while(c_plan.next()){
i++;
}
if(i == 1){
gr.cost_plan = c_plan.sys_id;
}
gr.resource_type = r_type;
gr.state = 'pending';
//gr.setWorkflow(false);
//gr.autoSysFields(false);
gr.insert();
}
gs.print('Total no. of Expense lines inserted '+i);

**************************

Any help would be greatly appreciated. We've been fighting this for several weeks, and I just need to get this resolved.

Mickey Cegon - FBL Financial Group, Inc.

[Sunil B N · Accepted solution]

Hi Mickey Cegon,

As you said, we can create a negative expense line and new expense line in one go.
You should use different variable inside the while to create new expense lines.
Feel free to reach out for further queries.
Best Regards,
Sunil B N