KNOWN ISSUE: Purchase Request, Purchase Order, and Invoice reports timing out and wants to keep references in report.

Products

• Asset Management Solution

Issue/Introduction

When trying to view Receiving Resource Types are being accessed they are getting a SQL time out.

Cause

The large amount of Receiving Resources and the process it goes through to resolve the reference columns in the report are taking a long time an causing the time outs.

Resolution

Here are the steps that you need to follow to resolve the issue:

-----------------------------
--Step 1 - create SQL objects
-----------------------------

IF EXISTS (SELECT 1 FROM sysobjects so WHERE so.name=N'ReceivingItemsHierarchy' AND so.type=N'U')
BEGIN
 DROP TABLE ReceivingItemsHierarchy
END
GO

CREATE TABLE dbo.ReceivingItemsHierarchy
(
 spid int NOT NULL,
 _ResourceGuid uniqueidentifier NOT NULL,
 AncestorGuid uniqueidentifier NOT NULL,
 Depth int NOT NULL
)
GO

CREATE CLUSTERED INDEX ReceivingItemsHierarchy_spid ON dbo.ReceivingItemsHierarchy (spid, _ResourceGuid)
GO

IF EXISTS (SELECT 1 FROM sysobjects so WHERE so.name=N'fnGetLinkedReceivingItemAsString_new' AND so.type=N'FN')
BEGIN
 DROP FUNCTION fnGetLinkedReceivingItemAsString_new
END
GO

CREATE FUNCTION dbo.fnGetLinkedReceivingItemAsString_new (
 @ChildItemGuid uniqueidentifier,
 @ReceivingTypeGuid uniqueidentifier
)
RETURNS nvarchar(255)
AS
BEGIN
 declare @PRstring nvarchar(255)
 select @PRstring = ''
 select @PRstring = case when len(@PRstring) = 0 then dbo.fnLocalizeStringByGuid('item.name', ReferenceGuid, '%_culture%')
  else dbo.fnLocalizeStringByGuid('item.name', ReferenceGuid, '%_culture%')  + ',...' end
 from
 (
  select distinct AncestorGuid as ReferenceGuid
  from  ReceivingItemsHierarchy rih
   join vResource vr on vr.Guid=rih.AncestorGuid
   join vResourceHierarchy rh on rh.ResourceTypeGuid = vr.ResourceTypeGuid
  where  rih._ResourceGuid = @ChildItemGuid
   and BaseResourceTypeGuid = @ReceivingTypeGuid
   and rih.spid = @@spid

  UNION

  select distinct rih._ResourceGuid
  from ReceivingItemsHierarchy rih
   join vResource vr on vr.Guid=rih._ResourceGuid
   join vResourceHierarchy rh on rh.ResourceTypeGuid = vr.ResourceTypeGuid
  where  rih.AncestorGuid = @ChildItemGuid
   and BaseResourceTypeGuid = @ReceivingTypeGuid
   and rih.spid = @@spid
 ) a
 return @PRstring
END
GO
-----------------------------
-- Step 2 - using sql below create report through NS console. 
-----------------------------
 
declare @curLevel int
set @curLevel = 0

DELETE FROM ReceivingItemsHierarchy WHERE spid = @@spid

INSERT INTO ReceivingItemsHierarchy
SELECT DISTINCT @@spid, _ResourceGuid, _ResourceGuid, 0 FROM Inv_Line_Items
INSERT INTO ReceivingItemsHierarchy
SELECT DISTINCT @@spid, _ResourceGuid, _ResourceGuid, 0 FROM Inv_Received_Line_Items

WHILE @curLevel < 5  -- we limit the search up 5 levels
BEGIN
 INSERT INTO ReceivingItemsHierarchy
 SELECT DISTINCT @@spid, r._ResourceGuid, li._ResourceGuid, @curLevel + 1 FROM Inv_Parent_Lines pl
 join Inv_Line_Items li ON li.[Line Item ID] = pl.[Parent Line Item ID]
 join ReceivingItemsHierarchy  r on r.AncestorGuid = pl._ResourceGuid and r.Depth = @curLevel and r.spid = @@spid
 SELECT @curLevel = @curLevel + 1
END

SELECT I.Guid AS _ResourceGuid,
 IPOD.[Receiving Slip Number],
 SUM(ILIP.Quantity) as [Number of Received Items],
 sum(isnull(ILIP.Quantity,0) * (isnull(ILIP.[Unit Cost],0) + isnull(ILIP.[Tax Amount],0)) ) as [Total Cost],
 isnull(reqTab.Name,'') as Requestor,
 I.Guid,
 dbo.fnGetLinkedReceivingItemAsString_new(I.Guid,'06c97c09-f96d-4260-a119-061396a67eb9') as [PR Reference],
 dbo.fnGetLinkedReceivingItemAsString_new(I.Guid,'DF81E731-09AB-4391-B2D9-3B16E9C4AA86') as [PO Reference],
 dbo.fnGetLinkedReceivingItemAsString_new(I.Guid,'22538EED-17FE-42cd-889D-949A15E3CF03') as [Invoice Reference],
 I.CreatedBy, I.CreatedDate, I.ModifiedBy, I.ModifiedDate
FROM  vResource v
 INNER JOIN Item I ON v.Guid = I.Guid
 JOIN ResourceTypeHierarchy rth on rth.ResourceTypeGuid = v.ResourceTypeGuid
  AND rth.BaseResourceTypeGuid = 'DF2F98F8-B408-4776-88E0-5418D917E833' -- Receiving Slip
 left join Inv_Received_Line_Items ILIP ON I.[Guid] = ILIP.[_ResourceGuid]
 left JOIN Inv_Receiving_Slip_Details IPOD ON IPOD._ResourceGuid = I.Guid
 left outer join
 (
  SELECT  r._ResourceGuid, vi.Name as Name
  FROM  fnRx_GetReceivingItemsHierarchy() r
   JOIN ResourceAssociation raReq on r.AncestorGuid = raReq.ParentResourceGuid
    AND raReq.ResourceAssociationTypeGuid = 'fd0a2540-c373-45c9-95c8-4e8177bf295b' -- Requestor
   JOIN vItem vi on vi.Guid = raReq.ChildResourceGuid
 ) reqTab ON reqTab._ResourceGuid = I.Guid
WHERE  lower(I.Name) like lower('%')
 AND lower(isnull(reqTab.Name,'')) like lower('%')
 AND (I.CreatedDate >= getdate()-1200 AND I.CreatedDate <= DateAdd(d,1,getdate()))
GROUP BY I.Guid, IPOD.[Receiving Slip Number],I.CreatedBy, I.CreatedDate, I.ModifiedBy, I.ModifiedDate, reqTab.Name
ORDER BY I.ModifiedDate desc, IPOD.[Receiving Slip Number] desc

Applies To
Asset Management 6.5