· 5 years ago · Oct 16, 2020, 07:16 AM
1CREATE OR REPLACE FUNCTION r_omset_penjualan_dan_stock(character varying, bigint, bigint, bigint, character varying, 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 pYearMonth ALIAS FOR $7;
14 pCurrCode ALIAS FOR $8;
15 pDateNow ALIAS FOR $9;
16
17 vLastProcessedYearMonth character varying(6);
18 vLastProcessedYearMonthForCogs character varying(6);
19 vPrevPeriodForFilter character varying(6);
20 vNextYearMonthFromLastProcessed character varying(6);
21 vNextYearMonthFromLastProcessedWithNow character varying(6);
22
23 vEmptyId bigint := -99;
24 vOuBuId bigint := -99;
25 vAll character varying := 'ALL';
26 vNol numeric := 0;
27 vFilterYearMonth text := '';
28 vRawData character varying := 'raw.data';
29 vSummaryData character varying := 'summary.data';
30 vEmptyValue character varying := '';
31 vUsername character varying;
32
33 vFilterOu character varying := '';
34 vFilterOu2 character varying := '';
35 vFilterCurrCode character varying := '';
36 vYes character varying := 'Y';
37 vNo character varying := 'N';
38 vSelesai character varying := 'S';
39 vVoid character varying := 'V';
40 vDocTypeIdPos bigint := 401;
41 vDocTypeIdSi bigint := 321;
42 vDocTypeIdTsi bigint := 331;
43
44BEGIN
45
46 IF pOuId <> vEmptyId THEN
47 vFilterOu := ' AND A.ou_id = '||pOuId||'';
48 vFilterOu2 := ' AND B.ou_id = '||pOuId||'';
49 END IF;
50
51
52
53 IF pCurrCode <> '-99' THEN
54 vFilterCurrCode := ' AND A.curr_code ='''||pCurrCode||'''';
55 END IF;
56
57 -- Ambil data ou bu untuk mencari tutup bulan terakhir
58 SELECT DISTINCT ou_bu_id INTO vOuBuId
59 FROM m_ou_structure
60 WHERE (ou_id = pOuId OR pOuId = vEmptyId)
61 ORDER BY ou_bu_id
62 LIMIT 1;
63
64 -- Ambil terkahir tutup bulan untuk ambil saldo awal
65 SELECT COALESCE(MAX(A.date_year_month),' ') INTO vLastProcessedYearMonth
66 FROM m_admin_process_ledger A, m_ou_structure B
67 WHERE A.tenant_id = pTenantId AND
68 B.ou_id = vOuBuId AND
69 A.ou_id = B.ou_bu_id AND
70 A.status_ledger = '1' AND
71 A.ledger_code = 'COST.FG' AND
72 A.date_year_month < pYearMonth;
73
74 --tutup bulan buat cogs
75 SELECT COALESCE(MAX(A.date_year_month),' ') INTO vLastProcessedYearMonthForCogs
76 FROM m_admin_process_ledger A, m_ou_structure B
77 WHERE A.tenant_id = pTenantId AND
78 B.ou_id = vOuBuId AND
79 A.ou_id = B.ou_bu_id AND
80 A.status_ledger = '1' AND
81 A.ledger_code = 'COST.FG' AND
82 A.date_year_month <= pYearMonth;
83
84 SELECT TO_CHAR(TO_DATE(MAX(pYearMonth),'YYYYMM') - interval '1 Month','YYYYMM') INTO vPrevPeriodForFilter;
85 SELECT TO_CHAR(TO_DATE(MAX(vLastProcessedYearMonth),'YYYYMM') + interval '1 Month','YYYYMM') INTO vNextYearMonthFromLastProcessed;
86
87
88 RAISE NOTICE 'vPrevPeriodForFilter = %, vNextYearMonthFromLastProcessed = %', vPrevPeriodForFilter, vNextYearMonthFromLastProcessed;
89
90 -- Ambil saldo awal stok
91 EXECUTE '
92 INSERT INTO tt_product_stock_cogs (
93 session_id, tenant_id, ou_id, product_id, avg_cogs, qty
94 )
95 SELECT $1, A.tenant_id, B.ou_id, A.product_id, 0, SUM(A.qty)
96 FROM in_summary_monthly_qty A
97 INNER JOIN m_warehouse_ou B ON A.warehouse_id = B.warehouse_id
98 WHERE A.tenant_id = $2
99 AND A.doc_type_id = $4
100 AND A.date_year_month <= $3
101 '||vFilterOu2||'
102 GROUP BY A.tenant_id, B.ou_id, A.product_id'
103 USING pSessionId, pTenantId, pYearMonth, vEmptyId;
104
105 -- Ambil saldo stok dari transaksi
106 EXECUTE '
107 INSERT INTO tt_product_stock_cogs (
108 session_id, tenant_id, ou_id, product_id, avg_cogs, qty
109 )
110 SELECT $1, A.tenant_id, B.ou_id, A.product_id, 0, SUM(A.qty)
111 FROM in_log_product_balance_stock A
112 INNER JOIN m_warehouse_ou B ON A.warehouse_id = B.warehouse_id
113 WHERE A.tenant_id = $2
114 AND LEFT(A.doc_date, 6) BETWEEN $5 AND $3
115 '||vFilterOu2||'
116 GROUP BY A.tenant_id, B.ou_id, A.product_id'
117 USING pSessionId, pTenantId, pYearMonth, vEmptyId, vNextYearMonthFromLastProcessed;
118
119 -- Ambil rata2 saldo awal dari avg price
120 EXECUTE '
121 INSERT INTO tt_product_stock_cogs (
122 session_id, tenant_id, ou_id, product_id, avg_cogs, qty
123 )
124 SELECT $1, A.tenant_id, B.ou_id, A.product_id, SUM(A.avg_price), 0
125 FROM in_summary_monthly_cogs A
126 INNER JOIN m_ou_structure B ON A.ou_id = B.ou_bu_id
127 WHERE A.tenant_id = $2
128 AND A.date_year_month = $3
129 '||vFilterOu2||vFilterCurrCode||'
130 GROUP BY A.tenant_id, B.ou_id, A.product_id'
131 USING pSessionId, pTenantId, vLastProcessedYearMonthForCogs, vEmptyId,vYes,vNo;
132
133 INSERT INTO tt_product_stock_cogs (
134 session_id, tenant_id, ou_id, product_id, avg_cogs, qty,data_type
135 )
136 SELECT pSessionId,tenant_id, ou_id, product_id, SUM(avg_cogs) total_avg, SUM(qty) total_qty,vSummaryData
137 FROM tt_product_stock_cogs A
138 WHERE A.tenant_id = pTenantId
139 AND A.session_id = pSessionId
140 GROUP BY tenant_id, ou_id, product_id;
141
142 DELETE FROM tt_product_stock_cogs WHERE data_type = vRawData;
143
144 WITH TEMP AS(
145 SELECT MAX(A.ref_doc_date) AS ref_doc_date, MAX(A.create_datetime) AS create_datetime, C.product_id
146 FROM tt_product_stock_cogs C
147 INNER JOIN pu_receive_goods_item B ON B.product_id = C.product_id
148 INNER JOIN pu_po_balance_invoice A ON A.ref_item_id = B.receive_goods_item_id AND A.ref_id = B.receive_goods_id
149 WHERE C.data_type = vSummaryData
150 AND C.avg_cogs=0
151 AND LEFT(A.ref_doc_date,6) <= pYearMonth AND A.price_po <> 0
152 GROUP BY C.product_id
153 )
154 UPDATE tt_product_stock_cogs A
155 SET avg_cogs = C.price_po
156 FROM TEMP B
157 INNER JOIN pu_po_balance_invoice C ON B.ref_doc_date = C.ref_doc_date
158 AND B.create_datetime = C.create_datetime
159 WHERE A.data_type = vSummaryData
160 AND A.product_id = B.product_id
161 AND A.avg_cogs = 0
162 AND A.tenant_id = pTenantId
163 AND A.session_id = pSessionId;
164
165 -- Kalkulasi total qty dan rata2 cogs, dimana total qty * total_avg per product
166 -- Masukan ke table temporary sebagai partner id = -99 untuk menentukan total qty dan total nominal per OU nya
167 EXECUTE '
168 WITH TEMP AS (
169 SELECT ou_id,qty, qty * avg_cogs total_nominal
170 FROM tt_product_stock_cogs
171 WHERE data_type=$4
172 GROUP BY tenant_id, ou_id,qty,avg_cogs,product_id
173 )
174 INSERT INTO tt_report_omzet_sales_stock (
175 session_id, ou_id, partner_id, total_omzet, total_qty, total_nominal
176 )
177 SELECT
178 $1, ou_id, $3, 0, SUM(qty), SUM(total_nominal)
179 FROM TEMP
180 GROUP BY ou_id'
181 USING pSessionId, pTenantId, vEmptyId,vSummaryData;
182
183 -- Ambil data omzet per ou dan per salesman
184 EXECUTE '
185 INSERT INTO tt_report_omzet_sales_stock (
186 session_id, ou_id, partner_id, total_omzet, total_qty, total_nominal
187 )
188 SELECT $1, A.ou_id, B.salesman_id, SUM(A.amount), 0, 0
189 FROM fi_invoice_ar_balance A
190 INNER JOIN sl_invoice B ON A.invoice_ar_id = B.invoice_id
191 AND A.doc_type_id = B.doc_type_id
192 WHERE A.tenant_id = $2
193 AND A.doc_type_id IN($5,$6)
194 AND LEFT(A.doc_date, 6) = $3
195 '||vFilterOu||vFilterCurrCode||'
196 GROUP BY A.ou_id, B.salesman_id'
197 USING pSessionId, pTenantId, pYearMonth, vEmptyId,vDocTypeIdSi,vDocTypeIdTsi;
198
199
200 EXECUTE '
201 INSERT INTO tt_report_omzet_sales_stock (
202 session_id, ou_id, partner_id, total_omzet, total_qty, total_nominal
203 )
204 SELECT $1, A.ou_id, A.salesman_id, SUM(A.total_amount), 0, 0
205 FROM i_trx_pos A
206 WHERE A.tenant_id = $2
207 AND A.doc_type_id = $5
208 AND A.status IN ($6,$7)
209 AND LEFT(A.doc_date, 6) = $3
210 '||vFilterOu||vFilterCurrCode||'
211 GROUP BY A.ou_id, A.salesman_id'
212 USING pSessionId, pTenantId, pYearMonth, vEmptyId,vDocTypeIdPos,vSelesai,vVoid;
213
214 EXECUTE '
215 INSERT INTO tt_report_omzet_sales_stock (
216 session_id, ou_id, partner_id, total_omzet, total_qty, total_nominal
217 )
218 SELECT $1, A.ou_id, A.salesman_id, SUM(-1*A.total_amount), 0, 0
219 FROM i_trx_pos A
220 WHERE A.tenant_id = $2
221 AND A.doc_type_id = $5
222 AND A.status = $6
223 AND LEFT(A.doc_date, 6) = $3
224 '||vFilterOu||vFilterCurrCode||'
225 GROUP BY A.ou_id, A.salesman_id'
226 USING pSessionId, pTenantId, pYearMonth, vEmptyId,vDocTypeIdPos,vVoid;
227
228 -- Get header
229 OPEN pRefHeader FOR
230 SELECT CASE WHEN pOuId = vEmptyId THEN vAll ELSE CONCAT(f_get_ou_code(pOuId),' / ',f_get_ou_name(pOuId)) END AS ou, pYearMonth AS year_month,
231 f_get_user_fullname(pUserId) AS username, pDatetime AS datetime;
232 RETURN NEXT pRefHeader;
233
234 --Get Detail
235 OPEN pRefDetail FOR
236 WITH TEMP_DATA_OMZET_PARTNER AS (
237 SELECT ou_id, partner_id, SUM(total_omzet) total_omzet
238 FROM tt_report_omzet_sales_stock
239 WHERE session_id = pSessionId
240 AND partner_id <> vEmptyId
241 GROUP BY ou_id, partner_id
242 )
243 SELECT f_get_ou_code(A.ou_id) ou_code, f_get_ou_name(A.ou_id) ou_name, f_get_partner_code(A.partner_id) partner_code,
244 f_get_partner_name(A.partner_id) AS partner_name, A.total_omzet, B.total_nominal
245 FROM TEMP_DATA_OMZET_PARTNER A
246 LEFT JOIN tt_report_omzet_sales_stock B ON B.session_id = pSessionId AND B.partner_id = vEmptyId AND A.ou_id = B.ou_id
247
248 UNION ALL
249
250 SELECT f_get_ou_code(A.ou_id) ou_code, f_get_ou_name(A.ou_id) ou_name, vEmptyValue AS partner_code,
251 vEmptyValue AS partner_name, vNol AS total_omzet, A.total_nominal
252 FROM tt_report_omzet_sales_stock A
253 WHERE A.session_id = pSessionId
254 AND A.partner_id = vEmptyId
255 AND A.total_nominal <> vNol
256 AND NOT EXISTS (SELECT 1 FROM TEMP_DATA_OMZET_PARTNER B
257 WHERE A.ou_id = B.ou_id)
258 ORDER BY ou_code, partner_name;
259
260 RETURN NEXT pRefDetail;
261
262 DELETE FROM tt_product_stock_cogs WHERE session_id = pSessionId;
263 DELETE FROM tt_report_omzet_sales_stock WHERE session_id = pSessionId;
264
265END;
266$BODY$
267 LANGUAGE plpgsql VOLATILE
268 COST 100
269 ROWS 1000;
270/
271