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