Line View is Returning All Items Instead of the Particular Item for That Line
(Doc ID 356823.1)
Last updated on MARCH 14, 2022
Applies to:
Oracle Bill Presentment Architecture - Version 11.5.10.0 and laterOracle Receivables - Version 11.5.10.0 and later
Information in this document applies to any platform.
Symptoms
A new line view was created using Order Entry as Interface Context to bring in the item number , however the printed invoice included item numbers that are not on that specific Receivables Transaction. Instead it included all item numbers on all Sales Orders, i.e. a 2 lines AR transactions returned more than 200 lines.
Changes
The view that was used to bring in the the item number was:
CREATE OR REPLACE VIEW XXABC_BPA_VIEW
(CUSTOMER_TRX_ID, CUSTOMER_TRX_LINE_ID, LINE_NUMBER, LINE_TYPE, DESCRIPTION,
QUANTITY, UNIT_OF_MEASURE_NAME, UNIT_PRICE, EXTENDED_AMOUNT, SALES_ORDER,
UOM_CODE, TRX_NUMBER, TAX_EXISTS_FOR_THIS_LINE_FLAG, LINE_TAX_RATE, TAX_CODE,
PRINTED_TAX_NAME, INTERFACE_LINE_ATTRIBUTE1, INTERFACE_LINE_ATTRIBUTE2,
INTERFACE_LINE_ATTRIBUTE3, INTERFACE_LINE_ATTRIBUTE4,
INTERFACE_LINE_ATTRIBUTE5, INTERFACE_LINE_ATTRIBUTE6,
INTERFACE_LINE_ATTRIBUTE7, INTERFACE_LINE_ATTRIBUTE8,
INTERFACE_LINE_ATTRIBUTE9,
INTERFACE_LINE_ATTRIBUTE10, INTERFACE_LINE_ATTRIBUTE11,
INTERFACE_LINE_ATTRIBUTE12, INTERFACE_LINE_ATTRIBUTE13,
INTERFACE_LINE_ATTRIBUTE14,
INTERFACE_LINE_ATTRIBUTE15, ITEM_NUMBER)
AS
SELECT
lines.customer_trx_id customer_trx_id,
lines.customer_trx_line_id customer_trx_line_id,
to_char(lines.line_number) line_number,
lines.line_type line_type,
nvl(AR_INVOICE_SQL_FUNC_PUB.get_description(lines.customer_trx_line_id ),
lines.description) description,
to_char(nvl(lines.quantity_invoiced,
lines.quantity_credited)) quantity,
uom.unit_of_measure unit_of_measure_name,
to_char(nvl(lines.unit_selling_price,0),
fnd_currency.get_format_mask(trx.invoice_currency_code,40)) unit_price,
to_char(lines.extended_amount,
fnd_currency.get_format_mask
(trx.invoice_currency_code,40)) extended_amount,
lines.sales_order,
lines.uom_code,
trx.trx_number,
AR_INVOICE_SQL_FUNC_PUB.GET_taxyn(lines.customer_trx_line_id)
tax_exists_for_this_line_flag,
AR_BPA_UTILS_PKG.FN_GET_LINE_TAXRATE(lines.customer_trx_line_id) line_tax_rate,
AR_BPA_UTILS_PKG.FN_GET_LINE_TAXCODE(lines.customer_trx_line_id) tax_code,
AR_BPA_UTILS_PKG.FN_GET_LINE_TAXNAME(lines.customer_trx_line_id)
printed_tax_name,
lines.interface_line_attribute1,
lines.interface_line_attribute2,
lines.interface_line_attribute3,
lines.interface_line_attribute4,
lines.interface_line_attribute5,
lines.interface_line_attribute6,
lines.interface_line_attribute7,
lines.interface_line_attribute8,
lines.interface_line_attribute9,
lines.interface_line_attribute10,
lines.interface_line_attribute11,
lines.interface_line_attribute12,
lines.interface_line_attribute13,
lines.interface_line_attribute14,
lines.interface_line_attribute15,
msi.segment1 item_number
FROM
mtl_units_of_measure uom,
ra_customer_trx_lines_all lines,
ra_customer_trx_all trx,
mtl_system_items_b msi
WHERE
trx.customer_trx_id = lines.customer_trx_id
AND trx.complete_flag = 'Y'
AND lines.uom_code = uom.uom_code(+)
and lines.line_type ='LINE'
and msi.inventory_item_id(+) = lines.inventory_item_id
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
Symptoms |
Changes |
Cause |
Solution |