· 5 years ago · Oct 24, 2020, 06:36 AM
1CREATE OR REPLACE FUNCTION r_report_daily_stock_mutation_all_ou_gudang(character varying, bigint, bigint, bigint, character varying, bigint, bigint, character varying, 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 pPeriodFrom ALIAS FOR $8;
15 pPeriodTo ALIAS FOR $9;
16 pShowAllProduct ALIAS FOR $10;
17
18 vEmptyId bigint;
19 vOuCodeName character varying(100);
20 vWarehouseCodeName character varying(100);
21 vOnlyWithStockMovement character varying(50);
22
23 vNol bigint;
24 vDocTypeReceiveGoods bigint;
25 vDocTypeGoodsTransferIn bigint;
26 vDocTypeDeliveryOrder bigint;
27 vDocTypeDeliveryOrderReceipt bigint;
28 vDocTypePointOfSales bigint;
29 vDocTypePointOfSalesVoid bigint;
30 vDocTypeClaimNote bigint;
31 vDocTypeReturnNote bigint;
32 vDocTypeGoodsTransferOut bigint;
33 vDocTypeAdjusmentStockQty bigint;
34 vDocTypeAdjusmentStockQtyAmount bigint;
35
36 vDocTypeSaldoAwal bigint;
37 vOuBuId bigint;
38
39 vAdminLedgerCodeInvQty character varying(5);
40 vStatusLedgerDone character varying(5);
41 vLastYearMonthProcessed character varying(30);
42 vLastYearMonthProcessedForNilaiSatuan character varying(30);
43 vNextLastYearMonthProcessed character varying(30);
44 vDateMinusOneDay character varying(30);
45 vAdminLedgerCodeCost character varying(30);
46
47 vStartTrxDate character varying;
48 vMaxDateYearMonthCogs character varying;
49
50
51BEGIN
52
53 vEmptyId := -99;
54 vOuCodeName := 'ALL';
55 vWarehouseCodeName := 'ALL';
56 vOnlyWithStockMovement := 'ONLYWITHSTOCKMOVEMENT';
57 vNol := 0;
58
59 vDocTypeReceiveGoods := 111;
60 vDocTypeGoodsTransferIn := 535;
61 vDocTypeDeliveryOrder := 311;
62 vDocTypeDeliveryOrderReceipt := 526;
63 vDocTypePointOfSales := 401;
64 vDocTypePointOfSalesVoid := 405;
65 vDocTypeClaimNote := 511;
66 vDocTypeReturnNote := 502;
67 vDocTypeGoodsTransferOut := 533;
68 vDocTypeAdjusmentStockQty := 521;
69 vDocTypeAdjusmentStockQtyAmount := 522;
70
71 vDocTypeSaldoAwal := -99;
72
73 vAdminLedgerCodeInvQty := 'INV';
74 vAdminLedgerCodeCost := 'COST.FG';
75 vStatusLedgerDone := '1';
76 vLastYearMonthProcessed := '';
77 vLastYearMonthProcessedForNilaiSatuan := '';
78 vNextLastYearMonthProcessed := '';
79 vDateMinusOneDay := '';
80 vMaxDateYearMonthCogs := '';
81
82
83 SELECT MAX(date_year_month) FROM in_summary_monthly_cogs WHERE date_year_month <= LEFT(pPeriodTo,6) INTO vMaxDateYearMonthCogs;
84
85 SELECT f_get_value_system_config_by_param_code(pTenantId, 'start.trx.date') INTO vStartTrxDate;
86
87 -- get last year month processed
88 SELECT COALESCE(MAX(date_year_month), '') INTO vLastYearMonthProcessed
89 FROM m_admin_process_ledger A, m_ou_structure B
90 WHERE A.tenant_id = pTenantId
91 AND B.ou_id = pOuId
92 AND B.ou_bu_id = A.ou_id
93 AND A.ledger_code = vAdminLedgerCodeInvQty
94 AND A.status_ledger = vStatusLedgerDone
95 AND A.date_year_month < LEFT(pPeriodFrom,6);
96
97 SELECT COALESCE(MAX(date_year_month), '') INTO vLastYearMonthProcessedForNilaiSatuan
98 FROM m_admin_process_ledger A, m_ou_structure B
99 WHERE A.tenant_id = pTenantId
100 AND B.ou_id = pOuId
101 AND B.ou_bu_id = A.ou_id
102 AND A.ledger_code = vAdminLedgerCodeCost
103 AND A.status_ledger = vStatusLedgerDone
104 AND A.date_year_month <= LEFT(pPeriodFrom,6);
105
106 IF (vLastYearMonthProcessed = '') THEN
107 vNextLastYearMonthProcessed = vStartTrxDate;
108 ELSE
109 -- vLastYearMonthProcessed + 1 month
110 SELECT TO_CHAR(CONCAT(vLastYearMonthProcessed,'01')::date + INTERVAL '1 month', 'YYYYMM') INTO vNextLastYearMonthProcessed;
111 END IF;
112
113 raise notice '%',pPeriodFrom;
114 raise notice '%',vLastYearMonthProcessed;
115
116 -- H-1 pPeriodFrom
117 SELECT TO_CHAR(pPeriodFrom::date - INTERVAL '1 day', 'YYYYMMDD') INTO vDateMinusOneDay;
118
119
120
121 DELETE FROM tt_r_daily_stock_mutation WHERE session_id = pSessionId;
122
123
124 -- insert saldo_awal into table temp tt_r_daily_stock_mutation from in_summary_monthly_qty
125 INSERT INTO tt_r_daily_stock_mutation(
126 session_id, product_id, product_code, product_name,
127 brand, satuan, saldo_awal)
128 SELECT pSessionId, A.product_id, f_get_product_code(A.product_id) AS product_code, f_get_product_name(A.product_id) AS product_name,
129 '' AS brand, f_get_uom_name(A.base_uom_id) AS satuan, COALESCE(SUM(A.qty), 0) AS saldo_awal
130 FROM in_summary_monthly_qty A
131 INNER JOIN m_warehouse_ou B ON A.warehouse_id = B.warehouse_id
132 INNER JOIN m_ou_structure C ON B.ou_id = C.ou_id
133 WHERE A.tenant_id = pTenantId
134 AND C.ou_bu_id = pOuId
135 AND A.date_year_month = vNextLastYearMonthProcessed
136 AND A.doc_type_id = vDocTypeSaldoAwal
137 GROUP BY A.product_id, A.base_uom_id;
138
139 -- insert saldo_awal into table temp tt_r_daily_stock_mutation from in_log_product_balance_stock
140 INSERT INTO tt_r_daily_stock_mutation(
141 session_id, product_id, product_code, product_name,
142 brand, satuan, saldo_awal)
143 SELECT pSessionId, A.product_id, f_get_product_code(A.product_id) AS product_code, f_get_product_name(A.product_id) AS product_name,
144 '' AS brand, f_get_uom_name(A.base_uom_id) AS satuan, COALESCE(SUM(A.qty), 0) AS saldo_awal
145 FROM in_log_product_balance_stock A
146 INNER JOIN m_warehouse_ou B ON A.warehouse_id = B.warehouse_id
147 INNER JOIN m_ou_structure C ON B.ou_id = C.ou_id
148 WHERE A.tenant_id = pTenantId
149 AND C.ou_bu_id = pOuId
150 AND SUBSTRING(A.doc_date, 1, 6) > vLastYearMonthProcessed
151 AND A.doc_date <= vDateMinusOneDay
152 GROUP BY A.product_id, A.base_uom_id;
153
154 --insert harga pokok penjualan
155 INSERT INTO tt_r_daily_stock_mutation(
156 session_id, product_id, product_code, product_name,
157 brand, satuan, nilai_satuan)
158 SELECT DISTINCT pSessionId, A.product_id, B.product_code, B.product_name,
159 '' AS brand, f_get_uom_name(B.base_uom_id) AS satuan, COALESCE(A.avg_price, 0) AS nilai_satuan
160 FROM in_summary_monthly_cogs A
161 INNER JOIN m_product B ON A.product_id = B.product_id
162 WHERE A.tenant_id = pTenantId
163 AND A.ou_id = pOuId
164 AND A.date_year_month = vMaxDateYearMonthCogs;
165
166 -- insert qty_terima_barang supplier table tt_r_daily_stock_mutation
167 INSERT INTO tt_r_daily_stock_mutation(
168 session_id, product_id, product_code, product_name,
169 brand, satuan, qty_terima_barang)
170 SELECT pSessionId, A.product_id, f_get_product_code(A.product_id) AS product_code, f_get_product_name(A.product_id) AS product_name,
171 '' AS brand, f_get_uom_name(A.base_uom_id) AS satuan, SUM(A.qty) AS qty_terima_barang
172 FROM in_log_product_balance_stock A
173 INNER JOIN m_warehouse_ou B ON A.warehouse_id = B.warehouse_id
174 INNER JOIN m_ou_structure C ON B.ou_id = C.ou_id
175 WHERE A.tenant_id = pTenantId
176 AND C.ou_bu_id = pOuId
177 AND A.doc_type_id IN (vDocTypeReceiveGoods)
178 AND A.doc_date BETWEEN pPeriodFrom AND pPeriodTo
179 GROUP BY A.product_id, A.base_uom_id;
180
181 -- insert qty_terima_barang antar gudang table tt_r_daily_stock_mutation
182 INSERT INTO tt_r_daily_stock_mutation(
183 session_id, product_id, product_code, product_name,
184 brand, satuan, qty_terima_barang_antar_gudang)
185 SELECT pSessionId, A.product_id, f_get_product_code(A.product_id) AS product_code, f_get_product_name(A.product_id) AS product_name,
186 '' AS brand, f_get_uom_name(A.base_uom_id) AS satuan, SUM(A.qty) AS qty_terima_barang_antar_gudang
187 FROM in_log_product_balance_stock A
188 INNER JOIN m_warehouse_ou B ON A.warehouse_id = B.warehouse_id
189 INNER JOIN m_ou_structure C ON B.ou_id = C.ou_id
190 WHERE A.tenant_id = pTenantId
191 AND C.ou_bu_id = pOuId
192 AND A.doc_type_id IN (vDocTypeGoodsTransferIn)
193 AND A.doc_date BETWEEN pPeriodFrom AND pPeriodTo
194 GROUP BY A.product_id, A.base_uom_id;
195
196 -- insert qty_jual table tt_r_daily_stock_mutation
197 INSERT INTO tt_r_daily_stock_mutation(
198 session_id, product_id, product_code, product_name,
199 brand, satuan, qty_jual)
200 SELECT pSessionId, A.product_id, f_get_product_code(A.product_id) AS product_code, f_get_product_name(A.product_id) AS product_name,
201 '' AS brand, f_get_uom_name(A.base_uom_id) AS satuan, SUM(A.qty) AS qty_jual
202 FROM in_log_product_balance_stock A
203 INNER JOIN m_warehouse_ou B ON A.warehouse_id = B.warehouse_id
204 INNER JOIN m_ou_structure C ON B.ou_id = C.ou_id
205 WHERE A.tenant_id = pTenantId
206 AND C.ou_bu_id = pOuId
207 AND A.doc_type_id IN (vDocTypeDeliveryOrder, vDocTypeDeliveryOrderReceipt, vDocTypePointOfSales, vDocTypePointOfSalesVoid)
208 AND A.doc_date BETWEEN pPeriodFrom AND pPeriodTo
209 GROUP BY A.product_id, A.base_uom_id;
210
211 -- insert qty_retur_beli table tt_r_daily_stock_mutation
212 INSERT INTO tt_r_daily_stock_mutation(
213 session_id, product_id, product_code, product_name,
214 brand, satuan, qty_retur_beli)
215 SELECT pSessionId, A.product_id, f_get_product_code(A.product_id) AS product_code, f_get_product_name(A.product_id) AS product_name,
216 '' AS brand, f_get_uom_name(A.base_uom_id) AS satuan, SUM(A.qty) AS qty_retur_beli
217 FROM in_log_product_balance_stock A
218 INNER JOIN m_warehouse_ou B ON A.warehouse_id = B.warehouse_id
219 INNER JOIN m_ou_structure C ON B.ou_id = C.ou_id
220 WHERE A.tenant_id = pTenantId
221 AND C.ou_bu_id = pOuId
222 AND A.doc_type_id = vDocTypeClaimNote
223 AND A.doc_date BETWEEN pPeriodFrom AND pPeriodTo
224 GROUP BY A.product_id, A.base_uom_id;
225
226 -- insert qty_retur_jual table tt_r_daily_stock_mutation
227 INSERT INTO tt_r_daily_stock_mutation(
228 session_id, product_id, product_code, product_name,
229 brand, satuan, qty_retur_jual)
230 SELECT pSessionId, A.product_id, f_get_product_code(A.product_id) AS product_code, f_get_product_name(A.product_id) AS product_name,
231 '' AS brand, f_get_uom_name(A.base_uom_id) AS satuan, SUM(A.qty) AS qty_retur_jual
232 FROM in_log_product_balance_stock A
233 INNER JOIN m_warehouse_ou B ON A.warehouse_id = B.warehouse_id
234 INNER JOIN m_ou_structure C ON B.ou_id = C.ou_id
235 WHERE A.tenant_id = pTenantId
236 AND C.ou_bu_id = pOuId
237 AND A.doc_type_id = vDocTypeReturnNote
238 AND A.doc_date BETWEEN pPeriodFrom AND pPeriodTo
239 GROUP BY A.product_id, A.base_uom_id;
240
241 -- insert qty_keluar_barang table tt_r_daily_stock_mutation
242 INSERT INTO tt_r_daily_stock_mutation(
243 session_id, product_id, product_code, product_name,
244 brand, satuan, qty_keluar_barang)
245 SELECT pSessionId, A.product_id, f_get_product_code(A.product_id) AS product_code, f_get_product_name(A.product_id) AS product_name,
246 '' AS brand, f_get_uom_name(A.base_uom_id) AS satuan, SUM(A.qty) AS qty_keluar_barang
247 FROM in_log_product_balance_stock A
248 INNER JOIN m_warehouse_ou B ON A.warehouse_id = B.warehouse_id
249 INNER JOIN m_ou_structure C ON B.ou_id = C.ou_id
250 WHERE A.tenant_id = pTenantId
251 AND C.ou_bu_id = pOuId
252 AND A.doc_type_id = vDocTypeGoodsTransferOut
253 AND A.doc_date BETWEEN pPeriodFrom AND pPeriodTo
254 GROUP BY A.product_id, A.base_uom_id;
255
256 -- insert qty_adj table tt_r_daily_stock_mutation
257 INSERT INTO tt_r_daily_stock_mutation(
258 session_id, product_id, product_code, product_name,
259 brand, satuan, qty_adj)
260 SELECT pSessionId, A.product_id, f_get_product_code(A.product_id) AS product_code, f_get_product_name(A.product_id) AS product_name,
261 '' AS brand, f_get_uom_name(A.base_uom_id) AS satuan, SUM(A.qty) AS qty_adj
262 FROM in_log_product_balance_stock A
263 INNER JOIN m_warehouse_ou B ON A.warehouse_id = B.warehouse_id
264 INNER JOIN m_ou_structure C ON B.ou_id = C.ou_id
265 WHERE A.tenant_id = pTenantId
266 AND C.ou_bu_id = pOuId
267 AND A.doc_type_id IN (vDocTypeAdjusmentStockQty, vDocTypeAdjusmentStockQtyAmount)
268 AND A.doc_date BETWEEN pPeriodFrom AND pPeriodTo
269 GROUP BY A.product_id, A.base_uom_id;
270
271
272 -- insert data yg belum ada di tt
273 INSERT INTO tt_r_daily_stock_mutation(
274 session_id, product_id, product_code, product_name,
275 brand, satuan)
276 SELECT pSessionId,A.product_id,A.product_code,A.product_name,
277 '' AS brand, f_get_uom_name(A.base_uom_id) AS satuan
278 FROM m_product A
279 WHERE A.active='Y' AND tenant_id =10 AND NOT EXISTS(
280 SELECT 1 from tt_r_daily_stock_mutation B WHERE B.product_id = A.product_id
281 );
282
283 OPEN pRefHeader FOR
284 SELECT vOuCodeName AS ou_code_name, vWarehouseCodeName AS warehouse_code_name,
285 pDatetime AS datetime, f_get_username(pUserId) AS username, pPeriodFrom AS period_from, pPeriodTo AS period_to,
286 CASE WHEN pShowAllProduct = vOnlyWithStockMovement THEN 'Only With Stock Movement' ELSE 'All' END AS showed_product;
287 RETURN NEXT pRefHeader;
288
289 IF(pShowAllProduct = vOnlyWithStockMovement) THEN
290 OPEN pRefDetail FOR
291 SELECT A.product_code, A.product_name,
292 CONCAT(f_get_brand_code(B.brand_id), ' - ', f_get_brand_name(B.brand_id)) AS brand,
293 A.satuan,
294 SUM(ABS(A.saldo_awal)) AS saldo_awal,SUM(ABS(A.saldo_awal))*SUM(A.nilai_satuan) AS nilai_saldo_awal,
295 SUM(ABS(A.qty_terima_barang)) AS qty_terima_barang_supplier, SUM(ABS(A.qty_terima_barang))*SUM(A.nilai_satuan) AS nilai_terima_barang_supplier,
296 SUM(ABS(A.qty_terima_barang_antar_gudang)) AS qty_terima_barang_antar_gudang, SUM(ABS(A.qty_terima_barang_antar_gudang))*SUM(A.nilai_satuan) AS nilai_terima_barang_antar_gudang,
297 SUM(ABS(A.qty_jual)) AS qty_jual, SUM(ABS(A.qty_jual))*SUM(A.nilai_satuan) AS nilai_jual,
298 SUM(ABS(A.qty_retur_beli)) As qty_retur_beli, SUM(ABS(A.qty_retur_beli))*SUM(A.nilai_satuan) AS nilai_retur_beli,
299 SUM(ABS(A.qty_retur_jual)) AS qty_retur_jual, SUM(ABS(A.qty_retur_jual))*SUM(A.nilai_satuan) AS nilai_retur_jual,
300 SUM(ABS(A.qty_keluar_barang)) AS qty_keluar_barang, SUM(ABS(A.qty_keluar_barang))*SUM(A.nilai_satuan) AS nilai_keluar_barang,
301 SUM(A.qty_adj) AS qty_adj, SUM(A.qty_adj)*SUM(A.nilai_satuan) AS nilai_adj,
302 (SUM(A.saldo_awal) + SUM(A.qty_terima_barang) + SUM(A.qty_terima_barang_antar_gudang) + SUM(A.qty_jual) + SUM(A.qty_retur_beli) +
303 SUM(A.qty_retur_jual) + SUM(A.qty_keluar_barang) + SUM(A.qty_adj)) AS saldo_akhir,
304 SUM(A.nilai_satuan) AS nilai_satuan,
305 SUM(A.nilai_satuan) * (SUM(A.saldo_awal) + SUM(A.qty_terima_barang) + SUM(A.qty_terima_barang_antar_gudang) + SUM(A.qty_jual) + SUM(A.qty_retur_beli) +
306 SUM(A.qty_retur_jual) + SUM(A.qty_keluar_barang) + SUM(A.qty_adj)) AS nilai_akhir
307 FROM tt_r_daily_stock_mutation A
308 INNER JOIN m_product B ON A.product_id = B.product_id
309 GROUP BY A.product_code, A.product_name, B.brand_id, A.satuan
310 HAVING SUM(A.qty_terima_barang) != 0 OR SUM(A.qty_terima_barang_antar_gudang) != 0 OR SUM(A.qty_jual) != 0 OR SUM(A.qty_retur_beli) != 0
311 OR SUM(A.qty_retur_jual) != 0 OR SUM(A.qty_keluar_barang) != 0 OR SUM(A.qty_adj) != 0
312 ORDER BY A.product_name;
313 RETURN NEXT pRefDetail;
314 ELSE
315 OPEN pRefDetail FOR
316 SELECT A.product_code, A.product_name,
317 CONCAT(f_get_brand_code(B.brand_id), ' - ', f_get_brand_name(B.brand_id)) AS brand,
318 A.satuan,
319 SUM(ABS(A.saldo_awal)) AS saldo_awal,SUM(ABS(A.saldo_awal))*SUM(A.nilai_satuan) AS nilai_saldo_awal,
320 SUM(ABS(A.qty_terima_barang)) AS qty_terima_barang_supplier, SUM(ABS(A.qty_terima_barang))*SUM(A.nilai_satuan) AS nilai_terima_barang_supplier,
321 SUM(ABS(A.qty_terima_barang_antar_gudang)) AS qty_terima_barang_antar_gudang, SUM(ABS(A.qty_terima_barang_antar_gudang))*SUM(A.nilai_satuan) AS nilai_terima_barang_antar_gudang,
322 SUM(ABS(A.qty_jual)) AS qty_jual, SUM(ABS(A.qty_jual))*SUM(A.nilai_satuan) AS nilai_jual,
323 SUM(ABS(A.qty_retur_beli)) As qty_retur_beli, SUM(ABS(A.qty_retur_beli))*SUM(A.nilai_satuan) AS nilai_retur_beli,
324 SUM(ABS(A.qty_retur_jual)) AS qty_retur_jual, SUM(ABS(A.qty_retur_jual))*SUM(A.nilai_satuan) AS nilai_retur_jual,
325 SUM(ABS(A.qty_keluar_barang)) AS qty_keluar_barang, SUM(ABS(A.qty_keluar_barang))*SUM(A.nilai_satuan) AS nilai_keluar_barang,
326 SUM(A.qty_adj) AS qty_adj, SUM(A.qty_adj)*SUM(A.nilai_satuan) AS nilai_adj,
327 (SUM(A.saldo_awal) + SUM(A.qty_terima_barang) + SUM(A.qty_terima_barang_antar_gudang) + SUM(A.qty_jual) + SUM(A.qty_retur_beli) +
328 SUM(A.qty_retur_jual) + SUM(A.qty_keluar_barang) + SUM(A.qty_adj)) AS saldo_akhir,
329 SUM(A.nilai_satuan) AS nilai_satuan,
330 SUM(A.nilai_satuan) * (SUM(A.saldo_awal) + SUM(A.qty_terima_barang) + SUM(A.qty_terima_barang_antar_gudang) + SUM(A.qty_jual) + SUM(A.qty_retur_beli) +
331 SUM(A.qty_retur_jual) + SUM(A.qty_keluar_barang) + SUM(A.qty_adj)) AS nilai_akhir
332 FROM tt_r_daily_stock_mutation A
333 INNER JOIN m_product B ON A.product_id = B.product_id
334 GROUP BY A.product_code, A.product_name, B.brand_id, A.satuan
335 ORDER BY A.product_name;
336 RETURN NEXT pRefDetail;
337 END IF;
338
339 DELETE FROM tt_r_daily_stock_mutation WHERE session_id = pSessionId;
340END;
341$BODY$
342 LANGUAGE plpgsql VOLATILE
343 COST 100;
344 /