USE [P21]
GO

/****** Object:  UserDefinedFunction [dbo].[nutech_fn_artb]    Script Date: 2/23/2025 4:20:27 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO





CREATE FUNCTION [dbo].[nutech_fn_artb]
(
	@year_period INT
	, @company_no VARCHAR(8)
	, @customer_salesrep_check CHAR(1)
	, @salesrep_start VARCHAR(16)
	, @salesrep_end VARCHAR(16)
	, @date_type_flag TINYINT = 0 --0 use periods, 1 use date created, 2 use paid period
	, @search_date	DATETIME = NULL
	, @include_consolidated_invoices	CHAR(1) = 'N' -- DT 1027475
	, @customer_id_start DECIMAL(19, 0) = 0
	, @customer_id_stop DECIMAL(19, 0) = 9999999999
)

RETURNS TABLE 

AS

RETURN

(

SELECT
	p21_view_invoice_hdr.company_no AS company_id
	,p21_view_invoice_hdr.customer_id_number AS customer_id
	,p21_view_invoice_hdr.invoice_no	
	,p21_view_invoice_hdr.total_amount AS total_amount
	, COALESCE(drv_invoice_hdr.total_amount,0) AS memo_amount
	, COALESCE(drv_invoice_hdr.total_allowed,0) AS bad_debt_amount
	,p21_view_invoice_hdr.invoice_date
	,p21_view_invoice_hdr.terms_desc
	,p21_view_invoice_hdr.salesrep_name
	,CASE WHEN oe_hdr.advanced_billing_flag = 'Y' THEN p21_view_invoice_hdr.amount_paid
	  ELSE COALESCE(drv_ar_receipts.total_payment_amount,0) END AS total_receipts
	,COALESCE(drv_ar_receipts.total_allowed_amount,0) AS allowed_amount
	, (CASE WHEN @customer_salesrep_check = 'Y' THEN customer.salesrep_id ELSE invoice_hdr_salesrep.salesrep_id END) AS salesrep_id
	,p21_view_invoice_hdr.order_date
	,p21_view_invoice_hdr.order_no
	,p21_view_invoice_hdr.date_created
	,p21_view_invoice_hdr.date_last_modified
	,p21_view_invoice_hdr.last_maintained_by

	,p21_view_invoice_hdr.period
	,p21_view_invoice_hdr.year_for_period
	, COALESCE(drv_ar_receipts.total_terms_amount,0) AS terms_amount 
	,p21_view_invoice_hdr.net_due_date
	,p21_view_invoice_hdr.terms_due_date
	,p21_view_invoice_hdr.branch_id
	,p21_view_invoice_hdr.po_no
	,p21_view_invoice_hdr.invoice_class
	,p21_view_invoice_hdr.ship_to_id
	,p21_view_invoice_hdr.ship2_name

	,p21_view_invoice_hdr.invoice_reference_no
	,invoice_adjustment_type
	,currency_line.to_currency_id AS currency_id
	,( 
		CASE 
			WHEN currency_line.to_currency_id IS NULL THEN NULL 
			ELSE p21_view_invoice_hdr.total_amount_home 
			END 
	) AS total_amount_home,
	( 
		CASE 
			WHEN currency_line.to_currency_id IS NULL THEN NULL 
			ELSE COALESCE(drv_invoice_hdr.total_amount_home,0)
		END 
	) AS memo_amount_home , 
	( 
		CASE 
			WHEN currency_line.to_currency_id IS NULL THEN NULL 
			ELSE COALESCE(drv_ar_receipts.total_allowed_amount_home,0)
		END 
	) AS allowed_home , 
	( 
		CASE 
			WHEN currency_line.to_currency_id IS NULL THEN NULL 
			ELSE COALESCE(drv_invoice_hdr.total_allowed_home,0)
		END 
	) AS bad_debt_amount_home , 
	( 
		CASE 
			WHEN currency_line.to_currency_id IS NULL THEN NULL 
			ELSE COALESCE(drv_ar_receipts.total_terms_amount_home,0)
		END 
	) AS terms_amount_home , 
	( 
		CASE 
			WHEN currency_line.to_currency_id IS NULL THEN NULL 
			ELSE 
			CASE WHEN oe_hdr.advanced_billing_flag = 'Y' THEN COALESCE(p21_view_invoice_hdr.amount_paid_home, 0)
			ELSE COALESCE(drv_ar_receipts.total_receipts_home,0) END
		END 
	) AS total_receipts_home 
	,[location].location_id 
	, address.corp_address_id
	, p21_view_invoice_hdr.terms_amount AS invoice_terms_amount
	, currency_line.currency_line_uid
	, customer.statement_batch_uid
	, p21_view_invoice_hdr.approved
	, p21_view_invoice_hdr.amount_paid
	, p21_view_invoice_hdr.paid_in_full_flag
	, currency_line.to_currency_id
    	, p21_view_invoice_hdr.consolidated		
    	, p21_view_invoice_hdr.invoice_period	
	, p21_view_invoice_hdr.job_id
	, p21_view_invoice_hdr.sold_to_customer_id

	, p21_view_invoice_hdr.fob
	, p21_view_invoice_hdr.record_type_cd
	, p21_view_invoice_hdr.record_type_reference_no
	, p21_view_invoice_hdr.tax_terms_amt AS invoice_tax_terms_amt

	, COALESCE(drv_ar_receipts.total_tax_terms_taken_amt,0) AS tax_terms_taken_amt 
	, (CASE 
			WHEN currency_line.to_currency_id IS NULL THEN NULL 
			ELSE COALESCE(drv_ar_receipts.total_tax_terms_taken_amt_home,0)
	   END 
	) AS tax_terms_taken_amt_home 
	,CASE WHEN COALESCE(p21_view_invoice_hdr.record_type_cd, 0) = 2741 OR COALESCE([location].location_id, 0) > 0  THEN 'Y' ELSE 'N' END [inter_company]
FROM 
	dbo.p21_view_invoice_hdr AS p21_view_invoice_hdr  WITH (NOLOCK) 
	INNER JOIN dbo.address AS address  WITH (NOLOCK) ON (address.id = p21_view_invoice_hdr.customer_id)
	INNER JOIN dbo.customer AS customer  WITH (NOLOCK) 
		ON p21_view_invoice_hdr.company_no = customer.company_id 
		AND p21_view_invoice_hdr.customer_id_number = customer.customer_id 
		AND 
		( 
			( 
				'N' = @customer_salesrep_check 
			) 
			OR 
			( 
				customer.salesrep_id BETWEEN @salesrep_start AND @salesrep_end
			) 
		) 
	LEFT OUTER JOIN dbo.invoice_hdr_salesrep AS invoice_hdr_salesrep  WITH (NOLOCK) 
		ON p21_view_invoice_hdr.invoice_no = invoice_hdr_salesrep.invoice_number 
		AND 
		( 
			( 
				'Y' = @customer_salesrep_check 
			) 
			OR 
			( 
				invoice_hdr_salesrep.salesrep_id     >= @salesrep_start 
				AND invoice_hdr_salesrep.salesrep_id <= @salesrep_end 
			) 
		) 
		AND invoice_hdr_salesrep.primary_salesrep = 'Y' 
		AND invoice_hdr_salesrep.delete_flag = 'N' 
	LEFT JOIN dbo.currency_line AS currency_line  WITH (NOLOCK)
		ON currency_line.currency_line_uid = p21_view_invoice_hdr.currency_line_uid 		
	LEFT JOIN
	(
		SELECT
			ar_receipts_detail.invoice_no
			, SUM( COALESCE(ar_receipts_detail.payment_amount, 0) ) AS total_payment_amount
			, SUM( COALESCE(ar_receipts_detail.allowed_amount, 0) ) AS total_allowed_amount
			, SUM( COALESCE(ar_receipts_detail.terms_amount, 0) ) AS total_terms_amount
			, SUM( COALESCE(ar_receipts_detail.allowed_amount_home, 0) ) AS total_allowed_amount_home
			, SUM( COALESCE(ar_receipts_detail.terms_amount_home, 0) ) AS total_terms_amount_home
			, SUM( COALESCE(ar_receipts_detail.payment_amount_home + ar_receipts_detail.currency_variance_amt_home , 0) ) AS total_receipts_home
			, SUM( COALESCE(ar_receipts_detail.tax_terms_taken_amt, 0) ) AS total_tax_terms_taken_amt
			, SUM( COALESCE(ar_receipts_detail.tax_terms_taken_amt_home, 0) ) AS total_tax_terms_taken_amt_home

		FROM
			dbo.p21_view_ar_receipts_detail AS ar_receipts_detail  WITH (NOLOCK)

		LEFT JOIN dbo.p21_view_reverse_payment_line reverse_payment_line WITH (NOLOCK) 
			ON reverse_payment_line.receipt_no = ar_receipts_detail.receipt_number
			AND reverse_payment_line.invoice_no = ar_receipts_detail.invoice_no
		LEFT JOIN dbo.p21_view_reverse_payment_hdr reverse_payment_hdr WITH (NOLOCK) ON (reverse_payment_hdr.reverse_payment_hdr_uid = reverse_payment_line.reverse_payment_hdr_uid)


		WHERE
			ar_receipts_detail.customer_id BETWEEN @customer_id_start AND @customer_id_stop 
			AND
			(
				COALESCE(ar_receipts_detail.reverse_payment_flag,'N') = 'N'
				OR 
				(
					(
						@date_type_flag = 0  
						AND (reverse_payment_hdr.year_for_period *1000) + reverse_payment_hdr.period > @year_period   
					)  
					OR  
					(  
						@date_Type_flag = 1  
						AND reverse_payment_line.date_created > @search_date  
					)
					OR  
					(  
						@date_Type_flag = 2  
						AND reverse_payment_line.date_created > @search_date  
					)  
				)
			)
			AND 
			(
				(
					@date_type_flag = 0
					AND (ar_receipts_detail.year_for_period *1000) + ar_receipts_detail.period <= @year_period 
				)
				OR
				(
					@date_Type_flag = 1
					AND ar_receipts_detail.date_created <= @search_date
				)
				OR
				(
					@date_Type_flag = 2
					AND ar_receipts_detail.payment_date <= @search_date
				)
			)
		GROUP BY
			ar_receipts_detail.invoice_no
	) drv_ar_receipts 
		ON drv_ar_receipts.invoice_no     = p21_view_invoice_hdr.invoice_no 
	LEFT JOIN
	(
		SELECT
			ih2.invoice_reference_no
			, ih2.customer_id_number
			, SUM( 
				CASE
					WHEN ih2.invoice_adjustment_type in ('C', 'D', 'A') then COALESCE(total_amount_home, 0)
					ELSE 0
				END) AS total_amount_home
			, SUM( 
				CASE
					WHEN ih2.invoice_adjustment_type in ('C', 'D', 'A') then COALESCE(total_amount, 0)
					ELSE 0
				END) AS total_amount
			, SUM( 
				CASE
					WHEN ih2.invoice_adjustment_type in ('R', 'W') then COALESCE(total_amount_home, 0)
					ELSE 0
				END) AS total_allowed_home
			, SUM( 
				CASE
					WHEN ih2.invoice_adjustment_type in ('R', 'W') then COALESCE(total_amount, 0)
					ELSE 0
				END) AS total_allowed
		FROM
			dbo.p21_view_invoice_hdr ih2  WITH (NOLOCK) 
		WHERE
			ih2.invoice_no <> ih2.invoice_reference_no 
			AND ih2.invoice_adjustment_type IN ( 'C' , 'D' , 'A', 'R', 'W' ) 
			AND ih2.approved = 'Y' 
			AND invoice_class <> 'DWREBILL'
			AND 
			(
				(
					@date_type_flag = 0
					AND (ih2.year_for_period *1000) + ih2.period <= @year_period 
				)
				OR
				(
					@date_Type_flag = 1
					AND ih2.date_created <= @search_date
				)
				OR
				(
					@date_Type_flag = 2
					AND ih2.invoice_date <= @search_date
				)
			)
		GROUP BY
			ih2.invoice_reference_no
			, ih2.customer_id_number
	)
	drv_invoice_hdr
		ON	p21_view_invoice_hdr.invoice_no = drv_invoice_hdr.invoice_reference_no 
		AND p21_view_invoice_hdr.customer_id_number = drv_invoice_hdr.customer_id_number
	LEFT JOIN oe_hdr WITH (NOLOCK) on oe_hdr.order_no = p21_view_invoice_hdr.order_no
	LEFT JOIN [location] WITH (NOLOCK) ON [location].location_id = customer.customer_id
									  AND [location].company_id <> p21_view_invoice_hdr.company_no
									  AND p21_view_invoice_hdr.order_no IS NULL
	WHERE
	p21_view_invoice_hdr.customer_id_number BETWEEN @customer_id_start AND @customer_id_stop
	AND (COALESCE (oe_hdr.advanced_billing_flag, 'N') = 'N' OR (oe_hdr.advanced_billing_flag='Y' AND p21_view_invoice_hdr.invoice_adjustment_type='P'))
	AND 
	( 
		(
		(@date_type_flag = 0 AND p21_view_invoice_hdr.invoice_paid_period >  @year_period) 
	OR  (@date_Type_flag = 1 AND p21_view_invoice_hdr.date_paid > @search_date AND p21_view_invoice_hdr.paid_in_full_flag = 'Y')
 	OR  (@date_Type_flag = 2 AND p21_view_invoice_hdr.date_paid > @search_date AND p21_view_invoice_hdr.paid_in_full_flag = 'Y')
		)
		OR 
		((COALESCE (oe_hdr.advanced_billing_flag, 'N') = 'N' and p21_view_invoice_hdr.invoice_paid_period IS NULL) OR
		 (oe_hdr.advanced_billing_flag='Y' AND p21_view_invoice_hdr.invoice_adjustment_type='P' AND p21_view_invoice_hdr.amount_paid < p21_view_invoice_hdr.total_amount))
	) 
	AND 
	( 
		p21_view_invoice_hdr.company_no = @company_no 
	) 

	AND 
	(
		@include_consolidated_invoices = 'Y'
		OR
		p21_view_invoice_hdr.consolidated <> 'Y' 
	) 
	AND 
	(
		p21_view_invoice_hdr.invoice_adjustment_type <> 'M'
	)
    AND (
		(@date_type_flag = 0 AND p21_view_invoice_hdr.invoice_period <=  @year_period) 
	OR  (@date_Type_flag = 1 AND p21_view_invoice_hdr.date_created <= @search_date)
 	OR  (@date_Type_flag = 2 AND p21_view_invoice_hdr.invoice_date <= @search_date)
		)

)

GO

