ALTER PROCEDURE rpt.usp_BIR2316_clientEmp_report_2019
(
    @clientUID UNIQUEIDENTIFIER,
    @clientEmpUIDs_client NVARCHAR(MAX),
    @Year INT,
    @inputValue TINYINT,
    @employeeTypeUIDs NVARCHAR(MAX),
    @departmentUIDs NVARCHAR(MAX),
    @siteUID NVARCHAR(MAX),
    @sectionUID NVARCHAR(MAX)
)
AS
BEGIN

    --DECLARE @clientUID UNIQUEIDENTIFIER
    --    = '2c923127-655f-4a32-aae4-77fb6f6454f3',
    --        @clientEmpUIDs_client NVARCHAR(MAX) = 'BC6608A1-03A2-4936-8DB7-0741D0ED1584,9131694D-D8DD-404F-BF52-89F4EE730973',
    --        @Year INT = 2018,
    --        @IsSubstitutedFiling BIT,
    --        @inputValue TINYINT,
    --        @employeeTypeUIDs NVARCHAR(MAX),
    --        @departmentUIDs NVARCHAR(MAX),
    --        @siteUID NVARCHAR(MAX),
    --        @sectionUID NVARCHAR(MAX);

    DECLARE @clientEmpID_list TABLE
    (
        clientEmpID BIGINT
    );

    DECLARE @employeeTypeIDList TABLE
    (
        EmployeeTypeID BIGINT
    );

    DECLARE @departmentIDList TABLE
    (
        DepartmentID BIGINT
    );

    DECLARE @sectionIDList TABLE
    (
        SectionID BIGINT
    );

    DECLARE @siteIDList TABLE
    (
        SiteId BIGINT
    );

    INSERT INTO @employeeTypeIDList
    SELECT TET.EmpTypeID
    FROM [dbo].[SplitByComma](@employeeTypeUIDs) AS SBC
        INNER JOIN dbo.tbl_EmpType AS TET
            ON TET.EmpTypeUID = SBC.Data
    WHERE Data != '';

    INSERT INTO @departmentIDList
    SELECT TD.DepartmentID
    FROM [dbo].[SplitByComma](@departmentUIDs) AS SBC
        INNER JOIN cc.tbl_Department AS TD
            ON TD.DepartmentUID = SBC.Data
    WHERE Data != '';

    INSERT INTO @sectionIDList
    SELECT TS.SectionID
    FROM [dbo].[SplitByComma](@sectionUID) AS SBC
        INNER JOIN cc.tbl_Section AS TS
            ON TS.SectionUID = SBC.Data
    WHERE Data != '';

    INSERT INTO @siteIDList
    SELECT TS1.SiteID
    FROM [dbo].[SplitByComma](@siteUID) AS SBC
        INNER JOIN cc.tbl_Site AS TS1
            ON TS1.SiteUID = SBC.Data
    WHERE Data != '';



    INSERT INTO @clientEmpID_list
    SELECT TCE.ClientEmpID
    FROM [dbo].[SplitByComma](@clientEmpUIDs_client) AS SBC
        INNER JOIN dbo.tbl_ClientEmp AS TCE
            ON TCE.ClientEmpUID = SBC.Data
    WHERE Data != '';


    DECLARE @IsSubstitutedTable TABLE
    (
        ClientEmpID BIGINT,
        Category INT,
        IsSubstituted BIGINT
    );

    INSERT INTO @IsSubstitutedTable
    (
        ClientEmpID,
        Category,
        IsSubstituted
    )
    SELECT CEIL.clientEmpID,
           TETBY.Category,
           IIF(TETBY.Category = 3 AND TETBY.IsMinimumWage = 0 AND ISNULL(TETBY.YDaily_MWE, 0) = 0, 1, 0) IsSubstitutedFilling
    FROM tmp.tbl_EmployeeTransactionByYear AS TETBY
        INNER JOIN @clientEmpID_list AS CEIL
            ON CEIL.clientEmpID = TETBY.ClientEmpID
        INNER JOIN dbo.tbl_ClientEmp AS TCE
            ON TCE.ClientEmpID = CEIL.clientEmpID
    WHERE TETBY.Category IN ( 1, 3, 4 )
          AND TETBY.Year = @Year
          AND TETBY.ClientUID = @clientUID
          AND
          (
              SeparatedOn IS NULL
              OR SeparatedOn > DATEFROMPARTS(@Year, 12, 31)
          )
          AND
          (
              HoldOn IS NULL
              OR HoldOn > DATEFROMPARTS(@Year, 12, 31)
          );


    DECLARE @statutoryTableKeyUID UNIQUEIDENTIFIER,
            @signatoryName VARCHAR(MAX),
            @signatoryPosition VARCHAR(MAX),
            @signatoryImagePath1 NVARCHAR(MAX),
            @signatoryImagePath2 NVARCHAR(MAX),
            @erTIN VARCHAR(12);

    SET @erTIN =
    (
        SELECT TOP 1
               REPLACE(Value, '-', '')
        FROM dbo.tbl_KeyValue AS TKV
            INNER JOIN dbo.tbl_Client C
                ON C.KeyValueCollID = TKV.KeyValueCollID
        WHERE KeyID = 9
              AND ClientID =
              (
                  SELECT ClientID FROM dbo.tbl_Client WHERE ClientUID = @clientUID
              )
              AND TKV.DeletedOn IS NULL
        ORDER BY TKV.CreatedOn DESC
    );

    SELECT @signatoryName = FullName,
           @signatoryPosition = ReferenceID,
           @signatoryImagePath1 = TFE.FilePath,
           @signatoryImagePath2 = TFE.FilePath
    FROM cc.tbl_Signatory AS TS
        INNER JOIN dbo.tbl_Emp AS TE
            ON TE.EmpID = TS.EmpID
        INNER JOIN dbo.tbl_Client AS TC
            ON TC.ClientID = TS.ClientID
        INNER JOIN dbo.tbl_FileEntry AS TFE
            ON TFE.FileEntryID = TS.FileEntryID
    WHERE StatutoryTableKeyID = 4
          AND TC.ClientUID = @clientUID;



    --DECLARE @tblDependent TABLE
    --    (
    --      ClientEmpID BIGINT
    --    , FullName NVARCHAR(MAX)
    --    , BirthDate DATE
    --    , RowNum INT
    --    )
    --INSERT  INTO @tblDependent
    --        SELECT
    --            VCE.ClientEmpID
    --          , VD.FullName
    --          , VD.Birthdate
    --          , ROW_NUMBER() OVER ( PARTITION BY VCE.ClientEmpID ORDER BY VD.DependentID ) RowNum
    --        FROM
    --            dbo.tbl_ClientEmp AS VCE
    --        INNER JOIN dbo.tbl_Client AS VC
    --            ON VC.ClientID = VCE.ClientID
    --        LEFT JOIN dbo.tbl_Dependent AS VD
    --            ON VD.DependentCollID = VCE2.DependentCollID
    --        WHERE
    --            VC.ClientUID = @clientUID

    SELECT FORMAT(UETBY.Year, '#     #     #     #') Col1,
           CASE
               WHEN YEAR(EmploymentFrom) < @Year THEN
                   '0    1     0    1'
               ELSE
                   FORMAT(CAST(FORMAT(EmploymentFrom, 'MMdd') AS BIGINT), '#    #    #    #')
           END Col2a,                                                                                                --period from
           CASE
               WHEN
               (
                   YEAR(EmploymentTo) > @Year
                   OR EmploymentTo IS NULL
               ) THEN
                   '1    2     3    1'
               ELSE
                   FORMAT(CAST(FORMAT(EmploymentTo, 'MMdd') AS BIGINT), '#    #    #    #')
           END Col2b,                                                                                                --period to


           utl.ufn_ThreeDigitSpacePadder_2316(EmployeeTIN, 1, 3) Col3a,
           utl.ufn_ThreeDigitSpacePadder_2316(EmployeeTIN, 4, 3) Col3b,
           utl.ufn_ThreeDigitSpacePadder_2316(EmployeeTIN, 7, 3) Col3c,
           UETBY.FullName Col4,
                                                                                                                     --, CAST(FORMAT(CAST(EmployeeBranchCode AS INT) , '000') AS NVARCHAR(30)) Col5 -- employee rdo code
                                                                                                                     --FORMAT(CAST(utl.ufn_RemoveNonNumericCharacter(TKV_BIRRDO.Value) AS BIGINT), '#    #    #'

           FORMAT(
                     CAST(utl.ufn_RemoveNonNumericCharacter(TKV_BIRRDO.Value) AS BIGINT),
                     IIF(LEN(CAST(utl.ufn_RemoveNonNumericCharacter(TKV_BIRRDO.Value) AS BIGINT)) = 2,
                         IIF(LEN(CAST(utl.ufn_RemoveNonNumericCharacter(TKV_BIRRDO.Value) AS BIGINT)) = 1,
                             '0   #   #',
                             '0   0   #'),
                         '#   #   #')
                 ) Col5,                                                                                             -- client rdo code
           TEIA_addr.Address Col6,                                                                                   -- registered address
           NULL Col6a,                                                                                               -- reg zip code
           TEIA_permAddr.Address Col6b,                                                                              -- local home address
           NULL Col6c,                                                                                               -- local home zip code
           NULL Col6d,                                                                                               -- foreign address
           NULL Col6e,                                                                                               -- foreign zip code
           CASE
               WHEN UETBY.ClientID = 40261 THEN
                   NULL
               --IN ( 40262 , 40261) THEN NULL
               --, CASE WHEN CONVERT(VARCHAR(10),Birthdate,112) <'19500101' THEN NULL
               ELSE
                   FORMAT(
                             CAST(FORMAT(UETBY.Birthdate, 'MMddyyyy') AS BIGINT),
                             IIF(LEN(CAST(FORMAT(UETBY.Birthdate, 'MMddyyyy') AS BIGINT)) = 8,
                                 '#    #   #   #   #   #   #   #',
                                 '0    #   #   #   #   #   #   #')
                         )
           END Col7,
           EmployeeTelephoneNumber Col8,                                                                             -- telephone number
           IIF(UETBY.CivilStatus = 1, IIF(TC.ClientID = 40440, 'X', NULL), NULL) Col9a,                              -- exemption status Single
                                                                                                                     --NULL Col9a,                                                                                     -- exemption status Single
           IIF(UETBY.CivilStatus IN ( 2, 4 ), IIF(TC.ClientID = 40440, 'X', NULL), NULL) Col9b,                      -- exemption status Maried
                                                                                                                     ----NULL Col9b,                                                                                       -- exemption status Maried
           NULL Col9aa,                                                                                              -- wife claim dependent YES
           NULL Col9ab,                                                                                              -- wife claim dependent NO
           DependentFullName1 Col10a,
           DependentBirthDate1 Col11a,
           DependentFullName2 Col10b,
           DependentBirthDate2 Col11b,
           DependentFullName3 Col10c,
           DependentBirthDate3 Col11c,
           DependentFullName4 Col10d,
           DependentBirthDate4 Col11d,
           CurDaily_MWE Col12,                                                                                       -- statutory Minimum wager rate per day
           CurMonthly_MWE Col13,                                                                                     -- statutory Minimum wager rate per month
           IIF(CurDaily_MWE IS NOT NULL, 'X', NULL) Col14,                                                           -- minimum wage earner
                                                                                                                     --SUBSTRING(REPLACE(@erTIN, '-', ''), 1, 3) Col15a,
                                                                                                                     --SUBSTRING(REPLACE(@erTIN, '-', ''), 4, 3) Col15b,
                                                                                                                     --SUBSTRING(REPLACE(@erTIN, '-', ''), 7, 3) Col15c,
           utl.ufn_ThreeDigitSpacePadder_2316(@erTIN, 1, 3) Col15a,
           utl.ufn_ThreeDigitSpacePadder_2316(@erTIN, 4, 3) Col15b,
           utl.ufn_ThreeDigitSpacePadder_2316(@erTIN, 7, 3) Col15c,
           UETBY.ClientName Col16,
           ClientAddress Col17,
           FORMAT(CAST(utl.ufn_RemoveNonNumericCharacter(TKV_EYerZipCode.Value) AS BIGINT), '#   #   #   #') Col17a, -- employer zip code
                                                                                                                     -- SUBSTRING(REPLACE(ISNULL(YPrevEyerTIN, YPrevEyerTIN_MWE), '-', ''), 1, 3) Col18a,                -- prev employer tin
                                                                                                                     -- SUBSTRING(REPLACE(ISNULL(YPrevEyerTIN, YPrevEyerTIN_MWE), '-', ''), 4, 3) Col18b,                -- prev employer tin
                                                                                                                     -- SUBSTRING(REPLACE(ISNULL(YPrevEyerTIN, YPrevEyerTIN_MWE), '-', ''), 7, 3) Col18c,                -- prev employer tin


           utl.ufn_ThreeDigitSpacePadder_2316(ISNULL(YPrevEyerTIN, YPrevEyerTIN_MWE), 1, 3) Col18a,
           utl.ufn_ThreeDigitSpacePadder_2316(ISNULL(YPrevEyerTIN, YPrevEyerTIN_MWE), 4, 3) Col18b,
           utl.ufn_ThreeDigitSpacePadder_2316(ISNULL(YPrevEyerTIN, YPrevEyerTIN_MWE), 7, 3) Col18c,
           ISNULL(YPrevEyerName, YPrevEyerName_MWE) Col19,                                                           -- prev employer name
           ISNULL(YPrevEyerAddress, YPrevEyerAddress_MWE) Col20,                                                     -- prev employer address
           NULL Col20a,                                                                                              -- prev employer zip code
                                                                                                                     --ISNULL(YGrossTaxableIncome , 0.00) + ISNULL(YTotalNonTaxableIncome ,0.00)
           ISNULL(YGrossPay, 0.00) Col21,
           ISNULL(YTotalNonTaxableIncome, 0.00) + ISNULL(YStatutoryDeduction, 0.00)
           + ISNULL(YAdditionalAllowableDeduction, 0) Col22,
           ISNULL(YGrossTaxableIncome, 0.00)
           - (ISNULL(YStatutoryDeduction, 0.00) + ISNULL(YAdditionalAllowableDeduction, 0)) Col23,
           ISNULL(YPrevEYerGrossTaxableIncome, 0.00) - ISNULL(YPrevEYerAllowableDeductions, 0.00) Col24,
           (ISNULL(YGrossTaxableIncome, 0.00) + ISNULL(YPrevEYerGrossTaxableIncome, 0.00)
            - ISNULL(YPrevEYerAllowableDeductions, 0.00)
           )
           - (ISNULL(YStatutoryDeduction, 0.00) + ISNULL(YAdditionalAllowableDeduction, 0)) Col25,
           IIF(@Year <= 2017, ISNULL(TaxExemptionAmount, 0.00), 0.00) Col26,
           0.00 Col27,                                                                                               --HMO PAID
           CASE
               WHEN (ISNULL(YGrossTaxableIncome, 0.00) + ISNULL(YPrevEYerGrossTaxableIncome, 0.00)
                     - ISNULL(YPrevEYerAllowableDeductions, 0.00)
                    )
                    - (ISNULL(TaxExemptionAmount, 0.00) + ISNULL(YStatutoryDeduction, 0.00)
                       + ISNULL(YAdditionalAllowableDeduction, 0)
                      ) > 0 THEN
           (ISNULL(YGrossTaxableIncome, 0.00) + ISNULL(YPrevEYerGrossTaxableIncome, 0.00)
            - ISNULL(YPrevEYerAllowableDeductions, 0.00)
           )
           - (ISNULL(TaxExemptionAmount, 0.00) + ISNULL(YStatutoryDeduction, 0.00)
              + ISNULL(YAdditionalAllowableDeduction, 0)
             )
               ELSE
                   0
           END Col28,                                                                                                --NET Taxable income
           (ISNULL(YTaxDue, 0.00)) Col29,                                                                            --Tax Due
           ISNULL(YWitholdingTaxAllMonths, 0.00) Col30a,                                                             -- pres witholding tax
           ISNULL(YPrevEYerTaxWithheld, 0.00) Col30b,                                                                -- prev witholding tax
           (ISNULL(YWitholdingTaxAllMonths, 0.00) + ISNULL(YPrevEYerTaxWithheld, 0.00)) Col31,                       -- tax adjusted
           ISNULL(YBasicPay_MWE, 0.00) - ISNULL(YStatutoryDeduction_MWE, 0.00)
           - ISNULL(YAdditionalAllowableDeduction_MWE, 0.00) Col32,
           ISNULL(YTotalOTHoliday_MWE, 0.00) Col33,
           ISNULL(YTotalOtherOvertime_MWE, 0.00) Col34,
           ISNULL(YTotalOTNightDiff_MWE, 0.00) Col35,
           ISNULL(YTotalEarnHazard_MWE, 0.00) Col36,
           ISNULL(YNonTaxableBonus, 0.00) Col37,
           ISNULL(YTotalDeminimis, 0.00) Col38,
           ISNULL(YStatutoryDeduction, 0.00) + ISNULL(YAdditionalAllowableDeduction, 0)
           + ISNULL(YStatutoryDeduction_MWE, 0.00) + ISNULL(YAdditionalAllowableDeduction_MWE, 0.00) Col39,
           ISNULL(YOtherNonTaxableIncome, 0.00) - ISNULL(YTotalDeminimis, 0.00) Col40,
           ISNULL(YTotalNonTaxableIncome, 0.00) + ISNULL(YStatutoryDeduction, 0.00)
           + ISNULL(YAdditionalAllowableDeduction, 0) Col41,
           (ISNULL(YBasicPay, 0.00) - (ISNULL(YStatutoryDeduction, 0.00) + ISNULL(YAdditionalAllowableDeduction, 0))
            + ISNULL(TETBYCF_bp.Amount, 0.00)
           ) Col42,
           ISNULL(TETBYCF_rep.Amount, 0.00) Col43,                                                                   -- taxable representation
           ISNULL(TETBYCF_transp.Amount, 0.00) Col44,                                                                -- taxable transportation
           ISNULL(TETBYCF_cola.Amount, 0.00) Col45,                                                                  -- taxable cola
           ISNULL(TETBYCF_fha.Amount, 0.00) Col46,                                                                   -- taxable fixed housing allowance
           ISNULL(VCERFM_47a.EarnReportFieldMapDisplayName, VCERFM_47a.ReportFieldDisplayName) Col47aa,              -- taxable specify
           ISNULL(TETBYCF_47a.Amount, 0.00) Col47ab,                                                                 -- taxable specify
           ISNULL(VCERFM_47b.EarnReportFieldMapDisplayName, VCERFM_47b.ReportFieldDisplayName) Col47ba,              -- taxable specify
           ISNULL(TETBYCF_47b.Amount, 0.00) Col47bb,                                                                 -- taxable specify
           ISNULL(TETBYCF_com.Amount, 0.00) Col48,                                                                   -- taxable Commission
           ISNULL(TETBYCF_ps.Amount, 0.00) Col49,                                                                    -- taxable Profit Sharing
           ISNULL(TETBYCF_fdf.Amount, 0.00) Col50,                                                                   -- taxable Director's fees
           ISNULL(YTaxableBonus, 0.00) + ISNULL(TETBYCF_t13thOben.Amount, 0.00) Col51,
           ISNULL(YTotalEarnHazard, 0.00) Col52,
           ISNULL(YOvertime, 0.00) + ISNULL(TETBYCF_ot.Amount, 0.00) Col53,
           ISNULL(
                     ISNULL(VCERFM_54a.EarnReportFieldMapDisplayName, VCERFM_54a.ReportFieldDisplayName),
                     'Salaries and other form of compensation'
                 ) Col54aa,                                                                                          -- taxable specify
           ISNULL(YGrossTaxableIncome, 0.00) - (ISNULL(YBasicPay, 0.00))
           - (ISNULL(YTaxableBonus, 0.00) + ISNULL(YTotalEarnHazard, 0.00) + ISNULL(YOvertime, 0.00)
              + ISNULL(TETBYCF_bp.Amount, 0.00) + ISNULL(TETBYCF_rep.Amount, 0.00)
              + ISNULL(TETBYCF_transp.Amount, 0.00) + ISNULL(TETBYCF_cola.Amount, 0.00)
              + ISNULL(TETBYCF_fha.Amount, 0.00) + ISNULL(TETBYCF_47a.Amount, 0.00) + ISNULL(TETBYCF_47b.Amount, 0.00)
              + ISNULL(TETBYCF_com.Amount, 0.00) + ISNULL(TETBYCF_ps.Amount, 0.00) + ISNULL(TETBYCF_fdf.Amount, 0.00)
              + ISNULL(TETBYCF_t13thOben.Amount, 0.00) + ISNULL(TETBYCF_ot.Amount, 0.00)
              + ISNULL(TETBYCF_54b.Amount, 0.00)
             ) --+ ISNULL(TETBYCF_54a.Amount , 0.00) -- THIS ALREADY ADDED TO GROSS TAX
           Col54ab,                                                                                                  -- taxable specify
           ISNULL(VCERFM_54b.EarnReportFieldMapDisplayName, VCERFM_54b.ReportFieldDisplayName) Col54ba,              -- taxable specify
           ISNULL(TETBYCF_54b.Amount, 0.00) Col54bb,                                                                 -- taxable specify
           ISNULL(YGrossTaxableIncome, 0.00)
           - (ISNULL(YStatutoryDeduction, 0.00) + ISNULL(YAdditionalAllowableDeduction, 0)) Col55,
           NULL Col56,
           UETBY.FullName Col57,
           ISNULL(TEI_passport.PassportID, '') Col58,

                                                                                                                     --FullName Col59 ,
           IIF(IST.IsSubstituted = 1, UETBY.FullName, NULL) Col59,
           @signatoryName signatoryName,
           @signatoryPosition signatoryPosition,
           @signatoryImagePath1 signatoryImagePath1,
           IIF(IST.IsSubstituted = 1, @signatoryImagePath2, NULL) signatoryImagePath2,
           TCE.ClientEmpCode,
           TC.ClientID,
           TCE.ClientEmpID,
           IIF(TC.ClientID IN ( 40440, 40328 ), TCE.ClientEmpCode, NULL) EmpCodeHeaderDisplay
    FROM
        --rpt.ufn__EmployeeTransactionByYear(@clientUID , @Year , 1 , NULL , NULL , NULL , NULL) AS UETBY
        tmp.tbl_EmployeeTransactionByYear AS UETBY
        INNER JOIN dbo.tbl_ClientEmp AS TCE
            ON TCE.ClientEmpID = UETBY.ClientEmpID
        LEFT JOIN dbo.tbl_EmpInfo AS TEI_passport
            ON TEI_passport.EmpID = TCE.EmpID
        LEFT JOIN dbo.tbl_EmpInfoAddr AS TEIA_addr
            ON TEIA_addr.EmpID = TCE.EmpID
               --AND TEIA_addr.EmpinfoAddrname = 'Address'
               AND TEIA_addr.AddressBaseID = 1
        LEFT JOIN dbo.tbl_EmpInfoAddr AS TEIA_permAddr
            ON TEIA_permAddr.EmpID = TCE.EmpID
               AND TEIA_permAddr.AddressBaseID = 3
        INNER JOIN dbo.tbl_Client AS TC
            ON TC.ClientID = UETBY.ClientID
        INNER JOIN c.tbl_ConfEmp AS VCE2
            ON VCE2.ClientEmpID = UETBY.ClientEmpID
        LEFT JOIN @IsSubstitutedTable AS IST
            ON IST.ClientEmpID = TCE.ClientEmpID
        OUTER APPLY
    (
        SELECT Value
        FROM dbo.tbl_KeyValue AS TKV
        WHERE TKV.KeyValueCollID = TC.KeyValueCollID
              AND TKV.KeyID = 10004
    ) TKV_EYerZipCode
        OUTER APPLY
    (
        SELECT Value
        FROM dbo.tbl_KeyValue AS TKV
        WHERE TKV.KeyValueCollID = TC.KeyValueCollID
              AND TKV.KeyID = 10002
    ) TKV_BIRRDO
        LEFT JOIN tmp.tbl_EmployeeTransactionByYearCustomField AS TETBYCF_bp
            ON TETBYCF_bp.EmployeeTransactionByYearID = UETBY.EmployeeTransactionByYearID
               AND TETBYCF_bp.ReportFieldID = 1
        LEFT JOIN tmp.tbl_EmployeeTransactionByYearCustomField AS TETBYCF_rep
            ON TETBYCF_rep.EmployeeTransactionByYearID = UETBY.EmployeeTransactionByYearID
               AND TETBYCF_rep.ReportFieldID = 2
        LEFT JOIN tmp.tbl_EmployeeTransactionByYearCustomField AS TETBYCF_transp
            ON TETBYCF_transp.EmployeeTransactionByYearID = UETBY.EmployeeTransactionByYearID
               AND TETBYCF_transp.ReportFieldID = 3
        LEFT JOIN tmp.tbl_EmployeeTransactionByYearCustomField AS TETBYCF_cola
            ON TETBYCF_cola.EmployeeTransactionByYearID = UETBY.EmployeeTransactionByYearID
               AND TETBYCF_cola.ReportFieldID = 4
        LEFT JOIN tmp.tbl_EmployeeTransactionByYearCustomField AS TETBYCF_fha
            ON TETBYCF_fha.EmployeeTransactionByYearID = UETBY.EmployeeTransactionByYearID
               AND TETBYCF_fha.ReportFieldID = 5
        LEFT JOIN tmp.tbl_EmployeeTransactionByYearCustomField AS TETBYCF_47a
            ON TETBYCF_47a.EmployeeTransactionByYearID = UETBY.EmployeeTransactionByYearID
               AND TETBYCF_47a.ReportFieldID = 6
        OUTER APPLY
    (
        SELECT TOP 1
               EarnReportFieldMapDisplayName,
               ReportFieldDisplayName
        FROM cc.vw__CCEarningReportFieldMap AS VCERFM_47a_temp
        WHERE VCERFM_47a_temp.ClientID = TC.ClientID
              AND VCERFM_47a_temp.ReportFieldID = 6
        ORDER BY VCERFM_47a_temp.EarnReportFieldMapID DESC
    ) VCERFM_47a
        LEFT JOIN tmp.tbl_EmployeeTransactionByYearCustomField AS TETBYCF_47b
            ON TETBYCF_47b.EmployeeTransactionByYearID = UETBY.EmployeeTransactionByYearID
               AND TETBYCF_47b.ReportFieldID = 7
        OUTER APPLY
    (
        SELECT TOP 1
               EarnReportFieldMapDisplayName,
               ReportFieldDisplayName
        FROM cc.vw__CCEarningReportFieldMap AS VCERFM_47b_temp
        WHERE VCERFM_47b_temp.ClientID = TC.ClientID
              AND VCERFM_47b_temp.ReportFieldID = 7
        ORDER BY VCERFM_47b_temp.EarnReportFieldMapID DESC
    ) VCERFM_47b
        LEFT JOIN tmp.tbl_EmployeeTransactionByYearCustomField AS TETBYCF_com
            ON TETBYCF_com.EmployeeTransactionByYearID = UETBY.EmployeeTransactionByYearID
               AND TETBYCF_com.ReportFieldID = 8
        LEFT JOIN tmp.tbl_EmployeeTransactionByYearCustomField AS TETBYCF_ps
            ON TETBYCF_ps.EmployeeTransactionByYearID = UETBY.EmployeeTransactionByYearID
               AND TETBYCF_ps.ReportFieldID = 9
        LEFT JOIN tmp.tbl_EmployeeTransactionByYearCustomField AS TETBYCF_fdf
            ON TETBYCF_fdf.EmployeeTransactionByYearID = UETBY.EmployeeTransactionByYearID
               AND TETBYCF_fdf.ReportFieldID = 10
        LEFT JOIN tmp.tbl_EmployeeTransactionByYearCustomField AS TETBYCF_t13thOben
            ON TETBYCF_t13thOben.EmployeeTransactionByYearID = UETBY.EmployeeTransactionByYearID
               AND TETBYCF_t13thOben.ReportFieldID = 11
        LEFT JOIN tmp.tbl_EmployeeTransactionByYearCustomField AS TETBYCF_ot
            ON TETBYCF_ot.EmployeeTransactionByYearID = UETBY.EmployeeTransactionByYearID
               AND TETBYCF_ot.ReportFieldID = 13
        LEFT JOIN tmp.tbl_EmployeeTransactionByYearCustomField AS TETBYCF_54a
            ON TETBYCF_54a.EmployeeTransactionByYearID = UETBY.EmployeeTransactionByYearID
               AND TETBYCF_54a.ReportFieldID = 14
        OUTER APPLY
    (
        SELECT TOP 1
               EarnReportFieldMapDisplayName,
               ReportFieldDisplayName
        FROM cc.vw__CCEarningReportFieldMap AS VCERFM_54a_temp
        WHERE VCERFM_54a_temp.ClientID = TC.ClientID
              AND VCERFM_54a_temp.ReportFieldID = 14
        ORDER BY VCERFM_54a_temp.EarnReportFieldMapID DESC
    ) VCERFM_54a
        LEFT JOIN tmp.tbl_EmployeeTransactionByYearCustomField AS TETBYCF_54b
            ON TETBYCF_54b.EmployeeTransactionByYearID = UETBY.EmployeeTransactionByYearID
               AND TETBYCF_54b.ReportFieldID = 15
        OUTER APPLY
    (
        SELECT TOP 1
               EarnReportFieldMapDisplayName,
               ReportFieldDisplayName
        FROM cc.vw__CCEarningReportFieldMap AS VCERFM_54b_temp
        WHERE VCERFM_54b_temp.ClientID = TC.ClientID
              AND VCERFM_54b_temp.ReportFieldID = 15
        ORDER BY VCERFM_54b_temp.EarnReportFieldMapID DESC
    ) VCERFM_54b
    --OUTER APPLY (
    --              SELECT
    --                FullName
    --              , BirthDate
    --              FROM
    --                @tblDependent AS VD1_temp
    --              WHERE
    --                VD1_temp.ClientEmpID = UETBY.ClientEmpID
    --                AND VD1_temp.RowNum = 1
    --            ) AS VD1
    --OUTER APPLY (
    --              SELECT
    --                FullName
    --              , BirthDate
    --              FROM
    --                @tblDependent AS VD2_temp
    --              WHERE
    --                VD2_temp.ClientEmpID = UETBY.ClientEmpID
    --                AND VD2_temp.RowNum = 2
    --            ) AS VD2
    --OUTER APPLY (
    --              SELECT
    --                FullName
    --              , BirthDate
    --              FROM
    --                @tblDependent AS VD3_temp
    --              WHERE
    --                VD3_temp.ClientEmpID = UETBY.ClientEmpID
    --                AND VD3_temp.RowNum = 3
    --            ) AS VD3
    --OUTER APPLY (
    --              SELECT
    --                FullName
    --              , BirthDate
    --              FROM
    --                @tblDependent AS VD4_temp
    --              WHERE
    --                VD4_temp.ClientEmpID = UETBY.ClientEmpID
    --                AND VD4_temp.RowNum = 4
    --            ) AS VD4
    WHERE UETBY.Category = 6
          AND (ISNULL(YWitholdingTaxAllMonths, 0.00) + ISNULL(YPrevEYerTaxWithheld, 0.00)) = UETBY.YTaxDue
          AND UETBY.Year = @Year
          AND
          (
              @clientEmpUIDs_client IS NULL
              OR TCE.ClientEmpID IN (
                                        SELECT clientEmpID FROM @clientEmpID_list
                                    )
          )
          --AND
          --(
          --    (@inputValue IS NULL)
          --    OR
          --    (
          --        @inputValue IN ( 1, -1 )
          --        AND
          --        (
          --            (
          --                SeparatedOn IS NOT NULL
          --                AND SeparatedOn <= DATEFROMPARTS(@Year, 12, 31)
          --            )
          --            OR
          --            (
          --                HoldOn IS NOT NULL
          --                AND HoldOn <= DATEFROMPARTS(@Year, 12, 31)
          --            )
          --        )
          --        AND
          --        (
          --            YPrevEyerName IS NULL
          --            AND YPrevEyerName_MWE IS NULL
          --        )
          --        AND (YBasicPay != 0)
          --    ) ------- ALP 7.1
          --    OR
          --    (
          --        @inputValue IN ( 2, -2 )
          --        AND
          --        (
          --            SeparatedOn IS NULL
          --            OR SeparatedOn > DATEFROMPARTS(@Year, 12, 31)
          --        )
          --        AND
          --        (
          --            HoldOn IS NULL
          --            OR HoldOn > DATEFROMPARTS(@Year, 12, 31)
          --        )
          --        AND
          --        (
          --            YPrevEyerName IS NULL
          --            AND YPrevEyerName_MWE IS NULL
          --        )
          --        AND (YBasicPay != 0)
          --    ) ------- ALP 7.3
          --    OR
          --    (
          --        @inputValue IN ( 3, -3 )
          --        AND
          --        (
          --            YPrevEyerName IS NOT NULL
          --            AND YBasicPay != 0
          --        )
          --    ) ------- ALP 7.4
          --    OR
          --    (
          --        @inputValue IN ( 4, -4 )
          --        AND
          --        (
          --            YBasicPay_MWE != 0
          --            OR YPrevEyerName_MWE IS NOT NULL
          --        )
          --    )
          --)
          AND
          (
              @employeeTypeUIDs IS NULL
              OR VCE2.EmpTypeID IN (
                                       SELECT EmployeeTypeID FROM @employeeTypeIDList AL
                                   )
          )
          AND
          (
              @departmentUIDs IS NULL
              OR VCE2.DepartmentID IN (
                                          SELECT DepartmentID FROM @departmentIDList AL
                                      )
          )
          AND
          (
              @sectionUID IS NULL
              OR VCE2.SectionID IN (
                                       SELECT SectionID FROM @sectionIDList AL
                                   )
          )
          AND
          (
              @siteUID IS NULL
              OR VCE2.SiteID IN (
                                    SELECT SiteId FROM @siteIDList AL
                                )
          )
    ORDER BY UETBY.FullName;

--*/
END;

GO