Creating Database Views (Oracle Bill Presentment Architecture)

To obtain billing data from a source other than the seeded ones, you must first register the application (or other source) as a new data source in Bill Presentment Architecture (BPA).

After registering new data sources, you must complete your BPA configuration by creating any specific database views required, and then registering those views as data source views in BPA. This lets you access additional billing information from the new data sources.

Some database views from Oracle Receivables and seeded applications, such as Oracle Service Contracts and Oracle Order Management, are seeded as data source views in BPA. You can create other database views for these applications to provide any additional data required, but you must then register them as data source views in BPA.

You can create as many views as you need for the Header and Footer display area. The Lines and Tax display area and the Details Page display area may each have only one view.

Suggestion: When creating a database view, consider the type of information that you want the bill to display. When you later register the view in BPA, carefully set up the parameters so that the view will retrieve the correct information that you want the bill to display.

For example, suppose you create a view that will retrieve additional information for display on a bill. When you register the view in BPA, you might enter Transaction Number as the value for the Transaction Attribute parameter to successfully return the additional information. See: Selecting Parameters.

SQL Template to Create a View for Lines and Tax Area

If you create a new database view to supply content items for the Lines and Tax area of a billing template, then you must use the following SQL template to create the view.

Note: If you create a new database view for the Header and Footer or Details Page display areas, do not use this template. Create a SQL statement that will create a view for your requirements.

This template creates a view with the Receivables columns in the sequence required to display billing lines in the Lines and Tax area. The view must include all columns from CUSTOMER_TRX_ID to INTERFACE_LINE_ATTRIBUTE15. You can add additional columns at the end of the query.

CREATE OR REPLACE FORCE VIEW <User created line view name> 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, to_char(nvl(lines.unit_selling_price,0)) unformatted_unit_price, lines.ATTRIBUTE1, lines.ATTRIBUTE2, lines.ATTRIBUTE3, lines.ATTRIBUTE4, lines.ATTRIBUTE5, lines.ATTRIBUTE6, lines.ATTRIBUTE7, lines.ATTRIBUTE8, lines.ATTRIBUTE9, lines.ATTRIBUTE10, lines.ATTRIBUTE11, lines.ATTRIBUTE12, lines.ATTRIBUTE13, lines.ATTRIBUTE14, lines.ATTRIBUTE15, lines.SET_OF_BOOKS_ID, lines.REASON_CODE, lines.QUANTITY_ORDERED, lines.QUANTITY_CREDITED, lines.UNIT_STANDARD_PRICE, lines.SALES_ORDER_LINE, lines.SALES_ORDER_DATE, lines.ACCOUNTING_RULE_DURATION, lines.ATTRIBUTE_CATEGORY, lines.RULE_START_DATE, lines.INTERFACE_LINE_CONTEXT, lines.SALES_ORDER_SOURCE, lines.REVENUE_AMOUNT, lines.DEFAULT_USSGL_TRANSACTION_CODE, lines.DEFAULT_USSGL_TRX_CODE_CONTEXT, lines.LAST_PERIOD_TO_CREDIT, lines.ITEM_CONTEXT, lines.TAX_EXEMPT_FLAG, lines.TAX_EXEMPT_NUMBER, lines.TAX_EXEMPT_REASON_CODE, lines.TAX_VENDOR_RETURN_CODE, lines.GLOBAL_ATTRIBUTE_CATEGORY, lines.GROSS_UNIT_SELLING_PRICE, lines.GROSS_EXTENDED_AMOUNT, lines.EXTENDED_ACCTD_AMOUNT, lines.MRC_EXTENDED_ACCTD_AMOUNT, lines.ORG_ID, lines.GLOBAL_ATTRIBUTE1, lines.GLOBAL_ATTRIBUTE2, lines.GLOBAL_ATTRIBUTE3, lines.GLOBAL_ATTRIBUTE4, lines.GLOBAL_ATTRIBUTE5, lines.GLOBAL_ATTRIBUTE6, lines.GLOBAL_ATTRIBUTE7, lines.GLOBAL_ATTRIBUTE8, lines.GLOBAL_ATTRIBUTE9, lines.GLOBAL_ATTRIBUTE10, lines.GLOBAL_ATTRIBUTE11, lines.GLOBAL_ATTRIBUTE12, lines.GLOBAL_ATTRIBUTE13, lines.GLOBAL_ATTRIBUTE14, lines.GLOBAL_ATTRIBUTE15, lines.GLOBAL_ATTRIBUTE16, lines.GLOBAL_ATTRIBUTE17, lines.GLOBAL_ATTRIBUTE18, lines.GLOBAL_ATTRIBUTE19, lines.GLOBAL_ATTRIBUTE20, <Additional line column>, < > < > FROM mtl_units_of_measure uom, ra_customer_trx_lines lines, ra_customer_trx trx, <Additional join table> 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 <Additional join condition>, and < >