FunctionSubmitGoodsPurchasing

--pu_submit_goods_purchasing

-- DROP FUNCTION pu_submit_goods_purchasing(bigint, character varying, character varying);

CREATE OR REPLACE FUNCTION pu_submit_goods_purchasing(bigint, character varying, character varying)
  RETURNS void AS
$BODY$
DECLARE
    pTenantId           ALIAS FOR $1;
    pSessionId          ALIAS FOR $2;
    pProcessNo          ALIAS FOR $3;

    vProcessId              bigint;
    vPoId                   bigint;
    vGoodsPurchasingId      bigint;
    vUserId                 bigint;
    vDatetime               character varying(14);
    vStatusRelease          character varying(1);
    vStatusFinal            character varying(1);
    vEmptyValue             character varying(1);
    vFlagInvoice            character varying(1);
    vFlagInvoiceY           character varying(1);
    vStatusDraft            character varying(1);
    vSignJournalD           character varying(1);
    vSignJournalC           character varying(1);
    vFlagPayment            character varying(1);
    vFlgSourceCoa           character varying(7);
    vTypeRate               character varying(3);
    vEmptyId                bigint;
    vPurchaseOrderDocTypeId bigint;
    vJournalTrxId           bigint;
    vJournalTrxIdNew        bigint;
    vReceiveGoodsDocTypeId  bigint;
    vInvoiceApBalanceId     bigint;
    vRoundingModeNonTax     character varying(5);
    vProductStatus          character varying(50);
    vGoodsPurchasingDocTypeId   bigint;
    vJournalReceiveGoodsId      bigint;
    vTypeDocumentJournal        bigint;
    vJointDppPpn            character varying(1);

