func fzi_regenerate_journal_receive_goods
unknown
mysql
11 days ago
11 kB
3
Indexable
Never
-- DROP FUNCTION public.fzi_regenerate_journal_receive_goods(varchar, int8, int8, varchar, int8); CREATE OR REPLACE FUNCTION public.fzi_regenerate_journal_receive_goods(psessionid character varying, ptenantid bigint, puserid bigint, pdatetime character varying, preceivegoodsid bigint) RETURNS void LANGUAGE plpgsql AS $function$ DECLARE vRow RECORD; vJournalTrxId bigint; vRoundingModeNonTax character varying(5); vEmptyId bigint := -99; vEmptyValue character varying(1) := ' '; vReceiveGoodsDocTypeId bigint := 111; vStatusDraft character varying(1) := 'D'; vSignDebit character varying(1) := 'D'; vProductCOA character varying(10) := 'PRODUCT'; vTypeRate character varying(3) := 'COM'; vActivityCOA character varying(10) := 'ACTIVITY'; vSystemCOA character varying(10) := 'SYSTEM'; vSignCredit character varying(1) := 'C'; vDocJournal DOC_JOURNAL%ROWTYPE; vOuStructure OU_BU_STRUCTURE%ROWTYPE; vOuStructureJournalItem OU_BU_STRUCTURE%ROWTYPE; BEGIN -- Ambil data dari pu_receive_goods SELECT * FROM pu_receive_goods A WHERE A.receive_goods_id = pReceiveGoodsId INTO vRow ; IF NOT FOUND THEN RAISE EXCEPTION 'Doc Receive Goods with id % not found', pReceiveGoodsId; END IF; vOuStructure := f_get_ou_bu_structure(vRow.ou_id); vDocJournal := f_get_document_journal(vRow.doc_type_id); -- Revert transaksi jurnal PERFORM fzi_revert_admin_journal_trx ( pTenantId, vOuStructure.ou_bu_id, vRow.ou_id, vDocJournal.journal_type, vDocJournal.ledger_code, f_get_year_month_date(vRow.doc_date), 'DAILY', pDatetime, pUserId ); -- Ambil konfigurasi rounding mode SELECT f_get_value_system_config_by_param_code(pTenantId, 'rounding.mode.non.tax') INTO vRoundingModeNonTax; -- Hapus data dari tabel temporary DELETE FROM tt_pu_product_balance WHERE session_id = pSessionId; DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId; DELETE FROM tt_pu_product_balance_summary_stock WHERE session_id = pSessionId; DELETE FROM tt_pu_monthly_price_product WHERE session_id = pSessionId; DELETE FROM tt_update_sell_price_with_formula WHERE session_id = pSessionId; DELETE FROM tt_last_avg_price WHERE session_id = pSessionId; -- Buat data transaksi jurnal PERFORM gl_manage_admin_journal_trx(vRow.tenant_id, vOuStructure.ou_bu_id, vRow.ou_id, vDocJournal.journal_type, vDocJournal.ledger_code, f_get_year_month_date(vRow.doc_date), 'DAILY', pDatetime, pUserId); -- Ambil nilai berikutnya untuk ID jurnal transaksi SELECT NEXTVAL('gl_journal_trx_seq') INTO vJournalTrxId; -- Insert ke gl_journal_trx INSERT INTO gl_journal_trx (journal_trx_id, tenant_id, journal_type, doc_type_id, doc_id, doc_no, doc_date, ou_bu_id, ou_branch_id, ou_sub_bu_id, partner_id, cashbank_id, warehouse_id, ext_doc_no, ext_doc_date, ref_doc_type_id, ref_id, due_date, curr_code, remark, status_doc, workflow_status, "version", create_datetime, create_user_id, update_datetime, update_user_id) SELECT vJournalTrxId, A.tenant_id, vDocJournal.journal_type, A.doc_type_id, A.receive_goods_id, A.doc_no, A.doc_date, 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, A.ref_doc_type_id, A.ref_id, A.doc_date, B.curr_code, A.remark, vStatusDraft, 'DRAFT', 0, pDatetime, pUserId, pDatetime, pUserId FROM pu_receive_goods A JOIN pu_po B ON A.ref_doc_type_id = B.doc_type_id AND A.ref_id = B.po_id WHERE A.receive_goods_id = pReceiveGoodsId; -- Insert ke tt_journal_trx_item INSERT INTO tt_journal_trx_item (session_id, tenant_id, journal_trx_id, line_no, ref_doc_type_id, ref_id, partner_id, product_id, cashbank_id, ou_rc_id, segmen_id, sign_journal, flg_source_coa, activity_gl_id, coa_id, curr_code, qty, uom_id, amount, journal_date, type_rate, numerator_rate, denominator_rate, journal_desc, remark) SELECT pSessionId, A.tenant_id, vJournalTrxId, ROW_NUMBER() OVER (PARTITION BY A.journal_trx_id), A.doc_type_id, B.receive_goods_item_id, A.partner_id, B.product_id, vEmptyId, C.ou_rc_id, vEmptyId, vSignDebit, vProductCOA, C.activity_gl_id, f_get_product_coa_group_product(A.tenant_id, B.product_id), C.curr_code, B.qty_rcv_int, B.base_uom_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(vReceiveGoodsDocTypeId, C.curr_code), vRoundingModeNonTax), A.doc_date, vTypeRate, 1, 1, 'PRODUCT_STOCK', B.remark FROM pu_receive_goods A JOIN pu_receive_goods_item B ON A.receive_goods_id = B.receive_goods_id JOIN pu_po_item C ON B.ref_id = C.po_item_id WHERE A.receive_goods_id = pReceiveGoodsId AND C.flg_stock = 'Y'; INSERT INTO tt_journal_trx_item (session_id, tenant_id, journal_trx_id, line_no, ref_doc_type_id, ref_id, partner_id, product_id, cashbank_id, ou_rc_id, segmen_id, sign_journal, flg_source_coa, activity_gl_id, coa_id, curr_code, qty, uom_id, amount, journal_date, type_rate, numerator_rate, denominator_rate, journal_desc, remark) SELECT pSessionId, A.tenant_id, vJournalTrxId, ROW_NUMBER() OVER (PARTITION BY A.journal_trx_id), A.doc_type_id, B.receive_goods_item_id, A.partner_id, B.product_id, vEmptyId, C.ou_rc_id, C.segment_id, vSignDebit, vActivityCOA, C.activity_gl_id, E.coa_id, C.curr_code, B.qty_rcv_int, B.base_uom_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(vReceiveGoodsDocTypeId, C.curr_code), vRoundingModeNonTax), A.doc_date, vTypeRate, 1, 1, 'PRODUCT_NON_STOCK', B.remark FROM pu_receive_goods A JOIN pu_receive_goods_item B ON A.receive_goods_id = B.receive_goods_id JOIN pu_po_item C ON B.ref_id = C.po_item_id JOIN m_activity_gl E ON C.activity_gl_id = E.activity_gl_id WHERE A.receive_goods_id = pReceiveGoodsId AND C.flg_stock = 'N'; -- Insert untuk inventory item INSERT INTO tt_journal_trx_item (session_id, tenant_id, journal_trx_id, line_no, ref_doc_type_id, ref_id, partner_id, product_id, cashbank_id, ou_rc_id, segmen_id, sign_journal, flg_source_coa, activity_gl_id, coa_id, curr_code, qty, uom_id, amount, journal_date, type_rate, numerator_rate, denominator_rate, journal_desc, remark) SELECT pSessionId, A.tenant_id, vJournalTrxId, ROW_NUMBER() OVER (PARTITION BY A.journal_trx_id), A.doc_type_id, B.receive_goods_item_id, A.partner_id, B.product_id, vEmptyId, C.ou_rc_id, C.segment_id, vSignDebit, vActivityCOA, C.activity_gl_id, E.coa_id, C.curr_code, B.qty_rcv_int, B.base_uom_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(vReceiveGoodsDocTypeId, C.curr_code), vRoundingModeNonTax), A.doc_date, vTypeRate, 1, 1, 'PRODUCT_INVENTARIS', B.remark FROM pu_receive_goods A JOIN pu_receive_goods_item B ON A.receive_goods_id = B.receive_goods_id JOIN pu_po_item C ON B.ref_id = C.po_item_id JOIN m_activity_gl E ON C.activity_gl_id = E.activity_gl_id WHERE A.receive_goods_id = pReceiveGoodsId AND C.flg_stock = 'I'; -- Insert ke gl_journal_trx_item INSERT INTO gl_journal_trx_item (tenant_id, journal_trx_id, line_no, ref_doc_type_id, ref_id, partner_id, product_id, cashbank_id, ou_rc_id, segmen_id, sign_journal, flg_source_coa, activity_gl_id, coa_id, curr_code, qty, uom_id, amount, journal_date, type_rate, numerator_rate, denominator_rate, journal_desc, remark, "version", create_datetime, create_user_id, update_datetime, update_user_id, ou_branch_id, ou_sub_bu_id) SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER (PARTITION BY A.journal_trx_id), A.ref_doc_type_id, A.ref_id, A.partner_id, A.product_id, A.cashbank_id, A.ou_rc_id, A.segmen_id, A.sign_journal, A.flg_source_coa, A.activity_gl_id, A.coa_id, A.curr_code, A.qty, A.uom_id, A.amount, A.journal_date, A.type_rate, A.numerator_rate, A.denominator_rate, A.journal_desc, A.remark, 0, pDatetime, pUserId, pDatetime, pUserId, vOuStructureJournalItem.ou_branch_id, vOuStructureJournalItem.ou_sub_bu_id FROM tt_journal_trx_item A WHERE A.session_id = pSessionId; -- Insert ke gl_journal_trx_mapping INSERT INTO gl_journal_trx_mapping (tenant_id, journal_trx_id, line_no, ref_doc_type_id, ref_id, partner_id, product_id, cashbank_id, ou_rc_id, segmen_id, sign_journal, flg_source_coa, activity_gl_id, coa_id, curr_code, qty, uom_id, amount, journal_date, type_rate, numerator_rate, denominator_rate, journal_desc, remark, "version", create_datetime, create_user_id, update_datetime, update_user_id) SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER (PARTITION BY A.journal_trx_id), vEmptyId, vEmptyId, A.partner_id, vEmptyId, vEmptyId, vEmptyId, vEmptyId, vSignCredit, vSystemCOA, vEmptyId, f_get_system_coa_by_group_coa(A.tenant_id, 'HutangHarusDibayar'), A.curr_code, 0, vEmptyId, SUM(A.amount), A.journal_date, A.type_rate, 1, 1, 'ACCR_AP', vEmptyValue, 0, pDatetime, pUserId, pDatetime, pUserId FROM tt_journal_trx_item A WHERE A.session_id = pSessionId GROUP BY A.tenant_id, A.journal_trx_id, A.partner_id, A.curr_code, A.journal_date, A.type_rate; -- Hapus data dari tabel temporary DELETE FROM tt_pu_product_balance WHERE session_id = pSessionId; DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId; DELETE FROM tt_pu_product_balance_summary_stock WHERE session_id = pSessionId; DELETE FROM tt_pu_monthly_price_product WHERE session_id = pSessionId; DELETE FROM tt_update_sell_price_with_formula WHERE session_id = pSessionId; DELETE FROM tt_last_avg_price WHERE session_id = pSessionId; END; $function$ ;
Leave a Comment