· 5 years ago · Mar 16, 2020, 08:30 AM
1CREATE OR REPLACE FUNCTION r_report_daily_stock_mutation(character varying, bigint, bigint, bigint, character varying, bigint, bigint, character varying, character varying)
2 RETURNS SETOF refcursor AS
3$BODY$
4DECLARE
5 pRefHeader REFCURSOR := 'refHeader';
6 pRefDetail REFCURSOR := 'refDetail';
7 pSessionId ALIAS FOR $1;
8 pTenantId ALIAS FOR $2;
9 pUserId ALIAS FOR $3;
10 pRoleId ALIAS FOR $4;
11 pDatetime ALIAS FOR $5;
12 pOuId ALIAS FOR $6;
13 pWarehouseId ALIAS FOR $7;
14 pDate ALIAS FOR $8;
15 pShowAllProduct ALIAS FOR $9;
16
17 vEmptyId bigint;
18 vOuCodeName character varying(100);
19 vWarehouseCodeName character varying(100);
20 vOnlyWithStockMovement character varying(50);
21
22 vNol bigint;
23 vDocTypeReceiveGoods bigint;
24 vDocTypeGoodsTransferIn bigint;
25 vDocTypeDeliveryOrder bigint;
26 vDocTypeDeliveryOrderReceipt bigint;
27 vDocTypePointOfSales bigint;
28 vDocTypePointOfSalesVoid bigint;
29 vDocTypeClaimNote bigint;
30 vDocTypeReturnNote bigint;
31 vDocTypeGoodsTransferOut bigint;
32 vDocTypeAdjusmentStockQty bigint;
33 vDocTypeAdjusmentStockQtyAmount bigint;
34
35 vAdminLedgerCodeInvQty character varying(5);
36 vStatusLedgerDone character varying(5);
37 vLastYearMonthProcessed character varying(30);
38 vNextLastYearMonthProcessed character varying(30);
39 vDateMinusOneDay character varying(30);
40
41
42BEGIN
43
44 vEmptyId := -99;
45 vOuCodeName := 'ALL';
46 vWarehouseCodeName := 'ALL';
47 vOnlyWithStockMovement := 'ONLYWITHSTOCKMOVEMENT';
48 vNol := 0;
49
50 vDocTypeReceiveGoods := 111;
51 vDocTypeGoodsTransferIn := 535;
52 vDocTypeDeliveryOrder := 311;
53 vDocTypeDeliveryOrderReceipt := 526;
54 vDocTypePointOfSales := 401;
55 vDocTypePointOfSalesVoid := 405;
56 vDocTypeClaimNote := 511;
57 vDocTypeReturnNote := 502;
58 vDocTypeGoodsTransferOut := 533;
59 vDocTypeAdjusmentStockQty := 521;
60 vDocTypeAdjusmentStockQtyAmount := 522;
61
62 vAdminLedgerCodeInvQty := 'INV';
63 vStatusLedgerDone := '1';
64 vLastYearMonthProcessed := '';
65 vNextLastYearMonthProcessed := '';
66 vDateMinusOneDay := '';
67
68 -- filter ou id
69 IF(pOuId <> vEmptyId) THEN
70 SELECT ou_code||' - '||ou_name INTO vOuCodeName
71 FROM t_ou
72 WHERE ou_id = pOuId;
73 END IF;
74
75 -- filter warehouse id
76 IF(pWarehouseId <> vEmptyId) THEN
77 SELECT warehouse_code||' - '||warehouse_name INTO vWarehouseCodeName
78 FROM m_warehouse
79 WHERE warehouse_id = pWarehouseId;
80 END IF;
81
82 -- get last year month processed
83 SELECT COALESCE(MAX(date_year_month), '') INTO vLastYearMonthProcessed --201909
84 FROM m_admin_process_ledger A
85 WHERE tenant_id = pTenantId
86 AND ou_id = pOuId
87 AND ledger_code = vAdminLedgerCodeInvQty
88 AND status_ledger = vStatusLedgerDone
89 AND date_year_month < LEFT(pDate,6);
90
91 -- vLastYearMonthProcessed + 1 month
92 SELECT TO_CHAR(CONCAT(vLastYearMonthProcessed,'01')::date + INTERVAL '1 month', 'YYYYMM') INTO vNextLastYearMonthProcessed;
93
94 -- H-1 pDate
95 SELECT TO_CHAR(pDate::date - INTERVAL '1 day', 'YYYYMMDD') INTO vDateMinusOneDay; --202002
96
97
98 DELETE FROM tt_r_daily_stock_mutation WHERE session_id = pSessionId;
99
100 -- insert table temporary tt_r_daily_stock_mutation
101 IF(pShowAllProduct = vOnlyWithStockMovement) THEN
102
103 INSERT INTO tt_r_daily_stock_mutation(
104 session_id, product_id, product_code, product_name, brand, satuan,
105 saldo_awal, qty_terima_barang, qty_jual, qty_retur_beli, qty_retur_jual,
106 qty_keluar_barang, qty_adj, saldo_akhir)
107 WITH data_stock_from_processed AS (
108 SELECT A.product_id, COALESCE(SUM(A.qty), vNol) AS beginning_qty
109 FROM in_summary_monthly_qty A
110 WHERE A.tenant_id = pTenantId
111 AND A.warehouse_id = pWarehouseId
112 AND A.date_year_month = vNextLastYearMonthProcessed
113 GROUP BY A.product_id
114 ),
115 data_rekap_saldo_awal AS (
116 SELECT A.product_id, COALESCE(SUM(A.qty), vNol) AS qty
117 FROM in_log_product_balance_stock A
118 WHERE A.tenant_id = pTenantId
119 AND A.warehouse_id = pWarehouseId
120 AND SUBSTRING(A.doc_date, 1, 6) > vLastYearMonthProcessed
121 AND A.doc_date <= vDateMinusOneDay
122 GROUP BY A.product_id
123 )
124 SELECT pSessionId, A.product_id, B.product_code, B.product_name,
125 CONCAT(f_get_brand_code(B.brand_id), ' - ', f_get_brand_name(B.brand_id)) AS brand, f_get_uom_name(A.base_uom_id) AS satuan,
126 Y.beginning_qty + COALESCE(X.qty, vNol) AS saldo_awal, vNol, vNol, vNol, vNol,
127 vNol, vNol, vNol
128 FROM in_log_product_balance_stock A
129 INNER JOIN m_product B ON A.product_id = B.product_id
130 LEFT JOIN data_stock_from_processed Y ON A.product_id = Y.product_id
131 LEFT JOIN data_rekap_saldo_awal X ON A.product_id = X.product_id
132 WHERE A.tenant_id = pTenantId
133 AND A.ou_id = pOuId
134 AND A.warehouse_id = pWarehouseId
135 AND A.doc_date = pDate
136 GROUP BY A.product_id, B.product_code, B.product_name, B.brand_id, A.base_uom_id, Y.beginning_qty, X.qty
137 ORDER BY B.product_name;
138
139 ELSE
140
141 INSERT INTO tt_r_daily_stock_mutation(
142 session_id, product_id, product_code, product_name, brand, satuan,
143 saldo_awal, qty_terima_barang, qty_jual, qty_retur_beli, qty_retur_jual,
144 qty_keluar_barang, qty_adj, saldo_akhir)
145 WITH data_stock_from_processed AS (
146 SELECT A.product_id, COALESCE(SUM(A.qty), vNol) AS beginning_qty
147 FROM in_summary_monthly_qty A
148 WHERE A.tenant_id = pTenantId
149 AND A.warehouse_id = pWarehouseId
150 AND A.date_year_month = vNextLastYearMonthProcessed
151 GROUP BY A.product_id
152 ),
153 data_rekap_saldo_awal AS (
154 SELECT A.product_id, COALESCE(SUM(A.qty), vNol) AS qty
155 FROM in_log_product_balance_stock A
156 WHERE A.tenant_id = pTenantId
157 AND A.warehouse_id = pWarehouseId
158 AND SUBSTRING(A.doc_date, 1, 6) > vLastYearMonthProcessed
159 AND A.doc_date <= vDateMinusOneDay
160 GROUP BY A.product_id
161 )
162 SELECT pSessionId, A.product_id, A.product_code, A.product_name,
163 CONCAT(f_get_brand_code(A.brand_id), ' - ', f_get_brand_name(A.brand_id)) AS brand, f_get_uom_name(A.base_uom_id) AS satuan,
164 Y.beginning_qty + COALESCE(X.qty, vNol) AS saldo_awal, vNol, vNol, vNol, vNol,
165 vNol, vNol, vNol
166 FROM m_product A
167 INNER JOIN m_item_warehouse B ON A.sub_ctgr_product_id = B.sub_ctgr_product_id AND A.ctgr_product_id = B.ctgr_product_id
168 LEFT JOIN data_stock_from_processed Y ON A.product_id = Y.product_id
169 LEFT JOIN data_rekap_saldo_awal X ON A.product_id = X.product_id
170 WHERE A.tenant_id = pTenantId
171 AND B.warehouse_id = pWarehouseId
172 ORDER BY A.product_name;
173
174 END IF;
175
176 -- update qty_terima_barang table tt_r_daily_stock_mutation
177 UPDATE tt_r_daily_stock_mutation A
178 SET qty_terima_barang = A.qty_terima_barang + B.qty
179 FROM in_log_product_balance_stock B
180 WHERE A.product_id = B.product_id
181 AND B.doc_type_id IN (vDocTypeReceiveGoods, vDocTypeGoodsTransferIn)
182 AND B.doc_date = pDate
183 AND A.session_id = pSessionId;
184
185 -- update qty_jual table tt_r_daily_stock_mutation
186 UPDATE tt_r_daily_stock_mutation A
187 SET qty_jual = A.qty_jual + B.qty
188 FROM in_log_product_balance_stock B
189 WHERE A.product_id = B.product_id
190 AND B.doc_type_id IN (vDocTypeDeliveryOrder, vDocTypeDeliveryOrderReceipt, vDocTypePointOfSales, vDocTypePointOfSalesVoid)
191 AND B.doc_date = pDate
192 AND A.session_id = pSessionId;
193
194 -- update qty_retur_beli table tt_r_daily_stock_mutation
195 UPDATE tt_r_daily_stock_mutation A
196 SET qty_retur_beli = A.qty_retur_beli + B.qty
197 FROM in_log_product_balance_stock B
198 WHERE A.product_id = B.product_id
199 AND B.doc_type_id = vDocTypeClaimNote
200 AND B.doc_date = pDate
201 AND A.session_id = pSessionId;
202
203 -- update qty_retur_jual table tt_r_daily_stock_mutation
204 UPDATE tt_r_daily_stock_mutation A
205 SET qty_retur_jual = A.qty_retur_jual + B.qty
206 FROM in_log_product_balance_stock B
207 WHERE A.product_id = B.product_id
208 AND B.doc_type_id = vDocTypeReturnNote
209 AND B.doc_date = pDate
210 AND A.session_id = pSessionId;
211
212 -- update qty_keluar_barang table tt_r_daily_stock_mutation
213 UPDATE tt_r_daily_stock_mutation A
214 SET qty_keluar_barang = A.qty_keluar_barang + B.qty
215 FROM in_log_product_balance_stock B
216 WHERE A.product_id = B.product_id
217 AND B.doc_type_id = vDocTypeGoodsTransferOut
218 AND B.doc_date = pDate
219 AND A.session_id = pSessionId;
220
221 -- update qty_adj table tt_r_daily_stock_mutation
222 UPDATE tt_r_daily_stock_mutation A
223 SET qty_adj = A.qty_adj + B.qty
224 FROM in_log_product_balance_stock B
225 WHERE A.product_id = B.product_id
226 AND B.doc_type_id IN (vDocTypeAdjusmentStockQty, vDocTypeAdjusmentStockQtyAmount)
227 AND B.doc_date = pDate
228 AND A.session_id = pSessionId;
229
230 -- update saldo_akhir table tt_r_daily_stock_mutation
231 UPDATE tt_r_daily_stock_mutation
232 SET saldo_akhir = saldo_awal + qty_terima_barang + qty_jual + qty_retur_beli + qty_retur_jual + qty_keluar_barang + qty_adj
233 WHERE session_id = pSessionId;
234
235 IF(pShowAllProduct != vOnlyWithStockMovement) THEN
236 DELETE FROM tt_r_daily_stock_mutation A
237 WHERE A.session_id = pSessionId AND A.saldo_awal = 0
238 AND NOT EXISTS (
239 SELECT 1
240 FROM in_log_product_balance_stock B
241 WHERE A.product_id = B.product_id
242 );
243 END IF;
244
245 OPEN pRefHeader FOR
246 SELECT CASE WHEN pOuId = vEmptyId THEN -99 ELSE pOuId END AS ou_id, vOuCodeName AS ou_code_name,
247 CASE WHEN pWarehouseId = vEmptyId THEN -99 ELSE pWarehouseId END AS warehouse_id, vWarehouseCodeName AS warehouse_code_name,
248 pDatetime AS datetime, f_get_username(pUserId) AS username, pDate AS date;
249 RETURN NEXT pRefHeader;
250
251 OPEN pRefDetail FOR
252 SELECT product_code, product_name, brand, satuan,
253 saldo_awal, qty_terima_barang, qty_jual, qty_retur_beli, qty_retur_jual,
254 qty_keluar_barang, qty_adj, saldo_akhir
255 FROM tt_r_daily_stock_mutation
256 ORDER BY product_name;
257 RETURN NEXT pRefDetail;
258
259 DELETE FROM tt_r_daily_stock_mutation WHERE session_id = pSessionId;
260END;
261$BODY$
262 LANGUAGE plpgsql VOLATILE
263 COST 100;