BEGIN

    vFlagInvoice := 'N';
    vFlagInvoiceY := 'Y';
    vFlagPayment := 'N';
    vStatusRelease := 'R';
    vStatusFinal := 'F';
    vStatusDraft := 'D';
    vSignJournalD := 'D';
    vSignJournalC := 'C';
    vFlgSourceCoa := 'PRODUCT';
    vTypeRate := 'COM';
    vEmptyId := -99;
    vEmptyValue := ' ';

    --vJournalReceiveGoodsId := 111;
    vReceiveGoodsDocTypeId := 111;
    vGoodsPurchasingDocTypeId := 160;
    vTypeDocumentJournal := 131;

    vJointDppPpn := f_get_value_system_config_by_param_code(pTenantId, 'joint.dpp.ppn.balance');

    vProductStatus := 'GOOD';

    /*
    SELECT product_status_code INTO vProductStatus
    FROM m_product_status
    WHERE tenant_id = pTenantId AND flg_buy = 'Y';
    */

    SELECT f_get_value_system_config_by_param_code(pTenantId, 'rounding.mode.non.tax') INTO vRoundingModeNonTax;

    SELECT A.process_message_id INTO vProcessId
    FROM t_process_message A
    WHERE A.tenant_id = pTenantId AND
        A.process_name = 'pu_submit_goods_purchasing' AND
        A.process_no = pProcessNo;

    SELECT CAST(A.process_parameter_value AS bigint) INTO vGoodsPurchasingId
    FROM t_process_parameter A
    WHERE A.process_message_id = vProcessId AND
        A.process_parameter_key = 'goodsPurchasingId';

    SELECT CAST(A.process_parameter_value AS bigint) INTO vUserId
    FROM t_process_parameter A
    WHERE A.process_message_id = vProcessId AND
        A.process_parameter_key = 'userId';

    SELECT CAST(A.process_parameter_value AS character varying(14)) INTO vDatetime
    FROM t_process_parameter A
    WHERE A.process_message_id = vProcessId AND
        A.process_parameter_key = 'datetime';


    DELETE FROM tt_pu_product_balance WHERE session_id = pSessionId;

    UPDATE pu_po SET status_doc = vStatusRelease, update_datetime = vDatetime, update_user_id = vUserId
    WHERE po_id = vGoodsPurchasingId;


    INSERT INTO pu_po_balance_invoice
    (tenant_id, ou_id, partner_id, po_id,
    ref_doc_type_id, ref_id, ref_doc_no, ref_doc_date, ref_item_id, qty_rcv_po, po_uom_id,
    curr_code, price_po, item_amount, flg_invoice, invoice_id,
    "version", create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT pTenantId, A.ou_id, A.partner_id, A.ref_id,
        A.doc_type_id, A.po_id, A.doc_no, A.doc_date, B.po_item_id, B.qty_po, B.po_uom_id,
        B.curr_code, B.nett_price_po,
        f_get_amount_before_tax(B.qty_po * (B.gross_price_po - B.discount_amount), B.flg_tax_amount, B.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, B.curr_code), vRoundingModeNonTax),
        vFlagInvoiceY, vEmptyId,
        0, vDatetime, vUserId, vDatetime, vUserId
    FROM pu_po A
    INNER JOIN pu_po_item B
        ON A.po_id = B.po_id
    WHERE A.po_id = vGoodsPurchasingId
    AND A.po_id = B.po_id
    AND A.tenant_id = pTenantId;
    --FILTER LAINNYA


    INSERT INTO pu_po_balance_invoice_tax
    (tenant_id, ou_id, partner_id, po_id,
    ref_doc_type_id, ref_id, ref_item_id, tax_id, flg_amount,
    tax_percentage, curr_code, base_amount, tax_amount, flg_invoice, invoice_id,
    "version", create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT pTenantId, A.ou_id, A.partner_id, A.po_id,
        A.doc_type_id, A.po_id, B.po_item_id, B.tax_id, C.flg_amount,
        B.tax_percentage, B.curr_code,
        f_get_amount_before_tax(B.qty_po * (B.gross_price_po - B.discount_amount), B.flg_tax_amount, B.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, B.curr_code), vRoundingModeNonTax),
        f_tax_rounding(A.tenant_id, f_get_amount_before_tax(B.qty_po * (B.gross_price_po - B.discount_amount), B.flg_tax_amount, B.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, B.curr_code), vRoundingModeNonTax), C.percentage),
        vFlagInvoiceY, vEmptyId,
        0, vDatetime, vUserId, vDatetime, vUserId
    FROM pu_po A
    INNER JOIN pu_po_item B
        ON A.po_id = B.po_id
    INNER JOIN m_tax C
        ON B.tax_id = C.tax_id
    WHERE A.po_id = vGoodsPurchasingId
    AND A.po_id = B.po_id
    AND B.tax_id = C.tax_id
    AND A.tenant_id = pTenantId;
    --FILTER LAINNYA


    --Insert ke tt_pu_product_balance produck nya belum terdaftar di product balance--
    INSERT INTO tt_pu_product_balance
    (session_id, warehouse_id, product_balance_id, tenant_id, ou_id, product_id,
    serial_number, lot_number, product_expired_date, product_year_made,
    product_price_balance_id, product_buy_date, partner_id,
    doc_type_id, ref_id, ref_item_id, doc_no, doc_date,
    po_id, po_no, po_date, po_item_id,
    curr_code, price, qty_rcv, po_uom_id, qty_int_rcv, base_uom_id, flg_stock)
    SELECT pSessionId, B.warehouse_id, vEmptyId, pTenantId, B.ou_id, A.product_id,
        A.serial_number, A.lot_number, A.expired_date, A.year_made,
        vEmptyId, B.doc_date, B.partner_id,
        B.doc_type_id, A.po_id, A.po_item_id, B.doc_no, B.doc_date,
        A.po_id, B.doc_no, B.doc_date, A.po_item_id,
        C.curr_code, C.gross_price_po, SUM(C.qty_po), C.po_uom_id, SUM(C.qty_int), C.base_uom_id, C.flg_stock
    FROM pu_po_item_product A
    INNER JOIN pu_po B
        ON A.po_id = B.po_id
    INNER JOIN pu_po_item C
        ON C.po_item_id = A.po_item_id
    WHERE A.po_id = vGoodsPurchasingId
    AND A.tenant_id = pTenantId
    AND NOT EXISTS
        (
            SELECT 1
            FROM in_product_balance Z
            WHERE Z.tenant_id = pTenantId
                AND Z.product_id = A.product_id
                AND Z.serial_number = A.serial_number
                AND Z.lot_number = A.lot_number
        )
    GROUP BY B.warehouse_id, A.tenant_id, B.ou_id, A.product_id, B.doc_date, B.partner_id,
        B.doc_type_id, A.po_id, A.po_item_id, B.doc_no, B.doc_date,
        A.po_id, B.doc_no, B.doc_date, A.po_item_id,
        C.curr_code, C.gross_price_po,  C.po_uom_id, C.base_uom_id, C.flg_stock;



    --Insert ke tt_pu_product_balance produck nya terdaftar di product balance--
    INSERT INTO tt_pu_product_balance
    (session_id, warehouse_id, product_balance_id, tenant_id, ou_id, product_id,
    serial_number, lot_number, product_expired_date, product_year_made,
    product_price_balance_id, product_buy_date, partner_id,
    doc_type_id, ref_id, ref_item_id, doc_no, doc_date,
    po_id, po_no, po_date, po_item_id,
    curr_code, price, qty_rcv, po_uom_id, qty_int_rcv, base_uom_id, flg_stock)
    SELECT pSessionId, B.warehouse_id, vEmptyId, A.tenant_id, B.ou_id, A.product_id,
        A.serial_number, A.lot_number, A.expired_date, A.year_made,
        vEmptyId, B.doc_date, B.partner_id,
        B.doc_type_id, A.po_id, A.po_item_id, B.doc_no, B.doc_date,
        A.po_id, B.doc_no, B.doc_date, A.po_item_id,
        C.curr_code, C.gross_price_po, SUM(C.qty_po), C.po_uom_id, SUM(C.qty_int), C.base_uom_id, C.flg_stock
    FROM pu_po_item_product A
    INNER JOIN pu_po B
        ON B.po_id = A.po_id
    INNER JOIN pu_po_item C
        ON C.po_item_id = A.po_item_id
    INNER JOIN in_product_balance D
        ON D.tenant_id = A.tenant_id
        AND D.product_id = A.product_id
        AND D.serial_number = A.serial_number
        AND D.lot_number = A.lot_number
    WHERE A.po_id = vGoodsPurchasingId
    AND A.tenant_id = pTenantId
    GROUP BY B.warehouse_id, A.tenant_id, B.ou_id, A.product_id, B.doc_date, B.partner_id,
        B.doc_type_id, A.po_id, A.po_item_id, B.doc_no, B.doc_date,
        A.po_id, B.doc_no, B.doc_date, A.po_item_id,
        C.curr_code, C.gross_price_po,  C.po_uom_id, C.base_uom_id, C.flg_stock;



    /*
     * update product_balance_id dari in_product_balance
     */
    UPDATE tt_pu_product_balance SET product_balance_id = A.product_balance_id
    FROM in_product_balance A
    WHERE tt_pu_product_balance.session_id = pSessionId AND
        tt_pu_product_balance.tenant_id = A.tenant_id AND
        tt_pu_product_balance.product_id = A.product_id AND
        tt_pu_product_balance.serial_number = A.serial_number AND
        tt_pu_product_balance.lot_number = A.lot_number;


    --Insert ke in_product_balance yang id nya masih emptyId/ tidak ada di in product balance--
    INSERT INTO in_product_balance
    (tenant_id, product_id,
    serial_number, lot_number, product_expired_date, product_year_made,
    "version", create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT A.tenant_id, A.product_id,
        A.serial_number, A.lot_number, A.product_expired_date, A.product_year_made,
        0, vDatetime, vUserId, vDatetime, vUserId
    FROM tt_pu_product_balance A
    WHERE A.session_id = pSessionId AND
        A.product_balance_id = vEmptyId
    GROUP BY A.tenant_id, A.product_id, serial_number, lot_number, product_expired_date, product_year_made;


    /*
     * update product_balance_id yang masih empty, ambil dari in_product_balance yang sebelumnya sudah diinsert
     */
    UPDATE tt_pu_product_balance SET product_balance_id = A.product_balance_id
    FROM in_product_balance A
    WHERE tt_pu_product_balance.session_id = pSessionId AND
        tt_pu_product_balance.product_balance_id = vEmptyId AND
        tt_pu_product_balance.tenant_id = A.tenant_id AND
        tt_pu_product_balance.product_id = A.product_id AND
        tt_pu_product_balance.serial_number = A.serial_number AND
        tt_pu_product_balance.lot_number = A.lot_number;


    --Insert ke in_product_price_balance dengan product balance yang sudah diinsert sebelumnya--
    INSERT INTO in_product_price_balance
    (tenant_id, ou_id, product_id, product_balance_id,
    product_buy_date, partner_id, doc_type_id, ref_id, doc_no, doc_date,
    curr_code, amount, qty, uom_id,
    "version", create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT A.tenant_id, A.ou_id, A.product_id, A.product_balance_id,
        A.doc_date, A.partner_id, A.doc_type_id, A.ref_item_id, A.doc_no, A.doc_date,
        A.curr_code, SUM(f_get_amount_before_tax(B.qty_po * A.price, B.flg_tax_amount, B.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, A.curr_code), vRoundingModeNonTax)),
        SUM(B.qty_int), A.base_uom_id,
        0, vDatetime, vUserId, vDatetime, vUserId
    FROM tt_pu_product_balance A
    INNER JOIN pu_po_item B
        ON A.po_id = B.po_id
    WHERE A.session_id = pSessionId AND
         A.po_id = B.po_id
    GROUP BY A.tenant_id, A.ou_id, A.product_id, A.product_balance_id, A.doc_date,
        A.partner_id, A.doc_type_id, A.ref_item_id, A.doc_no, A.curr_code, A.base_uom_id;


    UPDATE tt_pu_product_balance SET product_price_balance_id = A.product_price_balance_id
    FROM in_product_price_balance A
    WHERE tt_pu_product_balance.session_id = pSessionId AND
        tt_pu_product_balance.tenant_id = A.tenant_id AND
        tt_pu_product_balance.ou_id = A.ou_id AND
        tt_pu_product_balance.product_id = A.product_id AND
        tt_pu_product_balance.product_balance_id = A.product_balance_id AND
        tt_pu_product_balance.partner_id = A.partner_id AND
        tt_pu_product_balance.doc_type_id = A.doc_type_id AND
        tt_pu_product_balance.ref_item_id = A.ref_id AND
        tt_pu_product_balance.doc_no = A.doc_no AND
        tt_pu_product_balance.doc_date = A.doc_date;


    /*
     * create summary dari tt_pu_product_balance untuk update yg sudah ada di in_product_balance_stock
     */
    INSERT INTO tt_pu_product_balance_summary_stock
            (session_id, warehouse_id, product_balance_id, tenant_id, product_id,
            product_price_balance_id, qty_rcv, po_uom_id, qty_int_rcv, base_uom_id, flg_stock)
    SELECT  pSessionId, A.warehouse_id, A.product_balance_id, A.tenant_id, A.product_id,
            vEmptyId, SUM(A.qty_rcv), A.po_uom_id, SUM(A.qty_int_rcv), A.base_uom_id, A.flg_stock
    FROM    tt_pu_product_balance A
    WHERE   A.session_id = pSessionId
    GROUP BY A.warehouse_id, A.product_balance_id, A.tenant_id, A.product_id,
             A.po_uom_id, A.base_uom_id, A.flg_stock;


    /*
     * update product_balance_stock, yang sudah ada di in_product_balance_stock
     */
    UPDATE in_product_balance_stock SET qty = qty + A.qty_int_rcv,
    update_datetime = vDatetime, update_user_id = vUserId,
    version = version + 1
    FROM tt_pu_product_balance_summary_stock A
    WHERE A.session_id = pSessionId AND
        in_product_balance_stock.product_id = A.product_id AND
        in_product_balance_stock.tenant_id = A.tenant_id AND
        in_product_balance_stock.warehouse_id = A.warehouse_id AND
        in_product_balance_stock.product_balance_id = A.product_balance_id AND
        in_product_balance_stock.product_status = vProductStatus AND
        A.flg_stock = 'Y';



    /*
     * insert data in_product_balance_stock yang datanya belum ada
     */
    INSERT INTO in_product_balance_stock
    (tenant_id, warehouse_id, product_id, product_balance_id, product_status, base_uom_id, qty,
    "version", create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT A.tenant_id, A.warehouse_id, A.product_id, A.product_balance_id, vProductStatus,
        A.base_uom_id, SUM(A.qty_int_rcv),
        0, vDatetime, vUserId, vDatetime, vUserId
    FROM tt_pu_product_balance A
    WHERE A.session_id = pSessionId AND
        A.flg_stock = 'Y' AND
        NOT EXISTS (SELECT 1 FROM in_product_balance_stock B
                    WHERE A.tenant_id = B.tenant_id AND
                        A.warehouse_id = B.warehouse_id AND
                        A.product_id = B.product_id AND
                        A.product_balance_id = B.product_balance_id AND
                        B.product_status = vProductStatus)
    GROUP BY A.tenant_id, A.warehouse_id, A.product_id, A.product_balance_id, A.base_uom_id;


    /*
     * insert data product_price_balance_stock
     */
    INSERT INTO in_product_price_balance_stock
    (tenant_id, warehouse_id, product_id, product_balance_id,
    product_price_balance_id, product_status, base_uom_id, qty,
    "version", create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT A.tenant_id, A.warehouse_id, A.product_id, A.product_balance_id,
        A.product_price_balance_id, vProductStatus, A.base_uom_id, SUM(A.qty_int_rcv),
        0, vDatetime, vUserId, vDatetime, vUserId
    FROM tt_pu_product_balance A
    WHERE A.session_id = pSessionId AND
        A.flg_stock = 'Y'
    GROUP BY A.tenant_id, A.warehouse_id, A.product_id, A.product_balance_id, A.product_price_balance_id, A.base_uom_id;


    /*
     * buat data log product balance stock
     */
    INSERT INTO in_log_product_balance_stock
    (tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
     product_id, product_balance_id, warehouse_id, product_status, base_uom_id, qty,
    "version", create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT A.tenant_id, B.ou_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.partner_id,
        A.product_id, A.product_balance_id, A.warehouse_id, vProductStatus, A.base_uom_id, SUM(A.qty_int_rcv),
        0, vDatetime, vUserId, vDatetime, vUserId
    FROM tt_pu_product_balance A, m_warehouse_ou B
    WHERE A.session_id = pSessionId AND
        A.flg_stock = 'Y' AND
        A.warehouse_id = B.warehouse_id
    GROUP BY A.tenant_id, B.ou_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.partner_id,
        A.product_id, A.product_balance_id, A.warehouse_id, A.base_uom_id;


    /*
     * buat data log product price balance stock
     */
    INSERT INTO in_log_product_price_balance_stock
    (tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
     product_id, warehouse_id, product_balance_id, product_price_balance_id, product_status, base_uom_id, qty,
    "version", create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT A.tenant_id, A.ou_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.partner_id,
        A.product_id, A.warehouse_id, A.product_balance_id, A.product_price_balance_id, vProductStatus, A.base_uom_id, SUM(A.qty_int_rcv),
        0, vDatetime, vUserId, vDatetime, vUserId
    FROM tt_pu_product_balance A
    WHERE A.session_id = pSessionId AND
        A.flg_stock = 'Y'
    GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.partner_id,
        A.product_id, A.warehouse_id, A.product_balance_id, A.product_price_balance_id, A.base_uom_id;


    /*
     * add data balance receive goods item yang akan digunakan di inventory,
     * saat akan membuat claim note
     */
    INSERT INTO in_balance_receive_goods_item
    (receive_goods_item_id, tenant_id, ou_id, receive_goods_id, doc_no, doc_date, partner_id,
      po_id, po_no, po_date, po_item_id,
      qty_rcv, qty_return, po_uom_id, qty_int_rcv,
      qty_int_return, base_uom_id, status_item,
      "version", create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT A.ref_item_id, A.tenant_id, A.ou_id, A.ref_id, A.doc_no, A.doc_date, A.partner_id,
            A.po_id, A.po_no, A.po_date, A.po_item_id,
            SUM(A.qty_rcv), 0, A.po_uom_id, SUM(A.qty_int_rcv),
            0, A.base_uom_id, vStatusRelease,
        0, vDatetime, vUserId, vDatetime, vUserId
    FROM tt_pu_product_balance A
    WHERE A.session_id = pSessionId
    GROUP BY A.ref_item_id, A.tenant_id, A.ou_id, A.ref_id, A.doc_no, A.doc_date, A.partner_id,
            A.po_id, A.po_no, A.po_date, A.po_item_id, A.po_uom_id, A.base_uom_id;



    /*
     * create monthly price product
     */
    INSERT INTO tt_pu_monthly_price_product
    (session_id, tenant_id, ou_id, year_month_date,
    product_id, curr_code, amount, qty, base_uom_id,
     ref_doc_type_id, ref_id, ref_doc_no, ref_doc_date, source_price,
     flg_tax_amount, tax_id, tax_percentage)
    SELECT pSessionId, A.tenant_id, A.ou_id, C.year_month_date,
        A.product_id, A.curr_code, SUM(f_get_amount_before_tax(A.qty_rcv * A.price, B.flg_tax_amount, B.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, A.curr_code), vRoundingModeNonTax)),
        SUM(A.qty_int_rcv), A.base_uom_id,
        A.doc_type_id, A.ref_id, A.doc_no, A.doc_date,
        'PURCHASING PO NO : '|| A.po_no || ' DATE : '|| A.po_date,
        B.flg_tax_amount, B.tax_id, B.tax_percentage
    FROM tt_pu_product_balance A, pu_po_item B, dt_date C
    WHERE A.session_id = pSessionId AND
        A.po_item_id = B.po_item_id AND
        A.doc_date = C.string_date
    GROUP BY A.tenant_id, A.ou_id, C.year_month_date, A.product_id, A.curr_code, A.base_uom_id,
        A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.po_no, A.po_date, B.flg_tax_amount, B.tax_id, B.tax_percentage;

    /*
     * Update pu_monthly_price_product
     */
    UPDATE pu_monthly_price_product SET curr_code = A.curr_code, amount = A.amount,
                                        qty = A.qty, ref_doc_type_id = A.ref_doc_type_id,
                                        ref_id = A.ref_id, ref_doc_no = A.ref_doc_no, ref_doc_date = A.ref_doc_date,
                                        source_price = A.source_price, flg_tax_amount = A.flg_tax_amount,
                                        tax_id = A.tax_id, tax_percentage = A.tax_percentage
    FROM tt_pu_monthly_price_product A
    WHERE A.session_id = pSessionId AND
        pu_monthly_price_product.tenant_id = A.tenant_id AND
        pu_monthly_price_product.ou_id = A.ou_id AND
        pu_monthly_price_product.year_month_date = A.year_month_date AND
        pu_monthly_price_product.product_id = A.product_id;

    INSERT INTO pu_monthly_price_product
    (tenant_id, ou_id, year_month_date, product_id,
    curr_code, amount, qty, base_uom_id, source_price,
    ref_doc_type_id, ref_id, ref_doc_no, ref_doc_date,
    "version", create_datetime, create_user_id, update_datetime, update_user_id,
    flg_tax_amount, tax_id, tax_percentage)
    SELECT A.tenant_id, A.ou_id, A.year_month_date, A.product_id,
            A.curr_code, SUM(A.amount), SUM(A.qty), A.base_uom_id, A.source_price,
            A.ref_doc_type_id, A.ref_id, A.ref_doc_no, A.ref_doc_date,
            0, vDatetime, vUserId, vDatetime, vUserId,
            A.flg_tax_amount, A.tax_id, A.tax_percentage
    FROM tt_pu_monthly_price_product A
    WHERE A.session_id = pSessionId AND
        NOT EXISTS (SELECT 1 FROM pu_monthly_price_product B
                    WHERE A.tenant_id = B.tenant_id AND
                        A.ou_id = B.ou_id AND
                        A.product_id = B.product_id AND
                        A.year_month_date = B.year_month_date)
    GROUP BY A.tenant_id, A.ou_id, A.year_month_date, A.product_id, A.curr_code, A.base_uom_id, A.source_price,
             A.ref_doc_type_id, A.ref_id, A.ref_doc_no, A.ref_doc_date, A.flg_tax_amount, A.tax_id, A.tax_percentage;

    INSERT INTO pu_log_monthly_price_product
    (tenant_id, ou_id, year_month_date, product_id,
    curr_code, amount, qty, base_uom_id, source_price,
    ref_doc_type_id, ref_id, ref_doc_no, ref_doc_date,
    "version", create_datetime, create_user_id, update_datetime, update_user_id,
    flg_tax_amount, tax_id, tax_percentage)
    SELECT A.tenant_id, A.ou_id, A.year_month_date, A.product_id,
        A.curr_code, SUM(A.amount), SUM(A.qty), A.base_uom_id, A.source_price,
        A.ref_doc_type_id, A.ref_id, A.ref_doc_no, A.ref_doc_date,
        0, vDatetime, vUserId, vDatetime, vUserId,
        A.flg_tax_amount, A.tax_id, A.tax_percentage
    FROM tt_pu_monthly_price_product A
    WHERE A.session_id = pSessionId
    GROUP BY A.tenant_id, A.ou_id, A.year_month_date, A.product_id,
             A.curr_code, A.base_uom_id, A.source_price, A.ref_doc_type_id, A.ref_id,
             A.ref_doc_no, A.ref_doc_date, A.flg_tax_amount, A.tax_id, A.tax_percentage;




    /*
     * membuat data transaksi jurnal
     */


    SELECT NEXTVAL('gl_journal_trx_seq') INTO vJournalTrxId;

    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, ref_doc_no, ref_doc_date,
    due_date, curr_code, remark, status_doc, workflow_status,
    "version", create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT vJournalTrxId, pTenantId, (f_get_document_journal(vReceiveGoodsDocTypeId)).journal_type, vReceiveGoodsDocTypeId, A.po_id, A.doc_no, A.doc_date,
        (f_get_ou_bu_structure(A.ou_id)).ou_bu_id, (f_get_ou_bu_structure(A.ou_id)).ou_branch_id, (f_get_ou_bu_structure(A.ou_id)).ou_sub_bu_id,
        A.partner_id, vEmptyId, A.warehouse_id, A.ext_doc_no, A.ext_doc_date,
        A.doc_type_id, A.po_id, A.doc_no, A.doc_date,
        A.doc_date, A.curr_code, A.remark, vStatusDraft, 'DRAFT',
        0, vDatetime, vUserId, vDatetime, vUserId
    FROM pu_po A
    WHERE A.po_id = vGoodsPurchasingId
    AND A.tenant_id = pTenantId;
        --A.ref_doc_type_id = B.doc_type_id AND
        --A.ref_id = B.po_id;

    /*------------------------------------------------*/

    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 pTenantId, vJournalTrxId, ROW_NUMBER() OVER ( PARTITION BY vJournalTrxId ),
        A.doc_type_id, B.po_item_id,
        A.partner_id, B.product_id, vEmptyId, B.ou_rc_id,
        vEmptyId, vSignJournalD, 'PRODUCT', B.activity_gl_id,
        B.product_coa_id, B.curr_code, B.qty_int, B.base_uom_id,
        SUM(f_get_amount_before_tax(B.qty_po * (B.gross_price_po - B.discount_amount), B.flg_tax_amount, B.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, B.curr_code), vRoundingModeNonTax)),
        A.doc_date, 'COM',
        1, 1, 'PRODUCT_STOCK', B.remark,
        0, vDatetime, vUserId, vDatetime, vUserId,
        vEmptyId, vEmptyId
    FROM pu_po A
    INNER JOIN pu_po_item B
        ON B.po_id = A.po_id
    WHERE A.po_id = vGoodsPurchasingId
    AND A.tenant_id = pTenantId
    AND B.flg_stock = 'Y'
    GROUP BY A.tenant_id, A.doc_type_id, B.po_item_id, A.partner_id, B.product_id, B.ou_rc_id,
          B.activity_gl_id, B.product_coa_id, B.curr_code, B.qty_int, B.base_uom_id, A.doc_date;

    /*------------------------------------------------*/

    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, vJournalTrxId, ROW_NUMBER() OVER ( PARTITION BY vJournalTrxId ),
        vEmptyId, vEmptyId,
        A.partner_id, vEmptyId, vEmptyId, vEmptyId,
        vEmptyId, vSignJournalC, 'SYSTEM', vEmptyId,
        f_get_system_coa_by_group_coa(A.tenant_id, 'HutangHarusDibayar'),
        A.curr_code, 0, vEmptyId,
        SUM(f_get_amount_before_tax(B.qty_po * (B.gross_price_po - B.discount_amount), B.flg_tax_amount, B.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, B.curr_code), vRoundingModeNonTax)),
        A.doc_date, 'COM',
        1, 1, 'ACCR_AP', B.remark,
        0, vDatetime, vUserId, vDatetime, vUserId
    FROM pu_po A
    INNER JOIN pu_po_item B
        ON B.po_id = A.po_id
    WHERE A.po_id = vGoodsPurchasingId
    AND A.tenant_id = pTenantId
    AND B.flg_stock = 'Y'
    GROUP BY A.tenant_id, A.doc_type_id, B.po_item_id, A.partner_id, B.product_id, B.ou_rc_id,
          A.curr_code, B.activity_gl_id, B.product_coa_id, B.curr_code, B.qty_int, B.base_uom_id, A.doc_date;

    /*------------------------------------------------*/

    SELECT nextval('fi_invoice_ap_balance_seq') INTO vInvoiceApBalanceId;

    IF vJointDppPpn = 'N' THEN

        INSERT INTO fi_invoice_ap_balance
        ( invoice_ap_balance_id, tenant_id, ou_id, doc_type_id, invoice_ap_id,
        doc_no, doc_date, ext_doc_no, ext_doc_date,
        ref_doc_type_id, ref_id,  partner_id, due_date,
        curr_code, amount, remark, payment_amount, flg_payment,
        "version", create_datetime, create_user_id, update_datetime, update_user_id)
        SELECT vInvoiceApBalanceId, pTenantId, A.ou_id, A.doc_type_id, A.po_id,
                A.doc_no, A.doc_date, A.ext_doc_no, A.ext_doc_date,
                vEmptyId, vEmptyId, A.partner_id, A.doc_date,
                A.curr_code, SUM(f_get_amount_before_tax(B.qty_po * (B.gross_price_po - B.discount_amount), B.flg_tax_amount, B.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, B.curr_code), vRoundingModeNonTax)),
                A.remark, 0, vFlagPayment,
                0, vDatetime, vUserId, vDatetime, vUserId
        FROM pu_po A
        INNER JOIN pu_po_item B
            ON B.po_id = A.po_id
        WHERE A.po_id = vGoodsPurchasingId
        AND A.tenant_id = pTenantId
        GROUP BY A.ou_id, A.doc_type_id, A.po_id, A.doc_no, A.doc_date,
                 A.curr_code, A.ext_doc_no, A.ext_doc_date, A.partner_id, A.doc_date, A.curr_code;


        INSERT INTO fi_invoice_tax_ap_balance
        ( tenant_id, ou_id, doc_type_id, invoice_ap_balance_id,
        partner_id, tax_id, tax_no, tax_date,
        curr_code, tax_amount, tax_curr_code, gov_tax_amount,
        due_date, remark, payment_amount, flg_payment,
        "version", create_datetime, create_user_id, update_datetime, update_user_id,
        gov_base_amount )
        SELECT pTenantId, A.ou_id, A.doc_type_id, vInvoiceApBalanceId,
            A.partner_id, B.tax_id, B.tax_no, B.tax_date,
            A.curr_code,
            SUM(f_tax_rounding(A.tenant_id, f_get_amount_before_tax(B.qty_po * (B.gross_price_po - B.discount_amount), B.flg_tax_amount, B.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, B.curr_code), vRoundingModeNonTax), C.percentage)),
            A.curr_code,
            SUM(f_tax_rounding(A.tenant_id, f_get_amount_before_tax(B.qty_po * (B.gross_price_po - B.discount_amount), B.flg_tax_amount, B.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, B.curr_code), vRoundingModeNonTax), C.percentage)),
            A.doc_date, A.remark, 0, vFlagPayment,
            0, vDatetime, vUserId, vDatetime, vUserId,
            SUM(f_tax_rounding(A.tenant_id, f_get_amount_before_tax(B.qty_po * (B.gross_price_po - B.discount_amount), B.flg_tax_amount, B.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, B.curr_code), vRoundingModeNonTax), C.percentage))
        FROM pu_po A
        INNER JOIN pu_po_item B
            ON B.po_id = A.po_id
        INNER JOIN m_tax C
            ON C.tax_id = B.tax_id
        WHERE A.po_id = vGoodsPurchasingId
        AND A.tenant_id = pTenantId;

        perform fi_insert_vat_in_for_efaktur(pTenantId, pSessionId, vGoodsPurchasingId, vUserId, vDatetime);

    ELSE

        INSERT INTO fi_invoice_ap_balance
        ( invoice_ap_balance_id, tenant_id, ou_id, doc_type_id, invoice_ap_id,
        doc_no, doc_date, ext_doc_no, ext_doc_date,
        ref_doc_type_id, ref_id,  partner_id, due_date, curr_code,
        amount, remark, payment_amount, flg_payment,
        "version", create_datetime, create_user_id, update_datetime, update_user_id)
        SELECT vInvoiceApBalanceId, pTenantId, A.ou_id, A.doc_type_id, A.po_id,
                A.doc_no, A.doc_date, A.ext_doc_no, A.ext_doc_date,
                vEmptyId, vEmptyId, A.partner_id, A.doc_date, A.curr_code,
                SUM(f_get_amount_before_tax(B.qty_po * (B.gross_price_po - B.discount_amount), B.flg_tax_amount, B.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, B.curr_code), vRoundingModeNonTax))
                    + SUM(f_tax_rounding(A.tenant_id, f_get_amount_before_tax(B.qty_po * (B.gross_price_po - B.discount_amount), B.flg_tax_amount, B.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, B.curr_code), vRoundingModeNonTax), COALESCE(B.tax_percentage,0))),
                A.remark, 0, vFlagPayment,
                0, vDatetime, vUserId, vDatetime, vUserId
        FROM pu_po A
        INNER JOIN pu_po_item B
            ON B.po_id = A.po_id
        LEFT JOIN m_tax C
            ON C.tax_id = B.tax_id
        WHERE A.po_id = vGoodsPurchasingId
        AND A.tenant_id = pTenantId
        GROUP BY A.ou_id, A.doc_type_id, A.po_id, A.doc_no, A.doc_date,
                 A.ext_doc_no, A.ext_doc_date, A.partner_id, A.doc_date, A.curr_code;

    /*
        INSERT INTO fi_invoice_ap_balance
        ( invoice_ap_balance_id, tenant_id, ou_id, doc_type_id, invoice_ap_id,
        doc_no, doc_date, ext_doc_no, ext_doc_date,
        ref_doc_type_id, ref_id,  partner_id, due_date, curr_code,
        amount, remark, payment_amount, flg_payment,
        "version", create_datetime, create_user_id, update_datetime, update_user_id)
        SELECT vInvoiceApBalanceId, pTenantId, A.ou_id, A.doc_type_id, A.po_id,
                A.doc_no, A.doc_date, A.ext_doc_no, A.ext_doc_date,
                vEmptyId, vEmptyId, A.partner_id, A.doc_date, A.curr_code,
                SUM(f_get_amount_before_tax(B.qty_po * (B.gross_price_po - B.discount_amount), B.flg_tax_amount, B.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, B.curr_code), vRoundingModeNonTax))
                + SUM(f_tax_rounding(A.tenant_id, f_get_amount_before_tax(B.qty_po * (B.gross_price_po - B.discount_amount), B.flg_tax_amount, B.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, B.curr_code), vRoundingModeNonTax), C.percentage)),
                A.remark, 0, vFlagPayment,
                0, vDatetime, vUserId, vDatetime, vUserId
        FROM pu_po A
        INNER JOIN pu_po_item B
            ON B.po_id = A.po_id
        INNER JOIN m_tax C
            ON C.tax_id = B.tax_id
        WHERE A.po_id = vGoodsPurchasingId
        AND A.tenant_id = pTenantId
        GROUP BY A.ou_id, A.doc_type_id, A.po_id, A.doc_no, A.doc_date,
                 A.ext_doc_no, A.ext_doc_date, A.partner_id, A.doc_date, A.curr_code;
    */

        perform fi_insert_vat_in_invoice_ap_for_efaktur(pTenantId, pSessionId, vGoodsPurchasingId, vUserId, vDatetime);

    END IF;


    /*
     * Insert ke journal gl
     */
    SELECT NEXTVAL('gl_journal_trx_seq') INTO vJournalTrxIdNew;

    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, ref_doc_no, ref_doc_date,
    due_date, curr_code, remark, status_doc, workflow_status,
    "version", create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT vJournalTrxIdNew, pTenantId, (f_get_document_journal(vTypeDocumentJournal)).journal_type,
        vTypeDocumentJournal, A.po_id, A.doc_no, A.doc_date,
        (f_get_ou_bu_structure(A.ou_id)).ou_bu_id, (f_get_ou_bu_structure(A.ou_id)).ou_branch_id, (f_get_ou_bu_structure(A.ou_id)).ou_sub_bu_id,
        A.partner_id, vEmptyId, vEmptyId, A.ext_doc_no, A.ext_doc_date,
        A.doc_type_id, A.po_id, A.doc_no, A.doc_date,
        A.doc_date, A.curr_code, A.remark, vStatusDraft, 'DRAFT',
        0, vDatetime, vUserId, vDatetime, vUserId
    FROM pu_po A
    WHERE A.po_id = vGoodsPurchasingId
    AND A.tenant_id = pTenantId;


    --Insert untuk item 1--
    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,
    gl_curr_code, gl_amount )
    SELECT pSessionId, pTenantId, vJournalTrxIdNew, 1,
        A.doc_type_id, B.po_item_id,
        A.partner_id,  vEmptyId, vEmptyId, vEmptyId,
        vEmptyId, vSignJournalD, 'SYSTEM', vEmptyId,
        f_get_system_coa_by_group_coa(A.tenant_id, 'HutangHarusDibayar'), B.curr_code, 0, vEmptyId,
        SUM(f_get_amount_before_tax(B.qty_po * (B.gross_price_po - B.discount_amount), B.flg_tax_amount, B.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, B.curr_code), vRoundingModeNonTax)),
        A.doc_date, 'COM',
        1, 1, 'ACCR_AP', B.remark,
        vEmptyValue, vEmptyId
    FROM pu_po A
    INNER JOIN pu_po_item B
        ON B.po_id = A.po_id
    WHERE A.po_id = vGoodsPurchasingId
    AND A.tenant_id = pTenantId
    AND B.flg_stock = 'Y'
    GROUP BY A.tenant_id, A.doc_type_id, B.po_item_id, A.partner_id, B.product_id, B.ou_rc_id,
          B.activity_gl_id, B.product_coa_id, B.curr_code, B.qty_int, B.base_uom_id, A.doc_date;

    --Insert untuk item 2--
    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,
    gl_curr_code, gl_amount )
    SELECT pSessionId, pTenantId, vJournalTrxIdNew, 1,
        A.doc_type_id, B.po_id,
        A.partner_id, B.product_id, vEmptyId, vEmptyId,
        vEmptyId, vSignJournalD, 'TAX', vEmptyId,
        C.receive_coa_id, B.curr_code, 0, vEmptyId,
        SUM(f_tax_rounding(A.tenant_id, f_get_amount_before_tax(B.qty_po * (B.gross_price_po - B.discount_amount), B.flg_tax_amount, B.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, B.curr_code), vRoundingModeNonTax), C.percentage)),
        A.doc_date, 'COM',
        1, 1, 'VAT_IN', B.remark,
        vEmptyId, vEmptyId
    FROM pu_po A
    INNER JOIN pu_po_item B
        ON B.po_id = A.po_id
    INNER JOIN m_tax C
        ON C.tax_id = B.tax_id
    WHERE A.po_id = vGoodsPurchasingId
    AND A.tenant_id = pTenantId
    AND B.flg_stock = 'Y'
    GROUP BY A.tenant_id, A.doc_type_id, B.po_item_id, A.partner_id, B.product_id, B.ou_rc_id,
          C.receive_coa_id, B.activity_gl_id, B.product_coa_id, B.curr_code, B.qty_int, B.base_uom_id, A.doc_date;


    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 pTenantId, 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, vDatetime, vUserId, vDatetime, vUserId,
        vEmptyId, vEmptyId
    FROM tt_journal_trx_item A
    WHERE A.session_id = pSessionId;


    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 pTenantId, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id ),
        vEmptyId, vEmptyId,
        A.partner_id, vEmptyId, vEmptyId, vEmptyId,
        vEmptyId, vSignJournalC, 'SYSTEM', vEmptyId,
        f_get_ap_coa_partner(A.tenant_id, A.partner_id),
        A.curr_code, 0, vEmptyId,
        SUM(A.amount),
        A.journal_date, 'COM',
        1, 1, 'AP', A.remark,
        0, vDatetime, vUserId, vDatetime, vUserId
    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, A.remark;


    --Delete table temp tt_pu_product_balance--
    DELETE FROM tt_pu_product_balance WHERE session_id = pSessionId;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
  /