pu_create_po_internal_from_temporary_20180329

/**
 * Adrian, Mar 27, 2018
 * Function untuk membuat PO Internal dari table temporary
 */
CREATE OR REPLACE FUNCTION pu_create_po_internal_from_temporary(bigint, character varying, bigint, bigint, bigint, character varying)
  RETURNS void AS
$BODY$
DECLARE
    pTenantId           ALIAS FOR $1;
    pSessionId          ALIAS FOR $2;
    pTempPoInternalId   ALIAS FOR $3;
    pUserId             ALIAS FOR $4;
    pRoleId             ALIAS FOR $5;
    pDatetime           ALIAS FOR $6;

    vDocTypeIdInternalPurchasing            bigint := 151;
    vDocTypeIdSalesInvoice                  bigint := 321;
    vPoInternalScheme                       character varying := 'CE01';
    vParamCodeRoundingModeNonTax            character varying := 'rounding.mode.non.tax';
    vParamCodeRoundingScaleNonTax           character varying := 'rounding.scale.non.tax';
    vParamCodeRoundingModeTax               character varying := 'rounding.mode.tax';
    vParamCodeRoundingScaleTax              character varying := 'rounding.scale.tax';
    vParamCodeValutaResmi                   character varying := 'ValutaResmi';
    vGroupPartnerCodeCustomer               character varying := 'C';
    vGroupPartnerCodeSupplier               character varying := 'S';
    vTypePartnerCodeCustomerInternal        character varying := 'CUSI';
    vTypePartnerCodeSupplierInternal        character varying := 'SUPI';
    vFlagCashbankCash                       character varying := 'C';
    vComboIdYesNo                           character varying := 'YESNO';
    vComboIdCurrency                        character varying := 'CURRENCY';
    vStatusRelease                          character varying := 'R';
    vWorkflowApproved                       character varying := 'APPROVED';
    vRoundingModeDown                       character varying := 'RD';
    vFlagOuPoSell                           character varying := 'S';
    vFlgUserRole                            character varying := 'R';
    vYes                                    character varying := 'Y';
    vNo                                     character varying := 'N';
    vEmptyId                                bigint := -99;
    vEmptyValue                             character varying := '';

    vTempPoInternalHeader                   RECORD;
    vRounding                               integer;
    vRoundingMode                           character varying;
    vRoundingTax                            integer;
    vRoundingModeTax                        character varying;
    vValutaResmi                            character varying;
    vDecimalForRounding                     integer;
    vPoInternalId                           bigint;
    vGrandTotal                             numeric;
    vCbInsufficientList                     text;
    vPoInternalFlowId                       bigint;

