USE [P21]
GO

/****** Object:  View [dbo].[nutech_view_cash_receipts_history_report]    Script Date: 2/23/2025 3:40:56 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO





CREATE VIEW [dbo].[nutech_view_cash_receipts_history_report]
AS

SELECT 
		p21_view_ar_receipts.company_id
		, p21_view_ar_receipts.remitter_id
		, p21_view_ar_receipts.receipt_number
		, p21_view_ar_receipts.date_received
		, 'N/A' AS payment_type 
		, p21_view_company.company_name
		, p21_view_company.use_branch_accounting
		, p21_view_address.name
		, p21_view_ar_receipts_detail.invoice_no
		, p21_view_ar_receipts_detail.payment_amount as receipts_amount
		, p21_view_ar_receipts_detail.allowed_amount
		, p21_view_ar_receipts_detail.terms_amount
		, dbo.p21_fn_datepart(p21_view_invoice_hdr.invoice_date) invoice_date
		, p21_view_invoice_hdr.invoice_date invoice_raw_date
		, p21_view_invoice_hdr.total_amount
		, p21_view_invoice_hdr.po_no
		, p21_view_invoice_hdr.branch_id
		, p21_view_invoice_hdr.sold_to_customer_id
		, p21_view_invoice_hdr.invoice_type
		, p21_view_invoice_hdr.invoice_adjustment_type
		, p21_view_invoice_hdr.invoice_class
		, p21_view_invoice_hdr.source_type_cd

		, p21_view_customer.customer_name
		, p21_view_ar_payment_details.payment_number
		, p21_view_ar_payment_details.check_number
		, p21_view_ar_payment_details.payment_date

		, p21_view_ar_receipts_detail.payment_amount
		, p21_view_ar_payment_details.payment_amount as totals_payment_amount

		,p21_view_ar_payment_details.payment_type_id
		,p21_view_ar_payment_details.cc_name
		,p21_view_ar_payment_details.cc_number

		, p21_view_ar_receipts_detail.allowed_amount as totals_allowed_amount
		, p21_view_ar_receipts_detail.terms_amount as totals_terms_amount
		, CAST('Customer ID :  ' + RTrim(p21_view_invoice_hdr.sold_to_customer_id)+' - '+
				     RTrim(p21_view_customer.customer_name) AS VARCHAR(80)) as customer_info
		, CAST('Remitter ID :  ' + RTrim(p21_view_ar_receipts.remitter_id) + ' - '+
				     RTrim(p21_view_address.name) AS VARCHAR(80))as remitter_info
		, CAST('Payment Date :  ' + dbo.p21_fn_datepart(p21_view_ar_receipts.date_received) AS VARCHAR(40)) as date_recvd_info
		, CAST('Total for Payment Date :  ' + dbo.p21_fn_datepart(p21_view_ar_receipts.date_received) AS VARCHAR(75)) as total_date_label
		, CAST('Total for Customer :  ' + RTrim(p21_view_customer.customer_name) AS VARCHAR(75)) as total_cust_label
		, CAST('Total for Remitter :  ' + RTrim(p21_view_address.name) AS VARCHAR)as total_remit_label
		,p21_view_invoice_hdr.inv_no_display
		,p21_view_invoice_hdr.disputed_flag
		,p21_view_invoice_hdr.job_id
		,p21_view_jc_job.job_description
		,p21_view_payment_types.payment_method_id
		,p21_view_payment_types.payment_type_desc
		,p21_view_ar_receipts_detail.tax_terms_taken_amt
		,p21_view_ar_receipts_detail.tax_terms_taken_amt AS totals_tax_terms_taken_amt
FROM p21_view_ar_receipts
INNER JOIN p21_view_company ON p21_view_ar_receipts.company_id = p21_view_company.company_id
INNER JOIN p21_view_address ON p21_view_ar_receipts.remitter_id = p21_view_address.id
INNER JOIN p21_view_ar_receipts_detail ON p21_view_ar_receipts.receipt_number = p21_view_ar_receipts_detail.receipt_number
	AND p21_view_ar_receipts_detail.company_id = p21_view_ar_receipts.company_id  
INNER JOIN p21_view_invoice_hdr ON p21_view_ar_receipts_detail.invoice_no = p21_view_invoice_hdr.invoice_no
	AND p21_view_invoice_hdr.company_no = p21_view_company.company_id
INNER JOIN p21_view_customer ON p21_view_invoice_hdr.company_no = p21_view_customer.company_id
	AND p21_view_invoice_hdr.sold_to_customer_id = p21_view_customer.customer_id	
LEFT JOIN p21_view_ar_payment_details ON p21_view_ar_payment_details.payment_number = p21_view_ar_receipts.payment_number
LEFT JOIN p21_view_jc_job ON p21_view_jc_job.job_id = p21_view_invoice_hdr.job_id
INNER JOIN p21_view_payment_types ON p21_view_payment_types.payment_type_id = p21_view_ar_payment_details.payment_type_id
	AND p21_view_payment_types.company_id = p21_view_ar_receipts.company_id 
 
UNION ALL

SELECT   
  p21_view_ar_receipts.company_id  
  , p21_view_ar_receipts.remitter_id  
  , p21_view_ar_receipts.receipt_number  
  , p21_view_ar_receipts.date_received  
  , 'Reverse Payment' AS payment_type  
  , p21_view_company.company_name  
  , p21_view_company.use_branch_accounting  
  , p21_view_address.name  
  , p21_view_ar_receipts_detail.invoice_no  
  , p21_view_ar_receipts_detail.payment_amount as receipts_amount  
  , p21_view_ar_receipts_detail.allowed_amount  
  , p21_view_ar_receipts_detail.terms_amount  
  , dbo.p21_fn_datepart(p21_view_invoice_hdr.invoice_date) invoice_date
  , p21_view_invoice_hdr.invoice_date invoice_raw_date  
  , p21_view_invoice_hdr.total_amount  
  , p21_view_invoice_hdr.po_no  
  , p21_view_invoice_hdr.branch_id  
  , p21_view_invoice_hdr.sold_to_customer_id  
  , p21_view_invoice_hdr.invoice_type  
  , p21_view_invoice_hdr.invoice_adjustment_type  
  , p21_view_invoice_hdr.invoice_class  
  , p21_view_invoice_hdr.source_type_cd  
  , p21_view_customer.customer_name  
  , p21_view_ar_payment_details.payment_number  
  , p21_view_ar_payment_details.check_number  
  , p21_view_ar_payment_details.payment_date  
  , p21_view_ar_receipts_detail.payment_amount  
  , p21_view_ar_payment_details.payment_amount as totals_payment_amount  
  , p21_view_ar_payment_details.payment_type_id  
  , p21_view_ar_payment_details.cc_name  
  , p21_view_ar_payment_details.cc_number  
  , p21_view_ar_receipts_detail.allowed_amount as totals_allowed_amount  
  , p21_view_ar_receipts_detail.terms_amount as totals_terms_amount  
  , CAST('Customer ID :  ' + RTrim(p21_view_invoice_hdr.sold_to_customer_id)+' - '+  
         RTrim(p21_view_customer.customer_name) AS VARCHAR(80)) as customer_info  
  , CAST('Remitter ID :  ' + RTrim(p21_view_ar_receipts.remitter_id) + ' - '+  
         RTrim(p21_view_address.name) AS VARCHAR(80))as remitter_info  
  , CAST('Payment Date :  ' + dbo.p21_fn_datepart(p21_view_ar_receipts.date_received) AS VARCHAR(40)) as date_recvd_info  
  , CAST('Total for Payment Date :  ' + dbo.p21_fn_datepart(p21_view_ar_receipts.date_received) AS VARCHAR(75)) as total_date_label  
  , CAST('Total for Customer :  ' + RTrim(p21_view_customer.customer_name) AS VARCHAR(75)) as total_cust_label  
  , CAST('Total for Remitter :  ' + RTrim(p21_view_address.name) AS VARCHAR)as total_remit_label  
  , p21_view_invoice_hdr.inv_no_display  
  , p21_view_invoice_hdr.disputed_flag  
  , p21_view_invoice_hdr.job_id  
  , p21_view_jc_job.job_description  
  , p21_view_payment_types.payment_method_id  
  , p21_view_payment_types.payment_type_desc  
  , p21_view_ar_receipts_detail.tax_terms_taken_amt
  , p21_view_ar_receipts_detail.tax_terms_taken_amt AS totals_tax_terms_taken_amt
FROM reverse_payment_line (NOLOCK)  
JOIN reverse_payment_hdr (NOLOCK) ON ( reverse_payment_hdr.reverse_payment_hdr_uid = reverse_payment_line.reverse_payment_hdr_uid )  
JOIN p21_view_ar_receipts (NOLOCK) ON ( p21_view_ar_receipts.receipt_number =  reverse_payment_line.receipt_no)   
INNER JOIN p21_view_company ON p21_view_ar_receipts.company_id = p21_view_company.company_id  
INNER JOIN p21_view_address ON p21_view_ar_receipts.remitter_id = p21_view_address.id  
INNER JOIN p21_view_ar_receipts_detail ON p21_view_ar_receipts.receipt_number = p21_view_ar_receipts_detail.receipt_number  
	AND p21_view_ar_receipts_detail.company_id = p21_view_ar_receipts.company_id  
	AND p21_view_ar_receipts_detail.invoice_no = reverse_payment_line.invoice_no
INNER JOIN p21_view_invoice_hdr ON p21_view_ar_receipts_detail.invoice_no = p21_view_invoice_hdr.invoice_no  
	AND p21_view_invoice_hdr.company_no = p21_view_company.company_id
INNER JOIN p21_view_customer ON p21_view_invoice_hdr.company_no = p21_view_customer.company_id  
 AND p21_view_invoice_hdr.sold_to_customer_id = p21_view_customer.customer_id   
LEFT JOIN p21_view_ar_payment_details ON p21_view_ar_payment_details.payment_number = p21_view_ar_receipts.payment_number    
LEFT JOIN p21_view_jc_job ON p21_view_jc_job.job_id = p21_view_invoice_hdr.job_id  
INNER JOIN p21_view_payment_types ON p21_view_payment_types.payment_type_id = p21_view_ar_payment_details.payment_type_id  
	AND p21_view_payment_types.company_id = p21_view_ar_receipts.company_id 
WHERE 	p21_view_invoice_hdr.approved = 'Y'
  AND p21_view_invoice_hdr.consolidated <> 'Y'
GO

