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