CREATE OR REPLACE PACKAGE BODY PKG_SUBJECT_PAYMENTS
AS
gc_scope_prefix constant VARCHAR2(31) := LOWER($$PLSQL_UNIT) || '.';

PROCEDURE P_GET_NEW_SUBJECT_PAYMENTS

  AS
v_scope logger_logs.scope%TYPE := gc_scope_prefix || 'P_GET_NEW_SUBJECT_PAYMENTS';
v_zmienna NUMBER;
v_count NUMBER;
v_subject_id NUMBER;
v_id NUMBER;
v_subject_payments_row SUBJECT_PAYMENTS%ROWTYPE;
BEGIN

logger.log('START - wyszukanie nowych rekordów z platnościami', v_scope);
FOR v_acc IN ((SELECT DISTINCT
                    to_number(account_to_pay) atp FROM subjects
                    WHERE account_to_pay IS NOT NULL)
                )

loop
FOR v_trans IN (SELECT
                    vpd.ID,
                    vpd.CREATED_DATE,
                    vpd.MODIFIED_DATE,
                    vpd.TRACKING_ID,
                    vpd.TRACKING_ID_SERV,
                    vpd.ACCOUNT_NO,
                    vpd.ACTION_CODE,
                    vpd.ANNOTATION,
                    vpd.TRANS_AMOUNT,
                    vpd.TRANS_DATE,
                    vpd.DESCRIPTION_TEXT
                FROM V_PAYMENTS_DETAILS vpd
                WHERE ACCOUNT_NO  = v_acc.atp
                AND
                NOT EXISTS (SELECT 1 FROM subject_payments WHERE id_trans=vpd.id )
               )
 loop
    SELECT ID INTO v_subject_id FROM SUBJECTS WHERE ACCOUNT_NUMBER = to_char(v_trans.ACCOUNT_NO);

 v_subject_payments_row.SUBJECT_ID := v_subject_id;
 v_subject_payments_row.ID := SUBJECT_PAYMENTS_SEQ.NEXTVAL;
 v_subject_payments_row.ANONYMIZATION_STATUS := 0;
 v_subject_payments_row.ID_TRANS := v_trans.ID;
 v_subject_payments_row.CREATE_DATE := v_trans.CREATED_DATE;
 v_subject_payments_row.MODIFIED_DATE := v_trans.MODIFIED_DATE;
 v_subject_payments_row.TRACKING_ID := v_trans.TRACKING_ID;
 v_subject_payments_row.TRACKING_ID_SERV := v_trans.TRACKING_ID_SERV;
 v_subject_payments_row.ACCOUNT_NO := v_trans.ACCOUNT_NO;
 v_subject_payments_row.ACTION_CODE := v_trans.ACTION_CODE;
 v_subject_payments_row.ANNOTATION := v_trans.ANNOTATION;
 v_subject_payments_row.TRANS_AMOUNT := v_trans.TRANS_AMOUNT;
 v_subject_payments_row.TRANS_DATE :=v_trans.TRANS_DATE;
 v_subject_payments_row.DESCRIPTION_TEXT := v_trans.DESCRIPTION_TEXT;
 v_subject_payments_row.INSERT_DATE := sysdate;

 P_INSERT_SUBJECT_PAYMENTS(pi_subject_payments_row => v_subject_payments_row);
END LOOP;
END LOOP;
    logger.log('END - koniec wyszukania nowych rekordów z platnościami ', v_scope);

  exception
    WHEN others THEN
      logger.log_error('Wystąpił nieznany blad '||SQLERRM,V_scope);
      raise;
  END P_GET_NEW_SUBJECT_PAYMENTS;

    PROCEDURE P_INSERT_SUBJECT_PAYMENTS(pi_subject_payments_row EXTOUSR.SUBJECT_PAYMENTS%ROWTYPE)
    AS

    v_scope logger_logs.scope%TYPE := gc_scope_prefix || 'P_INSERT_SUBJECT_PAYMENTS';
    v_params logger.tab_param;
    v_subject_pay_row SUBJECT_PAYMENTS%ROWTYPE := pi_subject_payments_row;
    v_id NUMBER;
    v_subject_id NUMBER;

    BEGIN
    logger.append_param(v_params, 'parametr', pi_subject_payments_row.ID);
    logger.log('START - dodawanie rekordu o parametrach '||v_subject_pay_row.ID, v_scope, NULL, v_params);

    INSERT INTO SUBJECT_PAYMENTS VALUES v_subject_pay_row returning id INTO v_id;

      logger.log('Utworzono nowy rekord ',v_id,v_scope);
    exception
    WHEN others THEN
      logger.log_error('Wystąpił błąd podczas tworzenia rekordu podmiotu: '||SQLERRM,v_scope);
      raise;
    END P_INSERT_SUBJECT_PAYMENTS;


     PROCEDURE P_GET_SUB_PAYMENTS_TO_UPDATE
     AS
     v_scope logger_logs.scope%TYPE := gc_scope_prefix || 'P_GET_SUB_PAYMENTS_TO_UPDATE';
     v_start_date DATE;
     v_end_date DATE;
     v_cnt NUMBER;
     v_err_msg VARCHAR(1000);
     v_status varchar2(50);

    BEGIN
    SELECT  MAX(START_DATE) sd INTO v_start_date  FROM LOAD_PAYMENTS_HISTORY lph WHERE END_DATE IS NOT NULL;

