USE [GrpInsurance]
GO
/****** Object:  StoredProcedure [dbo].[proc_Rep10770]    Script Date: 11/18/2015 16:25:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- last modified by Pribadi Pamungkas, 2015-11-19. Perubahan: [1] ubah tipe data float ke numeric. [2] Rapikan format
ALTER PROCEDURE [dbo].[proc_Rep10770]
      @TglAwal DATETIME,
      @TglPosisi DATETIME,
      @JenisNote VARCHAR(200),
      @CatNote INT,
      @TipeNote INT,
      @Valas INT,
      @SubInit INT,
      @GTOG INT,
      @IdPayor VARCHAR(2),
      @NoPolis VARCHAR(11),
      @NoAwal INT,
      @Pilih INT,
      @TglPosisiYa BIT,
      @TglAwalYa BIT
AS 
BEGIN TRAN
  --DECLARE @SQLText NVarchar(4000)
DECLARE @Tgl_Posisi VARCHAR(10)
DECLARE @Id_Valas VARCHAR(2)
DECLARE @Sub_Init VARCHAR(1)
DECLARE @GT_OG VARCHAR(2)
DECLARE @Tahun INT

IF @Valas = 0 
   SET @Id_Valas = ''
ELSE 
   IF @Valas = 1 
      SET @Id_Valas = '01'
   ELSE 
      IF @Valas = 2 
         SET @Id_Valas = '02'

IF @SubInit = 0 
   SET @Sub_Init = ''
ELSE 
   IF @SubInit = 1 
      SET @Sub_Init = 'I'
   ELSE 
      IF @SubInit = 2 
         SET @Sub_Init = 'S'

IF @GTOG = 0 
   SET @GT_OG = ''
  --ELSE IF @SubInit=1 SET @GT_OG='GT'
  --ELSE IF @SubInit=2 SET @GT_OG='OG'
ELSE 
   IF @GTOG = 1 
      SET @GT_OG = 'GT'
   ELSE 
      IF @GTOG = 2 
         SET @GT_OG = 'OG'

IF @TglPosisiYa = 1 
   SET @Tgl_Posisi = dbo.func_DateToStr(@TglPosisi)
ELSE 
   SET @Tgl_Posisi = dbo.func_DateToStr(GETDATE())
                                                                                                              --001-002-003-004-005-006-009-011-012-019-031-032-033-034-099
IF ISNULL(@CatNote, 0) = 1 
   SET @JenisNote = ISNULL(@JenisNote, '') + '-001-002-003-004-005-006-009-011-012-018-019-031-032-033-034-037-038-050-060-061-062-068-069-099'
ELSE 
   IF ISNULL(@CatNote, 0) = 2 
      SET @JenisNote = ISNULL(@JenisNote, '') + '-020-024'
   ELSE 
      IF ISNULL(@CatNote, 0) = 3 
         SET @JenisNote = ISNULL(@JenisNote, '') + '-020-024-001-002-003-004-005-006-009-011-012-018-019-031-032-033-034-037-038-050-060-061-062-068-069-099'

SELECT
  tk.Id_Broker,
  tk.Tgl_Posisi,
  tk.File_Rtf,
  tk.No_Awal,
  tk.No_Surat,
  tk.No_Ref,
  tk.Tgl_Buat,
  tk.No_Note,
  tk.No_Polis,
  tk.Jenis,
  --tk.Debet,
  CAST(tk.Debet AS NUMERIC(12,2)) AS Debet,
  --tk.Kredit,
  CAST(tk.Kredit AS NUMERIC(12,2)) AS Kredit,
  --tk.Saldo,
  CAST(tk.Debet AS NUMERIC(12,2)) - CAST(tk.Kredit AS NUMERIC(12,2)) AS Saldo,
  tk.Nama_Perusahaan,
  tk.ALAMAT,
  tk.KOTA,
  tk.kode_pos,
  tk.telepon,
  tk.fax,
  tk.Contact_Person,
  tk.Cabang
FROM
  (
    SELECT
      dbo.Polis_Asuransi.Broker_Id AS Id_Broker,
      --SELECT (SELECT Broker_Id FROM Polis_Asuransi WHERE (No_Polis=@NoPolis OR isnull(@NoPolis,'')='') AND (Active_Ya=1)) as Id_Broker,
      @Tgl_Posisi AS Tgl_Posisi,
      File_Rtf = CASE dbo.Polis_Asuransi.Id_Payor
                   WHEN '01' THEN '\\equity-db\dbtext_grp\dbText\StatementAcc.Rtf' -- '\\equity-elife\dbText\StatementAcc.Rtf'
                   WHEN '02' THEN '\\equity-db\dbtext_grp\StatementAcc_Medicom.Rtf' -- '\\equity-elife\dbText\StatementAcc_Medicom.Rtf'
                   ELSE '\\equity-db\dbtext_grp\StatementAcc.Rtf' -- '\\equity-elife\dbText\StatementAcc.Rtf'
                    --WHEN '01' THEN 'C:\My Project\Office\GrpInsuranceMDI\dbText\StatementAcc.Rtf'
                    --WHEN '02' THEN 'C:\My Project\Office\GrpInsuranceMDI\dbText\StatementAcc_Medicom.Rtf'
                    --ELSE 'C:\My Project\Office\GrpInsuranceMDI\dbText\StatementAcc.Rtf'
                 END,
      -- @NoAwal as No_Awal, '/'+Payor_Header.Surat+'/FIN/'+ dbo.func_GetRomawi(DATEPART(month,@TglPosisi))+'/'+CAST(DATEPART(Year, @TglPosisi) as varchar) As No_Surat,
      @NoAwal AS No_Awal,
      '/CSC/' + dbo.func_GetRomawi(DATEPART(month, @TglPosisi)) + '/' + CAST(DATEPART(Year, @TglPosisi) AS VARCHAR) AS No_Surat,
      Note_Terbit_Master.No_Ref,
      Note_Terbit_Master.Tgl_Buat,
      Note_Terbit_Master.No_Note,
      Note_Terbit_Master.No_Polis,
      Note_Jenis.[Description] AS Jenis,
      Debet = CASE Note_Terbit_Master.Tipe
                WHEN 1 THEN ABS(Note_Terbit_Master.Total)
                ELSE CASE 
                          --WHEN isnull(Note_Lunas.Jml_Lunas,0)<>0 THEN Abs(Note_Terbit_Master.Tipe*(isnull(Note_Lunas.Jml_Lunas,0) - isnull(Note_Terbit_Master.Jml_Adjust,0))) 
                          WHEN (ISNULL(Note_Lunas.Jml_Lunas, 0) <> 0)
                               OR (Note_Terbit_Master.Total + ISNULL(Note_Terbit_Master.Jml_Adjust, 0) = 0) THEN ABS(Note_Terbit_Master.Tipe * (ISNULL(Note_Lunas.Jml_Lunas, 0) - ISNULL(Note_Terbit_Master.Jml_Adjust, 0)))
                          ELSE ABS(Note_Terbit_Master.Tipe * ISNULL(Note_Lunas.Jml_Lunas, 0))
                     END
              END,
      Kredit = CASE Note_Terbit_Master.Tipe
                 WHEN -1 THEN ABS(Note_Terbit_Master.Total)
                 ELSE CASE 
                            --WHEN isnull(Note_Lunas.Jml_Lunas,0)<>0 THEN Abs(Note_Terbit_Master.Tipe*(isnull(Note_Lunas.Jml_Lunas,0) - isnull(Note_Terbit_Master.Jml_Adjust,0))) 
                           WHEN (ISNULL(Note_Lunas.Jml_Lunas, 0) <> 0)
                                OR (Note_Terbit_Master.Total + ISNULL(Note_Terbit_Master.Jml_Adjust, 0) = 0) THEN ABS(Note_Terbit_Master.Tipe * (ISNULL(Note_Lunas.Jml_Lunas, 0) - ISNULL(Note_Terbit_Master.Jml_Adjust, 0)))
                           ELSE ABS(Note_Terbit_Master.Tipe * ISNULL(Note_Lunas.Jml_Lunas, 0))
                      END
               END,
      CASE Note_Terbit_Master.Tipe
        WHEN 1 THEN ABS(Note_Terbit_Master.Total)
        ELSE CASE 
                  --WHEN isnull(Note_Lunas.Jml_Lunas,0)<>0 THEN Abs(Note_Terbit_Master.Tipe*(isnull(Note_Lunas.Jml_Lunas,0) - isnull(Note_Terbit_Master.Jml_Adjust,0))) 
                  WHEN (ISNULL(Note_Lunas.Jml_Lunas, 0) <> 0)
                       OR (Note_Terbit_Master.Total + ISNULL(Note_Terbit_Master.Jml_Adjust, 0) = 0) THEN ABS(Note_Terbit_Master.Tipe * (ISNULL(Note_Lunas.Jml_Lunas, 0) - ISNULL(Note_Terbit_Master.Jml_Adjust, 0)))
                  ELSE ABS(Note_Terbit_Master.Tipe * ISNULL(Note_Lunas.Jml_Lunas, 0))
             END
      END - CASE Note_Terbit_Master.Tipe
              WHEN -1 THEN ABS(Note_Terbit_Master.Total)
              ELSE CASE WHEN (ISNULL(Note_Lunas.Jml_Lunas, 0) <> 0)
                             OR (Note_Terbit_Master.Total + ISNULL(Note_Terbit_Master.Jml_Adjust, 0) = 0) THEN ABS(Note_Terbit_Master.Tipe * (ISNULL(Note_Lunas.Jml_Lunas, 0) - ISNULL(Note_Terbit_Master.Jml_Adjust, 0)))  
                        --WHEN isnull(Note_Lunas.Jml_Lunas,0)<>0 THEN Abs(Note_Terbit_Master.Tipe*(isnull(Note_Lunas.Jml_Lunas,0) - isnull(Note_Terbit_Master.Jml_Adjust,0))) 
                        ELSE ABS(Note_Terbit_Master.Tipe * ISNULL(Note_Lunas.Jml_Lunas, 0))
                   END
            END AS Saldo,
      dbo.Perusahaan.Nama_Perusahaan + ' [' + dbo.Polis_Asuransi.No_Polis + ']' AS Nama_Perusahaan,
      dbo.Perusahaan.Alamat3 AS ALAMAT,
      dbo.Perusahaan.Kota3 AS KOTA,
      dbo.Perusahaan.Kode_Pos3 AS kode_pos,
      dbo.Perusahaan.Telepon3 AS telepon,
      dbo.Perusahaan.Fax3 AS fax, 
      --dbo.Polis_Asuransi.Contact_Person, 
      dbo.func_GetContactPerson(dbo.Polis_Asuransi.No_Polis) AS Contact_Person,
      Cabang.Nama AS Cabang,
      Note_Terbit_Master.Tipe,
      dbo.Polis_Asuransi.Mata_Uang
    FROM
      Note_Terbit_Master
      INNER JOIN Note_Jenis ON Note_Terbit_Master.Jenis = Note_Jenis.Kode
      LEFT OUTER JOIN (
                       SELECT
                        No_Ref,
                        -1 * ABS(SUM(ISNULL(Jumlah, 0))) AS Jml_Lunas
                       FROM
                        Note_Lunas_Master
                       GROUP BY
                        No_Ref
                      ) AS Note_Lunas ON Note_Terbit_Master.No_Ref = Note_Lunas.No_Ref
      INNER JOIN dbo.Polis_Asuransi ON dbo.Note_Terbit_Master.No_Proposal = dbo.Polis_Asuransi.No_Proposal
      INNER JOIN Payor_Header ON dbo.Polis_Asuransi.Id_Payor = Payor_Header.Id_Payor
      INNER JOIN dbo.Perusahaan ON dbo.Polis_Asuransi.No_Prs = dbo.Perusahaan.No_Prs
      LEFT OUTER JOIN Cabang ON Note_Terbit_Master.Cabang = Cabang.Kode
                                AND Polis_Asuransi.No_Prs = Cabang.No_Prs
    WHERE
      (Note_Terbit_Master.Status = 'I')
      --AND (ABS(CASE Note_Terbit_Master.Tipe
      --           WHEN 1 THEN ABS(Note_Terbit_Master.Total)
      --           ELSE CASE 
      --                    --WHEN isnull(Note_Lunas.Jml_Lunas,0)<>0 THEN Abs(Note_Terbit_Master.Tipe*(isnull(Note_Lunas.Jml_Lunas,0) - isnull(Note_Terbit_Master.Jml_Adjust,0))) 
      --                     WHEN (ISNULL(Note_Lunas.Jml_Lunas, 0) <> 0)
      --                          OR (Note_Terbit_Master.Total + ISNULL(Note_Terbit_Master.Jml_Adjust, 0) = 0) THEN ABS(Note_Terbit_Master.Tipe * (ISNULL(Note_Lunas.Jml_Lunas, 0) - ISNULL(Note_Terbit_Master.Jml_Adjust, 0)))
      --                     ELSE ABS(Note_Terbit_Master.Tipe * ISNULL(Note_Lunas.Jml_Lunas, 0)) - ISNULL(Note_Terbit_Master.Jml_Adjust, 0)
      --                END
      --         END - CASE Note_Terbit_Master.Tipe
      --                 WHEN -1 THEN ABS(Note_Terbit_Master.Total)
      --                 ELSE CASE WHEN Note_Terbit_Master.Write_Off = 1 THEN ABS(Note_Terbit_Master.Total)
      --                           ELSE ABS(Note_Terbit_Master.Tipe * (ISNULL(Note_Lunas.Jml_Lunas, 0) - ISNULL(Note_Terbit_Master.Jml_Adjust, 0)))
      --                      END
      --               END) > dbo.func_AdjustAuto(dbo.Polis_Asuransi.Mata_Uang))
      AND (dbo.Note_Terbit_Master.Tgl_Buat <= @TglPosisi)
      AND (dbo.Note_Terbit_Master.Tgl_Buat >= @TglAwal
      OR @TglAwalYa = 0)
      AND ((dbo.Note_Terbit_Master.Tipe = 1
      AND @TipeNote = 1)
      OR (dbo.Note_Terbit_Master.Tipe = -1
      AND @TipeNote = 2)
      OR (@TipeNote = 0))
      AND (dbo.Polis_Asuransi.Mata_Uang = @Id_Valas
      OR ISNULL(@Id_Valas, '') = '')
      AND (dbo.Perusahaan.GT_OG = @GT_OG
      OR ISNULL(@GT_OG, '') = '')
      AND (dbo.Polis_Asuransi.Sub_Init = @Sub_Init
      OR ISNULL(@Sub_Init, '') = '')
      AND (dbo.Polis_Asuransi.Id_Payor = @IdPayor
      OR ISNULL(@IdPayor, '') = '')
      AND (dbo.Polis_Asuransi.No_Polis = @NoPolis
      OR ISNULL(@NoPolis, '') = '')
      AND (CHARINDEX(RIGHT('000' + CAST(Note_Terbit_Master.Jenis AS VARCHAR(3)), 3), @JenisNote) <> 0
      OR ISNULL(@JenisNote, '') = '')
    --ORDER BY
    --  --Note_Terbit_Master.No_Polis, 
    --  dbo.Perusahaan.Nama_Perusahaan + ' [' + dbo.Polis_Asuransi.No_Polis + ']' ASC,
    --  Note_Terbit_Master.Tgl_Buat,
    --  Note_Terbit_Master.Tipe ASC,
    --  Note_Terbit_Master.No_Ref
  ) AS tk
WHERE
  ABS(CAST(tk.Debet AS NUMERIC(12,2)) - CAST(tk.Kredit AS NUMERIC(12,2))) > dbo.func_AdjustAuto(tk.Mata_Uang)
ORDER BY
  tk.Nama_Perusahaan + ' [' + tk.No_Polis + ']' ASC,
  tk.Tgl_Buat,
  tk.Tipe ASC,
  tk.No_Ref
  
COMMIT TRAN

