4 ARP Invalids generated after performing Extended Base line patching
Apps : 11.5.10.2
Activity : Extended baseline patching
Main issue is 4 new Invalids got generated during Post Health checks. Unfortunately these are appearing in Production where in didn't appear in Non-Prod.
Details:
Below are the 4 Invalids
OWNER OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------ -------------------
APPS ARP_APP_PKG PACKAGE BODY
APPS ARP_PROCESS_APPLICATION PACKAGE BODY
APPS ARP_PROC_RECEIPTS1 PACKAGE BODY
APPS ARP_PS_UTIL PACKAGE BODY
Trying to compile these Invalids manually using below commands
alter package apps.ARP_APP_PKG compile body;
alter package apps.ARP_PROCESS_APPLICATION compile body;
alter package apps.ARP_PROC_RECEIPTS1 compile body;
alter package apps.ARP_PS_UTIL compile body;
** Error Details **
SQL> alter package apps.ARP_APP_PKG compile body;
Warning: Package Body altered with compilation errors.
SQL> sho error;
Errors for PACKAGE BODY APPS.ARP_APP_PKG:
LINE/COL ERROR
-------- -----------------------------------------------------------------
90/7 PL/SQL: SQL Statement ignored
329/14 PL/SQL: ORA-00984: column not allowed here
329/14 PLS-00302: component 'INCLUDE_IN_ACCUMULATION' must be declared
SQL>
SQL> alter package apps.ARP_PROCESS_APPLICATION compile body;
Warning: Package Body altered with compilation errors.
SQL> sho error;
Errors for PACKAGE BODY APPS.ARP_PROCESS_APPLICATION:
LINE/COL ERROR
-------- -----------------------------------------------------------------
385/8 PL/SQL: SQL Statement ignored
386/51 PL/SQL: ORA-00904: "INCLUDE_IN_ACCUMULATION": invalid identifier
1850/3 PL/SQL: Statement ignored
1850/12 PLS-00302: component 'INCLUDE_IN_ACCUMULATION' must be declared
3347/6 PL/SQL: SQL Statement ignored
3348/12 PL/SQL: ORA-00904: "INCLUDE_IN_ACCUMULATION": invalid identifier
SQL> alter package apps.ARP_PROC_RECEIPTS1 compile body;
Warning: Package Body altered with compilation errors.
SQL> sho error;
Errors for PACKAGE BODY APPS.ARP_PROC_RECEIPTS1:
LINE/COL ERROR
-------- -----------------------------------------------------------------
717/6 PL/SQL: SQL Statement ignored
718/12 PL/SQL: ORA-00904: "INCLUDE_IN_ACCUMULATION": invalid identifier
791/6 PL/SQL: SQL Statement ignored
792/12 PL/SQL: ORA-00904: "INCLUDE_IN_ACCUMULATION": invalid identifier
1281/6 PL/SQL: SQL Statement ignored
1282/12 PL/SQL: ORA-00904: "INCLUDE_IN_ACCUMULATION": invalid identifier
SQL>
SQL> alter package apps.ARP_PS_UTIL compile body;
Warning: Package Body altered with compilation errors.
SQL> sho error;
Errors for PACKAGE BODY APPS.ARP_PS_UTIL:
LINE/COL ERROR
-------- -----------------------------------------------------------------
409/7 PL/SQL: SQL Statement ignored
413/17 PL/SQL: ORA-00904: "RA"."INCLUDE_IN_ACCUMULATION": invalid
identifier
430/7 PL/SQL: SQL Statement ignored
435/17 PL/SQL: ORA-00904: "RA"."INCLUDE_IN_ACCUMULATION": invalid
identifier
Solution:
Ran the script as apps user as mentioned in Note : 737508.1
SQL> sho user
USER is "APPS"
SQL> !hostname -f;id
kiranginni.ramu.com
uid=20000(apkirang) gid=20000(aakirang) groups=5341(infallai),20000(aakirang)
SQL> CREATE OR REPLACE FORCE VIEW ar_receivable_applications AS
SELECT
RECEIVABLE_APPLICATION_ID ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
CREATED_BY ,
CREATION_DATE ,
AMOUNT_APPLIED ,
AMOUNT_APPLIED_FROM ,
TRANS_TO_RECEIPT_RATE ,
GL_DATE ,
CODE_COMBINATION_ID ,
SET_OF_BOOKS_ID ,
DISPLAY ,
APPLY_DATE ,
APPLICATION_TYPE ,
STATUS ,
PAYMENT_SCHEDULE_ID ,
LAST_UPDATE_LOGIN ,
CASH_RECEIPT_ID ,
APPLIED_CUSTOMER_TRX_ID ,
APPLIED_CUSTOMER_TRX_LINE_ID ,
APPLIED_PAYMENT_SCHEDULE_ID ,
CUSTOMER_TRX_ID ,
LINE_APPLIED ,
TAX 2 3 4 5 6 7 8 9 10 11 _APPLIED ,
FREIGHT_APPLIED ,
RECEIVABLES_CHARGES_APPLIED ,
ON_ACCOUNT_CUSTOMER ,
RECEIVABLES_TRX_ID ,
EARNED_DISCOUNT_TAKEN ,
UNEARNED_DISCOUNT_TAKEN ,
DAYS_LATE ,
APPLICATION_RULE ,
GL_POSTED_DATE ,
COMMENTS ,
ATTRIBUTE_CATEGORY ,
ATTRIBUTE1 ,
ATTRIBUTE2 12 13 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
POSTABLE ,
POSTING_CONTROL_ID ,
ACCTD_AMOUNT_APPLIED_FROM ,
ACCTD_AMOUNT_APPLIED_TO ,
ACCTD_EARNED_DISCOUNT_TAKEN ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRI 14 15 BUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15 ,
CONFIRMED_FLAG ,
PROGRAM_APPLICATION_ID ,
PROGRAM_ID ,
PROGRAM_UPDATE_DATE ,
REQUEST_ID ,
USSGL_TRANSACTION_CODE ,
USSGL_TRANSACTION_CODE_CONTEXT ,
EARNED_DISCOUNT_CCID ,
UNEARNED_DISCOUNT_CCID ,
ACCTD_UNEARNED_DISCOUNT 16 _TAKEN ,
REVERSAL_GL_DATE ,
CASH_RECEIPT_HISTORY_ID ,
ORG_ID ,
TAX_CODE ,
GLOBAL_ATTRIBUTE1 ,
GLOBAL_ATTRIBUTE2 ,
GLOBAL_ATTRIBUTE3 ,
GLOBAL_ATTRIBUTE4 ,
GLOBAL_ATTRIBUTE5 ,
GLOBAL_ATTRIBUTE6 ,
GLOBAL_ATTRIBUTE7 ,
GLOBAL_ATTRIBUTE8 ,
GLOBAL_ATTRIBUTE9 ,
GL 17 OBAL_ATTRIBUTE10 ,
GLOBAL_ATTRIBUTE11 ,
GLOBAL_ATTRIBUTE12 ,
GLOBAL_ATTRIBUTE13 ,
GLOBAL_ATTRIBUTE14 ,
GLOBAL_ATTRIBUTE15 ,
GLOBAL_ATTRIBUTE16 ,
GLOBAL_ATTRIBUTE17 ,
GLOBAL_ATTRIBUTE18 ,
GLOBAL_ATTRIBUTE19 ,
GLOBAL_ATTRIBUTE20 ,
GLOBAL_ATTRIBUTE_CATEGORY , 18
CONS_INV_ID ,
CONS_INV_ID_TO ,
LINE_EDISCOUNTED ,
TAX_EDISCOUNTED ,
FREIGHT_EDISCOUNTED ,
CHARGES_EDISCOUNTED ,
LINE_UEDISCOUNTED ,
TAX_UEDISCOUNTED ,
FREIGHT_UEDISCOUNTED ,
CHARGES_UEDISCOUNTED ,
RULE_SET_ID ,
EDISC_TAX_ACCT_RULE ,
UNEDISC 19 20 21 _TAX_ACCT_RULE ,
LINK_TO_TRX_HIST_ID ,
LINK_TO_CUSTOMER_TRX_ID,
APPLICATION_REF_TYPE ,
APPLICATION_REF_ID ,
APPLICATION_REF_NUM ,
CHARGEBACK_CUSTOMER_TRX_ID ,
SECONDARY_APPLICATION_REF_ID ,
PAYMENT_SET_ID ,
APPLICATION_REF_REASON ,
CUSTOMER_REFERENCE ,
cus 22 tomer_reason ,
applied_rec_app_id ,
secondary_application_ref_type ,
secondary_application_ref_num,
ON_ACCT_CUST_ID,
ON_ACCT_CUST_SITE_USE_ID,
ON_ACCT_PO_NUM,
INCLUDE_IN_ACCUMULATION
FROM AR_RECEIVABLE_APPLICATIONS_ALL
WHERE NVL(ORG_ID,NVL(TO_NUMBER(DECODE 23 (SUBSTRB(USERENV('CLIENT_INFO'),1 ,1),' ',
NULL, SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)) =
NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1),' ',
NULL,SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99); 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128
View created.
Compiling the Apps Schema using adadmin :
Extracted information from the terminal..
All workers have quit.
Dropping FND_INSTALL_PROCESSES table...
FND_INSTALL_PROCESSES table dropped.
Dropping AD_DEFERRED_JOBS table...
AD_DEFERRED_JOBS table dropped.
sqlplus -s APPS/***** @/kirang/applmgr/11510/ad/11.5.0/admin/sql/adutlrcmp.pls APPLSYS ***** APPS ***** ***** 4 0 NONE FALSE
...
...
Done purging timing information for prior sessions.
AD Administration is complete.
Errors and warnings are listed in the log file
/kirang/applmgr/11510/admin/kirang/log/adadmin.log
and in other log files in the same directory.
Verification:-
-------------
Compared Post Invalids and dropped as all the earlier 4 Invalids got compiled.
SQL> SELECT OWNER, OBJECT_NAME, OBJECT_TYPE FROM DBA_OBJECTS WHERE STATUS='INVALID' MINUS SELECT OWNER, OBJECT_NAME, OBJECT_TYPE FROM invbkp_17jul11;
no rows selected
SQL> drop table invbkp_17jul11;
Table dropped.
Summary:-
===========
View APPS.AR_RECEIVABLE_APPLICATIONS does not have column INCLUDE_IN_ACCUMULATION
The file that adds this column to the view is arb5473882.sql 115.3, and it is delivered in Patch 7317995 that customer applied, however, for some reason, the column was added to AR.AR_RECEIVABLE_APPLICATIONS_ALL table, but it was not added to the view.
Ran that script as mentioned in the Note and Compiled Apps Schema through adadmin.
Thanks
Kiran G
