sl_submit_cancel_so_from_webclinic

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

    vStatusRelease  character varying(1);
    vStatusCancel   character varying(1);
    vStatusFinal    character varying(1);
    vEmptyId        bigint;
    vSoId           bigint;
    vUnfinishedItem     numeric;

    pUserIdForGenerateDoc   BIGINT;
    vRoleIdForGenerateDoc   BIGINT;
    vSchema                 CHARACTER VARYING := 'FA02';
    vYes                    character varying := 'Y';
    vWorkflowStatus         CHARACTER VARYING := 'APPROVED';
    vParamUserIdForGenerateDoc  CHARACTER VARYING := 'created.user.document.for.webclinic';
    vParamRoleIdForGenerateDoc  CHARACTER VARYING := 'created.role.document.for.webclinic';
    vFlowId                 BIGINT;
    vFlgUserRole            CHARACTER VARYING := 'R';
    vCurrentDateTime        character varying;

BEGIN

    vStatusRelease := 'R';
    vStatusCancel := 'C';
    vStatusFinal := 'F';
    vEmptyId := -99;
    vUnfinishedItem := 0;

    -- Set user id from sysconfig
    SELECT f_get_value_system_config_by_param_code(pTenantId, vParamUserIdForGenerateDoc)::bigint INTO pUserIdForGenerateDoc;

    -- Set role id from sysconfig
    SELECT f_get_value_system_config_by_param_code(pTenantId, vParamRoleIdForGenerateDoc)::bigint INTO vRoleIdForGenerateDoc;

    SELECT f_datetime((extract(epoch from now())*1000)::bigint) INTO vCurrentDateTime;

    SELECT so_id INTO vSoId
    FROM sl_manage_so A
    WHERE manage_so_id = pCancelSoId;

/*
 * 1.update status item balance SO
 * 2.update status doc sl so, jika seluruh balance item sudah tidak ada yg release
 * 3.insert sl log balance item SO
 * 4.update workflow status manage SO
 * 5.update qty cancel dan status item balance PO terhadap SO
 */
    UPDATE sl_so_balance_item A SET status_item = vStatusCancel,
           qty_cancel = B.qty_so,
           qty_cancel_int = B.qty_int,
           update_datetime = vDatetime,
           update_user_id = vUserId
    FROM sl_manage_so_item B
    WHERE A.so_item_id = B.so_item_id AND
        B.manage_so_id = vCancelSoId;

    SELECT COUNT(1) INTO vUnfinishedItem
    FROM sl_so_balance_item A, sl_so_item B
    WHERE A.so_item_id = B.so_item_id AND
        B.so_id = vSoId AND
        A.status_item = vStatusRelease;

    IF vUnfinishedItem = 0 THEN
        UPDATE sl_so SET status_doc = vStatusFinal, update_datetime = pDatetime, update_user_id = pUserId, version = version+1
        WHERE so_id = vSoId;
    ELSE
        UPDATE sl_so SET status_doc = vStatusRelease, update_datetime = pDatetime, update_user_id = pUserId, version = version+1
        WHERE so_id = vSoId;
    END IF;

    INSERT INTO sl_log_so_balance_item
    (tenant_id, so_id, so_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.so_id, B.so_item_id, A.doc_type_id, A.manage_so_id, B.manage_so_item_id,
        -1 * B.qty_so, B.so_uom_id, -1 * B.qty_int, B.base_uom_id, A.remark,
        0, pDatetime, pUserId, pDatetime, pUserId
    FROM sl_manage_so A, sl_manage_so_item B
    WHERE A.manage_so_id = pCancelSoId AND
        A.manage_so_id = B.manage_so_id;

    UPDATE sl_manage_so SET status_doc = vStatusRelease, update_datetime = pDatetime, update_user_id = pUserId
    WHERE manage_so_id = pCancelSoId;

    -- WTC, 20141227, update qty cancel dan status item balance PO terhadap SO
    -- TODO: insert to sl_log_so_po_balance_item
    UPDATE sl_so_po_balance_item A SET status_item = vStatusCancel, update_datetime = pDatetime, update_user_id = pUserId, version = A.version + 1
    FROM sl_manage_so_item B
    WHERE A.so_item_id = B.so_item_id AND
        B.manage_so_id = pCancelSoId;

    -- Mendapatkan default approval flow ID so
    SELECT awe_flow_id INTO vFlowId
    FROM awe_flow
    WHERE scheme = vSchema AND
        flg_validate = vYes AND
        active = vYes;

    -- Generate data awe_currdoc_status so
    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.manage_so_id||'_'||A.doc_no, A.tenant_id, vSchema, A.manage_so_id, A.doc_no, A.doc_date, vWorkflowStatus,
        A.remark, pUserIdForGenerateDoc, vRoleIdForGenerateDoc, vFlgUserRole, 'Delivery Order Receipt '||A.doc_no,
        '{}', vFlowId, vCurrentDateTime, pUserIdForGenerateDoc, vRoleIdForGenerateDoc,
        vCurrentDateTime, pUserIdForGenerateDoc, vRoleIdForGenerateDoc, 0
    FROM sl_manage_so A
    WHERE A.manage_so_id = pCancelSoId;

    -- Generate data awe_historydoc so
    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.manage_so_id, vSchema, pUserIdForGenerateDoc, vRoleIdForGenerateDoc,
            'AUTO GENERATE', '', '', A.remark, vEmptyId, vEmptyId,
            vFlgUserRole, vCurrentDateTime, 0
    FROM sl_manage_so A
    INNER JOIN awe_currdoc_status B ON B.doc_id = A.manage_so_id AND B.doc_no = A.doc_no AND B.doc_date = A.doc_date
    WHERE A.manage_so_id = pCancelSoId;

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