CREATE OR REPLACE
PACKAGE P_PACKAGE AS
PROCEDURE ACTION(П_СОТРУДНИК_ИД IN NUMBER,
                        П_ОТДЕЛ_ИД IN NUMBER,
                        П_ДОЛЖНОСТЬ_ИД IN NUMBER,
                        П_СОСТОЯНИЕ_ИД IN NUMBER,
                        П_ДАТА IN DATE, 
                        П_КОММЕНТАРИЙ IN LONG); 
                        PROCEDURE HIRING;
                        PROCEDURE TRANSFER_POST;
                        FUNCTION GET_COUNT_BY_DATE(П_ОТДЕЛ_ИД IN NUMBER, П_ДАТА IN DATE) RETURN NUMBER;
END P_PACKAGE;
/

CREATE OR REPLACE PACKAGE BODY P_PACKAGE AS

  PROCEDURE ACTION(П_СОТРУДНИК_ИД IN NUMBER, 
                        П_ОТДЕЛ_ИД IN NUMBER,
                        П_ДОЛЖНОСТЬ_ИД IN NUMBER,
                        П_СОСТОЯНИЕ_ИД IN NUMBER,
                        П_ДАТА IN DATE,
                        П_КОММЕНТАРИЙ IN LONG)

AS                    
С_ПРИНЯТ_ИД NUMBER;
С_ПЕРЕВЕДЕН_ОТД_ИД NUMBER;
С_ПЕРЕВЕДЕН_ДОЛЖН_ИД NUMBER;

АВТО_К_ПЕРЕВЕДЕН_ОТД LONG;
АВТО_К_ПЕРЕВЕДЕН_ДОЛЖН LONG;
  
BEGIN
  С_ПРИНЯТ_ИД:=1;
  С_ПЕРЕВЕДЕН_ОТД_ИД:=2;
  С_ПЕРЕВЕДЕН_ДОЛЖН_ИД:=3;
  
  АВТО_К_ПЕРЕВЕДЕН_ОТД:='Авто: перевод в отдел. ' || П_КОММЕНТАРИЙ;
  АВТО_К_ПЕРЕВЕДЕН_ДОЛЖН:='Авто: перевод на должность. ' || П_КОММЕНТАРИЙ;

  INSERT INTO T_HISTORY (ЛЮДИ_ИД, ОТДЕЛ_ИД, ДОЛЖН_ИД, СОСТ_ИД, ДАТА, КОММЕНТАРИЙ)
          values (П_СОТРУДНИК_ИД, П_ОТДЕЛ_ИД, П_ДОЛЖНОСТЬ_ИД, П_СОСТОЯНИЕ_ИД, П_ДАТА, П_КОММЕНТАРИЙ);
  
  IF (П_СОСТОЯНИЕ_ИД = С_ПРИНЯТ_ИД)
      THEN
          INSERT INTO T_HISTORY (ЛЮДИ_ИД, ОТДЕЛ_ИД, ДОЛЖН_ИД, СОСТ_ИД, ДАТА, КОММЕНТАРИЙ)
            values (П_СОТРУДНИК_ИД, П_ОТДЕЛ_ИД, П_ДОЛЖНОСТЬ_ИД, С_ПЕРЕВЕДЕН_ОТД_ИД, П_ДАТА, АВТО_К_ПЕРЕВЕДЕН_ОТД);
          INSERT INTO T_HISTORY (ЛЮДИ_ИД, ОТДЕЛ_ИД, ДОЛЖН_ИД, СОСТ_ИД, ДАТА, КОММЕНТАРИЙ)
            values (П_СОТРУДНИК_ИД, П_ОТДЕЛ_ИД, П_ДОЛЖНОСТЬ_ИД, С_ПЕРЕВЕДЕН_ДОЛЖН_ИД, П_ДАТА, АВТО_К_ПЕРЕВЕДЕН_ДОЛЖН);                        
  END IF;    
END ACTION;


  PROCEDURE HIRING AS
  BEGIN
    ACTION(1, 1, 1, 1, TO_DATE('01.01.14', 'dd.mm.yy'), 'Создание компании');
    ACTION(2, 1, 2, 1, TO_DATE('01.01.14', 'dd.mm.yy'), 'Создание компании');
    ACTION(11, 2, 15, 1,  TO_DATE('02.01.14', 'dd.mm.yy'), 'Новый мастер боевых исскусств');
    ACTION(16, 2, 14, 1,  TO_DATE('02.01.14', 'dd.mm.yy'), 'Новый мастер по по укладке плитки');
    ACTION(6, 3, 12, 1,  TO_DATE('05.01.14', 'dd.mm.yy'), 'Новый водитель');
    ACTION(10, 4, 6, 1,  TO_DATE('04.01.14', 'dd.mm.yy'), 'Новый главный охранник');
    ACTION(12, 4, 4, 1,  TO_DATE('06.01.14', 'dd.mm.yy'), 'Новый охранник');
    ACTION(13, 4, 4, 1,  TO_DATE('12.01.14', 'dd.mm.yy'), 'Новый охранник');
    ACTION(3, 6, 5, 1,  TO_DATE('12.01.14', 'dd.mm.yy'), 'Новый тестировщик');
    ACTION(4, 6, 5, 1,  TO_DATE('24.01.14', 'dd.mm.yy'), 'Новый тестировщик');
    ACTION(5, 6, 5, 1,  TO_DATE('02.02.14', 'dd.mm.yy'), 'Новый тестировщик');                                  
  END HIRING;
  
  PROCEDURE TRANSFER_POST AS
  BEGIN
  /* Тестировщик -> Охранник */
    ACTION(3,4,4,3, TO_DATE('26.03.14', 'dd.mm.yy'), 'Разжалован в должность охранника');
    ACTION(3,4,4,2, TO_DATE('26.03.14', 'dd.mm.yy'), 'После разжалования переведен в отдел охранников');
    /* Увольнение */
    ACTION(13, 10, 4, 4,  TO_DATE('16.08.14', 'dd.mm.yy'), 'Украл всю плитку, уволен!');
    /* Мастер из мастеров в отдел Штукатуров-моляров*/
    ACTION(16,7,7,3, TO_DATE('01.04.14', 'dd.mm.yy'), 'Плитки пока нет, пусть красит стены');
  END;
  
FUNCTION GET_COUNT_BY_DATE(П_ОТДЕЛ_ИД IN NUMBER, П_ДАТА IN DATE) RETURN NUMBER
AS
people_count number;
department_id number;

  BEGIN
    people_count:=0;
    FOR rec IN (SELECT * FROM 
                          (SELECT ИД AS p_id, ДАТА AS p_date, ROW_NUMBER() OVER (PARTITION BY ЛЮДИ_ИД ORDER BY ДАТА DESC) AS rownumber
                                 FROM T_HISTORY WHERE ДАТА <= П_ДАТА)
                        WHERE rownumber = 1)
    LOOP        
      SELECT ОТДЕЛ_ИД into department_id FROM T_HISTORY WHERE T_HISTORY.ИД = rec.p_id;                 
      IF (department_id = П_ОТДЕЛ_ИД)
        THEN
          people_count := people_count + 1;
      END IF;    
    END LOOP;  
    RETURN people_count; 
  END;

END P_PACKAGE;

--SELECT COUNT(DISTINCT ЛЮДИ_ИД) IN people_count
--FROM HISTORY
--WHERE ДАТА<=