USE [GrpInsurance]
GO
/****** Object:  StoredProcedure [dbo].[proc_GetKlaimPending]    Script Date: 08/03/2017 11:33:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/**
select Saldo, Tgl_buat, Tgl_terima, Jenis, * from note_terbit_master WHERE 
no_polis='2008.0063.1' --no_proposal='2009.0089' 
and saldo<>0 and tipe=1 and status<>'C'
and tgl_terima<'2010-10-19'
GO
**/

--ALTER Procedure [dbo].[proc_GetKlaimPending] 
DECLARE
        @NoProposal VARCHAR(9) = '2016.0399',
        @TglTrans DATETIME = '2017-08-04',
        @Tipe INT = 0,
        @Jenis INT = 3,
        @Msg VARCHAR(100)
 --OUTPUT  
--AS
  ---JENIS TRANSAKSI   
IF @Tipe = 1 
   PRINT 'RETURN 0-1'
 --RETURN 0  

  ---GET VARIABEL
DECLARE @GTOG VARCHAR(2)
DECLARE @NoPolis VARCHAR(11)
   
DECLARE @Mata_Uang VARCHAR(2)

DECLARE @Tunggakan FLOAT
DECLARE @Tgl_Selesai DATETIME
DECLARE @Aso_Ya BIT
SET @GTOG = ''
SET @Msg = ''

SELECT
  @Aso_Ya = ISNULL(Aso_Ya, 0),
  @GTOG = Perusahaan.GT_OG,
  @NoPolis = No_Polis,
  @Tgl_Selesai = Tgl_Selesai,
  @Mata_Uang = Mata_Uang
FROM
  Polis_Asuransi
  INNER JOIN Perusahaan ON Polis_Asuransi.No_Prs = Perusahaan.No_Prs
WHERE
  Polis_Asuransi.No_Proposal = @NoProposal

  
IF EXISTS ( SELECT
              *
            FROM
              Pending_Limit
            WHERE
              No_Proposal = @NoProposal ) 
   SELECT
    @Tunggakan = ISNULL(Batas_Minimum, 0)
   FROM
    Pending_Limit
   WHERE
    No_Proposal = @NoProposal 

ELSE 
   SET @Tunggakan = 500000
IF @Tunggakan < 500000 
   SET @Tunggakan = 500000
IF @Mata_Uang = '02' 
   SET @Tunggakan = @Tunggakan / 11000
IF @Aso_Ya = 1 
   PRINT 'RETURN 0-2'
 --RETURN 0

  ---APPROVAL PASTI
IF @GTOG = 'GT'
   AND NOT (@NoPolis IN (
  --'1991.0210.0', -- PT Panen Lestari Internusa, -- dibuka tgl 2015-05-27 by Pribadi Pamungkas, permintaan via email tgl 2015-05-27
   '2012.0068.0', -- PT Panen Lestari Internusa (Expatriate),      Polis No. 
   '2012.0067.0', -- PT Panen Lestari Internusa (Pensiun),         Polis No. 
   '2012.0058.0', -- PT Panen GL Indonesia,                             Polis No. 
   '2013.0102.0', -- PT Sari Food Lestari,                                 Polis No. 
   '2013.0098.0', -- PT Panen Cosmetic Indonesia,                   Polis No. 
   '2013.0101.0', -- PT Prima Mode Indonesia,                          Polis No. 
   '2013.0100.0', -- PT Panen Fashion Indonesia,                      Pols No. 
   '1991.0207.0', '2004.0103.0',
--  '2001.0004.0',
   '2000.0011.0', '1995.0048.0', '2002.0079.0', '1995.0044.0', '1991.0219.0', '2005.0184.1', --PT. Visi Utama Indonesia
   '2001.0036.0', --PT. Java Retailindo
  --'1991.0168.0', --PT. Gajah Tunggal Prakarsa
   '1996.0043.0',
 
  
  -- by Pribadi Pamungkas 2014-07-11
  -- permintaan via email tgl 2014-07-10, dgn subject: "PEMBLOKIRAN POLIS PT REKSADANA PERDANA TBK."
   '2004.0219.0', -- PT Reksadana Perdana  Tbk.
  
  -- by Pribadi Pamungkas 2014-07-14
  -- permintaan via email tgl 2014-07-14, dgn subject: "PEMBLOKIRAN POLIS PT JAVA RETAILINDO (EXPATRIATE)"
   '2012.0153.0', -- PT JAVA RETAILINDO (EXPATRIATE).
  
  -- by Pribadi Pamungkas 2015-12-18
  -- permintaan via email tgl 2015-12-18, dgn subject: "BLOKIR POLIS PERUSAHAAN GT GROUP"
  --'1991.0186.0',  -- PT Filamendo Indonesia
  --'1996.0061.0',  --PT Gajah Tunggal Tbk. Div. SBR
  --'1991.0189.0',  --PT Gajah Tunggal Tbk. Div. Tyre Cord
  --'2005.0065.0',  --PT Prima Sentra Megah
  --'1991.0171.0',   --PT Softex Indonesia
  -- sudah diunblok pada 2016-01-26 Pribadi Pamungkas, email tgl 2016-01-25
  
  -- by Pribadi Pamungkas 2016-01-19
  -- permintaan via email tgl 2016-01-19, dgn subject: "BLOKIR POLIS PT MITRA GEMILANG MANDIRI"
   '2004.0099.0',   -- PT Mitra Gemilang Mandiri
  
  -- by Pribadi Pamungkas 2016-06-10
  -- permintaan via email tgl 2016-06-10, dgn subject: "BLOKIR POLIS PT CHARISMA COLORS CHALIFA"
   '2004.0156.1'-- PT Charisma Colors Chalifa
  
  )) 
   PRINT 'RETURN 0-3'
 --RETURN 0

