Seed Bag Returns - Purchasing - Returns Transaction Entry

(0) Share

Report

Posted on by Community Member Microsoft Employee

Trying to help out an end user who manages the sale of "bagged seed" items using the GP PO module -- they use PO at receipt and then sells them out of inventory. The user needs to return bagged seed to a customer. They currently use Returns Transaction Entry (Transactions > Purchasing > Returns Transaction Entry). This process of returning bagged seed is very time consuming since they have to find the bags to return based on the original PO, plus there are 4 different types of returns: Returns, Returns with Credit, Inventory and I believe we generally use Returns with Credit, but that may not be the return type we need to use for all returns in this scenario. The issue here is that between figuring out where the bags are (what PO) and what type of return to use will take a ton of time to sort through. Is there any way that we can select the bags that need to be returned by seed type (item) and simply hit RETURN somewhere in the system, or is there a better way to handle this mass return scenario?

*This post is locked for comments

All responses (17)

Answers (1)

Community Member Microsoft Employee on at

Like (0)

Report

RE: Seed Bag Returns - Purchasing - Returns Transaction Entry

I believe this solution would have fit our needs, however the user and I had a long chat and we decided to handle their bagged seed process and PO returns in an entirely different manner utilizing invoicing to relieve inventory rather than Returns Transaction Entry. I have marked your post/custom reporting solution as doing the trick. Thanks again for your time and information!
Community Member Microsoft Employee on at

Like (0)

Report

RE: Seed Bag Returns - Purchasing - Returns Transaction Entry

Will do, once we hear back from the end-user that they have what they need. Thanks!
Verified answer

Redbeard 12,931 on at

Like (0)

Report

RE: Seed Bag Returns - Purchasing - Returns Transaction Entry

Trey -

A word of caution on this one. Quantity invoiced is only part of the equation. If quantities have been allocated to an Invoice/Sales Order, they cannot/should not be returned to the vendor. So, the Quantity On Hand field in the last query I provided is likely the number the end-users is looking for. One other thing to note, is I have included all Receipt records and Sales records in the query, so the user could more easily see the details that result in the Quantity on Hand (and reconcile them). If the solution fits the needs, I'd appreciate you identifying the particular post that did the trick, so others with similar issues can easily identify the solution.
Community Member Microsoft Employee on at

Like (0)

Report

RE: Seed Bag Returns - Purchasing - Returns Transaction Entry

We have a lot of good info to sort through now. We've created SmartLists using the views you have provided and the end user is sifting through the reports. The trick here is trying to find the actual number of bags left, not dollars remaining (e.g. 4 bags remaining on the PO after 2 bags were sold). I believe we'll have to do a calculated field that is something like QuantityShipped - QuantityInvoiced in order to get that number. We'll play around with all the info you provided and will revert back if we cannot get anywhere. Thanks again for all your help!!
Suggested answer

Redbeard 12,931 on at

Like (0)

Report

RE: Seed Bag Returns - Purchasing - Returns Transaction Entry

Trey -

Without warranty or assurance (please validate your data), here is a script to build a view I believe accomplishes what you're looking for. It starts by pulling all the data from the Inventory Lot Transaction Table. Then Joins Continuing Table Expressions, which pull data from PO Receipt Line work and history tables and Purchases Serial / Lot work tables. It also pulls Sales Serial / Lot Work & History Table, joining in SOP Header Tables Work and History. In the end, for Lot Items It should give you the customer/vendor (Master_Name), Document Number (PO or Invoice), Item_Number, Item_Description, Location, Lot, and Quantities Received, Sold and currently On_Hand. My Fabrikam demo environment ties out.

CREATE View [dbo].[_Lots_Breakdown]

as

with

CTE_POs

as