BEGIN

    DELETE FROM cb_insufficient_cashbank_balance WHERE session_id = pSessionId;

    -- Mendapatkan default approval flow ID
    IF NOT EXISTS (
        SELECT 1
        FROM awe_flow
        WHERE scheme = vPoInternalScheme AND
            flg_validate = vYes AND
            active = vYes
    ) THEN
        RAISE EXCEPTION 'No Approval Flow for PO Internal';
    ELSE
        SELECT awe_flow_id INTO vPoInternalFlowId
        FROM awe_flow
        WHERE scheme = vPoInternalScheme AND
            flg_validate = vYes AND
            active = vYes;
    END IF;

    --Validasi data temporary untuk header ada
    IF NOT EXISTS (
        SELECT 1
        FROM tt_po_internal A
        WHERE A.session_id = pSessionId AND
            A.tenant_id = pTenantId AND
            A.tt_po_internal_id = pTempPoInternalId
    ) THEN
        RAISE EXCEPTION 'Temporary PO Internal is not found';
    END IF;

    SELECT A.*
    FROM tt_po_internal A
    WHERE A.session_id = pSessionId AND
        A.tenant_id = pTenantId AND
        A.tt_po_internal_id = pTempPoInternalId
    INTO vTempPoInternalHeader;

    SELECT f_get_value_system_config_by_param_code(pTenantId, vParamCodeRoundingModeNonTax) INTO vRoundingMode;
    SELECT f_get_value_system_config_by_param_code(pTenantId, vParamCodeRoundingScaleNonTax) INTO vRounding;
    SELECT f_get_value_system_config_by_param_code(pTenantId, vParamCodeRoundingModeTax) INTO vRoundingModeTax;
    SELECT f_get_value_system_config_by_param_code(pTenantId, vParamCodeRoundingScaleTax) INTO vRoundingTax;
    SELECT f_get_value_system_config_by_param_code(pTenantId, vParamCodeValutaResmi) INTO vValutaResmi;
    SELECT f_get_digit_decimal_doc_curr(vDocTypeIdInternalPurchasing, vTempPoInternalHeader.curr_code) INTO vDecimalForRounding;

    --Validasi data temporary untuk item ada
    IF NOT EXISTS (
        SELECT 1
        FROM tt_po_internal A
        INNER JOIN tt_po_internal_item B
            ON A.session_id = B.session_id AND
                A.tenant_id = B.tenant_id AND
                A.tt_po_internal_id = B.tt_po_internal_id
        WHERE A.session_id = pSessionId AND
            A.tenant_id = pTenantId AND
            A.tt_po_internal_id = pTempPoInternalId
    ) THEN
        RAISE EXCEPTION 'Temporary PO Internal item is not found';
    END IF;

    --Validasi data temporary untuk product ada
    IF NOT EXISTS (
        SELECT 1
        FROM tt_po_internal A
        INNER JOIN tt_po_internal_item B
            ON A.session_id = B.session_id AND
                A.tenant_id = B.tenant_id AND
                A.tt_po_internal_id = B.tt_po_internal_id
        INNER JOIN tt_po_internal_product C
            ON B.session_id = C.session_id AND
                B.tenant_id = C.tenant_id AND
                B.tt_po_internal_item_id = C.tt_po_internal_item_id
        WHERE A.session_id = pSessionId AND
            A.tenant_id = pTenantId AND
            A.tt_po_internal_id = pTempPoInternalId
    ) THEN
        RAISE EXCEPTION 'Temporary PO Internal product is not found';
    END IF;

    --Validasi product_id pada item dan product_id pada product sama
    IF EXISTS (
        SELECT 1
        FROM tt_po_internal A
        INNER JOIN tt_po_internal_item B
            ON A.session_id = B.session_id AND
                A.tenant_id = B.tenant_id AND
                A.tt_po_internal_id = B.tt_po_internal_id
        INNER JOIN tt_po_internal_product C
            ON B.session_id = C.session_id AND
                B.tenant_id = C.tenant_id AND
                B.tt_po_internal_item_id = C.tt_po_internal_item_id
        INNER JOIN in_product_balance_stock D
            ON C.product_balance_stock_id = D.product_balance_stock_id AND
                C.tenant_id = D.tenant_id
        WHERE A.session_id = pSessionId AND
            A.tenant_id = pTenantId AND
            A.tt_po_internal_id = pTempPoInternalId AND
            (B.product_id <> D.product_id OR B.product_status <> D.product_status)
    ) THEN
        RAISE EXCEPTION 'Temporary PO Internal product is not match with item';
    END IF;

    --Validasi tanggal dokumen harus <= tanggal sistem
    IF (vTempPoInternalHeader.doc_date > TO_CHAR(CURRENT_DATE, 'YYYYMMDD')::character varying) THEN
        RAISE EXCEPTION 'Doc Date must <= Today';
    END IF;

    --Validasi curr_code adalah valuta resmi
    IF (vTempPoInternalHeader.curr_code <> vValutaResmi) THEN
        RAISE EXCEPTION 'PO Internal must use government tax currency';
    END IF;

    --Validasi ou_buy_id ada
    IF NOT EXISTS (
        SELECT 1
        FROM t_ou A
        WHERE A.ou_id = vTempPoInternalHeader.ou_buy_id
    ) THEN
        RAISE EXCEPTION 'OU Id % not found', vTempPoInternalHeader.ou_buy_id;
    END IF;

    --Validasi ou_buy_id memiliki tenant yang sesuai dengan tenant_id
    IF NOT EXISTS (
        SELECT 1
        FROM t_ou A
        WHERE A.ou_id = vTempPoInternalHeader.ou_buy_id AND
            A.tenant_id = vTempPoInternalHeader.tenant_id
    ) THEN
        RAISE EXCEPTION 'Data PO Internal cannot use data OU Buy from different tenant';
    END IF;

    --Validasi ou_buy_id active
    IF NOT EXISTS (
        SELECT 1
        FROM t_ou A
        WHERE A.ou_id = vTempPoInternalHeader.ou_buy_id AND
            A.tenant_id = vTempPoInternalHeader.tenant_id AND
            A.active = vYes
    ) THEN
        RAISE EXCEPTION 'Cannot use selected data OU Buy, because it is not active';
    END IF;

    --Validasi user-role terautorisasi untuk ou_buy_id
    IF (f_authorize_user_role_policy_ou(pTenantId, pUserId, pRoleId, vTempPoInternalHeader.ou_buy_id) <> 1) THEN
        RAISE EXCEPTION 'Current login user and role is not authorized for selected OU in field OU Buy Business Unit';
    END IF;

    --Validasi ou_buy_id merupakan main BU
    IF EXISTS (
        SELECT 1
        FROM t_ou A
        INNER JOIN t_ou_type B ON A.ou_type_id = B.ou_type_id
        WHERE A.ou_id = vTempPoInternalHeader.ou_buy_id AND
            A.ou_parent_id <> vEmptyId AND
            B.flg_branch = vYes AND
            B.flg_sub_bu = vYes
    ) THEN
        RAISE EXCEPTION 'OU % is not a main business unit (main business unit means
            cannot have parent ou, and does not have type branch or sub business unit)', vTempPoInternalHeader.ou_buy_id;
    END IF;

    --Validasi ou_sell_id ada
    IF NOT EXISTS (
        SELECT 1
        FROM t_ou A
        WHERE A.ou_id = vTempPoInternalHeader.ou_sell_id
    ) THEN
        RAISE EXCEPTION 'OU Id % not found', vTempPoInternalHeader.ou_sell_id;
    END IF;

    --Validasi ou_sell_id memiliki tenant yang sesuai dengan tenant_id
    IF NOT EXISTS (
        SELECT 1
        FROM t_ou A
        WHERE A.ou_id = vTempPoInternalHeader.ou_sell_id AND
            A.tenant_id = vTempPoInternalHeader.tenant_id
    ) THEN
        RAISE EXCEPTION 'Data PO Internal cannot use data OU Sell from different tenant';
    END IF;

    --Validasi ou_sell_id active
    IF NOT EXISTS (
        SELECT 1
        FROM t_ou A
        WHERE A.ou_id = vTempPoInternalHeader.ou_sell_id AND
            A.tenant_id = vTempPoInternalHeader.tenant_id AND
            A.active = vYes
    ) THEN
        RAISE EXCEPTION 'Cannot use selected data OU Sell, because it is not active';
    END IF;

    --Validasi ou_sell_id merupakan main BU
    IF EXISTS (
        SELECT 1
        FROM t_ou A
        INNER JOIN t_ou_type B ON A.ou_type_id = B.ou_type_id
        WHERE A.ou_id = vTempPoInternalHeader.ou_sell_id AND
            A.ou_parent_id <> vEmptyId AND
            B.flg_branch = vYes AND
            B.flg_sub_bu = vYes
    ) THEN
        RAISE EXCEPTION 'OU % is not a main business unit (main business unit means
            cannot have parent ou, and does not have type branch or sub business unit)', vTempPoInternalHeader.ou_sell_id;
    END IF;

    --Validasi warehouse_buy_id ada
    IF NOT EXISTS (
        SELECT 1
        FROM m_warehouse A
        WHERE A.warehouse_id = vTempPoInternalHeader.warehouse_buy_id
    ) THEN
        RAISE EXCEPTION 'Warehouse Id % not found', vTempPoInternalHeader.warehouse_buy_id;
    END IF;

    --Validasi warehouse_buy_id memiliki tenant yang sesuai dengan tenant_id
    IF NOT EXISTS (
        SELECT 1
        FROM m_warehouse A
        WHERE A.warehouse_id = vTempPoInternalHeader.warehouse_buy_id
            AND A.tenant_id = vTempPoInternalHeader.tenant_id
    ) THEN
        RAISE EXCEPTION 'Data PO Internal cannot use data Warehouse Buy from different tenant';
    END IF;

    --Validasi warehouse_buy_id active
    IF NOT EXISTS (
        SELECT 1
        FROM m_warehouse A
        WHERE A.warehouse_id = vTempPoInternalHeader.warehouse_buy_id AND
            A.tenant_id = vTempPoInternalHeader.tenant_id AND
            A.active = vYes
    ) THEN
        RAISE EXCEPTION 'Cannot use selected data Warehouse Buy, because it is not active';
    END IF;

    --Validasi warehouse_buy_id adalah milik ou_buy_id
    IF NOT EXISTS (
        SELECT 1
        FROM m_warehouse_ou A
        INNER JOIN t_ou B ON A.ou_id = B.ou_id
        WHERE A.warehouse_id = vTempPoInternalHeader.warehouse_buy_id AND
            (A.ou_id = vTempPoInternalHeader.ou_buy_id OR
                B.ou_parent_id = vTempPoInternalHeader.ou_buy_id)
    ) THEN
        RAISE EXCEPTION 'Warehouse with id % is not belong to ou with id %',
            vTempPoInternalHeader.warehouse_buy_id, vTempPoInternalHeader.ou_buy_id;
    END IF;

    --Validasi warehouse_buy_id adalah bukan warehouse outlet
    IF EXISTS (
        SELECT 1
        FROM m_warehouse_ou A
        INNER JOIN t_ou B ON A.ou_id = B.ou_id
        INNER JOIN t_ou_type C ON B.ou_type_id = C.ou_type_id
        WHERE A.warehouse_id = vTempPoInternalHeader.warehouse_buy_id AND
            C.flg_bu = vYes AND
            C.flg_accounting = vNo AND
            C.flg_sub_bu = vYes AND
            C.flg_branch = vNo
    ) THEN
        RAISE EXCEPTION 'Warehouse Buy must a warehouse coming from non outlet organization';
    END IF;

    --Validasi user-role terautorisasi untuk ou_buy_id
    IF (f_authorize_user_role_policy_warehouse(pTenantId, pUserId, pRoleId, vTempPoInternalHeader.warehouse_buy_id) <> 1) THEN
        RAISE EXCEPTION 'Current login user and role is not authorized for selected warehouse in field Warehouse Buy';
    END IF;

    --Validasi warehouse_sell_id ada
    IF NOT EXISTS (
        SELECT 1
        FROM m_warehouse A
        WHERE A.warehouse_id = vTempPoInternalHeader.warehouse_sell_id
    ) THEN
        RAISE EXCEPTION 'Warehouse Id % not found', vTempPoInternalHeader.warehouse_sell_id;
    END IF;

    --Validasi warehouse_sell_id memiliki tenant yang sesuai dengan tenant_id
    IF NOT EXISTS (
        SELECT 1
        FROM m_warehouse A
        WHERE A.warehouse_id = vTempPoInternalHeader.warehouse_sell_id AND
            A.tenant_id = vTempPoInternalHeader.tenant_id
    ) THEN
        RAISE EXCEPTION 'Data PO Internal cannot use data Warehouse Sell from different tenant';
    END IF;

    --Validasi warehouse_sell_id active
    IF NOT EXISTS (
        SELECT 1
        FROM m_warehouse A
        WHERE A.warehouse_id = vTempPoInternalHeader.warehouse_sell_id AND
            A.tenant_id = vTempPoInternalHeader.tenant_id AND
            A.active = vYes
    ) THEN
        RAISE EXCEPTION 'Cannot use selected data Warehouse Sell, because it is not active';
    END IF;

    --Validasi warehouse_sell_id adalah milik ou_sell_id
    IF NOT EXISTS (
        SELECT 1
        FROM m_warehouse_ou A
        INNER JOIN t_ou B ON A.ou_id = B.ou_id
        WHERE A.warehouse_id = vTempPoInternalHeader.warehouse_sell_id AND
            (A.ou_id = vTempPoInternalHeader.ou_sell_id OR
                B.ou_parent_id = vTempPoInternalHeader.ou_sell_id)
    ) THEN
        RAISE EXCEPTION 'Warehouse with id % is not belong to ou with id %',
            vTempPoInternalHeader.warehouse_sell_id, vTempPoInternalHeader.ou_sell_id;
    END IF;

    --Validasi warehouse_sell_id adalah bukan warehouse outlet
    IF EXISTS (
        SELECT 1
        FROM m_warehouse_ou A
        INNER JOIN t_ou B ON A.ou_id = B.ou_id
        INNER JOIN t_ou_type C ON B.ou_type_id = C.ou_type_id
        WHERE A.warehouse_id = vTempPoInternalHeader.warehouse_sell_id AND
            C.flg_bu = vYes AND
            C.flg_accounting = vNo AND
            C.flg_sub_bu = vYes AND
            C.flg_branch = vNo
    ) THEN
        RAISE EXCEPTION 'Warehouse Sell must a warehouse coming from non outlet organization';
    END IF;

    --Validasi partner_buy_id ada
    IF NOT EXISTS (
        SELECT 1
        FROM m_partner A
        WHERE A.partner_id = vTempPoInternalHeader.partner_buy_id
    ) THEN
        RAISE EXCEPTION 'Partner Id % not found', vTempPoInternalHeader.partner_buy_id;
    END IF;

    --Validasi partner_buy_id memiliki tenant yang sesuai dengan tenant_id
    IF NOT EXISTS (
        SELECT 1
        FROM m_partner A
        WHERE A.partner_id = vTempPoInternalHeader.partner_buy_id AND
            A.tenant_id = vTempPoInternalHeader.tenant_id
    ) THEN
        RAISE EXCEPTION 'Data PO Internal cannot use data Customer Internal from different tenant';
    END IF;

    --Validasi partner_buy_id active
    IF NOT EXISTS (
        SELECT 1
        FROM m_partner A
        WHERE A.partner_id = vTempPoInternalHeader.partner_buy_id AND
            A.tenant_id = vTempPoInternalHeader.tenant_id AND
            A.active = vYes
    ) THEN
        RAISE EXCEPTION 'Cannot use selected data Customer Internal, because it is not active';
    END IF;

    --Validasi partner_buy_id merupakan customer
    IF (f_authorize_partner_as_specific_group_partner(vTempPoInternalHeader.partner_buy_id, vGroupPartnerCodeCustomer) <> 1) THEN
        RAISE EXCEPTION 'Selected partner with id % does not have
            role setting as customer internal (please check Partner Settings)',
            vTempPoInternalHeader.partner_buy_id;
    END IF;

    --Validasi partner_buy_id merupakan customer internal
    IF NOT EXISTS (
        SELECT 1
        FROM m_partner_type A
        INNER JOIN m_type_partner B ON A.type_partner_id = B.type_partner_id
        WHERE A.partner_id = vTempPoInternalHeader.partner_buy_id AND
            A.tenant_id = vTempPoInternalHeader.tenant_id AND
            B.type_partner_code = vTypePartnerCodeCustomerInternal
    ) THEN
        RAISE EXCEPTION 'Selected partner with id % does not have
            role setting as customer internal (please check Partner Settings)',
            vTempPoInternalHeader.partner_buy_id;
    END IF;

    --Validasi partner_buy_id ter-mapping ke dalam ou sesuai ou_buy_id
    IF NOT EXISTS (
        SELECT 1
        FROM m_partner_ou A
        WHERE A.partner_id = vTempPoInternalHeader.partner_buy_id AND
            A.ou_id = vTempPoInternalHeader.ou_buy_id
    ) THEN
        RAISE EXCEPTION 'Selected customer internal with id %
            does not have authorization for OU with id %',
            vTempPoInternalHeader.partner_buy_id,
            vTempPoInternalHeader.ou_buy_id;
    END IF;

    --Validasi partner_sell_id ada
    IF NOT EXISTS (
        SELECT 1
        FROM m_partner A
        WHERE A.partner_id = vTempPoInternalHeader.partner_sell_id
    ) THEN
        RAISE EXCEPTION 'Partner Id % not found', vTempPoInternalHeader.partner_sell_id;
    END IF;

    --Validasi partner_sell_id memiliki tenant yang sesuai dengan tenant_id
    IF NOT EXISTS (
        SELECT 1
        FROM m_partner A
        WHERE A.partner_id = vTempPoInternalHeader.partner_sell_id AND
            A.tenant_id = vTempPoInternalHeader.tenant_id
    ) THEN
        RAISE EXCEPTION 'Data PO Internal cannot use data Supplier Internal from different tenant';
    END IF;

    --Validasi partner_sell_id active
    IF NOT EXISTS (
        SELECT 1
        FROM m_partner A
        WHERE A.partner_id = vTempPoInternalHeader.partner_sell_id AND
            A.tenant_id = vTempPoInternalHeader.tenant_id AND
            A.active = vYes
    ) THEN
        RAISE EXCEPTION 'Cannot use selected data Supplier Internal, because it is not active';
    END IF;

    --Validasi partner_sell_id merupakan supplier
    IF (f_authorize_partner_as_specific_group_partner(vTempPoInternalHeader.partner_sell_id, vGroupPartnerCodeSupplier) <> 1) THEN
        RAISE EXCEPTION 'Selected partner with id % does not have
            role setting as supplier internal (please check Partner Settings)',
            vTempPoInternalHeader.partner_sell_id;
    END IF;

    --Validasi partner_sell_id merupakan supplier internal
    IF NOT EXISTS (
        SELECT 1
        FROM m_partner_type A
        INNER JOIN m_type_partner B ON A.type_partner_id = B.type_partner_id
        WHERE A.partner_id = vTempPoInternalHeader.partner_sell_id AND
            A.tenant_id = vTempPoInternalHeader.tenant_id AND
            B.type_partner_code = vTypePartnerCodeSupplierInternal
    ) THEN
        RAISE EXCEPTION 'Selected partner with id % does not have
            role setting as supplier internal (please check Partner Settings)',
            vTempPoInternalHeader.partner_sell_id;
    END IF;

    --Validasi partner_sell_id ter-mapping ke dalam ou sesuai ou_sell_id
    IF NOT EXISTS (
        SELECT 1
        FROM m_partner_ou A
        WHERE A.partner_id = vTempPoInternalHeader.partner_sell_id AND
            A.ou_id = vTempPoInternalHeader.ou_sell_id
    ) THEN
        RAISE EXCEPTION 'Selected supplier internal with id %
            does not have authorization for OU with id %',
            vTempPoInternalHeader.partner_sell_id,
            vTempPoInternalHeader.ou_sell_id;
    END IF;

    --Validasi flg_settle harus sesuai dengan combo YESNO
    IF NOT EXISTS (
        SELECT 1
        FROM m_partner A
        WHERE A.partner_id = vTempPoInternalHeader.partner_sell_id
    ) THEN
        RAISE EXCEPTION 'Partner Id % not found', vTempPoInternalHeader.partner_sell_id;
    END IF;

    --Validasi flg_settle harus sesuai dengan combo YESNO
    IF NOT EXISTS (
        SELECT 1
        FROM t_combo_value A
        WHERE A.combo_id = vComboIdYesNo AND
            A.code = vTempPoInternalHeader.flg_settle
    ) THEN
        RAISE EXCEPTION 'Value Settle is not valid ,should not have values : %',
            vTempPoInternalHeader.flg_settle;
    END IF;

    --Jika flg_settle = Y, validasi cashbank
    IF (vTempPoInternalHeader.flg_settle = vYes) THEN

        --Validasi cashbank_buy_id ada
        IF NOT EXISTS (
            SELECT 1
            FROM m_cashbank A
            WHERE A.cashbank_id = vTempPoInternalHeader.cashbank_buy_id
        ) THEN
            RAISE EXCEPTION 'Cashbank Id % not found', vTempPoInternalHeader.cashbank_buy_id;
        END IF;

        --Validasi flg_casbank dari cashbank_buy_id adalah cash
        IF NOT EXISTS (
            SELECT 1
            FROM m_cashbank A
            WHERE A.cashbank_id = vTempPoInternalHeader.cashbank_buy_id AND
                A.flg_cash_bank = vFlagCashbankCash
        ) THEN
            RAISE EXCEPTION 'PO Internal must use cash for cashbank sell / buy,
                selected cashbank is not cash = %', vTempPoInternalHeader.cashbank_buy_id;
        END IF;

        --Validasi curr_code dari cashbank_buy_id sama dengan curr_code header PO Internal
        IF NOT EXISTS (
            SELECT 1
            FROM m_cashbank A
            WHERE A.cashbank_id = vTempPoInternalHeader.cashbank_buy_id AND
                A.curr_code = vTempPoInternalHeader.curr_code
        ) THEN
            RAISE EXCEPTION 'PO Internal must use cash currency with same currency for cashbank sell / buy,
                selected cashbank currency is not match with transaction currency = %', vTempPoInternalHeader.cashbank_buy_id;
        END IF;

        --Validasi cashbank_buy_id memiliki tenant yang sesuai dengan tenant_id
        IF NOT EXISTS (
            SELECT 1
            FROM m_cashbank A
            WHERE A.cashbank_id = vTempPoInternalHeader.cashbank_buy_id AND
                A.tenant_id = vTempPoInternalHeader.tenant_id
        ) THEN
            RAISE EXCEPTION 'Data PO Internal cannot use data Cashbank Buy from different tenant';
        END IF;

        --Validasi cashbank_buy_id active
        IF NOT EXISTS (
            SELECT 1
            FROM m_cashbank A
            WHERE A.cashbank_id = vTempPoInternalHeader.cashbank_buy_id AND
                A.tenant_id = vTempPoInternalHeader.tenant_id AND
                A.active = vYes
        ) THEN
            RAISE EXCEPTION 'Cannot use selected data Cashbank Buy, because it is not active';
        END IF;

        --Validasi user-role terautorisasi untuk cashbank_buy_id
        IF (f_authorize_user_role_policy_cashbank(pTenantId, pUserId, pRoleId, vTempPoInternalHeader.cashbank_buy_id) <> 1) THEN
            RAISE EXCEPTION 'Current login user and role is not authorized for selected cash bank in field Cashbank Buy';
        END IF;

        --Validasi cashbank_sell_id ada
        IF NOT EXISTS (
            SELECT 1
            FROM m_cashbank A
            WHERE A.cashbank_id = vTempPoInternalHeader.cashbank_sell_id
        ) THEN
            RAISE EXCEPTION 'Cashbank Id % not found', vTempPoInternalHeader.cashbank_sell_id;
        END IF;

        --Validasi flg_casbank dari cashbank_sell_id adalah cash
        IF NOT EXISTS (
            SELECT 1
            FROM m_cashbank A
            WHERE A.cashbank_id = vTempPoInternalHeader.cashbank_sell_id AND
                A.flg_cash_bank = vFlagCashbankCash
        ) THEN
            RAISE EXCEPTION 'PO Internal must use cash for cashbank sell / buy,
                selected cashbank is not cash = %', vTempPoInternalHeader.cashbank_sell_id;
        END IF;

        --Validasi curr_code dari cashbank_sell_id sama dengan curr_code header PO Internal
        IF NOT EXISTS (
            SELECT 1
            FROM m_cashbank A
            WHERE A.cashbank_id = vTempPoInternalHeader.cashbank_sell_id AND
                A.curr_code = vTempPoInternalHeader.curr_code
        ) THEN
            RAISE EXCEPTION 'PO Internal must use cash currency with same currency for cashbank sell / buy,
                selected cashbank currency is not match with transaction currency = %', vTempPoInternalHeader.cashbank_sell_id;
        END IF;

        --Validasi cashbank_sell_id memiliki tenant yang sesuai dengan tenant_id
        IF NOT EXISTS (
            SELECT 1
            FROM m_cashbank A
            WHERE A.cashbank_id = vTempPoInternalHeader.cashbank_sell_id AND
                A.tenant_id = vTempPoInternalHeader.tenant_id
        ) THEN
            RAISE EXCEPTION 'Data PO Internal cannot use data Cashbank Sell from different tenant';
        END IF;

        --Validasi cashbank_sell_id active
        IF NOT EXISTS (
            SELECT 1
            FROM m_cashbank A
            WHERE A.cashbank_id = vTempPoInternalHeader.cashbank_sell_id AND
                A.tenant_id = vTempPoInternalHeader.tenant_id AND
                A.active = vYes
        ) THEN
            RAISE EXCEPTION 'Cannot use selected data Cashbank Sell, because it is not active';
        END IF;

    END IF;

    --Validasi curr_code harus sesuai dengan combo CURRENCY
    IF NOT EXISTS (
        SELECT 1
        FROM t_combo_value A
        WHERE A.combo_id = vComboIdCurrency AND
            A.code = vTempPoInternalHeader.curr_code
    ) THEN
        RAISE EXCEPTION 'Value Currency is not valid ,should not have values : %',
            vTempPoInternalHeader.curr_code;
    END IF;

    --Validasi flg_accept_tax harus sesuai dengan combo YESNO
    IF NOT EXISTS (
        SELECT 1
        FROM t_combo_value A
        WHERE A.combo_id = vComboIdYesNo AND
            A.code = vTempPoInternalHeader.flg_accept_tax
    ) THEN
        RAISE EXCEPTION 'Value Acknowledge Tax is not valid ,should not have values : %',
            vTempPoInternalHeader.flg_accept_tax;
    END IF;

    --Validasi unique key header PO Internal belum ada
    IF EXISTS (
        SELECT 1
        FROM pu_po_internal A
        WHERE A.tenant_id = vTempPoInternalHeader.tenant_id AND
            A.doc_type_id = vDocTypeIdInternalPurchasing AND
            A.doc_date = vTempPoInternalHeader.doc_date AND
            A.doc_no = vTempPoInternalHeader.doc_no AND
            A.ou_buy_id = vTempPoInternalHeader.ou_buy_id
    ) THEN
        RAISE EXCEPTION 'PO Internal with doc date %, doc no %, and ou buy id % is already exists',
            vTempPoInternalHeader.doc_date,
            vTempPoInternalHeader.doc_no,
            vTempPoInternalHeader.ou_buy_id;
    END IF;

    --Validasi qty_int item PO Internal > 0
    IF EXISTS (
        SELECT 1
        FROM tt_po_internal A
        INNER JOIN tt_po_internal_item B
            ON A.session_id = B.session_id AND
                A.tenant_id = B.tenant_id AND
                A.tt_po_internal_id = B.tt_po_internal_id
        WHERE A.session_id = pSessionId AND
            A.tenant_id = pTenantId AND
            A.tt_po_internal_id = pTempPoInternalId AND
            B.qty_int <= 0
    ) THEN
        RAISE EXCEPTION 'Item Qty must > 0';
    END IF;

    --Validasi product_id item PO Internal ada
    IF EXISTS (
        SELECT 1
        FROM tt_po_internal A
        INNER JOIN tt_po_internal_item B
            ON A.session_id = B.session_id AND
                A.tenant_id = B.tenant_id AND
                A.tt_po_internal_id = B.tt_po_internal_id
        WHERE A.session_id = pSessionId AND
            A.tenant_id = pTenantId AND
            A.tt_po_internal_id = pTempPoInternalId AND
            NOT EXISTS (
                SELECT 1
                FROM m_product C
                WHERE B.product_id = C.product_id
            )
    ) THEN
        RAISE EXCEPTION 'Product Id of PO Internal Item not found';
    END IF;

    --Validasi product_id item PO Internal memiliki tenant yang sesuai
    IF EXISTS (
        SELECT 1
        FROM tt_po_internal A
        INNER JOIN tt_po_internal_item B
            ON A.session_id = B.session_id AND
                A.tenant_id = B.tenant_id AND
                A.tt_po_internal_id = B.tt_po_internal_id
        WHERE A.session_id = pSessionId AND
            A.tenant_id = pTenantId AND
            A.tt_po_internal_id = pTempPoInternalId AND
            NOT EXISTS (
                SELECT 1
                FROM m_product C
                WHERE B.product_id = C.product_id AND
                    B.tenant_id = C.tenant_id
            )
    ) THEN
        RAISE EXCEPTION 'Data PO Internal Item cannot use data Product from different tenant';
    END IF;

    --Validasi user-role terautorisasi untuk product_id item PO Internal
    IF EXISTS (
        SELECT 1
        FROM tt_po_internal A
        INNER JOIN tt_po_internal_item B
            ON A.session_id = B.session_id AND
                A.tenant_id = B.tenant_id AND
                A.tt_po_internal_id = B.tt_po_internal_id
        WHERE A.session_id = pSessionId AND
            A.tenant_id = pTenantId AND
            A.tt_po_internal_id = pTempPoInternalId AND
            f_authorize_user_role_policy_product(pTenantId, pUserId, pRoleId, B.product_id) <> 1
    ) THEN
        RAISE EXCEPTION 'Current login user and role is not authorized for PO Internal Item product in field Product';
    END IF;

    --Validasi product_id item PO Internal terautorisasi dalam warehouse buy
    IF EXISTS (
        SELECT 1
        FROM tt_po_internal A
        INNER JOIN tt_po_internal_item B
            ON A.session_id = B.session_id AND
                A.tenant_id = B.tenant_id AND
                A.tt_po_internal_id = B.tt_po_internal_id
        INNER JOIN m_product C
            ON B.product_id = C.product_id AND
                B.tenant_id = C.tenant_id
        WHERE A.session_id = pSessionId AND
            A.tenant_id = pTenantId AND
            A.tt_po_internal_id = pTempPoInternalId AND
            NOT EXISTS(
                SELECT 1
                FROM m_item_warehouse D
                WHERE B.tenant_id = D.tenant_id AND
                    A.warehouse_buy_id = D.warehouse_id AND
                    C.sub_ctgr_product_id = D.sub_ctgr_product_id
            )
    ) THEN
        RAISE EXCEPTION 'Product id of PO Internal Item is not authorized in warehouse id';
    END IF;

    --Validasi tax_id item PO Internal ada
    IF EXISTS (
        SELECT 1
        FROM tt_po_internal A
        INNER JOIN tt_po_internal_item B
            ON A.session_id = B.session_id AND
                A.tenant_id = B.tenant_id AND
                A.tt_po_internal_id = B.tt_po_internal_id
        WHERE A.session_id = pSessionId AND
            A.tenant_id = pTenantId AND
            A.tt_po_internal_id = pTempPoInternalId AND
            B.tax_id <> vEmptyId AND
            NOT EXISTS (
                SELECT 1
                FROM m_tax C
                WHERE B.tax_id = C.tax_id
            )
    ) THEN
        RAISE EXCEPTION 'Tax Id of PO Internal Item not found';
    END IF;

    --Validasi tax_id item PO Internal memiliki tenant yang sesuai
    IF EXISTS (
        SELECT 1
        FROM tt_po_internal A
        INNER JOIN tt_po_internal_item B
            ON A.session_id = B.session_id AND
                A.tenant_id = B.tenant_id AND
                A.tt_po_internal_id = B.tt_po_internal_id
        WHERE A.session_id = pSessionId AND
            A.tenant_id = pTenantId AND
            A.tt_po_internal_id = pTempPoInternalId AND
            B.tax_id <> vEmptyId AND
            NOT EXISTS (
                SELECT 1
                FROM m_tax C
                WHERE B.tax_id = C.tax_id AND
                    B.tenant_id = C.tenant_id
            )
    ) THEN
        RAISE EXCEPTION 'Data PO Internal Item cannot use data Tax from different tenant';
    END IF;

    --Validasi tax_id item PO Internal active
    IF EXISTS (
        SELECT 1
        FROM tt_po_internal A
        INNER JOIN tt_po_internal_item B
            ON A.session_id = B.session_id AND
                A.tenant_id = B.tenant_id AND
                A.tt_po_internal_id = B.tt_po_internal_id
        WHERE A.session_id = pSessionId AND
            A.tenant_id = pTenantId AND
            A.tt_po_internal_id = pTempPoInternalId AND
            B.tax_id <> vEmptyId AND
            NOT EXISTS (
                SELECT 1
                FROM m_tax C
                WHERE B.tax_id = C.tax_id AND
                    C.active = vYes
            )
    ) THEN
        RAISE EXCEPTION 'Cannot use selected data Tax, because it is not active';
    END IF;

    --Validasi flg_tax_amount harus sesuai dengan combo YESNO
    IF EXISTS (
        SELECT 1
        FROM tt_po_internal A
        INNER JOIN tt_po_internal_item B
            ON A.session_id = B.session_id AND
                A.tenant_id = B.tenant_id AND
                A.tt_po_internal_id = B.tt_po_internal_id
        WHERE A.session_id = pSessionId AND
            A.tenant_id = pTenantId AND
            A.tt_po_internal_id = pTempPoInternalId AND
            NOT EXISTS (
                SELECT 1
                FROM t_combo_value C
                WHERE C.combo_id = vComboIdYesNo AND
                    C.code = B.flg_tax_amount
            )
    ) THEN
        RAISE EXCEPTION 'Value Flag Tax Amount is not valid';
    END IF;

    --Validasi tax_id harus diisi jika flg_tax_amount = 'Y'
    IF EXISTS (
        SELECT 1
        FROM tt_po_internal A
        INNER JOIN tt_po_internal_item B
            ON A.session_id = B.session_id AND
                A.tenant_id = B.tenant_id AND
                A.tt_po_internal_id = B.tt_po_internal_id
        WHERE A.session_id = pSessionId AND
            A.tenant_id = pTenantId AND
            A.tt_po_internal_id = pTempPoInternalId AND
            B.flg_tax_amount = vYes AND
            B.tax_id = vEmptyId
    ) THEN
        RAISE EXCEPTION 'Gross Price is including tax, so you must choose tax value';
    END IF;

    --Rounding gross_price_po item PO Internal
    UPDATE tt_po_internal_item Z
    SET gross_price_po =
        CASE WHEN (vRoundingMode = vRoundingModeDown) THEN
            TRUNC(Z.gross_price_po, vDecimalForRounding)
        ELSE
            ROUND(Z.gross_price_po, vDecimalForRounding)
        END
    FROM tt_po_internal A
    WHERE A.session_id = pSessionId AND
            A.tenant_id = pTenantId AND
            A.tt_po_internal_id = pTempPoInternalId AND
            A.session_id = Z.session_id AND
            A.tenant_id = Z.tenant_id AND
            A.tt_po_internal_id = Z.tt_po_internal_id;

    --Hitung tax untuk item PO Internal dengan flg_tax_amount = 'Y'
    UPDATE tt_po_internal_item Z
    SET tax_percentage = B.percentage,
        tax_price =
            CASE WHEN (vRoundingMode = vRoundingModeDown) THEN
                TRUNC(Z.gross_price_po * B.percentage / (100 + B.percentage), vDecimalForRounding)
            ELSE
                ROUND(Z.gross_price_po * B.percentage / (100 + B.percentage), vDecimalForRounding)
            END,
        nett_price_po =
            CASE WHEN (vRoundingMode = vRoundingModeDown) THEN
                Z.gross_price_po - TRUNC(Z.gross_price_po * B.percentage / (100 + B.percentage), vDecimalForRounding)
            ELSE
                Z.gross_price_po - ROUND(Z.gross_price_po * B.percentage / (100 + B.percentage), vDecimalForRounding)
            END
    FROM tt_po_internal A, m_tax B
    WHERE A.session_id = pSessionId AND
            A.tenant_id = pTenantId AND
            A.tt_po_internal_id = pTempPoInternalId AND
            A.session_id = Z.session_id AND
            A.tenant_id = Z.tenant_id AND
            A.tt_po_internal_id = Z.tt_po_internal_id AND
            Z.flg_tax_amount = vYes AND
            Z.tax_id <> vEmptyId AND
            Z.tax_id = B.tax_id;

    --Hitung nett price untuk item PO Internal dengan flg_tax_amount = 'N'
    UPDATE tt_po_internal_item Z
    SET nett_price_po = Z.gross_price_po
    FROM tt_po_internal A
    WHERE A.session_id = pSessionId AND
            A.tenant_id = pTenantId AND
            A.tt_po_internal_id = pTempPoInternalId AND
            A.session_id = Z.session_id AND
            A.tenant_id = Z.tenant_id AND
            A.tt_po_internal_id = Z.tt_po_internal_id AND
            Z.flg_tax_amount = vNo;

    --Hitung tax untuk item PO Internal dengan flg_tax_amount = 'N' dan tax_id <> -99
    UPDATE tt_po_internal_item Z
    SET tax_percentage = B.percentage,
        tax_price =
            CASE WHEN (vRoundingMode = vRoundingModeDown) THEN
                TRUNC(Z.gross_price_po * B.percentage / 100, vDecimalForRounding)
            ELSE
                ROUND(Z.gross_price_po * B.percentage / 100, vDecimalForRounding)
            END
    FROM tt_po_internal A, m_tax B
    WHERE A.session_id = pSessionId AND
            A.tenant_id = pTenantId AND
            A.tt_po_internal_id = pTempPoInternalId AND
            A.session_id = Z.session_id AND
            A.tenant_id = Z.tenant_id AND
            A.tt_po_internal_id = Z.tt_po_internal_id AND
            Z.flg_tax_amount = vNo AND
            Z.tax_id <> vEmptyId AND
            Z.tax_id = B.tax_id;

    --Hitung amount untuk item PO Internal
    UPDATE tt_po_internal_item Z
    SET gross_item_amount = Z.gross_price_po * Z.qty_int,
        nett_item_amount = Z.nett_price_po * Z.qty_int,
        tax_amount = Z.tax_price * Z.qty_int
    FROM tt_po_internal A
    WHERE A.session_id = pSessionId AND
            A.tenant_id = pTenantId AND
            A.tt_po_internal_id = pTempPoInternalId AND
            A.session_id = Z.session_id AND
            A.tenant_id = Z.tenant_id AND
            A.tt_po_internal_id = Z.tt_po_internal_id;

    --Jika flg_settle = Y, validasi cashbank balance cukup
    IF (vTempPoInternalHeader.flg_settle = vYes) THEN

        --Simpan grand total
        SELECT SUM(B.nett_item_amount) + SUM(B.tax_amount)
        FROM tt_po_internal A
        INNER JOIN tt_po_internal_item B
            ON A.session_id = B.session_id AND
                A.tenant_id = B.tenant_id AND
                A.tt_po_internal_id = B.tt_po_internal_id
        WHERE A.session_id = pSessionId AND
            A.tenant_id = pTenantId AND
            A.tt_po_internal_id = pTempPoInternalId
        INTO vGrandTotal;

        --Validasi cashbank balance cukup
        PERFORM f_val_sufficient_cashbank_balance(pSessionId, vTempPoInternalHeader.cashbank_buy_id, vTempPoInternalHeader.doc_date, vGrandTotal);

        UPDATE cb_insufficient_cashbank_balance
        SET tenant_id = pTenantId,
            ou_id = vTempPoInternalHeader.ou_buy_id,
            doc_type_id = vDocTypeIdInternalPurchasing,
            doc_no = vTempPoInternalHeader.doc_no,
            create_datetime = pDatetime,
            create_user_id = pUserId
        WHERE session_id = pSessionId;

        IF (SELECT COUNT(1) FROM cb_insufficient_cashbank_balance WHERE session_id = pSessionId) > 0 THEN
            SELECT string_agg(cashbank_name||' with credit = '||credit::character varying, ',') INTO vCbInsufficientList
            FROM cb_insufficient_cashbank_balance WHERE session_id = pSessionId;

            RAISE EXCEPTION 'There is insufficient cashbank balance for cancel approved : %', vCbInsufficientList;
        END IF;

    END IF;

    --Validasi qty_int item PO Internal > 0
    IF EXISTS (
        SELECT 1
        FROM tt_po_internal A
        INNER JOIN tt_po_internal_item B
            ON A.session_id = B.session_id AND
                A.tenant_id = B.tenant_id AND
                A.tt_po_internal_id = B.tt_po_internal_id
        INNER JOIN tt_po_internal_product C
            ON B.session_id = C.session_id AND
                B.tenant_id = C.tenant_id AND
                B.tt_po_internal_item_id = C.tt_po_internal_item_id
        WHERE A.session_id = pSessionId AND
            A.tenant_id = pTenantId AND
            A.tt_po_internal_id = pTempPoInternalId AND
            C.qty_int <= 0
    ) THEN
        RAISE EXCEPTION 'PO Internal Product Qty must > 0';
    END IF;

    --Validasi qty_int item PO Internal > 0
    IF EXISTS (
        SELECT 1
        FROM tt_po_internal A
        INNER JOIN tt_po_internal_item B
            ON A.session_id = B.session_id AND
                A.tenant_id = B.tenant_id AND
                A.tt_po_internal_id = B.tt_po_internal_id
        INNER JOIN tt_po_internal_product C
            ON B.session_id = C.session_id AND
                B.tenant_id = C.tenant_id AND
                B.tt_po_internal_item_id = C.tt_po_internal_item_id
        WHERE A.session_id = pSessionId AND
            A.tenant_id = pTenantId AND
            A.tt_po_internal_id = pTempPoInternalId AND
            C.qty_int <= 0
    ) THEN
        RAISE EXCEPTION 'PO Internal Product Qty must > 0';
    END IF;

    --Validasi qty_int item PO Internal > 0
    IF EXISTS (
        SELECT 1
        FROM tt_po_internal A
        INNER JOIN tt_po_internal_item B
            ON A.session_id = B.session_id AND
                A.tenant_id = B.tenant_id AND
                A.tt_po_internal_id = B.tt_po_internal_id
        INNER JOIN tt_po_internal_product C
            ON B.session_id = C.session_id AND
                B.tenant_id = C.tenant_id AND
                B.tt_po_internal_item_id = C.tt_po_internal_item_id
        WHERE A.session_id = pSessionId AND
            A.tenant_id = pTenantId AND
            A.tt_po_internal_id = pTempPoInternalId
        GROUP BY B.tt_po_internal_item_id
        HAVING SUM(C.qty_int) <> B.qty_int
    ) THEN
        RAISE EXCEPTION 'Total quantity in detail products not match with total quantity in the item with line no';
    END IF;

    --Validasi product_balance_stock_id product PO Internal ada
    IF EXISTS (
        SELECT 1
        FROM tt_po_internal A
        INNER JOIN tt_po_internal_item B
            ON A.session_id = B.session_id AND
                A.tenant_id = B.tenant_id AND
                A.tt_po_internal_id = B.tt_po_internal_id
        INNER JOIN tt_po_internal_product C
            ON B.session_id = C.session_id AND
                B.tenant_id = C.tenant_id AND
                B.tt_po_internal_item_id = C.tt_po_internal_item_id
        WHERE A.session_id = pSessionId AND
            A.tenant_id = pTenantId AND
            A.tt_po_internal_id = pTempPoInternalId AND
            NOT EXISTS (
                SELECT 1
                FROM in_product_balance_stock D
                WHERE C.product_balance_stock_id = D.product_balance_stock_id
            )
    ) THEN
        RAISE EXCEPTION 'Product Id of PO Internal Item not found';
    END IF;

    --Validasi qty product_balance_stock masih cukup
    IF EXISTS (
        SELECT 1
        FROM tt_po_internal A
        INNER JOIN tt_po_internal_item B
            ON A.session_id = B.session_id AND
                A.tenant_id = B.tenant_id AND
                A.tt_po_internal_id = B.tt_po_internal_id
        INNER JOIN tt_po_internal_product C
            ON B.session_id = C.session_id AND
                B.tenant_id = C.tenant_id AND
                B.tt_po_internal_item_id = C.tt_po_internal_item_id
        WHERE A.session_id = pSessionId AND
            A.tenant_id = pTenantId AND
            A.tt_po_internal_id = pTempPoInternalId AND
            EXISTS (
                SELECT 1
                FROM in_product_balance_stock D
                WHERE C.product_balance_stock_id = D.product_balance_stock_id AND
                    D.qty < C.qty_int
            )
    ) THEN
        RAISE EXCEPTION 'PO Internal Product Qty must <= Stock Qty';
    END IF;

    --Validasi product_status product_balance_stock ada
    IF EXISTS (
        SELECT 1
        FROM tt_po_internal A
        INNER JOIN tt_po_internal_item B
            ON A.session_id = B.session_id AND
                A.tenant_id = B.tenant_id AND
                A.tt_po_internal_id = B.tt_po_internal_id
        INNER JOIN tt_po_internal_product C
            ON B.session_id = C.session_id AND
                B.tenant_id = C.tenant_id AND
                B.tt_po_internal_item_id = C.tt_po_internal_item_id
        INNER JOIN in_product_balance_stock D
            ON C.product_balance_stock_id = D.product_balance_stock_id AND
                C.tenant_id = D.tenant_id
        WHERE A.session_id = pSessionId AND
            A.tenant_id = pTenantId AND
            A.tt_po_internal_id = pTempPoInternalId AND
            NOT EXISTS (
                SELECT 1
                FROM m_product_status E
                WHERE D.product_status = E.product_status_code AND
                    D.tenant_id = E.tenant_id
            )
    ) THEN
        RAISE EXCEPTION 'Product Status of PO Internal Product not found';
    END IF;

    --Validasi product_status product_balance_stock sama dengan product status pada item
    IF EXISTS (
        SELECT 1
        FROM tt_po_internal A
        INNER JOIN tt_po_internal_item B
            ON A.session_id = B.session_id AND
                A.tenant_id = B.tenant_id AND
                A.tt_po_internal_id = B.tt_po_internal_id
        INNER JOIN tt_po_internal_product C
            ON B.session_id = C.session_id AND
                B.tenant_id = C.tenant_id AND
                B.tt_po_internal_item_id = C.tt_po_internal_item_id
        INNER JOIN in_product_balance_stock D
            ON C.product_balance_stock_id = D.product_balance_stock_id
        WHERE A.session_id = pSessionId AND
            A.tenant_id = pTenantId AND
            A.tt_po_internal_id = pTempPoInternalId AND
            B.product_status <> D.product_status
    ) THEN
        RAISE EXCEPTION 'Product Status of PO Internal Product not match with item';
    END IF;

    --Validasi product_status product_balance_stock masih cukup
    IF EXISTS (
        SELECT 1
        FROM tt_po_internal A
        INNER JOIN tt_po_internal_item B
            ON A.session_id = B.session_id AND
                A.tenant_id = B.tenant_id AND
                A.tt_po_internal_id = B.tt_po_internal_id
        INNER JOIN tt_po_internal_product C
            ON B.session_id = C.session_id AND
                B.tenant_id = C.tenant_id AND
                B.tt_po_internal_item_id = C.tt_po_internal_item_id
        INNER JOIN in_product_balance_stock D
            ON C.product_balance_stock_id = D.product_balance_stock_id AND
                C.tenant_id = D.tenant_id
        WHERE A.session_id = pSessionId AND
            A.tenant_id = pTenantId AND
            A.tt_po_internal_id = pTempPoInternalId AND
            NOT EXISTS (
                SELECT 1
                FROM m_product_status E
                WHERE D.product_status = E.product_status_code AND
                    D.tenant_id = E.tenant_id AND
                    E.flg_sell = vYes
            )
    ) THEN
        RAISE EXCEPTION 'Product Status of PO Internal Product not found';
    END IF;


    SELECT NEXTVAL('pu_po_internal_seq') INTO vPoInternalId;

    --Insert data header PO Internal sesuai header temporary
    INSERT INTO pu_po_internal(
            po_internal_id, tenant_id, doc_type_id, doc_no, doc_date, ou_buy_id,
            partner_sell_id, purchaser_id, warehouse_buy_id, ou_sell_id,
            partner_buy_id, salesman_id, warehouse_sell_id, ext_doc_no, ext_doc_date,
            ref_doc_type_id, ref_id, due_date, remark, flg_delivery, status_doc,
            workflow_status, version, create_datetime, create_user_id, update_datetime,
            update_user_id, flg_accept_tax, curr_code, cashbank_buy_id, cashbank_sell_id,
            flg_settle)
    SELECT vPoInternalId, A.tenant_id, vDocTypeIdInternalPurchasing, A.doc_no, A.doc_date, A.ou_buy_id,
            A.partner_sell_id, vEmptyId, A.warehouse_buy_id, A.ou_sell_id,
            A.partner_buy_id, vEmptyId, A.warehouse_sell_id, vEmptyValue, vEmptyValue,
            vEmptyId, vEmptyId, vEmptyValue, A.remark, vNo, vStatusRelease,
            vWorkflowApproved, 0, pDatetime, pUserId, pDatetime,
            pUserId, A.flg_accept_tax, A.curr_code, A.cashbank_buy_id, A.cashbank_sell_id,
            A.flg_settle
    FROM tt_po_internal A
    WHERE A.session_id = pSessionId AND
        A.tenant_id = pTenantId AND
        A.tt_po_internal_id = pTempPoInternalId;

    --Insert data item PO Internal sesuai item temporary
    INSERT INTO pu_po_internal_item(
            tenant_id, po_internal_id, line_no, ref_doc_type_id,
            ref_id, product_id, curr_code, gross_price_po, flg_tax_amount,
            tax_id, tax_percentage, discount_percentage, discount_amount,
            nett_price_po, qty_int, base_uom_id, gross_item_amount, nett_item_amount,
            tax_amount, remark, version, create_datetime, create_user_id,
            update_datetime, update_user_id, tax_price, product_status)
    SELECT B.tenant_id, vPoInternalId, B.line_no, vEmptyId,
            vEmptyId, B.product_id, B.curr_code, B.gross_price_po, B.flg_tax_amount,
            B.tax_id, B.tax_percentage, B.discount_percentage, B.discount_amount,
            B.nett_price_po, B.qty_int, B.base_uom_id, B.gross_item_amount, B.nett_item_amount,
            B.tax_amount, vEmptyValue, 0, pDatetime, pUserId,
            pDatetime, pUserId, B.tax_price, B.product_status
    FROM tt_po_internal A
    INNER JOIN tt_po_internal_item B
        ON A.session_id = B.session_id AND
            A.tenant_id = B.tenant_id AND
            A.tt_po_internal_id = B.tt_po_internal_id
    WHERE A.session_id = pSessionId AND
        A.tenant_id = pTenantId AND
        A.tt_po_internal_id = pTempPoInternalId;

    --Insert data tax PO Internal
    INSERT INTO pu_po_internal_tax(
            tenant_id, po_internal_id, tax_id, flg_amount,
            tax_percentage, base_amount,
            tax_amount,
            tax_no, tax_date, tax_curr_code,
            gov_tax_amount, remark, version, create_datetime, create_user_id,
            update_datetime, update_user_id)
    SELECT B.tenant_id, vPoInternalId, B.tax_id, C.flg_amount,
            C.percentage, SUM(B.nett_item_amount),
            CASE WHEN (vRoundingModeTax = vRoundingModeDown) THEN
                TRUNC(SUM(B.nett_item_amount) * C.percentage / 100, vRoundingTax)
            ELSE
                ROUND(SUM(B.nett_item_amount) * C.percentage / 100, vRoundingTax)
            END,
            vEmptyValue, vEmptyValue, C.tax_curr_code,
            0, vEmptyValue, 0, pDatetime, pUserId,
            pDatetime, pUserId
    FROM tt_po_internal A
    INNER JOIN tt_po_internal_item B
        ON A.session_id = B.session_id AND
            A.tenant_id = B.tenant_id AND
            A.tt_po_internal_id = B.tt_po_internal_id
    INNER JOIN m_tax C
        ON B.tax_id = C.tax_id AND
            B.tenant_id = C.tenant_id
    WHERE A.session_id = pSessionId AND
        A.tenant_id = pTenantId AND
        A.tt_po_internal_id = pTempPoInternalId
    GROUP BY B.tenant_id, B.tax_id, C.flg_amount, C.percentage, C.tax_curr_code;

    --Insert data product PO Internal
    INSERT INTO pu_po_internal_product(
            tenant_id, po_internal_item_id, line_no,
            product_id, product_balance_id, product_status, qty_int, base_uom_id,
            remark, version, create_datetime, create_user_id, update_datetime,
            update_user_id)
    SELECT C.tenant_id, po_internal_item_id, C.line_no,
            D.product_id, D.product_balance_id, D.product_status, C.qty_int, D.base_uom_id,
            vEmptyValue, 0, pDatetime, pUserId, pDatetime,
            pUserId
    FROM tt_po_internal A
    INNER JOIN tt_po_internal_item B
        ON A.session_id = B.session_id AND
            A.tenant_id = B.tenant_id AND
            A.tt_po_internal_id = B.tt_po_internal_id
    INNER JOIN tt_po_internal_product C
        ON B.session_id = C.session_id AND
            B.tenant_id = C.tenant_id AND
            B.tt_po_internal_item_id = C.tt_po_internal_item_id
    INNER JOIN in_product_balance_stock D
        ON C.product_balance_stock_id = D.product_balance_stock_id AND
            C.tenant_id = D.tenant_id
    INNER JOIN pu_po_internal_item E
        ON B.tenant_id = E.tenant_id AND
            E.po_internal_id = vPoInternalId AND
            B.product_id = E.product_id AND
            B.line_no = E.line_no
    WHERE A.session_id = pSessionId AND
        A.tenant_id = pTenantId AND
        A.tt_po_internal_id = pTempPoInternalId;

    UPDATE in_product_balance_stock Z
    SET qty = Z.qty - C.qty_int,
        update_datetime = pDatetime,
        update_user_id = pUserId
    FROM tt_po_internal A
    INNER JOIN tt_po_internal_item B
        ON A.session_id = B.session_id AND
            A.tenant_id = B.tenant_id AND
            A.tt_po_internal_id = B.tt_po_internal_id
    INNER JOIN tt_po_internal_product C
        ON B.session_id = C.session_id AND
            B.tenant_id = C.tenant_id AND
            B.tt_po_internal_item_id = C.tt_po_internal_item_id
    WHERE A.session_id = pSessionId AND
        A.tenant_id = pTenantId AND
        A.tt_po_internal_id = pTempPoInternalId AND
        Z.product_balance_stock_id = C.product_balance_stock_id;

    --Jika ref_doc_no tidak kosong, insert ke pu_po_internal_doc_no
    IF (vTempPoInternalHeader.ref_doc_no <> vEmptyValue) THEN

        INSERT INTO pu_po_internal_doc_no(
                tenant_id, po_internal_id, flg_ou_po,
                ref_doc_type_id, ref_doc_no, ref_id, version, create_datetime,
                create_user_id, update_datetime, update_user_id, autonum_id)
        SELECT A.tenant_id, vPoInternalId, vFlagOuPoSell,
                vDocTypeIdSalesInvoice, A.ref_doc_no, vEmptyId, 0, pDatetime,
                pUserId, pDatetime, pUserId, vEmptyId
        FROM tt_po_internal A
        WHERE A.session_id = pSessionId AND
            A.tenant_id = pTenantId AND
            A.tt_po_internal_id = pTempPoInternalId;

    END IF;

    -- Generate data awe_currdoc_status
    INSERT INTO awe_currdoc_status(
        req_id, tenant_id, scheme, doc_id, doc_no, doc_date, current_state,
        remark, current_user_id, current_role_id, flg_user_role, label,
        data, flow_id, create_datetime, create_user_id, create_role_id,
        update_datetime, update_user_id, update_role_id, version)
    SELECT A.po_internal_id||'_'||A.doc_no, A.tenant_id, vPoInternalScheme, A.po_internal_id, A.doc_no, A.doc_date, vWorkflowApproved,
        A.remark, pUserId, pRoleId, vFlgUserRole, 'poInternal'||A.doc_no,
        '{}', vPoInternalFlowId, pDatetime, pUserId, pRoleId,
        pDatetime, pUserId, pRoleId, 0
    FROM pu_po_internal A
    WHERE A.po_internal_id = vPoInternalId;

    -- Generate data awe_historydoc
    INSERT INTO awe_historydoc(
            tenant_id, req_id, doc_id, scheme, user_id, role_id,
            activity, previous_state, next_state, remark, next_user_id, next_role_id,
            flg_user_role, activity_datetime, version)
    SELECT pTenantId, B.req_id, A.po_internal_id, vPoInternalScheme, pUserId, pRoleId,
            'CREATED FROM FUNCTION', '', '', A.remark, vEmptyId, vEmptyId,
            vFlgUserRole, pDateTime, 0
    FROM pu_po_internal A
    INNER JOIN awe_currdoc_status B ON B.doc_id = A.po_internal_id AND B.doc_no = A.doc_no AND B.doc_date = A.doc_date
    WHERE A.tenant_id = pTenantId
        AND A.po_internal_id = vPoInternalId;


    DELETE FROM cb_insufficient_cashbank_balance WHERE session_id = pSessionId;

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