Menu ▾ ▴

#1691 Aging is not working - checking account date flag

Core
closed-fixed
nobody
Other Functionality (68)
9
2014-08-18
2009-01-19
Carlos Ruiz
No

Version: >= 353a

This must be related with the invoiceopentodate pl/pgsql function contributed to replace the pl/java function.

As reported by Susanne Calderon here:
Postgres AGING
https://sourceforge.net/forum/forum.php?thread_id=2867860&forum_id=610548

Test Case in postgresql:
- Start "Aging" Report
- Check the "Account Date" flag
Issue the report.

In the console you'll see:
===========> Aging.doIt: SELECT bp.C_BP_Group_ID, oi.C_BPartner_ID,oi.C_Invoice_ID,oi.C_InvoicePaySchedule_ID, oi.C_Currency_ID, oi.IsSOTrx, oi.DateInvoiced, oi.NetDays,oi.DueDate,oi.DaysDue, oi.GrandTotal, invoicePaidToDate(oi.C_Invoice_ID, oi.C_Currency_ID, 1,TO_DATE('2009-01-19','YYYY-MM-DD')) AS PaidAmt, invoiceOpenToDate(oi.C_Invoice_ID,oi.C_InvoicePaySchedule_ID,TO_DATE('2009-01-19','YYYY-MM-DD')) AS OpenAmt ,oi.C_Activity_ID,oi.C_Campaign_ID,oi.C_Project_ID FROM RV_OpenItemToDate oi INNER JOIN C_BPartner bp ON (oi.C_BPartner_ID=bp.C_BPartner_ID) WHERE oi.ISSoTrx='Y'AND invoiceOpenToDate(oi.C_Invoice_ID,oi.C_InvoicePaySchedule_ID,TO_DATE('2009-01-19','YYYY-MM-DD')) <> 0 AND oi.AD_Client_ID IN(0,11) AND oi.AD_Org_ID IN(50005,50007,50002,50000,50004,0,50006,11,50001,12) AND bp.C_BPartner_ID NOT IN ( SELECT Record_ID FROM AD_Private_Access WHERE AD_Table_ID = 291 AND AD_User_ID <> 100 AND IsActive = 'Y' ) ORDER BY oi.C_BPartner_ID, oi.C_Currency_ID, oi.C_Invoice_ID [12]
org.postgresql.util.PSQLException: ERROR: no existe la función invoiceopentodate(numeric, numeric, timestamp with time zone); State=42883; ErrorCode=0

The offending postgres query is:
SELECT bp.C_BP_Group_ID, oi.C_BPartner_ID,oi.C_Invoice_ID,oi.C_InvoicePaySchedule_ID, oi.C_Currency_ID, oi.IsSOTrx, oi.DateInvoiced, oi.NetDays,oi.DueDate,oi.DaysDue, oi.GrandTotal, invoicePaidToDate(oi.C_Invoice_ID, oi.C_Currency_ID, 1,TO_TIMESTAMP('2009-01-19','YYYY-MM-DD')) AS PaidAmt, invoiceOpenToDate(oi.C_Invoice_ID,oi.C_InvoicePaySchedule_ID,TO_TIMESTAMP('2009-01-19','YYYY-MM-DD')) AS OpenAmt ,oi.C_Activity_ID,oi.C_Campaign_ID,oi.C_Project_ID FROM RV_OpenItemToDate oi INNER JOIN C_BPartner bp ON (oi.C_BPartner_ID=bp.C_BPartner_ID) WHERE oi.ISSoTrx='Y'AND invoiceOpenToDate(oi.C_Invoice_ID,oi.C_InvoicePaySchedule_ID,TO_TIMESTAMP('2009-01-19','YYYY-MM-DD')) <> 0 AND oi.AD_Client_ID IN(0,11) AND oi.AD_Org_ID IN(50005,50007,50002,50000,50004,0,50006,11,50001,12) AND bp.C_BPartner_ID NOT IN ( SELECT Record_ID FROM AD_Private_Access WHERE AD_Table_ID = 291 AND AD_User_ID <> 100 AND IsActive = 'Y' ) ORDER BY oi.C_BPartner_ID, oi.C_Currency_ID, oi.C_Invoice_ID

Regards,

Carlos Ruiz

