Invoice consolidate with Sale Journal

(0) Share

Report

Posted on by Flarez

195

I have a problem running the sale journal consolidated sales invoices, the sale journal is printed in blank , the invoice print ok

*This post is locked for comments

All responses (13)

Answers (0)

Fujimi DB 275 on at

Like (0)

Report

RE: Invoice consolidate with Sale Journal

Hmmm...you might need to tinker with the trace settings so that you can see the error that is triggering the rollback. It looks like it might have something to do with shipper ID EB0000017, but it's hard to say.
Flarez 195 on at

Like (0)

Report

RE: Invoice consolidate with Sale Journal

begin transaction

go

declare @p1 int

set @p1=180150085

declare @p3 int

set @p3=2

declare @p5 int

set @p5=-1

exec sp_cursoropen @cursor=@p1 output,@stmt='DMG_SOShipHeader_AllStamp ''100'', ''EB0000018'' ',@scrollopt=@p3 output,@ccopt=4,@rows=@p5 output

select @p1, @p3, @p5

go

exec sp_cursorfetch @cursor=180150085,@fetchtype=1,@rownumber=0,@nrows=7

go

update soshipheader set accrdocdate=accrdocdate where cpnyid= '100' and shipperid= 'EB0000018' and tstamp <= 0x0000000056e91cd6

go

update soshipheader set arbatnbr= '017943',ardoctype= 'IN',inbatnbr= '147042',lupd_datetime= '03/26/2013 00:00:00:00',lupd_prog= '40690',perpost= '201210',shipregisterid= '0000000094' where cpnyid= '100' and shipperid= 'EB0000018'

go

exec sp_cursorclose @cursor=180150085

go

declare @p1 int

set @p1=180150087

declare @p3 int

set @p3=2

declare @p5 int

set @p5=-1

exec sp_cursoropen @cursor=@p1 output,@stmt='DMG_SOShipHeader_AllStamp ''100'', ''EB0000017'' ',@scrollopt=@p3 output,@ccopt=4,@rows=@p5 output

select @p1, @p3, @p5

go

exec sp_cursorfetch @cursor=180150087,@fetchtype=1,@rownumber=0,@nrows=7

go

update soshipheader set accrdocdate=accrdocdate where cpnyid= '100' and shipperid= 'EB0000017' and tstamp <= 0x0000000056e91e19

go

rollback transaction

go
Fujimi DB 275 on at

Like (0)

Report

RE: Invoice consolidate with Sale Journal

Can you adjust your SQL Profiler trace settings and try again? You should be able to see the statement that is causing the error.

Select all columns for the following events only:

Database

--Data File Auto Grow

--Data File Auto Shrink

--Log File Auto Grow

--Log File Auto Shrink

Errors and Warnings

--Exception

--User Error Message

Stored Procedures

--RPC: Completed

--SP: Completed

--SP: Starting

--SP: StmtCompleted

--SP: StmtStarting

TSQL

--SQL: BatchCompleted

--SQL: BatchStarting

--SQL: StmtCompleted

--SQL: StmtStarting

Thanks,

DB
Flarez 195 on at

Like (0)

Report

RE: Invoice consolidate with Sale Journal

This is part of the trace, the strange thing is that makes a RollBack

begin transaction

go

update soshipheader set accrdocdate=accrdocdate where cpnyid= '100' and shipperid= 'EB0000018' and tstamp <= 0x0000000056e91cd6

go

update soshipheader set arbatnbr= '017943',ardoctype= 'IN',inbatnbr= '147042',lupd_datetime= '03/26/2013 00:00:00:00',lupd_prog= '40690',perpost= '201210',shipregisterid= '0000000094' where cpnyid= '100' and shipperid= 'EB0000018'

go

update soshipheader set accrdocdate=accrdocdate where cpnyid= '100' and shipperid= 'EB0000017' and tstamp <= 0x0000000056e91d58

go

