FA_BOOKS_PKG.UPDATE_ROW - Fixed Assets Insert/Update/Delete

FA_BOOKS_PKG.UPDATE_ROW is the package used to insert/update/delete the Asset data. This package can almost update all the Asset columns in the table FA_BOOKS.

Below are 2 versions, Before patch and After Patch.
Choose which ever worked for you.

Navigation
-----------
Fixed Assets Manager --> Assets --> Assets Workbench --> View financial Information

In this topic i will mention how to update production_quantity for an existing Asset Number. For the below procedure p_asset_num,book_name are mandatory columns along with these the column that has to be modified new_prd_qty in below case is also mandatory

Before Patch

CREATE OR REPLACE PROCEDURE FA_ASSET_UPDATE (p_asset_num varchar2,
new_prd_qty number) is
book_name varchar2(100) := <book name> -- Can be found from fa_books_v table
l_asset_id number;
l_call_fn varchar2(256);
l_book_type_code varchar2(100);
begin
mo_global.SET_POLICY_CONTEXT('S',<org to be modified>);
begin
select fb.asset_id,fb.book_type_code into l_asset_id,l_book_type_code from fa_book_controls fbc,fa_books_v fb
where BOOK_TYPE_NAME = book_name
and fb.asset_id = (select asset_id from FA_ADDITIONS_V fa
where fa.asset_number = p_asset_num)
and fb.book_type_code = fbc.book_type_code;
exception when others then
l_asset_id := null;
end;

if l_asset_id is not null then

fa_books_pkg.update_row(X_Asset_Id => l_asset_id,
X_Book_Type_Code => l_book_type_code,
X_Production_Capacity => new_prd_qty,
X_Calling_Fn => l_call_fn);
commit;
else
dbms_output.put_line('Asset Number '||p_asset_num||' Update failed');
end if;
exception when others then
dbms_output.put_line('Sqlerrm - Asset Number '||p_asset_num||' Update failed '||sqlerrm);
end;

After Patch

CREATE OR REPLACE PROCEDURE FA_ASSET_UPDATE (p_asset_num varchar2,
new_prd_qty number) is
book_name varchar2(100) := <book name> -- Can be found from fa_books_v table
l_asset_id number;
l_call_fn varchar2(256);
l_book_type_code varchar2(100);
l_set_of_books_id number;
begin
mo_global.SET_POLICY_CONTEXT('S',<org to be modified>);
begin
select fb.asset_id,fb.book_type_code,fbc.set_of_books_id into l_asset_id,l_book_type_code,l_set_of_books_id from fa_book_controls fbc,fa_books_v fb
where BOOK_TYPE_NAME = book_name
and fb.asset_id = (select asset_id from FA_ADDITIONS_V fa
where fa.asset_number = p_asset_num)
and fb.book_type_code = fbc.book_type_code;
exception when others then
l_asset_id := null;
end;

if l_asset_id is not null then

fa_books_pkg.update_row(X_Asset_Id => l_asset_id,
X_Book_Type_Code => l_book_type_code,
X_Production_Capacity => new_prd_qty,
X_set_of_books_id => l_set_of_books_id,
X_Calling_Fn => l_call_fn,
p_log_level_rec => null );
commit;
else
dbms_output.put_line('Asset Number '||p_asset_num||' Update failed');
end if;
exception when others then
dbms_output.put_line('Sqlerrm - Asset Number '||p_asset_num||' Update failed '||sqlerrm);
end;

Project Status Inquiry (PSI) - R12

In this section i would like to expand the view pa_status_task_generic_v which is used to display the Project Status Inquiry task wise.

Querying pa_status_task_generic_v in sql doesn't give the data because it needs inputs which are passed from oracle form.

pa_status_task_generic_v uses four views

pa_status_proj_bgt_rev_v => R -- Revenue

pa_status_proj_bgt_cost_v => C -- Actual Costs , Also can be used to get Forecast Cost by modifying budget_type_code = AC/FC

pa_project_accum_actuals => A -- Actual Expenditure

pa_project_accum_commitments => M -- Commitment

which in turn uses some more views.

So i have decoded the view to make easy for newbie's. This can be directly run from SQL by passing the parameter

