Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0008191Openbravo ERP07. Sales managementpublic2009-03-18 11:272009-04-22 00:01
Reporterthirumalai 
Assigned Toshuehner 
PrioritynormalSeveritymajorReproducibilityalways
StatusclosedResolutionfixed 
PlatformOS20OS VersionFedora 9
Product Version2.40 
Target VersionFixed in Version2.50 
Merge Request Status
Review Assigned To
OBNetwork customer
Web browser
ModulesCore
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0008191: Cannot able to view grid in Commission payment
Description
Sales Management || Transactions || Commission Payment || Header >> Amounts >> Details

Grid view says -->ORA-00918: column ambiguously defined

Note:No change was done by us.
Steps To Reproduce1.Sales management >> Setup >> Sales commission
2.Create a header and process Generate commission.
3.It shows commission payment document no.
4.Retrieve that document no in Sales Management >> Transactions >> Commission Payment
5.Go to Header >> Amounts >> Details

Grid view says -->ORA-00918: column ambiguously defined
Proposed Solution
Additional Information
TagsNo tags attached.
Relationships
depends on backport 0008240 closed shuehner Cannot able to view grid in Commission payment 
Attached Filespng CommissionPaymentERROR.PNG (145,327) 2009-03-18 11:27
https://issues.openbravo.com/file_download.php?file_id=1015&type=bug
png
Issue History
Date ModifiedUsernameFieldChange
2009-03-18 11:27thirumalaiNew Issue
2009-03-18 11:27thirumalaiAssigned To => rafaroda
2009-03-18 11:27thirumalaiFile Added: CommissionPaymentERROR.PNG
2009-03-18 11:30thirumalaiNote Added: 0014769
2009-03-18 11:36thirumalaiNote Added: 0014770
2009-03-18 11:58shuehnerAssigned Torafaroda => shuehner
2009-03-23 15:09shuehnerNote Added: 0014863
2009-03-23 15:09shuehnerStatusnew => feedback
2009-03-23 16:54thirumalaiNote Added: 0014868
2009-03-23 18:13shuehnerNote Added: 0014871
2009-03-23 18:13shuehnerStatusfeedback => new
2009-03-23 18:35shuehnerStatusnew => scheduled
2009-03-23 18:35shuehnerfix_in_branch => pi
2009-03-23 18:50thirumalaiNote Added: 0014873
2009-03-23 19:25hgbotCheckin
2009-03-23 19:25hgbotNote Added: 0014875
2009-03-23 19:25hgbotStatusscheduled => resolved
2009-03-23 19:25hgbotResolutionopen => fixed
2009-03-23 19:25hgbotFixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/095fc4597cde7ae6abd67f10e6f9b96c30bdc431 [^]
2009-04-07 09:49thirumalaiNote Added: 0015241
2009-04-21 10:53psarobeStatusresolved => closed
2009-04-22 00:01anonymoussf_bug_id0 => 2778084

Notes
(0014769)
thirumalai   
2009-03-18 11:30   
Error message n LOG

15:51:13 [http-8080-Processor24] ERROR org.openbravo.erpCommon.utility.ExecuteQuery - SQL error in query: SELECT COUNT(*) AS TOTAL FROM C_CommissionDetail
left join (SELECT C_CommissionAmt_ID, C_CommissionRun_ID, C_CommissionLine_ID FROM C_CommissionAmt) td0 on C_CommissionDetail.C_CommissionAmt_ID = td0.C_CommissionAmt_ID
 
left join (SELECT C_CommissionRun_ID, DocumentNo FROM C_CommissionRun) td1 on td0.C_CommissionRun_ID = td1.C_CommissionRun_ID
 
left join (SELECT C_CommissionLine_ID, C_Commission_ID, Line FROM C_CommissionLine) td2 on td0.C_CommissionLine_ID = td2.C_CommissionLine_ID
 
left join (SELECT C_Commission_ID, Name FROM C_Commission) td3 on td2.C_Commission_ID = td3.C_Commission_ID
 
left join (SELECT C_OrderLine_ID, C_Order_ID, Line, LineNetAmt, C_OrderLine_ID FROM C_OrderLine) td4 on C_CommissionDetail.C_OrderLine_ID = td4.C_OrderLine_ID
 
left join (SELECT C_Order_ID, DocumentNo, DateOrdered, GrandTotal FROM C_Order) td5 on td4.C_Order_ID = td5.C_Order_ID
 
