Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE OR REPLACE FUNCTION ul_upload_ou(bigint)
- RETURNS bigint AS
- $BODY$
- DECLARE
- pUploadHeaderId ALIAS FOR $1;
- vFail character varying(4) := 'FAIL';
- vOk character varying(2) := 'OK';
- vYes character varying(1) := 'Y';
- vNo character varying(1) := 'N';
- vActionInsert character varying(1) := 'I';
- vActionUpdate character varying(1) := 'U';
- vActionError character varying(1) := 'E';
- vEmpty character varying(1) := '';
- vSpaceValue character varying(1) := ' ';
- vTenantId bigint;
- vUserId bigint;
- vDatetime character varying(14);
- vCountFail bigint:=0;
- vEmptyId bigint := -99;
- vTempId bigint := -999;
- BEGIN
- -- siapkan parameter
- vTenantId = CAST( f_get_upload_parameter(pUploadHeaderId, 'tenantId') AS bigint );
- vUserId = CAST( f_get_upload_parameter(pUploadHeaderId, 'userId') AS bigint );
- vDatetime = CAST( f_get_upload_parameter(pUploadHeaderId, 'datetime') AS character varying );
- -- Validasi
- -- # Kode OU harus diisi
- -- # Max character dari Kode OU adalah 50
- -- # Kode OU harus belum ada dalam sistem
- -- # Kode Tipe OU harus diisi
- -- # Kode Tipe OU harus ada dalam sistem
- -- # Jika Kode OU Parent diisi, Kode OU Parent tersebut harus memiliki tenant yang sama
- -- # Nama OU harus diisi
- -- # Max character dari Nama OU adalah 255
- -- # Jika Kode OU Parent diisi, Kode OU Parent tersebut harus ada dalam sistem
- -- # List pada CSV yang diupload, tidak boleh duplikat berdasarkan Kode OU
- -- # Kode OU harus diisi
- UPDATE ul_import_data_ou A
- SET status = vFail,
- message = message || 'Kode OU harus diisi, '
- WHERE A.upload_header_id = pUploadHeaderId
- AND TRIM(A.kode_ou) = vEmpty;
- -- # Max character dari Kode OU adalah 50
- UPDATE ul_import_data_ou A
- SET status = vFail,
- message = message || 'Panjang Kode OU maksimal 50 karakter, '
- WHERE A.upload_header_id = pUploadHeaderId
- AND length(A.kode_ou) > 50;
- -- # Kode OU harus belum ada dalam sistem
- UPDATE ul_import_data_ou A
- SET status = vFail,
- message = message || 'Kode OU sudah ada dalam sistem, '
- WHERE A.upload_header_id = pUploadHeaderId
- AND EXISTS (
- SELECT 1
- FROM t_ou B
- WHERE B.ou_code = A.kode_ou
- );
- -- # Kode Tipe OU harus diisi
- UPDATE ul_import_data_ou A
- SET status = vFail,
- message = message || 'Kode Tipe OU harus diisi, '
- WHERE A.upload_header_id = pUploadHeaderId
- AND TRIM(A.kode_tipe_ou) = vEmpty;
- -- # Kode Tipe OU harus ada dalam sistem
- UPDATE ul_import_data_ou A
- SET status = vFail,
- message = message || 'Kode Tipe OU tidak ada dalam sistem, '
- WHERE A.upload_header_id = pUploadHeaderId
- AND TRIM(A.kode_tipe_ou) <> vEmpty
- AND NOT EXISTS (
- SELECT 1
- FROM t_ou_type B
- WHERE B.ou_type_code = A.kode_tipe_ou
- AND B.tenant_id = vTenantId
- );
- -- # Nama OU harus diisi
- UPDATE ul_import_data_ou A
- SET status = vFail,
- message = message || 'Nama OU harus diisi, '
- WHERE A.upload_header_id = pUploadHeaderId
- AND TRIM(A.nama_ou) = vEmpty;
- -- # Max character dari Nama OU adalah 255
- UPDATE ul_import_data_ou A
- SET status = vFail,
- message = message || 'Panjang Nama OU maksimal 255 karakter, '
- WHERE A.upload_header_id = pUploadHeaderId
- AND length(A.nama_ou) > 255;
- -- # Jika Kode OU Parent diisi, Kode OU Parent tersebut harus ada dalam sistem
- UPDATE ul_import_data_ou A
- SET status = vFail,
- message = message || 'Kode OU Parent tidak ada dalam sistem, '
- WHERE A.upload_header_id = pUploadHeaderId
- AND TRIM(A.kode_ou_parent) <> vEmpty
- AND NOT EXISTS (
- SELECT 1
- FROM t_ou B
- WHERE B.ou_code = A.kode_ou_parent
- AND B.tenant_id = vTenantId
- );
- -- # List pada CSV yang diupload, tidak boleh duplikat berdasarkan Kode OU
- WITH duplicate_data_ou AS (
- SELECT A.kode_ou
- FROM ul_import_data_ou A
- WHERE A.upload_header_id = pUploadHeaderId
- AND TRIM(A.kode_ou) <> vEmpty
- GROUP BY A.kode_ou
- HAVING COUNT(1) > 1
- )
- UPDATE ul_import_data_ou A
- SET status = vFail,
- message = message || 'Kode OU duplikat, '
- WHERE A.upload_header_id = pUploadHeaderId
- AND EXISTS (
- SELECT 1 FROM duplicate_data_ou B WHERE A.kode_ou = B.kode_ou
- );
- -- # Cari data Tipe OU
- UPDATE ul_import_data_ou A
- SET flg_bu = B.flg_bu,
- flg_accounting = B.flg_accounting,
- flg_sub_bu = B.flg_sub_bu,
- flg_branch = B.flg_branch
- FROM t_ou_type B
- WHERE A.upload_header_id = pUploadHeaderId
- AND A.status <> vFail
- AND B.ou_type_code = A.kode_tipe_ou;
- -- # Cari data OU Parent
- UPDATE ul_import_data_ou A
- SET ou_parent_id = B.ou_id,
- parent_flg_bu = C.flg_bu,
- parent_flg_accounting = C.flg_accounting,
- parent_flg_sub_bu = C.flg_sub_bu,
- parent_flg_branch = C.flg_branch
- FROM t_ou B
- INNER JOIN t_ou_type C ON B.ou_type_id = C.ou_type_id
- WHERE A.upload_header_id = pUploadHeaderId
- AND A.status <> vFail
- AND B.ou_code = A.kode_ou_parent;
- -- # Untuk OU yang merupakan Business Unit, parent OU harus -99
- UPDATE ul_import_data_ou A
- SET status = vFail,
- message = message || 'OU business unit tidak boleh memiliki parent, '
- WHERE A.upload_header_id = pUploadHeaderId
- AND A.status <> vFail
- AND A.flg_bu = vYes
- AND A.flg_accounting = vNo
- AND A.flg_sub_bu = vNo
- AND A.flg_branch = vNo
- AND A.ou_parent_id <> vEmptyId;
- -- # Selain Business Unit, harus memiliki parent Id
- UPDATE ul_import_data_ou A
- SET status = vFail,
- message = message || 'OU selain business unit harus memiliki parent, '
- WHERE A.upload_header_id = pUploadHeaderId
- AND A.status <> vFail
- AND NOT (A.flg_bu = vYes
- AND A.flg_accounting = vNo
- AND A.flg_sub_bu = vNo
- AND A.flg_branch = vNo)
- AND A.ou_parent_id = vEmptyId;
- -- # Untuk OU yang merupakan Sub Business Unit, parent OU nya harus merupakan Business Unit atau Branch
- UPDATE ul_import_data_ou A
- SET status = vFail,
- message = message || 'OU sub business unit harus memiliki parent business unit atau branch, '
- WHERE A.upload_header_id = pUploadHeaderId
- AND A.status <> vFail
- AND A.flg_bu = vYes
- AND A.flg_accounting = vNo
- AND A.flg_sub_bu = vYes
- AND A.flg_branch = vNo
- AND NOT (A.parent_flg_bu = vYes
- AND A.parent_flg_accounting = vNo
- AND A.parent_flg_sub_bu = vNo);
- -- # Untuk OU yang merupakan Branch, parent OU nya harus merupakan Business Unit
- UPDATE ul_import_data_ou A
- SET status = vFail,
- message = message || 'OU branch harus memiliki parent business unit, '
- WHERE A.upload_header_id = pUploadHeaderId
- AND A.status <> vFail
- AND A.flg_bu = vYes
- AND A.flg_accounting = vNo
- AND A.flg_sub_bu = vNo
- AND A.flg_branch = vYes
- AND NOT (A.parent_flg_bu = vYes
- AND A.parent_flg_accounting = vNo
- AND A.parent_flg_sub_bu = vNo
- AND A.parent_flg_branch = vNo);
- -- Cek apakah ada item CSV yang statusnya fail
- IF EXISTS ( SELECT 1 FROM ul_import_data_ou B
- WHERE B.upload_header_id = pUploadHeaderId
- AND B.status = vFail ) THEN
- -- Sesuaikan message, agar message paling belakang tidak ada koma
- UPDATE ul_import_data_ou A
- SET message = substr(A.message, 1, length(A.message)-2)
- WHERE A.upload_header_id = pUploadHeaderId
- AND A.status = vFail
- AND TRIM(A.message) != vEmpty;
- -- Update semua status menjadi FAIL jika ada salah satu item yang terkena validasi
- -- Karena jika ada salah 1 item yang fail, maka 1 csv dianggap tidak valid
- -- ubah update_status menjadi E untuk menandakan bahwa data error / tidak digunakan untuk membuat data
- UPDATE ul_import_data_ou A
- SET status = vFail,
- update_status = vActionError
- WHERE upload_header_id = pUploadHeaderId;
- -- Hitung jumlah data yang FAIL
- SELECT COUNT(1) INTO vCountFail
- FROM ul_import_data_ou
- WHERE upload_header_id = pUploadHeaderId
- AND status = vFail;
- ELSE
- -- Update status non FAIL to OK
- UPDATE ul_import_data_ou
- SET status = vOk
- WHERE upload_header_id = pUploadHeaderId;
- -- UPDATE OK data untuk ou code terdaftar
- /*WITH update_data_ou AS (
- UPDATE t_ou A SET
- ou_name=B.nama_ou,
- --ou_parent_id=B.ou_parent_id,
- ou_type_id=C.ou_type_id,
- update_datetime=vDatetime,
- update_user_id=vUserId,
- version=A.version+1
- FROM ul_import_data_ou B
- INNER JOIN t_ou_type C ON B.kode_tipe_ou = C.ou_type_code
- WHERE B.upload_header_id = pUploadHeaderId
- AND B.status = vOk
- AND A.ou_code = B.kode_ou
- AND A.tenant_id = vTenantId
- AND C.tenant_id = vTenantId
- RETURNING A.*
- )
- -- Ubah update_status menjadi U jika data digunakan untuk Update
- UPDATE ul_import_data_ou A SET
- update_status = vActionUpdate
- WHERE A.upload_header_id = pUploadHeaderId
- AND A.status = vOk
- AND EXISTS (
- SELECT 1 FROM update_data_ou B WHERE A.kode_ou = B.ou_code AND B.tenant_id = vTenantId
- );*/
- -- INSERT OK data untuk ou code baru
- WITH insert_data_ou AS (
- INSERT INTO t_ou(
- tenant_id, ou_code, ou_name, ou_parent_id, ou_type_id,
- create_datetime, create_user_id, update_datetime, update_user_id,
- version, active, active_datetime, non_active_datetime)
- SELECT vTenantId, kode_ou, nama_ou, ou_parent_id, B.ou_type_id, vDatetime,
- vUserId, vDatetime, vUserId, 0, vYes,
- vDatetime, vSpaceValue
- FROM ul_import_data_ou A
- INNER JOIN t_ou_type B ON A.kode_tipe_ou = B.ou_type_code
- WHERE A.upload_header_id = pUploadHeaderId
- AND A.status = vOk
- AND B.tenant_id = vTenantId
- AND NOT EXISTS (
- SELECT 1 FROM t_ou Z
- WHERE A.kode_ou = Z.ou_code
- AND B.tenant_id = vTenantId
- )
- RETURNING *
- )
- -- Ubah update_status menjadi I jika data digunakan untuk Insert
- UPDATE ul_import_data_ou A SET
- update_status = vActionInsert
- WHERE A.upload_header_id = pUploadHeaderId
- AND A.status = vOk
- AND EXISTS (
- SELECT 1 FROM insert_data_ou B WHERE A.kode_ou = B.ou_code AND B.tenant_id = vTenantId
- );
- END IF;
- RETURN vCountFail;
- END;
- $BODY$
- LANGUAGE plpgsql VOLATILE
- COST 100;
- /
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement