How to get invoice number from product receipt number in Dynamics Ax 2012

Overview:-

Here we will see how to get invoice number from product receipt in Ax 2012. Let’s take an example of purchase order, I have created one purchase order after in that purchase order I have created one or more product receipt and GRN. When I will go for purchase invoice I will select two product receipt and in that product receipt I will select only 1(one) line if I have one or more lines in that product receipt. Then I will go for invoice.

Purchase invoice complete based on product receipt selected lines. The remaining product receipt and their lines I will create a new purchase invoice. Now I have one product receipt and multiple invoice based on lines I will get an invoice number of product receipt.

For Example:-

Purchase order number: - 'XYZ-000001'

Product Receipt: - 'ABC','ABC-1','ABC-2'

In my first product receipt 'ABC' I have 2 (Two) lines same as I have multiple line in other product receipt.

Product Receipt 'ABC'

Line Number	Item	Ordered Qty	Receipt Qty	Invoice Qty
1	PPP	1000	500	0
2	PPP-1	1500	500	0

Product Receipt 'ABC-1'

Line Number	Item	Ordered Qty	Receipt Qty	Invoice Qty
1	PPP	500	250	0
2	PPP-1	1000	500	0

Product Receipt 'ABC-1'

Line Number	Item	Ordered Qty	Receipt Qty	Invoice Qty
1	PPP	250	250	0
2	PPP-1	500	500	0

Now in first Invoice I will select Product receipt 'ABC', 'ABC-2'.

In product receipt ‘ABC’ I will select 1 line, after in product receipt 'ABC-2' I will select 1 line.

Purchase Invoice number 'INV-00001'

Line Number	Item	Invoice Qty
1	PPP	750

After that in second invoice I will select product receipt 'ABC', 'ABC-1'.

In product receipt 'ABC' I will select 2 line, after in product receipt 'ABC-1' I will select 1 line.

Purchase Invoice number 'INV-00002'

Line Number	Item	Invoice Qty
1	PPP	250
2	PPP-1	500

Now I will create last invoice from remaining product receipt lines, so my third invoice generate like below.

Purchase Invoice number 'INV-00003'

Line Number	Item	Invoice Qty
2	PPP-1	1000

So my final product receipt wise data will be like below.

Purchase Order	Product Receipt	Line Number	Item Number	Purchase Invoice	Ordered Qty	Received Qty	Invoice Qty
XYZ-000001	ABC	1	PPP	INV-00001	1000	500	500
XYZ-000001	ABC	2	PPP-1	INV-00002	1500	500	500
XYZ-000001	ABC-1	1	PPP	INV-00002	500	250	250
XYZ-000001	ABC-1	2	PPP-1	INV-00003	1000	500	500
XYZ-000001	ABC-2	1	PPP	INV-00001	250	250	250
XYZ-000001	ABC-2	2	PPP-1	INV-00003	500	500	500

Here is the SQL query for get product receipt line wise invoice number.

SELECT vendpackingslipjour.purchid                                 AS
       'Purchase Order Number',
       CONVERT(DATE, purchtable.accountingdate)                    AS
       'Accounting Date',
       purchtable.purchstatus                                      AS
       'Purchase Status',
       vendpackingslipjour.grnnumber_in                            AS
       'GRN Number',
       CONVERT(DATE, vendpackingslipjour.deliverydate)             AS
       'Delivery Date',
       vendpackingslipjour.packingslipid                           AS
       'Product Receipt Number',
       vendinvoicejour.invoiceid                                   AS
       'Invoice Number',
       vendtable.accountnum                                        AS
       'Vendor Account',
       dirpartytable.NAME                                          AS
       'Vendor Name',
       Cast(vendpackingsliptrans.linenum AS DECIMAL(10, 2))        AS
       'Line Number',
       vendpackingsliptrans.itemid                                 AS
       'Item Number',
       ecoresproducttranslation.NAME                               AS
       'Item Name',
       Cast(vendpackingsliptrans.ordered AS DECIMAL(10, 2))        AS
       'Ordered Quamtity',
       Cast(vendpackingsliptrans.receivedqty_in AS DECIMAL(10, 2)) AS
       'Received Quantity',
       Cast(vendinvoiceinfosubline.inventnow AS DECIMAL(10, 2))    AS
       'Invoice Quantity'
