I’m working the ‘View Accounting Lines Detailed Report’ and need to add an additional field with the logic based on field PA_ADDITION_FLAG of AP_INVOICE_DISTRIBUTIONS_ALL table to specify if project related invoice distributions have been transferred into Oracle Projects.
This is the original query which has been working fine:
select
NVL(ai.invoice_num, APC.CHECK_NUMBER) doc_num,
pov.vendor_name Supplier,
al.ae_line_type_code line_type,
gcc.segment1||'.'||gcc.segment2||'.'||gcc.segment3||'.'||gcc.segment4||'.'||gcc.segment5||'.'||gcc.segment6||'.'||gcc.segment7 ACCOUNT,
al.accounted_dr Debit_CAD,
al.accounted_cr Credit_CAD,
al.currency_code curr_code,
al.entered_dr Entered_DR,
al.entered_cr Entered_CR,
al.currency_conversion_rate curr_conv_rate,
ah.accounting_date acc_date,
decode(ah.gl_transfer_flag,'Y','Yes','E', 'Error', 'No') GL_flag,
pov.segment1 Supplier_num,
pva.vendor_site_code Supplier_site,
apt.name tax_code,
nvl (ai.DOC_SEQUENCE_VALUE, apc.DOC_SEQUENCE_VALUE) doc_seq,
aid.po_number,
pta.SEGMENT1,
pat.task_number,
AID.Expenditure_Type,
ai.description doc_desc,
NVL(ai.INVOICE_TYPE_LOOKUP_CODE, apc.PAYMENT_METHOD_LOOKUP_CODE) doc_type,
ai.invoice_date,
ae.accounting_date
from ap_accounting_events_all ae
INNER JOIN ap_ae_headers_all ah ON ae.accounting_event_id=ah.accounting_event_id
INNER JOIN ap_ae_lines_all al ON ah.ae_header_id=al.ae_header_id
INNER JOIN gl_code_combinations gcc ON al.code_combination_id=gcc.code_combination_id
LEFT JOIN ap_invoices_all ai ON ai.invoice_id=ae.source_id and ae.source_table = 'AP_INVOICES'
LEFT JOIN ap_invoice_distributions_v aid ON al.source_id=aid.invoice_distribution_id and al.source_table='AP_INVOICE_DISTRIBUTIONS'
LEFT JOIN ap_tax_codes_all apt on apt.tax_id = aid.tax_code_id
LEFT JOIN pa_projects_all pta on aid.project_id = pta.project_id
LEFT JOIN pa_tasks pat on aid.project_id = pat.project_id
LEFT JOIN pa_expenditure_types pet on aid.expenditure_Type = pet.expenditure_type
LEFT JOIN ap_checks_all apc on apc.check_id = ae.source_id and ae.source_table='AP_CHECKS'
LEFT JOIN po_vendors pov on pov.vendor_id = nvl(ai.vendor_id, apc.vendor_id)
LEFT JOIN po_vendor_sites_all pva on pva.vendor_id = pov.vendor_id and pva.vendor_site_id = ai.vendor_site_id
So I added a new field in the select clause: decode(aida.pa_addition_flag,'Y','Yes','Z','Yes','No') PJ_Flag
And LEFT JOIN ap_invoice_distributions_all aida on aida.ACCOUNTING_EVENT_ID = ae.ACCOUNTING_EVENT_ID in the from clause
And I got the following error message:
SQL Error: ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table
01445. 00000 - "cannot select ROWID from, or sample, a join view without a key-preserved table"
There is no ROWID field in my query, so the problem is obviously nothing to do with the ROWID.
Then I googled and found the cause and the solution.
Real cause: Oracle has a limit of 1050 columns in any query that uses ANSI joins. If add all columns from all tables in FROM clause, the total number of columns is greater than 1050.
Solution:
Instead of using ‘LEFT JOIN ap_invoice_distributions_all aida on aida.ACCOUNTING_EVENT_ID = ae.ACCOUNTING_EVENT_ID’,
use ‘LEFT JOIN (select ACCOUNTING_EVENT_ID, PA_ADDITION_FLAG from ap_invoice_distributions_all) aida on aida.ACCOUNTING_EVENT_ID = ae.ACCOUNTING_EVENT_ID’
Even after the change, the same error popped up while executing the query, so I continue to shorten the columns in other joined tables, finally the below query works.
select
NVL(ai.invoice_num, APC.CHECK_NUMBER) doc_num,
pov.vendor_name Supplier,
al.ae_line_type_code line_type,
gcc.segment1||'.'||gcc.segment2||'.'||gcc.segment3||'.'||gcc.segment4||'.'||gcc.segment5||'.'||gcc.segment6||'.'||gcc.segment7 ACCOUNT,
al.accounted_dr Debit_CAD,
al.accounted_cr Credit_CAD,
al.currency_code curr_code,
al.entered_dr Entered_DR,
al.entered_cr Entered_CR,
al.currency_conversion_rate curr_conv_rate,
ah.accounting_date acc_date,
decode(ah.gl_transfer_flag,'Y','Yes','E', 'Error', 'No') GL_flag,
pov.segment1 Supplier_num,
pva.vendor_site_code Supplier_site,
apt.name tax_code,
nvl (ai.DOC_SEQUENCE_VALUE, apc.DOC_SEQUENCE_VALUE) doc_seq,
aid.po_number,
pta.SEGMENT1,
pat.task_number,
AID.Expenditure_Type,
ai.description doc_desc,
NVL(ai.INVOICE_TYPE_LOOKUP_CODE, apc.PAYMENT_METHOD_LOOKUP_CODE) doc_type,
ai.invoice_date,
ae.accounting_date,
decode(aida.PA_ADDITION_FLAG,'Y','Yes','Z', 'Yes', 'No') PJ_flag
from ap_accounting_events_all ae
INNER JOIN ap_ae_headers_all ah ON ae.accounting_event_id=ah.accounting_event_id
INNER JOIN ap_ae_lines_all al ON ah.ae_header_id=al.ae_header_id
INNER JOIN gl_code_combinations gcc ON al.code_combination_id=gcc.code_combination_id
LEFT JOIN ap_invoices_all ai ON ai.invoice_id=ae.source_id and ae.source_table = 'AP_INVOICES'
LEFT JOIN (select invoice_distribution_id, po_number, Expenditure_Type, tax_code_id, project_id from ap_invoice_distributions_v) aid ON al.source_id=aid.invoice_distribution_id and al.source_table='AP_INVOICE_DISTRIBUTIONS'
LEFT JOIN ap_tax_codes_all apt on apt.tax_id = aid.tax_code_id
LEFT JOIN pa_projects_all pta on aid.project_id = pta.project_id
LEFT JOIN pa_tasks pat on aid.project_id = pat.project_id
LEFT JOIN pa_expenditure_types pet on aid.expenditure_Type = pet.expenditure_type
LEFT JOIN ap_checks_all apc on apc.check_id = ae.source_id and ae.source_table='AP_CHECKS'
LEFT JOIN (select vendor_id, segment1,vendor_name from po_vendors) pov on pov.vendor_id = nvl(ai.vendor_id, apc.vendor_id)
LEFT JOIN (select vendor_id, vendor_site_id, vendor_site_code from po_vendor_sites_all) pva on pva.vendor_id = pov.vendor_id and pva.vendor_site_id = ai.vendor_site_id
LEFT JOIN (select ACCOUNTING_EVENT_ID, PA_ADDITION_FLAG from ap_invoice_distributions_all) aida on aida.ACCOUNTING_EVENT_ID = ae.ACCOUNTING_EVENT_ID
--where ai.invoice_num = '100034-1'
--where AE.ORG_ID = :P_ORG_ID
--and ae.accounting_date between :P_DATE_FROM and nvl(:P_DATE_TO, SYSDATE)
Thanks Andrew Channels for his very helpful post!
