Configure Business Intelligence Publisher to Print Joint Venture Receivables Invoices
To print joint venture invoices from Oracle Receivables, you can configure the existing Oracle Business Intelligence Publisher (BI Publisher) data model and template for printing receivables invoices. This enables you to print the invoice in a format that outlines the joint venture transaction and partner information associated with each invoice.
To do this, an application implementation consultant must perform the following one-time tasks:
-
Update the Receivables data model to include the joint venture data source.
-
Build the RTF template using a sample XML file as the data source for joint ventures.
Update the Receivables Data Model to Include Joint Venture Data Source
A data model contains a set of instructions for BI Publisher to retrieve and structure data for a report. A data model consists of data sets that are retrieved from a single or multiple data sources (for example, the data returned from the columns in the Receivables table). To print joint venture invoices using the data set for printing receivables invoices, you update the data set to include a data source for joint ventures.
You update the SQL query for the Receivables Transactions Print data model to retrieve joint venture data from the columns in these tables: Joint Venture Distributions, Joint Venture Transactions, and so on. At a minimum, you are recommended to add the following joint venture data fields in the SQL query:
-
Joint Venture Name
-
Transaction Date
-
Invoicing Partner Name
-
Ownership Percentage
-
Gross Amount
-
Primary Ledger Currency
-
Distributed Amount
-
Project
-
Task Name
-
Expenditure Item Name
-
Access the Catalog for BI Publisher Enterprise.
-
In the Folders pane of the Catalog, navigate to Financials, Receivables, and click Bill Presentment.
-
Click the Expand button below the Data Models folder and search for Transactions Print Data Model. Click Edit to update this data model.
-
On TransactionsPrintDm page, ensure that the Diagram tab is selected and then scroll down to access the data columns.
The G_Line column contains the data fields for invoice lines. Joint venture invoices are created at the line level, and therefore you must update the SQL query for this column.
-
With the G_Line column selected, click Edit Selected Data Set (pencil icon).
-
On Edit Data Set Lines, add the query for joint ventures to the existing SQL query.
Use the following SQL query as an example to add additional data source for joint ventures.
, c.interface_line_attribute1 jv_jv_name, (select to_char(transaction_date, 'YYYY-MM-DD' )from jv_distributions where c.interface_line_attribute5 = to_char(distribution_id ) ) jv_transaction_date, (select INVOICING_PARTNER_SHORT_NAME from jv_invoicing_partners_b inv, jv_stakeholders_b stk, jv_distributions dis where c.interface_line_attribute5 = to_char(dis.distribution_id ) and stk.INVOICING_PARTNER_ID = inv.INVOICING_PARTNER_ID and dis.STAKEHOLDER_ID = stk.STAKEHOLDER_ID) jv_ip_name, (select PERCENTAGE_OF_INTEREST from jv_distributions where c.interface_line_attribute5 = to_char(distribution_id )) jv_ownership_percentage, ((select case when gl.accounted_DR is not null then gl.accounted_DR else gl.accounted_CR end from jv_distributions dis ,jv_transactions tran, gl_je_lines gl where c.interface_line_attribute5 = to_char(distribution_id) and tran.application_id <=-99 and gl.JE_HEADER_ID = tran.header_id and gl.je_line_num = tran.line_num and dis.TRANSACTION_ID = tran.TRANSACTION_ID) union (select case when ae.accounted_DR is not null then ae.accounted_DR else ae.accounted_CR end from jv_distributions dis ,jv_transactions tran, xla_ae_lines ae where c.interface_line_attribute5 = to_char(distribution_id) and tran.application_id >0 and ae.AE_HEADER_ID = tran.header_id and ae.ae_line_num = tran.line_num AND dis.TRANSACTION_ID = tran.TRANSACTION_ID ) union (select case when oh.DEBIT_AMOUNT is not null then oh.DEBIT_AMOUNT else oh.CREDIT_AMOUNT end from jv_distributions dis ,jv_transactions tran, jv_generated_transactions oh where c.interface_line_attribute5 = to_char(distribution_id) and tran.application_id = -10568 and oh.HEADER_ID = tran.header_id and oh.LINE_NUM = tran.line_num and dis.TRANSACTION_ID = tran.TRANSACTION_ID)) jv_gross_amount, (select currency_code from jv_distributions where c.interface_line_attribute5 = to_char(distribution_id ) ) as jv_primary_ledger_currency, (select nvl(distributed_debit_amount,distributed_credit_amount) from jv_distributions where c.interface_line_attribute5 = to_char(distribution_id ) ) as jv_distributed_amount, (select name from pjf_projects_all_vl proj, jv_distributions dist, jv_transactions tran where c.interface_line_attribute5 = to_char(dist.distribution_id ) and tran.transaction_id = dist.transaction_id and tran.project_id = proj.project_id ) jv_project, (select name from PJF_PROJ_ELEMENTS_VL proj, jv_distributions dist, jv_transactions tran where c.interface_line_attribute5 = to_char(dist.distribution_id ) and tran.transaction_id = dist.transaction_id and tran.TASK_ID = proj.PROJ_ELEMENT_ID ) jv_task_name, (select EXPENDITURE_TYPE_NAME from PJF_EXP_TYPES_VL expen, jv_distributions dist, jv_transactions tran where c.interface_line_attribute5 = to_char(dist.distribution_id ) and tran.transaction_id = dist.transaction_id and tran.EXPENDITURE_TYPE_ID = expen.EXPENDITURE_TYPE_ID ) jv_exp_item_nameThe following example shows the complete SQL query after adding the example query provided above. The query for joint ventures is in bold for easy identification.
select base.*, to_char(rownum) as sequence_number from (SELECT c.customer_trx_id customer_trx_id_line, c.customer_trx_line_id customer_trx_line_id, to_char(decode( c2.line_number, null, c.line_number, null)) line_number, c.line_type line_type, AR_BPA_UTILS_PKG.fn_get_line_description(c.customer_trx_line_id) line_description, decode(c.line_type, 'TAX', null, nvl(c.quantity_invoiced, c.quantity_credited)) quantity, uom.unit_of_measure unit_of_measure_name, eitem.item_number item_number, --null unit_of_measure_name, c.unit_selling_price unit_price, to_char(c.extended_amount,fnd_currency.get_format_mask(trx.invoice_currency_code,40)) extended_amount, c.sales_order line_sales_order, c.uom_code, -- null uom_code, trx.trx_number line_trx_number, decode(c.line_type, 'TAX', null, AR_INVOICE_SQL_FUNC_PUB.get_taxyn ( c.customer_trx_line_id )) tax_exists_for_this_line_flag, c.tax_rate as line_tax_rate, --decode(c.line_type, 'TAX', v.tax_code, null) as line_tax_code, null line_tax_code, null printed_tax_name, --decode(c.line_type, 'TAX', v.printed_tax_name, null) as line_printed_tax_name, (SELECT party_site.PARTY_SITE_NAME FROM hz_party_sites party_site, hz_cust_site_uses_all acct_uses , hz_cust_acct_sites_all acct_site WHERE party_site.party_site_id = acct_site.party_site_id and acct_site.cust_acct_site_id = acct_uses.cust_acct_site_id and acct_uses.site_use_id = c.ship_to_site_use_id ) ship_to_site_name, --Bug: 18705899 c.interface_line_attribute1, c.interface_line_attribute2, c.interface_line_attribute3, c.interface_line_attribute4, c.interface_line_attribute5, c.interface_line_attribute6, c.interface_line_attribute7, c.interface_line_attribute8, c.interface_line_attribute9, c.interface_line_attribute10, c.interface_line_attribute11, c.interface_line_attribute12, c.interface_line_attribute13, c.interface_line_attribute14, c.interface_line_attribute15, to_char(c.unit_selling_price) unformatted_unit_price, to_char(nvl(c.extended_amount,0)) unformatted_extended_amount, c.ATTRIBUTE1, c.ATTRIBUTE2, c.ATTRIBUTE3, c.ATTRIBUTE4, c.ATTRIBUTE5, c.ATTRIBUTE6, c.ATTRIBUTE7, c.ATTRIBUTE8, c.ATTRIBUTE9, c.ATTRIBUTE10, c.ATTRIBUTE11, c.ATTRIBUTE12, c.ATTRIBUTE13, c.ATTRIBUTE14, c.ATTRIBUTE15, c.SET_OF_BOOKS_ID, c.REASON_CODE, c.QUANTITY_ORDERED, c.QUANTITY_CREDITED, c.UNIT_STANDARD_PRICE, c.SALES_ORDER_LINE, to_char(c.SALES_ORDER_DATE,'YYYY-MM-DD') SALES_ORDER_DATE, c.ACCOUNTING_RULE_DURATION, c.ATTRIBUTE_CATEGORY, to_char(c.RULE_START_DATE, 'YYYY-MM-DD') RULE_START_DATE, to_char(c.billing_period_start_date,'YYYY-MM-DD') billing_period_start_date, to_char(c.billing_period_end_date,'YYYY-MM-DD') billing_period_end_date, c.INTERFACE_LINE_CONTEXT, c.SALES_ORDER_SOURCE, c.REVENUE_AMOUNT, c.DEFAULT_USSGL_TRANSACTION_CODE, c.DEFAULT_USSGL_TRX_CODE_CONTEXT, c.LAST_PERIOD_TO_CREDIT, c.ITEM_CONTEXT, c.TAX_EXEMPT_FLAG, c.TAX_EXEMPT_NUMBER, c.TAX_EXEMPT_REASON_CODE, c.TAX_VENDOR_RETURN_CODE, c.GLOBAL_ATTRIBUTE_CATEGORY, c.GROSS_UNIT_SELLING_PRICE, c.GROSS_EXTENDED_AMOUNT, c.EXTENDED_ACCTD_AMOUNT, c.MRC_EXTENDED_ACCTD_AMOUNT, c.ORG_ID line_org_id, c.GLOBAL_ATTRIBUTE1, c.GLOBAL_ATTRIBUTE2, c.GLOBAL_ATTRIBUTE3, c.GLOBAL_ATTRIBUTE4, c.GLOBAL_ATTRIBUTE5, c.GLOBAL_ATTRIBUTE6, c.GLOBAL_ATTRIBUTE7, c.GLOBAL_ATTRIBUTE8, c.GLOBAL_ATTRIBUTE9, c.GLOBAL_ATTRIBUTE10, c.GLOBAL_ATTRIBUTE11, c.GLOBAL_ATTRIBUTE12, c.GLOBAL_ATTRIBUTE13, c.GLOBAL_ATTRIBUTE14, c.GLOBAL_ATTRIBUTE15, c.GLOBAL_ATTRIBUTE16, c.GLOBAL_ATTRIBUTE17, c.GLOBAL_ATTRIBUTE18, c.GLOBAL_ATTRIBUTE19, c.GLOBAL_ATTRIBUTE20, c.interface_line_attribute1 jv_jv_name, (select to_char(transaction_date, 'YYYY-MM-DD' )from jv_distributions where c.interface_line_attribute5 = to_char(distribution_id ) ) jv_transaction_date, (select INVOICING_PARTNER_SHORT_NAME from jv_invoicing_partners_b inv, jv_stakeholders_b stk, jv_distributions dis where c.interface_line_attribute5 = to_char(dis.distribution_id ) and stk.INVOICING_PARTNER_ID = inv.INVOICING_PARTNER_ID and dis.STAKEHOLDER_ID = stk.STAKEHOLDER_ID) jv_ip_name, (select PERCENTAGE_OF_INTEREST from jv_distributions where c.interface_line_attribute5 = to_char(distribution_id )) jv_ownership_percentage, ((select case when gl.accounted_DR is not null then gl.accounted_DR else gl.accounted_CR end from jv_distributions dis ,jv_transactions tran, gl_je_lines gl where c.interface_line_attribute5 = to_char(distribution_id) and tran.application_id <=-99 and gl.JE_HEADER_ID = tran.header_id and gl.je_line_num = tran.line_num and dis.TRANSACTION_ID = tran.TRANSACTION_ID) union (select case when ae.accounted_DR is not null then ae.accounted_DR else ae.accounted_CR end from jv_distributions dis ,jv_transactions tran, xla_ae_lines ae where c.interface_line_attribute5 = to_char(distribution_id) and tran.application_id >0 and ae.AE_HEADER_ID = tran.header_id and ae.ae_line_num = tran.line_num AND dis.TRANSACTION_ID = tran.TRANSACTION_ID ) union (select case when oh.DEBIT_AMOUNT is not null then oh.DEBIT_AMOUNT else oh.CREDIT_AMOUNT end from jv_distributions dis ,jv_transactions tran, jv_generated_transactions oh where c.interface_line_attribute5 = to_char(distribution_id) and tran.application_id = -10568 and oh.HEADER_ID = tran.header_id and oh.LINE_NUM = tran.line_num and dis.TRANSACTION_ID = tran.TRANSACTION_ID)) jv_gross_amount, (select currency_code from jv_distributions where c.interface_line_attribute5 = to_char(distribution_id ) ) as jv_primary_ledger_currency, (select nvl(distributed_debit_amount,distributed_credit_amount) from jv_distributions where c.interface_line_attribute5 = to_char(distribution_id ) ) as jv_distributed_amount, (select name from pjf_projects_all_vl proj, jv_distributions dist, jv_transactions tran where c.interface_line_attribute5 = to_char(dist.distribution_id ) and tran.transaction_id = dist.transaction_id and tran.project_id = proj.project_id ) jv_project, (select name from PJF_PROJ_ELEMENTS_VL proj, jv_distributions dist, jv_transactions tran where c.interface_line_attribute5 = to_char(dist.distribution_id ) and tran.transaction_id = dist.transaction_id and tran.TASK_ID = proj.PROJ_ELEMENT_ID ) jv_task_name, (select EXPENDITURE_TYPE_NAME from PJF_EXP_TYPES_VL expen, jv_distributions dist, jv_transactions tran where c.interface_line_attribute5 = to_char(dist.distribution_id ) and tran.transaction_id = dist.transaction_id and tran.EXPENDITURE_TYPE_ID = expen.EXPENDITURE_TYPE_ID ) jv_exp_item_name from ra_customer_trx_lines_all c, ra_customer_trx_lines_all c2, inv_units_of_measure_vl uom, ra_customer_trx_all trx, egp_system_items_vl eitem, ar_system_parameters_all sysp --ar_vat_tax_all_vl v where c.customer_trx_id = :customer_trx_id AND trx.customer_trx_id = c.customer_trx_id AND trx.org_id = c.org_id AND trx.complete_flag = 'Y' and c.link_to_cust_trx_line_id = c2.customer_trx_line_id(+) and c.org_id = c2.org_id(+) and c.uom_code = uom.uom_code(+) and c.inventory_item_id = eitem.inventory_item_id (+) and c.org_id = sysp.org_id and ( (c.inventory_item_id is not null and sysp.item_validation_org_id = eitem.organization_id) or c.inventory_item_id is null) --and c.vat_tax_id = v.vat_tax_id(+) --and c.org_id = v.org_id(+) --and c.line_type in ('LINE', 'TAX', 'CB') --&WHERE_LINE_TYPE order by decode( c2.line_number, null, decode( c.line_type, 'LINE', c.line_number*10000+0, 'TAX', c.line_number*10000+8000, 100000000000), decode( c2.line_type, 'LINE', c2.line_number*10000+0, 'TAX', c2.line_number*10000+8000, c2.line_number*10000+9000) + decode( c.line_type, 'LINE', 0, 'CB', 0, 'TAX', 8000, 'FREIGHT', 9000) + c.line_number )) base -
Click OK to save the query.
Note: If the system issues an error about missing right parenthesis, verify that you commented the following line in the sql query: --&WHERE_LINE_TYPE -
If the system displays a window to enter values for lexical references in SQL Lines, enter LINES and click OK.
-
Click the save icon.
Build the RTF Template Using a Sample XML File as the Data Source for Joint Ventures
After you build your data model, you need to attach a representative set of sample data generated from your data model. The BI Publisher Template Builder requires sample data to build a template.
-
To get a sample XML file, run the Print Receivables Transactions ESS process for a joint venture invoice. Go to the Output section and download the XML file. The XML file has joint venture information because of the modified data model.
-
Upload the sample XML file to the data model from a local directory. To do so:
-
Download a copy of the existing Default Invoice Template from BI Publisher into your local directory.
To download, navigate to Shared Folders, Financials, Receivables, and Bill Presentment on the Catalog tab. Click Edit under Invoice Print Template, and click the Edit button under Default Invoice Template to download it.
-
Open the downloaded template and upload the sample XML file.
The joint venture data fields are added to the RTF template.
-
-
Edit the template as needed. Add data fields that you want to print in the invoice, and upload the template to BI Publisher with an appropriate name.
Once the new template is available in BI Publisher, it is available in the option of the Print Receivables Transactions ESS process.