FROM   vendpackingsliptrans
       JOIN ecoresproduct
         ON ecoresproduct.displayproductnumber = vendpackingsliptrans.itemid
       JOIN ecoresproducttranslation
         ON ecoresproducttranslation.product = ecoresproduct.recid
       JOIN vendpackingslipjour
         ON vendpackingslipjour.recid = vendpackingsliptrans.vendpackingslipjour
            AND vendpackingslipjour.grnnumber_in != ''
            AND vendpackingslipjour.dataareaid = 'appl'
            AND ( @PURCHID = ''
                   OR vendpackingslipjour.purchid = @PURCHID )
            AND ( ( Format(vendpackingslipjour.deliverydate, 'dd-MM-yyyy') >=
                          Format(@FromDate, 'dd-MM-yyyy') )
                   OR ( Format(vendpackingslipjour.deliverydate, 'dd-MM-yyyy')
                        <=
                            Format(@ToDate, 'dd-MM-yyyy') ) )
       JOIN purchtable
         ON purchtable.purchid = vendpackingslipjour.purchid
            AND purchtable.dataareaid = 'appl'
       JOIN vendtable
         ON vendtable.accountnum = purchtable.orderaccount
            AND vendtable.dataareaid = 'appl'
            AND ( @VendAccount = ''
                   OR vendtable.accountnum = @VendAccount )
       JOIN dirpartytable
         ON dirpartytable.recid = vendtable.party
       LEFT JOIN vendinvoiceinfosubline
              ON vendinvoiceinfosubline.journalrefrecid =
                 vendpackingsliptrans.recid
                 AND vendinvoiceinfosubline.dataareaid = 'appl'
                 AND vendinvoiceinfosubline.documentid =
                     vendpackingsliptrans.packingslipid
       LEFT JOIN vendinvoiceinfosubtable
              ON vendinvoiceinfosubtable.parmid = vendinvoiceinfosubline.parmid
                 AND vendinvoiceinfosubtable.dataareaid = 'appl'
       LEFT JOIN vendinvoiceinfotable
              ON vendinvoiceinfotable.parmid = vendinvoiceinfosubline.parmid
                 AND vendinvoiceinfotable.parmjobstatus = 1
                 --and VENDINVOICEINFOTABLE.VENDINVOICESAVESTATUS not in ('1','2')
                 AND vendinvoiceinfotable.dataareaid = 'appl'
       --Executed-0
       --ContainErrors-1
       --Waiting-2
       LEFT JOIN vendinvoiceinfoline
              ON vendinvoiceinfoline.parmid = vendinvoiceinfosubline.parmid
                 AND vendinvoiceinfoline.tablerefid =
                     vendinvoiceinfotable.tablerefid
                 AND vendinvoiceinfoline.parmid = vendinvoiceinfosubtable.parmid
                 AND vendinvoiceinfoline.origpurchid =
                     vendinvoiceinfosubtable.origpurchid
                 AND vendinvoiceinfoline.tablerefid =
                     vendinvoiceinfosubtable.tablerefid
                 AND vendinvoiceinfoline.itemid = vendpackingsliptrans.itemid
                 AND vendinvoiceinfoline.dataareaid = 'appl'
       LEFT JOIN vendinvoicejour
              ON vendinvoicejour.parmid = vendinvoiceinfosubline.parmid
                 AND vendinvoicejour.dataareaid = 'appl'
--left join VENDINVOICETRANS on VENDINVOICETRANS.INVOICEID = VENDINVOICEJOUR.INVOICEID
--  and VENDINVOICETRANS.INVOICEDATE = VENDINVOICEJOUR.INVOICEDATE
--  and VENDINVOICETRANS.PURCHID = VENDINVOICEJOUR.PURCHID
--  and VENDINVOICETRANS.NUMBERSEQUENCEGROUP = VENDINVOICEJOUR.NUMBERSEQUENCEGROUP
--  and VENDINVOICETRANS.INTERNALINVOICEID = VENDINVOICEJOUR.INTERNALINVOICEID
--  and VENDINVOICETRANS.ITEMID = VENDPACKINGSLIPTRANS.ITEMID  
--where VENDPACKINGSLIPTRANS.FULLYMATCHED in ('1','0')
ORDER  BY vendinvoicejour.purchid ASC