Notes

--NAMA PACKAGE ATAU FUNCTION/PROCEDURE HARUS SAMA MENGIKUTI SOAL
--NAMA VARIABEL TIDAK BOLEH/JANGAN/JANGAN SAMPAI SAMA..


--MEMBUAT TABEL LOGINMANAGER
CREATE TABLE LoginManager (
USER_ID VARCHAR(10) NOT NULL,
PASSWORD VARCHAR(20) NOT NULL,
PRIMARY KEY(USER_ID)
);

--MEMBUAT PACKAGE SPECIFICATION
CREATE OR REPLACE PACKAGE PKG_HRD IS
FUNCTION F_PCT_DEPT_WITH_EMP RETURN VARCHAR;
FUNCTION F_CHK_MGR_ID(EMP_ID NUMBER) RETURN NUMBER;
FUNCTION F_GEN_PASS_MGR(EMP_ID NUMBER) RETURN VARCHAR;
PROCEDURE P_INS_MGR_LOGIN(EMP_ID NUMBER);
END PKG_HRD;

--MEMBUAT PACKAGE BODY
CREATE OR REPLACE PACKAGE BODY PKG_HRD IS

FUNCTION F_PCT_DEPT_WITH_EMP RETURN VARCHAR IS
DEPT_TOTAL NUMBER;
DEPT_WITH_EMP NUMBER;
BEGIN
SELECT COUNT(DEPARTMENT_ID) INTO DEPT_TOTAL FROM DEPARTMENTS;
SELECT COUNT(DEPARTMENT_ID) INTO DEPT_WITH_EMP FROM DEPARTMENTS WHERE MANAGER_ID IS NOT NULL;

RETURN (DEPT_WITH_EMP/DEPT_TOTAL) * 100;
END;

FUNCTION F_CHK_MGR_ID (EMP_ID NUMBER) RETURN NUMBER IS
HASIL NUMBER;
BEGIN
SELECT COUNT(MANAGER_ID) INTO HASIL FROM EMPLOYEES
WHERE MANAGER_ID = EMP_ID;
IF HASIL > 0 THEN
HASIL := 1;
ELSE
HASIL := 0;
END IF;
RETURN HASIL;
END;

FUNCTION F_GEN_PASS_MGR (EMP_ID NUMBER) RETURN VARCHAR IS
LAST_NAME_PASS VARCHAR(20);
HASIL VARCHAR(20);
BEGIN
IF F_CHK_MGR_ID(EMP_ID) = 1 THEN
SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(LOWER(LAST_NAME), 'a', '4'), 'i', 1), 'u', '7'), 'e', '6'), 'o', '0') INTO LAST_NAME_PASS
FROM EMPLOYEES WHERE EMPLOYEE_ID = EMP_ID;

HASIL := EMP_ID||LAST_NAME_PASS;
ELSE
RAISE_APPLICATION_ERROR(-20010,'EMPLOYEE_ID BUKAN MILIK MANAGER');
END IF;
RETURN HASIL;
END;

PROCEDURE P_INS_MGR_LOGIN(EMP_ID NUMBER) IS
ID_USER VARCHAR(20);
HASIL_GEN_PASS VARCHAR(20);
BEGIN
IF F_CHK_MGR_ID(EMP_ID) = 1 THEN
SELECT EMPLOYEE_ID||SUBSTR(FIRST_NAME, 1, 1)||SUBSTR(LAST_NAME, 1, 1), F_GEN_PASS_MGR(EMPLOYEE_ID)
INTO ID_USER, HASIL_GEN_PASS
FROM EMPLOYEES WHERE EMPLOYEE_ID = EMP_ID;

INSERT INTO LOGINMANAGER VALUES (ID_USER, HASIL_GEN_PASS);

DBMS_OUTPUT.PUT_LINE('DATA TELAH TERSIMPAN');
ELSE
RAISE_APPLICATION_ERROR(-20020,'EMPLOYEE_ID BUKAN MILIK MANAGER');
END IF;
END;

END PKG_HRD;

--TEST
SET SERVEROUTPUT ON;

EXEC PKG_HRD.P_INS_MGR_LOGIN('100');
EXEC PKG_HRD.P_INS_MGR_LOGIN('173');
EXEC PKG_HRD.P_INS_MGR_LOGIN('201');