logger.log('START - wyszukanie platnosci do update ', v_scope);
FOR v_payment IN (SELECT
                    sp.ID,
                    sp.MODIFIED_DATE,
                    sp.ACTION_CODE
                    FROM SUBJECT_PAYMENTS sp
                    JOIN V_PAYMENTS_DETAILS vpd
                    ON sp.ID_TRANS = vpd.ID
                    WHERE sp.MODIFIED_DATE > v_start_date
                    )
loop
P_UPDATE_SUBJECT_PAYMENTS(pi_id_trans => v_payment.ID,pi_modified_date=>v_payment.MODIFIED_DATE,pi_action_code => v_payment.ACTION_CODE);
END loop;
    logger.log('END - koniec wyszukania platnosciu do update ', v_scope);
exception
     WHEN others THEN
      logger.log_error('Wystąpił błąd podczas wyszukiwania platnosci do update '||SQLERRM,V_scope);
      v_status := 'Failed';
      v_err_msg := SQLERRM;
      UPDATE LOAD_PAYMENTS_HISTORY
        SET STATUS = v_status, ERROR_MESSAGES = v_err_msg
        WHERE END_DATE IS NULL;
      raise;
    END P_GET_SUB_PAYMENTS_TO_UPDATE;

    PROCEDURE P_UPDATE_SUBJECT_PAYMENTS(
    pi_id_trans EXTOUSR.SUBJECT_PAYMENTS.ID_TRANS%TYPE,
    pi_modified_date EXTOUSR.SUBJECT_PAYMENTS.MODIFIED_DATE%TYPE,
    pi_action_code EXTOUSR.SUBJECT_PAYMENTS.ACTION_CODE%TYPE
  )
  AS
  v_scope logger_logs.scope%TYPE := gc_scope_prefix || 'P_UPDATE_SUBJECT_PAYMENTS';
  v_params logger.tab_param;
  v_id_trans SUBJECT_PAYMENTS.ID_TRANS%TYPE := pi_id_trans;
  v_modified_date SUBJECT_PAYMENTS.MODIFIED_DATE%TYPE := pi_modified_date;
  v_action_code SUBJECT_PAYMENTS.ACTION_CODE%TYPE := pi_action_code;
  v_err_msg VARCHAR(1000);
  v_status varchar2(50);
BEGIN
    logger.append_param(v_params, 'parametr', v_id_trans);
    logger.append_param(v_params, 'parametr', v_modified_date);
    logger.append_param(v_params, 'parametr', v_action_code);

    logger.log('START - rozpoczęcie update ACTION_CODE dla transakcji: '||v_id_trans, v_scope, NULL, v_params);

    UPDATE SUBJECT_PAYMENTS
        SET ACTION_CODE = v_action_code, MODIFIED_DATE = v_modified_date
        WHERE ID = v_id_trans;
    logger.log('END - koniec update ACTION_CODE dla transakcji: '||v_id_trans, v_scope, NULL, v_params);
exception
  WHEN others THEN
    logger.log_error('Nieznany błąd: '||SQLERRM, v_scope, NULL, v_params);
      v_status := 'Failed';
      v_err_msg := SQLERRM;
      UPDATE LOAD_PAYMENTS_HISTORY
        SET STATUS = v_status, ERROR_MESSAGES = v_err_msg
        WHERE END_DATE IS NULL;
      raise;
    END P_UPDATE_SUBJECT_PAYMENTS;