left join (SELECT C_InvoiceLine_ID, C_Invoice_ID, Line, LineNetAmt FROM C_InvoiceLine) td6 on C_CommissionDetail.C_InvoiceLine_ID = td6.C_InvoiceLine_ID
 
left join (SELECT C_Invoice_ID, DocumentNo, GrandTotal, DateInvoiced FROM C_Invoice) td7 on td6.C_Invoice_ID = td7.C_Invoice_ID
 
left join (SELECT C_Currency_ID, ISO_Code FROM C_Currency) td8 on C_CommissionDetail.C_Currency_ID = td8.C_Currency_ID
 
WHERE C_CommissionDetail.AD_Client_ID IN (0,1000000)
AND C_CommissionDetail.AD_Org_ID IN (1000052,0,1000053,1000054,1000055,1000056,1000045,1000029,1000006,1000008,1000044,1000059,1000017,1000009,1000013,1000023,1000030,1000031,1000002,1000046,1000004,1000012,1000019,1000022,1000047,1000048,1000025,1000007,1000020,1000032,1000049,1000005,1000018,1000028,1000027,1000026,1000010,1000011,1000014,1000015,1000016,1000021,1000024,1000003,1000000,1000050)
AND C_CommissionDetail.C_CommissionAmt_ID = ?
Exception:java.sql.SQLException: ORA-00918: column ambiguously defined
(0014770)
thirumalai   
2009-03-18 11:36   
15:51:13 [http-8080-Processor24] ERROR org.openbravo.erpCommon.utility.ExecuteQuery - SQL error in query: SELECT COALESCE(TO_CHAR(C_CommissionDetail.C_CommissionDetail_ID), '') AS C_CommissionDetail_ID , C_CommissionDetail.C_CommissionAmt_ID AS C_CommissionAmt_ID , COALESCE(TO_CHAR(td1.DocumentNo), '') || ' - ' || COALESCE(TO_CHAR(td3.Name), '') || ' - ' || CAST(td2.Line AS INTEGER) AS C_CommissionAmt_ID_R , COALESCE(TO_CHAR(C_CommissionDetail.Reference), '') AS Reference , C_CommissionDetail.C_OrderLine_ID AS C_OrderLine_ID , COALESCE(TO_CHAR(td5.DocumentNo), '') || ' - ' || TO_CHAR(td5.DateOrdered, 'DD-MM-YYYY') || ' - ' || TO_NUMBER(td5.GrandTotal) || ' - ' || CAST(td4.Line AS INTEGER) || ' - ' || TO_NUMBER(td4.LineNetAmt) || ' - ' || COALESCE(TO_CHAR(td4.C_OrderLine_ID), '') AS C_OrderLine_ID_R , C_CommissionDetail.C_InvoiceLine_ID AS C_InvoiceLine_ID , COALESCE(TO_CHAR(td7.DocumentNo), '') || ' - ' || TO_NUMBER(td7.GrandTotal) || ' - ' || TO_CHAR(td7.DateInvoiced, 'DD-MM-YYYY') || ' - ' || CAST(td6.Line AS INTEGER) || ' - ' || TO_NUMBER(td6.LineNetAmt) AS C_InvoiceLine_ID_R , COALESCE(C_CommissionDetail.IsActive, 'N') AS IsActive , COALESCE(TO_CHAR(C_CommissionDetail.Info), '') AS Info , TO_NUMBER(C_CommissionDetail.ActualAmt) AS ActualAmt , C_CommissionDetail.C_Currency_ID AS C_Currency_ID , COALESCE(TO_CHAR(td8.ISO_Code), '') AS C_Currency_ID_R , TO_NUMBER(C_CommissionDetail.ConvertedAmt) AS ConvertedAmt , TO_NUMBER(C_CommissionDetail.ActualQty) AS ActualQty
FROM C_CommissionDetail
left join (SELECT C_CommissionAmt_ID, C_CommissionRun_ID, C_CommissionLine_ID FROM C_CommissionAmt) td0 on C_CommissionDetail.C_CommissionAmt_ID = td0.C_CommissionAmt_ID
 
left join (SELECT C_CommissionRun_ID, DocumentNo FROM C_CommissionRun) td1 on td0.C_CommissionRun_ID = td1.C_CommissionRun_ID
 
left join (SELECT C_CommissionLine_ID, C_Commission_ID, Line FROM C_CommissionLine) td2 on td0.C_CommissionLine_ID = td2.C_CommissionLine_ID
 
left join (SELECT C_Commission_ID, Name FROM C_Commission) td3 on td2.C_Commission_ID = td3.C_Commission_ID
 
