Serial Number for Invoice

[Former Member]

Hi Experts,  

I need multiple serial Number in at rowlevel for the invoices...  If serial number is created from Delivery then serial number for delivery must be displayed in invoice

Requirement is mentioned below...

Itemcode    Item Description    Invoice No    PartNo        Itemgroup     Qty     Serial Number 

FBT0001    HP Notebook          1510004        ABC          Notebook       5      ABC001 , ABC002 ,

                                                                                                                        ABC003 , ABC004 ,

                                                                                                                        ABC005

FBT0002    HP Desktop            1510005        BAC            Desktop        3      BAC001, BAC002,

                                                                                                                         BAC003

Regards,

K.Ramasamy

[KennedyT21 · Accepted solution]

Hi Ramasamy,

You can find Serial no details in OSRI and SRI1 table item wise.

Why do you want to capture in the invoice itself...

Regards

Kennedy

[Former Member · Accepted solution]

Hi Kennedy Sir,

This is the requirement from our management.. They need to find the serial number for which invoice prepared ...

Regards,

K.Ramasamy

[KennedyT21 · Accepted solution]

You can show the management by opening the serial number screen .. or the invoice printout with the screen...

Coming to your requirement it is not possible by the standard...

Hope Helpful

Regards

Kennedy

[Former Member · Accepted solution]

Kennedy Sir,

I have made a Query where I am getting Multiple Serial Number for the Invoices ..In the Below query I need a subquery for Displaying Itemgroup name & FirmName ... 

          select p2.docentry , p2.docnum, p2.cardcode ,p2.cardname , p1.U_part_no as 'PartNo', P1.Itemcode, P1.Dscription, p1.price as 'UnitPrice' , P1.Quantity , p1.linetotal as 'TotalSP' , Case When P1.BaseType=15 then 'S/N : ' + convert(varchar(2000),(Select distinct isnull(SN2.IntrSerial,'')+', ' From SRI1 SN1 Left Join OSRI SN2 ON SN1.ItemCode=SN2.ItemCode And SN1.SysSerial=SN2.SysSerial Where SN1.BaseType=15  and SN1.BaseEntry=P1.BaseEntry and SN1.BaseLinNum=P1.BaseLine FOR XML PATH(''))) else 'S/N : ' + convert(varchar(2000),(Select distinct isnull(SN2.IntrSerial,'')+', ' From SRI1 SN1 Left Join OSRI SN2 ON SN1.ItemCode=SN2.ItemCode And SN1.SysSerial=SN2.SysSerial Where SN1.BaseType=13  and SN1.BaseEntry=P1.DocEntry and SN1.BaseLinNum=P1.LineNum FOR XML PATH(''))) end AS SerialNo   from  OINV P2 inner join INV1 P1 on p2.DocEntry = p1.DocEntry where p2.DocDate >= '20120401' and p2.DocDate<='20130331'

Regards,

K.Ramasamy

[KennedyT21 · Accepted solution]

What do you mean by firm name...

[KennedyT21 · Accepted solution]

with itemgroup

SELECT p2.docentry,

       p2.docnum,

       p2.cardcode,

       p2.cardname,

       p1.U_part_no AS 'PartNo',

       P1.Itemcode,

       P1.Dscription,

       p1.price AS 'UnitPrice',

       P1.Quantity,

       p1.linetotal AS 'TotalSP',

       CASE

            WHEN P1.BaseType = 15 THEN 'S/N : ' + CONVERT(

                     VARCHAR(2000),

                     (

                         SELECT DISTINCT ISNULL(SN2.IntrSerial, '') + ', '

                         FROM   SRI1 SN1

                                LEFT JOIN OSRI SN2

                                     ON  SN1.ItemCode = SN2.ItemCode

                                     AND SN1.SysSerial = SN2.SysSerial

                         WHERE  SN1.BaseType = 15

                                AND SN1.BaseEntry = P1.BaseEntry

                                AND SN1.BaseLinNum = P1.BaseLine FOR XML PATH('')

                     )

                 )

            ELSE 'S/N : ' + CONVERT(

                     VARCHAR(2000),

                     (

                         SELECT DISTINCT ISNULL(SN2.IntrSerial, '') + ', '

                         FROM   SRI1 SN1

                                LEFT JOIN OSRI SN2

                                     ON  SN1.ItemCode = SN2.ItemCode

                                     AND SN1.SysSerial = SN2.SysSerial

                         WHERE  SN1.BaseType = 13

                                AND SN1.BaseEntry = P1.DocEntry

                                AND SN1.BaseLinNum = P1.LineNum FOR XML PATH('')

                     )

                 )

       END AS SerialNo, t2.ItmsGrpNam

FROM   OINV P2

       INNER JOIN INV1 P1

            ON  p2.DocEntry = p1.DocEntry

            INNER JOIN oitm t1 ON t1.itemcode=p1.itemcode

            INNER JOIN oitb t2 ON t2.ItmsGrpCod=t1.ItmsGrpCod

WHERE  p2.DocDate >= '20120401'

       AND p2.DocDate <= '20130331'

[Former Member · Accepted solution]

Kennedy sir,

Firm name means  Manufacturer name ..

In Item master data  --> General Tab

U can find the manufacturer where the field name is OITM.Firmcode..

[Former Member]

Thank you Kennedy ...Its Working