[Home] [Help]
PACKAGE BODY: APPS.OKC_TERMS_QA_PVT
Source
1 PACKAGE BODY OKC_TERMS_QA_PVT AS
2 /* $Header: OKCVDQAB.pls 120.22.12010000.2 2008/10/24 08:04:47 ssreekum 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 CURSOR contract_admin_emp is
1133 SELECT 'Y' FROM OKC_TEMPLATE_USAGES USG, fnd_user ctrtadm, PER_ALL_PEOPLE_F adminppl
1134 WHERE USG.DOCUMENT_TYPE = P_DOC_TYPE
1135 AND USG.DOCUMENT_ID = P_DOC_ID
1136 AND USG.CONTRACT_ADMIN_ID IS NOT NULL
1137 AND USG.contract_admin_id = ctrtadm.user_id
1138 AND ctrtadm.employee_id = adminppl.person_id
1139 and rownum < 2;
1140
1141 CURSOR contract_admin_valid_emp is
1142 SELECT 'Y' FROM OKC_TEMPLATE_USAGES USG, fnd_user ctrtadm, PER_ALL_PEOPLE_F adminppl
1143 WHERE USG.DOCUMENT_TYPE = P_DOC_TYPE
1144 AND USG.DOCUMENT_ID = P_DOC_ID
1145 AND USG.CONTRACT_ADMIN_ID IS NOT NULL
1146 AND USG.contract_admin_id = ctrtadm.user_id
1147 AND ctrtadm.employee_id = adminppl.person_id
1148 and adminppl.effective_start_date = adminppl.start_date;
1149
1150 BEGIN
1151
1152 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1153 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1500: Entered check_contract_admin');
1154 END IF;
1155
1156 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1157 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: l_ctrt_admin : '||l_ctrt_admin);
1158
1159 END IF;
1160
1161 -- Initialize API return status to success
1162 x_return_status := G_RET_STS_SUCCESS;
1163
1164 OPEN l_get_qa_detail_csr(G_OKC_CHECK_CONTRACT_ADMIN);
1165 FETCH l_get_qa_detail_csr into l_perform_ctrt_admin_qa,l_ctrt_admin_desc,l_ctrt_admin_severity_flag;
1166 CLOSE l_get_qa_detail_csr;
1167
1168
1169
1170 IF l_perform_ctrt_admin_qa ='Y' THEN
1171
1172 IF (NOT do_validation(l_ctrt_admin_severity, p_doc_type)) THEN
1173 -- validation is not required
1174 RETURN;
1175 END IF;
1176 l_ctrt_admin := 'N';
1177 OPEN contract_admin_exists;
1178 fetch contract_admin_exists into l_ctrt_admin;
1179 close contract_admin_exists;
1180 if(l_ctrt_admin <> 'Y') THEN
1181
1182 l_indx := x_qa_result_tbl.COUNT + 1;
1183
1184 x_qa_result_tbl(l_indx).error_record_type := G_CONTRACT;
1185 x_qa_result_tbl(l_indx).article_id := null;
1186 x_qa_result_tbl(l_indx).deliverable_id := Null;
1187 x_qa_result_tbl(l_indx).title := null;
1188 x_qa_result_tbl(l_indx).section_name := null;
1189
1190 x_qa_result_tbl(l_indx).qa_code := G_OKC_CHECK_CONTRACT_ADMIN ;
1191 x_qa_result_tbl(l_indx).message_name := G_OKC_CHECK_CTRT_ADMIN_S ;
1192 x_qa_result_tbl(l_indx).suggestion := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC,G_OKC_CHECK_CTRT_ADMIN_S);
1193 OPEN l_get_ctrt_admin_err_wrn_csr;
1194 FETCH l_get_ctrt_admin_err_wrn_csr into l_ctrt_admin_severity_flag;
1195 IF (l_get_ctrt_admin_err_wrn_csr%NOTFOUND) THEN
1196 x_qa_result_tbl(l_indx).error_severity := 'W';
1197 x_qa_return_status := 'W';
1198 ELSE
1199 x_qa_result_tbl(l_indx).error_severity := l_ctrt_admin_severity_flag;
1200 x_qa_return_status := l_ctrt_admin_severity_flag;
1201 END IF;
1202 x_qa_result_tbl(l_indx).problem_short_desc := l_ctrt_admin_desc;
1203 x_qa_result_tbl(l_indx).problem_details_short := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_CHECK_CTRT_ADMIN_SH);
1204 x_qa_result_tbl(l_indx).problem_details := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_CHECK_CTRT_ADMIN_SH);
1205 x_qa_result_tbl(l_indx).document_type := p_doc_type;
1206 x_qa_result_tbl(l_indx).document_id := p_doc_id;
1207 x_qa_result_tbl(l_indx).creation_date := sysdate;
1208
1209
1210 ELSE
1211 l_ctrt_admin_emp := 'N';
1212 OPEN contract_admin_emp;
1213 fetch contract_admin_emp into l_ctrt_admin_emp;
1214 close contract_admin_emp;
1215 if(l_ctrt_admin_emp <> 'Y') THEN
1216 x_qa_result_tbl(l_indx).error_record_type := G_CONTRACT;
1217 x_qa_result_tbl(l_indx).article_id := null;
1218 x_qa_result_tbl(l_indx).deliverable_id := Null;
1219 x_qa_result_tbl(l_indx).title := null;
1220 x_qa_result_tbl(l_indx).section_name := null;
1221
1222 x_qa_result_tbl(l_indx).qa_code := G_OKC_CHECK_CONTRACT_ADMIN ;
1223 x_qa_result_tbl(l_indx).message_name := G_OKC_CTRT_ADMIN_EMP_S ;
1224 x_qa_result_tbl(l_indx).suggestion := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC,G_OKC_ADMIN_EMP_SUGG);
1225 OPEN l_get_ctrt_admin_err_wrn_csr;
1226 FETCH l_get_ctrt_admin_err_wrn_csr into l_ctrt_admin_severity_flag;
1227 IF (l_get_ctrt_admin_err_wrn_csr%NOTFOUND) THEN
1228 x_qa_result_tbl(l_indx).error_severity := 'W';
1229 x_qa_return_status := 'W';
1230 ELSE
1231 x_qa_result_tbl(l_indx).error_severity := l_ctrt_admin_severity_flag;
1232 x_qa_return_status := l_ctrt_admin_severity_flag;
1233 END IF;
1234 x_qa_result_tbl(l_indx).problem_short_desc := l_ctrt_admin_desc;
1235 x_qa_result_tbl(l_indx).problem_details_short := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_CTRT_ADMIN_EMP_SH);
1236 x_qa_result_tbl(l_indx).problem_details := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_CTRT_ADMIN_EMP_DT);
1237 x_qa_result_tbl(l_indx).document_type := p_doc_type;
1238 x_qa_result_tbl(l_indx).document_id := p_doc_id;
1239 x_qa_result_tbl(l_indx).creation_date := sysdate;
1240
1241 ELSE
1242 l_ctrt_admin_valid_emp := 'N';
1243 OPEN contract_admin_valid_emp;
1244 fetch contract_admin_valid_emp into l_ctrt_admin_valid_emp;
1245 close contract_admin_valid_emp;
1246 if(l_ctrt_admin_valid_emp <> 'Y') THEN
1247 x_qa_result_tbl(l_indx).error_record_type := G_CONTRACT;
1248 x_qa_result_tbl(l_indx).article_id := null;
1249 x_qa_result_tbl(l_indx).deliverable_id := Null;
1250 x_qa_result_tbl(l_indx).title := null;
1251 x_qa_result_tbl(l_indx).section_name := null;
1252
1253 x_qa_result_tbl(l_indx).qa_code := G_OKC_CHECK_CONTRACT_ADMIN ;
1254 x_qa_result_tbl(l_indx).message_name := G_OKC_ADMIN_VALID_EMP_S ;
1255 x_qa_result_tbl(l_indx).suggestion := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC,G_OKC_ADMIN_VALID_EMP_SUGG);
1256 OPEN l_get_ctrt_admin_err_wrn_csr;
1257 FETCH l_get_ctrt_admin_err_wrn_csr into l_ctrt_admin_severity_flag;
1258 IF (l_get_ctrt_admin_err_wrn_csr%NOTFOUND) THEN
1259 x_qa_result_tbl(l_indx).error_severity := 'W';
1260 x_qa_return_status := 'W';
1261 ELSE
1262 x_qa_result_tbl(l_indx).error_severity := l_ctrt_admin_severity_flag;
1263 x_qa_return_status := l_ctrt_admin_severity_flag;
1264 END IF;
1265 x_qa_result_tbl(l_indx).problem_short_desc := l_ctrt_admin_desc;
1266 x_qa_result_tbl(l_indx).problem_details_short := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_ADMIN_VALID_EMP_SH);
1267 x_qa_result_tbl(l_indx).problem_details := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_ADMIN_VALID_EMP_DT);
1268 x_qa_result_tbl(l_indx).document_type := p_doc_type;
1269 x_qa_result_tbl(l_indx).document_id := p_doc_id;
1270 x_qa_result_tbl(l_indx).creation_date := sysdate;
1271
1272 END IF; -- if(l_ctrt_admin_valid_emp <> 'Y')
1273
1274 END IF; -- if(l_ctrt_admin_emp <> 'Y')
1275
1276
1277
1278 END IF; -- IF (l_ctrt_admin = 'Y')
1279
1280 END IF;-- IF l_perform_ctrt_admin_qa ='Y'
1281
1282 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1283 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1600: Leaving Check_Incomp_and_alternate');
1284 END IF;
1285
1286 EXCEPTION
1287 WHEN FND_API.G_EXC_ERROR THEN
1288 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1289 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7800: Leaving Check_Incomp_and_alternate : OKC_API.G_EXCEPTION_ERROR Exception');
1290 END IF;
1291
1292 x_return_status := G_RET_STS_ERROR ;
1293
1294 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1295 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1296 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');
1297 END IF;
1298
1299 x_return_status := G_RET_STS_UNEXP_ERROR ;
1300
1301 WHEN OTHERS THEN
1302 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1303 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'8000: Leaving Check_Incomp_and_alternate because of EXCEPTION: '||sqlerrm);
1304 END IF;
1305
1306 x_return_status := G_RET_STS_UNEXP_ERROR ;
1307
1308 END check_contract_admin;
1309
1310
1311
1312
1313 -------------------------------------------
1314 -- PROCEDURE check_duplicate_articles
1315 -------------------------------------------
1316 /* API to do QA Check for duplicate article */
1317
1318 PROCEDURE check_duplicate_articles (
1319 p_qa_mode IN VARCHAR2,
1320 p_doc_type IN VARCHAR2,
1321 p_doc_id IN NUMBER,
1322
1323 px_qa_result_tbl IN OUT NOCOPY qa_result_tbl_type,
1324 x_return_status OUT NOCOPY VARCHAR2)
1325 IS
1326
1327 l_api_name CONSTANT VARCHAR2(30) := 'check_duplicate_articles';
1328 l_indx NUMBER;
1329 l_error_count NUMBER := 0;
1330 l_severity OKC_QA_ERRORS_T.ERROR_SEVERITY%TYPE;
1331 l_desc OKC_QA_ERRORS_T.PROBLEM_SHORT_DESC%TYPE;
1332 l_perform_duplicate_qa Varchar2(1);
1333
1334 /* expert commented out
1335 -- new cursor to check for xprt articles
1336 CURSOR l_get_dup_xprt_csr IS
1337 SELECT
1338 Rule.clause_id xprt_article_id,
1339 Rule.rule_id rule_id,
1340 Rule.rule_name rule_name
1341 FROM OKC_XPRT_CLAUSES_V rule,
1342 OKC_K_ARTICLES_B kart
1343 WHERE rule.template_id = p_doc_id
1344 AND kart.document_type = p_doc_type
1345 AND kart.document_id = p_doc_id
1346 AND rule.clause_id =kart.sav_sae_id;
1347 */
1348
1349 BEGIN
1350
1351 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1352 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1500: Entered check_duplicate_articles');
1353 END IF;
1354
1355 get_qa_code_detail(p_qa_code => 'CHECK_DUPLICATE',
1356 x_perform_qa => l_perform_duplicate_qa,
1357 x_qa_name => l_desc,
1358 x_severity_flag => l_severity,
1359 x_return_status => x_return_status);
1360 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1361 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1362 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1363 RAISE FND_API.G_EXC_ERROR ;
1364 END IF;
1365
1366 IF l_perform_duplicate_qa ='Y' THEN
1367
1368 IF (NOT do_validation(l_severity, p_doc_type)) THEN
1369 -- validation is not required
1370 RETURN;
1371 END IF;
1372
1373 IF l_article_tbl.COUNT > 0 THEN
1374
1375 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1376 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1600: totally:'||l_article_tbl.COUNT||' articles to check');
1377 END IF;
1378
1379 FOR i IN l_article_tbl.FIRST..l_article_tbl.LAST LOOP
1380
1381 --Bug 4128923
1382 l_error_count := 0;
1383 IF( p_qa_mode=G_AMEND_QA and l_article_tbl(i).amendment_operation_code IS NOT NULL
1384 OR p_qa_mode<>G_AMEND_QA )
1385 and nvl(l_article_tbl(i).amendment_operation_code,'?')<>G_AMEND_CODE_DELETED THEN
1386
1387 --Bug 4128923 l_error_count := 0;
1388
1389 FOR k IN l_article_tbl.FIRST..l_article_tbl.LAST LOOP
1390 --IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1391 -- FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1600: i:'||i||', k:'||k);
1392 --END IF;
1393
1394 IF nvl(l_article_tbl(k).amendment_operation_code,'?')<>G_AMEND_CODE_DELETED
1395 AND Nvl(l_article_tbl(k).std_art_id,l_article_tbl(k).article_id)
1396 =Nvl(l_article_tbl(i).std_art_id,l_article_tbl(i).article_id) THEN
1397
1398 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1399 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1600: Dupicate found - i:'||i||', k:'||k);
1400 END IF;
1401 l_error_count := l_error_count + 1;
1402 END IF;
1403 END LOOP; -- FOR k IN l_article_tbl1.FIRST..l_article_tbl1.LAST LOOP
1404 END IF; -- IF ( ( p_qa_mode =G_AMEND_QA
1405
1406 IF l_error_count > 1 THEN
1407 l_indx := px_qa_result_tbl.COUNT + 1;
1408
1409 px_qa_result_tbl(l_indx).error_record_type := G_ART_QA_TYPE;
1410 px_qa_result_tbl(l_indx).article_id := l_article_tbl(i).article_id;
1411 px_qa_result_tbl(l_indx).deliverable_id := Null;
1412 px_qa_result_tbl(l_indx).title := get_article_title(l_article_tbl(i).id);
1413 px_qa_result_tbl(l_indx).section_name := get_section_title(l_article_tbl(i).scn_id);
1414 px_qa_result_tbl(l_indx).qa_code := G_CHECK_DUPLICATE;
1415 px_qa_result_tbl(l_indx).message_name := G_OKC_CHECK_DUPLICATE;
1416 px_qa_result_tbl(l_indx).suggestion := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC,G_OKC_CHECK_DUPLICATE_S);
1417
1418 px_qa_result_tbl(l_indx).error_severity := l_severity;
1419 px_qa_result_tbl(l_indx).problem_short_desc := l_desc;
1420 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);
1421 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);
1422 END IF; -- IF l_error_count > 1 THEN
1423
1424 /* expert commented out
1425 IF ((g_expert_enabled = 'Y') and (p_doc_type = 'TEMPLATE')) THEN
1426
1427 FOR cr IN l_get_dup_xprt_csr LOOP
1428 l_indx := px_qa_result_tbl.COUNT + 1;
1429
1430 px_qa_result_tbl(l_indx).error_record_type := G_EXP_QA_TYPE;
1431 px_qa_result_tbl(l_indx).article_id := cr.xprt_article_id;
1432 px_qa_result_tbl(l_indx).deliverable_id := Null;
1433 px_qa_result_tbl(l_indx).title :=
1434 get_xprt_article_title(cr.xprt_article_id);
1435 px_qa_result_tbl(l_indx).section_name := Null;
1436 px_qa_result_tbl(l_indx).qa_code := G_CHECK_RUL_DUP;
1437 px_qa_result_tbl(l_indx).message_name := G_OKC_CHECK_RUL_DUP;
1438 px_qa_result_tbl(l_indx).suggestion :=
1439 OKC_TERMS_UTIL_PVT.Get_Message(G_OKC,G_OKC_CHECK_RUL_DUP_S);
1440 px_qa_result_tbl(l_indx).error_severity := l_severity;
1441 px_qa_result_tbl(l_indx).problem_short_desc := l_desc;
1442 px_qa_result_tbl(l_indx).problem_details_short :=
1443 OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_CHECK_RUL_DUP_SH);
1444 px_qa_result_tbl(l_indx).problem_details :=
1445 OKC_TERMS_UTIL_PVT.Get_Message(G_OKC,G_OKC_CHECK_RUL_DUP,
1446 'RULE', cr.rule_name,
1447 'XPRT_ARTICLE', get_xprt_article_title(cr.xprt_article_id));
1448 END LOOP;
1449
1450 END IF; -- of IF ((g_expert_enabled = 'Y') and (p_doc_type = 'TEMPLATE')) THEN
1451 */
1452
1453 END LOOP; -- FOR i IN l_article_tbl.FIRST..l_article_tbl.LAST LOOP
1454
1455 END IF; -- IF l_article_tbl.COUNT > 0 THEN
1456
1457 END IF; -- IF l_perform_duplicate_qa ='Y' THEN
1458
1459 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1460 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1600: Leaving check_duplicate_articles');
1461 END IF;
1462
1463 EXCEPTION
1464 WHEN FND_API.G_EXC_ERROR THEN
1465 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1466 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7800: Leaving check_duplicate_articles : OKC_API.G_EXCEPTION_ERROR Exception');
1467 END IF;
1468
1469 /* expert commented out
1470 IF l_get_dup_xprt_csr%ISOPEN THEN
1471 CLOSE l_get_dup_xprt_csr;
1472 END IF;
1473 */
1474
1475 x_return_status := G_RET_STS_ERROR ;
1476
1477 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1478 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1479 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7900: Leaving check_duplicate_articles : OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
1480 END IF;
1481
1482 /* expert commented out
1483 IF l_get_dup_xprt_csr%ISOPEN THEN
1484 CLOSE l_get_dup_xprt_csr;
1485 END IF;
1486 */
1487 x_return_status := G_RET_STS_UNEXP_ERROR ;
1488
1489 WHEN OTHERS THEN
1490 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1491 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'8000: Leaving check_duplicate_articles because of EXCEPTION: '||sqlerrm);
1492 END IF;
1493
1494 /* expert commented out
1495 IF l_get_dup_xprt_csr%ISOPEN THEN
1496 CLOSE l_get_dup_xprt_csr;
1497 END IF;
1498 */
1499
1500 x_return_status := G_RET_STS_UNEXP_ERROR ;
1501 END check_duplicate_articles;
1502
1503 -------------------------------------------
1504 -- PROCEDURE check_var_doc_type_usage
1505 -------------------------------------------
1506 /* API to do QA Check variable Doc type usage */
1507
1508 PROCEDURE check_var_doc_type_usage (
1509 p_qa_mode IN VARCHAR2,
1510 p_doc_type IN VARCHAR2,
1511 p_doc_id IN NUMBER,
1512 px_qa_result_tbl IN OUT NOCOPY qa_result_tbl_type,
1513 x_return_status OUT NOCOPY VARCHAR2)
1514 IS
1515
1516 l_api_name CONSTANT VARCHAR2(30) := 'G_Check_var_doc_type_usage';
1517 l_indx NUMBER;
1518 l_severity OKC_QA_ERRORS_T.ERROR_SEVERITY%TYPE;
1519 l_desc OKC_QA_ERRORS_T.PROBLEM_SHORT_DESC%TYPE;
1520 l_perform_var_usg_qa VARCHAR2(1);
1521
1522 /* 11.5.10+ modified cursor - see below
1523 CURSOR l_check_usage_csr IS
1524 SELECT kart.id id,
1525 kart.sav_sae_id article_id,
1526 kart.article_version_id article_version_id,
1527 kart.amendment_operation_code amendment_operation_code,
1528 kart.scn_id scn_id,
1529 var.variable_code variable_code,
1530 busvar.variable_name variable_name,
1531 busdoc.name doc_type
1532 FROM okc_k_articles_b kart,
1533 okc_k_art_variables var,
1534 okc_bus_variables_vl busvar,
1535 okc_bus_doc_types_v busdoc
1536 WHERE kart.document_type=p_doc_type
1537 and kart.document_id=p_doc_id
1538 and var.cat_id=kart.id
1539 and var.variable_type IN ('S','D')
1540 and var.variable_code=busvar.variable_code
1541 and busdoc.document_type = kart.document_type
1542 and not exists (SELECT 'x' from OKC_VARIABLE_DOC_TYPES vo
1543 WHERE var.variable_code=vo.variable_code
1544 and doc_type=p_doc_type)
1545 and p_doc_type<>G_TMPL_DOC_TYPE
1546 and nvl(kart.amendment_operation_code,'?')<>G_AMEND_CODE_DELETED
1547 and nvl(kart.summary_amend_operation_code,'?')<>G_AMEND_CODE_DELETED
1548 UNION ALL
1549 SELECT kart.id id,
1550 kart.sav_sae_id article_id,
1551 vers.article_version_id article_version_id,
1552 kart.amendment_operation_code amendment_operation_code,
1553 kart.scn_id scn_id,
1554 var.variable_code variable_code,
1555 busvar.variable_name variable_name,
1556 busdoc.name doc_type
1557 FROM okc_k_articles_b kart,
1558 okc_article_versions vers,
1559 okc_allowed_tmpl_usages allwd ,
1560 okc_article_variables var,
1561 okc_bus_variables_vl busvar ,
1562 okc_bus_doc_types_v busdoc
1563 WHERE kart.document_type=G_TMPL_DOC_TYPE
1564 and kart.document_id=p_doc_id
1565 and allwd.template_id=kart.document_id
1566 and kart.sav_sae_id=vers.article_id
1567 and vers.article_status='APPROVED'
1568 AND vers.start_date = (SELECT max(start_date)
1569 FROM OKC_ARTICLE_VERSIONS
1570 WHERE article_id= kart.sav_sae_id
1571 AND article_status='APPROVED')
1572 and vers.article_version_id=var.article_version_id
1573 and busvar.variable_code=var.variable_code
1574 and busvar.variable_type IN ('S','D')
1575 and busdoc.document_type = allwd.document_type
1576 and not exists (SELECT 'x' FROM OKC_VARIABLE_DOC_TYPES vo
1577 WHERE var.variable_code=vo.variable_code
1578 and doc_type=allwd.document_type)
1579 and p_doc_type=G_TMPL_DOC_TYPE
1580 and nvl(kart.amendment_operation_code,'?')<>G_AMEND_CODE_DELETED
1581 and nvl(kart.summary_amend_operation_code,'?')<>G_AMEND_CODE_DELETED;
1582 */
1583
1584 /* 11.5.10+ modified cursor definition
1585 1. no change for non TEMPLATE doc types
1586 2. for TEMPLATE doc types, get selected draft versions also
1587 */
1588 --Repository Enhancement 12.1 (For Validate Action)
1589 p_rep_doc_type varchar2(30);
1590 l_intent okc_bus_doc_types_b.intent%type;
1591 -- Repository Enhancement 12.1 Ends (For Validate Action)
1592
1593 CURSOR l_check_usage_csr IS
1594 -- no change for non TEMPLATE document types
1595 SELECT kart.id id,
1596 kart.sav_sae_id article_id,
1597 kart.article_version_id article_version_id,
1598 kart.amendment_operation_code amendment_operation_code,
1599 kart.scn_id scn_id,
1600 var.variable_code variable_code,
1601 busvar.variable_name variable_name,
1602 busdoc.name doc_type
1603 FROM okc_k_articles_b kart,
1604 okc_k_art_variables var,
1605 okc_bus_variables_vl busvar,
1606 okc_bus_doc_types_v busdoc
1607 WHERE kart.document_type=p_doc_type
1608 and kart.document_id=p_doc_id
1609 and var.cat_id=kart.id
1610 and var.variable_type IN ('S','D')
1611 and var.variable_code=busvar.variable_code
1612 --Repository Enhancement 12.1 (For Validate Action)
1613 and busdoc.document_type = kart.document_type
1614 -- and busdoc.document_type = p_rep_doc_type
1615 and not exists (SELECT 'x' from OKC_VARIABLE_DOC_TYPES vo
1616 WHERE var.variable_code=vo.variable_code
1617 --Repository Enhancement 12.1 (For Validate Action)
1618 -- and doc_type=p_doc_type)
1619 and doc_type= p_rep_doc_type)
1620 and p_doc_type<>G_TMPL_DOC_TYPE
1621 and nvl(kart.amendment_operation_code,'?')<>G_AMEND_CODE_DELETED
1622 and nvl(kart.summary_amend_operation_code,'?')<>G_AMEND_CODE_DELETED
1623 UNION ALL
1624 -- change to get draft/rejected clause versions also
1625 SELECT kart.id id,
1626 kart.sav_sae_id article_id,
1627 vers.article_version_id article_version_id,
1628 kart.amendment_operation_code amendment_operation_code,
1629 kart.scn_id scn_id,
1630 var.variable_code variable_code,
1631 busvar.variable_name variable_name,
1632 busdoc.name doc_type
1633 FROM okc_k_articles_b kart,
1634 okc_article_versions vers,
1635 okc_allowed_tmpl_usages allwd ,
1636 okc_article_variables var,
1637 okc_bus_variables_vl busvar ,
1638 okc_bus_doc_types_v busdoc
1639 WHERE kart.document_type=G_TMPL_DOC_TYPE
1640 and kart.document_id=p_doc_id
1641 and allwd.template_id=kart.document_id
1642 and kart.sav_sae_id=vers.article_id
1643 -- new logic for determining clause version
1644 and vers.article_version_id = OKC_TERMS_UTIL_PVT.get_latest_tmpl_art_version_id(
1645 kart.sav_sae_id,
1646 g_start_date,
1647 g_end_date,
1648 g_status_code,
1649 p_doc_type,
1650 p_doc_id)
1651 /* existing logic of determining clause version
1652 and vers.article_status='APPROVED'
1653 AND vers.start_date = (select max(start_date)
1654 FROM OKC_ARTICLE_VERSIONS
1655 WHERE article_id= kart.sav_sae_id
1656 AND article_status='APPROVED')
1657 */
1658 and vers.article_version_id=var.article_version_id
1659 and busvar.variable_code=var.variable_code
1660 and busvar.variable_type IN ('S','D')
1661 and busdoc.document_type = allwd.document_type
1662 and not exists ((SELECT 'x' FROM OKC_VARIABLE_DOC_TYPES vo
1663 WHERE var.variable_code=vo.variable_code
1664 and doc_type=allwd.document_type)
1665 UNION ALL
1666 (SELECT 'x' FROM OKC_VARIABLE_DOC_TYPES vo
1667 WHERE var.variable_code=vo.variable_code
1668 and doc_type like '%REPOSITORY%'))
1669 and p_doc_type=G_TMPL_DOC_TYPE
1670 and nvl(kart.amendment_operation_code,'?')<>G_AMEND_CODE_DELETED
1671 and nvl(kart.summary_amend_operation_code,'?')<>G_AMEND_CODE_DELETED;
1672
1673 -- Repository Enhancement 12.1 (For Validate Action)
1674 CURSOR l_get_intent is
1675 SELECT intent
1676 FROM okc_bus_doc_types_b
1677 WHERE document_type = p_doc_type;
1678 -- Repository Enhancement 12.1 Ends (For Validate Action)
1679
1680
1681 /* expert commented out
1682 CURSOR l_xprt_check_usage_csr IS
1683 SELECT
1684 Rule.clause_id xprt_article_id,
1685 Rule.rule_id rule_id,
1686 Rule.rule_name rule_name,
1687 var.variable_code variable_code,
1688 busvar.variable_name variable_name,
1689 busdoc.name doc_type
1690 FROM okc_xprt_clauses_v rule,
1691 okc_allowed_tmpl_usages allwd ,
1692 okc_article_versions vers,
1693 okc_article_variables var,
1694 okc_bus_variables_vl busvar ,
1695 okc_bus_doc_types_v busdoc
1696 WHERE rule.template_id = p_doc_id
1697 and allwd.template_id=p_doc_id
1698 and vers.article_id = rule.clause_id
1699 and vers.article_version_id =
1700 OKC_TERMS_UTIL_PVT.get_latest_tmpl_art_version_id(
1701 rule.clause_id,
1702 g_start_date,
1703 g_end_date,
1704 g_status_code,
1705 p_doc_type,
1706 p_doc_id)
1707 and var.article_version_id = vers.article_version_id
1708 and busvar.variable_code = var.variable_code
1709 and busvar.variable_type IN ('S','D')
1710 and busdoc.document_type = allwd.document_type
1711 and not exists (select 'x' from OKC_VARIABLE_DOC_TYPES vo
1712 where vo.variable_code=var.variable_code
1713 and vo.doc_type=allwd.document_type) ;
1714 */
1715
1716 BEGIN
1717
1718 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1719 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1500: Entered Check_var_doc_type_usage');
1720 END IF;
1721
1722 get_qa_code_detail(p_qa_code => G_CHECK_VAR_USAGE,
1723 x_perform_qa => l_perform_var_usg_qa,
1724 x_qa_name => l_desc,
1725 x_severity_flag => l_severity,
1726 x_return_status => x_return_status);
1727 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1728 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1729 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1730 RAISE FND_API.G_EXC_ERROR ;
1731 END IF;
1732
1733 IF l_perform_var_usg_qa ='Y' THEN
1734
1735 IF (NOT do_validation(l_severity, p_doc_type)) THEN
1736 -- validation is not required
1737 RETURN;
1738 END IF;
1739
1740 -- Repository Enhancement 12.1 (For Validate Action)
1741 OPEN l_get_intent;
1742 FETCH l_get_intent into l_intent;
1743 CLOSE l_get_intent;
1744 IF SubStr(p_doc_type,1,3) = 'REP' and l_intent = 'S' THEN
1745 p_rep_doc_type:='OKC_REPOSITORY_SELL';
1746 ELSIF SubStr(p_doc_type,1,3) = 'REP' and l_intent = 'B' THEN
1747 p_rep_doc_type:='OKC_REPOSITORY_BUY';
1748 ELSE
1749 p_rep_doc_type:=p_doc_type;
1750 END IF;
1751 -- Repository Enhancement 12.1 Ends(For Validate Action)
1752
1753 FOR cr IN l_check_usage_csr LOOP
1754
1755 IF ( ( p_qa_mode =G_AMEND_QA
1756 and cr.amendment_operation_code IS NOT NULL
1757 )
1758 OR p_qa_mode<>G_AMEND_QA) THEN
1759
1760 l_indx := px_qa_result_tbl.COUNT + 1;
1761
1762 px_qa_result_tbl(l_indx).error_record_type:= G_ART_QA_TYPE;
1763 px_qa_result_tbl(l_indx).article_id := cr.article_id;
1764 px_qa_result_tbl(l_indx).deliverable_id := Null;
1765 px_qa_result_tbl(l_indx).title := get_article_title(cr.id);
1766 px_qa_result_tbl(l_indx).section_name := get_section_title(cr.scn_id);
1767 px_qa_result_tbl(l_indx).qa_code := G_CHECK_VAR_USAGE;
1768 px_qa_result_tbl(l_indx).message_name := G_OKC_CHECK_VAR_USAGE;
1769 px_qa_result_tbl(l_indx).suggestion := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC,G_OKC_CHECK_VAR_USAGE_S);
1770
1771 px_qa_result_tbl(l_indx).error_severity := l_severity;
1772 px_qa_result_tbl(l_indx).problem_short_desc := l_desc;
1773 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);
1774 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);
1775 END IF; -- IF ( ( p_qa_mode =G_AMEND_QA
1776
1777 END LOOP; -- FOR cr IN l_check_usage_csr LOOP
1778
1779 /* expert commented out
1780 IF ((g_expert_enabled = 'Y') AND (p_doc_type = 'TEMPLATE')) THEN
1781 FOR cr IN l_xprt_check_usage_csr LOOP
1782 l_indx := px_qa_result_tbl.COUNT + 1;
1783
1784 px_qa_result_tbl(l_indx).error_record_type := G_EXP_QA_TYPE;
1785 px_qa_result_tbl(l_indx).article_id := cr.xprt_article_id;
1786 px_qa_result_tbl(l_indx).deliverable_id := Null;
1787 px_qa_result_tbl(l_indx).title :=
1788 get_xprt_article_title(cr.xprt_article_id);
1789 px_qa_result_tbl(l_indx).section_name := Null;
1790
1791 px_qa_result_tbl(l_indx).qa_code := G_CHECK_RUL_VAR_DOC;
1792 px_qa_result_tbl(l_indx).message_name := G_OKC_CHECK_RUL_VAR_DOC;
1793 px_qa_result_tbl(l_indx).suggestion :=
1794 OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_CHECK_RUL_VAR_DOC_S);
1795 px_qa_result_tbl(l_indx).error_severity := l_severity;
1796 px_qa_result_tbl(l_indx).problem_short_desc := l_desc;
1797 px_qa_result_tbl(l_indx).problem_details_short :=
1798 OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_CHECK_RUL_VAR_DOC_SH);
1799 px_qa_result_tbl(l_indx).problem_details :=
1800 OKC_TERMS_UTIL_PVT.Get_Message(G_OKC,G_OKC_CHECK_RUL_VAR_DOC,
1801 'RULE', cr.rule_name,
1802 'XPRT_ARTICLE', get_xprt_article_title(cr.xprt_article_id),
1803 'DOCUMENT_TYPE', cr.doc_type);
1804 END LOOP;
1805 END IF; -- of IF ((g_expert_enabled = 'Y') AND (p_doc_type = 'TEMPLATE'))
1806 */
1807
1808 END IF; -- IF l_perform_var_usg_qa ='Y' THEN
1809
1810
1811 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1812 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1600: Leaving Check_var_doc_type_usage');
1813 END IF;
1814
1815 EXCEPTION
1816 WHEN FND_API.G_EXC_ERROR THEN
1817 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1818 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');
1819 END IF;
1820
1821 IF l_check_usage_csr%ISOPEN THEN
1822 CLOSE l_check_usage_csr ;
1823 END IF;
1824
1825 /* expert commented out
1826 IF l_xprt_check_usage_csr%ISOPEN THEN
1827 CLOSE l_xprt_check_usage_csr ;
1828 END IF;
1829 */
1830
1831 x_return_status := G_RET_STS_ERROR ;
1832
1833 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1834 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1835 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');
1836 END IF;
1837
1838 IF l_check_usage_csr%ISOPEN THEN
1839 CLOSE l_check_usage_csr ;
1840 END IF;
1841
1842 /* expert commented out
1843 IF l_xprt_check_usage_csr%ISOPEN THEN
1844 CLOSE l_xprt_check_usage_csr ;
1845 END IF;
1846 */
1847
1848 x_return_status := G_RET_STS_UNEXP_ERROR ;
1849
1850 WHEN OTHERS THEN
1851 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1852 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'8000: Leaving Check_var_doc_type_usage because of EXCEPTION: '||sqlerrm);
1853 END IF;
1854
1855 IF l_check_usage_csr%ISOPEN THEN
1856 CLOSE l_check_usage_csr ;
1857 END IF;
1858
1859 /* expert commented out
1860 IF l_xprt_check_usage_csr%ISOPEN THEN
1861 CLOSE l_xprt_check_usage_csr ;
1862 END IF;
1863 */
1864
1865 x_return_status := G_RET_STS_UNEXP_ERROR ;
1866
1867 END check_var_doc_type_usage;
1868
1869 -------------------------------------------
1870 -- PROCEDURE check_user_vars_with_procs
1871 -------------------------------------------
1872
1873 PROCEDURE check_user_vars_with_procs (
1874 p_doc_type IN VARCHAR2,
1875 p_doc_id IN NUMBER,
1876 px_qa_result_tbl IN OUT NOCOPY qa_result_tbl_type,
1877 x_return_status OUT NOCOPY VARCHAR2)
1878 IS
1879
1880 l_api_name CONSTANT VARCHAR2(30) := 'check_user_vars_with_procs';
1881 l_indx NUMBER;
1882 l_int_severity OKC_QA_ERRORS_T.ERROR_SEVERITY%TYPE;
1883 l_int_desc OKC_QA_ERRORS_T.PROBLEM_SHORT_DESC%TYPE;
1884 l_perform_int_qa VARCHAR2(1);
1885
1886 l_variable_value VARCHAR2(2500) := NULL;
1887 l_previous_var_code okc_bus_variables_b.variable_code%TYPE := '-99';
1888 l_return_status VARCHAR2(10);
1889 l_msg_count NUMBER;
1890 l_msg_data VARCHAR2(2500);
1891
1892
1893 CURSOR csr_get_udv_with_procs IS
1894 SELECT VB.variable_code,
1895 KA.id,
1896 KA.sav_sae_id article_id,
1897 KA.scn_id,
1898 VT.variable_name
1899 FROM okc_k_articles_b KA,
1900 okc_k_art_variables KV,
1901 okc_bus_variables_b VB,
1902 okc_bus_variables_tl VT
1903 WHERE VB.variable_code = KV.variable_code
1904 AND KA.id = KV.cat_id
1905 AND VB.variable_code = VT.variable_code
1906 AND VB.variable_source = 'P'
1907 AND KA.document_type = p_doc_type
1908 AND KA.document_id = p_doc_id
1909 AND language = USERENV('LANG')
1910 ORDER BY VB.variable_code;
1911
1912
1913 BEGIN
1914 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1915 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: Entered check_user_vars_with_procs');
1916 END IF;
1917
1918 get_qa_code_detail(p_qa_code => G_CHECK_INT_VAR_VALUE,
1919 x_perform_qa => l_perform_int_qa,
1920 x_qa_name => l_int_desc,
1921 x_severity_flag => l_int_severity,
1922 x_return_status => x_return_status);
1923 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1924 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1925 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1926 RAISE FND_API.G_EXC_ERROR ;
1927 END IF;
1928
1929
1930 FOR csr_udv_with_procs_rec IN csr_get_udv_with_procs LOOP
1931
1932 /* Get the variable value */
1933 IF l_previous_var_code <> csr_udv_with_procs_rec.variable_code THEN
1934
1935 l_variable_value := NULL;
1936
1937 OKC_TERMS_UTIL_PVT.get_udv_with_proc_value (
1938 p_document_type => p_doc_type,
1939 p_document_id => p_doc_id,
1940 p_variable_code => csr_udv_with_procs_rec.variable_code,
1941 p_output_error => FND_API.G_FALSE,
1942 x_variable_value => l_variable_value,
1943 x_return_status => l_return_status,
1944 x_msg_data => l_msg_data,
1945 x_msg_count => l_msg_count );
1946
1947 END IF;
1948
1949 /* Add to the qa results, if the variable is unresolved */
1950 IF l_variable_value IS NULL THEN
1951
1952 l_indx := px_qa_result_tbl.COUNT + 1;
1953
1954 px_qa_result_tbl(l_indx).error_record_type:= G_ART_QA_TYPE;
1955 px_qa_result_tbl(l_indx).article_id := csr_udv_with_procs_rec.article_id;
1956 px_qa_result_tbl(l_indx).deliverable_id := NULL;
1957 px_qa_result_tbl(l_indx).title := get_article_title(csr_udv_with_procs_rec.id);
1958 px_qa_result_tbl(l_indx).qa_code := G_CHECK_INT_VAR_VALUE;
1959 px_qa_result_tbl(l_indx).message_name := G_OKC_CHECK_INT_VAR_VALUE;
1960 px_qa_result_tbl(l_indx).suggestion := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC,G_OKC_CHECK_INT_VAR_VALUE_S);
1961 px_qa_result_tbl(l_indx).section_name := get_section_title(csr_udv_with_procs_rec.scn_id);
1962
1963 px_qa_result_tbl(l_indx).error_severity := l_INT_severity;
1964 px_qa_result_tbl(l_indx).problem_short_desc := l_INT_desc;
1965 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);
1966 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);
1967
1968 END IF;
1969
1970 l_previous_var_code := csr_udv_with_procs_rec.variable_code;
1971
1972 END LOOP;
1973
1974
1975 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1976 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'600: Leaving check_user_vars_with_procs');
1977 END IF;
1978
1979 EXCEPTION
1980 WHEN FND_API.G_EXC_ERROR THEN
1981 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1982 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');
1983 END IF;
1984
1985 IF csr_get_udv_with_procs%ISOPEN THEN
1986 CLOSE csr_get_udv_with_procs;
1987 END IF;
1988
1989 x_return_status := G_RET_STS_ERROR ;
1990
1991 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1992 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1993 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');
1994 END IF;
1995
1996 IF csr_get_udv_with_procs%ISOPEN THEN
1997 CLOSE csr_get_udv_with_procs;
1998 END IF;
1999
2000 x_return_status := G_RET_STS_UNEXP_ERROR ;
2001
2002 WHEN OTHERS THEN
2003 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2004 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'1300: Leaving check_user_vars_with_procs because of EXCEPTION: '||sqlerrm);
2005 END IF;
2006
2007 IF csr_get_udv_with_procs%ISOPEN THEN
2008 CLOSE csr_get_udv_with_procs;
2009 END IF;
2010
2011 x_return_status := G_RET_STS_UNEXP_ERROR ;
2012
2013 END check_user_vars_with_procs;
2014
2015
2016 -------------------------------------------
2017 -- PROCEDURE check_variables
2018 -------------------------------------------
2019 /* API to do QA Check variables*/
2020
2021 PROCEDURE check_variables (
2022 p_doc_type IN VARCHAR2,
2023 p_doc_id IN NUMBER,
2024 px_qa_result_tbl IN OUT NOCOPY qa_result_tbl_type,
2025 x_return_status OUT NOCOPY VARCHAR2)
2026 IS
2027
2028 l_api_name CONSTANT VARCHAR2(30) := 'G_Check_variables';
2029 l_indx NUMBER;
2030 l_int_severity OKC_QA_ERRORS_T.ERROR_SEVERITY%TYPE;
2031 l_int_desc OKC_QA_ERRORS_T.PROBLEM_SHORT_DESC%TYPE;
2032 l_ext_severity OKC_QA_ERRORS_T.ERROR_SEVERITY%TYPE;
2033 l_ext_desc OKC_QA_ERRORS_T.PROBLEM_SHORT_DESC%TYPE;
2034 l_perform_int_qa VARCHAR2(1);
2035 l_perform_ext_qa VARCHAR2(1);
2036 l_sys_severity OKC_QA_ERRORS_T.ERROR_SEVERITY%TYPE;
2037 l_sys_desc OKC_QA_ERRORS_T.PROBLEM_SHORT_DESC%TYPE;
2038 l_perform_sys_qa Varchar2(1);
2039 l_var_value_tbl OKC_TERMS_UTIL_GRP.sys_var_value_tbl_type;
2040 l_msg_count NUMBER;
2041 l_msg_data VARCHAR2(1000);
2042
2043
2044 -- Bug# 6002595. Modified the cursor to exclude user variables with procedures
2045 --Rep Enh, Modified cursor to fetch system defined variables for all Repository Document Types
2046 CURSOR l_check_variable_csr IS
2047 SELECT kart.id id,
2048 kart.sav_sae_id article_id,
2049 kart.amendment_operation_code amendment_operation_code,
2050 kart.scn_id scn_id,
2051 var.variable_code variable_code,
2052 busvar.variable_name variable_name,
2053 var.variable_value variable_value,
2054 var.variable_type,
2055 var.external_yn,
2056 var.variable_value_id
2057 FROM okc_k_articles_b kart,
2058 okc_k_art_variables var,
2059 okc_bus_variables_vl busvar
2060 WHERE kart.document_type=p_doc_type
2061 and kart.document_id=p_doc_id
2062 and var.cat_id=kart.id
2063 and busvar.variable_code=var.variable_code
2064 and nvl(kart.amendment_operation_code,'?')<>G_AMEND_CODE_DELETED
2065 and nvl(kart.summary_amend_operation_code,'?')<>G_AMEND_CODE_DELETED
2066 and ( (var.variable_type = 'U' AND busvar.variable_source = 'M') OR exists (( SELECT 'x' FROM okc_variable_doc_types vo
2067 WHERE vo.variable_code = var.variable_code
2068 AND vo.doc_type = p_doc_type
2069 )UNION all ( SELECT 'x' FROM okc_variable_doc_types vo
2070 WHERE vo.variable_code = var.variable_code
2071 AND vo.doc_type LIKE '%REPOSITORY%'))) ;
2072
2073
2074 BEGIN
2075
2076 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2077 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1500: Entered Check_variables');
2078 END IF;
2079
2080 get_qa_code_detail(p_qa_code => G_CHECK_UNRESOLVED_SYS_VAR,
2081 x_perform_qa => l_perform_sys_qa ,
2082 x_qa_name => l_sys_desc,
2083 x_severity_flag => l_sys_severity,
2084 x_return_status => x_return_status);
2085 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2086 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2087 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2088 RAISE FND_API.G_EXC_ERROR ;
2089 END IF;
2090
2091 get_qa_code_detail(p_qa_code => G_CHECK_INT_VAR_VALUE,
2092 x_perform_qa => l_perform_int_qa,
2093 x_qa_name => l_int_desc,
2094 x_severity_flag => l_int_severity,
2095 x_return_status => x_return_status);
2096 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2097 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2098 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2099 RAISE FND_API.G_EXC_ERROR ;
2100 END IF;
2101
2102 get_qa_code_detail(p_qa_code => G_CHECK_EXT_VAR_VALUE,
2103 x_perform_qa => l_perform_ext_qa,
2104 x_qa_name => l_ext_desc,
2105 x_severity_flag => l_ext_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 IF l_perform_sys_qa ='Y' THEN
2113 OKC_TERMS_UTIL_PVT.Get_System_Variables (
2114 p_api_version => 1,
2115 x_return_status => x_return_status,
2116 x_msg_data => l_msg_data,
2117 x_msg_count => l_msg_count,
2118 p_doc_type => p_doc_type,
2119 p_doc_id => p_doc_id,
2120 p_only_doc_variables => FND_API.G_TRUE,
2121 x_sys_var_value_tbl => l_var_value_tbl
2122 );
2123
2124 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2125 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2126 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2127 RAISE FND_API.G_EXC_ERROR ;
2128 END IF;
2129 END IF;-- IF l_perform_sys_qa ='Y' THEN
2130
2131 -- Bug# 6002595. Invoking check_user_vars_with_procs to check unresolved user variables with procedures
2132 IF l_perform_int_QA ='Y' THEN
2133
2134 check_user_vars_with_procs (
2135 p_doc_type => p_doc_type,
2136 p_doc_id => p_doc_id,
2137 px_qa_result_tbl => px_qa_result_tbl,
2138 x_return_status => x_return_status
2139 );
2140
2141 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2142 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2143 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2144 RAISE FND_API.G_EXC_ERROR ;
2145 END IF;
2146
2147 END IF;
2148 -- End of fix for Bug# 6002595.
2149
2150 IF l_perform_int_QA='Y' or l_perform_ext_qa='Y' or l_perform_sys_qa ='Y' THEN
2151
2152 FOR cr IN l_check_variable_csr LOOP
2153
2154 IF cr.variable_type='U' AND cr.external_yn='Y' AND l_perform_ext_qa='Y'
2155 and cr.variable_value IS NULL and cr.variable_value_id IS NULL THEN
2156
2157 l_indx := px_qa_result_tbl.COUNT + 1;
2158
2159 px_qa_result_tbl(l_indx).error_record_type:= G_ART_QA_TYPE;
2160 px_qa_result_tbl(l_indx).article_id := cr.article_id;
2161 px_qa_result_tbl(l_indx).deliverable_id := Null;
2162 px_qa_result_tbl(l_indx).title := get_article_title(cr.id);
2163 px_qa_result_tbl(l_indx).section_name := get_section_title(cr.scn_id);
2164 px_qa_result_tbl(l_indx).qa_code := G_CHECK_EXT_VAR_VALUE;
2165 px_qa_result_tbl(l_indx).message_name := G_OKC_CHECK_EXT_VAR_VALUE;
2166 px_qa_result_tbl(l_indx).suggestion := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC,G_OKC_CHECK_EXT_VAR_VALUE_S);
2167
2168 px_qa_result_tbl(l_indx).error_severity := l_ext_severity;
2169 px_qa_result_tbl(l_indx).problem_short_desc := l_ext_desc;
2170 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);
2171 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);
2172
2173 ELSIF cr.variable_type='U' AND cr.external_yn='N' AND l_perform_int_qa='Y'
2174 AND cr.variable_value IS NULL AND cr.variable_value_id IS NULL THEN
2175
2176 l_indx := px_qa_result_tbl.COUNT + 1;
2177
2178 px_qa_result_tbl(l_indx).error_record_type:= G_ART_QA_TYPE;
2179 px_qa_result_tbl(l_indx).article_id := cr.article_id;
2180 px_qa_result_tbl(l_indx).deliverable_id := Null;
2181 px_qa_result_tbl(l_indx).title := get_article_title(cr.id);
2182 px_qa_result_tbl(l_indx).qa_code := G_CHECK_INT_VAR_VALUE;
2183 px_qa_result_tbl(l_indx).message_name := G_OKC_CHECK_INT_VAR_VALUE;
2184 px_qa_result_tbl(l_indx).suggestion := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC,G_OKC_CHECK_INT_VAR_VALUE_S);
2185 px_qa_result_tbl(l_indx).section_name := get_section_title(cr.scn_id);
2186
2187 px_qa_result_tbl(l_indx).error_severity := l_INT_severity;
2188 px_qa_result_tbl(l_indx).problem_short_desc := l_INT_desc;
2189 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);
2190 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);
2191
2192 ELSIF cr.variable_type='S' AND l_perform_sys_qa='Y' THEN
2193
2194 IF l_var_value_tbl.COUNT > 0 THEN
2195 FOR i IN l_var_value_tbl.FIRST..l_var_value_tbl.LAST LOOP
2196 IF l_var_value_tbl(i).variable_code=cr.variable_code AND l_var_value_tbl(i).variable_value_id is NULL THEN
2197
2198 l_indx := px_qa_result_tbl.COUNT + 1;
2199 px_qa_result_tbl(l_indx).error_record_type:= G_ART_QA_TYPE;
2200 px_qa_result_tbl(l_indx).article_id := cr.article_id;
2201 px_qa_result_tbl(l_indx).deliverable_id := Null;
2202 px_qa_result_tbl(l_indx).title := get_article_title(cr.id);
2203 px_qa_result_tbl(l_indx).section_name := get_section_title(cr.scn_id);
2204 px_qa_result_tbl(l_indx).qa_code := G_CHECK_UNRESOLVED_SYS_VAR;
2205 px_qa_result_tbl(l_indx).message_name := G_OKC_CHECK_UNRES_SYS_VAR;
2206 px_qa_result_tbl(l_indx).suggestion := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC,G_OKC_CHECK_UNRES_SYS_VAR_S);
2207
2208 px_qa_result_tbl(l_indx).error_severity := l_sys_severity;
2209 px_qa_result_tbl(l_indx).problem_short_desc := l_sys_desc;
2210 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);
2211 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);
2212 END IF; -- IF l_var_value_tbl(i).variable_code
2213 END LOOP; -- FOR i IN l_var_value_tbl.FIRST..
2214 END IF; -- IF l_var_value_tbl.COUNT > 0 THEN
2215
2216 END IF; -- IF cr_variable_type<>'S' AND cr.external_yn='Y'
2217
2218 END LOOP; -- FOR cr IN l_check_variable_csr LOOP
2219
2220 END IF; -- IF l_perform_int_QA='Y' or l_perform_ext_qa='Y' or l_perf
2221
2222
2223 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2224 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1600: Leaving Check_variables');
2225 END IF;
2226
2227 EXCEPTION
2228 WHEN FND_API.G_EXC_ERROR THEN
2229 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2230 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7800: Leaving Check_variables : OKC_API.G_EXCEPTION_ERROR Exception');
2231 END IF;
2232
2233 IF l_check_variable_csr%ISOPEN THEN
2234 CLOSE l_check_variable_csr;
2235 END IF;
2236
2237 x_return_status := G_RET_STS_ERROR ;
2238
2239 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2240 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2241 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7900: Leaving Check_variables : OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
2242 END IF;
2243
2244 IF l_check_variable_csr%ISOPEN THEN
2245 CLOSE l_check_variable_csr;
2246 END IF;
2247
2248 x_return_status := G_RET_STS_UNEXP_ERROR ;
2249
2250 WHEN OTHERS THEN
2251 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2252 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'8000: Leaving Check_variables because of EXCEPTION: '||sqlerrm);
2253 END IF;
2254
2255 IF l_check_variable_csr%ISOPEN THEN
2256 CLOSE l_check_variable_csr;
2257 END IF;
2258
2259 x_return_status := G_RET_STS_UNEXP_ERROR ;
2260
2261 END check_variables;
2262
2263 -------------------------------------------
2264 -- PROCEDURE check_unassigned_articles
2265 -------------------------------------------
2266 PROCEDURE check_unassigned_articles (
2267 p_doc_type IN VARCHAR2,
2268 p_doc_id IN NUMBER,
2269 px_qa_result_tbl IN OUT NOCOPY qa_result_tbl_type,
2270 x_return_status OUT NOCOPY VARCHAR2)
2271 IS
2272
2273 l_api_name CONSTANT VARCHAR2(30) := 'G_check_unassigned_articles';
2274 l_indx NUMBER;
2275 l_severity OKC_QA_ERRORS_T.ERROR_SEVERITY%TYPE;
2276 l_desc OKC_QA_ERRORS_T.PROBLEM_SHORT_DESC%TYPE;
2277 l_perform_unas_art_qa VARCHAR2(1);
2278
2279 CURSOR l_get_unass_art_crs IS
2280 SELECT
2281 kart.id id,
2282 kart.sav_sae_id article_id,
2283 kart.scn_id scn_id
2284 FROM OKC_K_ARTICLES_B KART,
2285 OKC_SECTIONS_B SCN
2286 WHERE kart.document_type=p_doc_type
2287 AND kart.document_id =p_doc_id
2288 AND scn.id = kart.scn_id
2289 AND scn.scn_code = G_UNASSIGNED_SECTION_CODE
2290 AND nvl(scn.amendment_operation_code,'?') <> G_AMEND_CODE_DELETED
2291 AND nvl(kart.amendment_operation_code,'?') <> G_AMEND_CODE_DELETED
2292 AND nvl(kart.summary_amend_operation_code,'?') <> G_AMEND_CODE_DELETED;
2293
2294 BEGIN
2295
2296 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2297 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1500: Entered check_unassigned_articles');
2298 END IF;
2299
2300 get_qa_code_detail(p_qa_code => G_CHECK_UNASSIGNED_ART,
2301 x_perform_qa => l_perform_unas_art_qa,
2302 x_qa_name => l_desc,
2303 x_severity_flag => l_severity,
2304 x_return_status => x_return_status);
2305 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2306 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2307 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2308 RAISE FND_API.G_EXC_ERROR ;
2309 END IF;
2310
2311 IF l_perform_unas_art_qa='Y' THEN
2312 FOR cr IN l_get_unass_art_crs LOOP
2313
2314 l_indx := px_qa_result_tbl.COUNT + 1;
2315
2316 px_qa_result_tbl(l_indx).error_record_type:= G_ART_QA_TYPE;
2317 px_qa_result_tbl(l_indx).article_id := cr.article_id;
2318 px_qa_result_tbl(l_indx).deliverable_id := Null;
2319 px_qa_result_tbl(l_indx).title := get_article_title(cr.id);
2320 px_qa_result_tbl(l_indx).section_name := get_section_title(cr.scn_id);
2321 px_qa_result_tbl(l_indx).qa_code := G_CHECK_UNASSIGNED_ART;
2322 px_qa_result_tbl(l_indx).message_name := G_OKC_CHECK_UNASSIGNED_ART;
2323 px_qa_result_tbl(l_indx).suggestion := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC,G_OKC_CHECK_UNASSIGNED_ART_S);
2324 px_qa_result_tbl(l_indx).error_severity := l_severity;
2325 px_qa_result_tbl(l_indx).problem_short_desc := l_desc;
2326 px_qa_result_tbl(l_indx).problem_details_short := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_CHECK_UNASSIGNED_ART_SH);
2327 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);
2328
2329 END LOOP; -- FOR cr IN l_get_unass_art_crs LOOP
2330 END IF; -- IF l_perform_unas_art_qa='Y' THEN
2331
2332 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2333 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1600: Leaving check_unassigned_articles');
2334 END IF;
2335
2336 EXCEPTION
2337 WHEN FND_API.G_EXC_ERROR THEN
2338 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2339 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7800: Leaving check_unassigned_articles : OKC_API.G_EXCEPTION_ERROR Exception');
2340 END IF;
2341
2342 IF l_get_unass_art_crs%ISOPEN THEN
2343 CLOSE l_get_unass_art_crs;
2344 END IF;
2345
2346 x_return_status := G_RET_STS_ERROR ;
2347
2348 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2349 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2350 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7900: Leaving check_unassigned_articles : OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
2351 END IF;
2352
2353 IF l_get_unass_art_crs%ISOPEN THEN
2354 CLOSE l_get_unass_art_crs;
2355 END IF;
2356
2357 x_return_status := G_RET_STS_UNEXP_ERROR ;
2358
2359 WHEN OTHERS THEN
2360 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2361 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'8000: Leaving check_unassigned_articles because of EXCEPTION: '||sqlerrm);
2362 END IF;
2363
2364 IF l_get_unass_art_crs%ISOPEN THEN
2365 CLOSE l_get_unass_art_crs;
2366 END IF;
2367
2368 x_return_status := G_RET_STS_UNEXP_ERROR ;
2369
2370 END check_unassigned_articles;
2371
2372 -------------------------------------------
2373 -- PROCEDURE check_empty_sections
2374 -------------------------------------------
2375 PROCEDURE check_empty_sections (
2376 p_doc_type IN VARCHAR2,
2377 p_doc_id IN NUMBER,
2378 px_qa_result_tbl IN OUT NOCOPY qa_result_tbl_type,
2379 x_return_status OUT NOCOPY VARCHAR2)
2380 IS
2381
2382 l_api_name CONSTANT VARCHAR2(30) := 'G_check_empty_sections';
2383 l_indx NUMBER;
2384 l_severity OKC_QA_ERRORS_T.ERROR_SEVERITY%TYPE;
2385 l_desc OKC_QA_ERRORS_T.PROBLEM_SHORT_DESC%TYPE;
2386 l_perform_empty_scn_qa VARCHAR2(1);
2387
2388 CURSOR l_get_empty_section_csr IS
2389 SELECT
2390 id id,
2391 scn_id scn_id
2392 FROM OKC_SECTIONS_B SCN
2393 WHERE document_type=p_doc_type
2394 AND document_id =p_doc_id
2395 AND nvl(amendment_operation_code,'?') <> G_AMEND_CODE_DELETED
2396 AND nvl(summary_amend_operation_code,'?') <> G_AMEND_CODE_DELETED
2397 AND not exists ( SELECT 'x' FROM OKC_K_ARTICLES_B WHERE scn_id=scn.id
2398 AND nvl(amendment_operation_code,'?') <> G_AMEND_CODE_DELETED
2399 AND nvl(summary_amend_operation_code,'?') <> G_AMEND_CODE_DELETED)
2400 AND not exists ( SELECT 'x' FROM OKC_SECTIONS_B SCN1 WHERE SCN1.scn_id = scn.id
2401 AND nvl(amendment_operation_code,'?') <> G_AMEND_CODE_DELETED
2402 AND nvl(summary_amend_operation_code,'?') <> G_AMEND_CODE_DELETED);
2403 -- if section has sub sections then it is not considered empty Bug 3219528
2404 BEGIN
2405
2406 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2407 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1500: Entered check_empty_sections');
2408 END IF;
2409
2410 get_qa_code_detail(p_qa_code => G_CHECK_EMPTY_SECTION,
2411 x_perform_qa => l_perform_empty_scn_qa,
2412 x_qa_name => l_desc,
2413 x_severity_flag => l_severity,
2414 x_return_status => x_return_status);
2415 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2416 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2417 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2418 RAISE FND_API.G_EXC_ERROR ;
2419 END IF;
2420
2421 IF l_perform_empty_scn_QA='Y' THEN
2422 FOR cr IN l_get_empty_section_csr LOOP
2423
2424 l_indx := px_qa_result_tbl.COUNT + 1;
2425
2426 px_qa_result_tbl(l_indx).error_record_type:= G_SCN_QA_TYPE;
2427 px_qa_result_tbl(l_indx).article_id := Null;
2428 px_qa_result_tbl(l_indx).deliverable_id := Null;
2429 px_qa_result_tbl(l_indx).title := get_section_title(cr.id);
2430 px_qa_result_tbl(l_indx).section_name := get_section_title(cr.scn_id);
2431 px_qa_result_tbl(l_indx).qa_code := G_CHECK_EMPTY_SECTION;
2432 px_qa_result_tbl(l_indx).message_name := G_OKC_CHECK_EMPTY_SECTION;
2433 px_qa_result_tbl(l_indx).suggestion := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC,G_OKC_CHECK_EMPTY_SECTION_S);
2434 px_qa_result_tbl(l_indx).error_severity := l_severity;
2435 px_qa_result_tbl(l_indx).problem_short_desc := l_desc;
2436 px_qa_result_tbl(l_indx).problem_details_short := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_CHECK_EMPTY_SECTION_SH);
2437 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);
2438
2439 END LOOP; -- FOR cr IN l_get_empty_section_csr LOOP
2440 END IF; -- IF l_perform_empty_scn_QA='Y' THEN
2441
2442 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2443 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1600: Leaving check_empty_sections');
2444 END IF;
2445
2446 EXCEPTION
2447 WHEN FND_API.G_EXC_ERROR THEN
2448 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2449 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7800: Leaving check_empty_sections : OKC_API.G_EXCEPTION_ERROR Exception');
2450 END IF;
2451
2452 IF l_get_empty_section_csr%ISOPEN THEN
2453 CLOSE l_get_empty_section_csr;
2454 END IF;
2455
2456 x_return_status := G_RET_STS_ERROR ;
2457
2458 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2459 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2460 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7900: Leaving check_empty_sections : OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
2461 END IF;
2462
2463 IF l_get_empty_section_csr%ISOPEN THEN
2464 CLOSE l_get_empty_section_csr;
2465 END IF;
2466
2467 x_return_status := G_RET_STS_UNEXP_ERROR ;
2468
2469 WHEN OTHERS THEN
2470 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2471 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'8000: Leaving check_empty_sections because of EXCEPTION: '||sqlerrm);
2472 END IF;
2473
2474 IF l_get_empty_section_csr%ISOPEN THEN
2475 CLOSE l_get_empty_section_csr;
2476 END IF;
2477
2478 x_return_status := G_RET_STS_UNEXP_ERROR ;
2479 END check_empty_sections;
2480
2481 -------------------------------------------
2482 -- PROCEDURE check_section_amend_no_texts
2483 -------------------------------------------
2484 PROCEDURE check_section_amend_no_texts (
2485 p_doc_type IN VARCHAR2,
2486 p_doc_id IN NUMBER,
2487 px_qa_result_tbl IN OUT NOCOPY qa_result_tbl_type,
2488 x_return_status OUT NOCOPY VARCHAR2)
2489 IS
2490
2491 l_api_name CONSTANT VARCHAR2(30) := 'G_Check_Section_Amend_No_Texts';
2492 l_indx NUMBER;
2493 l_severity OKC_QA_ERRORS_T.ERROR_SEVERITY%TYPE;
2494 l_desc OKC_QA_ERRORS_T.PROBLEM_SHORT_DESC%TYPE;
2495 l_perform_scn_amend_qa VARCHAR2(1);
2496 l_disable_amend_yn VARCHAR2(1);
2497
2498 CURSOR l_get_doc_disable_amend_csr IS
2499 SELECT NVL(disable_amend_yn,'N')
2500 FROM OKC_BUS_DOC_TYPES_B
2501 WHERE document_type = p_doc_type;
2502
2503 BEGIN
2504
2505 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2506 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1500: Entered Check_Section_Amend_No_Texts');
2507 END IF;
2508
2509 --Bug 3681462 Check for disable_amend_yn
2510 OPEN l_get_doc_disable_amend_csr;
2511 FETCH l_get_doc_disable_amend_csr INTO l_disable_amend_yn;
2512 CLOSE l_get_doc_disable_amend_csr;
2513
2514 IF l_disable_amend_yn = 'N' THEN
2515
2516 get_qa_code_detail(p_qa_code => G_CHECK_SCN_AMEND_NO_TEXT,
2517 x_perform_qa => l_perform_scn_amend_qa,
2518 x_qa_name => l_desc,
2519 x_severity_flag => l_severity,
2520 x_return_status => x_return_status);
2521 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2522 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2523 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2524 RAISE FND_API.G_EXC_ERROR ;
2525 END IF;
2526
2527 IF l_perform_scn_amend_qa='Y' THEN
2528 IF l_section_tbl.count>0 THEN
2529 FOR i IN l_section_tbl.FIRST..l_section_tbl.LAST LOOP
2530
2531 IF l_section_tbl(i).amendment_operation_code IS NOT NULL AND l_section_tbl(i).amendment_description IS NULL THEN
2532
2533 l_indx := px_qa_result_tbl.COUNT + 1;
2534
2535 px_qa_result_tbl(l_indx).error_record_type:= G_SCN_QA_TYPE;
2536 px_qa_result_tbl(l_indx).article_id := Null;
2537 px_qa_result_tbl(l_indx).deliverable_id := Null;
2538 px_qa_result_tbl(l_indx).title := get_section_title(l_section_tbl(i).id);
2539 px_qa_result_tbl(l_indx).section_name := get_section_title(l_section_tbl(i).scn_id);
2540 px_qa_result_tbl(l_indx).qa_code := G_CHECK_SCN_AMEND_NO_TEXT;
2541 px_qa_result_tbl(l_indx).message_name := G_OKC_CHECK_SCN_AMEND_NO_TEXT;
2542 px_qa_result_tbl(l_indx).suggestion := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC,G_OKC_CHK_SCN_AMEND_NO_TEXT_S);
2543 px_qa_result_tbl(l_indx).error_severity := l_severity;
2544 px_qa_result_tbl(l_indx).problem_short_desc := l_desc;
2545 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);
2546 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);
2547 END IF; -- IF l_section_tbl(i).amendment_operation_code
2548
2549 END LOOP;-- FOR i IN l_section_tbl.FIRST..l_section_tbl.LAST
2550 END IF; -- IF l_section_tbl.count>0 THEN
2551 END IF; -- IF l_perform_scn_amend_qa='Y' THEN
2552
2553 END IF; -- l_disable_amend_yn = 'N'
2554
2555 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2556 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1600: Leaving Check_Section_Amend_No_Texts');
2557 END IF;
2558
2559 EXCEPTION
2560 WHEN FND_API.G_EXC_ERROR THEN
2561 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2562 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');
2563 END IF;
2564
2565 x_return_status := G_RET_STS_ERROR ;
2566
2567 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2568 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2569 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');
2570 END IF;
2571
2572 x_return_status := G_RET_STS_UNEXP_ERROR ;
2573
2574 WHEN OTHERS THEN
2575 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2576 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'8000: Leaving Check_Section_Amend_No_Texts because of EXCEPTION: '||sqlerrm);
2577 END IF;
2578
2579 x_return_status := G_RET_STS_UNEXP_ERROR ;
2580 END check_section_amend_no_texts;
2581
2582 -------------------------------------------
2583 -- PROCEDURE check_article_amend_no_texts
2584 -------------------------------------------
2585 PROCEDURE check_article_amend_no_texts (
2586 p_doc_type IN VARCHAR2,
2587 p_doc_id IN NUMBER,
2588 px_qa_result_tbl IN OUT NOCOPY qa_result_tbl_type,
2589 x_return_status OUT NOCOPY VARCHAR2)
2590 IS
2591
2592 l_api_name CONSTANT VARCHAR2(30) := 'G_check_article_amend_no_texts';
2593 l_indx NUMBER;
2594 l_severity OKC_QA_ERRORS_T.ERROR_SEVERITY%TYPE;
2595 l_desc OKC_QA_ERRORS_T.PROBLEM_SHORT_DESC%TYPE;
2596 l_perform_art_amend_qa VARCHAR2(1);
2597 l_disable_amend_yn VARCHAR2(1);
2598
2599 CURSOR l_get_doc_disable_amend_csr IS
2600 SELECT NVL(disable_amend_yn,'N')
2601 FROM OKC_BUS_DOC_TYPES_B
2602 WHERE document_type = p_doc_type;
2603
2604 BEGIN
2605
2606 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2607 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1500: Entered check_article_amend_no_texts');
2608 END IF;
2609
2610 --Bug 3681462 Check for disable_amend_yn
2611 OPEN l_get_doc_disable_amend_csr;
2612 FETCH l_get_doc_disable_amend_csr INTO l_disable_amend_yn;
2613 CLOSE l_get_doc_disable_amend_csr;
2614
2615 IF l_disable_amend_yn = 'N' THEN
2616
2617 get_qa_code_detail(p_qa_code => G_CHECK_ART_AMEND_NO_TEXT,
2618 x_perform_qa => l_perform_art_amend_qa,
2619 x_qa_name => l_desc,
2620 x_severity_flag => l_severity,
2621 x_return_status => x_return_status);
2622 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2623 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2624 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2625 RAISE FND_API.G_EXC_ERROR ;
2626 END IF;
2627
2628 IF l_perform_art_amend_qa ='Y' THEN
2629
2630 IF l_article_tbl.count > 0 THEN
2631
2632 FOR i IN l_article_tbl.FIRST..l_article_tbl.LAST LOOP
2633
2634 IF l_article_tbl(i).amendment_operation_code IS NOT NULL
2635 AND l_article_tbl(i).amendment_description IS NULL THEN
2636
2637 l_indx := px_qa_result_tbl.COUNT + 1;
2638
2639 px_qa_result_tbl(l_indx).error_record_type:= G_ART_QA_TYPE;
2640 px_qa_result_tbl(l_indx).article_id := l_article_tbl(i).article_id;
2641 px_qa_result_tbl(l_indx).deliverable_id := Null;
2642 px_qa_result_tbl(l_indx).title := get_article_title(l_article_tbl(i).id);
2643 px_qa_result_tbl(l_indx).section_name := get_section_title(l_article_tbl(i).scn_id);
2644 px_qa_result_tbl(l_indx).qa_code := G_CHECK_ART_AMEND_NO_TEXT;
2645 px_qa_result_tbl(l_indx).message_name := G_OKC_CHECK_ART_AMEND_NO_TEXT;
2646 px_qa_result_tbl(l_indx).suggestion := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC,G_OKC_CHK_ART_AMEND_NO_TEXT_S);
2647 px_qa_result_tbl(l_indx).error_severity := l_severity;
2648 px_qa_result_tbl(l_indx).problem_short_desc := l_desc;
2649 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);
2650 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);
2651 END IF; -- IF l_article_tbl(i).amendment_operation_code I
2652
2653 END LOOP; -- FOR i IN l_article_tbl.FIRST..l_article_tbl.LAST LOOP
2654 END IF; -- IF l_article_tbl.count>0 THEN
2655 END IF; -- IF l_perform_art_amend_qa ='Y' THEN
2656
2657 END IF; -- l_disable_amend_yn = 'N'
2658
2659 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2660 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1600: Leaving check_article_amend_no_texts');
2661 END IF;
2662
2663 EXCEPTION
2664 WHEN FND_API.G_EXC_ERROR THEN
2665 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2666 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');
2667 END IF;
2668
2669 x_return_status := G_RET_STS_ERROR ;
2670
2671 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2672 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2673 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');
2674 END IF;
2675
2676 x_return_status := G_RET_STS_UNEXP_ERROR ;
2677
2678 WHEN OTHERS THEN
2679 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2680 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'8000: Leaving check_article_amend_no_texts because of EXCEPTION: '||sqlerrm);
2681 END IF;
2682
2683 x_return_status := G_RET_STS_UNEXP_ERROR ;
2684 END check_article_amend_no_texts;
2685
2686 -------------------------------------------
2687 -- PROCEDURE check_inactive_template
2688 -------------------------------------------
2689 /* Check inactive Template */
2690
2691 PROCEDURE check_inactive_template (
2692 p_doc_type IN VARCHAR2,
2693 p_doc_id IN NUMBER,
2694 px_qa_result_tbl IN OUT NOCOPY qa_result_tbl_type,
2695 x_return_status OUT NOCOPY VARCHAR2)
2696 IS
2697
2698 l_api_name CONSTANT VARCHAR2(30) := 'check_inactive_template';
2699 l_indx NUMBER;
2700 l_severity OKC_QA_ERRORS_T.ERROR_SEVERITY%TYPE;
2701 l_desc OKC_QA_ERRORS_T.PROBLEM_SHORT_DESC%TYPE;
2702 l_perform_tmpl_qa VARCHAR2(1);
2703 l_tmpl_usg_exists_flag varchar2(1):=OKC_API.G_MISS_CHAR;
2704 l_doc_type_name OKC_BUS_DOC_TYPES_TL.NAME%TYPE;
2705
2706
2707 CURSOR l_get_template_crs IS
2708 SELECT status_code,end_date,template_name
2709 FROM
2710 OKC_TERMS_TEMPLATES_ALL TMPL,
2711 OKC_TEMPLATE_USAGES USG
2712 WHERE USG.DOCUMENT_TYPE = p_doc_type
2713 AND USG.DOCUMENT_ID = p_doc_id
2714 AND TMPL.TEMPLATE_ID = USG.TEMPLATE_ID;
2715
2716 --Bug 4126819 Added cursor to get check if the template is associated to the document.
2717 CURSOR l_get_template_usg_csr IS
2718 SELECT 'Y'
2719 FROM OKC_ALLOWED_TMPL_USAGES
2720 WHERE TEMPLATE_ID = (SELECT TEMPLATE_ID
2721 FROM OKC_TEMPLATE_USAGES
2722 WHERE DOCUMENT_ID = p_doc_id
2723 AND DOCUMENT_TYPE = p_doc_type)
2724 AND DOCUMENT_TYPE = p_doc_type;
2725 --Bug 4126819 Added cursor to get the document type
2726 CURSOR l_get_doc_type_name IS
2727 SELECT name
2728 FROM okc_bus_doc_types_tl
2729 WHERE document_type = p_doc_type
2730 AND LANGUAGE = userenv('LANG');
2731
2732 BEGIN
2733
2734 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2735 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1500: Entered check_inactive_template');
2736 END IF;
2737
2738 get_qa_code_detail(p_qa_code => G_CHECK_TMPL_EFFECTIVITY,
2739 x_perform_qa => l_perform_tmpl_qa,
2740 x_qa_name => l_desc,
2741 x_severity_flag => l_severity,
2742 x_return_status => x_return_status);
2743 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2744 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2745 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2746 RAISE FND_API.G_EXC_ERROR ;
2747 END IF;
2748
2749 IF l_perform_tmpl_qa ='Y' THEN
2750 FOR cr in l_get_template_crs LOOP
2751
2752 IF cr.status_code='ON_HOLD'
2753 OR ( cr.status_code='APPROVED'
2754 AND nvl(cr.end_date,sysdate+1)< sysdate) THEN
2755
2756 l_indx := px_qa_result_tbl.COUNT + 1;
2757
2758 px_qa_result_tbl(l_indx).error_record_type := G_TMPL_QA_TYPE;
2759 px_qa_result_tbl(l_indx).article_id := Null;
2760 px_qa_result_tbl(l_indx).deliverable_id := Null;
2761 px_qa_result_tbl(l_indx).title := cr.template_name;
2762 px_qa_result_tbl(l_indx).section_name := Null;
2763 px_qa_result_tbl(l_indx).qa_code := G_CHECK_TMPL_EFFECTIVITY;
2764 px_qa_result_tbl(l_indx).message_name := G_OKC_CHECK_TMPL_EFFECTIVITY;
2765 px_qa_result_tbl(l_indx).suggestion := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC,G_OKC_CHECK_TMPL_EFFECTIVITY_S);
2766 px_qa_result_tbl(l_indx).error_severity := l_severity;
2767 px_qa_result_tbl(l_indx).problem_short_desc := l_desc;
2768 px_qa_result_tbl(l_indx).problem_details_short := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_CHK_TMPL_EFFECTIVITY_SH);
2769 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);
2770 END IF; -- IF cr.status_code='ON_HOLD'
2771
2772 --Bug 4126819 Getting the document type
2773 OPEN l_get_doc_type_name;
2774 FETCH l_get_doc_type_name into l_doc_type_name;
2775 CLOSE l_get_doc_type_name;
2776
2777 --Checking if the template is associated to the document type.
2778 OPEN l_get_template_usg_csr;
2779 FETCH l_get_template_usg_csr into l_tmpl_usg_exists_flag;
2780 IF l_get_template_usg_csr%NOTFOUND THEN
2781
2782 l_indx := px_qa_result_tbl.COUNT + 1;
2783
2784 px_qa_result_tbl(l_indx).error_record_type := G_TMPL_QA_TYPE;
2785 px_qa_result_tbl(l_indx).article_id := Null;
2786 px_qa_result_tbl(l_indx).deliverable_id := Null;
2787 px_qa_result_tbl(l_indx).title := cr.template_name;
2788 px_qa_result_tbl(l_indx).section_name := Null;
2789 px_qa_result_tbl(l_indx).qa_code := G_CHECK_TMPL_EFFECTIVITY;
2790 px_qa_result_tbl(l_indx).message_name := G_OKC_CHECK_TEMPL_USG_ASSO;
2791 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);
2792 px_qa_result_tbl(l_indx).error_severity := l_severity;
2793 px_qa_result_tbl(l_indx).problem_short_desc := l_desc;
2794 px_qa_result_tbl(l_indx).problem_details_short := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_CHECK_TEMPL_USG_ASSO_SH);
2795 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);
2796 END IF;
2797 CLOSE l_get_template_usg_csr;
2798
2799 END LOOP; -- FOR cr in l_get_template_crs LOOP
2800 END IF; -- IF l_perform_tmpl_qa ='Y' THEN
2801
2802
2803 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2804 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1600: Leaving check_inactive_template');
2805 END IF;
2806
2807 EXCEPTION
2808 WHEN FND_API.G_EXC_ERROR THEN
2809 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2810 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7800: Leaving check_inactive_template : OKC_API.G_EXCEPTION_ERROR Exception');
2811 END IF;
2812
2813 IF l_get_template_crs%ISOPEN THEN
2814 CLOSE l_get_template_crs;
2815 END IF;
2816
2817 x_return_status := G_RET_STS_ERROR ;
2818
2819 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2820 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2821 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7900: Leaving check_inactive_template : OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
2822 END IF;
2823
2824 IF l_get_template_crs%ISOPEN THEN
2825 CLOSE l_get_template_crs;
2826 END IF;
2827
2828 x_return_status := G_RET_STS_UNEXP_ERROR ;
2829
2830 WHEN OTHERS THEN
2831 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2832 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'8000: Leaving check_inactive_template because of EXCEPTION: '||sqlerrm);
2833 END IF;
2834
2835 IF l_get_template_crs%ISOPEN THEN
2836 CLOSE l_get_template_crs;
2837 END IF;
2838
2839 x_return_status := G_RET_STS_UNEXP_ERROR ;
2840 END check_inactive_template;
2841
2842 -------------------------------------------
2843 -- PROCEDURE check_art_effectivity
2844 -------------------------------------------
2845 /* Check article effectitvity */
2846
2847 PROCEDURE check_art_effectivity (
2848 p_qa_mode IN VARCHAR2,
2849 p_doc_type IN VARCHAR2,
2850 p_doc_id IN NUMBER,
2851 px_qa_result_tbl IN OUT NOCOPY qa_result_tbl_type,
2852 x_return_status OUT NOCOPY VARCHAR2 )
2853 IS
2854
2855 l_api_name CONSTANT VARCHAR2(30) := 'G_check_art_effectivity';
2856 l_indx NUMBER;
2857 l_indx1 NUMBER;
2858 l_art_val_severity OKC_QA_ERRORS_T.ERROR_SEVERITY%TYPE;
2859 l_art_val_desc OKC_QA_ERRORS_T.PROBLEM_SHORT_DESC%TYPE;
2860
2861 l_lat_vers_severity OKC_QA_ERRORS_T.ERROR_SEVERITY%TYPE;
2862 l_lat_vers_desc OKC_QA_ERRORS_T.PROBLEM_SHORT_DESC%TYPE;
2863 l_start_date DATE;
2864 l_end_date DATE;
2865 l_found BOOLEAN := FALSE;
2866
2867 l_perform_art_val_qa VARCHAR2(1);
2868 l_perform_lat_vers_qa VARCHAR2(1);
2869 TYPE article_version_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
2870 l_article_version_tbl article_version_tbl_type;
2871
2872 CURSOR l_get_tmpl_dates_csr IS
2873 SELECT START_DATE,END_DATE
2874 FROM OKC_TERMS_TEMPLATES_ALL
2875 WHERE TEMPLATE_ID=p_doc_id;
2876
2877 /* This will executed only in TEMPLATE QA */
2878 /*
2879 CURSOR l_check_art_tmpl_csr(p_article_effective_date IN DATE) IS
2880 SELECT kart.id,
2881 kart.sav_sae_id article_id,
2882 kart.scn_id scn_id,
2883 kart.amendment_operation_code amendment_operation_code,
2884 kart.amendment_description amendment_description
2885 FROM OKC_K_ARTICLES_B KART
2886 WHERE DOCUMENT_TYPE=p_doc_type
2887 AND DOCUMENT_ID =p_doc_id
2888 AND NOT EXISTS ( SELECT 'X' FROM OKC_ARTICLE_VERSIONS VERS
2889 WHERE VERS.ARTICLE_ID=KART.SAV_SAE_ID
2890 AND VERS.ARTICLE_STATUS='APPROVED'
2891 AND nvl(p_article_effective_date,sysdate) >= VERS.START_DATE
2892 AND nvl(p_article_effective_date,sysdate) <= nvl(VERS.end_date, nvl(p_article_effective_date,sysdate) +1)
2893 );
2894
2895 */
2896
2897 /* This will executed only in TEMPLATE QA
2898 11.5.10+ Modify the cursor l_check_art_tmpl_csr to allow draft/rejected clauses also*/
2899
2900 CURSOR l_check_art_tmpl_csr(p_article_effective_date IN DATE) IS
2901 SELECT kart.id,
2902 kart.sav_sae_id article_id,
2903 kart.scn_id scn_id,
2904 kart.amendment_operation_code amendment_operation_code,
2905 kart.amendment_description amendment_description
2906 FROM OKC_K_ARTICLES_B KART,
2907 okc_terms_templates_all tmpl,
2908 okc_articles_all art
2909 WHERE kart.document_id = tmpl.template_id
2910 AND kart.sav_sae_id = art.article_id
2911 AND DOCUMENT_TYPE=p_doc_type
2912 AND DOCUMENT_ID =p_doc_id
2913 AND
2914 (
2915 ( art.org_id <> tmpl.org_id
2916 AND NOT EXISTS ( SELECT 'X'
2917 FROM OKC_ARTICLE_ADOPTIONS ADP,
2918 OKC_ARTICLE_VERSIONS VER
2919 WHERE ADP.GLOBAL_ARTICLE_VERSION_ID = VER.article_version_id
2920 AND VER.article_id = KART.SAV_SAE_ID
2921 AND ADP.LOCAL_ORG_ID = tmpl.org_id
2922 AND ADP.adoption_status IN ( 'APPROVED', 'ON_HOLD')
2923 AND ADP.ADOPTION_TYPE = 'ADOPTED'
2924 AND VER.ARTICLE_STATUS='APPROVED'
2925 AND nvl(p_article_effective_date,sysdate) >= VER.START_DATE
2926 AND nvl(p_article_effective_date,sysdate)
2927 <= nvl(VER.end_date, nvl(p_article_effective_date,sysdate) +1)
2928 )
2929 ) OR
2930 ( art.org_id = tmpl.org_id
2931 AND NOT EXISTS ( SELECT 'X' FROM OKC_ARTICLE_VERSIONS VERS
2932 WHERE VERS.ARTICLE_ID=KART.SAV_SAE_ID
2933 -- modified to include DRAFT and REJECTED statuses also
2934 --AND VERS.ARTICLE_STATUS='APPROVED'
2935 AND VERS.ARTICLE_STATUS in ('APPROVED', 'DRAFT' , 'REJECTED')
2936 AND nvl(p_article_effective_date,sysdate) >= VERS.START_DATE
2937 AND nvl(p_article_effective_date,sysdate) <= nvl(VERS.end_date, nvl(p_article_effective_date,sysdate) +1)
2938 )
2939 )
2940 );
2941
2942
2943 /* Define a new cursor to check expert clauses for validity */
2944 /* expert commented out
2945 CURSOR l_check_xprt_art_tmpl_csr (p_article_effective_date IN DATE) IS
2946 SELECT
2947 Rule.clause_id xprt_article_id,
2948 Rule.rule_id rule_id,
2949 Rule.rule_name rule_name
2950 FROM okc_xprt_clauses_v rule,
2951 okc_terms_templates_all tmpl,
2952 okc_articles_all art
2953 WHERE rule.template_id = tmpl.template_id
2954 AND tmpl.template_id = p_doc_id
2955 AND art.article_id = rule.clause_id
2956 AND
2957 (
2958 ( art.org_id <> tmpl.org_id
2959 AND NOT EXISTS ( SELECT 'X'
2960 FROM OKC_ARTICLE_ADOPTIONS ADP,
2961 OKC_ARTICLE_VERSIONS VER
2962 WHERE ADP.GLOBAL_ARTICLE_VERSION_ID = VER.article_version_id
2963 AND VER.article_id = rule.clause_id
2964 AND ADP.LOCAL_ORG_ID = tmpl.org_id
2965 AND ADP.adoption_status IN ( 'APPROVED', 'ON_HOLD')
2966 AND ADP.ADOPTION_TYPE = 'ADOPTED'
2967 AND VER.ARTICLE_STATUS='APPROVED'
2968 AND nvl(p_article_effective_date,sysdate) >= VER.START_DATE
2969 AND nvl(p_article_effective_date,sysdate) <= nvl(VER.end_date,
2970 nvl(p_article_effective_date,sysdate) +1)
2971 )
2972 )
2973 OR
2974 ( art.org_id = tmpl.org_id
2975 AND NOT EXISTS ( SELECT 'X' FROM OKC_ARTICLE_VERSIONS VERS
2976 WHERE VERS.ARTICLE_ID=rule.clause_id
2977 AND VERS.ARTICLE_STATUS in ('APPROVED', 'DRAFT' , 'REJECTED')
2978 AND nvl(p_article_effective_date,sysdate) >= VERS.START_DATE
2979 AND nvl(p_article_effective_date,sysdate) <= nvl(VERS.end_date,
2980 nvl(p_article_effective_date,sysdate) +1)
2981 )
2982 )
2983 );
2984 */
2985
2986 CURSOR l_check_art_doc_csr(b_effective_date IN DATE) IS
2987 SELECT kart.id,
2988 kart.sav_sae_id article_id,
2989 kart.article_version_id article_version_id,
2990 kart.scn_id scn_id,
2991 vers.start_date start_date,
2992 kart.amendment_operation_code amendment_operation_code,
2993 kart.amendment_description amendment_description
2994 FROM OKC_K_ARTICLES_B KART,
2995 OKC_ARTICLE_VERSIONS VERS,
2996 OKC_ARTICLES_ALL ART
2997 WHERE DOCUMENT_TYPE=p_doc_type
2998 AND DOCUMENT_ID =p_doc_id
2999 AND VERS.ARTICLE_VERSION_ID=KART.ARTICLE_VERSION_ID
3000 AND ART.ARTICLE_ID = KART.SAV_SAE_ID
3001 AND ART.STANDARD_YN='Y'
3002 AND nvl(AMENDMENT_OPERATION_CODE,'?')<>G_AMEND_CODE_DELETED
3003 AND nvl(SUMMARY_AMEND_OPERATION_CODE,'?')<>G_AMEND_CODE_DELETED
3004 AND ( ARTICLE_STATUS<>'APPROVED'
3005 OR ( ARTICLE_STATUS='APPROVED' AND
3006 nvl(END_DATE,b_effective_date+1)< b_effective_date
3007 )
3008 );
3009
3010 CURSOR l_check_latest_version_csr IS
3011 SELECT distinct KART.ID ID,
3012 KART.SAV_SAE_ID ARTICLE_ID,
3013 KART.ARTICLE_VERSION_ID ARTICLE_VERSION_ID,
3014 KART.SCN_ID SCN_ID,
3015 KART.AMENDMENT_OPERATION_CODE AMENDMENT_OPERATION_CODE,
3016 KART.AMENDMENT_DESCRIPTION AMENDMENT_DESCRIPTION
3017 FROM OKC_K_ARTICLES_B KART,
3018 OKC_ARTICLE_VERSIONS VERS,
3019 OKC_ARTICLE_VERSIONS VERS1,
3020 OKC_TEMPLATE_USAGES USG,
3021 OKC_TERMS_TEMPLATES_ALL TMPL
3022 WHERE KART.DOCUMENT_TYPE=p_doc_type
3023 AND KART.DOCUMENT_ID =p_doc_id
3024 AND KART.DOCUMENT_TYPE= USG.DOCUMENT_TYPE
3025 AND KART.DOCUMENT_ID = USG.DOCUMENT_ID
3026 AND USG.TEMPLATE_ID = TMPL.TEMPLATE_ID
3027 AND KART.SAV_SAE_ID = VERS.ARTICLE_ID
3028 AND nvl(KART.AMENDMENT_OPERATION_CODE,'?')<>G_AMEND_CODE_DELETED
3029 AND nvl(KART.SUMMARY_AMEND_OPERATION_CODE,'?')<>G_AMEND_CODE_DELETED
3030 AND KART.ARTICLE_VERSION_ID = VERS1.ARTICLE_VERSION_ID
3031 AND VERS.START_DATE > VERS1.START_DATE
3032 AND trunc(NVL(USG.ARTICLE_EFFECTIVE_DATE,SYSDATE)) BETWEEN trunc(VERS.START_DATE) AND NVL(VERS.END_DATE,SYSDATE)
3033 AND VERS.ARTICLE_STATUS = 'APPROVED'
3034 AND (EXISTS
3035 (SELECT 1
3036 FROM OKC_ARTICLE_ADOPTIONS ADP
3037 WHERE ADP.GLOBAL_ARTICLE_VERSION_ID = VERS.ARTICLE_VERSION_ID
3038 AND ADP.ADOPTION_TYPE = 'ADOPTED'
3039 AND ADP.ADOPTION_STATUS = 'APPROVED'
3040 AND ADP.LOCAL_ORG_ID = TMPL.ORG_ID)
3041 OR
3042 NOT EXISTS
3043 (SELECT 1
3044 FROM OKC_ARTICLE_ADOPTIONS ADP
3045 WHERE ADP.GLOBAL_ARTICLE_VERSION_ID = VERS1.ARTICLE_VERSION_ID
3046 AND ADP.ADOPTION_TYPE = 'ADOPTED'
3047 AND ADP.LOCAL_ORG_ID = TMPL.ORG_ID)
3048 ) ;
3049
3050 BEGIN
3051
3052 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3053 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1500: Entered check_art_effectivity');
3054 END IF;
3055
3056 get_qa_code_detail(p_qa_code => G_CHECK_ART_VALIDITY,
3057 x_perform_qa => l_perform_art_val_qa,
3058 x_qa_name => l_art_val_desc,
3059 x_severity_flag => l_art_val_severity,
3060 x_return_status => x_return_status);
3061 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3062 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
3063 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
3064 RAISE FND_API.G_EXC_ERROR ;
3065 END IF;
3066
3067 get_qa_code_detail(p_qa_code => G_CHECK_LATEST_VERSION,
3068 x_perform_qa => l_perform_lat_vers_qa,
3069 x_qa_name => l_lat_vers_desc,
3070 x_severity_flag => l_lat_vers_severity,
3071 x_return_status => x_return_status);
3072 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3073 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
3074 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
3075 RAISE FND_API.G_EXC_ERROR ;
3076 END IF;
3077
3078 -- 11.5.10+: check should be OR not AND
3079 --IF l_perform_art_val_qa='Y' AND l_perform_lat_vers_qa='Y' THEN
3080 IF l_perform_art_val_qa='Y' OR l_perform_lat_vers_qa='Y' THEN
3081
3082
3083 IF p_doc_type=G_TMPL_DOC_TYPE THEN
3084
3085 IF l_perform_art_val_qa='Y' THEN
3086
3087 /*
3088 OPEN l_get_tmpl_dates_csr;
3089 FETCH l_get_tmpl_dates_csr INTO l_start_date,l_end_date;
3090 CLOSE l_get_tmpl_dates_csr;
3091 */
3092
3093 IF (NOT do_validation(l_art_val_severity, p_doc_type)) THEN
3094 -- validation is not required
3095 RETURN;
3096 END IF;
3097
3098 FOR cr in l_check_art_tmpl_csr(l_article_effective_date) LOOP
3099 -- FOR cr in l_check_art_tmpl_csr(l_start_date,l_end_date) LOOP
3100 l_indx := px_qa_result_tbl.COUNT + 1;
3101
3102 px_qa_result_tbl(l_indx).error_record_type := G_ART_QA_TYPE;
3103 px_qa_result_tbl(l_indx).article_id := cr.article_id;
3104 px_qa_result_tbl(l_indx).deliverable_id := Null;
3105 px_qa_result_tbl(l_indx).title := Get_article_title(cr.id);
3106 px_qa_result_tbl(l_indx).section_name := Get_section_title(cr.scn_id);
3107 px_qa_result_tbl(l_indx).qa_code := G_CHECK_ART_VALIDITY;
3108 px_qa_result_tbl(l_indx).message_name := G_OKC_CHECK_TMPL_ART_VALIDITY;
3109 px_qa_result_tbl(l_indx).suggestion := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC,G_OKC_CHECK_TMPL_ART_VALID_S);
3110 px_qa_result_tbl(l_indx).error_severity := l_art_val_severity;
3111 px_qa_result_tbl(l_indx).problem_short_desc := l_art_val_desc;
3112 px_qa_result_tbl(l_indx).problem_details_short := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_CHECK_TMPL_ART_VALID_SH);
3113 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);
3114
3115 END LOOP; -- FOR cr in l_check_art_tmpl_csr
3116
3117 /* expert commented out
3118 -- check expert clauses for validtity
3119 IF (g_expert_enabled = 'Y') THEN
3120 FOR cr IN l_check_xprt_art_tmpl_csr(l_article_effective_date ) LOOP
3121 l_indx := px_qa_result_tbl.COUNT + 1;
3122
3123 px_qa_result_tbl(l_indx).error_record_type := G_EXP_QA_TYPE;
3124 px_qa_result_tbl(l_indx).article_id := cr.xprt_article_id;
3125 px_qa_result_tbl(l_indx).deliverable_id := Null;
3126 px_qa_result_tbl(l_indx).title :=
3127 get_xprt_article_title(cr.xprt_article_id);
3128 px_qa_result_tbl(l_indx).section_name := Null;
3129
3130 px_qa_result_tbl(l_indx).qa_code := G_CHECK_RUL_ART_VAL;
3131 px_qa_result_tbl(l_indx).message_name := G_OKC_CHECK_RUL_ART_VAL;
3132 px_qa_result_tbl(l_indx).suggestion :=
3133 OKC_TERMS_UTIL_PVT.Get_Message(G_OKC,G_OKC_CHECK_RUL_ART_VAL_S);
3134 px_qa_result_tbl(l_indx).error_severity := l_art_val_severity;
3135 px_qa_result_tbl(l_indx).problem_short_desc := l_art_val_desc;
3136 px_qa_result_tbl(l_indx).problem_details_short :=
3137 OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_CHECK_RUL_ART_VAL_SH);
3138 px_qa_result_tbl(l_indx).problem_details :=
3139 OKC_TERMS_UTIL_PVT.Get_Message(G_OKC,G_OKC_CHECK_RUL_ART_VAL,
3140 'RULE', cr.rule_name,
3141 'XPRT_ARTICLE', get_xprt_article_title(cr.xprt_article_id)
3142 );
3143 END LOOP;
3144 END IF; -- of IF (g_expert_enabled = 'Y') THEN
3145 */
3146
3147 END IF; -- of IF l_perform_art_val_qa='Y' THEN
3148
3149 ELSE
3150
3151 -- non TEMPLATE document types
3152 IF l_perform_lat_vers_qa ='Y' THEN
3153 FOR cr IN l_check_latest_version_csr LOOP
3154 IF ( p_qa_mode =G_AMEND_QA
3155 AND cr.amendment_operation_code IS NOT NULL
3156 )
3157 OR p_qa_mode<>G_AMEND_QA THEN
3158
3159 l_indx := px_qa_result_tbl.COUNT + 1;
3160
3161 l_indx1 := l_article_version_tbl.COUNT + 1;
3162 l_article_version_tbl(l_indx1) := cr.article_version_id;
3163
3164 px_qa_result_tbl(l_indx).error_record_type := G_ART_QA_TYPE;
3165 px_qa_result_tbl(l_indx).article_id := cr.article_id;
3166 px_qa_result_tbl(l_indx).deliverable_id := Null;
3167 px_qa_result_tbl(l_indx).title := Get_article_title(cr.id);
3168 px_qa_result_tbl(l_indx).section_name := Get_section_title(cr.scn_id);
3169 px_qa_result_tbl(l_indx).qa_code := G_CHECK_LATEST_VERSION;
3170 px_qa_result_tbl(l_indx).message_name := G_OKC_CHECK_LATEST_VERSION;
3171 px_qa_result_tbl(l_indx).suggestion := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC,G_OKC_CHECK_LATEST_VERSION_S);
3172 px_qa_result_tbl(l_indx).error_severity := l_lat_vers_severity;
3173 px_qa_result_tbl(l_indx).problem_short_desc := l_lat_vers_desc;
3174 px_qa_result_tbl(l_indx).problem_details_short := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_CHECK_LATEST_VERSION_SH);
3175 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);
3176
3177 END IF; -- IF ( p_qa_mode =G_AMEND_QA
3178 END LOOP; -- FOR cr IN l_check_latest_version_csr LOOP
3179 END IF; -- IF l_perform_lat_vers_qa ='Y' THEN
3180
3181 IF l_perform_art_val_qa='Y' THEN
3182 FOR cr in l_check_art_doc_csr(l_article_effective_date) LOOP
3183
3184 IF ( p_qa_mode =G_AMEND_QA
3185 AND cr.amendment_operation_code IS NOT NULL
3186 )
3187 OR p_qa_mode<>G_AMEND_QA THEN
3188
3189 l_found := FALSE;
3190
3191 IF l_article_version_tbl.COUNT > 0 THEN
3192 FOR k in l_article_version_tbl.FIRST..l_article_version_tbl.LAST LOOP
3193 IF l_article_version_tbl(k) = cr.article_version_id THEN
3194 l_found := TRUE;
3195 END IF;
3196 END LOOP; -- FOR k in l_article_version_tbl.FI
3197 END IF; -- IF l_article_version_tbl.COUNT > 0 THEN
3198
3199 IF NOT l_found THEN
3200 l_indx := px_qa_result_tbl.COUNT + 1;
3201
3202 px_qa_result_tbl(l_indx).error_record_type := G_ART_QA_TYPE;
3203 px_qa_result_tbl(l_indx).article_id := cr.article_id;
3204 px_qa_result_tbl(l_indx).deliverable_id := Null;
3205 px_qa_result_tbl(l_indx).title := Get_article_title(cr.id);
3206 px_qa_result_tbl(l_indx).section_name := Get_section_title(cr.scn_id);
3207
3208 px_qa_result_tbl(l_indx).qa_code := G_CHECK_ART_VALIDITY;
3209 px_qa_result_tbl(l_indx).message_name := G_OKC_CHECK_ART_VALIDITY;
3210 px_qa_result_tbl(l_indx).suggestion := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC,G_OKC_CHECK_ART_VALIDITY_S);
3211 px_qa_result_tbl(l_indx).error_severity := l_art_val_severity;
3212 px_qa_result_tbl(l_indx).problem_short_desc := l_art_val_desc;
3213 px_qa_result_tbl(l_indx).problem_details_short := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_CHECK_ART_VALIDITY_SH);
3214 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);
3215 END IF; -- IF NOT l_found THEN
3216
3217 END IF; -- IF ( p_qa_mode =G_AMEND_QA
3218
3219 END LOOP; -- FOR cr in l_check_art_doc_csr(l_article_effect
3220
3221 END IF; -- IF l_perform_art_val_qa='Y' THEN
3222
3223 END IF; -- IF p_doc_type=G_TMPL_DOC_TYPE ELSE branch
3224
3225 END IF; -- IF l_perform_art_val_qa='Y' OR l_perform_lat_vers_qa='Y'
3226
3227
3228 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3229 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1600: Leaving check_art_effectivity');
3230 END IF;
3231
3232 EXCEPTION
3233 WHEN FND_API.G_EXC_ERROR THEN
3234 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3235 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7800: Leaving check_art_effectivity : OKC_API.G_EXCEPTION_ERROR Exception');
3236 END IF;
3237
3238 IF l_get_tmpl_dates_csr%ISOPEN THEN
3239 CLOSE l_get_tmpl_dates_csr;
3240 END IF;
3241 IF l_check_art_tmpl_csr%ISOPEN THEN
3242 CLOSE l_check_art_tmpl_csr;
3243 END IF;
3244 IF l_check_art_doc_csr%ISOPEN THEN
3245 CLOSE l_check_art_doc_csr;
3246 END IF;
3247 IF l_check_latest_version_csr%ISOPEN THEN
3248 CLOSE l_check_latest_version_csr;
3249 END IF;
3250
3251 /* expert commented out
3252 IF l_check_xprt_art_tmpl_csr%ISOPEN THEN
3253 CLOSE l_check_xprt_art_tmpl_csr;
3254 END IF;
3255 */
3256
3257 x_return_status := G_RET_STS_ERROR ;
3258
3259 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3260 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3261 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7900: Leaving check_art_effectivity : OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
3262 END IF;
3263
3264 IF l_get_tmpl_dates_csr%ISOPEN THEN
3265 CLOSE l_get_tmpl_dates_csr;
3266 END IF;
3267 IF l_check_art_tmpl_csr%ISOPEN THEN
3268 CLOSE l_check_art_tmpl_csr;
3269 END IF;
3270 IF l_check_art_doc_csr%ISOPEN THEN
3271 CLOSE l_check_art_doc_csr;
3272 END IF;
3273 IF l_check_latest_version_csr%ISOPEN THEN
3274 CLOSE l_check_latest_version_csr;
3275 END IF;
3276
3277 /* expert commented out
3278 IF l_check_xprt_art_tmpl_csr%ISOPEN THEN
3279 CLOSE l_check_xprt_art_tmpl_csr;
3280 END IF;
3281 */
3282
3283 x_return_status := G_RET_STS_UNEXP_ERROR ;
3284
3285 WHEN OTHERS THEN
3286 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3287 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'8000: Leaving check_art_effectivity because of EXCEPTION: '||sqlerrm);
3288 END IF;
3289
3290 IF l_get_tmpl_dates_csr%ISOPEN THEN
3291 CLOSE l_get_tmpl_dates_csr;
3292 END IF;
3293 IF l_check_art_tmpl_csr%ISOPEN THEN
3294 CLOSE l_check_art_tmpl_csr;
3295 END IF;
3296 IF l_check_art_doc_csr%ISOPEN THEN
3297 CLOSE l_check_art_doc_csr;
3298 END IF;
3299 IF l_check_latest_version_csr%ISOPEN THEN
3300 CLOSE l_check_latest_version_csr;
3301 END IF;
3302
3303 /* expert commented out
3304 IF l_check_xprt_art_tmpl_csr%ISOPEN THEN
3305 CLOSE l_check_xprt_art_tmpl_csr;
3306 END IF;
3307 */
3308
3309 x_return_status := G_RET_STS_UNEXP_ERROR ;
3310 END check_art_effectivity;
3311
3312 -------------------------------------------
3313 -- PROCEDURE check_layout_template
3314 -------------------------------------------
3315 /* check_layout_template */
3316
3317 PROCEDURE check_layout_template (
3318 p_doc_type IN VARCHAR2,
3319 p_doc_id IN NUMBER,
3320 px_qa_result_tbl IN OUT NOCOPY qa_result_tbl_type,
3321 x_return_status OUT NOCOPY VARCHAR2)
3322 IS
3323
3324 l_api_name CONSTANT VARCHAR2(30) := 'G_CHECK_LAYOUT_TEMPLATE';
3325 l_indx NUMBER;
3326 l_severity OKC_QA_ERRORS_T.ERROR_SEVERITY%TYPE;
3327 l_desc OKC_QA_ERRORS_T.PROBLEM_SHORT_DESC%TYPE;
3328 l_perform_layout_tmpl_qa VARCHAR2(1);
3329 l_template_name OKC_TERMS_TEMPLATES_ALL.TEMPLATE_NAME%TYPE;
3330 l_print_template_id OKC_TERMS_TEMPLATES_ALL.PRINT_TEMPLATE_ID%TYPE;
3331
3332 CURSOR l_get_layout_tmpl_csr IS
3333 SELECT template_name, print_template_id
3334 FROM okc_terms_templates_all
3335 WHERE template_id = p_doc_id ;
3336
3337 BEGIN
3338
3339 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3340 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1500: Entered check_layout_template');
3341 END IF;
3342 get_qa_code_detail(p_qa_code => G_CHECK_LAYOUT_TMPL,
3343 x_perform_qa => l_perform_layout_tmpl_qa,
3344 x_qa_name => l_desc,
3345 x_severity_flag => l_severity,
3346 x_return_status => x_return_status);
3347 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3348 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
3349 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
3350 RAISE FND_API.G_EXC_ERROR ;
3351 END IF;
3352
3353
3354 IF l_perform_layout_tmpl_qa='Y' THEN
3355
3356 IF NOT do_validation(l_severity, p_doc_type) THEN
3357 -- validation is not required
3358 RETURN;
3359 END IF;
3360
3361 OPEN l_get_layout_tmpl_csr;
3362 FETCH l_get_layout_tmpl_csr INTO l_template_name, l_print_template_id;
3363 CLOSE l_get_layout_tmpl_csr;
3364
3365 IF l_print_template_id IS NULL THEN
3366
3367 l_indx := px_qa_result_tbl.COUNT + 1;
3368
3369 px_qa_result_tbl(l_indx).error_record_type := G_TMPL_QA_TYPE;
3370 px_qa_result_tbl(l_indx).article_id := Null;
3371 px_qa_result_tbl(l_indx).deliverable_id := Null;
3372 px_qa_result_tbl(l_indx).title := l_template_name;
3373 px_qa_result_tbl(l_indx).section_name := Null;
3374 px_qa_result_tbl(l_indx).qa_code := G_CHECK_LAYOUT_TMPL;
3375 px_qa_result_tbl(l_indx).message_name := G_OKC_CHECK_LAYOUT_TMPL;
3376 px_qa_result_tbl(l_indx).suggestion := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC,G_OKC_CHECK_LAYOUT_TMPL_S);
3377 px_qa_result_tbl(l_indx).error_severity := l_severity;
3378 px_qa_result_tbl(l_indx).problem_short_desc := l_desc;
3379 px_qa_result_tbl(l_indx).problem_details_short := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_CHECK_LAYOUT_TMPL);
3380 px_qa_result_tbl(l_indx).problem_details := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_CHECK_LAYOUT_TMPL);
3381
3382 END IF;
3383
3384 END IF; -- IF l_perform_layout_tmpl_qa='Y' THEN
3385
3386 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3387 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1600: Leaving check_layout_template');
3388 END IF;
3389
3390 EXCEPTION
3391 WHEN FND_API.G_EXC_ERROR THEN
3392 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3393 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7800: Leaving check_layout_template : OKC_API.G_EXCEPTION_ERROR Exception');
3394 END IF;
3395
3396 IF l_get_layout_tmpl_csr%ISOPEN THEN
3397 CLOSE l_get_layout_tmpl_csr;
3398 END IF;
3399
3400 x_return_status := G_RET_STS_ERROR ;
3401
3402 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3403 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3404 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7900: Leaving check_layout_template : OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
3405 END IF;
3406
3407 IF l_get_layout_tmpl_csr%ISOPEN THEN
3408 CLOSE l_get_layout_tmpl_csr;
3409 END IF;
3410
3411 x_return_status := G_RET_STS_UNEXP_ERROR ;
3412
3413 WHEN OTHERS THEN
3414 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3415 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'8000: Leaving check_layout_template because of EXCEPTION: '||sqlerrm);
3416 END IF;
3417
3418 IF l_get_layout_tmpl_csr%ISOPEN THEN
3419 CLOSE l_get_layout_tmpl_csr;
3420 END IF;
3421
3422 x_return_status := G_RET_STS_UNEXP_ERROR ;
3423
3424 END check_layout_template;
3425
3426
3427 --MLS for templates
3428 -------------------------------------------
3429 -- PROCEDURE Check_translated_tmpl_revision
3430 -------------------------------------------
3431 /* Check_translated_tmpl_revision */
3432
3433 PROCEDURE Check_translated_tmpl_rev (
3434 p_doc_type IN VARCHAR2,
3435 p_doc_id IN NUMBER,
3436 px_qa_result_tbl IN OUT NOCOPY qa_result_tbl_type,
3437 x_return_status OUT NOCOPY VARCHAR2)
3438 IS
3439
3440 l_api_name CONSTANT VARCHAR2(40) := 'G_CHECK_TRANSLATED_TMPL_REVISION';
3441 l_indx NUMBER;
3442 l_severity OKC_QA_ERRORS_T.ERROR_SEVERITY%TYPE;
3443 l_desc OKC_QA_ERRORS_T.PROBLEM_SHORT_DESC%TYPE;
3444 l_perform_trans_rev_qa VARCHAR2(1);
3445 l_template_name OKC_TERMS_TEMPLATES_ALL.TEMPLATE_NAME%TYPE;
3446 l_trans_tmpl_name OKC_TERMS_TEMPLATES_ALL.TEMPLATE_NAME%TYPE;
3447 l_template_id OKC_TERMS_TEMPLATES_ALL.TEMPLATE_ID%TYPE;
3448
3449 CURSOR l_translated_tmpl_revision_csr IS
3450 SELECT parent.template_id, parent.template_name, trans.template_name
3451 FROM okc_terms_templates_all parent, okc_terms_templates_all trans
3452 WHERE parent.template_id = trans.translated_from_tmpl_id
3453 AND trans.template_id = p_doc_id
3454 AND exists (SELECT 1
3455 FROM okc_terms_templates_all revision
3456 WHERE parent.template_id = revision.parent_template_id);
3457
3458
3459 BEGIN
3460
3461 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3462 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1500: Entered Check_translated_tmpl_revision');
3463 END IF;
3464 get_qa_code_detail(p_qa_code => G_CHECK_TRANS_TMPL_REVISION,
3465 x_perform_qa => l_perform_trans_rev_qa,
3466 x_qa_name => l_desc,
3467 x_severity_flag => l_severity,
3468 x_return_status => x_return_status);
3469 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3470 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
3471 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
3472 RAISE FND_API.G_EXC_ERROR ;
3473 END IF;
3474
3475
3476 IF l_perform_trans_rev_qa ='Y' THEN
3477
3478 IF NOT do_validation(l_severity, p_doc_type) THEN
3479 -- validation is not required
3480 RETURN;
3481 END IF;
3482
3483 OPEN l_translated_tmpl_revision_csr;
3484 FETCH l_translated_tmpl_revision_csr INTO l_template_id, l_template_name, l_trans_tmpl_name;
3485 CLOSE l_translated_tmpl_revision_csr;
3486
3487 IF l_template_id IS NOT NULL THEN
3488
3489 l_indx := px_qa_result_tbl.COUNT + 1;
3490
3491 px_qa_result_tbl(l_indx).error_record_type := G_TMPL_QA_TYPE;
3492 px_qa_result_tbl(l_indx).article_id := Null;
3493 px_qa_result_tbl(l_indx).deliverable_id := Null;
3494 px_qa_result_tbl(l_indx).title := l_trans_tmpl_name;
3495 px_qa_result_tbl(l_indx).section_name := Null;
3496 px_qa_result_tbl(l_indx).qa_code := G_CHECK_TRANS_TMPL_REVISION;
3497 px_qa_result_tbl(l_indx).message_name := G_OKC_CHECK_TRANS_TMPL_REV;
3498 px_qa_result_tbl(l_indx).suggestion := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC,G_OKC_CHECK_TRANS_TMPL_REV_S);
3499 px_qa_result_tbl(l_indx).error_severity := l_severity;
3500 px_qa_result_tbl(l_indx).problem_short_desc := l_desc;
3501 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);
3502 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);
3503
3504 END IF;
3505
3506 END IF;
3507
3508 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3509 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1600: Leaving Check_translated_tmpl_revision');
3510 END IF;
3511
3512 EXCEPTION
3513 WHEN FND_API.G_EXC_ERROR THEN
3514 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3515 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7800: Leaving Check_translated_tmpl_revision : OKC_API.G_EXCEPTION_ERROR Exception');
3516 END IF;
3517
3518 IF l_translated_tmpl_revision_csr%ISOPEN THEN
3519 CLOSE l_translated_tmpl_revision_csr;
3520 END IF;
3521
3522 x_return_status := G_RET_STS_ERROR ;
3523
3524 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3525 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3526 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');
3527 END IF;
3528
3529 IF l_translated_tmpl_revision_csr%ISOPEN THEN
3530 CLOSE l_translated_tmpl_revision_csr;
3531 END IF;
3532
3533 x_return_status := G_RET_STS_UNEXP_ERROR ;
3534
3535 WHEN OTHERS THEN
3536 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3537 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'8000: Leaving Check_translated_tmpl_revision because of EXCEPTION: '||sqlerrm);
3538 END IF;
3539
3540 IF l_translated_tmpl_revision_csr%ISOPEN THEN
3541 CLOSE l_translated_tmpl_revision_csr;
3542 END IF;
3543
3544 x_return_status := G_RET_STS_UNEXP_ERROR ;
3545
3546 END Check_translated_tmpl_rev;
3547
3548
3549 --MLS for templates
3550
3551 -------------------------------------------
3552 -- PROCEDURE Check_translated_tmpl_effectivity
3553 -------------------------------------------
3554 /* Check_translated_tmpl_effectivity */
3555
3556 PROCEDURE Check_translated_tmpl_eff (
3557 p_doc_type IN VARCHAR2,
3558 p_doc_id IN NUMBER,
3559 px_qa_result_tbl IN OUT NOCOPY qa_result_tbl_type,
3560 x_return_status OUT NOCOPY VARCHAR2)
3561 IS
3562
3563 l_api_name CONSTANT VARCHAR2(40) := 'G_CHECK_TRANSLATED_TMPL_EFFECTIVITY';
3564 l_indx NUMBER;
3565 l_severity OKC_QA_ERRORS_T.ERROR_SEVERITY%TYPE;
3566 l_desc OKC_QA_ERRORS_T.PROBLEM_SHORT_DESC%TYPE;
3567 l_perform_trans_tmpl_eff_qa VARCHAR2(1);
3568 l_template_name OKC_TERMS_TEMPLATES_ALL.TEMPLATE_NAME%TYPE;
3569 l_trans_tmpl_name OKC_TERMS_TEMPLATES_ALL.TEMPLATE_NAME%TYPE;
3570 l_template_id OKC_TERMS_TEMPLATES_ALL.TEMPLATE_ID%TYPE;
3571
3572 CURSOR l_get_trans_tmpl_csr IS
3573 SELECT parent.template_id, parent.template_name, trans.template_name
3574 FROM okc_terms_templates_all parent, okc_terms_templates_all trans
3575 WHERE ( trunc(sysdate) >= nvl(trunc(parent.end_date),sysdate+1)
3576 OR parent.status_code = 'ON_HOLD' )
3577 AND parent.template_id = trans.translated_from_tmpl_id
3578 AND trans.template_id = p_doc_id;
3579
3580
3581 BEGIN
3582
3583 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3584 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1500: Entered Check_translated_tmpl_effectivity');
3585 END IF;
3586 get_qa_code_detail(p_qa_code => G_CHECK_TRANS_TMPL_EFF,
3587 x_perform_qa => l_perform_trans_tmpl_eff_qa,
3588 x_qa_name => l_desc,
3589 x_severity_flag => l_severity,
3590 x_return_status => x_return_status);
3591 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3592 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
3593 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
3594 RAISE FND_API.G_EXC_ERROR ;
3595 END IF;
3596
3597
3598 IF l_perform_trans_tmpl_eff_qa ='Y' THEN
3599
3600 IF NOT do_validation(l_severity, p_doc_type) THEN
3601 -- validation is not required
3602 RETURN;
3603 END IF;
3604
3605 OPEN l_get_trans_tmpl_csr;
3606 FETCH l_get_trans_tmpl_csr INTO l_template_id, l_template_name, l_trans_tmpl_name;
3607 CLOSE l_get_trans_tmpl_csr;
3608
3609 IF l_template_id IS NOT NULL THEN
3610
3611 l_indx := px_qa_result_tbl.COUNT + 1;
3612
3613 px_qa_result_tbl(l_indx).error_record_type := G_TMPL_QA_TYPE;
3614 px_qa_result_tbl(l_indx).article_id := Null;
3615 px_qa_result_tbl(l_indx).deliverable_id := Null;
3616 px_qa_result_tbl(l_indx).title := l_trans_tmpl_name;
3617 px_qa_result_tbl(l_indx).section_name := Null;
3618 px_qa_result_tbl(l_indx).qa_code := G_CHECK_TRANS_TMPL_EFF;
3619 px_qa_result_tbl(l_indx).message_name := G_OKC_CHECK_TRANS_TMPL_EFF ;
3620 px_qa_result_tbl(l_indx).suggestion := OKC_TERMS_UTIL_PVT.Get_Message(G_OKC,G_OKC_CHECK_TRANS_TMPL_EFF_S);
3621 px_qa_result_tbl(l_indx).error_severity := l_severity;
3622 px_qa_result_tbl(l_indx).problem_short_desc := l_desc;
3623 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);
3624 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);
3625
3626 END IF;
3627
3628 END IF;
3629
3630 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3631 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1600: Leaving Check_translated_tmpl_effectivity');
3632 END IF;
3633
3634 EXCEPTION
3635 WHEN FND_API.G_EXC_ERROR THEN
3636 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3637 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7800: Leaving Check_translated_tmpl_effectivity : OKC_API.G_EXCEPTION_ERROR Exception');
3638 END IF;
3639
3640 IF l_get_trans_tmpl_csr%ISOPEN THEN
3641 CLOSE l_get_trans_tmpl_csr;
3642 END IF;
3643
3644 x_return_status := G_RET_STS_ERROR ;
3645
3646 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3647 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3648 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');
3649 END IF;
3650
3651 IF l_get_trans_tmpl_csr%ISOPEN THEN
3652 CLOSE l_get_trans_tmpl_csr;
3653 END IF;
3654
3655 x_return_status := G_RET_STS_UNEXP_ERROR ;
3656
3657 WHEN OTHERS THEN
3658 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3659 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'8000: Leaving Check_translated_tmpl_effectivity because of EXCEPTION: '||sqlerrm);
3660 END IF;
3661
3662 IF l_get_trans_tmpl_csr%ISOPEN THEN
3663 CLOSE l_get_trans_tmpl_csr;
3664 END IF;
3665
3666 x_return_status := G_RET_STS_UNEXP_ERROR ;
3667
3668 END Check_translated_tmpl_eff;
3669
3670 --MLS for templates
3671
3672
3673 -------------------------------------------
3674 -- PROCEDURE check_articles_exist
3675 -------------------------------------------
3676 /* check_articles_exist */
3677
3678 PROCEDURE check_articles_exist (
3679 p_doc_type IN VARCHAR2,
3680 p_doc_id IN NUMBER,
3681 px_qa_result_tbl IN OUT NOCOPY qa_result_tbl_type,
3682 x_return_status OUT NOCOPY VARCHAR2)
3683 IS
3684
3685 l_api_name CONSTANT VARCHAR2(30) := 'check_articles_exist';
3686 l_indx NUMBER;
3687 l_severity OKC_QA_ERRORS_T.ERROR_SEVERITY%TYPE;
3688 l_desc OKC_QA_ERRORS_T.PROBLEM_SHORT_DESC%TYPE;
3689 l_perform_articles_exist_qa VARCHAR2(1);
3690
3691 BEGIN
3692
3693 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3694 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1500: Entered check_articles_exist');
3695 END IF;
3696
3697 get_qa_code_detail(p_qa_code => G_CHECK_ART_EXT,
3698 x_perform_qa => l_perform_articles_exist_qa,
3699 x_qa_name => l_desc,
3700 x_severity_flag => l_severity,
3701 x_return_status => x_return_status);
3702 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3703 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
3704 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
3705 RAISE FND_API.G_EXC_ERROR ;
3706 END IF;
3707
3708
3709 IF (l_perform_articles_exist_qa <> 'Y') THEN
3710 RETURN;
3711 END IF;
3712
3713 IF NOT do_validation(l_severity, p_doc_type) then
3714 -- validation is not required
3715 RETURN;
3716 END IF;
3717
3718 IF (p_doc_type = 'TEMPLATE' AND l_article_tbl.COUNT = 0) then
3719
3720 -- l_article_tbl is a global variable containing the template articles.
3721 -- indicates no clauses are present.
3722 l_indx := px_qa_result_tbl.COUNT + 1;
3723
3724 px_qa_result_tbl(l_indx).error_record_type := G_TMPL_QA_TYPE;
3725 px_qa_result_tbl(l_indx).article_id := Null;
3726 px_qa_result_tbl(l_indx).deliverable_id := Null;
3727 px_qa_result_tbl(l_indx).title := g_template_name;
3728 px_qa_result_tbl(l_indx).section_name := Null;
3729 px_qa_result_tbl(l_indx).qa_code := G_CHECK_ART_EXT;
3730 px_qa_result_tbl(l_indx).message_name := G_OKC_CHECK_ART_EXT;
3731 px_qa_result_tbl(l_indx).suggestion :=
3732 OKC_TERMS_UTIL_PVT.get_message(G_OKC,G_OKC_CHECK_ART_EXT_S);
3733 px_qa_result_tbl(l_indx).error_severity := l_severity;
3734 px_qa_result_tbl(l_indx).problem_short_desc := l_desc;
3735 px_qa_result_tbl(l_indx).problem_details_short :=
3736 OKC_TERMS_UTIL_PVT.get_message(G_OKC, G_OKC_CHECK_ART_EXT_SH);
3737 px_qa_result_tbl(l_indx).problem_details :=
3738 OKC_TERMS_UTIL_PVT.get_message(G_OKC, G_OKC_CHECK_ART_EXT);
3739 END IF;
3740
3741
3742 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3743 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1600: Leaving check_articles_exist');
3744 END IF;
3745
3746 EXCEPTION
3747 WHEN FND_API.G_EXC_ERROR THEN
3748 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3749 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7800: Leaving check_articles_exist : OKC_API.G_EXCEPTION_ERROR Exception');
3750 END IF;
3751
3752 x_return_status := G_RET_STS_ERROR ;
3753
3754 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3755 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3756 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7900: Leaving check_articles_exist : OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
3757 END IF;
3758
3759 x_return_status := G_RET_STS_UNEXP_ERROR ;
3760
3761 WHEN OTHERS THEN
3762 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3763 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'8000: Leaving check_articles_exist because of EXCEPTION: '||sqlerrm);
3764 END IF;
3765
3766 x_return_status := G_RET_STS_UNEXP_ERROR ;
3767
3768 END check_articles_exist;
3769
3770 -------------------------------------------
3771 -- PROCEDURE check_validate_draft_articles
3772 -------------------------------------------
3773 /* check_validate_draft_articles gets all the draft versions
3774 in a template and validates them for errors.
3775 */
3776 PROCEDURE check_validate_draft_articles (
3777 p_doc_type IN VARCHAR2,
3778 p_doc_id IN NUMBER,
3779 px_qa_result_tbl IN OUT NOCOPY qa_result_tbl_type,
3780 x_return_status OUT NOCOPY VARCHAR2)
3781 IS
3782
3783 /*
3784 TYPE draft_art_rec_type IS RECORD (
3785 article_id OKC_ARTICLES_ALL.ARTICLE_ID%TYPE,
3786 article_version_id OKC_ARTICLE_VERSIONS.ARTICLE_VERSION_ID%TYPE,
3787 title OKC_QA_ERRORS_T.title%TYPE,
3788 section OKC_TMPL_DRAFT_CLAUSES.SECTION_NAME%TYPE
3789 );
3790
3791 TYPE draft_art_tbl_type IS TABLE OF draft_art_rec_type INDEX BY BINARY_INTEGER;
3792 */
3793
3794 TYPE art_id_tbl_type IS TABLE of OKC_TMPL_DRAFT_CLAUSES.ARTICLE_ID%TYPE INDEX BY BINARY_INTEGER;
3795 TYPE ver_id_tbl_type IS TABLE of OKC_TMPL_DRAFT_CLAUSES.ARTICLE_VERSION_ID%TYPE INDEX BY BINARY_INTEGER;
3796 TYPE title_tbl_type IS TABLE of OKC_ARTICLE_VERSIONS.DISPLAY_NAME%TYPE INDEX BY BINARY_INTEGER;
3797 TYPE sec_tbl_type IS TABLE of OKC_TMPL_DRAFT_CLAUSES.SECTION_NAME%TYPE INDEX BY BINARY_INTEGER;
3798
3799 l_api_name CONSTANT VARCHAR2(30) := 'check_validate_draft_articles';
3800 l_indx NUMBER;
3801
3802 --l_draft_art_tbl draft_art_tbl_type;
3803
3804 l_draft_art_id_tbl art_id_tbl_type;
3805 l_draft_ver_id_tbl ver_id_tbl_type;
3806 l_draft_title_tbl title_tbl_type;
3807 l_draft_sec_tbl sec_tbl_type;
3808
3809 l_art_ver_tbl OKC_ART_BLK_PVT.NUM_TBL_TYPE;
3810 l_validation_results OKC_ART_BLK_PVT.VALIDATION_TBL_TYPE;
3811 l_msg_count NUMBER;
3812 l_msg_data VARCHAR2(2000);
3813 l_qa_return_status VARCHAR2(1);
3814
3815 l_perform_qa_art_typ VARCHAR2(1);
3816 l_severity_art_typ OKC_QA_ERRORS_T.ERROR_SEVERITY%TYPE;
3817 l_desc_art_typ OKC_QA_ERRORS_T.PROBLEM_SHORT_DESC%TYPE;
3818
3819 l_perform_qa_def_sec VARCHAR2(1);
3820 l_severity_def_sec OKC_QA_ERRORS_T.ERROR_SEVERITY%TYPE;
3821 l_desc_def_sec OKC_QA_ERRORS_T.PROBLEM_SHORT_DESC%TYPE;
3822
3823 l_perform_qa_inv_var VARCHAR2(1);
3824 l_severity_inv_var OKC_QA_ERRORS_T.ERROR_SEVERITY%TYPE;
3825 l_desc_inv_var OKC_QA_ERRORS_T.PROBLEM_SHORT_DESC%TYPE;
3826
3827 l_perform_qa_inv_val VARCHAR2(1);
3828 l_severity_inv_val OKC_QA_ERRORS_T.ERROR_SEVERITY%TYPE;
3829 l_desc_inv_val OKC_QA_ERRORS_T.PROBLEM_SHORT_DESC%TYPE;
3830
3831 l_title OKC_QA_ERRORS_T.TITLE%TYPE;
3832 l_section OKC_QA_ERRORS_T.SECTION_NAME%TYPE;
3833
3834 CURSOR l_draft_art_csr IS
3835 SELECT DRA.article_id, DRA.article_version_id,
3836 --NVL(VER.display_name, ART.article_title) title, DRA.section_name section
3837 NVL(VER.display_name, ART.article_title) title,NVL(DRA.section_name, '*') section
3838 FROM OKC_TMPL_DRAFT_CLAUSES DRA,
3839 OKC_ARTICLES_ALL ART, OKC_ARTICLE_VERSIONS VER
3840 WHERE DRA.template_id = p_doc_id and
3841 DRA.selected_yn = 'Y' and
3842 DRA.article_id = ART.article_id and
3843 VER.article_version_id = DRA.article_version_id
3844 -- additional check to ensure that we are not checking some
3845 -- orphaned records in the OKC_TMPL_DRAFT_CLAUSES table
3846 AND EXISTS (SELECT '1' FROM OKC_K_ARTICLES_B KART WHERE
3847 KART.document_type = p_doc_type AND
3848 KART.document_id = p_doc_id AND
3849 KART.sav_sae_id = DRA.article_id);
3850
3851 PROCEDURE get_art_title_sec(
3852 p_art_id IN NUMBER,
3853 x_title OUT NOCOPY VARCHAR2,
3854 x_section OUT NOCOPY VARCHAR2)
3855 IS
3856 BEGIN
3857 x_title := NULL;
3858 x_section := NULL;
3859
3860 /*
3861 IF l_draft_art_tbl.COUNT > 0 THEN
3862 FOR i IN l_draft_art_tbl.FIRST..l_draft_art_tbl.LAST LOOP
3863 IF (l_draft_art_tbl(i).article_id = p_art_id) THEN
3864 x_title := l_draft_art_tbl(i).title;
3865 x_section := l_draft_art_tbl(i).section;
3866 EXIT;
3867 END IF;
3868 END LOOP;
3869 END IF;
3870
3871 */
3872
3873 IF l_draft_art_id_tbl.COUNT > 0 THEN
3874 FOR i IN l_draft_art_id_tbl.FIRST..l_draft_art_id_tbl.LAST LOOP
3875 IF (l_draft_art_id_tbl(i) = p_art_id) THEN
3876 x_title := l_draft_title_tbl(i);
3877 x_section := l_draft_sec_tbl(i);
3878 EXIT;
3879 END IF;
3880 END LOOP;
3881 END IF;
3882
3883 END get_art_title_sec;
3884
3885 BEGIN
3886
3887 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3888 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1500: Entered check_validate_draft_articles');
3889 END IF;
3890
3891 IF (p_doc_type <> 'TEMPLATE') THEN
3892 RETURN; -- no other doc type is supported.
3893 END IF;
3894
3895 -- Fix for the bug# 4448520, validate the template with Revision status
3896 IF (g_status_code NOT in ('DRAFT', 'REJECTED', 'REVISION')) THEN
3897 RETURN; -- no article validation for other statuses.
3898 END IF;
3899 --
3900 -- muteshev
3901 -- Insert records in OKC_TMPL_DRAFT_CLAUSES before validation
3902 -- bug 4159533 start
3903 declare
3904 l_drafts_present varchar2(1);
3905 x_return_status varchar2(150);
3906 x_msg_data varchar2(2000);
3907 x_msg_count number;
3908 begin
3909 OKC_TERMS_UTIL_PVT.create_tmpl_clauses_to_submit (
3910 p_api_version => 1,
3911 p_init_msg_list => FND_API.G_FALSE,
3912 p_template_id => p_doc_id,
3913 p_org_id => g_org_id,
3914 x_drafts_present => l_drafts_present,
3915 x_return_status => x_return_status,
3916 x_msg_count => x_msg_count,
3917 x_msg_data => x_msg_data);
3918 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3919 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3920 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
3921 RAISE FND_API.G_EXC_ERROR;
3922 END IF;
3923 end;
3924 -- bug 4159533 end
3925 -- the above code simulates template submittion else the below
3926 -- l_draft_art_csr will never get any rows unless user submits template himself
3927 -- (template submittion populates the OKC_TMPL_DRAFT_CLAUSES table)
3928 --
3929 OPEN l_draft_art_csr;
3930 FETCH l_draft_art_csr BULK COLLECT INTO l_draft_art_id_tbl, l_draft_ver_id_tbl,
3931 l_draft_title_tbl, l_draft_sec_tbl;
3932 CLOSE l_draft_art_csr;
3933
3934 --IF NOT (l_draft_art_tbl.COUNT > 0) THEN
3935 IF NOT (l_draft_art_id_tbl.COUNT > 0) THEN
3936 RETURN; -- no articles to validate
3937 END IF;
3938
3939 get_qa_code_detail(p_qa_code => G_CHECK_ART_TYP,
3940 x_perform_qa => l_perform_qa_art_typ,
3941 x_qa_name => l_desc_art_typ,
3942 x_severity_flag => l_severity_art_typ,
3943 x_return_status => x_return_status);
3944 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3945 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
3946 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
3947 RAISE FND_API.G_EXC_ERROR ;
3948 END IF;
3949
3950 get_qa_code_detail(p_qa_code => G_CHECK_ART_DEF_SEC,
3951 x_perform_qa => l_perform_qa_def_sec,
3952 x_qa_name => l_desc_def_sec,
3953 x_severity_flag => l_severity_def_sec,
3954 x_return_status => x_return_status);
3955 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3956 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
3957 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
3958 RAISE FND_API.G_EXC_ERROR ;
3959 END IF;
3960
3961 get_qa_code_detail(p_qa_code => G_CHECK_ART_INV_VAR,
3962 x_perform_qa => l_perform_qa_inv_var,
3963 x_qa_name => l_desc_inv_var,
3964 x_severity_flag => l_severity_inv_var,
3965 x_return_status => x_return_status);
3966 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3967 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
3968 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
3969 RAISE FND_API.G_EXC_ERROR ;
3970 END IF;
3971
3972 get_qa_code_detail(p_qa_code => G_CHECK_ART_INV_VAL,
3973 x_perform_qa => l_perform_qa_inv_val,
3974 x_qa_name => l_desc_inv_val,
3975 x_severity_flag => l_severity_inv_val,
3976 x_return_status => x_return_status);
3977 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3978 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
3979 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
3980 RAISE FND_API.G_EXC_ERROR ;
3981 END IF;
3982
3983
3984 l_art_ver_tbl := OKC_ART_BLK_PVT.NUM_TBL_TYPE();
3985 --FOR i in 1..l_draft_art_tbl.COUNT LOOP
3986 FOR i in 1..l_draft_art_id_tbl.COUNT LOOP
3987 l_art_ver_tbl.EXTEND;
3988 --l_art_ver_tbl(i) := l_draft_art_tbl(i).article_version_id;
3989 l_art_ver_tbl(i) := l_draft_ver_id_tbl(i);
3990 END LOOP;
3991
3992 -- Call the bulk validate api which does the actual validation,
3993 -- if the appropriate flags are set.
3994 IF ((l_perform_qa_art_typ ='Y' AND do_validation(l_severity_art_typ, p_doc_type)) OR
3995 (l_perform_qa_def_sec ='Y' AND do_validation(l_severity_def_sec, p_doc_type)) OR
3996 (l_perform_qa_inv_var ='Y' AND do_validation(l_severity_inv_var, p_doc_type)) OR
3997 (l_perform_qa_inv_val ='Y' AND do_validation(l_severity_inv_val, p_doc_type))
3998 ) THEN
3999
4000 OKC_ART_BLK_PVT.validate_article_versions_blk(
4001 p_api_version => 1.0 ,
4002 p_init_msg_list => FND_API.G_FALSE,
4003 p_commit => FND_API.G_FALSE,
4004 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
4005 x_return_status => x_return_status,
4006 x_msg_count => l_msg_count,
4007 x_msg_data => l_msg_data,
4008 p_org_id => g_org_id,
4009 p_art_ver_tbl => l_art_ver_tbl,
4010 x_qa_return_status => l_qa_return_status,
4011 x_validation_results => l_validation_results
4012 );
4013 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
4014 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
4015 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
4016 RAISE FND_API.G_EXC_ERROR ;
4017 END IF;
4018
4019 ELSE
4020 -- nothing to validate
4021 RETURN;
4022 END IF;
4023
4024 -- Bug#4086586: Removed Switch/Case stetments as this does not work on 8.1.7.4.
4025
4026 IF(l_validation_results.COUNT > 0) THEN
4027 -- some validation errors where found, populate the px_qa_result_tbl with appropriate values.
4028 FOR i in 1..l_validation_results.COUNT LOOP
4029
4030 -- CASE (l_validation_results(i).error_code)
4031 -- WHEN (G_CHECK_ART_TYP) THEN
4032 IF (l_validation_results(i).error_code) = (G_CHECK_ART_TYP) THEN
4033
4034 -- insert into px_qa_result_tbl only if the appropriate validation level is set
4035 IF (l_perform_qa_art_typ ='Y' AND
4036 do_validation(l_severity_art_typ, p_doc_type) )THEN
4037
4038 l_indx := px_qa_result_tbl.COUNT + 1;
4039 px_qa_result_tbl(l_indx).error_record_type := G_ART_QA_TYPE;
4040 px_qa_result_tbl(l_indx).article_id := l_validation_results(i).article_id;
4041 px_qa_result_tbl(l_indx).deliverable_id := Null;
4042
4043 get_art_title_sec(
4044 p_art_id => l_validation_results(i).article_id,
4045 x_title =>l_title,
4046 x_section =>l_section);
4047
4048 px_qa_result_tbl(l_indx).title := l_title;
4049 px_qa_result_tbl(l_indx).section_name := l_section;
4050 px_qa_result_tbl(l_indx).qa_code := l_validation_results(i).error_code;
4051
4052 px_qa_result_tbl(l_indx).message_name := G_OKC_CHECK_ART_TYP;
4053 px_qa_result_tbl(l_indx).suggestion :=
4054 OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_CHECK_ART_TYP_S);
4055 px_qa_result_tbl(l_indx).error_severity := l_severity_art_typ;
4056 px_qa_result_tbl(l_indx).problem_short_desc := l_desc_art_typ;
4057 px_qa_result_tbl(l_indx).problem_details_short :=
4058 --OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_CHECK_ART_TYP_SH);
4059 -- use same as problem_details
4060 l_validation_results(i).error_message;
4061 px_qa_result_tbl(l_indx).problem_details :=
4062 l_validation_results(i).error_message;
4063 END IF;
4064
4065 -- WHEN (G_CHECK_ART_DEF_SEC) THEN
4066
4067 ELSIF (l_validation_results(i).error_code) = (G_CHECK_ART_DEF_SEC) THEN
4068 -- insert into px_qa_result_tbl only if the appropriate validation level is set
4069 IF (l_perform_qa_def_sec ='Y' AND
4070 do_validation(l_severity_def_sec, 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_DEF_SEC;
4087 px_qa_result_tbl(l_indx).suggestion :=
4088 OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_CHECK_ART_DEF_SEC_S);
4089 px_qa_result_tbl(l_indx).error_severity := l_severity_def_sec;
4090 px_qa_result_tbl(l_indx).problem_short_desc := l_desc_def_sec;
4091 px_qa_result_tbl(l_indx).problem_details_short :=
4092 --OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_CHECK_ART_DEF_SEC_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 -- WHEN (G_CHECK_ART_INV_VAR) THEN
4099 ELSIF (l_validation_results(i).error_code) = (G_CHECK_ART_INV_VAR) THEN
4100
4101 -- insert into px_qa_result_tbl only if the appropriate validation level is set
4102 IF (l_perform_qa_inv_var ='Y' AND
4103 do_validation(l_severity_inv_var, p_doc_type) )THEN
4104
4105 l_indx := px_qa_result_tbl.COUNT + 1;
4106 px_qa_result_tbl(l_indx).error_record_type := G_ART_QA_TYPE;
4107 px_qa_result_tbl(l_indx).article_id := l_validation_results(i).article_id;
4108 px_qa_result_tbl(l_indx).deliverable_id := Null;
4109
4110 get_art_title_sec(
4111 p_art_id => l_validation_results(i).article_id,
4112 x_title =>l_title,
4113 x_section =>l_section);
4114
4115 px_qa_result_tbl(l_indx).title := l_title;
4116 px_qa_result_tbl(l_indx).section_name := l_section;
4117 px_qa_result_tbl(l_indx).qa_code := l_validation_results(i).error_code;
4118
4119 px_qa_result_tbl(l_indx).message_name := G_OKC_CHECK_ART_INV_VAR;
4120 px_qa_result_tbl(l_indx).suggestion :=
4121 OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_CHECK_ART_INV_VAR_S);
4122 px_qa_result_tbl(l_indx).error_severity := l_severity_inv_var;
4123 px_qa_result_tbl(l_indx).problem_short_desc := l_desc_inv_var;
4124 px_qa_result_tbl(l_indx).problem_details_short :=
4125 --OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_CHECK_ART_INV_VAR_SH);
4126 -- use same as problem_details
4127 l_validation_results(i).error_message;
4128 px_qa_result_tbl(l_indx).problem_details :=
4129 l_validation_results(i).error_message;
4130 END IF;
4131
4132 -- WHEN (G_CHECK_ART_INV_VAL) THEN
4133 ELSIF (l_validation_results(i).error_code) = (G_CHECK_ART_INV_VAL) THEN
4134
4135 -- insert into px_qa_result_tbl only if the appropriate validation level is set
4136 IF (l_perform_qa_inv_val ='Y' AND
4137 do_validation(l_severity_inv_val, 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_VAL;
4154 px_qa_result_tbl(l_indx).suggestion :=
4155 OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_CHECK_ART_INV_VAL_S);
4156 px_qa_result_tbl(l_indx).error_severity := l_severity_inv_val;
4157 px_qa_result_tbl(l_indx).problem_short_desc := l_desc_inv_val;
4158 px_qa_result_tbl(l_indx).problem_details_short :=
4159 --OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_CHECK_ART_INV_VAL_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 ELSE
4167 -- all other errors are treated as error conditions
4168 l_indx := px_qa_result_tbl.COUNT + 1;
4169 px_qa_result_tbl(l_indx).error_record_type := G_ART_QA_TYPE;
4170 px_qa_result_tbl(l_indx).article_id := l_validation_results(i).article_id;
4171 px_qa_result_tbl(l_indx).deliverable_id := Null;
4172
4173 get_art_title_sec(
4174 p_art_id => l_validation_results(i).article_id,
4175 x_title =>l_title,
4176 x_section =>l_section);
4177
4178 px_qa_result_tbl(l_indx).title := l_title;
4179 px_qa_result_tbl(l_indx).section_name := l_section;
4180 px_qa_result_tbl(l_indx).qa_code := l_validation_results(i).error_code;
4181
4182 px_qa_result_tbl(l_indx).message_name := Null;
4183 px_qa_result_tbl(l_indx).suggestion := Null;
4184 px_qa_result_tbl(l_indx).error_severity := G_QA_STS_ERROR;
4185 px_qa_result_tbl(l_indx).problem_short_desc := Null;
4186 px_qa_result_tbl(l_indx).problem_details_short := Null;
4187 px_qa_result_tbl(l_indx).problem_details :=
4188 l_validation_results(i).error_message;
4189 END IF;
4190 -- END CASE;
4191 END LOOP;
4192 END IF;
4193
4194
4195 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4196 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1600: Leaving check_validate_draft_articles');
4197 END IF;
4198
4199 EXCEPTION
4200 WHEN FND_API.G_EXC_ERROR THEN
4201 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4202 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7800: Leaving check_validate_draft_articles : OKC_API.G_EXCEPTION_ERROR Exception');
4203 END IF;
4204
4205 IF l_draft_art_csr%ISOPEN THEN
4206 CLOSE l_draft_art_csr;
4207 END IF;
4208
4209 x_return_status := G_RET_STS_ERROR ;
4210
4211 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4212 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4213 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');
4214 END IF;
4215
4216 IF l_draft_art_csr%ISOPEN THEN
4217 CLOSE l_draft_art_csr;
4218 END IF;
4219
4220 x_return_status := G_RET_STS_UNEXP_ERROR ;
4221
4222 WHEN OTHERS THEN
4223 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4224 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'8000: Leaving check_validate_draft_articles because of EXCEPTION: '||sqlerrm);
4225 END IF;
4226
4227 IF l_draft_art_csr%ISOPEN THEN
4228 CLOSE l_draft_art_csr;
4229 END IF;
4230
4231 x_return_status := G_RET_STS_UNEXP_ERROR ;
4232
4233 END check_validate_draft_articles;
4234
4235
4236 -- For Bug# 6979012
4237 -------------------------------------------
4238 -- PROCEDURE check_rejected_clauses
4239 -------------------------------------------
4240 /* check_rejected_clauses checks for rejected clauses added to the template and
4241 displays a warning for each one of them */
4242 PROCEDURE check_rejected_clauses (
4243 p_doc_type IN VARCHAR2,
4244 p_doc_id IN NUMBER,
4245 px_qa_result_tbl IN OUT NOCOPY qa_result_tbl_type,
4246 x_return_status OUT NOCOPY VARCHAR2)
4247 IS
4248 l_api_name CONSTANT VARCHAR2(30) := 'check_rejected_clauses';
4249 l_indx NUMBER;
4250 l_severity OKC_QA_ERRORS_T.ERROR_SEVERITY%TYPE;
4251 l_desc OKC_QA_ERRORS_T.PROBLEM_SHORT_DESC%TYPE;
4252 l_perform_articles_exist_qa VARCHAR2(1);
4253
4254 CURSOR l_rejected_art_csr IS
4255 SELECT DRA.article_id article_id, DRA.article_version_id,
4256 --NVL(VER.display_name, ART.article_title) title, DRA.section_name section
4257 NVL(VER.display_name, ART.article_title) title,NVL(DRA.section_name, '*') section
4258 FROM OKC_TMPL_DRAFT_CLAUSES DRA,
4259 OKC_ARTICLES_ALL ART, OKC_ARTICLE_VERSIONS VER
4260 WHERE DRA.template_id = p_doc_id and
4261 DRA.selected_yn = 'Y' and
4262 DRA.article_id = ART.article_id and
4263 VER.article_version_id = DRA.article_version_id
4264 -- additional check to ensure that we are not checking some
4265 -- orphaned records in the OKC_TMPL_DRAFT_CLAUSES table
4266 AND EXISTS (SELECT '1' FROM OKC_K_ARTICLES_B KART WHERE
4267 KART.document_type = p_doc_type AND
4268 KART.document_id = p_doc_id AND
4269 KART.sav_sae_id = DRA.article_id)
4270 AND VER.article_status='REJECTED';
4271
4272 BEGIN
4273
4274 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4275 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1500: Entered check_rejected_clauses');
4276 END IF;
4277
4278 IF (p_doc_type <> 'TEMPLATE') THEN
4279 RETURN; -- no other doc type is supported.
4280 END IF;
4281
4282 IF (g_status_code NOT in ('DRAFT', 'REJECTED')) THEN
4283 RETURN; -- no article validation for other statuses.
4284 END IF;
4285
4286 get_qa_code_detail(p_qa_code => G_CHECK_ART_REJECTED,
4287 x_perform_qa => l_perform_articles_exist_qa,
4288 x_qa_name => l_desc,
4289 x_severity_flag => l_severity,
4290 x_return_status => x_return_status);
4291 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
4292 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
4293 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
4294 RAISE FND_API.G_EXC_ERROR ;
4295 END IF;
4296
4297
4298 IF (l_perform_articles_exist_qa <> 'Y') THEN
4299 RETURN;
4300 END IF;
4301
4302 IF NOT do_validation(l_severity, p_doc_type) then
4303 -- validation is not required
4304 RETURN;
4305 END IF;
4306
4307 FOR l_rejected_art in l_rejected_art_csr LOOP
4308 l_indx := px_qa_result_tbl.COUNT + 1;
4309 px_qa_result_tbl(l_indx).error_record_type := G_ART_QA_TYPE;
4310 px_qa_result_tbl(l_indx).article_id := l_rejected_art.article_id;
4311 px_qa_result_tbl(l_indx).deliverable_id := Null;
4312 px_qa_result_tbl(l_indx).title := l_rejected_art.title;
4313 px_qa_result_tbl(l_indx).section_name := l_rejected_art.section;
4314 px_qa_result_tbl(l_indx).qa_code := G_CHECK_ART_REJECTED;
4315 px_qa_result_tbl(l_indx).message_name := G_OKC_CHECK_ART_REJECTED;
4316 px_qa_result_tbl(l_indx).suggestion :=OKC_TERMS_UTIL_PVT.Get_Message(G_OKC,G_OKC_CHECK_ART_REJECTED_S);
4317 px_qa_result_tbl(l_indx).error_severity := l_severity;
4318 px_qa_result_tbl(l_indx).problem_short_desc := l_desc;
4319 px_qa_result_tbl(l_indx).problem_details_short :=OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_CHECK_ART_REJECTED);
4320 px_qa_result_tbl(l_indx).problem_details :=OKC_TERMS_UTIL_PVT.Get_Message(G_OKC, G_OKC_CHECK_ART_REJECTED);
4321 END LOOP;
4322 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4323 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1600: Leaving check_rejected_clauses');
4324 END IF;
4325
4326 EXCEPTION
4327 WHEN FND_API.G_EXC_ERROR THEN
4328 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4329 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7800: Leaving check_rejected_clauses : OKC_API.G_EXCEPTION_ERROR Exception');
4330 END IF;
4331
4332 IF l_rejected_art_csr%ISOPEN THEN
4333 CLOSE l_rejected_art_csr;
4334 END IF;
4335
4336 x_return_status := G_RET_STS_ERROR ;
4337
4338 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4339 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4340 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'7900: Leaving check_rejected_clauses : OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
4341 END IF;
4342
4343 IF l_rejected_art_csr%ISOPEN THEN
4344 CLOSE l_rejected_art_csr;
4345 END IF;
4346
4347 x_return_status := G_RET_STS_UNEXP_ERROR ;
4348
4349 WHEN OTHERS THEN
4350 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4351 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'8000: Leaving check_rejected_clauses because of EXCEPTION: '||sqlerrm);
4352 END IF;
4353
4354 IF l_rejected_art_csr%ISOPEN THEN
4355 CLOSE l_rejected_art_csr;
4356 END IF;
4357
4358 x_return_status := G_RET_STS_UNEXP_ERROR ;
4359
4360 END check_rejected_clauses;
4361 -- Changes for Bug# 6979012 Ends
4362
4363
4364 -->>>>>>>>>>>>>>>>>> INTERNAL QA Check API PROCEDURES >>>>>>>>>>>>>>>>>>
4365
4366 ------------------------------------------------------------
4367 -- PROCEDURE QA_DOC main api for validating the documents
4368 ------------------------------------------------------------
4369
4370 /* 11.5.10+ modified to accept additional in parameter p_validation_level
4371 p_validation_level 'A' do all qa checks
4372 p_validation_level 'E' do only error checks
4373 applies only to p_doc_type='TEMPLATE'
4374 */
4375 PROCEDURE QA_DOC(
4376 p_qa_mode IN VARCHAR2,
4377 p_doc_type IN VARCHAR2,
4378 p_doc_id IN NUMBER,
4379 x_return_status OUT NOCOPY VARCHAR2,
4380 x_msg_count OUT NOCOPY NUMBER,
4381 x_msg_data OUT NOCOPY VARCHAR2,
4382 x_sequence_id OUT NOCOPY NUMBER,
4383 x_qa_result_tbl OUT NOCOPY qa_result_tbl_type,
4384 x_qa_return_status OUT NOCOPY VARCHAR2,
4385 p_validation_level IN VARCHAR2,
4386 p_run_expert_flag IN VARCHAR2 DEFAULT 'Y') -- Bug 5186245
4387 IS
4388
4389 l_api_name CONSTANT VARCHAR2(30) := 'QA_Doc';
4390 l_now DATE;
4391 i NUMBER := 0;
4392 q NUMBER := 0;
4393 l_error_found Boolean := FALSE;
4394 l_warning_found Boolean := FALSE;
4395 l_expert_articles_tbl OKC_XPRT_UTIL_PVT.expert_articles_tbl_type;
4396 l_perform_qa BOOLEAN := TRUE;
4397
4398 CURSOR l_get_qa_detail_csr IS
4399 SELECT fnd.lookup_code qa_code,
4400 fnd.meaning qa_name,
4401 nvl(qa.severity_flag,G_QA_STS_WARNING) severity_flag ,
4402 decode(fnd.enabled_flag,'N','N','Y',decode(qa.enable_qa_yn,'N','N','Y'),'Y') perform_qa
4403 FROM FND_LOOKUPS FND, OKC_DOC_QA_LISTS QA
4404 WHERE QA.DOCUMENT_TYPE(+)=p_doc_type
4405 AND QA.QA_CODE(+) = FND.LOOKUP_CODE
4406 AND Fnd.LOOKUP_TYPE=G_QA_LOOKUP;
4407
4408 CURSOR l_get_doc_articles_csr IS
4409 SELECT KART.ID ID,
4410 KART.SAV_SAE_ID ARTICLE_ID,
4411 KART.ARTICLE_VERSION_ID ARTICLE_VERSION_ID,
4412 KART.AMENDMENT_OPERATION_CODE AMENDMENT_OPERATION_CODE,
4413 KART.AMENDMENT_DESCRIPTION AMENDMENT_DESCRIPTION,
4414 KART.SCN_ID SCN_ID,
4415 OKC_TERMS_UTIL_PVT.get_article_name(KART.SAV_SAE_ID ,KART.ARTICLE_VERSION_ID) TITLE,
4416 Decode(ART.standard_yn,'N',KART.ref_article_id,NULL) STD_ART_ID
4417 FROM OKC_K_ARTICLES_B KART,
4418 OKC_ARTICLES_ALL ART,
4419 OKC_ARTICLE_VERSIONS VERS
4420 WHERE KART.DOCUMENT_TYPE = p_doc_type
4421 AND KART.DOCUMENT_ID = p_doc_id
4422 AND KART.ARTICLE_VERSION_ID = VERS.ARTICLE_VERSION_ID
4423 AND KART.SAV_SAE_ID = ART.ARTICLE_ID;
4424
4425 /* modified in 11.5.10+
4426 CURSOR l_get_tmpl_articles_csr IS
4427 SELECT KART.ID ID,
4428 KART.SAV_SAE_ID ARTICLE_ID,
4429 STD.ARTICLE_ID STD_ART_ID,
4430 KART.ARTICLE_VERSION_ID ARTICLE_VERSION_ID,
4431 KART.AMENDMENT_OPERATION_CODE AMENDMENT_OPERATION_CODE,
4432 KART.AMENDMENT_DESCRIPTION AMENDMENT_DESCRIPTION,
4433 KART.SCN_ID SCN_ID,
4434 OKC_TERMS_UTIL_PVT.get_article_name(KART.SAV_SAE_ID ,VERS.ARTICLE_VERSION_ID) TITLE
4435 FROM OKC_K_ARTICLES_B KART, OKC_ARTICLE_VERSIONS VERS, OKC_ARTICLE_VERSIONS STD
4436 WHERE KART.DOCUMENT_TYPE = p_doc_type
4437 AND KART.DOCUMENT_ID = p_doc_id
4438 AND STD.ARTICLE_VERSION_ID(+) = VERS.STD_ARTICLE_VERSION_ID
4439 AND KART.SAV_SAE_ID = VERS.ARTICLE_ID
4440 AND nvl(VERS.START_DATE,sysdate) = ( SELECT nvl(MAX(START_DATE),sysdate)
4441 FROM OKC_ARTICLE_VERSIONS
4442 WHERE ARTICLE_ID=VERS.ARTICLE_ID);
4443 */
4444
4445 -- 11.5.10+ modified cursor to get the version from OKC_TERMS_UTIL_PVT.get_latest_tmpl_art_version_id
4446 CURSOR l_get_tmpl_articles_csr IS
4447 SELECT KART.ID ID,
4448 KART.SAV_SAE_ID ARTICLE_ID,
4449 STD.ARTICLE_ID STD_ART_ID,
4450 KART.ARTICLE_VERSION_ID ARTICLE_VERSION_ID,
4451 KART.AMENDMENT_OPERATION_CODE AMENDMENT_OPERATION_CODE,
4452 KART.AMENDMENT_DESCRIPTION AMENDMENT_DESCRIPTION,
4453 KART.SCN_ID SCN_ID,
4454 OKC_TERMS_UTIL_PVT.get_article_name(KART.SAV_SAE_ID ,VERS.ARTICLE_VERSION_ID) TITLE
4455 FROM OKC_K_ARTICLES_B KART, OKC_ARTICLE_VERSIONS VERS, OKC_ARTICLE_VERSIONS STD
4456 WHERE KART.DOCUMENT_TYPE = p_doc_type
4457 AND KART.DOCUMENT_ID = p_doc_id
4458 AND STD.ARTICLE_VERSION_ID(+) = VERS.STD_ARTICLE_VERSION_ID
4459 AND KART.SAV_SAE_ID = VERS.ARTICLE_ID
4460 AND VERS.ARTICLE_VERSION_ID = OKC_TERMS_UTIL_PVT.get_latest_tmpl_art_version_id(
4461 KART.sav_sae_id,
4462 g_start_date,
4463 g_end_date,
4464 g_status_code,
4465 p_doc_type,
4466 p_doc_id
4467 );
4468
4469 /* expert commented out
4470 -- new cursor to fetch the expert articles and their details
4471 CURSOR l_get_xprt_articles_csr IS
4472 SELECT XPRT.clause_id article_id,
4473 ver.article_version_id article_version_id,
4474 Nvl(ver.display_name, art.article_title) title,
4475 xprt.rule_id rule_id,
4476 xprt.rule_name rule_name
4477 FROM OKC_XPRT_CLAUSES_V XPRT,
4478 OKC_ARTICLES_ALL ART, OKC_ARTICLE_VERSIONS VER
4479 WHERE XPRT.template_id = p_doc_id
4480 AND ART.article_id = XPRT.clause_id
4481 AND VER.article_id = ART.article_id
4482 AND VER.article_version_id = OKC_TERMS_UTIL_PVT.get_latest_tmpl_art_version_id(
4483 ART.article_id,
4484 g_start_date,
4485 g_end_date,
4486 g_status_code,
4487 p_doc_type,
4488 p_doc_id
4489 );
4490 */
4491
4492 CURSOR l_get_sections_csr IS
4493 SELECT ID ID,
4494 AMENDMENT_OPERATION_CODE AMENDMENT_OPERATION_CODE,
4495 AMENDMENT_DESCRIPTION AMENDMENT_DESCRIPTION,
4496 SCN_ID SCN_ID,
4497 SCN_CODE SCN_CODE,
4498 DECODE(LABEL,NULL,HEADING,
4499 okc_terms_util_pvt.get_message('OKC',
4500 'OKC_TERMS_LABEL_AND_NAME',
4501 'LABEL', LABEL,
4502 'NAME', HEADING)) HEADING
4503 FROM OKC_SECTIONS_B
4504 WHERE DOCUMENT_TYPE = p_doc_type
4505 AND DOCUMENT_ID = p_doc_id;
4506
4507 CURSOR l_get_effective_date IS
4508 SELECT nvl(ARTICLE_EFFECTIVE_DATE ,sysdate)
4509 FROM OKC_TEMPLATE_USAGES
4510 WHERE DOCUMENT_TYPE=p_doc_type
4511 AND DOCUMENT_ID=p_doc_id;
4512
4513 /* Modified in 11.5.10+ to get additonal template information
4514 CURSOR l_get_effective_date_template IS
4515 SELECT start_date, end_date
4516 FROM okc_terms_templates_all
4517 WHERE template_id = p_doc_id;
4518 */
4519
4520 -- 11.5.10+ modified cursor to get additional template details
4521 CURSOR l_get_effective_date_template IS
4522 SELECT start_date, end_date,
4523 nvl(contract_expert_enabled, 'N'), nvl(status_code, 'DRAFT'),
4524 template_name,org_id
4525 FROM okc_terms_templates_all
4526 WHERE template_id = p_doc_id;
4527
4528 /* 11.5.10+ replace with global variables
4529 l_start_date DATE;
4530 l_end_date DATE;
4531 */
4532
4533 BEGIN
4534 g_validation_level :='A';
4535 g_expert_enabled :='N';
4536 -- because of GSCC warnings the default values are removed
4537 -- but it's not allowed to assign something to IN parameters
4538 -- don't think it's good idea to remove default values
4539 -- it can cause regression
4540 -- if p_qa_mode is null then p_qa_mode := G_NORMAL_QA; end if;
4541 -- if p_validation_level is null then p_validation_level := 'A'; end if;
4542 l_now := SYSDATE;
4543
4544 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4545 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2100: Entered QA_Doc');
4546 END IF;
4547 -- Initialize API return status to success
4548 x_return_status := G_RET_STS_SUCCESS;
4549 x_qa_return_status := G_QA_STS_SUCCESS;
4550 g_validation_level := p_validation_level;
4551
4552 IF p_doc_type = G_TMPL_DOC_TYPE THEN
4553
4554 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4555 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2110: Opening cursor l_get_effective_date_template');
4556 END IF;
4557 OPEN l_get_effective_date_template;
4558 --FETCH l_get_effective_date_template INTO l_start_date, l_end_date;
4559 FETCH l_get_effective_date_template INTO g_start_date, g_end_date,
4560 g_expert_enabled, g_status_code, g_template_name, g_org_id;
4561 IF l_get_effective_date_template%NOTFOUND THEN
4562 l_perform_qa := FALSE;
4563 END IF;
4564 CLOSE l_get_effective_date_template;
4565
4566 IF NVL(g_end_date,sysdate) >= sysdate THEN
4567 IF g_start_date > sysdate THEN
4568 l_article_effective_date := g_start_date;
4569 ELSE
4570 l_article_effective_date := sysdate;
4571 END IF;
4572 ELSE
4573 l_article_effective_date := g_end_date;
4574 END IF;
4575
4576 ELSE
4577 -- doc type not Template
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,'2120: Opening cursor l_get_effective_date');
4580 END IF;
4581 OPEN l_get_effective_date;
4582 FETCH l_get_effective_date INTO l_article_effective_date;
4583 IF l_get_effective_date%NOTFOUND THEN
4584 l_perform_qa := FALSE;
4585 END IF;
4586 CLOSE l_get_effective_date;
4587 END IF;
4588
4589 IF l_perform_QA then
4590
4591 -- Reset PL/SQl Tables
4592 x_qa_result_tbl.DELETE;
4593 l_article_tbl.DELETE;
4594 l_section_tbl.DELETE;
4595 l_qa_detail_tbl.DELETE;
4596
4597 /* expert commented out
4598 l_xprt_article_tbl.DELETE;
4599 */
4600
4601 -- Get all QA Detail
4602 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4603 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2130: Before FOR cr IN l_get_qa_detail_csr LOOP');
4604 END IF;
4605 q := 0;
4606 FOR cr IN l_get_qa_detail_csr LOOP
4607
4608 l_qa_detail_tbl(q).qa_code := cr.qa_code;
4609 l_qa_detail_tbl(q).qa_name := cr.qa_name;
4610 l_qa_detail_tbl(q).severity_flag :=cr.severity_flag;
4611 l_qa_detail_tbl(q).perform_qa :=cr.perform_qa;
4612
4613 IF (l_qa_detail_tbl(q).qa_code = G_CHECK_ART_EXT) THEN
4614
4615 -- Modify the severity level of the qa check for existence of clauses
4616 -- to 'W' (warning) if the template is expert enabled. It is 'E' otherwise.
4617 IF (g_expert_enabled = 'Y') THEN
4618 l_qa_detail_tbl(q).severity_flag := G_QA_STS_WARNING;
4619 END IF;
4620
4621 -- bug 4083727 - muteshev - start
4622 -- Modify the severity level of the qa check for existence of clauses
4623 -- to 'W' (warning) if the template contains deliverables. It is 'E' otherwise.
4624 if okc_terms_util_grp.Is_Deliverable_Exist(
4625 p_api_version => 1,
4626 p_init_msg_list => FND_API.G_FALSE,
4627 x_return_status => x_return_status,
4628 x_msg_data => x_msg_data,
4629 x_msg_count => x_msg_count,
4630 p_doc_type => 'TEMPLATE',
4631 p_doc_id => p_doc_id
4632 ) is not null then
4633 l_qa_detail_tbl(q).severity_flag := G_QA_STS_WARNING;
4634 end if;
4635 -- bug 4083727 - muteshev - end
4636
4637 END IF;
4638 q := q +1;
4639 END LOOP;
4640
4641
4642 -- populate l_article_tbl with all the articles.
4643 IF p_doc_type <> G_TMPL_DOC_TYPE THEN
4644 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4645 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2140: Before FOR cr IN l_get_doc_articles_csr LOOP');
4646 END IF;
4647 i := 0;
4648 FOR cr IN l_get_doc_articles_csr LOOP
4649 i := i+1;
4650 l_article_tbl(i).id := cr.id;
4651 l_article_tbl(i).article_id := cr.article_id;
4652 l_article_tbl(i).std_art_id := cr.std_art_id;
4653 l_article_tbl(i).article_version_id := cr.article_version_id;
4654 l_article_tbl(i).amendment_operation_code := cr.amendment_operation_code;
4655 l_article_tbl(i).amendment_description := cr.amendment_description;
4656 l_article_tbl(i).scn_id := cr.scn_id;
4657 l_article_tbl(i).title := cr.title;
4658 END LOOP;
4659 ELSE
4660 -- doc type is TEMPLATE, if expert enabled get the expert clauses
4661 /* expert commented out
4662 IF (g_expert_enabled = 'Y') then
4663 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4664 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2150: Before OPEN l_get_xprt_articles_csr');
4665 END IF;
4666 OPEN l_get_xprt_articles_csr;
4667 FETCH l_get_xprt_articles_csr BULK COLLECT INTO l_xprt_article_tbl;
4668 CLOSE l_get_xprt_articles_csr;
4669 END IF;
4670 */
4671
4672 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4673 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2160: Before FOR cr IN l_get_tmpl_articles_csr');
4674 END IF;
4675 i := 0;
4676 FOR cr IN l_get_tmpl_articles_csr LOOP
4677 i := i+1;
4678 l_article_tbl(i).id := cr.id;
4679 l_article_tbl(i).article_id := cr.article_id;
4680 l_article_tbl(i).std_art_id := cr.std_art_id;
4681 l_article_tbl(i).article_version_id := cr.article_version_id;
4682 l_article_tbl(i).amendment_operation_code := cr.amendment_operation_code;
4683 l_article_tbl(i).amendment_description := cr.amendment_description;
4684 l_article_tbl(i).scn_id := cr.scn_id;
4685 l_article_tbl(i).title := cr.title;
4686 END LOOP;
4687 END IF;
4688
4689 -- populate l_section_tbl with all the sections.
4690 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4691 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2170: Before FOR cr IN l_get_sections_csr LOOP');
4692 END IF;
4693 i := 0;
4694 FOR cr IN l_get_sections_csr LOOP
4695 i := i+1;
4696 l_section_tbl(i).id := cr.id;
4697 l_section_tbl(i).amendment_operation_code := cr.amendment_operation_code;
4698 l_section_tbl(i).amendment_description := cr.amendment_description;
4699 l_section_tbl(i).scn_id := cr.scn_id;
4700 l_section_tbl(i).heading := cr.heading;
4701 l_section_tbl(i).scn_code := cr.scn_code;
4702 END LOOP;
4703
4704 --****************************************--
4705 -- Run set of validation --
4706 --****************************************--
4707
4708 ------------------------------------------------------------
4709 -- QA Check for Layout Template to be run for templates ONLY
4710 ------------------------------------------------------------
4711 IF p_doc_type=G_TMPL_DOC_TYPE THEN
4712 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4713 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2180: calling check_layout_template');
4714 END IF;
4715 check_layout_template(
4716 p_doc_type => p_doc_type,
4717 p_doc_id => p_doc_id,
4718 px_qa_result_tbl => x_qa_result_tbl,
4719 x_return_status => x_return_status
4720 );
4721 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
4722 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
4723 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
4724 RAISE FND_API.G_EXC_ERROR ;
4725 END IF;
4726
4727 --call new internal procedure to check for article existence
4728 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4729 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2190: calling check_articles_exist');
4730 END IF;
4731 check_articles_exist (
4732 p_doc_type => p_doc_type,
4733 p_doc_id => p_doc_id,
4734 px_qa_result_tbl => x_qa_result_tbl,
4735 x_return_status => x_return_status
4736 );
4737 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
4738 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
4739 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
4740 RAISE FND_API.G_EXC_ERROR ;
4741 END IF;
4742
4743 --call new internal procedure to validate any draft articles
4744 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4745 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2200: calling check_validate_draft_articles');
4746 END IF;
4747 check_validate_draft_articles(
4748 p_doc_type => p_doc_type,
4749 p_doc_id => p_doc_id,
4750 px_qa_result_tbl => x_qa_result_tbl,
4751 x_return_status => x_return_status
4752 );
4753 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
4754 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
4755 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
4756 RAISE FND_API.G_EXC_ERROR ;
4757 END IF;
4758
4759 -- For Bug# 6979012
4760 --call new internal procedure to give a warning for rejected articles
4761 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4762 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2200: calling check_rejected_clauses');
4763 END IF;
4764 check_rejected_clauses(
4765 p_doc_type => p_doc_type,
4766 p_doc_id => p_doc_id,
4767 px_qa_result_tbl => x_qa_result_tbl,
4768 x_return_status => x_return_status
4769 );
4770 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
4771 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
4772 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
4773 RAISE FND_API.G_EXC_ERROR ;
4774 END IF;
4775 -- Changes for Bug# 6979012 Ends
4776
4777 --MLS for templates
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,'2202: calling Check_translated_tmpl_revision');
4780 END IF;
4781 Check_translated_tmpl_rev(
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 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4794 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2205: calling Check_translated_tmpl_effectivity');
4795 END IF;
4796 Check_translated_tmpl_eff(
4797 p_doc_type => p_doc_type,
4798 p_doc_id => p_doc_id,
4799 px_qa_result_tbl => x_qa_result_tbl,
4800 x_return_status => x_return_status
4801 );
4802 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
4803 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
4804 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
4805 RAISE FND_API.G_EXC_ERROR ;
4806 END IF;
4807 --MLS for templates
4808
4809
4810 END IF; -- p_doc_type=G_TMPL_DOC_TYPE
4811
4812
4813 --------------------------------------------
4814 -- QA Check for Incompatible articles
4815 --------------------------------------------
4816 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4817 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2210: calling check_validate_draft_articles');
4818 END IF;
4819 Check_Incomp_and_alternate(
4820 p_qa_mode => p_qa_mode,
4821 p_doc_type => p_doc_type,
4822 p_doc_id => p_doc_id,
4823 px_qa_result_tbl => x_qa_result_tbl,
4824 x_return_status => x_return_status
4825 );
4826 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
4827 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
4828 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
4829 RAISE FND_API.G_EXC_ERROR ;
4830 END IF;
4831
4832 --------------------------------------------
4833 -- QA Check for Duplicate articles
4834 --------------------------------------------
4835 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4836 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2220: calling Check_duplicate_articles');
4837 END IF;
4838 Check_duplicate_articles(
4839 p_qa_mode => p_qa_mode,
4840 p_doc_type => p_doc_type,
4841 p_doc_id => p_doc_id,
4842 px_qa_result_tbl => x_qa_result_tbl,
4843 x_return_status => x_return_status
4844 );
4845 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
4846 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
4847 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
4848 RAISE FND_API.G_EXC_ERROR ;
4849 END IF;
4850
4851 --------------------------------------------
4852 -- QA Check for variable-doc type usage
4853 --------------------------------------------
4854 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4855 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2230: calling Check_var_doc_type_usage');
4856 END IF;
4857 Check_var_doc_type_usage(
4858 p_qa_mode => p_qa_mode,
4859 p_doc_type => p_doc_type,
4860 p_doc_id => p_doc_id,
4861 px_qa_result_tbl => x_qa_result_tbl,
4862 x_return_status => x_return_status
4863 );
4864 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
4865 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
4866 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
4867 RAISE FND_API.G_EXC_ERROR ;
4868 END IF;
4869
4870 ----------------------------------------------------------------
4871 -- QA Check for article effectivity and old version of article
4872 ----------------------------------------------------------------
4873 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4874 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2240: calling Check_art_effectivity');
4875 END IF;
4876 Check_art_effectivity(
4877 p_qa_mode => p_qa_mode,
4878 p_doc_type => p_doc_type,
4879 p_doc_id => p_doc_id,
4880 px_qa_result_tbl => x_qa_result_tbl,
4881 x_return_status => x_return_status
4882 );
4883 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
4884 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
4885 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
4886 RAISE FND_API.G_EXC_ERROR ;
4887 END IF;
4888
4889
4890 -----------------------------------------------------------------------
4891 -- QA Check for non-entered user variables(Both External and Internal)
4892 -----------------------------------------------------------------------
4893 IF p_doc_type <>G_TMPL_DOC_TYPE THEN
4894
4895 --------------------------------------------
4896 -- QA Check for User Variables
4897 --------------------------------------------
4898 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4899 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2250: calling Check_variables');
4900 END IF;
4901 Check_variables(
4902 p_doc_type => p_doc_type,
4903 p_doc_id => p_doc_id,
4904 px_qa_result_tbl => x_qa_result_tbl,
4905 x_return_status => x_return_status
4906 );
4907 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
4908 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
4909 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
4910 RAISE FND_API.G_EXC_ERROR ;
4911 END IF;
4912
4913 ---------------------------------------------------------
4914 -- QA Check for unassigned articles
4915 ---------------------------------------------------------
4916 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4917 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2260: calling Check_unassigned_articles');
4918 END IF;
4919 Check_unassigned_articles(
4920 p_doc_type => p_doc_type,
4921 p_doc_id => p_doc_id,
4922 px_qa_result_tbl => x_qa_result_tbl,
4923 x_return_status => x_return_status
4924 );
4925 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
4926 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
4927 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
4928 RAISE FND_API.G_EXC_ERROR ;
4929 END IF;
4930
4931 ---------------------------------------------------------------
4932 -- QA Check for empty sections
4933 ---------------------------------------------------------------
4934 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4935 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2270: calling Check_empty_sections');
4936 END IF;
4937 Check_empty_sections(
4938 p_doc_type => p_doc_type,
4939 p_doc_id => p_doc_id,
4940 px_qa_result_tbl => x_qa_result_tbl,
4941 x_return_status => x_return_status
4942 );
4943 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
4944 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
4945 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
4946 RAISE FND_API.G_EXC_ERROR ;
4947 END IF;
4948
4949 ---------------------------------------------------------------
4950 -- QA Check for inactive template
4951 ---------------------------------------------------------------
4952 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4953 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2280: calling check_inactive_template');
4954 END IF;
4955 check_inactive_template(
4956 p_doc_type => p_doc_type,
4957 p_doc_id => p_doc_id,
4958 px_qa_result_tbl => x_qa_result_tbl,
4959 x_return_status => x_return_status
4960 );
4961 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
4962 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
4963 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
4964 RAISE FND_API.G_EXC_ERROR ;
4965 END IF;
4966
4967 IF p_qa_mode = G_AMEND_QA THEN
4968
4969 ------------------------------------------------------------
4970 -- QA Check for Amended article with no description
4971 ------------------------------------------------------------
4972 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4973 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2290: calling Check_article_Amend_No_Texts');
4974 END IF;
4975 Check_article_Amend_No_Texts(
4976 p_doc_type => p_doc_type,
4977 p_doc_id => p_doc_id,
4978 px_qa_result_tbl => x_qa_result_tbl,
4979 x_return_status => x_return_status
4980 );
4981 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
4982 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
4983 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
4984 RAISE FND_API.G_EXC_ERROR ;
4985 END IF;
4986
4987 ------------------------------------------------------------
4988 -- QA Check for Amended section with no description
4989 ------------------------------------------------------------
4990 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4991 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2300: calling Check_Section_Amend_No_Texts');
4992 END IF;
4993 Check_Section_Amend_No_Texts(
4994 p_doc_type => p_doc_type,
4995 p_doc_id => p_doc_id,
4996 px_qa_result_tbl => x_qa_result_tbl,
4997 x_return_status => x_return_status
4998 );
4999 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
5000 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
5001 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
5002 RAISE FND_API.G_EXC_ERROR ;
5003 END IF;
5004
5005 END IF; -- IF p_qa_mode = G_AMEND_QA THEN
5006
5007 END IF; -- IF p_doc_type <>G_TMPL_DOC_TYPE THEN
5008
5009 ------------------------------------------------------------
5010 -- QA Check for expert
5011 ------------------------------------------------------------
5012 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5013 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2310: calling OKC_EXPRT_UTIL_GRP.contract_expert_bv');
5014 END IF;
5015
5016 IF p_doc_type = G_TMPL_DOC_TYPE THEN
5017 --Added 11.5.10+ CE
5018 OKC_XPRT_UTIL_PVT.validate_template_for_expert (
5019 p_api_version => 1,
5020 p_init_msg_list => FND_API.G_FALSE,
5021 p_template_id => p_doc_id,
5022 x_msg_data => x_msg_data,
5023 x_msg_count => x_msg_count,
5024 x_qa_result_tbl => x_qa_result_tbl,
5025 x_return_status => x_return_status);
5026
5027 ELSE
5028 IF p_run_expert_flag = 'Y' AND p_doc_type <> 'OKS' THEN -- Added for Bug 5186245
5029 -- Bug# 4874729. Not invoking expert validation for OKS doc type.
5030
5031 OKC_XPRT_UTIL_PVT.contract_expert_bv (
5032 p_api_version => 1,
5033 p_init_msg_list => FND_API.G_FALSE,
5034 p_document_type => p_doc_type,
5035 p_document_id => p_doc_id,
5036 p_bv_mode => 'QA',
5037 x_expert_articles_tbl => l_expert_articles_tbl,
5038 x_msg_data => x_msg_data,
5039 x_msg_count => x_msg_count,
5040 x_qa_result_tbl => x_qa_result_tbl,
5041 x_return_status => x_return_status);
5042
5043 END IF;
5044 END IF;
5045
5046 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
5047 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
5048 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
5049 RAISE FND_API.G_EXC_ERROR ;
5050 END IF;
5051
5052 --****************************************--
5053 -- End of validation set --
5054 --****************************************--
5055
5056 -- update common attributes for the QA table
5057 IF x_qa_result_tbl.COUNT > 0 THEN
5058
5059 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5060 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');
5061 END IF;
5062
5063 FOR i IN x_qa_result_tbl.FIRST..x_qa_result_tbl.LAST LOOP
5064 x_qa_result_tbl(i).document_type := p_doc_type;
5065 x_qa_result_tbl(i).document_id := p_doc_id;
5066 x_qa_result_tbl(i).creation_date := l_now;
5067
5068 IF x_qa_result_tbl(i).Error_severity = G_QA_STS_ERROR THEN
5069 l_error_found := true;
5070 END IF;
5071 IF x_qa_result_tbl(i).Error_severity = G_QA_STS_WARNING THEN
5072 l_warning_found := true;
5073 END IF;
5074 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5075 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);
5076 END IF;
5077
5078 END LOOP;
5079 IF l_error_found THEN
5080 x_qa_return_status := G_QA_STS_ERROR;
5081 ELSIF l_warning_found THEN
5082 x_qa_return_status := G_QA_STS_WARNING;
5083 END IF;
5084 END IF;
5085
5086
5087 END IF; -- IF l_perform_QA then
5088
5089 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5090 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2300: Leaving QA_Doc');
5091 END IF;
5092 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
5093
5094 EXCEPTION
5095 WHEN FND_API.G_EXC_ERROR THEN
5096 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5097 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'2400: Leaving QA_Doc : OKC_API.G_EXCEPTION_ERROR Exception');
5098 END IF;
5099
5100 IF l_get_qa_detail_csr%ISOPEN THEN
5101 CLOSE l_get_qa_detail_csr;
5102 END IF;
5103 IF l_get_doc_articles_csr%ISOPEN THEN
5104 CLOSE l_get_doc_articles_csr;
5105 END IF;
5106 IF l_get_tmpl_articles_csr%ISOPEN THEN
5107 CLOSE l_get_tmpl_articles_csr;
5108 END IF;
5109 IF l_get_sections_csr %ISOPEN THEN
5110 CLOSE l_get_sections_csr ;
5111 END IF;
5112 IF l_get_effective_date%ISOPEN THEN
5113 CLOSE l_get_effective_date;
5114 END IF;
5115
5116 /* expert commented out
5117 IF l_get_xprt_articles_csr%ISOPEN THEN
5118 CLOSE l_get_xprt_articles_csr;
5119 END IF;
5120 */
5121
5122 x_return_status := G_RET_STS_ERROR ;
5123 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
5124
5125 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5126 ROLLBACK TO g_QA_Doc;
5127 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5128 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'2500: Leaving QA_Doc : OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
5129 END IF;
5130 IF l_get_qa_detail_csr%ISOPEN THEN
5131 CLOSE l_get_qa_detail_csr;
5132 END IF;
5133 IF l_get_doc_articles_csr%ISOPEN THEN
5134 CLOSE l_get_doc_articles_csr;
5135 END IF;
5136 IF l_get_tmpl_articles_csr%ISOPEN THEN
5137 CLOSE l_get_tmpl_articles_csr;
5138 END IF;
5139 IF l_get_sections_csr %ISOPEN THEN
5140 CLOSE l_get_sections_csr ;
5141 END IF;
5142 IF l_get_effective_date%ISOPEN THEN
5143 CLOSE l_get_effective_date;
5144 END IF;
5145
5146 /* expert commented out
5147 IF l_get_xprt_articles_csr%ISOPEN THEN
5148 CLOSE l_get_xprt_articles_csr;
5149 END IF;
5150 */
5151
5152 x_return_status := G_RET_STS_UNEXP_ERROR ;
5153 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
5154
5155 WHEN OTHERS THEN
5156 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5157 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'2600: Leaving QA_Doc because of EXCEPTION: '||sqlerrm);
5158 END IF;
5159
5160 IF l_get_qa_detail_csr%ISOPEN THEN
5161 CLOSE l_get_qa_detail_csr;
5162 END IF;
5163 IF l_get_doc_articles_csr%ISOPEN THEN
5164 CLOSE l_get_doc_articles_csr;
5165 END IF;
5166 IF l_get_tmpl_articles_csr%ISOPEN THEN
5167 CLOSE l_get_tmpl_articles_csr;
5168 END IF;
5169 IF l_get_sections_csr %ISOPEN THEN
5170 CLOSE l_get_sections_csr ;
5171 END IF;
5172 IF l_get_effective_date%ISOPEN THEN
5173 CLOSE l_get_effective_date;
5174 END IF;
5175
5176 /* expert commented out
5177 IF l_get_xprt_articles_csr%ISOPEN THEN
5178 CLOSE l_get_xprt_articles_csr;
5179 END IF;
5180 */
5181
5182 x_return_status := G_RET_STS_UNEXP_ERROR ;
5183 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
5184 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
5185 END IF;
5186 END QA_Doc;
5187
5188 END OKC_TERMS_QA_PVT;