/
PO Notes Audit

PO Notes Audit

Owned by Kaitlyn Niemann (Unlicensed), created
Last updated: Apr 22, 2024 by Albert Saludaga
2 min read

Requirements and Purpose

Report to provide a list of recently po'd items, who po'd them, the current notes, and allow for search functions.

Report Definition

  1. Stakeholders
    @Kaitlyn Niemann (Unlicensed)

    1. Samantha Fortenberry

  2. Author

    1. @Kaitlyn Niemann (Unlicensed)

  3. Subject matter expert(s)

    1. Samantha Fortenberry

  4. Presentation Medium

    1. SSRS

  5. Fields (data)

    1. PO_#(int)

    2. Inventory_Reference_Number(varchar)

    3. PO'd  by(varchar)

    4. PO Date(datetime)

    5. PO Total(decimal)

    6. cards(varchar)

    7. ShippingMethodName(varchar)

    8. eName(varchar)

    9. VenueName(varchar)

    10. eDate(datetime)

    11. InHandDate(tinyInt)

    12. IsPDFAvailable(tinyInt)

    13. InternalNote(varchar)

    14. ExternalNote(varchar)

    15. StandardizedNoteName(varchar)

    16. NearTermOptionName(varchar)

    17. nconc(int)

Data source(s)

The data source for this report is the Eibo-215 Database. Using the stored procedure sr_GetPONotesAudit.

  1. eiboxoffice.dbo

    1. BuyOrder

    2. Client

    3. BuyOrderTicketGroup

    4. OrderPayment

    5. TicketGroup

    6. Users

    7. ShippingMethod

    8. StandardizedNote

    9. NearTermOption

    10. Event

    11. Venue

    12. Region

Calculations

DataSet Name

Calculation

Description

DataSet Name

Calculation

Description

CTE1

SELECT CONCAT(h.eventname,o.eventname,v.venuename,(tg.eventdate+ISNULL(tg.eventtime,0))) conc, CASE WHEN COUNT(DISTINCT CONCAT(tg.internalnote, tg.description, tg.StandardizedNoteID,tg.NearTermOptionID,tg.InHandDate,tg.IsPDFAvailable)) = 1 THEN 1 ELSE 2 END nconc FROM TicketGroup tg LEFT JOIN Event h ON tg.PrimaryEventID = h.EventID LEFT JOIN Event o ON tg.SecondaryEventID = o.EventID LEFT JOIN Venue v ON tg.VenueID = v.VenueID WHERE tg.CreatedDateTime BETWEEN @StartDate AND @EndDate GROUP BY h.EventName, o.EventName, v.VenueName, tg.EventDate, tg.EventTime

nconc generator

CTE2

SELECT DISTINCT b.PONumber PO_#, CASE WHEN U.fIRSTNAME = U.LASTNAME THEN U.FirstName ELSE CONCAT(u.FirstName,' ',u.Lastname) end 'PO''d by', b.Totalamount PO_Total, c.firstname Buying_Account, ISNULL(LTRIM(RTRIM(LEFT(op.nameoncard,CHARINDEX(' - ',op.NameOnCard)))),'Accounts Payable') Card_Used, sm.ShippingMethodName, tg.InternalNote, tg.Description ExternalNote, sn.StandardizedNoteName, nt.NearTermOptionName, tg.InHandDate, tg.IsPDFAvailable, b.ExternalReferenceNumber Inventory_Reference_Number, b.finalizeddatetime PO_Date, CASE WHEN tg.SecondaryEventID = 1 THEN h.EventName ELSE CONCAT(h.EventName,'/',o.EventName) END eName, v.VenueName, tg.EventDate+ISNULL(tg.EventTime,0) edate, CASE WHEN CHARINDEX(ISNULL(@event,h.eventname),h.eventname)+CHARINDEX(ISNULL(@event,o.eventname),o.eventname) > 0 THEN 1 ELSE 0 END sc, CASE WHEN CHARINDEX(ISNULL(@venue,v.venuename),v.venuename)+CHARINDEX(ISNULL(@venue,r.RegionName),r.RegionName)+CHARINDEX(ISNULL(@venue,v.City),v.City) > 0 THEN 1 ELSE 0 END vc, CONCAT(h.eventname,o.eventname,v.venuename,(tg.eventdate+ISNULL(tg.eventtime,0))) conc FROM BUYORDER b LEFT JOIN Client c on b.ClientID = c.ClientID LEFT JOIN BuyOrderTicketGroup bt on b.BuyOrderID = bt.BuyOrderID LEFT JOIN OrderPayment op ON b.buyorderid = op.orderid AND op.NameOnCard != '' LEFT JOIN TicketGroup tg ON bt.BuyOrderTicketGroupGUID = tg.TicketGroupGUID LEFT JOIN Users u ON b.CreatedUserID = u.UserID LEFT JOIN ShippingMethod sm ON b.ShippingMethodID = sm.ShippingMethodID LEFT JOIN StandardizedNote sn ON tg.StandardizedNoteID = sn.StandardizedNoteID LEFT JOIN NearTermOption nt ON tg.NearTermOptionID = nt.NearTermOptionID LEFT JOIN Event h ON tg.PrimaryEventID = h.EventID LEFT JOIN Event o ON tg.SecondaryEventID = o.EventID LEFT JOIN Venue v ON tg.VenueID = v.VenueID LEFT JOIN Region r ON v.RegionID = r.RegionID WHERE tg.CreatedDateTime BETWEEN @StartDate AND @EndDate

Returns detailed po info

nconc

=CASE
WHEN COUNT(DISTINCT CONCAT(tg.internalnote, tg.description, tg.StandardizedNoteID,tg.NearTermOptionID,tg.InHandDate,tg.IsPDFAvailable)) = 1
THEN 1
ELSE 2
END nconc

Determines number of unique event lines on po.

Parameters

  1. Filters

    1. @event

      1. text

      2. can be blank

    2. @venue

      1. text

      2. can be blank

    3. @startdate

      1. datetime

      2. defines start of po date range

    4. @end

      1. datetime

      2. defines end of po date range

    5. @po

      1. int

      2. searches po number

    6. @pricer

      1. text

      2. searches user who po'd

    7. @status

      1. int

        1. Single=1

        2. Multiple = 2

        3. All = 0

  2. Grouping

    1. PO_#

  3. Intentions for printing vs. online viewing

    1. The report is delivered via SSRS 

    2. This report can be accessed here: SSRS → Asset → Newly PO'd

  4. Intended delivery mechanism (run on demand vs. delivered by subscription)

    1. On Demand

  5. Future maintenance tasks or manually maintained items

    1. None

Results

  1. Data Accuracy

    1. QA

      1. Validate against PO Report

        1. Verify data points against eibo PO

  2. Limitations

    1. Report shortfalls

Change Log

  1. https://ticketboat.atlassian.net/browse/SP-259

Images



Related content