cancel
Showing results for 
Search instead for 
Did you mean: 

Still Cant connect an Invoice item to batch number

Former Member
0 Kudos

8.80.236 PL 18 HOTFIX1

I thought I got this answered but it is still illuding me.

I still cannot make the connection between INV1 and the correct record in OBTN for a crystal report.

Example:

Table: INV1 Field: DocEntry Value: 234

Table: INV1 Field: LineNum Value: 0

Table: INV1 Field: ItemCode Value: Product A

Table: INV1 Field: Quantity Value: 2

Came from

Table: OBTN Field: ItemCode Value: Product A

Table: OBTN Field: DistNumber Value: Product A-0001

Table: OBTN Field: MnfSerial Value: Product B-0002

My issue is that besides the ItemCode and Date fields in both tables there is no specific link to a batch. If the Itemcode in OBTN has 2 records then there is no way of telling which batch the INV1 came from because it will link to both records in OBTN.

Simular issues arise with OBTQ OBTW OITL ITL1

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

Hello Jon,

To solve this problem, I did the following:

Used the standard SBO CR Lay-out

Built a custom CR subreport, in this report I used the Command function to retreive data:

SELECT DISTINCT

dbo.OITL.CardCode, dbo.OITL.CardName, dbo.OITL.DocNum, dbo.DLN1.DocEntry, dbo.OITL.DocType, dbo.OSRN.DistNumber, dbo.OSRN.MnfSerial,

dbo.OITL.ItemCode, dbo.OITL.ItemName, dbo.OSRN.MnfDate, dbo.OSRN.GrntExp

FROM dbo.ITL1 INNER JOIN

dbo.OITL ON dbo.ITL1.LogEntry = dbo.OITL.LogEntry INNER JOIN

dbo.OSRN ON dbo.ITL1.ItemCode = dbo.OSRN.ItemCode AND dbo.ITL1.SysNumber = dbo.OSRN.SysNumber RIGHT OUTER JOIN

dbo.DLN1 ON dbo.OITL.DocLine = dbo.DLN1.LineNum AND dbo.OITL.DocEntry = dbo.DLN1.DocEntry

WHERE (dbo.OITL.DocType = 15)

The subreport is embeded by the DocEntry and the (dbo.OITL.DocType = 15) in the subreport, The OITL.DocType of a sales invoices is 13

If you give your e-mail, I can send you a delivery report in CR if you like.

Grtz,

Freek

Former Member
0 Kudos

Freek, would love the Delivery CR report. Sean nsbizsolutions com

Former Member
0 Kudos

Hi Jon,

These two tables may not have direct link between them. Do you have delivery in process or copy sales order to invoice directly?

Thanks,

Gordon

Former Member
0 Kudos

I have been entering directly in Modules> Sales A/R> A/R Invoice for now in a demo DB to make sure I can produce this type of report due to some state reg.