Clarity

All, I am trying to get actuals per each line item in cost plan detail and i was wondering if anybody has done this before. the requirement is to display each cost plan details line item with planned to date amount and actuals to date amount. I am doing this as NSQL portlet and tried to see if there is any slice table i can use but it seems i couldn't find it. Then i tried to join fin_cost_plan_details table to ppa_wip with grouping attributes but having hard time doing it as they don't have same grouping attributes for each cost plan. if you have done this or seen this before, please share how you did this. any help or input will be greatly appreciated. i am working on 13.2.

Hi Leo,

The Planned Cost values for each Cost Plan line item is stored within the attribute total_cost in the table fin_cost_plan_details (Cost Plan Details sub-object). You can do an inner join to the table fin_plans (Cost Plan object) on fin_plans.id = fin_cost_plan_details.plan_id. The Actual Cost values for each Cost Plan line item is stored within the attribute actualcost in the table ppa_wip_values table. The table ppa_wip stores values for the grouping attributes such as charge_code, etc., and you can do an inner join on ppa_wip.transno = ppa_wip_values.transno. If you need the data based on the Charge Code grouping attribute, I would advise you to also look into the table prchargecode for the complete list of Charge Codes and connect to the table ppa_wip on prchargecode.prexternalid = ppa_wip.charge_code. Furthermore, you can connect the tables ppa_wip and inv_investments using ppa_wip.investment_id = inv.id as well as inv_investments and fin_plans on inv_investments.id = fin_plans.object_id. You can also use the table biz_com_periods within your query to connect to the table ppa_wip on biz_com_periods.start_date = ppa_wip.month_begin and biz_com_periods.end_date = ppa_wip.month_end, where you can set the biz_com_periods.period_type to be equal to 'MONTHLY', ppa_wip.month_begin and ppa_wip.month_end to values based on your requirements, etc. The fin_plans.object_code = 'project', fin_plans.is_plan_of_record = '1' and inv.investments.is_active = '1' can even be used for further simplification of your query.

I used this query on the grouping attribute to be Charge Code only, but, you can check if this works for you for the other grouping attributes too as you mentioned in your original post.

Hope this helps!

Suhail.

Also, check this -

SQL Script on Project Cost Plan Details

https://communities.ca.com/message/241699943#241699943

NJ

Total Actuals on Cost Plan

https://communities.ca.com/message/12036850#12036850

NJ