Function Create Receive Goods

CREATE OR REPLACE FUNCTION f_create_rg_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;

    vReceiveGoodsDocTypeId      bigint := 111;
    vEmptyString                character varying:='';
    vStatusDocDraft             character varying(1):='D';
    vWorkflowStatusDraft        character varying:='DRAFT';
    vWorkflowStatusApproved     character varying:='APPROVED';
    vRgId                       bigint;

BEGIN

    SELECT NEXTVAL('pu_receive_goods_seq') INTO vRgId;

    --karena langsung disubmit maka workflow status RG langsung dibuat approved
    INSERT INTO pu_receive_goods(
            receive_goods_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,
            warehouse_id, status_doc, workflow_status, version,
            create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT vRgId, pTenantId, vReceiveGoodsDocTypeId, 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,
            A.warehouse_id, vStatusDocDraft, vWorkflowStatusApproved, 0,
            pDatetime, pUserId, pDatetime, pUserId
    FROM pu_po A
    WHERE po_id = pGoodsPurchasingDocTypeId;

    INSERT INTO pu_receive_goods_item(
            tenant_id, receive_goods_id, line_no,
            ref_doc_type_id, ref_id, product_id, qty_rcv_po, po_uom_id, qty_rcv_int,
            base_uom_id, remark,
            version, create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT pTenantId, vRgId, A.line_no,
            B.doc_type_id, A.po_item_id, A.product_id, A.qty_po, A.po_uom_id, A.qty_int,
            A.base_uom_id, A.remark,
            0, pDatetime, pUserId, pDatetime, pUserId
    FROM pu_po_item A
    JOIN pu_po B ON A.po_id = B.po_id
    WHERE B.po_id = pGoodsPurchasingDocTypeId;

    INSERT INTO pu_receive_goods_product(
                tenant_id, receive_goods_item_id, line_no,
                product_id, serial_number, product_expired_date, product_year_made,
                lot_number, qty_rcv_po, po_uom_id, qty_rcv_int, base_uom_id, remark,
                version, create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT A.tenant_id, A.receive_goods_item_id, B.line_no,
        A.product_id, B.serial_number, B.expired_date, B.year_made,
        B.lot_number, B.qty_rcv_po, B.base_uom_id, B.qty_rcv_int, B.base_uom_id, b.remark,
        0, pDatetime, pUserId, pDatetime, pUserId
    FROM pu_receive_goods_item A
    JOIN pu_po_item_product B ON A.ref_id = B.po_item_id
    WHERE A.receive_goods_id = vRgId;

    --  add data serial number auto sn
    -- INSERT INTO  pu_receive_goods_product_auto_sn
    -- NOTE: untuk bagian ini tidak dibuat karena semua serial number dari GP tidak di auto generate

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