Function Create Purchase Invoice

CREATE OR REPLACE FUNCTION f_create_pi_for_submit_goods_purchasing(bigint, bigint, bigint, character varying)
  RETURNS void AS
$BODY$
DECLARE
    pTenantId                   ALIAS FOR $1;
    pGoodsPurchasingDocTypeId   ALIAS FOR $2;
    pUserId                     ALIAS FOR $3;
    pDatetime                   ALIAS FOR $4;

    vPurchaseInvoiceDocTypeId   bigint := 131;
    vGpDocTypeId                bigint := 160;
    vEmptyString                character varying:='';
    vEmptyId                    bigint:=-99;
    vStatusDocDraft             character varying(1):='D';
    vWorkflowStatusDraft        character varying:='DRAFT';
    vWorkflowStatusApproved     character varying:='APPROVED';
    vPiId                       bigint;

BEGIN

    SELECT NEXTVAL('pu_invoice_seq ') INTO vPiId;

    INSERT INTO pu_invoice(
                invoice_id, tenant_id, doc_type_id, doc_no, doc_date, ou_id,
                ext_doc_no, ext_doc_date, ref_doc_type_id, ref_id, remark, partner_id,
                ou_legal_id, due_date, purchaser_id, curr_code, gross_amount,
                advance_amount, tax_amount, add_amount, total_amount, status_doc,
                workflow_status,
                version, create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT vPiId, pTenantId, vPurchaseInvoiceDocTypeId, A.doc_no, A.doc_date, A.ou_id,
            A.ext_doc_no, A.ext_doc_date, A.doc_type_id, A.po_id, A.remark, A.partner_id,
            vEmptyId, A.doc_date, A.purchaser_id, A.curr_code, 0,
            0, 0, 0, 0, vStatusDocDraft,
            vWorkflowStatusApproved,
            0, pDatetime, pUserId, pDatetime, pUserId
    FROM pu_po A
    WHERE A.po_id = pGoodsPurchasingDocTypeId;

    INSERT INTO pu_invoice_item(
            tenant_id, invoice_id, line_no, ref_doc_type_id,
            ref_id, ref_item_id, ref_item_amount, remark,
            version, create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT pTenantId, vPiId, A.line_no, B.doc_type_id,
            B.receive_goods_id, A.receive_goods_item_id, C.item_amount, A.remark,
            0, pDatetime, pUserId, pDatetime, pUserId
    FROM pu_receive_goods_item A
    JOIN pu_receive_goods B ON A.receive_goods_id = B.receive_goods_id
    JOIN pu_po_balance_invoice C ON B.ref_id = C.po_id
                    AND B.doc_type_id = C.ref_doc_type_id
                    AND B.receive_goods_id = C.ref_id
                    AND A.receive_goods_item_id = C.ref_item_id
    WHERE B.ref_id = pGoodsPurchasingDocTypeId
    AND B.ref_doc_type_id = vGpDocTypeId;

    INSERT INTO pu_invoice_tax(
                tenant_id, invoice_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 pTenantId, vPiId, A.tax_id, A.flg_amount, A.tax_percentage,
            A.base_amount, A.tax_amount, B.tax_no, B.tax_date, B.tax_curr_code, B.gov_tax_amount,
            A.remark,
            0, pDatetime, pUserId, pDatetime, pUserId
    FROM pu_po_tax A
    JOIN pu_po_tax_custom B ON A.po_id = B.po_id
    WHERE A.po_id = pGoodsPurchasingDocTypeId;

    WITH calc_nilai_item AS(
        SELECT A.invoice_id, SUM(A.ref_item_amount) AS item_amount
        FROM pu_invoice_item A
        WHERE A.invoice_id = vPiId
        GROUP BY A.invoice_id
    )
    UPDATE pu_invoice A
    SET gross_amount = B.item_amount + COALESCE(C.tax_amount, 0),
        tax_amount = COALESCE(C.tax_amount, 0),
        total_amount = B.item_amount
    FROM calc_nilai_item B
    LEFT JOIN pu_invoice_tax C ON B.invoice_id = C.invoice_id
    WHERE A.invoice_id = B.invoice_id
    AND A.invoice_id = vPiId;

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