CREATE OR REPLACE PROCEDURE AT_CREATE_OFFER_CO1(P_IDENT            IN PERS.VI_PERSON.IDENT%TYPE,
                                                P_NAME2            IN PERS.VI_PERSON.NAME2%TYPE,
                                                P_CAMPAIGN_SYBTYPE IN CAMPAIGN.PRODUCT_OFFER.CAMPAIGN_SUBTYPE%TYPE,
                                                P_REPAID_CONTRACT  IN CAMPAIGN.VI_INDIVIDUAL_OFFER.REPAID_CONTRACT%TYPE,
                                                P_PRODUCT_SETS     IN VARCHAR2,
                                                P_LIMIT            IN campaign.vi_individual_offer.limit%TYPE,
                                                P_ANNUITY_LIMIT    IN campaign.vi_individual_offer.annuity_limit%TYPE,
                                                P_RESPONSE         OUT VARCHAR2) AS

  v_ident            pers.vi_person.ident%TYPE := P_IDENT; --ident = Id card number
  v_name             pers.vi_person.name2%TYPE := P_NAME2; --name of client
  v_offer_valid_from campaign.vi_individual_offer.valid_from%TYPE := TO_DATE('16.08.2017',
                                                                             'DD.MM.YYYY'); --offer valid from
  v_offer_valid_to   campaign.vi_individual_offer.valid_to%TYPE := TO_DATE('5.11.2099',
                                                                           'DD.MM.YYYY'); --offer valid to
  v_product_sets     VARCHAR2(4000) := P_PRODUCT_SETS; --'6086|6146|6149'; --product set definition = See Product series screen in Approval process >> system administration part of VB6 Homer
  v_limit            campaign.vi_individual_offer.limit%TYPE := P_LIMIT; --limit = Maximal credit amount of loan
  v_annuity_limit    campaign.vi_individual_offer.annuity_limit%TYPE := P_ANNUITY_LIMIT; --annuity limit = Maximal credit amount of single installment
  v_campaign_sybtype campaign.product_offer.campaign_subtype%TYPE := P_CAMPAIGN_SYBTYPE; --campaign subtype = use value from column REG_VAL_CODE from SELECT * FROM param.registers WHERE reg_number = 304;

  v_product_offer_param campaign.t_sasadp_offer_rec;
  v_offer_limit_tab     campaign.t_sasadp_indiv_offer_tab := campaign.t_sasadp_indiv_offer_tab();
  v_id_person           pers.person.id%TYPE;
  v_id_emp              campaign.product_offer.id_emp%TYPE := 1;

  v_selection_score campaign.vi_individual_offer.selection_score%TYPE := 123.123456; --attributes counted by DWH sent through SAS
  v_risk_grade      campaign.vi_individual_offer.risk_grade%TYPE := 'risk_grade'; --attributes counted by DWH sent through SAS
  v_pilot_name      campaign.vi_individual_offer.pilot_name%TYPE := 'pilot_name'; --attributes counted by DWH sent through SAS
  v_minimal_limit   campaign.vi_individual_offer.minimal_limit%TYPE := 2000;
  v_repaid_contract campaign.vi_individual_offer.repaid_contract%TYPE := P_REPAID_CONTRACT; --evid_srv of repaid contract for SC003 (TOP-UP)
BEGIN
  BEGIN
    --zjisteni ID_PERSON
    SELECT p.id
      INTO v_id_person
      FROM pers.vi_person p
     WHERE p.status = 'a'
       AND p.ident = v_ident
       AND p.name2 = v_name;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      --DBMS_OUTPUT.PUT_LINE('Client not found');
      P_RESPONSE := 'Client not found';
    WHEN TOO_MANY_ROWS THEN
      --DBMS_OUTPUT.PUT_LINE('Two or more clients found');
      P_RESPONSE := 'Two or more clients found';
  END;

  IF v_id_person IS NOT NULL THEN

    v_product_offer_param := campaign.t_sasadp_offer_rec(NULL,
                                                         NULL,
                                                         NULL,
                                                         NULL,
                                                         NULL,
                                                         NULL,
                                                         NULL,
                                                         NULL,
                                                         NULL,
                                                         NULL,
                                                         supp.ifc_supp.get_sys_date());

    v_product_offer_param.campaign_subtype       := v_campaign_sybtype;
    v_product_offer_param.start_date             := v_offer_valid_from;
    v_product_offer_param.end_date               := v_offer_valid_to;
    v_product_offer_param.start_date_rel         := 0;
    v_product_offer_param.end_date_rel           := 0;
    v_product_offer_param.product_sets           := v_product_sets;
    v_product_offer_param.response_tracking_code := -1 *
                                                    (campaign.sq_product_offer.NEXTVAL);

    v_offer_limit_tab.extend(1);
      v_offer_limit_tab(1) := campaign.t_sasadp_indiv_offer_rec(v_id_person,        -- id_person
                                                                v_limit,            -- loan_limit
                                                                v_annuity_limit,    -- annuity_limit
                                                                v_selection_score,  -- selection_score
                                                                v_risk_grade,       -- risk_grade
                                                                v_pilot_name,       -- pilot_name
                                                                v_minimal_limit,    -- minimal_limit
                                                                v_repaid_contract,  -- repaid_contract
                                                                NULL,               -- id_individual_offer
                                                                NULL,               -- repeated_offer_count
                                                                NULL,               -- repeated_premium_offer_count
                                                                NULL,               -- duration_pre_offer
                                                                NULL,               -- duration_pre_premium_offer
                                                                NULL,               -- text_xell_risk_pilot
                                                                NULL,                  -- cnt_term_max
                                                                NULL,             -- rank
                                                                NULL,               -- real_limit
                                                                NULL,               -- add_number_info1
                                                                NULL,               -- add_text_info1
                                                                NULL,               -- add_text_info2
                                                                NULL,               -- homer_status
                                                                NULL,               -- err_msg
                                                                NULL,               -- security_auth_needed
                                                                NULL,              -- personal_info_update_needed
                                                                NULL);              -- cnt_term_min

    campaign.pkg_sasadp_offer.create_product_offer(p_product_offer_param => v_product_offer_param,
                                                   p_offer_limit_tab     => v_offer_limit_tab,
                                                   p_id_emp              => v_id_emp);

    --DBMS_OUTPUT.PUT_LINE(V_OFFER_LIMIT_TAB(1).HOMER_STATUS);
    --DBMS_OUTPUT.PUT_LINE(V_OFFER_LIMIT_TAB(1).ERR_MSG);
    P_RESPONSE := 'STATUS: ' || V_OFFER_LIMIT_TAB(1).HOMER_STATUS ||
                  ', ERROR: ' || V_OFFER_LIMIT_TAB(1).ERR_MSG;

  END IF;
END;