USE [P21]
GO

/****** Object:  View [dbo].[vatcImportINVOICE]    Script Date: 9/29/2024 9:53:53 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




CREATE VIEW [dbo].[vatcImportINVOICE]
AS
SELECT dbo.invoice_hdr.company_no AS CompanyID, dbo.invoice_hdr.customer_id AS CustID, dbo.invoice_hdr.invoice_no AS TranNo, 
       dbo.invoice_hdr.invoice_type AS TranType, dbo.invoice_hdr.invoice_desc AS InvoiceCmnt, dbo.invoice_hdr.invoice_date AS TranDate, 
       dbo.invoice_hdr.invoice_date AS PostDate, dbo.invoice_hdr.net_due_date AS DueDate, dbo.invoice_hdr.terms_due_date AS DiscDate, 
       dbo.invoice_hdr.date_paid AS ClosingTranDate, dbo.invoice_hdr.po_no AS CustPONo, CAST(dbo.invoice_hdr.total_amount AS decimal(15, 3)) AS TranAmt, 
       CAST(dbo.invoice_hdr.total_amount AS decimal(15, 3)) AS TranAmtHC, dbo.invoice_hdr.terms_amount AS DiscAmt, dbo.invoice_hdr.terms_amount AS DiscAmtHC, 
       (dbo.invoice_hdr.total_amount - dbo.invoice_hdr.amount_paid - dbo.invoice_hdr.terms_taken - dbo.invoice_hdr.allowed + dbo.invoice_hdr.memo_amount + dbo.invoice_hdr.bad_debt_amount) AS Balance,
       (dbo.invoice_hdr.total_amount - dbo.invoice_hdr.amount_paid - dbo.invoice_hdr.terms_taken - dbo.invoice_hdr.allowed + dbo.invoice_hdr.memo_amount + dbo.invoice_hdr.bad_debt_amount) AS BalanceHC,
	   dbo.invoice_hdr.terms_id AS PmtTermsID, dbo.currency_hdr.currency_desc AS CurrID, 
       dbo.currency_hdr.currency_desc AS HomeCurrID, '' AS CurrExchRate, '' AS Status, dbo.invoice_hdr.date_created AS CreateDate, '' AS PrimarySperName, 
       dbo.invoice_hdr.tax_amount AS StaxAmt, dbo.invoice_hdr.tax_amount AS StaxAmtHC, dbo.invoice_hdr.invoice_no AS UDF1, '' AS UDF2, '' AS UDF3, '' AS UDF4, 
       '' AS UDF5, '' AS UDF6, '' AS UDF7, '' AS UDF8, '' AS UDF9, '' AS UDF10, dbo.invoice_hdr.date_last_modified AS UpdateDate
FROM dbo.invoice_hdr with (nolock)
     INNER JOIN dbo.customer with (nolock) ON
        dbo.invoice_hdr.customer_id = dbo.customer.customer_id
	 LEFT OUTER JOIN dbo.customer_ud with (nolock) ON
	    dbo.customer.customer_id = dbo.customer_ud.customer_id
	 INNER JOIN dbo.currency_hdr with (nolock) ON
	    dbo.customer.currency_id = dbo.currency_hdr.currency_id
WHERE (dbo.invoice_hdr.consolidated = 'N' AND 
         ((dbo.invoice_hdr.total_amount - dbo.invoice_hdr.amount_paid - dbo.invoice_hdr.terms_taken - dbo.invoice_hdr.allowed + dbo.invoice_hdr.memo_amount + dbo.invoice_hdr.bad_debt_amount) <> 0) AND
         dbo.customer.use_consolidated_invoicing = 'N') OR
      ((dbo.invoice_hdr.consolidated = 'N') AND 
         (dbo.invoice_hdr.total_amount - dbo.invoice_hdr.amount_paid - dbo.invoice_hdr.terms_taken - dbo.invoice_hdr.allowed + dbo.invoice_hdr.memo_amount + dbo.invoice_hdr.bad_debt_amount <> 0) AND
         (dbo.customer.use_consolidated_invoicing = 'Y') AND
         (dbo.invoice_hdr.invoice_date < ISNULL(dbo.customer_ud.consolidated_inv_start_date, '01/01/1990'))) OR
      ((dbo.invoice_hdr.consolidated = 'N') AND
	     (dbo.customer.use_consolidated_invoicing = 'N') AND
	     (dbo.invoice_hdr.total_amount <> 0) AND
	     (DATEDIFF(dd, dbo.invoice_hdr.date_last_modified, GETDATE()) < 7)) OR
      ((dbo.invoice_hdr.consolidated = 'N') AND
	     (dbo.customer.use_consolidated_invoicing = 'Y') AND 
         (dbo.invoice_hdr.invoice_date < ISNULL(dbo.customer_ud.consolidated_inv_start_date, '01/01/1990')) AND
	     (dbo.invoice_hdr.total_amount <> 0) AND
	     (DATEDIFF(dd, dbo.invoice_hdr.date_last_modified, GETDATE()) < 7)) OR
      ((dbo.invoice_hdr.consolidated = 'C') AND 
         (dbo.invoice_hdr.total_amount - dbo.invoice_hdr.amount_paid - dbo.invoice_hdr.terms_taken - dbo.invoice_hdr.allowed + dbo.invoice_hdr.memo_amount + dbo.invoice_hdr.bad_debt_amount <> 0)) OR
      (dbo.invoice_hdr.consolidated = 'C') AND (dbo.invoice_hdr.total_amount > 0) AND
	     (DATEDIFF(dd, dbo.invoice_hdr.date_last_modified, GETDATE()) < 7) AND
	  dbo.invoice_hdr.approved = 'Y'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties = 
   Begin PaneConfigurations = 
      Begin PaneConfiguration = 0
         NumPanes = 4
         Configuration = "(H (1[18] 4[16] 2[58] 3) )"
      End
      Begin PaneConfiguration = 1
         NumPanes = 3
         Configuration = "(H (1 [50] 4 [25] 3))"
      End
      Begin PaneConfiguration = 2
         NumPanes = 3
         Configuration = "(H (1 [50] 2 [25] 3))"
      End
      Begin PaneConfiguration = 3
         NumPanes = 3
         Configuration = "(H (4 [30] 2 [40] 3))"
      End
      Begin PaneConfiguration = 4
         NumPanes = 2
         Configuration = "(H (1 [56] 3))"
      End
      Begin PaneConfiguration = 5
         NumPanes = 2
         Configuration = "(H (2 [66] 3))"
      End
      Begin PaneConfiguration = 6
         NumPanes = 2
         Configuration = "(H (4 [50] 3))"
      End
      Begin PaneConfiguration = 7
         NumPanes = 1
         Configuration = "(V (3))"
      End
      Begin PaneConfiguration = 8
         NumPanes = 3
         Configuration = "(H (1[56] 4[18] 2) )"
      End
      Begin PaneConfiguration = 9
         NumPanes = 2
         Configuration = "(H (1 [75] 4))"
      End
      Begin PaneConfiguration = 10
         NumPanes = 2
         Configuration = "(H (1[66] 2) )"
      End
      Begin PaneConfiguration = 11
         NumPanes = 2
         Configuration = "(H (4 [60] 2))"
      End
      Begin PaneConfiguration = 12
         NumPanes = 1
         Configuration = "(H (1) )"
      End
      Begin PaneConfiguration = 13
         NumPanes = 1
         Configuration = "(V (4))"
      End
      Begin PaneConfiguration = 14
         NumPanes = 1
         Configuration = "(V (2))"
      End
      ActivePaneConfig = 0
   End
   Begin DiagramPane = 
      Begin Origin = 
         Top = 0
         Left = 0
      End
      Begin Tables = 
         Begin Table = "invoice_hdr"
            Begin Extent = 
               Top = 6
               Left = 38
               Bottom = 135
               Right = 320
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "customer"
            Begin Extent = 
               Top = 6
               Left = 358
               Bottom = 135
               Right = 634
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "customer_ud"
            Begin Extent = 
               Top = 138
               Left = 317
               Bottom = 267
               Right = 551
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "currency_hdr"
            Begin Extent = 
               Top = 138
               Left = 38
               Bottom = 267
               Right = 279
            End
            DisplayFlags = 280
            TopColumn = 0
         End
      End
   End
   Begin SQLPane = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
      Begin ColumnWidths = 38
         Width = 284
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         W' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'vatcImportINVOICE'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane2', @value=N'idth = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
      End
   End
   Begin CriteriaPane = 
      Begin ColumnWidths = 14
         Column = 4920
         Alias = 2310
         Table = 1170
         Output = 720
         Append = 1400
         NewValue = 1170
         SortType = 1350
         SortOrder = 1410
         GroupBy = 1350
         Filter = 1350
         Or = 1350
         Or = 1350
         Or = 1350
         Or = 1350
         Or = 1350
         Or = 1350
      End
   End
End
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'vatcImportINVOICE'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=2 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'vatcImportINVOICE'
GO


