[Home] [Help]
PACKAGE BODY: APPS.OKC_TERMS_QA_PVT
Source
1 PACKAGE BODY OKC_TERMS_QA_PVT AS
2 /* $Header: OKCVDQAB.pls 120.29.12020000.2 2013/02/04 07:35:53 skavutha ship $ */
3
4 ---------------------------------------------------------------------------
5 -- GLOBAL MESSAGE CONSTANTS
6 ---------------------------------------------------------------------------
7 G_FND_APP CONSTANT VARCHAR2(200) := OKC_API.G_FND_APP;
8 G_REQUIRED_VALUE CONSTANT VARCHAR2(200) := OKC_API.G_REQUIRED_VALUE;
9 G_INVALID_VALUE CONSTANT VARCHAR2(200) := OKC_API.G_INVALID_VALUE;
10 G_COL_NAME_TOKEN CONSTANT VARCHAR2(200) := OKC_API.G_COL_NAME_TOKEN;
11 G_OKC_MSG_INVALID_ARGUMENT CONSTANT VARCHAR2(200) := 'OKC_CONTRACTS_INVALID_ARGUMENT';
12 -- ARG_NAME ARG_VALUE is invalid.
13 ---------------------------------------------------------------------------
14 -- GLOBAL CONSTANTS
15 ---------------------------------------------------------------------------
16 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKC_TERMS_QA_PVT';
17 G_MODULE CONSTANT VARCHAR2(250) := 'okc.plsql.'||G_PKG_NAME||'.';
18 G_APP_NAME CONSTANT VARCHAR2(3) := OKC_API.G_APP_NAME;
19 G_TMPL_DOC_TYPE CONSTANT VARCHAR2(30) := OKC_TERMS_UTIL_GRP.G_TMPL_DOC_TYPE;
20
21 G_FALSE CONSTANT VARCHAR2(1) := FND_API.G_FALSE;
22 G_TRUE CONSTANT VARCHAR2(1) := FND_API.G_TRUE;
23 G_OKC CONSTANT VARCHAR2(3) := 'OKC';
24
25 G_QA_STS_SUCCESS CONSTANT varchar2(1) := OKC_TERMS_QA_GRP.G_QA_STS_SUCCESS;
26 G_QA_STS_ERROR CONSTANT varchar2(1) := OKC_TERMS_QA_GRP.G_QA_STS_ERROR;
27 G_QA_STS_WARNING CONSTANT varchar2(1) := OKC_TERMS_QA_GRP.G_QA_STS_WARNING;
28
29 G_RET_STS_SUCCESS CONSTANT varchar2(1) := FND_API.G_RET_STS_SUCCESS;
30 G_RET_STS_ERROR CONSTANT varchar2(1) := FND_API.G_RET_STS_ERROR;
31 G_RET_STS_UNEXP_ERROR CONSTANT varchar2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
32
33 G_UNEXPECTED_ERROR CONSTANT varchar2(200) := 'OKC_UNEXPECTED_ERROR';
34 G_SQLERRM_TOKEN CONSTANT varchar2(200) := 'ERROR_MESSAGE';
35 G_SQLCODE_TOKEN CONSTANT varchar2(200) := 'ERROR_CODE';
36 G_ART_QA_TYPE CONSTANT VARCHAR2(30) := 'ARTICLE';
37 G_TMPL_QA_TYPE CONSTANT VARCHAR2(30) := 'TEMPLATE';
38 G_SCN_QA_TYPE CONSTANT VARCHAR2(30) := 'SECTION';
39 G_DLV_QA_TYPE CONSTANT VARCHAR2(30) := 'DELIVERABLE';
40 G_EXP_QA_TYPE CONSTANT VARCHAR2(30) := 'CONTRACT_EXPERT';
41 G_QA_LOOKUP CONSTANT VARCHAR2(30) := 'OKC_TERM_QA_LIST';
42 G_INCOMPATIBLE CONSTANT VARCHAR2(30) := 'INCOMPATIBLE';
43 G_ALTERNATE CONSTANT VARCHAR2(30) := 'ALTERNATE';
44 G_AMEND_CODE_DELETED CONSTANT VARCHAR2(30) := 'DELETED';
45 G_UNASSIGNED_SECTION_CODE CONSTANT VARCHAR2(30) := 'UNASSIGNED';
46 G_CONTRACT CONSTANT VARCHAR2(30) := 'CONTRACT';
47
48
49 -- QA Checks --
50
51 G_CHECK_INCOMPATIBILITY CONSTANT VARCHAR2(30) := 'CHECK_INCOMPATIBILITY';
52 G_CHECK_ALTERNATE CONSTANT VARCHAR2(30) := 'CHECK_ALTERNATE';
53 G_CHECK_DUPLICATE CONSTANT VARCHAR2(30) := 'CHECK_DUPLICATE';
54 G_CHECK_ART_VALIDITY CONSTANT VARCHAR2(30) := 'CHECK_ART_VALIDITY';
55 G_CHECK_LATEST_VERSION CONSTANT VARCHAR2(30) := 'CHECK_LATEST_VERSION';
56 G_CHECK_UNRESOLVED_SYS_VAR CONSTANT VARCHAR2(30) := 'CHECK_UNRESOLVED_SYS_VAR';
57 G_CHECK_VAR_USAGE CONSTANT VARCHAR2(30) := 'CHECK_VAR_USAGE';
58 G_CHECK_EXT_VAR_VALUE CONSTANT VARCHAR2(30) := 'CHECK_EXT_VAR_VALUE';
59 G_CHECK_INT_VAR_VALUE CONSTANT VARCHAR2(30) := 'CHECK_INT_VAR_VALUE';
60 G_CHECK_UNASSIGNED_ART CONSTANT VARCHAR2(30) := 'CHECK_UNASSIGNED_ARTICLE';
61 G_CHECK_EMPTY_SECTION CONSTANT VARCHAR2(30) := 'CHECK_EMPTY_SECTION';
62 G_CHECK_SCN_AMEND_NO_TEXT CONSTANT VARCHAR2(30) := 'CHECK_SCN_AMEND_NO_TEXT';
63 G_CHECK_ART_AMEND_NO_TEXT CONSTANT VARCHAR2(30) := 'CHECK_ART_AMEND_NO_TEXT';
64 G_CHECK_TMPL_EFFECTIVITY CONSTANT VARCHAR2(30) := 'CHECK_TEMPL_EFFECTIVITY';
65 G_CHECK_LAYOUT_TMPL CONSTANT VARCHAR2(30) := 'CHECK_LAYOUT_TMPL';
66 G_OKC_CHECK_LOCK_CONTRACT CONSTANT VARCHAR2(30) := 'CHECK_LOCK_CONTRACT';
67 G_OKC_CHECK_CONTRACT_ADMIN CONSTANT VARCHAR2(30) := 'CHECK_CONTRACT_ADMIN';
68 -- For Bug# 6979012
69 G_CHECK_ART_REJECTED CONSTANT VARCHAR2(30) := 'CHECK_ART_REJECTED';
70
71 G_CHECK_ART_EXT CONSTANT VARCHAR2(30) := 'CHECK_ART_EXT';
72 G_CHECK_ART_TYP CONSTANT VARCHAR2(30) := 'CHECK_ART_INV_TYP';
73 G_CHECK_ART_DEF_SEC CONSTANT VARCHAR2(30) := 'CHECK_ART_DEF_SEC';
74 G_CHECK_ART_INV_VAR CONSTANT VARCHAR2(30) := 'CHECK_ART_INV_VAR';
75 G_CHECK_ART_INV_VAL CONSTANT VARCHAR2(30) := 'CHECK_ART_INV_VAL';
76 G_CHECK_TRANS_TMPL_REVISION CONSTANT VARCHAR2(30) := 'CHECK_TRANS_TMPL_REV';
77 G_CHECK_TRANS_TMPL_EFF CONSTANT VARCHAR2(30) := 'CHECK_TRANS_TMPL_EFF';
78
79 /* expert commented out
80 G_CHECK_RUL_INC CONSTANT VARCHAR2(30) := 'CHECK_RUL_INC';
81 G_CHECK_RUL_ALT CONSTANT VARCHAR2(30) := 'CHECK_RUL_ALT';
82 G_CHECK_RUL_DUP CONSTANT VARCHAR2(30) := 'CHECK_RUL_DUP';
83 G_CHECK_RUL_VAR_DOC CONSTANT VARCHAR2(30) := 'CHECK_RUL_VAR_DOC';
84 G_CHECK_RUL_ART_VAL CONSTANT VARCHAR2(30) := 'CHECK_RUL_ART_VAL';
85 */
86 -- QA Error messages --
87
88 G_OKC_CHECK_INCOMPATIBILITY CONSTANT VARCHAR2(30) := 'OKC_CHECK_INCOMPATIBILITY';
89 G_OKC_CHECK_ALTERNATE CONSTANT VARCHAR2(30) := 'OKC_CHECK_ALTERNATE';
90 G_OKC_CHECK_DUPLICATE CONSTANT VARCHAR2(30) := 'OKC_CHECK_DUPLICATE';
91 G_OKC_CHECK_ART_VALIDITY CONSTANT VARCHAR2(30) := 'OKC_CHECK_ART_VALIDITY';
92 G_OKC_CHECK_TMPL_ART_VALIDITY CONSTANT VARCHAR2(30) := 'OKC_CHECK_TMPL_ART_VALIDITY';
93 G_OKC_CHECK_LATEST_VERSION CONSTANT VARCHAR2(30) := 'OKC_CHECK_LATEST_VERSION';
94 G_OKC_CHECK_UNRES_SYS_VAR CONSTANT VARCHAR2(30) := 'OKC_CHECK_UNRES_SYS_VAR';
95 G_OKC_CHECK_VAR_USAGE CONSTANT VARCHAR2(30) := 'OKC_CHECK_VAR_USAGE';
96 G_OKC_CHECK_EXT_VAR_VALUE CONSTANT VARCHAR2(30) := 'OKC_CHECK_EXT_VAR_VALUE';
97 G_OKC_CHECK_INT_VAR_VALUE CONSTANT VARCHAR2(30) := 'OKC_CHECK_INT_VAR_VALUE';
98 G_OKC_CHECK_UNASSIGNED_ART CONSTANT VARCHAR2(30) := 'OKC_CHECK_UNASSIGNED_ART';
99 G_OKC_CHECK_EMPTY_SECTION CONSTANT VARCHAR2(30) := 'OKC_CHECK_EMPTY_SECTION';
100 G_OKC_CHECK_SCN_AMEND_NO_TEXT CONSTANT VARCHAR2(30) := 'OKC_CHECK_SCN_AMEND_NO_TEXT';
101 G_OKC_CHECK_ART_AMEND_NO_TEXT CONSTANT VARCHAR2(30) := 'OKC_CHECK_ART_AMEND_NO_TEXT';
102 G_OKC_CHECK_TMPL_EFFECTIVITY CONSTANT VARCHAR2(30) := 'OKC_CHECK_TEMPL_EFFECTIVITY';
103 --Bug 4126819
104 G_OKC_CHECK_TEMPL_USG_ASSO CONSTANT VARCHAR2(30) := 'OKC_CHECK_TEMPL_USG_ASSO';
105 G_OKC_CHECK_LAYOUT_TMPL CONSTANT VARCHAR2(30) := 'OKC_CHECK_LAYOUT_TMPL';
106 -- For Bug# 6979012
107 G_OKC_CHECK_ART_REJECTED CONSTANT VARCHAR2(30) := 'OKC_CHECK_ART_REJECTED';
108
109 G_OKC_CHECK_ART_EXT CONSTANT VARCHAR2(30) := 'OKC_CHECK_ART_EXT';
110 G_OKC_CHECK_ART_TYP CONSTANT VARCHAR2(30) := 'OKC_CHECK_ART_INV_TYP';
111 G_OKC_CHECK_ART_DEF_SEC CONSTANT VARCHAR2(30) := 'OKC_CHECK_ART_DEF_SEC';
112 G_OKC_CHECK_ART_INV_VAR CONSTANT VARCHAR2(30) := 'OKC_CHECK_ART_INV_VAR';
113 G_OKC_CHECK_ART_INV_VAL CONSTANT VARCHAR2(30) := 'OKC_CHECK_ART_INV_VAL';
114 G_OKC_CHECK_TRANS_TMPL_REV CONSTANT VARCHAR2(30) := 'OKC_CHECK_TRANS_TMPL_REV';
115 G_OKC_CHECK_TRANS_TMPL_EFF CONSTANT VARCHAR2(30) := 'OKC_CHECK_TRANS_TMPL_EFF';
116
117 /* expert commented out
118 G_OKC_CHECK_RUL_INC CONSTANT VARCHAR2(30) := 'OKC_CHECK_RUL_INC';
119 G_OKC_CHECK_RUL_ALT CONSTANT VARCHAR2(30) := 'OKC_CHECK_RUL_ALT';
120 G_OKC_CHECK_RUL_DUP CONSTANT VARCHAR2(30) := 'OKC_CHECK_RUL_DUP';
121 G_OKC_CHECK_RUL_VAR_DOC CONSTANT VARCHAR2(30) := 'OKC_CHECK_RUL_VAR_DOC';
122 G_OKC_CHECK_RUL_ART_VAL CONSTANT VARCHAR2(30) := 'OKC_CHECK_RUL_ART_VAL';
123 */
124 -- QA Error messages (Short)--
125
126 G_OKC_CHECK_INCOMPATIBILITY_SH CONSTANT VARCHAR2(50) := 'OKC_CHECK_INCOMPATIBILITY_SH';
127 G_OKC_CHECK_ALTERNATE_SH CONSTANT VARCHAR2(50) := 'OKC_CHECK_ALTERNATE_SH';
128 G_OKC_CHECK_DUPLICATE_SH CONSTANT VARCHAR2(50) := 'OKC_CHECK_DUPLICATE_SH';
129 G_OKC_CHECK_ART_VALIDITY_SH CONSTANT VARCHAR2(50) := 'OKC_CHECK_ART_VALIDITY_SH';
130 G_OKC_CHECK_TMPL_ART_VALID_SH CONSTANT VARCHAR2(30) := 'OKC_CHECK_TMPL_ART_VALIDITY_SH';
131 G_OKC_CHECK_LATEST_VERSION_SH CONSTANT VARCHAR2(50) := 'OKC_CHECK_LATEST_VERSION_SH';
132 G_OKC_CHECK_UNRES_SYS_VAR_SH CONSTANT VARCHAR2(50) := 'OKC_CHECK_UNRES_SYS_VAR_SH';
133 G_OKC_CHECK_VAR_USAGE_SH CONSTANT VARCHAR2(50) := 'OKC_CHECK_VAR_USAGE_SH';
134 G_OKC_CHECK_EXT_VAR_VALUE_SH CONSTANT VARCHAR2(50) := 'OKC_CHECK_EXT_VAR_VALUE_SH';
135 G_OKC_CHECK_INT_VAR_VALUE_SH CONSTANT VARCHAR2(50) := 'OKC_CHECK_INT_VAR_VALUE_SH';
136 G_OKC_CHECK_UNASSIGNED_ART_SH CONSTANT VARCHAR2(50) := 'OKC_CHECK_UNASSIGNED_ART_SH';
137 G_OKC_CHECK_EMPTY_SECTION_SH CONSTANT VARCHAR2(50) := 'OKC_CHECK_EMPTY_SECTION_SH';
138 G_OKC_CHK_SCN_AMEND_NO_TEXT_SH CONSTANT VARCHAR2(50) := 'OKC_CHECK_SCN_AMEND_NO_TEXT_SH';
139 G_OKC_CHK_ART_AMEND_NO_TEXT_SH CONSTANT VARCHAR2(50) := 'OKC_CHECK_ART_AMEND_NO_TEXT_SH';
140 G_OKC_CHK_TMPL_EFFECTIVITY_SH CONSTANT VARCHAR2(50) := 'OKC_CHECK_TEMPL_EFFECTIVITY_SH';
141 --Bug 4126819
142 G_OKC_CHECK_TEMPL_USG_ASSO_SH CONSTANT VARCHAR2(50) := 'OKC_CHECK_TEMPL_USG_ASSO_SH';
143
144 G_OKC_CHECK_ART_EXT_SH CONSTANT VARCHAR2(30) := 'OKC_CHECK_ART_EXT_SH';
145 G_OKC_CHECK_ART_TYP_SH CONSTANT VARCHAR2(30) := 'OKC_CHECK_ART_INV_TYP_SH';
146 G_OKC_CHECK_ART_DEF_SEC_SH CONSTANT VARCHAR2(30) := 'OKC_CHECK_ART_DEF_SEC_SH';
147 G_OKC_CHECK_ART_INV_VAR_SH CONSTANT VARCHAR2(30) := 'OKC_CHECK_ART_INV_VAR_SH';
148 G_OKC_CHECK_ART_INV_VAL_SH CONSTANT VARCHAR2(30) := 'OKC_CHECK_ART_INV_VAL_SH';
149 G_OKC_CHECK_TRANS_TMPL_REV_SH CONSTANT VARCHAR2(30) := 'OKC_CHECK_TRANS_TMPL_REV_SH';
150 G_OKC_CHECK_TRANS_TMPL_EFF_SH CONSTANT VARCHAR2(30) := 'OKC_CHECK_TRANS_TMPL_EFF_SH';
151 G_OKC_CHECK_LOCK_CONTRACT_SH CONSTANT VARCHAR2(30) := 'OKC_CHECK_LOCK_CONTRACT_SH';
152 G_OKC_CHECK_CTRT_ADMIN_SH CONSTANT VARCHAR2(30) := 'OKC_CHECK_CTRT_ADMIN_SH';
153 G_OKC_CTRT_ADMIN_EMP_SH CONSTANT VARCHAR2(30) := 'OKC_CTRT_ADMIN_EMP_SH';
154 G_OKC_ADMIN_VALID_EMP_SH CONSTANT VARCHAR2(30) := 'OKC_ADMIN_VALID_EMP_SH';
155 G_OKC_CTRT_ADMIN_EMP_DT CONSTANT VARCHAR2(30) := 'OKC_CTRT_ADMIN_EMP_DT';
156 G_OKC_ADMIN_VALID_EMP_DT CONSTANT VARCHAR2(30) := 'OKC_ADMIN_VALID_EMP_DT';
157
158 /* expert commented out
159 G_OKC_CHECK_RUL_INC_SH CONSTANT VARCHAR2(30) := 'OKC_CHECK_RUL_INC_SH';
160 G_OKC_CHECK_RUL_ALT_SH CONSTANT VARCHAR2(30) := 'OKC_CHECK_RUL_ALT_SH';
161 G_OKC_CHECK_RUL_DUP_SH CONSTANT VARCHAR2(30) := 'OKC_CHECK_RUL_DUP_SH';
162 G_OKC_CHECK_RUL_VAR_DOC_SH CONSTANT VARCHAR2(30) := 'OKC_CHECK_RUL_VAR_DOC_SH';
163 G_OKC_CHECK_RUL_ART_VAL_SH CONSTANT VARCHAR2(30) := 'OKC_CHECK_RUL_ART_VAL_SH';
164 */
165 -- QA Suggestion messages --
166
167 G_OKC_CHECK_INCOMPATIBILITY_S CONSTANT VARCHAR2(30) := 'OKC_CHECK_INCOMPATIBILITY_S';
168 G_OKC_CHECK_ALTERNATE_S CONSTANT VARCHAR2(30) := 'OKC_CHECK_ALTERNATE_S';
169 G_OKC_CHECK_DUPLICATE_S CONSTANT VARCHAR2(30) := 'OKC_CHECK_DUPLICATE_S';
170 G_OKC_CHECK_ART_VALIDITY_S CONSTANT VARCHAR2(30) := 'OKC_CHECK_ART_VALIDITY_S';
171 G_OKC_CHECK_TMPL_ART_VALID_S CONSTANT VARCHAR2(30) := 'OKC_CHECK_TMPL_ART_VALIDITY_S';
172 G_OKC_CHECK_LATEST_VERSION_S CONSTANT VARCHAR2(30) := 'OKC_CHECK_LATEST_VERSION_S';
173 G_OKC_CHECK_UNRES_SYS_VAR_S CONSTANT VARCHAR2(30) := 'OKC_CHECK_UNRES_SYS_VAR_S';
174 G_OKC_CHECK_VAR_USAGE_S CONSTANT VARCHAR2(30) := 'OKC_CHECK_VAR_USAGE_S';
175 G_OKC_CHECK_EXT_VAR_VALUE_S CONSTANT VARCHAR2(30) := 'OKC_CHECK_EXT_VAR_VALUE_S';
176 G_OKC_CHECK_INT_VAR_VALUE_S CONSTANT VARCHAR2(30) := 'OKC_CHECK_INT_VAR_VALUE_S';
177 G_OKC_CHECK_UNASSIGNED_ART_S CONSTANT VARCHAR2(30) := 'OKC_CHECK_UNASSIGNED_ART_S';
178 G_OKC_CHECK_EMPTY_SECTION_S CONSTANT VARCHAR2(30) := 'OKC_CHECK_EMPTY_SECTION_S';
179 G_OKC_CHK_SCN_AMEND_NO_TEXT_S CONSTANT VARCHAR2(30) := 'OKC_CHECK_SCN_AMEND_NO_TEXT_S';
180 G_OKC_CHK_ART_AMEND_NO_TEXT_S CONSTANT VARCHAR2(30) := 'OKC_CHECK_ART_AMEND_NO_TEXT_S';
181 G_OKC_CHECK_TMPL_EFFECTIVITY_S CONSTANT VARCHAR2(30) := 'OKC_CHECK_TEMPL_EFFECTIVITY_S';
182 --Bug 4126819
183 G_OKC_CHECK_TEMPL_USG_ASSO_S CONSTANT VARCHAR2(30) := 'OKC_CHECK_TEMPL_USG_ASSO_S';
184 G_OKC_CHECK_LAYOUT_TMPL_S CONSTANT VARCHAR2(30) := 'OKC_CHECK_LAYOUT_TMPL_S';
185 -- For Bug# 6979012
186 G_OKC_CHECK_ART_REJECTED_S CONSTANT VARCHAR2(30) := 'OKC_CHECK_ART_REJECTED_S';
187
188 G_OKC_CHECK_ART_EXT_S CONSTANT VARCHAR2(30) := 'OKC_CHECK_ART_EXT_S';
189 G_OKC_CHECK_ART_TYP_S CONSTANT VARCHAR2(30) := 'OKC_CHECK_ART_INV_TYP_S';
190 G_OKC_CHECK_ART_DEF_SEC_S CONSTANT VARCHAR2(30) := 'OKC_CHECK_ART_DEF_SEC_S';
191 G_OKC_CHECK_ART_INV_VAR_S CONSTANT VARCHAR2(30) := 'OKC_CHECK_ART_INV_VAR_S';
192 G_OKC_CHECK_ART_INV_VAL_S CONSTANT VARCHAR2(30) := 'OKC_CHECK_ART_INV_VAL_S';
193 G_OKC_CHECK_TRANS_TMPL_REV_S CONSTANT VARCHAR2(30) := 'OKC_CHECK_TRANS_TMPL_REV_S';
194 G_OKC_CHECK_TRANS_TMPL_EFF_S CONSTANT VARCHAR2(30) := 'OKC_CHECK_TRANS_TMPL_EFF_S';
195 G_OKC_CHECK_LOCK_CONTRACT_S CONSTANT VARCHAR2(30) := 'OKC_CHECK_LOCK_CONTRACT_S';
196 G_OKC_CHECK_CTRT_ADMIN_S CONSTANT VARCHAR2(30) := 'OKC_CHECK_CTRT_ADMIN_S';
197 G_OKC_CTRT_ADMIN_EMP_S CONSTANT VARCHAR2(30) := 'OKC_CTRT_ADMIN_EMP_S';
198 G_OKC_ADMIN_VALID_EMP_S CONSTANT VARCHAR2(30) := 'OKC_ADMIN_VALID_EMP_S';
199 G_OKC_ADMIN_EMP_SUGG CONSTANT VARCHAR2(30) := 'OKC_ADMIN_EMP_SUGG';
200 G_OKC_ADMIN_VALID_EMP_SUGG CONSTANT VARCHAR2(30) := 'OKC_ADMIN_VALID_EMP_SUGG';
201 /* expert commented out
202 G_OKC_CHECK_RUL_INC_S CONSTANT VARCHAR2(30) := 'OKC_CHECK_RUL_INC_S';
203 G_OKC_CHECK_RUL_ALT_S CONSTANT VARCHAR2(30) := 'OKC_CHECK_RUL_ALT_S';
204 G_OKC_CHECK_RUL_DUP_S CONSTANT VARCHAR2(30) := 'OKC_CHECK_RUL_DUP_S';
205 G_OKC_CHECK_RUL_VAR_DOC_S CONSTANT VARCHAR2(30) := 'OKC_CHECK_RUL_VAR_DOC_S';
206 G_OKC_CHECK_RUL_ART_VAL_S CONSTANT VARCHAR2(30) := 'OKC_CHECK_ART_VAL_DOC_S';
207 */
208 ---------------------------------------------------------------------------
209 -- GLOBAL VARIABLES
210 ------------------------------------------------------------------------------
211 TYPE article_rec_type IS RECORD (
212 id OKC_K_ARTICLES_B.ID%TYPE,
213 article_id OKC_K_ARTICLES_B.SAV_SAE_ID%TYPE,
214 article_version_id OKC_K_ARTICLES_B.ARTICLE_VERSION_ID%TYPE,
215 amendment_operation_code OKC_K_ARTICLES_B.amendment_operation_code%TYPE,
216 amendment_description OKC_K_ARTICLES_B.amendment_description%TYPE,
217 scn_id OKC_K_ARTICLES_B.scn_id%TYPE,
218 title OKC_QA_ERRORS_T.title%TYPE,
219 std_art_id OKC_K_ARTICLES_B.SAV_SAE_ID%TYPE
220 );
221
222 /* expert commented out
223 -- new type to store xprt clause details
224 TYPE xprt_article_rec_type IS RECORD (
225 article_id OKC_ARTICLES_ALL.ARTICLE_ID%TYPE,
226 article_version_id OKC_ARTICLE_VERSIONS.ARTICLE_VERSION_ID%TYPE,
227 title OKC_QA_ERRORS_T.title%TYPE,
228 rule_id OKC_XPRT_CLAUSES_V.rule_id%TYPE,
229 rule_name OKC_XPRT_CLAUSES_V.rule_name%TYPE
230 );
231 */
232
233 TYPE section_rec_type IS RECORD (
234 id OKC_SECTIONS_B.ID%TYPE,
235 amendment_operation_code OKC_SECTIONS_B.amendment_operation_code%TYPE,
236 amendment_description OKC_SECTIONS_B.amendment_description%TYPE,
237 scn_id OKC_SECTIONS_B.scn_id%TYPE,
238 heading OKC_QA_ERRORS_T.section_name%TYPE,
239 scn_code OKC_SECTIONS_B.scn_code%TYPE
240 );
241
242 TYPE qa_detail_type IS RECORD (
243 qa_code FND_LOOKUPS.LOOKUP_CODE%TYPE,
244 qa_name FND_LOOKUPS.MEANING%TYPE,
245 severity_flag OKC_DOC_QA_LISTS.SEVERITY_FLAG%TYPE,
246 perform_qa VARCHAR2(1)
247 );
248
249 TYPE qa_detail_tbl_type IS TABLE OF qa_detail_type INDEX BY BINARY_INTEGER;
250 TYPE article_tbl_type IS TABLE OF article_rec_type INDEX BY BINARY_INTEGER;
251 TYPE section_tbl_type IS TABLE OF section_rec_type INDEX BY BINARY_INTEGER;
252
253 /* expert commented out
254 TYPE xprt_article_tbl_type IS TABLE OF xprt_article_rec_type INDEX BY BINARY_INTEGER;
255 */
256
257 g_validation_level VARCHAR2(1);
258 g_expert_enabled VARCHAR2(1);
259 g_start_date DATE;
260 g_end_date DATE;
261 g_status_code OKC_TERMS_TEMPLATES_ALL.STATUS_CODE%TYPE;
262 g_template_name OKC_TERMS_TEMPLATES_ALL.TEMPLATE_NAME%TYPE;
263 g_org_id NUMBER;
264
265 l_qa_detail_tbl qa_detail_tbl_type;
266 l_article_tbl article_tbl_type;
267 l_section_tbl section_tbl_type;
268 l_article_effective_date DATE ;
269
270 /* expert commented out
271 l_xprt_article_tbl xprt_article_tbl_type;
272 */
273 --<<<<<<<<<<<<<<<<<< INTERNAL Simple API PROCEDURES for OKC_QA_ERRORS_T <<<<<<<<<<<<<<<<<<
274
275 ---------------------------------------------------------------------------
276 -- FUNCTION do_validation
277 ---------------------------------------------------------------------------
278 /* new function to check the validation level */
279
280 FUNCTION do_validation (
281 p_severity IN VARCHAR2,
282 p_doc_type IN VARCHAR2) RETURN BOOLEAN
283 IS
284
285 l_api_name CONSTANT VARCHAR2(30) := 'do_validation';
286
287 BEGIN
288
289 IF (p_doc_type = 'TEMPLATE') THEN
290 IF (g_validation_level = 'A') THEN
291 -- always do validations
292 RETURN TRUE;
293 ELSE
294 -- do only error checks (now g_validation_level = 'E')
295 IF (p_severity = 'E') THEN
296 RETURN TRUE;
297 ELSE
298 RETURN FALSE;
299 END IF;
300 END IF;
301 ELSE
302 -- for non TEMPLATE doc types always return true.
303 RETURN TRUE;
304 END IF;
305
306 END do_validation;
307
308 ---------------------------------------------------------------------------
309 -- FUNCTION get_seq_id
310 ---------------------------------------------------------------------------
311 FUNCTION get_seq_id (
312 x_sequence_id OUT NOCOPY NUMBER)RETURN VARCHAR2
313 IS
314
315 l_api_name CONSTANT VARCHAR2(30) := 'get_seq_id';
316 CURSOR l_seq_csr IS
317 SELECT OKC_QA_ERRORS_T_S.NEXTVAL FROM DUAL;
318
319 BEGIN
320 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
321 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: Entered get_seq_id');
322 END IF;
323
324 OPEN l_seq_csr;
325 FETCH l_seq_csr INTO x_sequence_id ;
326 IF l_seq_csr%NOTFOUND THEN
327 RAISE NO_DATA_FOUND;
328 END IF;
329 CLOSE l_seq_csr;
330
331 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
332 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'200: Leaving get_seq_id');
333 END IF;
334 RETURN G_RET_STS_SUCCESS;
335 EXCEPTION
336 WHEN OTHERS THEN
337
338 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
339 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'300: Leaving get_seq_id because of EXCEPTION: '||sqlerrm);
340 END IF;
341
342 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
343 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
344 END IF;
345
346 IF l_seq_csr%ISOPEN THEN
347 CLOSE l_seq_csr;
348 END IF;
349
350 RETURN G_RET_STS_UNEXP_ERROR ;
351
352 END get_seq_id;
353
354 ------------------------------------------------
355 -- PROCEDURE insert_row for:OKC_QA_ERRORS_T --
356 ------------------------------------------------
357 FUNCTION insert_row(
358 p_document_type IN VARCHAR2,
359 p_document_id IN NUMBER,
360 p_sequence_id IN NUMBER,
361 p_error_record_type IN VARCHAR2,
362 p_title IN VARCHAR2,
363 p_error_severity IN VARCHAR2,
364 p_qa_code IN VARCHAR2,
365 p_message_name IN VARCHAR2,
366 p_problem_short_desc IN VARCHAR2,
367 p_problem_details_short IN VARCHAR2,
368 p_problem_details IN VARCHAR2,
369 p_Sgestion IN VARCHAR2,
370 p_article_id IN NUMBER,
371 p_deliverable_id IN NUMBER,
372 p_section_name IN VARCHAR2,
373 p_reference_column1 IN VARCHAR2,
374 p_reference_column2 IN VARCHAR2,
375 p_reference_column3 IN VARCHAR2,
376 p_reference_column4 IN VARCHAR2,
377 p_reference_column5 IN VARCHAR2,
378 p_creation_date IN DATE,
379 p_error_record_type_name IN VARCHAR2,
380 p_error_severity_name IN VARCHAR2 )RETURN VARCHAR2
381 IS
382
383 l_api_name CONSTANT VARCHAR2(30) := 'Insert_Row';
384
385 BEGIN
386
387 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
388 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'700: Entered Insert_Row function');
389 END IF;
390
391 INSERT INTO OKC_QA_ERRORS_T
392 (
393 DOCUMENT_TYPE,
394 DOCUMENT_ID,
395 SEQUENCE_ID,
396 ERROR_RECORD_TYPE,
397 TITLE,
398 ERROR_SEVERITY,
399 QA_CODE,
400 MESSAGE_NAME,
401 PROBLEM_SHORT_DESC,
402 PROBLEM_DETAILS_SHORT,
403 PROBLEM_DETAILS,
404 SUGGESTION,
405 ARTICLE_ID,
406 DELIVERABLE_ID,
407 SECTION_NAME,
408 REFERENCE_COLUMN1,
409 REFERENCE_COLUMN2,
410 REFERENCE_COLUMN3,
411 REFERENCE_COLUMN4,
412 REFERENCE_COLUMN5,
413 CREATION_DATE,
414 ERROR_RECORD_TYPE_NAME,
415 ERROR_SEVERITY_NAME
416 )
417 VALUES
418 (
419 p_document_type,
420 p_document_id,
421 p_sequence_id,
422 p_error_record_type,
423 p_title,
424 p_error_severity,
425 p_qa_code,
426 p_message_name,
427 p_problem_short_desc,
428 p_problem_details_short,
429 p_problem_details,
430 p_Sgestion,
431 p_article_id,
432 p_deliverable_id,
433 p_section_name,
434 p_reference_column1,
435 p_reference_column2,
436 p_reference_column3,
437 p_reference_column4,
438 p_reference_column5,
439 p_creation_date,
440 p_error_record_type_name,
441 p_error_severity_name
442 );
443
444 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
445 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'800: Leaving Insert_Row');
446 END IF;
447
448 RETURN( G_RET_STS_SUCCESS );
449
450 EXCEPTION
451 WHEN OTHERS THEN
452
453 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
454 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'900: Leaving Insert_Row:OTHERS Exception');
455 END IF;
456
457 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
458 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
459 END IF;
460
461 RETURN( G_RET_STS_UNEXP_ERROR );
462
463 END insert_row;
464
465
466
467 -------------------------------------
468 -- FUNCTION get_article_title
469 -------------------------------------
470 FUNCTION get_article_title (
471 p_cat_id IN NUMBER) RETURN VARCHAR2
472 IS
473
474 l_article_title OKC_QA_ERRORS_T.TITLE%TYPE;
475
476 BEGIN
477 IF l_article_tbl.COUNT > 0 THEN
478 FOR i IN l_article_tbl.FIRST..l_article_tbl.LAST LOOP
479 IF l_article_tbl(i).id=p_cat_id THEN
480 l_article_title := substr(l_article_tbl(i).title,1,240);
481 Exit;
482 END IF;
483 END LOOP;
484 END IF; -- IF l_article_tbl.count > 0 THEN
485 Return l_article_title;
486 END get_article_title;
487
488 -------------------------------------
489 -- FUNCTION get_section_title
490 -------------------------------------
491 FUNCTION get_section_title (
492 p_scn_id IN NUMBER
493 ) RETURN VARCHAR2
494 IS
495
496 l_section_title OKC_QA_ERRORS_T.TITLE%TYPE;
497
498 BEGIN
499 IF l_section_tbl.COUNT > 0 THEN
500 FOR i IN l_section_tbl.FIRST..l_section_tbl.LAST LOOP
501 IF l_section_tbl(i).id = p_scn_id THEN
502 l_section_title := substr(l_section_tbl(i).heading,1,240);
503 Exit;
504 END IF;
505 END LOOP;
506 END IF; -- IF l_section_tbl.COUNT > 0 THEN
507 Return l_section_title;
508 END get_section_title;
509
510 -------------------------------------
511 -- PROCEDURE get_qa_code_detail
512 -------------------------------------
513 /* API to get severity and qa name for any QA .Will be used to populate QA result table. */
514 PROCEDURE get_qa_code_detail(
515 p_qa_code IN VARCHAR2,
516 x_perform_qa OUT NOCOPY VARCHAR2,
517 x_qa_name OUT NOCOPY VARCHAR2,
518 x_severity_flag OUT NOCOPY VARCHAR2,
519 x_return_status OUT NOCOPY VARCHAR2)
520 IS
521
522 l_api_name constant varchar2(30) := 'get_qa_code_detail';
523 l_found boolean :=FALSE;
524
525 BEGIN
526
527 x_return_status := G_RET_STS_SUCCESS ;
528 IF l_qa_detail_tbl.COUNT > 0 THEN
529 FOR i IN l_qa_detail_tbl.FIRST..l_qa_detail_tbl.LAST LOOP
530 IF l_qa_detail_tbl(i).qa_code = p_qa_code THEN
531 x_perform_qa := l_qa_detail_tbl(i).perform_qa;
532 x_severity_flag := l_qa_detail_tbl(i).severity_flag ;
533 x_qa_name := l_qa_detail_tbl(i).qa_name ;
534 l_found := TRUE;
535 EXIT;
536 END IF;
537 END LOOP; -- FOR i IN l_qa_detail_tbl.FIRST..l_qa_detail_tbl.LAST LOOP
538 END IF; -- IF l_qa_detail_tbl.COUNT > 0 THEN
539
540 IF not l_found THEN
541 FND_MESSAGE.set_name(G_APP_NAME, G_OKC_MSG_INVALID_ARGUMENT);
542 FND_MESSAGE.set_token('ARG_NAME', 'p_qa_code');
543 FND_MESSAGE.set_token('ARG_VALUE', p_qa_code);
544 FND_MSG_PUB.add;
545 FND_MSG_PUB.ADD;
546 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
547 END IF;
548 EXCEPTION
549 WHEN FND_API.G_EXC_ERROR THEN
550 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
551 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'2400: Leaving get_qa_code_detail : OKC_API.G_EXCEPTION_ERROR Exception');
552 END IF;
553 x_return_status := G_RET_STS_ERROR ;
554
555 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
556 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
557 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'2400: Leaving get_qa_code_detail : FND_API.G_EXC_UNEXPECTED_ERROR Exception');
558 END IF;
559 x_return_status := G_RET_STS_UNEXP_ERROR ;
560
561 WHEN OTHERS THEN
562 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
563 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'2000: Leaving get_qa_code_detail:OTHERS Exception');
564 END IF;
565
566 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
567 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
568 END IF;
569 x_return_status := G_RET_STS_UNEXP_ERROR ;
570
571 END get_qa_code_detail;
572
573 -------------------------------------
574 -- PROCEDURE log_qa_messages
575 -------------------------------------
576 PROCEDURE log_qa_messages (
577 x_return_status OUT NOCOPY VARCHAR2,
578 p_qa_result_tbl IN qa_result_tbl_type,
579 x_sequence_id OUT NOCOPY NUMBER)
580 IS
581
582 l_api_name CONSTANT VARCHAR2(30) := 'log_qa_messages';
583 i NUMBER ;
584
585 BEGIN
586 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
587 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1000: Entered log_qa_messages');
588 END IF;
589 -- Initialize API return status to success
590 x_return_status := G_RET_STS_SUCCESS;
591
592 --- Setting item attributes
593 -- Set primary key value
594 x_return_status := get_seq_id(
595 x_sequence_id => x_sequence_id
596 );
597
598 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
599 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
600 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
601 RAISE FND_API.G_EXC_ERROR;
602 END IF;
603
604 IF p_qa_result_tbl.COUNT > 0 THEN
605 FOR i IN p_qa_result_tbl.FIRST..p_qa_result_tbl.LAST LOOP
606 --------------------------------------------
607 -- Calling Simple API for Creating A Row
608 --------------------------------------------
609 x_return_status := insert_row(
610 p_sequence_id => x_sequence_id,
611 p_document_type => p_qa_result_tbl(i).document_type,
612 p_document_id => p_qa_result_tbl(i).document_id,
613 p_error_record_type => p_qa_result_tbl(i).error_record_type,
614 p_title => p_qa_result_tbl(i).title,
615 p_error_severity => p_qa_result_tbl(i).error_severity,
616 p_qa_code => p_qa_result_tbl(i).qa_code,
617 p_message_name => p_qa_result_tbl(i).message_name,
618 p_problem_short_desc => p_qa_result_tbl(i).problem_short_desc,
619 p_problem_details_short => p_qa_result_tbl(i).problem_details_short,
620 p_problem_details => p_qa_result_tbl(i).problem_details,
621 p_Sgestion => p_qa_result_tbl(i).suggestion,
622 p_article_id => p_qa_result_tbl(i).article_id,
623 p_deliverable_id => p_qa_result_tbl(i).deliverable_id,
624 p_section_name => p_qa_result_tbl(i).section_name,
625 p_reference_column1 => p_qa_result_tbl(i).reference_column1,
626 p_reference_column2 => p_qa_result_tbl(i).reference_column2,
627 p_reference_column3 => p_qa_result_tbl(i).reference_column3,
628 p_reference_column4 => p_qa_result_tbl(i).reference_column4,
629 p_reference_column5 => p_qa_result_tbl(i).reference_column5,
630 p_creation_date => p_qa_result_tbl(i).creation_date,
631 p_error_record_type_name => p_qa_result_tbl(i).error_record_type_name,
632 p_error_severity_name => p_qa_result_tbl(i).error_severity_name);
633
634 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
635 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
636 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
637 RAISE FND_API.G_EXC_ERROR ;
638 END IF;
639
640 END LOOP; -- FOR i IN p_qa_result_tbl.FIRST..p_qa_result_tbl.LAST LOOP
641 END IF; -- If p_qa_result_tbl.COUNT > 0 THEN
642
643 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
644 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1100: Leaving log_qa_messages');
645 END IF;
646
647 EXCEPTION
648 WHEN FND_API.G_EXC_ERROR THEN
649 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
650 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'1200: Leaving Log_QA_Messages : OKC_API.G_EXCEPTION_ERROR Exception');
651 END IF;
652 x_return_status := G_RET_STS_ERROR ;
653
654 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
655 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
656 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'1300: Leaving Log_QA_Messages : OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
657 END IF;
658 x_return_status := G_RET_STS_UNEXP_ERROR ;
659
660 WHEN OTHERS THEN
661 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
662 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'1400: Leaving Log_QA_Messages because of EXCEPTION: '||sqlerrm);
663 END IF;
664 x_return_status := G_RET_STS_UNEXP_ERROR ;
665
666 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
667 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
668 END IF;
669 END log_qa_messages;
670
671
672 --<<<<<<<<<<<<<<<<<< INTERNAL QA Check API PROCEDURES <<<<<<<<<<<<<<<<<<
673 -------------------------------------------
674 -- PROCEDURE check_incomp_and_alternate
675 -------------------------------------------
676 PROCEDURE check_incomp_and_alternate (
677 p_qa_mode IN VARCHAR2,
678 p_doc_type IN VARCHAR2,
679 p_doc_id IN NUMBER,
680
681 px_qa_result_tbl IN OUT NOCOPY qa_result_tbl_type,
682 x_return_status OUT NOCOPY VARCHAR2)
683 IS
684
685 l_api_name CONSTANT VARCHAR2(30) := 'Check_Incomp_and_alternate';
686 l_indx NUMBER;
687 l_incom_severity OKC_QA_ERRORS_T.Error_severity%TYPE;
688 l_incom_desc OKC_QA_ERRORS_T.PROBLEM_SHORT_DESC%TYPE;
689 l_alternate_severity OKC_QA_ERRORS_T.Error_severity%TYPE;
690 l_alternate_desc OKC_QA_ERRORS_T.PROBLEM_SHORT_DESC%TYPE;
691 l_perform_alternate_qa VARCHAR2(1);
692 l_perform_incom_qa VARCHAR2(1);
693
694 l_current_org_id VARCHAR2(100);
695
696 CURSOR l_get_incomp_alter_csr(c_current_org_id IN NUMBER) IS
697 SELECT kart1.id source_cat_id,
698 rel.SOURCE_ARTICLE_ID source_article_id,
699 kart1.SCN_ID scn_id,
700 kart1.label source_label,
701 Kart2.ID target_cat_id,
702 rel.TARGET_ARTICLE_ID target_article_id,
703 kart2.label target_label,
704 kart1.AMENDMENT_OPERATION_CODE amendment_operation_code,
705 rel.RELATIONSHIP_TYPE relationship_type
706 FROM OKC_K_ARTICLES_B kart1,
707 OKC_ARTICLE_RELATNS_ALL rel,
708 OKC_K_ARTICLES_B kart2
709 WHERE kart1.document_type = p_doc_type
710 AND kart1.document_id=p_doc_id
711 AND kart1.sav_sae_id=rel.source_article_id
712 AND kart2.document_type = p_doc_type
713 AND kart2.document_id=p_doc_id
714 AND kart2.sav_sae_id=rel.target_article_id
715 AND rel.org_id = c_current_org_id
716 AND rel.relationship_type in (G_INCOMPATIBLE ,G_ALTERNATE)
717 AND nvl(kart2.amendment_operation_code,'?')<>G_AMEND_CODE_DELETED
718 AND nvl(kart2.summary_amend_operation_code,'?')<>G_AMEND_CODE_DELETED
719 AND nvl(kart1.amendment_operation_code,'?')<>G_AMEND_CODE_DELETED
720 AND nvl(kart1.summary_amend_operation_code,'?')<>G_AMEND_CODE_DELETED;
721
722 /* expert commented out
723 -- new cursor to check for xprt articles
724 CURSOR l_get_incomp_alter_xprt_csr(c_current_org_id IN NUMBER) IS
725 SELECT
726 Rule.clause_id source_article_id,
727 Rule.rule_id rule_id,
728 Rule.rule_name rule_name,
729 Kart.sav_sae_id target_article_id,
730 Rel.relationship_type relationship_type
731 FROM OKC_XPRT_CLAUSES_V rule,
732 OKC_K_ARTICLES_B kart,
733 OKC_ARTICLE_RELATNS_ALL rel
734 WHERE rule.template_id = p_doc_id
735 AND kart.document_type = p_doc_type
736 AND kart.document_id = p_doc_id
737 AND rule.rule_id =rel.source_article_id
738 AND kart.sav_sae_id = rel.target_article_id
739 AND rel.org_id = c_current_org_id
740 AND rel.relationship_type in (G_INCOMPATIBLE ,G_ALTERNATE) ;
741 */
742
743 BEGIN
744
745 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
746 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1500: Entered Check_Incomp_and_alternate');
747 END IF;
748
749 -- current Org Id
750 -- fnd_profile.get('ORG_ID',l_current_org_id);
751 l_current_org_id := OKC_TERMS_UTIL_PVT.get_current_org_id(p_doc_type, p_doc_id);
752
753 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
754 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: l_current_org_id : '||l_current_org_id);
755 END IF;
756
757 get_qa_code_detail(p_qa_code => G_CHECK_INCOMPATIBILITY,
758 x_perform_qa => l_perform_incom_qa,
759 x_qa_name => l_incom_desc,
760 x_severity_flag => l_incom_severity,
761 x_return_status => x_return_status);
762 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
763 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
764 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
765 RAISE FND_API.G_EXC_ERROR ;
766 END IF;
767
768 get_qa_code_detail(p_qa_code => G_CHECK_ALTERNATE,
769 x_perform_qa => l_perform_alternate_qa,
770 x_qa_name => l_alternate_desc,
771 x_severity_flag => l_alternate_severity,
772 x_return_status => x_return_status);
773 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
774 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
775 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
776 RAISE FND_API.G_EXC_ERROR ;
777 END IF;
778
779 IF l_perform_incom_QA ='Y' OR l_perform_alternate_QA='Y' THEN
780
781 IF (NOT do_validation(l_incom_severity, p_doc_type)) AND
782 (NOT do_validation(l_alternate_severity, p_doc_type)) THEN
783 -- validation is not required
784 RETURN;
785 END IF;
786
787 FOR cr IN l_get_incomp_alter_csr(l_current_org_id) LOOP
788 IF (p_qa_mode =G_AMEND_QA and cr.amendment_operation_code IS NOT NULL)
789 OR p_qa_mode<>G_AMEND_QA THEN
790
791 IF cr.relationship_type=G_INCOMPATIBLE AND l_perform_incom_qa ='Y' THEN
792 l_indx := px_qa_result_tbl.COUNT + 1;
793
794 px_qa_result_tbl(l_indx).error_record_type := G_ART_QA_TYPE;
795 px_qa_result_tbl(l_indx).article_id := cr.source_article_id;
796 px_qa_result_tbl(l_indx).deliverable_id := Null;
797 px_qa_result_tbl(l_indx).title := get_article_title(cr.source_cat_id);
798 px_qa_result_tbl(l_indx).section_name := get_section_title(cr.scn_id);
799
800 px_qa_result_tbl(l_indx).qa_code := G_CHECK_INCOMPATIBILITY;
801 px_qa_result_tbl(l_indx).message_name := G_OKC_CHECK_INCOMPATIBILITY;
802 px_qa_result_tbl(l_indx).suggestion := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC,G_OKC_CHECK_INCOMPATIBILITY_S);
803
804 px_qa_result_tbl(l_indx).error_severity := l_incom_severity;
805 px_qa_result_tbl(l_indx).problem_short_desc := l_incom_desc;
806 px_qa_result_tbl(l_indx).problem_details_short := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_CHECK_INCOMPATIBILITY_SH, 'TARGET_ARTICLE', get_article_title(cr.target_cat_id));
807 px_qa_result_tbl(l_indx).problem_details := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_CHECK_INCOMPATIBILITY, 'SOURCE_ARTICLE', px_qa_result_tbl(l_indx).title, 'TARGET_ARTICLE', get_article_title(cr.target_cat_id));
808
809 ELSIF cr.relationship_type=G_ALTERNATE AND l_perform_alternate_qa ='Y' THEN
810 l_indx := px_qa_result_tbl.COUNT + 1;
811
812 px_qa_result_tbl(l_indx).error_record_type := G_ART_QA_TYPE;
813 px_qa_result_tbl(l_indx).article_id := cr.source_article_id;
814 px_qa_result_tbl(l_indx).deliverable_id := Null;
815 px_qa_result_tbl(l_indx).title := get_article_title(cr.source_cat_id);
816 px_qa_result_tbl(l_indx).section_name := get_section_title(cr.scn_id);
817
818 px_qa_result_tbl(l_indx).qa_code := G_CHECK_ALTERNATE;
819 px_qa_result_tbl(l_indx).message_name := G_OKC_CHECK_ALTERNATE;
820 px_qa_result_tbl(l_indx).suggestion := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC,G_OKC_CHECK_ALTERNATE_S);
821 px_qa_result_tbl(l_indx).error_severity := l_alternate_severity;
822 px_qa_result_tbl(l_indx).problem_short_desc := l_alternate_desc;
823 px_qa_result_tbl(l_indx).problem_details_short := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_CHECK_ALTERNATE_SH, 'ALTERNATE_ARTICLE', get_article_title(cr.target_cat_id));
824 px_qa_result_tbl(l_indx).problem_details := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_CHECK_ALTERNATE, 'SOURCE_ARTICLE', px_qa_result_tbl(l_indx).title, 'TARGET_ARTICLE', get_article_title(cr.target_cat_id));
825
826 END IF; -- IF cr.relationship_type=G_INCOMPATIBLE AND
827
828 END IF; -- IF (p_qa_mode =G_AMEND_QA and cr.amendment_operation_c
829
830 END LOOP; -- FOR cr IN l_get_incomp_alter_csr LOOP
831
832 /* expert commented out
833 -- validate expert clauses
834 IF (g_expert_enabled = 'Y') THEN
835 FOR cr IN l_get_incomp_alter_xprt_csr(l_current_org_id) LOOP
836
837 IF ( (cr.relationship_type = G_INCOMPATIBLE) AND (l_perform_incom_qa = 'Y')
838 AND do_validation(l_incom_severity, p_doc_type) )THEN
839 l_indx := px_qa_result_tbl.COUNT + 1;
840
841 px_qa_result_tbl(l_indx).error_record_type := G_EXP_QA_TYPE;
842 px_qa_result_tbl(l_indx).article_id := cr.source_article_id;
843 px_qa_result_tbl(l_indx).deliverable_id := Null;
844 px_qa_result_tbl(l_indx).title :=
845 get_xprt_article_title(cr.source_article_id);
846 px_qa_result_tbl(l_indx).section_name := Null;
847 px_qa_result_tbl(l_indx).qa_code := G_CHECK_RUL_INC;
848 px_qa_result_tbl(l_indx).message_name := G_OKC_CHECK_RUL_INC;
849 px_qa_result_tbl(l_indx).suggestion :=
850 OKC_TERMS_UTIL_PVT.Get_Message(G_OKC,G_OKC_CHECK_RUL_INC_S);
851 px_qa_result_tbl(l_indx).error_severity := l_incom_severity;
852 px_qa_result_tbl(l_indx).problem_short_desc := l_incom_desc;
853 px_qa_result_tbl(l_indx).problem_details_short :=
854 OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_CHECK_RUL_INC_SH);
855 px_qa_result_tbl(l_indx).problem_details :=
856 OKC_TERMS_UTIL_PVT.Get_Message(G_OKC,G_OKC_CHECK_RUL_INC,
857 'RULE', cr.rule_name,
858 'SOURCE_ARTICLE', get_xprt_article_title(cr.source_article_id),
859 'TARGET_ARTICLE', get_xprt_article_title(cr.target_article_id));
860
861 ELSIF ( (cr.relationship_type = G_ALTERNATE) AND (l_perform_alternate_qa = 'Y')
862 AND do_validation(l_alternate_severity, p_doc_type) )THEN
863 l_indx := px_qa_result_tbl.COUNT + 1;
864
865 px_qa_result_tbl(l_indx).error_record_type := G_EXP_QA_TYPE;
866 px_qa_result_tbl(l_indx).article_id := cr.source_article_id;
867 px_qa_result_tbl(l_indx).deliverable_id := Null;
868 px_qa_result_tbl(l_indx).title :=
869 get_xprt_article_title(cr.source_article_id);
870 px_qa_result_tbl(l_indx).section_name := Null;
871 px_qa_result_tbl(l_indx).qa_code := G_CHECK_RUL_ALT;
872 px_qa_result_tbl(l_indx).message_name := G_OKC_CHECK_RUL_ALT;
873 px_qa_result_tbl(l_indx).suggestion :=
874 OKC_TERMS_UTIL_PVT.Get_Message(G_OKC,G_OKC_CHECK_RUL_ALT_S);
875 px_qa_result_tbl(l_indx).error_severity := l_alternate_severity;
876 px_qa_result_tbl(l_indx).problem_short_desc := l_alternate_desc;
877 px_qa_result_tbl(l_indx).problem_details_short :=
878 OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_CHECK_RUL_ALT_SH);
879 px_qa_result_tbl(l_indx).problem_details :=
880 OKC_TERMS_UTIL_PVT.Get_Message(G_OKC,G_OKC_CHECK_RUL_ALT,
881 'RULE', cr.rule_name,
882 'SOURCE_ARTICLE', get_xprt_article_title(cr.source_article_id),
883 'TARGET_ARTICLE', get_xprt_article_title(cr.target_article_id));
884 NULL;
885 END IF;
886
887 END LOOP;
888 END IF; -- of IF (g_expert_enabled = 'Y') THEN
889 */
890
891 END IF;-- IF l_perform_incom_QA ='Y' OR l_perform_alternate_QA='Y'
892
893 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
894 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1600: Leaving Check_Incomp_and_alternate');
895 END IF;
896
897 EXCEPTION
898 WHEN FND_API.G_EXC_ERROR THEN
899 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
900 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7800: Leaving Check_Incomp_and_alternate : OKC_API.G_EXCEPTION_ERROR Exception');
901 END IF;
902
903 IF l_get_incomp_alter_csr%ISOPEN THEN
904 CLOSE l_get_incomp_alter_csr;
905 END IF;
906 /* expert commented out
907 IF l_get_incomp_alter_xprt_csr%ISOPEN THEN
908 CLOSE l_get_incomp_alter_xprt_csr;
909 END IF;
910 */
911
912 x_return_status := G_RET_STS_ERROR ;
913
914 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
915 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
916 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7900: Leaving Check_Incomp_and_alternate : OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
917 END IF;
918
919 IF l_get_incomp_alter_csr%ISOPEN THEN
920 CLOSE l_get_incomp_alter_csr;
921 END IF;
922 /* expert commented out
923 IF l_get_incomp_alter_xprt_csr%ISOPEN THEN
924 CLOSE l_get_incomp_alter_xprt_csr;
925 END IF;
926 */
927
928 x_return_status := G_RET_STS_UNEXP_ERROR ;
929
930 WHEN OTHERS THEN
931 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
932 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'8000: Leaving Check_Incomp_and_alternate because of EXCEPTION: '||sqlerrm);
933 END IF;
934
935 IF l_get_incomp_alter_csr%ISOPEN THEN
936 CLOSE l_get_incomp_alter_csr;
937 END IF;
938 /* expert commented out
939 IF l_get_incomp_alter_xprt_csr%ISOPEN THEN
940 CLOSE l_get_incomp_alter_xprt_csr;
941 END IF;
942 */
943
944 x_return_status := G_RET_STS_UNEXP_ERROR ;
945
946 END check_incomp_and_alternate;
947
948
949 -------------------------------------------
950 -- PROCEDURE check_lock_contract
951 -------------------------------------------
952 PROCEDURE check_lock_contract (
953 p_qa_mode IN VARCHAR2,
954 p_doc_type IN VARCHAR2,
955 p_doc_id IN NUMBER,
956
957 x_qa_result_tbl IN OUT NOCOPY qa_result_tbl_type,
958 x_qa_return_status IN OUT NOCOPY VARCHAR2,
959 x_return_status OUT NOCOPY VARCHAR2)
960 IS
961
962 l_api_name CONSTANT VARCHAR2(30) := 'check_lock_contract';
963 l_indx NUMBER;
964 l_lock_severity OKC_QA_ERRORS_T.Error_severity%TYPE;
965 l_lock_desc OKC_QA_ERRORS_T.PROBLEM_SHORT_DESC%TYPE;
966 l_lock_severity_flag OKC_QA_ERRORS_T.Error_severity%TYPE;
967
968 l_perform_lock_qa VARCHAR2(1);
969 l_lock_contract VARCHAR2(1);
970
971 CURSOR l_get_qa_detail_csr(p_qa_code VARCHAR2) IS
972 SELECT
973 decode(fnd.enabled_flag,'N','N','Y',decode(qa.enable_qa_yn,'N','N','Y'),'Y') perform_qa,
974 fnd.meaning qa_name,
975 nvl(qa.severity_flag,G_QA_STS_WARNING) severity_flag
976 FROM FND_LOOKUPS FND, OKC_DOC_QA_LISTS QA
977 WHERE QA.DOCUMENT_TYPE(+)=p_doc_type
978 AND QA.QA_CODE(+) = FND.LOOKUP_CODE
979 AND Fnd.LOOKUP_TYPE=G_QA_LOOKUP
980 AND fnd.lookup_code = p_qa_code;
981
982
983 CURSOR l_get_lock_error_warn_csr IS
984 SELECT nvl(qa.severity_flag,G_QA_STS_ERROR) severity_flag
985 FROM OKC_DOC_QA_LISTS QA
986 WHERE QA.DOCUMENT_TYPE(+)=p_doc_type
987 AND QA.QA_CODE(+) = G_OKC_CHECK_LOCK_CONTRACT;
988
989
990
991
992 BEGIN
993
994 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
995 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1500: Entered check_lock_contract');
996 END IF;
997
998 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
999 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: l_lock_contract : '||l_lock_contract);
1000 END IF;
1001
1002 -- Initialize API return status to success
1003 x_return_status := G_RET_STS_SUCCESS;
1004
1005 OPEN l_get_qa_detail_csr(G_OKC_CHECK_LOCK_CONTRACT);
1006 FETCH l_get_qa_detail_csr into l_perform_lock_qa,l_lock_desc,l_lock_severity;
1007 CLOSE l_get_qa_detail_csr;
1008
1009
1010 IF l_perform_lock_qa ='Y' THEN
1011
1012 IF (NOT do_validation(l_lock_severity, p_doc_type)) THEN
1013 -- validation is not required
1014 RETURN;
1015 END IF;
1016
1017 l_lock_contract := OKC_TERMS_UTIL_PVT.is_terms_locked(p_doc_type,p_doc_id);
1018 if(l_lock_contract = 'Y') THEN
1019
1020 l_indx := x_qa_result_tbl.COUNT + 1;
1021
1022 x_qa_result_tbl(l_indx).error_record_type := G_CONTRACT;
1023 x_qa_result_tbl(l_indx).article_id := null;
1024 x_qa_result_tbl(l_indx).deliverable_id := Null;
1025 x_qa_result_tbl(l_indx).title := null;
1026 x_qa_result_tbl(l_indx).section_name := null;
1027
1028 x_qa_result_tbl(l_indx).qa_code := G_OKC_CHECK_LOCK_CONTRACT ;
1029 x_qa_result_tbl(l_indx).message_name := G_OKC_CHECK_LOCK_CONTRACT ;
1030 x_qa_result_tbl(l_indx).suggestion := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC,G_OKC_CHECK_LOCK_CONTRACT_S);
1031 OPEN l_get_lock_error_warn_csr;
1032 FETCH l_get_lock_error_warn_csr into l_lock_severity_flag;
1033 IF (l_get_lock_error_warn_csr%NOTFOUND) THEN
1034 x_qa_result_tbl(l_indx).error_severity := 'E';
1035 x_qa_return_status := 'E';
1036 ELSE
1037 x_qa_result_tbl(l_indx).error_severity := l_lock_severity_flag;
1038 x_qa_return_status := l_lock_severity_flag;
1039 END IF;
1040 x_qa_result_tbl(l_indx).problem_short_desc := l_lock_desc;
1041 x_qa_result_tbl(l_indx).problem_details_short := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_CHECK_LOCK_CONTRACT_SH);
1042 x_qa_result_tbl(l_indx).problem_details := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_CHECK_LOCK_CONTRACT_SH);
1043 x_qa_result_tbl(l_indx).document_type := p_doc_type;
1044 x_qa_result_tbl(l_indx).document_id := p_doc_id;
1045 x_qa_result_tbl(l_indx).creation_date := sysdate;
1046
1047
1048 END IF; -- IF (l_lock_contract = 'Y')
1049
1050 END IF;-- IF l_perform_lock_QA ='Y'
1051
1052 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1053 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1600: Leaving Check_Incomp_and_alternate');
1054 END IF;
1055
1056 EXCEPTION
1057 WHEN FND_API.G_EXC_ERROR THEN
1058 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1059 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7800: Leaving Check_Incomp_and_alternate : OKC_API.G_EXCEPTION_ERROR Exception');
1060 END IF;
1061
1062 x_return_status := G_RET_STS_ERROR ;
1063
1064 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1065 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1066 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7900: Leaving Check_Incomp_and_alternate : OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
1067 END IF;
1068
1069 x_return_status := G_RET_STS_UNEXP_ERROR ;
1070
1071 WHEN OTHERS THEN
1072 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1073 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'8000: Leaving Check_Incomp_and_alternate because of EXCEPTION: '||sqlerrm);
1074 END IF;
1075
1076 x_return_status := G_RET_STS_UNEXP_ERROR ;
1077
1078 END check_lock_contract;
1079
1080
1081
1082 -------------------------------------------
1083 -- PROCEDURE check_contract_admin
1084 -------------------------------------------
1085 PROCEDURE check_contract_admin (
1086 p_qa_mode IN VARCHAR2,
1087 p_doc_type IN VARCHAR2,
1088 p_doc_id IN NUMBER,
1089
1090 x_qa_result_tbl IN OUT NOCOPY qa_result_tbl_type,
1091 x_qa_return_status IN OUT NOCOPY VARCHAR2,
1092 x_return_status OUT NOCOPY VARCHAR2)
1093 IS
1094
1095 l_api_name CONSTANT VARCHAR2(30) := 'check_contract_admin';
1096 l_indx NUMBER;
1097 l_ctrt_admin_severity OKC_QA_ERRORS_T.Error_severity%TYPE;
1098 l_ctrt_admin_desc OKC_QA_ERRORS_T.PROBLEM_SHORT_DESC%TYPE;
1099 l_ctrt_admin_severity_flag OKC_QA_ERRORS_T.Error_severity%TYPE;
1100
1101 l_perform_ctrt_admin_qa VARCHAR2(1);
1102 l_ctrt_admin VARCHAR2(1);
1103 l_ctrt_admin_emp VARCHAR2(1);
1104 l_ctrt_admin_valid_emp VARCHAR2(1);
1105
1106
1107 CURSOR l_get_qa_detail_csr(p_qa_code VARCHAR2) IS
1108 SELECT
1109 decode(fnd.enabled_flag,'N','N','Y',decode(qa.enable_qa_yn,'N','N','Y'),'Y') perform_qa,
1110 fnd.meaning qa_name,
1111 nvl(qa.severity_flag,G_QA_STS_WARNING) severity_flag
1112 FROM FND_LOOKUPS FND, OKC_DOC_QA_LISTS QA
1113 WHERE QA.DOCUMENT_TYPE(+)=p_doc_type
1114 AND QA.QA_CODE(+) = FND.LOOKUP_CODE
1115 AND Fnd.LOOKUP_TYPE=G_QA_LOOKUP
1116 AND fnd.lookup_code = p_qa_code;
1117
1118
1119
1120 CURSOR l_get_ctrt_admin_err_wrn_csr IS
1121 SELECT nvl(qa.severity_flag,G_QA_STS_ERROR) severity_flag
1122 FROM OKC_DOC_QA_LISTS QA
1123 WHERE QA.DOCUMENT_TYPE(+)=p_doc_type
1124 AND QA.QA_CODE(+) = G_OKC_CHECK_CONTRACT_ADMIN;
1125
1126 CURSOR contract_admin_exists is
1127 SELECT 'Y' FROM OKC_TEMPLATE_USAGES
1128 WHERE DOCUMENT_TYPE = P_DOC_TYPE
1129 AND DOCUMENT_ID = P_DOC_ID
1130 AND CONTRACT_ADMIN_ID IS NOT NULL;
1131
1132 -- Fix for 13435490 start
1133 CURSOR rep_contract_admin_exists is
1134 SELECT 'Y' FROM okc_rep_contracts_all
1135 WHERE CONTRACT_TYPE = P_DOC_TYPE
1136 AND CONTRACT_ID = P_DOC_ID
1137 AND OWNER_ID IS NOT NULL;
1138
1139 -- Fix for 13435490 end
1140
1141
1142 CURSOR contract_admin_emp is
1143 SELECT 'Y' FROM OKC_TEMPLATE_USAGES USG, fnd_user ctrtadm, PER_ALL_PEOPLE_F adminppl
1144 WHERE USG.DOCUMENT_TYPE = P_DOC_TYPE
1145 AND USG.DOCUMENT_ID = P_DOC_ID
1146 AND USG.CONTRACT_ADMIN_ID IS NOT NULL
1147 AND USG.contract_admin_id = ctrtadm.user_id
1148 AND ctrtadm.employee_id = adminppl.person_id
1149 and rownum < 2;
1150
1151 CURSOR contract_admin_valid_emp is
1152 SELECT 'Y' FROM OKC_TEMPLATE_USAGES USG, fnd_user ctrtadm, PER_ALL_PEOPLE_F adminppl
1153 WHERE USG.DOCUMENT_TYPE = P_DOC_TYPE
1154 AND USG.DOCUMENT_ID = P_DOC_ID
1155 AND USG.CONTRACT_ADMIN_ID IS NOT NULL
1156 AND USG.contract_admin_id = ctrtadm.user_id
1157 AND ctrtadm.employee_id = adminppl.person_id
1158 and adminppl.effective_start_date = adminppl.start_date;
1159
1160 BEGIN
1161
1162 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1163 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1500: Entered check_contract_admin');
1164 END IF;
1165
1166 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1167 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: l_ctrt_admin : '||l_ctrt_admin);
1168
1169 END IF;
1170
1171 -- Initialize API return status to success
1172 x_return_status := G_RET_STS_SUCCESS;
1173
1174 OPEN l_get_qa_detail_csr(G_OKC_CHECK_CONTRACT_ADMIN);
1175 FETCH l_get_qa_detail_csr into l_perform_ctrt_admin_qa,l_ctrt_admin_desc,l_ctrt_admin_severity_flag;
1176 CLOSE l_get_qa_detail_csr;
1177
1178
1179
1180 IF l_perform_ctrt_admin_qa ='Y' THEN
1181
1182 IF (NOT do_validation(l_ctrt_admin_severity, p_doc_type)) THEN
1183 -- validation is not required
1184 RETURN;
1185 END IF;
1186 l_ctrt_admin := 'N';
1187 -- Fix for 13435490 start
1188 IF(p_doc_type LIKE 'REP%') THEN
1189 OPEN rep_contract_admin_exists;
1190 fetch rep_contract_admin_exists into l_ctrt_admin;
1191 close rep_contract_admin_exists;
1192 ELSE -- Fix for 13435490 end
1193 OPEN contract_admin_exists;
1194 fetch contract_admin_exists into l_ctrt_admin;
1195 close contract_admin_exists;
1196 END IF;
1197 l_indx := x_qa_result_tbl.COUNT + 1; /* Added for Bug#15944671*/
1198
1199 if(l_ctrt_admin <> 'Y') THEN
1200 /* Commented for Bug#15944671*/
1201 --l_indx := x_qa_result_tbl.COUNT + 1;
1202
1203 x_qa_result_tbl(l_indx).error_record_type := G_CONTRACT;
1204 x_qa_result_tbl(l_indx).article_id := null;
1205 x_qa_result_tbl(l_indx).deliverable_id := Null;
1206 x_qa_result_tbl(l_indx).title := null;
1207 x_qa_result_tbl(l_indx).section_name := null;
1208
1209 x_qa_result_tbl(l_indx).qa_code := G_OKC_CHECK_CONTRACT_ADMIN ;
1210 x_qa_result_tbl(l_indx).message_name := G_OKC_CHECK_CTRT_ADMIN_S ;
1211 x_qa_result_tbl(l_indx).suggestion := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC,G_OKC_CHECK_CTRT_ADMIN_S);
1212 OPEN l_get_ctrt_admin_err_wrn_csr;
1213 FETCH l_get_ctrt_admin_err_wrn_csr into l_ctrt_admin_severity_flag;
1214 IF (l_get_ctrt_admin_err_wrn_csr%NOTFOUND) THEN
1215 x_qa_result_tbl(l_indx).error_severity := 'W';
1216 x_qa_return_status := 'W';
1217 ELSE
1218 x_qa_result_tbl(l_indx).error_severity := l_ctrt_admin_severity_flag;
1219 x_qa_return_status := l_ctrt_admin_severity_flag;
1220 END IF;
1221 x_qa_result_tbl(l_indx).problem_short_desc := l_ctrt_admin_desc;
1222 x_qa_result_tbl(l_indx).problem_details_short := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_CHECK_CTRT_ADMIN_SH);
1223 x_qa_result_tbl(l_indx).problem_details := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_CHECK_CTRT_ADMIN_SH);
1224 x_qa_result_tbl(l_indx).document_type := p_doc_type;
1225 x_qa_result_tbl(l_indx).document_id := p_doc_id;
1226 x_qa_result_tbl(l_indx).creation_date := sysdate;
1227
1228
1229 ELSE
1230 l_ctrt_admin_emp := 'N';
1231 OPEN contract_admin_emp;
1232 fetch contract_admin_emp into l_ctrt_admin_emp;
1233 close contract_admin_emp;
1234 if(l_ctrt_admin_emp <> 'Y') THEN
1235 x_qa_result_tbl(l_indx).error_record_type := G_CONTRACT;
1236 x_qa_result_tbl(l_indx).article_id := null;
1237 x_qa_result_tbl(l_indx).deliverable_id := Null;
1238 x_qa_result_tbl(l_indx).title := null;
1239 x_qa_result_tbl(l_indx).section_name := null;
1240
1241 x_qa_result_tbl(l_indx).qa_code := G_OKC_CHECK_CONTRACT_ADMIN ;
1242 x_qa_result_tbl(l_indx).message_name := G_OKC_CTRT_ADMIN_EMP_S ;
1243 x_qa_result_tbl(l_indx).suggestion := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC,G_OKC_ADMIN_EMP_SUGG);
1244 OPEN l_get_ctrt_admin_err_wrn_csr;
1245 FETCH l_get_ctrt_admin_err_wrn_csr into l_ctrt_admin_severity_flag;
1246 IF (l_get_ctrt_admin_err_wrn_csr%NOTFOUND) THEN
1247 x_qa_result_tbl(l_indx).error_severity := 'W';
1248 x_qa_return_status := 'W';
1249 ELSE
1250 x_qa_result_tbl(l_indx).error_severity := l_ctrt_admin_severity_flag;
1251 x_qa_return_status := l_ctrt_admin_severity_flag;
1252 END IF;
1253 x_qa_result_tbl(l_indx).problem_short_desc := l_ctrt_admin_desc;
1254 x_qa_result_tbl(l_indx).problem_details_short := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_CTRT_ADMIN_EMP_SH);
1255 x_qa_result_tbl(l_indx).problem_details := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_CTRT_ADMIN_EMP_DT);
1256 x_qa_result_tbl(l_indx).document_type := p_doc_type;
1257 x_qa_result_tbl(l_indx).document_id := p_doc_id;
1258 x_qa_result_tbl(l_indx).creation_date := sysdate;
1259
1260 ELSE
1261 l_ctrt_admin_valid_emp := 'N';
1262 OPEN contract_admin_valid_emp;
1263 fetch contract_admin_valid_emp into l_ctrt_admin_valid_emp;
1264 close contract_admin_valid_emp;
1265 if(l_ctrt_admin_valid_emp <> 'Y') THEN
1266 x_qa_result_tbl(l_indx).error_record_type := G_CONTRACT;
1267 x_qa_result_tbl(l_indx).article_id := null;
1268 x_qa_result_tbl(l_indx).deliverable_id := Null;
1269 x_qa_result_tbl(l_indx).title := null;
1270 x_qa_result_tbl(l_indx).section_name := null;
1271
1272 x_qa_result_tbl(l_indx).qa_code := G_OKC_CHECK_CONTRACT_ADMIN ;
1273 x_qa_result_tbl(l_indx).message_name := G_OKC_ADMIN_VALID_EMP_S ;
1274 x_qa_result_tbl(l_indx).suggestion := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC,G_OKC_ADMIN_VALID_EMP_SUGG);
1275 OPEN l_get_ctrt_admin_err_wrn_csr;
1276 FETCH l_get_ctrt_admin_err_wrn_csr into l_ctrt_admin_severity_flag;
1277 IF (l_get_ctrt_admin_err_wrn_csr%NOTFOUND) THEN
1278 x_qa_result_tbl(l_indx).error_severity := 'W';
1279 x_qa_return_status := 'W';
1280 ELSE
1281 x_qa_result_tbl(l_indx).error_severity := l_ctrt_admin_severity_flag;
1282 x_qa_return_status := l_ctrt_admin_severity_flag;
1283 END IF;
1284 x_qa_result_tbl(l_indx).problem_short_desc := l_ctrt_admin_desc;
1285 x_qa_result_tbl(l_indx).problem_details_short := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_ADMIN_VALID_EMP_SH);
1286 x_qa_result_tbl(l_indx).problem_details := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_ADMIN_VALID_EMP_DT);
1287 x_qa_result_tbl(l_indx).document_type := p_doc_type;
1288 x_qa_result_tbl(l_indx).document_id := p_doc_id;
1289 x_qa_result_tbl(l_indx).creation_date := sysdate;
1290
1291 END IF; -- if(l_ctrt_admin_valid_emp <> 'Y')
1292
1293 END IF; -- if(l_ctrt_admin_emp <> 'Y')
1294
1295
1296
1297 END IF; -- IF (l_ctrt_admin = 'Y')
1298
1299 END IF;-- IF l_perform_ctrt_admin_qa ='Y'
1300
1301 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1302 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1600: Leaving Check_Incomp_and_alternate');
1303 END IF;
1304
1305 EXCEPTION
1306 WHEN FND_API.G_EXC_ERROR THEN
1307 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1308 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7800: Leaving Check_Incomp_and_alternate : OKC_API.G_EXCEPTION_ERROR Exception');
1309 END IF;
1310
1311 x_return_status := G_RET_STS_ERROR ;
1312
1313 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1314 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1315 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7900: Leaving Check_Incomp_and_alternate : OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
1316 END IF;
1317
1318 x_return_status := G_RET_STS_UNEXP_ERROR ;
1319
1320 WHEN OTHERS THEN
1321 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1322 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'8000: Leaving Check_Incomp_and_alternate because of EXCEPTION: '||sqlerrm);
1323 END IF;
1324
1325 x_return_status := G_RET_STS_UNEXP_ERROR ;
1326
1327 END check_contract_admin;
1328
1329
1330
1331
1332 -------------------------------------------
1333 -- PROCEDURE check_duplicate_articles
1334 -------------------------------------------
1335 /* API to do QA Check for duplicate article */
1336
1337 PROCEDURE check_duplicate_articles (
1338 p_qa_mode IN VARCHAR2,
1339 p_doc_type IN VARCHAR2,
1340 p_doc_id IN NUMBER,
1341
1342 px_qa_result_tbl IN OUT NOCOPY qa_result_tbl_type,
1343 x_return_status OUT NOCOPY VARCHAR2)
1344 IS
1345
1346 l_api_name CONSTANT VARCHAR2(30) := 'check_duplicate_articles';
1347 l_indx NUMBER;
1348 l_error_count NUMBER := 0;
1349 l_severity OKC_QA_ERRORS_T.ERROR_SEVERITY%TYPE;
1350 l_desc OKC_QA_ERRORS_T.PROBLEM_SHORT_DESC%TYPE;
1351 l_perform_duplicate_qa Varchar2(1);
1352
1353 /* expert commented out
1354 -- new cursor to check for xprt articles
1355 CURSOR l_get_dup_xprt_csr IS
1356 SELECT
1357 Rule.clause_id xprt_article_id,
1358 Rule.rule_id rule_id,
1359 Rule.rule_name rule_name
1360 FROM OKC_XPRT_CLAUSES_V rule,
1361 OKC_K_ARTICLES_B kart
1362 WHERE rule.template_id = p_doc_id
1363 AND kart.document_type = p_doc_type
1364 AND kart.document_id = p_doc_id
1365 AND rule.clause_id =kart.sav_sae_id;
1366 */
1367
1368 BEGIN
1369
1370 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1371 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1500: Entered check_duplicate_articles');
1372 END IF;
1373
1374 get_qa_code_detail(p_qa_code => 'CHECK_DUPLICATE',
1375 x_perform_qa => l_perform_duplicate_qa,
1376 x_qa_name => l_desc,
1377 x_severity_flag => l_severity,
1378 x_return_status => x_return_status);
1379 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1380 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1381 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1382 RAISE FND_API.G_EXC_ERROR ;
1383 END IF;
1384
1385 IF l_perform_duplicate_qa ='Y' THEN
1386
1387 IF (NOT do_validation(l_severity, p_doc_type)) THEN
1388 -- validation is not required
1389 RETURN;
1390 END IF;
1391
1392 IF l_article_tbl.COUNT > 0 THEN
1393
1394 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1395 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1600: totally:'||l_article_tbl.COUNT||' articles to check');
1396 END IF;
1397
1398 FOR i IN l_article_tbl.FIRST..l_article_tbl.LAST LOOP
1399
1400 --Bug 4128923
1401 l_error_count := 0;
1402 IF( p_qa_mode=G_AMEND_QA and l_article_tbl(i).amendment_operation_code IS NOT NULL
1403 OR p_qa_mode<>G_AMEND_QA )
1404 and nvl(l_article_tbl(i).amendment_operation_code,'?')<>G_AMEND_CODE_DELETED THEN
1405
1406 --Bug 4128923 l_error_count := 0;
1407
1408 FOR k IN l_article_tbl.FIRST..l_article_tbl.LAST LOOP
1409 --IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1410 -- FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1600: i:'||i||', k:'||k);
1411 --END IF;
1412
1413 IF nvl(l_article_tbl(k).amendment_operation_code,'?')<>G_AMEND_CODE_DELETED
1414 AND Nvl(l_article_tbl(k).std_art_id,l_article_tbl(k).article_id)
1415 =Nvl(l_article_tbl(i).std_art_id,l_article_tbl(i).article_id) THEN
1416
1417 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1418 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1600: Dupicate found - i:'||i||', k:'||k);
1419 END IF;
1420 l_error_count := l_error_count + 1;
1421 END IF;
1422 END LOOP; -- FOR k IN l_article_tbl1.FIRST..l_article_tbl1.LAST LOOP
1423 END IF; -- IF ( ( p_qa_mode =G_AMEND_QA
1424
1425 IF l_error_count > 1 THEN
1426 l_indx := px_qa_result_tbl.COUNT + 1;
1427
1428 px_qa_result_tbl(l_indx).error_record_type := G_ART_QA_TYPE;
1429 px_qa_result_tbl(l_indx).article_id := l_article_tbl(i).article_id;
1430 px_qa_result_tbl(l_indx).deliverable_id := Null;
1431 px_qa_result_tbl(l_indx).title := get_article_title(l_article_tbl(i).id);
1432 px_qa_result_tbl(l_indx).section_name := get_section_title(l_article_tbl(i).scn_id);
1433 px_qa_result_tbl(l_indx).qa_code := G_CHECK_DUPLICATE;
1434 px_qa_result_tbl(l_indx).message_name := G_OKC_CHECK_DUPLICATE;
1435 px_qa_result_tbl(l_indx).suggestion := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC,G_OKC_CHECK_DUPLICATE_S);
1436
1437 px_qa_result_tbl(l_indx).error_severity := l_severity;
1438 px_qa_result_tbl(l_indx).problem_short_desc := l_desc;
1439 px_qa_result_tbl(l_indx).problem_details_short := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_CHECK_DUPLICATE_SH, 'NUMBER',l_error_count);
1440 px_qa_result_tbl(l_indx).problem_details := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_CHECK_DUPLICATE, 'ARTICLE', px_qa_result_tbl(l_indx).title, 'NUMBER',l_error_count);
1441 END IF; -- IF l_error_count > 1 THEN
1442
1443 /* expert commented out
1444 IF ((g_expert_enabled = 'Y') and (p_doc_type = 'TEMPLATE')) THEN
1445
1446 FOR cr IN l_get_dup_xprt_csr LOOP
1447 l_indx := px_qa_result_tbl.COUNT + 1;
1448
1449 px_qa_result_tbl(l_indx).error_record_type := G_EXP_QA_TYPE;
1450 px_qa_result_tbl(l_indx).article_id := cr.xprt_article_id;
1451 px_qa_result_tbl(l_indx).deliverable_id := Null;
1452 px_qa_result_tbl(l_indx).title :=
1453 get_xprt_article_title(cr.xprt_article_id);
1454 px_qa_result_tbl(l_indx).section_name := Null;
1455 px_qa_result_tbl(l_indx).qa_code := G_CHECK_RUL_DUP;
1456 px_qa_result_tbl(l_indx).message_name := G_OKC_CHECK_RUL_DUP;
1457 px_qa_result_tbl(l_indx).suggestion :=
1458 OKC_TERMS_UTIL_PVT.Get_Message(G_OKC,G_OKC_CHECK_RUL_DUP_S);
1459 px_qa_result_tbl(l_indx).error_severity := l_severity;
1460 px_qa_result_tbl(l_indx).problem_short_desc := l_desc;
1461 px_qa_result_tbl(l_indx).problem_details_short :=
1462 OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_CHECK_RUL_DUP_SH);
1463 px_qa_result_tbl(l_indx).problem_details :=
1464 OKC_TERMS_UTIL_PVT.Get_Message(G_OKC,G_OKC_CHECK_RUL_DUP,
1465 'RULE', cr.rule_name,
1466 'XPRT_ARTICLE', get_xprt_article_title(cr.xprt_article_id));
1467 END LOOP;
1468
1469 END IF; -- of IF ((g_expert_enabled = 'Y') and (p_doc_type = 'TEMPLATE')) THEN
1470 */
1471
1472 END LOOP; -- FOR i IN l_article_tbl.FIRST..l_article_tbl.LAST LOOP
1473
1474 END IF; -- IF l_article_tbl.COUNT > 0 THEN
1475
1476 END IF; -- IF l_perform_duplicate_qa ='Y' THEN
1477
1478 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1479 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1600: Leaving check_duplicate_articles');
1480 END IF;
1481
1482 EXCEPTION
1483 WHEN FND_API.G_EXC_ERROR THEN
1484 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1485 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7800: Leaving check_duplicate_articles : OKC_API.G_EXCEPTION_ERROR Exception');
1486 END IF;
1487
1488 /* expert commented out
1489 IF l_get_dup_xprt_csr%ISOPEN THEN
1490 CLOSE l_get_dup_xprt_csr;
1491 END IF;
1492 */
1493
1494 x_return_status := G_RET_STS_ERROR ;
1495
1496 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1497 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1498 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7900: Leaving check_duplicate_articles : OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
1499 END IF;
1500
1501 /* expert commented out
1502 IF l_get_dup_xprt_csr%ISOPEN THEN
1503 CLOSE l_get_dup_xprt_csr;
1504 END IF;
1505 */
1506 x_return_status := G_RET_STS_UNEXP_ERROR ;
1507
1508 WHEN OTHERS THEN
1509 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1510 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'8000: Leaving check_duplicate_articles because of EXCEPTION: '||sqlerrm);
1511 END IF;
1512
1513 /* expert commented out
1514 IF l_get_dup_xprt_csr%ISOPEN THEN
1515 CLOSE l_get_dup_xprt_csr;
1516 END IF;
1517 */
1518
1519 x_return_status := G_RET_STS_UNEXP_ERROR ;
1520 END check_duplicate_articles;
1521
1522 -------------------------------------------
1523 -- PROCEDURE check_var_doc_type_usage
1524 -------------------------------------------
1525 /* API to do QA Check variable Doc type usage */
1526
1527 PROCEDURE check_var_doc_type_usage (
1528 p_qa_mode IN VARCHAR2,
1529 p_doc_type IN VARCHAR2,
1530 p_doc_id IN NUMBER,
1531 px_qa_result_tbl IN OUT NOCOPY qa_result_tbl_type,
1532 x_return_status OUT NOCOPY VARCHAR2)
1533 IS
1534
1535 l_api_name CONSTANT VARCHAR2(30) := 'G_Check_var_doc_type_usage';
1536 l_indx NUMBER;
1537 l_severity OKC_QA_ERRORS_T.ERROR_SEVERITY%TYPE;
1538 l_desc OKC_QA_ERRORS_T.PROBLEM_SHORT_DESC%TYPE;
1539 l_perform_var_usg_qa VARCHAR2(1);
1540
1541 /* 11.5.10+ modified cursor - see below
1542 CURSOR l_check_usage_csr IS
1543 SELECT kart.id id,
1544 kart.sav_sae_id article_id,
1545 kart.article_version_id article_version_id,
1546 kart.amendment_operation_code amendment_operation_code,
1547 kart.scn_id scn_id,
1548 var.variable_code variable_code,
1549 busvar.variable_name variable_name,
1550 busdoc.name doc_type
1551 FROM okc_k_articles_b kart,
1552 okc_k_art_variables var,
1553 okc_bus_variables_vl busvar,
1554 okc_bus_doc_types_v busdoc
1555 WHERE kart.document_type=p_doc_type
1556 and kart.document_id=p_doc_id
1557 and var.cat_id=kart.id
1558 and var.variable_type IN ('S','D')
1559 and var.variable_code=busvar.variable_code
1560 and busdoc.document_type = kart.document_type
1561 and not exists (SELECT 'x' from OKC_VARIABLE_DOC_TYPES vo
1562 WHERE var.variable_code=vo.variable_code
1563 and doc_type=p_doc_type)
1564 and p_doc_type<>G_TMPL_DOC_TYPE
1565 and nvl(kart.amendment_operation_code,'?')<>G_AMEND_CODE_DELETED
1566 and nvl(kart.summary_amend_operation_code,'?')<>G_AMEND_CODE_DELETED
1567 UNION ALL
1568 SELECT kart.id id,
1569 kart.sav_sae_id article_id,
1570 vers.article_version_id article_version_id,
1571 kart.amendment_operation_code amendment_operation_code,
1572 kart.scn_id scn_id,
1573 var.variable_code variable_code,
1574 busvar.variable_name variable_name,
1575 busdoc.name doc_type
1576 FROM okc_k_articles_b kart,
1577 okc_article_versions vers,
1578 okc_allowed_tmpl_usages allwd ,
1579 okc_article_variables var,
1580 okc_bus_variables_vl busvar ,
1581 okc_bus_doc_types_v busdoc
1582 WHERE kart.document_type=G_TMPL_DOC_TYPE
1583 and kart.document_id=p_doc_id
1584 and allwd.template_id=kart.document_id
1585 and kart.sav_sae_id=vers.article_id
1586 and vers.article_status='APPROVED'
1587 AND vers.start_date = (SELECT max(start_date)
1588 FROM OKC_ARTICLE_VERSIONS
1589 WHERE article_id= kart.sav_sae_id
1590 AND article_status='APPROVED')
1591 and vers.article_version_id=var.article_version_id
1592 and busvar.variable_code=var.variable_code
1593 and busvar.variable_type IN ('S','D')
1594 and busdoc.document_type = allwd.document_type
1595 and not exists (SELECT 'x' FROM OKC_VARIABLE_DOC_TYPES vo
1596 WHERE var.variable_code=vo.variable_code
1597 and doc_type=allwd.document_type)
1598 and p_doc_type=G_TMPL_DOC_TYPE
1599 and nvl(kart.amendment_operation_code,'?')<>G_AMEND_CODE_DELETED
1600 and nvl(kart.summary_amend_operation_code,'?')<>G_AMEND_CODE_DELETED;
1601 */
1602
1603 /* 11.5.10+ modified cursor definition
1604 1. no change for non TEMPLATE doc types
1605 2. for TEMPLATE doc types, get selected draft versions also
1606 */
1607 --Repository Enhancement 12.1 (For Validate Action)
1608 p_rep_doc_type varchar2(30);
1609 l_intent okc_bus_doc_types_b.intent%type;
1610 -- Repository Enhancement 12.1 Ends (For Validate Action)
1611
1612 CURSOR l_check_usage_csr IS
1613 -- no change for non TEMPLATE document types
1614 SELECT kart.id id,
1615 kart.sav_sae_id article_id,
1616 kart.article_version_id article_version_id,
1617 kart.amendment_operation_code amendment_operation_code,
1618 kart.scn_id scn_id,
1619 var.variable_code variable_code,
1620 busvar.variable_name variable_name,
1621 busdoc.name doc_type
1622 FROM okc_k_articles_b kart,
1623 okc_k_art_variables var,
1624 okc_bus_variables_vl busvar,
1625 okc_bus_doc_types_v busdoc
1626 WHERE kart.document_type=p_doc_type
1627 and kart.document_id=p_doc_id
1628 and var.cat_id=kart.id
1629 and var.variable_type IN ('S','D')
1630 and var.variable_code=busvar.variable_code
1631 --Repository Enhancement 12.1 (For Validate Action)
1632 and busdoc.document_type = kart.document_type
1633 -- and busdoc.document_type = p_rep_doc_type
1634 and not exists (SELECT 'x' from OKC_VARIABLE_DOC_TYPES vo
1635 WHERE var.variable_code=vo.variable_code
1636 --Repository Enhancement 12.1 (For Validate Action)
1637 -- and doc_type=p_doc_type)
1638 and doc_type= p_rep_doc_type)
1639 and p_doc_type<>G_TMPL_DOC_TYPE
1640 and nvl(kart.amendment_operation_code,'?')<>G_AMEND_CODE_DELETED
1641 and nvl(kart.summary_amend_operation_code,'?')<>G_AMEND_CODE_DELETED
1642 UNION ALL
1643 -- change to get draft/rejected clause versions also
1644 SELECT kart.id id,
1645 kart.sav_sae_id article_id,
1646 vers.article_version_id article_version_id,
1647 kart.amendment_operation_code amendment_operation_code,
1648 kart.scn_id scn_id,
1649 var.variable_code variable_code,
1650 busvar.variable_name variable_name,
1651 busdoc.name doc_type
1652 FROM okc_k_articles_b kart,
1653 okc_article_versions vers,
1654 okc_allowed_tmpl_usages allwd ,
1655 okc_article_variables var,
1656 okc_bus_variables_vl busvar ,
1657 okc_bus_doc_types_v busdoc
1658 WHERE kart.document_type=G_TMPL_DOC_TYPE
1659 and kart.document_id=p_doc_id
1660 and allwd.template_id=kart.document_id
1661 and kart.sav_sae_id=vers.article_id
1662 -- new logic for determining clause version
1663 and vers.article_version_id = OKC_TERMS_UTIL_PVT.get_latest_tmpl_art_version_id(
1664 kart.sav_sae_id,
1665 g_start_date,
1666 g_end_date,
1667 g_status_code,
1668 p_doc_type,
1669 p_doc_id)
1670 /* existing logic of determining clause version
1671 and vers.article_status='APPROVED'
1672 AND vers.start_date = (select max(start_date)
1673 FROM OKC_ARTICLE_VERSIONS
1674 WHERE article_id= kart.sav_sae_id
1675 AND article_status='APPROVED')
1676 */
1677 and vers.article_version_id=var.article_version_id
1678 and busvar.variable_code=var.variable_code
1679 and busvar.variable_type IN ('S','D')
1680 and busdoc.document_type = allwd.document_type
1681 and not exists ((SELECT 'x' FROM OKC_VARIABLE_DOC_TYPES vo
1682 WHERE var.variable_code=vo.variable_code
1683 and doc_type=allwd.document_type)
1684 UNION ALL
1685 (SELECT 'x' FROM OKC_VARIABLE_DOC_TYPES vo
1686 WHERE var.variable_code=vo.variable_code
1687 and doc_type like '%REPOSITORY%'))
1688 and p_doc_type=G_TMPL_DOC_TYPE
1689 and nvl(kart.amendment_operation_code,'?')<>G_AMEND_CODE_DELETED
1690 and nvl(kart.summary_amend_operation_code,'?')<>G_AMEND_CODE_DELETED;
1691
1692 -- Repository Enhancement 12.1 (For Validate Action)
1693 CURSOR l_get_intent is
1694 SELECT intent
1695 FROM okc_bus_doc_types_b
1696 WHERE document_type = p_doc_type;
1697 -- Repository Enhancement 12.1 Ends (For Validate Action)
1698
1699
1700 /* expert commented out
1701 CURSOR l_xprt_check_usage_csr IS
1702 SELECT
1703 Rule.clause_id xprt_article_id,
1704 Rule.rule_id rule_id,
1705 Rule.rule_name rule_name,
1706 var.variable_code variable_code,
1707 busvar.variable_name variable_name,
1708 busdoc.name doc_type
1709 FROM okc_xprt_clauses_v rule,
1710 okc_allowed_tmpl_usages allwd ,
1711 okc_article_versions vers,
1712 okc_article_variables var,
1713 okc_bus_variables_vl busvar ,
1714 okc_bus_doc_types_v busdoc
1715 WHERE rule.template_id = p_doc_id
1716 and allwd.template_id=p_doc_id
1717 and vers.article_id = rule.clause_id
1718 and vers.article_version_id =
1719 OKC_TERMS_UTIL_PVT.get_latest_tmpl_art_version_id(
1720 rule.clause_id,
1721 g_start_date,
1722 g_end_date,
1723 g_status_code,
1724 p_doc_type,
1725 p_doc_id)
1726 and var.article_version_id = vers.article_version_id
1727 and busvar.variable_code = var.variable_code
1728 and busvar.variable_type IN ('S','D')
1729 and busdoc.document_type = allwd.document_type
1730 and not exists (select 'x' from OKC_VARIABLE_DOC_TYPES vo
1731 where vo.variable_code=var.variable_code
1732 and vo.doc_type=allwd.document_type) ;
1733 */
1734
1735 BEGIN
1736
1737 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1738 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1500: Entered Check_var_doc_type_usage');
1739 END IF;
1740
1741 get_qa_code_detail(p_qa_code => G_CHECK_VAR_USAGE,
1742 x_perform_qa => l_perform_var_usg_qa,
1743 x_qa_name => l_desc,
1744 x_severity_flag => l_severity,
1745 x_return_status => x_return_status);
1746 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1747 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1748 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1749 RAISE FND_API.G_EXC_ERROR ;
1750 END IF;
1751
1752 IF l_perform_var_usg_qa ='Y' THEN
1753
1754 IF (NOT do_validation(l_severity, p_doc_type)) THEN
1755 -- validation is not required
1756 RETURN;
1757 END IF;
1758
1759 -- Repository Enhancement 12.1 (For Validate Action)
1760 OPEN l_get_intent;
1761 FETCH l_get_intent into l_intent;
1762 CLOSE l_get_intent;
1763 IF SubStr(p_doc_type,1,3) = 'REP' and l_intent = 'S' THEN
1764 p_rep_doc_type:='OKC_REPOSITORY_SELL';
1765 ELSIF SubStr(p_doc_type,1,3) = 'REP' and l_intent = 'B' THEN
1766 p_rep_doc_type:='OKC_REPOSITORY_BUY';
1767 ELSE
1768 p_rep_doc_type:=p_doc_type;
1769 END IF;
1770 -- Repository Enhancement 12.1 Ends(For Validate Action)
1771
1772 FOR cr IN l_check_usage_csr LOOP
1773
1774 IF ( ( p_qa_mode =G_AMEND_QA
1775 and cr.amendment_operation_code IS NOT NULL
1776 )
1777 OR p_qa_mode<>G_AMEND_QA) THEN
1778
1779 l_indx := px_qa_result_tbl.COUNT + 1;
1780
1781 px_qa_result_tbl(l_indx).error_record_type:= G_ART_QA_TYPE;
1782 px_qa_result_tbl(l_indx).article_id := cr.article_id;
1783 px_qa_result_tbl(l_indx).deliverable_id := Null;
1784 px_qa_result_tbl(l_indx).title := get_article_title(cr.id);
1785 px_qa_result_tbl(l_indx).section_name := get_section_title(cr.scn_id);
1786 px_qa_result_tbl(l_indx).qa_code := G_CHECK_VAR_USAGE;
1787 px_qa_result_tbl(l_indx).message_name := G_OKC_CHECK_VAR_USAGE;
1788 px_qa_result_tbl(l_indx).suggestion := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC,G_OKC_CHECK_VAR_USAGE_S);
1789
1790 px_qa_result_tbl(l_indx).error_severity := l_severity;
1791 px_qa_result_tbl(l_indx).problem_short_desc := l_desc;
1792 px_qa_result_tbl(l_indx).problem_details_short := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_CHECK_VAR_USAGE_SH, 'VARIABLE',cr.variable_name,'DOCUMENT_TYPE',cr.doc_type);
1793 px_qa_result_tbl(l_indx).problem_details := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_CHECK_VAR_USAGE, 'VARIABLE', cr.variable_name, 'DOCUMENT_TYPE',cr.doc_type);
1794 END IF; -- IF ( ( p_qa_mode =G_AMEND_QA
1795
1796 END LOOP; -- FOR cr IN l_check_usage_csr LOOP
1797
1798 /* expert commented out
1799 IF ((g_expert_enabled = 'Y') AND (p_doc_type = 'TEMPLATE')) THEN
1800 FOR cr IN l_xprt_check_usage_csr LOOP
1801 l_indx := px_qa_result_tbl.COUNT + 1;
1802
1803 px_qa_result_tbl(l_indx).error_record_type := G_EXP_QA_TYPE;
1804 px_qa_result_tbl(l_indx).article_id := cr.xprt_article_id;
1805 px_qa_result_tbl(l_indx).deliverable_id := Null;
1806 px_qa_result_tbl(l_indx).title :=
1807 get_xprt_article_title(cr.xprt_article_id);
1808 px_qa_result_tbl(l_indx).section_name := Null;
1809
1810 px_qa_result_tbl(l_indx).qa_code := G_CHECK_RUL_VAR_DOC;
1811 px_qa_result_tbl(l_indx).message_name := G_OKC_CHECK_RUL_VAR_DOC;
1812 px_qa_result_tbl(l_indx).suggestion :=
1813 OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_CHECK_RUL_VAR_DOC_S);
1814 px_qa_result_tbl(l_indx).error_severity := l_severity;
1815 px_qa_result_tbl(l_indx).problem_short_desc := l_desc;
1816 px_qa_result_tbl(l_indx).problem_details_short :=
1817 OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_CHECK_RUL_VAR_DOC_SH);
1818 px_qa_result_tbl(l_indx).problem_details :=
1819 OKC_TERMS_UTIL_PVT.Get_Message(G_OKC,G_OKC_CHECK_RUL_VAR_DOC,
1820 'RULE', cr.rule_name,
1821 'XPRT_ARTICLE', get_xprt_article_title(cr.xprt_article_id),
1822 'DOCUMENT_TYPE', cr.doc_type);
1823 END LOOP;
1824 END IF; -- of IF ((g_expert_enabled = 'Y') AND (p_doc_type = 'TEMPLATE'))
1825 */
1826
1827 END IF; -- IF l_perform_var_usg_qa ='Y' THEN
1828
1829
1830 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1831 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1600: Leaving Check_var_doc_type_usage');
1832 END IF;
1833
1834 EXCEPTION
1835 WHEN FND_API.G_EXC_ERROR THEN
1836 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1837 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7800: Leaving Check_var_doc_type_usage : OKC_API.G_EXCEPTION_ERROR Exception');
1838 END IF;
1839
1840 IF l_check_usage_csr%ISOPEN THEN
1841 CLOSE l_check_usage_csr ;
1842 END IF;
1843
1844 /* expert commented out
1845 IF l_xprt_check_usage_csr%ISOPEN THEN
1846 CLOSE l_xprt_check_usage_csr ;
1847 END IF;
1848 */
1849
1850 x_return_status := G_RET_STS_ERROR ;
1851
1852 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1853 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1854 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7900: Leaving Check_var_doc_type_usage : OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
1855 END IF;
1856
1857 IF l_check_usage_csr%ISOPEN THEN
1858 CLOSE l_check_usage_csr ;
1859 END IF;
1860
1861 /* expert commented out
1862 IF l_xprt_check_usage_csr%ISOPEN THEN
1863 CLOSE l_xprt_check_usage_csr ;
1864 END IF;
1865 */
1866
1867 x_return_status := G_RET_STS_UNEXP_ERROR ;
1868
1869 WHEN OTHERS THEN
1870 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1871 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'8000: Leaving Check_var_doc_type_usage because of EXCEPTION: '||sqlerrm);
1872 END IF;
1873
1874 IF l_check_usage_csr%ISOPEN THEN
1875 CLOSE l_check_usage_csr ;
1876 END IF;
1877
1878 /* expert commented out
1879 IF l_xprt_check_usage_csr%ISOPEN THEN
1880 CLOSE l_xprt_check_usage_csr ;
1881 END IF;
1882 */
1883
1884 x_return_status := G_RET_STS_UNEXP_ERROR ;
1885
1886 END check_var_doc_type_usage;
1887
1888 -------------------------------------------
1889 -- PROCEDURE check_user_vars_with_procs
1890 -------------------------------------------
1891
1892 PROCEDURE check_user_vars_with_procs (
1893 p_doc_type IN VARCHAR2,
1894 p_doc_id IN NUMBER,
1895 px_qa_result_tbl IN OUT NOCOPY qa_result_tbl_type,
1896 x_return_status OUT NOCOPY VARCHAR2)
1897 IS
1898
1899 l_api_name CONSTANT VARCHAR2(30) := 'check_user_vars_with_procs';
1900 l_indx NUMBER;
1901 l_int_severity OKC_QA_ERRORS_T.ERROR_SEVERITY%TYPE;
1902 l_int_desc OKC_QA_ERRORS_T.PROBLEM_SHORT_DESC%TYPE;
1903 l_perform_int_qa VARCHAR2(1);
1904
1905 l_variable_value VARCHAR2(2500) := NULL;
1906 l_previous_var_code okc_bus_variables_b.variable_code%TYPE := '-99';
1907 l_return_status VARCHAR2(10);
1908 l_msg_count NUMBER;
1909 l_msg_data VARCHAR2(2500);
1910
1911
1912 CURSOR csr_get_udv_with_procs IS
1913 SELECT VB.variable_code,
1914 KA.id,
1915 KA.sav_sae_id article_id,
1916 KA.scn_id,
1917 VT.variable_name
1918 FROM okc_k_articles_b KA,
1919 okc_k_art_variables KV,
1920 okc_bus_variables_b VB,
1921 okc_bus_variables_tl VT
1922 WHERE VB.variable_code = KV.variable_code
1923 AND KA.id = KV.cat_id
1924 AND VB.variable_code = VT.variable_code
1925 AND VB.variable_source = 'P'
1926 AND KA.document_type = p_doc_type
1927 AND KA.document_id = p_doc_id
1928 AND language = USERENV('LANG')
1929 ORDER BY VB.variable_code;
1930
1931
1932 BEGIN
1933 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1934 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: Entered check_user_vars_with_procs');
1935 END IF;
1936
1937 get_qa_code_detail(p_qa_code => G_CHECK_INT_VAR_VALUE,
1938 x_perform_qa => l_perform_int_qa,
1939 x_qa_name => l_int_desc,
1940 x_severity_flag => l_int_severity,
1941 x_return_status => x_return_status);
1942 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1943 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1944 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1945 RAISE FND_API.G_EXC_ERROR ;
1946 END IF;
1947
1948
1949 FOR csr_udv_with_procs_rec IN csr_get_udv_with_procs LOOP
1950
1951 /* Get the variable value */
1952 IF l_previous_var_code <> csr_udv_with_procs_rec.variable_code THEN
1953
1954 l_variable_value := NULL;
1955
1956 OKC_TERMS_UTIL_PVT.get_udv_with_proc_value (
1957 p_document_type => p_doc_type,
1958 p_document_id => p_doc_id,
1959 p_variable_code => csr_udv_with_procs_rec.variable_code,
1960 p_output_error => FND_API.G_FALSE,
1961 x_variable_value => l_variable_value,
1962 x_return_status => l_return_status,
1963 x_msg_data => l_msg_data,
1964 x_msg_count => l_msg_count );
1965
1966 END IF;
1967
1968 /* Add to the qa results, if the variable is unresolved */
1969 IF l_variable_value IS NULL THEN
1970
1971 l_indx := px_qa_result_tbl.COUNT + 1;
1972
1973 px_qa_result_tbl(l_indx).error_record_type:= G_ART_QA_TYPE;
1974 px_qa_result_tbl(l_indx).article_id := csr_udv_with_procs_rec.article_id;
1975 px_qa_result_tbl(l_indx).deliverable_id := NULL;
1976 px_qa_result_tbl(l_indx).title := get_article_title(csr_udv_with_procs_rec.id);
1977 px_qa_result_tbl(l_indx).qa_code := G_CHECK_INT_VAR_VALUE;
1978 px_qa_result_tbl(l_indx).message_name := G_OKC_CHECK_INT_VAR_VALUE;
1979 px_qa_result_tbl(l_indx).suggestion := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC,G_OKC_CHECK_INT_VAR_VALUE_S);
1980 px_qa_result_tbl(l_indx).section_name := get_section_title(csr_udv_with_procs_rec.scn_id);
1981
1982 px_qa_result_tbl(l_indx).error_severity := l_INT_severity;
1983 px_qa_result_tbl(l_indx).problem_short_desc := l_INT_desc;
1984 px_qa_result_tbl(l_indx).problem_details_short := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_CHECK_INT_VAR_VALUE_SH, 'VARIABLE', csr_udv_with_procs_rec.variable_name);
1985 px_qa_result_tbl(l_indx).problem_details := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_CHECK_INT_VAR_VALUE, 'VARIABLE', csr_udv_with_procs_rec.variable_name,'ARTICLE',px_qa_result_tbl(l_indx).title);
1986
1987 END IF;
1988
1989 l_previous_var_code := csr_udv_with_procs_rec.variable_code;
1990
1991 END LOOP;
1992
1993
1994 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1995 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'600: Leaving check_user_vars_with_procs');
1996 END IF;
1997
1998 EXCEPTION
1999 WHEN FND_API.G_EXC_ERROR THEN
2000 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2001 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'1100: Leaving check_user_vars_with_procs : OKC_API.G_EXCEPTION_ERROR Exception');
2002 END IF;
2003
2004 IF csr_get_udv_with_procs%ISOPEN THEN
2005 CLOSE csr_get_udv_with_procs;
2006 END IF;
2007
2008 x_return_status := G_RET_STS_ERROR ;
2009
2010 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2011 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2012 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'1200: Leaving check_user_vars_with_procs : OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
2013 END IF;
2014
2015 IF csr_get_udv_with_procs%ISOPEN THEN
2016 CLOSE csr_get_udv_with_procs;
2017 END IF;
2018
2019 x_return_status := G_RET_STS_UNEXP_ERROR ;
2020
2021 WHEN OTHERS THEN
2022 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2023 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'1300: Leaving check_user_vars_with_procs because of EXCEPTION: '||sqlerrm);
2024 END IF;
2025
2026 IF csr_get_udv_with_procs%ISOPEN THEN
2027 CLOSE csr_get_udv_with_procs;
2028 END IF;
2029
2030 x_return_status := G_RET_STS_UNEXP_ERROR ;
2031
2032 END check_user_vars_with_procs;
2033
2034
2035 -------------------------------------------
2036 -- PROCEDURE check_variables
2037 -------------------------------------------
2038 /* API to do QA Check variables*/
2039
2040 PROCEDURE check_variables (
2041 p_doc_type IN VARCHAR2,
2042 p_doc_id IN NUMBER,
2043 px_qa_result_tbl IN OUT NOCOPY qa_result_tbl_type,
2044 x_return_status OUT NOCOPY VARCHAR2)
2045 IS
2046
2047 l_api_name CONSTANT VARCHAR2(30) := 'G_Check_variables';
2048 l_indx NUMBER;
2049 l_int_severity OKC_QA_ERRORS_T.ERROR_SEVERITY%TYPE;
2050 l_int_desc OKC_QA_ERRORS_T.PROBLEM_SHORT_DESC%TYPE;
2051 l_ext_severity OKC_QA_ERRORS_T.ERROR_SEVERITY%TYPE;
2052 l_ext_desc OKC_QA_ERRORS_T.PROBLEM_SHORT_DESC%TYPE;
2053 l_perform_int_qa VARCHAR2(1);
2054 l_perform_ext_qa VARCHAR2(1);
2055 l_sys_severity OKC_QA_ERRORS_T.ERROR_SEVERITY%TYPE;
2056 l_sys_desc OKC_QA_ERRORS_T.PROBLEM_SHORT_DESC%TYPE;
2057 l_perform_sys_qa Varchar2(1);
2058 l_var_value_tbl OKC_TERMS_UTIL_GRP.sys_var_value_tbl_type;
2059 l_msg_count NUMBER;
2060 l_msg_data VARCHAR2(1000);
2061
2062
2063 -- Bug# 6002595. Modified the cursor to exclude user variables with procedures
2064 --Rep Enh, Modified cursor to fetch system defined variables for all Repository Document Types
2065 CURSOR l_check_variable_csr IS
2066 SELECT kart.id id,
2067 kart.sav_sae_id article_id,
2068 kart.amendment_operation_code amendment_operation_code,
2069 kart.scn_id scn_id,
2070 var.variable_code variable_code,
2071 busvar.variable_name variable_name,
2072 var.variable_value variable_value,
2073 var.variable_type,
2074 var.external_yn,
2075 var.variable_value_id,
2076 busvar.mrv_flag,
2077 var.mr_variable_html,
2078 var.mr_variable_xml
2079 FROM okc_k_articles_b kart,
2080 okc_k_art_variables var,
2081 okc_bus_variables_vl busvar
2082 WHERE kart.document_type=p_doc_type
2083 and kart.document_id=p_doc_id
2084 and var.cat_id=kart.id
2085 and busvar.variable_code=var.variable_code
2086 and nvl(kart.amendment_operation_code,'?')<>G_AMEND_CODE_DELETED
2087 and nvl(kart.summary_amend_operation_code,'?')<>G_AMEND_CODE_DELETED
2088 and ( (var.variable_type = 'U' AND busvar.variable_source = 'M') OR exists (( SELECT 'x' FROM okc_variable_doc_types vo
2089 WHERE vo.variable_code = var.variable_code
2090 AND vo.doc_type = p_doc_type
2091 )UNION all ( SELECT 'x' FROM okc_variable_doc_types vo
2092 WHERE vo.variable_code = var.variable_code
2093 AND vo.doc_type LIKE '%REPOSITORY%'))) ;
2094
2095
2096 BEGIN
2097
2098 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2099 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1500: Entered Check_variables');
2100 END IF;
2101
2102 get_qa_code_detail(p_qa_code => G_CHECK_UNRESOLVED_SYS_VAR,
2103 x_perform_qa => l_perform_sys_qa ,
2104 x_qa_name => l_sys_desc,
2105 x_severity_flag => l_sys_severity,
2106 x_return_status => x_return_status);
2107 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2108 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2109 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2110 RAISE FND_API.G_EXC_ERROR ;
2111 END IF;
2112
2113 get_qa_code_detail(p_qa_code => G_CHECK_INT_VAR_VALUE,
2114 x_perform_qa => l_perform_int_qa,
2115 x_qa_name => l_int_desc,
2116 x_severity_flag => l_int_severity,
2117 x_return_status => x_return_status);
2118 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2119 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2120 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2121 RAISE FND_API.G_EXC_ERROR ;
2122 END IF;
2123
2124 get_qa_code_detail(p_qa_code => G_CHECK_EXT_VAR_VALUE,
2125 x_perform_qa => l_perform_ext_qa,
2126 x_qa_name => l_ext_desc,
2127 x_severity_flag => l_ext_severity,
2128 x_return_status => x_return_status);
2129 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2130 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2131 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2132 RAISE FND_API.G_EXC_ERROR ;
2133 END IF;
2134 IF l_perform_sys_qa ='Y' THEN
2135 OKC_TERMS_UTIL_PVT.Get_System_Variables (
2136 p_api_version => 1,
2137 x_return_status => x_return_status,
2138 x_msg_data => l_msg_data,
2139 x_msg_count => l_msg_count,
2140 p_doc_type => p_doc_type,
2141 p_doc_id => p_doc_id,
2142 p_only_doc_variables => FND_API.G_TRUE,
2143 x_sys_var_value_tbl => l_var_value_tbl
2144 );
2145
2146 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2147 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2148 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2149 RAISE FND_API.G_EXC_ERROR ;
2150 END IF;
2151 END IF;-- IF l_perform_sys_qa ='Y' THEN
2152
2153 -- Bug# 6002595. Invoking check_user_vars_with_procs to check unresolved user variables with procedures
2154 IF l_perform_int_QA ='Y' THEN
2155
2156 check_user_vars_with_procs (
2157 p_doc_type => p_doc_type,
2158 p_doc_id => p_doc_id,
2159 px_qa_result_tbl => px_qa_result_tbl,
2160 x_return_status => x_return_status
2161 );
2162
2163 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2164 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2165 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2166 RAISE FND_API.G_EXC_ERROR ;
2167 END IF;
2168
2169 END IF;
2170 -- End of fix for Bug# 6002595.
2171
2172 IF l_perform_int_QA='Y' or l_perform_ext_qa='Y' or l_perform_sys_qa ='Y' THEN
2173
2174 FOR cr IN l_check_variable_csr LOOP
2175
2176 IF ( ( cr.variable_type='U' AND cr.external_yn='Y' AND l_perform_ext_qa='Y')
2177 AND
2178 ( ( Nvl(cr.mrv_flag,'N') = 'N' AND cr.variable_value IS NULL
2179 and cr.variable_value_id IS NULL )
2180 OR
2181 ( Nvl(cr.mrv_flag,'N') = 'Y' AND cr.mr_variable_html IS NULL )
2182 )
2183 ) THEN
2184
2185 l_indx := px_qa_result_tbl.COUNT + 1;
2186
2187 px_qa_result_tbl(l_indx).error_record_type:= G_ART_QA_TYPE;
2188 px_qa_result_tbl(l_indx).article_id := cr.article_id;
2189 px_qa_result_tbl(l_indx).deliverable_id := Null;
2190 px_qa_result_tbl(l_indx).title := get_article_title(cr.id);
2191 px_qa_result_tbl(l_indx).section_name := get_section_title(cr.scn_id);
2192 px_qa_result_tbl(l_indx).qa_code := G_CHECK_EXT_VAR_VALUE;
2193 px_qa_result_tbl(l_indx).message_name := G_OKC_CHECK_EXT_VAR_VALUE;
2194 px_qa_result_tbl(l_indx).suggestion := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC,G_OKC_CHECK_EXT_VAR_VALUE_S);
2195
2196 px_qa_result_tbl(l_indx).error_severity := l_ext_severity;
2197 px_qa_result_tbl(l_indx).problem_short_desc := l_ext_desc;
2198 px_qa_result_tbl(l_indx).problem_details_short := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_CHECK_EXT_VAR_VALUE_SH, 'VARIABLE', cr.variable_name);
2199 px_qa_result_tbl(l_indx).problem_details := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_CHECK_EXT_VAR_VALUE, 'VARIABLE', cr.variable_name,'ARTICLE',px_qa_result_tbl(l_indx).title);
2200
2201 ELSIF ( ( cr.variable_type='U' AND cr.external_yn='N' AND l_perform_int_qa='Y')
2202 AND
2203 (
2204 (Nvl(cr.mrv_flag,'N') = 'N' AND cr.variable_value IS NULL
2205 and cr.variable_value_id IS NULL )
2206 OR
2207 (Nvl(cr.mrv_flag,'N') = 'Y' AND cr.mr_variable_html IS NULL )
2208 )
2209 ) THEN
2210
2211 l_indx := px_qa_result_tbl.COUNT + 1;
2212 px_qa_result_tbl(l_indx).error_record_type:= G_ART_QA_TYPE;
2213 px_qa_result_tbl(l_indx).article_id := cr.article_id;
2214 px_qa_result_tbl(l_indx).deliverable_id := Null;
2215 px_qa_result_tbl(l_indx).title := get_article_title(cr.id);
2216 px_qa_result_tbl(l_indx).qa_code := G_CHECK_INT_VAR_VALUE;
2217 px_qa_result_tbl(l_indx).message_name := G_OKC_CHECK_INT_VAR_VALUE;
2218 px_qa_result_tbl(l_indx).suggestion := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC,G_OKC_CHECK_INT_VAR_VALUE_S);
2219 px_qa_result_tbl(l_indx).section_name := get_section_title(cr.scn_id);
2220
2221 px_qa_result_tbl(l_indx).error_severity := l_INT_severity;
2222 px_qa_result_tbl(l_indx).problem_short_desc := l_INT_desc;
2223 px_qa_result_tbl(l_indx).problem_details_short := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_CHECK_INT_VAR_VALUE_SH, 'VARIABLE', cr.variable_name);
2224 px_qa_result_tbl(l_indx).problem_details := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_CHECK_INT_VAR_VALUE, 'VARIABLE', cr.variable_name,'ARTICLE',px_qa_result_tbl(l_indx).title);
2225
2226 ELSIF cr.variable_type='S' AND l_perform_sys_qa='Y' THEN
2227
2228 IF l_var_value_tbl.COUNT > 0 THEN
2229 FOR i IN l_var_value_tbl.FIRST..l_var_value_tbl.LAST LOOP
2230 IF l_var_value_tbl(i).variable_code=cr.variable_code AND l_var_value_tbl(i).variable_value_id is NULL THEN
2231
2232 l_indx := px_qa_result_tbl.COUNT + 1;
2233 px_qa_result_tbl(l_indx).error_record_type:= G_ART_QA_TYPE;
2234 px_qa_result_tbl(l_indx).article_id := cr.article_id;
2235 px_qa_result_tbl(l_indx).deliverable_id := Null;
2236 px_qa_result_tbl(l_indx).title := get_article_title(cr.id);
2237 px_qa_result_tbl(l_indx).section_name := get_section_title(cr.scn_id);
2238 px_qa_result_tbl(l_indx).qa_code := G_CHECK_UNRESOLVED_SYS_VAR;
2239 px_qa_result_tbl(l_indx).message_name := G_OKC_CHECK_UNRES_SYS_VAR;
2240 px_qa_result_tbl(l_indx).suggestion := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC,G_OKC_CHECK_UNRES_SYS_VAR_S);
2241
2242 px_qa_result_tbl(l_indx).error_severity := l_sys_severity;
2243 px_qa_result_tbl(l_indx).problem_short_desc := l_sys_desc;
2244 px_qa_result_tbl(l_indx).problem_details_short := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_CHECK_UNRES_SYS_VAR_SH, 'VARIABLE',cr.variable_name);
2245 px_qa_result_tbl(l_indx).problem_details := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_CHECK_UNRES_SYS_VAR, 'ARTICLE', px_qa_result_tbl(l_indx).title, 'VARIABLE',cr.variable_name);
2246 END IF; -- IF l_var_value_tbl(i).variable_code
2247 END LOOP; -- FOR i IN l_var_value_tbl.FIRST..
2248 END IF; -- IF l_var_value_tbl.COUNT > 0 THEN
2249
2250 END IF; -- IF cr_variable_type<>'S' AND cr.external_yn='Y'
2251
2252 END LOOP; -- FOR cr IN l_check_variable_csr LOOP
2253
2254 END IF; -- IF l_perform_int_QA='Y' or l_perform_ext_qa='Y' or l_perf
2255
2256
2257 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2258 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1600: Leaving Check_variables');
2259 END IF;
2260
2261 EXCEPTION
2262 WHEN FND_API.G_EXC_ERROR THEN
2263 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2264 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7800: Leaving Check_variables : OKC_API.G_EXCEPTION_ERROR Exception');
2265 END IF;
2266
2267 IF l_check_variable_csr%ISOPEN THEN
2268 CLOSE l_check_variable_csr;
2269 END IF;
2270
2271 x_return_status := G_RET_STS_ERROR ;
2272
2273 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2274 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2275 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7900: Leaving Check_variables : OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
2276 END IF;
2277
2278 IF l_check_variable_csr%ISOPEN THEN
2279 CLOSE l_check_variable_csr;
2280 END IF;
2281
2282 x_return_status := G_RET_STS_UNEXP_ERROR ;
2283
2284 WHEN OTHERS THEN
2285 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2286 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'8000: Leaving Check_variables because of EXCEPTION: '||sqlerrm);
2287 END IF;
2288
2289 IF l_check_variable_csr%ISOPEN THEN
2290 CLOSE l_check_variable_csr;
2291 END IF;
2292
2293 x_return_status := G_RET_STS_UNEXP_ERROR ;
2294
2295 END check_variables;
2296
2297 -------------------------------------------
2298 -- PROCEDURE check_unassigned_articles
2299 -------------------------------------------
2300 PROCEDURE check_unassigned_articles (
2301 p_doc_type IN VARCHAR2,
2302 p_doc_id IN NUMBER,
2303 px_qa_result_tbl IN OUT NOCOPY qa_result_tbl_type,
2304 x_return_status OUT NOCOPY VARCHAR2)
2305 IS
2306
2307 l_api_name CONSTANT VARCHAR2(30) := 'G_check_unassigned_articles';
2308 l_indx NUMBER;
2309 l_severity OKC_QA_ERRORS_T.ERROR_SEVERITY%TYPE;
2310 l_desc OKC_QA_ERRORS_T.PROBLEM_SHORT_DESC%TYPE;
2311 l_perform_unas_art_qa VARCHAR2(1);
2312
2313 CURSOR l_get_unass_art_crs IS
2314 SELECT
2315 kart.id id,
2316 kart.sav_sae_id article_id,
2317 kart.scn_id scn_id
2318 FROM OKC_K_ARTICLES_B KART,
2319 OKC_SECTIONS_B SCN
2320 WHERE kart.document_type=p_doc_type
2321 AND kart.document_id =p_doc_id
2322 AND scn.id = kart.scn_id
2323 AND scn.scn_code = G_UNASSIGNED_SECTION_CODE
2324 AND nvl(scn.amendment_operation_code,'?') <> G_AMEND_CODE_DELETED
2325 AND nvl(kart.amendment_operation_code,'?') <> G_AMEND_CODE_DELETED
2326 AND nvl(kart.summary_amend_operation_code,'?') <> G_AMEND_CODE_DELETED;
2327
2328 BEGIN
2329
2330 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2331 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1500: Entered check_unassigned_articles');
2332 END IF;
2333
2334 get_qa_code_detail(p_qa_code => G_CHECK_UNASSIGNED_ART,
2335 x_perform_qa => l_perform_unas_art_qa,
2336 x_qa_name => l_desc,
2337 x_severity_flag => l_severity,
2338 x_return_status => x_return_status);
2339 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2340 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2341 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2342 RAISE FND_API.G_EXC_ERROR ;
2343 END IF;
2344
2345 IF l_perform_unas_art_qa='Y' THEN
2346 FOR cr IN l_get_unass_art_crs LOOP
2347
2348 l_indx := px_qa_result_tbl.COUNT + 1;
2349
2350 px_qa_result_tbl(l_indx).error_record_type:= G_ART_QA_TYPE;
2351 px_qa_result_tbl(l_indx).article_id := cr.article_id;
2352 px_qa_result_tbl(l_indx).deliverable_id := Null;
2353 px_qa_result_tbl(l_indx).title := get_article_title(cr.id);
2354 px_qa_result_tbl(l_indx).section_name := get_section_title(cr.scn_id);
2355 px_qa_result_tbl(l_indx).qa_code := G_CHECK_UNASSIGNED_ART;
2356 px_qa_result_tbl(l_indx).message_name := G_OKC_CHECK_UNASSIGNED_ART;
2357 px_qa_result_tbl(l_indx).suggestion := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC,G_OKC_CHECK_UNASSIGNED_ART_S);
2358 px_qa_result_tbl(l_indx).error_severity := l_severity;
2359 px_qa_result_tbl(l_indx).problem_short_desc := l_desc;
2360 px_qa_result_tbl(l_indx).problem_details_short := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_CHECK_UNASSIGNED_ART_SH);
2361 px_qa_result_tbl(l_indx).problem_details := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_CHECK_UNASSIGNED_ART, 'ARTICLE', px_qa_result_tbl(l_indx).title);
2362
2363 END LOOP; -- FOR cr IN l_get_unass_art_crs LOOP
2364 END IF; -- IF l_perform_unas_art_qa='Y' THEN
2365
2366 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2367 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1600: Leaving check_unassigned_articles');
2368 END IF;
2369
2370 EXCEPTION
2371 WHEN FND_API.G_EXC_ERROR THEN
2372 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2373 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7800: Leaving check_unassigned_articles : OKC_API.G_EXCEPTION_ERROR Exception');
2374 END IF;
2375
2376 IF l_get_unass_art_crs%ISOPEN THEN
2377 CLOSE l_get_unass_art_crs;
2378 END IF;
2379
2380 x_return_status := G_RET_STS_ERROR ;
2381
2382 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2383 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2384 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7900: Leaving check_unassigned_articles : OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
2385 END IF;
2386
2387 IF l_get_unass_art_crs%ISOPEN THEN
2388 CLOSE l_get_unass_art_crs;
2389 END IF;
2390
2391 x_return_status := G_RET_STS_UNEXP_ERROR ;
2392
2393 WHEN OTHERS THEN
2394 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2395 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'8000: Leaving check_unassigned_articles because of EXCEPTION: '||sqlerrm);
2396 END IF;
2397
2398 IF l_get_unass_art_crs%ISOPEN THEN
2399 CLOSE l_get_unass_art_crs;
2400 END IF;
2401
2402 x_return_status := G_RET_STS_UNEXP_ERROR ;
2403
2404 END check_unassigned_articles;
2405
2406 -------------------------------------------
2407 -- PROCEDURE check_empty_sections
2408 -------------------------------------------
2409 PROCEDURE check_empty_sections (
2410 p_doc_type IN VARCHAR2,
2411 p_doc_id IN NUMBER,
2412 px_qa_result_tbl IN OUT NOCOPY qa_result_tbl_type,
2413 x_return_status OUT NOCOPY VARCHAR2)
2414 IS
2415
2416 l_api_name CONSTANT VARCHAR2(30) := 'G_check_empty_sections';
2417 l_indx NUMBER;
2418 l_severity OKC_QA_ERRORS_T.ERROR_SEVERITY%TYPE;
2419 l_desc OKC_QA_ERRORS_T.PROBLEM_SHORT_DESC%TYPE;
2420 l_perform_empty_scn_qa VARCHAR2(1);
2421
2422 CURSOR l_get_empty_section_csr IS
2423 SELECT
2424 id id,
2425 scn_id scn_id
2426 FROM OKC_SECTIONS_B SCN
2427 WHERE document_type=p_doc_type
2428 AND document_id =p_doc_id
2429 AND nvl(amendment_operation_code,'?') <> G_AMEND_CODE_DELETED
2430 AND nvl(summary_amend_operation_code,'?') <> G_AMEND_CODE_DELETED
2431 AND not exists ( SELECT 'x' FROM OKC_K_ARTICLES_B WHERE scn_id=scn.id
2432 AND nvl(amendment_operation_code,'?') <> G_AMEND_CODE_DELETED
2433 AND nvl(summary_amend_operation_code,'?') <> G_AMEND_CODE_DELETED)
2434 AND not exists ( SELECT 'x' FROM OKC_SECTIONS_B SCN1 WHERE SCN1.scn_id = scn.id
2435 AND nvl(amendment_operation_code,'?') <> G_AMEND_CODE_DELETED
2436 AND nvl(summary_amend_operation_code,'?') <> G_AMEND_CODE_DELETED);
2437 -- if section has sub sections then it is not considered empty Bug 3219528
2438 BEGIN
2439
2440 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2441 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1500: Entered check_empty_sections');
2442 END IF;
2443
2444 get_qa_code_detail(p_qa_code => G_CHECK_EMPTY_SECTION,
2445 x_perform_qa => l_perform_empty_scn_qa,
2446 x_qa_name => l_desc,
2447 x_severity_flag => l_severity,
2448 x_return_status => x_return_status);
2449 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2450 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2451 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2452 RAISE FND_API.G_EXC_ERROR ;
2453 END IF;
2454
2455 IF l_perform_empty_scn_QA='Y' THEN
2456 FOR cr IN l_get_empty_section_csr LOOP
2457
2458 l_indx := px_qa_result_tbl.COUNT + 1;
2459
2460 px_qa_result_tbl(l_indx).error_record_type:= G_SCN_QA_TYPE;
2461 px_qa_result_tbl(l_indx).article_id := Null;
2462 px_qa_result_tbl(l_indx).deliverable_id := Null;
2463 px_qa_result_tbl(l_indx).title := get_section_title(cr.id);
2464 px_qa_result_tbl(l_indx).section_name := get_section_title(cr.scn_id);
2465 px_qa_result_tbl(l_indx).qa_code := G_CHECK_EMPTY_SECTION;
2466 px_qa_result_tbl(l_indx).message_name := G_OKC_CHECK_EMPTY_SECTION;
2467 px_qa_result_tbl(l_indx).suggestion := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC,G_OKC_CHECK_EMPTY_SECTION_S);
2468 px_qa_result_tbl(l_indx).error_severity := l_severity;
2469 px_qa_result_tbl(l_indx).problem_short_desc := l_desc;
2470 px_qa_result_tbl(l_indx).problem_details_short := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_CHECK_EMPTY_SECTION_SH);
2471 px_qa_result_tbl(l_indx).problem_details := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_CHECK_EMPTY_SECTION, 'SECTION', px_qa_result_tbl(l_indx).title);
2472
2473 END LOOP; -- FOR cr IN l_get_empty_section_csr LOOP
2474 END IF; -- IF l_perform_empty_scn_QA='Y' THEN
2475
2476 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2477 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1600: Leaving check_empty_sections');
2478 END IF;
2479
2480 EXCEPTION
2481 WHEN FND_API.G_EXC_ERROR THEN
2482 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2483 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7800: Leaving check_empty_sections : OKC_API.G_EXCEPTION_ERROR Exception');
2484 END IF;
2485
2486 IF l_get_empty_section_csr%ISOPEN THEN
2487 CLOSE l_get_empty_section_csr;
2488 END IF;
2489
2490 x_return_status := G_RET_STS_ERROR ;
2491
2492 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2493 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2494 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7900: Leaving check_empty_sections : OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
2495 END IF;
2496
2497 IF l_get_empty_section_csr%ISOPEN THEN
2498 CLOSE l_get_empty_section_csr;
2499 END IF;
2500
2501 x_return_status := G_RET_STS_UNEXP_ERROR ;
2502
2503 WHEN OTHERS THEN
2504 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2505 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'8000: Leaving check_empty_sections because of EXCEPTION: '||sqlerrm);
2506 END IF;
2507
2508 IF l_get_empty_section_csr%ISOPEN THEN
2509 CLOSE l_get_empty_section_csr;
2510 END IF;
2511
2512 x_return_status := G_RET_STS_UNEXP_ERROR ;
2513 END check_empty_sections;
2514
2515 -------------------------------------------
2516 -- PROCEDURE check_section_amend_no_texts
2517 -------------------------------------------
2518 PROCEDURE check_section_amend_no_texts (
2519 p_doc_type IN VARCHAR2,
2520 p_doc_id IN NUMBER,
2521 px_qa_result_tbl IN OUT NOCOPY qa_result_tbl_type,
2522 x_return_status OUT NOCOPY VARCHAR2)
2523 IS
2524
2525 l_api_name CONSTANT VARCHAR2(30) := 'G_Check_Section_Amend_No_Texts';
2526 l_indx NUMBER;
2527 l_severity OKC_QA_ERRORS_T.ERROR_SEVERITY%TYPE;
2528 l_desc OKC_QA_ERRORS_T.PROBLEM_SHORT_DESC%TYPE;
2529 l_perform_scn_amend_qa VARCHAR2(1);
2530 l_disable_amend_yn VARCHAR2(1);
2531
2532 CURSOR l_get_doc_disable_amend_csr IS
2533 SELECT NVL(disable_amend_yn,'N')
2534 FROM OKC_BUS_DOC_TYPES_B
2535 WHERE document_type = p_doc_type;
2536
2537 BEGIN
2538
2539 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2540 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1500: Entered Check_Section_Amend_No_Texts');
2541 END IF;
2542
2543 --Bug 3681462 Check for disable_amend_yn
2544 OPEN l_get_doc_disable_amend_csr;
2545 FETCH l_get_doc_disable_amend_csr INTO l_disable_amend_yn;
2546 CLOSE l_get_doc_disable_amend_csr;
2547
2548 IF l_disable_amend_yn = 'N' THEN
2549
2550 get_qa_code_detail(p_qa_code => G_CHECK_SCN_AMEND_NO_TEXT,
2551 x_perform_qa => l_perform_scn_amend_qa,
2552 x_qa_name => l_desc,
2553 x_severity_flag => l_severity,
2554 x_return_status => x_return_status);
2555 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2556 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2557 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2558 RAISE FND_API.G_EXC_ERROR ;
2559 END IF;
2560
2561 IF l_perform_scn_amend_qa='Y' THEN
2562 IF l_section_tbl.count>0 THEN
2563 FOR i IN l_section_tbl.FIRST..l_section_tbl.LAST LOOP
2564
2565 IF l_section_tbl(i).amendment_operation_code IS NOT NULL AND l_section_tbl(i).amendment_description IS NULL THEN
2566
2567 l_indx := px_qa_result_tbl.COUNT + 1;
2568
2569 px_qa_result_tbl(l_indx).error_record_type:= G_SCN_QA_TYPE;
2570 px_qa_result_tbl(l_indx).article_id := Null;
2571 px_qa_result_tbl(l_indx).deliverable_id := Null;
2572 px_qa_result_tbl(l_indx).title := get_section_title(l_section_tbl(i).id);
2573 px_qa_result_tbl(l_indx).section_name := get_section_title(l_section_tbl(i).scn_id);
2574 px_qa_result_tbl(l_indx).qa_code := G_CHECK_SCN_AMEND_NO_TEXT;
2575 px_qa_result_tbl(l_indx).message_name := G_OKC_CHECK_SCN_AMEND_NO_TEXT;
2576 px_qa_result_tbl(l_indx).suggestion := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC,G_OKC_CHK_SCN_AMEND_NO_TEXT_S);
2577 px_qa_result_tbl(l_indx).error_severity := l_severity;
2578 px_qa_result_tbl(l_indx).problem_short_desc := l_desc;
2579 px_qa_result_tbl(l_indx).problem_details_short := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_CHK_SCN_AMEND_NO_TEXT_SH);
2580 px_qa_result_tbl(l_indx).problem_details := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_CHECK_SCN_AMEND_NO_TEXT, 'SECTION', px_qa_result_tbl(l_indx).title);
2581 END IF; -- IF l_section_tbl(i).amendment_operation_code
2582
2583 END LOOP;-- FOR i IN l_section_tbl.FIRST..l_section_tbl.LAST
2584 END IF; -- IF l_section_tbl.count>0 THEN
2585 END IF; -- IF l_perform_scn_amend_qa='Y' THEN
2586
2587 END IF; -- l_disable_amend_yn = 'N'
2588
2589 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2590 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1600: Leaving Check_Section_Amend_No_Texts');
2591 END IF;
2592
2593 EXCEPTION
2594 WHEN FND_API.G_EXC_ERROR THEN
2595 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2596 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7800: Leaving Check_Section_Amend_No_Texts : OKC_API.G_EXCEPTION_ERROR Exception');
2597 END IF;
2598
2599 x_return_status := G_RET_STS_ERROR ;
2600
2601 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2602 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2603 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7900: Leaving Check_Section_Amend_No_Texts : OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
2604 END IF;
2605
2606 x_return_status := G_RET_STS_UNEXP_ERROR ;
2607
2608 WHEN OTHERS THEN
2609 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2610 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'8000: Leaving Check_Section_Amend_No_Texts because of EXCEPTION: '||sqlerrm);
2611 END IF;
2612
2613 x_return_status := G_RET_STS_UNEXP_ERROR ;
2614 END check_section_amend_no_texts;
2615
2616 -------------------------------------------
2617 -- PROCEDURE check_article_amend_no_texts
2618 -------------------------------------------
2619 PROCEDURE check_article_amend_no_texts (
2620 p_doc_type IN VARCHAR2,
2621 p_doc_id IN NUMBER,
2622 px_qa_result_tbl IN OUT NOCOPY qa_result_tbl_type,
2623 x_return_status OUT NOCOPY VARCHAR2)
2624 IS
2625
2626 l_api_name CONSTANT VARCHAR2(30) := 'G_check_article_amend_no_texts';
2627 l_indx NUMBER;
2628 l_severity OKC_QA_ERRORS_T.ERROR_SEVERITY%TYPE;
2629 l_desc OKC_QA_ERRORS_T.PROBLEM_SHORT_DESC%TYPE;
2630 l_perform_art_amend_qa VARCHAR2(1);
2631 l_disable_amend_yn VARCHAR2(1);
2632
2633 CURSOR l_get_doc_disable_amend_csr IS
2634 SELECT NVL(disable_amend_yn,'N')
2635 FROM OKC_BUS_DOC_TYPES_B
2636 WHERE document_type = p_doc_type;
2637
2638 BEGIN
2639
2640 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2641 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1500: Entered check_article_amend_no_texts');
2642 END IF;
2643
2644 --Bug 3681462 Check for disable_amend_yn
2645 OPEN l_get_doc_disable_amend_csr;
2646 FETCH l_get_doc_disable_amend_csr INTO l_disable_amend_yn;
2647 CLOSE l_get_doc_disable_amend_csr;
2648
2649 IF l_disable_amend_yn = 'N' THEN
2650
2651 get_qa_code_detail(p_qa_code => G_CHECK_ART_AMEND_NO_TEXT,
2652 x_perform_qa => l_perform_art_amend_qa,
2653 x_qa_name => l_desc,
2654 x_severity_flag => l_severity,
2655 x_return_status => x_return_status);
2656 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2657 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2658 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2659 RAISE FND_API.G_EXC_ERROR ;
2660 END IF;
2661
2662 IF l_perform_art_amend_qa ='Y' THEN
2663
2664 IF l_article_tbl.count > 0 THEN
2665
2666 FOR i IN l_article_tbl.FIRST..l_article_tbl.LAST LOOP
2667
2668 IF l_article_tbl(i).amendment_operation_code IS NOT NULL
2669 AND l_article_tbl(i).amendment_description IS NULL THEN
2670
2671 l_indx := px_qa_result_tbl.COUNT + 1;
2672
2673 px_qa_result_tbl(l_indx).error_record_type:= G_ART_QA_TYPE;
2674 px_qa_result_tbl(l_indx).article_id := l_article_tbl(i).article_id;
2675 px_qa_result_tbl(l_indx).deliverable_id := Null;
2676 px_qa_result_tbl(l_indx).title := get_article_title(l_article_tbl(i).id);
2677 px_qa_result_tbl(l_indx).section_name := get_section_title(l_article_tbl(i).scn_id);
2678 px_qa_result_tbl(l_indx).qa_code := G_CHECK_ART_AMEND_NO_TEXT;
2679 px_qa_result_tbl(l_indx).message_name := G_OKC_CHECK_ART_AMEND_NO_TEXT;
2680 px_qa_result_tbl(l_indx).suggestion := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC,G_OKC_CHK_ART_AMEND_NO_TEXT_S);
2681 px_qa_result_tbl(l_indx).error_severity := l_severity;
2682 px_qa_result_tbl(l_indx).problem_short_desc := l_desc;
2683 px_qa_result_tbl(l_indx).problem_details_short := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_CHK_ART_AMEND_NO_TEXT_SH);
2684 px_qa_result_tbl(l_indx).problem_details := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_CHECK_ART_AMEND_NO_TEXT, 'ARTICLE', px_qa_result_tbl(l_indx).title);
2685 END IF; -- IF l_article_tbl(i).amendment_operation_code I
2686
2687 END LOOP; -- FOR i IN l_article_tbl.FIRST..l_article_tbl.LAST LOOP
2688 END IF; -- IF l_article_tbl.count>0 THEN
2689 END IF; -- IF l_perform_art_amend_qa ='Y' THEN
2690
2691 END IF; -- l_disable_amend_yn = 'N'
2692
2693 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2694 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1600: Leaving check_article_amend_no_texts');
2695 END IF;
2696
2697 EXCEPTION
2698 WHEN FND_API.G_EXC_ERROR THEN
2699 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2700 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7800: Leaving check_article_amend_no_texts : OKC_API.G_EXCEPTION_ERROR Exception');
2701 END IF;
2702
2703 x_return_status := G_RET_STS_ERROR ;
2704
2705 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2706 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2707 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7900: Leaving check_article_amend_no_texts : OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
2708 END IF;
2709
2710 x_return_status := G_RET_STS_UNEXP_ERROR ;
2711
2712 WHEN OTHERS THEN
2713 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2714 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'8000: Leaving check_article_amend_no_texts because of EXCEPTION: '||sqlerrm);
2715 END IF;
2716
2717 x_return_status := G_RET_STS_UNEXP_ERROR ;
2718 END check_article_amend_no_texts;
2719
2720 -------------------------------------------
2721 -- PROCEDURE check_inactive_template
2722 -------------------------------------------
2723 /* Check inactive Template */
2724
2725 PROCEDURE check_inactive_template (
2726 p_doc_type IN VARCHAR2,
2727 p_doc_id IN NUMBER,
2728 px_qa_result_tbl IN OUT NOCOPY qa_result_tbl_type,
2729 x_return_status OUT NOCOPY VARCHAR2)
2730 IS
2731
2732 l_api_name CONSTANT VARCHAR2(30) := 'check_inactive_template';
2733 l_indx NUMBER;
2734 l_severity OKC_QA_ERRORS_T.ERROR_SEVERITY%TYPE;
2735 l_desc OKC_QA_ERRORS_T.PROBLEM_SHORT_DESC%TYPE;
2736 l_perform_tmpl_qa VARCHAR2(1);
2737 l_tmpl_usg_exists_flag varchar2(1):=OKC_API.G_MISS_CHAR;
2738 l_doc_type_name OKC_BUS_DOC_TYPES_TL.NAME%TYPE;
2739
2740
2741 CURSOR l_get_template_crs IS
2742 SELECT status_code,end_date,template_name
2743 FROM
2744 OKC_TERMS_TEMPLATES_ALL TMPL,
2745 OKC_TEMPLATE_USAGES USG
2746 WHERE USG.DOCUMENT_TYPE = p_doc_type
2747 AND USG.DOCUMENT_ID = p_doc_id
2748 AND TMPL.TEMPLATE_ID = USG.TEMPLATE_ID;
2749
2750 --Bug 4126819 Added cursor to get check if the template is associated to the document.
2751 CURSOR l_get_template_usg_csr IS
2752 SELECT 'Y'
2753 FROM OKC_ALLOWED_TMPL_USAGES
2754 WHERE TEMPLATE_ID = (SELECT TEMPLATE_ID
2755 FROM OKC_TEMPLATE_USAGES
2756 WHERE DOCUMENT_ID = p_doc_id
2757 AND DOCUMENT_TYPE = p_doc_type)
2758 AND DOCUMENT_TYPE = p_doc_type;
2759 --Bug 4126819 Added cursor to get the document type
2760 CURSOR l_get_doc_type_name IS
2761 SELECT name
2762 FROM okc_bus_doc_types_tl
2763 WHERE document_type = p_doc_type
2764 AND LANGUAGE = userenv('LANG');
2765
2766 BEGIN
2767
2768 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2769 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1500: Entered check_inactive_template');
2770 END IF;
2771
2772 get_qa_code_detail(p_qa_code => G_CHECK_TMPL_EFFECTIVITY,
2773 x_perform_qa => l_perform_tmpl_qa,
2774 x_qa_name => l_desc,
2775 x_severity_flag => l_severity,
2776 x_return_status => x_return_status);
2777 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2778 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2779 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2780 RAISE FND_API.G_EXC_ERROR ;
2781 END IF;
2782
2783 IF l_perform_tmpl_qa ='Y' THEN
2784 FOR cr in l_get_template_crs LOOP
2785
2786 IF cr.status_code='ON_HOLD'
2787 OR ( cr.status_code='APPROVED'
2788 AND nvl(cr.end_date,sysdate+1)< sysdate) THEN
2789
2790 l_indx := px_qa_result_tbl.COUNT + 1;
2791
2792 px_qa_result_tbl(l_indx).error_record_type := G_TMPL_QA_TYPE;
2793 px_qa_result_tbl(l_indx).article_id := Null;
2794 px_qa_result_tbl(l_indx).deliverable_id := Null;
2795 px_qa_result_tbl(l_indx).title := cr.template_name;
2796 px_qa_result_tbl(l_indx).section_name := Null;
2797 px_qa_result_tbl(l_indx).qa_code := G_CHECK_TMPL_EFFECTIVITY;
2798 px_qa_result_tbl(l_indx).message_name := G_OKC_CHECK_TMPL_EFFECTIVITY;
2799 px_qa_result_tbl(l_indx).suggestion := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC,G_OKC_CHECK_TMPL_EFFECTIVITY_S);
2800 px_qa_result_tbl(l_indx).error_severity := l_severity;
2801 px_qa_result_tbl(l_indx).problem_short_desc := l_desc;
2802 px_qa_result_tbl(l_indx).problem_details_short := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_CHK_TMPL_EFFECTIVITY_SH);
2803 px_qa_result_tbl(l_indx).problem_details := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_CHECK_TMPL_EFFECTIVITY, 'TEMPLATE', cr.template_name);
2804 END IF; -- IF cr.status_code='ON_HOLD'
2805
2806 --Bug 4126819 Getting the document type
2807 OPEN l_get_doc_type_name;
2808 FETCH l_get_doc_type_name into l_doc_type_name;
2809 CLOSE l_get_doc_type_name;
2810
2811 --Checking if the template is associated to the document type.
2812 OPEN l_get_template_usg_csr;
2813 FETCH l_get_template_usg_csr into l_tmpl_usg_exists_flag;
2814 IF l_get_template_usg_csr%NOTFOUND THEN
2815
2816 l_indx := px_qa_result_tbl.COUNT + 1;
2817
2818 px_qa_result_tbl(l_indx).error_record_type := G_TMPL_QA_TYPE;
2819 px_qa_result_tbl(l_indx).article_id := Null;
2820 px_qa_result_tbl(l_indx).deliverable_id := Null;
2821 px_qa_result_tbl(l_indx).title := cr.template_name;
2822 px_qa_result_tbl(l_indx).section_name := Null;
2823 px_qa_result_tbl(l_indx).qa_code := G_CHECK_TMPL_EFFECTIVITY;
2824 px_qa_result_tbl(l_indx).message_name := G_OKC_CHECK_TEMPL_USG_ASSO;
2825 px_qa_result_tbl(l_indx).suggestion := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC,G_OKC_CHECK_TEMPL_USG_ASSO_S,'DOCUMENT_TYPE',l_doc_type_name);
2826 px_qa_result_tbl(l_indx).error_severity := l_severity;
2827 px_qa_result_tbl(l_indx).problem_short_desc := l_desc;
2828 px_qa_result_tbl(l_indx).problem_details_short := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_CHECK_TEMPL_USG_ASSO_SH);
2829 px_qa_result_tbl(l_indx).problem_details := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_CHECK_TEMPL_USG_ASSO,'DOCUMENT_TYPE',l_doc_type_name,'TEMPLATE', cr.template_name);
2830 END IF;
2831 CLOSE l_get_template_usg_csr;
2832
2833 END LOOP; -- FOR cr in l_get_template_crs LOOP
2834 END IF; -- IF l_perform_tmpl_qa ='Y' THEN
2835
2836
2837 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2838 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1600: Leaving check_inactive_template');
2839 END IF;
2840
2841 EXCEPTION
2842 WHEN FND_API.G_EXC_ERROR THEN
2843 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2844 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7800: Leaving check_inactive_template : OKC_API.G_EXCEPTION_ERROR Exception');
2845 END IF;
2846
2847 IF l_get_template_crs%ISOPEN THEN
2848 CLOSE l_get_template_crs;
2849 END IF;
2850
2851 x_return_status := G_RET_STS_ERROR ;
2852
2853 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2854 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2855 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7900: Leaving check_inactive_template : OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
2856 END IF;
2857
2858 IF l_get_template_crs%ISOPEN THEN
2859 CLOSE l_get_template_crs;
2860 END IF;
2861
2862 x_return_status := G_RET_STS_UNEXP_ERROR ;
2863
2864 WHEN OTHERS THEN
2865 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2866 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'8000: Leaving check_inactive_template because of EXCEPTION: '||sqlerrm);
2867 END IF;
2868
2869 IF l_get_template_crs%ISOPEN THEN
2870 CLOSE l_get_template_crs;
2871 END IF;
2872
2873 x_return_status := G_RET_STS_UNEXP_ERROR ;
2874 END check_inactive_template;
2875
2876 -------------------------------------------
2877 -- PROCEDURE check_art_effectivity
2878 -------------------------------------------
2879 /* Check article effectitvity */
2880
2881 PROCEDURE check_art_effectivity (
2882 p_qa_mode IN VARCHAR2,
2883 p_doc_type IN VARCHAR2,
2884 p_doc_id IN NUMBER,
2885 px_qa_result_tbl IN OUT NOCOPY qa_result_tbl_type,
2886 x_return_status OUT NOCOPY VARCHAR2 )
2887 IS
2888
2889 l_api_name CONSTANT VARCHAR2(30) := 'G_check_art_effectivity';
2890 l_indx NUMBER;
2891 l_indx1 NUMBER;
2892 l_art_val_severity OKC_QA_ERRORS_T.ERROR_SEVERITY%TYPE;
2893 l_art_val_desc OKC_QA_ERRORS_T.PROBLEM_SHORT_DESC%TYPE;
2894
2895 l_lat_vers_severity OKC_QA_ERRORS_T.ERROR_SEVERITY%TYPE;
2896 l_lat_vers_desc OKC_QA_ERRORS_T.PROBLEM_SHORT_DESC%TYPE;
2897 l_start_date DATE;
2898 l_end_date DATE;
2899 l_found BOOLEAN := FALSE;
2900
2901 l_perform_art_val_qa VARCHAR2(1);
2902 l_perform_lat_vers_qa VARCHAR2(1);
2903 TYPE article_version_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
2904 l_article_version_tbl article_version_tbl_type;
2905
2906 CURSOR l_get_tmpl_dates_csr IS
2907 SELECT START_DATE,END_DATE
2908 FROM OKC_TERMS_TEMPLATES_ALL
2909 WHERE TEMPLATE_ID=p_doc_id;
2910
2911 /* This will executed only in TEMPLATE QA */
2912 /*
2913 CURSOR l_check_art_tmpl_csr(p_article_effective_date IN DATE) IS
2914 SELECT kart.id,
2915 kart.sav_sae_id article_id,
2916 kart.scn_id scn_id,
2917 kart.amendment_operation_code amendment_operation_code,
2918 kart.amendment_description amendment_description
2919 FROM OKC_K_ARTICLES_B KART
2920 WHERE DOCUMENT_TYPE=p_doc_type
2921 AND DOCUMENT_ID =p_doc_id
2922 AND NOT EXISTS ( SELECT 'X' FROM OKC_ARTICLE_VERSIONS VERS
2923 WHERE VERS.ARTICLE_ID=KART.SAV_SAE_ID
2924 AND VERS.ARTICLE_STATUS='APPROVED'
2925 AND nvl(p_article_effective_date,sysdate) >= VERS.START_DATE
2926 AND nvl(p_article_effective_date,sysdate) <= nvl(VERS.end_date, nvl(p_article_effective_date,sysdate) +1)
2927 );
2928
2929 */
2930
2931 /* This will executed only in TEMPLATE QA
2932 11.5.10+ Modify the cursor l_check_art_tmpl_csr to allow draft/rejected clauses also*/
2933
2934 CURSOR l_check_art_tmpl_csr(p_article_effective_date IN DATE) IS
2935 SELECT kart.id,
2936 kart.sav_sae_id article_id,
2937 kart.scn_id scn_id,
2938 kart.amendment_operation_code amendment_operation_code,
2939 kart.amendment_description amendment_description
2940 FROM OKC_K_ARTICLES_B KART,
2941 okc_terms_templates_all tmpl,
2942 okc_articles_all art
2943 WHERE kart.document_id = tmpl.template_id
2944 AND kart.sav_sae_id = art.article_id
2945 AND DOCUMENT_TYPE=p_doc_type
2946 AND DOCUMENT_ID =p_doc_id
2947 AND
2948 (
2949 ( art.org_id <> tmpl.org_id
2950 AND NOT EXISTS ( SELECT 'X'
2951 FROM OKC_ARTICLE_ADOPTIONS ADP,
2952 OKC_ARTICLE_VERSIONS VER
2953 WHERE ADP.GLOBAL_ARTICLE_VERSION_ID = VER.article_version_id
2954 AND VER.article_id = KART.SAV_SAE_ID
2955 AND ADP.LOCAL_ORG_ID = tmpl.org_id
2956 AND ADP.adoption_status IN ( 'APPROVED', 'ON_HOLD')
2957 AND ADP.ADOPTION_TYPE = 'ADOPTED'
2958 AND VER.ARTICLE_STATUS='APPROVED'
2959 AND nvl(p_article_effective_date,sysdate) >= VER.START_DATE
2960 AND nvl(p_article_effective_date,sysdate)
2961 <= nvl(VER.end_date, nvl(p_article_effective_date,sysdate) +1)
2962 )
2963 ) OR
2964 ( art.org_id = tmpl.org_id
2965 AND NOT EXISTS ( SELECT 'X' FROM OKC_ARTICLE_VERSIONS VERS
2966 WHERE VERS.ARTICLE_ID=KART.SAV_SAE_ID
2967 -- modified to include DRAFT and REJECTED statuses also
2968 --AND VERS.ARTICLE_STATUS='APPROVED'
2969 AND VERS.ARTICLE_STATUS in ('APPROVED', 'DRAFT' , 'REJECTED')
2970 AND nvl(p_article_effective_date,sysdate) >= VERS.START_DATE
2971 AND nvl(p_article_effective_date,sysdate) <= nvl(VERS.end_date, nvl(p_article_effective_date,sysdate) +1)
2972 )
2973 )
2974 );
2975
2976
2977 /* Define a new cursor to check expert clauses for validity */
2978 /* expert commented out
2979 CURSOR l_check_xprt_art_tmpl_csr (p_article_effective_date IN DATE) IS
2980 SELECT
2981 Rule.clause_id xprt_article_id,
2982 Rule.rule_id rule_id,
2983 Rule.rule_name rule_name
2984 FROM okc_xprt_clauses_v rule,
2985 okc_terms_templates_all tmpl,
2986 okc_articles_all art
2987 WHERE rule.template_id = tmpl.template_id
2988 AND tmpl.template_id = p_doc_id
2989 AND art.article_id = rule.clause_id
2990 AND
2991 (
2992 ( art.org_id <> tmpl.org_id
2993 AND NOT EXISTS ( SELECT 'X'
2994 FROM OKC_ARTICLE_ADOPTIONS ADP,
2995 OKC_ARTICLE_VERSIONS VER
2996 WHERE ADP.GLOBAL_ARTICLE_VERSION_ID = VER.article_version_id
2997 AND VER.article_id = rule.clause_id
2998 AND ADP.LOCAL_ORG_ID = tmpl.org_id
2999 AND ADP.adoption_status IN ( 'APPROVED', 'ON_HOLD')
3000 AND ADP.ADOPTION_TYPE = 'ADOPTED'
3001 AND VER.ARTICLE_STATUS='APPROVED'
3002 AND nvl(p_article_effective_date,sysdate) >= VER.START_DATE
3003 AND nvl(p_article_effective_date,sysdate) <= nvl(VER.end_date,
3004 nvl(p_article_effective_date,sysdate) +1)
3005 )
3006 )
3007 OR
3008 ( art.org_id = tmpl.org_id
3009 AND NOT EXISTS ( SELECT 'X' FROM OKC_ARTICLE_VERSIONS VERS
3010 WHERE VERS.ARTICLE_ID=rule.clause_id
3011 AND VERS.ARTICLE_STATUS in ('APPROVED', 'DRAFT' , 'REJECTED')
3012 AND nvl(p_article_effective_date,sysdate) >= VERS.START_DATE
3013 AND nvl(p_article_effective_date,sysdate) <= nvl(VERS.end_date,
3014 nvl(p_article_effective_date,sysdate) +1)
3015 )
3016 )
3017 );
3018 */
3019
3020 CURSOR l_check_art_doc_csr(b_effective_date IN DATE) IS
3021 SELECT kart.id,
3022 kart.sav_sae_id article_id,
3023 kart.article_version_id article_version_id,
3024 kart.scn_id scn_id,
3025 vers.start_date start_date,
3026 kart.amendment_operation_code amendment_operation_code,
3027 kart.amendment_description amendment_description
3028 FROM OKC_K_ARTICLES_B KART,
3029 OKC_ARTICLE_VERSIONS VERS,
3030 OKC_ARTICLES_ALL ART
3031 WHERE DOCUMENT_TYPE=p_doc_type
3032 AND DOCUMENT_ID =p_doc_id
3033 AND VERS.ARTICLE_VERSION_ID=KART.ARTICLE_VERSION_ID
3034 AND ART.ARTICLE_ID = KART.SAV_SAE_ID
3035 AND ART.STANDARD_YN='Y'
3036 AND nvl(AMENDMENT_OPERATION_CODE,'?')<>G_AMEND_CODE_DELETED
3037 AND nvl(SUMMARY_AMEND_OPERATION_CODE,'?')<>G_AMEND_CODE_DELETED
3038 AND ( ARTICLE_STATUS<>'APPROVED'
3039 OR ( ARTICLE_STATUS='APPROVED' AND
3040 nvl(END_DATE,b_effective_date+1)< b_effective_date
3041 )
3042 );
3043
3044 CURSOR l_check_latest_version_csr IS
3045 SELECT distinct KART.ID ID,
3046 KART.SAV_SAE_ID ARTICLE_ID,
3047 KART.ARTICLE_VERSION_ID ARTICLE_VERSION_ID,
3048 KART.SCN_ID SCN_ID,
3049 KART.AMENDMENT_OPERATION_CODE AMENDMENT_OPERATION_CODE,
3050 KART.AMENDMENT_DESCRIPTION AMENDMENT_DESCRIPTION
3051 FROM OKC_K_ARTICLES_B KART,
3052 OKC_ARTICLE_VERSIONS VERS,
3053 OKC_ARTICLE_VERSIONS VERS1,
3054 OKC_TEMPLATE_USAGES USG,
3055 OKC_TERMS_TEMPLATES_ALL TMPL
3056 WHERE KART.DOCUMENT_TYPE=p_doc_type
3057 AND KART.DOCUMENT_ID =p_doc_id
3058 AND KART.DOCUMENT_TYPE= USG.DOCUMENT_TYPE
3059 AND KART.DOCUMENT_ID = USG.DOCUMENT_ID
3060 AND USG.TEMPLATE_ID = TMPL.TEMPLATE_ID
3061 AND KART.SAV_SAE_ID = VERS.ARTICLE_ID
3062 AND nvl(KART.AMENDMENT_OPERATION_CODE,'?')<>G_AMEND_CODE_DELETED
3063 AND nvl(KART.SUMMARY_AMEND_OPERATION_CODE,'?')<>G_AMEND_CODE_DELETED
3064 AND KART.ARTICLE_VERSION_ID = VERS1.ARTICLE_VERSION_ID
3065 AND VERS.START_DATE > VERS1.START_DATE
3066 AND trunc(NVL(USG.ARTICLE_EFFECTIVE_DATE,SYSDATE)) BETWEEN trunc(VERS.START_DATE) AND NVL(VERS.END_DATE,SYSDATE)
3067 AND VERS.ARTICLE_STATUS = 'APPROVED'
3068 AND (EXISTS
3069 (SELECT 1
3070 FROM OKC_ARTICLE_ADOPTIONS ADP
3071 WHERE ADP.GLOBAL_ARTICLE_VERSION_ID = VERS.ARTICLE_VERSION_ID
3072 AND ADP.ADOPTION_TYPE = 'ADOPTED'
3073 AND ADP.ADOPTION_STATUS = 'APPROVED'
3074 AND ADP.LOCAL_ORG_ID = TMPL.ORG_ID)
3075 OR
3076 NOT EXISTS
3077 (SELECT 1
3078 FROM OKC_ARTICLE_ADOPTIONS ADP
3079 WHERE ADP.GLOBAL_ARTICLE_VERSION_ID = VERS1.ARTICLE_VERSION_ID
3080 AND ADP.ADOPTION_TYPE = 'ADOPTED'
3081 AND ADP.LOCAL_ORG_ID = TMPL.ORG_ID)
3082 ) ;
3083
3084 BEGIN
3085
3086 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3087 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1500: Entered check_art_effectivity');
3088 END IF;
3089
3090 get_qa_code_detail(p_qa_code => G_CHECK_ART_VALIDITY,
3091 x_perform_qa => l_perform_art_val_qa,
3092 x_qa_name => l_art_val_desc,
3093 x_severity_flag => l_art_val_severity,
3094 x_return_status => x_return_status);
3095 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3096 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
3097 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
3098 RAISE FND_API.G_EXC_ERROR ;
3099 END IF;
3100
3101 get_qa_code_detail(p_qa_code => G_CHECK_LATEST_VERSION,
3102 x_perform_qa => l_perform_lat_vers_qa,
3103 x_qa_name => l_lat_vers_desc,
3104 x_severity_flag => l_lat_vers_severity,
3105 x_return_status => x_return_status);
3106 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3107 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
3108 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
3109 RAISE FND_API.G_EXC_ERROR ;
3110 END IF;
3111
3112 -- 11.5.10+: check should be OR not AND
3113 --IF l_perform_art_val_qa='Y' AND l_perform_lat_vers_qa='Y' THEN
3114 IF l_perform_art_val_qa='Y' OR l_perform_lat_vers_qa='Y' THEN
3115
3116
3117 IF p_doc_type=G_TMPL_DOC_TYPE THEN
3118
3119 IF l_perform_art_val_qa='Y' THEN
3120
3121 /*
3122 OPEN l_get_tmpl_dates_csr;
3123 FETCH l_get_tmpl_dates_csr INTO l_start_date,l_end_date;
3124 CLOSE l_get_tmpl_dates_csr;
3125 */
3126
3127 IF (NOT do_validation(l_art_val_severity, p_doc_type)) THEN
3128 -- validation is not required
3129 RETURN;
3130 END IF;
3131
3132 FOR cr in l_check_art_tmpl_csr(l_article_effective_date) LOOP
3133 -- FOR cr in l_check_art_tmpl_csr(l_start_date,l_end_date) LOOP
3134 l_indx := px_qa_result_tbl.COUNT + 1;
3135
3136 px_qa_result_tbl(l_indx).error_record_type := G_ART_QA_TYPE;
3137 px_qa_result_tbl(l_indx).article_id := cr.article_id;
3138 px_qa_result_tbl(l_indx).deliverable_id := Null;
3139 px_qa_result_tbl(l_indx).title := Get_article_title(cr.id);
3140 px_qa_result_tbl(l_indx).section_name := Get_section_title(cr.scn_id);
3141 px_qa_result_tbl(l_indx).qa_code := G_CHECK_ART_VALIDITY;
3142 px_qa_result_tbl(l_indx).message_name := G_OKC_CHECK_TMPL_ART_VALIDITY;
3143 px_qa_result_tbl(l_indx).suggestion := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC,G_OKC_CHECK_TMPL_ART_VALID_S);
3144 px_qa_result_tbl(l_indx).error_severity := l_art_val_severity;
3145 px_qa_result_tbl(l_indx).problem_short_desc := l_art_val_desc;
3146 px_qa_result_tbl(l_indx).problem_details_short := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_CHECK_TMPL_ART_VALID_SH);
3147 px_qa_result_tbl(l_indx).problem_details := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_CHECK_TMPL_ART_VALIDITY, 'ARTICLE', px_qa_result_tbl(l_indx).title);
3148
3149 END LOOP; -- FOR cr in l_check_art_tmpl_csr
3150
3151 /* expert commented out
3152 -- check expert clauses for validtity
3153 IF (g_expert_enabled = 'Y') THEN
3154 FOR cr IN l_check_xprt_art_tmpl_csr(l_article_effective_date ) LOOP
3155 l_indx := px_qa_result_tbl.COUNT + 1;
3156
3157 px_qa_result_tbl(l_indx).error_record_type := G_EXP_QA_TYPE;
3158 px_qa_result_tbl(l_indx).article_id := cr.xprt_article_id;
3159 px_qa_result_tbl(l_indx).deliverable_id := Null;
3160 px_qa_result_tbl(l_indx).title :=
3161 get_xprt_article_title(cr.xprt_article_id);
3162 px_qa_result_tbl(l_indx).section_name := Null;
3163
3164 px_qa_result_tbl(l_indx).qa_code := G_CHECK_RUL_ART_VAL;
3165 px_qa_result_tbl(l_indx).message_name := G_OKC_CHECK_RUL_ART_VAL;
3166 px_qa_result_tbl(l_indx).suggestion :=
3167 OKC_TERMS_UTIL_PVT.Get_Message(G_OKC,G_OKC_CHECK_RUL_ART_VAL_S);
3168 px_qa_result_tbl(l_indx).error_severity := l_art_val_severity;
3169 px_qa_result_tbl(l_indx).problem_short_desc := l_art_val_desc;
3170 px_qa_result_tbl(l_indx).problem_details_short :=
3171 OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_CHECK_RUL_ART_VAL_SH);
3172 px_qa_result_tbl(l_indx).problem_details :=
3173 OKC_TERMS_UTIL_PVT.Get_Message(G_OKC,G_OKC_CHECK_RUL_ART_VAL,
3174 'RULE', cr.rule_name,
3175 'XPRT_ARTICLE', get_xprt_article_title(cr.xprt_article_id)
3176 );
3177 END LOOP;
3178 END IF; -- of IF (g_expert_enabled = 'Y') THEN
3179 */
3180
3181 END IF; -- of IF l_perform_art_val_qa='Y' THEN
3182
3183 ELSE
3184
3185 -- non TEMPLATE document types
3186 IF l_perform_lat_vers_qa ='Y' THEN
3187 FOR cr IN l_check_latest_version_csr LOOP
3188 IF ( p_qa_mode =G_AMEND_QA
3189 AND cr.amendment_operation_code IS NOT NULL
3190 )
3191 OR p_qa_mode<>G_AMEND_QA THEN
3192
3193 l_indx := px_qa_result_tbl.COUNT + 1;
3194
3195 l_indx1 := l_article_version_tbl.COUNT + 1;
3196 l_article_version_tbl(l_indx1) := cr.article_version_id;
3197
3198 px_qa_result_tbl(l_indx).error_record_type := G_ART_QA_TYPE;
3199 px_qa_result_tbl(l_indx).article_id := cr.article_id;
3200 px_qa_result_tbl(l_indx).deliverable_id := Null;
3201 px_qa_result_tbl(l_indx).title := Get_article_title(cr.id);
3202 px_qa_result_tbl(l_indx).section_name := Get_section_title(cr.scn_id);
3203 px_qa_result_tbl(l_indx).qa_code := G_CHECK_LATEST_VERSION;
3204 px_qa_result_tbl(l_indx).message_name := G_OKC_CHECK_LATEST_VERSION;
3205 px_qa_result_tbl(l_indx).suggestion := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC,G_OKC_CHECK_LATEST_VERSION_S);
3206 px_qa_result_tbl(l_indx).error_severity := l_lat_vers_severity;
3207 px_qa_result_tbl(l_indx).problem_short_desc := l_lat_vers_desc;
3208 px_qa_result_tbl(l_indx).problem_details_short := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_CHECK_LATEST_VERSION_SH);
3209 px_qa_result_tbl(l_indx).problem_details := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_CHECK_LATEST_VERSION, 'ARTICLE', px_qa_result_tbl(l_indx).title);
3210
3211 END IF; -- IF ( p_qa_mode =G_AMEND_QA
3212 END LOOP; -- FOR cr IN l_check_latest_version_csr LOOP
3213 END IF; -- IF l_perform_lat_vers_qa ='Y' THEN
3214
3215 IF l_perform_art_val_qa='Y' THEN
3216 FOR cr in l_check_art_doc_csr(l_article_effective_date) LOOP
3217
3218 IF ( p_qa_mode =G_AMEND_QA
3219 AND cr.amendment_operation_code IS NOT NULL
3220 )
3221 OR p_qa_mode<>G_AMEND_QA THEN
3222
3223 l_found := FALSE;
3224
3225 IF l_article_version_tbl.COUNT > 0 THEN
3226 FOR k in l_article_version_tbl.FIRST..l_article_version_tbl.LAST LOOP
3227 IF l_article_version_tbl(k) = cr.article_version_id THEN
3228 l_found := TRUE;
3229 END IF;
3230 END LOOP; -- FOR k in l_article_version_tbl.FI
3231 END IF; -- IF l_article_version_tbl.COUNT > 0 THEN
3232
3233 IF NOT l_found THEN
3234 l_indx := px_qa_result_tbl.COUNT + 1;
3235
3236 px_qa_result_tbl(l_indx).error_record_type := G_ART_QA_TYPE;
3237 px_qa_result_tbl(l_indx).article_id := cr.article_id;
3238 px_qa_result_tbl(l_indx).deliverable_id := Null;
3239 px_qa_result_tbl(l_indx).title := Get_article_title(cr.id);
3240 px_qa_result_tbl(l_indx).section_name := Get_section_title(cr.scn_id);
3241
3242 px_qa_result_tbl(l_indx).qa_code := G_CHECK_ART_VALIDITY;
3243 px_qa_result_tbl(l_indx).message_name := G_OKC_CHECK_ART_VALIDITY;
3244 px_qa_result_tbl(l_indx).suggestion := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC,G_OKC_CHECK_ART_VALIDITY_S);
3245 px_qa_result_tbl(l_indx).error_severity := l_art_val_severity;
3246 px_qa_result_tbl(l_indx).problem_short_desc := l_art_val_desc;
3247 px_qa_result_tbl(l_indx).problem_details_short := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_CHECK_ART_VALIDITY_SH);
3248 px_qa_result_tbl(l_indx).problem_details := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_CHECK_ART_VALIDITY, 'ARTICLE', px_qa_result_tbl(l_indx).title);
3249 END IF; -- IF NOT l_found THEN
3250
3251 END IF; -- IF ( p_qa_mode =G_AMEND_QA
3252
3253 END LOOP; -- FOR cr in l_check_art_doc_csr(l_article_effect
3254
3255 END IF; -- IF l_perform_art_val_qa='Y' THEN
3256
3257 END IF; -- IF p_doc_type=G_TMPL_DOC_TYPE ELSE branch
3258
3259 END IF; -- IF l_perform_art_val_qa='Y' OR l_perform_lat_vers_qa='Y'
3260
3261
3262 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3263 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1600: Leaving check_art_effectivity');
3264 END IF;
3265
3266 EXCEPTION
3267 WHEN FND_API.G_EXC_ERROR THEN
3268 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3269 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7800: Leaving check_art_effectivity : OKC_API.G_EXCEPTION_ERROR Exception');
3270 END IF;
3271
3272 IF l_get_tmpl_dates_csr%ISOPEN THEN
3273 CLOSE l_get_tmpl_dates_csr;
3274 END IF;
3275 IF l_check_art_tmpl_csr%ISOPEN THEN
3276 CLOSE l_check_art_tmpl_csr;
3277 END IF;
3278 IF l_check_art_doc_csr%ISOPEN THEN
3279 CLOSE l_check_art_doc_csr;
3280 END IF;
3281 IF l_check_latest_version_csr%ISOPEN THEN
3282 CLOSE l_check_latest_version_csr;
3283 END IF;
3284
3285 /* expert commented out
3286 IF l_check_xprt_art_tmpl_csr%ISOPEN THEN
3287 CLOSE l_check_xprt_art_tmpl_csr;
3288 END IF;
3289 */
3290
3291 x_return_status := G_RET_STS_ERROR ;
3292
3293 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3294 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3295 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7900: Leaving check_art_effectivity : OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
3296 END IF;
3297
3298 IF l_get_tmpl_dates_csr%ISOPEN THEN
3299 CLOSE l_get_tmpl_dates_csr;
3300 END IF;
3301 IF l_check_art_tmpl_csr%ISOPEN THEN
3302 CLOSE l_check_art_tmpl_csr;
3303 END IF;
3304 IF l_check_art_doc_csr%ISOPEN THEN
3305 CLOSE l_check_art_doc_csr;
3306 END IF;
3307 IF l_check_latest_version_csr%ISOPEN THEN
3308 CLOSE l_check_latest_version_csr;
3309 END IF;
3310
3311 /* expert commented out
3312 IF l_check_xprt_art_tmpl_csr%ISOPEN THEN
3313 CLOSE l_check_xprt_art_tmpl_csr;
3314 END IF;
3315 */
3316
3317 x_return_status := G_RET_STS_UNEXP_ERROR ;
3318
3319 WHEN OTHERS THEN
3320 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3321 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'8000: Leaving check_art_effectivity because of EXCEPTION: '||sqlerrm);
3322 END IF;
3323
3324 IF l_get_tmpl_dates_csr%ISOPEN THEN
3325 CLOSE l_get_tmpl_dates_csr;
3326 END IF;
3327 IF l_check_art_tmpl_csr%ISOPEN THEN
3328 CLOSE l_check_art_tmpl_csr;
3329 END IF;
3330 IF l_check_art_doc_csr%ISOPEN THEN
3331 CLOSE l_check_art_doc_csr;
3332 END IF;
3333 IF l_check_latest_version_csr%ISOPEN THEN
3334 CLOSE l_check_latest_version_csr;
3335 END IF;
3336
3337 /* expert commented out
3338 IF l_check_xprt_art_tmpl_csr%ISOPEN THEN
3339 CLOSE l_check_xprt_art_tmpl_csr;
3340 END IF;
3341 */
3342
3343 x_return_status := G_RET_STS_UNEXP_ERROR ;
3344 END check_art_effectivity;
3345
3346 -------------------------------------------
3347 -- PROCEDURE check_layout_template
3348 -------------------------------------------
3349 /* check_layout_template */
3350
3351 PROCEDURE check_layout_template (
3352 p_doc_type IN VARCHAR2,
3353 p_doc_id IN NUMBER,
3354 px_qa_result_tbl IN OUT NOCOPY qa_result_tbl_type,
3355 x_return_status OUT NOCOPY VARCHAR2)
3356 IS
3357
3358 l_api_name CONSTANT VARCHAR2(30) := 'G_CHECK_LAYOUT_TEMPLATE';
3359 l_indx NUMBER;
3360 l_severity OKC_QA_ERRORS_T.ERROR_SEVERITY%TYPE;
3361 l_desc OKC_QA_ERRORS_T.PROBLEM_SHORT_DESC%TYPE;
3362 l_perform_layout_tmpl_qa VARCHAR2(1);
3363 l_template_name OKC_TERMS_TEMPLATES_ALL.TEMPLATE_NAME%TYPE;
3364 l_print_template_id OKC_TERMS_TEMPLATES_ALL.PRINT_TEMPLATE_ID%TYPE;
3365
3366 CURSOR l_get_layout_tmpl_csr IS
3367 SELECT template_name, print_template_id
3368 FROM okc_terms_templates_all
3369 WHERE template_id = p_doc_id ;
3370
3371 BEGIN
3372
3373 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3374 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1500: Entered check_layout_template');
3375 END IF;
3376 get_qa_code_detail(p_qa_code => G_CHECK_LAYOUT_TMPL,
3377 x_perform_qa => l_perform_layout_tmpl_qa,
3378 x_qa_name => l_desc,
3379 x_severity_flag => l_severity,
3380 x_return_status => x_return_status);
3381 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3382 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
3383 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
3384 RAISE FND_API.G_EXC_ERROR ;
3385 END IF;
3386
3387
3388 IF l_perform_layout_tmpl_qa='Y' THEN
3389
3390 IF NOT do_validation(l_severity, p_doc_type) THEN
3391 -- validation is not required
3392 RETURN;
3393 END IF;
3394
3395 OPEN l_get_layout_tmpl_csr;
3396 FETCH l_get_layout_tmpl_csr INTO l_template_name, l_print_template_id;
3397 CLOSE l_get_layout_tmpl_csr;
3398
3399 IF l_print_template_id IS NULL THEN
3400
3401 l_indx := px_qa_result_tbl.COUNT + 1;
3402
3403 px_qa_result_tbl(l_indx).error_record_type := G_TMPL_QA_TYPE;
3404 px_qa_result_tbl(l_indx).article_id := Null;
3405 px_qa_result_tbl(l_indx).deliverable_id := Null;
3406 px_qa_result_tbl(l_indx).title := l_template_name;
3407 px_qa_result_tbl(l_indx).section_name := Null;
3408 px_qa_result_tbl(l_indx).qa_code := G_CHECK_LAYOUT_TMPL;
3409 px_qa_result_tbl(l_indx).message_name := G_OKC_CHECK_LAYOUT_TMPL;
3410 px_qa_result_tbl(l_indx).suggestion := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC,G_OKC_CHECK_LAYOUT_TMPL_S);
3411 px_qa_result_tbl(l_indx).error_severity := l_severity;
3412 px_qa_result_tbl(l_indx).problem_short_desc := l_desc;
3413 px_qa_result_tbl(l_indx).problem_details_short := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_CHECK_LAYOUT_TMPL);
3414 px_qa_result_tbl(l_indx).problem_details := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_CHECK_LAYOUT_TMPL);
3415
3416 END IF;
3417
3418 END IF; -- IF l_perform_layout_tmpl_qa='Y' THEN
3419
3420 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3421 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1600: Leaving check_layout_template');
3422 END IF;
3423
3424 EXCEPTION
3425 WHEN FND_API.G_EXC_ERROR THEN
3426 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3427 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7800: Leaving check_layout_template : OKC_API.G_EXCEPTION_ERROR Exception');
3428 END IF;
3429
3430 IF l_get_layout_tmpl_csr%ISOPEN THEN
3431 CLOSE l_get_layout_tmpl_csr;
3432 END IF;
3433
3434 x_return_status := G_RET_STS_ERROR ;
3435
3436 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3437 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3438 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7900: Leaving check_layout_template : OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
3439 END IF;
3440
3441 IF l_get_layout_tmpl_csr%ISOPEN THEN
3442 CLOSE l_get_layout_tmpl_csr;
3443 END IF;
3444
3445 x_return_status := G_RET_STS_UNEXP_ERROR ;
3446
3447 WHEN OTHERS THEN
3448 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3449 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'8000: Leaving check_layout_template because of EXCEPTION: '||sqlerrm);
3450 END IF;
3451
3452 IF l_get_layout_tmpl_csr%ISOPEN THEN
3453 CLOSE l_get_layout_tmpl_csr;
3454 END IF;
3455
3456 x_return_status := G_RET_STS_UNEXP_ERROR ;
3457
3458 END check_layout_template;
3459
3460
3461 --MLS for templates
3462 -------------------------------------------
3463 -- PROCEDURE Check_translated_tmpl_revision
3464 -------------------------------------------
3465 /* Check_translated_tmpl_revision */
3466
3467 PROCEDURE Check_translated_tmpl_rev (
3468 p_doc_type IN VARCHAR2,
3469 p_doc_id IN NUMBER,
3470 px_qa_result_tbl IN OUT NOCOPY qa_result_tbl_type,
3471 x_return_status OUT NOCOPY VARCHAR2)
3472 IS
3473
3474 l_api_name CONSTANT VARCHAR2(40) := 'G_CHECK_TRANSLATED_TMPL_REVISION';
3475 l_indx NUMBER;
3476 l_severity OKC_QA_ERRORS_T.ERROR_SEVERITY%TYPE;
3477 l_desc OKC_QA_ERRORS_T.PROBLEM_SHORT_DESC%TYPE;
3478 l_perform_trans_rev_qa VARCHAR2(1);
3479 l_template_name OKC_TERMS_TEMPLATES_ALL.TEMPLATE_NAME%TYPE;
3480 l_trans_tmpl_name OKC_TERMS_TEMPLATES_ALL.TEMPLATE_NAME%TYPE;
3481 l_template_id OKC_TERMS_TEMPLATES_ALL.TEMPLATE_ID%TYPE;
3482
3483 CURSOR l_translated_tmpl_revision_csr IS
3484 SELECT parent.template_id, parent.template_name, trans.template_name
3485 FROM okc_terms_templates_all parent, okc_terms_templates_all trans
3486 WHERE parent.template_id = trans.translated_from_tmpl_id
3487 AND trans.template_id = p_doc_id
3488 AND exists (SELECT 1
3489 FROM okc_terms_templates_all revision
3490 WHERE parent.template_id = revision.parent_template_id);
3491
3492
3493 BEGIN
3494
3495 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3496 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1500: Entered Check_translated_tmpl_revision');
3497 END IF;
3498 get_qa_code_detail(p_qa_code => G_CHECK_TRANS_TMPL_REVISION,
3499 x_perform_qa => l_perform_trans_rev_qa,
3500 x_qa_name => l_desc,
3501 x_severity_flag => l_severity,
3502 x_return_status => x_return_status);
3503 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3504 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
3505 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
3506 RAISE FND_API.G_EXC_ERROR ;
3507 END IF;
3508
3509
3510 IF l_perform_trans_rev_qa ='Y' THEN
3511
3512 IF NOT do_validation(l_severity, p_doc_type) THEN
3513 -- validation is not required
3514 RETURN;
3515 END IF;
3516
3517 OPEN l_translated_tmpl_revision_csr;
3518 FETCH l_translated_tmpl_revision_csr INTO l_template_id, l_template_name, l_trans_tmpl_name;
3519 CLOSE l_translated_tmpl_revision_csr;
3520
3521 IF l_template_id IS NOT NULL THEN
3522
3523 l_indx := px_qa_result_tbl.COUNT + 1;
3524
3525 px_qa_result_tbl(l_indx).error_record_type := G_TMPL_QA_TYPE;
3526 px_qa_result_tbl(l_indx).article_id := Null;
3527 px_qa_result_tbl(l_indx).deliverable_id := Null;
3528 px_qa_result_tbl(l_indx).title := l_trans_tmpl_name;
3529 px_qa_result_tbl(l_indx).section_name := Null;
3530 px_qa_result_tbl(l_indx).qa_code := G_CHECK_TRANS_TMPL_REVISION;
3531 px_qa_result_tbl(l_indx).message_name := G_OKC_CHECK_TRANS_TMPL_REV;
3532 px_qa_result_tbl(l_indx).suggestion := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC,G_OKC_CHECK_TRANS_TMPL_REV_S);
3533 px_qa_result_tbl(l_indx).error_severity := l_severity;
3534 px_qa_result_tbl(l_indx).problem_short_desc := l_desc;
3535 px_qa_result_tbl(l_indx).problem_details_short := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_CHECK_TRANS_TMPL_REV_SH,'TMPL1',l_template_name);
3536 px_qa_result_tbl(l_indx).problem_details := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_CHECK_TRANS_TMPL_REV,'TMPL1',l_template_name);
3537
3538 END IF;
3539
3540 END IF;
3541
3542 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3543 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1600: Leaving Check_translated_tmpl_revision');
3544 END IF;
3545
3546 EXCEPTION
3547 WHEN FND_API.G_EXC_ERROR THEN
3548 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3549 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7800: Leaving Check_translated_tmpl_revision : OKC_API.G_EXCEPTION_ERROR Exception');
3550 END IF;
3551
3552 IF l_translated_tmpl_revision_csr%ISOPEN THEN
3553 CLOSE l_translated_tmpl_revision_csr;
3554 END IF;
3555
3556 x_return_status := G_RET_STS_ERROR ;
3557
3558 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3559 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3560 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7900: Leaving Check_translated_tmpl_revision : OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
3561 END IF;
3562
3563 IF l_translated_tmpl_revision_csr%ISOPEN THEN
3564 CLOSE l_translated_tmpl_revision_csr;
3565 END IF;
3566
3567 x_return_status := G_RET_STS_UNEXP_ERROR ;
3568
3569 WHEN OTHERS THEN
3570 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3571 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'8000: Leaving Check_translated_tmpl_revision because of EXCEPTION: '||sqlerrm);
3572 END IF;
3573
3574 IF l_translated_tmpl_revision_csr%ISOPEN THEN
3575 CLOSE l_translated_tmpl_revision_csr;
3576 END IF;
3577
3578 x_return_status := G_RET_STS_UNEXP_ERROR ;
3579
3580 END Check_translated_tmpl_rev;
3581
3582
3583 --MLS for templates
3584
3585 -------------------------------------------
3586 -- PROCEDURE Check_translated_tmpl_effectivity
3587 -------------------------------------------
3588 /* Check_translated_tmpl_effectivity */
3589
3590 PROCEDURE Check_translated_tmpl_eff (
3591 p_doc_type IN VARCHAR2,
3592 p_doc_id IN NUMBER,
3593 px_qa_result_tbl IN OUT NOCOPY qa_result_tbl_type,
3594 x_return_status OUT NOCOPY VARCHAR2)
3595 IS
3596
3597 l_api_name CONSTANT VARCHAR2(40) := 'G_CHECK_TRANSLATED_TMPL_EFFECTIVITY';
3598 l_indx NUMBER;
3599 l_severity OKC_QA_ERRORS_T.ERROR_SEVERITY%TYPE;
3600 l_desc OKC_QA_ERRORS_T.PROBLEM_SHORT_DESC%TYPE;
3601 l_perform_trans_tmpl_eff_qa VARCHAR2(1);
3602 l_template_name OKC_TERMS_TEMPLATES_ALL.TEMPLATE_NAME%TYPE;
3603 l_trans_tmpl_name OKC_TERMS_TEMPLATES_ALL.TEMPLATE_NAME%TYPE;
3604 l_template_id OKC_TERMS_TEMPLATES_ALL.TEMPLATE_ID%TYPE;
3605
3606 CURSOR l_get_trans_tmpl_csr IS
3607 SELECT parent.template_id, parent.template_name, trans.template_name
3608 FROM okc_terms_templates_all parent, okc_terms_templates_all trans
3609 WHERE ( trunc(sysdate) >= nvl(trunc(parent.end_date),sysdate+1)
3610 OR parent.status_code = 'ON_HOLD' )
3611 AND parent.template_id = trans.translated_from_tmpl_id
3612 AND trans.template_id = p_doc_id;
3613
3614
3615 BEGIN
3616
3617 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3618 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1500: Entered Check_translated_tmpl_effectivity');
3619 END IF;
3620 get_qa_code_detail(p_qa_code => G_CHECK_TRANS_TMPL_EFF,
3621 x_perform_qa => l_perform_trans_tmpl_eff_qa,
3622 x_qa_name => l_desc,
3623 x_severity_flag => l_severity,
3624 x_return_status => x_return_status);
3625 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3626 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
3627 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
3628 RAISE FND_API.G_EXC_ERROR ;
3629 END IF;
3630
3631
3632 IF l_perform_trans_tmpl_eff_qa ='Y' THEN
3633
3634 IF NOT do_validation(l_severity, p_doc_type) THEN
3635 -- validation is not required
3636 RETURN;
3637 END IF;
3638
3639 OPEN l_get_trans_tmpl_csr;
3640 FETCH l_get_trans_tmpl_csr INTO l_template_id, l_template_name, l_trans_tmpl_name;
3641 CLOSE l_get_trans_tmpl_csr;
3642
3643 IF l_template_id IS NOT NULL THEN
3644
3645 l_indx := px_qa_result_tbl.COUNT + 1;
3646
3647 px_qa_result_tbl(l_indx).error_record_type := G_TMPL_QA_TYPE;
3648 px_qa_result_tbl(l_indx).article_id := Null;
3649 px_qa_result_tbl(l_indx).deliverable_id := Null;
3650 px_qa_result_tbl(l_indx).title := l_trans_tmpl_name;
3651 px_qa_result_tbl(l_indx).section_name := Null;
3652 px_qa_result_tbl(l_indx).qa_code := G_CHECK_TRANS_TMPL_EFF;
3653 px_qa_result_tbl(l_indx).message_name := G_OKC_CHECK_TRANS_TMPL_EFF ;
3654 px_qa_result_tbl(l_indx).suggestion := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC,G_OKC_CHECK_TRANS_TMPL_EFF_S);
3655 px_qa_result_tbl(l_indx).error_severity := l_severity;
3656 px_qa_result_tbl(l_indx).problem_short_desc := l_desc;
3657 px_qa_result_tbl(l_indx).problem_details_short := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_CHECK_TRANS_TMPL_EFF_SH,'TMPL1',l_template_name);
3658 px_qa_result_tbl(l_indx).problem_details := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_CHECK_TRANS_TMPL_EFF,'TMPL1',l_template_name);
3659
3660 END IF;
3661
3662 END IF;
3663
3664 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3665 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1600: Leaving Check_translated_tmpl_effectivity');
3666 END IF;
3667
3668 EXCEPTION
3669 WHEN FND_API.G_EXC_ERROR THEN
3670 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3671 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7800: Leaving Check_translated_tmpl_effectivity : OKC_API.G_EXCEPTION_ERROR Exception');
3672 END IF;
3673
3674 IF l_get_trans_tmpl_csr%ISOPEN THEN
3675 CLOSE l_get_trans_tmpl_csr;
3676 END IF;
3677
3678 x_return_status := G_RET_STS_ERROR ;
3679
3680 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3681 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3682 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7900: Leaving Check_translated_tmpl_effectivity : OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
3683 END IF;
3684
3685 IF l_get_trans_tmpl_csr%ISOPEN THEN
3686 CLOSE l_get_trans_tmpl_csr;
3687 END IF;
3688
3689 x_return_status := G_RET_STS_UNEXP_ERROR ;
3690
3691 WHEN OTHERS THEN
3692 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3693 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'8000: Leaving Check_translated_tmpl_effectivity because of EXCEPTION: '||sqlerrm);
3694 END IF;
3695
3696 IF l_get_trans_tmpl_csr%ISOPEN THEN
3697 CLOSE l_get_trans_tmpl_csr;
3698 END IF;
3699
3700 x_return_status := G_RET_STS_UNEXP_ERROR ;
3701
3702 END Check_translated_tmpl_eff;
3703
3704 --MLS for templates
3705
3706
3707 -------------------------------------------
3708 -- PROCEDURE check_articles_exist
3709 -------------------------------------------
3710 /* check_articles_exist */
3711
3712 PROCEDURE check_articles_exist (
3713 p_doc_type IN VARCHAR2,
3714 p_doc_id IN NUMBER,
3715 px_qa_result_tbl IN OUT NOCOPY qa_result_tbl_type,
3716 x_return_status OUT NOCOPY VARCHAR2)
3717 IS
3718
3719 l_api_name CONSTANT VARCHAR2(30) := 'check_articles_exist';
3720 l_indx NUMBER;
3721 l_severity OKC_QA_ERRORS_T.ERROR_SEVERITY%TYPE;
3722 l_desc OKC_QA_ERRORS_T.PROBLEM_SHORT_DESC%TYPE;
3723 l_perform_articles_exist_qa VARCHAR2(1);
3724
3725 BEGIN
3726
3727 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3728 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1500: Entered check_articles_exist');
3729 END IF;
3730
3731 get_qa_code_detail(p_qa_code => G_CHECK_ART_EXT,
3732 x_perform_qa => l_perform_articles_exist_qa,
3733 x_qa_name => l_desc,
3734 x_severity_flag => l_severity,
3735 x_return_status => x_return_status);
3736 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3737 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
3738 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
3739 RAISE FND_API.G_EXC_ERROR ;
3740 END IF;
3741
3742
3743 IF (l_perform_articles_exist_qa <> 'Y') THEN
3744 RETURN;
3745 END IF;
3746
3747 IF NOT do_validation(l_severity, p_doc_type) then
3748 -- validation is not required
3749 RETURN;
3750 END IF;
3751
3752 IF (p_doc_type = 'TEMPLATE' AND l_article_tbl.COUNT = 0) then
3753
3754 -- l_article_tbl is a global variable containing the template articles.
3755 -- indicates no clauses are present.
3756 l_indx := px_qa_result_tbl.COUNT + 1;
3757
3758 px_qa_result_tbl(l_indx).error_record_type := G_TMPL_QA_TYPE;
3759 px_qa_result_tbl(l_indx).article_id := Null;
3760 px_qa_result_tbl(l_indx).deliverable_id := Null;
3761 px_qa_result_tbl(l_indx).title := g_template_name;
3762 px_qa_result_tbl(l_indx).section_name := Null;
3763 px_qa_result_tbl(l_indx).qa_code := G_CHECK_ART_EXT;
3764 px_qa_result_tbl(l_indx).message_name := G_OKC_CHECK_ART_EXT;
3765 px_qa_result_tbl(l_indx).suggestion :=
3766 OKC_TERMS_UTIL_PVT.get_message(G_OKC,G_OKC_CHECK_ART_EXT_S);
3767 px_qa_result_tbl(l_indx).error_severity := l_severity;
3768 px_qa_result_tbl(l_indx).problem_short_desc := l_desc;
3769 px_qa_result_tbl(l_indx).problem_details_short :=
3770 OKC_TERMS_UTIL_PVT.get_message(G_OKC, G_OKC_CHECK_ART_EXT_SH);
3771 px_qa_result_tbl(l_indx).problem_details :=
3772 OKC_TERMS_UTIL_PVT.get_message(G_OKC, G_OKC_CHECK_ART_EXT);
3773 END IF;
3774
3775
3776 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3777 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1600: Leaving check_articles_exist');
3778 END IF;
3779
3780 EXCEPTION
3781 WHEN FND_API.G_EXC_ERROR THEN
3782 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3783 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7800: Leaving check_articles_exist : OKC_API.G_EXCEPTION_ERROR Exception');
3784 END IF;
3785
3786 x_return_status := G_RET_STS_ERROR ;
3787
3788 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3789 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3790 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7900: Leaving check_articles_exist : OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
3791 END IF;
3792
3793 x_return_status := G_RET_STS_UNEXP_ERROR ;
3794
3795 WHEN OTHERS THEN
3796 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3797 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'8000: Leaving check_articles_exist because of EXCEPTION: '||sqlerrm);
3798 END IF;
3799
3800 x_return_status := G_RET_STS_UNEXP_ERROR ;
3801
3802 END check_articles_exist;
3803
3804 -------------------------------------------
3805 -- PROCEDURE check_validate_draft_articles
3806 -------------------------------------------
3807 /* check_validate_draft_articles gets all the draft versions
3808 in a template and validates them for errors.
3809 */
3810 PROCEDURE check_validate_draft_articles (
3811 p_doc_type IN VARCHAR2,
3812 p_doc_id IN NUMBER,
3813 px_qa_result_tbl IN OUT NOCOPY qa_result_tbl_type,
3814 x_return_status OUT NOCOPY VARCHAR2)
3815 IS
3816
3817 /*
3818 TYPE draft_art_rec_type IS RECORD (
3819 article_id OKC_ARTICLES_ALL.ARTICLE_ID%TYPE,
3820 article_version_id OKC_ARTICLE_VERSIONS.ARTICLE_VERSION_ID%TYPE,
3821 title OKC_QA_ERRORS_T.title%TYPE,
3822 section OKC_TMPL_DRAFT_CLAUSES.SECTION_NAME%TYPE
3823 );
3824
3825 TYPE draft_art_tbl_type IS TABLE OF draft_art_rec_type INDEX BY BINARY_INTEGER;
3826 */
3827
3828 TYPE art_id_tbl_type IS TABLE of OKC_TMPL_DRAFT_CLAUSES.ARTICLE_ID%TYPE INDEX BY BINARY_INTEGER;
3829 TYPE ver_id_tbl_type IS TABLE of OKC_TMPL_DRAFT_CLAUSES.ARTICLE_VERSION_ID%TYPE INDEX BY BINARY_INTEGER;
3830 TYPE title_tbl_type IS TABLE of OKC_ARTICLE_VERSIONS.DISPLAY_NAME%TYPE INDEX BY BINARY_INTEGER;
3831 TYPE sec_tbl_type IS TABLE of OKC_TMPL_DRAFT_CLAUSES.SECTION_NAME%TYPE INDEX BY BINARY_INTEGER;
3832
3833 l_api_name CONSTANT VARCHAR2(30) := 'check_validate_draft_articles';
3834 l_indx NUMBER;
3835
3836 --l_draft_art_tbl draft_art_tbl_type;
3837
3838 l_draft_art_id_tbl art_id_tbl_type;
3839 l_draft_ver_id_tbl ver_id_tbl_type;
3840 l_draft_title_tbl title_tbl_type;
3841 l_draft_sec_tbl sec_tbl_type;
3842
3843 l_art_ver_tbl OKC_ART_BLK_PVT.NUM_TBL_TYPE;
3844 l_validation_results OKC_ART_BLK_PVT.VALIDATION_TBL_TYPE;
3845 l_msg_count NUMBER;
3846 l_msg_data VARCHAR2(2000);
3847 l_qa_return_status VARCHAR2(1);
3848
3849 l_perform_qa_art_typ VARCHAR2(1);
3850 l_severity_art_typ OKC_QA_ERRORS_T.ERROR_SEVERITY%TYPE;
3851 l_desc_art_typ OKC_QA_ERRORS_T.PROBLEM_SHORT_DESC%TYPE;
3852
3853 l_perform_qa_def_sec VARCHAR2(1);
3854 l_severity_def_sec OKC_QA_ERRORS_T.ERROR_SEVERITY%TYPE;
3855 l_desc_def_sec OKC_QA_ERRORS_T.PROBLEM_SHORT_DESC%TYPE;
3856
3857 l_perform_qa_inv_var VARCHAR2(1);
3858 l_severity_inv_var OKC_QA_ERRORS_T.ERROR_SEVERITY%TYPE;
3859 l_desc_inv_var OKC_QA_ERRORS_T.PROBLEM_SHORT_DESC%TYPE;
3860
3861 l_perform_qa_inv_val VARCHAR2(1);
3862 l_severity_inv_val OKC_QA_ERRORS_T.ERROR_SEVERITY%TYPE;
3863 l_desc_inv_val OKC_QA_ERRORS_T.PROBLEM_SHORT_DESC%TYPE;
3864
3865 l_title OKC_QA_ERRORS_T.TITLE%TYPE;
3866 l_section OKC_QA_ERRORS_T.SECTION_NAME%TYPE;
3867
3868 CURSOR l_draft_art_csr IS
3869 SELECT DRA.article_id, DRA.article_version_id,
3870 --NVL(VER.display_name, ART.article_title) title, DRA.section_name section
3871 NVL(VER.display_name, ART.article_title) title,NVL(DRA.section_name, '*') section
3872 FROM OKC_TMPL_DRAFT_CLAUSES DRA,
3873 OKC_ARTICLES_ALL ART, OKC_ARTICLE_VERSIONS VER
3874 WHERE DRA.template_id = p_doc_id and
3875 DRA.selected_yn = 'Y' and
3876 DRA.article_id = ART.article_id and
3877 VER.article_version_id = DRA.article_version_id
3878 -- additional check to ensure that we are not checking some
3879 -- orphaned records in the OKC_TMPL_DRAFT_CLAUSES table
3880 AND EXISTS (SELECT '1' FROM OKC_K_ARTICLES_B KART WHERE
3881 KART.document_type = p_doc_type AND
3882 KART.document_id = p_doc_id AND
3883 KART.sav_sae_id = DRA.article_id);
3884
3885 PROCEDURE get_art_title_sec(
3886 p_art_id IN NUMBER,
3887 x_title OUT NOCOPY VARCHAR2,
3888 x_section OUT NOCOPY VARCHAR2)
3889 IS
3890 BEGIN
3891 x_title := NULL;
3892 x_section := NULL;
3893
3894 /*
3895 IF l_draft_art_tbl.COUNT > 0 THEN
3896 FOR i IN l_draft_art_tbl.FIRST..l_draft_art_tbl.LAST LOOP
3897 IF (l_draft_art_tbl(i).article_id = p_art_id) THEN
3898 x_title := l_draft_art_tbl(i).title;
3899 x_section := l_draft_art_tbl(i).section;
3900 EXIT;
3901 END IF;
3902 END LOOP;
3903 END IF;
3904
3905 */
3906
3907 IF l_draft_art_id_tbl.COUNT > 0 THEN
3908 FOR i IN l_draft_art_id_tbl.FIRST..l_draft_art_id_tbl.LAST LOOP
3909 IF (l_draft_art_id_tbl(i) = p_art_id) THEN
3910 x_title := l_draft_title_tbl(i);
3911 x_section := l_draft_sec_tbl(i);
3912 EXIT;
3913 END IF;
3914 END LOOP;
3915 END IF;
3916
3917 END get_art_title_sec;
3918
3919 BEGIN
3920
3921 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3922 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1500: Entered check_validate_draft_articles');
3923 END IF;
3924
3925 IF (p_doc_type <> 'TEMPLATE') THEN
3926 RETURN; -- no other doc type is supported.
3927 END IF;
3928
3929 -- Fix for the bug# 4448520, validate the template with Revision status
3930 IF (g_status_code NOT in ('DRAFT', 'REJECTED', 'REVISION')) THEN
3931 RETURN; -- no article validation for other statuses.
3932 END IF;
3933 --
3934 -- muteshev
3935 -- Insert records in OKC_TMPL_DRAFT_CLAUSES before validation
3936 -- bug 4159533 start
3937 declare
3938 l_drafts_present varchar2(1);
3939 x_return_status varchar2(150);
3940 x_msg_data varchar2(2000);
3941 x_msg_count number;
3942 begin
3943 OKC_TERMS_UTIL_PVT.create_tmpl_clauses_to_submit (
3944 p_api_version => 1,
3945 p_init_msg_list => FND_API.G_FALSE,
3946 p_template_id => p_doc_id,
3947 p_org_id => g_org_id,
3948 x_drafts_present => l_drafts_present,
3949 x_return_status => x_return_status,
3950 x_msg_count => x_msg_count,
3951 x_msg_data => x_msg_data);
3952 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3953 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3954 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
3955 RAISE FND_API.G_EXC_ERROR;
3956 END IF;
3957 end;
3958 -- bug 4159533 end
3959 -- the above code simulates template submittion else the below
3960 -- l_draft_art_csr will never get any rows unless user submits template himself
3961 -- (template submittion populates the OKC_TMPL_DRAFT_CLAUSES table)
3962 --
3963 OPEN l_draft_art_csr;
3964 FETCH l_draft_art_csr BULK COLLECT INTO l_draft_art_id_tbl, l_draft_ver_id_tbl,
3965 l_draft_title_tbl, l_draft_sec_tbl;
3966 CLOSE l_draft_art_csr;
3967
3968 --IF NOT (l_draft_art_tbl.COUNT > 0) THEN
3969 IF NOT (l_draft_art_id_tbl.COUNT > 0) THEN
3970 RETURN; -- no articles to validate
3971 END IF;
3972
3973 get_qa_code_detail(p_qa_code => G_CHECK_ART_TYP,
3974 x_perform_qa => l_perform_qa_art_typ,
3975 x_qa_name => l_desc_art_typ,
3976 x_severity_flag => l_severity_art_typ,
3977 x_return_status => x_return_status);
3978 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3979 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
3980 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
3981 RAISE FND_API.G_EXC_ERROR ;
3982 END IF;
3983
3984 get_qa_code_detail(p_qa_code => G_CHECK_ART_DEF_SEC,
3985 x_perform_qa => l_perform_qa_def_sec,
3986 x_qa_name => l_desc_def_sec,
3987 x_severity_flag => l_severity_def_sec,
3988 x_return_status => x_return_status);
3989 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3990 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
3991 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
3992 RAISE FND_API.G_EXC_ERROR ;
3993 END IF;
3994
3995 get_qa_code_detail(p_qa_code => G_CHECK_ART_INV_VAR,
3996 x_perform_qa => l_perform_qa_inv_var,
3997 x_qa_name => l_desc_inv_var,
3998 x_severity_flag => l_severity_inv_var,
3999 x_return_status => x_return_status);
4000 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
4001 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
4002 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
4003 RAISE FND_API.G_EXC_ERROR ;
4004 END IF;
4005
4006 get_qa_code_detail(p_qa_code => G_CHECK_ART_INV_VAL,
4007 x_perform_qa => l_perform_qa_inv_val,
4008 x_qa_name => l_desc_inv_val,
4009 x_severity_flag => l_severity_inv_val,
4010 x_return_status => x_return_status);
4011 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
4012 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
4013 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
4014 RAISE FND_API.G_EXC_ERROR ;
4015 END IF;
4016
4017
4018 l_art_ver_tbl := OKC_ART_BLK_PVT.NUM_TBL_TYPE();
4019 --FOR i in 1..l_draft_art_tbl.COUNT LOOP
4020 FOR i in 1..l_draft_art_id_tbl.COUNT LOOP
4021 l_art_ver_tbl.EXTEND;
4022 --l_art_ver_tbl(i) := l_draft_art_tbl(i).article_version_id;
4023 l_art_ver_tbl(i) := l_draft_ver_id_tbl(i);
4024 END LOOP;
4025
4026 -- Call the bulk validate api which does the actual validation,
4027 -- if the appropriate flags are set.
4028 IF ((l_perform_qa_art_typ ='Y' AND do_validation(l_severity_art_typ, p_doc_type)) OR
4029 (l_perform_qa_def_sec ='Y' AND do_validation(l_severity_def_sec, p_doc_type)) OR
4030 (l_perform_qa_inv_var ='Y' AND do_validation(l_severity_inv_var, p_doc_type)) OR
4031 (l_perform_qa_inv_val ='Y' AND do_validation(l_severity_inv_val, p_doc_type))
4032 ) THEN
4033
4034 OKC_ART_BLK_PVT.validate_article_versions_blk(
4035 p_api_version => 1.0 ,
4036 p_init_msg_list => FND_API.G_FALSE,
4037 p_commit => FND_API.G_FALSE,
4038 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
4039 x_return_status => x_return_status,
4040 x_msg_count => l_msg_count,
4041 x_msg_data => l_msg_data,
4042 p_org_id => g_org_id,
4043 p_art_ver_tbl => l_art_ver_tbl,
4044 x_qa_return_status => l_qa_return_status,
4045 x_validation_results => l_validation_results
4046 );
4047 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
4048 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
4049 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
4050 RAISE FND_API.G_EXC_ERROR ;
4051 END IF;
4052
4053 ELSE
4054 -- nothing to validate
4055 RETURN;
4056 END IF;
4057
4058 -- Bug#4086586: Removed Switch/Case stetments as this does not work on 8.1.7.4.
4059
4060 IF(l_validation_results.COUNT > 0) THEN
4061 -- some validation errors where found, populate the px_qa_result_tbl with appropriate values.
4062 FOR i in 1..l_validation_results.COUNT LOOP
4063
4064 -- CASE (l_validation_results(i).error_code)
4065 -- WHEN (G_CHECK_ART_TYP) THEN
4066 IF (l_validation_results(i).error_code) = (G_CHECK_ART_TYP) THEN
4067
4068 -- insert into px_qa_result_tbl only if the appropriate validation level is set
4069 IF (l_perform_qa_art_typ ='Y' AND
4070 do_validation(l_severity_art_typ, p_doc_type) )THEN
4071
4072 l_indx := px_qa_result_tbl.COUNT + 1;
4073 px_qa_result_tbl(l_indx).error_record_type := G_ART_QA_TYPE;
4074 px_qa_result_tbl(l_indx).article_id := l_validation_results(i).article_id;
4075 px_qa_result_tbl(l_indx).deliverable_id := Null;
4076
4077 get_art_title_sec(
4078 p_art_id => l_validation_results(i).article_id,
4079 x_title =>l_title,
4080 x_section =>l_section);
4081
4082 px_qa_result_tbl(l_indx).title := l_title;
4083 px_qa_result_tbl(l_indx).section_name := l_section;
4084 px_qa_result_tbl(l_indx).qa_code := l_validation_results(i).error_code;
4085
4086 px_qa_result_tbl(l_indx).message_name := G_OKC_CHECK_ART_TYP;
4087 px_qa_result_tbl(l_indx).suggestion :=
4088 OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_CHECK_ART_TYP_S);
4089 px_qa_result_tbl(l_indx).error_severity := l_severity_art_typ;
4090 px_qa_result_tbl(l_indx).problem_short_desc := l_desc_art_typ;
4091 px_qa_result_tbl(l_indx).problem_details_short :=
4092 --OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_CHECK_ART_TYP_SH);
4093 -- use same as problem_details
4094 l_validation_results(i).error_message;
4095 px_qa_result_tbl(l_indx).problem_details :=
4096 l_validation_results(i).error_message;
4097 END IF;
4098
4099 -- WHEN (G_CHECK_ART_DEF_SEC) THEN
4100
4101 ELSIF (l_validation_results(i).error_code) = (G_CHECK_ART_DEF_SEC) THEN
4102 -- insert into px_qa_result_tbl only if the appropriate validation level is set
4103 IF (l_perform_qa_def_sec ='Y' AND
4104 do_validation(l_severity_def_sec, p_doc_type) )THEN
4105
4106 l_indx := px_qa_result_tbl.COUNT + 1;
4107 px_qa_result_tbl(l_indx).error_record_type := G_ART_QA_TYPE;
4108 px_qa_result_tbl(l_indx).article_id := l_validation_results(i).article_id;
4109 px_qa_result_tbl(l_indx).deliverable_id := Null;
4110
4111 get_art_title_sec(
4112 p_art_id => l_validation_results(i).article_id,
4113 x_title =>l_title,
4114 x_section =>l_section);
4115
4116 px_qa_result_tbl(l_indx).title := l_title;
4117 px_qa_result_tbl(l_indx).section_name := l_section;
4118 px_qa_result_tbl(l_indx).qa_code := l_validation_results(i).error_code;
4119
4120 px_qa_result_tbl(l_indx).message_name := G_OKC_CHECK_ART_DEF_SEC;
4121 px_qa_result_tbl(l_indx).suggestion :=
4122 OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_CHECK_ART_DEF_SEC_S);
4123 px_qa_result_tbl(l_indx).error_severity := l_severity_def_sec;
4124 px_qa_result_tbl(l_indx).problem_short_desc := l_desc_def_sec;
4125 px_qa_result_tbl(l_indx).problem_details_short :=
4126 --OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_CHECK_ART_DEF_SEC_SH);
4127 -- use same as problem_details
4128 l_validation_results(i).error_message;
4129 px_qa_result_tbl(l_indx).problem_details :=
4130 l_validation_results(i).error_message;
4131 END IF;
4132 -- WHEN (G_CHECK_ART_INV_VAR) THEN
4133 ELSIF (l_validation_results(i).error_code) = (G_CHECK_ART_INV_VAR) THEN
4134
4135 -- insert into px_qa_result_tbl only if the appropriate validation level is set
4136 IF (l_perform_qa_inv_var ='Y' AND
4137 do_validation(l_severity_inv_var, p_doc_type) )THEN
4138
4139 l_indx := px_qa_result_tbl.COUNT + 1;
4140 px_qa_result_tbl(l_indx).error_record_type := G_ART_QA_TYPE;
4141 px_qa_result_tbl(l_indx).article_id := l_validation_results(i).article_id;
4142 px_qa_result_tbl(l_indx).deliverable_id := Null;
4143
4144 get_art_title_sec(
4145 p_art_id => l_validation_results(i).article_id,
4146 x_title =>l_title,
4147 x_section =>l_section);
4148
4149 px_qa_result_tbl(l_indx).title := l_title;
4150 px_qa_result_tbl(l_indx).section_name := l_section;
4151 px_qa_result_tbl(l_indx).qa_code := l_validation_results(i).error_code;
4152
4153 px_qa_result_tbl(l_indx).message_name := G_OKC_CHECK_ART_INV_VAR;
4154 px_qa_result_tbl(l_indx).suggestion :=
4155 OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_CHECK_ART_INV_VAR_S);
4156 px_qa_result_tbl(l_indx).error_severity := l_severity_inv_var;
4157 px_qa_result_tbl(l_indx).problem_short_desc := l_desc_inv_var;
4158 px_qa_result_tbl(l_indx).problem_details_short :=
4159 --OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_CHECK_ART_INV_VAR_SH);
4160 -- use same as problem_details
4161 l_validation_results(i).error_message;
4162 px_qa_result_tbl(l_indx).problem_details :=
4163 l_validation_results(i).error_message;
4164 END IF;
4165
4166 -- WHEN (G_CHECK_ART_INV_VAL) THEN
4167 ELSIF (l_validation_results(i).error_code) = (G_CHECK_ART_INV_VAL) THEN
4168
4169 -- insert into px_qa_result_tbl only if the appropriate validation level is set
4170 IF (l_perform_qa_inv_val ='Y' AND
4171 do_validation(l_severity_inv_val, p_doc_type) )THEN
4172
4173 l_indx := px_qa_result_tbl.COUNT + 1;
4174 px_qa_result_tbl(l_indx).error_record_type := G_ART_QA_TYPE;
4175 px_qa_result_tbl(l_indx).article_id := l_validation_results(i).article_id;
4176 px_qa_result_tbl(l_indx).deliverable_id := Null;
4177
4178 get_art_title_sec(
4179 p_art_id => l_validation_results(i).article_id,
4180 x_title =>l_title,
4181 x_section =>l_section);
4182
4183 px_qa_result_tbl(l_indx).title := l_title;
4184 px_qa_result_tbl(l_indx).section_name := l_section;
4185 px_qa_result_tbl(l_indx).qa_code := l_validation_results(i).error_code;
4186
4187 px_qa_result_tbl(l_indx).message_name := G_OKC_CHECK_ART_INV_VAL;
4188 px_qa_result_tbl(l_indx).suggestion :=
4189 OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_CHECK_ART_INV_VAL_S);
4190 px_qa_result_tbl(l_indx).error_severity := l_severity_inv_val;
4191 px_qa_result_tbl(l_indx).problem_short_desc := l_desc_inv_val;
4192 px_qa_result_tbl(l_indx).problem_details_short :=
4193 --OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_CHECK_ART_INV_VAL_SH);
4194 -- use same as problem_details
4195 l_validation_results(i).error_message;
4196 px_qa_result_tbl(l_indx).problem_details :=
4197 l_validation_results(i).error_message;
4198 END IF;
4199
4200 ELSE
4201 -- all other errors are treated as error conditions
4202 l_indx := px_qa_result_tbl.COUNT + 1;
4203 px_qa_result_tbl(l_indx).error_record_type := G_ART_QA_TYPE;
4204 px_qa_result_tbl(l_indx).article_id := l_validation_results(i).article_id;
4205 px_qa_result_tbl(l_indx).deliverable_id := Null;
4206
4207 get_art_title_sec(
4208 p_art_id => l_validation_results(i).article_id,
4209 x_title =>l_title,
4210 x_section =>l_section);
4211
4212 px_qa_result_tbl(l_indx).title := l_title;
4213 px_qa_result_tbl(l_indx).section_name := l_section;
4214 px_qa_result_tbl(l_indx).qa_code := l_validation_results(i).error_code;
4215
4216 px_qa_result_tbl(l_indx).message_name := Null;
4217 px_qa_result_tbl(l_indx).suggestion := Null;
4218 px_qa_result_tbl(l_indx).error_severity := G_QA_STS_ERROR;
4219 px_qa_result_tbl(l_indx).problem_short_desc := Null;
4220 px_qa_result_tbl(l_indx).problem_details_short := Null;
4221 px_qa_result_tbl(l_indx).problem_details :=
4222 l_validation_results(i).error_message;
4223 END IF;
4224 -- END CASE;
4225 END LOOP;
4226 END IF;
4227
4228
4229 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4230 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1600: Leaving check_validate_draft_articles');
4231 END IF;
4232
4233 EXCEPTION
4234 WHEN FND_API.G_EXC_ERROR THEN
4235 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4236 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7800: Leaving check_validate_draft_articles : OKC_API.G_EXCEPTION_ERROR Exception');
4237 END IF;
4238
4239 IF l_draft_art_csr%ISOPEN THEN
4240 CLOSE l_draft_art_csr;
4241 END IF;
4242
4243 x_return_status := G_RET_STS_ERROR ;
4244
4245 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4246 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4247 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7900: Leaving check_validate_draft_articles : OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
4248 END IF;
4249
4250 IF l_draft_art_csr%ISOPEN THEN
4251 CLOSE l_draft_art_csr;
4252 END IF;
4253
4254 x_return_status := G_RET_STS_UNEXP_ERROR ;
4255
4256 WHEN OTHERS THEN
4257 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4258 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'8000: Leaving check_validate_draft_articles because of EXCEPTION: '||sqlerrm);
4259 END IF;
4260
4261 IF l_draft_art_csr%ISOPEN THEN
4262 CLOSE l_draft_art_csr;
4263 END IF;
4264
4265 x_return_status := G_RET_STS_UNEXP_ERROR ;
4266
4267 END check_validate_draft_articles;
4268
4269
4270 -- For Bug# 6979012
4271 -------------------------------------------
4272 -- PROCEDURE check_rejected_clauses
4273 -------------------------------------------
4274 /* check_rejected_clauses checks for rejected clauses added to the template and
4275 displays a warning for each one of them */
4276 PROCEDURE check_rejected_clauses (
4277 p_doc_type IN VARCHAR2,
4278 p_doc_id IN NUMBER,
4279 px_qa_result_tbl IN OUT NOCOPY qa_result_tbl_type,
4280 x_return_status OUT NOCOPY VARCHAR2)
4281 IS
4282 l_api_name CONSTANT VARCHAR2(30) := 'check_rejected_clauses';
4283 l_indx NUMBER;
4284 l_severity OKC_QA_ERRORS_T.ERROR_SEVERITY%TYPE;
4285 l_desc OKC_QA_ERRORS_T.PROBLEM_SHORT_DESC%TYPE;
4286 l_perform_articles_exist_qa VARCHAR2(1);
4287
4288 CURSOR l_rejected_art_csr IS
4289 SELECT DRA.article_id article_id, DRA.article_version_id,
4290 --NVL(VER.display_name, ART.article_title) title, DRA.section_name section
4291 NVL(VER.display_name, ART.article_title) title,NVL(DRA.section_name, '*') section
4292 FROM OKC_TMPL_DRAFT_CLAUSES DRA,
4293 OKC_ARTICLES_ALL ART, OKC_ARTICLE_VERSIONS VER
4294 WHERE DRA.template_id = p_doc_id and
4295 DRA.selected_yn = 'Y' and
4296 DRA.article_id = ART.article_id and
4297 VER.article_version_id = DRA.article_version_id
4298 -- additional check to ensure that we are not checking some
4299 -- orphaned records in the OKC_TMPL_DRAFT_CLAUSES table
4300 AND EXISTS (SELECT '1' FROM OKC_K_ARTICLES_B KART WHERE
4301 KART.document_type = p_doc_type AND
4302 KART.document_id = p_doc_id AND
4303 KART.sav_sae_id = DRA.article_id)
4304 AND VER.article_status='REJECTED';
4305
4306 BEGIN
4307
4308 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4309 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1500: Entered check_rejected_clauses');
4310 END IF;
4311
4312 IF (p_doc_type <> 'TEMPLATE') THEN
4313 RETURN; -- no other doc type is supported.
4314 END IF;
4315
4316 IF (g_status_code NOT in ('DRAFT', 'REJECTED')) THEN
4317 RETURN; -- no article validation for other statuses.
4318 END IF;
4319
4320 get_qa_code_detail(p_qa_code => G_CHECK_ART_REJECTED,
4321 x_perform_qa => l_perform_articles_exist_qa,
4322 x_qa_name => l_desc,
4323 x_severity_flag => l_severity,
4324 x_return_status => x_return_status);
4325 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
4326 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
4327 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
4328 RAISE FND_API.G_EXC_ERROR ;
4329 END IF;
4330
4331
4332 IF (l_perform_articles_exist_qa <> 'Y') THEN
4333 RETURN;
4334 END IF;
4335
4336 IF NOT do_validation(l_severity, p_doc_type) then
4337 -- validation is not required
4338 RETURN;
4339 END IF;
4340
4341 FOR l_rejected_art in l_rejected_art_csr LOOP
4342 l_indx := px_qa_result_tbl.COUNT + 1;
4343 px_qa_result_tbl(l_indx).error_record_type := G_ART_QA_TYPE;
4344 px_qa_result_tbl(l_indx).article_id := l_rejected_art.article_id;
4345 px_qa_result_tbl(l_indx).deliverable_id := Null;
4346 px_qa_result_tbl(l_indx).title := l_rejected_art.title;
4347 px_qa_result_tbl(l_indx).section_name := l_rejected_art.section;
4348 px_qa_result_tbl(l_indx).qa_code := G_CHECK_ART_REJECTED;
4349 px_qa_result_tbl(l_indx).message_name := G_OKC_CHECK_ART_REJECTED;
4350 px_qa_result_tbl(l_indx).suggestion :=OKC_TERMS_UTIL_PVT.Get_Message(G_OKC,G_OKC_CHECK_ART_REJECTED_S);
4351 px_qa_result_tbl(l_indx).error_severity := l_severity;
4352 px_qa_result_tbl(l_indx).problem_short_desc := l_desc;
4353 px_qa_result_tbl(l_indx).problem_details_short :=OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_CHECK_ART_REJECTED);
4354 px_qa_result_tbl(l_indx).problem_details :=OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_CHECK_ART_REJECTED);
4355 END LOOP;
4356 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4357 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1600: Leaving check_rejected_clauses');
4358 END IF;
4359
4360 EXCEPTION
4361 WHEN FND_API.G_EXC_ERROR THEN
4362 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4363 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7800: Leaving check_rejected_clauses : OKC_API.G_EXCEPTION_ERROR Exception');
4364 END IF;
4365
4366 IF l_rejected_art_csr%ISOPEN THEN
4367 CLOSE l_rejected_art_csr;
4368 END IF;
4369
4370 x_return_status := G_RET_STS_ERROR ;
4371
4372 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4373 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4374 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7900: Leaving check_rejected_clauses : OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
4375 END IF;
4376
4377 IF l_rejected_art_csr%ISOPEN THEN
4378 CLOSE l_rejected_art_csr;
4379 END IF;
4380
4381 x_return_status := G_RET_STS_UNEXP_ERROR ;
4382
4383 WHEN OTHERS THEN
4384 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4385 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'8000: Leaving check_rejected_clauses because of EXCEPTION: '||sqlerrm);
4386 END IF;
4387
4388 IF l_rejected_art_csr%ISOPEN THEN
4389 CLOSE l_rejected_art_csr;
4390 END IF;
4391
4392 x_return_status := G_RET_STS_UNEXP_ERROR ;
4393
4394 END check_rejected_clauses;
4395 -- Changes for Bug# 6979012 Ends
4396
4397
4398 -->>>>>>>>>>>>>>>>>> INTERNAL QA Check API PROCEDURES >>>>>>>>>>>>>>>>>>
4399
4400 ------------------------------------------------------------
4401 -- PROCEDURE QA_DOC main api for validating the documents
4402 ------------------------------------------------------------
4403
4404 /* 11.5.10+ modified to accept additional in parameter p_validation_level
4405 p_validation_level 'A' do all qa checks
4406 p_validation_level 'E' do only error checks
4407 applies only to p_doc_type='TEMPLATE'
4408 */
4409 PROCEDURE QA_DOC(
4410 p_qa_mode IN VARCHAR2,
4411 p_doc_type IN VARCHAR2,
4412 p_doc_id IN NUMBER,
4413 x_return_status OUT NOCOPY VARCHAR2,
4414 x_msg_count OUT NOCOPY NUMBER,
4415 x_msg_data OUT NOCOPY VARCHAR2,
4416 x_sequence_id OUT NOCOPY NUMBER,
4417 x_qa_result_tbl OUT NOCOPY qa_result_tbl_type,
4418 x_qa_return_status OUT NOCOPY VARCHAR2,
4419 p_validation_level IN VARCHAR2,
4420 p_run_expert_flag IN VARCHAR2 DEFAULT 'Y') -- Bug 5186245
4421 IS
4422
4423 l_api_name CONSTANT VARCHAR2(30) := 'QA_Doc';
4424 l_now DATE;
4425 i NUMBER := 0;
4426 q NUMBER := 0;
4427 l_error_found Boolean := FALSE;
4428 l_warning_found Boolean := FALSE;
4429 l_expert_articles_tbl OKC_XPRT_UTIL_PVT.expert_articles_tbl_type;
4430 l_perform_qa BOOLEAN := TRUE;
4431
4432 CURSOR l_get_qa_detail_csr IS
4433 SELECT fnd.lookup_code qa_code,
4434 fnd.meaning qa_name,
4435 nvl(qa.severity_flag,G_QA_STS_WARNING) severity_flag ,
4436 decode(fnd.enabled_flag,'N','N','Y',decode(qa.enable_qa_yn,'N','N','Y'),'Y') perform_qa
4437 FROM FND_LOOKUPS FND, OKC_DOC_QA_LISTS QA
4438 WHERE QA.DOCUMENT_TYPE(+)=p_doc_type
4439 AND QA.QA_CODE(+) = FND.LOOKUP_CODE
4440 AND Fnd.LOOKUP_TYPE=G_QA_LOOKUP;
4441
4442 CURSOR l_get_doc_articles_csr IS
4443 SELECT KART.ID ID,
4444 KART.SAV_SAE_ID ARTICLE_ID,
4445 KART.ARTICLE_VERSION_ID ARTICLE_VERSION_ID,
4446 KART.AMENDMENT_OPERATION_CODE AMENDMENT_OPERATION_CODE,
4447 KART.AMENDMENT_DESCRIPTION AMENDMENT_DESCRIPTION,
4448 KART.SCN_ID SCN_ID,
4449 OKC_TERMS_UTIL_PVT.get_article_name(KART.SAV_SAE_ID ,KART.ARTICLE_VERSION_ID) TITLE,
4450 Decode(ART.standard_yn,'N',KART.ref_article_id,NULL) STD_ART_ID
4451 FROM OKC_K_ARTICLES_B KART,
4452 OKC_ARTICLES_ALL ART,
4453 OKC_ARTICLE_VERSIONS VERS
4454 WHERE KART.DOCUMENT_TYPE = p_doc_type
4455 AND KART.DOCUMENT_ID = p_doc_id
4456 AND KART.ARTICLE_VERSION_ID = VERS.ARTICLE_VERSION_ID
4457 AND KART.SAV_SAE_ID = ART.ARTICLE_ID;
4458
4459 /* modified in 11.5.10+
4460 CURSOR l_get_tmpl_articles_csr IS
4461 SELECT KART.ID ID,
4462 KART.SAV_SAE_ID ARTICLE_ID,
4463 STD.ARTICLE_ID STD_ART_ID,
4464 KART.ARTICLE_VERSION_ID ARTICLE_VERSION_ID,
4465 KART.AMENDMENT_OPERATION_CODE AMENDMENT_OPERATION_CODE,
4466 KART.AMENDMENT_DESCRIPTION AMENDMENT_DESCRIPTION,
4467 KART.SCN_ID SCN_ID,
4468 OKC_TERMS_UTIL_PVT.get_article_name(KART.SAV_SAE_ID ,VERS.ARTICLE_VERSION_ID) TITLE
4469 FROM OKC_K_ARTICLES_B KART, OKC_ARTICLE_VERSIONS VERS, OKC_ARTICLE_VERSIONS STD
4470 WHERE KART.DOCUMENT_TYPE = p_doc_type
4471 AND KART.DOCUMENT_ID = p_doc_id
4472 AND STD.ARTICLE_VERSION_ID(+) = VERS.STD_ARTICLE_VERSION_ID
4473 AND KART.SAV_SAE_ID = VERS.ARTICLE_ID
4474 AND nvl(VERS.START_DATE,sysdate) = ( SELECT nvl(MAX(START_DATE),sysdate)
4475 FROM OKC_ARTICLE_VERSIONS
4476 WHERE ARTICLE_ID=VERS.ARTICLE_ID);
4477 */
4478
4479 -- 11.5.10+ modified cursor to get the version from OKC_TERMS_UTIL_PVT.get_latest_tmpl_art_version_id
4480 CURSOR l_get_tmpl_articles_csr IS
4481 SELECT KART.ID ID,
4482 KART.SAV_SAE_ID ARTICLE_ID,
4483 STD.ARTICLE_ID STD_ART_ID,
4484 KART.ARTICLE_VERSION_ID ARTICLE_VERSION_ID,
4485 KART.AMENDMENT_OPERATION_CODE AMENDMENT_OPERATION_CODE,
4486 KART.AMENDMENT_DESCRIPTION AMENDMENT_DESCRIPTION,
4487 KART.SCN_ID SCN_ID,
4488 OKC_TERMS_UTIL_PVT.get_article_name(KART.SAV_SAE_ID ,VERS.ARTICLE_VERSION_ID) TITLE
4489 FROM OKC_K_ARTICLES_B KART, OKC_ARTICLE_VERSIONS VERS, OKC_ARTICLE_VERSIONS STD
4490 WHERE KART.DOCUMENT_TYPE = p_doc_type
4491 AND KART.DOCUMENT_ID = p_doc_id
4492 AND STD.ARTICLE_VERSION_ID(+) = VERS.STD_ARTICLE_VERSION_ID
4493 AND KART.SAV_SAE_ID = VERS.ARTICLE_ID
4494 AND VERS.ARTICLE_VERSION_ID = OKC_TERMS_UTIL_PVT.get_latest_tmpl_art_version_id(
4495 KART.sav_sae_id,
4496 g_start_date,
4497 g_end_date,
4498 g_status_code,
4499 p_doc_type,
4500 p_doc_id
4501 );
4502
4503 /* expert commented out
4504 -- new cursor to fetch the expert articles and their details
4505 CURSOR l_get_xprt_articles_csr IS
4506 SELECT XPRT.clause_id article_id,
4507 ver.article_version_id article_version_id,
4508 Nvl(ver.display_name, art.article_title) title,
4509 xprt.rule_id rule_id,
4510 xprt.rule_name rule_name
4511 FROM OKC_XPRT_CLAUSES_V XPRT,
4512 OKC_ARTICLES_ALL ART, OKC_ARTICLE_VERSIONS VER
4513 WHERE XPRT.template_id = p_doc_id
4514 AND ART.article_id = XPRT.clause_id
4515 AND VER.article_id = ART.article_id
4516 AND VER.article_version_id = OKC_TERMS_UTIL_PVT.get_latest_tmpl_art_version_id(
4517 ART.article_id,
4518 g_start_date,
4519 g_end_date,
4520 g_status_code,
4521 p_doc_type,
4522 p_doc_id
4523 );
4524 */
4525
4526 CURSOR l_get_sections_csr IS
4527 SELECT ID ID,
4528 AMENDMENT_OPERATION_CODE AMENDMENT_OPERATION_CODE,
4529 AMENDMENT_DESCRIPTION AMENDMENT_DESCRIPTION,
4530 SCN_ID SCN_ID,
4531 SCN_CODE SCN_CODE,
4532 DECODE(LABEL,NULL,HEADING,
4533 okc_terms_util_pvt.get_message('OKC',
4534 'OKC_TERMS_LABEL_AND_NAME',
4535 'LABEL', LABEL,
4536 'NAME', HEADING)) HEADING
4537 FROM OKC_SECTIONS_B
4538 WHERE DOCUMENT_TYPE = p_doc_type
4539 AND DOCUMENT_ID = p_doc_id;
4540
4541 CURSOR l_get_effective_date IS
4542 SELECT nvl(ARTICLE_EFFECTIVE_DATE ,sysdate)
4543 FROM OKC_TEMPLATE_USAGES
4544 WHERE DOCUMENT_TYPE=p_doc_type
4545 AND DOCUMENT_ID=p_doc_id;
4546
4547 /* Modified in 11.5.10+ to get additonal template information
4548 CURSOR l_get_effective_date_template IS
4549 SELECT start_date, end_date
4550 FROM okc_terms_templates_all
4551 WHERE template_id = p_doc_id;
4552 */
4553
4554 -- 11.5.10+ modified cursor to get additional template details
4555 CURSOR l_get_effective_date_template IS
4556 SELECT start_date, end_date,
4557 nvl(contract_expert_enabled, 'N'), nvl(status_code, 'DRAFT'),
4558 template_name,org_id
4559 FROM okc_terms_templates_all
4560 WHERE template_id = p_doc_id;
4561
4562 /* 11.5.10+ replace with global variables
4563 l_start_date DATE;
4564 l_end_date DATE;
4565 */
4566
4567 BEGIN
4568 g_validation_level :='A';
4569 g_expert_enabled :='N';
4570 -- because of GSCC warnings the default values are removed
4571 -- but it's not allowed to assign something to IN parameters
4572 -- don't think it's good idea to remove default values
4573 -- it can cause regression
4574 -- if p_qa_mode is null then p_qa_mode := G_NORMAL_QA; end if;
4575 -- if p_validation_level is null then p_validation_level := 'A'; end if;
4576 l_now := SYSDATE;
4577
4578 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4579 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2100: Entered QA_Doc');
4580 END IF;
4581 -- Initialize API return status to success
4582 x_return_status := G_RET_STS_SUCCESS;
4583 x_qa_return_status := G_QA_STS_SUCCESS;
4584 g_validation_level := p_validation_level;
4585
4586 IF p_doc_type = G_TMPL_DOC_TYPE THEN
4587
4588 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4589 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2110: Opening cursor l_get_effective_date_template');
4590 END IF;
4591 OPEN l_get_effective_date_template;
4592 --FETCH l_get_effective_date_template INTO l_start_date, l_end_date;
4593 FETCH l_get_effective_date_template INTO g_start_date, g_end_date,
4594 g_expert_enabled, g_status_code, g_template_name, g_org_id;
4595 IF l_get_effective_date_template%NOTFOUND THEN
4596 l_perform_qa := FALSE;
4597 END IF;
4598 CLOSE l_get_effective_date_template;
4599
4600 IF NVL(g_end_date,sysdate) >= sysdate THEN
4601 IF g_start_date > sysdate THEN
4602 l_article_effective_date := g_start_date;
4603 ELSE
4604 l_article_effective_date := sysdate;
4605 END IF;
4606 ELSE
4607 l_article_effective_date := g_end_date;
4608 END IF;
4609
4610 ELSE
4611 -- doc type not Template
4612 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4613 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2120: Opening cursor l_get_effective_date');
4614 END IF;
4615 OPEN l_get_effective_date;
4616 FETCH l_get_effective_date INTO l_article_effective_date;
4617 IF l_get_effective_date%NOTFOUND THEN
4618 l_perform_qa := FALSE;
4619 END IF;
4620 CLOSE l_get_effective_date;
4621 END IF;
4622
4623 IF l_perform_QA then
4624
4625 -- Reset PL/SQl Tables
4626 x_qa_result_tbl.DELETE;
4627 l_article_tbl.DELETE;
4628 l_section_tbl.DELETE;
4629 l_qa_detail_tbl.DELETE;
4630
4631 /* expert commented out
4632 l_xprt_article_tbl.DELETE;
4633 */
4634
4635 -- Get all QA Detail
4636 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4637 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2130: Before FOR cr IN l_get_qa_detail_csr LOOP');
4638 END IF;
4639 q := 0;
4640 FOR cr IN l_get_qa_detail_csr LOOP
4641
4642 l_qa_detail_tbl(q).qa_code := cr.qa_code;
4643 l_qa_detail_tbl(q).qa_name := cr.qa_name;
4644 l_qa_detail_tbl(q).severity_flag :=cr.severity_flag;
4645 l_qa_detail_tbl(q).perform_qa :=cr.perform_qa;
4646
4647 IF (l_qa_detail_tbl(q).qa_code = G_CHECK_ART_EXT) THEN
4648
4649 -- Modify the severity level of the qa check for existence of clauses
4650 -- to 'W' (warning) if the template is expert enabled. It is 'E' otherwise.
4651 IF (g_expert_enabled = 'Y') THEN
4652 l_qa_detail_tbl(q).severity_flag := G_QA_STS_WARNING;
4653 END IF;
4654
4655 -- bug 4083727 - muteshev - start
4656 -- Modify the severity level of the qa check for existence of clauses
4657 -- to 'W' (warning) if the template contains deliverables. It is 'E' otherwise.
4658 if okc_terms_util_grp.Is_Deliverable_Exist(
4659 p_api_version => 1,
4660 p_init_msg_list => FND_API.G_FALSE,
4661 x_return_status => x_return_status,
4662 x_msg_data => x_msg_data,
4663 x_msg_count => x_msg_count,
4664 p_doc_type => 'TEMPLATE',
4665 p_doc_id => p_doc_id
4666 ) is not null then
4667 l_qa_detail_tbl(q).severity_flag := G_QA_STS_WARNING;
4668 end if;
4669 -- bug 4083727 - muteshev - end
4670
4671 END IF;
4672 q := q +1;
4673 END LOOP;
4674
4675
4676 -- populate l_article_tbl with all the articles.
4677 IF p_doc_type <> G_TMPL_DOC_TYPE THEN
4678 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4679 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2140: Before FOR cr IN l_get_doc_articles_csr LOOP');
4680 END IF;
4681 i := 0;
4682 FOR cr IN l_get_doc_articles_csr LOOP
4683 i := i+1;
4684 l_article_tbl(i).id := cr.id;
4685 l_article_tbl(i).article_id := cr.article_id;
4686 l_article_tbl(i).std_art_id := cr.std_art_id;
4687 l_article_tbl(i).article_version_id := cr.article_version_id;
4688 l_article_tbl(i).amendment_operation_code := cr.amendment_operation_code;
4689 l_article_tbl(i).amendment_description := cr.amendment_description;
4690 l_article_tbl(i).scn_id := cr.scn_id;
4691 l_article_tbl(i).title := cr.title;
4692 END LOOP;
4693 ELSE
4694 -- doc type is TEMPLATE, if expert enabled get the expert clauses
4695 /* expert commented out
4696 IF (g_expert_enabled = 'Y') then
4697 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4698 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2150: Before OPEN l_get_xprt_articles_csr');
4699 END IF;
4700 OPEN l_get_xprt_articles_csr;
4701 FETCH l_get_xprt_articles_csr BULK COLLECT INTO l_xprt_article_tbl;
4702 CLOSE l_get_xprt_articles_csr;
4703 END IF;
4704 */
4705
4706 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4707 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2160: Before FOR cr IN l_get_tmpl_articles_csr');
4708 END IF;
4709 i := 0;
4710 FOR cr IN l_get_tmpl_articles_csr LOOP
4711 i := i+1;
4712 l_article_tbl(i).id := cr.id;
4713 l_article_tbl(i).article_id := cr.article_id;
4714 l_article_tbl(i).std_art_id := cr.std_art_id;
4715 l_article_tbl(i).article_version_id := cr.article_version_id;
4716 l_article_tbl(i).amendment_operation_code := cr.amendment_operation_code;
4717 l_article_tbl(i).amendment_description := cr.amendment_description;
4718 l_article_tbl(i).scn_id := cr.scn_id;
4719 l_article_tbl(i).title := cr.title;
4720 END LOOP;
4721 END IF;
4722
4723 -- populate l_section_tbl with all the sections.
4724 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4725 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2170: Before FOR cr IN l_get_sections_csr LOOP');
4726 END IF;
4727 i := 0;
4728 FOR cr IN l_get_sections_csr LOOP
4729 i := i+1;
4730 l_section_tbl(i).id := cr.id;
4731 l_section_tbl(i).amendment_operation_code := cr.amendment_operation_code;
4732 l_section_tbl(i).amendment_description := cr.amendment_description;
4733 l_section_tbl(i).scn_id := cr.scn_id;
4734 l_section_tbl(i).heading := cr.heading;
4735 l_section_tbl(i).scn_code := cr.scn_code;
4736 END LOOP;
4737
4738 --****************************************--
4739 -- Run set of validation --
4740 --****************************************--
4741
4742 ------------------------------------------------------------
4743 -- QA Check for Layout Template to be run for templates ONLY
4744 ------------------------------------------------------------
4745 IF p_doc_type=G_TMPL_DOC_TYPE THEN
4746 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4747 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2180: calling check_layout_template');
4748 END IF;
4749 check_layout_template(
4750 p_doc_type => p_doc_type,
4751 p_doc_id => p_doc_id,
4752 px_qa_result_tbl => x_qa_result_tbl,
4753 x_return_status => x_return_status
4754 );
4755 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
4756 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
4757 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
4758 RAISE FND_API.G_EXC_ERROR ;
4759 END IF;
4760
4761 --call new internal procedure to check for article existence
4762 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4763 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2190: calling check_articles_exist');
4764 END IF;
4765 check_articles_exist (
4766 p_doc_type => p_doc_type,
4767 p_doc_id => p_doc_id,
4768 px_qa_result_tbl => x_qa_result_tbl,
4769 x_return_status => x_return_status
4770 );
4771 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
4772 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
4773 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
4774 RAISE FND_API.G_EXC_ERROR ;
4775 END IF;
4776
4777 --call new internal procedure to validate any draft articles
4778 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4779 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2200: calling check_validate_draft_articles');
4780 END IF;
4781 check_validate_draft_articles(
4782 p_doc_type => p_doc_type,
4783 p_doc_id => p_doc_id,
4784 px_qa_result_tbl => x_qa_result_tbl,
4785 x_return_status => x_return_status
4786 );
4787 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
4788 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
4789 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
4790 RAISE FND_API.G_EXC_ERROR ;
4791 END IF;
4792
4793 -- For Bug# 6979012
4794 --call new internal procedure to give a warning for rejected articles
4795 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4796 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2200: calling check_rejected_clauses');
4797 END IF;
4798 check_rejected_clauses(
4799 p_doc_type => p_doc_type,
4800 p_doc_id => p_doc_id,
4801 px_qa_result_tbl => x_qa_result_tbl,
4802 x_return_status => x_return_status
4803 );
4804 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
4805 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
4806 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
4807 RAISE FND_API.G_EXC_ERROR ;
4808 END IF;
4809 -- Changes for Bug# 6979012 Ends
4810
4811 --MLS for templates
4812 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4813 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2202: calling Check_translated_tmpl_revision');
4814 END IF;
4815 Check_translated_tmpl_rev(
4816 p_doc_type => p_doc_type,
4817 p_doc_id => p_doc_id,
4818 px_qa_result_tbl => x_qa_result_tbl,
4819 x_return_status => x_return_status
4820 );
4821 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
4822 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
4823 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
4824 RAISE FND_API.G_EXC_ERROR ;
4825 END IF;
4826
4827 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4828 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2205: calling Check_translated_tmpl_effectivity');
4829 END IF;
4830 Check_translated_tmpl_eff(
4831 p_doc_type => p_doc_type,
4832 p_doc_id => p_doc_id,
4833 px_qa_result_tbl => x_qa_result_tbl,
4834 x_return_status => x_return_status
4835 );
4836 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
4837 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
4838 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
4839 RAISE FND_API.G_EXC_ERROR ;
4840 END IF;
4841 --MLS for templates
4842
4843
4844 END IF; -- p_doc_type=G_TMPL_DOC_TYPE
4845
4846
4847 --------------------------------------------
4848 -- QA Check for Incompatible articles
4849 --------------------------------------------
4850 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4851 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2210: calling check_validate_draft_articles');
4852 END IF;
4853 Check_Incomp_and_alternate(
4854 p_qa_mode => p_qa_mode,
4855 p_doc_type => p_doc_type,
4856 p_doc_id => p_doc_id,
4857 px_qa_result_tbl => x_qa_result_tbl,
4858 x_return_status => x_return_status
4859 );
4860 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
4861 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
4862 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
4863 RAISE FND_API.G_EXC_ERROR ;
4864 END IF;
4865
4866 --------------------------------------------
4867 -- QA Check for Duplicate articles
4868 --------------------------------------------
4869 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4870 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2220: calling Check_duplicate_articles');
4871 END IF;
4872 Check_duplicate_articles(
4873 p_qa_mode => p_qa_mode,
4874 p_doc_type => p_doc_type,
4875 p_doc_id => p_doc_id,
4876 px_qa_result_tbl => x_qa_result_tbl,
4877 x_return_status => x_return_status
4878 );
4879 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
4880 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
4881 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
4882 RAISE FND_API.G_EXC_ERROR ;
4883 END IF;
4884
4885 --------------------------------------------
4886 -- QA Check for variable-doc type usage
4887 --------------------------------------------
4888 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4889 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2230: calling Check_var_doc_type_usage');
4890 END IF;
4891 Check_var_doc_type_usage(
4892 p_qa_mode => p_qa_mode,
4893 p_doc_type => p_doc_type,
4894 p_doc_id => p_doc_id,
4895 px_qa_result_tbl => x_qa_result_tbl,
4896 x_return_status => x_return_status
4897 );
4898 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
4899 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
4900 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
4901 RAISE FND_API.G_EXC_ERROR ;
4902 END IF;
4903
4904 ----------------------------------------------------------------
4905 -- QA Check for article effectivity and old version of article
4906 ----------------------------------------------------------------
4907 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4908 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2240: calling Check_art_effectivity');
4909 END IF;
4910 Check_art_effectivity(
4911 p_qa_mode => p_qa_mode,
4912 p_doc_type => p_doc_type,
4913 p_doc_id => p_doc_id,
4914 px_qa_result_tbl => x_qa_result_tbl,
4915 x_return_status => x_return_status
4916 );
4917 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
4918 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
4919 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
4920 RAISE FND_API.G_EXC_ERROR ;
4921 END IF;
4922
4923
4924 -----------------------------------------------------------------------
4925 -- QA Check for non-entered user variables(Both External and Internal)
4926 -----------------------------------------------------------------------
4927 IF p_doc_type <>G_TMPL_DOC_TYPE THEN
4928
4929 --------------------------------------------
4930 -- QA Check for User Variables
4931 --------------------------------------------
4932 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4933 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2250: calling Check_variables');
4934 END IF;
4935 Check_variables(
4936 p_doc_type => p_doc_type,
4937 p_doc_id => p_doc_id,
4938 px_qa_result_tbl => x_qa_result_tbl,
4939 x_return_status => x_return_status
4940 );
4941 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
4942 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
4943 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
4944 RAISE FND_API.G_EXC_ERROR ;
4945 END IF;
4946
4947 ---------------------------------------------------------
4948 -- QA Check for unassigned articles
4949 ---------------------------------------------------------
4950 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4951 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2260: calling Check_unassigned_articles');
4952 END IF;
4953 Check_unassigned_articles(
4954 p_doc_type => p_doc_type,
4955 p_doc_id => p_doc_id,
4956 px_qa_result_tbl => x_qa_result_tbl,
4957 x_return_status => x_return_status
4958 );
4959 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
4960 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
4961 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
4962 RAISE FND_API.G_EXC_ERROR ;
4963 END IF;
4964
4965 ---------------------------------------------------------------
4966 -- QA Check for empty sections
4967 ---------------------------------------------------------------
4968 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4969 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2270: calling Check_empty_sections');
4970 END IF;
4971 Check_empty_sections(
4972 p_doc_type => p_doc_type,
4973 p_doc_id => p_doc_id,
4974 px_qa_result_tbl => x_qa_result_tbl,
4975 x_return_status => x_return_status
4976 );
4977 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
4978 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
4979 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
4980 RAISE FND_API.G_EXC_ERROR ;
4981 END IF;
4982
4983 ---------------------------------------------------------------
4984 -- QA Check for inactive template
4985 ---------------------------------------------------------------
4986 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4987 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2280: calling check_inactive_template');
4988 END IF;
4989 check_inactive_template(
4990 p_doc_type => p_doc_type,
4991 p_doc_id => p_doc_id,
4992 px_qa_result_tbl => x_qa_result_tbl,
4993 x_return_status => x_return_status
4994 );
4995 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
4996 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
4997 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
4998 RAISE FND_API.G_EXC_ERROR ;
4999 END IF;
5000
5001 IF p_qa_mode = G_AMEND_QA THEN
5002
5003 ------------------------------------------------------------
5004 -- QA Check for Amended article with no description
5005 ------------------------------------------------------------
5006 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5007 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2290: calling Check_article_Amend_No_Texts');
5008 END IF;
5009 Check_article_Amend_No_Texts(
5010 p_doc_type => p_doc_type,
5011 p_doc_id => p_doc_id,
5012 px_qa_result_tbl => x_qa_result_tbl,
5013 x_return_status => x_return_status
5014 );
5015 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
5016 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
5017 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
5018 RAISE FND_API.G_EXC_ERROR ;
5019 END IF;
5020
5021 ------------------------------------------------------------
5022 -- QA Check for Amended section with no description
5023 ------------------------------------------------------------
5024 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5025 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2300: calling Check_Section_Amend_No_Texts');
5026 END IF;
5027 Check_Section_Amend_No_Texts(
5028 p_doc_type => p_doc_type,
5029 p_doc_id => p_doc_id,
5030 px_qa_result_tbl => x_qa_result_tbl,
5031 x_return_status => x_return_status
5032 );
5033 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
5034 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
5035 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
5036 RAISE FND_API.G_EXC_ERROR ;
5037 END IF;
5038
5039 END IF; -- IF p_qa_mode = G_AMEND_QA THEN
5040
5041 END IF; -- IF p_doc_type <>G_TMPL_DOC_TYPE THEN
5042
5043 ------------------------------------------------------------
5044 -- QA Check for expert
5045 ------------------------------------------------------------
5046 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5047 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2310: calling OKC_EXPRT_UTIL_GRP.contract_expert_bv');
5048 END IF;
5049
5050 IF p_doc_type = G_TMPL_DOC_TYPE THEN
5051 --Added 11.5.10+ CE
5052 OKC_XPRT_UTIL_PVT.validate_template_for_expert (
5053 p_api_version => 1,
5054 p_init_msg_list => FND_API.G_FALSE,
5055 p_template_id => p_doc_id,
5056 x_msg_data => x_msg_data,
5057 x_msg_count => x_msg_count,
5058 x_qa_result_tbl => x_qa_result_tbl,
5059 x_return_status => x_return_status);
5060
5061 ELSE
5062 IF p_run_expert_flag = 'Y' AND p_doc_type <> 'OKS' THEN -- Added for Bug 5186245
5063 -- Bug# 4874729. Not invoking expert validation for OKS doc type.
5064
5065 OKC_XPRT_UTIL_PVT.contract_expert_bv (
5066 p_api_version => 1,
5067 p_init_msg_list => FND_API.G_FALSE,
5068 p_document_type => p_doc_type,
5069 p_document_id => p_doc_id,
5070 p_bv_mode => 'QA',
5071 x_expert_articles_tbl => l_expert_articles_tbl,
5072 x_msg_data => x_msg_data,
5073 x_msg_count => x_msg_count,
5074 x_qa_result_tbl => x_qa_result_tbl,
5075 x_return_status => x_return_status);
5076
5077 END IF;
5078 END IF;
5079
5080 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
5081 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
5082 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
5083 RAISE FND_API.G_EXC_ERROR ;
5084 END IF;
5085
5086 --****************************************--
5087 -- End of validation set --
5088 --****************************************--
5089
5090 -- update common attributes for the QA table
5091 IF x_qa_result_tbl.COUNT > 0 THEN
5092
5093 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5094 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2320: before FOR i IN x_qa_result_tbl.FIRST..x_qa_result_tbl.LAST LOOP');
5095 END IF;
5096
5097 FOR i IN x_qa_result_tbl.FIRST..x_qa_result_tbl.LAST LOOP
5098 x_qa_result_tbl(i).document_type := p_doc_type;
5099 x_qa_result_tbl(i).document_id := p_doc_id;
5100 x_qa_result_tbl(i).creation_date := l_now;
5101
5102 IF x_qa_result_tbl(i).Error_severity = G_QA_STS_ERROR THEN
5103 l_error_found := true;
5104 END IF;
5105 IF x_qa_result_tbl(i).Error_severity = G_QA_STS_WARNING THEN
5106 l_warning_found := true;
5107 END IF;
5108 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5109 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1600: i:'||i||', article_id:'||x_qa_result_tbl(i).article_id||', title:'||x_qa_result_tbl(i).title||', qa_code:'||x_qa_result_tbl(i).qa_code);
5110 END IF;
5111
5112 END LOOP;
5113 IF l_error_found THEN
5114 x_qa_return_status := G_QA_STS_ERROR;
5115 ELSIF l_warning_found THEN
5116 x_qa_return_status := G_QA_STS_WARNING;
5117 END IF;
5118 END IF;
5119
5120
5121 END IF; -- IF l_perform_QA then
5122
5123 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5124 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2300: Leaving QA_Doc');
5125 END IF;
5126 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
5127
5128 EXCEPTION
5129 WHEN FND_API.G_EXC_ERROR THEN
5130 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5131 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'2400: Leaving QA_Doc : OKC_API.G_EXCEPTION_ERROR Exception');
5132 END IF;
5133
5134 IF l_get_qa_detail_csr%ISOPEN THEN
5135 CLOSE l_get_qa_detail_csr;
5136 END IF;
5137 IF l_get_doc_articles_csr%ISOPEN THEN
5138 CLOSE l_get_doc_articles_csr;
5139 END IF;
5140 IF l_get_tmpl_articles_csr%ISOPEN THEN
5141 CLOSE l_get_tmpl_articles_csr;
5142 END IF;
5143 IF l_get_sections_csr %ISOPEN THEN
5144 CLOSE l_get_sections_csr ;
5145 END IF;
5146 IF l_get_effective_date%ISOPEN THEN
5147 CLOSE l_get_effective_date;
5148 END IF;
5149
5150 /* expert commented out
5151 IF l_get_xprt_articles_csr%ISOPEN THEN
5152 CLOSE l_get_xprt_articles_csr;
5153 END IF;
5154 */
5155
5156 x_return_status := G_RET_STS_ERROR ;
5157 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
5158
5159 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5160 ROLLBACK TO g_QA_Doc;
5161 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5162 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'2500: Leaving QA_Doc : OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
5163 END IF;
5164 IF l_get_qa_detail_csr%ISOPEN THEN
5165 CLOSE l_get_qa_detail_csr;
5166 END IF;
5167 IF l_get_doc_articles_csr%ISOPEN THEN
5168 CLOSE l_get_doc_articles_csr;
5169 END IF;
5170 IF l_get_tmpl_articles_csr%ISOPEN THEN
5171 CLOSE l_get_tmpl_articles_csr;
5172 END IF;
5173 IF l_get_sections_csr %ISOPEN THEN
5174 CLOSE l_get_sections_csr ;
5175 END IF;
5176 IF l_get_effective_date%ISOPEN THEN
5177 CLOSE l_get_effective_date;
5178 END IF;
5179
5180 /* expert commented out
5181 IF l_get_xprt_articles_csr%ISOPEN THEN
5182 CLOSE l_get_xprt_articles_csr;
5183 END IF;
5184 */
5185
5186 x_return_status := G_RET_STS_UNEXP_ERROR ;
5187 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
5188
5189 WHEN OTHERS THEN
5190 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5191 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'2600: Leaving QA_Doc because of EXCEPTION: '||sqlerrm);
5192 END IF;
5193
5194 IF l_get_qa_detail_csr%ISOPEN THEN
5195 CLOSE l_get_qa_detail_csr;
5196 END IF;
5197 IF l_get_doc_articles_csr%ISOPEN THEN
5198 CLOSE l_get_doc_articles_csr;
5199 END IF;
5200 IF l_get_tmpl_articles_csr%ISOPEN THEN
5201 CLOSE l_get_tmpl_articles_csr;
5202 END IF;
5203 IF l_get_sections_csr %ISOPEN THEN
5204 CLOSE l_get_sections_csr ;
5205 END IF;
5206 IF l_get_effective_date%ISOPEN THEN
5207 CLOSE l_get_effective_date;
5208 END IF;
5209
5210 /* expert commented out
5211 IF l_get_xprt_articles_csr%ISOPEN THEN
5212 CLOSE l_get_xprt_articles_csr;
5213 END IF;
5214 */
5215
5216 x_return_status := G_RET_STS_UNEXP_ERROR ;
5217 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
5218 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
5219 END IF;
5220 END QA_Doc;
5221
5222 END OKC_TERMS_QA_PVT;