--====================================================================================
  --- APPROVAL TETAP -----------------------------------------------------------------
  --- IF EXISTS(SELECT *  FROM Approval_Fixed WHERE No_Polis=@NoPolis) PRINT 'RETURN 0' --RETURN 0 ------
  --- APPROVAL TEMPORARY -------------------------------------------------------------
--====================================================================================

--- POLIS OG YANG DIPENDING (10-02-12)
--   IF @NoPolis in (
--  '2008.0090.1') -- eastern logistic
---  '2007.0140.1') -- griyamas mukti buka 2-4-12
--   PRINT 'RETURN 1' --RETURN 1 -- (0 dibuka 1 pending)
PRINT @NoPolis
IF @NoPolis IN ('2016.0123.1'-- PT. Langgeng Makmur Industri II TBK. By Pribadi Pamungkas, 2017-08-04. Ticket RQ2017080130. 
                  ) 
   BEGIN
      PRINT 'true'
         SET @Msg = 'Dipending'
         PRINT 'RETURN 1' --RETURN 1 -- (0 dibuka 1 pending)
   END
ELSE
  PRINT 'false'

   
-- DN PERTAMA BELUM TERBIT
IF EXISTS ( SELECT
              *
            FROM
              Approval_Klaim
            WHERE
              (No_Polis = @NoPolis)
              AND ((Klaim_Ya = 1
              AND @Jenis = 0)
              OR (Surat_Ya = 1
              AND @Jenis = 1))
              AND (Status <> 'C')
              AND (Tgl_Awal <= @TglTrans)
              AND (Tgl_Akhir >= @TglTrans) ) 
   BEGIN 
         SET @Msg = 'Ada approval'
         PRINT 'RETURN 0-4' --RETURN 0
   END
ELSE
-- Polis terakhir harus inforce
   IF EXISTS ( SELECT
                *
               FROM
                Polis_Asuransi
               WHERE
                No_Polis = @NoPolis
                AND ISNULL(Active_Ya, 0) = 1
                AND Status <> 'I'
                AND Status <> 'C' ) 
      BEGIN 
            SET @Msg = 'Polis Lapse'
            PRINT 'RETURN 1' --RETURN 1
      END


-- DN PERTAMA BELUM TERBIT
IF NOT EXISTS ( SELECT
                  *
                FROM
                  Note_Terbit_Master
                WHERE
                  No_Proposal = @NoProposal
                  AND Status = 'I'
                  AND Jenis IN (1, 2) ) 
   BEGIN
         SET @Msg = 'DN pertama belum terbit'
         PRINT 'RETURN 1' --RETURN 1  -- edited by Pribadi Pamungkas, 2014-03-14. Tadinya PRINT 'RETURN 0' --RETURN 0
   END