----------------------------------------------------------------------------------------------------------------

    PROCEDURE P_GET_CASES_WITH_SENTENCE

    AS
     v_scope logger_logs.scope%TYPE := gc_scope_prefix || 'P_GET_CASES_WITH_SENTENCE';
     v_params logger.tab_param;
     v_subject_id SUBJECT_PAYMENTS.SUBJECT_ID%TYPE;
     v_tracking_id SUBJECT_PAYMENTS.TRACKING_ID%TYPE;
     v_tracking_id_serv SUBJECT_PAYMENTS.TRACKING_ID_SERV%TYPE;
     v_account_no SUBJECT_PAYMENTS.ACCOUNT_NO%TYPE;
     v_annotation SUBJECT_PAYMENTS.ANNOTATION%TYPE;
     v_history_row ANONYMIZATION_PAYMENTS_HISTORY%ROWTYPE;
     v_subject_payments_row SUBJECT_PAYMENTS%ROWTYPE;
     v_id NUMBER;
     v_status varchar2(20);

    BEGIN
    logger.log('START - wyszukanie podmiotu dla którego zapadl wyrok skazujacy i jego polatnosc znajduje sie w SUBJECT_PAYMENTS', v_scope);
FOR v_subject_s IN ((SELECT
                            sp.SUBJECT_ID,
                            sp.ID,
                            sp.TRACKING_ID,
                            sp.TRACKING_ID_SERV,
                            sp.ACCOUNT_NO,
                            sp.ANNOTATION
                            FROM SUBJECT_PAYMENTS sp
                            WHERE EXISTS
                            (SELECT 1 FROM SUBJECTS_CASES sc
                            WHERE SENTENCE = 1
                            AND sc.SUBJECT_ID = sp.SUBJECT_ID
                           )
                           AND ANONYMIZATION_STATUS <> 1
                           ))
loop
    v_history_row.ID := ANONYMIZATION_PAY_HISTORY_SEQ.NEXTVAL;
    v_history_row.ID_SUBJECT_PAYMENTS := v_subject_s.SUBJECT_ID;
    v_history_row.ID_TRANS := v_subject_s.ID;
    v_history_row.ACCOUNT_NO := v_subject_s.ACCOUNT_NO;
    v_history_row.OPERATION := 'ANONYMIZATION';
    v_history_row.OPERATION_DATE := sysdate;


    v_subject_id := v_subject_s.SUBJECT_ID;
    v_tracking_id := v_subject_s.ID;
    v_tracking_id_serv := v_subject_s.TRACKING_ID;
    v_account_no := v_subject_s.ACCOUNT_NO;
    logger.append_param(v_params, 'parametr', v_subject_id);
    logger.append_param(v_params, 'parametr', v_tracking_id);
    logger.append_param(v_params, 'parametr', v_tracking_id_serv);
    logger.append_param(v_params, 'parametr', v_account_no);

    logger.log('START - rozpoczęcie update ANONYMIZATION_STATUS dla trackind_id: '||v_tracking_id ||' tracking_id_serv: ' ||v_tracking_id_serv ||' account_no: '|| v_account_no || ' Subject_id: '||v_subject_id,  v_scope, NULL, v_params);


    --- WYWOLANIE P_SEND_REQUEST_ANONYMIZATION DO ANONIMIZACJI W BILLINGU PO WYSTAWIENIU INTERFEJSU
    P_SEND_REQUEST_ANONYMIZATION(pi_tracking_id => v_tracking_id, pi_tracking_id_serv => v_tracking_id_serv, pi_account_no => v_account_no);
   -- logger.log('END - koniec wyszukiwania podmiotu dla którego zapadl wyrok skazujacy i jego polatnosc znajduje sie w SUBJECT_PAYMENTS', v_scope);
    v_status := ANONYMIZATION_REQUEST_STATUS();
    IF v_status = 'Ok' THEN
    UPDATE SUBJECT_PAYMENTS SP
        SET ANONYMIZATION_STATUS = 1, ANONYMIZATION_DATE = sysdate
        WHERE SP.ID = v_subject_s.ID;
    logger.log('END - koniec update ANONYMIZATION_STATUS dla trackind_id: '||v_tracking_id ||' tracking_id_serv: ' ||v_tracking_id_serv ||' account_no: '|| v_account_no || ' Subject_id: '||v_subject_id, v_scope, NULL, v_params);


    INSERT INTO ANONYMIZATION_PAYMENTS_HISTORY VALUES v_history_row returning id INTO v_id;
          logger.log('Utworzono nowy rekord w tabeli ANONYMIZATION_PAYMENTS_HISTORY',v_id,v_scope);
