USE [P21]
GO

/****** Object:  View [dbo].[nutech_view_check_hdr]    Script Date: 2/23/2025 3:58:16 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




CREATE VIEW [dbo].[nutech_view_check_hdr]
AS


Select	payments.check_no header_number,
	payments.check_no,
	payments.check_date,
	payments.vendor_id,
	substring(address_a.name,1,50) vendor_name  
	,COALESCE(address_a.mail_address1, '') 'format_vendor_addr1'
      	,COALESCE(address_a.mail_address2, '') 'format_vendor_addr2'
	,CASE		WHEN COALESCE(address_a.mail_city, '') = '' 
					THEN '' 
				ELSE address_a.mail_city 
		END +
		CASE	WHEN COALESCE(address_a.mail_city, '') <> '' AND COALESCE(address_a.mail_state, '') <> '' 
					THEN ', ' 
				ELSE '' 
		END +
		CASE	WHEN COALESCE(address_a.mail_state, '') = '' 
				THEN '' 
				ELSE address_a.mail_state + '' 
		END + 
		CASE	WHEN COALESCE(address_a.mail_postal_code, '') = '' 
				THEN '' 
				ELSE ' ' + address_a.mail_postal_code 
	END 'format_vendor_addr3'
      	,COALESCE(address_a.mail_country, '') 'format_vendor_addr4',
	address_a.mail_address1,
	address_a.mail_address2,
	address_a.mail_address3,
	address_a.mail_city,
	address_a.mail_state,
	address_a.mail_postal_code,
	address_a.mail_country,
	bank_accounts.bank_name,
	bank_accounts.bank_branch,
	bank_accounts.bank_code,
	bank_accounts.bank_address1,
	bank_accounts.bank_address2,
	bank_accounts.bank_no,
	bank_accounts.gl_account_no,
	bank_accounts.transit_code,
	bank_accounts.account_no,
	bank_accounts.fed_res_code,
	space(255) written_amount_text,
	substring(company.company_name,1,40) company_name,
	' ' format_company_addr1,
	' ' format_company_addr2,
	' ' format_company_addr3,
	' ' format_company_addr4,
	CASE 
	    WHEN  bank_accounts.address_type_cd = 2139
	           THEN address_b.mail_address1  
	            ELSE address_b.phys_address1
	     END 'phys_address1',
	CASE 
	     WHEN bank_accounts.address_type_cd = 2139
	          THEN address_b.mail_address2
		  ELSE address_b.phys_address2 
		END 'phys_address2',
	CASE 
	     WHEN bank_accounts.address_type_cd = 2139
	          THEN address_b.mail_address3
		  ELSE address_b.phys_address3 
		END 'phys_address3',
	CASE 
	     WHEN bank_accounts.address_type_cd = 2139
	           THEN address_b.mail_city  
		ELSE address_b.phys_city
	END 'phys_city',
	CASE 
	      WHEN bank_accounts.address_type_cd = 2139
	           THEN address_b.mail_state  
		ELSE address_b.phys_state
	END 'phys_state',
	CASE 
	     WHEN bank_accounts.address_type_cd = 2139
	            THEN address_b.mail_postal_code  
		ELSE address_b.phys_postal_code
	END 'phys_postal_code',

	address_b.central_phone_number,
	space(30) transit_number,
	space(30) account_number,
	space(30) chk_no,
	system_setting_a.value 'used_in_us',
	cast(system_setting_b.value as decimal(19,2)) 'amount_req_two_sigs',
	cast(system_setting_c.value as int) 'number_of_sigs',
	' ' codes,
	' ' check_label,
	cast(0 as decimal) check_amount,
	' ' sign_line1,
	' ' sign_line2,
	' ' sign_label,
	payments.company_no company_number,
	payments.bank_no    bank_number,
	payments.currency_id 

	,CASE payments.transmission_method 
	          WHEN 708  THEN 'Y' 
	          ELSE 'N'
	END 'edi_check'
	,CASE payments.transmission_method   
		   WHEN 2694  THEN 'Y'   
		   ELSE 'N'  
	END 'eft'
From Payments
INNER JOIN address address_a ON (payments.vendor_id = address_a.id)
INNER JOIN bank_accounts ON (payments.company_no = bank_accounts.company_no ) and  
         		    ( payments.bank_no = bank_accounts.bank_no )
INNER JOIN company ON ( bank_accounts.company_no = company.company_id )
INNER JOIN address address_b ON ( company.address_id = address_b.id )
cross join system_setting system_setting_a
cross join system_setting system_setting_b
cross join system_setting system_setting_c
WHERE ( payments.cleared_bank <>'Y' ) and
      ( payments.void <> 'Y') and
      ( payments.check_amount_display > 0 ) and
      system_setting_a.configuration_id = 0 AND
      system_setting_a.name = 'used_in_us' AND
      system_setting_b.configuration_id = 0 AND
      system_setting_b.name = 'amount_req_two_sigs' AND
      system_setting_c.configuration_id = 0 AND
      system_setting_c.name = 'number_of_sigs' 
 AND RIGHT(payments.check_no, 1) <> 'M'
  AND	RIGHT(payments.check_no, 1) <> 'A'

GO

