[Home] [Help]
PACKAGE BODY: APPS.OE_PC_CONSTRAINTS_ADMIN_PVT
Source
1 PACKAGE BODY Oe_PC_Constraints_Admin_Pvt as
2 /* $Header: OEXVPCAB.pls 120.1 2007/11/26 09:32:16 vbkapoor ship $ */
3
4 -- Global constant holding the package name
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'Oe_PC_Constraints_Admin_Pvt';
7
8 -- Record/Table used to cache conditions for a constraint
9 TYPE ConstraintRule_Rec_Type IS RECORD
10 (
11 application_id number,
12 entity_short_name varchar2(15),
13 condition_id number,
14 group_number number,
15 modifier_flag varchar2(1),
16 validation_application_id number,
17 validation_entity_short_name varchar2(15),
18 validation_tmplt_short_name varchar2(8),
19 record_set_short_name varchar2(8),
20 scope_op varchar2(3),
21 validation_pkg varchar2(30),
22 validation_proc varchar2(30),
23 validation_tmplt_id number,
24 record_set_id number,
25 validation_entity_id number,
26 entity_id number
27 );
28
29 TYPE ConstraintRule_Tbl_Type IS TABLE OF ConstraintRule_Rec_Type
30 INDEX BY BINARY_INTEGER;
31
32 G_ConstraintRuleTbl ConstraintRule_Tbl_Type;
33
34 -- Constant to indicate maximum number of condition records that
35 -- can be cached per constraint
36 G_MAX_CONDITIONS NUMBER := 100;
37
38 -- Record/Table used to cache results of a validated condition
39 TYPE Result_Rec_Type IS RECORD
40 (
41 validation_tmplt_id number,
42 record_set_id number,
43 validation_entity_id number,
44 entity_id number,
45 scope_op varchar2(3),
46 result number
47 );
48
49 TYPE Result_Tbl_Type IS TABLE OF Result_Rec_Type
50 INDEX BY BINARY_INTEGER;
51
52 G_RESULT_CACHE Result_Tbl_Type;
53
54 -- Record/Table Type to cache constraints
55 TYPE Constraint_Cache_Rec_Type IS RECORD
56 ( ENTITY_ID NUMBER
57 , COLUMN_NAME VARCHAR2(30)
58 , CONSTRAINT_ID NUMBER
59 , ON_OPERATION_ACTION NUMBER
60 );
61
62 TYPE Constraint_Cache_TBL_Type IS TABLE OF Constraint_Cache_Rec_Type
63 INDEX BY BINARY_INTEGER;
64
65 G_CHECK_ON_INSERT_CACHE Constraint_Cache_TBL_Type;
66
67 -- Maximum check on insert constraints that can be cached
68 -- per entity
69 G_MAX_CONSTRAINTS CONSTANT NUMBER := 1000;
70
71 -- ** For other validations, modify the value_string
72 -- ** to make them more intelligent to datatype conversions, string padding (strings
73 -- ** that may contain single quotes and double quotes etc.
74 FUNCTION Convert_Value_String
75 ( value_string IN VARCHAR2
76 , data_type IN VARCHAR2)
77 RETURN VARCHAR2
78 IS
79 l_value_string VARCHAR2(300) := NULL;
80 from_char NUMBER;
81 found_char NUMBER;
82 BEGIN
83
84 IF value_string IS NOT NULL THEN
85 IF data_type = 'VARCHAR2' THEN
86 l_value_string := value_string;
87 from_char := 1;
88 -- Padding for single quotes
89 WHILE TRUE LOOP
90 SELECT INSTR(l_value_string,'''',1,from_char)
91 INTO found_char
92 FROM DUAL;
93 EXIT WHEN (found_char=0);
94 l_value_string := substr(l_value_string,1,found_char)||
95 ''''||substr(l_value_string,found_char+1,
96 length(l_value_string));
97 from_char := found_char+2;
98 END LOOP;
99 l_value_string := ''''||l_value_string||'''';
100 ELSIF (data_type = 'DATE') THEN
101 l_value_string := 'TO_DATE('''||value_string||''',''RRRR/MM/DD HH24:MI:SS'')';
102 ELSE
103 l_value_string := value_string;
104 END IF;
105 END IF;
106
107 RETURN l_value_string;
108 END;
109
110
111 ------------------------------------------------------------------
112 FUNCTION Concatenate_VTMPLTCOL_SQL
113 ( p_vc_sql IN LONG
114 , p_validation_tmplt_id IN NUMBER
115 , p_use_where IN BOOLEAN := TRUE
116 )
117 RETURN VARCHAR2 IS
118 ------------------------------------------------------------------
119 -- Validation Template Columns selected such that state attribute columns
120 -- are selected first. This way, if there is even one condition for state
121 -- attribute, that will be selected first and this will help in appending
122 -- the database object name to the FROM cursor.
123 CURSOR C_VTMPLTCOLS IS
124 SELECT vc.column_name, attr.data_type, vc.validation_op, vc.value_string
125 , decode(attr.state_attribute_flag,NULL,1,'Y',0,'N',1) state_attribute
126 FROM oe_pc_vtmplt_cols vc,
127 oe_pc_vtmplts vt,
128 oe_pc_attributes_v attr
129 WHERE vc.validation_tmplt_id = p_validation_tmplt_id
130 AND vc.validation_tmplt_id = vt.validation_tmplt_id
131 AND attr.entity_id = vt.entity_id
132 AND attr.column_name = vc.column_name
133 ORDER BY state_attribute;
134 l_value_string VARCHAR2(240);
135 l_vc_sql LONG := p_vc_sql;
136 i NUMBER := 1;
137 BEGIN
138
139 for val_rec in C_VTMPLTCOLS loop
140
141 l_value_string := Convert_Value_String(val_rec.value_string, val_rec.data_type);
142
143 if (i = 1) and (p_use_where) then
144 -- where clause only for the first one
145 l_vc_sql := l_vc_sql || ' WHERE a.' || val_rec.column_name ||
146 ' ' ||val_rec.validation_op || ' ' || l_value_string || OE_PC_GLOBALS.NEWLINE;
147 else
148 l_vc_sql := l_vc_sql || ' AND a.' || val_rec.column_name ||
149 ' ' ||val_rec.validation_op || ' ' || l_value_string || OE_PC_GLOBALS.NEWLINE;
150 end if;
151 i := i + 1;
152
153 end loop;
154
155 RETURN l_vc_sql;
156
157 END Concatenate_VTMPLTCOL_SQL;
158
159
160 ------------------------------------------------------------------
161 FUNCTION Concatenate_VTMPLTWF_SQL
162 ( p_vc_sql IN LONG
163 , p_wf_item_type IN VARCHAR2
164 , p_wf_activity_name IN VARCHAR2
165 , p_wf_activity_status_code IN VARCHAR2
166 , p_wf_activity_result_code IN VARCHAR2
167 , p_validation_db_object_name IN VARCHAR2 := NULL
168 , p_use_where IN BOOLEAN := TRUE
169 , x_bind_var_stmt OUT NOCOPY LONG
170 )
171 RETURN VARCHAR2 IS
172 ------------------------------------------------------------------
173 l_vc_sql LONG := p_vc_sql;
174 BEGIN
175
176 -- Bug 3739681
177 -- Use bind variables for WF columns to be passed to the
178 -- validation cursor
179
180 x_bind_var_stmt := x_bind_var_stmt||
181 ' l_wf_item_type varchar2(8) :='||
182 ''''|| p_wf_item_type||''';' || OE_PC_GLOBALS.NEWLINE;
183 x_bind_var_stmt := x_bind_var_stmt||
184 ' l_wf_activity_name varchar2(30) :='||
185 ''''||p_wf_activity_name||''';' || OE_PC_GLOBALS.NEWLINE;
186
187 l_vc_sql := l_vc_sql || ' FROM wf_item_activity_statuses w, wf_process_activities wpa'
188 || OE_PC_GLOBALS.NEWLINE;
189
190 IF p_validation_db_object_name IS NOT NULL THEN
191 l_vc_sql := l_vc_sql || ' ,' || p_validation_db_object_name
192 ||' a ' || OE_PC_GLOBALS.NEWLINE;
193 END IF;
194
195 IF (p_use_where) THEN
196 l_vc_sql := l_vc_sql || ' WHERE w.item_type = l_wf_item_type'
197 || OE_PC_GLOBALS.NEWLINE;
198 ELSE
199 l_vc_sql := l_vc_sql || ' AND w.item_type = l_wf_item_type'
200 || OE_PC_GLOBALS.NEWLINE;
201 END IF;
202
203 l_vc_sql := l_vc_sql || ' AND w.process_activity = wpa.instance_id'
204 || OE_PC_GLOBALS.NEWLINE;
205
206 l_vc_sql := l_vc_sql || ' AND wpa.activity_name = l_wf_activity_name'
207 || OE_PC_GLOBALS.NEWLINE;
208
209 IF p_wf_activity_status_code IS NOT NULL THEN
210 x_bind_var_stmt := x_bind_var_stmt||
211 ' l_wf_activity_status_code varchar2(8) :='||
212 ''''||p_wf_activity_status_code||''';' || OE_PC_GLOBALS.NEWLINE;
213 l_vc_sql := l_vc_sql || ' AND w.activity_status = l_wf_activity_status_code'
214 || OE_PC_GLOBALS.NEWLINE;
215 END IF;
216
217 IF p_wf_activity_result_code IS NOT NULL THEN
218 x_bind_var_stmt := x_bind_var_stmt||
219 ' l_wf_activity_result_code varchar2(30) :='||
220 ''''||p_wf_activity_result_code||''';' || OE_PC_GLOBALS.NEWLINE;
221 l_vc_sql := l_vc_sql || ' AND w.activity_result_code = l_wf_activity_result_code'
222 || OE_PC_GLOBALS.NEWLINE;
223 END IF;
224
225 RETURN l_vc_sql;
226
227 END Concatenate_VTMPLTWF_SQL;
228
229
230 ------------------------------------------------------------------
231 PROCEDURE Concatenate_Itemkey_Cols(
232 p_prefix in varchar2
233 ,p_delimiter in varchar2
234 ,p_column1 in varchar2
235 ,p_column2 in varchar2
236 ,p_column3 in varchar2
237 ,p_column4 in varchar2
238 ,x_conc_string out nocopy varchar2
239
240 )
241 ------------------------------------------------------------------
242 is
243 DOT varchar2(1) := '.';
244 CONC varchar2(4) := ' || ';
245 l_string varchar2(500);
246 begin
247 l_string := p_prefix || DOT || p_column1;
248 if (p_column2 is not null) then
249 l_string := l_string || CONC || p_delimiter || p_prefix || DOT || p_column2;
250 end if;
251 if (p_column3 is not null) then
252 l_string := l_string || CONC || p_delimiter || p_prefix || DOT || p_column3;
253 end if;
254 if (p_column4 is not null) then
255 l_string := l_string || CONC || p_delimiter || p_prefix || DOT || p_column4;
256 end if;
257 x_conc_string := l_string || CONC || '''''';
258
259 end Concatenate_Itemkey_Cols;
260
261
262 --------------------------------------------------------------
263 PROCEDURE Make_Validation_Cursors(
264 p_entity_id in number
265 ,p_validation_entity_id in number
266 ,p_validation_tmplt_id in number
267 ,p_record_set_id in number
268 ,p_global_record_name in varchar2
269 ,x_valid_count_cursor out nocopy long
270
271 ,x_set_count_cursor out nocopy long
272
273 ,x_validation_stmt out nocopy long
274 ,x_bind_var_stmt out nocopy long
275 )
276 --------------------------------------------------------------
277 is
278 l_vc_sql long;
279 l_rs_sql long;
280 l_vc_pk_list varchar2(1000);
281 l_rs_pk_list varchar2(1000);
282 l_concatenated_itemkey_columns varchar2(1000);
283
284 l_wf_item_type OE_PC_ENTITIES_V.WF_ITEM_TYPE%TYPE;
285
286 CURSOR C_VTBL IS
287 SELECT application_id, db_object_name, db_object_type,
288 wf_item_type, itemkey_column1, itemkey_column2,
289 itemkey_column3,itemkey_column4, itemkey_delimiter
290 FROM OE_PC_ENTITIES_V
291 where entity_id = p_validation_entity_id;
292
293 CURSOR C_VTBL1 IS
294 SELECT e.application_id, e.db_object_name, e.db_object_type,
295 wf.itemkey_column1, wf.itemkey_column2,
296 wf.itemkey_column3,wf.itemkey_column4, wf.itemkey_delimiter
297 FROM OE_PC_ENTITIES_V e, OE_AK_OBJ_WF_ITEMS wf
298 where e.entity_id = p_validation_entity_id
299 and wf.database_object_name(+) = e.db_object_name
300 and (l_wf_item_type IS NULL
301 or wf.item_type = l_wf_item_type);
302
303 CURSOR C_DTBL IS
304 SELECT application_id, db_object_name, db_object_type
305 FROM OE_PC_ENTITIES_V
306 where entity_id = p_entity_id;
307
308 CURSOR C_VTMPLT IS
309 SELECT validation_type, activity_name, activity_status_code, activity_result_code, wf_item_type
310 FROM oe_pc_vtmplts
311 WHERE validation_tmplt_id = p_validation_tmplt_id
312 and (validation_type = 'WF'
313 OR validation_type = 'TBL');
314
315 -- Validation Template Columns selected such that state attribute columns
316 -- are selected first. This way, if there is even one condition for state
317 -- attribute, that will be selected first and this will help in appending
318 -- the database object name to the FROM cursor.
319 CURSOR C_VTMPLTCOLS IS
320 SELECT vc.column_name, attr.data_type, vc.validation_op, vc.value_string
321 , decode(attr.state_attribute_flag,NULL,1,'Y',0,'N',1) state_attribute
322 FROM oe_pc_vtmplt_cols vc,
323 oe_pc_vtmplts vt,
324 oe_pc_attributes_v attr
325 WHERE vc.validation_tmplt_id = p_validation_tmplt_id
326 AND vc.validation_tmplt_id = vt.validation_tmplt_id
327 AND attr.entity_id = vt.entity_id
328 AND attr.column_name = vc.column_name
329 ORDER BY state_attribute;
330
331 CURSOR C_RS IS
332 SELECT pk_record_set_flag
333 FROM oe_pc_rsets
334 WHERE record_set_id = p_record_set_id;
335
336 CURSOR C_RSCOLS IS
337 SELECT column_name
338 FROM oe_pc_rset_sel_cols
339 WHERE record_set_id = p_record_set_id;
340
341
342 CURSOR C_PKCOLS (cp_application_id number,
343 cp_db_object_name varchar2,
344 cp_db_object_type varchar2)
345 IS
346 SELECT uk_column_name pk_column_name
347 FROM oe_pc_ukey_cols_v
348 WHERE application_id = cp_application_id
349 AND db_object_name = cp_db_object_name
350 ANd db_object_type = cp_db_object_type
351 AND primary_key_flag = 'Y'
352 AND uk_column_sequence <= 5
353 ORDER BY uk_column_sequence;
354
355 CURSOR C_FKCOLS (cp_fk_application_id number,
356 cp_fk_db_object_name varchar2,
357 cp_uk_application_id number,
358 cp_uk_db_object_name varchar2,
359 cp_db_object_type varchar2)
360 IS
361 SELECT fk_column_name, uk_column_name
362 FROM oe_pc_fkey_cols_v
363 WHERE application_id = cp_fk_application_id
364 AND db_object_name = cp_fk_db_object_name
365 AND db_object_type = cp_db_object_type
366 AND uk_application_id = cp_uk_application_id
367 AND uk_db_object_name = cp_uk_db_object_name
368 ORDER BY fk_column_sequence;
369
370
371 CURSOR C_DFK
372 IS SELECT 'Y'
373 FROM sys.dual
374 WHERE EXISTS (SELECT 'EXISTS'
375 FROM oe_pc_rentities_v re
376 WHERE entity_id = p_entity_id
377 AND related_entity_id = p_validation_entity_id);
378
379
380 --driver is the term used to descibe the entity using which we have
381 --to identify the record of the validation (validated) entity to
382 --perform the validations. for example, if you are checking
383 --constraints against Order Line, but the condition is to check the
384 --status of its header, then the driver entity is LINE, and the
385 --validation entity is HEADER. you should use the foreign key
386 --definition of HEADER or LINE (ofcouse it's on LINE in this case but
387 --the foreign key may be on either of the entities, if you treat
388 --the problem as generic) to navigate to the HEADER.
389 ------------------------------------------------------------------------
390 l_driver_appln_id number;
391 l_driver_db_object_name OE_PC_ENTITIES_V.db_object_name%TYPE;
392 l_driver_db_object_type OE_PC_ENTITIES_V.db_object_type%TYPE;
393 l_driver_entity_fk_flag varchar2(1) := 'N';
394
395 l_validation_appln_id number;
396 l_validation_db_object_name OE_PC_ENTITIES_V.db_object_name%TYPE;
397 l_validation_db_object_type OE_PC_ENTITIES_V.db_object_type%TYPE;
398 l_itemkey_column1 OE_PC_ENTITIES_V.ITEMKEY_COLUMN1%TYPE;
399 l_itemkey_column2 OE_PC_ENTITIES_V.ITEMKEY_COLUMN2%TYPE;
400 l_itemkey_column3 OE_PC_ENTITIES_V.ITEMKEY_COLUMN3%TYPE;
401 l_itemkey_column4 OE_PC_ENTITIES_V.ITEMKEY_COLUMN4%TYPE;
402 l_itemkey_delimiter OE_PC_ENTITIES_V.ITEMKEY_DELIMITER%TYPE;
403 l_pk_record_set_flag OE_PC_RSETS.pk_record_set_flag%TYPE;
404 l_validation_type OE_PC_VTMPLTS.VALIDATION_TYPE%TYPE;
405 l_wf_activity_name OE_PC_VTMPLTS.ACTIVITY_NAME%TYPE;
406 l_wf_activity_status_code OE_PC_VTMPLTS.ACTIVITY_STATUS_CODE%TYPE;
407 l_wf_activity_result_code OE_PC_VTMPLTS.ACTIVITY_RESULT_CODE%TYPE;
408 l_value_string VARCHAR2(240);
409 l_ve_condn_sql VARCHAR2(240);
410 from_char NUMBER;
411 found_char NUMBER;
412
413 -- LOOP variables/counters
414 i number := 0;
415 j number := 0;
416 use_where BOOLEAN;
417
418 l_condn_logic_only BOOLEAN;
419
420 --
421 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
422 --
423 begin
424
425
426 -- find out the validation type
427 OPEN C_VTMPLT;
428 fetch C_VTMPLT into l_validation_type, l_wf_activity_name,
429 l_wf_activity_status_code, l_wf_activity_result_code, l_wf_item_type;
430 CLOSE C_VTMPLT;
431
432 OPEN C_RS;
433 fetch C_RS into l_pk_record_set_flag;
434 CLOSE C_RS;
435
436 -- get validation tbl and WF item/key details
437 IF OE_Code_Control.Get_Code_Release_Level < '110510' THEN
438 OPEN C_VTBL;
439 Fetch C_VTBL into l_validation_appln_id,l_validation_db_object_name,
440 l_validation_db_object_type, l_wf_item_type,
441 l_itemkey_column1,l_itemkey_column2,
442 l_itemkey_column3, l_itemkey_column4, l_itemkey_delimiter;
443 Close C_VTBL;
444 ELSE
445 OPEN C_VTBL1;
446 Fetch C_VTBL1 into l_validation_appln_id,l_validation_db_object_name,
447 l_validation_db_object_type,
448 l_itemkey_column1,l_itemkey_column2,
449 l_itemkey_column3, l_itemkey_column4, l_itemkey_delimiter;
450 Close C_VTBL1;
451
452 END IF;
453
454 if (p_entity_id = p_validation_entity_id) then
455
456 --
457 IF l_debug_level > 0 THEN
458 oe_debug_pub.add( 'ENTITY ID = VALIDATION ENTITY ID' , 2 ) ;
459 END IF;
460 --
461 if(l_validation_type = OE_PC_GLOBALS.WF_VALIDATION) then
462 --
463 IF l_debug_level > 0 THEN
464 oe_debug_pub.add( 'VALIDATION TYPE = WF' , 2 ) ;
465 END IF;
466 --
467
468 if(l_pk_record_set_flag = OE_PC_GLOBALS.YES_FLAG) then
469 --
470 IF l_debug_level > 0 THEN
471 oe_debug_pub.add( 'PK_RECORD_SET_FLAG = Y' , 2 ) ;
472 END IF;
473 --------------------------------------------------------------------
474 -- 1. e.g. for entity LINE, validation_type = 'WF' and
475 -- entity = LINE; validation_entity = LINE (SAME)
476 -- validation_tmplt = 'Invoice Complete'
477 -- record set is LINE (Single record set)
478
479 -- the procedure body will look like
480 -- -- assume that the condition will fail
481 -- x_result := 0;
482 -- l_valid_count := 0;
483 --
484 -- SELECT count(*)
485 -- INTO x_valid_count
486 -- FROM wf_item_activity_statuses_v w
487 -- WHERE w.item_type = 'OEOL'
488 -- AND w.activity_name = 'INVOICE'
489 -- AND w.activity_status_code = 'COMPLETE'
490 -- AND w.activity_result_code = '#NULL'
491 -- AND w.item_key = '||p_global_record_name||'.itemkey_col1 || '||p_global_record_name||'.itemkey_cols
492 --
493 -- x_result := l_valid_count;
494 -- return;
495 ---------------------------------------------------------------------
496
497 Concatenate_Itemkey_Cols(p_prefix => p_global_record_name
498 ,p_delimiter => l_itemkey_delimiter
499 ,p_column1 => l_itemkey_column1
500 ,p_column2 => l_itemkey_column2
501 ,p_column3 => l_itemkey_column3
502 ,p_column4 => l_itemkey_column4
503 ,x_conc_string => l_concatenated_itemkey_columns);
504
505 -- construct the the FROM .. WHERE clasue for the cursor
506
507 l_vc_sql := Concatenate_VTMPLTWF_SQL
508 (p_vc_sql => l_vc_sql
509 ,p_wf_item_type => l_wf_item_type
510 ,p_wf_activity_name => l_wf_activity_name
511 ,p_wf_activity_status_code => l_wf_activity_status_code
512 ,p_wf_activity_result_code => l_wf_activity_result_code
513 ,x_bind_var_stmt => x_bind_var_stmt
514 );
515
516 l_vc_sql := l_vc_sql || ' AND w.item_key = ' ||
517 l_concatenated_itemkey_columns || OE_PC_GLOBALS.NEWLINE;
518
519
520 else
521
522 --
523 IF l_debug_level > 0 THEN
524 oe_debug_pub.add( 'PK_RECORD_SET_FLAG <> Y' , 2 ) ;
525 END IF;
526 -- l_pk_record_set_flag <> OE_PC_GLOBALS.YES_FLAG)
527 -- mult record set
528 ---------------------------------------------------------------------
529 -- 2. e.g. for validation_type = 'WF' and
530 -- entity = LINE; validation_entity = LINE (SAME)
531 -- validation_tmplt = 'Invoice Complete'
532 -- record_set = 'Ship Set' (Mult Record Set)
533 --
534 -- the procedure body will look like
535 -- -- assume that the validation will fail
536 -- x_result := 0;
537 -- l_valid_count := 0;
538 -- l_set_count := 0;
539 --
540 -- SELECT count(*)
541 -- INTO l_valid_count
542 -- FROM wf_item_activity_statuses_v w
543 -- WHERE w.item_type = 'OEOL'
544 -- AND w.activity_name = 'INVOICE'
545 -- AND w.activity_status_code = 'COMPLETE'
546 -- AND w.activity_result_code = '#NULL'
547 -- AND w.item_key IN
548 -- (SELECT b.concatenated_itemkey_columns
549 -- FROM oe_order_lines b
550 -- WHERE b.record_set_selector_columns = '||p_global_record_name||'.record_set_selector_columns);
551 --
552 -- if (l_valid_count > 0 ) then
553 -- if (scope = 'ALL') then
554 -- SELECT count(*)
555 -- into l_set_count
556 -- FROM oe_order_lines b
557 -- WHERE b.record_set_selector_columns = '||p_global_record_name||'.record_set_selector_columns;
558 --
559 -- if (l_set_count = l_valid_count) then
560 -- x_result = 1;
561 -- end if;
562 -- else
563 -- x_result = 1;
564 -- end if;
565 -- end if;
566 -- return;
567 ---------------------------------------------------------------------
568 Concatenate_Itemkey_Cols(p_prefix => 'b'
569 ,p_delimiter => l_itemkey_delimiter
570 ,p_column1 => l_itemkey_column1
571 ,p_column2 => l_itemkey_column2
572 ,p_column3 => l_itemkey_column3
573 ,p_column4 => l_itemkey_column4
574 ,x_conc_string => l_concatenated_itemkey_columns);
575
576
577 -- first let's make the record set sql
578 l_rs_sql := ' FROM ' || l_validation_db_object_name ||' b ' || OE_PC_GLOBALS.NEWLINE;
579
580
581 -- add logic to select the record set
582 i := 1;
583 for rs_rec in C_RSCOLS loop
584 if (i = 1) then
585 -- where clause only for the first one
586 l_rs_sql := l_rs_sql || ' WHERE b.' || rs_rec.column_name || ' = '||p_global_record_name||'.' || rs_rec.column_name || OE_PC_GLOBALS.NEWLINE;
587 else
588 l_rs_sql := l_rs_sql || ' AND b.' || rs_rec.column_name || ' = '||p_global_record_name||'.' || rs_rec.column_name || OE_PC_GLOBALS.NEWLINE;
589 end if;
590 i := i+ 1;
591 end loop;
592
593
594 -- construct the the FROM .. WHERE clasue for the cursor
595
596 l_vc_sql := Concatenate_VTMPLTWF_SQL
597 (p_vc_sql => l_vc_sql
598 ,p_wf_item_type => l_wf_item_type
599 ,p_wf_activity_name => l_wf_activity_name
600 ,p_wf_activity_status_code => l_wf_activity_status_code
601 ,p_wf_activity_result_code => l_wf_activity_result_code
602 ,x_bind_var_stmt => x_bind_var_stmt
603 );
604
605 l_vc_sql := l_vc_sql || ' AND w.item_key IN ' || OE_PC_GLOBALS.NEWLINE;
606 l_vc_sql := l_vc_sql || ' ( SELECT ' || l_concatenated_itemkey_columns || OE_PC_GLOBALS.NEWLINE;
607 l_vc_sql := l_vc_sql || ' ' || l_rs_sql || OE_PC_GLOBALS.NEWLINE;
608 l_vc_sql := l_vc_sql || ' )';
609
610 end if;
611
612 else
613
614 -- l_validation_type <> OE_PC_GLOBALS.WF_VALIDATION
615 -- validation type is database column validation
616 if(l_pk_record_set_flag = OE_PC_GLOBALS.YES_FLAG) then
617 ---------------------------------------------------------------------
618 -- 3. e.g. for p_validation_type = 'COLS' and
619 -- entity = LINE; validation_entity = LINE (SAME)
620 -- validation_tmplt = 'Prorated Price Exists'
621 -- record set = LINE (Single Record Set)
622 -- the procedure body will look like
623 -- -- assume that the validation will fail
624 -- x_result := 0;
625 -- l_valid_count := 0;
626 --
627 -- SELECT count(*)
628 -- INTO x_valid_count
629 -- FROM sys.dual
630 -- WHERE '||p_global_record_name||'.prorated_price = 'YES'
631 --
632 -- x_result := l_valid_count;
633 -- return;
634 ---------------------------------------------------------------------
635
636
637 i := 1;
638 l_condn_logic_only := FALSE;
639 FOR val_rec in C_VTMPLTCOLS LOOP
640
641 l_value_string := Convert_Value_String(val_rec.value_string, val_rec.data_type);
642
643 IF (i = 1) THEN
644
645 -- Even if one state attribute makes up this validation template,
646 -- then it will be selected first because cursor C_VTMPLTCOLS
647 -- orders by state_attribute
648
649 -- If state attribute THEN FROM cursor selects from the database object
650 IF val_rec.state_attribute = 0 THEN
651
652 l_vc_sql := ' FROM '|| l_validation_db_object_name ||
653 ' a ' || OE_PC_GLOBALS.NEWLINE;
654 -- IF state attribute THEN add logic to get to the DB record
655 -- E.g. WHERE a.line_id = '||p_global_record_name||'.line_id
656 j := 1;
657 FOR pk_rec in C_PKCOLS (l_validation_appln_id, l_validation_db_object_name, l_validation_db_object_type) loop
658 IF j= 1 THEN
659 l_vc_sql := l_vc_sql || ' WHERE a.' || pk_rec.pk_column_name || ' = '||p_global_record_name||'.'||pk_rec.pk_column_name || OE_PC_GLOBALS.NEWLINE;
660 ELSE
661 l_vc_sql := l_vc_sql || ' AND a.' || pk_rec.pk_column_name || ' = '||p_global_record_name||'.' ||pk_rec.pk_column_name|| OE_PC_GLOBALS.NEWLINE;
662 END IF;
663 j:= j + 1;
664 END LOOP;
665
666 l_vc_sql := l_vc_sql || ' AND a.' || val_rec.column_name ||
667 ' ' || val_rec.validation_op || ' ' || l_value_string || OE_PC_GLOBALS.NEWLINE;
668
669 -- If first attribute NOT state attribute THEN there are no state attributes
670 -- on this validation template, hence select FROM SYS.DUAL
671 ELSE
672 l_condn_logic_only := TRUE;
673 l_vc_sql := ' IF '||p_global_record_name||'.' || val_rec.column_name ||
674 ' ' || val_rec.validation_op || ' ' || l_value_string || OE_PC_GLOBALS.NEWLINE;
675 /*
676 l_vc_sql := ' FROM SYS.DUAL' || OE_PC_GLOBALS.NEWLINE;
677 l_vc_sql := l_vc_sql || ' WHERE '||p_global_record_name||'.' || val_rec.column_name ||
678 ' ' || val_rec.validation_op || ' ' || l_value_string || OE_PC_GLOBALS.NEWLINE;
679 */
680 END IF;
681
682 -- AND clause if not the first attribute
683 ELSE
684
685 IF val_rec.state_attribute = 0 THEN
686 l_vc_sql := l_vc_sql || ' AND a.' || val_rec.column_name || ' ' || val_rec.validation_op || ' ' || l_value_string || OE_PC_GLOBALS.NEWLINE;
687 ELSE
688 l_vc_sql := l_vc_sql || ' AND '||p_global_record_name||'.' || val_rec.column_name || ' ' || val_rec.validation_op || ' ' || l_value_string || OE_PC_GLOBALS.NEWLINE;
689 END IF;
690
691 END IF; -- end of check to see if i= 1
692 i := i + 1;
693
694 END LOOP;
695
696 else
697 -- l_pk_record_set_flag <> OE_PC_GLOBALS.YES_FLAG)
698 -- mult record set
699 ---------------------------------------------------------------------
700 -- 4. e.g. for p_validation_type = 'COLS' and
701 -- entity = LINE; validation_entity = LINE (SAME)
702 -- validation_tmplt = 'Prorated Price Exists'
703 -- record_set = 'Ship Set' (Multi Record Set)
704 -- the procedure body will look like
705 -- -- assume that the condition will fail
706 -- x_result := 0;
707 -- l_valid_count := 0;
708 -- l_set_count := 0;
709 --
710 -- SELECT count(*)
711 -- INTO x_valid_count
712 -- FROM oe_order_lines a
713 -- WHERE a.prorated_price = 'YES'
714 -- AND (a.pk1_columns) IN
715 -- (SELECT (b.pk1_columns)
716 -- FROM oe_order_lines b
717 -- WHERE b.record_set_selector_columns = '||p_global_record_name||'.record_set_selector_columns);
718 -- if (l_valid_count > 0 ) then
719 -- if (scope = 'ALL') then
720 -- SELECT count(*)
721 -- into l_set_count
722 -- FROM oe_order_lines b
723 -- WHERE b.record_set_selector_columns = '||p_global_record_name||'.record_set_selector_columns);
724 --
725 -- if (l_set_count = l_valid_count) then
726 -- x_result = 1;
727 -- end if;
728 -- else
729 -- x_result = 1;
730 -- end if;
731 -- end if;
732 -- return;
733 --
734 --------------------------------------------------------------------
735 -- first let's make the record set sql
736
737 -- first let's make the record set sql
738 l_rs_sql := ' FROM ' || l_validation_db_object_name ||' b ' || OE_PC_GLOBALS.NEWLINE;
739
740 -- add logic to get to the intented record
741 i := 1;
742 for pk_rec in C_PKCOLS (l_validation_appln_id, l_validation_db_object_name, l_validation_db_object_type) loop
743 if (i = 1) then
744 l_rs_pk_list := ' b.' || pk_rec.pk_column_name;
745 l_vc_pk_list := ' a.' || pk_rec.pk_column_name;
746 else
747 l_rs_pk_list := l_rs_pk_list || ', b.'|| pk_rec.pk_column_name;
748 l_vc_pk_list := l_vc_pk_list || ', a.'|| pk_rec.pk_column_name;
749
750 end if;
751 i := i + 1;
752 end loop;
753 -- add logic to select the record set
754 i := 1;
755 for rs_rec in C_RSCOLS loop
756 if (i = 1) then
757 l_rs_sql := l_rs_sql || ' WHERE b.' || rs_rec.column_name || ' = '||p_global_record_name||'.' || rs_rec.column_name || OE_PC_GLOBALS.NEWLINE;
758 else
759 l_rs_sql := l_rs_sql || ' AND b.' || rs_rec.column_name || ' = '||p_global_record_name||'.' || rs_rec.column_name || OE_PC_GLOBALS.NEWLINE;
760 end if;
761 i := i + 1;
762 end loop;
763
764
765 -- make the validation sql
766 l_vc_sql := ' FROM '|| l_validation_db_object_name ||' a ' || OE_PC_GLOBALS.NEWLINE;
767
768 l_vc_sql := Concatenate_VTMPLTCOL_Sql(l_vc_sql, p_validation_tmplt_id);
769
770 -- add logic to get to the intented record
771 l_vc_sql := l_vc_sql || ' AND ( ' || l_vc_pk_list || ' ) IN ' || OE_PC_GLOBALS.NEWLINE;
772 l_vc_sql := l_vc_sql || ' ( SELECT ' || l_rs_pk_list || OE_PC_GLOBALS.NEWLINE;
773 l_vc_sql := l_vc_sql || l_rs_sql || OE_PC_GLOBALS.NEWLINE;
774 l_vc_sql := l_vc_sql || ' )';
775
776 end if;
777 end if;
778 else
779 -- p_entity_id <> p_validation_entity_id;
780 IF l_debug_level > 0 THEN
781 oe_debug_pub.add( 'P_ENTITY_ID <> P_VALIDATION_ENTITY_ID' , 2 ) ;
782 END IF;
783 --
784
785 -- get the driver tbl details
786 OPEN C_DTBL;
787 Fetch C_DTBL into l_driver_appln_id, l_driver_db_object_name, l_driver_db_object_type;
788 Close C_DTBL;
789
790 -- we need to find out the direction of the foreign key.
791 -- the foreign key could be on the validating entity(driver) or on the validation entity
792 -- for example when LINE is validating HEADER the fk (Line.header_id)is on the LINE (validating entity)
793 -- when HEADER is validating LINE the fk (line.header_id)is on the LINE (validation entity)
794 --
795 l_driver_entity_fk_flag := 'N';
796 -- is the validating entity the fk entity?
797 OPEN C_DFK;
798 Fetch C_DFK into l_driver_entity_fk_flag;
799 CLOSE C_DFK;
800
801 IF l_debug_level > 0 THEN
802 oe_debug_pub.add( 'L_DRIVER_ENTITY_FK_FLAG IS ' || L_DRIVER_ENTITY_FK_FLAG , 2 ) ;
803 END IF;
804
805
806 if(l_validation_type = OE_PC_GLOBALS.WF_VALIDATION) then
807
808 if(l_driver_entity_fk_flag = 'Y') then
809
810 IF l_debug_level > 0 THEN
811 oe_debug_pub.add( 'L_DRIVER_ENTITY_FK_FLAG =Y AND L_VALIDATION_TYPE =WF' , 2 ) ;
812 END IF;
813
814 ---------------------------------------------------------------------
815 -- 6. e.g. entity = LINE; validation_entity = HEADER (DIFFERENT)
816 -- validation_type = 'WF' and
817 -- validation_tmplt = 'Invoice Complete'
818 -- record_set = only the PK record set is allowed so it doesn't matter
819 -- since you are navigating to another record based on a the fk_values,
820 -- of the validating record identified using its pk_valuess,
821 -- the resulting record set will always be one record (parent record)
822 -- the procedure body will look like
823 -- -- assume that the condition will fail
824 -- x_result := 0;
825 -- l_valid_count := 0;
826 -- l_set_count := 0;
827 --
828 -- SELECT count(*)
829 -- INTO l_valid_count
830 -- FROM wf_item_activity_statuses_v w,
831 -- oe_order_headers a
832 -- WHERE w.item_type = 'OEOH'
833 -- AND w.activity_name = 'INVOICE'
834 -- AND w.activity_status_code = 'COMPLETE'
835 -- AND w.activity_result_code = '#NULL'
836 -- AND w.item_key = a.concatenated_itemkey_columns
837 -- AND a.uk_columns = '||p_global_record_name||'.fk_columns;
838
839 --
840 -- if (l_valid_count > 0 ) then
841 -- x_result = 1;
842 -- end if;
843 -- return;
844 ---------------------------------------------------------------------
845
846 Concatenate_Itemkey_Cols(p_prefix => 'a'
847 ,p_delimiter => l_itemkey_delimiter
848 ,p_column1 => l_itemkey_column1
849 ,p_column2 => l_itemkey_column2
850 ,p_column3 => l_itemkey_column3
851 ,p_column4 => l_itemkey_column4
852 ,x_conc_string => l_concatenated_itemkey_columns);
853
854
855
856 -- construct the the FROM .. WHERE clasue for the cursor
857
858 l_vc_sql := Concatenate_VTMPLTWF_SQL
859 (p_vc_sql => l_vc_sql
860 ,p_wf_item_type => l_wf_item_type
861 ,p_wf_activity_name => l_wf_activity_name
862 ,p_wf_activity_status_code => l_wf_activity_status_code
863 ,p_wf_activity_result_code => l_wf_activity_result_code
864 ,p_validation_db_object_name => l_validation_db_object_name
865 ,x_bind_var_stmt => x_bind_var_stmt
866 );
867
868 l_vc_sql := l_vc_sql || ' AND w.item_key = ' || l_concatenated_itemkey_columns || OE_PC_GLOBALS.NEWLINE;
869
870
871 -- navigate to the validation table
872 -- note that here the fk is defined on the driver entity
873 IF l_debug_level > 0 THEN
874 oe_debug_pub.add( 'DR DBO: ' || L_DRIVER_DB_OBJECT_NAME || ' VDBO: ' || L_VALIDATION_DB_OBJECT_NAME || ' TYPE: ' || L_DRIVER_DB_OBJECT_TYPE , 2 ) ;
875 END IF;
876
877 for fk_rec in C_FKCOLS (l_driver_appln_id, l_driver_db_object_name,
878 l_validation_appln_id, l_validation_db_object_name, l_driver_db_object_type) loop
879
880 l_vc_sql := l_vc_sql || ' AND a.' || fk_rec.uk_column_name || ' = '||p_global_record_name||'.' || fk_rec.fk_column_name || OE_PC_GLOBALS.NEWLINE;
881 end loop;
882
883 else
884
885 IF l_debug_level > 0 THEN
886 oe_debug_pub.add( 'L_DRIVER_ENTITY_FK_FLAG =N AND L_VALIDATION_TYPE =WF' , 2 ) ;
887 END IF;
888
889 -- l_driver_entity_fk_flag <> OE_PC_GLOBALS.YES_FLAG
890 ---------------------------------------------------------------------
891 -- 7. e.g. entity = HEADER; validation_entity = LINE (DIFFERENT)
892 -- validation_type = 'WF' and
893 -- validation_tmplt = 'Invoice Complete'
894 -- record_set = 'Ship Set' OR LINE (doesn't matter)
895 -- since you are navigating to another record using pk_values,
896 -- the resulting record set will laways be multi record set
897 -- the procedure body will look like
898 -- -- assume that the condition will fail
899 -- x_result := 0;
900 -- l_valid_count := 0;
901 -- l_set_count := 0;
902 --
903 -- SELECT count(*)
904 -- INTO l_valid_count
905 -- FROM wf_item_activity_statuses_v w
906 -- WHERE w.item_type = 'OEOL'
907 -- AND w.activity_name = 'INVOICE'
908 -- AND w.activity_status_code = 'COMPLETE'
909 -- AND w.activity_result_code = '#NULL'
910 -- AND w.item_key IN
911 -- (SELECT a.concatenated_itemkey_columns
912 -- FROM oe_order_lines a,
913 -- oe_order_lines b
914 -- WHERE b.fk_columns = '||p_global_record_name||'.uk_column
915 -- AND a.record_set_selector_columns = b.record_set_selector_columns);
916 --
917 -- if (l_valid_count > 0 ) then
918 -- if (scope = 'ALL') then
919 -- SELECT count(*)
920 -- into l_set_count
921 -- FROM oe_order_lines a,
922 -- oe_order_lines b
923 -- WHERE b.fk_columns = '||p_global_record_name||'.uk_column
924 -- AND a.record_set_selector_columns = b.record_set_selector_columns;
925 --
926 -- if (l_set_count = l_valid_count) then
927 -- x_result = 1;
928 -- end if;
929 -- else
930 -- x_result = 1;
931 -- end if;
932 -- end if;
933 -- return;
934 ---------------------------------------------------------------------
935
936 Concatenate_Itemkey_Cols(p_prefix => 'a'
937 ,p_delimiter => l_itemkey_delimiter
938 ,p_column1 => l_itemkey_column1
939 ,p_column2 => l_itemkey_column2
940 ,p_column3 => l_itemkey_column3
941 ,p_column4 => l_itemkey_column4
942 ,x_conc_string => l_concatenated_itemkey_columns);
943
944 -- (i) make the validation sql
945 l_vc_sql := Concatenate_VTMPLTWF_SQL
946 (p_vc_sql => l_vc_sql
947 ,p_wf_item_type => l_wf_item_type
948 ,p_wf_activity_name => l_wf_activity_name
949 ,p_wf_activity_status_code => l_wf_activity_status_code
950 ,p_wf_activity_result_code => l_wf_activity_result_code
951 ,x_bind_var_stmt => x_bind_var_stmt
952 );
953
954 l_vc_sql := l_vc_sql || ' AND w.item_key IN ' || OE_PC_GLOBALS.NEWLINE;
955 l_vc_sql := l_vc_sql || ' ( SELECT '|| l_concatenated_itemkey_columns || OE_PC_GLOBALS.NEWLINE;
956
957 -- (ii) make the record set sql
958 l_rs_sql := ' FROM ' || l_validation_db_object_name ||' a ' || OE_PC_GLOBALS.NEWLINE;
959 use_where := TRUE;
960 if l_pk_record_set_flag = 'N' then
961 l_rs_sql := l_rs_sql ||' ,' || l_validation_db_object_name ||' b ' || OE_PC_GLOBALS.NEWLINE;
962 i := 1;
963 for rs_rec in C_RSCOLS loop
964 if (i=1) then
965 l_rs_sql := l_rs_sql || ' WHERE b.'
966 || rs_rec.column_name || ' = a.' || rs_rec.column_name || OE_PC_GLOBALS.NEWLINE;
967 use_where := FALSE;
968 else
969 l_rs_sql := l_rs_sql || ' AND b.'
970 || rs_rec.column_name || ' = a.' || rs_rec.column_name || OE_PC_GLOBALS.NEWLINE;
971 end if;
972 i := i+1;
973 end loop;
974 end if;
975
976 -- (iii) add navigate to the validation tbl
977 for fk_rec in C_FKCOLS (l_validation_appln_id, l_validation_db_object_name,
978 l_driver_appln_id, l_driver_db_object_name, l_driver_db_object_type )
979 loop
980 if (use_where) then
981 l_rs_sql := l_rs_sql || ' WHERE a.' || fk_rec.fk_column_name ||
982 ' = '||p_global_record_name||'.' || fk_rec.uk_column_name || OE_PC_GLOBALS.NEWLINE;
983 use_where := FALSE;
984 else
985 l_rs_sql := l_rs_sql || ' AND a.' || fk_rec.fk_column_name ||
986 ' = '||p_global_record_name||'.' || fk_rec.uk_column_name || OE_PC_GLOBALS.NEWLINE;
987 end if;
988 end loop;
989
990 l_vc_sql := l_vc_sql || ' ' || l_rs_sql || OE_PC_GLOBALS.NEWLINE;
991 l_vc_sql := l_vc_sql || ' )'|| OE_PC_GLOBALS.NEWLINE;
992
993 end if;
994
995 else
996
997 -- l_validation_type <> OE_PC_GLOBALS.WF_VALIDATION
998 -- validation type is database column validation
999
1000 if(l_driver_entity_fk_flag = 'Y') then
1001 --
1002 IF l_debug_level > 0 THEN
1003 oe_debug_pub.add( 'L_DRIVER_ENTITY_FK_FLAG =Y AND L_VALIDATION_TYPE =COL' , 2 ) ;
1004 END IF;
1005 --
1006 ---------------------------------------------------------------------
1007 -- 9. e.g. for p_validation_type = 'COL' and
1008 -- entity <> validation_entity (DIFFERENT)
1009 -- entity = LINE, validation_entiy = HEADER
1010 -- validation_tmplt = 'Prorated Price Exists'
1011 -- record_set = 'Order'(doesn't matter)
1012 -- since you are navigating to another record based on a the fk_values,
1013 -- of the validating record identified using its pk_valuess,
1014 -- the resulting record set will always be one record (parent record)
1015 -- the procedure body will look like
1016 -- -- assume that the condition will fail
1017 -- x_result := 0;
1018 -- l_valid_count := 0;
1019 -- l_set_count := 0;
1020 --
1021 -- SELECT count(*)
1022 -- INTO l_valid_count
1023 -- FROM oe_order_headers a
1024 -- WHERE a.uk_columns = '||p_global_record_name||'.fk_columns;
1025 -- AND a.prorated_price = 'YES'
1026 --
1027 -- if (l_valid_count > 0 ) then
1028 -- x_result = 1;
1029 -- end if;
1030 -- return;
1031 ---------------------------------------------------------------------
1032
1033 IF l_debug_level > 0 THEN
1034 oe_debug_pub.add( 'VALIDATION OBJ: '||L_VALIDATION_DB_OBJECT_NAME , 2 ) ;
1035 END IF;
1036
1037 -- make the validation sql
1038 l_vc_sql := ' FROM '|| l_validation_db_object_name ||' a ' || OE_PC_GLOBALS.NEWLINE;
1039
1040
1041 -- add navigate to the validation tbl
1042 i:= 1;
1043 use_where := TRUE;
1044 for fk_rec in C_FKCOLS (l_driver_appln_id, l_driver_db_object_name,
1045 l_validation_appln_id, l_validation_db_object_name, l_driver_db_object_type)loop
1046 if (use_where) then
1047 -- where clause only for the first one
1048 l_vc_sql := l_vc_sql || ' WHERE a.' || fk_rec.uk_column_name || ' = '||p_global_record_name||'.' || fk_rec.fk_column_name || OE_PC_GLOBALS.NEWLINE;
1049 else
1050 l_vc_sql := l_vc_sql || ' AND a.' || fk_rec.uk_column_name || ' = '||p_global_record_name||'.' || fk_rec.fk_column_name || OE_PC_GLOBALS.NEWLINE;
1051 end if;
1052 i := i + 1;
1053 use_where := FALSE;
1054
1055 end loop;
1056
1057 l_vc_sql := Concatenate_VTMPLTCOL_Sql(l_vc_sql
1058 , p_validation_tmplt_id
1059 , use_where);
1060
1061 else
1062 ---------------------------------------------------------------------
1063 IF l_debug_level > 0 THEN
1064 oe_debug_pub.add( 'DRIVER_ENTITY_FK_FLAG =N AND L_VALIDATION_TYPE =COL' , 2 ) ;
1065 END IF;
1066
1067 -- 9. e.g. for p_validation_type = 'COL' and
1068 -- entity <> validation_entity (DIFFERENT)
1069 -- entity = HEADER, validation_entiy = LINE
1070 -- validation_tmplt = 'Prorated Price Exists'
1071 -- record_set = 'Ship Set' OR LINE (doesn't matter)
1072 -- since you are navigating to another record using pk_values,
1073 -- the resulting record set will laways be multi record set
1074 -- the procedure body will look like
1075 -- -- assume that the condition will fail
1076 -- x_result := 0;
1077 -- l_valid_count := 0;
1078 -- l_set_count := 0;
1079 --
1080 -- SELECT count(*)
1081 -- INTO l_valid_count
1082 -- FROM oe_order_lines a
1083 -- WHERE a.prorated_price = 'YES'
1084 -- AND a.pk_columns IN
1085 -- (SELECT b.pk_columns
1086 -- FROM oe_order_lines b,
1087 -- oe_order_lines c
1088 -- WHERE c.fk_columns = '||p_global_record_name||'.uk_columns
1089 -- AND b.record_set_selector_columns = c.record_set_selector_columns);
1090 -- if (l_valid_count > 0 ) then
1091 -- if (scope = 'ALL') then
1092 -- SELECT count(*)
1093 -- INTO l_set_count
1094 -- FROM oe_order_lines b,
1095 -- oe_order_lines c,
1096 -- WHERE c.fk_columns = '||p_global_record_name||'.uk_columns
1097 -- AND b.record_set_selector_columns = c.record_set_selector_columns
1098 --
1099 -- if (l_set_count = l_valid_count) then
1100 -- x_result = 1;
1101 -- end if;
1102 -- else
1103 -- x_result = 1;
1104 -- end if;
1105 -- end if;
1106 -- return;
1107 ---------------------------------------------------------------------
1108
1109 IF l_debug_level > 0 THEN
1110 oe_debug_pub.add( 'VALIDATION OBJ NAME: '||L_VALIDATION_DB_OBJECT_NAME , 2 ) ;
1111 END IF;
1112
1113 -- first let's make the record set sql
1114 l_rs_sql := ' FROM ' || l_validation_db_object_name ||' a ' || OE_PC_GLOBALS.NEWLINE;
1115
1116 if l_pk_record_set_flag = 'N' then
1117 l_rs_sql := l_rs_sql ||' ,' || l_validation_db_object_name ||' b ' || OE_PC_GLOBALS.NEWLINE;
1118 end if;
1119
1120 use_where := TRUE;
1121 -- add navigate to the validation tbl
1122 i := 1;
1123 for fk_rec in C_FKCOLS (l_validation_appln_id, l_validation_db_object_name,
1124 l_driver_appln_id, l_driver_db_object_name, l_driver_db_object_type )loop
1125 if (i = 1) then
1126 l_ve_condn_sql := ' WHERE a.' || fk_rec.fk_column_name ||
1127 ' = '||p_global_record_name||'.' || fk_rec.uk_column_name || OE_PC_GLOBALS.NEWLINE;
1128 use_where := FALSE;
1129 else
1130 l_ve_condn_sql := l_ve_condn_sql || ' AND a.' || fk_rec.fk_column_name ||
1131 ' = '||p_global_record_name||'.' || fk_rec.uk_column_name || OE_PC_GLOBALS.NEWLINE;
1132 end if;
1133 i := i + 1;
1134 end loop;
1135 l_rs_sql := l_rs_sql || l_ve_condn_sql;
1136
1137 -- make the validation sql
1138 l_vc_sql := ' FROM ' || l_validation_db_object_name ||' a ' || OE_PC_GLOBALS.NEWLINE;
1139 l_vc_sql := l_vc_sql || l_ve_condn_sql;
1140 l_vc_sql := Concatenate_VTMPLTCOL_Sql(l_vc_sql
1141 , p_validation_tmplt_id
1142 , use_where);
1143
1144 -- add logic to get to the intented record
1145 i := 1;
1146 for pk_rec in C_PKCOLS (l_validation_appln_id, l_validation_db_object_name
1147 , l_validation_db_object_type) loop
1148
1149 if (i = 1) then
1150 l_rs_pk_list := ' b.' || pk_rec.pk_column_name;
1151 l_vc_pk_list := ' a.' || pk_rec.pk_column_name;
1152 else
1153 l_rs_pk_list := l_rs_pk_list || ', b.'|| pk_rec.pk_column_name;
1154 l_vc_pk_list := l_vc_pk_list || ', a.'|| pk_rec.pk_column_name;
1155 end if;
1156 i := i + 1;
1157 end loop;
1158
1159 if l_pk_record_set_flag = 'N' then
1160 IF l_debug_level > 0 THEN
1161 oe_debug_pub.add( 'NOT PK RECORD SET' ) ;
1162 END IF;
1163 -- add logic to select the record set
1164 l_vc_sql := l_vc_sql || ' AND ('||l_vc_pk_list||') IN ( SELECT '||
1165 l_rs_pk_list|| OE_PC_GLOBALS.NEWLINE;
1166 l_vc_sql := l_vc_sql || ' FROM '||
1167 l_validation_db_object_name||' b'||OE_PC_GLOBALS.NEWLINE;
1168 i := 1;
1169 for rs_rec in C_RSCOLS loop
1170 if (i=1) then
1171 l_vc_sql := l_vc_sql || ' WHERE b.'
1172 || rs_rec.column_name || ' = a.' || rs_rec.column_name || OE_PC_GLOBALS.NEWLINE;
1173 else
1174 l_vc_sql := l_vc_sql || ' AND b.'
1175 || rs_rec.column_name || ' = a.' || rs_rec.column_name || OE_PC_GLOBALS.NEWLINE;
1176 end if;
1177 l_rs_sql := l_rs_sql || ' AND b.'
1178 || rs_rec.column_name || ' = a.' || rs_rec.column_name || OE_PC_GLOBALS.NEWLINE;
1179 i := i+1;
1180 end loop;
1181 l_vc_sql := l_vc_sql || ' )'
1182 || OE_PC_GLOBALS.NEWLINE;
1183 end if;
1184
1185 end if;
1186 end if;
1187 end if;
1188
1189 IF (l_condn_logic_only) THEN
1190 x_validation_stmt := l_vc_sql;
1191 ELSE
1192 x_valid_count_cursor := ' CURSOR C_VC IS ' || OE_PC_GLOBALS.NEWLINE ||
1193 ' SELECT count(*) ' || OE_PC_GLOBALS.NEWLINE || l_vc_sql || ';';
1194 END IF;
1195
1196 IF l_rs_sql is not null THEN
1197 x_set_count_cursor := ' CURSOR C_RSC IS ' || OE_PC_GLOBALS.NEWLINE ||
1198 ' SELECT count(*) ' || OE_PC_GLOBALS.NEWLINE || l_rs_sql || ';';
1199 END IF;
1200
1201 IF l_debug_level > 0 THEN
1202 oe_debug_pub.add( 'VALID COUNT CURSOR: '||X_VALID_COUNT_CURSOR , 2 ) ;
1203 oe_debug_pub.add( 'VALIDATION STMT: '||X_VALIDATION_STMT ) ;
1204 oe_debug_pub.add( 'SET COUNT CURSOR: '||X_SET_COUNT_CURSOR ) ;
1205 oe_debug_pub.add( 'BIND VAR STMT :'||X_BIND_VAR_STMT);
1206 oe_debug_pub.add( 'RETURNING FROM MAKE_VALIDATION_CURSORS' , 2 ) ;
1207 END IF;
1208
1209 End Make_Validation_Cursors;
1210
1211 -----------------------------------
1212 PROCEDURE Make_Control_Tbl_Sql(
1213 p_entity_id in number,
1214 p_validation_entity_id in number,
1215 p_validation_tmplt_id in number,
1216 p_record_set_id in number,
1217 p_pkg_name in varchar2,
1218 p_proc_name in varchar2,
1219 x_control_tbl_sql out nocopy varchar2
1220
1221 )
1222 IS
1223
1224 CURSOR C IS
1225 SELECT 'Y'
1226 FROM OE_PC_VALIDATION_PKGS
1227 WHERE validating_entity_id = p_entity_id
1228 AND validation_entity_id = p_validation_entity_id
1229 AND validation_tmplt_id = p_validation_tmplt_id
1230 AND record_set_id = p_record_set_id;
1231
1232 l_update varchar2(1) := 'N';
1233 l_sql varchar2(2000);
1234 COMMA varchar2(1) := ',';
1235 Q varchar2(1) := '''';
1236
1237 --
1238 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1239 --
1240 Begin
1241
1242 OPEN C;
1243 Fetch C into l_update;
1244 Close C;
1245
1246 -- BUG 2935346
1247 -- Use bind variables instead of literals. The only exception is p_proc_name
1248 -- in the INSERT sql as this is always set to one value - 'Is_Valid' and thus
1249 -- is equivalent to hardcoding a string. Also, this sql is executed from
1250 -- OEXPCRQB.pls which does not have visibility to p_proc_name value.
1251
1252 if (l_update = 'Y') then
1253 -- make update statement
1254 l_sql := 'UPDATE OE_PC_VALIDATION_PKGS SET last_update_date = sysdate ' || OE_PC_GLOBALS.NEWLINE;
1255 l_sql := l_sql || ' WHERE validating_entity_id = :b1 '||OE_PC_GLOBALS.NEWLINE;
1256 l_sql := l_sql || ' AND validation_entity_id = :b2 '||OE_PC_GLOBALS.NEWLINE;
1257 l_sql := l_sql || ' AND validation_tmplt_id = :b3 '||OE_PC_GLOBALS.NEWLINE;
1258 l_sql := l_sql || ' AND record_set_id = :b4 '||OE_PC_GLOBALS.NEWLINE;
1259 l_sql := l_sql || ' AND EXISTS (SELECT 1 '|| OE_PC_GLOBALS.NEWLINE;
1260 l_sql := l_sql || ' FROM USER_OBJECTS u '|| OE_PC_GLOBALS.NEWLINE;
1261 l_sql := l_sql || ' WHERE OBJECT_NAME = :b5 '||OE_PC_GLOBALS.NEWLINE;
1262 l_sql := l_sql || ' AND OBJECT_TYPE = '|| Q || 'PACKAGE BODY'|| Q || OE_PC_GLOBALS.NEWLINE;
1263 l_sql := l_sql || ' AND STATUS = '|| Q || 'VALID'|| Q || OE_PC_GLOBALS.NEWLINE;
1264 l_sql := l_sql || ' ) ' || OE_PC_GLOBALS.NEWLINE;
1265
1266
1267 else
1268 -- make insert statement
1269 l_sql := l_sql || 'INSERT INTO OE_PC_VALIDATION_PKGS ';
1270 l_sql := l_sql || '( ' || OE_PC_GLOBALS.NEWLINE;
1271 l_sql := l_sql || ' validating_entity_id ' || OE_PC_GLOBALS.NEWLINE;
1272 l_sql := l_sql || ' ,validation_entity_id ' || OE_PC_GLOBALS.NEWLINE;
1273 l_sql := l_sql || ' ,validation_tmplt_id ' || OE_PC_GLOBALS.NEWLINE;
1274 l_sql := l_sql || ' ,record_set_id ' || OE_PC_GLOBALS.NEWLINE;
1275 l_sql := l_sql || ' ,validation_pkg ' || OE_PC_GLOBALS.NEWLINE;
1276 l_sql := l_sql || ' ,validation_proc ' || OE_PC_GLOBALS.NEWLINE;
1277 l_sql := l_sql || ' ,created_by ' || OE_PC_GLOBALS.NEWLINE;
1278 l_sql := l_sql || ' ,creation_date ' || OE_PC_GLOBALS.NEWLINE;
1279 l_sql := l_sql || ' ,last_updated_by ' || OE_PC_GLOBALS.NEWLINE;
1280 l_sql := l_sql || ' ,last_update_date ' || OE_PC_GLOBALS.NEWLINE;
1281 l_sql := l_sql || ' ,last_update_login ' || OE_PC_GLOBALS.NEWLINE;
1282 l_sql := l_sql || ') ' || OE_PC_GLOBALS.NEWLINE;
1283 l_sql := l_sql || 'SELECT ' || OE_PC_GLOBALS.NEWLINE;
1284 l_sql := l_sql || ' :b1 '|| OE_PC_GLOBALS.NEWLINE;
1285 l_sql := l_sql || COMMA ||' :b2 '|| OE_PC_GLOBALS.NEWLINE;
1286 l_sql := l_sql || COMMA ||' :b3 '|| OE_PC_GLOBALS.NEWLINE;
1287 l_sql := l_sql || COMMA ||' :b4 '|| OE_PC_GLOBALS.NEWLINE;
1288 l_sql := l_sql || COMMA ||' :b5 '|| OE_PC_GLOBALS.NEWLINE;
1289 l_sql := l_sql || COMMA || Q || p_proc_name || Q || OE_PC_GLOBALS.NEWLINE;
1290 l_sql := l_sql || ',1' || OE_PC_GLOBALS.NEWLINE;
1291 l_sql := l_sql || ',sysdate' || OE_PC_GLOBALS.NEWLINE;
1292 l_sql := l_sql || ',1' || OE_PC_GLOBALS.NEWLINE;
1293 l_sql := l_sql || ',sysdate' || OE_PC_GLOBALS.NEWLINE;
1294 l_sql := l_sql || ',1' || OE_PC_GLOBALS.NEWLINE;
1295 l_sql := l_sql || 'FROM SYS.DUAL' || OE_PC_GLOBALS.NEWLINE;
1296 l_sql := l_sql || 'WHERE EXISTS (SELECT ''EXISTS'''|| OE_PC_GLOBALS.NEWLINE;
1297 l_sql := l_sql || ' FROM USER_OBJECTS u '|| OE_PC_GLOBALS.NEWLINE;
1298 l_sql := l_sql || ' WHERE u.OBJECT_NAME = :b5 '|| OE_PC_GLOBALS.NEWLINE;
1299 l_sql := l_sql || ' AND u.OBJECT_TYPE = '|| Q || 'PACKAGE BODY'|| Q || OE_PC_GLOBALS.NEWLINE;
1300 l_sql := l_sql || ' AND u.STATUS = '|| Q || 'VALID'|| Q || OE_PC_GLOBALS.NEWLINE;
1301 l_sql := l_sql || ' ) ' || OE_PC_GLOBALS.NEWLINE;
1302
1303 end if;
1304 x_control_tbl_sql := l_sql;
1305 End Make_Control_Tbl_Sql;
1306
1307
1308 --------------------------------
1309 PROCEDURE Make_Validation_Pkg
1310 (
1311 p_entity_id in number,
1312 p_entity_short_name in varchar2,
1313 p_db_object_name in varchar2,
1314 p_validation_entity_id in number,
1315 p_validation_entity_short_name in varchar2,
1316 p_validation_db_object_name in varchar2,
1317 p_validation_tmplt_id in number,
1318 p_validation_tmplt_short_name in varchar2,
1319 p_record_set_id in number,
1320 p_record_set_short_name in varchar2,
1321 p_global_record_name in varchar2,
1322 x_pkg_name out nocopy varchar2,
1323
1324 x_pkg_spec out nocopy long,
1325
1326 x_pkg_body out nocopy long,
1327
1328 x_control_tbl_sql out nocopy varchar2,
1329
1330 x_return_status out nocopy varchar2,
1331
1332 x_msg_data out nocopy varchar2,
1333
1334 x_msg_count out nocopy number
1335
1336 )
1337 IS
1338 l_pkg_name varchar2(30);
1339 l_pkg_spec LONG;
1340 l_pkg_body LONG;
1341 l_pkg_end VARCHAR2(40);
1342 l_proc_name VARCHAR2(30);
1343 l_proc_spec VARCHAR2(2000);
1344 l_control_tbl_sql varchar2(2000);
1345
1346 l_valid_count_cursor long;
1347 l_set_count_cursor long;
1348 l_validation_stmt long;
1349 -- Bug 3739681
1350 l_bind_var_stmt long;
1351
1352 --
1353 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1354 --
1355 BEGIN
1356 --
1357 IF l_debug_level > 0 THEN
1358 oe_debug_pub.add( ' ' , 2 ) ;
1359 oe_debug_pub.add( 'BEING : MAKE_VALIDATION_PKG' , 2 ) ;
1360 oe_debug_pub.add( 'VALIDATION TMPLT = ' || P_VALIDATION_TMPLT_ID , 2 ) ;
1361 oe_debug_pub.add( 'RECORD SET = ' || P_RECORD_SET_ID , 2 ) ;
1362 END IF;
1363 --
1364
1365 -- the package name
1366 -- example : OE_101PC100_HEADER_BOOKED
1367 l_pkg_name := 'OE_' || to_char(p_entity_id) || 'PC' ||
1368 to_char(p_validation_entity_id) || '_' ||
1369 p_record_set_short_name || '_' ||
1370 p_validation_tmplt_short_name;
1371
1372 l_pkg_spec := 'CREATE OR REPLACE PACKAGE ' || l_pkg_name || ' AS ' || OE_PC_GLOBALS.NEWLINE;
1373 l_pkg_body := 'CREATE OR REPLACE PACKAGE BODY ' || l_pkg_name || ' AS ' || OE_PC_GLOBALS.NEWLINE;
1374 l_pkg_end := 'END ' || l_pkg_name || ';' || OE_PC_GLOBALS.NEWLINE;
1375
1376
1377 l_proc_name := 'Is_Valid';
1378 l_proc_spec := ' PROCEDURE ' || l_proc_name || OE_PC_GLOBALS.NEWLINE;
1379 l_proc_spec := l_proc_spec || ' ( ' || OE_PC_GLOBALS.NEWLINE;
1380 l_proc_spec := l_proc_spec || ' p_application_id in number,' || OE_PC_GLOBALS.NEWLINE;
1381 l_proc_spec := l_proc_spec || ' p_entity_short_name in varchar2,' || OE_PC_GLOBALS.NEWLINE;
1382 l_proc_spec := l_proc_spec || ' p_validation_entity_short_name in varchar2, ' || OE_PC_GLOBALS.NEWLINE;
1383 l_proc_spec := l_proc_spec || ' p_validation_tmplt_short_name in varchar2,' || OE_PC_GLOBALS.NEWLINE;
1384 l_proc_spec := l_proc_spec || ' p_record_set_short_name in varchar2,' || OE_PC_GLOBALS.NEWLINE;
1385 l_proc_spec := l_proc_spec || ' p_scope in varchar2,' || OE_PC_GLOBALS.NEWLINE;
1386 l_proc_spec := l_proc_spec || ' x_result out nocopy number' || OE_PC_GLOBALS.NEWLINE;
1387
1388 l_proc_spec := l_proc_spec || ' )';
1389
1390 -- add a ; to end the PROCEDURE specs
1391 l_pkg_spec := l_pkg_spec || l_proc_spec || ';' || OE_PC_GLOBALS.NEWLINE;
1392
1393
1394 -- continue building procedure body
1395 l_pkg_body := l_pkg_body || l_proc_spec || OE_PC_GLOBALS.NEWLINE || ' IS ' || OE_PC_GLOBALS.NEWLINE;
1396 -- declare local variables
1397 l_pkg_body := l_pkg_body || ' l_valid_count NUMBER := 0; ' || OE_PC_GLOBALS.NEWLINE;
1398 l_pkg_body := l_pkg_body || ' l_set_count NUMBER := 0; ' || OE_PC_GLOBALS.NEWLINE;
1399
1400 -- construct the cursor for validating the conditions as well as to count the number of
1401 -- records in the validated record set
1402 --------------------------------------------------------------------------------------
1403 Make_Validation_Cursors (p_entity_id => p_entity_id
1404 ,p_validation_entity_id => p_validation_entity_id
1405 ,p_validation_tmplt_id => p_validation_tmplt_id
1406 ,p_record_set_id => p_record_set_id
1407 ,p_global_record_name => p_global_record_name
1408 ,x_valid_count_cursor => l_valid_count_cursor
1409 ,x_set_count_cursor => l_set_count_cursor
1410 ,x_validation_stmt => l_validation_stmt
1411 -- Bug 3739681
1412 ,x_bind_var_stmt => l_bind_var_stmt
1413 );
1414 --
1415 IF l_debug_level > 0 THEN
1416 oe_debug_pub.add( 'MAKE_VALIDATION_CURSORS COMPLETE' , 2 ) ;
1417 END IF;
1418 --
1419
1420 l_pkg_body := l_pkg_body || ' ' || OE_PC_GLOBALS.NEWLINE;
1421 -- Bug 3739681
1422 IF l_bind_var_stmt IS NOT NULL THEN
1423 l_pkg_body := l_pkg_body || l_bind_var_stmt || OE_PC_GLOBALS.NEWLINE;
1424 END IF;
1425 IF l_valid_count_cursor IS NOT NULL THEN
1426 l_pkg_body := l_pkg_body || l_valid_count_cursor || OE_PC_GLOBALS.NEWLINE;
1427 END IF;
1428 l_pkg_body := l_pkg_body || ' ' || OE_PC_GLOBALS.NEWLINE;
1429 l_pkg_body := l_pkg_body || l_set_count_cursor || OE_PC_GLOBALS.NEWLINE;
1430 l_pkg_body := l_pkg_body || ' ' || OE_PC_GLOBALS.NEWLINE;
1431
1432 l_pkg_body := l_pkg_body || 'BEGIN ' || OE_PC_GLOBALS.NEWLINE;
1433
1434 -- now add the procedure logic
1435
1436 -- first let's assume that the validation will be false
1437 l_pkg_body := l_pkg_body || ' x_result := 0; ' || OE_PC_GLOBALS.NEWLINE;
1438
1439 -- execute the validation cursor or the validation statement
1440 IF l_valid_count_cursor IS NOT NULL THEN
1441 l_pkg_body := l_pkg_body || ' OPEN C_VC; ' || OE_PC_GLOBALS.NEWLINE;
1442 l_pkg_body := l_pkg_body || ' FETCH C_VC into l_valid_count; ' || OE_PC_GLOBALS.NEWLINE;
1443 l_pkg_body := l_pkg_body || ' CLOSE C_VC; ' || OE_PC_GLOBALS.NEWLINE;
1444 ELSIF l_validation_stmt IS NOT NULL THEN
1445 l_pkg_body := l_pkg_body || l_validation_stmt;
1446 l_pkg_body := l_pkg_body || ' THEN ' || OE_PC_GLOBALS.NEWLINE;
1447 l_pkg_body := l_pkg_body || ' l_valid_count := 1; '|| OE_PC_GLOBALS.NEWLINE;
1448 l_pkg_body := l_pkg_body || ' END IF;' || OE_PC_GLOBALS.NEWLINE;
1449 END IF;
1450
1451 l_pkg_body := l_pkg_body || ' If (l_valid_count > 0) then ' || OE_PC_GLOBALS.NEWLINE;
1452 if (l_set_count_cursor is not null) then
1453 l_pkg_body := l_pkg_body || ' If (p_scope = ' || ''''|| 'ALL' ||'''' || ') then ' || OE_PC_GLOBALS.NEWLINE;
1454 l_pkg_body := l_pkg_body || ' OPEN C_RSC; ' || OE_PC_GLOBALS.NEWLINE;
1455 l_pkg_body := l_pkg_body || ' FETCH C_RSC into l_set_count; ' || OE_PC_GLOBALS.NEWLINE;
1456 l_pkg_body := l_pkg_body || ' CLOSE C_RSC; ' || OE_PC_GLOBALS.NEWLINE;
1457 l_pkg_body := l_pkg_body || ' If (l_valid_count = l_set_count) then ' || OE_PC_GLOBALS.NEWLINE;
1458 l_pkg_body := l_pkg_body || ' x_result := 1; ' || OE_PC_GLOBALS.NEWLINE;
1459 l_pkg_body := l_pkg_body || ' End If; ' || OE_PC_GLOBALS.NEWLINE;
1460 l_pkg_body := l_pkg_body || ' Else ' || OE_PC_GLOBALS.NEWLINE;
1461 l_pkg_body := l_pkg_body || ' x_result := 1; ' || OE_PC_GLOBALS.NEWLINE;
1462 l_pkg_body := l_pkg_body || ' End If; ' || OE_PC_GLOBALS.NEWLINE;
1463 else
1464 l_pkg_body := l_pkg_body || ' x_result := 1; ' || OE_PC_GLOBALS.NEWLINE;
1465 end if;
1466 l_pkg_body := l_pkg_body || ' End If; ' || OE_PC_GLOBALS.NEWLINE;
1467 l_pkg_body := l_pkg_body || ' Return; ' || OE_PC_GLOBALS.NEWLINE;
1468 l_pkg_body := l_pkg_body || 'END ' || l_proc_name || ';' || OE_PC_GLOBALS.NEWLINE;
1469
1470 l_pkg_spec := l_pkg_spec || OE_PC_GLOBALS.NEWLINE || l_pkg_end || OE_PC_GLOBALS.NEWLINE;
1471 l_pkg_body := l_pkg_body || OE_PC_GLOBALS.NEWLINE || l_pkg_end || OE_PC_GLOBALS.NEWLINE;
1472
1473 -- create the SQL to insert/update a record into OE_PC_VALIDATION_PKGS to timestamp the
1474 -- generated Package.
1475 --------------------------------------------------------------------------------------
1476 Make_Control_Tbl_Sql( p_entity_id => p_entity_id
1477 ,p_validation_entity_id => p_validation_entity_id
1478 ,p_validation_tmplt_id => p_validation_tmplt_id
1479 ,p_record_set_id => p_record_set_id
1480 ,p_pkg_name => l_pkg_name
1481 ,p_proc_name => l_proc_name
1482 ,x_control_tbl_sql => l_control_tbl_sql);
1483
1484 x_pkg_spec := l_pkg_spec;
1485 x_pkg_body := l_pkg_body;
1486 x_pkg_name := l_pkg_name;
1487 x_control_tbl_sql := l_control_tbl_sql;
1488 x_return_status := fnd_api.G_RET_STS_SUCCESS;
1489
1490 EXCEPTION
1491 WHEN OTHERS THEN
1492 x_return_status := fnd_api.G_RET_STS_ERROR;
1493 IF l_debug_level > 0 THEN
1494 oe_debug_pub.add( 'EXCEPTION IN MAKE_VALIDATION_PKG' ) ;
1495 END IF;
1496
1497
1498 END Make_Validation_Pkg;
1499 -------------------------------------
1500
1501 PROCEDURE Add_To_Check_On_Insert_Cache
1502 (p_entity_id IN NUMBER
1503 ,p_responsibility_id IN NUMBER
1504 ,p_application_id IN NUMBER --added for bug3631547
1505 )
1506 IS
1507 l_index PLS_INTEGER; -- for bug 6473618 NUMBER;
1508
1509 TYPE T_NUM is TABLE OF NUMBER;
1510 TYPE T_V1 is TABLE OF VARCHAR2(1);
1511 TYPE T_V30 is TABLE OF VARCHAR2(30);
1512
1513 t_constraint_ids T_NUM := T_NUM();
1514 t_entity_ids T_NUM := T_NUM();
1515 t_on_operation_actions T_NUM := T_NUM();
1516 t_column_names T_V30 := T_V30();
1517
1518 CURSOR C_CHECK_ON_INSERT_OP
1519 IS
1520 SELECT
1521 c.constraint_id, c.entity_id
1522 ,c.on_operation_action, c.column_name
1523 FROM oe_pc_constraints c
1524 WHERE c.entity_id = P_ENTITY_ID
1525 AND c.constrained_operation = OE_PC_GLOBALS.UPDATE_OP
1526 AND c.check_on_insert_flag = 'Y'
1527 AND nvl(c.enabled_flag, 'Y') = 'Y'
1528 AND EXISTS (
1529 SELECT 'EXISTS'
1530 FROM OE_PC_ASSIGNMENTS A
1531 WHERE a.constraint_id = c.constraint_id
1532 AND ( a.responsibility_id = p_responsibility_id
1533 OR a.responsibility_id IS NULL)
1534 AND ( a.application_id = p_application_id
1535 OR a.application_id IS NULL )
1536 AND NOT EXISTS (
1537 SELECT 'EXISTS'
1538 FROM OE_PC_EXCLUSIONS e
1539 WHERE e.responsibility_id = p_responsibility_id
1540 AND e.assignment_id = a.assignment_id
1541 AND e.application_id = p_application_id
1542 )
1543 )
1544 ORDER BY c.column_name, c.on_operation_action;
1545 --
1546 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1547 --
1548 BEGIN
1549
1550 l_index := (p_entity_id)*G_MAX_CONSTRAINTS + 1;
1551 IF G_Check_On_Insert_Cache.EXISTS(l_index) THEN
1552 RETURN;
1553 END IF;
1554
1555 OPEN c_check_on_insert_op;
1556 FETCH c_check_on_insert_op BULK COLLECT INTO t_constraint_ids,
1557 t_entity_ids,
1558 t_on_operation_actions,
1559 t_column_names;
1560 CLOSE c_check_on_insert_op;
1561
1562 FOR i IN 1..t_constraint_ids.count LOOP
1563 IF l_debug_level > 0 THEN
1564 oe_debug_pub.add( 'CHECK ON INSERT-ADDTOCACHE , COLUMN:' ||T_COLUMN_NAMES(i) ) ;
1565 END IF;
1566 G_Check_On_Insert_Cache(l_index).entity_id := p_entity_id;
1567 G_Check_On_Insert_Cache(l_index).column_name := t_column_names(i);
1568 G_Check_On_Insert_Cache(l_index).constraint_id := t_constraint_ids(i);
1569 G_Check_On_Insert_Cache(l_index).on_operation_action := t_on_operation_actions(i);
1570 l_index := l_index + 1;
1571 END LOOP;
1572
1573 IF l_index = (p_entity_id)*G_MAX_CONSTRAINTS + 1 THEN
1574 G_Check_On_Insert_Cache(l_index).column_name := FND_API.G_MISS_CHAR;
1575 END IF;
1576
1577 END Add_To_Check_On_Insert_Cache;
1578
1579 FUNCTION Check_On_Insert_Exists
1580 (p_entity_id IN NUMBER
1581 ,p_responsibility_id IN NUMBER
1582 ,p_application_id IN NUMBER --added for bug3631547
1583 )
1584 RETURN BOOLEAN IS
1585 l_index PLS_INTEGER; -- for bug 6473618 NUMBER;
1586 --
1587 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1588 --
1589 BEGIN
1590
1591 l_index := (p_entity_id)*G_MAX_CONSTRAINTS + 1;
1592
1593 Add_To_Check_On_Insert_Cache(p_entity_id => p_entity_id
1594 ,p_responsibility_id => p_responsibility_id
1595 ,p_application_id => p_application_id);
1596
1597 IF G_Check_On_Insert_Cache(l_index).column_name <> FND_API.G_MISS_CHAR THEN
1598 RETURN TRUE;
1599 ELSE
1600 RETURN FALSE;
1601 END IF;
1602
1603 EXCEPTION
1604 WHEN OTHERS THEN
1605 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1606 OE_MSG_PUB.Add_Exc_Msg
1607 ( G_PKG_NAME
1608 , 'Check_On_Insert_Exists'
1609 );
1610 END IF;
1611 RAISE;
1612 END Check_On_Insert_Exists;
1613
1614 PROCEDURE Add_To_ConstraintRuleTbl
1615 ( p_constraint_id IN NUMBER )
1616 IS
1617
1618 --Cursors
1619 CURSOR C_R
1620 IS SELECT
1621 c.application_id,
1622 c.entity_short_name,
1623 c.condition_id,
1624 c.group_number,
1625 c.modifier_flag,
1626 c.validation_application_id,
1627 c.validation_entity_short_name,
1628 c.validation_tmplt_short_name,
1629 c.record_set_short_name,
1630 c.scope_op,
1631 c.validation_pkg,
1632 c.validation_proc,
1633 c.validation_tmplt_id,
1634 c.record_set_id,
1635 c.entity_id,
1636 c.validation_entity_id
1637 FROM oe_pc_conditions_v c
1638 WHERE constraint_id = p_constraint_id
1639 AND nvl(enabled_flag, 'Y') = 'Y'
1640 ORDER BY c.group_number;
1641
1642 l_index PLS_INTEGER; -- for bug 6473618 NUMBER;
1643 --
1644 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1645 --
1646 BEGIN
1647
1648 l_index := (mod(p_constraint_id,100000) * G_MAX_CONDITIONS) + 1; --added mod for bug 3603559
1649
1650 OPEN C_R;
1651
1652 LOOP
1653
1654 FETCH C_R INTO
1655 g_constraintRuleTbl(l_index).application_id,
1656 g_constraintRuleTbl(l_index).entity_short_name,
1657 g_constraintRuleTbl(l_index).condition_id,
1658 g_constraintRuleTbl(l_index).group_number,
1659 g_constraintRuleTbl(l_index).modifier_flag,
1660 g_constraintRuleTbl(l_index).validation_application_id,
1661 g_constraintRuleTbl(l_index).validation_entity_short_name,
1662 g_constraintRuleTbl(l_index).validation_tmplt_short_name,
1663 g_constraintRuleTbl(l_index).record_set_short_name,
1664 g_constraintRuleTbl(l_index).scope_op,
1665 g_constraintRuleTbl(l_index).validation_pkg,
1666 g_constraintRuleTbl(l_index).validation_proc,
1667 g_constraintRuleTbl(l_index).validation_tmplt_id,
1668 g_constraintRuleTbl(l_index).record_set_id,
1669 g_constraintRuleTbl(l_index).entity_id,
1670 g_constraintRuleTbl(l_index).validation_entity_id;
1671
1672 IF (C_R%NOTFOUND) THEN
1673 IF l_index =((mod(p_constraint_id ,100000) * G_MAX_CONDITIONS) + 1) THEN --added mod for bug 3603559
1674 g_constraintRuleTbl(l_index).condition_id := -1;
1675 END IF;
1676 EXIT;
1677 END IF;
1678
1679 l_index := l_index + 1;
1680
1681 END LOOP;
1682
1683 CLOSE C_R;
1684
1685 END Add_To_ConstraintRuleTbl;
1686
1687 FUNCTION Get_Cached_Result
1688 (
1689 p_validation_tmplt_id in number
1690 ,p_record_set_id in number
1691 ,p_validation_entity_id in number
1692 ,p_entity_id in number
1693 ,p_scope_op in varchar2
1694 )
1695 RETURN NUMBER
1696 IS
1697 l_index PLS_INTEGER; -- for bug 6473618 NUMBER;
1698 l_result NUMBER := -1;
1699 --
1700 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1701 --
1702 BEGIN
1703
1704 l_index := (p_validation_tmplt_id*G_MAX_CONDITIONS) + 1;
1705
1706 WHILE G_Result_Cache.Exists(l_index) LOOP
1707
1708 IF G_Result_Cache(l_index).validation_tmplt_id = p_validation_tmplt_id
1709 AND G_Result_Cache(l_index).record_set_id = p_record_set_id
1710 AND G_Result_Cache(l_index).validation_entity_id = p_validation_entity_id
1711 AND G_Result_Cache(l_index).entity_id = p_entity_id
1712 AND G_Result_Cache(l_index).scope_op = p_scope_op
1713 THEN
1714 l_result := G_Result_Cache(l_index).result;
1715 IF l_debug_level > 0 THEN
1716 oe_debug_pub.add( 'VALIDATION TMPLT :'||P_VALIDATION_TMPLT_ID ||' CACHED RESULT :'||L_RESULT , 5 ) ;
1717 END IF;
1718 EXIT;
1719 END IF;
1720
1721 -- Bug 2312542: Code to increment the index counter was missing earlier.
1722 -- If result for same validation template is cached multiple times
1723 -- for different entities e.g. Order Closed template result is cached
1724 -- for Order Line and Order Sales Credit, then get_cached_result
1725 -- was going into an infinite loop causing the session to hang.
1726 l_index := l_index + 1;
1727
1728 END LOOP;
1729
1730 RETURN l_result;
1731
1732 END Get_Cached_Result;
1733
1734 PROCEDURE Add_Result_To_Cache
1735 (
1736 p_validation_tmplt_id in number
1737 ,p_record_set_id in number
1738 ,p_validation_entity_id in number
1739 ,p_entity_id in number
1740 ,p_scope_op in varchar2
1741 ,p_result in number
1742 )
1743 IS
1744 l_index PLS_INTEGER; -- for bug 6473618 NUMBER;
1745 --
1746 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1747 --
1748 BEGIN
1749
1750 l_index := (p_validation_tmplt_id*G_MAX_CONDITIONS) + 1;
1751
1752 WHILE G_Result_Cache.Exists(l_index) LOOP
1753 l_index := l_index + 1;
1754 END LOOP;
1755
1756 G_Result_Cache(l_index).validation_tmplt_id := p_validation_tmplt_id;
1757 G_Result_Cache(l_index).record_set_id := p_record_set_id;
1758 G_Result_Cache(l_index).validation_entity_id := p_validation_entity_id;
1759 G_Result_Cache(l_index).entity_id := p_entity_id;
1760 G_Result_Cache(l_index).scope_op := p_scope_op;
1761 G_Result_Cache(l_index).result := p_result;
1762
1763 END Add_Result_To_Cache;
1764
1765 -- Bug 1755817: procedure to clear cached results
1766 -- if validation_entity_id is passed, only results with that
1767 -- validation_entity are cleared else entire cache is cleared
1768 --------------------------------------------------------------
1769 PROCEDURE Clear_Cached_Results
1770 --------------------------------------------------------------
1771 (
1772 p_validation_entity_id in number
1773 )
1774 IS
1775 l_index BINARY_INTEGER;
1776 --
1777 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1778 --
1779 BEGIN
1780
1781 IF p_validation_entity_id IS NULL THEN
1782
1783 G_Result_Cache.DELETE;
1784
1785 ELSE
1786
1787 l_index := G_Result_Cache.FIRST;
1788
1789 WHILE G_Result_Cache.Exists(l_index) LOOP
1790
1791 IF G_Result_Cache(l_index).validation_entity_id
1792 = p_validation_entity_id THEN
1793 G_Result_Cache.DELETE(l_index);
1794 END IF;
1795
1796 l_index := G_Result_Cache.NEXT(l_index);
1797
1798 END LOOP;
1799
1800 END IF;
1801
1802 EXCEPTION
1803 WHEN OTHERS THEN
1804 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1805 OE_MSG_PUB.Add_Exc_Msg
1806 ( G_PKG_NAME
1807 , 'Clear_Cached_Results'
1808 );
1809 END IF;
1810 RAISE;
1811 END Clear_Cached_Results;
1812
1813 -------------------------------------
1814 PROCEDURE Validate_Constraint
1815 (
1816 p_constraint_id in number
1817 ,p_use_cached_results in varchar2
1818 ,x_condition_count out nocopy number
1819
1820 ,x_valid_condition_group out nocopy number
1821
1822 ,x_result out nocopy number
1823
1824 )
1825 IS
1826
1827 l_constraintRuleRec ConstraintRule_Rec_Type;
1828 l_dsqlCursor integer;
1829 l_dynamicSqlString varchar2(2000);
1830 l_rule_count number;
1831 l_ConstrainedStatus number;
1832 l_dummy integer;
1833 i number;
1834 l_tempResult boolean;
1835 l_result_01 number;
1836 l_currGrpNumber number;
1837 l_currGrpResult boolean;
1838 l_index PLS_INTEGER; -- for bug 6473618 number;
1839 --
1840 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1841 --
1842 BEGIN
1843
1844 IF l_debug_level > 0 THEN
1845 oe_debug_pub.add( 'CONSTRAINT ID:'||P_CONSTRAINT_ID , 5 ) ;
1846 END IF;
1847
1848 l_ConstrainedStatus := OE_PC_GLOBALS.NO;
1849 l_rule_count := 0;
1850 i := 0;
1851 l_currGrpNumber := -1;
1852 l_currGrpResult := FALSE;
1853
1854 -----------------------------------------------------
1855 -- Bug 1755817: USE CACHING TO IMPROVE PERFORMANCE
1856 -----------------------------------------------------
1857
1858 l_index := (mod(p_constraint_id ,100000)* G_MAX_CONDITIONS) + 1;
1859
1860 -- Cache the conditions for this constraint
1861 IF (NOT g_constraintRuleTbl.Exists(l_index)) THEN
1862 Add_To_ConstraintRuleTbl(p_constraint_id);
1863 END IF;
1864
1865 -- If there are no conditions associated with this constraint,
1866 -- return result of YES - constraint valid
1867 IF g_constraintRuleTbl(l_index).condition_id = -1 THEN
1868 GOTO RETURN_VALIDATION_RESULT;
1869 END IF;
1870
1871 -- Else loop through the conditions and validate all tthe
1872 -- constraints
1873 WHILE g_constraintRuleTbl.Exists(l_index) LOOP
1874
1875 l_constraintRuleRec := g_constraintRuleTbl(l_index);
1876
1877 IF (l_currGrpNumber <> l_constraintRuleRec.group_number) THEN
1878
1879 -- we are entering the new group of conditions..
1880 -- groups are ORd together, so if the previous group was evaluated
1881 -- to TRUE (OE_PC_GLOBALS.YES) then no need to evaluvate this group.
1882 IF (l_currGrpResult = TRUE) THEN
1883 l_ConstrainedStatus := OE_PC_GLOBALS.YES;
1884 EXIT; -- exit the loop
1885 END IF;
1886
1887 -- previous group did not evaluvate to TRUE, so lets pursue this new group
1888 l_currGrpNumber := l_constraintRuleRec.group_number;
1889 l_currGrpResult := FALSE;
1890 i := 0;
1891
1892 END IF;
1893
1894 -- we have a got a record, increment the count by 1
1895 l_rule_count := l_rule_count+1;
1896
1897 -- If validation entity <> constrained entity AND
1898 -- user has indicated that cached results should be used
1899 --, then check for the cached result
1900 -- It is not safe to use cached results if validation_entity =
1901 -- constrained entity as the entity's record picture may
1902 -- change during the checks e.g. by defaulting
1903 IF ( p_use_cached_results = 'Y'
1904 AND (l_constraintRuleRec.validation_entity_id
1905 <> l_constraintRuleRec.entity_id) ) THEN
1906
1907 l_result_01 := Get_Cached_Result
1908 (p_validation_tmplt_id => l_constraintRuleRec.validation_tmplt_id
1909 ,p_record_set_id => l_constraintRuleRec.record_set_id
1910 ,p_entity_id => l_constraintRuleRec.entity_id
1911 ,p_validation_entity_id => l_constraintRuleRec.validation_entity_id
1912 ,p_scope_op => l_constraintRuleRec.scope_op
1913 );
1914
1915 -- if result is not -1, then result was cached!
1916 IF l_result_01 <> -1 THEN
1917 GOTO CHECK_GROUP_RESULT;
1918 END IF;
1919
1920 END IF;
1921
1922 -- Execute the validation package for this condition
1923 -- pkg.function(p1, p2, ...)
1924 l_dynamicSqlString := ' begin ';
1925 l_dynamicSqlString := l_dynamicSqlString || l_constraintRuleRec.validation_pkg ||'.';
1926 l_dynamicSqlString := l_dynamicSqlString || l_constraintRuleRec.validation_proc;
1927
1928 -- IN Parameters
1929 l_dynamicSqlString := l_dynamicSqlString || '( ';
1930 l_dynamicSqlString := l_dynamicSqlString || ':t_application_id, ';
1931 l_dynamicSqlString := l_dynamicSqlString || ':t_entity_short_name, ';
1932 l_dynamicSqlString := l_dynamicSqlString || ':t_validation_entity_short_name, ';
1933 l_dynamicSqlString := l_dynamicSqlString || ':t_validation_tmplt_short_name, ';
1934 l_dynamicSqlString := l_dynamicSqlString || ':t_record_set_short_name, ';
1935 l_dynamicSqlString := l_dynamicSqlString || ':t_scope, ';
1936
1937 -- OUT Parameters
1938 l_dynamicSqlString := l_dynamicSqlString || ':t_result );';
1939 l_dynamicSqlString := l_dynamicSqlString || ' end; ';
1940
1941 -- EXECUTE THE DYNAMIC SQL
1942 EXECUTE IMMEDIATE l_dynamicSqlString USING IN l_constraintRuleRec.application_id,
1943 IN l_constraintRuleRec.entity_short_name,
1944 IN l_constraintRuleRec.validation_entity_short_name,
1945 IN l_constraintRuleRec.validation_tmplt_short_name,
1946 IN l_constraintRuleRec.record_set_short_name,
1947 IN l_constraintRuleRec.scope_op,
1948 OUT l_result_01;
1949
1950 IF l_debug_level > 0 THEN
1951 oe_debug_pub.add( 'VALIDATION PKG :'||L_CONSTRAINTRULEREC.VALIDATION_PKG ||' RESULT :'||L_RESULT_01 ) ;
1952 END IF;
1953
1954 IF (p_use_cached_results = 'Y'
1955 AND (l_constraintRuleRec.validation_entity_id
1956 <> l_constraintRuleRec.entity_id) ) THEN
1957
1958 Add_Result_To_Cache
1959 (p_validation_tmplt_id => l_constraintRuleRec.validation_tmplt_id
1960 ,p_record_set_id => l_constraintRuleRec.record_set_id
1961 ,p_entity_id => l_constraintRuleRec.entity_id
1962 ,p_validation_entity_id => l_constraintRuleRec.validation_entity_id
1963 ,p_scope_op => l_constraintRuleRec.scope_op
1964 ,p_result => l_result_01
1965 );
1966
1967 END IF;
1968
1969 <<CHECK_GROUP_RESULT>>
1970 IF (l_result_01 = 0) THEN
1971 l_tempResult := FALSE;
1972 ELSE
1973 l_tempResult := TRUE;
1974 END IF;
1975
1976 -- apply the modifier on the result
1977 if(l_constraintRuleRec.modifier_flag = OE_PC_GLOBALS.YES_FLAG) then
1978 l_tempResult := NOT(l_tempResult);
1979 end if;
1980
1981 IF (i = 0) THEN
1982 l_currGrpResult := l_tempResult;
1983 ELSE
1984 l_currGrpResult := l_currGrpResult AND l_tempResult;
1985 END IF;
1986
1987 -- increment the index
1988 i := i+1;
1989 l_index := l_index + 1;
1990
1991 END LOOP; -- end validatate validators
1992
1993 IF (l_currGrpNumber <> -1 AND l_currGrpResult = TRUE) THEN
1994 l_ConstrainedStatus := OE_PC_GLOBALS.YES;
1995 END IF;
1996
1997 <<RETURN_VALIDATION_RESULT>>
1998 -- did we validate any constraint rules?. if there is none then the
1999 -- constraint is valid and we will return YES
2000 IF (l_rule_count = 0) THEN
2001 x_condition_count := 0;
2002 x_valid_condition_group := -1;
2003 x_result := OE_PC_GLOBALS.YES;
2004 ELSE
2005 x_condition_count := l_rule_count;
2006 x_valid_condition_group := l_currGrpNumber;
2007 x_result := l_ConstrainedStatus;
2008 END IF;
2009
2010 EXCEPTION
2011 WHEN OTHERS THEN
2012 x_result := OE_PC_GLOBALS.ERROR;
2013 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2014 OE_MSG_PUB.Add_Exc_Msg
2015 ( G_PKG_NAME
2016 , 'Validate_Constraint : '||l_constraintRuleRec.validation_pkg
2017 );
2018 END IF;
2019 END Validate_Constraint;
2020 -------------------------------------------
2021
2022 FUNCTION Is_Op_Constrained
2023 (
2024 p_responsibility_id in number
2025 ,p_application_id in number --added for bug3631547
2026 ,p_operation in varchar2
2027 ,p_entity_id in number
2028 ,p_qualifier_attribute in varchar2
2029 ,p_column_name in varchar2
2030 ,p_check_all_cols_constraint in varchar2
2031 ,p_is_caller_defaulting in varchar2
2032 ,p_use_cached_results in varchar2
2033 ,x_constraint_id out nocopy number
2034
2035 ,x_constraining_conditions_grp out nocopy number
2036
2037 ,x_on_operation_action out nocopy number
2038
2039 )
2040 RETURN NUMBER
2041 IS
2042
2043 -- Local Variables
2044 l_validation_result number;
2045 l_condition_count number;
2046 l_valid_condition_group number;
2047 l_db_object_name varchar2(30);
2048
2049 TYPE T_NUM is TABLE OF NUMBER;
2050 TYPE T_V1 is TABLE OF VARCHAR2(1);
2051 TYPE T_V30 is TABLE OF VARCHAR2(30);
2052
2053 t_constraint_ids T_NUM := T_NUM();
2054 t_entity_ids T_NUM := T_NUM();
2055 t_on_operation_actions T_NUM := T_NUM();
2056 t_column_names T_V30 := T_V30();
2057
2058 -- Cursor to select all constraints for the CREATE operation
2059 CURSOR C_CREATE_OP
2060 IS
2061 SELECT
2062 c.constraint_id, c.entity_id
2063 ,c.on_operation_action, c.column_name
2064 FROM oe_pc_constraints c
2065 WHERE c.entity_id = P_ENTITY_ID
2066 AND c.constrained_operation = OE_PC_GLOBALS.CREATE_OP
2067 AND EXISTS (
2068 SELECT 'EXISTS'
2069 FROM OE_PC_ASSIGNMENTS A
2070 WHERE a.constraint_id = c.constraint_id
2071 AND ( a.responsibility_id = p_responsibility_id
2072 OR a.responsibility_id IS NULL)
2073 AND ( a.application_id = p_application_id
2074 OR a.application_id IS NULL)
2075 AND NOT EXISTS (
2076 SELECT 'EXISTS'
2077 FROM OE_PC_EXCLUSIONS e
2078 WHERE e.responsibility_id = p_responsibility_id
2079 AND e.assignment_id = a.assignment_id
2080 AND e.application_id = p_application_id
2081 )
2082 )
2083 AND nvl(c.enabled_flag, 'Y') = 'Y'
2084 AND ((p_qualifier_attribute IS NULL)
2085 OR nvl(c.qualifier_attribute, p_qualifier_attribute) = p_qualifier_attribute)
2086 ORDER BY c.on_operation_action;
2087
2088
2089 -- Cursor to select all constraints for the UPDATE operation
2090 CURSOR C_UPDATE_OP
2091 IS
2092 SELECT
2093 c.constraint_id, c.entity_id
2094 ,c.on_operation_action, c.column_name
2095 FROM oe_pc_constraints c
2096 WHERE c.entity_id = P_ENTITY_ID
2097 AND c.constrained_operation = OE_PC_GLOBALS.UPDATE_OP
2098 -- if p_column_name is NULL then check only for constraints with NULL column
2099 -- name
2100 -- if check_all_cols_constraint = 'N', then check for constraint with
2101 -- column_name = p_column_name (do not check for NULL column_name) but
2102 -- if check_all_cols_constraint = 'Y', then check for constraint with
2103 -- column_name = p_column_name or NULL column_name.
2104 AND ( (c.column_name is null
2105 AND p_column_name is null)
2106 OR (p_check_all_cols_constraint = 'N'
2107 AND c.column_name = p_column_name)
2108 OR (p_check_all_cols_constraint = 'Y'
2109 AND (c.column_name = p_column_name OR c.column_name is null))
2110 )
2111 -- if caller is defaulting then DO NOT CHECK those constraints
2112 -- that have honored_by_def_flag = 'N'
2113 AND decode(honored_by_def_flag,'N',decode(p_is_caller_defaulting,'Y','N','Y'),
2114 nvl(honored_by_def_flag,'Y')) = 'Y'
2115 AND EXISTS (
2116 SELECT 'EXISTS'
2117 FROM OE_PC_ASSIGNMENTS A
2118 WHERE a.constraint_id = c.constraint_id
2119 AND ( a.responsibility_id = p_responsibility_id
2120 OR a.responsibility_id IS NULL)
2121 AND ( a.application_id =p_application_id --added for bug3631547
2122 OR a.application_id IS NULL )
2123 AND NOT EXISTS (
2124 SELECT 'EXISTS'
2125 FROM OE_PC_EXCLUSIONS e
2126 WHERE e.responsibility_id = p_responsibility_id
2127 AND e.assignment_id = a.assignment_id
2128 AND e.application_id = p_application_id
2129 )
2130 )
2131 AND nvl(c.enabled_flag, 'Y') = 'Y'
2132 AND ((p_qualifier_attribute IS NULL)
2133 OR nvl(c.qualifier_attribute, p_qualifier_attribute) = p_qualifier_attribute)
2134 ORDER BY c.on_operation_action;
2135
2136 -- Cursor to select all constraints for other operations
2137 CURSOR C_C
2138 IS
2139 SELECT DISTINCT
2140 c.constraint_id, c.entity_id
2141 ,c.on_operation_action, c.column_name
2142 FROM oe_pc_constraints c,
2143 oe_pc_assignments a
2144 WHERE (a.responsibility_id = p_responsibility_id OR a.responsibility_id IS NULL)
2145 AND a.constraint_id = c.constraint_id
2146 AND c.entity_id = P_ENTITY_ID
2147 AND c.constrained_operation = p_operation
2148 AND (a.application_id = p_application_id OR a.application_id IS NULL) --added for bug3631547
2149 AND NOT EXISTS (
2150 SELECT 'EXISTS'
2151 FROM OE_PC_EXCLUSIONS e
2152 WHERE e.responsibility_id = p_responsibility_id
2153 AND e.assignment_id = a.assignment_id
2154 AND e.application_id = p_application_id
2155 )
2156 AND nvl(c.enabled_flag, 'Y') = 'Y'
2157 AND ((p_qualifier_attribute IS NULL)
2158 OR nvl(c.qualifier_attribute, p_qualifier_attribute) = p_qualifier_attribute)
2159 ORDER BY c.on_operation_action;
2160
2161 l_column_name VARCHAR2(30);
2162 l_index PLS_INTEGER; -- for bug 6473618 NUMBER;
2163
2164 --
2165 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2166 --
2167 BEGIN
2168
2169 IF l_debug_level > 0 THEN
2170 oe_debug_pub.add( 'ENTER OE_PC_CONSTRAINTS_ADMIN_PVT.IS_OP_CONSTRAINED , COLUMN:' || P_COLUMN_NAME , 1 ) ;
2171 END IF;
2172
2173 l_validation_result := OE_PC_GLOBALS.NO;
2174
2175 -- IF OPERATION IS CREATE
2176 IF p_operation = OE_PC_GLOBALS.CREATE_OP THEN
2177
2178 -- FIRST, check for generic (not column-specific) CREATE constraints
2179
2180 IF (p_check_all_cols_constraint = 'Y'
2181 OR p_column_name is null) THEN
2182
2183 OPEN c_create_op;
2184 FETCH c_create_op BULK COLLECT INTO t_constraint_ids,
2185 t_entity_ids,
2186 t_on_operation_actions,
2187 t_column_names;
2188 CLOSE c_create_op;
2189
2190 FOR i IN 1..t_constraint_ids.count LOOP
2191 OE_PC_Constraints_Admin_Pvt.Validate_Constraint (
2192 p_constraint_id => t_constraint_ids(i)
2193 ,p_use_cached_results => p_use_cached_results
2194 ,x_condition_count => l_condition_count
2195 ,x_valid_condition_group => l_valid_condition_group
2196 ,x_result => l_validation_result
2197 );
2198 IF (l_condition_count = 0
2199 OR l_validation_result = OE_PC_GLOBALS.YES) then
2200 l_column_name := t_column_names(i);
2201 x_constraint_id := t_constraint_ids(i);
2202 x_on_operation_action := t_on_operation_actions(i);
2203 x_constraining_conditions_grp := l_valid_condition_group;
2204 EXIT;
2205 END IF;
2206 END LOOP;
2207
2208 END IF;
2209
2210 IF p_column_name IS NULL THEN
2211 GOTO Return_Validation_Result;
2212 END IF;
2213
2214
2215 -- NEXT, If column name is provided, check for update constraints with
2216 -- check_on_insert_flag = 'Y'
2217
2218 Add_To_Check_On_Insert_Cache
2219 (p_entity_id => p_entity_id
2220 ,p_responsibility_id => p_responsibility_id
2221 ,p_application_id => p_application_id); --added for bug3631547
2222
2223 l_index := (p_entity_id)*G_MAX_CONSTRAINTS + 1;
2224
2225 LOOP
2226
2227 IF (NOT G_Check_On_Insert_Cache.EXISTS(l_index))
2228 OR G_Check_On_Insert_Cache(l_index).column_name = FND_API.G_MISS_CHAR
2229 OR G_Check_On_Insert_Cache(l_index).column_name > p_column_name
2230 THEN
2231 EXIT;
2232 END IF;
2233
2234 IF G_Check_On_Insert_Cache(l_index).column_name = p_column_name THEN
2235
2236 OE_PC_Constraints_Admin_Pvt.Validate_Constraint (
2237 p_constraint_id
2238 => G_Check_On_Insert_Cache(l_index).constraint_id
2239 ,p_use_cached_results => p_use_cached_results
2240 ,x_condition_count => l_condition_count
2241 ,x_valid_condition_group => l_valid_condition_group
2242 ,x_result => l_validation_result
2243 );
2244 IF (l_condition_count = 0
2245 OR l_validation_result = OE_PC_GLOBALS.YES) then
2246 l_column_name := G_Check_On_Insert_Cache(l_index).column_name;
2247 x_constraint_id := G_Check_On_Insert_Cache(l_index).constraint_id;
2248 x_on_operation_action := G_Check_On_Insert_Cache(l_index).on_operation_action;
2249 x_constraining_conditions_grp := l_valid_condition_group;
2250 EXIT;
2251 END IF;
2252
2253 END IF;
2254
2255 l_index := l_index+1;
2256
2257 END LOOP;
2258
2259 -- IF OPERATION IS UPDATE
2260 ELSIF p_operation = OE_PC_GLOBALS.UPDATE_OP THEN
2261
2262 OPEN c_update_op;
2263 FETCH c_update_op BULK COLLECT INTO t_constraint_ids,
2264 t_entity_ids,
2265 t_on_operation_actions,
2266 t_column_names;
2267 CLOSE c_update_op;
2268
2269 FOR i IN 1..t_constraint_ids.count LOOP
2270 OE_PC_Constraints_Admin_Pvt.Validate_Constraint (
2271 p_constraint_id => t_constraint_ids(i)
2272 ,p_use_cached_results => p_use_cached_results
2273 ,x_condition_count => l_condition_count
2274 ,x_valid_condition_group => l_valid_condition_group
2275 ,x_result => l_validation_result
2276 );
2277 IF (l_condition_count = 0
2278 OR l_validation_result = OE_PC_GLOBALS.YES) then
2279 l_column_name := t_column_names(i);
2280 x_constraint_id := t_constraint_ids(i);
2281 x_on_operation_action := t_on_operation_actions(i);
2282 x_constraining_conditions_grp := l_valid_condition_group;
2283 EXIT;
2284 END IF;
2285 END LOOP;
2286
2287 -- IF OPERATION IS DELETE, CANCEL or SPLIT
2288 ELSE
2289
2290 OPEN c_c;
2291 FETCH c_c BULK COLLECT INTO t_constraint_ids,
2292 t_entity_ids,
2293 t_on_operation_actions,
2294 t_column_names;
2295 CLOSE c_c;
2296
2297 FOR i IN 1..t_constraint_ids.count LOOP
2298 OE_PC_Constraints_Admin_Pvt.Validate_Constraint (
2299 p_constraint_id => t_constraint_ids(i)
2300 ,p_use_cached_results => p_use_cached_results
2301 ,x_condition_count => l_condition_count
2302 ,x_valid_condition_group => l_valid_condition_group
2303 ,x_result => l_validation_result
2304 );
2305 IF (l_condition_count = 0
2306 OR l_validation_result = OE_PC_GLOBALS.YES) then
2307 l_column_name := t_column_names(i);
2308 x_constraint_id := t_constraint_ids(i);
2309 x_on_operation_action := t_on_operation_actions(i);
2310 x_constraining_conditions_grp := l_valid_condition_group;
2311 EXIT;
2312 END IF;
2313 END LOOP;
2314
2315 END IF;
2316
2317 <<Return_Validation_Result>>
2318
2319 -- Add message to the stack if the operation IS constrained!
2320 IF l_validation_result = OE_PC_GLOBALS.YES THEN
2321
2322 SELECT database_object_name
2323 INTO l_db_object_name
2324 FROM oe_ak_objects_ext
2325 WHERE entity_id = p_entity_id;
2326
2327 IF l_debug_level > 0 THEN
2328 oe_debug_pub.add( 'ADD CONSTRAINT MESSAGE' ) ;
2329 END IF;
2330 -- l_column_name is the name of the column on the constraint
2331 -- This maybe NULL if update on all columns is constrained
2332 -- (even if p_column_name is not null)
2333 OE_PC_Constraints_Admin_PUB.Add_Constraint_Message
2334 ( p_application_id => 660
2335 , p_database_object_name => l_db_object_name
2336 , p_column_name => l_column_name
2337 , p_operation => p_operation
2338 , p_constraint_id => x_constraint_id
2339 , p_group_number => x_constraining_conditions_grp
2340 , p_on_operation_action => x_on_operation_action
2341 );
2342
2343 END IF;
2344
2345 IF l_debug_level > 0 THEN
2346 oe_debug_pub.add( 'EXIT OE_PC_CONSTRAINTS_ADMIN_PVT.IS_OP_CONSTRAINED , RESULT:' ||L_VALIDATION_RESULT , 1 ) ;
2347 END IF;
2348 RETURN l_validation_result;
2349
2350 EXCEPTION
2351 WHEN OTHERS THEN
2352 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2353 OE_MSG_PUB.Add_Exc_Msg
2354 ( G_PKG_NAME
2355 , 'Is_Op_Constrained'
2356 );
2357 END IF;
2358 RETURN OE_PC_GLOBALS.ERROR;
2359 END Is_Op_Constrained;
2360
2361 END Oe_PC_Constraints_Admin_Pvt;