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