USE [P21]
GO

/****** Object:  UserDefinedFunction [dbo].[nutech_fn_artb_customer]    Script Date: 2/23/2025 4:28:06 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO



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

Returns Table 

AS

RETURN

(

SELECT
	p21_view_invoice_hdr.customer_id_number AS customer_id
	, p21_view_invoice_hdr.net_due_date

	, p21_view_invoice_hdr.total_amount 
		+ COALESCE(drv_invoice_hdr.total_amount,0) 
		+  COALESCE(drv_invoice_hdr.total_allowed,0)
		- COALESCE(drv_ar_receipts.total_allowed_amount,0)
		- COALESCE(drv_ar_receipts.total_terms_amount,0)
		- COALESCE(drv_ar_receipts.total_tax_terms_taken_amt,0)
		- COALESCE(drv_ar_receipts.total_payment_amount,0) AS summary_amount

	, p21_view_invoice_hdr.approved
	, p21_view_invoice_hdr.invoice_class
FROM 
	p21_view_invoice_hdr  WITH (NOLOCK) 
	INNER JOIN 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 
		( 
			( 
				@customer_salesrep_check = 'N' 
			) 
			OR 
			( 
				customer.salesrep_id BETWEEN @salesrep_start AND @salesrep_end
			) 
		) 
	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

			----------------- 11/27/2012 Feature 57163 ----------------
			, 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
			p21_view_ar_receipts_detail AS ar_receipts_detail  WITH (NOLOCK)
			--LSC, Scopus 1173425
			LEFT JOIN dbo.p21_view_reverse_payment_line reverse_payment_line 
			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 ON (reverse_payment_hdr.reverse_payment_hdr_uid = reverse_payment_line.reverse_payment_hdr_uid)
			--LSC, Scopus 1173425
		WHERE
			--521845 CMD
			ar_receipts_detail.customer_id BETWEEN @customer_id_start AND @customer_id_stop 
			AND 
			--LSC, Scopus 1173425
			(
				COALESCE(ar_receipts_detail.reverse_payment_flag,'N') = 'N'
				OR 
				(
					(
				-- SUB 2/1/07 Scopus 508279: Consider Reverse Payment records for ARTB
						@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
			--LSC, Scopus 1173425
			(
				(
					@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
			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
			(
				(
					@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 
WHERE 
	--521845 CMD
	p21_view_invoice_hdr.customer_id_number BETWEEN @customer_id_start AND @customer_id_stop
	AND
	( 
		p21_view_invoice_hdr.invoice_paid_period > @year_period
		OR 
		p21_view_invoice_hdr.invoice_paid_period IS NULL
	) 
	AND 
	(
		p21_view_invoice_hdr.invoice_period	<=  @year_period 
	) 

	AND 
	( 
		p21_view_invoice_hdr.company_no = @company_no 
	) 
-- 	AND 
--	( 
--		p21_view_invoice_hdr.approved = 'Y' 
--	) 
	AND 
	( 
		p21_view_invoice_hdr.consolidated <> 'Y' 
	) 

)


--$Author: Ricardo.aguirre $
--$Date: 10/24/13 3:38p $
--$Revision: 13 $
--$Log: /Server/CommerceCenter/Z_Cat (12.13)/Functions/p21_fnt_artb_customer.sql $
-- 
-- 13    10/24/13 3:38p Ricardo.aguirre
-- DEV: LSC
-- DBA: RAR
-- Scopus# 1173425: Modify p21_fnt_artb_customer to consider reversed
-- payments when determining whether the invoice has a debit or credit
-- balance
-- b012_013_038273.sql
-- 
-- 11    7/12/13 7:44a Chad.dinerman
-- DBA: CMD
-- Fixed Deprecated NOLOCK Syntax
-- 
-- 10    11/27/12 4:53p Jorge.villanueva
-- DEV: U.Vedavanam
-- DBA: J.Villanueva
-- B012_011_035887
-- Feature 52163: Modify function p21_fnt_artb_customer
-- 
-- 8     4/09/09 2:22p Steve.rosendale
-- DEV: TDD
-- DBA: SAR
-- Defect: 1048218
-- Script: apa026890a-c
-- 
-- 6     3/08/07 3:24p Chad.dinerman
-- same as previous
-- 
-- 5     3/08/07 3:14p Chad.dinerman
-- DEV: LSC
-- DBA: CMD
-- Scopus: 521426, 527763, 530820, 530289, 521845
-- Script: ana019319a-c
-- Added customer id range
-- 
-- 4     12/28/05 12:17p Chad_dinerman
-- DBA: CMD
-- DEV: SUB
-- FT: 20455
-- Script: ana014026c
-- Added reverse_payment_flag 
-- 
-- 3     12/15/05 2:29p Chad_dinerman
-- 
-- 2     12/15/05 8:20a Chad_dinerman
-- DEV: CMD
-- DBA: CMD
-- FT:  20455
-- Script: ana013898.sql
-- Added Date Params
-- 
-- 1     11/29/05 11:47a Chad_dinerman
-- DEV: CMD
-- DBA: CMD
-- FT: 20455
-- Script: ana013707
-- Created Function to remove #artb_temp
-- 
--
--
GO

