Contracts define our companies’ obligations and expectations, and it is crucial that we maintain complete archives of contracts, whether they are still in force or have expired. But retrieving a contract can be surprisingly difficult, especially when it has been saved to a separate repository, removed from its original context.

Here, we’ll show you an easy way to add contracts—even complex ones with multiple attachments—to purchase orders, and how to quickly view a complete list of all contracts that pertain to a given vendor.

First, you’ll add a contract and associated files to a purchase order. This elastic window allows you to enter a contract’s title and any dates associated with its provisions, and to attach files as needed. The dates you enter can be used to create SmartLists and alerts that remind you when the contract comes into force and when it expires, and when contractual provisions requiring your review are about to kick in.

Viewing the contracts associated with a given vendor is quick and easy. This elastic window presents every contract associated with the vendor you name. The information provided through this window is not editable. To make changes to a contract file, or to add or remove attachments, you will need to identify the purchase order to which the contract file was attached, and use its Purchase Order Entry window to alter the contract record.

The information provided in this elastic window can also be accessed through a SQL view.

— ================================================
— TitaniumGP

— Sample script

— This block of comments will not be included in
— the definition of the procedure.
— ================================================
— Author:      TitaniumGP
— Create date: 20 Jan 2020
— Description: Create a Contract definition in 
—              the PO Entry window and a view 
—              to the contracts in Vendor Mnt.
— =============================================
CREATE TABLE [dbo].[adv_PurchaseOrderEntry_Contract]
  (
     [PONUMBER]        [CHAR](17) NOT NULL,
     [Contract Title]  [VARCHAR](100) NULL,
     [Service Type]    [VARCHAR](55) NULL,
     [Contract Start]  [DATETIME] NULL,
     [Contract End]    [DATETIME] NULL,
     [Expiration]      [DATETIME] NULL,
     [Alert]           [DATETIME] NULL,
     [FileAttachments] [VARCHAR](10) NULL,
     [RowTS]           [DATETIME] NOT NULL,
     [RowID]           [INT] IDENTITY(1, 1) NOT NULL,
     CONSTRAINT [PK_adv_PurchaseOrderEntry_Contract_VENDORID] PRIMARY KEY
     CLUSTERED ( [PONUMBER] ASC )WITH ( pad_index = OFF, statistics_norecompute
     = OFF, ignore_dup_key = OFF, allow_row_locks = on, allow_page_locks = on )
     ON [PRIMARY]
  )
ON [PRIMARY]

go

ALTER TABLE [dbo].[adv_PurchaseOrderEntry_Contract]
  ADD CONSTRAINT [DF_adv_PurchaseOrderEntry_Contract_RowTS] DEFAULT (Getdate())
  FOR [RowTS]

go

EXEC sys.sp_addextendedproperty
  @name=N’ComboValues’,
  @value=N’Type 1 |Type 2 |Type 3′,
  @level0type=N’SCHEMA’,
  @level0name=N’dbo’,
  @level1type=N’TABLE’,
  @level1name=N’adv_PurchaseOrderEntry_Contract’,
  @level2type=N’COLUMN’,
  @level2name=N’Service Type’

go

EXEC sys.sp_addextendedproperty
  @name=N’CtrlType’,
  @value=N’COMBOBOX’,
  @level0type=N’SCHEMA’,
  @level0name=N’dbo’,
  @level1type=N’TABLE’,
  @level1name=N’adv_PurchaseOrderEntry_Contract’,
  @level2type=N’COLUMN’,
  @level2name=N’Service Type’

go

EXEC sys.sp_addextendedproperty
  @name=N’CtrlType’,
  @value=N’FileAttachments’,
  @level0type=N’SCHEMA’,
  @level0name=N’dbo’,
  @level1type=N’TABLE’,
  @level1name=N’adv_PurchaseOrderEntry_Contract’,
  @level2type=N’COLUMN’,
  @level2name=N’FileAttachments’

go

— View to show contracts from the Purchase Order Window
CREATE VIEW adv_vendormaintenance_contracts
AS
  SELECT VENDORID,
         Space(10) ContractPOs,
         Space(10) Documents
  FROM   PM00200

go

GRANT SELECT ON adv_vendormaintenance_contracts TO dyngrp

go

EXEC sys.sp_addextendedproperty
  @name=N’CtrlType’,
  @value=N’GridView’,
  @level0type=N’SCHEMA’,
  @level0name=N’dbo’,
  @level1type=N’VIEW’,
  @level1name=N’adv_VendorMaintenance_Contracts’,
  @level2type=N’COLUMN’,
  @level2name=N’ContractPOs’

go

EXEC sys.sp_addextendedproperty
  @name=N’CtrlType’,
  @value=N’FileAttachments’,
  @level0type=N’SCHEMA’,
  @level0name=N’dbo’,
  @level1type=N’VIEW’,
  @level1name=N’adv_VendorMaintenance_Contracts’,
  @level2type=N’COLUMN’,
  @level2name=N’Documents’

go

EXEC sys.sp_addextendedproperty
  @name=N’SQLScript’,
  @value=
N’SELECT [Contract Title], [Service Type], [Contract Start], [Contract End], Expiration, Alert FROM POP10100 P JOIN adv_PurchaseOrderEntry_Contract A ON P.PONUMBER = A.PONUMBER WHERE P.VENDORID = @Key1′
,
@level0type=N’SCHEMA’,
@level0name=N’dbo’,
@level1type=N’VIEW’,
@level1name=N’adv_VendorMaintenance_Contracts’,
@level2type=N’COLUMN’,
@level2name=N’ContractPOs’

go

EXEC sys.sp_addextendedproperty
  @name=N’TopLeftWidthHeightMid’,
  @value=N’25,1,200,250,25′,
  @level0type=N’SCHEMA’,
  @level0name=N’dbo’,
  @level1type=N’VIEW’,
  @level1name=N’adv_VendorMaintenance_Contracts’,
  @level2type=N’COLUMN’,
  @level2name=N’ContractPOs’

go

EXEC sys.sp_addextendedproperty
  @name=N’MS_Description’,
  @value=N’Contracts for this vendor entered in the PO Window`.’,
  @level0type=N’SCHEMA’,
  @level0name=N’dbo’,
  @level1type=N’VIEW’,
  @level1name=N’adv_VendorMaintenance_Contracts’

go