CLIENT SUPPORT » Asuransi Bintang - Existing

??USE [Bintang_Prod]

GO

/****** Object:  StoredProcedure [dbo].[Rpt_Bintang_HR_PA_OffLet_PKWT_New_Amount]    Script Date: 10/11/2021 4:52:11 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[Rpt_Bintang_HR_PA_OffLet_PKWT_New_Amount]

    (

      @emp_id VARCHAR(10)

    )

AS 

--DECLARE @emp_id VARCHAR(10) = '90001763';

DECLARE @now VARCHAR(20) = dbo.fn_formatdatetime(GETDATE(), 'yyyymmdd');

DECLARE @landscape VARCHAR(100);

SET @landscape = '100';

DECLARE @temp_off VARCHAR (100)

SET @temp_off=( SELECT template_offering

FROM hr_md_app_md0028			

WHERE emp_id = @emp_id 

and start_date <= @now

and end_date >= @now)

DECLARE @Tbl_Amount TABLE

(

    emp_id VARCHAR(8),

    ptkp DECIMAL(18, 0),

    _1000 DECIMAL(18, 0),

    _1001 DECIMAL(18, 0),

    _1002 DECIMAL(18, 0),

    _1005 DECIMAL(18, 0),

    _1006 DECIMAL(18, 0),

    _1026 DECIMAL(18, 0),

    _1030 DECIMAL(18, 0),

    _1031 DECIMAL(18, 0),

	_1035 DECIMAL(18, 0)

);

DECLARE @Tbl_Amount_1 TABLE

(

    emp_id VARCHAR(8),

    ptkp DECIMAL(18, 0),

    _1000 DECIMAL(18, 0),

    _1001 DECIMAL(18, 0),

    _1002 DECIMAL(18, 0),

    _1005 DECIMAL(18, 0),

    _1006 DECIMAL(18, 0),

    _1026 DECIMAL(18, 0),

    _1030 DECIMAL(18, 0),

    _1031 DECIMAL(18, 0),

	_1035 DECIMAL(18, 0),

    insentif_kinerja DECIMAL(18, 0),

    JHT DECIMAL(18, 0),

    JKK DECIMAL(18, 0),

    JKM DECIMAL(18, 0),

    Pensiun DECIMAL(18, 0),

    Kesehatan DECIMAL(18, 0)

);

DECLARE @Tbl_Amount_2 TABLE

(

    emp_id VARCHAR(8),

    ptkp DECIMAL(18, 0),

    _1000 DECIMAL(18, 0),

    _1001 DECIMAL(18, 0),

    _1002 DECIMAL(18, 0),

    _1005 DECIMAL(18, 0),

    _1006 DECIMAL(18, 0),

    _1026 DECIMAL(18, 0),

    _1030 DECIMAL(18, 0),

    _1031 DECIMAL(18, 0),

	_1035 DECIMAL(18, 0),

    insentif_kinerja DECIMAL(18, 0),

    JHT DECIMAL(18, 0),

    JKK DECIMAL(18, 0),

    JKM DECIMAL(18, 0),

    Pensiun DECIMAL(18, 0),

    Kesehatan DECIMAL(18, 0),

    TotalPendapatan DECIMAL(18, 0),

    GajiNettoSetahun DECIMAL(18, 0),

    PphTerutang DECIMAL(18, 0),

    JHT_pot DECIMAL(18, 0),

    Pensiun_pot DECIMAL(18, 0),

    Kesehatan_pot DECIMAL(18, 0)

);

DECLARE @Tbl_Amount_3 TABLE

(

    emp_id VARCHAR(8),

    ptkp DECIMAL(18, 0),

    _1000 DECIMAL(18, 0),

    _1001 DECIMAL(18, 0),

    _1002 DECIMAL(18, 0),

    _1005 DECIMAL(18, 0),

    _1006 DECIMAL(18, 0),

    _1026 DECIMAL(18, 0),

    _1030 DECIMAL(18, 0),

    _1031 DECIMAL(18, 0),

	_1035 DECIMAL(18, 0),

    insentif_kinerja DECIMAL(18, 0),

    JHT DECIMAL(18, 0),

    JKK DECIMAL(18, 0),

    JKM DECIMAL(18, 0),

    Pensiun DECIMAL(18, 0),

    Kesehatan DECIMAL(18, 0),

    TotalPendapatan DECIMAL(18, 0),

    GajiNettoSetahun DECIMAL(18, 0),

    PphTerutang DECIMAL(18, 0),

    pphbulanan DECIMAL(18, 0),

    JHT_pot DECIMAL(18, 0),

    Pensiun_pot DECIMAL(18, 0),

    Kesehatan_pot DECIMAL(18, 0),

    TotalPotongan DECIMAL(18, 0),

    Thp DECIMAL(18, 0)

);

INSERT INTO @Tbl_Amount

SELECT *

FROM

(

    SELECT DISTINCT

           md4.emp_id,

           md4.wage_type,

           CONVERT(DECIMAL(18, 0), dbo.SDE(md4.amount, N'M!N0V@2010')) AS amount,

           ptkp.ptkp

    FROM hr_md_app_md0004 md4

        LEFT JOIN dbo.hr_md_app_md0003 md3

            ON md4.emp_id = md3.emp_id

        LEFT JOIN dbo.hr_cu0302 ptkp

            ON md3.tax_status = ptkp.status

    WHERE md4.emp_id = @emp_id

) AS SourceTable

PIVOT

(

    MIN(amount)

    FOR wage_type IN ([1000], [1001], [1002], [1005], [1006], [1026], [1030], [1031],[1035])

) AS pvt;

INSERT INTO @Tbl_Amount_1

SELECT emp_id,

       ptkp,

       CONVERT(DECIMAL(18,2),ISNULL(_1000,0)) AS gapok,