CREATE TABLE [dbo].[al_purchase_order]
(
[purchase_order_id] [int] NOT NULL,
[purchase_order_number] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[purchase_order_date] [datetime] NULL,
[comments] [nvarchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[signature] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ship_to_overide_flag] [int] NULL,
[ship_to_contact_uuid] [binary] (16) NULL,
[ship_to_location_address] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[billing_overide_flag] [int] NULL,
[billing_location_uuid] [binary] (16) NULL,
[billing_address] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[reseller_overide_flag] [int] NULL,
[reseller_uuid] [binary] (16) NULL,
[reseller_address] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[total_amount] [float] NULL,
[total_tax_amount] [float] NULL,
[sub_total_amount] [float] NULL,
[currency_type_id] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
[assets_created] [int] NULL CONSTRAINT [DF__al_purcha__asset__7E5C7E91] DEFAULT ((0)),
[purchase_order_status_id] [int] NOT NULL,
[creation_user] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[creation_date] [int] NULL,
[last_update_user] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[last_update_date] [int] NULL,
[version_number] [int] NULL CONSTRAINT [DF__al_purcha__versi__7F50A2CA] DEFAULT ((0)),
[buyer_contact_uuid] [binary] (16) NULL,
[terms] [nvarchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[date_required] [datetime] NULL,
[shipment_type_id] [int] NULL,
[billing_company_uuid] [binary] (16) NULL
) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE TRIGGER al_c_purchordr_tr ON dbo.al_purchase_order
AFTER INSERT
AS
BEGIN

    DECLARE @purchaseorderid int
    DECLARE @creationuser nvarchar(255)
    DECLARE @creationdate int
    DECLARE @lastupdateuser nvarchar(255)
    DECLARE @lastupdatedate int

    SELECT  @purchaseorderid = purchase_order_id,
            @creationuser = creation_user,
            @creationdate = creation_date,
            @lastupdateuser = last_update_user,
            @lastupdatedate = last_update_date
    FROM INSERTED

    INSERT INTO al_purchase_order_flags
    (purchase_order_id, from_uapm, creation_user, creation_date, last_update_user, last_update_date,  version_number)
    VALUES
    (@purchaseorderid, 0, @creationuser,@creationdate, @lastupdateuser, @lastupdatedate, 0)
END

GO
CREATE TRIGGER al_d_purchordr_tr ON dbo.al_purchase_order
INSTEAD OF DELETE
AS
BEGIN

    DECLARE @purchaseorderid int

    SELECT  @purchaseorderid = purchase_order_id
    FROM DELETED

    DELETE FROM al_purchase_order_flags WHERE purchase_order_id = @purchaseorderid
    DELETE FROM al_purchase_order WHERE purchase_order_id = @purchaseorderid
END

GO
ALTER TABLE [dbo].[al_purchase_order] ADD CONSTRAINT [PK_al_purchase_order] PRIMARY KEY CLUSTERED ([purchase_order_id]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[al_purchase_order] ADD CONSTRAINT [al_purchase_order_fk01] FOREIGN KEY ([currency_type_id]) REFERENCES [dbo].[ca_currency_type] ([currency_type_code])
GO
ALTER TABLE [dbo].[al_purchase_order] ADD CONSTRAINT [al_purchase_order_fk02] FOREIGN KEY ([purchase_order_status_id]) REFERENCES [dbo].[al_purchase_order_status] ([id])
GO
ALTER TABLE [dbo].[al_purchase_order] ADD CONSTRAINT [al_purchase_order_fk03] FOREIGN KEY ([ship_to_contact_uuid]) REFERENCES [dbo].[ca_contact] ([contact_uuid])
GO
ALTER TABLE [dbo].[al_purchase_order] ADD CONSTRAINT [al_purchase_order_fk04] FOREIGN KEY ([billing_location_uuid]) REFERENCES [dbo].[ca_location] ([location_uuid])
GO
ALTER TABLE [dbo].[al_purchase_order] ADD CONSTRAINT [al_purchase_order_fk05] FOREIGN KEY ([reseller_uuid]) REFERENCES [dbo].[ca_company] ([company_uuid])
GO
ALTER TABLE [dbo].[al_purchase_order] ADD CONSTRAINT [al_purchase_order_fk07] FOREIGN KEY ([buyer_contact_uuid]) REFERENCES [dbo].[ca_contact] ([contact_uuid])
GO
ALTER TABLE [dbo].[al_purchase_order] ADD CONSTRAINT [al_purchase_order_fk08] FOREIGN KEY ([billing_company_uuid]) REFERENCES [dbo].[ca_company] ([company_uuid])
GO
ALTER TABLE [dbo].[al_purchase_order] ADD CONSTRAINT [al_purchase_order_fk09] FOREIGN KEY ([shipment_type_id]) REFERENCES [dbo].[al_shipment_type] ([id])
GO
GRANT SELECT ON  [dbo].[al_purchase_order] TO [swcmadmin]
GRANT INSERT ON  [dbo].[al_purchase_order] TO [swcmadmin]
GRANT DELETE ON  [dbo].[al_purchase_order] TO [swcmadmin]
GRANT UPDATE ON  [dbo].[al_purchase_order] TO [swcmadmin]
GRANT SELECT ON  [dbo].[al_purchase_order] TO [uapmadmin]
GRANT INSERT ON  [dbo].[al_purchase_order] TO [uapmadmin]
GRANT DELETE ON  [dbo].[al_purchase_order] TO [uapmadmin]
GRANT UPDATE ON  [dbo].[al_purchase_order] TO [uapmadmin]
GRANT SELECT ON  [dbo].[al_purchase_order] TO [uapmadmin_group]
GRANT INSERT ON  [dbo].[al_purchase_order] TO [uapmadmin_group]
GRANT DELETE ON  [dbo].[al_purchase_order] TO [uapmadmin_group]
GRANT UPDATE ON  [dbo].[al_purchase_order] TO [uapmadmin_group]
GRANT SELECT ON  [dbo].[al_purchase_order] TO [uapmbatch]
GRANT INSERT ON  [dbo].[al_purchase_order] TO [uapmbatch]
GRANT DELETE ON  [dbo].[al_purchase_order] TO [uapmbatch]
GRANT UPDATE ON  [dbo].[al_purchase_order] TO [uapmbatch]
GRANT SELECT ON  [dbo].[al_purchase_order] TO [uapmbatch_group]
GRANT INSERT ON  [dbo].[al_purchase_order] TO [uapmbatch_group]
GRANT DELETE ON  [dbo].[al_purchase_order] TO [uapmbatch_group]
GRANT UPDATE ON  [dbo].[al_purchase_order] TO [uapmbatch_group]
GRANT SELECT ON  [dbo].[al_purchase_order] TO [uapmreporting]
GRANT INSERT ON  [dbo].[al_purchase_order] TO [uapmreporting]
GRANT DELETE ON  [dbo].[al_purchase_order] TO [uapmreporting]
GRANT UPDATE ON  [dbo].[al_purchase_order] TO [uapmreporting]
GRANT SELECT ON  [dbo].[al_purchase_order] TO [uapmreporting_group]
GRANT INSERT ON  [dbo].[al_purchase_order] TO [uapmreporting_group]
GRANT DELETE ON  [dbo].[al_purchase_order] TO [uapmreporting_group]
GRANT UPDATE ON  [dbo].[al_purchase_order] TO [uapmreporting_group]
GO