The "Invoice sequence" issue

[Xavier Olivella]

As told in the other message my problem is that I want the "invoice
sequence" to be set as "Restart on Fiscal Year (no gaps, per org, reset
to 1 each year)" but it doesn't work. The only way it works is with
default option.
 
I am getting really mad about that, I've been testing dozens of things
and searching in docs for days and I cannot make it run.
I really could not find any "in-deep" documentation about setting the
company accounting, so it's impossible for me to solve the problem.
 
When I make an invoice it works, but on the following the program gives
it the same invoice number so it crashes with the error below.
 
Could anyone help me?
 
 
 
***************************************
The Following Errors Occurred:  
 
ERROR: Could not complete the Sub-method used by quickShip methods to
create a shipment
[file:/usr/local/test01/applications/product/script/org/ofbiz/shipment/shipment/ShipmentServices.xml#createShipmentForFacilityAndShipGroup] process [problem invoking the [updateShipment] service with the map named [packedContext] containing [[shipmentId=10012, statusId=SHIPMENT_PACKED, userLogin=[GenericEntity:UserLogin][createdStamp,2007-05-31 13:21:26.0(java.sql.Timestamp)][createdTxStamp,2007-05-31 13:21:26.0(java.sql.Timestamp)][currentPassword,a059d9ac5e0b9c8067ae0cebb9a5bcba1636c386(java.lang.String)][disabledDateTime,null()][enabled,Y(java.lang.String)][hasLoggedOut,N(java.lang.String)][isSystem,null()][lastCurrencyUom,null()][lastLocale,ca(java.lang.String)][lastUpdatedStamp,2007-07-04 18:25:10.0(java.sql.Timestamp)][lastUpdatedTxStamp,2007-07-04 18:25:10.0(java.sql.Timestamp)][partyId,X.Olivella(java.lang.String)][passwordHint,null()][successiveFailedLogins,0(java.lang.Long)][userLoginId,x.olivella(java.lang.String)], locale=ca]]: Problems with the transaction. (Exception while inserting the following entity: [GenericEntity:Invoice][billingAccountId,null()][createdStamp,2007-07-04 19:59:06.653(java.sql.Timestamp)][createdTxStamp,2007-07-04 19:59:04.314(java.sql.Timestamp)][currencyUomId,EUR(java.lang.String)][dueDate,null()][invoiceDate,2007-07-04 19:59:06.511(java.sql.Timestamp)][invoiceId,2007-1(java.lang.String)][invoiceTypeId,SALES_INVOICE(java.lang.String)][lastUpdatedStamp,2007-07-04 19:59:06.653(java.sql.Timestamp)][lastUpdatedTxStamp,2007-07-04 19:59:04.314(java.sql.Timestamp)][partyId,10001(java.lang.String)][partyIdFrom,Company(java.lang.String)][statusId,INVOICE_IN_PROCESS(java.lang.String)] (while inserting: [GenericEntity:Invoice][billingAccountId,null()][createdStamp,2007-07-04 19:59:06.653(java.sql.Timestamp)][createdTxStamp,2007-07-04 19:59:04.314(java.sql.Timestamp)][currencyUomId,EUR(java.lang.String)][dueDate,null()][invoiceDate,2007-07-04 19:59:06.511(java.sql.Timestamp)][invoiceId,2007-1(java.lang.String)][invoiceTypeId,SALES_INVOICE(java.lang.String)][lastUpdatedStamp,2007-07-04 19:59:06.653(java.sql.Timestamp)][lastUpdatedTxStamp,2007-07-04 19:59:04.314(java.sql.Timestamp)][partyId,10001(java.lang.String)][partyIdFrom,Company(java.lang.String)][statusId,INVOICE_IN_PROCESS(java.lang.String)] (SQL Exception while executing the following:INSERT INTO INVOICE (INVOICE_ID, INVOICE_TYPE_ID, PARTY_ID_FROM, PARTY_ID, ROLE_TYPE_ID, STATUS_ID, BILLING_ACCOUNT_ID, CONTACT_MECH_ID, INVOICE_DATE, DUE_DATE, PAID_DATE, INVOICE_MESSAGE, REFERENCE_NUMBER, DESCRIPTION, CURRENCY_UOM_ID, LAST_UPDATED_STAMP, LAST_UPDATED_TX_STAMP, CREATED_STAMP, CREATED_TX_STAMP) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) (Duplicate key or integrity constraint violation message from server: "Duplicate entry '2007-1' for key 1"))))]

[David E Jones]

If there's a bug no documentation will fix it, a human will have to go in, reproduce the bug, find the problem, and fix the code.

You can file a Jira issue to help keep track of it, but that won't necessarily get resources allocated to getting it fixed. Someone with an interest in the feature will actually have to do it, or hire someone else to do it.

-David


Xavier Olivella wrote:

> As told in the other message my problem is that I want the "invoice
> sequence" to be set as "Restart on Fiscal Year (no gaps, per org, reset
> to 1 each year)" but it doesn't work. The only way it works is with
> default option.
>  
> I am getting really mad about that, I've been testing dozens of things
> and searching in docs for days and I cannot make it run.
> I really could not find any "in-deep" documentation about setting the
> company accounting, so it's impossible for me to solve the problem.
>  
> When I make an invoice it works, but on the following the program gives
> it the same invoice number so it crashes with the error below.
>  
> Could anyone help me?
>  
>  
>  
> ***************************************
> The Following Errors Occurred:  
>  
> ERROR: Could not complete the Sub-method used by quickShip methods to
> create a shipment
> [file:/usr/local/test01/applications/product/script/org/ofbiz/shipment/shipment/ShipmentServices.xml#createShipmentForFacilityAndShipGroup] process [problem invoking the [updateShipment] service with the map named [packedContext] containing [[shipmentId=10012, statusId=SHIPMENT_PACKED, userLogin=[GenericEntity:UserLogin][createdStamp,2007-05-31 13:21:26.0(java.sql.Timestamp)][createdTxStamp,2007-05-31 13:21:26.0(java.sql.Timestamp)][currentPassword,a059d9ac5e0b9c8067ae0cebb9a5bcba1636c386(java.lang.String)][disabledDateTime,null()][enabled,Y(java.lang.String)][hasLoggedOut,N(java.lang.String)][isSystem,null()][lastCurrencyUom,null()][lastLocale,ca(java.lang.String)][lastUpdatedStamp,2007-07-04 18:25:10.0(java.sql.Timestamp)][lastUpdatedTxStamp,2007-07-04 18:25:10.0(java.sql.Timestamp)][partyId,X.Olivella(java.lang.String)][passwordHint,null()][successiveFailedLogins,0(java.lang.Long)][userLoginId,x.olivella(java.lang.String)], locale=ca]]: Problems with the transaction. (Ex

ception while inserting the following entity: [GenericEntity:Invoice][billingAccountId,null()][createdStamp,2007-07-04 19:59:06.653(java.sql.Timestamp)][createdTxStamp,2007-07-04 19:59:04.314(java.sql.Timestamp)][currencyUomId,EUR(java.lang.String)][dueDate,null()][invoiceDate,2007-07-04 19:59:06.511(java.sql.Timestamp)][invoiceId,2007-1(java.lang.String)][invoiceTypeId,SALES_INVOICE(java.lang.String)][lastUpdatedStamp,2007-07-04 19:59:06.653(java.sql.Timestamp)][lastUpdatedTxStamp,2007-07-04 19:59:04.314(java.sql.Timestamp)][partyId,10001(java.lang.String)][partyIdFrom,Company(java.lang.String)][statusId,INVOICE_IN_PROCESS(java.lang.String)] (while inserting: [GenericEntity:Invoice][billingAccountId,null()][createdStamp,2007-07-04 19:59:06.653(java.sql.Timestamp)][createdTxStamp,2007-07-04 19:59:04.314(java.sql.Timestamp)][currencyUomId,EUR(java.lang.String)][dueDate,null()][invoiceDate,2007-07-04 19:59:06.511(java.sql.Timestamp)][invoiceId,2007-1(java.lang.String)][invoiceT
ypeId,SALES_INVOICE(java.lang.String)][lastUpdatedStamp,2007-07-04 19:59:06.653(java.sql.Timestamp)][lastUpdatedTxStamp,2007-07-04 19:59:04.314(java.sql.Timestamp)][partyId,10001(java.lang.String)][partyIdFrom,Company(java.lang.String)][statusId,INVOICE_IN_PROCESS(java.lang.String)] (SQL Exception while executing the following:INSERT INTO INVOICE (INVOICE_ID, INVOICE_TYPE_ID, PARTY_ID_FROM, PARTY_ID, ROLE_TYPE_ID, STATUS_ID, BILLING_ACCOUNT_ID, CONTACT_MECH_ID, INVOICE_DATE, DUE_DATE, PAID_DATE, INVOICE_MESSAGE, REFERENCE_NUMBER, DESCRIPTION, CURRENCY_UOM_ID, LAST_UPDATED_STAMP, LAST_UPDATED_TX_STAMP, CREATED_STAMP, CREATED_TX_STAMP) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) (Duplicate key or integrity constraint violation message from server: "Duplicate entry '2007-1' for key 1"))))]
>

