1 PACKAGE OKC_XPRT_UTIL_PVT AS
2 /* $Header: OKCVXUTLS.pls 120.17.12000000.2 2007/07/16 14:04:22 krallapa ship $ */
3
4 TYPE expert_articles_tbl_type IS TABLE OF NUMBER -- column of article IDs
5 INDEX BY BINARY_INTEGER;
6
7 -- Begin: Added for R12
8 TYPE expert_deviations_list IS TABLE OF NUMBER -- column of Deviation Rule IDs
9 INDEX BY BINARY_INTEGER;
10
11 TYPE expert_dev_line_nbr_list IS TABLE OF NUMBER -- column of Deviation Rule Line Numbers
12 INDEX BY BINARY_INTEGER;
13
14 TYPE dev_rule_rec_type IS RECORD (
15 line_number varchar2(250),
16 rule_id OKC_XPRT_RULE_HDRS_ALL.rule_id%TYPE
17 );
18
19 TYPE dev_rule_variables_rec_type IS RECORD (
20 line_number varchar2(250),
21 rule_id OKC_XPRT_RULE_HDRS_ALL.rule_id%TYPE,
22 variable_id OKC_BUS_VARIABLES_B.variable_code%TYPE
23 );
24
25 TYPE dev_rule_questions_rec_type IS RECORD (
26 --line_number varchar2(250),
27 rule_id OKC_XPRT_RULE_HDRS_ALL.rule_id%TYPE,
28 question_id OKC_XPRT_QUESTIONS_B.question_id%TYPE
29 );
30
31 TYPE dev_rule_var_values_rec_type IS RECORD (
32 line_number varchar2(250),
33 rule_id OKC_XPRT_RULE_HDRS_ALL.rule_id%TYPE,
34 variable_id OKC_BUS_VARIABLES_B.variable_code%TYPE,
35 variable_value VARCHAR2(40)
36 );
37
38 TYPE dev_rule_qst_values_rec_type IS RECORD (
39 --line_number varchar2(250),
40 rule_id OKC_XPRT_RULE_HDRS_ALL.rule_id%TYPE,
41 question_id OKC_XPRT_QUESTIONS_B.question_id%TYPE,
42 question_value VARCHAR2(40)
43 );
44
45 TYPE dev_rule_tbl_type IS TABLE OF dev_rule_rec_type
46 INDEX BY BINARY_INTEGER;
47
48 TYPE dev_rule_variables_tbl_type IS TABLE OF dev_rule_variables_rec_type
49 INDEX BY BINARY_INTEGER;
50
51 TYPE dev_rule_questions_tbl_type IS TABLE OF dev_rule_questions_rec_type
52 INDEX BY BINARY_INTEGER;
53
54 TYPE dev_rule_var_values_tbl_type IS TABLE OF dev_rule_var_values_rec_type
55 INDEX BY BINARY_INTEGER;
56
57 TYPE dev_rule_qst_values_tbl_type IS TABLE OF dev_rule_qst_values_rec_type
58 INDEX BY BINARY_INTEGER;
59
60 -- End: Added for R12
61
62 ---------------------------------------------------
63 -- Procedure:
64 ---------------------------------------------------
65 PROCEDURE check_import_status
66 (
67 p_run_id IN NUMBER,
68 p_import_status IN VARCHAR2,
69 p_model_type IN VARCHAR2,
70 x_return_status OUT NOCOPY VARCHAR2,
71 x_msg_data OUT NOCOPY VARCHAR2,
72 x_msg_count OUT NOCOPY NUMBER
73 );
74
75 --Bug 4723548 Added new function
76 FUNCTION is_value_set_changed (
77 p_object_code IN VARCHAR2,
78 p_object_value_set_id IN NUMBER)
79 RETURN VARCHAR2;
80
81 FUNCTION get_value_set_id
82 (
83 p_value_set_name IN VARCHAR2
84 ) RETURN NUMBER;
85
86 FUNCTION is_rule_line_level
87 (
88 p_rule_id IN NUMBER
89 ) RETURN VARCHAR2;
90
91 FUNCTION xprt_enabled_template
92 (
93 p_template_id IN NUMBER
94 ) RETURN VARCHAR2;
95
96 PROCEDURE create_test_publication
97 (
98 x_return_status OUT NOCOPY VARCHAR2,
99 x_msg_data OUT NOCOPY VARCHAR2,
100 x_msg_count OUT NOCOPY NUMBER
101 );
102
103 PROCEDURE populate_questions_order
104 (
105 p_Template_Id IN NUMBER,
106 p_Commit_Flag IN VARCHAR2,
107 p_Mode IN VARCHAR2,
108 x_Return_Status OUT NOCOPY VARCHAR2,
109 x_Msg_Count OUT NOCOPY NUMBER,
110 x_Msg_Data OUT NOCOPY VARCHAR2
111 );
112
113 FUNCTION Ok_To_Delete_Question
114 (
115 p_question_id IN NUMBER
116 ) RETURN VARCHAR2;
117
118 PROCEDURE create_production_publication
119 (
120 p_calling_mode IN VARCHAR2,
121 p_template_id IN NUMBER,
122 x_return_status OUT NOCOPY VARCHAR2,
123 x_msg_data OUT NOCOPY VARCHAR2,
124 x_msg_count OUT NOCOPY NUMBER
125 );
126
127 PROCEDURE validate_template_for_expert
128 (
129 p_api_version IN NUMBER,
130 p_init_msg_list IN VARCHAR2,
131 p_template_id IN NUMBER,
132 x_qa_result_tbl IN OUT NOCOPY OKC_TERMS_QA_GRP.qa_result_tbl_type,
133 x_return_status OUT NOCOPY VARCHAR2,
134 x_msg_count OUT NOCOPY NUMBER,
135 x_msg_data OUT NOCOPY VARCHAR2
136 );
137
138 PROCEDURE build_cz_xml_init_msg
139 (
140 p_api_version IN NUMBER,
141 p_init_msg_list IN VARCHAR2,
142 p_document_id IN NUMBER,
143 p_document_type IN VARCHAR2,
144 p_config_header_id IN NUMBER,
145 p_config_rev_nbr IN NUMBER,
146 p_template_id IN NUMBER,
147 x_cz_xml_init_msg OUT NOCOPY LONG,
148 x_return_status OUT NOCOPY VARCHAR2,
149 x_msg_count OUT NOCOPY NUMBER,
150 x_msg_data OUT NOCOPY VARCHAR2
151 );
152
153 PROCEDURE parse_cz_xml_terminate_msg
154 (
155 p_api_version IN NUMBER,
156 p_init_msg_list IN VARCHAR2,
157 p_cz_xml_terminate_msg IN LONG,
158 x_valid_config OUT NOCOPY VARCHAR2,
159 x_complete_config OUT NOCOPY VARCHAR2,
160 x_config_header_id OUT NOCOPY NUMBER,
161 x_config_rev_nbr OUT NOCOPY NUMBER,
162 x_return_status OUT NOCOPY VARCHAR2,
163 x_msg_count OUT NOCOPY NUMBER,
164 x_msg_data OUT NOCOPY VARCHAR2
165 );
166
167 PROCEDURE process_qa_result
168 (
169 p_api_version IN NUMBER,
170 p_init_msg_list IN VARCHAR2,
171 p_document_id IN NUMBER,
172 p_document_type IN VARCHAR2,
173 p_config_header_id IN NUMBER,
174 p_config_rev_nbr IN NUMBER,
175 x_qa_result_tbl IN OUT NOCOPY OKC_TERMS_QA_GRP.qa_result_tbl_type,
176 x_return_status OUT NOCOPY VARCHAR2,
177 x_msg_count OUT NOCOPY NUMBER,
178 x_msg_data OUT NOCOPY VARCHAR2
179 );
180
181 PROCEDURE get_expert_articles
182 (
183 p_api_version IN NUMBER,
184 p_init_msg_list IN VARCHAR2,
185 p_document_id IN NUMBER,
186 p_document_type IN VARCHAR2,
187 p_config_header_id IN NUMBER,
188 p_config_rev_nbr IN NUMBER,
189 x_expert_articles_tbl OUT NOCOPY expert_articles_tbl_type,
190 x_return_status OUT NOCOPY VARCHAR2,
191 x_msg_count OUT NOCOPY NUMBER,
192 x_msg_data OUT NOCOPY VARCHAR2
193 );
194
195 /*PROCEDURE contract_expert_bv
196 (
197 p_api_version IN NUMBER,
198 p_init_msg_list IN VARCHAR2,
199 p_document_id IN NUMBER,
200 p_document_type IN VARCHAR2,
201 p_bv_mode IN VARCHAR2,
202 p_sequence_id IN NUMBER,
203 x_qa_result_tbl IN OUT NOCOPY OKC_TERMS_QA_GRP.qa_result_tbl_type,
204 x_expert_articles_tbl OUT NOCOPY expert_articles_tbl_type,
205 x_return_status OUT NOCOPY VARCHAR2,
206 x_msg_count OUT NOCOPY NUMBER,
207 x_msg_data OUT NOCOPY VARCHAR2
208 );*/
209
210 PROCEDURE update_ce_config
211 (
212 p_api_version IN NUMBER,
213 p_init_msg_list IN VARCHAR2,
214 p_document_id IN NUMBER,
215 p_document_type IN VARCHAR2,
216 p_config_header_id IN NUMBER,
217 p_config_rev_nbr IN NUMBER,
218 p_doc_update_mode IN VARCHAR2,
219 x_count_articles_dropped OUT NOCOPY NUMBER ,
220 x_return_status OUT NOCOPY VARCHAR2,
221 x_msg_count OUT NOCOPY NUMBER,
222 x_msg_data OUT NOCOPY VARCHAR2
223 );
224
225 PROCEDURE update_config_id_rev_nbr
226 (
227 p_api_version IN NUMBER,
228 p_init_msg_list IN VARCHAR2,
229 p_document_id IN NUMBER,
230 p_document_type IN VARCHAR2,
231 p_config_header_id IN NUMBER,
232 p_config_rev_nbr IN NUMBER,
233 p_template_id IN NUMBER,
234 x_return_status OUT NOCOPY VARCHAR2,
235 x_msg_count OUT NOCOPY NUMBER,
236 x_msg_data OUT NOCOPY VARCHAR2
237 );
238
239 PROCEDURE get_article_details
240 (
241 p_api_version IN NUMBER,
242 p_init_msg_list IN VARCHAR2,
243 p_document_id IN NUMBER,
244 p_document_type IN VARCHAR2,
245 p_article_id IN NUMBER,
246 p_effectivity_date IN DATE,
247 x_article_id OUT NOCOPY NUMBER,
248 x_article_version_id OUT NOCOPY NUMBER,
249 x_doc_lib OUT NOCOPY VARCHAR2,
250 x_return_status OUT NOCOPY VARCHAR2,
251 x_msg_count OUT NOCOPY NUMBER,
252 x_msg_data OUT NOCOPY VARCHAR2
253 );
254
255 FUNCTION check_clause_exists (
256 p_rule_id IN NUMBER,
257 p_clause_id IN NUMBER)
258 RETURN VARCHAR2;
259
260 FUNCTION check_variable_exists (
261 p_rule_id IN NUMBER,
262 p_variable_code IN VARCHAR2)
263 RETURN VARCHAR2;
264
265 FUNCTION check_question_exists (
266 p_rule_id IN NUMBER,
267 p_question_id IN NUMBER)
268 RETURN VARCHAR2;
269
270 FUNCTION check_template_exists(
271 p_rule_id IN NUMBER,
272 p_template_id IN NUMBER)
273 RETURN VARCHAR2;
274
275 FUNCTION check_orgwide_rule_exists
276 RETURN VARCHAR2;
277
278 FUNCTION get_object_name (
279 p_object_name IN VARCHAR2,
280 p_object_code IN VARCHAR2)
281 RETURN VARCHAR2;
282
283 FUNCTION get_value_display (
284 p_object_value_type IN VARCHAR2,
285 p_object_value_code IN VARCHAR2,
286 p_object_value_set_id IN NUMBER,
287 p_validation_type IN VARCHAR2,
288 p_longlist_flag IN VARCHAR2,
289 p_mode IN VARCHAR2 DEFAULT NULL)
290 RETURN VARCHAR2;
291
292 FUNCTION get_value_desc (
293 p_object_value_type IN VARCHAR2,
294 p_object_value_code IN VARCHAR2,
295 p_object_value_set_id IN NUMBER,
296 p_validation_type IN VARCHAR2,
297 p_longlist_flag IN VARCHAR2)
298 RETURN VARCHAR2;
299
300 FUNCTION get_valueset_value (
301 p_object_value_set_id IN NUMBER,
302 p_object_value_code IN VARCHAR2,
303 p_validation_type IN VARCHAR2)
304 RETURN VARCHAR2 ;
305
306 FUNCTION get_valueset_value_desc (
307 p_object_value_set_id IN NUMBER,
308 p_object_value_code IN VARCHAR2,
309 p_validation_type IN VARCHAR2)
310 RETURN VARCHAR2;
311
312 FUNCTION get_value_desc (
313 p_rule_condition_id IN NUMBER,
314 p_object_value_code IN VARCHAR2)
315 RETURN VARCHAR2;
316
317 FUNCTION get_value_display (
318 p_rule_condition_id IN NUMBER,
319 p_object_value_code IN VARCHAR2)
320 RETURN VARCHAR2 ;
321
322 -- Added for Policy Deviations Project
323 FUNCTION get_concat_condition_values (
324 p_rule_condition_id IN NUMBER)
325 RETURN VARCHAR2;
326
327 -- Bug#4728299 Added for Policy Deviations Project
328 FUNCTION get_deviation_document_value (
329 p_rule_id IN NUMBER,
330 p_object_type IN VARCHAR2,
331 p_object_code IN VARCHAR2,
332 p_sequence_id IN VARCHAR2,
333 p_value_set_id IN NUMBER,
334 p_object_value_type IN VARCHAR2,
335 p_object_value_code IN VARCHAR2,
336 p_line_number IN VARCHAR2)
337 RETURN VARCHAR2;
338
339 PROCEDURE publish_rule_with_no_tmpl
340 (
341 p_calling_mode IN VARCHAR2,
342 x_return_status OUT NOCOPY VARCHAR2,
343 x_msg_data OUT NOCOPY VARCHAR2,
344 x_msg_count OUT NOCOPY NUMBER
345 );
346
347 PROCEDURE check_rules_validity
348 (
349 p_qa_mode IN VARCHAR2,
350 p_template_id IN NUMBER,
351 x_sequence_id OUT NOCOPY NUMBER,
355 x_msg_count OUT NOCOPY NUMBER
352 x_qa_status OUT NOCOPY VARCHAR2,
353 x_return_status OUT NOCOPY VARCHAR2,
354 x_msg_data OUT NOCOPY VARCHAR2,
356 );
357
358 FUNCTION is_valid (
359 p_object_id IN NUMBER,
360 p_object_type IN VARCHAR2)
361 RETURN VARCHAR2 ;
362
363 FUNCTION is_value_valid (
364 p_object_code IN VARCHAR2,
365 p_rule_condition_id IN NUMBER)
366 RETURN VARCHAR2 ;
367
368 FUNCTION get_message(p_appl_name IN VARCHAR2,
369 p_msg_name IN VARCHAR2)
370 RETURN VARCHAR2;
371
372 PROCEDURE get_publication_id
373 (
374 p_api_version IN NUMBER,
375 p_init_msg_list IN VARCHAR2,
376 p_template_id IN NUMBER,
377 x_publication_id OUT NOCOPY NUMBER,
378 x_return_status OUT NOCOPY VARCHAR2,
379 x_msg_count OUT NOCOPY NUMBER,
380 x_msg_data OUT NOCOPY VARCHAR2
381 );
382
383 FUNCTION ok_to_delete_clause
384 (
385 p_article_id IN NUMBER
386 ) RETURN VARCHAR2;
387
388 PROCEDURE get_qa_code_detail
389 (
390 p_document_type IN VARCHAR2,
391 p_qa_code IN VARCHAR2,
392 x_perform_qa OUT NOCOPY VARCHAR2,
393 x_qa_name OUT NOCOPY VARCHAR2,
394 x_severity_flag OUT NOCOPY VARCHAR2,
395 x_return_status OUT NOCOPY VARCHAR2
396 );
397
398 PROCEDURE enable_expert_button
399 (
400 p_api_version IN NUMBER,
401 p_init_msg_list IN VARCHAR2,
402 p_template_id IN NUMBER,
403 p_document_id IN NUMBER,
404 p_document_type IN VARCHAR2,
405 x_enable_expert_button OUT NOCOPY VARCHAR2, -- FND_API.G_FALSE or G_TRUE
406 x_return_status OUT NOCOPY VARCHAR2,
407 x_msg_count OUT NOCOPY NUMBER,
408 x_msg_data OUT NOCOPY VARCHAR2
409 );
410
411
412
413 --Under development - Arun
414 PROCEDURE contract_expert_bv
415 (
416 p_api_version IN NUMBER,
417 p_init_msg_list IN VARCHAR2,
418 p_document_id IN NUMBER,
419 p_document_type IN VARCHAR2,
420 p_bv_mode IN VARCHAR2,
421 p_sequence_id IN NUMBER DEFAULT NULL,
422 x_qa_result_tbl IN OUT NOCOPY OKC_TERMS_QA_GRP.qa_result_tbl_type,
423 x_expert_articles_tbl OUT NOCOPY expert_articles_tbl_type,
424 x_return_status OUT NOCOPY VARCHAR2,
425 x_msg_count OUT NOCOPY NUMBER,
426 x_msg_data OUT NOCOPY VARCHAR2
427 );
428
429 PROCEDURE get_expert_selections(
430 p_api_version IN NUMBER,
431 p_init_msg_list IN VARCHAR2,
432 p_document_id IN NUMBER,
433 p_document_type IN VARCHAR2,
434 p_config_header_id IN NUMBER,
435 p_config_rev_nbr IN NUMBER,
436 x_expert_clauses_tbl OUT NOCOPY expert_articles_tbl_type,
437 x_expert_deviations_tbl OUT NOCOPY dev_rule_tbl_type,
438 x_return_status OUT NOCOPY VARCHAR2,
439 x_msg_count OUT NOCOPY NUMBER,
440 x_msg_data OUT NOCOPY VARCHAR2);
441
442 PROCEDURE get_rule_details(
443 p_api_version IN NUMBER,
444 p_init_msg_list IN VARCHAR2,
445 p_dev_rule_tbl IN dev_rule_tbl_type,
446 x_dev_rule_questions_tbl OUT NOCOPY dev_rule_questions_tbl_type,
447 x_dev_rule_variables_tbl OUT NOCOPY dev_rule_variables_tbl_type,
448 x_return_status OUT NOCOPY VARCHAR2,
449 x_msg_count OUT NOCOPY NUMBER,
450 x_msg_data OUT NOCOPY VARCHAR2);
451
452 PROCEDURE get_rule_variable_values(
453 p_api_version IN NUMBER,
454 p_init_msg_list IN VARCHAR2,
455 p_sequence_id IN NUMBER,
456 p_dev_rule_variables_tbl IN dev_rule_variables_tbl_type,
457 x_dev_rule_var_values_tbl OUT NOCOPY dev_rule_var_values_tbl_type,
458 x_return_status OUT NOCOPY VARCHAR2,
459 x_msg_count OUT NOCOPY NUMBER,
460 x_msg_data OUT NOCOPY VARCHAR2);
461
462 PROCEDURE get_rule_question_values(
463 p_api_version IN NUMBER,
464 p_init_msg_list IN VARCHAR2,
465 p_config_header_id IN NUMBER,
466 p_config_rev_nbr IN NUMBER,
467 p_dev_rule_questions_tbl IN dev_rule_questions_tbl_type,
468 x_dev_rule_qst_values_tbl OUT NOCOPY dev_rule_qst_values_tbl_type,
469 x_return_status OUT NOCOPY VARCHAR2,
470 x_msg_count OUT NOCOPY NUMBER,
471 x_msg_data OUT NOCOPY VARCHAR2);
472
473 PROCEDURE get_article_details(
474 p_api_version IN NUMBER,
475 p_init_msg_list IN VARCHAR2,
476 p_document_id IN NUMBER,
477 p_document_type IN VARCHAR2,
478 p_article_id IN NUMBER,
479 p_effectivity_date IN DATE,
480 x_article_id OUT NOCOPY NUMBER,
481 x_article_version_id OUT NOCOPY NUMBER,
482 x_article_title OUT NOCOPY VARCHAR2,
483 x_article_description OUT NOCOPY VARCHAR2,
484 x_doc_lib OUT NOCOPY VARCHAR2,
485 x_scn_heading OUT NOCOPY VARCHAR2,
486 x_return_status OUT NOCOPY VARCHAR2,
487 x_msg_count OUT NOCOPY NUMBER,
488 x_msg_data OUT NOCOPY VARCHAR2);
489
490 PROCEDURE populate_terms_deviations_tbl(
491 p_api_version IN NUMBER,
492 p_init_msg_list IN VARCHAR2,
493 p_document_id IN NUMBER,
494 p_document_type IN VARCHAR2,
495 p_sequence_id IN NUMBER,
496 p_config_header_id IN NUMBER,
497 p_config_rev_nbr IN NUMBER,
498 p_rule_qst_values_tbl IN dev_rule_qst_values_tbl_type,
499 p_rule_var_values_tbl IN dev_rule_var_values_tbl_type,
500 p_clause_tbl IN expert_articles_tbl_type,
501 p_mode IN VARCHAR2,
502 x_return_status OUT NOCOPY VARCHAR2,
503 x_msg_count OUT NOCOPY NUMBER,
504 x_msg_data OUT NOCOPY VARCHAR2);
505
506 PROCEDURE get_expert_results(
507 p_api_version IN NUMBER,
508 p_init_msg_list IN VARCHAR2,
509 p_document_id IN NUMBER,
510 p_document_type IN VARCHAR2,
511 p_config_header_id IN NUMBER,
512 p_config_rev_nbr IN NUMBER,
513 p_mode IN VARCHAR2,
514 p_sequence_id IN OUT NOCOPY NUMBER,
515 x_return_status OUT NOCOPY VARCHAR2,
516 x_msg_count OUT NOCOPY NUMBER,
517 x_msg_data OUT NOCOPY VARCHAR2);
518
519 -- Rajendra
520 PROCEDURE is_template_applied (
521 p_api_version IN NUMBER,
522 p_init_msg_list IN VARCHAR2,
523 p_document_type IN VARCHAR2,
524 p_document_id IN NUMBER,
525 p_template_id IN NUMBER,
526 x_template_applied_yn OUT NOCOPY VARCHAR2,
527 x_return_status OUT NOCOPY VARCHAR2,
528 x_msg_count OUT NOCOPY NUMBER,
529 x_msg_data OUT NOCOPY VARCHAR2
530 );
531
532 PROCEDURE get_current_config_dtls (
533 p_api_version IN NUMBER,
534 p_init_msg_list IN VARCHAR2,
535 p_document_type IN VARCHAR2,
536 p_document_id IN NUMBER,
537 p_template_id IN NUMBER,
538 x_expert_enabled_yn OUT NOCOPY VARCHAR2,
539 x_config_header_id OUT NOCOPY NUMBER,
540 x_config_rev_nbr OUT NOCOPY NUMBER,
541 x_return_status OUT NOCOPY VARCHAR2,
542 x_msg_count OUT NOCOPY NUMBER,
543 x_msg_data OUT NOCOPY VARCHAR2
544 );
545
546 FUNCTION check_rule_type_has_questions (
547 p_template_id IN NUMBER,
548 p_rule_type IN VARCHAR2)
549 RETURN VARCHAR2;
550
551 PROCEDURE contract_expert_bv(
552 p_api_version IN NUMBER,
553 p_init_msg_list IN VARCHAR2,
554 p_document_id IN NUMBER,
555 p_document_type IN VARCHAR2,
556 p_bv_mode IN VARCHAR2 ,
557 x_return_status OUT NOCOPY VARCHAR2,
558 x_msg_count OUT NOCOPY NUMBER,
559 x_msg_data OUT NOCOPY VARCHAR2);
560
561 FUNCTION is_config_complete(
562 p_document_type IN VARCHAR2,
563 p_document_id IN NUMBER
564 ) RETURN VARCHAR2;
565
566 FUNCTION has_unanswered_questions(
567 p_document_type IN VARCHAR2,
568 p_document_id IN NUMBER,
569 p_rule_type IN VARCHAR2
570 ) RETURN VARCHAR2;
571
572
573
574 END OKC_XPRT_UTIL_PVT ;
[Home] [Help]