SELECT t.project_id, t.task_id, t.parent_task_id, t.wbs_level,
c.budget_type_code, r.budget_type_code,
DECODE (pa_task_utils.check_child_exists (t.task_id), 1, '+', 0, ' '),
t.task_number, t.task_name,
DECODE ((SIGN ( (NVL (c.baseline_burdened_cost_itd, 0) * 1.1)
- ( NVL (a.burdened_cost_itd, 0)
+ NVL (m.cmt_burdened_cost_ptd, 0)
)
)
),
-1, '*',
NULL
),
ROUND (NVL (r.baseline_revenue_itd, 0)),
ROUND (NVL (a.revenue_itd, 0)),
ROUND (NVL (c.baseline_burdened_cost_itd, 0)),
ROUND (NVL (a.burdened_cost_itd, 0)),
ROUND (NVL (m.cmt_burdened_cost_ptd, 0)) cmt_burdened_cost_ptd,
ROUND (NVL (c.baseline_burdened_cost_ptd, 0)),
ROUND (NVL (a.burdened_cost_ptd, 0)),
ROUND (NVL (c.baseline_labor_hours_itd, 0)),
ROUND (NVL (a.billable_labor_hours_ytd, 0)),
ROUND (NVL (c.baseline_labor_hours_ptd, 0)),
ROUND (NVL (a.billable_labor_hours_ytd, 0)),
ROUND (NVL (r.baseline_revenue_tot, 0)),
ROUND (NVL (c.baseline_burdened_cost_tot, 0)),
ROUND (NVL (c.baseline_labor_hours_tot, 0)),
ROUND (NVL (r.original_revenue_tot, 0)),
ROUND (NVL (c.original_burdened_cost_tot, 0)),
ROUND (NVL (c.original_labor_hours_tot, 0)),
ROUND (NVL (r.original_revenue_itd, 0)),
ROUND (DECODE (c.baseline_burdened_cost_tot,
0, 0,
(a.burdened_cost_itd / c.baseline_burdened_cost_tot
)
* 100
)
),
ROUND (DECODE (c.baseline_labor_hours_tot,
0, 0,
(a.BILLABLE_LABOR_HOURS_YTD
/ c.baseline_labor_hours_tot
)
* 100
)
),
ROUND ( NVL (c.baseline_burdened_cost_tot, 0)
- NVL (m.cmt_burdened_cost_ptd, 0)
- NVL (a.burdened_cost_itd, 0)
),
ROUND (NVL (a.burdened_cost_itd, 0) + NVL (m.cmt_burdened_cost_ptd, 0)),
ROUND ( NVL (r.baseline_revenue_tot, 0)
- NVL (c.baseline_burdened_cost_tot, 0)
),
ROUND (NVL (a.revenue_itd, 0) - NVL (a.burdened_cost_itd, 0)), 0, 0, 0,
0, 0, 0
FROM pa_tasks t,
(SELECT pah.project_id,
pah.task_id,
pab.budget_type_code,
NVL (pab.base_raw_cost_itd, 0),
NVL (pab.base_raw_cost_ytd, 0),
NVL (pab.base_raw_cost_pp, 0),
NVL (pab.base_raw_cost_ptd, 0),
NVL (pab.base_burdened_cost_itd, 0) baseline_burdened_cost_itd,
NVL (pab.base_burdened_cost_ytd, 0),
NVL (pab.base_burdened_cost_pp, 0),
NVL (pab.base_burdened_cost_ptd, 0) baseline_burdened_cost_ptd,
NVL (pab.orig_raw_cost_itd, 0),
NVL (pab.orig_raw_cost_ytd, 0),
NVL (pab.orig_raw_cost_pp, 0),
NVL (pab.orig_raw_cost_ptd, 0),
NVL (pab.orig_burdened_cost_itd, 0),
NVL (pab.orig_burdened_cost_ytd, 0),
NVL (pab.orig_burdened_cost_pp, 0),
NVL (pab.orig_burdened_cost_ptd, 0),
NVL (pab.orig_labor_hours_itd, 0),
NVL (pab.orig_labor_hours_ytd, 0),
NVL (pab.orig_labor_hours_pp, 0),
NVL (pab.orig_labor_hours_ptd, 0),
NVL (pab.base_labor_hours_itd, 0) baseline_labor_hours_itd,
NVL (pab.base_labor_hours_ytd, 0),
NVL (pab.base_labor_hours_pp, 0),
NVL (pab.base_labor_hours_ptd, 0) baseline_labor_hours_ptd,
NVL (pab.base_raw_cost_tot, 0),
NVL (pab.base_burdened_cost_tot, 0) baseline_burdened_cost_tot,
NVL (pab.orig_raw_cost_tot, 0),
NVL (pab.orig_burdened_cost_tot, 0) original_burdened_cost_tot,
NVL (pab.orig_labor_hours_tot, 0) original_labor_hours_tot,
NVL (pab.base_labor_hours_tot, 0) baseline_labor_hours_tot
FROM pa_project_accum_headers pah, pa_project_accum_budgets pab
WHERE pah.task_id > 0
AND pah.project_id = :P_PROJECT_ID
AND pah.resource_list_id = 0
AND pah.project_accum_id = pab.project_accum_id
AND pab.budget_type_code = 'AC'
--UNION
--SELECT t.project_id, t.task_id, bt.budget_type_code, 0, 0, 0, 0, 0, 0, 0, 0,
-- 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
-- FROM pa_tasks t, pa_budget_types bt
-- WHERE bt.budget_type_code = 'AC'
-- AND t.project_id = :P_PROJECT_ID
-- GROUP BY t.project_id, t.task_id,bt.budget_type_code
) c,
(SELECT pah.project_id,
pah.task_id,
pab.budget_type_code,
NVL (pab.base_revenue_itd, 0) baseline_revenue_itd,
NVL (pab.base_revenue_ytd, 0),
NVL (pab.base_revenue_pp, 0),
NVL (pab.base_revenue_ptd, 0),
NVL (pab.orig_revenue_itd, 0) original_revenue_itd,
NVL (pab.orig_revenue_ytd, 0),
NVL (pab.orig_revenue_pp, 0),
NVL (pab.orig_revenue_ptd, 0),
NVL (pab.orig_labor_hours_itd, 0),
NVL (pab.orig_labor_hours_ytd, 0),
NVL (pab.orig_labor_hours_pp, 0),
NVL (pab.orig_labor_hours_ptd, 0),
NVL (pab.base_labor_hours_itd, 0),
NVL (pab.base_labor_hours_ytd, 0),
NVL (pab.base_labor_hours_pp, 0),
NVL (pab.base_labor_hours_ptd, 0),
NVL (pab.base_revenue_tot, 0) baseline_revenue_tot,
NVL (pab.orig_revenue_tot, 0) original_revenue_tot,
NVL (pab.orig_labor_hours_tot, 0),
NVL (pab.base_labor_hours_tot, 0)
FROM pa_project_accum_headers pah, pa_project_accum_budgets pab
WHERE pah.task_id > 0
AND pah.project_id = :P_PROJECT_ID
AND pah.resource_list_id = 0
AND pah.project_accum_id = pab.project_accum_id
AND pab.budget_type_code = 'AC'
--UNION
-- SELECT t.project_id, t.task_id, bt.budget_type_code, 0, 0, 0, 0, 0, 0, 0, 0,
-- 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
-- FROM pa_tasks t, pa_budget_types bt
-- WHERE bt.budget_type_code = 'AC'
-- AND t.project_id = :P_PROJECT_ID
) r,
(SELECT pah.project_id, pah.task_id, NVL (paa.raw_cost_itd, 0),
NVL (paa.raw_cost_ytd, 0), NVL (paa.raw_cost_pp, 0),
NVL (paa.raw_cost_ptd, 0), NVL (paa.billable_raw_cost_itd, 0),
NVL (paa.billable_raw_cost_ytd, 0), NVL (paa.billable_raw_cost_pp, 0),
NVL (paa.billable_raw_cost_ptd, 0), NVL (paa.burdened_cost_itd, 0) burdened_cost_itd,
NVL (paa.burdened_cost_ytd, 0), NVL (paa.burdened_cost_pp, 0),
NVL (paa.burdened_cost_ptd, 0) burdened_cost_ptd ,
NVL (paa.billable_burdened_cost_itd, 0),
NVL (paa.billable_burdened_cost_ytd, 0),
NVL (paa.billable_burdened_cost_pp, 0),
NVL (paa.billable_burdened_cost_ptd, 0), NVL (paa.labor_hours_itd, 0),
NVL (paa.labor_hours_ytd, 0), NVL (paa.labor_hours_pp, 0),
NVL (paa.labor_hours_ptd, 0), NVL (paa.billable_labor_hours_itd, 0),
NVL (paa.billable_labor_hours_ytd, 0),
NVL (paa.billable_labor_hours_pp, 0),
NVL (paa.billable_labor_hours_ptd, 0), NVL (paa.revenue_itd, 0) revenue_itd,
NVL (paa.revenue_ytd, 0), NVL (paa.revenue_pp, 0),
NVL (paa.revenue_ptd, 0),
1,
paa.BILLABLE_LABOR_HOURS_YTD
FROM pa_project_accum_headers pah, pa_project_accum_actuals paa
WHERE pah.task_id > 0
AND pah.project_id = :P_PROJECT_ID
AND pah.resource_list_id = 0
AND pah.project_accum_id = paa.project_accum_id) a,
(SELECT pah.project_id, pah.task_id, NVL (pac.cmt_raw_cost_itd, 0),
NVL (pac.cmt_raw_cost_ytd, 0), NVL (pac.cmt_raw_cost_pp, 0),
NVL (pac.cmt_raw_cost_ptd, 0), NVL (pac.cmt_burdened_cost_itd, 0),
NVL (pac.cmt_burdened_cost_ytd, 0), NVL (pac.cmt_burdened_cost_pp, 0),
NVL (pac.cmt_burdened_cost_ptd, 0) cmt_burdened_cost_ptd, NVL (pac.cmt_quantity_itd, 0),
NVL (pac.cmt_quantity_ytd, 0), NVL (pac.cmt_quantity_pp, 0),
NVL (pac.cmt_quantity_ptd, 0)
FROM pa_project_accum_headers pah, pa_project_accum_commitments pac
WHERE pah.task_id > 0
AND pah.project_id = :P_PROJECT_ID
AND pah.resource_list_id = 0
AND pah.project_accum_id = pac.project_accum_id) m
WHERE t.project_id = :P_PROJECT_ID
AND t.task_id = c.task_id(+)
AND t.task_id = r.task_id(+)
AND t.task_id = a.task_id(+)
AND t.task_id = m.task_id(+)