• [ SAP ] Cara Membuat Kartu Stok ( Stock Card ) SAP B 1 v 9.2

    Setelah saya searching dan tak kunjung mendapat jawaban akhirnya terjawab sudah cara membuat pengurangan antar row sql.
    ini di implementasikan di dalam kartu stok.

    logikanya : sum stok masuk sebelum variabel @dateFrom - sum stok keluar sebeleum variabel @dateFrom + transaksi kluar masuk di dalam variabel @dateFrom s/d @dateTo

    perhitungan antar kolom menggunakan fungsi PARTITION OVER
    terdiri dari tabel transaksi barang yaitu :
    1. OIVL.

    dan sisanya ada di dalam source code my sql server di bawah ini.

    Variabel yang di butuhkan :


            @DateFrom date,
    @DateTo Date,
    @Branch varchar(50),
    @Item varchar(50)
    AS
    BEGIN

    SET NOCOUNT ON;

    SELECT A.DocDate, A.BASE_REF, A.ItemCode, a.InQty, a.OutQty, A.OpenQty, A.LocCode, B1.SeriesName AS CreditMemo, C1.SeriesName as Delivery, D1.SeriesName as Returnn, E1.SeriesName as TransferIn, f1.SeriesName as GRPO, G1.SeriesName AS GoodsIssue, H1.SeriesName AS GoodsRecipt, I1.SeriesName AS ARInvoice, K1.SeriesName AS APInvo,

    (select ISNULL(sum(A.inqty), 0)  from oivl A where A.DocDate < @DateFrom and A.LocCode = @Branch and A.ItemCode = @Item) - (select ISNULL(sum(A.OutQty), 0) from oivl A where A.DocDate < @DateFrom and A.LocCode = @Branch and A.ItemCode = @Item) as 'Saldo Awal Keluar', 
    (select ISNULL(sum(A.inqty), 0)  from oivl A where A.DocDate < @DateFrom and A.LocCode = @Branch and A.ItemCode = @Item) -(select ISNULL(sum(A.OutQty), 0) from oivl A where A.DocDate < @DateFrom and A.LocCode = @Branch and A.ItemCode = @Item) + (sum(a.InQty - a.OutQty)
               OVER (PARTITION BY a.ITEMCODE ORDER BY a.docdate,a.BASE_REF)) As Balance, (select ISNULL(sum(A.inqty), 0)  from oivl A where A.DocDate < @DateFrom and A.LocCode = @Branch and A.ItemCode = @Item) as 'Saldo Awal Masuk'
    FROM OIVL A

    -- Credit Memo Sales
    LEFT JOIN ORIN B ON A.BASE_REF = B.DocNum AND A.TransType = 14
    LEFT JOIN NNM1 B1 ON B.Series = B1.Series
    -- Delivery
    LEFT JOIN ODLN C ON A.BASE_REF = C.DocNum AND A.TransType = 15
    LEFT JOIN NNM1 C1 ON C.Series = C1.Series
    -- Return
    LEFT JOIN ORDN D ON A.BASE_REF = D.DocNum AND A.TransType = 16
    LEFT JOIN NNM1 D1 ON D.Series = D1.Series
    -- Transfer In
    LEFT JOIN OWTR E ON A.BASE_REF = E.DocNum AND A.TransType = 67
    LEFT JOIN NNM1 E1 ON E.Series = E1.Series
    -- Good Receipt PO
    LEFT JOIN OPDN F ON A.BASE_REF = f.DocNum AND A.TransType = 20
    LEFT JOIN NNM1 F1 ON F.Series = F1.Series
    -- Goods Issue
    LEFT JOIN Oige G ON A.BASE_REF = g.DocNum AND A.TransType = 60
    LEFT JOIN NNM1 g1 ON g.Series = G1.Series
    -- Good Receipt
    LEFT JOIN Oign H ON A.BASE_REF = h.DocNum AND A.TransType = 59
    LEFT JOIN NNM1 H1 ON H.Series = H1.Series
    -- AR Invoice
    LEFT JOIN Oinv i ON A.BASE_REF = I.DocNum AND A.TransType = 13
    LEFT JOIN NNM1 i1 ON i.Series = i1.Series
    -- Goods Return
    LEFT JOIN Orpd J ON A.BASE_REF = J.DocNum AND A.TransType = 21
    LEFT JOIN NNM1 J1 ON J.Series = J1.Series
    -- AP Invoice
    LEFT JOIN Opch k ON A.BASE_REF = k.DocNum AND A.TransType = 18
    LEFT JOIN NNM1 k1 ON k.Series = K1.Series
    -- AP Invoice
    LEFT JOIN Orpc l ON A.BASE_REF = l.DocNum AND A.TransType = 19
    LEFT JOIN NNM1 l1 ON l.Series = l1.Series
    where A.LocCode = @Branch and A.DocDate between @DateFrom and @DateTo and A.ItemCode = @Item
    group by A.DocDate,A.BASE_REF,A.ItemCode,A.InQty,A.OutQty,A.OpenQty,A.LocCode,B1.SeriesName,C1.SeriesName,D1.SeriesName,E1.SeriesName,
    f1.SeriesName, G1.SeriesName, H1.SeriesName,
    I1.SeriesName,K1.SeriesName
    END

    dan berikut adalah design crystal reportnya dapat di download disini :
    Kartu Stok
    Sekian semoga bermanfaat.



  • 0 comments:

    Posting Komentar