Function Submit Goods Purchasing

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

    vDocJournalRg                   DOC_JOURNAL%ROWTYPE;
    vDocJournalPi                   DOC_JOURNAL%ROWTYPE;
    vOuStructure                OU_BU_STRUCTURE%ROWTYPE;
    result                      RECORD;
    vDocTypeReceiveGoodsId      bigint;
    vDocTypePurchaseInvoiceId   bigint;

    vYes                    character varying:='Y';
    vWorkFlowStatus         character varying:='APPROVED';
    vFlgRole                character varying:='R';
    vProcessNameRg          character varying:='pu_submit_receive_goods';
    vParamKeyRg             character varying:='receiveGoodsId';
    vSchemeRg               character varying:='CB01';
    vFlowRgId               bigint;
    vProcessNamePi          character varying:='pu_submit_purch_invoice';
    vParamKeyPi             character varying:='purchInvoiceId';
    vSchemePi               character varying:='CD01';
    vSchemeGp               character varying:='CF01';
    vFlowPiId               bigint;
    vRoleId                 bigint;
    vActivity               character varying := 'AUTO GENERATE';

BEGIN

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

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

    SELECT role_id INTO vRoleId
    FROM awe_historydoc
    WHERE scheme = vSchemeGp
    AND doc_id = vGoodsPurchasingId
    AND activity = 'SUBMIT';

    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 balance item po
    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, A.qty_po, 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 = vGoodsPurchasingId
    AND A.po_id = B.po_id;

    -- insert log balance item po
    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 = vGoodsPurchasingId;

    -- pemanggilan function create RG
    PERFORM f_create_rg_for_submit_goods_purchasing(pTenantId, vGoodsPurchasingId, vUserId, vDatetime);

    -- process submit RG
    --insert process message
    PERFORM generate_process_message_for_submit_doc(pSessionId, pTenantId, vProcessNameRg, A.receive_goods_id ||'_'||A.doc_no,
                vDatetime, vParamKeyRg, A.receive_goods_id::character varying, vUserId)
    FROM pu_receive_goods A
    WHERE A.ref_id = vGoodsPurchasingId
    AND A.ref_doc_type_id = vGoodsPurchasingDocTypeId;

    -- Mendapatkan default approval flow ID yang dipakai dari sysconfig
    SELECT awe_flow_id INTO vFlowRgId
    FROM awe_flow
    WHERE scheme = vSchemeRg
    AND flg_validate = vYes
    AND active = vYes;

     -- 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.receive_goods_id||'_'||A.doc_no, A.tenant_id, vSchemeRg, A.receive_goods_id, A.doc_no, A.doc_date, vWorkFlowStatus,
        A.remark, A.create_user_id, vEmptyId, vFlgRole, 'RECEIVE GOODS'||A.doc_no,
        '{}', vFlowRgId, vDatetime, vUserId, vEmptyId,
        vDatetime, vUserId, vEmptyId, 0
    FROM pu_receive_goods A
    WHERE A.ref_id = vGoodsPurchasingId
    AND A.ref_doc_type_id = vGoodsPurchasingDocTypeId;

    INSERT INTO awe_worklist(
            req_id, user_id, role_id, no_item, tenant_id, scheme, doc_id,
            doc_no, doc_date, status, remark, flg_user_role, flg_to_do,
            create_datetime, create_user_id, update_datetime, update_user_id, version)
    SELECT A.receive_goods_id||'_'||A.doc_no, vUserId, vEmptyId, 1, A.tenant_id, vSchemeRg, A.receive_goods_id,
        A.doc_no, A.doc_date, vWorkFlowStatus, A.remark, vFlgRole ,vYes,
        vDatetime, vUserId, vDatetime, vUserId, 0
    FROM pu_receive_goods A
    WHERE A.ref_id = vGoodsPurchasingId
    AND A.ref_doc_type_id = vGoodsPurchasingDocTypeId;

    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 A.tenant_id, A.receive_goods_id||'_'||A.doc_no, A.receive_goods_id, vSchemeRg, vUserId, vRoleId,
            vActivity, '', vWorkFlowStatus, A.remark, vEmptyId, vEmptyId,
            vFlgRole, vDatetime,0
    FROM pu_receive_goods A
    WHERE A.ref_id = vGoodsPurchasingId
    AND A.ref_doc_type_id = vGoodsPurchasingDocTypeId;

    -- pemanggilan function create PI
    PERFORM f_create_pi_for_submit_goods_purchasing(pTenantId, vGoodsPurchasingId, vUserId, vDatetime);

    -- UPDATE balance invoice
    UPDATE pu_po_balance_invoice B
    SET invoice_id = A.invoice_id
    FROM pu_invoice A
    WHERE A.ref_id = vGoodsPurchasingId
    AND A.ref_doc_type_id = vGoodsPurchasingDocTypeId
    AND B.po_id = A.ref_id;

    -- UPDATE balance invoice tax
    UPDATE pu_po_balance_invoice_tax B
    SET invoice_id = A.invoice_id
    FROM pu_invoice A
    WHERE A.ref_id = vGoodsPurchasingId
    AND A.ref_doc_type_id = vGoodsPurchasingDocTypeId
    AND B.po_id = A.ref_id;


    -- process submit PI
    --insert process message
    PERFORM generate_process_message_for_submit_doc(pSessionId, pTenantId, vProcessNamePi, A.invoice_id ||'_'||A.doc_no,
                vDatetime, vParamKeyPi, A.invoice_id::character varying, vUserId)
    FROM pu_invoice A
    WHERE A.ref_id = vGoodsPurchasingId
    AND A.ref_doc_type_id = vGoodsPurchasingDocTypeId;

    -- Mendapatkan default approval flow ID yang dipakai dari sysconfig
    SELECT awe_flow_id INTO vFlowPiId
    FROM awe_flow
    WHERE scheme = vSchemePi
    AND flg_validate = vYes
    AND active = vYes;

     -- 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.invoice_id||'_'||A.doc_no, A.tenant_id, vSchemePi, A.invoice_id, A.doc_no, A.doc_date, vWorkFlowStatus,
        A.remark, A.create_user_id, vEmptyId, vFlgRole, 'PURCHASE INVOICE'||A.doc_no,
        '{}', vFlowPiId, vDatetime, vUserId, vEmptyId,
        vDatetime, vUserId, vEmptyId, 0
    FROM pu_invoice A
    WHERE A.ref_id = vGoodsPurchasingId
    AND A.ref_doc_type_id = vGoodsPurchasingDocTypeId;

    INSERT INTO awe_worklist(
            req_id, user_id, role_id, no_item, tenant_id, scheme, doc_id,
            doc_no, doc_date, status, remark, flg_user_role, flg_to_do,
            create_datetime, create_user_id, update_datetime, update_user_id, version)
    SELECT A.invoice_id||'_'||A.doc_no, vUserId, vEmptyId, 1, A.tenant_id, vSchemePi, A.invoice_id,
        A.doc_no, A.doc_date, vWorkFlowStatus, A.remark, vFlgRole ,vYes,
        vDatetime, vUserId, vDatetime, vUserId, 0
    FROM pu_invoice A
    WHERE A.ref_id = vGoodsPurchasingId
    AND A.ref_doc_type_id = vGoodsPurchasingDocTypeId;

    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 A.tenant_id, A.invoice_id||'_'||A.doc_no, A.invoice_id, vSchemePi, vUserId, vRoleId,
            vActivity, '', vWorkFlowStatus, A.remark, vEmptyId, vEmptyId,
            vFlgRole, vDatetime,0
    FROM pu_invoice A
    WHERE A.ref_id = vGoodsPurchasingId
    AND A.ref_doc_type_id = vGoodsPurchasingDocTypeId;

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