rollback transaction

go
Fujimi DB 275 on at

Like (0)

Report

RE: Invoice consolidate with Sale Journal

That's good that the first query returned records.

Do you know how to run a SQL Server Profiler trace? If not, read this knowledge base article:

support.microsoft.com/.../967955

Try running a trace of the sales journal process. Review the results to see if you can find any errors.

Thanks,

DB
Flarez 195 on at

Like (0)

Report

RE: Invoice consolidate with Sale Journal

These are the results, the first query

100 EB0000017 014310

100 EB0000018 014310

100 EB0000008 014323

100 EB0000027 014323

100 EB0000033 014338

100 EB0000034 014338

100 EB0000031 014339

100 EB0000037 014341

the second query is 9 results records with a specific shipment, for example EB000000017

100 2013-03-15 15:53:00 2013-03-15 15:53:00 1507 2013-03-15 15:53:00 CRTS

100 2013-03-15 15:55:00 2013-03-15 15:55:00 1508 2013-03-15 15:55:00 MSHP

100 2013-03-15 15:57:00 2013-03-15 15:57:00 1509 2013-03-15 15:57:00 MSHP

100 2013-03-15 15:57:00 2013-03-15 15:57:00 1510 2013-03-15 15:57:00 PPAK

100 2013-03-15 15:58:00 2013-03-15 15:58:00 1511 2013-03-15 15:58:00 MSHP

100 2013-03-15 15:58:00 2013-03-15 15:58:00 1512 2013-03-15 15:58:00 CSHP

100 2013-03-15 15:58:00 2013-03-15 15:58:00 1513 2013-03-15 15:58:00 RUPD

100 2013-03-15 15:59:00 2013-03-15 15:59:00 1514 2013-03-15 15:59:00 USHP

100 2013-03-15 16:26:00 2013-03-15 16:26:00 1528 2013-03-15 16:26:00 PINV 014310

missing some fields entering this screen
Fujimi DB 275 on at

Like (0)

Report

RE: Invoice consolidate with Sale Journal

Can you run this SQL statement against your database:

Select CpnyID,

ShipperID,

InvcNbr

From SOShipHeader (NOLOCK)

Where Status = 'C'

And ShipRegisterID = ''

And ConsolInv = 1

And InvcNbr <> ''

And Exists(select *

from SOEvent (NOLOCK)

where CpnyID = SOShipHeader.CpnyID And ShipperID = SOShipHeader.ShipperID And EventType = 'PINV')

Order By InvcNbr

Does it return any results?

If it doesn't return any results, can you run these two statements and post the result:

select CpnyID, ShipperID, InvcNbr, Status, ShipRegisterID, ConsolInv, InvcNbr

from SOShipHeader

where ShipperID = 'One of your Shipper numbers here.'

select *

from SOEvent

where ShipperID = 'Same Shipper number as above here.'

Thanks,

DB
Flarez 195 on at

Like (0)

Report

RE: Invoice consolidate with Sale Journal

When you run the Sales Journal, are you selecting any particular options or do you just run it?

I just run it

Can you confirm your set up for consolidated invoices (OM Setup settings, Aggregations, Customer settings, and specific Shipper settings)?

If you are selected consolidated invoice in setup order screen 40950 and Customer settings

When I print the invoice for the report prints fine 40682, several shipments into one invoice. but the sales journal is bad
Fujimi DB 275 on at

Like (0)

Report

RE: Invoice consolidate with Sale Journal

Flarez,

When you run the Sales Journal, are you selecting any particular options or do you just run it?

Can you confirm your set up for consolidated invoices (OM Setup settings, Aggregations, Customer settings, and specific Shipper settings)?

Thanks,

DB
Flarez 195 on at

Like (0)

Report

RE: Invoice consolidate with Sale Journal

If you check, and has nothing in that field (the Sales Journal ID) is blank the screen 4011000 tab other information , but have number the invoice and date is correct