create or replace package hl_con_contract_content_pkg is
  PROCEDURE contract_content_create(p_contract_id   NUMBER,
                                    p_content_id    out number,
                                    p_user_id       NUMBER,
                                    p_templet_usage VARCHAR2 DEFAULT NULL,
                                    p_content_type  VARCHAR2 DEFAULT NULL);

end hl_con_contract_content_pkg;
/
create or replace package body hl_con_contract_content_pkg is
  g_check_yes CONSTANT VARCHAR2(1) := 'Y';
  g_check_no  CONSTANT VARCHAR2(1) := 'N';
  PROCEDURE con_contract_check(p_contract_id NUMBER,
                               p_user_id     NUMBER,
                               p_con_rec     OUT con_contract%ROWTYPE) IS
    e_con_status_error EXCEPTION;
  BEGIN
    p_con_rec := con_contract_pkg.get_contract_rec(p_contract_id => p_contract_id,
                                                   p_user_id     => p_user_id);
    /*if p_con_rec.contract_status not in
       ('NEW', 'SIGN', 'INCEPT', 'PAID', 'APPROVING') then
      raise e_con_status_error;
    end if;*/
  EXCEPTION
    WHEN e_con_status_error THEN
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'CON_CONTRACT_CONTENT_PKG.CON_STATUS_ERROR',
                                                      p_created_by              => p_user_id,
                                                      p_package_name            => 'con_contract_content_pkg',
                                                      p_procedure_function_name => 'con_contract_check');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
  END;
  --删除合同伙伴已被删除的打印文本
  PROCEDURE delete_when_create_check(p_contract_id NUMBER,
                                     p_user_id     NUMBER) IS
    v_cdd_list_id number;
  BEGIN
  
    DELETE FROM con_contract_content t WHERE t.contract_id = p_contract_id;
  END;

  FUNCTION contract_tmpt_check(p_contract_id  NUMBER,
                               p_tmpt_id      NUMBER,
                               p_user_id      NUMBER,
                               p_bp_type      VARCHAR2,
                               p_bp_class     VARCHAR2,
                               p_bp_id        NUMBER,
                               p_content_type VARCHAR2 DEFAULT NULL)
    RETURN VARCHAR2 IS
    v_count        NUMBER;
    v_exist        NUMBER;
    v_nationality  VARCHAR2(30); --国籍
    v_country_code VARCHAR2(30);
    v_data_class   VARCHAR2(30);
  BEGIN
    SELECT COUNT(*)
      INTO v_count
      FROM con_contract_tmpt_clause c
     WHERE c.tmpt_id = p_tmpt_id;
    IF v_count = 0 THEN
      RETURN g_check_yes;
    END IF;
  
    /* BEGIN
      SELECT nvl(fc.country_code, 'CHN')
        INTO v_country_code
        FROM hls_bp_master bm, fnd_country fc
       WHERE bm.nationality = fc.country_id(+)
         AND bm.bp_id = p_bp_id;
    EXCEPTION
      WHEN OTHERS THEN
        v_country_code := 'CHN';
    END;
    
    IF v_country_code = 'CHN' THEN
      v_nationality := 'inland';
    ELSE
      v_nationality := 'foreign';
    END IF;*/
  
    SELECT cc.data_class
      INTO v_data_class
      FROM con_contract cc
     WHERE cc.contract_id = p_contract_id;
  
    FOR cv IN (SELECT *
                 FROM con_contract_tmpt_clause c
                WHERE c.tmpt_id = p_tmpt_id) LOOP
      IF v_data_class = 'NORMAL' THEN
        SELECT COUNT(*)
          INTO v_exist
          FROM con_contract          ct,
               con_contract_mortgage ccm,
               con_contract_bp       ccb --add by zhangyu 2015-07-20
         WHERE ct.contract_id = p_contract_id
              --add by zhangyu 2015-07-20
           and ct.contract_id = ccb.contract_id
           and ccm.contract_id(+) = ccb.contract_id
           and ccm.bp_id_mortgagor(+) = ccb.bp_id
           and ccb.bp_type = p_bp_type
           and ccb.bp_class = p_bp_class
           and ccb.bp_id = p_bp_id
              --end
           AND ct.data_class = 'NORMAL'
           AND ct.business_type = nvl(cv.business_type, ct.business_type) --业务类型
        /* AND ct.contract_id = nvl(cv.contract_id, ct.contract_id) --合同
        AND ct.lease_organization =
            nvl(cv.lease_organization, ct.lease_organization) --事业部
        AND ct.lease_channel = nvl(cv.lease_channel, ct.lease_channel) --商业模式
        AND ct.document_type = nvl(cv.document_type, ct.document_type) --单据类型
        AND nvl(ct.price_list, '##') =
            nvl(cv.price_list, nvl(ct.price_list, '##')) --报价方案
        AND ct.division = nvl(cv.division, ct.division) --产品线
        AND p_bp_type = nvl(cv.bp_category, p_bp_type)
        AND p_bp_class = nvl(cv.bp_class, p_bp_class)
        AND v_nationality = nvl(cv.other_tmpt_clause, v_nationality)
        AND nvl(p_content_type, 'N') =
            nvl(cv.content_type, nvl(p_content_type, 'N'))
        AND nvl(cv.data_class, 'NORMAL') = ct.data_class
           --add by zhangyu 2015-07-20
        and nvl(ccm.mortgage_asset_detail, 'N') =
            nvl(cv.mortgage_asset_detail,
                nvl(ccm.mortgage_asset_detail, 'N'))
        and decode(ct.down_payment, '0', 'N', null, 'N', 'Y') =
            nvl(cv.down_payment_flag,
                decode(ct.down_payment, '0', 'N', null, 'N', 'Y')) -- end */
        /*AND ct.OTHER_TMPT_CLAUSE =
        nvl(cv.other_tmpt_clause, ct.OTHER_TMPT_CLAUSE)*/
        ;
      ELSE
        SELECT COUNT(*)
          INTO v_exist
          FROM con_contract ct, con_contract_change_req cr
         WHERE ct.contract_id = p_contract_id
              --AND ct.data_class = 'NORMAL'
           AND ct.business_type = nvl(cv.business_type, ct.business_type) --业务类型
           AND ct.contract_id = nvl(cv.contract_id, ct.contract_id) --合同
           AND ct.lease_organization =
               nvl(cv.lease_organization, ct.lease_organization) --事业部
           AND ct.lease_channel = nvl(cv.lease_channel, ct.lease_channel) --商业模式
           AND ct.document_type = nvl(cv.document_type, ct.document_type) --单据类型
           AND ct.price_list = nvl(cv.price_list, ct.price_list) --报价方案
           AND ct.division = nvl(cv.division, ct.division) --产品线
           AND p_bp_type = nvl(cv.bp_category, p_bp_type)
           AND p_bp_class = nvl(cv.bp_class, p_bp_class)
           AND v_nationality = nvl(cv.other_tmpt_clause, v_nationality)
           AND nvl(p_content_type, 'N') =
               nvl(cv.content_type, nvl(p_content_type, 'N'))
           AND nvl(cv.data_class, 'NORMAL') = ct.data_class
           AND cr.change_req_id = ct.contract_id
           AND (cr.reschedule_flag = nvl(cv.reschedule_flag, 'A') OR
               cr.et_flag = nvl(cv.et_flag, 'A') OR
               cr.tenant_change_flag = nvl(cv.tenant_change_flag, 'A') OR
               cr.bank_acc_change_flag = nvl(cv.bank_acc_change_flag, 'A') OR
               cr.lease_item_change_flag =
               nvl(cv.lease_item_change_flag, 'A') OR
               cr.mortgage_change_flag = nvl(cv.mortgage_change_flag, 'A') OR
               cr.guarantor_change_flag =
               nvl(cv.guarantor_change_flag, 'A') OR
               cr.contract_cancel_flag = nvl(cv.contract_cancel_flag, 'A') OR
               cr.cashflow_change_flag = nvl(cv.cashflow_change_flag, 'A'))
        /*AND ct.OTHER_TMPT_CLAUSE =
        nvl(cv.other_tmpt_clause, ct.OTHER_TMPT_CLAUSE)*/
        ;
      
      END IF;
    
      IF v_exist = 0 THEN
        NULL;
        --RETURN g_check_no;
      ELSE
        RETURN g_check_yes;
      END IF;
    END LOOP;
    RETURN g_check_no;
  END contract_tmpt_check;
  PROCEDURE insert_contract_content(p_content_id         OUT con_contract_content.content_id%TYPE,
                                    p_contract_id        con_contract_content.contract_id%TYPE,
                                    p_content_number     con_contract_content.content_number%TYPE,
                                    p_clause_usage       con_contract_content.clause_usage%TYPE,
                                    p_templet_id         con_contract_content.templet_id%TYPE,
                                    p_con_contract_bp_id con_contract_content.con_contract_bp_id%TYPE,
                                    p_mortgage_id        con_contract_content.mortgage_id%TYPE,
                                    p_content_print_flag con_contract_content.content_print_flag%TYPE,
                                    p_available_flag     con_contract_content.available_flag%TYPE,
                                    p_user_id            con_contract_content.created_by%TYPE,
                                    p_bp_id              number default null) IS
  BEGIN
  
    SELECT con_contract_content_s.nextval INTO p_content_id FROM dual;
    -- p_content_id := con_contract_content_s.nextval;
  
    INSERT INTO con_contract_content
      (content_id,
       contract_id,
       content_number,
       clause_usage,
       templet_id,
       con_contract_bp_id,
       mortgage_id,
       content_print_flag,
       available_flag,
       creation_date,
       created_by,
       last_update_date,
       last_updated_by)
    VALUES
      (p_content_id,
       p_contract_id,
       p_content_number,
       p_clause_usage,
       p_templet_id,
       p_con_contract_bp_id,
       p_mortgage_id,
       p_content_print_flag,
       p_available_flag,
       SYSDATE,
       p_user_id,
       SYSDATE,
       p_user_id);
  
  END;
  PROCEDURE contract_content_create(p_contract_id   NUMBER,
                                    p_content_id    out number,
                                    p_user_id       NUMBER,
                                    p_templet_usage VARCHAR2 DEFAULT NULL,
                                    p_content_type  VARCHAR2 DEFAULT NULL) IS
    v_content_id    NUMBER;
    v_record_id     NUMBER;
    v_clause_usage  con_contract_content.clause_usage%TYPE;
    v_business_type con_contract.business_type%TYPE;
    r_prj_rec       prj_project%ROWTYPE;
    r_con_rec       con_contract%ROWTYPE;
    e_clause_usage_null_error EXCEPTION;
  
    v_contract_bp_id     NUMBER;
    v_tmpt_count         NUMBER;
    v_contract_number    VARCHAR2(64);
    v_lease_channel      VARCHAR2(24);
    v_cdd_list_id        NUMBER;
    v_exists             NUMBER;
    v_count_normal       NUMBER;
    v_source_con_id      NUMBER;
    v_need_rec_0         NUMBER;
    r_con_clause_templet con_clause_templet%ROWTYPE;
  
  BEGIN
    con_contract_check(p_contract_id => p_contract_id,
                       p_user_id     => p_user_id,
                       p_con_rec     => r_con_rec);
  
    /* r_prj_rec := prj_project_pkg.project_get_record(p_project_id => r_con_rec.project_id,
    p_user_id    => p_user_id);*/
    ----删除合同伙伴已被删除的打印文本
    delete_when_create_check(p_contract_id => r_con_rec.contract_id,
                             p_user_id     => p_user_id);
   FOR cv IN (SELECT *
                 FROM con_clause_templet t
                WHERE t.enabled_flag = 'Y'
                and t.templet_usage =
                      nvl(p_templet_usage, t.templet_usage)--FOR TEST 20170628 BOBO
               ) LOOP
      FOR c_cps IN (SELECT cb.record_id,
                           ct.contract_number,
                           cb.bp_id,
                           cb.bp_category, --modify by zhangyu 2015-08-20
                           cb.bp_type,
                           cb.bp_class
                      FROM con_contract ct, con_contract_bp cb --,
                    --hls_bp_master   bm
                     WHERE ct.contract_id = p_contract_id
                       AND cb.contract_id = ct.contract_id
                      and cb.bp_category = 'TENANT'
                    --AND cb.bp_id = bm.bp_id(+)
                    ) LOOP
        IF contract_tmpt_check(p_contract_id,
                               cv.templet_id,
                               p_user_id,
                               c_cps.bp_category,
                               c_cps.bp_class,
                               c_cps.bp_id,
                               p_content_type) = g_check_yes THEN
          SELECT COUNT(*)
            INTO v_tmpt_count
            FROM con_contract_content cc
           WHERE cc.contract_id = p_contract_id
             AND cc.templet_id = cv.templet_id
             AND cc.con_contract_bp_id = c_cps.record_id;
          IF v_tmpt_count = 0 THEN
            insert_contract_content(p_content_id         => v_content_id,
                                    p_contract_id        => p_contract_id,
                                    p_content_number     => cv.description,
                                    p_clause_usage       => cv.templet_usage,
                                    p_templet_id         => cv.templet_id,
                                    p_con_contract_bp_id => c_cps.record_id,
                                    p_mortgage_id        => '',
                                    p_content_print_flag => 'N',
                                    p_available_flag     => 'Y',
                                    p_user_id            => p_user_id
                                 );
            UPDATE con_contract_content ct
               SET ct.ref_n05      = cv.print_num
                 --  ct.content_type = p_content_type,
                  -- ct.data_class   = r_con_rec.data_class
             WHERE ct.content_id = v_content_id;
          END IF;
        END IF;
      END LOOP;
    END LOOP;
     hls_document_save_pkg.save_cdd_item_from_doc(p_document_category => 'CON_CONTRACT',
                                                 p_document_id       => p_contract_id,
                                                 p_user_id           => p_user_id,
                                                 p_cdd_list_id       => r_con_rec.cdd_list_id);
  EXCEPTION
    WHEN e_clause_usage_null_error THEN
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'CON_CONTRACT_CONTENT_PKG.CLAUSE_USAGE_NULL_ERROR',
                                                      p_created_by              => p_user_id,
                                                      p_package_name            => 'con_contract_content_pkg',
                                                      p_procedure_function_name => 'contract_content_create');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
  END;
end hl_con_contract_content_pkg;
/