func fzi_regenerate_journal_receive_goods

-- 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$
;