

-- LANGKAH 1

DECLARE
  @pi_no_spaj  varchar(12),
  @po_message  varchar(100)
SET @pi_no_spaj = 'TEST01'

  DECLARE 
    @p_id_dokumen_register varchar(11),
    @p_id_dokumen_terima   varchar(12),
    @p_create_date         datetime,
    @p_create_by           varchar(4),
    @p_return_status       int
  
  SET @po_message = ''
  
  -- Find SPAJ on SPAJ REGISTER
  IF NOT (EXISTS(
    SELECT 1 
    FROM spaj_register 
    WHERE no_spaj = @pi_no_spaj)
  )
  BEGIN
    -- Ambil data spaj  
    SELECT 
      @p_create_date = create_date, 
      @p_create_by = create_by 
    FROM spaj 
    WHERE no_spaj = @pi_no_spaj  

    --------------------------------------------------------------------------------------------------------
    
    -- Proses Dokumen Register

    EXEC @p_return_status = gproc_getno @p_create_date, 'id_dokumen_register', @p_id_dokumen_register OUTPUT
    
    IF @p_return_status <> 0
    BEGIN
      SET @po_message = 'Error on generating id_dokumen_register'
      --RETURN (-1)
    END
    
    INSERT INTO dokumen_register (
      id_dokumen_register, tgl_terima, id_jenis_sumber_dokumen, sumber_dokumen, 
      no_referensi_sumber, nama_pengirim, id_jenis_tujuan_dokumen, keterangan, 
      status, create_date, create_by
    )
    VALUES (
      @p_id_dokumen_register, @p_create_date, '01', 'TELEMARKETING PUSAT', 
      '-', 'TELEMARKETING RADEN SALEH', '2210', 'DATA EXCEL TELEMARKETING', 
      'I', @p_create_date, @p_create_by
    )

    IF (@@ERROR <> 0) OR (@@ROWCOUNT = 0)
    BEGIN
      SET @po_message = 'Error on inserting into table dokumen_register'
      --RETURN (-1)
    END

    --------------------------------------------------------------------------------------------------------

    -- Proses Terima Dokumen
    
    EXEC @p_return_status = gproc_getno @p_create_date, 'id_dokumen_terima', @p_id_dokumen_terima OUTPUT

    IF @p_return_status <> 0
    BEGIN
      SET @po_message = 'Error on generating id_dokumen_terima'
      --RETURN (-1)
    END

    INSERT dokumen_terima (
      id_dokumen_terima, id_dokumen_register, id_jenis_dokumen, tgl_terima, no_referensi, 
      keterangan, status, create_date, create_by
    )
    VALUES (
      @p_id_dokumen_terima, @p_id_dokumen_register, '00001', @p_create_date, @pi_no_spaj, 
      'KONVERSI TELE', 'I', @p_create_date, @p_create_by
    )

    IF (@@ERROR <> 0) OR (@@ROWCOUNT = 0)
    BEGIN
      SET @po_message = 'Error on inserting into table dokumen_terima'
      --RETURN (-1)
    END

    --------------------------------------------------------------------------------------------------------

    -- Insert Into SPAJ Register
    INSERT INTO spaj_register (
      no_spaj, no_spajk, id_pemegang, nama_pemegang, gelar_sebelum_nama_pemegang, gelar_setelah_nama_pemegang, 
      id_jenis_kelamin_pemegang, tgl_lahir_pemegang, id_tertanggung, nama_tertanggung, gelar_sebelum_nama_tertanggung, 
      gelar_setelah_nama_tertanggung, id_jenis_kelamin_tertanggung, tgl_lahir_tertanggung, id_hubungan, id_plan_dasar, 
      tgl_mulai, tgl_produksi, id_jenis_investasi, id_periode_bayar, id_cara_bayar_nasabah, keterangan_cara_bayar_nasabah, 
      id_valuta, masa_kontrak_dasar, masa_pembayaran_premi_dasar, uang_pertanggungan_dasar, premi_dasar, premi_rider, 
      premi_berkala, top_up_berkala, total_premi, id_agen, id_cabang, id_cabang_korespondensi, multi_polis_ya, 
      multi_payor_benefit_rider_ya, id_status_spaj_register, catatan, administrasi_ya, materai_kuitansi_ya, 
      biaya_administrasi, biaya_materai, status, create_date, create_by, change_date, change_by, approve_date, approve_by, 
      approve_note, id_perusahaan, cancel_date, cancel_note, cancel_by, medical_ttg_ya, medical_ppm_ya, id_program_marketing, 
      id_jenis_em, id_jenis_em_ppm, id_reas_fakultatif, prosen_ekstra_premi_ttg, prosen_ekstra_premi_ppm, id_dokumen_register, 
      fakultatif_ya, tgl_validasi, id_unit_pelayanan, id_unit_pelayanan_produksi, id_unit_pelayanan_korespondensi, 
      postpone_date, postpone_by, postpone_note
    )
    SELECT 
      s.no_spaj, s.no_spajk, sppm.id_pemegang, sppm.nama_lengkap, sppm.gelar_sebelum_nama, sppm.gelar_setelah_nama,
      sppm.id_jenis_kelamin, sppm.tgl_lahir,  sttg.id_tertanggung, sttg.nama_lengkap, sttg.gelar_sebelum_nama, 
      sttg.gelar_setelah_nama, sttg.id_jenis_kelamin, sttg.tgl_lahir, sttg.id_hubungan, s.id_plan_dasar,  
      s.tgl_mulai, s.tgl_produksi, s.id_jenis_investasi, s.id_periode_bayar, s.id_cara_bayar_nasabah, s.keterangan_cara_bayar_nasabah, 
      s.id_valuta, s.masa_kontrak_dasar,  s.masa_pembayaran_premi_dasar, s.uang_pertanggungan_dasar, s.premi_dasar, s.premi_rider, 
      s.premi_berkala, s.top_up_berkala, s.total_premi, s.id_agen, s.id_cabang, s.id_cabang_korespondensi, s.multi_polis_ya, 
      s.multi_payor_benefit_rider_ya, '03', s.catatan, s.administrasi_ya, s.materai_kuitansi_ya, 
      s.biaya_administrasi, s.biaya_materai, 'I', s.create_date, s.create_by, s.change_date, s.change_by, s.create_date, s.create_by, 
      NULL, s.id_perusahaan, s.cancel_date, s.cancel_note, s.cancel_by,  s.medical_ttg_ya, s.medical_ppm_ya, s.id_program_marketing, 
      s.id_jenis_em, s.id_jenis_em_ppm, s.id_reas_fakultatif, s.prosen_ekstra_premi_ttg, s.prosen_ekstra_premi_ppm, @p_id_dokumen_register, 
      0, NULL, NULL, id_unit_pelayanan_produksi, id_unit_pelayanan_korespondensi, NULL, NULL, NULL
    FROM
      spaj s
      LEFT JOIN 
      spaj_plan sp 
      ON (s.no_spaj = sp.no_spaj)
      LEFT JOIN 
      spaj_pemegang sppm 
      on (s.no_spaj = sppm.no_spaj)
      LEFT JOIN 
      spaj_tertanggung sttg 
      ON (s.no_spaj = sttg.no_spaj)
    WHERE 
      s.no_spaj = @pi_no_spaj

    IF (@@ERROR <> 0) OR (@@ROWCOUNT = 0)
    BEGIN
      SET @po_message = 'Error on inserting into table spaj_register'
      --RETURN (-1)
    END

    --------------------------------------------------------------------------------------------------------

    -- Insert Into SPAJ Register Plan
    insert into spaj_register_plan (
      no_spaj, id_plan_dasar, id_plan, aktif_ya, no_urut, deskripsi, kelas, risiko, 
      masa_pembayaran_premi, masa_kontrak, tgl_mulai, uang_pertanggungan, premi, 
      ekstra_premi, prosen_ekstra_premi, total_premi, premi_lama, status, create_date, 
      create_by, change_date, change_by, tgl_selesai, tgl_mulai_berlaku, tgl_selesai_berlaku, 
      diskon_premi
    )
    SELECT
      no_spaj, id_plan_dasar, id_plan, 1, no_urut, deskripsi, kelas, risiko, 
      masa_pembayaran_premi, masa_kontrak, tgl_mulai, uang_pertanggungan, premi, 
      ekstra_premi, prosen_ekstra_premi, total_premi, premi_lama, 'I', create_date, 
      create_by, change_date, change_by, tgl_selesai, tgl_mulai_berlaku, tgl_selesai_berlaku, 
      diskon_premi
    FROM spaj_plan
    WHERE no_spaj = @pi_no_spaj

    IF (@@ERROR <> 0) OR (@@ROWCOUNT = 0)
    BEGIN
      SET @po_message = 'Error on inserting into table spaj_register_plan'
      --RETURN (-1)
    END

    --------------------------------------------------------------------------------------------------------
      
  END -- End: IF NOT (EXISTS(
  
  SET @po_message = 'Copy berhasil'
  --RETURN 0

SELECT @po_message


-- end: proc_tele_copy_spaj_to_register


----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

-- LANGKAH 2

DECLARE
  @pi_no_spaj  varchar(12),
  @po_message  varchar(100),
  @pi_Status varchar(10),
  @pi_Approve_Date datetime,
  @pi_idUser varchar(4),
  @pi_note varchar(500),
  @pi_id_status_spaj_register varchar(2)
  
  
  
SET @pi_no_spaj = 'TEST01'
--SET @pi_Status = 'E'  -- ini untuk pertama kali
SET @pi_Status = 'I'
SET @pi_Approve_Date = GETDATE()
SET @pi_idUser = 'SUP'
SET @pi_note = ''

  DECLARE 
    @p_no_polis varchar(11), 
    @p_status_lama varchar(1), 
    @p_return_status int,
    @p_id_polis varchar(11),
    @p_no_polis_angka varchar(11),
    @p_no_polis_VA varchar(11),
    @p_no_virtual_account varchar(16),
    @p_id_jenis_investasi varchar(2),
    @p_jenis_investasi varchar(2),
    @p_id_valuta varchar(3),
    @p_kode_virtual_account varchar(4),
    @p_id_kelompok_produk varchar(2),
    @p_id_plan_dasar varchar(4),
    @p_id_jenis_alamat_korespondensi_tertanggung varchar(2),
    @p_id_jenis_alamat_korespondensi_pemegang varchar(2),
    @p_tgl_produksi datetime,
    @p_flexi bit,
    @p_id_agen varchar(9),
    @p_id_hubungan varchar(2),
    @p_id_polis_cetak varchar(12),
    @p_jumlah_bayar_PP float,
    @p_id_valuta_setoran varchar(3),
    @p_max_tgl_bayar datetime,
    @p_id_kelompok_produk_setoran varchar(2),
    @p_kelompok_produk_desc varchar(30),
    @p_kelompok_produk_setoran_desc varchar(30),
    @p_simbol_valuta_setoran varchar(5),
    @p_simbol_valuta varchar(5),
    @p_biaya_administrasi float,
    @p_biaya_materai float,
    @p_total_premi float,
    @p_id_cabang varchar(4),
    @p_cek_PP_ya bit,
    @p_diskon_premi float,
    @po_id_premi varchar(12),
    @p_id_unit_pelayanan varchar(4),
    @p_id_cara_bayar varchar(4),
    @p_tgl_mulai datetime,
    @p_id_link_nasabah varchar(12),
    @p_id_premi varchar(12),
    @p_id_transaksi varchar(12),
    @p_tgl_bayar datetime,
    @p_tgl_valuta datetime,
    @p_tgl_sekarang datetime,
    @p_tgl_otorisasi datetime,
    @p_top_up_tunggal float,
    @p_id_transaksi_lunas varchar(12),
    @p_field_reference varchar(50), 
    @p_object_reference varchar(50),
    @p_id_jenis_gl varchar(3), 
    @p_key_field_reference varchar(50), 
    @p_urutan int, 
    @p_tipe int,
    @p_tanggal datetime,
    @p_SQLStr Nvarchar(4000),
    @p_tgl_skrg datetime,
    @p_total_premi_all float,
    @p_patokan float,
    @p_id_transaksi_min varchar(12),
    @p_min_tgl_valuta datetime,
    @p_total_premi_header float 




  SELECT @p_status_lama = status 
  FROM spaj
  WHERE no_spaj = @pi_no_spaj
  -----------------------------------------------------------------------------------------------------------

BEGIN TRAN
  
  IF NOT EXISTS(
    SELECT 1 FROM SPAJ
    WHERE no_SPAJ = @pi_no_SPAJ
  )
  BEGIN
    SET @po_message = 'Data tidak ada.'
    --RETURN 1
  END
  
  -----------------------------------------------------------------------------------------------------------
  
  -- Jika verifikasi atau otorisasi
  IF @pi_status IN ('I' ,'E')
  BEGIN
    SELECT  
      @p_id_valuta          = UPPER(id_valuta), 
      @p_Status_Lama        = status,  
      @p_id_plan_dasar      = id_plan_dasar, 
      @p_tgl_produksi       = tgl_produksi, 
      @p_id_agen            = id_agen, 
      @p_biaya_administrasi = biaya_administrasi, 
      @p_biaya_materai      = biaya_materai,
      @p_id_cara_bayar      = id_cara_bayar_nasabah,
      @p_no_polis           = no_polis
    FROM SPAJ 
    WHERE no_SPAJ = @pi_no_SPAJ

    ---------------------------------------------------------------------------------------------------------
    
    IF (EXISTS(
      SELECT 1
      FROM dbo.func_get_history_agen_hirarki(@p_tgl_produksi)
      WHERE id_agen = @p_id_agen
    ))
    BEGIN
      SELECT 
        @p_id_cabang = id_cabang, 
        @p_id_unit_pelayanan = id_unit_pelayanan
      FROM dbo.func_get_history_agen_hirarki(@p_tgl_produksi)
      WHERE 
        id_agen = @p_id_agen
      
      -------------------------------------------------------------------------------------------------------

      UPDATE spaj_register
      SET 
        id_cabang = @p_id_cabang,
        id_unit_pelayanan_korespondensi = @p_id_unit_pelayanan,
        id_unit_pelayanan_produksi = @p_id_unit_pelayanan
      WHERE 
        no_spaj = @pi_no_spaj

      IF @@ERROR <> 0
      BEGIN
        SET @po_message = 'Error updating table spaj_register'
        --RETURN (-1)
      END
      
      -------------------------------------------------------------------------------------------------------

      IF (EXISTS(
        SELECT 1 FROM spaj
        WHERE 
          (no_spaj = @pi_no_spaj) AND 
          (id_unit_pelayanan_korespondensi IS NULL)
      ))
      BEGIN
        UPDATE spaj
        SET 
          id_unit_pelayanan_korespondensi = @p_id_unit_pelayanan,
          id_unit_pelayanan_produksi = @p_id_unit_pelayanan
        WHERE 
          no_spaj = @pi_no_spaj

        IF @@ERROR <> 0
        BEGIN
          SET @po_message = 'Error updating table spaj'
          --RETURN (-1)
        END
      END
    END -- End: IF (EXISTS(SELECT 1 FROM dbo.func_get_history_agen_hirarki(@p_tgl_produksi)....
    ELSE
    BEGIN
      SET @po_message = 'Cabang Agen tidak terdaftar'
      --RETURN 1
    END
    
    ---------------------------------------------------------------------------------------------------------

    SELECT 
      @p_cek_PP_ya = ISNULL(p.cek_premi_pertama_ya, 1) 
    FROM 
      produk p, 
      plan_asuransi a 
    WHERE 
      (a.id_plan = @p_id_plan_dasar) AND 
      (a.id_produk = p.id_produk)
    
    ---------------------------------------------------------------------------------------------------------

    -- Cek kelompok produk
    IF (EXISTS(
      SELECT 1 
      FROM 
        jenis_produk j,
        produk p, 
        plan_asuransi a 
      WHERE 
        (a.id_plan = @p_id_plan_dasar) AND 
        (a.id_produk = p.id_produk) AND 
        (p.id_jenis_produk = j.id_jenis_produk) AND 
        (j.id_kelompok_produk = '01')
      ))
      BEGIN
        -- Jika tradisional
        SET @p_flexi = 0
      END
    ELSE
      BEGIN
        -- Jika unit link
        SET @p_flexi = 1
      END
    
    ---------------------------------------------------------------------------------------------------------

    -- Cek validasi premi
    
    -- Jika unit link
    IF (@p_flexi = 1) 
      BEGIN
        IF (EXISTS(
          SELECT 1 FROM spaj
          WHERE no_spaj = @pi_no_spaj
        ))
        BEGIN
          SELECT @p_total_premi = ISNULL(premi_berkala, 0) + ISNULL(top_up_berkala, 0) 
          FROM spaj
          WHERE no_spaj = @pi_no_spaj
        END
        ELSE
        BEGIN
          SET @p_total_premi = 0
        END
      END -- End: Jika unit link
    ELSE
    -- Jika tradisional
    BEGIN
      IF (EXISTS(
        SELECT 1 FROM spaj_plan
        WHERE 
          (UPPER(status) = 'I') AND 
          (no_spaj = @pi_no_spaj)
        ))
        BEGIN
          SELECT @p_total_premi = SUM(ISNULL(total_premi, 0)) 
          FROM spaj_plan
          WHERE 
            (UPPER(status) = 'I') AND 
            (no_spaj = @pi_no_spaj)
            
          -- diskon premi ga dihitung?
        END
      ELSE
        BEGIN
          SET @p_total_premi = 0
          SET @p_diskon_premi = 0
        END
    END -- End: Jika tradisional
    
    ---------------------------------------------------------------------------------------------------------

    SELECT 
      @p_total_premi_header = COALESCE(premi_dasar, 0) + COALESCE(premi_rider, 0) + 
                              COALESCE(premi_berkala, 0) + COALESCE(top_up_berkala, 0) 
    FROM spaj
    WHERE no_spaj = @pi_no_spaj
    
    IF (@p_total_premi_header <> @p_Total_premi)
    BEGIN
      SET @po_message = 'Total Premi belum benar'
      --RETURN 1
    END 
    
    ---------------------------------------------------------------------------------------------------------

    --Cek jika premi pertama dan bukan transfer boleh lewatin otorisasi premi pertama

    -- 004 Transfer

    -- Jika cek pp atau cara bayar = transfer
    IF (@p_cek_PP_ya = 1) OR (@p_id_cara_bayar = '004')
    BEGIN
      --cek penerimaan premi pertama
      SELECT 
        @p_id_kelompok_produk = j.id_kelompok_produk 
      FROM 
        spaj h, 
        plan_asuransi a,
        produk p,
        jenis_produk j
      WHERE 
        (h.no_spaj = @pi_no_spaj) AND 
        (h.id_plan_dasar = a.id_plan) AND 
        (a.id_produk = p.id_produk) AND
        (p.id_jenis_produk = j.id_jenis_produk)

      -- Jika premi pertama belum diinput
      IF (NOT(EXISTS(
        SELECT 1 FROM SPAJ_setoran
        WHERE 
          (no_spaj = @pi_no_spaj) AND 
          (UPPER(status) = 'I')
      )))
      BEGIN
        SET @po_message = 'Premi Pertama belum diinput';
        --RETURN 1
      END
      ELSE
      -- Jika premi pertama sudah diinput
      BEGIN
        SELECT 
          @p_id_valuta_setoran          = UPPER(id_valuta), 
          @p_jumlah_bayar_PP            = (ISNULL(jml_premi_pertama, 0) + ISNULL(jml_penyesuaian, 0)),
          @p_id_kelompok_produk_setoran = id_kelompok_produk
        FROM SPAJ_setoran
        WHERE 
          (no_spaj = @pi_no_spaj) AND 
          (UPPER(status) = 'I')

        -----------------------------------------------------------------------------------------------------

        -- Cek valuta setoran dengan valuta spaj harus sama
        IF (@p_id_valuta <> @p_id_valuta_setoran)
        BEGIN
          SET @po_message = 'Mata Uang Penerimaan Premi Pertama (' + @p_id_valuta_setoran + 
                            ') tidak sama dengan mata uang SPAJ (' + @p_id_valuta + ')';
          --RETURN 1
        END
        
        -----------------------------------------------------------------------------------------------------

        --Cek kelompok produk harus sama
        IF (@p_id_kelompok_produk <> @p_id_kelompok_produk_setoran)
        BEGIN
          SELECT @p_kelompok_produk_setoran_desc = deskripsi
          FROM Kelompok_Produk
          WHERE id_kelompok_produk = @p_id_kelompok_produk_setoran

          SELECT @p_kelompok_produk_desc = deskripsi
          FROM Kelompok_Produk
          WHERE id_kelompok_produk = @p_id_kelompok_produk
          
          SET @po_message = 'Kelompok Produk Penerimaan Premi Pertama (' + @p_kelompok_produk_setoran_desc + 
                            ') tidak sama dengan kelompok produk SPAJ (' + @p_kelompok_produk_desc + ')';
          --RETURN 1
        END
        
        -----------------------------------------------------------------------------------------------------

        SELECT @p_max_tgl_bayar = tgl_lunas 
        FROM spaj_setoran
        WHERE 
          (no_spaj = @pi_no_spaj) AND 
          (UPPER(status) = 'I')

        -----------------------------------------------------------------------------------------------------

        -- Apakah sudah ada di atas????
        -- Yang di atas sepertinya salah
        
        -- Jika unit link
        IF (@p_flexi = 1) 
        BEGIN
          IF (EXISTS(
            SELECT 1 FROM spaj
            WHERE no_spaj = @pi_no_spaj
          ))
          BEGIN
            SELECT @p_total_premi = ISNULL(premi_berkala, 0) + ISNULL(top_up_berkala, 0) 
            FROM spaj
            WHERE no_spaj = @pi_no_spaj
          END
          ELSE
          BEGIN
            SET @p_total_premi = 0
          END

          SET @p_diskon_premi = 0
        END -- End: Jika unit link
        ELSE
        -- Jika tradisional
        BEGIN
          IF  (EXISTS(
            SELECT 1 FROM spaj_plan
            WHERE 
              (UPPER(status) = 'I') AND 
              (no_spaj = @pi_no_spaj)
          ))
          BEGIN
            SELECT @p_total_premi = SUM(ISNULL(total_premi, 0)) 
            FROM spaj_plan
            WHERE 
              (UPPER(status) = 'I') AND 
              (no_spaj = @pi_no_spaj)

            SELECT 
              @p_diskon_premi = SUM(ISNULL(diskon_premi, 0)) 
            FROM spaj_plan
            WHERE 
              (UPPER(status) = 'I') AND 
              (no_spaj = @pi_no_spaj)
          END
          ELSE
          BEGIN
            SET @p_total_premi = 0
            SET @p_diskon_premi = 0
          END
        END -- End: Jika tradisional
        
        -----------------------------------------------------------------------------------------------------

        SET @p_total_premi_all = @p_total_premi + @p_biaya_administrasi + @p_Biaya_materai - @p_diskon_premi 

        IF (EXISTS(
          SELECT 1 FROM plan_valuta
          WHERE 
            (id_plan = @p_id_plan_dasar) AND 
            (id_valuta = @p_id_valuta) AND 
            (COALESCE(premi_disetor, 0) = 0)
        ))
        BEGIN
          IF @p_id_valuta = 'IDR'
          BEGIN
            SET @p_total_premi_all = CAST(@p_total_premi_all AS decimal(18, 0))
            SET @p_jumlah_bayar_pp = CAST(@p_jumlah_bayar_pp AS decimal(18, 0))
            SET @p_patokan = 10000
          END
          ELSE
          BEGIN
            -- Asumsi USD
            SET @p_patokan = 1
          END

          IF ((@p_total_premi_all) - @p_jumlah_bayar_PP) >= @p_patokan
          BEGIN
            SELECT @p_simbol_valuta_setoran = simbol 
            FROM valuta
            WHERE id_valuta = @p_id_valuta_setoran
            
            SELECT @p_simbol_valuta = simbol 
            FROM valuta
            WHERE id_valuta = @p_id_valuta
            
            SET @po_message = 
              'Premi Pertama belum lunas' + char(13)+ char(10) +
              'Jumlah bayar = ' + @p_simbol_valuta_setoran + ' ' + dbo.func_format_valuta (@p_id_valuta_setoran, @p_jumlah_bayar_pp, 18, 1) + char(13)+ char(10) +
              'Jumlah premi = ' + @p_simbol_valuta + ' ' + dbo.func_format_valuta (@p_id_valuta_setoran, @p_total_premi_all, 18, 1) + char(13)+ char(10)
            --RETURN 1
          END
        END
        ELSE
        BEGIN
          IF ((@p_total_premi_all) - @p_jumlah_bayar_PP) >= @p_patokan
          BEGIN
            SELECT @p_simbol_valuta_setoran = simbol 
            FROM valuta
            WHERE id_valuta = @p_id_valuta_setoran
            
            SELECT @p_simbol_valuta = simbol 
            FROM valuta
            WHERE id_valuta = @p_id_valuta
            
            SET @po_message = 
              'Premi Pertama belum lunas' + char(13)+ char(10) +
              'Jumlah bayar = ' + @p_simbol_valuta_setoran + ' ' + dbo.func_format_valuta (@p_id_valuta_setoran, @p_jumlah_bayar_pp, 18, 1) + char(13)+ char(10) +
              'Jumlah premi = ' + @p_simbol_valuta + ' ' + dbo.func_format_valuta (@p_id_valuta_setoran, @p_total_premi_all, 18, 1) + char(13)+ char(10)
            --RETURN 1
          END
        END
      END -- End: Jika premi pertama sudah diinput
    END -- End: IF (@p_cek_PP_ya = 1) OR (@p_id_cara_bayar = '004')
  END -- End: IF @pi_Status IN ('I' ,'E')
  
  -----------------------------------------------------------------------------------------------------------
 
  -- Jika otorisasi kedua
  IF @pi_status = 'I' 
  BEGIN
    IF @p_status_lama = 'I'
      BEGIN
        SET @po_message = 'SPAJ sudah pernah diotorisasi kedua.'
        --RETURN 1
      END
    ELSE
    IF @p_status_lama = 'G'
      BEGIN
        SET @po_message = 'SPAJ belum diotorisasi pertama.'
        --RETURN 1
      END
    ELSE
    IF @p_status_lama = 'C'
      BEGIN
        SET @po_message = 'Status SPAJ sudah batal.'
        --RETURN 1
      END
    ELSE
    -- Jika boleh otorisasi kedua
    BEGIN
      -- Ambil no_polis
      --IF (@p_no_polis IS NULL) OR (@p_no_polis = '')
      --BEGIN
      --  EXEC @p_return_status = dbo.gproc_GetNo @p_tgl_produksi, 'no_polis', @p_no_polis OUTPUT
        
      --  IF (@p_return_status <> 0) OR (COALESCE(@p_no_polis, '') = '')
      --  BEGIN
      --    SET @po_message = 'Error generating no_polis'
      --    --RETURN 1
      --  END
      --SET @p_no_polis='1234567'
      --END

      -------------------------------------------------------------------------------------------------------

      ---- Buat Virtual Account
      
      ---- Ambil kelompok produk
      --SELECT 
      --  @p_id_kelompok_produk = j.id_kelompok_produk 
      --FROM 
      --  plan_asuransi a 
      --  INNER JOIN
      --  produk p 
      --  ON a.id_produk = p.id_produk 
      --  INNER JOIN
      --  jenis_produk j 
      --  ON p.id_jenis_produk = j.id_jenis_produk
      --WHERE 
      --  a.id_plan = @p_id_plan_dasar

      ---- Jika unit link
      --IF (@p_id_kelompok_produk = '11')
      --BEGIN
      --  SELECT @p_id_jenis_investasi = id_jenis_investasi 
      --  FROM spaj
      --  WHERE no_spaj = @pi_no_spaj
        
      --  SELECT @p_jenis_investasi = mapping_virtual_account 
      --  FROM Jenis_Investasi
      --  WHERE id_jenis_investasi = @p_id_jenis_investasi
        
      --  IF (LEN(@p_jenis_investasi) < 2)
      --  BEGIN
      --    SET @po_message= 'Mapping virtual account pada master jenis investasi kurang dari 2 huruf'
      --    --RETURN 1
      --  END
      --  ELSE
      --  BEGIN
      --    SET @p_jenis_investasi = SUBSTRING(@p_jenis_investasi, 1, 2)
      --  END
      --END -- End: IF (@p_id_kelompok_produk = '11')
      --ELSE
      ---- Jika tradisional
      --BEGIN
      --  set @p_jenis_investasi = '00'
      --END -- End: Jika tradisional

      --IF (@p_id_valuta = 'IDR')
      --BEGIN
      --  SET @p_kode_virtual_account = '8809'
      --end
      --ELSE
      --IF (@p_id_valuta = 'USD')
      --BEGIN
      --  SET @p_kode_virtual_account = '8819'
      --END

      --SELECT @p_no_polis_Angka = dbo.func_ambil_angka(@p_no_polis)
      --SELECT @p_no_Polis_VA = dbo.func_Add_Prefiks_Custom('0', @p_no_polis_angka, 10)
      
      --SET @p_no_virtual_account = @p_kode_virtual_account + @p_jenis_investasi + @p_no_polis_VA

      -------------------------------------------------------------------------------------------------------

      -- Update SPAJ
      UPDATE spaj
      SET no_polis = @p_no_polis,
      STATUS = @pi_status
      WHERE no_spaj = @pi_no_spaj
      
      IF (@@ROWCOUNT = 0) OR (@@ERROR <> 0)
      BEGIN
        SET @po_message= 'Error updating table spaj'
        --RETURN 1
      END

      -------------------------------------------------------------------------------------------------------

      -- Update Autodebet untuk telemarketing

      IF (EXISTS(
        SELECT 1 FROM auto_debet
        WHERE no_spaj = @pi_no_spaj
      ))
      BEGIN
        UPDATE auto_debet
        SET no_polis = @p_no_polis
        WHERE no_spaj = @pi_no_spaj
        
        IF (@@ROWCOUNT = 0) OR (@@ERROR <> 0)
        BEGIN
          SET @po_message= 'Error updating table auto_debet'
          --RETURN 1
        END
      END

      -------------------------------------------------------------------------------------------------------
  
      -- Update/Insert spaj_persetujuan

      IF (EXISTS(
        SELECT 1 FROM spaj_persetujuan
        WHERE no_spaj = @pi_no_spaj
      )) 
      BEGIN
        UPDATE spaj_persetujuan
        SET 
          approve2_by   = @pi_idUser,
          approve2_date = @pi_approve_date,
          approve2_note = @pi_note,
          change_date   = @pi_approve_date,
          change_by     = @pi_idUser
        WHERE 
          no_spaj = @pi_no_spaj

        IF (@@ROWCOUNT = 0) OR (@@ERROR <> 0)
        BEGIN
          SET @po_message= 'Error updating table spaj_persetujuan'
          --RETURN 1
        END
      END
      ELSE
      BEGIN
        INSERT INTO spaj_persetujuan (
          no_spaj, approve2_by, approve2_date, approve2_note, status, create_date, create_by, change_date, change_by
        )
        VALUES (
          @pi_no_spaj, @pi_idUser, @pi_approve_date, @pi_note, @pi_status, @pi_approve_date, @pi_idUser, @pi_approve_date, @pi_idUser
        )
        
        IF (@@ROWCOUNT = 0) OR (@@ERROR <> 0)
        BEGIN
          SET @po_message= 'Error inserting spaj persetujuan'
          --RETURN 1
        END
      END
      
      -------------------------------------------------------------------------------------------------------

      -- Masukkan kartu kredit

      EXEC @p_return_status = proc_copy_kartu_kredit_from_spaj 
        @pi_no_spaj, 
        @pi_approve_date
      
      IF @P_RETURN_STATUS <> 0
      BEGIN
        SET @po_message= 'Error on executing proc_copy_kartu_kredit_from_spaj'
        --RETURN 1
      END

      -------------------------------------------------------------------------------------------------------

      -- Generate id_polis
      
      EXEC @p_return_status = dbo.gproc_GetNo  @pi_approve_date, 'id_polis', @p_id_polis OUTPUT
      
      IF (@@ERROR <> 0) OR (@p_return_status <> 0)
      BEGIN
        SET @po_message = 'Error on generating new id_polis.'
        --RETURN(-1)
      END

      -------------------------------------------------------------------------------------------------------

      -- Masukkan data ke table polis

      -- Jika unit link
      IF (@p_flexi = 1)
      BEGIN
        INSERT INTO polis (
          id_polis, no_polis, no_spaj, id_plan_dasar, id_periode_bayar, id_cara_bayar_nasabah, 
          keterangan_cara_bayar_nasabah, id_valuta, tgl_mulai, tgl_selesai, tgl_produksi, id_jenis_investasi, 
          id_program_marketing, masa_kontrak_dasar, masa_pembayaran_premi_dasar, uang_pertanggungan_dasar, 
          premi_dasar, premi_rider, premi_berkala, top_up_berkala, total_premi, id_perusahaan, id_agen, id_cabang, 
          id_unit_pelayanan_korespondensi, id_unit_pelayanan_produksi, medical_PPM_ya, medical_TTG_ya, multi_polis_ya, 
          multi_payor_benefit_rider_ya, paket_ya, alternatif2_ya, tgl_alternatif2, catatan, aktif_ya, 
          id_status_perubahan, biaya_administrasi, biaya_materai, status_valid, create_date, create_by, 
          change_date, change_by, approve_date, approve_by, tgl_mulai_investasi, 
          prosen_ekstra_premi_ttg, prosen_ekstra_premi_ppm, no_polis_cetak, tgl_berlaku
        )
        SELECT 
          @p_id_polis, no_polis, no_spaj, id_plan_dasar, id_periode_bayar, id_cara_bayar_nasabah, 
          keterangan_cara_bayar_nasabah, id_valuta, tgl_mulai, tgl_selesai, tgl_produksi, id_jenis_investasi, 
          id_program_marketing, masa_kontrak_dasar, masa_pembayaran_premi_dasar, uang_pertanggungan_dasar, 
          premi_dasar, premi_rider, premi_berkala, top_up_berkala, total_premi, id_perusahaan, id_agen, id_cabang, 
          id_unit_pelayanan_korespondensi, id_unit_pelayanan_produksi, medical_PPM_ya, medical_TTG_ya, multi_polis_ya, 
          multi_payor_benefit_rider_ya, paket_ya, alternatif2_ya, tgl_alternatif2, catatan, 1, 
          '00', biaya_administrasi*administrasi_ya, biaya_materai*materai_kuitansi_ya, 1, @pi_approve_date, @pi_IdUser, 
          @pi_approve_date, @pi_IdUser, @pi_approve_date, @pi_IdUser, dbo.func_link_next_date(tgl_mulai), 
          prosen_ekstra_premi_ttg, prosen_ekstra_premi_ppm, no_polis, tgl_mulai
        FROM spaj
        WHERE no_spaj = @pi_no_spaj
        
        IF (@@ROWCOUNT = 0) OR (@@ERROR <> 0)
        BEGIN
          SET @po_message= 'Error inserting polis flexi'
          --RETURN 1
        END
      END
      ELSE
      -- Jika tradisional
      BEGIN
        INSERT INTO polis (
          id_polis, no_polis, no_spaj, id_plan_dasar, id_periode_bayar, id_cara_bayar_nasabah, 
          keterangan_cara_bayar_nasabah, id_valuta, tgl_mulai, tgl_selesai, tgl_produksi, id_jenis_investasi, 
          id_program_marketing, masa_kontrak_dasar, masa_pembayaran_premi_dasar, uang_pertanggungan_dasar, 
          premi_dasar, premi_rider, premi_berkala, top_up_berkala, total_premi, id_perusahaan, id_agen, id_cabang, 
          id_unit_pelayanan_korespondensi, id_unit_pelayanan_produksi, medical_PPM_ya, medical_TTG_ya, multi_polis_ya, 
          multi_payor_benefit_rider_ya, paket_ya, alternatif2_ya, tgl_alternatif2, catatan, aktif_ya, 
          id_status_perubahan, biaya_administrasi, biaya_materai, status_valid, create_date, create_by, 
          change_date, change_by, approve_date, approve_by, prosen_ekstra_premi_ttg, prosen_ekstra_premi_ppm, 
          no_polis_cetak, tgl_berlaku
        )
        SELECT 
          @p_id_polis, no_polis, no_spaj, id_plan_dasar, id_periode_bayar, COALESCE(id_cara_bayar_next, id_cara_bayar_nasabah), 
          keterangan_cara_bayar_nasabah, id_valuta, tgl_mulai, tgl_selesai, tgl_produksi, id_jenis_investasi, 
          id_program_marketing, masa_kontrak_dasar, masa_pembayaran_premi_dasar, uang_pertanggungan_dasar, 
          premi_dasar, premi_rider, premi_berkala, top_up_berkala, total_premi, id_perusahaan, id_agen, id_cabang, 
          id_unit_pelayanan_korespondensi, id_unit_pelayanan_produksi, medical_PPM_ya, medical_TTG_ya, multi_polis_ya, 
          multi_payor_benefit_rider_ya, paket_ya, alternatif2_ya, tgl_alternatif2, catatan, 1, 
          '00', biaya_administrasi*administrasi_ya, biaya_materai*materai_kuitansi_ya, 1, @pi_approve_date, @pi_IdUser, 
          @pi_approve_date, @pi_IdUser, @pi_approve_date, @pi_IdUser, prosen_ekstra_premi_ttg, prosen_ekstra_premi_ppm, 
          no_polis, tgl_mulai
        FROM spaj
        WHERE no_spaj = @pi_no_spaj
        
        IF (@@ROWCOUNT = 0) OR (@@ERROR <> 0)
        BEGIN
          SET @po_message= 'Error inserting polis tradisional'
          --RETURN 1
        END
      END
      
      -------------------------------------------------------------------------------------------------------

      ---- Masukkan virtual Account
      
      --INSERT INTO virtual_account (
      --  id_polis, no_polis, no_virtual_account, tgl_berlaku, status, create_by, create_date, change_by, change_date
      --)
      --VALUES (
      --  @p_id_polis, @p_no_polis, @p_no_virtual_account, @pi_approve_date, 'I', @pi_idUser, @pi_approve_date, @pi_idUser, @pi_approve_date
      --)

      --IF (@@ROWCOUNT = 0) OR (@@ERROR <> 0)
      --BEGIN
      --  SET @po_message= 'Error inserting into table virtual_account'
      --  --RETURN 1
      --END

      -------------------------------------------------------------------------------------------------------

      -- Masukkan polis pemegang
      
      INSERT INTO polis_pemegang (
        id_polis, id_pemegang, nama_lengkap, gelar_sebelum_nama, gelar_setelah_nama, id_jenis_kelamin, tgl_lahir, 
        id_agama, id_jenis_identitas, no_identitas, id_jenis_warga_negara, id_status_kawin, id_pekerjaan,
        id_jenis_alamat_korespondensi, hp, email, status, create_date, create_by, change_date, change_by
      )
      SELECT 
        @p_id_polis, id_pemegang, nama_lengkap, gelar_sebelum_nama, gelar_setelah_nama, id_jenis_kelamin, tgl_lahir,
        id_agama, id_jenis_identitas, no_identitas, id_jenis_warga_negara, id_status_kawin, id_pekerjaan, 
        id_jenis_alamat_korespondensi, hp, email, 'I', @pi_approve_date, @pi_IdUser, @pi_approve_date, @pi_IdUser
      FROM spaj_pemegang
      WHERE no_spaj = @pi_no_spaj

      IF (@@ERROR <> 0)
      BEGIN
        SET @po_message = 'Error on inserting into table polis_pemegang'
        PRINT @po_message
        --RETURN(-1)
      END
      
      -------------------------------------------------------------------------------------------------------
      
      -- Masukkan polis pemegang alamat
      
      INSERT INTO polis_pemegang_alamat (
        id_polis, id_jenis_alamat, alamat, no_rt, no_rw, kelurahan, kecamatan, kota, id_kd_pos, id_provinsi, 
        id_negara, telp, fax, status, create_date, create_by, change_date, change_by,alamat_1, alamat_2, alamat_3
      )
      SELECT 
        @p_id_polis, id_jenis_alamat, COALESCE(alamat, ''), no_rt, no_rw, kelurahan, kecamatan, kota, id_kd_pos, id_provinsi, 
        id_negara, telp, fax, 'I', @pi_approve_date, @pi_IdUser, @pi_approve_date, @pi_IdUser,alamat_1, alamat_2, alamat_3
      FROM spaj_pemegang_alamat
      WHERE no_spaj = @pi_no_spaj
      
      IF (@@ERROR <> 0)
      BEGIN
        SET @po_message = 'Error on inserting into table polis_pemegang_alamat'
        PRINT @po_message
        --RETURN(-1)
      END

      -------------------------------------------------------------------------------------------------------

      -- Masukkan polis plan
      INSERT INTO polis_plan (
        id_polis, aktif_ya, id_plan_dasar, id_plan, deskripsi, kelas, risiko, masa_pembayaran_premi, masa_kontrak, 
        tgl_mulai, tgl_selesai, tgl_mulai_berlaku, tgl_selesai_berlaku, uang_pertanggungan, premi, ekstra_premi, 
        prosen_ekstra_premi, total_premi, premi_lama, no_urut, status, create_date, create_by, change_date, change_by,
        id_status_plan, diskon_premi
      )
      SELECT 
        @p_id_polis, 1, id_plan_dasar, id_plan, deskripsi, kelas, risiko, masa_pembayaran_premi, masa_kontrak, 
        tgl_mulai, tgl_selesai, tgl_mulai_berlaku, tgl_selesai_berlaku, uang_pertanggungan, premi, ekstra_premi, 
        prosen_ekstra_premi, total_premi, premi_lama, no_urut, 'I', @pi_approve_date, @pi_IdUser, @pi_approve_date, @pi_IdUser, 
        '00', diskon_premi
      FROM spaj_plan
      WHERE no_spaj = @pi_no_spaj

      IF (@@ERROR <> 0)
      BEGIN
        SET @po_message = 'Error on inserting into table polis_plan'
        PRINT @po_message
        --RETURN(-1)
      END

      -------------------------------------------------------------------------------------------------------

      -- Masukkan polis termaslahat
      
      INSERT INTO polis_termaslahat (
        id_polis, no_urut, nama_ahli_waris, gelar_sebelum_nama, gelar_setelah_nama, tgl_lahir, 
        id_hubungan, status, create_date, create_by, change_date, change_by
      )
      SELECT 
        @p_id_polis, no_urut, nama_ahli_waris, gelar_sebelum_nama, gelar_setelah_nama, tgl_lahir, 
        id_hubungan, 'I', @pi_approve_date, @pi_IdUser, @pi_approve_date, @pi_IdUser
      FROM spaj_termaslahat
      WHERE no_spaj = @pi_no_spaj

      IF (@@ERROR <> 0)
      BEGIN
        SET @po_message = 'Error on inserting into table polis_termaslahat'
        PRINT @po_message
        --RETURN(-1)
      END

      -------------------------------------------------------------------------------------------------------

      -- Masukkan polis tertanggung
      
      INSERT INTO polis_tertanggung (
        id_polis, id_tertanggung, nama_lengkap, gelar_sebelum_nama, gelar_setelah_nama, id_jenis_kelamin, tgl_lahir, 
        id_agama, id_jenis_identitas, no_identitas, id_jenis_warga_negara, id_status_kawin, id_pekerjaan, 
        id_jenis_alamat_korespondensi, hp, email, id_hubungan, status, create_date, create_by, change_date,
        change_by
      )
      SELECT 
        @p_id_polis, id_tertanggung, nama_lengkap, gelar_sebelum_nama, gelar_setelah_nama, id_jenis_kelamin, tgl_lahir,
        id_agama, id_jenis_identitas, no_identitas, id_jenis_warga_negara, id_status_kawin, id_pekerjaan, 
        id_jenis_alamat_korespondensi, hp, email, id_hubungan, 'I', @pi_approve_date, @pi_IdUser, @pi_approve_date, 
        @pi_IdUser
      FROM spaj_tertanggung
      WHERE no_spaj = @pi_no_spaj

      IF (@@ERROR <> 0)
      BEGIN
        SET @po_message = 'Error on inserting into table polis_tertanggung'
        PRINT @po_message
        --RETURN(-1)
      END

      -------------------------------------------------------------------------------------------------------

      -- Masukkan polis tertanggung tambahan
      
      INSERT INTO polis_tertanggung_tambahan (
        id_polis, no_urut, id_tertanggung, nama_lengkap, gelar_sebelum_nama, gelar_setelah_nama, id_jenis_kelamin, tgl_lahir, 
        id_agama, id_jenis_identitas, no_identitas, id_jenis_warga_negara, id_status_kawin, id_pekerjaan, id_hubungan, 
        status, create_date, create_by, change_date, change_by
      )
      SELECT 
        @p_id_polis, no_urut, id_tertanggung, nama_lengkap, gelar_sebelum_nama, gelar_setelah_nama, id_jenis_kelamin, tgl_lahir, 
        id_agama, id_jenis_identitas, no_identitas, id_jenis_warga_negara, id_status_kawin, id_pekerjaan, id_hubungan, 
        'I', @pi_approve_date, @pi_IdUser, @pi_approve_date, @pi_IdUser
      FROM spaj_tertanggung_tambahan
      WHERE no_spaj = @pi_no_spaj

      IF (@@ERROR <> 0)
      BEGIN
        SET @po_message = 'Error on inserting into table polis_tertanggung_tambahan'
        PRINT @po_message
        --RETURN(-1)
      END

      -------------------------------------------------------------------------------------------------------

      -- Masukkan polis tertanggung alamat
      
      INSERT INTO polis_tertanggung_alamat (
        id_polis, id_jenis_alamat, alamat, no_rt, no_rw, kelurahan, kecamatan, kota, id_kd_pos, id_provinsi, 
        id_negara, telp, fax, status, create_date, create_by, change_date, change_by, alamat_1, alamat_2, alamat_3
      )
      SELECT 
        @p_id_polis, id_jenis_alamat, COALESCE(alamat, ''), no_rt, no_rw, kelurahan, kecamatan, kota, id_kd_pos, id_provinsi, 
        id_negara, telp, fax, 'I', @pi_approve_date, @pi_IdUser, @pi_approve_date, @pi_IdUser, alamat_1, alamat_2, alamat_3
      FROM spaj_tertanggung_alamat
      WHERE no_spaj = @pi_no_spaj

      IF (@@ERROR <> 0)
      BEGIN
        SET @po_message = 'Error on inserting into table polis_tertanggung_alamat'
        PRINT @po_message
        --RETURN(-1)
      END

      -------------------------------------------------------------------------------------------------------

      -- Masukkan polis jenis_em_ttg

      INSERT INTO polis_jenis_em_ttg (
        id_polis, id_jenis_em, status, create_date, create_by, change_date, change_by
      )
      SELECT 
        @p_id_polis, id_jenis_em, 'I', @pi_approve_date, @pi_IdUser, @pi_approve_date, @pi_IdUser
      FROM spaj_jenis_em_ttg
      WHERE no_spaj = @pi_no_spaj

      IF (@@ERROR <> 0)
      BEGIN
        SET @po_message = 'Error on inserting into table polis_jenis_em_ttg'
        PRINT @po_message
        --RETURN(-1)
      END

      -------------------------------------------------------------------------------------------------------

      -- Masukkan polis jenis_em_ppm
      
      INSERT INTO polis_jenis_em_ppm (
        id_polis, id_jenis_em, status, create_date, create_by, change_date, change_by
      )
      SELECT 
        @p_id_polis, id_jenis_em, 'I', @pi_approve_date, @pi_IdUser, @pi_approve_date, @pi_IdUser
      FROM spaj_jenis_em_ppm
      WHERE no_spaj = @pi_no_spaj
      
      IF (@@ERROR <> 0)
      BEGIN
        SET @po_message = 'Error on inserting into table polis_jenis_em_ppm'
        PRINT @po_message
        --RETURN(-1)
      END

      -------------------------------------------------------------------------------------------------------

      -- Generating id_polis_cetak

      EXEC @p_return_status = [dbo].[gproc_GetNo] @p_tgl_produksi, 'id_polis_cetak', @p_id_polis_cetak OUTPUT
      
      IF (@p_return_status <> 0)
      BEGIN
        SET @po_message= 'Error on generating id_polis_cetak'
        --RETURN 1
      END

      -------------------------------------------------------------------------------------------------------

      -- Masukkan polis cetak
      
      INSERT INTO polis_cetak (
        id_polis_cetak, no_polis, alasan, print_ya, create_date, create_by, change_date, change_by, approve_date, 
        approve_by, approve_note, status
      )
      VALUES (
        @p_id_polis_cetak, @p_no_polis, 'Cetak Pertama', 0, @pi_approve_date, @pi_IdUser, @pi_approve_date, @pi_IdUser, @pi_approve_date, 
        @pi_IdUser, 'Cetak Pertama', 'I'
      )
      
      IF (@@ERROR <> 0)
      BEGIN
        SET @po_message = 'Error on inserting into table polis_cetak'
        PRINT @po_message
        --RETURN(-1)
      END

      -------------------------------------------------------------------------------------------------------

      -- Update nasabah
      
      UPDATE n
      SET 
        n.nama_lengkap = r.nama_lengkap,
        n.gelar_sebelum_nama = r.gelar_sebelum_nama,
        n.gelar_setelah_nama = r.gelar_setelah_nama,
        n.id_jenis_kelamin = r.id_jenis_kelamin,
        n.tgl_lahir = r.tgl_lahir
      FROM 
        nasabah n 
        INNER JOIN 
        spaj_pemegang r 
        ON n.id_nasabah = r.id_pemegang
      WHERE 
        r.no_spaj = @pi_no_spaj
      
      IF (@@ERROR <> 0) AND (@@ROWCOUNT = 0)
      BEGIN
        SET @po_message = 'Error on updating table nasabah'
        PRINT @po_message
        --RETURN(-1)
      END
      
      SELECT @p_id_hubungan = id_hubungan 
      FROM spaj_tertanggung
      WHERE no_spaj = @pi_no_spaj
      
      -- Jika bukan diri sendiri
      IF (@p_id_hubungan <> '00')
      BEGIN
        UPDATE n
        SET 
          n.nama_lengkap = r.nama_lengkap,
          n.gelar_sebelum_nama = r.gelar_sebelum_nama,
          n.gelar_setelah_nama = r.gelar_setelah_nama,
          n.id_jenis_kelamin = r.id_jenis_kelamin,
          n.tgl_lahir = r.tgl_lahir
        FROM 
          nasabah n 
          INNER JOIN 
          spaj_tertanggung r 
          ON n.id_nasabah = r.id_tertanggung
        WHERE r.no_spaj = @pi_no_spaj
        
        IF (@@ERROR <> 0) AND (@@ROWCOUNT = 0)
        BEGIN
          SET @po_message = 'Error updating nasabah 00'
          PRINT @po_message
          --RETURN(-1)
        END
      END

      -------------------------------------------------------------------------------------------------------

      -- Masukkan premi_jt
      
      EXEC @p_return_status = dbo.proc_copy_from_spaj_setoran 
        @p_no_polis, 
        @pi_idUser,
        @po_id_premi OUTPUT, 
        @po_message OUTPUT
        
      IF @p_return_status <> 0
      --BEGIN
      --  --RETURN 1
      --END

      -------------------------------------------------------------------------------------------------------

      -- Masukkan top up tunggal
      
      SELECT @p_top_up_tunggal = COALESCE(jml_top_up_tunggal, 0) 
      FROM spaj_setoran
      WHERE no_spaj = @pi_no_spaj
      
      SELECT 
        @p_id_premi = id_premi, 
        @p_tanggal = tanggal 
      FROM premi_jt
      WHERE 
        (no_polis = @p_no_polis) AND 
        (jt_ke = 1) AND 
        (status = 'I')

      IF @p_top_up_tunggal > 0
      BEGIN
        IF (NOT(EXISTS(
          SELECT 1 FROM transaksi 
          WHERE 
            (id_jenis_transaksi = '205') AND 
            (no_referensi = @p_no_polis) AND 
            (total_debet = @p_top_up_tunggal) AND (status = 'I')
        )))
        BEGIN
          EXEC @p_return_status = gproc_GetNo @pi_Approve_Date, 'id_transaksi', @p_id_transaksi OUTPUT
          
          IF (@p_return_status <> 0)
          BEGIN
            SET @po_message = 'Error on generating id transaksi'
            PRINT @po_message
            --RETURN(-1)
          END
          
          ---------------------------------------------------------------------------------------------------

          SELECT @p_min_tgl_valuta = MIN(tgl_valuta) 
          FROM transaksi
          WHERE 
            (id_jenis_transaksi = '023') AND 
            (status = 'I') AND 
            (no_referensi = @p_id_premi)
          
          SELECT @p_id_transaksi_min = id_transaksi 
          FROM transaksi
          WHERE 
            (id_jenis_transaksi = '023') AND 
            (status = 'I') AND 
            (no_referensi = @p_id_premi) AND 
            (tgl_valuta = @p_min_tgl_valuta)
          
          ---------------------------------------------------------------------------------------------------

          INSERT INTO transaksi (
            id_transaksi, id_jenis_transaksi, tgl_konfirmasi, tgl_valuta, id_dokumen_register, no_acuan, 
            no_referensi, id_valuta, tipe, total_debet, total_kredit, saldo, otorisasi_ya, otorisasi_date, 
            otorisasi_by, otorisasi_adm, otorisasi_adm_date, otorisasi_adm_by, otorisasi_fin, otorisasi_fin_date, 
            otorisasi_fin_by, status, create_date, create_by, change_date, change_by, kode_konversi, ke, tahun, 
            id_plan, id_jenis_produk, id_cabang, id_departemen, id_group, id_acc, tgl_jtempo, tgl_jurnal
          )
          SELECT 
            @p_id_transaksi, '205', tgl_konfirmasi, tgl_valuta, id_dokumen_register, no_acuan, 
            no_acuan, id_valuta, tipe, @p_top_up_tunggal, 0, @p_top_up_tunggal, otorisasi_ya, otorisasi_date, 
            otorisasi_by, otorisasi_adm, otorisasi_adm_date, otorisasi_adm_by, otorisasi_fin, otorisasi_fin_date, 
            otorisasi_fin_by, status, create_date, create_by, change_date, change_by, kode_konversi, ke, tahun, 
            id_plan, id_jenis_produk, id_cabang, id_departemen, id_group, id_acc, tgl_jtempo, tgl_jurnal 
          FROM transaksi
          WHERE id_transaksi = @p_id_transaksi_min

          IF (@@ERROR <> 0) AND (@@ROWCOUNT = 0)
          BEGIN
            SET @po_message = 'Error inserting into table transaksi (205)'
            PRINT @po_message
            --RETURN(-1)
          END
          
          ---------------------------------------------------------------------------------------------------

          -- Check this!
          -- Apakah jam harus masuk?
          SET @p_tgl_skrg = getdate()
          
          DECLARE transauto_Cursor CURSOR FOR
          SELECT id_jenis_gl, object_reference, field_reference, key_field_reference, urutan, tipe 
          FROM jenis_transaksi_gl 
          WHERE id_jenis_transaksi = '205' 
          ORDER BY urutan

          OPEN transauto_Cursor
          FETCH NEXT FROM transauto_Cursor 
          INTO @p_id_jenis_gl, @p_object_reference, @p_field_reference, @p_key_field_reference, @p_urutan, @p_tipe

          WHILE @@FETCH_STATUS = 0
          BEGIN
            IF (ISNULL(LTRIM(RTRIM(@p_Object_reference)), '') <> '') AND
               (ISNULL(LTRIM(RTRIM(@p_field_reference)), '') <> '')
            BEGIN
              SET @p_SQLStr=
                N'INSERT INTO transaksi_detail(id_transaksi, id_jenis_gl, urutan, tgl_bayar, no_acuan, no_referensi, tipe, id_valuta, '+
                N'jml_terbit, ke, tahun, id_plan, id_jenis_produk, id_cabang, '+
                N'jml_lunas, tgl_kurs, nilai_kurs, '+
                N'id_acc, status, create_date, create_by, change_date, change_by, tgl_valuta) '+
                N'SELECT @_id_transaksi, @_id_jenis_gl, @_urutan, tgl_bayar, no_acuan, no_referensi, @_tipe, id_valuta, '+
                N'IsNull(' + @p_field_reference + ', 0), ke, tahun, id_plan, dbo.func_get_id_jenis_produk(id_plan), id_cabang, '+
                N'IsNull(' + @p_field_reference + ', 0), null, null, '+
                N'''01'', @_status, @_create_date, @pi_id_user, null, null, null '+
                N'FROM dbo.' + @p_object_reference + '(@_no_referensi, @_tanggal)'
              
              --PRINT @SQLStr

              EXECUTE sp_executesql @p_SQLStr, 
                N'@_id_transaksi varchar(12), @_id_jenis_gl varchar(3), @_urutan int, @_tipe int, @_field_reference varchar(50),
                @_status varchar(1), @_create_date datetime, @pi_id_user varchar(4), @_no_referensi varchar(15), @_tanggal datetime', 
                @p_id_transaksi, @p_id_jenis_gl, @p_urutan, @p_tipe, @p_field_reference, 'G', @p_tgl_skrg, @pi_iduser, @p_no_polis, @p_tanggal 

              IF (@@ROWCOUNT = 0) OR(@@ERROR <> 0)
              BEGIN
                SET @po_message = 'Error on inserting into table transaksi_detail.'
                PRINT @po_message
                --RETURN(-1)
              END
            END -- End: IF (ISNULL(LTRIM(RTRIM(@p_Object_reference)), '') <> '') AND...
          
            FETCH NEXT FROM transauto_Cursor 
            INTO @p_id_jenis_gl, @p_object_reference, @p_field_reference, @p_key_field_reference, @p_urutan, @p_tipe
          END -- End: WHILE @@FETCH_STATUS = 0

          CLOSE transauto_Cursor
          DEALLOCATE transauto_Cursor  
                
          ---------------------------------------------------------------------------------------------------

          SELECT @p_id_transaksi_lunas = id_transaksi 
          FROM transaksi
          WHERE 
            (id_jenis_transaksi = '023') AND 
            (status = 'I') AND 
            (no_referensi = @p_id_premi)

          INSERT INTO transaksi_lunas (
            id_transaksi, urutan, tgl_bayar, tgl_valuta, id_valuta, no_bukti, jml_lunas, tgl_kurs, nilai_kurs, 
            jml_transaksi, id_rekening_sumber, no_cek, id_cara_bayar, biaya_transfer, status, create_date, create_by, 
            change_date, change_by, keterangan
          )
          SELECT 
            @p_id_transaksi, urutan, tgl_bayar, tgl_valuta, id_valuta, no_bukti, @p_top_up_tunggal, tgl_kurs, nilai_kurs, 
            @p_top_up_tunggal, id_rekening_sumber, no_cek, id_cara_bayar, biaya_transfer, status, create_date, create_by, 
            change_date, change_by, keterangan
          FROM transaksi_lunas
          WHERE id_transaksi = @p_id_transaksi_lunas

          IF (@@ERROR <> 0) AND (@@ROWCOUNT = 0)
          BEGIN
            SET @po_message = 'Error on inserting into table transaksi_lunas'
            PRINT @po_message
            --RETURN(-1)
          END
        END -- End: IF (NOT(EXISTS(SELECT 1 FROM transaksi WHERE (id_jenis_transaksi = '205') AND ...
      END -- End: IF @p_top_up_tunggal > 0
      
      -------------------------------------------------------------------------------------------------------
      
      SELECT @p_id_transaksi = id_transaksi 
      FROM transaksi
      WHERE 
        (id_jenis_transaksi = '023') AND 
        (no_referensi = @po_id_premi)
      
      IF (@p_flexi = 1)
      BEGIN
        -- Masukkan link_nasabah
        EXEC @p_return_status = dbo.proc_link_create_id_nasabah 
          @p_no_polis, 
          @pi_idUser, 
          @po_message OUTPUT

        IF @p_return_status <> 0
        SET @po_message = 'error'
        --BEGIN
        --  --RETURN 1
        --END
        ELSE
        BEGIN
          SELECT @p_id_link_nasabah = id_link_nasabah 
          FROM link_nasabah
          WHERE no_polis = @p_no_polis

          SELECT 
            @p_id_transaksi = id_transaksi, 
            @p_tgl_otorisasi = dbo.func_fixed_date(otorisasi_fin_date)
            --@p_tgl_otorisasi = dbo.func_fixed_date(tgl_jurnal) -- Suspect
          FROM transaksi 
          WHERE 
            (id_jenis_transaksi = '023') AND 
            (no_referensi = @p_id_premi)
          
          SELECT @p_tgl_valuta = tgl_valuta 
          FROM transaksi_lunas 
          WHERE id_transaksi = @p_id_transaksi

          SELECT @p_id_jenis_investasi = id_jenis_investasi 
          FROM polis
          WHERE 
            (no_polis = @p_no_polis) AND 
            (id_polis = dbo.func_get_id_polis_per_tanggal(no_polis, getdate(), 0))
          
          -- Check this!
          -- Duplicate, sudah pernah ada di atas
          SET @p_tgl_sekarang = getdate()
          
          -- Masukkan Link transaksi
          EXEC @p_return_status = dbo.proc_link_proses_transaksi_all_pp  
            @p_no_polis, 
            @p_id_link_nasabah,
            @p_tgl_otorisasi,
            @p_tgl_otorisasi,
            @p_id_jenis_investasi,
            @pi_idUser, 
            1,
            1,
            1,
            @po_message OUTPUT

          IF @p_return_status <> 0
          SET @po_message = 'error'
          --BEGIN
          --  --RETURN 1
          --END
        END
      END -- End: IF (@p_flexi = 1)
      
      -------------------------------------------------------------------------------------------------------

      -- Masukkan history status polis

      SELECT @p_tgl_mulai = tgl_mulai 
      FROM polis
      WHERE no_polis = @p_no_polis
      
      INSERT INTO history_status_polis (
        no_polis, tgl_berlaku, id_status_polis, keterangan, fixed_status, status, create_date, create_by, 
        change_date, change_by, tgl_kejadian
      )
      VALUES (
        @p_no_polis, @pi_approve_date, '00', @pi_note, 0, 'I', @pi_approve_date, @pi_iduser, 
        @pi_approve_date, @pi_iduser, @p_tgl_mulai
      )
      
      IF (@@ERROR <> 0) OR (@@ROWCOUNT = 0)
      BEGIN
        SET @po_message = 'Error on inserting into table history_status_polis'
        --RETURN(1)
      END

      -------------------------------------------------------------------------------------------------------

      -- Masukan auto_debet
      -- Check this!
      -- Redundant, sudah ada di atas
      EXEC proc_update_auto_debet_spaj @pi_no_spaj

      -------------------------------------------------------------------------------------------------------
      
      SET @po_message = 'Otorisasi kedua berhasil'
      --RETURN 0
    END -- End: Jika boleh otorisasi kedua
  END -- End: IF @pi_status = 'I' 
  
  -----------------------------------------------------------------------------------------------------------

  -- Jika otorisasi pertama (verifikasi)
  ELSE 
  IF @pi_status = 'E'
  BEGIN
    IF @p_status_lama = 'E'
    BEGIN
      SET @po_message = 'SPAJ sudah diotorisasi pertama.'
      --RETURN 1
    END
    ELSE
    IF @p_status_lama = 'I'
    BEGIN
      SET @po_message = 'SPAJ sudah diotorisasi kedua.'
      --RETURN 1
    END
    ELSE
    IF @p_status_lama = 'C'
    BEGIN
      SET @po_message = 'Status SPAJ sudah batal'
      --RETURN 1
    END
    ELSE
    -- Jika boleh otorisasi pertama
    IF @p_status_lama = 'G'
    BEGIN
      -- Cek spaj register
      IF (EXISTS(
        SELECT 1 FROM spaj_register
        WHERE 
          (no_spaj = @pi_no_spaj) AND 
          (status = 'G')
      ))
      BEGIN
        SET @po_message= 'SPAJ Register masih draft'
        --RETURN 1
      END

      -------------------------------------------------------------------------------------------------------
      
      -- Validasi SPAJ - Alamat korespondensi pemegang polis
      
      SELECT @p_id_jenis_alamat_korespondensi_pemegang = id_jenis_alamat_korespondensi 
      FROM SPAJ_Pemegang
      WHERE no_spaj = @pi_no_spaj

      IF (NOT(EXISTS(
        SELECT id_jenis_alamat 
        FROM spaj_pemegang_alamat
        WHERE 
          (no_spaj = @pi_no_spaj) AND 
          (id_jenis_alamat = @p_id_jenis_alamat_korespondensi_pemegang)
      )))
      BEGIN
        SET @po_message= 'Jenis Alamat tagihan/korespondensi pada tab pembayaran premi tidak ada dalam jenis alamat pemegang'
        --RETURN 1
      END

      -------------------------------------------------------------------------------------------------------

      -- Validasi SPAJ - Alamat korespondensi tertanggung

      SELECT @p_id_jenis_alamat_korespondensi_Tertanggung = id_jenis_alamat_korespondensi 
      FROM SPAJ_Tertanggung
      WHERE no_spaj = @pi_no_spaj

      IF (NOT(EXISTS(
        SELECT id_jenis_alamat 
        FROM spaj_tertanggung_alamat
      WHERE 
        (no_spaj = @pi_no_spaj) AND 
        (id_jenis_alamat = @p_id_jenis_alamat_korespondensi_tertanggung)
      )))
      BEGIN
        SET @po_message= 'Alamat korespondensi tertanggung tidak ada dalam jenis alamat tertanggung'
        --RETURN 1
      END

      -------------------------------------------------------------------------------------------------------

      -- Update SPAJ

      UPDATE spaj
      SET status = @pi_status
      WHERE no_spaj = @pi_no_spaj
      
      IF (@@ERROR <> 0) AND (@@ROWCOUNT = 0)
      BEGIN
        SET @po_message = 'Error on updating table spaj'
        PRINT @po_message
        --RETURN(-1)
      END

      -------------------------------------------------------------------------------------------------------
  
      -- Update SPAJ Persetujuan

      IF (EXISTS(
        SELECT 1 FROM spaj_persetujuan
        WHERE no_spaj = @pi_no_spaj
      )) 
      BEGIN
        UPDATE spaj_persetujuan
        SET 
          approve_by = @pi_idUser,
          approve_date = @pi_approve_date,
          approve_note = @pi_note,
          change_date = @pi_approve_date,
          change_by = @pi_idUser
        WHERE 
          no_spaj = @pi_no_spaj

        IF (@@ERROR <> 0) AND (@@ROWCOUNT = 0)
        BEGIN
          SET @po_message = 'Error on updating table spaj_persetujuan'
          PRINT @po_message
          --RETURN(-1)
        END
      END
      ELSE
      BEGIN
        INSERT INTO spaj_Persetujuan (
          no_spaj, approve_by, approve_date, approve_note, status, create_date, create_by, change_date, change_by
        )
        VALUES (
          @pi_no_spaj, @pi_idUser, @pi_approve_date, @pi_note, @pi_status, @pi_approve_date, @pi_idUser, @pi_approve_date, @pi_idUser
        )

        IF (@@ERROR <> 0) AND (@@ROWCOUNT = 0)
        BEGIN
          SET @po_message = 'Error on inserting into table spaj_persetujuan'
          PRINT @po_message
          --RETURN(-1)
        END
      END
      
      -------------------------------------------------------------------------------------------------------

      SET @po_message = 'Otorisasi pertama berhasil'      
      --RETURN 0
    END -- End: IF @p_status_lama = 'G'
  END

  -----------------------------------------------------------------------------------------------------------

  -- Jika pembatalan spaj
  ELSE 
  IF @pi_status = 'C'
  BEGIN
    IF @p_status_lama = 'I'
    BEGIN
      SET @po_message = 'Sudah jadi polis tidak dapat batal'
      --RETURN 1
    END
    ELSE
    -- Jika boleh batal
    -- Yang boleh batal status 'G' dan 'E'
    BEGIN
      UPDATE spaj
      SET 
        status = @pi_status,
        cancel_date = @pi_approve_date,
        cancel_note = @pi_note,
        cancel_by = @pi_IdUser
      WHERE
        no_spaj = @pi_no_spaj

      IF (@@ERROR > 0) OR (@@ROWCOUNT = 0)
      BEGIN
        SET @po_message = 'Error on updating table spaj'
        --RETURN 1
      END 

      -------------------------------------------------------------------------------------------------------

      UPDATE spaj_register
      SET 
        status = @pi_status,
        cancel_date = @pi_approve_date,
        cancel_note = @pi_note,
        cancel_by = @pi_IdUser,
        id_status_spaj_register = @pi_id_status_spaj_register
      WHERE 
        no_spaj = @pi_no_spaj

      IF (@@ERROR > 0) OR (@@ROWCOUNT = 0)
      BEGIN
        SET @po_message = 'Error on updating table spaj_register'
        --RETURN 1
      END 
      
      -------------------------------------------------------------------------------------------------------

      SET @po_message = 'Pembatalan berhasil'
      --RETURN 0
    END -- End: Jika boleh batal
  END -- End: IF @pi_status = 'C'
  
  COMMIT
  
  SELECT @po_message