(

select POP10310.PONUMBER PO,POP10310.ITEMNMBR ITEM,POP10330.SERLTNUM LOT,POP10330.RCPTLNNM LINE,POP10310.POPRCTNM RCPT from POP10310

left join POP10330 on POP10330.POPRCTNM = POP10310.POPRCTNM and POP10310.RCPTLNNM = POP10330.RCPTLNNM

where SERLTNUM is not null

Union

select POP30310.PONUMBER PO,POP30310.ITEMNMBR ITEM,POP30330.SERLTNUM LOT,POP30330.RCPTLNNM LINE,POP30310.POPRCTNM RCPT from POP30310

left join POP30330 on POP30310.POPRCTNM = POP30330.POPRCTNM and POP30310.RCPTLNNM = POP30330.RCPTLNNM

where SERLTNUM is not null

)

,

CTE_Receipts

as

(

select POP10330.POPRCTNM,ITEMNMBR,SERLTNUM,VNDDOCNM from POP10330

left join POP10300 on POP10330.POPRCTNM = POP10300.POPRCTNM

Union All

select POP30330.POPRCTNM,ITEMNMBR,SERLTNUM,VNDDOCNM from POP30330

left join POP30300 on POP30330.POPRCTNM = POP30300.POPRCTNM

)

,

CTE_Lot_Sales

as

(

select ITEMNMBR ITEM,SOP10201.SERLTNUM LOT, SERLTQTY Qty_Sold, SOP10201.SOPNUMBE Invoice_Number

,isnull(SOP10100.CUSTNMBR,SOP30200.CUSTNMBR) CUSTOMER_ID

,isnull(SOP10100.CUSTNAME,SOP30200.CUSTNAME) CUSTOMER_NAME

from SOP10201

left Join SOP10100 ON SOP10201.SOPTYPE = SOP10100.SOPTYPE and SOP10201.SOPNUMBE = SOP10100.SOPNUMBE

left Join SOP30200 on SOP10201.SOPTYPE = SOP30200.SOPTYPE and SOP10201.SOPNUMBE = SOP30200.SOPNUMBE

)

select distinct

isnull(CTE_Lot_Sales.CUSTOMER_NAME,VEND.VENDNAME) Master_Name,

isnull(CTE_POs.PO,CTE_Lot_Sales.Invoice_Number) as Doc_Number,

LOTS.[ITEMNMBR] AS Item_Number,

ITEMS.[ITEMDESC] AS Item_Description,

LOTS.[LOCNCODE] AS Location_Code,

LOTS.[LOTNUMBR] AS Lot_Number,

LOTS.[QTYRECVD] AS QTY_Received,

isnull(CTE_Lot_Sales.Qty_Sold,0) Qty_Sold,

LOTS.[QTYRECVD]-LOTS.[QTYSOLD]-LOTS.[ATYALLOC] AS QTY_ON_HAND

from IV00300 as LOTS

inner join IV00101 as items on LOTS.ITEMNMBR = ITEMS.ITEMNMBR

left join PM00200 as VEND on LOTS.VNDRNMBR = VEND.VENDORID

Left Join CTE_Receipts on LOTS.ITEMNMBR = CTE_Receipts.ITEMNMBR and LOTS.LOTNUMBR = CTE_Receipts.SERLTNUM

left join CTE_POs on LOTS.ITEMNMBR = CTE_POs.ITEM and LOTS.LOTNUMBR = CTE_POs.LOT

left join CTE_Lot_Sales on LOTS.ITEMNMBR = CTE_Lot_Sales.ITEM and LOTS.LOTNUMBR = CTE_Lot_Sales.LOT

where (CTE_Lot_Sales.Invoice_Number is not null OR CTE_POs.PO is not null)

Order by Lot_Number

go

Grant Select on _Lots_Breakdown to DYNGRP
Community Member Microsoft Employee on at

Like (0)

Report

RE: Seed Bag Returns - Purchasing - Returns Transaction Entry

I did some online research and the following view created by Mahmood Alsaadi was really close to giving us what we needed (has a lot of info), although the endgame for us to getting the number (qty, not $ amt) of bags (items) we need to return by PO. Everything I have found is missing that info. For instance, say PO # 123 is for item 2020.555, 4 total bags (qty) on this PO, we sold one customer 2 bags leaving 2 bags to go back/return to the vendor. We need a a report that shows PO # 123 was for 4 bags, 2 bags sold, 2 bags remaining. Qty remaining is what we need.

