pu_submit_po

-- Function: pu_submit_po(bigint, character varying, character varying)

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

CREATE OR REPLACE FUNCTION pu_submit_po(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;
    vUserId                     bigint;
    vDatetime                   character varying(14);
    vStatusRelease              character varying(1);
    vStatusFinal                character varying(1);
    vEmptyId                    bigint;
    vEmptyString                character varying;
    vPurchaseOrderDocTypeId     bigint;
    vRoundingModeNonTax         character varying(5);
    vFlgNo                      character varying(1);
    vFlgYes                     character varying(1);
    vDiscountAmount             numeric;


BEGIN

    vFlgYes := 'Y';
    vFlgNo := 'N';
    vStatusRelease := 'R';
    vStatusFinal := 'F';
    vEmptyId := -99;
    vEmptyString := '';
    vPurchaseOrderDocTypeId = 101;

    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_po' AND
        A.process_no = pProcessNo;

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

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


    INSERT INTO pu_po_tax
    (tenant_id, po_id, tax_id, flg_amount,
    tax_percentage, base_amount, tax_amount, remark,
    version, create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT A.tenant_id, A.po_id, A.tax_id, B.flg_amount,
        A.tax_percentage, (SUM(f_get_amount_before_tax(A.gross_item_amount,
                                                        A.flg_tax_amount,
                                                        A.tax_percentage,
                                                        f_get_digit_decimal_doc_curr(vPurchaseOrderDocTypeId, A.curr_code),
                                                        vRoundingModeNonTax)) - C.discount_amount),
        f_tax_rounding(A.tenant_id,  (SUM(f_get_amount_before_tax(A.gross_item_amount,
                                                                    A.flg_tax_amount,
                                                                    A.tax_percentage,
                                                                    f_get_digit_decimal_doc_curr(vPurchaseOrderDocTypeId, A.curr_code),
                                                                    vRoundingModeNonTax)) - C.discount_amount), A.tax_percentage), B.tax_name,
        0, vDatetime, vUserId, vDatetime, vUserId
    FROM pu_po_item A, m_tax B, pu_po_discount C
    WHERE A.tax_id = B.tax_id AND
          A.po_id = vPoId AND
          A.tax_id <> vEmptyId AND
          A.po_id = C.po_id
    GROUP BY A.tenant_id, A.po_id, A.tax_id, B.flg_amount,
            A.tax_percentage, B.tax_name, C.discount_amount;

    INSERT INTO pu_po_balance_item
    (po_item_id, tenant_id, ou_id, qty_po, qty_rcv, qty_return, qty_cancel, qty_add, po_uom_id,
     qty_int_po, qty_int_rcv, qty_int_return, qty_int_cancel, qty_int_add, base_uom_id,
     tolerance_rcv_qty, status_item, version, create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT A.po_item_id, A.tenant_id, B.ou_id, A.qty_po, 0, 0, 0, 0, A.po_uom_id,
            A.qty_int, 0, 0, 0, 0, A.base_uom_id,
            A.tolerance_rcv_qty, vStatusRelease, 0, vDatetime, vUserId, vDatetime, vUserId
    FROM pu_po_item A, pu_po B
    WHERE A.po_id = vPoId AND
        A.po_id = B.po_id;

    INSERT INTO pu_log_po_balance_item
    (tenant_id, po_id, po_item_id, ref_doc_type_id, ref_id, ref_item_id,
     qty_trx, trx_uom_id, qty_int, base_uom_id, remark,
    version, create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT A.tenant_id, A.po_id, A.po_item_id, vEmptyId, vEmptyId, vEmptyId,
        A.qty_po, A.po_uom_id, A.qty_int, A.base_uom_id, A.remark,
        0, vDatetime, vUserId, vDatetime, vUserId
    FROM pu_po_item A
    WHERE A.po_id = vPoId;

    -- Jika PO reference dari SO, maka update qty_int dan status table sl_so_po_balance_item
    UPDATE sl_so_po_balance_item
        SET qty_po_int = qty_po_int + A.qty_int, status_item = vStatusRelease,
            update_datetime = vDatetime, update_user_id = vUserId, version = sl_so_po_balance_item.version + 1
    FROM pu_po_item A
    WHERE A.po_id = vPoId AND
        A.ref_id <> -99 AND
        A.ref_id = sl_so_po_balance_item.so_item_id;

    -- Update status item = F pada table sl_so_po_balance_item, jika total qty SO <= total qty PO
    UPDATE sl_so_po_balance_item
        SET status_item = vStatusFinal
    FROM pu_po_item A, sl_so_balance_item B
    WHERE A.po_id = vPoId AND
        A.ref_id <> -99 AND
        A.ref_id = sl_so_po_balance_item.so_item_id AND
        B.so_item_id = sl_so_po_balance_item.so_item_id AND
        (B.qty_so_int + B.qty_add_int - B.qty_cancel_int) <= (sl_so_po_balance_item.qty_po_int + sl_so_po_balance_item.qty_po_int_add - sl_so_po_balance_item.qty_po_int_cancel);

    UPDATE pu_po SET status_doc = vStatusRelease, update_datetime = vDatetime, update_user_id = vUserId, version = pu_po.version + 1
    WHERE po_id = vPoId;



    -- Jika ada discount maka Insert into pu_po_balance_discount
    INSERT INTO pu_po_balance_discount(tenant_id, ou_id, po_id, curr_code, discount_amount, current_amount, status,
        ref_id, ref_doc_type_id, remark, version, create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT A.tenant_id, A.ou_id, A.po_id, A.curr_code, B.discount_amount, 0, vFlgNo,
            vEmptyId, vEmptyId, vEmptyString, 0, vDatetime, vUserId, vDatetime, vUserId
    FROM pu_po A
    INNER JOIN pu_po_discount B ON A.po_id = B.po_id
    WHERE A.po_id = vPoId AND B.discount_amount <> 0;


END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION pu_submit_po(bigint, character varying, character varying)
  OWNER TO sts;