Discussion

  • Mario Calderon

    Mario Calderon - 2009-01-19

    SQL invoiceopentodate

     
    invoiceopentodate.sql

  • Mario Calderon

    Mario Calderon - 2009-01-19

    Hello,
    after trying different possibilities, I ended up defining another function "invoiceopentodate", with a different signature:
    invoiceopentodate(p_c_invoice_id numeric, p_c_invoicepayschedule_id numeric, p_dateacct timestamp with time zone).

    The parameter "p_dateacct" is a "timestamp with time zone" instead of a "date".
    In the Function body, the parameter "p_dateacct" is replaced by "cast(p_DateAcct as date)".
    This way I managed to run the Aging (and other) processes without errors.

    It seems that the internal function to_date() doesn't return a date even if it is clearly defined as such in pg_catalog.

    Strange.

    I don't know if we should have the same function with 2 signatures. In case of any use, I attach the new function to this post.

    Best regards,
    Mario Calderon

    File Added: invoiceopentodate.sql

     

  • Carlos Ruiz

    Carlos Ruiz - 2009-01-30
    • summary: Aging is not working in postgres --> Aging is not working - checking account date flag
     

  • Carlos Ruiz

    Carlos Ruiz - 2009-01-30

    It seems this is also broken with oracle SE (sqlj version).

    In oracle is throwing (tested in a 352 - need to test with latest qa branch)

    SQLException: SQLState(99999) vendor code(29531)
    ===========> Aging.doIt: SELECT bp.C_BP_Group_ID, oi.C_BPartner_ID,oi.C_Invoice_ID,oi.C_InvoicePaySchedule_ID, oi.C_Currency_ID, oi.IsSOTrx, oi.DateInvoiced, oi.NetDays,oi.DueDate,oi.DaysDue, oi.GrandTotal, invoicePaidToDate(oi.C_Invoice_ID, oi.C_Currency_ID, 1,TO_DATE('2009-01-29','YYYY-MM-DD')) AS PaidAmt, invoiceOpenToDate(oi.C_Invoice_ID,oi.C_InvoicePaySchedule_ID,TO_DATE('2009-01-29','YYYY-MM-DD')) AS OpenAmt ,oi.C_Activity_ID,oi.C_Campaign_ID,oi.C_Project_ID FROM RV_OpenItemToDate oi INNER JOIN C_BPartner bp ON (oi.C_BPartner_ID=bp.C_BPartner_ID) WHERE oi.ISSoTrx='Y'AND invoiceOpenToDate(oi.C_Invoice_ID,oi.C_InvoicePaySchedule_ID,TO_DATE('2009-01-29','YYYY-MM-DD')) <> 0 AND oi.AD_Client_ID IN(1000003,0) AND oi.AD_Org_ID IN(1000003,1000004,1000006,0,1000005) AND bp.C_BPartner_ID NOT IN ( SELECT Record_ID FROM AD_Private_Access WHERE AD_Table_ID = 291 AND AD_User_ID <> 100 AND IsActive = 'Y' ) ORDER BY oi.C_BPartner_ID, oi.C_Currency_ID, oi.C_Invoice_ID [40]
    java.sql.SQLException: ORA-29531: no method paid in class org/compiere/sqlj/Invoice
    ; State=99999; ErrorCode=29531
    at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)
    at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:110)
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:171)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)
    at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1030)
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:194)
    at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:785)
    at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:860)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1186)
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3381)
    at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3425)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1202)
    at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76)
    at org.compiere.util.CPreparedStatement.executeQuery(CPreparedStatement.java:122)
    at org.compiere.process.Aging.doIt(Aging.java:150)
    at org.compiere.process.SvrProcess.process(SvrProcess.java:134)
    at org.compiere.process.SvrProcess.startProcess(SvrProcess.java:92)
    at org.adempiere.util.ProcessUtil.startJavaProcess(ProcessUtil.java:116)
    at org.compiere.apps.ProcessCtl.startProcess(ProcessCtl.java:654)
    at org.compiere.apps.ProcessCtl.run(ProcessCtl.java:397)

    -----------> DataEngine.loadPrintData: NO Rows - ms=57 [40]

    Regards,

    Carlos Ruiz

     

  • Teo Sarca

    Teo Sarca - 2009-02-10

    I found an SQL typo error in Aging process.
    Fixed in revision http://adempiere.svn.sourceforge.net/viewvc/adempiere?view=rev&revision=8406 .

    Are there more issues regarding Aging ?

    Best regards,
    Teo Sarca - www.arhipac.ro

     

  • Carlos Ruiz

    Carlos Ruiz - 2009-02-11

    Yes Teo, pl/java (oracle SE) and pl/pgsql aging are broken - did you test those?

    Regards,

    Carlos Ruiz

     

  • Tony Snook

    Tony Snook - 2009-08-13
    • status: open --> pending-fixed
     

  • SourceForge Robot

    SourceForge Robot - 2009-08-28

    This Tracker item was closed automatically by the system. It was
    previously set to a Pending status, and the original submitter
    did not respond within 14 days (the time period specified by
    the administrator of this Tracker).

     

  • SourceForge Robot

    SourceForge Robot - 2009-08-28
    • status: pending-fixed --> closed-fixed
     

Log in to post a comment.