Received and Not Invoiced report

(0) Share

Report

Posted on by babubaskaran@outloo...

Dear All,

Good to back on Community after a long gap. I am trying to create a smartlist for Purchasing-Received Not Invoiced report. I have created a new smartlist under Purchasing-Receiving Line Items- with POP TYPE not equal to Invoice, Qty Shipped is greater than Qty Matched with PO status is not equal to cancelled. This report giving me value about 1.5m whereas if I run Reports-Purchasing-Analysis-Received Not Invoiced and this reports producing a value of 285K which is matching to purchase accrual balance. I also try to use ReceivingsLineItems Shipments Received but not Invoiced on excel dataconnections and that also gives me a big number. Did anyone experience this issue and how did you resolve the same.

Thanks,

Babu

*This post is locked for comments

I have the same question (0)

All responses (11)

Answers (0)

Suggested answer

Joseph Markovich 3,972 on at

Like (0)

Report

RE: Received and Not Invoiced report

Hi Joy-

Here is a link to all of the SQL views I use for forms and reports in GP. Look in the Views folder and the SQL view is viewPOPReceivedNotInvoiced.

josephmarkovich.com/.../SSRS.zip

I hope this helps.

Joe

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Received and Not Invoiced report

Hi Tania,

Good afternoon! I am unable to get your above query to work in SQL. Is there a trick to it?

Thank you,

Joy

Was this reply helpful? Yes No
GovindDevda 617 on at

Like (0)

Report

RE: Received and Not Invoiced report

SELECT

CASE POL.POLNESTA

WHEN 1 THEN 'New'

WHEN 2 THEN 'Released'

WHEN 3 THEN 'Change Order'

WHEN 4 THEN 'Received'

WHEN 5 THEN 'Closed'

WHEN 6 THEN 'Canceled'

END PO_Line_Status,

CASE RWH.POPTYPE

WHEN 1 THEN 'Shipment'

WHEN 2 THEN 'Invoice'

WHEN 3 THEN 'Shipment/Invoice'

END PO_Type,

RWH.VENDORID AS 'Vendor ID',

RWH.VENDNAME AS 'Vendor Name',

RWH.VNDDOCNM AS 'Vendor Doc #',

RLQ.POPRCTNM AS 'RCT #',

POL.PONUMBER AS 'PO #',

POL.LineNumber AS 'PO Line #',

POL.QTYORDER AS 'Order#',

RLQ.QTYSHPPD AS 'QTY Shipped',

RLQ.QTYMATCH AS 'QTY Matched',

RLH.EXTDCOST AS 'Line Extended Cost',

RWH.Total_Landed_Cost_Amount AS 'Total Landed Cost',

RWH.BACHNUMB AS 'Batch ID',

RLH.LOCNCODE AS 'Site ID',

RWH.POSTEDDT AS 'Posted Date',

RWH.receiptdate AS 'Receipt Date',

RWH.GLPOSTDT AS 'GL Posting Date',

RLH.ITEMNMBR AS 'Item #',

RLH.VNDITNUM AS 'Vendor Item #',

RLH.ITEMDESC AS 'Item Description',

RLH.VNDITDSC AS 'Vendor Item Description',

RLQ.QTYINVCD AS 'QTY Invoiced',

RLH.UOFM AS 'U of M',

RWH.SUBTOTAL AS 'Receipt Subtotal'

INTO #PO_RECEIPT_NOT_INV

FROM

POP10110 POL,

POP10500 RLQ,

POP30300 RWH,

POP30310 RLH

WHERE

RLQ.PONUMBER = POL.PONUMBER AND RLQ.POLNENUM = POL.ORD AND RWH.POPRCTNM = RLQ.POPRCTNM AND RLH.POPRCTNM = RWH.POPRCTNM

AND RLH.POPRCTNM = RLQ.POPRCTNM AND RLH.RCPTLNNM = RLQ.RCPTLNNM AND RLH.PONUMBER = POL.PONUMBER

AND RLH.PONUMBER = RLQ.PONUMBER AND (POL.POLNESTA Not In ('5', '6'))

