PURCH_INVOICE_NUM somehow gets Leading Zeros in W_AP_AGING_INVOICE_A (OBIA Snapshot Table)

user9252465

Hi All,

I have spotted a issue in OBI Apps v11.1.1.7 where the table W_AP_AGING_INVOICE_A is being populated with snapshots but the historical snapshot records have leading zeros in the Purch Invoice Num. If I track back through the ODI ETL then W_AP_AGING_INVOICE_A is populated from W_AP_XACT_F and the Invoice Number comes from AP_INVOICES_ALL in EBS. Therefore my observations are below:

select * from ap_invoices_all where invoice_num IN ('0000001234', '1234');

--brings back 1 row, there is no '0000001234'

select * from w_ap_xact_f where purch_invoice num IN ('0000001234', '1234');

--brings back 1 row, there is no '0000001234'

select * from w_ap_aging_invoice_a where purch_invoice num IN ('0000001234', '1234');

-- brings back 5 rows:

PURCH_INVOICE_NUM	SNAPSHOT_DT_WID	REF_DOC_NUM
1234	20170228	98765~1
1234	20170131	98765~1
0000001234	20161231	45678~1
0000001234	20161031	45678~1
0000001234	20160930	45678~1

Therefore my issue is where and how does the purch_invoice_num '0000001234' come into W_AP_AGING_INVOICE_A? It seems like somewhere in the ODI ETL it is generating previous snapshots and makes the PURCH_INVOICE_NUM 10 characters so adds the leading zeros.

There are other examples of Purch Invoice Nums where this also happens so looks like a issues for all the data that gets populated.

All help is much appreciated!!

user9252465

Please note the invoice num '0000001234' does not exist in the EBS source or W_AP_XACT_F, so how does it end up in W_AP_AGING_INVOICE_A with the leading zeros? is there a parameter or setting somewhere which adds the leading zeros to make it 10 characters?

This happens for other invoices where zeros are added to make it 10 characters.