Material Receipt - Create Lines Fails

[vivek gupta]

Dear Members,

I have a problem that I am listing below:
a) I have created the necessary Product, Warehouse and PO
b) When I do Requisitions-to-invoice -> PO - > Material Receipt
c) On the Material Receipt Window - the "Create Line From" button throws up the window for PO selection
d) On PO Selection i.e. product selection - Receipt Line is not populated and I get the following error:

  - M_InOutLine_ID=1000022, SQL=SELECT AD_Client_ID,AD_Org_ID,AD_OrgTrx_ID,C_Activity_ID,C_Campaign_ID,C_Charge_ID,C_OrderLine_ID,C_Project_ID,C_ProjectPhase_ID,C_ProjectTask_ID,C_UOM_ID,ConfirmedQty,Created,CreatedBy,Description,IsActive,IsDescription,IsInvoiced,Line,M_AttributeSetInstance_ID,M_InOut_ID,M_InOutLine_ID,M_Locator_ID,M_Product_ID,M_RMALine_ID,MovementQty,PickedQty,Processed,QtyEntered,Ref_InOutLine_ID,ReversalLine_ID,ScrappedQty,TargetQty,Updated,UpdatedBy,User1_ID,User2_ID,SELECT Value FROM M_Product p WHERE p.M_Product_ID=M_InOutLine.M_Product_ID AS Value FROM M_InOutLine WHERE M_InOutLine_ID=?

org.postgresql.util.PSQLException: ERROR: syntax error at or near "SELECT"
  Position: 463; State=42601; ErrorCode=0
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2062)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1795)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:479)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:367)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:271)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.compiere.db.StatementProxy.invoke(StatementProxy.java:100)
at $Proxy0.executeQuery(Unknown Source)
at org.compiere.model.PO.load(PO.java:1339)
at org.compiere.model.PO.saveNew(PO.java:2735)
at org.compiere.model.PO.save(PO.java:2081)
at org.compiere.grid.CreateFromShipment.save(CreateFromShipment.java:662)
at org.compiere.grid.VCreateFromDialog.save(VCreateFromDialog.java:149)
at org.compiere.grid.VCreateFromDialog$1.run(VCreateFromDialog.java:109)
at org.compiere.util.Trx.run(Trx.java:521)
at org.compiere.util.Trx.run(Trx.java:489)
at org.compiere.grid.VCreateFromDialog.actionPerformed(VCreateFromDialog.java:105)
at org.compiere.apps.AppsAction.actionPerformed(AppsAction.java:286)

---

I have not touched the code and I am using the Adempiere 360 version as it is. 

Could somebody help me on the above error. 

I fail to understand the following:
a) In the Postgres backend - I query the above and see no matching M_InOutLine_ID=1000022
How does adempiere generate M_InOutLine_ID=1000022 when the Select (as described above) fails ?
b) I have a similar setup working on another computer and it works - on the pg_dmp I am trying it fails
How do I debug or what all should I look at to crack this problems, which is blocking me since past 1 month ?

Any suggestions or Ideas would be welcome as I am nearing my product delivery

Thanks in advance
Vivek

[Daniel Tamm]

WIthout looking too much into it, this is what I suspect:

The syntax error is because the
"SELECT Value FROM M_Product
p WHERE p.M_Product_ID=M_InOutLine.M_Product_ID AS Value" part right before User2_ID should be enclosed in paranthesis.

Have you perhaps added a virtual column in M_InOutLine in AD and forgot to enclose it in paranthesis?

The SQL should be:

SELECT
AD_Client_ID,AD_Org_ID,AD_OrgTrx_ID,C_Activity_ID,C_Campaign_ID,C_Charge_ID,C_Or
derLine_ID,C_Project_ID,C_ProjectPhase_ID,C_ProjectTask_ID,C_UOM_ID,ConfirmedQty
,Created,CreatedBy,Description,IsActive,IsDescription,IsInvoiced,Line,M_Attribut
eSetInstance_ID,M_InOut_ID,M_InOutLine_ID,M_Locator_ID,M_Product_ID,M_RMALine_ID
,MovementQty,PickedQty,Processed,QtyEntered,Ref_InOutLine_ID,ReversalLine_ID,Scr
appedQty,TargetQty,Updated,UpdatedBy,User1_ID,User2_ID,(SELECT Value FROM M_Product
p WHERE p.M_Product_ID=M_InOutLine.M_Product_ID) AS Value FROM M_InOutLine WHERE
M_InOutLine_ID=?

/Daniel T

[vivek gupta]

Thanks
Thanks a lot Daniel

vivek