Anonymous | Login
Project:
RSS
  
News | My View | View Issues | Roadmap | Summary

View Issue DetailsJump to Notes ] Issue History ] Print ]
ID
0008191
TypeCategorySeverityReproducibilityDate SubmittedLast Update
defect[Openbravo ERP] 07. Sales managementmajoralways2009-03-18 11:272009-04-22 00:01
ReporterthirumalaiView Statuspublic 
Assigned Toshuehner 
PrioritynormalResolutionfixedFixed in Version2.50
StatusclosedFix in branchpiFixed in SCM revision095fc4597cde
ProjectionnoneETAnoneTarget Version
OSLinux 32 bitDatabaseOracleJava version1.6.0
OS VersionFedora 9Database version10 GAnt version1.6.0
Product Version2.40SCM revision 
Review Assigned To
Web browser
ModulesCore
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary

0008191: 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
TagsNo tags attached.
Attached Filespng file icon CommissionPaymentERROR.PNG [^] (145,327 bytes) 2009-03-18 11:27

- Relationships Relation Graph ] Dependency Graph ]
depends on backport 0008240 closedshuehner Cannot able to view grid in Commission payment 

-  Notes
(0014769)
thirumalai (reporter)
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 (reporter)
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 (administrator)
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 (reporter)
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 (administrator)
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 (reporter)
2009-03-23 18:50

What can i do to resolve this error? I can't able to get your point.
(0014875)
hgbot (developer)
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 (reporter)
2009-04-07 09:49

Tested - Working fine

- Issue History
Date Modified Username Field Change
2009-03-18 11:27 thirumalai New Issue
2009-03-18 11:27 thirumalai Assigned To => rafaroda
2009-03-18 11:27 thirumalai File Added: CommissionPaymentERROR.PNG
2009-03-18 11:30 thirumalai Note Added: 0014769
2009-03-18 11:36 thirumalai Note Added: 0014770
2009-03-18 11:58 shuehner Assigned To rafaroda => shuehner
2009-03-23 15:09 shuehner Note Added: 0014863
2009-03-23 15:09 shuehner Status new => feedback
2009-03-23 16:54 thirumalai Note Added: 0014868
2009-03-23 18:13 shuehner Note Added: 0014871
2009-03-23 18:13 shuehner Status feedback => new
2009-03-23 18:35 shuehner Status new => scheduled
2009-03-23 18:35 shuehner fix_in_branch => pi
2009-03-23 18:50 thirumalai Note Added: 0014873
2009-03-23 19:25 hgbot Checkin
2009-03-23 19:25 hgbot Note Added: 0014875
2009-03-23 19:25 hgbot Status scheduled => resolved
2009-03-23 19:25 hgbot Resolution open => fixed
2009-03-23 19:25 hgbot Fixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/095fc4597cde7ae6abd67f10e6f9b96c30bdc431 [^]
2009-04-07 09:49 thirumalai Note Added: 0015241
2009-04-21 10:53 psarobe Status resolved => closed
2009-04-22 00:01 anonymous sf_bug_id 0 => 2778084


Copyright © 2000 - 2009 MantisBT Group
Powered by Mantis Bugtracker