IBANK - PROJECT

??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