Back to Home

Aging Inventory Report

Table of Contents

• Overview
• Dropdown
• Open Inventory
• Closed Inventory
• Stored Procedure
• Views and Functions
• Tables
• Additional Information

Overview

1. Ability to view Intake Forms' flow in Open or Closed Inventory Stages
2. Ability to check multiple Inspection Types from checkbox list
3. Ability to sort by any column
4. Ability to sort by Start and End Date within 30 day date range in Closed Inventory.

Dropdown

Bootsrap styling used to create dropdown to select multiple checklists.

• Ability to select All
• Reset button - unselect all
• Choose specfic Inspection Types with checkbox selection

Open Inventory

Description: Stages shown from "New" to "ReCheck"

Calculations: Not including holidays and weekends.

• Days In Stage: (DateSubmitted + getDate()) / 24.0
• Hours in Stage: DateSubmitted + getDate()

Open Inventory Tab Image

Closed Inventory

Description:

• Stages shown from "Closed" to "Rebuttal Final". Dates filtered by DateClosed
• Date Range can be filtered between a maximum of 30 days only to keep the report from being too long. A date range more than 30 days will trigger custom validator - "Cannot be more than 30 days".

Calculations: Not including holidays and weekends.

• Complete Initial: From "Stage- 1st Level Review" to "Stage- Closed"

  • Complete Initial = (DateReceived - DateClosed or DateStarted) / 24.0

  • Avg Complete Days = sum Complete Initial column / # of rows

  • Only calculates when Audit goes directly from 1st QR Review to Closed, or 1st BO Review, QR Recheck then to Closed.

• FeedBack Loop: From "Stage- 1st Level Review" to "Stage- Closed"

  • FeedBack Loop = (DateStarted + DateClosed) / 24.0
  • Avg FeedBack Loop = sum Feedback Loop column / # of rows

• Total Days: Length calculated from "Stage- New" to "Stage- Closed"

  • Total Days = (DateReceived + DateClosed) / 24.0
  • Avg Total Days = sum Total Days column / # of rows

Closed Inventory Tab Image

Stored Procedure

• [smart].[p11_Read_Inventory]

  • Options: 'OpenInventory' & 'ClosedInventory'

• [smart].[p11_Read_IntakeForm_All]

  • Options: 'Open' & 'Closed'

Views and Functions

• [smart].p11_qry_IntakeForm_Short
• [smart].p11_f_GetHourDiffExceptHoliday

Tables

• [smart].p11_AuditActivity
• [smart].p11_IntakeForm_PlanCount
• [smart].p11_TeamMember
• [smart].p11_UserList
• [smart].p11_Team
• [smart].p11_CodeList
• [smart].p11_IntakeForm

Additional Information

TransactionEffectiveDate is coming from the feed in Core_CEM dbo.CEM_IntakeForm, column CASE_EF_DT GSO (Group Sales Office) column will come from Verint. Placeholder for now.

##

FieldTypeNo is Count Type (LookUpCategory = Count)

SELECT * FROM smart.p11_CodeList WHERE LookupCategory = 'Count'

Below example is selecting the FieldTypeNo's 6,8

• Document Type
• Document Count

SELECT [Count]
FROM smart.p11_IntakeForm_Count
WHERE UniqueInstanceNo = a.UniqueInstanceNo
    AND FieldTypeNo IN (6,8) AND Deleted = 0) AS DocumentCount