View - dbo.arch_PurchaseOrderHeader

Property	Value
Name	arch_PurchaseOrderHeader
Schema	dbo
Quoted Identifier Status	True
Ansi Nulls Status	True
Returns View Metadata	False
Create Date	10/2/2015 3:05 PM
Description

Column Name	Data Type	Allow Nulls	Fulltext Indexed	Identity	Description / Computed Formula
AddProgram	varchar(8)	True	False	False
AddressType	varchar(1)	True	False	False
AddUserId	varchar(8)	True	False	False
AttachmentInd	varchar(1)	True	False	False
BID_NBR	varchar(10)	True	False	False
BidDate	date(10, 0)	True	False	False
BillToAddressCode	varchar(2)	True	False	False
BillToAddrLine1	varchar(40)	True	False	False
BillToAddrLine2	varchar(40)	True	False	False
BillToAddrLine3	varchar(40)	True	False	False
BillToAddrLine4	varchar(40)	True	False	False
BillToAddrLine5	varchar(40)	True	False	False
BlanketBeginDate	date(10, 0)	True	False	False
BlanketEndDate	date(10, 0)	True	False	False
BlanketMaxAmount	numeric(17, 2)	False	False	False
BoardDate	date(10, 0)	True	False	False
Building	varchar(4)	True	False	False
BuyerId	varchar(3)	True	False	False
BuyerName	varchar(25)	True	False	False
CalculatedTotalAmount	numeric(17, 2)	False	False	False
CancelledActionCode	varchar(2)	True	False	False
ContactFaxNumber	varchar(10)	True	False	False
ContactName	varchar(40)	True	False	False
ContactPhone	varchar(14)	True	False	False
ContactPhoneExt	varchar(5)	True	False	False
ContractNumber	varchar(16)	True	False	False
DeliverRoomNumber	varchar(6)	True	False	False
DeliverToName	varchar(40)	True	False	False
DeliveryDate	date(10, 0)	True	False	False
DeliveryFacilityName	varchar(50)	True	False	False
DeliveryLocation	varchar(50)	True	False	False
DeliveryLocationNumber	varchar(4)	True	False	False
EmailId	varchar(40)	True	False	False
EncumberedBalances	numeric(17, 2)	False	False	False
EncumbranceTotalAmount	numeric(17, 2)	False	False	False
FinStatus	varchar(15)	True	False	False
FinStatusCode	varchar(2)	True	False	False
FiscalYear	varchar(4)	True	False	False
FobTerms	varchar(3)	True	False	False
FreightAmount	numeric(17, 2)	False	False	False
FreightItemNumber	int(10, 0)	True	False	False
FreightPrePaidInd	varchar(1)	True	False	False
FromReqReferenceNumber	varchar(12)	True	False	False
GLTotalAdjustedAmount	numeric(17, 2)	False	False	False
MaintDateTime	datetime(23, 3)	True	False	False
MaintProgram	varchar(8)	True	False	False
MaintUserId	varchar(8)	True	False	False
MultiplePrqInd	varchar(1)	True	False	False
NotesForBuyer	varchar(1)	True	False	False
NotesForVendor	varchar(1)	True	False	False
PaidTotalAmount	numeric(17, 2)	False	False	False
PaymentTerms	varchar(25)	True	False	False
PaymentTermsCode	varchar(2)	True	False	False
PayStatus	varchar(15)	False	False	False
PayStatusCode	varchar(2)	True	False	False
PoCreateDate	datetime(23, 3)	True	False	False
PoFreeText	varchar(92)	True	False	False
PoSigCode	varchar(2)	True	False	False
PostedJournalNumber	varchar(15)	True	False	False
PrevRefInfo	varchar(12)	True	False	False
PrintDate	date(10, 0)	True	False	False
ProjectNumber	varchar(12)	True	False	False
PurchaseOrderNumber	varchar(8)	True	False	False
QuoteIndicator	varchar(1)	True	False	False
ReceivingStatus	varchar(15)	False	False	False
ReceivingStatusCode	varchar(2)	True	False	False
ReferenceType	varchar(30)	True	False	False
ReferenceTypeCode	varchar(2)	True	False	False
RequestorName	varchar(40)	True	False	False
ShipToAddrLine1	varchar(40)	True	False	False
ShipToAddrLine2	varchar(40)	True	False	False
ShipToAddrLine3	varchar(40)	True	False	False
ShipToAddrLine4	varchar(40)	True	False	False
ShipToAddrLine5	varchar(40)	True	False	False
ShipToCode	varchar(2)	True	False	False
SiteLocalNumber	varchar(1)	True	False	False
Status	varchar(15)	True	False	False
StatusCode	varchar(2)	True	False	False
SuggestedVendor	varchar(40)	True	False	False
Tolerance	varchar(30)	True	False	False
ToleranceInd	varchar(2)	True	False	False
TotalAccruedAmount	numeric(17, 2)	False	False	False
TotalOriginalPoAmount	numeric(17, 2)	False	False	False
VendorAddressSequence	varchar(4)	True	False	False
VendorAttention	varchar(64)	True	False	False
VendorCityStateZip	varchar(64)	True	False	False
VendorName	varchar(64)	True	False	False
VendorNumber	varchar(9)	True	False	False
VendorPhone	varchar(64)	True	False	False
VendorPoBox	varchar(64)	True	False	False
VendorStreet	varchar(64)	True	False	False
VendorSuite	varchar(64)	True	False	False
YearPurchaseOrderNumber	varchar(12)	True	False	False

