Line View is Returning All Items Instead of the Particular Item for That Line

Last updated on FEBRUARY 01, 2018

Applies to:

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