hl_con_contract_content_pkg.pck 14.4 KB
Newer Older
lijingjing's avatar
lijingjing committed
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324
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;
/