[Home] [Help]
10331: END LOOP;
10332: END get_rule_intl_text_ids;
10333:
10334: ----->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
10335: ---------procedure to copy data into cz_lce_texts
10336: ---------reason ids and unsatisfied message ids of cz rules
10337: ---------are copied and resolved in this procedure
10338: ---------
10339: PROCEDURE copy_lce_texts(p_copy_rules_flag IN VARCHAR2)
10372: v_lce_text_tbl.DELETE;
10373:
10374: SELECT lce_header_id, seq_nbr, lce_text
10375: BULK COLLECT INTO v_text_hdr_tbl, v_text_seq_tbl, v_lce_text_tbl
10376: FROM cz_lce_texts
10377: WHERE cz_lce_texts.lce_header_id = cz_pb_mgr.v_cz_lce_headers_old_ref(i)
10378: ORDER BY lce_header_id, seq_nbr;
10379: EXCEPTION
10380: WHEN NO_DATA_FOUND
10373:
10374: SELECT lce_header_id, seq_nbr, lce_text
10375: BULK COLLECT INTO v_text_hdr_tbl, v_text_seq_tbl, v_lce_text_tbl
10376: FROM cz_lce_texts
10377: WHERE cz_lce_texts.lce_header_id = cz_pb_mgr.v_cz_lce_headers_old_ref(i)
10378: ORDER BY lce_header_id, seq_nbr;
10379: EXCEPTION
10380: WHEN NO_DATA_FOUND
10381: THEN
10378: ORDER BY lce_header_id, seq_nbr;
10379: EXCEPTION
10380: WHEN NO_DATA_FOUND
10381: THEN
10382: cz_pb_mgr.v_err_message := cz_utils.get_text('CZ_PB_BULK_COLLECT_ERR', 'TABLENAME', 'cz_lce_texts', 'SQLERRM', SQLERRM);
10383: cz_pb_mgr.error_msg_populate(cz_pb_mgr.v_err_message, 'cz_pb_mgr.MODEL', SQLCODE);
10384: WHEN OTHERS
10385: THEN
10386: cz_pb_mgr.v_err_message := cz_utils.get_text('CZ_PB_BULK_COLLECT_ERR', 'TABLENAME', 'cz_lce_texts', 'SQLERRM', SQLERRM);
10382: cz_pb_mgr.v_err_message := cz_utils.get_text('CZ_PB_BULK_COLLECT_ERR', 'TABLENAME', 'cz_lce_texts', 'SQLERRM', SQLERRM);
10383: cz_pb_mgr.error_msg_populate(cz_pb_mgr.v_err_message, 'cz_pb_mgr.MODEL', SQLCODE);
10384: WHEN OTHERS
10385: THEN
10386: cz_pb_mgr.v_err_message := cz_utils.get_text('CZ_PB_BULK_COLLECT_ERR', 'TABLENAME', 'cz_lce_texts', 'SQLERRM', SQLERRM);
10387: cz_pb_mgr.error_msg_populate(cz_pb_mgr.v_err_message, 'cz_pb_mgr.MODEL', SQLCODE);
10388: cz_pb_mgr.v_status_code := PUBLICATION_ERROR;
10389: END;
10390:
10405:
10406: IF (v_text_hdr_ref.COUNT > 0)
10407: THEN
10408: -------------Resolving lce header id
10409: resolve_ids(v_text_hdr_ref, cz_pb_mgr.v_cz_lce_headers_idx_ref, 'lce header id of cz lce texts');
10410: END IF;
10411:
10412: -------replace reason ids in lce text
10413: IF (v_lce_text_ref.COUNT > 0) THEN
10471: THEN
10472: FOR i IN v_text_hdr_new_ref.FIRST .. v_text_hdr_new_ref.LAST
10473: LOOP
10474: BEGIN
10475: EXECUTE IMMEDIATE 'INSERT INTO cz_lce_texts'
10476: || cz_pb_mgr.v_db_link
10477: || '( LCE_HEADER_ID'
10478: || ' '
10479: || ',SEQ_NBR'
10489: EXCEPTION
10490: WHEN OTHERS
10491: THEN
10492: ROLLBACK;
10493: cz_pb_mgr.v_err_message := cz_utils.get_text('CZ_PB_INSERT_ERR', 'TABLENAME', 'cz_lce_texts', 'SQLERRM', SQLERRM);
10494: cz_pb_mgr.error_msg_populate(cz_pb_mgr.v_err_message, 'cz_pb_mgr.LCETEXTS', SQLCODE);
10495: cz_pb_mgr.v_status_code := PUBLICATION_ERROR;
10496: END;
10497: END LOOP;
10508: END IF; /* end if of cz_pb_mgr.v_status_code <> PUBLICATION_ERROR */
10509: EXCEPTION
10510: WHEN OTHERS
10511: THEN
10512: cz_pb_mgr.v_err_message := cz_utils.get_text('CZ_PB_INSERT_ERR', 'TABLENAME', 'cz_lce_texts', 'SQLERRM', SQLERRM);
10513: cz_pb_mgr.error_msg_populate(cz_pb_mgr.v_err_message, 'cz_pb_mgr.COPYLCETEXTS', SQLCODE);
10514: cz_pb_mgr.v_sql_err_msg := SUBSTR(SQLERRM, 1, 2000);
10515: cz_pb_mgr.v_status_code := PUBLICATION_ERROR;
10516: RAISE;