· 5 years ago · Nov 12, 2020, 09:08 AM
1/**
2 * Adrian, Jun 22, 2017
3 * pu_get_latest_purchasing_price_by_date dimasukkan ke dalam looping per OU BU
4 */
5
6/**
7 * Modified by Adrian, May 17, 2017
8 * Menambahkan transaksi edc transfer
9 * Device_merchant_code untuk edc_transfer dibuat sama dengan ou_code outlet
10 */
11
12/**
13 * Modified by Deo, Apr 22, 2020
14 * Penyesuaian function report daily sales untuk sasa
15 * Mengganti table i_trx_pos_ext diganti menjadi join i_trx_pos_custom dan mengganti join-joinnya
16 * Menghilangkan filter vehicle brand, vehicle type, dan year made
17 */
18--DROP FUNCTION IF EXISTS r_daily_sales(character varying, bigint, bigint, character varying, character varying);
19--DROP FUNCTION IF EXISTS r_daily_sales(character varying, bigint, bigint, bigint, character varying, bigint, character varying, character varying);
20CREATE OR REPLACE FUNCTION r_daily_sales(character varying, bigint, bigint, bigint, character varying, bigint, character varying, character varying, character varying)
21 RETURNS SETOF refcursor AS
22$BODY$
23DECLARE
24 pRefHeader REFCURSOR := 'refHeader';
25 pRefDetail REFCURSOR := 'refDetail';
26 pSessionId ALIAS FOR $1;
27 pTenantId ALIAS FOR $2;
28 pUserId ALIAS FOR $3;
29 pRoleId ALIAS FOR $4;
30 pDatetime ALIAS FOR $5;
31 pOuId ALIAS FOR $6;
32 pTypeDoc ALIAS FOR $7;
33 pDateFrom ALIAS FOR $8;
34 pDateTo ALIAS FOR $9;
35
36 vEmptyValue character varying(1);
37 vEmptySpaceValue character varying(1);
38 vEmptyId bigint;
39 vAllId bigint;
40
41 vFilterOuId text := '';
42
43 vParentOuId bigint;
44 vYearMonth character varying(6);
45 vDatetime character varying(14);
46 vMaxYearMonth character varying(6);
47
48 vOuBuId bigint;
49 vDocTypeReturnSI bigint := 331;
50 vDocTypeSI bigint := 321;
51 vTypeDocAll character varying := 'typeAll';
52 vTypeDocPenjualan character varying := 'typePenjualan';
53 vTypeDocReturn character varying := 'typeReturn';
54
55 vDocDescPosShop character varying := 'POS Shop';
56 vKredit character varying := 'KREDIT';
57 vTunai character varying := 'TUNAI';
58
59
60BEGIN
61
62 vEmptyValue := '';
63 vEmptySpaceValue := ' ';
64 vEmptyId := -99;
65 vAllId := -99;
66
67 DELETE FROM tr_daily_sales_report WHERE session_id = pSessionId;
68 DELETE FROM tt_in_latest_purchasing_price_by_date WHERE session_id = pSessionId;
69 DELETE FROM tt_out_latest_purchasing_price_by_date WHERE session_id = pSessionId;
70
71 vParentOuId := f_get_parent_ou_bu(pTenantId, pOuId);
72 SELECT TO_CHAR(now(), 'YYYYMM') INTO vYearMonth;
73 SELECT TO_CHAR(now(), 'YYYYMMDDHH24MISS') INTO vDatetime;
74 SELECT MAX(date_year_month) INTO vMaxYearMonth FROM in_summary_monthly_cogs;
75
76 IF pOuId <> vAllId THEN
77 vFilterOuId := ' AND A.ou_id = ' || pOuId;
78 END IF;
79
80 -- produk non nempil
81 -- untuk uang kembalian (total_refund) dikurangkan pada pembayaran tunai
82 EXECUTE '
83 WITH summed_i_trx_pos_non_cash_payment AS (
84 SELECT A.process_no, A.tenant_id, A.trx_pos_id, SUM(COALESCE(A.payment_amount, 0)) AS payment_amount, COALESCE(A.curr_payment_code, $2) AS curr_payment_code
85 FROM i_trx_pos_non_cash_payment A
86 WHERE NOT EXISTS (
87 SELECT (1)
88 FROM m_device_merchant B
89 INNER JOIN t_ou C ON B.device_merchant_code = C.ou_code
90 WHERE B.device_merchant_id = A.device_merchant_id
91 )
92 GROUP BY A.process_no, A.tenant_id, A.trx_pos_id, A.curr_payment_code
93 ), summed_i_trx_pos_transfer_payment AS (
94 SELECT A.process_no, A.tenant_id, A.trx_pos_id, SUM(COALESCE(A.payment_amount, 0)) AS payment_amount, COALESCE(A.curr_payment_code, $2) AS curr_payment_code
95 FROM i_trx_pos_non_cash_payment A
96 WHERE EXISTS (
97 SELECT (1)
98 FROM m_device_merchant B
99 INNER JOIN t_ou C ON B.device_merchant_code = C.ou_code
100 WHERE B.device_merchant_id = A.device_merchant_id
101 )
102 GROUP BY A.process_no, A.tenant_id, A.trx_pos_id, A.curr_payment_code
103 )
104 INSERT INTO tr_daily_sales_report (
105 session_id, ou_code, ou_name, year,
106 month, date, doc_no, vehicle_brand,
107 vehicle_type, year_made, customer_id, customer_name,
108 salesman_name, mekanik_name,
109 product_code, product_name, ctgr_product_name, qty, curr_code,
110 modal_price, gross_sell_price, discount_amount,
111 nett_sell_price, curr_payment_code_cash, payment_amount_cash,
112 curr_payment_code_non_cash, payment_amount_non_cash,
113 curr_payment_code_transfer, payment_amount_transfer,
114 curr_under_payment, under_payment_amount,
115 tenant_id, ou_id, doc_date, product_id, line_no,
116 tax_price, omzet_bruto_amount,
117 tax_amount, nett_item_amount, doc_type_desc, group_brand, parent_group_brand)
118 SELECT $1, B.ou_code, B.ou_name, SUBSTRING(A.doc_date, 1, 4) AS year,
119 SUBSTRING(A.doc_date, 5, 2) AS month, SUBSTRING(A.doc_date, 7, 2) AS date, A.doc_no, $2 AS vehicle_brand,
120 $2 AS vehicle_type, $2 AS year_made, A.partner_id, COALESCE(f_get_partner_name(A.partner_id), ''UMUM'') AS customer_name,
121 COALESCE(f_get_partner_name(A.salesman_id), $2) AS salesman_name, COALESCE(f_get_partner_name(C.mekanik_id), $2) AS mekanik_name,
122 G.product_code AS product_code, G.product_name AS product_name, f_get_ctgr_product_name(G.ctgr_product_id) AS ctgr_product_name, F.qty, F.curr_code,
123 0, F.gross_sell_price, COALESCE(F.discount_amount, 0) AS discount_amount,
124 F.nett_sell_price, COALESCE(H.curr_payment_code, $2) AS curr_payment_code_cash, CASE WHEN F.line_no = 1 THEN
125 CASE WHEN C.flg_termin = ''Y'' THEN H.payment_amount ELSE (H.payment_amount- A.total_refund) END ELSE 0 END AS payment_amount_cash,
126 I.curr_payment_code AS curr_payment_code_non_cash, CASE WHEN F.line_no = 1 THEN COALESCE(I.payment_amount, 0) ELSE 0 END AS payment_amount_non_cash,
127 M.curr_payment_code AS curr_payment_code_transfer, CASE WHEN F.line_no = 1 THEN COALESCE(M.payment_amount, 0) ELSE 0 END AS payment_amount_transfer,
128 COALESCE(J.curr_payment_code, $2) AS curr_under_payment, CASE WHEN F.line_no = 1 THEN COALESCE(j.under_payment_amount, 0) ELSE 0 END AS under_payment_amount,
129 A.tenant_id, B.ou_id, A.doc_date, G.product_id, F.line_no,
130 F.gross_sell_price - COALESCE(F.discount_amount,0) - F.nett_sell_price AS tax_price, F.qty * F.gross_sell_price AS omzet_bruto_amount,
131 F.tax_amount, F.nett_amount_item AS nett_item_amount, f_get_doc_desc(A.doc_type_id) AS doc_type_desc,
132 Q.group_brand_code ||''-''|| Q.group_brand_name AS group_brand, Q.parent_group_brand
133 FROM i_trx_pos A
134 INNER JOIN t_ou B on A.ou_id = B.ou_id
135 INNER JOIN i_trx_pos_custom C on C.process_no = A.process_no and C.tenant_id = A.tenant_id and C.trx_pos_id = A.trx_pos_id
136
137 INNER JOIN i_trx_pos_item F on A.process_no = F.process_no and A.tenant_id = F.tenant_id and A.trx_pos_id = F.trx_pos_id
138 INNER JOIN m_product G on F.product_id = G.product_id
139 INNER JOIN m_product_custom_for_sasa P on G.product_id = P.product_id
140 INNER JOIN m_group_brand Q ON P.group_brand_id = Q.group_brand_id
141
142 INNER JOIN t_user_role Z ON Z.user_id = $6 AND Z.role_id = $7
143 INNER JOIN t_policy_ou Y ON A.ou_id = Y.ou_id AND Z.policy_id = Y.policy_id
144 LEFT OUTER JOIN i_trx_pos_cash_payment H on A.process_no = H.process_no and B.tenant_id = H.tenant_id and A.trx_pos_id = H.trx_pos_id
145 LEFT OUTER JOIN summed_i_trx_pos_non_cash_payment I on A.process_no = I.process_no and A.tenant_id = I.tenant_id and A.trx_pos_id = I.trx_pos_id
146 LEFT OUTER JOIN summed_i_trx_pos_transfer_payment M on A.process_no = M.process_no and A.tenant_id = M.tenant_id and A.trx_pos_id = M.trx_pos_id
147 LEFT OUTER JOIN i_trx_pos_termin_payment J on A.process_no = J.process_no and A.tenant_id = J.tenant_id and A.trx_pos_id = J.trx_pos_id
148
149 WHERE A.tenant_id = $3
150 ' || vFilterOuId || '
151 AND A.doc_date BETWEEN $4 AND $5
152 AND $11 IN ($8, $10)
153
154 UNION
155
156 SELECT $1, B.ou_code, B.ou_name, SUBSTRING(E.doc_date, 1, 4) AS year,
157 SUBSTRING(E.doc_date, 5, 2) AS month, SUBSTRING(E.doc_date, 7, 2) AS date, A.doc_no || ''_VOID'', $2 AS vehicle_brand,
158 $2 AS vehicle_type, $2 AS year_made, A.partner_id, COALESCE(f_get_partner_name(A.partner_id), ''UMUM'') AS customer_name,
159 COALESCE(f_get_partner_name(A.salesman_id), $2) AS salesman_name, COALESCE(f_get_partner_name(C.mekanik_id), $2) AS mekanik_name,
160 G.product_code AS product_code, G.product_name AS product_name, f_get_ctgr_product_name(G.ctgr_product_id) AS ctgr_product_name, F.qty*(-1), F.curr_code,
161 0, F.gross_sell_price, COALESCE(F.discount_amount, 0) AS discount_amount,
162 F.nett_sell_price, COALESCE(H.curr_payment_code, $2) AS curr_payment_code_cash, CASE WHEN F.line_no = 1 THEN
163 CASE WHEN C.flg_termin = ''Y'' THEN H.payment_amount*(-1) ELSE (H.payment_amount- A.total_refund)*-1 END ELSE 0 END AS payment_amount_cash,
164 I.curr_payment_code AS curr_payment_code_non_cash, CASE WHEN F.line_no = 1 THEN COALESCE(I.payment_amount*(-1), 0) ELSE 0 END AS payment_amount_non_cash,
165 M.curr_payment_code AS curr_payment_code_transfer, CASE WHEN F.line_no = 1 THEN COALESCE(M.payment_amount*(-1), 0) ELSE 0 END AS payment_amount_transfer,
166 COALESCE(J.curr_payment_code, $2) AS curr_under_payment, CASE WHEN F.line_no = 1 THEN COALESCE(j.under_payment_amount*(-1), 0) ELSE 0 END AS under_payment_amount,
167 A.tenant_id, B.ou_id, E.doc_date, G.product_id, F.line_no,
168 F.gross_sell_price - COALESCE(F.discount_amount,0) - F.nett_sell_price AS tax_price, (F.qty*(-1)) * F.gross_sell_price AS omzet_bruto_amount,
169 F.tax_amount*(-1), F.nett_amount_item*(-1) AS nett_item_amount, f_get_doc_desc(A.doc_type_id) AS doc_type_desc,
170 Q.group_brand_code ||''-''|| Q.group_brand_name AS group_brand, Q.parent_group_brand
171 FROM i_trx_pos A
172 INNER JOIN t_ou B on A.ou_id = B.ou_id
173 INNER JOIN i_trx_pos_custom C on C.process_no = A.process_no and C.tenant_id = A.tenant_id and C.trx_pos_id = A.trx_pos_id
174 INNER JOIN i_trx_log_voided_pos_custom E ON A.doc_no = E.doc_no AND A.ou_id = E.ou_id AND A.tenant_id = E.tenant_id
175 INNER JOIN i_trx_pos_item F on A.process_no = F.process_no and A.tenant_id = F.tenant_id and A.trx_pos_id = F.trx_pos_id
176 INNER JOIN m_product G on F.product_id = G.product_id
177 INNER JOIN m_product_custom_for_sasa P on G.product_id = P.product_id
178 INNER JOIN m_group_brand Q ON P.group_brand_id = Q.group_brand_id
179
180 INNER JOIN t_user_role Z ON Z.user_id = $6 AND Z.role_id = $7
181 INNER JOIN t_policy_ou Y ON A.ou_id = Y.ou_id AND Z.policy_id = Y.policy_id
182 LEFT OUTER JOIN i_trx_pos_cash_payment H on A.process_no = H.process_no and B.tenant_id = H.tenant_id and A.trx_pos_id = H.trx_pos_id
183 LEFT OUTER JOIN summed_i_trx_pos_non_cash_payment I on A.process_no = I.process_no and A.tenant_id = I.tenant_id and A.trx_pos_id = I.trx_pos_id
184 LEFT OUTER JOIN summed_i_trx_pos_transfer_payment M on A.process_no = M.process_no and A.tenant_id = M.tenant_id and A.trx_pos_id = M.trx_pos_id
185 LEFT OUTER JOIN i_trx_pos_termin_payment J on A.process_no = J.process_no and A.tenant_id = J.tenant_id and A.trx_pos_id = J.trx_pos_id
186
187 WHERE A.status = ''V''
188 AND A.tenant_id = $3
189 ' || vFilterOuId || '
190 AND E.doc_date BETWEEN $4 AND $5
191 AND $11 IN ($9, $10) '
192 USING pSessionId, vEmptyValue, pTenantId, pDateFrom, pDateTo, pUserId, pRoleId, vTypeDocPenjualan, vTypeDocReturn, vTypeDocAll, pTypeDoc;
193
194
195 -- produk nempil
196 /**
197 * mod by Didit, 2 des 2016
198 * gunakan distinct on, karena ada produk nempil yg kode produk nya sama tp beda di trx_nempil_barang_item_id sehingga
199 * produk nempil akan muncul lebih dari satu kali dan menyebabkan perhitungan nett amount akan salah
200 */
201 EXECUTE '
202 WITH summed_i_trx_pos_non_cash_payment AS (
203 SELECT A.process_no, A.tenant_id, A.trx_pos_id, SUM(COALESCE(A.payment_amount, 0)) AS payment_amount, COALESCE(A.curr_payment_code, $2) AS curr_payment_code
204 FROM i_trx_pos_non_cash_payment A
205 WHERE NOT EXISTS (
206 SELECT (1)
207 FROM m_device_merchant B
208 INNER JOIN t_ou C ON B.device_merchant_code = C.ou_code
209 WHERE B.device_merchant_id = A.device_merchant_id
210 )
211 GROUP BY A.process_no, A.tenant_id, A.trx_pos_id, A.curr_payment_code
212 ), summed_i_trx_pos_transfer_payment AS (
213 SELECT A.process_no, A.tenant_id, A.trx_pos_id, SUM(COALESCE(A.payment_amount, 0)) AS payment_amount, COALESCE(A.curr_payment_code, $2) AS curr_payment_code
214 FROM i_trx_pos_non_cash_payment A
215 WHERE EXISTS (
216 SELECT (1)
217 FROM m_device_merchant B
218 INNER JOIN t_ou C ON B.device_merchant_code = C.ou_code
219 WHERE B.device_merchant_id = A.device_merchant_id
220 )
221 GROUP BY A.process_no, A.tenant_id, A.trx_pos_id, A.curr_payment_code
222 )
223 INSERT INTO tr_daily_sales_report (
224 session_id, ou_code, ou_name, year,
225 month, date, doc_no, vehicle_brand,
226 vehicle_type, year_made, customer_id, customer_name,
227 salesman_name, mekanik_name,
228 product_code, product_name, ctgr_product_name, qty, curr_code,
229 modal_price, gross_sell_price, discount_amount,
230 nett_sell_price, curr_payment_code_cash, payment_amount_cash,
231 curr_payment_code_non_cash, payment_amount_non_cash,
232 curr_payment_code_transfer, payment_amount_transfer,
233 curr_under_payment, under_payment_amount,
234 tenant_id, ou_id, doc_date, product_id, line_no,
235 tax_price, omzet_bruto_amount, tax_amount, nett_item_amount, doc_type_desc, group_brand, parent_group_brand)
236 SELECT $1, B.ou_code, B.ou_name, SUBSTRING(A.doc_date, 1, 4) AS year,
237 SUBSTRING(A.doc_date, 5, 2) AS month, SUBSTRING(A.doc_date, 7, 2) AS date, A.doc_no, $2 AS vehicle_brand,
238 $2 AS vehicle_type, $2 AS year_made, A.partner_id, COALESCE(f_get_partner_name(A.partner_id), ''UMUM'') AS customer_name,
239 COALESCE(f_get_partner_name(A.salesman_id), $2) AS salesman_name, COALESCE(f_get_partner_name(C.mekanik_id), $2) AS mekanik_name,
240 G.product_code, G.product_name, $2 AS ctgr_product_name, F.qty, F.curr_code,
241 0 AS modal_price, F.sell_price, 0 AS discount_amount,
242 F.sell_price, COALESCE(H.curr_payment_code, $2) AS curr_payment_code_cash, CASE WHEN F.line_no = 1 THEN COALESCE(H.payment_amount, 0) ELSE 0 END AS payment_amount_cash,
243 I.curr_payment_code AS curr_payment_code_non_cash, CASE WHEN F.line_no = 1 THEN COALESCE(I.payment_amount, 0) ELSE 0 END AS payment_amount_non_cash,
244 M.curr_payment_code AS curr_payment_code_transfer, CASE WHEN F.line_no = 1 THEN COALESCE(M.payment_amount, 0) ELSE 0 END AS payment_amount_transfer,
245 COALESCE(J.curr_payment_code, $2) AS curr_under_payment, CASE WHEN F.line_no = 1 THEN COALESCE(J.under_payment_amount, 0) ELSE 0 END AS under_payment_amount,
246 A.tenant_id, B.ou_id, A.doc_date, -99, F.line_no,
247 0 AS tax_price, F.qty * F.sell_price AS omzet_bruto_amount, 0 AS tax_amount, F.qty * F.sell_price AS nett_item_amount, f_get_doc_desc(A.doc_type_id) AS doc_type_desc,
248 $2, $2
249 FROM i_trx_pos A
250 INNER JOIN t_ou B on A.ou_id = B.ou_id
251 INNER JOIN i_trx_pos_custom C on C.process_no = A.process_no and C.tenant_id = A.tenant_id and C.trx_pos_id = A.trx_pos_id
252
253 INNER JOIN i_trx_pos_item_nempil_barang F on A.process_no = F.process_no and A.tenant_id = F.tenant_id and A.trx_pos_id = F.trx_pos_id
254 INNER JOIN p_nempil_barang_balance_stock G on F.product_code = G.product_code AND A.ou_id = G.ou_id
255
256 INNER JOIN t_user_role Z ON Z.user_id = $6 AND Z.role_id = $7
257 INNER JOIN t_policy_ou Y ON A.ou_id = Y.ou_id AND Z.policy_id = Y.policy_id
258 LEFT OUTER JOIN i_trx_pos_cash_payment H on A.process_no = H.process_no and A.tenant_id = H.tenant_id and A.trx_pos_id = H.trx_pos_id
259 LEFT OUTER JOIN summed_i_trx_pos_non_cash_payment I on A.process_no = I.process_no and A.tenant_id = I.tenant_id and A.trx_pos_id = I.trx_pos_id
260 LEFT OUTER JOIN summed_i_trx_pos_transfer_payment M on A.process_no = M.process_no and A.tenant_id = M.tenant_id and A.trx_pos_id = M.trx_pos_id
261 LEFT OUTER JOIN i_trx_pos_termin_payment J on A.process_no = J.process_no and A.tenant_id = J.tenant_id and A.trx_pos_id = J.trx_pos_id
262
263 WHERE A.tenant_id = $3
264 ' || vFilterOuId || '
265 AND A.doc_date BETWEEN $4 AND $5
266 AND $11 IN ($8, $10)
267
268 UNION
269
270 SELECT $1, B.ou_code, B.ou_name, SUBSTRING(E.doc_date, 1, 4) AS year,
271 SUBSTRING(E.doc_date, 5, 2) AS month, SUBSTRING(E.doc_date, 7, 2) AS date, A.doc_no || ''_VOID'', $2 AS vehicle_brand,
272 $2 AS vehicle_type, $2 AS year_made, A.partner_id, COALESCE(f_get_partner_name(A.partner_id), ''UMUM'') AS customer_name,
273 COALESCE(f_get_partner_name(A.salesman_id), $2) AS salesman_name, COALESCE(f_get_partner_name(C.mekanik_id), $2) AS mekanik_name,
274 G.product_code, G.product_name, $2 AS ctgr_product_name, F.qty*(-1), F.curr_code,
275 0 AS modal_price, F.sell_price, 0 AS discount_amount,
276 F.sell_price, COALESCE(H.curr_payment_code, $2) AS curr_payment_code_cash, CASE WHEN F.line_no = 1 THEN COALESCE(H.payment_amount*(-1), 0) ELSE 0 END AS payment_amount_cash,
277 I.curr_payment_code AS curr_payment_code_non_cash, CASE WHEN F.line_no = 1 THEN COALESCE(I.payment_amount*(-1), 0) ELSE 0 END AS payment_amount_non_cash,
278 M.curr_payment_code AS curr_payment_code_transfer, CASE WHEN F.line_no = 1 THEN COALESCE(M.payment_amount*(-1), 0) ELSE 0 END AS payment_amount_transfer,
279 COALESCE(J.curr_payment_code, $2) AS curr_under_payment, CASE WHEN F.line_no = 1 THEN COALESCE(J.under_payment_amount*(-1), 0) ELSE 0 END AS under_payment_amount,
280 A.tenant_id, B.ou_id, E.doc_date, -99, F.line_no,
281 0 AS tax_price, (F.qty*(-1)) * F.sell_price AS omzet_bruto_amount, 0 AS tax_amount, (F.qty*(-1)) * F.sell_price AS nett_item_amount, f_get_doc_desc(A.doc_type_id) AS doc_type_desc,
282 $2, $2
283 FROM i_trx_pos A
284 INNER JOIN t_ou B on A.ou_id = B.ou_id
285 INNER JOIN i_trx_pos_custom C on C.process_no = A.process_no and C.tenant_id = A.tenant_id and C.trx_pos_id = A.trx_pos_id
286 INNER JOIN i_trx_log_voided_pos_custom E ON A.doc_no = E.doc_no AND A.ou_id = E.ou_id AND A.tenant_id = E.tenant_id
287 INNER JOIN i_trx_pos_item_nempil_barang F on A.process_no = F.process_no and A.tenant_id = F.tenant_id and A.trx_pos_id = F.trx_pos_id
288 INNER JOIN p_nempil_barang_balance_stock G on F.product_code = G.product_code AND A.ou_id = G.ou_id
289
290 INNER JOIN t_user_role Z ON Z.user_id = $6 AND Z.role_id = $7
291 INNER JOIN t_policy_ou Y ON A.ou_id = Y.ou_id AND Z.policy_id = Y.policy_id
292 LEFT OUTER JOIN i_trx_pos_cash_payment H on A.process_no = H.process_no and A.tenant_id = H.tenant_id and A.trx_pos_id = H.trx_pos_id
293 LEFT OUTER JOIN summed_i_trx_pos_non_cash_payment I on A.process_no = I.process_no and A.tenant_id = I.tenant_id and A.trx_pos_id = I.trx_pos_id
294 LEFT OUTER JOIN summed_i_trx_pos_transfer_payment M on A.process_no = M.process_no and A.tenant_id = M.tenant_id and A.trx_pos_id = M.trx_pos_id
295 LEFT OUTER JOIN i_trx_pos_termin_payment J on A.process_no = J.process_no and A.tenant_id = J.tenant_id and A.trx_pos_id = J.trx_pos_id
296
297 WHERE A.status = ''V''
298 AND A.tenant_id = $3
299 ' || vFilterOuId || '
300 AND E.doc_date BETWEEN $4 AND $5
301 AND $11 IN ($9, $10) '
302 USING pSessionId, vEmptyValue, pTenantId, pDateFrom, pDateTo, pUserId, pRoleId, vTypeDocPenjualan, vTypeDocReturn, vTypeDocAll, pTypeDoc;
303
304 /**
305 * mod by Didit, 2 Des 2016
306 * cash, non cash, dan piutang hanya terisi untuk baris yg pertama
307 * selain baris pertama di isi 0
308 */
309 UPDATE tr_daily_sales_report A
310 SET payment_amount_cash = 0,
311 payment_amount_non_cash = 0,
312 under_payment_amount = 0
313 WHERE A.product_id = -99
314 AND A.line_no = 1
315 AND EXISTS(
316 SELECT 1 FROM tr_daily_sales_report B WHERE A.doc_no = B.doc_no AND B.product_id <> -99 AND B.line_no = 1
317 );
318
319 -- nilai rounding POS
320 IF pTypeDoc = vTypeDocAll OR pTypeDoc = vTypeDocPenjualan THEN
321 EXECUTE 'INSERT INTO tr_daily_sales_report (
322 session_id, ou_code, ou_name, year,
323 month, date, doc_no, vehicle_brand,
324 vehicle_type, year_made, customer_id, customer_name,
325 salesman_name, mekanik_name,
326 product_code, product_name, ctgr_product_name, qty, curr_code,
327 modal_price, gross_sell_price, discount_amount,
328 nett_sell_price, curr_payment_code_cash, payment_amount_cash,
329 curr_payment_code_non_cash, payment_amount_non_cash,
330 curr_payment_code_transfer, payment_amount_transfer,
331 curr_under_payment, under_payment_amount,
332 tenant_id, ou_id, doc_date, product_id, line_no,
333 tax_price, omzet_bruto_amount, tax_amount, nett_item_amount, doc_type_desc, group_brand, parent_group_brand)
334 SELECT $1, B.ou_code, B.ou_name, SUBSTRING(A.doc_date, 1, 4) AS year,
335 SUBSTRING(A.doc_date, 5, 2) AS month, SUBSTRING(A.doc_date, 7, 2) AS date, A.doc_no, $2 AS vehicle_brand,
336 $2 AS vehicle_type, $2 AS year_made, A.partner_id, COALESCE(f_get_partner_name(A.partner_id), ''UMUM'') AS customer_name,
337 COALESCE(f_get_partner_name(A.salesman_id), $2) AS salesman_name, COALESCE(f_get_partner_name(C.mekanik_id), $2) AS mekanik_name,
338 ''ROUNDING'' AS product_code, ''ROUNDING'' AS product_name, ''ROUNDING'' AS ctgr_product_name, 1, COALESCE(A.curr_code, $2),
339 0, A.rounding_amount, 0,
340 A.rounding_amount, COALESCE(A.curr_code, $2) AS curr_payment_code_cash, CASE WHEN A.rounding_amount < 0 THEN A.rounding_amount ELSE 0 END AS payment_amount_cash,
341 COALESCE(A.curr_code, $2) AS curr_payment_code_non_cash, 0 AS payment_amount_non_cash,
342 COALESCE(A.curr_code, $2) AS curr_payment_code_transfer, 0 AS payment_amount_transfer,
343 COALESCE(A.curr_code, $2) AS curr_under_payment, 0 AS under_payment_amount,
344 A.tenant_id, B.ou_id, A.doc_date, -99, -1,
345 0, CASE WHEN A.rounding_amount < 0 THEN A.rounding_amount ELSE 0 END AS omzet_bruto_amount, 0, A.rounding_amount, f_get_doc_desc(A.doc_type_id) AS doc_type_desc,
346 $2, $2
347 FROM i_trx_pos A
348 INNER JOIN t_ou B on A.ou_id = B.ou_id
349 INNER JOIN i_trx_pos_custom C on C.process_no = A.process_no and C.tenant_id = A.tenant_id and C.trx_pos_id = A.trx_pos_id
350
351 INNER JOIN t_user_role Z ON Z.user_id = $6 AND Z.role_id = $7
352 INNER JOIN t_policy_ou Y ON A.ou_id = Y.ou_id AND Z.policy_id = Y.policy_id
353 WHERE A.tenant_id = $3
354 AND A.rounding_amount <> 0
355 ' || vFilterOuId || '
356 AND A.doc_date BETWEEN $4 AND $5'
357 USING pSessionId, vEmptyValue, pTenantId, pDateFrom, pDateTo, pUserId, pRoleId;
358 END IF;
359
360 -- nilai rounding POS Void
361 IF pTypeDoc = vTypeDocAll OR pTypeDoc = vTypeDocReturn THEN
362 EXECUTE 'INSERT INTO tr_daily_sales_report (
363 session_id, ou_code, ou_name, year,
364 month, date, doc_no, vehicle_brand,
365 vehicle_type, year_made, customer_id, customer_name,
366 salesman_name, mekanik_name,
367 product_code, product_name, ctgr_product_name, qty, curr_code,
368 modal_price, gross_sell_price, discount_amount,
369 nett_sell_price, curr_payment_code_cash, payment_amount_cash,
370 curr_payment_code_non_cash, payment_amount_non_cash,
371 curr_payment_code_transfer, payment_amount_transfer,
372 curr_under_payment, under_payment_amount,
373 tenant_id, ou_id, doc_date, product_id, line_no,
374 tax_price, omzet_bruto_amount, tax_amount, nett_item_amount, doc_type_desc, group_brand, parent_group_brand)
375 SELECT $1, B.ou_code, B.ou_name, SUBSTRING(E.doc_date, 1, 4) AS year,
376 SUBSTRING(E.doc_date, 5, 2) AS month, SUBSTRING(E.doc_date, 7, 2) AS date, A.doc_no, $2 AS vehicle_brand,
377 $2 AS vehicle_type, $2 AS year_made, A.partner_id, COALESCE(f_get_partner_name(A.partner_id), ''UMUM'') AS customer_name,
378 COALESCE(f_get_partner_name(A.salesman_id), $2) AS salesman_name, COALESCE(f_get_partner_name(C.mekanik_id), $2) AS mekanik_name,
379 ''ROUNDING'' AS product_code, ''ROUNDING'' AS product_name, ''ROUNDING'' AS ctgr_product_name, 1, COALESCE(A.curr_code, $2),
380 0, 0, 0,
381 A.rounding_amount, COALESCE(A.curr_code, $2) AS curr_payment_code_cash, CASE WHEN A.rounding_amount < 0 THEN A.rounding_amount*(-1) ELSE 0 END AS payment_amount_cash,
382 COALESCE(A.curr_code, $2) AS curr_payment_code_non_cash, 0 AS payment_amount_non_cash,
383 COALESCE(A.curr_code, $2) AS curr_payment_code_transfer, 0 AS payment_amount_transfer,
384 COALESCE(A.curr_code, $2) AS curr_under_payment, 0 AS under_payment_amount,
385 A.tenant_id, B.ou_id, E.doc_date, -99, -1,
386 0, CASE WHEN A.rounding_amount < 0 THEN A.rounding_amount*(-1) ELSE 0 END AS omzet_bruto_amount, 0, A.rounding_amount, f_get_doc_desc(A.doc_type_id) AS doc_type_desc,
387 $2, $2
388 FROM i_trx_pos A
389 INNER JOIN t_ou B on A.ou_id = B.ou_id
390 INNER JOIN i_trx_pos_custom C on C.process_no = A.process_no and C.tenant_id = A.tenant_id and C.trx_pos_id = A.trx_pos_id
391 INNER JOIN i_trx_log_voided_pos_custom E ON A.doc_no = E.doc_no AND A.ou_id = E.ou_id AND A.tenant_id = E.tenant_id
392
393 INNER JOIN t_user_role Z ON Z.user_id = $6 AND Z.role_id = $7
394 INNER JOIN t_policy_ou Y ON A.ou_id = Y.ou_id AND Z.policy_id = Y.policy_id
395 WHERE A.status = ''V''
396 AND A.tenant_id = $3
397 AND A.rounding_amount <> 0
398 ' || vFilterOuId || '
399 AND E.doc_date BETWEEN $4 AND $5'
400 USING pSessionId, vEmptyValue, pTenantId, pDateFrom, pDateTo, pUserId, pRoleId;
401 END IF;
402
403 --dokumen SI, nilai amountnya masuk dalam amount piutang
404 IF pTypeDoc = vTypeDocAll OR pTypeDoc = vTypeDocPenjualan THEN
405 EXECUTE '
406 INSERT INTO tr_daily_sales_report (
407 session_id, ou_code, ou_name, year,
408 month, date, doc_no, vehicle_brand,
409 vehicle_type, year_made, customer_id, customer_name,
410 salesman_name, mekanik_name,
411 product_code, product_name, ctgr_product_name, qty, curr_code,
412 modal_price, gross_sell_price, discount_amount,
413 nett_sell_price, curr_payment_code_cash, payment_amount_cash,
414 curr_payment_code_non_cash, payment_amount_non_cash,
415 curr_payment_code_transfer, payment_amount_transfer,
416 curr_under_payment, under_payment_amount,
417 tenant_id, ou_id, doc_date, product_id, line_no,
418 tax_price, omzet_bruto_amount, tax_amount, nett_item_amount, doc_type_desc, group_brand, parent_group_brand)
419 SELECT $1, B.ou_code, B.ou_name, SUBSTRING(A.doc_date, 1, 4) AS year,
420 SUBSTRING(A.doc_date, 5, 2) AS month, SUBSTRING(A.doc_date, 7, 2) AS date, A.doc_no, $2 AS vehicle_brand,
421 $2 AS vehicle_type, $2 AS year_made, A.partner_id, COALESCE(f_get_partner_name(A.partner_id), ''UMUM'') AS customer_name,
422 COALESCE(f_get_partner_name(A.salesman_id), $2) AS salesman_name, $2 AS mekanik_name,
423 E.product_code, E.product_name, f_get_ctgr_product_name(E.ctgr_product_id) AS ctgr_product_name, SUM(J.qty_dlv_so) AS qty, H.curr_code,
424 0 AS modal_price, (H.gross_sell_price - H.discount_amount) AS gross_sell_price, H.discount_amount AS discount_amount,
425 H.nett_sell_price, H.curr_code AS curr_payment_code_cash, 0 AS payment_amount_cash,
426 H.curr_code AS curr_payment_code_non_cash, 0 AS payment_amount_non_cash,
427 H.curr_code AS curr_payment_code_transfer, 0 AS payment_amount_transfer,
428 H.curr_code AS curr_under_payment,
429 SUM((J.item_amount - (J.regular_disc_amount + J.promo_disc_amount + J.adj_regular_disc_amount + J.adj_promo_disc_amount)) + I.tax_amount) AS under_payment_amount,
430 A.tenant_id, B.ou_id, A.doc_date, E.product_id, H.line_no,
431 H.tax_price, SUM((J.item_amount - (J.regular_disc_amount + J.promo_disc_amount + J.adj_regular_disc_amount + J.adj_promo_disc_amount)) + I.tax_amount) AS omzet_bruto_amount,
432 SUM(I.tax_amount) AS tax_amount, SUM(J.item_amount - (J.regular_disc_amount + J.promo_disc_amount + J.adj_regular_disc_amount + J.adj_promo_disc_amount)) AS nett_item_amount,
433 f_get_doc_desc(A.doc_type_id) AS doc_type_desc,
434 P.group_brand_code ||''-''|| P.group_brand_name AS group_brand, P.parent_group_brand
435 FROM sl_invoice A
436 INNER JOIN t_ou B ON A.ou_id = B.ou_id
437 INNER JOIN sl_invoice_item C ON C.invoice_id = A.invoice_id
438 INNER JOIN sl_do_item D ON C.ref_id = D.do_id AND C.ref_item_id = D.do_item_id
439 INNER JOIN m_product E ON D.product_id = E.product_id
440 INNER JOIN sl_so_item H ON A.ref_id = H.so_id AND D.ref_id = H.so_item_id
441 INNER JOIN sl_so_balance_invoice J ON J.ref_doc_type_id = C.ref_doc_type_id
442 AND J.ref_id = C.ref_id
443 AND J.ref_item_id = C.ref_item_id
444 AND J.do_receipt_item_id = C.do_receipt_item_id
445 INNER JOIN sl_so_balance_invoice_tax I ON I.ref_doc_type_id = C.ref_doc_type_id
446 AND I.ref_id = C.ref_id
447 AND I.ref_item_id = C.ref_item_id
448 AND I.do_receipt_item_id = C.do_receipt_item_id
449
450 INNER JOIN m_product_custom_for_sasa O on E.product_id = O.product_id
451 INNER JOIN m_group_brand P ON O.group_brand_id = P.group_brand_id
452
453
454 INNER JOIN t_user_role Z ON Z.user_id = $6 AND Z.role_id = $7
455 INNER JOIN t_policy_ou Y ON A.ou_id = Y.ou_id AND Z.policy_id = Y.policy_id
456 WHERE A.tenant_id = $3
457 AND A.status_doc = ''R''
458 AND A.doc_type_id = $8
459 AND A.doc_date BETWEEN $4 AND $5
460 ' || vFilterOuId || '
461 GROUP BY B.ou_code, B.ou_name, A.doc_date, A.doc_no, A.partner_id, A.salesman_id, E.product_id, E.ctgr_product_id,
462 H.gross_sell_price, H.nett_sell_price, H.curr_code, A.tenant_id, B.ou_id, A.doc_date, E.product_id,
463 H.line_no, H.tax_price, H.discount_amount, doc_type_desc, group_brand, parent_group_brand
464 HAVING SUM(J.qty_dlv_so) <> 0 '
465 USING pSessionId, vEmptyValue, pTenantId, pDateFrom, pDateTo, pUserId, pRoleId, vDocTypeSI;
466 END IF;
467
468 --Dokumen Return Sales Invoice, sebagai pengurang piutang
469 IF pTypeDoc = vTypeDocAll OR pTypeDoc = vTypeDocReturn THEN
470 EXECUTE '
471 INSERT INTO tr_daily_sales_report (
472 session_id, ou_code, ou_name, year,
473 month, date, doc_no, vehicle_brand,
474 vehicle_type, year_made, customer_id, customer_name,
475 salesman_name, mekanik_name,
476 product_code, product_name, ctgr_product_name, qty, curr_code,
477 modal_price, gross_sell_price, discount_amount,
478 nett_sell_price, curr_payment_code_cash, payment_amount_cash,
479 curr_payment_code_non_cash, payment_amount_non_cash,
480 curr_payment_code_transfer, payment_amount_transfer,
481 curr_under_payment, under_payment_amount,
482 tenant_id, ou_id, doc_date, product_id, line_no,
483 tax_price, omzet_bruto_amount, tax_amount, nett_item_amount, doc_type_desc, group_brand, parent_group_brand)
484 SELECT $1, I.ou_code, I.ou_name, SUBSTRING(A.doc_date, 1, 4) AS year,
485 SUBSTRING(A.doc_date, 5, 2) AS month, SUBSTRING(A.doc_date, 7, 2) AS date, A.doc_no, $2 AS vehicle_brand,
486 $2 AS vehicle_type, $2 AS year_made, A.partner_id, f_get_partner_name(A.partner_id) AS customer_name,
487 f_get_partner_name(A.salesman_id) AS salesman_name, $2 AS mekanik_name,
488 E.product_code AS product_code, E.product_name AS product_name,
489 f_get_ctgr_product_name(E.ctgr_product_id) AS ctgr_product_name, SUM(C.qty_dlv_so)*(-1) AS qty, H.curr_code,
490 0 AS modal_price, (H.gross_sell_price - H.discount_amount) AS gross_sell_price, H.discount_amount AS discount_amount,
491 H.nett_sell_price, H.curr_code AS curr_payment_code_cash, 0 AS payment_amount_cash,
492 H.curr_code AS curr_payment_code_non_cash, 0 AS payment_amount_non_cash,
493 H.curr_code AS curr_payment_code_transfer, 0 AS payment_amount_transfer,
494 H.curr_code AS curr_under_payment,
495 SUM((C.item_amount - (C.regular_disc_amount + C.promo_disc_amount + C.adj_regular_disc_amount + COALESCE(C.adj_promo_disc_amount,0))) + F.tax_amount)*-1 AS under_payment_amount,
496 A.tenant_id, A.ou_id, A.doc_date, D.product_id, H.line_no, H.tax_price,
497 SUM((C.item_amount - (C.regular_disc_amount + C.promo_disc_amount + C.adj_regular_disc_amount + COALESCE(C.adj_promo_disc_amount,0))) + F.tax_amount)*-1 AS omzet_bruto_amount,
498 SUM(F.tax_amount)*-1 AS tax_amount, SUM(C.item_amount - (C.regular_disc_amount + C.promo_disc_amount + C.adj_regular_disc_amount + COALESCE(C.adj_promo_disc_amount,0)))*-1 AS nett_item_amount,
499 f_get_doc_desc(A.doc_type_id) AS doc_type_desc, P.group_brand_code ||''-''|| P.group_brand_name AS group_brand, P.parent_group_brand
500 FROM sl_invoice A
501 INNER JOIN sl_invoice_item B ON A.invoice_id = B.invoice_id AND A.tenant_id = B.tenant_id
502 INNER JOIN sl_so_balance_invoice C ON B.ref_id = C.ref_id AND B.ref_doc_type_id = C.ref_doc_type_id AND B.ref_item_id = C.ref_item_id
503 INNER JOIN in_inventory_item D ON D.inventory_id = C.ref_id AND D.ref_item_id = C.ref_item_id
504 INNER JOIN m_product E ON D.product_id = E.product_id
505
506 INNER JOIN m_product_custom_for_sasa O on E.product_id = O.product_id
507 INNER JOIN m_group_brand P ON O.group_brand_id = P.group_brand_id
508
509 INNER JOIN sl_so_balance_invoice_tax F ON B.ref_id = F.ref_id AND B.ref_doc_type_id = F.ref_doc_type_id AND B.ref_item_id = F.ref_item_id
510 INNER JOIN sl_do_item G ON D.ref_id = G.do_id AND D.ref_item_id = G.do_item_id
511 INNER JOIN sl_so_item H ON C.so_id = H.so_id AND G.ref_id = H.so_item_id
512 INNER JOIN t_ou I ON A.ou_id = I.ou_id
513 INNER JOIN t_user_role Z ON Z.user_id = $6 AND Z.role_id = $7
514 INNER JOIN t_policy_ou Y ON A.ou_id = Y.ou_id AND Z.policy_id = Y.policy_id
515 WHERE A.tenant_id = $3
516 AND A.status_doc = ''R''
517 AND A.doc_date BETWEEN $4 AND $5
518 ' || vFilterOuId || '
519 AND A.doc_type_id = $8
520 GROUP BY I.ou_code, I.ou_name, A.doc_date, A.doc_no, A.partner_id, A.salesman_id, E.product_code, E.product_name,
521 E.ctgr_product_id, H.gross_sell_price, H.discount_amount, H.nett_sell_price, H.curr_code, A.tenant_id, A.ou_id, D.product_id,
522 H.line_no, H.tax_price, doc_type_desc, group_brand, parent_group_brand '
523 USING pSessionId, vEmptyValue, pTenantId, pDateFrom, pDateTo, pUserId, pRoleId, vDocTypeReturnSI;
524 END IF;
525
526 --update hppa (modal price)
527 WITH product_cogs AS (
528 SELECT D.tenant_id, D.ou_id, MAX(D.date_year_month) AS year_month_date, D.product_id, D.avg_price
529 FROM in_summary_monthly_cogs D
530 INNER JOIN m_ou_structure E ON E.ou_bu_id = D.ou_id
531 GROUP BY D.tenant_id, D.ou_id, D.product_id, D.avg_price
532 )
533 UPDATE tr_daily_sales_report B
534 SET modal_price = COALESCE(A.avg_price, 0)
535 FROM product_cogs A
536 INNER JOIN m_ou_structure C ON C.ou_bu_id = A.ou_id
537 WHERE B.session_id = pSessionId
538 AND B.product_id = A.product_id
539 AND B.tenant_id = A.tenant_id
540 AND A.year_month_date <= SUBSTRING(B.doc_date, 1, 6)
541 AND C.ou_id = B.ou_id
542 AND B.modal_price = 0;
543
544 Open pRefHeader FOR
545 SELECT pTenantId;
546 RETURN NEXT pRefHeader;
547
548 Open pRefDetail FOR
549 SELECT A.ou_code, A.ou_name, A.year, A.month, A.date, A.doc_no, A.vehicle_brand, A.vehicle_type, A.year_made, B.partner_code, A.customer_name, A.salesman_name, A.mekanik_name,
550 A.product_code, A.product_name, A.ctgr_product_name, A.qty,
551 A.curr_code, A.modal_price, A.gross_sell_price, A.discount_amount, A.nett_sell_price, A.curr_payment_code_cash, A.payment_amount_cash,
552 A.curr_payment_code_non_cash, A.payment_amount_non_cash, A.curr_payment_code_transfer, A.payment_amount_transfer, A.curr_under_payment, A.under_payment_amount,
553 A.tax_price, A.omzet_bruto_amount, A.tax_amount, A.nett_item_amount, doc_type_desc, group_brand, parent_group_brand,
554 CASE WHEN B.partner_name = 'ECERAN CASH' AND A.payment_amount_cash <> 0 AND A.mekanik_name IN (vEmptySpaceValue, vEmptyValue) THEN 'WIC'
555 WHEN B.partner_name = 'ECERAN CASH' AND A.payment_amount_cash <> 0 AND A.mekanik_name NOT IN (vEmptySpaceValue, vEmptyValue) THEN 'Bengkel Umum'
556 WHEN B.price_level = 'PROSAS' THEN 'Prosas'
557 WHEN B.price_level = 'OJOL' THEN 'Ojol'
558 WHEN C.ctgr_partner_code = 'PSMOBILE' THEN 'Psmobile'
559 WHEN C.ctgr_partner_code = 'INTERNAL' THEN 'Inventaris Kantor'
560 WHEN C.ctgr_partner_code = 'KHUSUS' THEN 'Korporasi'
561 WHEN C.ctgr_partner_code = 'UMUM' AND A.under_payment_amount <> 0 THEN 'Umum Kredit'
562 ELSE vEmptyValue
563 END AS remark,
564 CASE WHEN doc_type_desc = vDocDescPosShop
565 THEN CASE WHEN under_payment_amount > 0 THEN vKredit ELSE vTunai END
566 ELSE vKredit END AS flg_lunas
567 FROM tr_daily_sales_report A
568 INNER JOIN m_partner B ON A.customer_id = B.partner_id
569 INNER JOIN m_ctgr_partner C ON B.ctgr_partner_id = C.ctgr_partner_id
570 WHERE session_id = pSessionId
571 ORDER BY ou_name, year, month, date, doc_no, line_no;
572 RETURN NEXT pRefDetail;
573
574 DELETE FROM tr_daily_sales_report WHERE session_id = pSessionId;
575 DELETE FROM tt_in_latest_purchasing_price_by_date WHERE session_id = pSessionId;
576 DELETE FROM tt_out_latest_purchasing_price_by_date WHERE session_id = pSessionId;
577
578END;
579$BODY$
580 LANGUAGE plpgsql VOLATILE
581 COST 100
582 ROWS 1000;
583/