Gather Schema Statistics fails with Ora-20001 errors after 11G database upgrade

http://www.conacent.com

Cause :-

There are duplicate rows on FND_HISTOGRAM_COLS table for JE_BE_LINE_TYPE_MAP table.Because of this problem, FND_STATS tries to gather histogram information using wrong command and it fails with ora-20001 errors.

Following SQL should have returned one row , not two.
SQL> select column_name, nvl(hsize,254) hsize
from FND_HISTOGRAM_COLS
where table_name = 'JE_BE_LINE_TYPE_MAP'
order by column_name;

COLUMN_NAME HSIZE
------------------------------ ----------
SOURCE 254
SOURCE 254


Solution :-Find out all duplicates and/or obsolete rows in FND_HISTOGRAM_COLS and delete one of them. Remember to take backup of the FND_HISTOGRAM_COLS table before deleting any data.

-- identify duplicate rows

select table_name, column_name, count(*)
from FND_HISTOGRAM_COLS
group by table_name, column_name
having count(*) > 1;

-- Use above results on the following SQL to delete duplicates

delete from FND_HISTOGRAM_COLS
where table_name = '&TABLE_NAME'
and column_name = '&COLUMN_NAME'
and rownum=1;




JE_FR_DAS_010
TYPE_ENREG

JE_FR_DAS_010_NEW
TYPE_ENREG

JE_BE_LINE_TYPE_MAP
SOURCE

JE_BE_LOGS
DECLARATION_TYPE_CODE

JG_ZZ_SYS_FORMATS_ALL_B
JGZZ_EFT_TYPE

JE_BE_VAT_REP_RULES
LINE_TYPE

JE_BE_VAT_REP_RULES
SOURCE

JE_BE_VAT_REP_RULES
VAT_REPORT_BOX



select owner, table_name, stattype_locked
from dba_tab_statistics
where stattype_locked is not null
and owner not in ('SYS');

SQL> select owner, table_name, stattype_locked
from dba_tab_statistics
where stattype_locked is not null
and owner not in ('SYS');


OWNER                          TABLE_NAME                     STATT
------------------------------ ------------------------------ -----
SYSTEM                         TBLMIG_MSG_QTAB                ALL
SYSTEM                         DEF$_AQERROR                   ALL
SYSTEM                         DEF$_AQCALL                    ALL
ODM                            DMS_QUEUE_TABLE                ALL
APPLSYS                        FND_CP_GSM_IPC_AQTBL           ALL
APPLSYS                        WF_NOTIFICATION_OUT            ALL
APPLSYS                        AQ$_WF_CONTROL_P               ALL


7 rows selected.

SQL>

From the above list 3 tables owned by APPS/APPLSYS shows as locked. Use the below API to release these locks and re-attempt the statitics task. Should be Ok Now.

exec dbms_stats.unlock_schema_stats('schema_owner');

In this case it will be APPS and APPLSYS

exec dbms_stats.unlock_schema_stats('APPLSYS');



SQL> exec dbms_stats.unlock_schema_stats('APPLSYS'); 


1. Run query to find the list of indexes which are in unusable status :

select owner, index_name, index_type,
table_owner, table_name, tablespace_name,
status, last_analyzed
from dba_indexes
where status='UNUSABLE';

 

How Can Used Function Multiple Data

One Function Can Used Multiple Data:

/* One Purchase Order Number Can Multiple Time Receipt and Multiple Invoice Generated . */

Function CF_rcp_noFormula return Char

is
vrcp varchar2(200);
begin
for i in (

Select Ad.Invoice_number rcp_no ----
From Rcv_shipment_headers rh,
Rcv_shipment_lines rl,
Po_distributions_all pd,
Ap_invoice_distributions_all ad
Where
pd.PO_DISTRIBUTION_ID = ad.po_distribution_id and
pd.PO_HEADER_ID = rl.PO_HEADER_ID and
pd.PO_LINE_ID = rl.PO_LINE_ID and
rh.SHIPMENT_HEADER_ID = rl.SHIPMENT_HEADER_ID

pd.PO_HEADER_ID = (Select Po_Header_id

From Po_Headers_all

Where Segment1 = :Po_Num ---Put any Purchase Order Number ))

loop
vrcp := i.rcp_no ||','||vrcp;
end loop ;
return substr(vrcp,1,instr(vrcp,',',1)-1); ------All Invoice Number Insert
exception
when no_data_found then
return null;
end;

OUTPUT:

10001, 10002, 1003, 1004,

How Can Create Place Holder In Report Builder