Name

Insert

Update

Delete

Instead

Name

Clustered

Columns

Name	Type
FAC199_LOCATION_A	Table
FAC203_FACILITY_A	Table
FIP_PO_HDR	Table
FIP_PO_HDR_BILL_TO_ADDR_LINE	Table
FIP_PO_HDR_PO_FREE_TEXT	Table
FIP_PO_HDR_SHIP_TO_ADDRESS	Table
FIP_PO_HDR_VENDOR_ADDRESS	Table
FIP_TABLES	Table
UTL_CODE_TABLE	Table

Name	Type
FAC199_LOCATION_A	TABLE
FAC203_FACILITY_A	TABLE
FIP_PO_HDR	TABLE
FIP_PO_HDR_BILL_TO_ADDR_LINE	TABLE
FIP_PO_HDR_PO_FREE_TEXT	TABLE
FIP_PO_HDR_SHIP_TO_ADDRESS	TABLE
FIP_PO_HDR_VENDOR_ADDRESS	TABLE
FIP_TABLES	TABLE
UTL_CODE_TABLE	TABLE

Object

Property

Value

Object

Property

Value

/****** Object: View [dbo].[arch_PurchaseOrderHeader] Script Date: 03/09/2017 16:47:09 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE VIEW [dbo].[arch_PurchaseOrderHeader]
AS
SELECT
   PO.FISCAL_YEAR + PO.REF_NBR AS YearPurchaseOrderNumber
  ,PO.FISCAL_YEAR AS FiscalYear
  ,PO.REF_NBR AS PurchaseOrderNumber
  ,PO.REF_TYPE AS ReferenceTypeCode
  ,RefTypesDesc.DESCRIPTION AS ReferenceType
  ,PO.STATUS AS StatusCode
  ,StatusDesc.STATUS_DESC AS Status
  ,PO.FIN_STATUS AS FinStatusCode
  ,FinStatusDesc.STATUS_DESC AS FinStatus
  ,SUBSTRING(PO.HDR_VENDOR_ADDR, 1, 9) AS VendorNumber
  ,SUBSTRING(PO.HDR_VENDOR_ADDR, 10, 4) AS VendorAddressSequence
  ,PO.VENDOR_ADDR_TYPE AS AddressType
  ,PO.BUYER_ID AS BuyerId
  ,BuyerName.BUYER_NAME AS BuyerName
  ,CONVERT(DATE,PO.PO_PRINT_DATE) AS PrintDate
  ,CONVERT(DATE,PO.DELIVER_DATE) AS DeliveryDate
  ,PO.PAYMENT_TERMS AS PaymentTermsCode
  ,FipTab.PAYMENT_TERMS_DESC AS PaymentTerms
  ,PO.FOB_TERMS AS FobTerms
  ,PO.TOLERANCE_FLAG AS ToleranceInd
  ,PoTol.DESCRIPTION AS Tolerance
  ,ISNULL(PO.CALC_TOT_AMT, 0) AS CalculatedTotalAmount
  ,ISNULL(PO.BALANCE_TOT_AMT, 0) AS EncumberedBalances
  ,ISNULL(PO.PAID_TOT_AMT, 0) AS PaidTotalAmount
  ,ISNULL(PO.GL_TOT_ADJUST_AMT, 0) AS GLTotalAdjustedAmount
  ,ISNULL(PO.TOTAL_ORG_PO_AMT, 0) AS TotalOriginalPoAmount
  ,ISNULL(PO.BLANKET_MAX_AMT, 0) AS BlanketMaxAmount
  ,CONVERT(DATE, PO.BLANKET_BEGIN_DATE) AS BlanketBeginDate
  ,CONVERT(DATE, PO.BLANKET_END_DATE) AS BlanketEndDate
  ,VendAddr1.VENDOR_ADDRESS AS VendorName
  ,VendAddr2.VENDOR_ADDRESS AS VendorAttention
  ,VendAddr3.VENDOR_ADDRESS AS VendorSuite
  ,VendAddr4.VENDOR_ADDRESS AS VendorPoBox
  ,VendAddr5.VENDOR_ADDRESS AS VendorStreet
  ,VendAddr6.VENDOR_ADDRESS AS VendorCityStateZip
  ,VendAddr7.VENDOR_ADDRESS AS VendorPhone
  ,BillAddr1.BILL_TO_ADDR_LINE AS BillToAddrLine1
  ,BillAddr2.BILL_TO_ADDR_LINE AS BillToAddrLine2
  ,BillAddr3.BILL_TO_ADDR_LINE AS BillToAddrLine3
  ,BillAddr4.BILL_TO_ADDR_LINE AS BillToAddrLine4
  ,BillAddr5.BILL_TO_ADDR_LINE AS BillToAddrLine5
  ,ShipAddr1.SHIP_TO_ADDRESS AS ShipToAddrLine1
  ,ShipAddr2.SHIP_TO_ADDRESS AS ShipToAddrLine2
  ,ShipAddr3.SHIP_TO_ADDRESS AS ShipToAddrLine3
  ,ShipAddr4.SHIP_TO_ADDRESS AS ShipToAddrLine4
  ,ShipAddr5.SHIP_TO_ADDRESS AS ShipToAddrLine5
  ,CASE
     WHEN Text1.PO_FREE_TEXT IS NULL THEN NULL
     ELSE Text1.PO_FREE_TEXT + ' ' + ISNULL(Text2.PO_FREE_TEXT, ' ') + ' ' + ISNULL(Text3.PO_FREE_TEXT, ' ')
   END AS PoFreeText
  ,PO.REQUESTOR_NAME AS RequestorName
  ,PO.E_MAIL_ID AS EmailId
  ,PO.CONTACT_NAME AS ContactName
  ,CASE
     WHEN SUBSTRING(PO.CONTACT_PHONE, 1, 3) = ' ' THEN SUBSTRING(PO.CONTACT_PHONE, 4, 3)  + '-' + SUBSTRING(PO.CONTACT_PHONE, 7, 4)
     ELSE '(' + SUBSTRING(PO.CONTACT_PHONE, 1, 3) + ') ' + SUBSTRING(PO.CONTACT_PHONE, 4, 3)  + '-' + SUBSTRING(PO.CONTACT_PHONE, 7, 4)
   END AS ContactPhone
  ,SUBSTRING(PO.CONTACT_PHONE, 11, 5)  AS ContactPhoneExt
  ,PO.DELIVER_TO_NAME AS DeliverToName
  ,PO.BUILDING AS Building
  ,PO.CONTACT_ROOM AS DeliverRoomNumber
  ,PO.SITE_LOCAL_NUM AS SiteLocalNumber
  ,PO.LOCATION_NUM AS DeliveryLocationNumber
  ,Loc.LOCATION_NAME AS DeliveryLocation
  ,Build.FACILITY_NAME AS DeliveryFacilityName
  ,PO.SHIP_TO_CODE AS ShipToCode
  ,PO.SUGGESTED_VENDOR AS SuggestedVendor
  ,PO.CANCELLED_ACTION_CODE AS CancelledActionCode
  ,PO.REC_STATUS AS ReceivingStatusCode
  ,ISNULL(RecStatDesc.STATUS_DESC,'N/A') AS ReceivingStatus
  ,PO.PAY_STATUS AS PayStatusCode
  ,ISNULL(PayStatDesc.STATUS_DESC, 'N/A') AS PayStatus
  ,CASE
     WHEN PO.FROM_PRQ_FISCAL_YEAR IS NOT NULL THEN PO.FROM_PRQ_FISCAL_YEAR + PO.FROM_PRQ_REF_NBR
     ELSE NULL
   END AS FromReqReferenceNumber
  ,PO.MULTIPLE_PRQ_IND AS MultiplePrqInd
  ,PO.NOTES_FOR_VENDOR AS NotesForVendor
  ,PO.NOTES_FOR_BUYER AS NotesForBuyer
  ,PO.ATTACHMENT_IND AS AttachmentInd
  ,PO.CONTRACT_NBR AS ContractNumber
  ,PO.PROJECT_NBR AS ProjectNumber
  ,CASE
     WHEN ISDATE(PO.BOARD_DATE) = 1 THEN CONVERT(DATE, PO.BOARD_DATE)
     ELSE NULL
   END AS BoardDate
  ,PO.BID_NBR
  ,CASE
     WHEN ISDATE(PO.BID_DATE) = 1 THEN CONVERT(DATE, PO.BID_DATE)
     ELSE NULL
   END AS BidDate
  ,ISNULL(PO.FREIGHT_AMT, 0) AS FreightAmount
  ,PO.FREIGHT_PRE_PAY_ADD AS FreightPrePaidInd
  ,PO.FREIGHT_ITM_NBR AS FreightItemNumber
  ,PO.BILL_TO_ADDR_CODE AS BillToAddressCode
  ,PO.PREV_PO_REF_INFO AS PrevRefInfo
  ,ISNULL(PO.ENC_TOT_AMT, 0) AS EncumbranceTotalAmount
  ,ISNULL(PO.TOTAL_AMT_ACCRUED, 0)  AS TotalAccruedAmount
  ,PO.PO_SIG_CODE AS PoSigCode
  ,PO.POSTED_PRD_TR_JRN_TYPE AS PostedJournalNumber
  ,PO.CONTACT_FAX_NBR AS ContactFaxNumber
  ,PO.QUOTE_INDICATOR AS QuoteIndicator
  ,CASE
     WHEN ISDATE(PO.ADD_DATE) = 0 THEN '1900-01-01 00:00:00.000'
     WHEN PO.ADD_TIME IS NULL THEN CONVERT(DATETIME, CONVERT(CHAR(8), PO.ADD_DATE) + ' '
              + '00:00:00.000')
     ELSE CONVERT(DATETIME, CONVERT(CHAR(8), PO.ADD_DATE) + ' '
            + STUFF(STUFF(STUFF(RIGHT('0000000' + cast(Po.ADD_TIME as varchar(7)),7),3,0,':'),6,0,':'),9,0,'.'))
   END AS PoCreateDate
  ,PO.ADD_USER_ID AS AddUserId
  ,PO.ADD_PGM AS AddProgram
  ,CASE
     WHEN ISDATE(PO.ADD_DATE) = 0 AND ISDATE(PO.MAINT_DATE) = 0 THEN '1900-01-01 00:00:00.000'
     WHEN ISDATE(PO.MAINT_DATE) = 0 AND PO.ADD_TIME IS NULL THEN CONVERT(DATETIME, CONVERT(CHAR(8), PO.ADD_DATE) + ' '
                + '00:00:00.000')
     WHEN ISDATE(PO.MAINT_DATE) = 0 AND PO.MAINT_TIME IS NULL THEN CONVERT(DATETIME, CONVERT(CHAR(8), PO.ADD_DATE) + ' '
              + STUFF(STUFF(STUFF(RIGHT('0000000' + cast(PO.ADD_TIME as varchar(7)),7),3,0,':'),6,0,':'),9,0,'.'))
     WHEN ISDATE(PO.MAINT_DATE) = 1 AND PO.MAINT_TIME IS NULL THEN CONVERT(DATETIME, CONVERT(CHAR(8), PO.MAINT_DATE) + ' '
               + '00:00:00.000')
     ELSE CONVERT(DATETIME, CONVERT(CHAR(8), PO.MAINT_DATE) + ' '
                + STUFF(STUFF(STUFF(RIGHT('0000000' + cast(PO.MAINT_TIME as varchar(7)),7),3,0,':'),6,0,':'),9,0,'.'))
   END AS         MaintDateTime
  ,PO.MAINT_USER_ID AS MaintUserId
  ,PO.MAINT_PGM AS MaintProgram
FROM FIP_PO_HDR  PO
LEFT OUTER JOIN FIP_PO_HDR_PO_FREE_TEXT Text1
ON
(
   PO.ISN_FIP_PO_HDR = Text1.ISN_FIP_PO_HDR
   AND Text1.cnxarraycolumn = 0
)
LEFT OUTER JOIN FIP_PO_HDR_PO_FREE_TEXT Text2
ON
(
   PO.ISN_FIP_PO_HDR = Text2.ISN_FIP_PO_HDR
   AND Text2.cnxarraycolumn = 1
)
LEFT OUTER JOIN FIP_PO_HDR_PO_FREE_TEXT Text3
ON
(
   PO.ISN_FIP_PO_HDR = Text3.ISN_FIP_PO_HDR
   AND Text3.cnxarraycolumn = 2
)
LEFT OUTER JOIN FIP_PO_HDR_BILL_TO_ADDR_LINE BillAddr1
ON
(
   PO.ISN_FIP_PO_HDR = BillAddr1.ISN_FIP_PO_HDR
   AND BillAddr1.cnxarraycolumn = 0
)
LEFT OUTER JOIN FIP_PO_HDR_BILL_TO_ADDR_LINE BillAddr2
ON
(
   PO.ISN_FIP_PO_HDR = BillAddr2.ISN_FIP_PO_HDR
   AND BillAddr2.cnxarraycolumn = 1
)
LEFT OUTER JOIN FIP_PO_HDR_BILL_TO_ADDR_LINE BillAddr3
ON
(
   PO.ISN_FIP_PO_HDR = BillAddr3.ISN_FIP_PO_HDR
   AND BillAddr3.cnxarraycolumn = 2
)
LEFT OUTER JOIN FIP_PO_HDR_BILL_TO_ADDR_LINE BillAddr4
ON
(
   PO.ISN_FIP_PO_HDR = BillAddr4.ISN_FIP_PO_HDR
   AND BillAddr4.cnxarraycolumn = 3
)
LEFT OUTER JOIN FIP_PO_HDR_BILL_TO_ADDR_LINE BillAddr5
ON
(
   PO.ISN_FIP_PO_HDR = BillAddr5.ISN_FIP_PO_HDR
   AND BillAddr5.cnxarraycolumn = 4
)
LEFT OUTER JOIN FIP_PO_HDR_VENDOR_ADDRESS VendAddr1
ON
(
   PO.ISN_FIP_PO_HDR = VendAddr1.ISN_FIP_PO_HDR
   AND VendAddr1.cnxarraycolumn = 0
)
LEFT OUTER JOIN FIP_PO_HDR_VENDOR_ADDRESS VendAddr2
ON
(
   PO.ISN_FIP_PO_HDR = VendAddr2.ISN_FIP_PO_HDR
   AND VendAddr2.cnxarraycolumn = 1
)
LEFT OUTER JOIN FIP_PO_HDR_VENDOR_ADDRESS VendAddr3
ON
(
   PO.ISN_FIP_PO_HDR = VendAddr3.ISN_FIP_PO_HDR
   AND VendAddr3.cnxarraycolumn = 2
)
LEFT OUTER JOIN FIP_PO_HDR_VENDOR_ADDRESS VendAddr4
ON
(
   PO.ISN_FIP_PO_HDR = VendAddr4.ISN_FIP_PO_HDR
   AND VendAddr4.cnxarraycolumn = 3
)
LEFT OUTER JOIN FIP_PO_HDR_VENDOR_ADDRESS VendAddr5
ON
(
   PO.ISN_FIP_PO_HDR = VendAddr5.ISN_FIP_PO_HDR
   AND VendAddr5.cnxarraycolumn = 4
)
LEFT OUTER JOIN FIP_PO_HDR_VENDOR_ADDRESS VendAddr6
ON
(
   PO.ISN_FIP_PO_HDR = VendAddr6.ISN_FIP_PO_HDR
   AND VendAddr6.cnxarraycolumn = 5
)
LEFT OUTER JOIN FIP_PO_HDR_VENDOR_ADDRESS VendAddr7
ON
(
   PO.ISN_FIP_PO_HDR = VendAddr7.ISN_FIP_PO_HDR
   AND VendAddr7.cnxarraycolumn = 6
)
LEFT OUTER JOIN FIP_PO_HDR_SHIP_TO_ADDRESS ShipAddr1
ON
(
   PO.ISN_FIP_PO_HDR = ShipAddr1.ISN_FIP_PO_HDR
   AND ShipAddr1.cnxarraycolumn = 0
)
LEFT OUTER JOIN FIP_PO_HDR_SHIP_TO_ADDRESS ShipAddr2
ON
(
   PO.ISN_FIP_PO_HDR = ShipAddr2.ISN_FIP_PO_HDR
   AND ShipAddr2.cnxarraycolumn = 1
)
LEFT OUTER JOIN FIP_PO_HDR_SHIP_TO_ADDRESS ShipAddr3
ON
(
   PO.ISN_FIP_PO_HDR = ShipAddr3.ISN_FIP_PO_HDR
   AND ShipAddr3.cnxarraycolumn = 2
)
LEFT OUTER JOIN FIP_PO_HDR_SHIP_TO_ADDRESS ShipAddr4
ON
(
   PO.ISN_FIP_PO_HDR = ShipAddr4.ISN_FIP_PO_HDR
   AND ShipAddr4.cnxarraycolumn = 3
)
LEFT OUTER JOIN FIP_PO_HDR_SHIP_TO_ADDRESS ShipAddr5
ON
(
   PO.ISN_FIP_PO_HDR = ShipAddr5.ISN_FIP_PO_HDR
   AND ShipAddr5.cnxarraycolumn = 4
)
LEFT OUTER JOIN UTL_CODE_TABLE RefTypesDesc
   ON (
       RefTypesDesc.TABLE_NAME = 'REF-TYPES'
            AND RefTypesDesc.CODE = PO.REF_TYPE
            AND RefTypesDesc.STATUS = 'A'
       )

LEFT OUTER JOIN FIP_TABLES StatusDesc
   ON (
       StatusDesc.STATUS_CODE = PO.STATUS
       )
LEFT OUTER JOIN FIP_TABLES FinStatusDesc
   ON (
       FinStatusDesc.STATUS_CODE = PO.FIN_STATUS
       )

LEFT OUTER JOIN FIP_TABLES RecStatDesc
   ON (
       RecStatDesc.STATUS_CODE = PO.REC_STATUS
       )

LEFT OUTER JOIN FIP_TABLES PayStatDesc
   ON (
       PayStatDesc.STATUS_CODE = PO.PAY_STATUS
       )
LEFT OUTER JOIN FIP_TABLES BuyerName
   ON (
       BuyerName.BUYER_ID = PO.BUYER_ID
       )
LEFT OUTER JOIN UTL_CODE_TABLE PoTol
ON
(
   PoTol.TABLE_NAME = 'TOLERANCE'
   AND PO.TOLERANCE_FLAG = PoTol.CODE
   AND PoTol.STATUS = 'A'
)
LEFT OUTER JOIN FIP_TABLES FipTab
ON
(
    FipTab.PAYMENT_TERMS_CODE = PO.PAYMENT_TERMS
    AND FipTab.TABLE_STATUS = 'A'
)
LEFT OUTER JOIN FAC203_FACILITY_A Fac
ON
(
    PO.SITE_LOCAL_NUM = Fac.SITE_LOCAL_NUM
    AND PO.LOCATION_NUM = Fac.LOCATION_NUM
    AND PO.BUILDING = Fac.FACILITY_LOCAL_NUM
)
LEFT OUTER JOIN FAC203_FACILITY_A Build
ON
(
   Build.SITE_NUM = Fac.SITE_LOCAL_NUM
   AND Build.FACILITY_NUM = Fac.FACILITY_NUM
  AND Build.FACILITY_NAME IS NOT NULL
)
LEFT OUTER JOIN FAC199_LOCATION_A Loc
ON
(
   PO.SITE_LOCAL_NUM = Loc.SITE_LOCAL_NUM
   AND PO.LOCATION_NUM = Loc.LOCATION_NUM
)