SHARE
TWEET

InvMovement Monthly per 11 April

MoemoeChan Apr 8th, 2020 (edited) 391 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. USE [RENO-AHa]
  2. GO
  3. /****** Object:  UserDefinedFunction [dbo].[FN_INVENTORY_MOVEMENT_ADIM]    Script Date: 13/04/2020 12.35.40 ******/
  4. SET ANSI_NULLS ON
  5. GO
  6. SET QUOTED_IDENTIFIER ON
  7. GO
  8. /*                                             */
  9. ALTER FUNCTION [dbo].[FN_INVENTORY_MOVEMENT_ADIM]
  10. (
  11. /** Part I **/ -- Parameter & Return Table
  12. @datestart Date = '2020/1/1',
  13. @dateend   Date = '2020/1/31',
  14. @Filter SmallInt = 0
  15. )
  16. -- Filter Tipe Barang:
  17. -- 0: All, 1: Barang Sendiri, 2: Kontra Bon, 3: Pastra, 4: Barang Sendiri pakai SubGroup
  18.  
  19. RETURNS @RESULT TABLE (
  20.   mgc            varchar(4),
  21.   mgn            varchar(64),
  22.   sgc            varchar(9),
  23.   sgn            varchar(128),
  24.   beginning      Numeric(18,2) Default 0,
  25.   receiving      Numeric(18,2) Default 0,
  26.   deposit_return Numeric(18,2) Default 0,
  27.   sales          Numeric(18,2) Default 0,
  28.   cogs           Numeric(18,2) Default 0,
  29.   wastage_real   Numeric(18,2) Default 0,
  30.   wastage_obral  Numeric(18,2) Default 0,
  31.   adjustment     Numeric(18,2) Default 0,
  32.   ending_balance Numeric(18,2) Default 0
  33. )
  34. /** Part I End **/
  35.  
  36. /** Part II **/ -- Deklarasi Variabel termasuk @Temp Table
  37. AS
  38.   BEGIN
  39.     DECLARE
  40.       @CutOff              Date     = '2020/1/1', --Nantinya dapat diubah
  41.       @TBStart             uniqueidentifier,   -- Filter Tipe Barang Awal
  42.       @TBEnd               uniqueidentifier,   -- Filter Tipe Barang Akhir
  43.       @Flag_DO             SmallInt = 100, -- Receiving
  44.       @Flag_Deposit_Return SmallInt = 220, -- Deposit Return
  45.       @Flag_Sales          SmallInt = 210, -- SALES
  46.       @Flag_COGS           SmallInt = 310, -- COGS
  47.       @Flag_CN             SmallInt = 110, -- Credit Note
  48.       @Flag_WR             SmallInt = 410, -- Wastage Real
  49.       @Flag_WO             SmallInt = 420, -- Wastage Obral
  50.       @Flag_Adjustment     SmallInt = 500; -- Adjustment
  51. /*
  52.     DECLARE @TEMP TABLE (
  53.       barang_id            uniqueidentifier,
  54.       beginning            Numeric(18,2) Default 0,
  55.       receiving            Numeric(18,2) Default 0,
  56.       deposit_return       Numeric(18,2) Default 0,
  57.       sales                Numeric(18,2) Default 0,
  58.       cogs                 Numeric(18,2) Default 0,
  59.       cn                   Numeric(18,2) Default 0,
  60.       wr                   Numeric(18,2) Default 0,
  61.       wo                   Numeric(18,2) Default 0,
  62.       adjustment           Numeric(18,2) Default 0,
  63.       ending               Numeric(18,2) Default 0
  64.     );*/
  65.  
  66.     declare @temp table (
  67.       transaksi  varchar(100),  
  68.       barang_id  uniqueidentifier,
  69.       qtyin      numeric(18,2) default 0,
  70.       qtyout     numeric(18,2) default 0,    
  71.       konversi   numeric(18,2) not null,
  72.       tgl        date
  73.     );
  74.  
  75.     declare @temp2 table (
  76.       barang_id    uniqueidentifier,
  77.       saldoawal    numeric(18,2) default 0,  --
  78.       do           numeric(18,2) default 0,
  79.       do_bonus     numeric(18,2) default 0,
  80.       cn_receiving numeric(18,2) default 0,--
  81.       dn_receiving numeric(18,2) default 0,--
  82.       trader       numeric(18,2) default 0, --
  83.       returtrader  numeric(18,2) default 0, --
  84.       pos          numeric(18,2) default 0,--
  85.       returpos     numeric(18,2) default 0,--
  86.       wastage      numeric(18,2) default 0, --
  87.       obral        numeric(18,2) default 0, --
  88.       adjustment   numeric(18,2) default 0,
  89.       saldoakhir   numeric(18,2) default 0   --
  90.     );
  91. /** Part II End **/
  92.  
  93. /** Part III **/ -- Mengatur Query Sesuai Filter
  94.     IF @Filter = 0 BEGIN -- Semua Tipe Barang
  95.       SELECT TOP 1 @TBStart = REF$TIPE_BARANG_ID FROM REF$TIPE_BARANG ORDER BY REF$TIPE_BARANG_ID;
  96.       SELECT TOP 1 @TBEnd   = REF$TIPE_BARANG_ID FROM REF$TIPE_BARANG ORDER BY REF$TIPE_BARANG_ID DESC;
  97.     END ELSE
  98.     IF @Filter = 1 OR @Filter = 4 BEGIN
  99.       SELECT @TBStart = REF$TIPE_BARANG_ID FROM REF$TIPE_BARANG  WHERE TPBRG_NAME = 'Barang Sendiri';
  100.       SELECT @TBEnd = @TBStart;
  101.     END ELSE
  102.     IF @Filter = 2 BEGIN
  103.       SELECT @TBStart = REF$TIPE_BARANG_ID  FROM REF$TIPE_BARANG WHERE TPBRG_NAME = 'Kontra Bon';
  104.       SELECT @TBEnd = @TBStart;
  105.     END ELSE
  106.     IF @Filter = 3 BEGIN
  107.       SELECT @TBStart = REF$TIPE_BARANG_ID  FROM REF$TIPE_BARANG WHERE TPBRG_NAME = 'Pasar Tradisional';
  108.       SELECT @TBEnd = @TBStart;
  109.     END
  110. /** Part III End **/
  111.  
  112. /** Part IV **/ -- Query Insert ke @Temp
  113.  
  114. --Isi @Temp dengan query berikut
  115.  
  116.     insert into @temp(transaksi, barang_id, qtyin, konversi, tgl)
  117.     select 'do', b.barang_id, b.dod_qty_order_recv, c.konvsat_scale, cast(a.do_date as date)
  118.     from do a
  119.          inner join do_detail b on a.do_id=b.do_id
  120.          inner join ref$konversi_satuan c on c.barang_id=b.barang_id and c.ref$satuan_id=b.ref$satuan_id
  121.     where cast(a.do_date as date) between @datestart and @dateend;
  122.  
  123.     insert into @temp(transaksi, barang_id, qtyin, konversi, tgl)
  124.     select 'do bonus', b.dobi_barang_id, b.dobi_qty, c.konvsat_scale, cast(a.dob_date as date)
  125.     from dobonus a
  126.          inner join dobonusitem b on a.dobonus_id = b.dobi_dobonus_id
  127.          inner join ref$konversi_satuan c on c.barang_id=b.dobi_barang_id and c.ref$satuan_id=b.dobi_uom_id
  128.     where cast(a.dob_date as date) between @datestart and @dateend;
  129.  
  130.   --
  131.     insert into @temp(transaksi, barang_id, qtyout, konversi, tgl)
  132.     select 'cn', b.barang_id, b.cnrd_qty, c.konvsat_scale, cast(a.cnr_date as date)
  133.     from cn_recv a
  134.          inner join cn_recv_detil b on a.cn_recv_id=b.cn_recv_id
  135.          inner join ref$konversi_satuan c on c.barang_id=b.barang_id and c.ref$satuan_id=b.ref$satuan_id
  136.       where cast(a.cnr_date as date) between @datestart and @dateend;
  137.  
  138.     insert into @temp(transaksi, barang_id, qtyin, konversi, tgl)
  139.     select 'dn', b.barang_id, b.dnrd_qty, c.konvsat_scale, cast(a.dnr_date as date)
  140.     from dn_recv a
  141.          inner join dn_recv_detil b on a.dn_recv_id=b.dn_recv_id
  142.          inner join ref$konversi_satuan c on c.barang_id=b.barang_id and c.ref$satuan_id=b.ref$satuan_id
  143.     where cast(a.dnr_date as date) between @datestart and @dateend;
  144.  
  145.   --
  146.     insert into @temp(transaksi, barang_id, qtyout, konversi, tgl)
  147.     select 'dot', b.dotitem_barang_id, b.dotitem_qty, c.konvsat_scale, a.dot_date
  148.     from dotrader a
  149.          inner join dotraderitem b on a.dotrader_id = b.dotitem_dotrader_id
  150.          inner join ref$konversi_satuan c on c.barang_id = b.dotitem_barang_id and c.ref$satuan_id = b.dotitem_satuan_id
  151.     where cast(a.dot_date as date) between @datestart and @dateend;
  152.  
  153.     insert into @temp(transaksi, barang_id, qtyin, konversi, tgl)
  154.     select 'ret', b.retitem_barang_id, b.retitem_qty, c.konvsat_scale, cast(a.ret_date as date)
  155.     from returtrader a
  156.         inner join returtraderitem b on a.returtrader_id = b.retitem_returtrader_id
  157.         inner join ref$konversi_satuan c on c.barang_id = b.retitem_barang_id and c.ref$satuan_id = b.retitem_satuan_id
  158.       where cast(a.ret_date as date) between @datestart and @dateend;
  159.  
  160.   --
  161.     insert into @temp(transaksi, barang_id, qtyout, konversi, tgl)
  162.     select 'wastage', b.igrarwd_barang_id, b.igrarwd_qty_igra, c.konvsat_scale, cast(a.igrarw_date as date)
  163.     from igrareal_wastage a
  164.          inner join igrareal_wastage_item b on a.igrareal_wastage_id = b.igrarwd_igrarw_id
  165.          inner join ref$konversi_satuan c on c.barang_id = b.igrarwd_barang_id and c.ref$satuan_id = b.igrarwd_uom_id
  166.       where cast(a.igrarw_date as date) between @datestart and @dateend;
  167.  
  168.     insert into @temp(transaksi, barang_id, qtyout, konversi, tgl)
  169.     select 'obral', b.igraobd_barang_id, b.igraobd_qty_igra, c.konvsat_scale, cast(a.igraob_date as date)
  170.     from igraobral a
  171.          inner join igraobral_item b on a.igraobral_id = b.igraobd_igraob_id
  172.          inner join ref$konversi_satuan c on c.barang_id = b.igraobd_barang_id and c.ref$satuan_id = b.igraobd_uom_id
  173.       where cast(a.igraob_date as date) between @datestart and @dateend;
  174.  
  175.   --  
  176.     insert into @temp(transaksi, barang_id, qtyout, konversi, tgl)
  177.     select 'pos', c.barang_id, b.transd_qty, d.konvsat_scale, a.trans_is_pending, cast(a.trans_date as date)
  178.     from transaksi a with (nolock)
  179.          inner join transaksi_detil b  with (nolock) on b.transaksi_id = a.transaksi_id
  180.          inner join barang c on b.transd_barang_id = c.barang_id
  181.          Inner join [ref$konversi_satuan] d on d.Barang_id = c.Barang_id and d.[ref$satuan_id] = b.Transd_satuan_id
  182.     where cast(a.trans_date as date) between @datestart and @dateend
  183.           and (a.trans_is_pending is null or a.trans_is_pending = 0);
  184.  
  185.     insert into @temp(transaksi, barang_id, qtyout, konversi, tgl)
  186.     select 'ret_transaksi', b.transrd_barang_id, b.transrd_qty, c.konvsat_scale, cast(a.transr_date as date)
  187.     from returtransaksi a
  188.          inner join returtransaksidetil b with (nolock) on a.returtransaksi_id = b.transrd_returtransaksi_id
  189.          inner join ref$konversi_satuan c on b.transrd_barang_id = c.barang_id and b.transrd_satuan_id = c.ref$satuan_id
  190.     where cast(a.transr_date as date) between @datestart and @dateend;
  191.  
  192.     insert into @temp(transaksi, barang_id, konversi, tgl, qtyin, qtyout)
  193.     select 'adjustment product', b.adjd_barang_id, c.konvsat_scale, cast(a.adj_date as date),
  194.            case when b.adjd_qty > 0 then b.adjd_qty else 0 end,
  195.            case when b.adjd_qty < 0 then -b.adjd_qty else 0 end
  196.     from adjustmentproduct a
  197.          inner join adjustmentproductitem b on a.adjustmentproduct_id = b.adjd_adjustmentproduct_id
  198.          inner join ref$konversi_satuan c on c.barang_id = b.adjd_barang_id and c.ref$satuan_id = b.adjd_uom_id
  199.       where cast(a.adj_date as date) between @datestart and @dateend;
  200.  
  201.  
  202.  
  203. --Isi @Temp2 dengan query berikut
  204.  
  205.     insert into @temp2(barang_id, saldoawal)
  206.     select barang_id, sum((qtyin - qtyout) * konversi) from @temp
  207.     where tgl between @cutoff and dateadd(day, -1, @datestart) group by barang_id
  208.  
  209.     insert into @temp2(barang_id, do)
  210.     select barang_id, sum((qtyin - qtyout) * konversi) from @temp where tgl between @datestart and @dateend
  211.     and    transaksi = 'do' group by barang_id
  212.  
  213.     insert into @temp2(barang_id, do_bonus)
  214.     select barang_id, sum((qtyin - qtyout) * konversi) from @temp where tgl between @datestart and @dateend
  215.     and    transaksi = 'do bonus' group by barang_id
  216.  
  217.     insert into @temp2(barang_id, cn_receiving)
  218.     select barang_id, sum((qtyin - qtyout) * konversi) from @temp where tgl between @datestart and @dateend
  219.     and    transaksi = 'cn' group by barang_id
  220.  
  221.     insert into @temp2(barang_id, dn_receiving)
  222.     select barang_id, sum((qtyin - qtyout) * konversi) from @temp where tgl between @datestart and @dateend
  223.     and    transaksi = 'dn' group by barang_id
  224.  
  225.     insert into @temp2(barang_id, trader)
  226.     select barang_id, sum((qtyin - qtyout) * konversi) from @temp where tgl between @datestart and @dateend
  227.     and    transaksi = 'dot' group by barang_id
  228.  
  229.     insert into @temp2(barang_id, returtrader)
  230.     select barang_id, sum((qtyin - qtyout) * konversi) from @temp where tgl between @datestart and @dateend
  231.     and    transaksi = 'ret' group by barang_id
  232.  
  233.     insert into @temp2(barang_id, pos)
  234.     select barang_id, sum((qtyin - qtyout) * konversi) from @temp where tgl between @datestart and @dateend
  235.     and    transaksi = 'pos' group by barang_id
  236.  
  237.     insert into @temp2(barang_id, returpos)
  238.     select barang_id, sum((qtyin - qtyout) * konversi) from @temp where tgl between @datestart and @dateend
  239.     and    transaksi = 'ret_transaklsi' group by barang_id
  240.  
  241.     insert into @temp2(barang_id, wastage)
  242.     select barang_id, sum((qtyin - qtyout) * konversi) from @temp where tgl between @datestart and @dateend
  243.     and    transaksi = 'wastage' group by barang_id
  244.  
  245.     insert into @temp2(barang_id, obral)
  246.     select barang_id, sum((qtyin - qtyout) * konversi) from @temp where tgl between @datestart and @dateend
  247.     and    transaksi = 'obral' group by barang_id
  248.  
  249.     insert into @temp2(barang_id, adjustment)
  250.     select barang_id, sum((qtyin - qtyout) * konversi) from @temp where tgl between @datestart and @dateend
  251.     and    transaksi = 'adjustment product' group by barang_id
  252.  
  253.     insert into @temp2(barang_id, saldoakhir)
  254.     select barang_id, sum((qtyin - qtyout) * konversi) from @temp
  255.     where tgl between @cutoff and @dateend group by barang_id
  256.  
  257. /** Part IV End **/
  258.  
  259. /** Part V **/ -- Return Table
  260.     INSERT INTO @RESULT (mgc, mgn, sgc, sgn,
  261.                 beginning, receiving,deposit_return, sales, cogs,
  262.                 wastage_real, wastage_obral, adjustment, ending_balance)
  263.  
  264.     SELECT m.MERCHANGRUP_CODE gc,m.MERCHANGRUP_NAME gn,
  265.            g.SUBGRUP_CODE sgc, g.SUBGRUP_NAME sgn,
  266.  
  267.            sum(tq.saldoawal) beginning,
  268.            sum(tq.do) receiving,
  269.            sum(tq.pos) deposit_return,
  270.            sum(tq.returpos) cogs,
  271.            sum(tq.cn_receiving) cn,
  272.            sum(tq.wastage) wr,
  273.            sum(tq.obral) wo,
  274.            sum(tq.adjustment) adjustment,
  275.            sum(tq.saldoakhir) ending
  276.  
  277.     FROM   @Temp2 tq
  278.            INNER JOIN barang b
  279.                    on b.BARANG_ID = tq.barang_id
  280.            INNER JOIN ref$kategori k
  281.                    on b.ref$kategori_id = k.ref$kategori_id
  282.            INNER JOIN ref$sub_grup g
  283.                    on k.ref$sub_grup_id = g.ref$sub_grup_id
  284.            INNER JOIN ref$merchandise_grup m
  285.                    on b.ref$merchandise_grup_id = m.ref$merchandise_grup_id
  286.  
  287.     GROUP BY g.SUBGRUP_CODE, g.SUBGRUP_NAME, m.MERCHANGRUP_CODE, m.MERCHANGRUP_NAME
  288. /** Part V End **/
  289.  
  290.     RETURN
  291.   END
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Top