Follow us on |
IDEAS PORTAL

Blanket Agreement Fulfillment Report Sugestions

Since I've been working on our Blanket Agreements I came up with a couple of wish list items for the fulfillment report. I love that it can be exported into Excel, as that is the format my co-workers prefer to see it in. But what I would like to see is a combination of the expanded and compact formats so that when I exported into excel all the data for each contract was on one line. I've attached a picture of what my spreadsheet ends up looking like. I also wish the fulfillment report would show me if an agreement was set as the default or not. 

 

Thanks!

Ashley

  • Ashley Anderson
  • Aug 11 2017
  • Added to Roadmap
  • Attach files
  • Ashley Anderson commented
    25 Aug, 2017 11:40pm

    This is perfect, thanks Conner! 

  • Conner Helton commented
    15 Aug, 2017 06:27pm

    Hey Ashley,

    Would you mind giving this query a go and letting me know what if that's what you're looking for?


    Cheers!

    Conner

    /* Query to provide an exportable result set of Open BP Agreements */

    SELECT DH.Number as 'Agreement No.'
    , DH.BpCode as 'Customer/Vendor Code'
    , DH.BpName as 'BP Name'
    , DH.StartDate
    , DH.EndDate
    , CASE DH.Status
    WHEN 'A' THEN 'Approved'
    WHEN 'D' THEN 'Draft'
    WHEN 'F' THEN 'On Hold'
    WHEN 'T' THEN 'Terminated'
    END as 'Status'
    , CASE WHEN BP.DflAgrmnt = DH.AbsID
    THEN 'Yes'
    ELSE 'No'
    END AS 'Default Agreement'
    , DL.ItemCode as 'Item No'
    , DL.ItemName as 'Item Description'
    , DL.UnitPrice
    , DL.PlanQty
    , DL.CumQty
    , DL.PlanQty - DL.CumQty as 'Open Qty'
    , DL.Currency + ' ' + CONVERT(VARCHAR(100),CAST(DL.CumAmntLC as money),1) as 'Cumulative Amount'
    , DL.Currency + ' ' + CONVERT(VARCHAR(100), CAST((DL.PlanQty - DL.CumQty) * DL.UnitPrice as money),1) as 'Open Amt'

    FROM OOAT DH
    INNER JOIN OAT1 DL on DH.AbsID = DL.AgrNo
    INNER JOIN OCRD BP on DH.BpCode = BP.CardCode

    /* Remove (T)erminated BP agreements & cancelled agreements from the data */

    WHERE DH.Status <> 'T' AND DH.Cancelled <> 'Y'