1 PACKAGE BODY okc_code_hook AS
2 /* $Header: OKCCCHKB.pls 120.13.12020000.2 2012/07/16 16:07:17 harchand ship $ */
3
4 /* Global constants*/
5 g_pkg_name CONSTANT VARCHAR2 (200) := 'OKC_XPRT_CODE_HOOK';
6 g_app_name CONSTANT VARCHAR2 (3) := okc_api.g_app_name;
7 g_module CONSTANT VARCHAR2 (250)
8 := 'okc.plsql.' || g_pkg_name || '.';
9 g_false CONSTANT VARCHAR2 (1) := fnd_api.g_false;
10 g_true CONSTANT VARCHAR2 (1) := fnd_api.g_true;
11 g_okc CONSTANT VARCHAR2 (3) := 'OKC';
12 g_ret_sts_success CONSTANT VARCHAR2 (1) := fnd_api.g_ret_sts_success;
13 g_ret_sts_error CONSTANT VARCHAR2 (1) := fnd_api.g_ret_sts_error;
14 g_ret_sts_unexp_error CONSTANT VARCHAR2 (1)
15 := fnd_api.g_ret_sts_unexp_error;
16 g_unexpected_error CONSTANT VARCHAR2 (200) := 'OKC_UNEXPECTED_ERROR';
17 g_sqlerrm_token CONSTANT VARCHAR2 (200) := 'ERROR_MESSAGE';
18 g_sqlcode_token CONSTANT VARCHAR2 (200) := 'ERROR_CODE';
19
20
21 G_DBG_LEVEL NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
22 G_PROC_LEVEL NUMBER := FND_LOG.LEVEL_PROCEDURE;
23 G_EXCP_LEVEL NUMBER := FND_LOG.LEVEL_EXCEPTION;
24
25
26 /* PROCEDURE
27 GET_MULTIVAL_UDV_FOR_XPRT This routine is used to get the multiple values for variables in expert.
28
29 INPUT PARAMETERS
30
31 p_doc_type Document Type of Contract(eg: PO_STANDARD)
32 p_doc_id Document_id of contract.
33 p_udf_var_code Variable Code
34
35
36 RETURN VALUE
37 X_RETURN_STATUS: Standard out variable to return the final API execution status.
38 X_MSG_COUNT : Standard out variable to return the number of messages.
39 X_MSG_DATA : Standard out variable to return the message string.
40 x_order_by_column Table which return variable-code and variable value of every variable. If multi values are to be returned for
41 x_hook_used 0 Hook has not been used
42 -1 Error in Hook
43 Any other value Hook is used
44 NOTE: Use all OUT and IN OUT parameter with NOCOPY option.
45
46 This procedure 'GET_MULTIVAL_UDV_FOR_XPRT' will be called for every user defined variable with procedure. Variable code is given as i/p for this procedure so that user can write logic if needed.
47 Users need to code for fetching the desired values for their variables here. This is required only for variables which have to return multiple values during expert.
48 The x_cust_udf_var_mul_val_tbl should have variable_code and variable_value_id as output.
49
50 ***********************************************************************************************************
51 Eg: x_cust_udf_var_tbl_values(j).variable_code := 'VARIABLE_CODE';
52 x_cust_udf_var_tbl_values(j).variable_value_id := <VARIABLE_VALUE>
53 ***********************************************************************************************************
54
55 x_hook_code needs to be populated to any value other than '0' for every variable. This value is responsible to check from which pl/sql the values need to be fetched.
56 */
57 PROCEDURE get_multival_udv_for_xprt (
58 p_api_version IN NUMBER,
59 p_init_msg_list IN VARCHAR2,
60 p_doc_type IN VARCHAR2,
61 p_doc_id IN NUMBER,
62 p_udf_var_code IN VARCHAR2,
63 x_return_status OUT NOCOPY VARCHAR2,
64 x_msg_count OUT NOCOPY NUMBER,
65 x_msg_data OUT NOCOPY VARCHAR2,
66 x_cust_udf_var_mul_val_tbl OUT NOCOPY okc_xprt_xrule_values_pvt.udf_var_value_tbl_type,
67 x_hook_used OUT NOCOPY NUMBER
68 )
69 IS
70 BEGIN
71 x_hook_used := 0;
72 EXCEPTION
73 WHEN OTHERS
74 THEN
75 x_hook_used := -1;
76 END get_multival_udv_for_xprt;
77
78 /* PROCEDURE
79 GET_XPRT_CLAUSE_ORDER This routine is used to get the column on which the clauses have to be ordered after the Contract Expert is run
80
81 INPUT PARAMETERS
82
83 None
84
85
86 RETURN VALUE
87 X_RETURN_STATUS: Standard out variable to return the final API execution status.
88 X_MSG_COUNT : Standard out variable to return the number of messages.
89 X_MSG_DATA : Standard out variable to return the message string.
90 x_order_by_column Column on which the clauses have to be ordered
91 x_hook_used 0 Hook has not been used
92 1 Hook is used
93 NOTE: Use all OUT and IN OUT parameter with NOCOPY option.
94
95 This procedure 'GET_XPRT_CLAUSE_ORDER' will be called from OKC_TERMS_MULTIGRP_REC.sync_doc_with_expert when the contract expert is run.
96 Currently ordering based on Clause Number is only supported. x_order_by_column is assigned to 'CLAUSE_NUMBER'.
97 x_hook_used needs to be populated to 1 if the ordering based on Clause Number is required.
98 */
99 PROCEDURE get_xprt_clause_order (
100 x_return_status IN OUT NOCOPY VARCHAR2,
101 x_msg_count OUT NOCOPY NUMBER,
102 x_msg_data OUT NOCOPY VARCHAR2,
103 x_order_by_column OUT NOCOPY VARCHAR2,
104 x_hook_used OUT NOCOPY NUMBER
105 )
106 IS
107 BEGIN
108 x_hook_used := 0;
109 x_order_by_column := 'CLAUSE_NUMBER';
110 EXCEPTION
111 WHEN OTHERS
112 THEN
113 x_hook_used := -1;
114 END get_xprt_clause_order;
115
116 /* FUNCTION
117 IS_NOT_PROVISIONAL_SECTION This routine is used to find out if a section is a provisional section or not
118 If it returns true, then it is not a provisional section.
119 Or else, it is a provisional section
120 INPUT PARAMETERS
121
122 None
123
124
125 RETURN VALUE
126 p_section_heading IN VARCHAR2, Section that has to be checked if it is a provisional section or not
127 */
128 FUNCTION is_not_provisional_section (p_section_heading IN VARCHAR2)
129 RETURN VARCHAR2
130 IS
131 x_hook_used NUMBER;
132 BEGIN
133 x_hook_used := 1;
134
135 IF x_hook_used = 1
136 THEN
137 --Copy the following if block with the section names within the quotes.
138 --One IF block is required for each section name
139 --Note that the section name is case-sensitive
140 IF p_section_heading = ' '
141 THEN
142 RETURN fnd_api.g_false;
143 END IF;
144 END IF;
145
146 RETURN fnd_api.g_true;
147 EXCEPTION
148 WHEN OTHERS
149 THEN
150 x_hook_used := -1;
151 END is_not_provisional_section;
152
153 /* FUNCTION
154 IS_NEW_KFF_ITEM_SEG_ENABLED This routine is used to decide on whether the new Item KFF segment setup should be considered during Contract Expert rule execution.
155 INPUT PARAMETERS
156 None
157
158 RETURN VALUE
159 BOOLEAN : TRUE if the new item seg setup is enabled, FALSE otherwise
160 */
161 FUNCTION is_new_kff_item_seg_enabled
162 RETURN BOOLEAN
163 IS
164 BEGIN
165 RETURN FALSE;
166 -- RETURN TRUE;
167 END;
168
169
170
171
172 /*
173 * Enable this procedure when you are using Mandatory and RWA columns on Rule Outcomes
174 * and you want these flags to be synced with the document.
175 *
176 * Added by serukull
177 *
178 */
179
180
181 PROCEDURE sync_rwa_with_document (
182 p_api_version IN NUMBER,
183 p_init_msg_list IN VARCHAR2,
184 p_doc_type IN VARCHAR2,
185 p_doc_id IN NUMBER,
186 p_article_id_tbl IN okc_terms_multirec_grp.article_id_tbl_type,
187 x_return_status IN OUT NOCOPY VARCHAR2,
188 x_msg_count OUT NOCOPY NUMBER,
189 x_msg_data OUT NOCOPY VARCHAR2
190 )
191 IS
192 l_template_id NUMBER;
193 l_intent VARCHAR2 (1);
194 l_org_id NUMBER;
195
196
197 l_tmp_lvl_mandatory_flag varchar2(1);
198
199 TYPE outcome_rec_type IS RECORD (clause_id NUMBER,mandatory_yn OKC_XPRT_RULE_OUTCOMES.mandatory_yn%TYPE
200 , mandatory_rwa OKC_XPRT_RULE_OUTCOMES.mandatory_rwa%type);
201
202 TYPE outcome_tbl_type IS TABLE OF outcome_rec_type INDEX BY PLS_INTEGER;
203
204 TYPE clause_id_tbl_type IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
205
206 l_outcome_tbl outcome_tbl_type;
207 l_clause_id_tbl clause_id_tbl_type;
208 BEGIN
209
210 /* -- Comment Start -- Comment this line if you want sync RWA
211
212 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
213 FND_LOG.STRING(G_PROC_LEVEL,
214 G_PKG_NAME, '100: sync_rwa_with_document Start');
215 END IF;
216
217 -- Get the template id from document usages
218 SELECT template_id
219 INTO l_template_id
220 FROM okc_template_usages
221 WHERE document_type = p_doc_type AND document_id = p_doc_id;
222
223 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
224 FND_LOG.STRING(G_PROC_LEVEL,
225 G_PKG_NAME, '110: sync_rwa_with_document : After getting Template_id '|| l_template_id);
226 END IF;
227
228 -- Get the intent and org_id from the template
229 SELECT intent, org_id, xprt_clause_mandatory_flag
230
231 INTO l_intent, l_org_id, l_tmp_lvl_mandatory_flag
232 FROM okc.okc_terms_templates_all
233
234 WHERE template_id = l_template_id;
235
236 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
237 FND_LOG.STRING(G_PROC_LEVEL,
238 G_PKG_NAME, '120: sync_rwa_with_document : After getting Intent as '|| l_intent ||' and Org id as' ||l_org_id );
239 END IF;
240
241
242 -- Get the clause outcomes of rules attached to the template
243 SELECT outcome.object_value_id,outcome.mandatory_yn, outcome.mandatory_rwa
244 bulk COLLECT INTO l_outcome_tbl
245 FROM (SELECT rule.rule_id rule_id
246 FROM okc_xprt_rule_hdrs rule,
247 okc_xprt_template_rules trule,
248 fnd_lookups lkup,
249 fnd_lookups ruletypelkup
250 WHERE rule.rule_id = trule.rule_id
251 AND trule.template_id = l_template_id
252 AND rule.status_code = lkup.lookup_code
253 AND lkup.lookup_type = 'OKC_XPRT_RULE_STATUS'
254 AND rule.status_code = 'ACTIVE'
255 AND ruletypelkup.lookup_type = 'OKC_XPRT_RULE_TYPE'
256 AND rule.rule_type = ruletypelkup.lookup_code
257 UNION ALL
258 -- Get the org wide rules
259 SELECT rule.rule_id rule_id
260 FROM okc_xprt_rule_hdrs rule,
261 fnd_lookups lkup,
262 fnd_lookups ruletypelkup
263 WHERE rule.status_code = lkup.lookup_code
264 AND lkup.lookup_type = 'OKC_XPRT_RULE_STATUS'
265 AND rule.status_code = 'ACTIVE'
266 AND ruletypelkup.lookup_type = 'OKC_XPRT_RULE_TYPE'
267 AND rule.rule_type = ruletypelkup.lookup_code
268 AND org_id = l_org_id
269 AND intent = l_intent
270 AND org_wide_flag = 'Y') rule,
271 okc_xprt_rule_outcomes outcome
272 WHERE outcome.rule_id = rule.rule_id
273 AND outcome.object_type = 'CLAUSE';
274
275 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
276 FND_LOG.STRING(G_PROC_LEVEL,
277 G_PKG_NAME, '130: sync_rwa_with_document : Outcome clauses '|| l_outcome_tbl.count);
278 END IF;
279
280 -- Create structure similar to Hasmap from the input articles.
281 IF p_article_id_tbl.COUNT > 0 THEN
282 FOR i IN p_article_id_tbl.first..p_article_id_tbl.last LOOP
283 l_clause_id_tbl(p_article_id_tbl(i)) := 1;
284 END LOOP;
285
286 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
287 FND_LOG.STRING(G_PROC_LEVEL,
288 G_PKG_NAME, '140: sync_rwa_with_document : Created Hash');
289 END IF;
290
291 END IF;
292
293 -- Delete the outcome record from the table structure, if it does not exists in the hasmap
294 FOR i IN 1.. l_outcome_tbl.Count
295 LOOP
296 IF l_clause_id_tbl.EXISTS(l_outcome_tbl(i).clause_id) = FALSE THEN
297 l_outcome_tbl.DELETE(i);
298 END IF;
299 END LOOP;
300
301 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
302 FND_LOG.STRING(G_PROC_LEVEL,
303 G_PKG_NAME, '140: sync_rwa_with_document : After setting Outcome table');
304 END IF;
305
306 -- Execute the update statement
307 -- Oracle Database 10g Release 1 (10.1.0.2)
308 FORALL i IN indices OF l_outcome_tbl
309 UPDATE okc_k_articles_b
310 SET mandatory_yn = Nvl(l_outcome_tbl(i).mandatory_yn,l_tmp_lvl_mandatory_flag)
311 ,mandatory_rwa = l_outcome_tbl(i).mandatory_rwa
312 WHERE document_type = p_doc_type
313 AND document_id = p_doc_id
314 AND sav_sae_id = l_outcome_tbl(i).clause_id ;
315
316 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
317 FND_LOG.STRING(G_PROC_LEVEL,
318 G_PKG_NAME, '140: sync_rwa_with_document : After updating okc_k_articles_b table');
319 END IF;
320
321 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
322 FND_LOG.STRING(G_PROC_LEVEL,
323 G_PKG_NAME, '100: sync_rwa_with_document End');
324 END IF;
325
326 */ -- Comment End -- Comment this line if you want sync RWA
327
328 x_return_status := g_ret_sts_success;
329
330
331 EXCEPTION
332 WHEN OTHERS
333 THEN
334 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
335 FND_LOG.STRING(G_PROC_LEVEL,
336 G_PKG_NAME, '100: sync_rwa_with_document Exception');
337 END IF;
338
339 RAISE;
340 END sync_rwa_with_document;
341
342
343 /*
344 * Enable this procedure if custom QA checks has to be added for the Contract Expert Rules Activation
345 * Parameters : INPUT : p_rule_id - Rule corresponding to which the QA check will be run
346 * p_sequence_id - for future use. No significant need of using this parameter as of now
347 * OUTPUT: x_hook_used - 0 - not used
348 * 1 - used
349 * x_qa_errors_tbl - This table should be populated with the QA check message details.
350 * More than one QA check can be written in this package and in this case, one row has to be entered in this table for each QA check message.
351 */
352
353
354
355 PROCEDURE rules_qa_check
356 (
357 p_rule_id IN NUMBER,
358 p_sequence_id IN NUMBER,
359 x_hook_used OUT NOCOPY NUMBER,
360 x_qa_errors_tbl OUT NOCOPY l_qa_errors_table,
361 x_return_status OUT NOCOPY VARCHAR2,
362 x_msg_count OUT NOCOPY NUMBER,
363 x_msg_data OUT NOCOPY VARCHAR2
364 )
365 IS
366
367 l_rule_id NUMBER :=0;
368
369 BEGIN
370
371 x_return_status := 'S';
372 x_hook_used := 0; -- Assign 1 to this if the code in this procedure should be considered by the standard flow
373
374 EXCEPTION
375 WHEN OTHERS THEN
376 x_hook_used := -1;
377 x_return_status := 'E';
378 END rules_qa_check;
379
380
381 PROCEDURE sort_clauses(
382 p_doc_type IN VARCHAR2,
383 p_doc_id IN NUMBER,
384 x_return_status OUT NOCOPY VARCHAR2,
385 x_msg_count OUT NOCOPY NUMBER,
386 x_msg_data OUT NOCOPY VARCHAR2,
387 x_cont_art_tbl OUT NOCOPY cont_art_sort_tbl
388 ) IS
389
390 l_api_version CONSTANT NUMBER := 1;
391 l_api_name CONSTANT VARCHAR2(30) := 'sort_clauses';
392
393 cursor c_sort_articles is
394 SELECT id, scn_id from
395 (SELECT id,scn_id,article_number,
396 Decode(InStr(article_number,'.'),0,Decode(InStr(article_number,' '),0,Decode(regexp_instr(article_number,'[a-zA-Z]'),0,article_number,NULL),SubStr(article_number,1, InStr(article_number,' ')-1)),
397 SubStr(article_number,1, InStr(article_number,'.')-1)) col1,
398 Decode(InStr(article_number,'-'),0,Decode(InStr(article_number,' '),0,Decode(InStr(article_number,'.'),0,0,SubStr(article_number,InStr(article_number,'.')+1)),decode(instr(article_number,'.'),0,0,
399 SubStr(article_number,InStr(article_number,'.')+1,InStr(article_number,' ')-InStr(article_number,'.')))),SubStr(article_number,InStr(article_number,'.')+1, InStr(article_number,'-')-InStr(article_number,'.')-1)) col2,
400 Decode(InStr(article_number,' '),0,Decode(InStr(article_number,'-'),0,0,SubStr(article_number,InStr(article_number,'-')+1)),
401 Decode(InStr(article_number,'-'),0,0,SubStr(article_number,InStr(article_number,'-')+1, InStr(article_number,' ')-InStr(article_number,'-')-1))) col3,
402 Decode(InStr(article_number,' '),0,' ',SubStr(article_number,InStr(article_number,' ')+1)) col4
403 FROM okc_k_articles_b,okc_articles_all
404 WHERE document_type=p_doc_type AND
405 document_id=p_doc_id AND
406 sav_sae_id=article_id
407 ORDER BY scn_id,To_Number(col1),To_Number(col2),To_Number(col3),col4);
408
409
410 cursor c_sort_aplhanumeric is
411 SELECT id,scn_id
412 FROM okc_k_articles_b,okc_articles_all
413 WHERE document_type=p_doc_type AND
414 document_id=p_doc_id AND
415 sav_sae_id=article_id
416 ORDER BY scn_id,article_number;
417
418 BEGIN
419 begin
420 OPEN c_sort_articles;
421 FETCH c_sort_articles BULK COLLECT INTO x_cont_art_tbl;
422 CLOSE c_sort_articles;
423 exception
424 when others then
425 if c_sort_articles%ISOPEN then
426 close c_sort_articles;
427 end if;
428 OPEN c_sort_aplhanumeric;
429 FETCH c_sort_aplhanumeric BULK COLLECT INTO x_cont_art_tbl;
430 CLOSE c_sort_aplhanumeric;
431 end;
432 EXCEPTION
433 WHEN OTHERS THEN
434 x_return_status := 'E';
435 END sort_clauses;
436
437 END okc_code_hook;
[Home] [Help]