Manufacturing, Distribution, and Internet Applications

[trodman]

Does anyone know what transaction type AM means in the tmfMRPDetail_HAI table? MRP is suggesting a purchase order, but I can't find where the demand is coming from.

[MCarlise]

I believe AM is for Project Accouting (don't ask me why it is AM and not PA). Take a look at your estimates that have material items. The TranID in tmfMRPDetail_HAI is the project ID followed by a number. Also if in MRP you go to the Transaction Detail tab you will see the demand.

I am still looking into this, but I think you'll find the demand is on the estimate, if not please let me know.

[trodman]

Thanks Mari. I think you are right. It references a project number and line number in the tPA00125 table. The estimate line matches the ItemKey, but the quantity doesn't match. Also, this particular estimate line is a variant line. I wouldn't think that MRP would look at variant lines.

[MCarlise]

I see that you are a programmer - which I am NOT! the code is in spfmMRPInitInsert. It looks like it is treating the 'AM' like 'WM' (material issues) and not looking to see if it is a variance. It is only looking at tpa00125 and tpa00175. Take a look at the code, I am sure you will understand it better than me.

Thanks,

[trodman]

Thanks Mari for posting that procedure.  I've opened a case with Sage.  We'll see how it goes.

I was able to confirm that this is an estimate line marked as variant with this code:

SELECT tPA00125.*
FROM tPA00125
	JOIN tPA00175 ON tPA00125.intJobKey=tPA00175.intJobKey
WHERE tPA00175.chrJobNumber='0000015289' AND numLineSeqNumber=457

 But I'm not sure what this piece of code is doing in the spfmMRPInitInsert procedure:

-- Fixed Price Detail (tPA00125)
   INSERT INTO #tmfNetReqDetlWrk (ItemKey, WhseKey, TranId, 
                                  TranType, TranQty, NetReqDate,
                                  OnHandBalance, CompanyId, SessionId, TranUOMKey)
   SELECT pad.ItemKey, nr.WhseKey, CONVERT(VARCHAR(11), pah.chrJobNumber) + '-' + CONVERT(VARCHAR, CAST(pad.numLineSeqNumber AS int)),
          'AM', (pad.NumEstimatedQty + SumTable.TotalQty) * -1, ISNULL(ISNULL(pad.dteSchedStartDate, pad.dteActualStartDate), pah.dteJobCommencement), 
          0, @iCompanyId, @iSessionId, pad.UnitMeasKey
     FROM tPA00175 pah WITH (NOLOCK)
     JOIN tPA00125 pad WITH (NOLOCK)
       ON (pah.intJobKey = pad.IntJobKey)
     JOIN timItem itm WITH (NOLOCK)
       ON (pad.ItemKey = itm.ItemKey)
     JOIN tarCustomer cust WITH (NOLOCK)
       ON (pah.CustKey = cust.CustKey)
     JOIN tarCustAddr cadr WITH (NOLOCK)
       ON (cust.CustKey = cadr.CustKey AND
		   cust.PrimaryAddrKey = cadr.AddrKey)
     JOIN #tmfNetReqHeadWrk nr
       ON (pad.ItemKey = nr.ItemKey)
     LEFT JOIN (SELECT J.intJobKey, D.intJobLineKey, COALESCE(SUM(R.TranQty),0) AS TotalQty
                FROM tPA00175 J
                LEFT JOIN tPA00125 D WITH (NOLOCK)
                  ON D.intJobKey = J.intJobKey
                LEFT JOIN tPA00100 T WITH (NOLOCK)
                  ON D.intJobLineKey = T.intJobLineKey
                LEFT JOIN timInvtTran R WITH (NOLOCK) 
                  ON T.intRelatedKey = R.InvtTranKey
               WHERE D.ItemKey IS NOT NULL
	             AND (J.siJobStatus < 4 AND 
                      J.siJobStatus > 1)
                 AND J.CompanyID = @iCompanyId
	           GROUP BY J.intJobKey, D.intJobLineKey, D.ItemKey) AS SumTable
       ON (pad.intJobKey = SumTable.intJobKey AND 
           pad.intJobLineKey = SumTable.intJobLineKey)
    WHERE (pad.NumEstimatedQty + SumTable.TotalQty) > 0
      AND (@iCutoffDate >= ISNULL(ISNULL(pad.dteSchedStartDate, pad.dteActualStartDate), pah.dteJobCommencement) OR
           @iCutoffDate IS NULL OR
           @iCutoffDate = '')
	  AND (pah.siJobStatus > 1 AND
	       pah.siJobStatus < 4)				                    -- Not Closed
	  AND  nr.SessionId = @iSessionId
	  AND  ISNULL(nr.Refresh, 0) = 0
      AND (((itm.DfltWhseKey = nr.WhseKey OR					-- Use Item Default Warehouse if exists
            (ISNULL(@iConsolidateWhse,0) = 1 AND
	         itm.DfltWhseKey IN (SELECT wdt.AssocWhseKey
                                   FROM tmfWhseDeflt_HAI wdt
                                  WHERE wdt.WhseKey = nr.WhseKey))) AND
             itm.DfltWhseKey IS NOT NULL) OR
           ((cadr.WhseKey = nr.WhseKey OR					    -- IF Not Customer Closest Warehouse
            (ISNULL(@iConsolidateWhse,0) = 1 AND
             cadr.WhseKey IN (SELECT wdt.AssocWhseKey
                                FROM tmfWhseDeflt_HAI wdt
                               WHERE wdt.WhseKey = nr.WhseKey))) AND
             itm.DfltWhseKey IS NULL AND
             cadr.WhseKey IS NOT NULL)) 

[trodman]

I opened a case (#4830858).  The two open questions are:

1. How is MRP supposed to interact with Project Accounting?

2. Should this section of code in spmfMRPInitInsert ...

LEFT JOIN timInvtTran R WITH (NOLOCK)
  ON T.intRelatedKey = R.InvtTranKey

... actually be ...

LEFT JOIN timInvtTran R WITH (NOLOCK)
  ON T.intRelatedKey = R.InvtTranKey AND t.siWhichLinkTable=3

... since otherwise it pulls inventory transactions if any tPA00100.intRelatedKey happens to match a timInvtTran.InvtTranKey.

[trodman · Accepted solution]

I got some answers to my questions from Sage support:

1. MRP can look at project estimate lines for demand.  The warehouse used will be the "Default Warehouse" setup in "Maintain Items" if one exists.  Otherwise the "Closest Warehouse" on the address record setup in "Maintain Customers" for the customer assigned to the project.

2. The code is fine as is.  I am having a problem because we have a customization which allows us to process inventory items against projects.  The standard behavior is to only allow non-inventory items to be processed against projects.  Because this is the standard behavior, there is no need to have MRP filter out variant lines.

My solution is going to be to comment out the section of code in spmfMRPInitInsert that pertains to project accounting since we are not relying on it.