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,
       CONVERT(DECIMAL(18,2),ISNULL(_1001,0)) AS tunj_tetap,
       CONVERT(DECIMAL(18,2),ISNULL(_1002,0)) AS tunj_kehadiran,
       CONVERT(DECIMAL(18,2),ISNULL(_1005,0)) AS tunj_operasional,
       CONVERT(DECIMAL(18,2),ISNULL(_1006,0)) AS tunj_transport,
       CONVERT(DECIMAL(18,2),ISNULL(_1026,0)) AS tunj_kemahalan,
       CONVERT(DECIMAL(18,2),_1000) +  CONVERT(DECIMAL(18,2),ISNULL(_1001,0)) AS honorarium,
        CONVERT(DECIMAL(18,2),ISNULL(_1031,0)) AS tunjangan_lain2,
	    CONVERT(DECIMAL(18,2),ISNULL(_1035,0)) AS _0135,
       ( CONVERT(DECIMAL(18,2),_1000) + CONVERT(DECIMAL(18,2),ISNULL(_1001,0))) * 15 / 100 AS insentif_kinerja,
         ( CONVERT(DECIMAL(18,2),_1000) + CONVERT(DECIMAL(18,2),ISNULL(_1001,0))) * 3.7 / 100 AS JHT,
      ( CONVERT(DECIMAL(18,2),_1000) + CONVERT(DECIMAL(18,2),ISNULL(_1001,0))) * 0.24 / 100 AS JKK,
        ( CONVERT(DECIMAL(18,2),_1000) + CONVERT(DECIMAL(18,2),ISNULL(_1001,0))) * 0.3 / 100 AS JKM,
         ( CONVERT(DECIMAL(18,2),_1000) + CONVERT(DECIMAL(18,2),ISNULL(_1001,0))) * 2 / 100 AS Pensiun,
         ( CONVERT(DECIMAL(18,2),_1000) + CONVERT(DECIMAL(18,2),ISNULL(_1001,0))) * 4 / 100 AS Kesehatan
FROM @Tbl_Amount;


INSERT INTO @Tbl_Amount_2
SELECT a.emp_id,
       a.ptkp,
       a._1000 AS gapok,
       a._1001 AS tunj_tetap,
       a._1002 AS tunj_kehadiran,
       a._1005 AS tunj_operasional,
       a._1006 AS tunj_transport,
       a._1026 AS tunj_kemahalan,
       CONVERT(DECIMAL(18,2),_1000) +  CONVERT(DECIMAL(18,2),_1001)  AS honorarium,
       a._1031 AS tunjangan_lain2,
	   a._1035,
       a.insentif_kinerja,
       a.JHT,
       a.JKK,
       a.JKM,
       a.Pensiun,
       a.Kesehatan,
       a._1000 + a._1001 + 630000 + a._1005 + a._1006 + a._1026  + a._1031 + a.insentif_kinerja + a.JHT
       + a.JKK + a.JKM + a.Pensiun + a.Kesehatan AS TotalPendapatan,

       ((a._1000 + a._1001 + a._1002 + a._1005 + a._1006 + a._1026  + a._1031 + a.insentif_kinerja + a.JHT
         + a.JKK + a.JKM + a.Pensiun + a.Kesehatan
        ) - (124000)
       ) * 12 AS GajiNettoSetahun,
       (((a._1000 + a._1001 + a._1002 + a._1005 + a._1006 + a._1026  + a._1031 + a.insentif_kinerja + a.JHT
          + a.JKK + a.JKM + a.Pensiun + a.Kesehatan
         ) - (124000)
        ) * 12
       ) - a.ptkp AS pph_terutang,
       ( a._1000 + a._1001 ) * 2 / 100 AS JHT_pot,
         ( a._1000 + a._1001 ) * 1 / 100 AS Penisun_pot,
         ( a._1000 + a._1001 ) * 1 / 100 AS Kesehatan_pot
FROM @Tbl_Amount_1 a;