Create Place Holder (Used Function Data ) in Report Builder

Function CF_BANK_NAMEFormula return Char is
cursor c1 is
select
abb.BANK_NAME,
abb.BANK_BRANCH_NAME,
abb.ADDRESS_LINE1,
abb.ADDRESS_LINE2,
abb.ADDRESS_LINE3,
abb.CITY,
abb.ZIP,
aba.BANK_ACCOUNT_NAME,
aba.BANK_ACCOUNT_NUM
from
ap_invoice_payments_all aip,
ap_bank_branches abb,
ap_bank_accounts_all aba,
ap_checks_all ac
where
aip.CHECK_ID =ac.CHECK_ID and
ac.BANK_ACCOUNT_ID = aba.BANK_ACCOUNT_ID
and aba.BANK_BRANCH_ID = abb.BANK_BRANCH_ID
and aip.INVOICE_ID = :invoice_id;
vbank varchar2(150);
vaddr varchar2(1000);
vbranch varchar2(100);
vacc_name varchar2(100);
vacc_no varchar2(100);
begin
for i in c1
loop
vbank := i.bank_name||' '||vbank;
vaddr := i.ADDRESS_LINE1||chr(10)||i.ADDRESS_LINE2||chr(10)||
i.ADDRESS_LINE3||chr(10)||i.city||'-'||i.zip
||' '||vaddr;
vbranch := i.BANK_BRANCH_NAME||' '||vbranch;
vacc_name := i.BANK_ACCOUNT_NAME||' '||vacc_name;
vacc_no := i.BANK_ACCOUNT_NUM||' '||vacc_no;
end loop;
:CP_bank_acc_name := vacc_name; /*All Cp Name Is Place Holder*/

:CP_bank_acc_no := vacc_no;
:CP_branch_name := vbranch;
:CP_branch_addr := vaddr;
return vbank;
exception
when no_data_found then
return null;

end;

11i And 12i Customer Wise Sales Order Price List

/* Customer Wise Sales Order Price List Query OR Sales Order Wise Price List Query And Table Name*/

Select

Ql.OPERAND Price

, Ql.list_header_id /* Order Header Id Match Oe_orders_header_all and show Order Number */

From

Apps.Qp_List_lines Ql /* All types of modifiers including price modifier list lines used to derive factors. The different types of list lines are based on Look-up Type */

, Apps.Qp_pricing_attributes Qt

Where Qt.List_line_id = Ql.list_line_id

And Ql.LIST_HEADER_ID in (Select PRICE_LIST_ID

From JA_IN_CUSTOMER_ADDRESSES Ca --11i

/*12i Table JAI_CMN_CUS_ADDRESSES ,

Holds the Additional Customer information such as excise and sales tax registration numbers*/

Where Ca.CUSTOMER_ID = Ca.CUSTOMER_ID

Group by PRICE_LIST_ID)

Sales Order Type Name in Oracle Apps 11i and 12i

Oracle Apps 11i & 12i Sales Order Type Name Query
Select Ot.NAME /*Order Type Name*/
From apps.oe_transaction_types_tl Ot, /*All Sales Order Type */
apps.oe_order_headers_all h
Where

H.Order_Number = <Order_Number> /*Put Order Number and Show Sales Order type */

And H.order_type_id = ot.transaction_type_id /*Join Order Header id and Transaction Type Id */

How To Convert Numbers Into Words in Oracle Apps 11i

Oracle Apps Has Provided an Inbuilt function AP_AMOUNT_UTILITIES_PKG which can be used to achieve the result.

SELECT (ap_amount_utilities_pkg.ap_convert_number (111234234324)) Amount_In_Word
FROM DUAL

Example:

One hundred eleven billion two hundred thirty-four million two hundred thirty-four thousand three hundred twenty-four

Convert Date Into Week Range

Firstly Lets See How To Get Week Of The Year. Following Query Can Be Used To Get This

SELECT to_char(sysdate,'WW') FROM Dual;

Now Lets Get The Week Range

SELECT TO_CHAR (TRUNC (SYSDATE, 'IYYY') + ((TO_CHAR (SYSDATE, 'WW') - 1) * 7), 'DD-MON-RR')
|| ' to '
|| TO_CHAR (TRUNC (SYSDATE, 'IYYY') + ((TO_CHAR (SYSDATE, 'WW')) * 7)-1, 'DD-MON-RR')
FROM Dual;

He Output of Above Query For Date 29-Nov-2008 is 24-NOV-08 to 30-NOV-08