left join (SELECT C_OrderLine_ID, C_Order_ID, Line, LineNetAmt, C_OrderLine_ID FROM C_OrderLine) td4 on C_CommissionDetail.C_OrderLine_ID = td4.C_OrderLine_ID
 
left join (SELECT C_Order_ID, DocumentNo, DateOrdered, GrandTotal FROM C_Order) td5 on td4.C_Order_ID = td5.C_Order_ID
 
left join (SELECT C_InvoiceLine_ID, C_Invoice_ID, Line, LineNetAmt FROM C_InvoiceLine) td6 on C_CommissionDetail.C_InvoiceLine_ID = td6.C_InvoiceLine_ID
 
left join (SELECT C_Invoice_ID, DocumentNo, GrandTotal, DateInvoiced FROM C_Invoice) td7 on td6.C_Invoice_ID = td7.C_Invoice_ID
 
left join (SELECT C_Currency_ID, ISO_Code FROM C_Currency) td8 on C_CommissionDetail.C_Currency_ID = td8.C_Currency_ID
 
WHERE (C_CommissionDetail.C_CommissionDetail_ID) IN (SELECT C_CommissionDetail_ID
 FROM (SELECT ROWNUM AS rn1, A.* FROM (
 SELECT C_CommissionDetail.C_CommissionDetail_ID
 FROM C_CommissionDetail
 WHERE C_CommissionDetail.AD_Client_ID IN (0,1000000)
 AND C_CommissionDetail.AD_Org_ID IN (1000052,0,1000053,1000054,1000055,1000056,1000045,1000029,1000006,1000008,1000044,1000059,1000017,1000009,1000013,1000023,1000030,1000031,1000002,1000046,1000004,1000012,1000019,1000022,1000047,1000048,1000025,1000007,1000020,1000032,1000049,1000005,1000018,1000028,1000027,1000026,1000010,1000011,1000014,1000015,1000016,1000021,1000024,1000003,1000000,1000050)
 AND C_CommissionDetail.C_CommissionAmt_ID = ?
ORDER BY C_CommissionDetail.Reference ASC, C_CommissionDetail.Reference ASC, C_CommissionDetail.C_CommissionDetail_ID)
A)
  WHERE rn1 BETWEEN ? AND ?)
ORDER BY C_CommissionDetail.Reference ASC, C_CommissionDetail.Reference ASC, C_CommissionDetail.C_CommissionDetail_IDException:java.sql.SQLException: ORA-00918: column ambiguously defined

15:51:13 [http-8080-Processor24] ERROR org.openbravo.erpCommon.utility.DataGrid - Error in print page data: javax.servlet.ServletException: @CODE=918@ORA-00918: column ambiguously defined
(0014863)
shuehner   
2009-03-23 15:09   
Hello thirumalai,

with unmodified 2.40 i was not able to reproduce the problem. However if the some change is done to the dictionary an problem like this could happen.

Question: Did you modify the identifier columns used for the table C_OrderLine (Sales Order Line)? If so, did you include the column C_OrderLine_ID into the identifiers?

This can be checked by i.e. executing the following sql-query:

 SELECT c.name, c.columnname, c. isidentifier from ad_column c, ad_table t where c.ad_table_id = t.ad_table_id and t.name = 'C_OrderLine' and c.columnname = 'C_OrderLine_ID';
(0014868)
thirumalai   
2009-03-23 16:54   
I checked, ya C_orderline_id is in Identifier

Other identifiers for C_orderline is C_order_id,line,linenetamt.
(0014871)
shuehner   
2009-03-23 18:13   
Issue will happen in general for all references of type TableDir when the identifier columns for the target table do include the target table primary key. (i.e. identifier for C_OrderLine does include C_OrderLine_ID).
(0014873)
thirumalai   
2009-03-23 18:50   
What can i do to resolve this error? I can't able to get your point.
(0014875)
hgbot   
2009-03-23 19:25   
Repository: erp/devel/pi
Changeset: 095fc4597cde7ae6abd67f10e6f9b96c30bdc431
Author: Stefan Hühner <stefan.huehner <at> openbravo.com>
Date: Mon Mar 23 19:25:30 2009 +0100
URL: http://code.openbravo.com/erp/devel/pi/rev/095fc4597cde7ae6abd67f10e6f9b96c30bdc431 [^]

Fixed 8191. Fix SQL generation for grid view with TableDir and target table pk.isidentifier='Y'

---
M src/org/openbravo/erpCommon/utility/TableSQLData.java
---
(0015241)
thirumalai   
2009-04-07 09:49   
Tested - Working fine