Batch and serial numbers in Crystal Document Layouts

[Former Member]

Hi,

I am attempting to write a set of document layouts using Crystal reports in Sap v8.82.

The stock items are either serial numbered items or batch numbered or none serial and batch number assign.

I need to display the serial numbers or batch numbers (if have) on the Delivery Note.

i'm cannot find a suitable link to the tables where the serial number/batch numbers are stored with the Good Receive PO site.

Does anyone know or have suggestions on how to link these (or other?)

Thanks in advance

[julie_jamieson2]

Hi Ben,

Try OSRI, SRI1 and (from memory) OSRN. You should be able to draw the numbers that have been allocated to the delivery / invoice from there

[former_member227598]

Hi Ben,

If you got correct answer then please marks as a correct Answer.

And Please close the thread....

Rgds,

Kamlesh Naware

[Former Member]

Hi Kamlesh,

Just to add, i have try to add both series + batch formatted search query in two separate column, i'm experience GRPO to A/P invoice copy is slow.

it delay more than 15 sec to copy on GRPO into A/P invoice.

if only 1x search query apply, it work pretty fast.

[Former Member]

Hi Kamlesh,

sure.

appreciate your help on this. but it still havent meet my users requirement that they wish it auto show under the print template.

Thanks

[former_member227598]

Hi Ben,

Try this for Serial Number Print.......

declare @batch as nvarchar(100)

declare @txt nvarchar(max)

set @txt = ''

declare P cursor for SELECT T0.[IntrSerial] + space(1)

  FROM OSRI T0 INNER JOIN SRI1 T1 ON T0.ItemCode = T1.ItemCode and T0.WhsCode = T1.WhsCode

  and T0.SysSerial = T1.SysSerial

  WHERE T1.[BaseEntry] =$[$38.45.0] and T1.[BaseLinNum] =$[$38.46.0]

open P

fetch next from P into @batch

while @@fetch_status = 0

begin

set @txt =  @batch +','

fetch next from P into @batch

end

close P

deallocate P

select @txt

Rgds,

Kamlesh Naware

[Former Member]

Hi Kamlesh,

apology for late respond.

how i can additional one field out? in this case, i wish to add the T0.notes and system provide me the error.

Did test this formatted query. it work great with the item manage by notes.

how about the item managed by series?

Once again thanks for your great help and appreciate you can provide me further advice on series item as well.

Thanks

[former_member227598]

Hi Ben,

Yes  ..... it is Possible to create a User Defined Field on A/R Invoice row level, but not in Delivery Order row level, which holds the Serial Numbers/Batch Details.  This field can be populated by Formatted Search when the A/R Invoice is recorded (created from delivery note).

Query check at below and set as per display fromat :

declare @batch as nvarchar(100)

declare @txt nvarchar(max)

set @txt = ''

declare P cursor for SELECT T0.[BatchNum] + space(1)

  + cast(cast(T1.[Quantity] as decimal(19,2)) as nvarchar)

  + space(1) + case when T0.[ExpDate] is null then '' else convert(nvarchar,T0.[ExpDate],102) end

  FROM OIBT T0 INNER JOIN IBT1 T1 ON T0.ItemCode = T1.ItemCode and T0.WhsCode = T1.WhsCode

  and T0.BatchNUm = T1.BatchNum

  WHERE T1.[BaseEntry] =$[$38.45.0] and T1.[BaseLinNum] =$[$38.46.0]

open P

fetch next from P into @batch

while @@fetch_status = 0

begin

set @txt = @txt '\n'@batch +','

fetch next from P into @batch

end

close P

deallocate P

select @txt

Thanks,

Kamlesh Naware

[Former Member]

Hi Kamlesh,

Thanks for your kind advice.

how i can make it possible under good receipe PO level?as we need to print an internal docs to our forwarder for necessary stock arrangements.

thanks

[former_member227598]

Hi Ben,

I think you have modified the Query and try in AP Invoice Screen And I think it will be work.

Thks,

Kamlesh Naware

[Former Member]

Hi Kamlesh,

can further advice how it work on AP invoices.

as i try at my site, it seen not able to work. i'm think i might do something wrong to join the table

Thanks.

[former_member227598]

Hi Ben,

It will work on AP Invoice also ,because I am using the OIBT & IBT1 Table and Inner Join with below condition (i.e INNER JOIN IBT1 T1 ON T0.ItemCode = T1.ItemCode and T0.WhsCode = T1.WhsCode   and T0.BatchNUm = T1.BatchNum )

Please try the below Query in AP Invoice Row Level.....

declare @batch as nvarchar(100)

declare @txt nvarchar(max)

set @txt = ''

declare P cursor for SELECT T0.[BatchNum] + space(1)

  + cast(cast(T1.[Quantity] as decimal(19,2)) as nvarchar)

  + space(1) + case when T0.[ExpDate] is null then '' else convert(nvarchar,T0.[ExpDate],102) end

  FROM OIBT T0 INNER JOIN IBT1 T1 ON T0.ItemCode = T1.ItemCode and T0.WhsCode = T1.WhsCode

  and T0.BatchNUm = T1.BatchNum

  WHERE T1.[BaseEntry] =$[$38.45.0] and T1.[BaseLinNum] =$[$38.46.0]

open P

fetch next from P into @batch

while @@fetch_status = 0

begin

set @txt =  @batch +','

fetch next from P into @batch

end

close P

deallocate P

select @txt

Thks,

Kamlesh Naware

[Former Member]

Hi Kamlesh,

thanks for the sricpt.

how i can let the system auto input the serial number if the particular item is not manage by batch? as we have the item managed by serial number, batch and none.

or can have a script that will auto input the record, if this particular item is manage by serial number, it show serial number.

if this particular items manage by batch, it show batch serial?

once again, thanks for your great help.

[former_member227598]

Hi Ben,

If you got correct answer then please marks as a correct Answer . And Close the Thread.

Rgds,

Kamlesh Naware