Stored Procedure - usp_FactPurchaseOrderAmounts

Property	Value
Name	usp_FactPurchaseOrderAmounts_Select
Schema	dbo
Is Encrypted	False
Ansi Nulls Status	True
Quoted Identifier Status	True
Description

Name	Data Type	Direction	Description
@IncrementalBeginDtTm	datetime(23, 3)	Input
@IncrementalEndDtTm	datetime(23, 3)	Input

Name	Type
FIP_CO_HDR_A	Table
FIP_PO_DET	Table
FIP_PO_HDR	Table
FIP_PO_HDR_VENDOR_ADDRESS	Table

Name	Type
FIP_CO_HDR_A	TABLE
FIP_PO_DET	TABLE
FIP_PO_HDR	TABLE
FIP_PO_HDR_VENDOR_ADDRESS	TABLE

Object

Property

Value

Object

Property

Value

/****** Object: StoredProcedure [dbo].[usp_FactPurchaseOrderAmounts_Select] Script Date: 03/09/2017 16:47:10 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
/*
Name:            usp_FactPurchaseOrderAmounts_Select
Title:            Primary Select for the Purchase Order Amounts Fact
Date:            11/1/2012
System/Project:    Integrated Financials
Description:    This procedure will combine the Purchase Order information from DWStaging for
the creation of the FactPurchaseOrderAmounts.
Revision History:
09/17/2013 Sophia Cowan        Created
01/29/2016 Sophia Cowan Added ISNUMERIC code for ZipCountyAK column
*/
CREATE PROC [dbo].[usp_FactPurchaseOrderAmounts_Select]
@IncrementalBeginDtTm datetime, /* Date and Time to validate against RowUpdatedOn in Source Tables */
@IncrementalEndDtTm datetime
AS
SET NOCOUNT ON
IF 2=3
    BEGIN
    SELECT

       CAST('aaa' as varchar(12)) as PurchaseOrderAK
      ,CAST('1900-01-01' AS DateTime) AS EntryDate
      ,CAST('1900-01-01' AS DateTime) AS FiscalYearDate
      ,CAST('aaa' AS VARCHAR(14)) as PayeeAK
      ,CAST('aaa' AS VARCHAR(5)) AS ZipCountyAK
      ,CAST('0.00' AS NUMERIC(11,2)) AS PurchaseOrderAmount
      ,CAST('0.00' AS NUMERIC(11,2)) AS EncumberedAmount
      ,CAST('0.00' AS NUMERIC(11,2)) AS PaidAmount
      ,CAST(0 as INT) AS NumberOfItems
       ,CAST(0 AS INT) AS NumberOfChangeOrders
     END

CREATE TABLE #NumItems
(
     NumItems int NOT NULL
    ,DetYearRefNbr VARCHAR(12) NOT NULL
  )
CREATE TABLE #NumChangeOrders
(
     NumChangeOrders int NOT NULL
    ,DetYearRefNbr VARCHAR(12) NOT NULL
  )
INSERT INTO #NumItems
(
     NumItems
    ,DetYearRefNbr
  )
  SELECT
     COUNT(DET_YEAR_REF_NBR)
    ,DET_YEAR_REF_NBR from FIP_PO_DET
    GROUP BY DET_YEAR_REF_NBR
INSERT INTO #NumChangeOrders
(
     NumChangeOrders
    ,DetYearRefNbr
  )
  SELECT
    COUNT(CHG_ORD_NBR)
   ,CAST(FISCAL_YEAR + REF_NBR AS VARCHAR(12)) AS FiscalYearCoRefNbr
   from FIP_CO_HDR_A
   GROUP BY FISCAL_YEAR, REF_NBR
SELECT
       CAST(Po.FISCAL_YEAR + Po.REF_NBR as varchar(12)) as PurchaseOrderAK
      ,CAST(Po.ADD_DATE AS DateTime) AS EntryDate
      ,CAST(Po.FISCAL_YEAR + '01' + '01' AS DateTime) AS FiscalYearDate
      ,CAST('V' + HDR_VENDOR_ADDR AS VARCHAR(14)) as PayeeAK
      ,CASE
         WHEN Va.VENDOR_ADDRESS = ' ' THEN -1
         WHEN ISNUMERIC(LEFT(REVERSE(SUBSTRING(REVERSE(Va.VENDOR_ADDRESS),1,CHARINDEX(' ',REVERSE(Va.VENDOR_ADDRESS)) -1)),5)) = 1
           THEN LEFT(REVERSE(SUBSTRING(REVERSE(Va.VENDOR_ADDRESS),1,CHARINDEX(' ',REVERSE(Va.VENDOR_ADDRESS)) -1)),5)
         ELSE -1
       END AS ZipCountyAK
      ,CAST(
        CASE
           WHEN Po.STATUS <> 'CA' THEN ISNULL(Po.CALC_TOT_AMT,0)
           ELSE 0
        END AS NUMERIC(11,2)) AS PurchaseOrderAmount
      ,CAST(ISNULL(Po.BALANCE_TOT_AMT,0) AS NUMERIC(11,2)) AS EncumberedAmount
      ,CAST(ISNULL(Po.PAID_TOT_AMT,0) AS NUMERIC(11,2)) AS PaidAmount
      ,ISNULL(Det.NumItems,0) AS NumberOfItems
      ,ISNULL(Co.NumChangeOrders,0) AS NumberOfChangeOrders
FROM FIP_PO_HDR Po
LEFT OUTER JOIN FIP_PO_HDR_VENDOR_ADDRESS Va
ON
  (
     Po.ISN_FIP_PO_HDR = Va.ISN_FIP_PO_HDR
     AND
     Va.cnxarraycolumn = 5
  )
LEFT OUTER JOIN #NumItems Det
ON
(
    LEFT(Det.DetYearRefNbr,4) = Po.FISCAL_YEAR
    AND RIGHT(Det.DetYearRefNbr,8) = Po.REF_NBR
)

LEFT OUTER JOIN #NumChangeOrders Co
ON
  (
    Po.FISCAL_YEAR = LEFT(Co.DetYearRefNbr,4)
    AND Po.REF_NBR = RIGHT(Co.DetYearRefNbr,8)
  )
WHERE (CAST(Po.FISCAL_YEAR + Po.REF_NBR as varchar(12))) IN
(
/* Select changed records from FIP PO HDR File */
SELECT (CAST(FISCAL_YEAR + REF_NBR as varchar(12)))
FROM FIP_PO_HDR
WHERE (
       FIP_PO_HDR.RowUpdatedOn >= @IncrementalBeginDtTm
       AND FIP_PO_HDR.RowUpdatedOn <= @IncrementalEndDtTm
      )
)