AND ((RLQ.QTYSHPPD - RLQ.QTYMATCH) <> '0')

AND POL.PONUMBER = 'TPOG1000LS'

ORDER BY RWH.VENDORID, RWH.VNDDOCNM, POL.PONUMBER,

POL.LineNumber

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (3)

Report
RE: Received and Not Invoiced report

I created my own query for this which balances the report write report for 'Shipped not Invoiced'. It also looks up the document number on the shipment to see if it exists as payables invoice (quite often the invoice gets paid without being matched to the shipment, in which case the P&L is doubled up and we do a return on the shipment).

-- GL00100 Account Master - AM -- GL00105 Account Index Master - AIM -- POP10110 Purchase Line Work - PLW -- POP30300 Purchasing Receipt History - RH -- POP10300 Receipt Header Work - RHW -- SY03900 Record Notes Master - RN -- POP30110 Purchase Line History - PLH -- POP30100 Purchase Order History - POH -- POP10100 Purchase Order Work - POW -- PM20000 Payables Open - PO -- PM30200 Payables History - PH select rli.[Receipt Date] [Rct Date], rli.[POP Receipt Number] [Rct No], --rli.[POP Type] [Type], rli.[PO Number], rli.[Vendor Document Number] [Doc No], Q4.DOCDATE [Doc Date Matched], rli.[Vendor ID] [Creditor ID], rli.[Vendor Name] [Creditor Name], rli.[Location Code] [Location], rli.[Item Number] [SJA Item No], isnull(IV.VNDITNUM,'') [Creditor Item No], rli.[Item Description], rli.[QTY Shipped], rli.[QTY Invoice Reserve] [Qty Inv Reserve], --rli.[QTY Invoiced], -- Only populated for shipment/invoice rli.[QTY Matched], rli.[QTY Rejected], rli.[QTY Reserved], rli.[U Of M] [Unit], rli.[Unit Cost] [Unit Cost], rli.[Extended Cost] [Extended Cost], (rli.[QTY Shipped]-rli.[QTY Matched]-rli.[QTY Rejected]) * rli.[Unit Cost] as [Accrued Amount], isnull((CASE Q2.POLNESTA WHEN 1 THEN 'New' WHEN 2 THEN 'Released' WHEN 3 THEN 'Change Order' WHEn 4 THEN 'Received' WHEN 5 THEN 'Closed' WHEN 6 THEN 'Cancelled' END),'') as [PO Line Status], --Case rli.[Non IV] WHEN 1 THEN 'No' WHEN 0 THEN 'Yes' ENd as [Inventory], isnull(RN.TXTFIELD,'') [RCT Record Note], isnull(RN2.TXTFIELD,'') [PO Record Note], rli.[Batch Number] [Batch No], rli.[GL Posting Date] [GL Post Date], rli.[Posted Date] [Posted Date], rli.[User To Enter] [User Entered], rli.[Posted User ID] [Posted User ID], AIM.ACTNUMST [Account Number], AM.ACTDESCR [Account Description] --rli.[Receipt Return Number] [RCT Return No], from GPSTJ..ReceivingsLineItems RLI with (nolock) left join GPSTJ..GL00100 AM with (nolock) on AM.ACTINDX = rli.[Inventory Index] left join GPSTJ..GL00105 AIM with (nolock) on AIM.ACTINDX = rli.[Inventory Index] left join GPSTJ..IV00103 IV with (nolock) on iV.ITEMNMBR = rli.[Item Number] and IV.VENDORID = rli.[Vendor ID] -- RCT Note left join ( select POPRCTNM, RCPTNOTE_1 from GPSTJ..POP10300 with (nolock) where POPTYPE = 1 UNION ALL select POPRCTNM, RCPTNOTE_1 from GPSTJ..POP30300 with (nolock) where POPTYPE = 1) Q1 on Q1.POPRCTNM = rli.[POP Receipt Number] left join GPSTJ..SY03900 RN with (nolock) on RN.NOTEINDX = Q1.RCPTNOTE_1 -- PO Line Status left join ( select PONUMBER, ORD, POLNESTA from GPSTJ..POP10110 with (nolock) UNION ALL select PONUMBER, ORD, POLNESTA from GPSTJ..POP30110 with (nolock)) Q2 on Q2.PONUMBER = rli.[PO Number] and Q2.ORD = rli.[PO Line Number] -- PO Note left join ( select PONUMBER, PONOTIDS_1 from GPSTJ..POP30100 with (nolock) UNION all select PONUMBER, PONOTIDS_1 from GPSTJ..POP10100 with (nolock)) Q3 on Q3.PONUMBER = rli.[PO Number] left join GPSTJ..SY03900 RN2 with (nolock) on RN2.NOTEINDX = Q3.PONOTIDS_1 -- Does Doc No Exist in AP left join ( select VENDORID, DOCNUMBR, DOCDATE from GPSTJ..PM20000 with (nolock) where DOCTYPE = 1 UNION ALL select VENDORID, DOCNUMBR, DOCDATE from GPSTJ..PM30200 with (nolock) where DOCTYPE = 1) Q4 on Q4.VENDORID = rli.[Vendor ID] and Q4.DOCNUMBR = rli.[Vendor Document Number] where rli.[Posting Status] = 'Posted' and rli.[POP Type] = 'Shipment' and rli.[QTY Shipped] > rli.[QTY Matched] and (Q2.POLNESTA NOT IN (5,6) or Q2.POLNESTA IS NULL) order by rli.[Receipt Date], rli.[POP Receipt Number], rli.[Receipt Line Number]

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Received and Not Invoiced report

