f_upload_coa

CREATE OR REPLACE FUNCTION f_upload_coa(bigint)
  RETURNS integer AS
$BODY$
DECLARE
    pUlHeaderId             ALIAS FOR $1;

    vCount                  bigint;
    vStatusOk               character varying := 'OK';
    vStatusFail             character varying := 'FAIL';
    vStatusX                character varying := 'X';
    vEmpty                  character varying := '';
    vTypeData               character varying := 'coa';
    vDateTo                 character varying := '22991231';
    vModeLogAdd             character varying := 'A';
    vModeLogUpdate          character varying := 'U';
    vComboIdCoaType         character varying := 'COATYPE';
    vNullLongValue          bigint := -99;

    vCurrentDateTime        character varying;
    vTenantId               bigint;
    vCurrentDate            character varying;
    vSessionId              character varying;

BEGIN
    SELECT f_datetime((extract(epoch from now())*1000)::bigint) INTO vCurrentDateTime;
    SELECT to_char(CURRENT_DATE, 'YYYYMMDD') INTO vCurrentDate;
    SELECT generate_session_id() INTO vSessionId;
    SELECT tenant_id INTO vTenantId FROM ul_header WHERE ul_header_id = pUlHeaderId;

    -- update tenant di table ul coa
    UPDATE ul_coa
        SET tenant_id = vTenantId
    WHERE ul_header_id = pUlHeaderId;

    UPDATE ul_coa Z
        SET status = vStatusX , message = Z.message || 'Type Coa tidak terdaftar, '
    FROM ul_coa A
    LEFT JOIN t_combo_value B ON A.type_coa_code = B.code AND B.combo_id = vComboIdCoaType
    WHERE A.ul_header_id = pUlHeaderId
        AND A.ul_coa_id = Z.ul_coa_id
        AND A.status <> vStatusFail
        AND B.code IS NULL;

    -- Sign coa code harus terdafar di table master
    UPDATE ul_coa Z
        SET status = vStatusX , message = Z.message || 'Sign Coa tidak terdaftar, '
    FROM ul_coa A
    LEFT JOIN t_combo_value B ON A.sign_coa = B.code AND B.combo_id = vComboIdCoaType
    WHERE A.ul_header_id = pUlHeaderId
        AND A.ul_coa_id = Z.ul_coa_id
        AND A.status <> vStatusFail
        AND B.code IS NULL;

    -- validasi kode group coa harus terdaftar di table master
    UPDATE ul_coa Z
        SET status = vStatusX , message = Z.message || 'Kode Group Coa tidak terdaftar,  '
    FROM ul_coa A
    LEFT JOIN m_group_coa B ON A.group_coa_code = B.group_coa_code AND A.tenant_id = B.tenant_id
    WHERE A.ul_header_id = pUlHeaderId
        AND A.ul_header_id = Z.ul_header_id
        AND A.ul_coa_id = Z.ul_coa_id
        AND A.status <> vStatusFail
        AND B.group_coa_code IS NULL;
    /*
     * TODO menambahkan valdiasi dupikasi.
     */
    WITH duplicate AS (
        SELECT tenant_id,main_acc,sub_acc,COUNT(main_acc)
        FROM ul_coa
        WHERE ul_header_id = pUlHeaderId
        GROUP BY tenant_id,main_acc,sub_acc
        HAVING COUNT(main_acc) > 1
        )
    UPDATE ul_coa AS Z
        SET status = vStatusX, message = Z.message || 'Coa yang diinput tidak boleh duplikat, '
    FROM ul_coa A
    INNER JOIN duplicate B
        ON A.tenant_id = B.tenant_id AND A.main_acc = B.main_acc AND A.sub_acc = B.sub_acc
    WHERE A.ul_header_id = pUlHeaderId
        AND A.status <> vStatusFail
        AND A.ul_coa_id = Z.ul_coa_id;

    /*
     * TODO: Menambahkan validasi
     */

    UPDATE ul_coa Z
        SET status = vStatusX, message = Z.message || 'Type Coa ' || A.type_coa_code || ' tidak terdaftar di Group Coa Code ' || A.group_coa_code || ', '
    FROM ul_coa A
    LEFT JOIN m_group_coa B ON A.tenant_id = B.tenant_id
        AND A.group_coa_code = B.group_coa_code
        AND A.type_coa_code = B.type_coa
    WHERE A.ul_header_id = pUlHeaderId
        AND A.status <> vStatusFail
        AND A.ul_coa_id = Z.ul_coa_id
        AND B.group_coa_code IS NULL;

    --Set Status X menjadi FAIL
    UPDATE ul_coa
        SET status = vStatusFail
    WHERE status = vStatusX
    AND ul_header_id = pUlHeaderId;

    --Set Status '' menjadi OK
    UPDATE ul_coa
        SET status = vStatusOk
    WHERE status <> vStatusFail
    AND ul_header_id = pUlHeaderId;

    INSERT INTO m_coa(
            coa_id, tenant_id, main_acc, sub_acc, group_coa_id, coa_desc,
            sign_coa, create_datetime, create_user_id, update_datetime, update_user_id,
            version, active, active_datetime, non_active_datetime)
    SELECT nextval('coa_seq') AS coa_id,A.tenant_id,A.main_acc,A.sub_acc,B.group_coa_id,A.coa_desc,
        A.sign_coa,vCurrentDateTime AS create_datetime,C.user_id AS create_user_id,vCurrentDateTime AS update_datetime,C.user_id AS update_user_id,
        0 AS version,'Y' AS active,vCurrentDateTime AS active_datetime,'' AS non_active_datetime
    FROM ul_coa A
    INNER JOIN m_group_coa B ON A.group_coa_code = B.group_coa_code AND A.tenant_id = B.tenant_id
    INNER JOIN ul_header C ON A.ul_header_id = C.ul_header_id
    WHERE A.status <> vStatusFail
    AND A.ul_header_id = pUlHeaderId
    AND NOT EXISTS (SELECT 1
            FROM m_coa Z
            WHERE A.tenant_id = Z.tenant_id
            AND A.main_acc = Z.main_acc
            AND A.sub_acc = Z.sub_acc);

    SELECT COUNT(1) INTO vCount
    FROM ul_coa
    WHERE status = vStatusFail AND ul_header_id = pUlHeaderId;

    RETURN vCount;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
/