-- DN PERTAMA BELUM LUNAS
DECLARE @TglPertama DATETIME
DECLARE @SubInit VARCHAR(1)
DECLARE @AgingTerbit INT
DECLARE @AgingTerima INT
DECLARE @JmlDN INT
DECLARE @Saldo FLOAT
DECLARE @note_mata_uang VARCHAR(4)

SELECT
  @TglPertama = MIN(Tgl_Buat)
FROM
  Note_Terbit_Master
WHERE
  No_Proposal = @NoProposal
  AND Status = 'I'
  AND Note_Terbit_Master.Jenis IN (1, 2) 

SELECT
  @Saldo = SUM(Saldo),
  @AgingTerbit = DATEDIFF(day, MAX(Note_Terbit_Master.Tgl_Buat), @TglTrans),
  @note_mata_uang = Mata_Uang
FROM
  Note_Terbit_Master
WHERE
  No_Proposal = @NoProposal
  AND Tgl_Buat = @TglPertama
  AND Status <> 'C'
  AND Status <> 'G'
  AND Note_Terbit_Master.Jenis IN (1, 2)
GROUP BY
  Mata_Uang
 -- add by Pribadi Pamungas. 2016-10-04

   ----IF @Saldo>100 --(>0)   -- sebelumnya seperti ini
   --IF @Saldo>0            -- diubah oleh Pribadi Pamungkas. 2016-09-16. Atas persetujuan dari pak Didik. Dikarenakan untuk USD bisa lolos
   --BEGIN
   --     SET @Msg= 'DN pertama belum lunas ' 
   --     PRINT 'RETURN 1' --RETURN 1
   --END
   
   
   -- diubah oleh Pribadi Pamungkas. 2016-10-04. Atas persetujuan dari pak Didik. Dikarenakan untuk USD bisa lolos, tetapi untuk Rupiah masih ada beberapa kasus sekian rupiah
IF @note_mata_uang = '01' -- rupiah
   BEGIN
         IF @Saldo > 100 
            BEGIN
                  SET @Msg = 'DN pertama belum lunas ' 
                  PRINT 'RETURN 1' --RETURN 1
            END
   END
ELSE -- selain rupiah
   BEGIN
         IF @Saldo > 0 
            BEGIN
                  SET @Msg = 'DN pertama belum lunas ' 
                  PRINT 'RETURN 1' --RETURN 1
            END
   END


--==================================================================================
DECLARE @Umur INT
DECLARE @Umur_Min INT
DECLARE @Umur_DN INT
DECLARE @Umur_Max INT
DECLARE @Jenis_Note INT
DECLARE @Tgl_Buat DATETIME
DECLARE @Tgl_Terima DATETIME
DECLARE @Tot_DNPremi FLOAT
DECLARE @Tot_DNKlaim FLOAT 
DECLARE @No_Ref VARCHAR(18)
DECLARE @HariSurat INT
DECLARE @HariKlaim INT

SET @Tot_DNPremi = 0
SET @Tot_DNKlaim = 0

SET @HariSurat = 14
SET @HariKlaim = 30

IF EXISTS ( SELECT
              *
            FROM
              Pending_Periode
            WHERE
              No_Proposal = @NoProposal
              AND Status = 'I' ) 
   SELECT
    @HariKlaim = Pending_Klaim,
    @HariSurat = Pending_Surat
   FROM
    Pending_Periode
   WHERE
    No_Proposal = @NoProposal
    AND Status = 'I'

--PRINT @HariKlaim
--PRINT @HariSurat

DECLARE Pending_Cursor CURSOR
FOR
SELECT
  Note_Terbit_Master.No_Ref,
  Note_Terbit_Master.Jenis,
  Note_Terbit_Master.Tgl_Buat,
  ISNULL(Note_Terbit_Master.Saldo, 0)
FROM
  Note_Terbit_Master
  INNER JOIN Polis_Asuransi ON Note_Terbit_Master.No_Proposal = Polis_Asuransi.No_Proposal
  INNER JOIN Perusahaan ON Polis_Asuransi.No_Prs = Perusahaan.No_Prs
WHERE
  --(Note_Terbit_Master.No_Polis=@NoPolis) AND 
  (Polis_Asuransi.No_Polis = @NoPolis)
  AND (Note_Terbit_Master.Saldo <> 0)
  AND (Perusahaan.GT_OG = 'OG')
  AND (Note_Terbit_Master.Status = 'I')
  AND (Note_Terbit_Master.Jenis IN (1, 2, 3, 4, 20))
  AND (Note_Terbit_Master.Tgl_Buat <= @TglTrans)
  AND (Polis_Asuransi.Tgl_Mulai <= @Tgl_Selesai)