Did we ever find a solution for this? It would be great to simply get a received but not invoiced report in Excel format.

Was this reply helpful? Yes No
babubaskaran@outloo... 12 on at

Like (0)

Report

RE: Received and Not Invoiced report

Thanks Redbeard and sorry for the delay in response. Yes I understand your point about various conditions to get this report on smartlist. So what I am doing is without any parameters building a view. I am using the conditions GP has it on the stored procedure which produces the Received not Invoice report seeSPreceivednotinvoiced. Once I am done with it will post it on the community.

Thanks,

babu

Was this reply helpful? Yes No
Suggested answer

Redbeard 12,931 on at

Like (0)

Report

RE: Received and Not Invoiced report

Babu,

I went through a similar issue with a client recently, and here is a short list of problems with this SmartList, when a client asked me why this SmartList didn't tie out to RNI report.

PO Line Items can be cancelled without the entire PO being cancelled. PO Line status is not an available column in this SmartList, so you would need to build one in SmartList Builder.

Additionally, POP Type 'Is Not Equal to' Invoice, Includes a lot of territory, so you'd need to get your calculations right.

Was this reply helpful? Yes No
babubaskaran@outloo... 12 on at

Like (0)

Report

RE: Received and Not Invoiced report

Thanks Frank I have checked the same. Sorry my question is I wanted get the data what ever I am getting on the Purchasing-Report-Analysis-Received Not Invoiced(is this information stored in multiple table) in smartlist or excel report . Since the standard report can not be exported to excel and use I am trying this.

Thanks,

Babu

Was this reply helpful? Yes No
Suggested answer

Frank Hamelly | MVP... 46,617 Super User 2025 Season 2 on at

Like (1)

Report

RE: Received and Not Invoiced report

Gotcha Babu. Here's a link to an article that Polino posted about a year ago, with other links to articles that Richard Whaley and I posted about 6 years ago. Hopefully, these will provide some useful insight. community.dynamics.com/.../weekly-dynamic-cleaning-up-received-not-invoiced.aspx

Was this reply helpful? Yes No
babubaskaran@outloo... 12 on at

Like (0)

Report

RE: Received and Not Invoiced report

Thanks Frank hope you are keeping well. Yes I used that and that giving a larger number. It has condition of POP type is not equal to Invoice, QTY shipped is greater than QTY matched and removed where the document status is not equal to canceled.

Thanks,

Babu

Was this reply helpful? Yes No