--    else
    END IF;
    END loop;
     logger.log('END - koniec wyszukiwania podmiotu dla którego zapadl wyrok skazujacy i jego platnosc znajduje sie w SUBJECT_PAYMENTS', v_scope);

  exception
    WHEN others THEN
      logger.log_error('Wystąpił nieznany blad '||SQLERRM,V_scope);
      raise;
    END P_GET_CASES_WITH_SENTENCE;
    PROCEDURE P_GET_TRANS_TO_UNVEIL AS
      v_scope logger_logs.scope%TYPE := gc_scope_prefix || 'P_UPDATE_SUBJECT_PAYMENTS';
      v_params logger.tab_param;
     v_tracking_id SUBJECT_PAYMENTS.TRACKING_ID%TYPE;
     v_tracking_id_serv SUBJECT_PAYMENTS.TRACKING_ID_SERV%TYPE;
     v_account_no SUBJECT_PAYMENTS.ACCOUNT_NO%TYPE;
     v_annotation SUBJECT_PAYMENTS.ANNOTATION%TYPE;
     v_history_row ANONYMIZATION_PAYMENTS_HISTORY%ROWTYPE;
     v_subject_payments_row SUBJECT_PAYMENTS%ROWTYPE;
     v_id NUMBER;
     v_variable varchar2(20);
     v_subject_id NUMBER;

    BEGIN
        logger.log('START - wyszukanie podmiotu do cofnięcia anonimizacji', v_scope);
    FOR v_subject_u IN ((SELECT
                                sp.SUBJECT_ID,
                                sp.ID,
                                sp.TRACKING_ID,
                                sp.TRACKING_ID_SERV,
                                sp.ACCOUNT_NO,
                                sp.ANNOTATION
                                FROM SUBJECT_PAYMENTS sp
                                WHERE EXISTS
                                (SELECT 1 FROM SUBJECTS_CASES sc
                                WHERE
                            SENTENCE = 0
                            AND sc.SUBJECT_ID = sp.SUBJECT_ID
                           )
                           AND ANONYMIZATION_STATUS = 1))
    loop
    v_history_row.ID := ANONYMIZATION_PAY_HISTORY_SEQ.NEXTVAL;
    v_history_row.ID_SUBJECT_PAYMENTS := v_subject_u.SUBJECT_ID ;
    v_history_row.ID_TRANS := v_subject_u.ID;
    v_history_row.ACCOUNT_NO := v_subject_u.ACCOUNT_NO;
    v_history_row.OPERATION := 'UNVEIL';
    v_history_row.OPERATION_DATE := sysdate;

    v_subject_id := v_subject_u.SUBJECT_ID;
    v_tracking_id := v_subject_u.ID;
    v_tracking_id_serv := v_subject_u.TRACKING_ID;
    v_account_no := v_subject_u.ACCOUNT_NO;
    v_annotation := v_subject_u.ANNOTATION;
    logger.append_param(v_params, 'parametr', v_tracking_id);
    logger.append_param(v_params, 'parametr', v_tracking_id_serv);
    logger.append_param(v_params, 'parametr', v_account_no);
    logger.append_param(v_params, 'parametr', v_annotation);

     logger.log('START - rozpoczęcie update ANONYMIZATION_STATUS dla trackind_id: '||v_tracking_id ||' tracking_id_serv: ' ||v_tracking_id_serv ||' account_no: '|| v_account_no || ' Subject_id: '||v_subject_id,  v_scope, NULL, v_params);

   P_SEND_REQUEST_UNVEIL (pi_tracking_id => v_tracking_id, pi_tracking_id_serv => v_tracking_id_serv, pi_account_no => v_account_no, pi_annotation => v_annotation );
    v_variable := UNVEIL_REQUEST_STATUS();
    IF v_variable = 'Ok' THEN
    UPDATE SUBJECT_PAYMENTS SP
        SET ANONYMIZATION_STATUS = 0, ANONYMIZATION_DATE = sysdate
        WHERE SP.ID = v_subject_u.ID;

 logger.log('END koniec update ANONYMIZATION_STATUS dla trackind_id: '||v_tracking_id ||' tracking_id_serv: ' ||v_tracking_id_serv ||' account_no: '|| v_account_no || ' Subject_id: '||v_subject_id,  v_scope, NULL, v_params);
    INSERT INTO ANONYMIZATION_PAYMENTS_HISTORY VALUES v_history_row returning id INTO v_id;
    logger.log('Utworzono nowy rekord w tabeli ANONYMIZATION_PAYMENTS_HISTORY',v_id,v_scope);
    END IF;
    END loop;
      exception
    WHEN others THEN
      logger.log_error('Wystąpił nieznany blad '||SQLERRM,V_scope);
      raise;
    END P_GET_TRANS_TO_UNVEIL;
    PROCEDURE P_SEND_REQUEST_ANONYMIZATION(
    pi_tracking_id EXTOUSR.SUBJECT_PAYMENTS.TRACKING_ID%TYPE,
    pi_tracking_id_serv EXTOUSR.SUBJECT_PAYMENTS.TRACKING_ID_SERV%TYPE,
    pi_account_no EXTOUSR.SUBJECT_PAYMENTS.ACCOUNT_NO%TYPE)
    AS
    BEGIN
    NULL;
    END P_SEND_REQUEST_ANONYMIZATION;

    PROCEDURE P_SEND_REQUEST_UNVEIL(
     pi_tracking_id EXTOUSR.SUBJECT_PAYMENTS.TRACKING_ID%TYPE,
     pi_tracking_id_serv EXTOUSR.SUBJECT_PAYMENTS.TRACKING_ID_SERV%TYPE,
     pi_account_no EXTOUSR.SUBJECT_PAYMENTS.ACCOUNT_NO%TYPE,
     pi_annotation EXTOUSR.SUBJECT_PAYMENTS.ANNOTATION%TYPE)
     AS
     BEGIN
     NULL;
     END P_SEND_REQUEST_UNVEIL;


    FUNCTION ANONYMIZATION_REQUEST_STATUS RETURN varchar2
    IS
    v_status BOOLEAN;
    v_return varchar2(10);
    BEGIN

    v_return := 'Ok';
    RETURN v_return;
    END ANONYMIZATION_REQUEST_STATUS;


    FUNCTION UNVEIL_REQUEST_STATUS RETURN varchar2
    IS
    v_status BOOLEAN;
    v_return varchar2(10);
    BEGIN

    v_return := 'Ok';

    RETURN v_return;
    END UNVEIL_REQUEST_STATUS;

    PROCEDURE P_LOAD_PAYMENTS_MAIN
      AS
    v_scope logger_logs.scope%TYPE := gc_scope_prefix || 'P_GET_NEW_SUBJECT_PAYMENTS';
    v_load_payments_history_row LOAD_PAYMENTS_HISTORY%ROWTYPE;
    v_start_date DATE;
    v_end_date DATE;
    v_id NUMBER;
    v_status varchar2(50);
    v_err_msg varchar2(1000);
    BEGIN
    v_err_msg := SQLERRM;
    SELECT sysdate INTO v_start_date FROM dual;
    v_load_payments_history_row.ID := LOAD_PAYMENTS_HISTORY_SEQ.NEXTVAL;
    v_load_payments_history_row.STATUS := 'IN PROGRESS';
    v_load_payments_history_row.START_DATE := v_start_date;
    v_load_payments_history_row.ERROR_MESSAGES := v_err_msg;
    INSERT INTO LOAD_PAYMENTS_HISTORY VALUES v_load_payments_history_row returning id INTO v_id;
    P_GET_SUB_PAYMENTS_TO_UPDATE;
    commit;
    P_GET_NEW_SUBJECT_PAYMENTS;
    commit;
    SELECT sysdate INTO v_end_date FROM dual;
    v_status := 'OK';
    v_err_msg := SQLERRM;
    UPDATE LOAD_PAYMENTS_HISTORY
        SET END_DATE = v_end_date, STATUS = v_status, ERROR_MESSAGES = v_err_msg
        WHERE END_DATE IS NULL;
    exception
    WHEN others THEN
      logger.log_error('Wystąpił nieznany blad '||SQLERRM,V_scope);
      v_status := 'FAILED';
      v_err_msg := SQLERRM;
      UPDATE LOAD_PAYMENTS_HISTORY
        SET END_DATE = v_end_date, STATUS = v_status, ERROR_MESSAGES = v_err_msg
        WHERE END_DATE IS NULL;
      raise;
    END P_LOAD_PAYMENTS_MAIN;

    PROCEDURE P_ANONYM_PAYMENTS_IN_BILLING AS
    BEGIN
    P_GET_CASES_WITH_SENTENCE;
    END P_ANONYM_PAYMENTS_IN_BILLING;


    PROCEDURE P_UNVEIL_TRANS_IN_BILLING AS
    BEGIN
    P_GET_TRANS_TO_UNVEIL;
    END P_UNVEIL_TRANS_IN_BILLING;
    END PKG_SUBJECT_PAYMENTS;