IGW_BUDGET_CATEGORY_V invalid in R12

If you are getting this in 11i then you can apply this patch as per article
Patch 6629280: APPSST11G:11510CU2: IGW: INVALID GROUP BY EXPRESSION IN VIEW DUE TO CBO IFX



Originally we got 70 invalids after applying R12.1.3 patch 9239090. Oracle advised us to run utlirp.sql, followed by utlrp.sql.

We ran utlirp.sql which invalidates all objects
then ran utlrp.sql which reduced the count to 53
then we ran utlrp.sql which reduced the count to 2:

VIEW IGW_BUDGET_CATEGORY_V
PACKAGE BODY IGW_REPORT_PROCESSING

The package was not compiling because of the invalid view. So I extracted the definition of the view from a working R12.1.3 instance through dbms_metadata:

SQL> connect apps/appspassword
SQL> set heading off
SQL> set long4000
SQL> select dbms_metadata.get_ddl('VIEW','IGW_BUDGET_CATEGORY_V') from dual;


CREATE OR REPLACE FORCE VIEW "APPS"."IGW_BUDGET_CATEGORY_V" ("PROPOSAL_ID", "VERSION_ID", "BUDGET_PERIOD_ID", "LINE_ITEM_ID", "EXPENDITURE_TYPE", "EXPENDITURE_CATEGORY_FLAG", "BUDGET_CATEGORY_CODE", "BUDGET_CATEGORY", "BASE_AMT", "OH_APPLIED_FLAG", "OH_COST", "EB_COST") AS
SELECT pbcv.proposal_id,
pbcv.version_id,
pbcv.budget_period_id,
pbcv.line_item_id,
pbcv.expenditure_type,

pbcv.expenditure_category_flag,
pbcv.budget_category_code,
fl.meaning budget_category,
SUM (pbcv.line_item_cost + pbcv.eb_cost) base_amt,
DECODE (pbcv.oh_cost, 0, 'N', 'Y') oh_applied_flag,
pbcv.oh_cost,
pbcv.eb_cost
FROM igw_budget_complete_v pbcv, igw_lookups_v fl
WHERE pbcv.budget_category_code = fl.lookup_code
AND fl.lookup_type = 'IGW_BUDGET_CATEGORY'
GROUP BY pbcv.proposal_id,
pbcv.version_id,
pbcv.budget_period_id,
pbcv.budget_category_code,
fl.meaning,
pbcv.line_item_id,
pbcv.expenditure_type,
pbcv.expenditure_category_flag,
DECODE (pbcv.oh_cost, 0, 'N', 'Y'),
pbcv.oh_cost,
pbcv.eb_cost

I have corrected the indentation above. You may have to correct indentation if you plan to copy from the output generated by dbms_metadata.

Executed this and we have zero invalids now:

1 CREATE OR REPLACE FORCE VIEW "APPS"."IGW_BUDGET_CATEGORY_V" ("PROPOSAL_ID ", "VERSION_ID", "BUDGET_PERIOD_ID", "LINE_ITEM_ID", "EXPENDITURE_TYPE", "EXPEND ITURE_CATEGORY_FLAG", "BUDGET_CATEGORY_CODE", "BUDGET_CATEGORY", "BASE_AMT", "OH _APPLIED_FLAG", "OH_COST", "EB_COST") AS
2 SELECT pbcv.proposal_id,
3 pbcv.version_id,
4 pbcv.budget_period_id,
5 pbcv.line_item_id,
6 pbcv.expenditure_type,
7 pbcv.expenditure_category_flag,
8 pbcv.budget_category_code,
9 fl.meaning budget_category,
10 SUM (pbcv.line_item_cost + pbcv.eb_cost) base_amt,
11 DECODE (pbcv.oh_cost, 0, 'N', 'Y') oh_applied_flag,
12 pbcv.oh_cost,
13 pbcv.eb_cost
14 FROM igw_budget_complete_v pbcv, igw_lookups_v fl
15 WHERE pbcv.budget_category_code = fl.lookup_code
16 AND fl.lookup_type = 'IGW_BUDGET_CATEGORY'
17 GROUP BY pbcv.proposal_id,
18 pbcv.version_id,
19 pbcv.budget_period_id,
20 pbcv.budget_category_code,
21 fl.meaning,
22 pbcv.line_item_id,
23 pbcv.expenditure_type,
24 pbcv.expenditure_category_flag,
25 DECODE (pbcv.oh_cost, 0, 'N', 'Y'),
26 pbcv.oh_cost,
27* pbcv.eb_cost
28 /

View created.

SQL> alter package IGW_REPORT_PROCESSING compile body;

Package body altered.

SQL> select instance from v$thread;

INSTANCE
--------------------------------------------------------------------------------
exampler12

SQL> select count(*) from dba_objects where status='INVALID';

COUNT(*)
----------
0