ALTER PROCEDURE [dbo].[TR_REQ_PAY_AUTO_GET_LIST_NOT_CONFIRM_SEND_MAIL]
@p_APP_DT VARCHAR(20) = NULL
AS
	SET @p_APP_DT = GETDATE();
	--SET @p_APP_DT = '2023-05-25T11:22:00';

	SELECT A.REQ_PAY_AUTO_ID, A.REQ_PAY_AUTO_CODE, B.EXPECTED_DT,
	A.BRANCH_ID, A.DEP_ID, A.BRANCH_CREATE, A.PROCESS, 
	A.MAKER_ID, A.TRANSFER_USER_RECEIVE, A.CHECKER_ID, 
	A.TRANSFER_MAKER, A.MAKER_ID_KT, A.CHECKER_ID_KT
	FROM TR_REQ_PAYMENT_AUTO A
	LEFT JOIN TR_REQ_PAY_AUTO_SCHEDULE_DETAIL B ON A.REQ_PAY_AUTO_ID = B.REQ_PAY_AUTO_ID
	WHERE 1=1
	AND A.REQ_PAY_AUTO_TYPE = 'A'
	AND A.AUTH_STATUS_KT = 'A' 
	AND REQ_PAY_AUTO_STATUS = 'EFFECTIVE' 
	AND A.RECORD_STATUS = '1' 
	AND A.CONTRACT_ID IS NOT NULL
	AND	A.IS_MAKER_CONFIRM_CHECKBOX = '1' 
	AND ISNULL(A.IS_MAKER_CONFIRM, '') <> '1'
	AND CONVERT(DATE, DATEADD(DAY, 7, @p_APP_DT), 103) >= CONVERT(DATE, B.EXPECTED_DT, 103)
	--AND CONVERT(DATE, @p_APP_DT, 103) < EXPECTED_DT
	AND ISNULL(B.PAY_AUTO_STATUS, '') <> 'Y'

	RETURN '0'