OPEN Pending_Cursor
FETCH NEXT FROM Pending_Cursor INTO @No_Ref, @Jenis_Note, @Tgl_Buat, @Saldo 
SET @Umur_DN = DATEDIFF(day, @Tgl_Buat, @TglTrans)
SET @Umur_Max = @Umur_DN
SET @Umur_Min = 9999
WHILE @@FETCH_STATUS = 0 
      BEGIN
            IF @Saldo < 0 
               SET @saldo = 0
  ---SET @Umur_Terima = DATEDIFF(Day, @Tgl_Terima, @TglTrans)
            IF EXISTS ( SELECT
                          *
                        FROM
                          Note_Shipping
                        WHERE
                          No_Ref = @No_Ref ) 
               BEGIN
                     SELECT
                      @Tgl_Terima = Tgl_Terima
                     FROM
                      Note_Shipping
                     WHERE
                      No_Ref = @No_Ref
                     IF @Jenis = 0 
                        SET @Umur = @HariKlaim
                     ELSE 
                        BEGIN 
                              IF @Jenis_Note IN (1, 2, 3, 4) 
                                 SET @Umur = @HariKlaim
                              ELSE 
                                 IF @Jenis_Note IN (20) 
                                    SET @Umur = @HariSurat
                        END
                     SET @Umur_DN = DATEDIFF(day, @Tgl_Terima, @TglTrans)
          

               END
            ELSE 
               BEGIN
     --     SELECT @Tot_DNPremi, @Tot_DNKlaim, @Umur_Max, @Umur
                     SET @Tgl_Terima = @Tgl_Buat
                     IF @Jenis = 0 
                        SET @Umur = 60
                     ELSE 
                        BEGIN 
                              IF @Jenis_Note IN (1, 2, 3, 4) 
                                 SET @Umur = 60
                              ELSE 
                                 IF @Jenis_Note IN (20) 
                                    SET @Umur = 30
                        END
                     SET @Umur_DN = DATEDIFF(day, @Tgl_Buat, @TglTrans)
               END
            IF @Umur_DN > @Umur 
               BEGIN
                     IF @Jenis_Note IN (1, 2, 3, 4) 
                        SET @Tot_DNPremi = @Tot_DNPremi + ISNULL(@Saldo, 0)
                     ELSE 
                        IF @Jenis_Note IN (20) 
                           SET @Tot_DNKlaim = @Tot_DNKlaim + ISNULL(@Saldo, 0)
               END
            IF @Tot_DNPremi + @Tot_DNKlaim > @Tunggakan 
               BEGIN
                     IF @Umur_Min > @Umur 
                        SET @Umur_Min = @Umur
               END
            IF @Umur_Max < @Umur_DN 
               SET @Umur_Max = @Umur_DN
 
-- buat trace 
-- SELECT @Saldo, @Tot_DNPremi, @Tot_DNKlaim, @Tgl_Terima, @TglTrans,@Umur, DATEDIFF(day,@Tgl_Terima, @TglTrans), @No_Ref, @Tgl_Buat


            FETCH NEXT FROM Pending_Cursor INTO @No_Ref, @Jenis_Note, @Tgl_Buat, @Saldo 
      END
CLOSE Pending_Cursor
DEALLOCATE Pending_Cursor
--select @Tot_DNPremi, @Tot_DNKlaim, @Umur_Max, @saldo
-------------------------

IF @Tot_DNPremi + @Tot_DNKlaim < @Tunggakan 
   BEGIN
         SET @Msg = 'Total DN < ' + +dbo.func_FormatCurr(@Tunggakan, 20, 2, 1)
         PRINT 'RETURN 0' --RETURN 0
   END
-- SELECT @Tot_DNPremi, @Tot_DNKlaim, @Umur_Max, @Umur
IF @Umur_Max >= @Umur_Min 
   BEGIN
         SET @Msg = 'DN Outstanding ' + dbo.func_FormatCurr(ISNULL(@Tot_DNPremi, 0) + ISNULL(@Tot_DNKlaim, 0), 20, 2, 1)
         PRINT 'RETURN 1' --RETURN 1
   END


SELECT
  @Msg