CREATE TABLE [dbo].[al_purchase_order_item]
(
[purchase_order_item_id] [int] NOT NULL,
[purchase_order_id] [int] NOT NULL,
[request_item_id] [int] NULL,
[request_id] [int] NULL,
[model_uuid] [binary] (16) NULL,
[item_description] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[item_quantity] [int] NULL,
[item_unit_price] [float] NULL,
[item_tax] [float] NULL,
[item_total_amount] [float] NULL,
[part_number] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[sku_number] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS 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__06F1C492] DEFAULT ((0)),
[line_number] [int] NULL,
[asset_status_id] [int] NULL,
[project_number] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[order_confirm_number] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[catalog_number] [int] NULL,
[gl_code_id] [int] NULL,
[cost_center_id] [int] NULL
) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER OFF
GO

/***** Create al_prchordr_totscalc_trd *****/
CREATE TRIGGER al_prchordr_totscalc_trd ON al_purchase_order_item
AFTER UPDATE, DELETE
AS
BEGIN
      DECLARE @total_amount float
      DECLARE @total_tax_amount float
      DECLARE @sub_total_amount float
      DECLARE @purchaseorderid float

      SELECT
      @purchaseorderid = purchase_order_id
      FROM deleted
      
      SELECT      @total_tax_amount = SUM(item_tax),
            @sub_total_amount = SUM(item_total_amount) - SUM(item_tax),
            @total_amount = SUM(item_total_amount)
      FROM al_purchase_order_item
      WHERE purchase_order_id = @purchaseorderid

      UPDATE      al_purchase_order
      SET   total_tax_amount = @total_tax_amount,
            sub_total_amount = @sub_total_amount,
            total_amount = @total_amount
      WHERE       purchase_order_id = @purchaseorderid
      
END

GO

/***** Create al_prchordr_totscalc_tri *****/
CREATE TRIGGER al_prchordr_totscalc_tri ON al_purchase_order_item
AFTER INSERT
AS
BEGIN
      DECLARE @total_amount float
      DECLARE @total_tax_amount float
      DECLARE @sub_total_amount float
      DECLARE @purchaseorderid float

      SELECT
      @purchaseorderid = purchase_order_id
      FROM inserted
      
      SELECT      @total_tax_amount = SUM(item_tax),
            @sub_total_amount = SUM(item_total_amount) - SUM(item_tax),
            @total_amount = SUM(item_total_amount)
      FROM al_purchase_order_item
      WHERE purchase_order_id = @purchaseorderid

      UPDATE      al_purchase_order
      SET   total_tax_amount = @total_tax_amount,
            sub_total_amount = @sub_total_amount,
            total_amount = @total_amount
      WHERE       purchase_order_id = @purchaseorderid
      
END

GO
ALTER TABLE [dbo].[al_purchase_order_item] ADD CONSTRAINT [PK_al_purchase_order_item] PRIMARY KEY CLUSTERED ([purchase_order_item_id]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[al_purchase_order_item] ADD CONSTRAINT [al_purchase_order_item_fk01] FOREIGN KEY ([purchase_order_id]) REFERENCES [dbo].[al_purchase_order] ([purchase_order_id])
GO
ALTER TABLE [dbo].[al_purchase_order_item] ADD CONSTRAINT [al_purchase_order_item_fk02] FOREIGN KEY ([model_uuid]) REFERENCES [dbo].[ca_model_def] ([model_uuid])
GO
ALTER TABLE [dbo].[al_purchase_order_item] ADD CONSTRAINT [al_purchase_order_item_fk03] FOREIGN KEY ([asset_status_id]) REFERENCES [dbo].[al_po_item_asset_status] ([id])
GO
ALTER TABLE [dbo].[al_purchase_order_item] ADD CONSTRAINT [al_purchase_order_item_fk04] FOREIGN KEY ([cost_center_id]) REFERENCES [dbo].[ca_resource_cost_center] ([id])
GO
ALTER TABLE [dbo].[al_purchase_order_item] ADD CONSTRAINT [al_purchase_order_item_fk05] FOREIGN KEY ([gl_code_id]) REFERENCES [dbo].[ca_resource_gl_code] ([id])
GO
GRANT SELECT ON  [dbo].[al_purchase_order_item] TO [swcmadmin]
GRANT INSERT ON  [dbo].[al_purchase_order_item] TO [swcmadmin]
GRANT DELETE ON  [dbo].[al_purchase_order_item] TO [swcmadmin]
GRANT UPDATE ON  [dbo].[al_purchase_order_item] TO [swcmadmin]
GRANT SELECT ON  [dbo].[al_purchase_order_item] TO [uapmadmin]
GRANT INSERT ON  [dbo].[al_purchase_order_item] TO [uapmadmin]
GRANT DELETE ON  [dbo].[al_purchase_order_item] TO [uapmadmin]
GRANT UPDATE ON  [dbo].[al_purchase_order_item] TO [uapmadmin]
GRANT SELECT ON  [dbo].[al_purchase_order_item] TO [uapmadmin_group]
GRANT INSERT ON  [dbo].[al_purchase_order_item] TO [uapmadmin_group]
GRANT DELETE ON  [dbo].[al_purchase_order_item] TO [uapmadmin_group]
GRANT UPDATE ON  [dbo].[al_purchase_order_item] TO [uapmadmin_group]
GRANT SELECT ON  [dbo].[al_purchase_order_item] TO [uapmbatch]
GRANT INSERT ON  [dbo].[al_purchase_order_item] TO [uapmbatch]
GRANT DELETE ON  [dbo].[al_purchase_order_item] TO [uapmbatch]
GRANT UPDATE ON  [dbo].[al_purchase_order_item] TO [uapmbatch]
GRANT SELECT ON  [dbo].[al_purchase_order_item] TO [uapmbatch_group]
GRANT INSERT ON  [dbo].[al_purchase_order_item] TO [uapmbatch_group]
GRANT DELETE ON  [dbo].[al_purchase_order_item] TO [uapmbatch_group]
GRANT UPDATE ON  [dbo].[al_purchase_order_item] TO [uapmbatch_group]
GRANT SELECT ON  [dbo].[al_purchase_order_item] TO [uapmreporting]
GRANT INSERT ON  [dbo].[al_purchase_order_item] TO [uapmreporting]
GRANT DELETE ON  [dbo].[al_purchase_order_item] TO [uapmreporting]
GRANT UPDATE ON  [dbo].[al_purchase_order_item] TO [uapmreporting]
GRANT SELECT ON  [dbo].[al_purchase_order_item] TO [uapmreporting_group]
GRANT INSERT ON  [dbo].[al_purchase_order_item] TO [uapmreporting_group]
GRANT DELETE ON  [dbo].[al_purchase_order_item] TO [uapmreporting_group]
GRANT UPDATE ON  [dbo].[al_purchase_order_item] TO [uapmreporting_group]
GO