create or replace TRIGGER "TRG_SYNC_POSITION" 
   AFTER INSERT OR UPDATE OR DELETE
   ON HRIS_POSITIONS    REFERENCING NEW AS NEW OLD AS OLD
   FOR EACH ROW
DECLARE
   V_DELETED_FLAG   CHAR (1 BYTE);
   V_COUNT          NUMBER;
   V_COMPANY_CODE   VARCHAR2(30 BYTE);
BEGIN
    BEGIN
        SELECT COUNT (*) INTO V_COUNT FROM HR_GRADE_CODE
        WHERE GRADE_CODE = TO_CHAR(:OLD.POSITION_ID,'FM000');
    EXCEPTION
        WHEN OTHERS THEN
            V_COUNT := 0;
    END;

    V_COMPANY_CODE := '01';

    IF :NEW.STATUS = 'E' THEN
        V_DELETED_FLAG := 'N';
    ELSE
        V_DELETED_FLAG := 'Y';
    END IF;
    IF V_COUNT = 0 THEN
        FOR I IN (SELECT COMPANY_CODE FROM COMPANY_SETUP) LOOP
            INSERT INTO HR_GRADE_CODE
            (GRADE_CODE,
            GRADE_EDESC, GRADE_NDESC, GRADE_INDEX_NO, BASIC_SALARY_MIN, BASIC_SALARY_MAX,
            EARNING_BASIS, ADVANCE_LIMIT, ACC_CODE, EREMARKS, NREMARKS,
            COMPANY_CODE, BRANCH_CODE, CREATED_BY, CREATED_DATE, DELETED_FLAG,
            SYN_ROWID, MODIFY_DATE, MODIFY_BY)
            VALUES
            ( TO_CHAR (:NEW.POSITION_ID,'FM000'), :NEW.POSITION_NAME,  :NEW.POSITION_NAME, :NEW.LEVEL_NO, 0,
            1000000, 'Monthly', 0, NULL, NULL, NULL, I.COMPANY_CODE, I.COMPANY_CODE||'.01', 'ADMIN', SYSDATE,
            'N', NULL, NULL, NULL);
        END LOOP;

   ELSIF V_COUNT >= 1THEN
        UPDATE HR_GRADE_CODE
        SET GRADE_EDESC = :NEW.POSITION_NAME,
        GRADE_INDEX_NO = :NEW.LEVEL_NO,
        DELETED_FLAG = V_DELETED_FLAG
        WHERE GRADE_CODE = TO_CHAR (:OLD.POSITION_ID,'FM000');
   END IF;
END;