USE [CAPDB_RET_UAT3]
GO
/****** Object:  StoredProcedure [dbo].[usp_GetUnConcentNCB]    Script Date: 4/28/2021 5:25:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_GetUnConcentNCB]
@PYear INT, @PMonth INT, @Pflag VARCHAR (01)
AS
BEGIN
    SET NOCOUNT ON;
    IF @Pflag = 'G'
        BEGIN
            SELECT   ROW_NUMBER() OVER (ORDER BY x.brnCde) AS rNo,
                     x.brnCde,
                     x.brnName,
                     x.email,
                     '' AS applno,
                     GETDATE() AS tranDte,
                     '' AS custCde,
                     '' AS titlename,
                     '' AS fstNamTH,
                     '' AS surNamTH,
                     '' AS FullName,
                     count(*) AS cnt
            FROM     (SELECT ROW_NUMBER() OVER (PARTITION BY a.brnCde ORDER BY a.brnCde, a.applno, a.tranDte) AS rNo,
                             a.brnCde,
                             c.thNam AS brnName,
                             isnull(c.email, '') AS email,
                             a.applno,
                             a.tranDte,
                             b.custCde,
                             isnull(e.thDesc, '') AS titlename,
                             isnull(d.fstNamTH, '') AS fstNamTH,
                             isnull(d.surNamTH, '') AS surNamTH
                      FROM   CBMSAPP010 AS a
                             LEFT OUTER JOIN
                             cbmsapp011 AS b
                             ON (a.movmntID = b.movmntID
                                 AND b.recsts = 'A')
                             LEFT OUTER JOIN
                             CBTBSYS010 AS c
                             ON (a.brnCde = c.brnCde)
                             LEFT OUTER JOIN
                             CBMSCIF010 AS d
                             ON (b.custCde = d.custCde
                                 AND d.recsts = 'A')
                             LEFT OUTER JOIN
                             CBTBGNP901 AS e
                             ON (e.prmtyp = 'titlename'
                                 AND e.prmCde = d.titleCde)
                      WHERE  year(a.trandte) = @PYear
                             AND month(a.trandte) = @PMonth
                             --AND isnull(b.consent, '') = ''
							 and isnull(b.consentRcpSts,'N') <> 'Y'
							 and ISNULL(b.ncbSndSts,0)  = 1
                             AND isnull(a.brnCde, '') <> ''
							 AND a.appstate >= 30
                             AND a.recsts = 'A') AS x
            GROUP BY x.brnCde, x.brnName, x.email
            ORDER BY x.brnCde, x.brnName, x.email;
        END
    ELSE
        BEGIN
            SELECT   ROW_NUMBER() OVER (PARTITION BY a.brnCde ORDER BY a.brnCde, a.applno, a.tranDte) AS rNo,
                     a.brnCde,
                     c.thNam AS brnName,
                     isnull(c.email, '') AS email,
                     a.applno,
                     a.tranDte,
                     b.custCde,
                     isnull(e.thDesc, '') AS titlename,
                     isnull(d.fstNamTH, '') AS fstNamTH,
                     isnull(d.surNamTH, '') AS surNamTH,
                     0 AS cnt,
                     '' AS FullName
            FROM     CBMSAPP010 AS a
                     LEFT OUTER JOIN
                     cbmsapp011 AS b
                     ON (a.movmntID = b.movmntID
                         --AND b.cusTyp = '01'
						 AND (b.cusTyp = '01' OR b.cusTyp = '02')
                         AND b.recsts = 'A')
                     LEFT OUTER JOIN
                     CBTBSYS010 AS c
                     ON (a.brnCde = c.brnCde)
                     LEFT OUTER JOIN
                     CBMSCIF010 AS d
                     ON (b.custCde = d.custCde
                         AND d.recsts = 'A')
                     LEFT OUTER JOIN
                     CBTBGNP901 AS e
                     ON (e.prmtyp = 'titlename'
                         AND e.prmCde = d.titleCde)
            WHERE    year(a.trandte) = @PYear
                     AND month(a.trandte) = @PMonth
                     --AND isnull(b.consent, '') = ''
					 and isnull(b.consentRcpSts,'N') <> 'Y'					
					 and ISNULL(b.ncbSndSts,0)  = 1
                     AND isnull(a.brnCde, '') <> ''
					 AND a.appstate >= 30
                     AND a.recsts = 'A'
            ORDER BY a.brnCde, a.applno, a.tranDte;
        END
END