autoinvoice: interface lines dont match invoice lines for 5% of invoices ???

[grlewycky]

I am encountering this problem on about 5 invoices out of 100 autoinvoice transactions where the sequencing of LINE_NUMBER appears in a descending sequence versus the correct sequence of the records I loaded using SQL*Loader into RA_INTERFACE_LINES_ALL &

RA_INTERFACE_DISTRIBUTIONS_ALL



Here is a case:

from: RA_INTERFACE_LINES_ALL

INTERFACE|1|LINE|01|95970|EXPENSES ASSOCIATED WITH NE
INTERFACE|1|LINE|02|95970|BONUS PAY
INTERFACE|1|LINE|03|95970|ADD: 146 OVERHEAD


from: Receivables Lines screen

01|95970|BONUS PAY
02|95970|ADD: 146% OVERHEAD
03|95970|EXPENSES ASSOCIATED WITH NEW


Has anyone seen or heard of this. Is this a bug of some sort ?

Why are they happening in a random sequence of some sort??



Thanks



George Lewycky
grlewycky@yahoo.com
Oracle & Financial Applications (pl/sql sql apps faq links code)

[grlewycky]

Hello everyone,

The mystery was solved as to why my records were not loading in the correct orer for these two Autoinvoice tables shown in the two control cards below:

By removing "REPLACE" as shown below SQL*Loader knew to use "TRUNCATE"
By keeping "REPLACE" SQL*Loader would know to use "DELETE"

"REPLACE" INTO TABLE RA_INTERFACE_LINES_ALL
"REPLACE" INTO TABLE RA_INTERFACE_DISTRIBUTIONS_AL

Truncate returns the freed space to the table space and resets the high watermark whereas DELETE does not!!!!

Oracle told me by using DELETE any new rows will be inserted in available spaces ith a degree of unpredictability which is what was happening to me!!


THE OTHER OPTION THAT I FOUND THAT ALSO WORKED AND LET ME AND ORACLE TO THE EXPLANATION WAS BY USING
truncate table ar.RA_INTERFACE_distributions_ALL;
truncate table ar.RA_INTERFACE_LINES_ALL;

Before using the original control cards which had "REPLACE"


George



--------------------------------------------
CORRECTED CONTROL CARDS:

LOAD DATA
INFILE 'c:\INTLINES.TXT'
INTO TABLE RA_INTERFACE_LINES_ALL
FIELDS TERMINATED BY '|' TRAILING NULLCOLS (ORIG_SYSTEM_BILL_CUSTOMER_REF, amount, ORIG_SYSTEM_BILL_ADDRESS_REF, BATCH_SOURCE_NAME, SET_OF_BOOKS_ID, LINE_TYPE, line_number, PURCHASE_ORDER, DESCRIPTION, CURRENCY_CODE, CONVERSION_TYPE, INTERFACE_LINE_ID, INTERFACE_LINE_CONTEXT, INTERFACE_LINE_ATTRIBUTE1, TERM_NAME, ORIG_SYSTEM_BILL_CUSTOMER_ID, CUST_TRX_TYPE_NAME, CONVERSION_DATE, QUANTITY, QUANTITY_ORDERED, UNIT_SELLING_PRICE, CONVERSION_RATE, SALES_ORDER,
trx_date,
gl_date,
term_id,
INTERFACE_LINE_ATTRIBUTE2)



LOAD DATA
INFILE 'c:\INTDIS.TXT'
INTO TABLE RA_INTERFACE_DISTRIBUTIONS_ALL
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(INTERFACE_LINE_ID,
INTERFACE_LINE_CONTEXT,
INTERFACE_LINE_ATTRIBUTE1,
ACCOUNT_CLASS,
PERCENT,
SEGMENT1,
SEGMENT2,
SEGMENT3,
SEGMENT4,
SEGMENT5)