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] 
@NoProposal varchar(9), 
@TglTrans datetime, 
@Tipe int, 
@Jenis int, 
@Msg varchar(100) OUTPUT  
AS
  ---JENIS TRANSAKSI   
  IF @Tipe =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 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
  
  ))
  RETURN 0

--====================================================================================
  --- APPROVAL TETAP -----------------------------------------------------------------
  --- IF EXISTS(SELECT *  FROM Approval_Fixed WHERE No_Polis=@NoPolis) 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
--   RETURN 1 -- (0 dibuka 1 pending)
   IF @NoPolis IN (
                    '2016.0123.1' -- PT. Langgeng Makmur Industri II TBK. By Pribadi Pamungkas, 2017-08-04. Ticket RQ2017080130. 
                  )
      BEGIN
        SET @Msg= 'Dipending'
        RETURN 1 -- (0 dibuka 1 pending)
      END

   
-- 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'
        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'
      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'
        RETURN 1  -- edited by Pribadi Pamungkas, 2014-03-14. Tadinya 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 ' 
   --     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 ' 
              RETURN 1
         END
    END
   ELSE -- selain rupiah
    BEGIN
      IF @Saldo>0
         BEGIN
              SET @Msg= 'DN pertama belum lunas ' 
              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)
    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)
     RETURN 1
END