[Xavier Olivella]

El dc 11 de 07 del 2007 a les 09:57 -0600, en/na David E Jones va
escriure:

> If there's a bug no documentation will fix it, a human will have to go in, reproduce the bug, find the problem, and fix the code.

I don't know if it's a bug, I don't think so, maybe it's just a bad
setting but as there is no docs I can't be sure.
There is nowhere (or I can't find) where explains how this annual reset
works, and how the prefix is set up (If you don't set any prefix, then
invoice number is -1, so I'm almost sure you need a year prefix)


> You can file a Jira issue to help keep track of it, but that won't necessarily get resources allocated to getting it fixed. Someone with an interest in the feature will actually have to do it, or hire someone else to do it.


Thanks for your comment. I'll try.

[cjhowe]

A quick note.

The requirements that you're discussing is probably handled better with
a different approach.  When you give meaning to data that is created
arbitrarily (surrogate primary keys like invoiceId) you WILL run into
problems.  This is likely better handled through the use of an external
id instead of the primary key.  

--- Xavier Olivella <[hidden email]> wrote:

> As told in the other message my problem is that I want the "invoice
> sequence" to be set as "Restart on Fiscal Year (no gaps, per org,
> reset
> to 1 each year)" but it doesn't work. The only way it works is with
> default option.
>  
> I am getting really mad about that, I've been testing dozens of
> things
> and searching in docs for days and I cannot make it run.
> I really could not find any "in-deep" documentation about setting the
> company accounting, so it's impossible for me to solve the problem.
>  
> When I make an invoice it works, but on the following the program
> gives
> it the same invoice number so it crashes with the error below.
>  
> Could anyone help me?
>  
>  
>  
> ***************************************
> The Following Errors Occurred:  
>  
> ERROR: Could not complete the Sub-method used by quickShip methods to
> create a shipment
>

[file:/usr/local/test01/applications/product/script/org/ofbiz/shipment/shipment/ShipmentServices.xml#createShipmentForFacilityAndShipGroup]
> process [problem invoking the [updateShipment] service with the map
> named [packedContext] containing [[shipmentId=10012,
> statusId=SHIPMENT_PACKED,
> userLogin=[GenericEntity:UserLogin][createdStamp,2007-05-31
> 13:21:26.0(java.sql.Timestamp)][createdTxStamp,2007-05-31
>
13:21:26.0(java.sql.Timestamp)][currentPassword,a059d9ac5e0b9c8067ae0cebb9a5bcba1636c386(java.lang.String)][disabledDateTime,null()][enabled,Y(java.lang.String)][hasLoggedOut,N(java.lang.String)][isSystem,null()][lastCurrencyUom,null()][lastLocale,ca(java.lang.String)][lastUpdatedStamp,2007-07-04
> 18:25:10.0(java.sql.Timestamp)][lastUpdatedTxStamp,2007-07-04
>
18:25:10.0(java.sql.Timestamp)][partyId,X.Olivella(java.lang.String)][passwordHint,null()][successiveFailedLogins,0(java.lang.Long)][userLoginId,x.olivella(java.lang.String)],
> locale=ca]]: Problems with the transaction. (Exception while
> inserting the following entity:
>
[GenericEntity:Invoice][billingAccountId,null()][createdStamp,2007-07-04
> 19:59:06.653(java.sql.Timestamp)][createdTxStamp,2007-07-04
>
19:59:04.314(java.sql.Timestamp)][currencyUomId,EUR(java.lang.String)][dueDate,null()][invoiceDate,2007-07-04
>
19:59:06.511(java.sql.Timestamp)][invoiceId,2007-1(java.lang.String)][invoiceTypeId,SALES_INVOICE(java.lang.String)][lastUpdatedStamp,2007-07-04
> 19:59:06.653(java.sql.Timestamp)][lastUpdatedTxStamp,2007-07-04
>
19:59:04.314(java.sql.Timestamp)][partyId,10001(java.lang.String)][partyIdFrom,Company(java.lang.String)][statusId,INVOICE_IN_PROCESS(java.lang.String)]
> (while inserting:
>
[GenericEntity:Invoice][billingAccountId,null()][createdStamp,2007-07-04
> 19:59:06.653(java.sql.Timestamp)][createdTxStamp,2007-07-04
>
19:59:04.314(java.sql.Timestamp)][currencyUomId,EUR(java.lang.String)][dueDate,null()][invoiceDate,2007-07-04
>
19:59:06.511(java.sql.Timestamp)][invoiceId,2007-1(java.lang.String)][invoiceTypeId,SALES_INVOICE(java.lang.String)][lastUpdatedStamp,2007-07-04
> 19:59:06.653(java.sql.Timestamp)][lastUpdatedTxStamp,2007-07-04
>
19:59:04.314(java.sql.Timestamp)][partyId,10001(java.lang.String)][partyIdFrom,Company(java.lang.String)][statusId,INVOICE_IN_PROCESS(java.lang.String)]

> (SQL Exception while executing the following:INSERT INTO INVOICE
> (INVOICE_ID, INVOICE_TYPE_ID, PARTY_ID_FROM, PARTY_ID, ROLE_TYPE_ID,
> STATUS_ID, BILLING_ACCOUNT_ID, CONTACT_MECH_ID, INVOICE_DATE,
> DUE_DATE, PAID_DATE, INVOICE_MESSAGE, REFERENCE_NUMBER, DESCRIPTION,
> CURRENCY_UOM_ID, LAST_UPDATED_STAMP, LAST_UPDATED_TX_STAMP,
> CREATED_STAMP, CREATED_TX_STAMP) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?,
> ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) (Duplicate key or integrity constraint
> violation message from server: "Duplicate entry '2007-1' for key
> 1"))))]
>

[Xavier Olivella]

Ok, I've found it, and I think you were right, maybe it's a bug.

The problem is in file
applications/accounting/script/org/ofbiz/accounting/invoice/InvoiceServices.xml

In method "getNextInvoiceId"

The bug is that when you first start, you have no lastInvoiceRestartDate
and no lastInvoiceNumber, so you need to set them as:

<calculate field-name="partyAcctgPreference.lastInvoiceNumber"
type="Long"><number value="1"/></calculate>
Set lastInvoiceNumber to 1, right.

<set from-field="nowTimestamp"
field="partyAcctgPreference.lastInvoiceRestartDate"/>
And here is the problem, I checked it after doing my first Invoice and
my lastInvoiceRestartDate is not set up to anything, so next invoice the
program tries to give it the "1" again.

I think the problem is that variable "nowTimestamp" is set up AFTER it's
used, but I'm not sure as I really don't know java.

After a while tries to set up the prefix to current year (as I figured)
and it doesn't work also, so maybe the problem is bigger and
java.util.Date is not working properly here...

<!-- get the current year string for prefix, etc; simple 4 digit year
date string -->
<call-class-method class-name="org.ofbiz.base.util.UtilDateTime"
method-name="toDateString" ret-field-name="curYearString">
  <field field-name="partyAcctgPreference.lastInvoiceRestartDate"
type="java.util.Date"/>
  <string value="yyyy"/>
</call-class-method>
<set value="${curYearString}-${partyAcctgPreference.lastInvoiceNumber}"
field="invoiceIdTemp"/>



El dc 11 de 07 del 2007 a les 09:57 -0600, en/na David E Jones va
escriure:

> If there's a bug no documentation will fix it, a human will have to go in, reproduce the bug, find the problem, and fix the code.
>
> You can file a Jira issue to help keep track of it, but that won't necessarily get resources allocated to getting it fixed. Someone with an interest in the feature will actually have to do it, or hire someone else to do it.
>
> -David
>
>
> Xavier Olivella wrote:
> > As told in the other message my problem is that I want the "invoice
> > sequence" to be set as "Restart on Fiscal Year (no gaps, per org, reset
> > to 1 each year)" but it doesn't work. The only way it works is with
> > default option.
> >  
> > I am getting really mad about that, I've been testing dozens of things
> > and searching in docs for days and I cannot make it run.
> > I really could not find any "in-deep" documentation about setting the
> > company accounting, so it's impossible for me to solve the problem.
> >  
> > When I make an invoice it works, but on the following the program gives
> > it the same invoice number so it crashes with the error below.
> >  
> > Could anyone help me?
> >  
> >  
> >  
> > ***************************************
> > The Following Errors Occurred:  
> >  
> > ERROR: Could not complete the Sub-method used by quickShip methods to
> > create a shipment
> > [file:/usr/local/test01/applications/product/script/org/ofbiz/shipment/shipment/ShipmentServices.xml#createShipmentForFacilityAndShipGroup] process [problem invoking the [updateShipment] service with the map named [packedContext] containing [[shipmentId=10012, statusId=SHIPMENT_PACKED, userLogin=[GenericEntity:UserLogin][createdStamp,2007-05-31 13:21:26.0(java.sql.Timestamp)][createdTxStamp,2007-05-31 13:21:26.0(java.sql.Timestamp)][currentPassword,a059d9ac5e0b9c8067ae0cebb9a5bcba1636c386(java.lang.String)][disabledDateTime,null()][enabled,Y(java.lang.String)][hasLoggedOut,N(java.lang.String)][isSystem,null()][lastCurrencyUom,null()][lastLocale,ca(java.lang.String)][lastUpdatedStamp,2007-07-04 18:25:10.0(java.sql.Timestamp)][lastUpdatedTxStamp,2007-07-04 18:25:10.0(java.sql.Timestamp)][partyId,X.Olivella(java.lang.String)][passwordHint,null()][successiveFailedLogins,0(java.lang.Long)][userLoginId,x.olivella(java.lang.String)], locale=ca]]: Problems with the transaction. (

 Ex
> ception while inserting the following entity: [GenericEntity:Invoice][billingAccountId,null()][createdStamp,2007-07-04 19:59:06.653(java.sql.Timestamp)][createdTxStamp,2007-07-04 19:59:04.314(java.sql.Timestamp)][currencyUomId,EUR(java.lang.String)][dueDate,null()][invoiceDate,2007-07-04 19:59:06.511(java.sql.Timestamp)][invoiceId,2007-1(java.lang.String)][invoiceTypeId,SALES_INVOICE(java.lang.String)][lastUpdatedStamp,2007-07-04 19:59:06.653(java.sql.Timestamp)][lastUpdatedTxStamp,2007-07-04 19:59:04.314(java.sql.Timestamp)][partyId,10001(java.lang.String)][partyIdFrom,Company(java.lang.String)][statusId,INVOICE_IN_PROCESS(java.lang.String)] (while inserting: [GenericEntity:Invoice][billingAccountId,null()][createdStamp,2007-07-04 19:59:06.653(java.sql.Timestamp)][createdTxStamp,2007-07-04 19:59:04.314(java.sql.Timestamp)][currencyUomId,EUR(java.lang.String)][dueDate,null()][invoiceDate,2007-07-04 19:59:06.511(java.sql.Timestamp)][invoiceId,2007-1(java.lang.String)][invoic
 eT
> ypeId,SALES_INVOICE(java.lang.String)][lastUpdatedStamp,2007-07-04 19:59:06.653(java.sql.Timestamp)][lastUpdatedTxStamp,2007-07-04 19:59:04.314(java.sql.Timestamp)][partyId,10001(java.lang.String)][partyIdFrom,Company(java.lang.String)][statusId,INVOICE_IN_PROCESS(java.lang.String)] (SQL Exception while executing the following:INSERT INTO INVOICE (INVOICE_ID, INVOICE_TYPE_ID, PARTY_ID_FROM, PARTY_ID, ROLE_TYPE_ID, STATUS_ID, BILLING_ACCOUNT_ID, CONTACT_MECH_ID, INVOICE_DATE, DUE_DATE, PAID_DATE, INVOICE_MESSAGE, REFERENCE_NUMBER, DESCRIPTION, CURRENCY_UOM_ID, LAST_UPDATED_STAMP, LAST_UPDATED_TX_STAMP, CREATED_STAMP, CREATED_TX_STAMP) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) (Duplicate key or integrity constraint violation message from server: "Duplicate entry '2007-1' for key 1"))))]
> >