SELECT A.[PONUMBER] ,
        CASE A.[POSTATUS]
           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 AS POSTATUS ,
        CASE A.[POTYPE]
           WHEN 1 THEN 'STANDARD'
           WHEN 2 THEN 'DROP-SHIP'
           WHEN 3 THEN 'BLANKET'
           WHEN 4 THEN 'BLANKET DROP-SHIP '
        END AS POTYPE ,
        A.[DOCDATE] AS Date ,
        A.[PRMDATE] AS PromiseDate ,
        A.[REQDATE] AS RequestedDate ,
        A.[REMSUBTO] AS RemainingSubTotal ,
        A.[SUBTOTAL] AS SubTotal ,
        A.OREMSUBT AS OriginatingRemainingSubTotal ,
        A.ORSUBTOT AS OriginatingSubTotal ,
        A.[VENDORID] AS VendorID ,
        A.[VENDNAME] AS VendorName ,
        A.[DUEDATE] AS DueDate ,
        A.[CURNCYID] ,
        A.[BUYERID] ,
        CASE B.[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 AS POLineStatus ,
        B.[ITEMNMBR] AS ItemNumber ,
        B.[ITEMDESC] AS ItemDescrption ,
        B.[VENDORID] AS LineVendorID ,
        B.[VNDITNUM] AS VendorItemNumber ,
        B.[LOCNCODE] ,
        B.[UOFM] ,
        B.[QTYORDER] AS QuantityOrdered ,
        B.[QTYCANCE] AS QuantityCancelled ,
        B.[UNITCOST] ,
        B.ORUNTCST AS OriginatingUnitCost ,
        B.[EXTDCOST] AS ExtendedCost ,
        B.OREXTCST AS OriginatingExtendedCost ,
        B.XCHGRATE AS ExchangeRate ,
        ISNULL(C.POPRCTNM, ' ') AS POPRCTNM ,
        ISNULL(C.QTYSHPPD, 0) AS QuantityShipped ,
        ISNULL(C.QTYINVCD, 0) AS QuantityInvoiced ,
        ISNULL(C.QTYREJ, 0) AS QuantityRejected ,
        ISNULL(C.QTYMATCH, 0) AS QuantityMatch ,
        ISNULL(C.QTYRESERVED, 0) AS QuantityReserved ,
        CASE C.POPTYPE
           WHEN 1 THEN 'SHIPMENT'
           WHEN 2 THEN 'INVOICE'
           WHEN 3 THEN 'SHIPMENT/INVOICE'
           ELSE ' '
        END AS DocumentType ,
        ISNULL(C.UOFM, ' ') AS UOFM,
        ISNULL(C.DATERECD ,'') AS DateReceived,
        ISNULL(C.PCHRPTCT, 0) AS PURCHASERECEIPTCOST ,
        ISNULL(D.ORUNTCST, 0) AS ORGRECUNITCOST ,
        ISNULL(D.OREXTCST, 0) AS ORGRECCOST
        FROM   dbo.POP10100 AS A
        INNER JOIN dbo.POP10110 AS B ON A.[PONUMBER] = B.[PONUMBER]
        LEFT OUTER JOIN dbo.POP10500 AS C ON B.[PONUMBER] = C.[PONUMBER]
                                         AND B.[ORD] = C.[POLNENUM]
        LEFT OUTER JOIN dbo.POP30310 AS D ON C.[PONUMBER] = D.[PONUMBER]
                                         AND C.[RCPTLNNM] = D.[RCPTLNNM]
                                         AND C.POPRCTNM = D.POPRCTNM
        LEFT OUTER JOIN dbo.POP30300 AS E ON D.[POPRCTNM] = E.[POPRCTNM]
Suggested answer

Redbeard 12,931 on at

Like (0)

Report

RE: Seed Bag Returns - Purchasing - Returns Transaction Entry

Trey -

I am glad you like the view. I am certain with a little tweaking you can get where you want to be. Here is a sample of what this query might look like modified to provide the Receipt Number and Vendor Document Number that resulted in the Lot.

/*

CREATE View [dbo].[_Lots_Summary_with_Receipts]

as

*/

with CTE_Receipts

as

(

select POP10330.POPRCTNM,ITEMNMBR,SERLTNUM,VNDDOCNM from POP10330

left join POP10300 on POP10330.POPRCTNM = POP10300.POPRCTNM

Union All

select POP30330.POPRCTNM,ITEMNMBR,SERLTNUM,VNDDOCNM from POP30330

left join POP30300 on POP30330.POPRCTNM = POP30300.POPRCTNM

)

select distinct LOTS.[ITEMNMBR] AS Item_Number,

ITEMS.[ITEMDESC] AS Item_Description,

LOTS.[LOTNUMBR] AS Lot_Number,

sum(LOTS.[QTYRECVD]) AS QTY_Received,

sum(LOTS.[QTYSOLD]) AS QTY_Sold,

sum(LOTS.[ATYALLOC]) AS QTY_Allocated,

LOTS.[LOCNCODE] AS Location_Code,

sum(LOTS.[QTYRECVD]-LOTS.[QTYSOLD]-LOTS.[ATYALLOC]) AS QTY_ON_HAND,

coalesce(CTE_Receipts.POPRCTNM,'Not A Receipt') as Receipt_Number,

coalesce(CTE_Receipts.VNDDOCNM,'Not A Receipt') as Vendor_Doc_Number

-- The QTY_ON_HAND column is the net, or what should be returned to vendor

-- Teh Receipt_Number the Return should go against

-- The Vendor Document Number (not a required field) is the Vendor Doc Number on the Receivings Transaciton Entry Screen and should be an invoice or packing slip #

from IV00300 as LOTS

inner join IV00101 as items on LOTS.ITEMNMBR = ITEMS.ITEMNMBR

left join PM00200 as VEND on LOTS.VNDRNMBR = VEND.VENDORID

Left Join CTE_Receipts on LOTS.ITEMNMBR = CTE_Receipts.ITEMNMBR and LOTS.LOTNUMBR = CTE_Receipts.SERLTNUM

group by LOTNUMBR,LOTS.ITEMNMBR,ITEMDESC,LOTS.LOCNCODE,CTE_Receipts.POPRCTNM,CTE_Receipts.VNDDOCNM

go

Grant Select on _Lots_Summary to DYNGRP
Community Member Microsoft Employee on at

Like (0)

Report

RE: Seed Bag Returns - Purchasing - Returns Transaction Entry

We were able to create the view by removing the aliases. This view is great. If it had the POs and actual quantities remaining (not just $ remaining) it would be perfect for the user as they would know exactly which POs have quantities to return instead of having to search through POs and return types to find which have quantities remaining/are unused and can be returned w/ credit. Again, I cannot say thank you enough for how much you have helped with this!
Suggested answer

Redbeard 12,931 on at

Like (1)

Report

RE: Seed Bag Returns - Purchasing - Returns Transaction Entry

Trey -

Try dropping the Distinct from the query, and/or changing the inner join into a Left Join between tables IV00300 (lots) and IV00101 (items)

Not sure if this will fix it, but it might make it run. If all else fails, please send me an email and we can set up a quick session to get this working for you.
Community Member Microsoft Employee on at

Like (1)

Report

RE: Seed Bag Returns - Purchasing - Returns Transaction Entry

We are running the script in GP 2013. When attempting to run it as a select it errors out for the same/similar reason(s):

SQL Error: 4104 [Microsoft][SQL Server Native Client 10.0][SQL Server]The multi-part identifier "ITEMS.ITEMNMBR" could not be bound.

When we start to peel away the parts that are causing the error we are just deconstructing your view and rendering it useless. I really appreciate you help with this. We might attempt to modify a report to get the same results.