· 4 years ago · Mar 23, 2021, 03:26 AM
1CREATE OR REPLACE FUNCTION f_generate_po_and_rg_supplier_pkp(bigint, bigint, character varying, character varying, character varying, character varying, bigint, character varying)
2 RETURNS void AS
3$BODY$
4DECLARE
5 pTenantId ALIAS FOR $1;
6 pOuId ALIAS FOR $2;
7 pSessionId ALIAS FOR $3;
8 pStartDate ALIAS FOR $4;
9 pEndDate ALIAS FOR $5;
10 pPeriodProcess ALIAS FOR $6;
11 pUserId ALIAS FOR $7;
12 pDatetime ALIAS FOR $8;
13
14 vEmptyId BIGINT := -99;
15 vEmptyString CHARACTER VARYING := '';
16 vEmptyNumeric NUMERIC := 0;
17 vReleaseDoc CHARACTER VARYING := 'R';
18 vStock CHARACTER VARYING := 'Y';
19 vFilterPartner TEXT := '';
20 vDocTypeDo BIGINT := 311;
21 vDocTypeDoReceipt BIGINT := 526;
22 vDocTypeReturnNote BIGINT := 502;
23 vYes CHARACTER VARYING := 'Y';
24
25 vWarehouseId bigint;
26 vPurchaserId bigint;
27 vRoundingModeNonTax character varying(5);
28 vCurrCode character varying := ' ';
29 vProductStatus character varying := ' ';
30 vTopCode character varying := ' ';
31
32 vStatusDraft character varying := 'D';
33 vStatusRelease character varying := 'R';
34 vStatusFinal character varying := 'F';
35 vFlagYes character varying := 'Y';
36 vFlagNo character varying := 'N';
37 vSignDebit character varying := 'D';
38 vSignCredit character varying := 'C';
39 vActivityCOA character varying := 'ACTIVITY';
40 vProductCOA character varying := 'PRODUCT';
41 vSystemCOA character varying := 'SYSTEM';
42 vTypeRate character varying := 'COM';
43
44 vDocTypeIdPo bigint := 101;
45 vDocTypeIdRg bigint := 111;
46 vDocTypeIdPoCommSheet bigint := 106;
47
48 vReceiveGoodsManualDocTypeId bigint := 114;
49
50 vDocNoDefaultPo character varying := ' ';
51 vDocNoDefaultRg character varying := ' ';
52 vDocNoDefaultPoReturn character varying;
53 vDocNoDefaultRgReturn character varying;
54
55 vPrefixDocNoVatNormal character varying := '010';
56 vWorkflowStatusApproved character varying(10) := 'APPROVED';
57
58 vRemarkPo character varying := 'PO Auto Generated';
59 vRemarkRg character varying := 'Receive Goods Auto Generated';
60 vEmptyValue character varying := ' ';
61 vValReleasedTrx bigint;
62 vValMagento bigint;
63
64 vDocJournal DOC_JOURNAL%ROWTYPE;
65 vOuStructure OU_BU_STRUCTURE%ROWTYPE;
66 result RECORD;
67
68 vYearMonth character varying(7);
69 vProcessName character varying := 'pu_create_doc_buy_from_consignment_sold';
70
71 vStatusGenerate character varying := 'S';
72 vTaxPercentage numeric;
73 vTaxId bigint;
74BEGIN
75
76 DELETE FROM tr_report_sales_supplier WHERE session_id = pSessionId;
77 DELETE FROM tt_generate_po_rg_for_journal WHERE session_id = pSessionId;
78 DELETE FROM tt_summary_item_penjualan_by_period WHERE session_id = pSessionId;
79 DELETE FROM tt_summary_item_return_by_period WHERE session_id = pSessionId;
80 DELETE FROM tt_monthly_supplier_po_rg WHERE session_id = pSessionId;
81 DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;
82
83 -------------------------------------------------------------------------------------------------
84 -- Ambil nilai-nilai untuk variabel-variabel tertentu
85 -------------------------------------------------------------------------------------------------
86 SELECT f_get_value_system_config_by_param_code(pTenantId, 'WHS.RECEIVE') INTO vWarehouseId;
87 SELECT f_get_value_system_config_by_param_code(pTenantId, 'PURCHASE.OFFICER.ID') INTO vPurchaserId;
88 SELECT f_get_value_system_config_by_param_code(pTenantId, 'rounding.mode.non.tax') INTO vRoundingModeNonTax;
89 SELECT f_get_value_system_config_by_param_code(pTenantId, 'ValutaBuku') INTO vCurrCode;
90
91 SELECT product_status_code INTO vProductStatus FROM m_product_status WHERE flg_buy = 'Y'; -- product status
92 SELECT prop_key INTO vTopCode FROM t_combo_value WHERE combo_id = 'TOPTYPE' AND code = 'TOP'; -- top
93
94 SELECT tax_id, percentage INTO vTaxId, vTaxPercentage FROM m_tax WHERE tenant_id = pTenantId AND tax_code = 'PPn';
95
96 SELECT concat('PO/', pPeriodProcess, '/') INTO vDocNoDefaultPo;
97 SELECT concat('GR/', pPeriodProcess, '/') INTO vDocNoDefaultRg;
98 SELECT concat('POR/', pPeriodProcess, '/') INTO vDocNoDefaultPoReturn;
99 SELECT concat('GRR/', pPeriodProcess, '/') INTO vDocNoDefaultRgReturn;
100
101 SELECT f_get_ou_bu_structure(pOuId) AS ou, f_get_document_journal(vDocTypeIdRg) AS doc INTO result;
102 vDocJournal := result.doc;
103 vOuStructure := result.ou;
104
105 SELECT val_unsubmit_trx_generate_po_rg_for_cland(pTenantId, pSessionId, pOuId, pPeriodProcess, pStartDate, pEndDate, pDatetime, pUserId, vProcessName) INTO vValReleasedTrx;
106 SELECT val_check_error_magento_trx_for_generate_po_rg_for_cland(pTenantId, pSessionId, pOuId, pDatetime, pUserId, vProcessName, pStartDate, pEndDate) INTO vValMagento;
107
108 IF vValReleasedTrx = 0 THEN
109
110 IF vValMagento = 0 THEN
111 /*
112 * insert data into table tr_report_sales_supplier for DO from in_log_product_balance_stock
113 */
114 EXECUTE '
115 INSERT INTO tr_report_sales_supplier(
116 session_id, tenant_id, ou_id, partner_id, so_item_id, transaction_type, doc_no,
117 doc_date, payment_method, product_id, product_code, supplier_product_code,
118 product_name, product_style, color, psize, normal_price, sold_price,
119 discount, margin_supp, sold_price_after_margin, qty_so, product_balance_id)
120 SELECT $1, $2, $3, C.supplier_id, E.ref_id, f_get_doc_desc(A.doc_type_id), A.doc_no,
121 A.doc_date, $4, A.product_id, f_get_product_code(A.product_id), C.supplier_product_code,
122 f_get_product_name(A.product_id), B.style_product, B.color, B.size, F.normal_price_correction, F.sold_price_used,
123 F.discount, F.margin_supp_correction, F.sold_price_after_margin, SUM(A.qty) * -1, A.product_balance_id
124 FROM in_log_product_balance_stock A
125 INNER JOIN m_product_custom B ON A.product_id = B.product_id
126 INNER JOIN m_product_consignment_supp_info C ON A.product_id = C.product_id
127 INNER JOIN sl_do D ON A.ref_id = D.do_id
128 INNER JOIN sl_do_item E ON D.do_id = E.do_id AND A.product_id = E.product_id
129 INNER JOIN pu_po_balance_item_consignment_sold F ON E.ref_id = F.so_item_id AND C.supplier_id = F.supplier_id
130 WHERE A.tenant_id = $2
131 AND A.doc_date BETWEEN $5 AND $6
132 AND A.ou_id = $3
133 AND B.flg_buy_konsinyasi = $7
134 AND A.doc_type_id = $8 ' ||
135 vFilterPartner || '
136 AND F.ref_doc_type_id = -99 AND F.ref_id = -99
137 AND F.from_manual = $9
138 GROUP BY C.supplier_id, E.ref_id, A.doc_type_id, A.doc_no, A.doc_date, A.product_id, C.supplier_product_code, B.style_product, B.color, B.size,
139 F.normal_price_correction, F.sold_price_used, F.discount, F.margin_supp_correction, F.sold_price_after_margin, A.product_balance_id
140 ' USING pSessionId, pTenantId, pOuId, vEmptyString, pStartDate, pEndDate, vYes, vDocTypeDo, vFlagNo;
141
142 -- insert data into table tr_report_sales_supplier for GR Manual Consignment
143 EXECUTE '
144 INSERT INTO tr_report_sales_supplier(
145 session_id, tenant_id, ou_id, partner_id, so_item_id, transaction_type, doc_no,
146 doc_date, payment_method, product_id, product_code, supplier_product_code,
147 product_name, product_style, color, psize, normal_price, sold_price,
148 discount, margin_supp, sold_price_after_margin, qty_so)
149 SELECT $1, $2, $3, C.supplier_id,A.so_item_id, f_get_doc_desc($10),
150 B.doc_no,
151 B.doc_date, $4,
152 C.product_id, f_get_product_code(C.product_id), C.supplier_product_code,
153 f_get_product_name(C.product_id), E.style_product, E.color, E.size,
154 A.normal_price_correction, A.sold_price_used,
155 A.discount, A.margin_supp_correction, A.sold_price_after_margin, SUM(D.qty_rcv_po)
156 FROM pu_po_balance_item_consignment_sold A
157 INNER JOIN pu_receive_goods B ON A.receive_goods_id = B.receive_goods_id
158 INNER JOIN pu_receive_goods_item D ON B.receive_goods_id = D.receive_goods_id AND A.receive_goods_item_id = D.receive_goods_item_id
159 INNER JOIN m_product_consignment_supp_info C ON C.product_id = D.product_id
160 INNER JOIN m_product_custom E ON E.product_id = C.product_id
161 WHERE B.tenant_id = $2
162 AND B.doc_date BETWEEN $5 AND $6
163 AND B.ou_id = $3
164 AND E.flg_buy_konsinyasi = $7
165 AND B.doc_type_id = $8 ' || vFilterPartner || '
166 AND A.from_manual = $7
167 GROUP BY C.supplier_id, A.so_item_id, B.doc_type_id, B.doc_no, B.doc_date, C.product_id,
168 C.supplier_product_code, E.style_product, E.color, E.size,
169 A.normal_price_correction, A.sold_price_used, A.discount, A.margin_supp_correction, A.sold_price_after_margin
170 ' USING pSessionId, pTenantId, pOuId, vEmptyString, pStartDate, pEndDate, vFlagYes, vReceiveGoodsManualDocTypeId, vYes, vDocTypeDo;
171
172 /*
173 * insert data into table tr_report_sales_supplier for DO from in_log_product_consignment_balance_stock
174 */
175 EXECUTE '
176 INSERT INTO tr_report_sales_supplier(
177 session_id, tenant_id, ou_id, partner_id, so_item_id, transaction_type, doc_no,
178 doc_date, payment_method, product_id, product_code, supplier_product_code,
179 product_name, product_style, color, psize, normal_price, sold_price,
180 discount, margin_supp, sold_price_after_margin, qty_so, product_balance_id)
181 SELECT $1, $2, $3, C.supplier_id, E.ref_id, f_get_doc_desc(A.doc_type_id), A.doc_no,
182 A.doc_date, $4, A.product_id, f_get_product_code(A.product_id), C.supplier_product_code,
183 f_get_product_name(A.product_id), B.style_product, B.color, B.size, F.normal_price_correction, F.sold_price_used,
184 F.discount, F.margin_supp_correction, F.sold_price_after_margin, SUM(A.qty) * -1, A.product_balance_id
185 FROM in_log_product_consignment_balance_stock A
186 INNER JOIN m_product_custom B ON A.product_id = B.product_id
187 INNER JOIN m_product_consignment_supp_info C ON A.product_id = C.product_id
188 INNER JOIN sl_do D ON A.ref_id = D.do_id
189 INNER JOIN sl_do_item E ON D.do_id = E.do_id AND A.product_id = E.product_id
190 INNER JOIN pu_po_balance_item_consignment_sold F ON E.ref_id = F.so_item_id AND C.supplier_id = F.supplier_id
191 WHERE A.tenant_id = $2
192 AND A.doc_date BETWEEN $5 AND $6
193 AND A.ou_id = $3
194 AND B.flg_buy_konsinyasi = $7
195 AND A.doc_type_id = $8 ' ||
196 vFilterPartner || '
197 AND F.ref_doc_type_id = -99 AND F.ref_id = -99
198 AND F.from_manual = $9
199 GROUP BY C.supplier_id, E.ref_id, A.doc_type_id, A.doc_no, A.doc_date, A.product_id, C.supplier_product_code, B.style_product, B.color, B.size,
200 F.normal_price_correction, F.sold_price_used, F.discount, F.margin_supp_correction, F.sold_price_after_margin, A.product_balance_id
201 ' USING pSessionId, pTenantId, pOuId, vEmptyString, pStartDate, pEndDate, vYes, vDocTypeDo, vFlagNo;
202
203 /*
204 * insert data into table tr_report_sales_supplier for Return Note from in_log_product_balance_stock
205 */
206 EXECUTE '
207 INSERT INTO tr_report_sales_supplier(
208 session_id, tenant_id, ou_id, partner_id, so_item_id, transaction_type, doc_no,
209 doc_date, payment_method, product_id, product_code, supplier_product_code,
210 product_name, product_style, color, psize, normal_price, sold_price,
211 discount, margin_supp, sold_price_after_margin, qty_so, product_balance_id)
212 SELECT $1, $2, $3, C.supplier_id, E.ref_id, f_get_doc_desc(A.doc_type_id), A.doc_no,
213 A.doc_date, $4, A.product_id, f_get_product_code(A.product_id), C.supplier_product_code,
214 f_get_product_name(A.product_id), B.style_product, B.color, B.size, F.normal_price_correction, F.sold_price_used,
215 F.discount, F.margin_supp_correction, F.sold_price_after_margin, SUM(A.qty) * -1, A.product_balance_id
216 FROM in_log_product_balance_stock A
217 INNER JOIN m_product_custom B ON A.product_id = B.product_id
218 INNER JOIN m_product_consignment_supp_info C ON A.product_id = C.product_id
219 INNER JOIN in_inventory D ON A.ref_id = D.inventory_id
220 INNER JOIN in_inventory_item G ON D.inventory_id = G.inventory_id AND A.product_id = G.product_id
221 INNER JOIN sl_do_item E ON G.ref_item_id = E.do_item_id AND G.product_id = E.product_id
222 INNER JOIN pu_po_balance_item_consignment_sold F ON E.ref_id = F.so_item_id AND C.supplier_id = F.supplier_id
223 WHERE A.tenant_id = $2
224 AND A.doc_date BETWEEN $5 AND $6
225 AND A.ou_id = $3
226 AND B.flg_buy_konsinyasi = $7
227 AND A.doc_type_id IN ($9) ' ||
228 vFilterPartner || '
229 AND F.from_manual = $10
230 GROUP BY C.supplier_id, E.ref_id, A.doc_type_id, A.doc_no, A.doc_date, A.product_id, C.supplier_product_code, B.style_product, B.color, B.size,
231 F.normal_price_correction, F.sold_price_used, F.discount, F.margin_supp_correction, F.sold_price_after_margin, A.product_balance_id
232 ' USING pSessionId, pTenantId, pOuId, vEmptyString, pStartDate, pEndDate, vYes, vDocTypeDoReceipt, vDocTypeReturnNote, vFlagNo;
233
234 /*
235 * insert data into table tr_report_sales_supplier for DO Receipt from in_log_product_balance_stock
236 */
237 EXECUTE '
238 INSERT INTO tr_report_sales_supplier(
239 session_id, tenant_id, ou_id, partner_id, so_item_id, transaction_type, doc_no,
240 doc_date, payment_method, product_id, product_code, supplier_product_code,
241 product_name, product_style, color, psize, normal_price, sold_price,
242 discount, margin_supp, sold_price_after_margin, qty_so, product_balance_id)
243 SELECT $1, $2, $3, C.supplier_id, E.ref_id, f_get_doc_desc(A.doc_type_id), A.doc_no,
244 A.doc_date, $4, A.product_id, f_get_product_code(A.product_id), C.supplier_product_code,
245 f_get_product_name(A.product_id), B.style_product, B.color, B.size, F.normal_price_correction, F.sold_price_used,
246 F.discount, F.margin_supp_correction, F.sold_price_after_margin, SUM(A.qty) * -1, A.product_balance_id
247 FROM in_log_product_balance_stock A
248 INNER JOIN m_product_custom B ON A.product_id = B.product_id
249 INNER JOIN m_product_consignment_supp_info C ON A.product_id = C.product_id
250 INNER JOIN in_do_receipt D ON A.ref_id = D.do_receipt_id
251 INNER JOIN in_do_receipt_item G ON D.do_receipt_id = G.do_receipt_id AND A.product_id = G.product_id
252 INNER JOIN sl_do_item E ON G.ref_id = E.do_item_id AND G.product_id = E.product_id
253 INNER JOIN pu_po_balance_item_consignment_sold F ON E.ref_id = F.so_item_id AND C.supplier_id = F.supplier_id
254 WHERE A.tenant_id = $2
255 AND A.doc_date BETWEEN $5 AND $6
256 AND A.ou_id = $3
257 AND B.flg_buy_konsinyasi = $7
258 AND A.doc_type_id IN ($8) ' ||
259 vFilterPartner || '
260 AND F.from_manual = $10
261 GROUP BY C.supplier_id, E.ref_id, A.doc_type_id, A.doc_no, A.doc_date, A.product_id, C.supplier_product_code, B.style_product, B.color, B.size,
262 F.normal_price_correction, F.sold_price_used, F.discount, F.margin_supp_correction, F.sold_price_after_margin, A.product_balance_id
263 ' USING pSessionId, pTenantId, pOuId, vEmptyString, pStartDate, pEndDate, vYes, vDocTypeDoReceipt, vDocTypeReturnNote, vFlagNo;
264
265 /*
266 * insert data into table tr_report_sales_supplier for Return Note from in_log_product_consignment_balance_stock
267 */
268 EXECUTE '
269 INSERT INTO tr_report_sales_supplier(
270 session_id, tenant_id, ou_id, partner_id, so_item_id, transaction_type, doc_no,
271 doc_date, payment_method, product_id, product_code, supplier_product_code,
272 product_name, product_style, color, psize, normal_price, sold_price,
273 discount, margin_supp, sold_price_after_margin, qty_so, product_balance_id)
274 SELECT $1, $2, $3, C.supplier_id, E.ref_id, f_get_doc_desc(A.doc_type_id), A.doc_no,
275 A.doc_date, $4, A.product_id, f_get_product_code(A.product_id), C.supplier_product_code,
276 f_get_product_name(A.product_id), B.style_product, B.color, B.size, F.normal_price_correction, F.sold_price_used,
277 F.discount, F.margin_supp_correction, F.sold_price_after_margin, SUM(A.qty) * -1, A.product_balance_id
278 FROM in_log_product_consignment_balance_stock A
279 INNER JOIN m_product_custom B ON A.product_id = B.product_id
280 INNER JOIN m_product_consignment_supp_info C ON A.product_id = C.product_id
281 INNER JOIN in_inventory D ON A.ref_id = D.inventory_id
282 INNER JOIN in_inventory_item G ON D.inventory_id = G.inventory_id AND A.product_id = G.product_id
283 INNER JOIN sl_do_item E ON G.ref_item_id = E.do_item_id AND G.product_id = E.product_id
284 INNER JOIN pu_po_balance_item_consignment_sold F ON E.ref_id = F.so_item_id AND C.supplier_id = F.supplier_id
285 WHERE A.tenant_id = $2
286 AND A.doc_date BETWEEN $5 AND $6
287 AND A.ou_id = $3
288 AND B.flg_buy_konsinyasi = $7
289 AND A.doc_type_id IN ($9) ' ||
290 vFilterPartner || '
291 AND F.from_manual = $10
292 GROUP BY C.supplier_id, E.ref_id, A.doc_type_id, A.doc_no, A.doc_date, A.product_id, C.supplier_product_code, B.style_product, B.color, B.size,
293 F.normal_price_correction, F.sold_price_used, F.discount, F.margin_supp_correction, F.sold_price_after_margin, A.product_balance_id
294 ' USING pSessionId, pTenantId, pOuId, vEmptyString, pStartDate, pEndDate, vYes, vDocTypeDoReceipt, vDocTypeReturnNote, vFlagNo;
295
296 /*
297 * insert data into table tr_report_sales_supplier for DO Receipt from in_log_product_consignment_balance_stock
298 */
299 EXECUTE '
300 INSERT INTO tr_report_sales_supplier(
301 session_id, tenant_id, ou_id, partner_id, so_item_id, transaction_type, doc_no,
302 doc_date, payment_method, product_id, product_code, supplier_product_code,
303 product_name, product_style, color, psize, normal_price, sold_price,
304 discount, margin_supp, sold_price_after_margin, qty_so, product_balance_id)
305 SELECT $1, $2, $3, C.supplier_id, E.ref_id, f_get_doc_desc(A.doc_type_id), A.doc_no,
306 A.doc_date, $4, A.product_id, f_get_product_code(A.product_id), C.supplier_product_code,
307 f_get_product_name(A.product_id), B.style_product, B.color, B.size, F.normal_price_correction, F.sold_price_used,
308 F.discount, F.margin_supp_correction, F.sold_price_after_margin, SUM(A.qty) * -1, A.product_balance_id
309 FROM in_log_product_consignment_balance_stock A
310 INNER JOIN m_product_custom B ON A.product_id = B.product_id
311 INNER JOIN m_product_consignment_supp_info C ON A.product_id = C.product_id
312 INNER JOIN in_do_receipt D ON A.ref_id = D.do_receipt_id
313 INNER JOIN in_do_receipt_item G ON D.do_receipt_id = G.do_receipt_id AND A.product_id = G.product_id
314 INNER JOIN sl_do_item E ON G.ref_id = E.do_item_id AND G.product_id = E.product_id
315 INNER JOIN pu_po_balance_item_consignment_sold F ON E.ref_id = F.so_item_id AND C.supplier_id = F.supplier_id
316 WHERE A.tenant_id = $2
317 AND A.doc_date BETWEEN $5 AND $6
318 AND A.ou_id = $3
319 AND B.flg_buy_konsinyasi = $7
320 AND A.doc_type_id IN ($8) ' ||
321 vFilterPartner || '
322 AND F.from_manual = $10
323 GROUP BY C.supplier_id, E.ref_id, A.doc_type_id, A.doc_no, A.doc_date, A.product_id, C.supplier_product_code, B.style_product, B.color, B.size,
324 F.normal_price_correction, F.sold_price_used, F.discount, F.margin_supp_correction, F.sold_price_after_margin, A.product_balance_id
325 ' USING pSessionId, pTenantId, pOuId, vEmptyString, pStartDate, pEndDate, vYes, vDocTypeDoReceipt, vDocTypeReturnNote, vFlagNo;
326
327 -- hanya yang supplier PKP saja, delete semua data partner yang non PKP
328 DELETE FROM tr_report_sales_supplier A
329 WHERE NOT EXISTS (
330 SELECT 1 FROM m_partner_npwp B
331 WHERE A.partner_id = B.partner_id
332 AND B.flg_pkp = 'Y'
333 );
334
335
336 -------------------------------------------------------------------------------------------------
337 -- Buat summary penjualan & return untuk persiapan offset
338 -------------------------------------------------------------------------------------------------
339
340 INSERT INTO tt_summary_doc_sales_item_penjualan_by_period
341 (session_id, supplier_id, record_type,
342 product_id, product_balance_id,
343 qty, gross_price_po, tax_percentage)
344 SELECT pSessionId, A.partner_id, 'SALES',
345 A.product_id, A.product_balance_id,
346 SUM(A.qty_so), A.sold_price_after_margin, vTaxPercentage
347 FROM tr_report_sales_supplier A
348 INNER JOIN m_document B ON A.transaction_type = B.doc_desc
349 WHERE A.session_id = pSessionId
350 AND B.doc_type_id IN (vDocTypeDo)
351 GROUP BY A.partner_id, A.product_id, A.product_balance_id, A.sold_price_after_margin;
352
353 RAISE NOTICE '4';
354
355 INSERT INTO tt_summary_doc_return_item_penjualan_by_period
356 (session_id, supplier_id, record_type,
357 product_id, product_balance_id,
358 qty, gross_price_po, tax_percentage)
359 SELECT pSessionId, A.partner_id, 'RETURN',
360 A.product_id, A.product_balance_id,
361 SUM(A.qty_so), A.sold_price_after_margin, vTaxPercentage
362 FROM tr_report_sales_supplier A
363 INNER JOIN m_document B ON A.transaction_type = B.doc_desc
364 WHERE A.session_id = pSessionId
365 AND B.doc_type_id IN (vDocTypeReturnNote, vDocTypeDoReceipt)
366 GROUP BY A.partner_id, A.product_id, A.product_balance_id, A.sold_price_after_margin;
367
368 RAISE NOTICE '5';
369
370 WITH summary_item_sales AS
371 (
372 SELECT A.session_id, A.supplier_id, A.tax_percentage, A.product_id, SUM(ABS(A.qty * A.gross_price_po)) AS item_amount
373 FROM tt_summary_doc_sales_item_penjualan_by_period A
374 WHERE A.session_id = pSessionId
375 GROUP BY A.session_id, A.supplier_id, A.tax_percentage, A.product_id
376 ), summary_item_return AS
377 (
378 SELECT A.session_id, A.supplier_id, A.tax_percentage, A.product_id, SUM(ABS(A.qty * A.gross_price_po)) AS item_amount
379 FROM tt_summary_doc_return_item_penjualan_by_period A
380 WHERE A.session_id = pSessionId
381 GROUP BY A.session_id, A.supplier_id, A.tax_percentage, A.product_id
382 )
383 UPDATE tt_summary_doc_return_item_penjualan_by_period A
384 SET record_type = 'SALES'
385 WHERE EXISTS (
386 SELECT 1 FROM summary_item_sales B
387 INNER JOIN summary_item_return C ON B.session_id = C.session_id AND B.product_id = C.product_id AND B.supplier_id = C.supplier_id AND B.tax_percentage = C.tax_percentage
388 WHERE A.session_id = B.session_id
389 AND A.supplier_id = B.supplier_id
390 AND A.tax_percentage = B.tax_percentage
391 AND A.product_id = B.product_id
392 AND C.item_amount <= B.item_amount
393 ) AND A.session_id = pSessionId;
394
395 INSERT INTO tt_summary_item_penjualan_by_period
396 (session_id, supplier_id,
397 product_id, product_balance_id,
398 qty, gross_price_po)
399 SELECT pSessionId, A.supplier_id,
400 A.product_id, A.product_balance_id,
401 A.qty, A.gross_price_po
402 FROM tt_summary_doc_sales_item_penjualan_by_period A
403 WHERE A.session_id = pSessionId;
404
405 RAISE NOTICE '7';
406
407 INSERT INTO tt_summary_item_penjualan_by_period
408 (session_id, supplier_id,
409 product_id, product_balance_id,
410 qty, gross_price_po)
411 SELECT pSessionId, A.supplier_id,
412 A.product_id, A.product_balance_id,
413 A.qty, A.gross_price_po
414 FROM tt_summary_doc_return_item_penjualan_by_period A
415 WHERE A.session_id = pSessionId
416 AND A.record_type = 'SALES';
417
418 RAISE NOTICE '8';
419
420 INSERT INTO tt_summary_item_return_by_period
421 (session_id, supplier_id,
422 product_id, product_balance_id,
423 qty, gross_price_po, tax_percentage)
424 SELECT pSessionId, A.supplier_id,
425 A.product_id, A.product_balance_id,
426 A.qty, A.gross_price_po, A.tax_percentage
427 FROM tt_summary_doc_return_item_penjualan_by_period A
428 WHERE A.session_id = pSessionId
429 AND A.record_type = 'RETURN';
430
431 INSERT INTO tt_monthly_supplier_po_rg(
432 session_id, supplier_id,
433 po_id, po_no, po_date,
434 receive_goods_id, receive_goods_no, receive_goods_date
435 )
436 SELECT pSessionId, A.supplier_id,
437 nextval('pu_po_seq'), vEmptyValue, vEmptyValue,
438 nextval('pu_receive_goods_seq'), vEmptyValue, vEmptyValue
439 FROM tt_summary_item_penjualan_by_period A
440 WHERE A.session_id = pSessionId
441 GROUP BY A.supplier_id;
442
443 INSERT INTO tt_monthly_supplier_po_rg_return(
444 session_id, supplier_id,
445 po_id, po_no, po_date,
446 receive_goods_id, receive_goods_no, receive_goods_date
447 )
448 SELECT pSessionId, A.supplier_id,
449 nextval('pu_po_seq'), vEmptyValue, vEmptyValue,
450 nextval('pu_receive_goods_seq'), vEmptyValue, vEmptyValue
451 FROM tt_summary_item_return_by_period A
452 WHERE A.session_id = pSessionId
453 GROUP BY A.supplier_id;
454
455 UPDATE tt_monthly_supplier_po_rg A
456 SET po_no = vDocNoDefaultPo||vPrefixDocNoVatNormal||'/'||lpad(A.supplier_id::varchar, 6, '0'),
457 receive_goods_no = vDocNoDefaultRg||vPrefixDocNoVatNormal||'/'||lpad(A.supplier_id::varchar, 6, '0')
458 WHERE A.session_id = pSessionId;
459
460 UPDATE tt_monthly_supplier_po_rg_return A
461 SET po_no = vDocNoDefaultPoReturn||vPrefixDocNoVatNormal||'/'||lpad(A.supplier_id::varchar, 6, '0'),
462 receive_goods_no = vDocNoDefaultRgReturn||vPrefixDocNoVatNormal||'/'||lpad(A.supplier_id::varchar, 6, '0')
463 WHERE A.session_id = pSessionId;
464
465
466
467 -- UPDATE po_date berdasarkan data penjualan minimum
468 WITH summary_item_penjualan AS (
469 SELECT A.session_id, A.partner_id, MIN(A.doc_date) AS doc_date FROM tr_report_sales_supplier A
470 WHERE A.session_id = pSessionId
471 GROUP BY A.session_id, A.partner_id
472 )
473 UPDATE tt_monthly_supplier_po_rg A
474 SET po_date = B.doc_date, receive_goods_date = B.doc_date
475 FROM summary_item_penjualan B
476 WHERE B.session_id = A.session_id
477 AND A.session_id = pSessionId
478 AND B.partner_id = A.supplier_id;
479
480 WITH summary_item_penjualan AS (
481 SELECT A.session_id, A.partner_id, MIN(A.doc_date) AS doc_date FROM tr_report_sales_supplier A
482 WHERE A.session_id = pSessionId
483 GROUP BY A.session_id, A.partner_id
484 )
485 UPDATE tt_monthly_supplier_po_rg_return A
486 SET po_date = B.doc_date, receive_goods_date = B.doc_date
487 FROM summary_item_penjualan B
488 WHERE B.session_id = A.session_id
489 AND A.session_id = pSessionId
490 AND B.partner_id = A.supplier_id;
491
492
493 -- UPDATE jika po_datenya NULL
494 UPDATE tt_monthly_supplier_po_rg A
495 SET po_date = pStartDate, receive_goods_date = pStartDate
496 WHERE A.session_id = pSessionId
497 AND po_date IS NULL;
498
499 UPDATE tt_monthly_supplier_po_rg_return A
500 SET po_date = pStartDate, receive_goods_date = pStartDate
501 WHERE A.session_id = pSessionId
502 AND po_date IS NULL;
503
504
505
506 UPDATE pu_po_balance_item_consignment_sold A
507 SET flg_process = vFlagYes, ref_doc_type_id = vDocTypeIdRg, ref_id = A.receive_goods_id,
508 version = A.version + 1, update_user_id = pUserId, update_datetime = pDatetime
509 FROM tt_monthly_supplier_po_rg B
510 WHERE B.session_id = pSessionId
511 AND B.supplier_id = A.supplier_id
512 AND A.ref_doc_type_id = -99 AND A.ref_id = -99
513 AND EXISTS (
514 SELECT 1
515 FROM tr_report_sales_supplier C
516 WHERE C.session_id = pSessionId
517 AND C.so_item_id = A.so_item_id
518 AND C.tenant_id = A.tenant_id
519 AND C.partner_id = A.supplier_id
520 );
521
522 -------------------------------------------------------------------------------------------------
523 -- Buat data PO
524 -------------------------------------------------------------------------------------------------
525 INSERT INTO pu_po
526 (po_id, tenant_id, doc_type_id, doc_no,
527 doc_date, ou_id, ext_doc_no, ext_doc_date,
528 ref_doc_type_id, ref_id, remark,
529 partner_id, purchaser_id, warehouse_id,
530 flg_delivery, curr_code, add_discount_percentage, add_discount_amount,
531 top_code, status_doc, workflow_status,
532 "version", create_datetime, create_user_id, update_datetime, update_user_id)
533 SELECT A.po_id, pTenantId, vDocTypeIdPo, A.po_no,
534 A.po_date, pOuId, vEmptyValue AS ext_doc_no, A.po_date AS ext_doc_date,
535 vDocTypeIdPoCommSheet, -99, vRemarkPo||' - PO No. : '|| A.po_no ||' - PO Date : ' || A.po_date AS remark,
536 A.supplier_id, vPurchaserId AS purchaser_id, vWarehouseId AS warehouse_id,
537 vFlagYes AS flg_delivery, vCurrCode AS curr_code, 0 AS add_discount_percentage, 0 AS add_discount_amount,
538 vTopCode AS top_code, vStatusFinal, vWorkflowStatusApproved,
539 0, pDatetime, pUserId, pDatetime, pUserId
540 FROM tt_monthly_supplier_po_rg A
541 WHERE A.session_id = pSessionId
542 GROUP BY A.po_id, A.po_no, A.po_date, A.supplier_id;
543
544 INSERT INTO pu_po
545 (po_id, tenant_id, doc_type_id, doc_no,
546 doc_date, ou_id, ext_doc_no, ext_doc_date,
547 ref_doc_type_id, ref_id, remark,
548 partner_id, purchaser_id, warehouse_id,
549 flg_delivery, curr_code, add_discount_percentage, add_discount_amount,
550 top_code, status_doc, workflow_status,
551 "version", create_datetime, create_user_id, update_datetime, update_user_id)
552 SELECT A.po_id, pTenantId, vDocTypeIdPo, A.po_no,
553 A.po_date, pOuId, vEmptyValue AS ext_doc_no, A.po_date AS ext_doc_date,
554 vDocTypeIdPoCommSheet, -99, vRemarkPo||' - PO No. : '|| A.po_no ||' - PO Date : ' || A.po_date AS remark,
555 A.supplier_id, vPurchaserId AS purchaser_id, vWarehouseId AS warehouse_id,
556 vFlagYes AS flg_delivery, vCurrCode AS curr_code, 0 AS add_discount_percentage, 0 AS add_discount_amount,
557 vTopCode AS top_code, vStatusFinal, vWorkflowStatusApproved,
558 0, pDatetime, pUserId, pDatetime, pUserId
559 FROM tt_monthly_supplier_po_rg_return A
560 WHERE A.session_id = pSessionId
561 GROUP BY A.po_id, A.po_no, A.po_date, A.supplier_id;
562
563 RAISE NOTICE '7';
564
565 INSERT INTO pu_po_item
566 (tenant_id, po_id, line_no,
567 ref_doc_type_id, ref_id, warehouse_id, product_id, flg_stock,
568 curr_code, gross_price_po,
569 flg_tax_amount, tax_id, tax_percentage,
570 tax_price,
571 nett_price_po,
572 qty_po, po_uom_id, qty_int, base_uom_id,
573 discount_percentage, discount_amount, gross_item_amount,
574 nett_item_amount, tax_amount,
575 activity_gl_id, product_coa_id, ou_rc_id, eta,
576 tolerance_rcv_qty, remark, version,
577 create_datetime, create_user_id, update_datetime, update_user_id,
578 segment_id, eta_day, flg_indent)
579 SELECT pTenantId, B.po_id, ROW_NUMBER() OVER ( PARTITION BY B.po_id ),
580 vEmptyId, vEmptyId, vWarehouseId, A.product_id, vFlagYes,
581 vCurrCode, A.gross_price_po,
582 vFlagYes, vTaxId, vTaxPercentage,
583 f_tax_rounding(pTenantId, f_get_amount_before_tax(A.gross_price_po, vFlagYes, vTaxPercentage, 10, vRoundingModeNonTax), vTaxPercentage),
584 f_get_amount_before_tax(A.gross_price_po, vFlagYes, vTaxPercentage, f_get_digit_decimal_doc_curr(vDocTypeIdPo, vCurrCode), vRoundingModeNonTax),
585 SUM(A.qty),f_get_product_base_uom_id(A.product_id), SUM(A.qty), f_get_product_base_uom_id(A.product_id),
586 0, 0, 0,
587 0, 0,
588 vEmptyId, f_get_product_coa_group_product(pTenantId, A.product_id), vEmptyId, B.po_date,
589 0, vEmptyValue, 0,
590 pDatetime, pUserId, pDatetime, pUserId,
591 vEmptyId, vEmptyValue, vFlagNo
592 FROM tt_summary_item_penjualan_by_period A
593 INNER JOIN tt_monthly_supplier_po_rg B ON A.supplier_id = B.supplier_id AND A.session_id = B.session_id
594 WHERE A.session_id = pSessionId
595 GROUP BY B.po_id, A.product_id, A.gross_price_po, B.po_date;
596
597 INSERT INTO pu_po_item
598 (tenant_id, po_id, line_no,
599 ref_doc_type_id, ref_id, warehouse_id, product_id, flg_stock,
600 curr_code, gross_price_po,
601 flg_tax_amount, tax_id, tax_percentage,
602 tax_price,
603 nett_price_po,
604 qty_po, po_uom_id, qty_int, base_uom_id,
605 discount_percentage, discount_amount, gross_item_amount,
606 nett_item_amount, tax_amount,
607 activity_gl_id, product_coa_id, ou_rc_id, eta,
608 tolerance_rcv_qty, remark, version,
609 create_datetime, create_user_id, update_datetime, update_user_id,
610 segment_id, eta_day, flg_indent)
611 SELECT pTenantId, B.po_id, ROW_NUMBER() OVER ( PARTITION BY B.po_id ),
612 vEmptyId, vEmptyId, vWarehouseId, A.product_id, vFlagYes,
613 vCurrCode, A.gross_price_po,
614 vFlagYes, vTaxId, vTaxPercentage,
615 f_tax_rounding(pTenantId, f_get_amount_before_tax(A.gross_price_po, vFlagYes, vTaxPercentage, 10, vRoundingModeNonTax), vTaxPercentage),
616 f_get_amount_before_tax(A.gross_price_po, vFlagYes, vTaxPercentage, f_get_digit_decimal_doc_curr(vDocTypeIdPo, vCurrCode), vRoundingModeNonTax),
617 SUM(A.qty),f_get_product_base_uom_id(A.product_id), SUM(A.qty), f_get_product_base_uom_id(A.product_id),
618 0, 0, 0,
619 0, 0,
620 vEmptyId, f_get_product_coa_group_product(pTenantId, A.product_id), vEmptyId, B.po_date,
621 0, vEmptyValue, 0,
622 pDatetime, pUserId, pDatetime, pUserId,
623 vEmptyId, vEmptyValue, vFlagNo
624 FROM tt_summary_item_return_by_period A
625 INNER JOIN tt_monthly_supplier_po_rg_return B ON A.supplier_id = B.supplier_id AND A.session_id = B.session_id
626 WHERE A.session_id = pSessionId
627 GROUP BY B.po_id, A.product_id, A.gross_price_po, B.po_date;
628
629 RAISE NOTICE '8';
630
631 UPDATE pu_po_item A
632 SET gross_item_amount = A.gross_price_po * A.qty_po,
633 nett_item_amount = A.nett_price_po * A.qty_po,
634 tax_amount = A.tax_price * A.qty_po
635 WHERE EXISTS (
636 SELECT 1 FROM tt_monthly_supplier_po_rg B
637 WHERE B.session_id = pSessionId
638 AND B.po_id = A.po_id
639 );
640
641 UPDATE pu_po_item A
642 SET gross_item_amount = A.gross_price_po * A.qty_po,
643 nett_item_amount = A.nett_price_po * A.qty_po,
644 tax_amount = A.tax_price * A.qty_po
645 WHERE EXISTS (
646 SELECT 1 FROM tt_monthly_supplier_po_rg_return B
647 WHERE B.session_id = pSessionId
648 AND B.po_id = A.po_id
649 );
650
651 RAISE NOTICE '9';
652
653 INSERT INTO pu_po_balance_item
654 (po_item_id, tenant_id, ou_id, qty_po, qty_rcv, qty_return, qty_cancel, qty_add, po_uom_id,
655 qty_int_po, qty_int_rcv, qty_int_return, qty_int_cancel, qty_int_add, base_uom_id,
656 tolerance_rcv_qty, status_item, version, create_datetime, create_user_id, update_datetime, update_user_id)
657 SELECT A.po_item_id, A.tenant_id, B.ou_id, A.qty_po, A.qty_po, 0, 0, 0, A.po_uom_id,
658 A.qty_int, A.qty_int, 0, 0, 0, A.base_uom_id,
659 A.tolerance_rcv_qty, vStatusFinal, 0, pDatetime, pUserId, pDatetime, pUserId
660 FROM pu_po_item A, pu_po B
661 WHERE A.po_id = B.po_id
662 AND EXISTS (
663 SELECT 1 FROM tt_monthly_supplier_po_rg C
664 WHERE C.session_id = pSessionId
665 AND C.po_id = A.po_id
666 );
667
668 INSERT INTO pu_po_balance_item
669 (po_item_id, tenant_id, ou_id, qty_po, qty_rcv, qty_return, qty_cancel, qty_add, po_uom_id,
670 qty_int_po, qty_int_rcv, qty_int_return, qty_int_cancel, qty_int_add, base_uom_id,
671 tolerance_rcv_qty, status_item, version, create_datetime, create_user_id, update_datetime, update_user_id)
672 SELECT A.po_item_id, A.tenant_id, B.ou_id, A.qty_po, A.qty_po, 0, 0, 0, A.po_uom_id,
673 A.qty_int, A.qty_int, 0, 0, 0, A.base_uom_id,
674 A.tolerance_rcv_qty, vStatusFinal, 0, pDatetime, pUserId, pDatetime, pUserId
675 FROM pu_po_item A, pu_po B
676 WHERE A.po_id = B.po_id
677 AND EXISTS (
678 SELECT 1 FROM tt_monthly_supplier_po_rg_return C
679 WHERE C.session_id = pSessionId
680 AND C.po_id = A.po_id
681 );
682
683 RAISE NOTICE '10';
684
685 INSERT INTO pu_log_po_balance_item
686 (tenant_id, po_id, po_item_id, ref_doc_type_id, ref_id, ref_item_id,
687 qty_trx, trx_uom_id, qty_int, base_uom_id, remark,
688 version, create_datetime, create_user_id, update_datetime, update_user_id)
689 SELECT A.tenant_id, A.po_id, A.po_item_id, B.doc_type_id, B.po_id, A.po_item_id,
690 A.qty_po, A.po_uom_id, A.qty_int, A.base_uom_id, A.remark,
691 0, pDatetime, pUserId, pDatetime, pUserId
692 FROM pu_po_item A, pu_po B
693 WHERE A.po_id = B.po_id
694 AND EXISTS(
695 SELECT 1 FROM tt_monthly_supplier_po_rg C
696 WHERE C.session_id = pSessionId
697 AND C.po_id = A.po_id
698 );
699
700 INSERT INTO pu_log_po_balance_item
701 (tenant_id, po_id, po_item_id, ref_doc_type_id, ref_id, ref_item_id,
702 qty_trx, trx_uom_id, qty_int, base_uom_id, remark,
703 version, create_datetime, create_user_id, update_datetime, update_user_id)
704 SELECT A.tenant_id, A.po_id, A.po_item_id, B.doc_type_id, B.po_id, A.po_item_id,
705 A.qty_po, A.po_uom_id, A.qty_int, A.base_uom_id, A.remark,
706 0, pDatetime, pUserId, pDatetime, pUserId
707 FROM pu_po_item A, pu_po B
708 WHERE A.po_id = B.po_id
709 AND EXISTS(
710 SELECT 1 FROM tt_monthly_supplier_po_rg_return C
711 WHERE C.session_id = pSessionId
712 AND C.po_id = A.po_id
713 );
714
715 RAISE NOTICE '11';
716
717 -------------------------------------------------------------------------------------------------
718 -- Buat data Receive Goods
719 -------------------------------------------------------------------------------------------------
720
721 INSERT INTO pu_receive_goods
722 (receive_goods_id, tenant_id, doc_type_id, doc_no, doc_date,
723 ou_id, ext_doc_no, ext_doc_date,
724 ref_doc_type_id, ref_id,
725 remark, partner_id, warehouse_id, status_doc, workflow_status,
726 "version", create_datetime, create_user_id, update_datetime, update_user_id)
727 SELECT A.receive_goods_id, pTenantId, vDocTypeIdRg, A.receive_goods_no, A.receive_goods_date,
728 pOuId, vEmptyValue AS ext_doc_no, A.receive_goods_date AS ext_doc_date,
729 vDocTypeIdPo, A.po_id,
730 vRemarkRg||' - RG No. : '|| A.receive_goods_no ||' - RG Date : ' || A.receive_goods_date AS remark, A.supplier_id, vWarehouseId AS warehouse_id,
731 vStatusRelease, vWorkflowStatusApproved,
732 0, pDatetime, pUserId, pDatetime, pUserId
733 FROM tt_monthly_supplier_po_rg A
734 WHERE A.session_id = pSessionId
735 GROUP BY A.receive_goods_id, A.receive_goods_no, A.receive_goods_date, A.po_id, A.supplier_id;
736
737 -- menambahkan untuk generate po and rg, RGnya harus tercatat di pu_po_rg_generated
738 INSERT INTO pu_po_rg_generated (doc_type_id, ref_id, doc_no, partner_id,
739 create_datetime, create_user_id,
740 update_datetime, update_user_id,
741 version)
742 SELECT vDocTypeIdRg, A.receive_goods_id, A.receive_goods_no, A.supplier_id,
743 pDatetime, pUserId,
744 pDatetime, pUserId,
745 0
746 FROM tt_monthly_supplier_po_rg A
747 WHERE A.session_id = pSessionId
748 GROUP BY A.receive_goods_id, A.receive_goods_no, A.receive_goods_date, A.po_id, A.supplier_id;
749
750 INSERT INTO pu_receive_goods
751 (receive_goods_id, tenant_id, doc_type_id, doc_no, doc_date,
752 ou_id, ext_doc_no, ext_doc_date,
753 ref_doc_type_id, ref_id,
754 remark, partner_id, warehouse_id, status_doc, workflow_status,
755 "version", create_datetime, create_user_id, update_datetime, update_user_id)
756 SELECT A.receive_goods_id, pTenantId, vDocTypeIdRg, A.receive_goods_no, A.receive_goods_date,
757 pOuId, vEmptyValue AS ext_doc_no, A.receive_goods_date AS ext_doc_date,
758 vDocTypeIdPo, A.po_id,
759 vRemarkRg||' - RG No. : '|| A.receive_goods_no ||' - RG Date : ' || A.receive_goods_date AS remark, A.supplier_id, vWarehouseId AS warehouse_id,
760 vStatusRelease, vWorkflowStatusApproved,
761 0, pDatetime, pUserId, pDatetime, pUserId
762 FROM tt_monthly_supplier_po_rg_return A
763 WHERE A.session_id = pSessionId
764 GROUP BY A.receive_goods_id, A.receive_goods_no, A.receive_goods_date, A.po_id, A.supplier_id;
765
766
767 RAISE NOTICE '12';
768
769 INSERT INTO pu_receive_goods_item
770 (tenant_id, receive_goods_id, line_no, ref_doc_type_id, ref_id,
771 product_id, qty_rcv_po, po_uom_id, qty_rcv_int, base_uom_id,
772 remark,
773 version, create_datetime, create_user_id, update_datetime, update_user_id)
774 SELECT pTenantId, A.receive_goods_id, C.line_no, B.doc_type_id, C.po_item_id,
775 C.product_id, C.qty_po, C.po_uom_id, C.qty_int, C.base_uom_id,
776 vEmptyValue,
777 0, pDatetime, pUserId, pDatetime, pUserId
778 FROM tt_monthly_supplier_po_rg A
779 INNER JOIN pu_po B ON A.po_id = B.po_id
780 INNER JOIN pu_po_item C ON B.po_id = C.po_id
781 WHERE A.session_id = pSessionId;
782
783 INSERT INTO pu_receive_goods_item
784 (tenant_id, receive_goods_id, line_no, ref_doc_type_id, ref_id,
785 product_id, qty_rcv_po, po_uom_id, qty_rcv_int, base_uom_id,
786 remark,
787 version, create_datetime, create_user_id, update_datetime, update_user_id)
788 SELECT pTenantId, A.receive_goods_id, C.line_no, B.doc_type_id, C.po_item_id,
789 C.product_id, C.qty_po, C.po_uom_id, C.qty_int, C.base_uom_id,
790 vEmptyValue,
791 0, pDatetime, pUserId, pDatetime, pUserId
792 FROM tt_monthly_supplier_po_rg_return A
793 INNER JOIN pu_po B ON A.po_id = B.po_id
794 INNER JOIN pu_po_item C ON B.po_id = C.po_id
795 WHERE A.session_id = pSessionId;
796
797 RAISE NOTICE '13';
798
799 INSERT INTO pu_log_po_balance_item
800 (tenant_id, po_id, po_item_id, ref_doc_type_id, ref_id, ref_item_id,
801 qty_trx, trx_uom_id, qty_int, base_uom_id, remark,
802 "version", create_datetime, create_user_id, update_datetime, update_user_id)
803 SELECT A.tenant_id, C.po_id, C.po_item_id, A.doc_type_id, A.receive_goods_id, B.receive_goods_item_id,
804 B.qty_rcv_po * -1, B.po_uom_id, B.qty_rcv_int * -1, B.base_uom_id, B.remark,
805 0, pDatetime, pUserId, pDatetime, pUserId
806 FROM pu_receive_goods A, pu_receive_goods_item B, pu_po_item C
807 WHERE A.receive_goods_id = B.receive_goods_id AND
808 B.ref_id = C.po_item_id AND EXISTS (
809 SELECT 1 FROM tt_monthly_supplier_po_rg D
810 WHERE D.receive_goods_id = A.receive_goods_id
811 AND D.session_id = pSessionId
812 );
813
814 INSERT INTO pu_log_po_balance_item
815 (tenant_id, po_id, po_item_id, ref_doc_type_id, ref_id, ref_item_id,
816 qty_trx, trx_uom_id, qty_int, base_uom_id, remark,
817 "version", create_datetime, create_user_id, update_datetime, update_user_id)
818 SELECT A.tenant_id, C.po_id, C.po_item_id, A.doc_type_id, A.receive_goods_id, B.receive_goods_item_id,
819 B.qty_rcv_po * -1, B.po_uom_id, B.qty_rcv_int * -1, B.base_uom_id, B.remark,
820 0, pDatetime, pUserId, pDatetime, pUserId
821 FROM pu_receive_goods A, pu_receive_goods_item B, pu_po_item C
822 WHERE A.receive_goods_id = B.receive_goods_id AND
823 B.ref_id = C.po_item_id AND EXISTS (
824 SELECT 1 FROM tt_monthly_supplier_po_rg_return D
825 WHERE D.receive_goods_id = A.receive_goods_id
826 AND D.session_id = pSessionId
827 );
828
829 RAISE NOTICE '14';
830
831 INSERT INTO pu_po_balance_invoice
832 (tenant_id, ou_id, partner_id, po_id,
833 ref_doc_type_id, ref_id, ref_doc_no, ref_doc_date, ref_item_id, qty_rcv_po, po_uom_id,
834 curr_code, price_po, item_amount, flg_invoice, invoice_id,
835 "version", create_datetime, create_user_id, update_datetime, update_user_id)
836 SELECT A.tenant_id, A.ou_id, A.partner_id, A.ref_id,
837 A.doc_type_id, A.receive_goods_id, A.doc_no, A.doc_date, B.receive_goods_item_id, B.qty_rcv_po, B.po_uom_id,
838 C.curr_code, C.nett_price_po,
839 f_get_amount_before_tax(B.qty_rcv_po * (C.gross_price_po - C.discount_amount), C.flg_tax_amount, C.tax_percentage, f_get_digit_decimal_doc_curr(vDocTypeIdRg, C.curr_code), vRoundingModeNonTax),
840 vFlagNo, vEmptyId,
841 0, pDatetime, pUserId, pDatetime, pUserId
842 FROM pu_receive_goods A, pu_receive_goods_item B, pu_po_item C
843 WHERE A.receive_goods_id = B.receive_goods_id AND
844 B.ref_id = C.po_item_id AND EXISTS (
845 SELECT 1 FROM tt_monthly_supplier_po_rg D
846 WHERE D.receive_goods_id = A.receive_goods_id
847 AND D.session_id = pSessionId
848 );
849
850 INSERT INTO pu_po_balance_invoice
851 (tenant_id, ou_id, partner_id, po_id,
852 ref_doc_type_id, ref_id, ref_doc_no, ref_doc_date, ref_item_id, qty_rcv_po, po_uom_id,
853 curr_code, price_po, item_amount, flg_invoice, invoice_id,
854 "version", create_datetime, create_user_id, update_datetime, update_user_id)
855 SELECT A.tenant_id, A.ou_id, A.partner_id, A.ref_id,
856 A.doc_type_id, A.receive_goods_id, A.doc_no, A.doc_date, B.receive_goods_item_id, B.qty_rcv_po, B.po_uom_id,
857 C.curr_code, C.nett_price_po,
858 f_get_amount_before_tax(B.qty_rcv_po * (C.gross_price_po - C.discount_amount), C.flg_tax_amount, C.tax_percentage, f_get_digit_decimal_doc_curr(vDocTypeIdRg, C.curr_code), vRoundingModeNonTax),
859 vFlagNo, vEmptyId,
860 0, pDatetime, pUserId, pDatetime, pUserId
861 FROM pu_receive_goods A, pu_receive_goods_item B, pu_po_item C
862 WHERE A.receive_goods_id = B.receive_goods_id AND
863 B.ref_id = C.po_item_id AND EXISTS (
864 SELECT 1 FROM tt_monthly_supplier_po_rg_return D
865 WHERE D.receive_goods_id = A.receive_goods_id
866 AND D.session_id = pSessionId
867 );
868
869 INSERT INTO pu_po_balance_invoice_tax
870 (tenant_id, ou_id, partner_id, po_id,
871 ref_doc_type_id, ref_id, ref_item_id, tax_id, flg_amount,
872 tax_percentage, curr_code, base_amount, tax_amount, flg_invoice, invoice_id,
873 "version", create_datetime, create_user_id, update_datetime, update_user_id)
874 SELECT A.tenant_id, A.ou_id, A.partner_id, A.ref_id,
875 A.doc_type_id, A.receive_goods_id, B.receive_goods_item_id, C.tax_id, D.flg_amount,
876 C.tax_percentage, C.curr_code,
877 f_get_amount_before_tax(B.qty_rcv_po * (C.gross_price_po - C.discount_amount), C.flg_tax_amount, C.tax_percentage, f_get_digit_decimal_doc_curr(vDocTypeIdRg, C.curr_code), vRoundingModeNonTax),
878 f_tax_rounding(A.tenant_id, f_get_amount_before_tax(B.qty_rcv_po * (C.gross_price_po - C.discount_amount), C.flg_tax_amount, C.tax_percentage, f_get_digit_decimal_doc_curr(vDocTypeIdRg, C.curr_code), vRoundingModeNonTax), C.tax_percentage),
879 vFlagNo, vEmptyId,
880 0, pDatetime, pUserId, pDatetime, pUserId
881 FROM pu_receive_goods A, pu_receive_goods_item B, pu_po_item C, m_tax D
882 WHERE A.receive_goods_id = B.receive_goods_id AND
883 B.ref_id = C.po_item_id AND
884 C.tax_id = D.tax_id AND EXISTS (
885 SELECT 1 FROM tt_monthly_supplier_po_rg D
886 WHERE D.receive_goods_id = A.receive_goods_id
887 AND D.session_id = pSessionId
888 );
889
890 RAISE NOTICE '32';
891
892 INSERT INTO pu_po_balance_invoice_tax
893 (tenant_id, ou_id, partner_id, po_id,
894 ref_doc_type_id, ref_id, ref_item_id, tax_id, flg_amount,
895 tax_percentage, curr_code, base_amount, tax_amount, flg_invoice, invoice_id,
896 "version", create_datetime, create_user_id, update_datetime, update_user_id)
897 SELECT A.tenant_id, A.ou_id, A.partner_id, A.ref_id,
898 A.doc_type_id, A.receive_goods_id, B.receive_goods_item_id, C.tax_id, D.flg_amount,
899 C.tax_percentage, C.curr_code,
900 f_get_amount_before_tax(B.qty_rcv_po * (C.gross_price_po - C.discount_amount), C.flg_tax_amount, C.tax_percentage, f_get_digit_decimal_doc_curr(vDocTypeIdRg, C.curr_code), vRoundingModeNonTax),
901 f_tax_rounding(A.tenant_id, f_get_amount_before_tax(B.qty_rcv_po * (C.gross_price_po - C.discount_amount), C.flg_tax_amount, C.tax_percentage, f_get_digit_decimal_doc_curr(vDocTypeIdRg, C.curr_code), vRoundingModeNonTax), C.tax_percentage),
902 vFlagNo, vEmptyId,
903 0, pDatetime, pUserId, pDatetime, pUserId
904 FROM pu_receive_goods A, pu_receive_goods_item B, pu_po_item C, m_tax D
905 WHERE A.receive_goods_id = B.receive_goods_id AND
906 B.ref_id = C.po_item_id AND
907 C.tax_id = D.tax_id AND EXISTS (
908 SELECT 1 FROM tt_monthly_supplier_po_rg_return D
909 WHERE D.receive_goods_id = A.receive_goods_id
910 AND D.session_id = pSessionId
911 );
912
913 RAISE NOTICE '33';
914
915 -------------------------------------------------------------------------------------------------
916 -- Buat data log product balance stock
917 -------------------------------------------------------------------------------------------------
918
919 INSERT INTO in_log_product_balance_stock
920 (tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
921 product_id, product_balance_id, warehouse_id, product_status, base_uom_id, qty,
922 "version", create_datetime, create_user_id, update_datetime, update_user_id)
923 SELECT pTenantId, pOuId, vDocTypeIdRg, B.receive_goods_id, B.receive_goods_no, B.receive_goods_date, B.supplier_id,
924 A.product_id, A.product_balance_id, vWarehouseId, vProductStatus, f_get_product_base_uom_id(product_id), SUM(A.qty),
925 0, pDatetime, pUserId, pDatetime, pUserId
926 FROM tt_summary_item_penjualan_by_period A
927 INNER JOIN tt_monthly_supplier_po_rg B ON A.supplier_id = B.supplier_id AND A.session_id = B.session_id
928 WHERE A.session_id = pSessionId
929 GROUP BY B.receive_goods_id, B.receive_goods_no, B.receive_goods_date, B.supplier_id, A.product_id, A.product_balance_id;
930
931 INSERT INTO in_log_product_balance_stock
932 (tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
933 product_id, product_balance_id, warehouse_id, product_status, base_uom_id, qty,
934 "version", create_datetime, create_user_id, update_datetime, update_user_id)
935 SELECT pTenantId, pOuId, vDocTypeIdRg, B.receive_goods_id, B.receive_goods_no, B.receive_goods_date, B.supplier_id,
936 A.product_id, A.product_balance_id, vWarehouseId, vProductStatus, f_get_product_base_uom_id(product_id), SUM(-1*A.qty),
937 0, pDatetime, pUserId, pDatetime, pUserId
938 FROM tt_summary_item_return_by_period A
939 INNER JOIN tt_monthly_supplier_po_rg_return B ON A.supplier_id = B.supplier_id AND A.session_id = B.session_id
940 WHERE A.session_id = pSessionId
941 GROUP BY B.receive_goods_id, B.receive_goods_no, B.receive_goods_date, B.supplier_id, A.product_id, A.product_balance_id;
942
943 -------------------------------------------------------------------------------------------------
944 -- Buat data jurnal penerimaan barang
945 -------------------------------------------------------------------------------------------------
946
947 PERFORM gl_manage_admin_journal_trx(A.tenant_id, (vOuStructure).ou_bu_id, A.ou_id, (vDocJournal).journal_type, (vDocJournal).ledger_code, f_get_year_month_date(A.doc_date), 'DAILY', pDatetime, pUserId)
948 FROM pu_receive_goods A
949 WHERE EXISTS (
950 SELECT 1 FROM tt_monthly_supplier_po_rg E
951 WHERE E.session_id = pSessionId
952 AND A.receive_goods_id = E.receive_goods_id
953 );
954
955 RAISE NOTICE '18';
956
957 INSERT INTO tt_generate_po_rg_for_journal
958 (session_id, receive_goods_id, journal_trx_id)
959 SELECT pSessionId, A.receive_goods_id, NEXTVAL('gl_journal_trx_seq')
960 FROM tt_monthly_supplier_po_rg A
961 WHERE A.session_id = pSessionId
962 GROUP BY A.receive_goods_id;
963
964 INSERT INTO tt_generate_po_rg_for_journal
965 (session_id, receive_goods_id, journal_trx_id)
966 SELECT pSessionId, A.receive_goods_id, NEXTVAL('gl_journal_trx_seq')
967 FROM tt_monthly_supplier_po_rg_return A
968 WHERE A.session_id = pSessionId
969 GROUP BY A.receive_goods_id;
970
971 RAISE NOTICE '19';
972
973 INSERT INTO gl_journal_trx
974 (journal_trx_id, tenant_id, journal_type, doc_type_id, doc_id, doc_no, doc_date,
975 ou_bu_id, ou_branch_id, ou_sub_bu_id, partner_id, cashbank_id, warehouse_id, ext_doc_no, ext_doc_date,
976 ref_doc_type_id, ref_id, due_date, curr_code, remark, status_doc, workflow_status,
977 "version", create_datetime, create_user_id, update_datetime, update_user_id)
978 SELECT C.journal_trx_id, A.tenant_id, (vDocJournal).journal_type, A.doc_type_id, A.receive_goods_id, A.doc_no, A.doc_date,
979 (vOuStructure).ou_bu_id, (vOuStructure).ou_branch_id, (vOuStructure).ou_sub_bu_id, A.partner_id, vEmptyId, A.warehouse_id, A.ext_doc_no, A.ext_doc_date,
980 A.ref_doc_type_id, A.ref_id, A.doc_date, B.curr_code, A.remark, vStatusDraft, 'DRAFT',
981 0, pDatetime, pUserId, pDatetime, pUserId
982 FROM pu_receive_goods A, pu_po B, tt_generate_po_rg_for_journal C
983 WHERE A.ref_doc_type_id = B.doc_type_id
984 AND A.ref_id = B.po_id
985 AND C.session_id = pSessionId
986 AND C.receive_goods_id = A.receive_goods_id;
987
988 RAISE NOTICE '20';
989
990 INSERT INTO tt_journal_trx_item
991 (session_id, tenant_id, journal_trx_id, line_no,
992 ref_doc_type_id, ref_id,
993 partner_id, product_id, cashbank_id, ou_rc_id,
994 segmen_id, sign_journal, flg_source_coa, activity_gl_id,
995 coa_id, curr_code, qty, uom_id,
996 amount, journal_date, type_rate,
997 numerator_rate, denominator_rate, journal_desc, remark)
998 SELECT pSessionId, A.tenant_id, D.journal_trx_id, 1,
999 A.doc_type_id, B.receive_goods_item_id,
1000 A.partner_id, B.product_id, vEmptyId, C.ou_rc_id,
1001 vEmptyId, CASE WHEN f_get_amount_before_tax(B.qty_rcv_po * (C.gross_price_po - C.discount_amount), C.flg_tax_amount, C.tax_percentage,f_get_digit_decimal_doc_curr(vDocTypeIdRg, C.curr_code), vRoundingModeNonTax) > 0 THEN vSignDebit ELSE vSignCredit END, vProductCOA, C.activity_gl_id,
1002 C.product_coa_id, C.curr_code, B.qty_rcv_int, B.base_uom_id,
1003 ABS(f_get_amount_before_tax(B.qty_rcv_po * (C.gross_price_po - C.discount_amount), C.flg_tax_amount, C.tax_percentage,f_get_digit_decimal_doc_curr(vDocTypeIdRg, C.curr_code), vRoundingModeNonTax)),
1004 A.doc_date, vTypeRate,
1005 1, 1, 'PRODUCT_STOCK', B.remark
1006 FROM pu_receive_goods A, pu_receive_goods_item B, pu_po_item C, tt_generate_po_rg_for_journal D
1007 WHERE A.receive_goods_id = B.receive_goods_id AND
1008 A.receive_goods_id = D.receive_goods_id AND
1009 B.ref_id = C.po_item_id AND
1010 D.session_id = pSessionId;
1011
1012 RAISE NOTICE '21';
1013
1014 INSERT INTO gl_journal_trx_item
1015 (tenant_id, journal_trx_id, line_no,
1016 ref_doc_type_id, ref_id,
1017 partner_id, product_id, cashbank_id, ou_rc_id,
1018 segmen_id, sign_journal, flg_source_coa, activity_gl_id,
1019 coa_id, curr_code, qty, uom_id,
1020 amount, journal_date, type_rate,
1021 numerator_rate, denominator_rate, journal_desc, remark,
1022 "version", create_datetime, create_user_id, update_datetime, update_user_id)
1023 SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id ),
1024 A.ref_doc_type_id, A.ref_id,
1025 A.partner_id, A.product_id, A.cashbank_id, A.ou_rc_id,
1026 A.segmen_id, A.sign_journal, A.flg_source_coa, A.activity_gl_id,
1027 A.coa_id, A.curr_code, A.qty, A.uom_id,
1028 abs(A.amount), A.journal_date, A.type_rate,
1029 A.numerator_rate, A.denominator_rate, A.journal_desc, A.remark,
1030 0, pDatetime, pUserId, pDatetime, pUserId
1031 FROM tt_journal_trx_item A
1032 WHERE A.session_id = pSessionId;
1033
1034 RAISE NOTICE '22';
1035
1036 -- UNTUK CREDIT
1037 INSERT INTO gl_journal_trx_mapping
1038 (tenant_id, journal_trx_id, line_no,
1039 ref_doc_type_id, ref_id,
1040 partner_id, product_id, cashbank_id, ou_rc_id,
1041 segmen_id, sign_journal, flg_source_coa, activity_gl_id,
1042 coa_id, curr_code, qty, uom_id,
1043 amount, journal_date, type_rate,
1044 numerator_rate, denominator_rate, journal_desc, remark,
1045 "version", create_datetime, create_user_id, update_datetime, update_user_id)
1046 SELECT A.tenant_id, A.journal_trx_id, 1,
1047 vEmptyId, vEmptyId,
1048 A.partner_id, vEmptyId, vEmptyId, vEmptyId,
1049 vEmptyId, vSignCredit, vSystemCOA, vEmptyId,
1050 f_get_system_coa_by_group_coa(A.tenant_id, 'HutangHarusDibayar'), A.curr_code, 0, vEmptyId,
1051 abs(SUM(A.amount)), A.journal_date, A.type_rate,
1052 1, 1, 'ACCR_AP', vEmptyValue,
1053 0, pDatetime, pUserId, pDatetime, pUserId
1054 FROM tt_journal_trx_item A
1055 WHERE A.session_id = pSessionId
1056 AND A.sign_journal = vSignDebit
1057 GROUP BY A.tenant_id, A.journal_trx_id, A.partner_id, A.curr_code, A.journal_date, A.type_rate;
1058
1059 -- UNTUK DEBIT
1060 INSERT INTO gl_journal_trx_mapping
1061 (tenant_id, journal_trx_id, line_no,
1062 ref_doc_type_id, ref_id,
1063 partner_id, product_id, cashbank_id, ou_rc_id,
1064 segmen_id, sign_journal, flg_source_coa, activity_gl_id,
1065 coa_id, curr_code, qty, uom_id,
1066 amount, journal_date, type_rate,
1067 numerator_rate, denominator_rate, journal_desc, remark,
1068 "version", create_datetime, create_user_id, update_datetime, update_user_id)
1069 SELECT A.tenant_id, A.journal_trx_id, 2,
1070 vEmptyId, vEmptyId,
1071 A.partner_id, vEmptyId, vEmptyId, vEmptyId,
1072 vEmptyId,vSignDebit, vSystemCOA, vEmptyId,
1073 f_get_system_coa_by_group_coa(A.tenant_id, 'HutangHarusDibayar'), A.curr_code, 0, vEmptyId,
1074 abs(SUM(A.amount)), A.journal_date, A.type_rate,
1075 1, 1, 'ACCR_AP', vEmptyValue,
1076 0, pDatetime, pUserId, pDatetime, pUserId
1077 FROM tt_journal_trx_item A
1078 WHERE A.session_id = pSessionId
1079 AND A.sign_journal = vSignCredit
1080 GROUP BY A.tenant_id, A.journal_trx_id, A.partner_id, A.curr_code, A.journal_date, A.type_rate;
1081
1082 -- insert ke pu_po_generated_po_and_rg
1083 INSERT INTO pu_po_generated_po_and_rg (year_month, po_id, po_doc_no, po_doc_date, receive_goods_id, receive_goods_doc_no, receive_goods_doc_date)
1084 SELECT pPeriodProcess, A.po_id, A.po_no, A.po_date, A.receive_goods_id, A.receive_goods_no, A.receive_goods_date
1085 FROM tt_monthly_supplier_po_rg A
1086 WHERE A.session_id = pSessionId
1087 GROUP BY A.po_id, A.po_no, A.po_date, A.receive_goods_id, A.receive_goods_no, A.receive_goods_date;
1088
1089 vStatusGenerate := 'S';
1090
1091 ELSE
1092
1093 vStatusGenerate := 'E';
1094 RAISE NOTICE 'TERKENA VALIDASI ERROR DOKUMEN PENJUALAN DARI MAGENTO, CEK HALAMAN REPROCESS MAGENTO ORDER';
1095
1096 END IF;
1097
1098 ELSE
1099 vStatusGenerate := 'E';
1100 RAISE NOTICE 'TERKENA VALIDASI DOKUMEN TRANSAKSI YANG BELUM DI APPROVED';
1101
1102 END IF;
1103
1104 DELETE FROM tr_report_sales_supplier WHERE session_id = pSessionId;
1105 DELETE FROM tt_generate_po_rg_for_journal WHERE session_id = pSessionId;
1106 DELETE FROM tt_summary_item_penjualan_by_period WHERE session_id = pSessionId;
1107 DELETE FROM tt_summary_item_return_by_period WHERE session_id = pSessionId;
1108 DELETE FROM tt_monthly_supplier_po_rg WHERE session_id = pSessionId;
1109 DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;
1110
1111END;
1112$BODY$
1113 LANGUAGE plpgsql VOLATILE
1114 COST 100;
1115/