none
saya newbie , tolong bantuan nya saya punya sp yang harus nya baca ke enduser tapi ini malah ke enduserlunas RRS feed

  • Pertanyaan

  • Apa yang salah nya ya para master tolong bantuan nya,, ini sp kok setiap di proses malah pindah ke enduserlunas padahal

    Alter PROCEDURE USP_BATCH_PAYMENT
        @batchid varchar(20), @duedate datetime, @angs float, @userid varchar(20),
        @payflag varchar(5) = null, @totpokok float = null, @totbunga float = null
    as

    set nocount on

    if (@angs is null)
    begin
        if     (    (@totpokok is null and @totbunga is null)     )   
        begin
            select @angs = totangsuran, @totpokok = totpokok, @totbunga = totbunga
            from VW_BATCH_DUEDATE
            where batchid = @batchid
                and duedate = @duedate
                and lunas = 0
        end
        else begin
            set @angs = @totpokok + @totbunga
        end
    end

    if (@payflag is null)
        set @payflag = '3'

    if (@payflag = '3')
    begin
        insert into payinstallment (JD_SCHEMAID, SEQ, PAYDATE, PAYBUNGA, PAYPOKOK, PAYTOTAL, SISAPOK_BLN, SISAPOK_TOT)
            select jd.schemaid, 1, convert(datetime, convert(varchar, getdate(), 112)), jd.bunga, jd.pokok, jd.angsuran, 0, jd.outstanding
            from jadwalangsuran jd join enduser u on u.enduserid = jd.enduserid
            where u.batchid = @batchid and u.approve = 1 and jd.duedate = @duedate
                and jd.schemaid not in (select jd_schemaid from payinstallment)

        update jadwalangsuran set status = 1
        from jadwalangsuran jd join enduser u on u.enduserid = jd.enduserid
        where u.batchid = @batchid and u.approve = 1 and jd.duedate = @duedate AND jd.STATUS <> 2

        update enduserflag set
            PayPokok = jd.AKMPOKOK, PayBunga= jd.AKMBUNGA, PayTotal = jd.AKMPOKOK + jd.AKMBUNGA, SisaPokok = jd.OUTSTANDING
        from enduser u join enduserflag j on j.enduserid = u.enduserid
            left join jadwalangsuran jd on jd.enduserid = u.enduserid and u.approve = 1 and jd.duedate = @duedate
        where u.batchid = @batchid


        --begin Change by vina 29/05/13 --> pindah data dr tabel blm lunas ke tabel lunas

        --update enduserflag set
        --    Lunas = 1, LunasDate = getdate(), LunasBy = NULL
        --from enduser u join enduserflag j on j.enduserid = u.enduserid
        --    left join jadwalangsuran jd on jd.enduserid = u.enduserid and jd.status = 0
        --where u.batchid = @batchid and jd.enduserid is null             -- no more un-lunas row in jadwalangsuran --> all lunas

        update pks set BUCKET_LUNAS = isnull(BUCKET_LUNAS, 0) + isnull(jd.pokok, 0)   
        from enduser u
            join batch b on b.batchid = u.batchid
            join pks p on p.pksid = b.pksid
            left join jadwalangsuran jd on jd.enduserid = u.enduserid and jd.duedate = @duedate
        where u.batchid = @batchid

        update batchpayment set
            APPROVEDATE = getdate(), APPROVEBY = @userid
        where batchid = @batchid and duedate = @duedate

        exec USPX_BATCH_AUTOKREDIT @batchid, @duedate, @totpokok, @totbunga, @userid

        IF NOT EXISTS (SELECT 1 from enduser u join enduserflag j on j.enduserid = u.enduserid
        left join jadwalangsuran jd on jd.enduserid = u.enduserid and jd.status = 0    
        where u.batchid = @batchid)
        BEGIN
            if not exists (select 1 from ENDUSERLUNASFLAG where ENDUSERID like '%'+@batchid+'%')
            begin
                INSERT INTO ENDUSERLUNASFLAG
                select * from ENDUSERFLAG where ENDUSERID like '%'+@batchid+'%'
            end
            if not exists (select 1 from ENDUSERLUNASDUPFLAG where ENDUSERID like '%'+@batchid+'%')
            begin
                INSERT INTO ENDUSERLUNASDUPFLAG
                select * from ENDUSERDUPFLAG where ENDUSERID like '%'+@batchid+'%'
                DELETE FROM ENDUSERDUPFLAG where ENDUSERID like '%'+@batchid+'%'
            end
            if not exists (select 1 from ENDUSERLUNAS where ENDUSERID like '%'+@batchid+'%')
            begin
                INSERT INTO ENDUSERLUNAS
                select * from ENDUSER  where ENDUSERID like '%'+@batchid+'%'
                DELETE FROM ENDUSERFLAG where ENDUSERID like '%'+@batchid+'%'
            end
            if not exists (select 1 from ENDUSERLUNAS where ENDUSERID like '%'+@batchid+'%')
            begin
                INSERT INTO JADWALANGSURANLUNAS
                select *,1 from JADWALANGSURAN  where ENDUSERID like '%'+@batchid+'%'
            end
            if not exists (select 1 from ENDUSERLUNAS where ENDUSERID like '%'+@batchid+'%')
            begin
                INSERT INTO PAYINSTALLMENTLUNAS
                select P.* from PAYINSTALLMENT  P JOIN JADWALANGSURAN J ON J.SCHEMAID=P.JD_SCHEMAID
                WHERE J.ENDUSERID  like '%'+@batchid+'%'
                DELETE FROM PAYINSTALLMENT WHERE JD_SCHEMAID IN
                (SELECT SCHEMAID FROM JADWALANGSURAN WHERE ENDUSERID  like '%'+@batchid+'%')
                DELETE FROM JADWALANGSURAN where ENDUSERID like '%'+@batchid+'%'
                DELETE FROM ENDUSER where ENDUSERID like '%'+@batchid+'%'
            end
    END

        --end

    end

    if not exists (select 1 from batchpayment where batchid = @batchid and duedate = @duedate)
    begin
        insert into batchpayment (batchid, duedate, payamount, payflag, flagdate, flagby)
        values (@batchid, @duedate, @angs, @payflag, getdate(), @userid)
    end
    else
    begin
        update batchpayment set
            payflag = @payflag, flagdate = getdate(), flagby = @userid
        where batchid = @batchid and duedate = @duedate
    end

                         
    Jumat, 21 Juni 2013 07.55

Jawaban

  • Hi

    Selamat datang di Forum Technet

    if not exists (select 1 from ENDUSERLUNAS where ENDUSERID like '%'+@batchid+'%')
            begin
                INSERT INTO ENDUSERLUNAS
                select * from ENDUSER  where ENDUSERID like '%'+@batchid+'%'
                DELETE FROM ENDUSERFLAG where ENDUSERID like '%'+@batchid+'%'
            end 
    

    dari kode diatas, tampaknya store procedure berjalan ke sub statement. Ada banyak statement "if", jadi cara kerjanya bergantung pada data, dan kita tidak ada contoh data disini
    Jadi saran saya debug kodenya

    Terima kasih


    Andy Nugraha

    TechNet Community Support

    Selasa, 25 Juni 2013 06.02
    Moderator