INSERT INTO @Tbl_Amount_3
SELECT emp_id,
       ptkp,
       _1000 AS gapok,
       _1001 AS tunj_tetap,
       _1002 AS tunj_kehadiran,
       _1005 AS tunj_operasional,
       _1006 AS tunj_transport,
       _1026 AS tunj_kemahalan,
       CONVERT(DECIMAL(18,2),_1000) +  CONVERT(DECIMAL(18,2),_1001) AS honorarium,
       _1031 AS tunjangan_lain2,
	   _1035,
       insentif_kinerja,
       JHT,
       JKK,
       JKM,
       Pensiun,
       Kesehatan,
       TotalPendapatan,
       GajiNettoSetahun,
       PphTerutang,
       [dbo].[BracketAmount](PphTerutang) / 12 AS pphbulanan,
       JHT_pot,
       Pensiun_pot,
       Kesehatan_pot,
       (([dbo].[BracketAmount](PphTerutang) / 12) + JHT + JKK + JKM + Pensiun + Kesehatan + JHT_pot + Pensiun_pot
        + Kesehatan_pot
       ) AS TotalPotongan,

       TotalPendapatan
       - (([dbo].[BracketAmount](PphTerutang) / 12) + JHT + JKK + JKM + Pensiun + Kesehatan + JHT_pot + Pensiun_pot
          + Kesehatan_pot
         ) AS Thp
FROM @Tbl_Amount_2;

DECLARE @Tbl_Amount_4 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_4
SELECT emp_id,
       ptkp,
       _1000 AS gapok,
       _1001 AS tunj_tetap,
       _1002 AS tunj_kehadiran,
       _1005 AS tunj_operasional,
       _1006 AS tunj_transport,
       _1026 AS tunj_kemahalan,
       CONVERT(DECIMAL(18,2),_1000) +  CONVERT(DECIMAL(18,2),_1001) AS honorarium,
       _1031 AS tunjangan_lain2,
	   _1035 ,
       insentif_kinerja,
       JHT,
       JKK,
       JKM,
       Pensiun,
       Kesehatan,
       TotalPendapatan +pphbulanan  AS TotalPendapatan,
    (((  TotalPendapatan +pphbulanan)-pphbulanan)-124000)*12  AS  GajiNettoSetahun,
      ( (((  TotalPendapatan +pphbulanan)-pphbulanan)-124000)*12 )-ptkp AS PphTerutang,
       pphbulanan,
       JHT_pot,
       Pensiun_pot,
       Kesehatan_pot,
       TotalPotongan,
       ( TotalPendapatan +pphbulanan)-TotalPotongan AS Thp
FROM @Tbl_Amount_3;

SELECT 

emp_id,
    ptkp ,
          _1000 AS gapok,
       _1001 AS tunj_tetap,
       _1002 AS tunj_kehadiran,
       _1005 AS tunj_operasional,
       _1006 AS tunj_transport,
       _1026 AS tunj_kemahalan,
       CONVERT(DECIMAL(18,2),ISNULL(_1000,0)) +  CONVERT(DECIMAL(18,2),ISNULL(_1001,0)) AS honorarium,
       _1031 AS tunjangan_lain2,
	   @temp_off,
	   CASE WHEN @temp_off = '002' THEN ISNULL( _1035,0) 
	ELSE 1 END AS tunjangan_Komunikasi,
    insentif_kinerja ,
    JHT ,
    JKK ,
    JKM ,
    Pensiun ,
    Kesehatan,
    TotalPendapatan ,
    GajiNettoSetahun ,
    CASE WHEN PphTerutang > 0 THEN PphTerutang ELSE 0 END AS PphTerutang ,
	CASE WHEN ([dbo].[BracketAmount](PphTerutang) / 12 ) > 0 THEN ([dbo].[BracketAmount](PphTerutang) / 12 ) ELSE 0 END AS pphbulanan,
	CASE WHEN  @temp_off > 1 THEN 'Tunjangan Komunikasi (reimbursment)'
	ELSE '' END AS point_11,
    --pphbulanan,
    JHT_pot ,
    Pensiun_pot ,
    Kesehatan_pot,
    TotalPotongan ,
    Thp 
	
	from
	@Tbl_Amount_4