eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.OE_PC_CONSTRAINTS_ADMIN_PUB

Source


1 PACKAGE  BODY Oe_PC_Constraints_Admin_Pub as
2 /* $Header: OEXPPCAB.pls 120.1 2010/11/22 09:02:59 skurella ship $ */
3 
4 -- global variables
5 G_PKG_NAME	 constant varchar2(30) := 'Oe_PC_Constraints_Admin_Pub';
6 
7 -------------------------------------------------------------------
8 PROCEDURE Generate_Constraint_API
9 (
10    p_api_version_number	       in  number,
11    p_application_id            in  number,
12    p_entity_short_name         in  varchar2,
13 x_script_file_name out nocopy varchar2,
14 
15 x_return_status out nocopy varchar2,
16 
17 x_msg_count out nocopy number,
18 
19 x_msg_data out nocopy varchar2
20 
21 )
22 -------------------------------------------------------------------
23 IS
24   l_fileDir			varchar2(255);
25   l_dirSeperator        varchar2(1);
26   l_fileNameTag	      varchar2(255);
27   l_sqlFileName	      varchar2(255);
28   l_specFileName        varchar2(255);
29   l_bodyFileName        varchar2(255);
30   l_sqlFileHandle	      UTL_FILE.FILE_TYPE;
31   l_specFileHandle	UTL_FILE.FILE_TYPE;
32   l_bodyFileHandle	UTL_FILE.FILE_TYPE;
33   l_pkg_name		varchar2(30);
34   l_fileNumber		number;
35   l_pkg_spec		LONG;
36   l_pkg_body 		LONG;
37   l_return_status  	varchar2(1);
38   l_msg_data           varchar2(255);
39   l_msg_count          number;
40   l_app_short_name     OE_PC_ENTITIES_V.APPLICATION_SHORT_NAME%TYPE;
41   l_db_object_name     OE_PC_ENTITIES_V.DB_OBJECT_NAME%TYPE;
42   l_entity_id          OE_PC_ENTITIES_V.ENTITY_ID%TYPE;
43 
44   l_Q                  varchar2(3) := '''';
45   l_NULL               varchar2(10) := l_Q || l_Q;
46   l_DUMMY_COL          varchar2(10) := l_Q || '#NULL'|| l_Q;
47 
48 
49 
50 
51   CURSOR C_FILENO
52   IS SELECT to_char(OE_PC_FILE_SEQUENCE_S.nextval)
53   FROM DUAL;
54 
55   CURSOR C_APP
56   IS SELECT application_short_name, db_object_name, entity_id
57   FROM OE_PC_ENTITIES_V
58   where application_id = p_application_id
59   AND   entity_short_name = p_entity_short_name;
60    l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
61 BEGIN
62      IF l_debug_level  > 0 THEN
63    OE_Debug_PUB.ADD('Generate_Constraint_API: begin ');
64    END IF;
65    OPEN C_APP;
66    Fetch C_APP into l_app_short_name, l_db_object_name, l_entity_id;
67    Close C_APP;
68         IF l_debug_level  > 0 THEN
69    OE_Debug_PUB.ADD('Entity ID: '||l_entity_id);
70    END IF;
71 
72 
73    l_fileDir := ltrim(rtrim(fnd_profile.value('OE_PC_FILE_DIRECTORY')));
74    --------------------------------------------
75    -- ***** Remove this
76    if (l_fileDir IS NULL OR l_fileDir = '' ) then
77      -- l_fileDir := '/oedev/oe/11.8/debug_log';
78       l_fileDir := '/sqlcom/log';
79    end if;
80    --------------------------------------------
81    if (l_fileDir IS NULL OR l_fileDir = '') then
82       -- raise error;
83       fnd_message.set_name('OE', 'OE_PC_FILE_DIRECTORY_MISSING');
84       --
85      IF l_debug_level  > 0 THEN
86       OE_Debug_PUB.ADD('couldnt find the log file directory.. profile missing..returning. End');
87      END IF;
88       --
89       -- ** In future, the PCFWK admin may maintain a log file and log the errors in it.
90       return;
91    end if;
92    --
93      IF l_debug_level  > 0 THEN
94    OE_Debug_PUB.ADD('log file directory : ' || l_fileDir);
95    END IF;
96    --
97    open  C_FILENO;
98    fetch C_FILENO into l_fileNumber;
99    close C_FILENO;
100 
101 
102    -- file names: example:
103    --     SQL File   : OEPC200_OE_HEADER.SQL
104    --     Spec File  : OEPC200_OE_HEADER_S.PLS
105    --     Body File  : OEPC200_OE_HEADER_B.PLS
106    l_fileNameTag := 'OEPC'|| to_char(l_fileNumber) || '_' ||
107                        l_app_short_name ||'_' || p_entity_short_name;
108    l_sqlFileName := l_fileNameTag || '.SQL';
109    l_specFileName := l_fileNameTag || '_S.PLS';
110    l_bodyFileName := l_fileNameTag || '_B.PLS';
111 
112    --
113      IF l_debug_level  > 0 THEN
114    OE_Debug_PUB.ADD('script (SQL) file name : ' || l_sqlFileName || ' DIR: ' ||l_fileDir);
115    END IF;
116    --
117    l_sqlFileHandle := utl_file.fopen(
118 		      	location	=>  l_fileDir
119 			      ,filename	=>  l_sqlFileName
120 	  	            ,open_mode	=>  'w'
121 				);
122    --
123      IF l_debug_level  > 0 THEN
124    OE_Debug_PUB.ADD('Spec file name : ' || l_specFileName || ' DIR: ' ||l_fileDir);
125      END IF;
126    --
127    l_specFileHandle := utl_file.fopen(
128 		      	location	=>  l_fileDir
129 			      ,filename	=>  l_specFileName
130 	  	            ,open_mode	=>  'w'
131 				);
132    --
133      IF l_debug_level  > 0 THEN
134    OE_Debug_PUB.ADD('Body file name : ' || l_bodyFileName || ' DIR: ' ||l_fileDir);
135    END IF;
136    --
137    l_bodyFileHandle := utl_file.fopen(
138 		      	location	=>  l_fileDir
139 			      ,filename	=>  l_bodyFileName
140 	  	            ,open_mode	=>  'w'
141 				);
142 
143 
144   l_pkg_name := l_app_short_name || '_' || p_entity_short_name || '_PCFWK';
145 
146   -- generate the package spec and body
147   -- ** enhance the standardization of the code by applying the prevailing coding standards
148   -- ** and by adding more comments
149   -----------------------------------------------------------------------------------------
150   -- generate spec
151   utl_file.put_line(l_specFileHandle, '--  ');
152   utl_file.put_line(l_specFileHandle, '--  Copyright (c) 1996 Oracle Corporation, Redwood Shores, CA, USA ');
153   utl_file.put_line(l_specFileHandle, '--  All rights reserved. ');
154   utl_file.put_line(l_specFileHandle, '--  ');
155   utl_file.put_line(l_specFileHandle, '--  FILENAME ');
156   utl_file.put_line(l_specFileHandle, '--  ');
157   utl_file.put_line(l_specFileHandle, '--   ' || l_specFileName);
158   utl_file.put_line(l_specFileHandle, '--  ');
159   utl_file.put_line(l_specFileHandle, '--  DESCRIPTION ');
160   utl_file.put_line(l_specFileHandle, '--  ');
161   utl_file.put_line(l_specFileHandle, '--      Spec of package ' || l_pkg_name );
162   utl_file.put_line(l_specFileHandle, '--  ');
163   utl_file.put_line(l_specFileHandle, '--  NOTES ');
164   utl_file.put_line(l_specFileHandle, '--  ');
165   utl_file.put_line(l_specFileHandle, 'WHENEVER SQLERROR EXIT FAILURE ROLLBACK; ');
166   utl_file.put_line(l_specFileHandle, ' ');
167   utl_file.put_line(l_specFileHandle, 'CREATE OR REPLACE PACKAGE ' || l_pkg_name);
168   utl_file.put_line(l_specFileHandle, 'AS ');
169   utl_file.put_line(l_specFileHandle, ' ');
170   utl_file.put_line(l_specFileHandle, 'g_record  ' || l_db_object_name ||'%ROWTYPE;');
171   utl_file.put_line(l_specFileHandle, '------------------------------------------- ');
172   utl_file.put_line(l_specFileHandle, '--  Start of Comments ');
173   utl_file.put_line(l_specFileHandle, '--  API name    Is_Op_Constrained ');
174   utl_file.put_line(l_specFileHandle, '--  Type        Public ');
175   utl_file.put_line(l_specFileHandle, '--  Function ');
176   utl_file.put_line(l_specFileHandle, '--     You should use this function to check for constraints ');
177   utl_file.put_line(l_specFileHandle, '--     against operations on ' || p_entity_short_name || ' or its columns ');
178   utl_file.put_line(l_specFileHandle, '--  Pre-reqs ');
179   utl_file.put_line(l_specFileHandle, '--  ');
180   utl_file.put_line(l_specFileHandle, '--  Parameters ');
181   utl_file.put_line(l_specFileHandle, '--  ');
182   utl_file.put_line(l_specFileHandle, '--  Return ');
183   utl_file.put_line(l_specFileHandle, '--  ');
184   utl_file.put_line(l_specFileHandle, '--  Version     Current version = 1.0 ');
185   utl_file.put_line(l_specFileHandle, '--              Initial version = 1.0 ');
186   utl_file.put_line(l_specFileHandle, '--  ');
187   utl_file.put_line(l_specFileHandle, '--  Notes ');
188   utl_file.put_line(l_specFileHandle, '--  ');
189   utl_file.put_line(l_specFileHandle, '--  End of Comments ');
190   utl_file.put_line(l_specFileHandle, 'FUNCTION Is_Op_Constrained ');
191   utl_file.put_line(l_specFileHandle, ' ( ');
192   utl_file.put_line(l_specFileHandle, '   p_responsibility_id             in number ');
193   utl_file.put_line(l_specFileHandle, '   ,p_application_id               in number default NULL'); --added for bug3631547
194   utl_file.put_line(l_specFileHandle, '   ,p_operation                    in varchar2 ');
195   utl_file.put_line(l_specFileHandle, '   ,p_column_name                  in varchar2 default NULL');
196   utl_file.put_line(l_specFileHandle, '   ,p_record                       in '||l_db_object_name || '%ROWTYPE');
197   utl_file.put_line(l_specFileHandle, '   ,p_check_all_cols_constraint    in varchar2 default ''Y''');
198   utl_file.put_line(l_specFileHandle, '   ,p_is_caller_defaulting         in varchar2 default ''N''');
199 utl_file.put_line(l_specFileHandle, ' ,x_constraint_id out nocopy number');
200 
201 utl_file.put_line(l_specFileHandle, ' ,x_constraining_conditions_grp out nocopy number');
202 
203 utl_file.put_line(l_specFileHandle, ' ,x_on_operation_action out nocopy number');
204 
205   utl_file.put_line(l_specFileHandle, ' ) ');
206   utl_file.put_line(l_specFileHandle, ' RETURN NUMBER; ');
207   utl_file.put_line(l_specFileHandle, ' ');
208   utl_file.put_line(l_specFileHandle, '------------------------------------------- ');
209   utl_file.put_line(l_specFileHandle, 'END ' || l_pkg_name || ';');
210   utl_file.put_line(l_specFileHandle , '/');
211   utl_file.put_line(l_specFileHandle , 'COMMIT;');
212   --utl_file.put_line(l_specFileHandle , 'EXIT;');
213   utl_file.fclose(l_specFileHandle);
214      IF l_debug_level  > 0 THEN
215   OE_Debug_PUB.ADD('generate body');
216      END IF;
217 
218   --generate body
219   utl_file.put_line(l_bodyFileHandle, '--  ');
220   utl_file.put_line(l_bodyFileHandle, '--  Copyright (c) 1996 Oracle Corporation, Redwood Shores, CA, USA ');
221   utl_file.put_line(l_bodyFileHandle, '--  All rights reserved. ');
222   utl_file.put_line(l_bodyFileHandle, '--  ');
223   utl_file.put_line(l_bodyFileHandle, '--  FILENAME ');
224   utl_file.put_line(l_bodyFileHandle, '--  ');
225   utl_file.put_line(l_bodyFileHandle, '--   ' || l_bodyFileName);
226   utl_file.put_line(l_bodyFileHandle, '--  ');
227   utl_file.put_line(l_bodyFileHandle, '--  DESCRIPTION ');
228   utl_file.put_line(l_bodyFileHandle, '--  ');
229   utl_file.put_line(l_bodyFileHandle, '--      Body of package ' || l_pkg_name );
230   utl_file.put_line(l_bodyFileHandle, '--  ');
231   utl_file.put_line(l_bodyFileHandle, '--  NOTES ');
232   utl_file.put_line(l_bodyFileHandle, '--  ');
233   utl_file.put_line(l_bodyFileHandle, 'WHENEVER SQLERROR EXIT FAILURE ROLLBACK; ');
234   utl_file.put_line(l_bodyFileHandle, ' ');
235 
236   utl_file.put_line(l_bodyFileHandle , 'CREATE OR REPLACE PACKAGE  BODY ' || l_pkg_name);
237   utl_file.put_line(l_bodyFileHandle , 'AS ');
238   utl_file.put_line(l_bodyFileHandle , ' ');
239 
240   utl_file.put_line(l_bodyFileHandle , '-- Globals ');
241   utl_file.put_line(l_bodyFileHandle , '------------------------------------------- ');
242   utl_file.put_line(l_bodyFileHandle , ' g_application_id     constant number := ' || to_char(p_application_id) || ';');
243   utl_file.put_line(l_bodyFileHandle , ' g_entity_id          constant number := ' || to_char(l_entity_id) || ';');
244   utl_file.put_line(l_bodyFileHandle , ' g_entity_short_name  constant varchar2(15) := ' || '''' ||p_entity_short_name || '''' || ';');
245      IF l_debug_level  > 0 THEN
246   OE_Debug_PUB.ADD('generateValidate_Constraint ');
247      END IF;
248 
249   utl_file.put_line(l_bodyFileHandle , '------------------------------------------- ');
250   utl_file.put_line(l_bodyFileHandle , 'PROCEDURE Validate_Constraint ');
251   utl_file.put_line(l_bodyFileHandle , ' ( ');
252   utl_file.put_line(l_bodyFileHandle , '    p_constraint_id                in  number');
253 utl_file.put_line(l_bodyFileHandle , ' ,x_condition_count out nocopy number');
254 
255 utl_file.put_line(l_bodyFileHandle , ' ,x_valid_condition_group out nocopy number');
256 
257 utl_file.put_line(l_bodyFileHandle , ' ,x_result out nocopy number');
258 
259   utl_file.put_line(l_bodyFileHandle , ' ) ');
260   utl_file.put_line(l_bodyFileHandle , ' IS ');
261   utl_file.put_line(l_bodyFileHandle , '  ');
262   utl_file.put_line(l_bodyFileHandle , ' --Cursors');
263   utl_file.put_line(l_bodyFileHandle , ' CURSOR C_R ');
264   utl_file.put_line(l_bodyFileHandle , ' IS SELECT ');
265   utl_file.put_line(l_bodyFileHandle , '       condition_id,');
266   utl_file.put_line(l_bodyFileHandle , '       group_number, ');
267   utl_file.put_line(l_bodyFileHandle , '       modifier_flag, ');
268   utl_file.put_line(l_bodyFileHandle , '       validation_application_id,');
269   utl_file.put_line(l_bodyFileHandle , '       validation_entity_short_name,');
270   utl_file.put_line(l_bodyFileHandle , '       validation_tmplt_short_name,');
271   utl_file.put_line(l_bodyFileHandle , '       record_set_short_name,');
272   utl_file.put_line(l_bodyFileHandle , '       scope_op,');
273   utl_file.put_line(l_bodyFileHandle , '       validation_pkg,');
274   utl_file.put_line(l_bodyFileHandle , '       validation_proc');
275   utl_file.put_line(l_bodyFileHandle , ' FROM  oe_pc_conditions_v');
276   utl_file.put_line(l_bodyFileHandle , ' WHERE constraint_id = p_constraint_id');
277   utl_file.put_line(l_bodyFileHandle , ' ORDER BY group_number;');
278   utl_file.put_line(l_bodyFileHandle , ' ');
279   utl_file.put_line(l_bodyFileHandle , '   ');
280   utl_file.put_line(l_bodyFileHandle , ' TYPE ConstraintRule_Rec_Type IS RECORD');
281   utl_file.put_line(l_bodyFileHandle , ' (  ');
282   utl_file.put_line(l_bodyFileHandle , '     condition_id                   number,');
283   utl_file.put_line(l_bodyFileHandle , '     group_number                   number,');
284   utl_file.put_line(l_bodyFileHandle , '     modifier_flag	                varchar2(1),');
285   utl_file.put_line(l_bodyFileHandle , '     validation_application_id      number,');
286   utl_file.put_line(l_bodyFileHandle , '     validation_entity_short_name   varchar2(15),');
287   utl_file.put_line(l_bodyFileHandle , '     validation_tmplt_short_name    varchar2(8),');
288   utl_file.put_line(l_bodyFileHandle , '     record_set_short_name          varchar2(8),');
289   utl_file.put_line(l_bodyFileHandle , '     scope_op	                      varchar2(3),');
290   utl_file.put_line(l_bodyFileHandle , '     validation_pkg	                varchar2(30),');
291   utl_file.put_line(l_bodyFileHandle , '     validation_proc	          varchar2(30)');
292   utl_file.put_line(l_bodyFileHandle , ' );');
293   utl_file.put_line(l_bodyFileHandle , '   ');
294   utl_file.put_line(l_bodyFileHandle , ' l_constraintRuleRec  ConstraintRule_Rec_Type;');
295   utl_file.put_line(l_bodyFileHandle , ' l_dsqlCursor		  integer;');
296   utl_file.put_line(l_bodyFileHandle , ' l_dynamicSqlString	  varchar2(2000);');
297   utl_file.put_line(l_bodyFileHandle , ' l_rule_count	        number;');
298   utl_file.put_line(l_bodyFileHandle , ' l_ConstrainedStatus  number;');
299   utl_file.put_line(l_bodyFileHandle , ' l_dummy              integer;');
300   utl_file.put_line(l_bodyFileHandle , ' i                    number;');
301   utl_file.put_line(l_bodyFileHandle , ' l_tempResult         boolean;');
302   utl_file.put_line(l_bodyFileHandle , ' l_result_01          number;');
303   utl_file.put_line(l_bodyFileHandle , ' l_currGrpNumber      number;');
304   utl_file.put_line(l_bodyFileHandle , ' l_currGrpResult      boolean;');
305   utl_file.put_line(l_bodyFileHandle , 'BEGIN ');
306   utl_file.put_line(l_bodyFileHandle , ' ');
307   utl_file.put_line(l_bodyFileHandle , '   l_ConstrainedStatus := OE_PC_GLOBALS.NO;');
308   utl_file.put_line(l_bodyFileHandle , '   l_rule_count := 0;');
309   utl_file.put_line(l_bodyFileHandle , '   i := 0;');
310   utl_file.put_line(l_bodyFileHandle , '   l_currGrpNumber := -1;');
311   utl_file.put_line(l_bodyFileHandle , '   l_currGrpResult := FALSE;');
312   utl_file.put_line(l_bodyFileHandle , ' ');
313   utl_file.put_line(l_bodyFileHandle , '   OPEN C_R;');
314   utl_file.put_line(l_bodyFileHandle , '   LOOP  -- validatate constraining conditions');
315   utl_file.put_line(l_bodyFileHandle , '      -- fetch all the validation procedure_names assigned to the constraint and ');
316   utl_file.put_line(l_bodyFileHandle , '	    -- build the dynamic sql string ');
317   utl_file.put_line(l_bodyFileHandle , '      FETCH C_R into ');
318   utl_file.put_line(l_bodyFileHandle , '		  	l_constraintRuleRec.condition_id,');
319   utl_file.put_line(l_bodyFileHandle , '		  	l_constraintRuleRec.group_number,');
320   utl_file.put_line(l_bodyFileHandle , '		  	l_constraintRuleRec.modifier_flag,');
321   utl_file.put_line(l_bodyFileHandle , '		  	l_constraintRuleRec.validation_application_id,');
322   utl_file.put_line(l_bodyFileHandle , '		  	l_constraintRuleRec.validation_entity_short_name,');
323   utl_file.put_line(l_bodyFileHandle , '		  	l_constraintRuleRec.validation_tmplt_short_name,');
324   utl_file.put_line(l_bodyFileHandle , '		  	l_constraintRuleRec.record_set_short_name,');
325   utl_file.put_line(l_bodyFileHandle , '		  	l_constraintRuleRec.scope_op,');
326   utl_file.put_line(l_bodyFileHandle , '		  	l_constraintRuleRec.validation_pkg,');
327   utl_file.put_line(l_bodyFileHandle , '		  	l_constraintRuleRec.validation_proc;');
328   utl_file.put_line(l_bodyFileHandle , ' ');
329   utl_file.put_line(l_bodyFileHandle , '      -- EXIT from loop ');
330   utl_file.put_line(l_bodyFileHandle , '      IF (C_R%NOTFOUND) THEN');
331   utl_file.put_line(l_bodyFileHandle , '         IF (l_currGrpNumber != -1 AND l_currGrpResult = TRUE) THEN');
332   utl_file.put_line(l_bodyFileHandle , '            l_ConstrainedStatus := OE_PC_GLOBALS.YES;');
333   utl_file.put_line(l_bodyFileHandle , '         END IF;');
334   utl_file.put_line(l_bodyFileHandle , '         EXIT;  -- exit the loop');
335   utl_file.put_line(l_bodyFileHandle , '      END IF;');
336   utl_file.put_line(l_bodyFileHandle , ' ');
337   utl_file.put_line(l_bodyFileHandle , '      IF (l_currGrpNumber != l_constraintRuleRec.group_number) THEN');
338   utl_file.put_line(l_bodyFileHandle , ' ');
339   utl_file.put_line(l_bodyFileHandle , '         -- we are entering the new group of conditions.. ');
340   utl_file.put_line(l_bodyFileHandle , '         -- groups are ORd together, so if the previous group was evaluated');
341   utl_file.put_line(l_bodyFileHandle , '         -- to TRUE (OE_PC_GLOBALS.YES) then no need to evaluvate this group.');
342   utl_file.put_line(l_bodyFileHandle , '         IF (l_currGrpResult = TRUE) THEN');
343   utl_file.put_line(l_bodyFileHandle , '            l_ConstrainedStatus := OE_PC_GLOBALS.YES;');
344   utl_file.put_line(l_bodyFileHandle , '            EXIT;  -- exit the loop');
345   utl_file.put_line(l_bodyFileHandle , '         END IF;');
346   utl_file.put_line(l_bodyFileHandle , ' ');
347   utl_file.put_line(l_bodyFileHandle , '         -- previous group did not evaluvate to TRUE, so lets pursue this new group');
348   utl_file.put_line(l_bodyFileHandle , '         l_currGrpNumber := l_constraintRuleRec.group_number;');
349   utl_file.put_line(l_bodyFileHandle , '         l_currGrpResult := FALSE;');
350   utl_file.put_line(l_bodyFileHandle , '         i := 0;');
351   utl_file.put_line(l_bodyFileHandle , '      END IF;');
352   utl_file.put_line(l_bodyFileHandle , '      -- we have a got a record, increment the count by 1');
353   utl_file.put_line(l_bodyFileHandle , '      l_rule_count := l_rule_count+1;');
354   utl_file.put_line(l_bodyFileHandle , ' ');
355   utl_file.put_line(l_bodyFileHandle , '      -- pkg.function(p1, p2, ...)');
356   utl_file.put_line(l_bodyFileHandle , '      l_dynamicSqlString := '' begin '';');
357   utl_file.put_line(l_bodyFileHandle , '      l_dynamicSqlString := l_dynamicSqlString || l_constraintRuleRec.validation_pkg ||''.'';');
358   utl_file.put_line(l_bodyFileHandle , '      l_dynamicSqlString := l_dynamicSqlString || l_constraintRuleRec.validation_proc;');
359   utl_file.put_line(l_bodyFileHandle , ' ');
360   utl_file.put_line(l_bodyFileHandle , '      -- IN Parameters');
361   utl_file.put_line(l_bodyFileHandle , '      l_dynamicSqlString := l_dynamicSqlString || ''( '';');
362   utl_file.put_line(l_bodyFileHandle , '      l_dynamicSqlString := l_dynamicSqlString || '':t_application_id, '';');
363   utl_file.put_line(l_bodyFileHandle , '      l_dynamicSqlString := l_dynamicSqlString || '':t_entity_short_name, '';');
364   utl_file.put_line(l_bodyFileHandle , '      l_dynamicSqlString := l_dynamicSqlString || '':t_validation_entity_short_name, '';');
365   utl_file.put_line(l_bodyFileHandle , '      l_dynamicSqlString := l_dynamicSqlString || '':t_validation_tmplt_short_name, '';');
366   utl_file.put_line(l_bodyFileHandle , '      l_dynamicSqlString := l_dynamicSqlString || '':t_record_set_short_name, '';');
367   utl_file.put_line(l_bodyFileHandle , '      l_dynamicSqlString := l_dynamicSqlString || '':t_scope, '';');
368 
369   utl_file.put_line(l_bodyFileHandle , ' ');
370   utl_file.put_line(l_bodyFileHandle , '      -- OUT Parameters ');
371   utl_file.put_line(l_bodyFileHandle , '      -- OUT Parameters ');
372   utl_file.put_line(l_bodyFileHandle , '      l_dynamicSqlString := l_dynamicSqlString || '':t_result );'';');
373   utl_file.put_line(l_bodyFileHandle , '      l_dynamicSqlString := l_dynamicSqlString || '' end; '';');
374   utl_file.put_line(l_bodyFileHandle , '      -- open the dynamic sql cursor');
375   utl_file.put_line(l_bodyFileHandle , '      l_dsqlCursor := dbms_sql.open_cursor;');
376   utl_file.put_line(l_bodyFileHandle , ' ');
377   utl_file.put_line(l_bodyFileHandle , '      -- parse the validator sql');
378   utl_file.put_line(l_bodyFileHandle , '      dbms_sql.parse(l_dsqlCursor, l_dynamicSqlString, DBMS_SQL.NATIVE);');
379   utl_file.put_line(l_bodyFileHandle , '      -- give the bind variables');
380   utl_file.put_line(l_bodyFileHandle , '      -- variables for IN parameters');
381   utl_file.put_line(l_bodyFileHandle , '	    dbms_sql.bind_variable(l_dsqlCursor, '':t_application_id'',    g_application_id);');
382   utl_file.put_line(l_bodyFileHandle , '	    dbms_sql.bind_variable(l_dsqlCursor, '':t_entity_short_name'', g_entity_short_name);');
383   utl_file.put_line(l_bodyFileHandle , '	    dbms_sql.bind_variable(l_dsqlCursor, '':t_validation_entity_short_name'',  l_constraintRuleRec.validation_entity_short_name);');
384   utl_file.put_line(l_bodyFileHandle , '	    dbms_sql.bind_variable(l_dsqlCursor, '':t_validation_tmplt_short_name'',  l_constraintRuleRec.validation_tmplt_short_name);');
385   utl_file.put_line(l_bodyFileHandle , '	    dbms_sql.bind_variable(l_dsqlCursor, '':t_record_set_short_name'',  l_constraintRuleRec.record_set_short_name);');
386   utl_file.put_line(l_bodyFileHandle , '	    dbms_sql.bind_variable(l_dsqlCursor, '':t_scope'',  l_constraintRuleRec.scope_op);');
387   utl_file.put_line(l_bodyFileHandle , ' ');
388   utl_file.put_line(l_bodyFileHandle , '      -- variables for OUT parameters');
389   utl_file.put_line(l_bodyFileHandle , '      -- variables for OUT parameters');
390   utl_file.put_line(l_bodyFileHandle , '      dbms_sql.bind_variable(l_dsqlCursor, '':t_result'', l_result_01);');
391   utl_file.put_line(l_bodyFileHandle , '      -- execute the validator pfunction');
392   utl_file.put_line(l_bodyFileHandle , '      l_dummy := dbms_sql.execute(l_dsqlCursor);');
393   utl_file.put_line(l_bodyFileHandle , ' ');
394   utl_file.put_line(l_bodyFileHandle , '      -- retrieve the values of the OUT variables');
395   utl_file.put_line(l_bodyFileHandle , '      -- retrieve the values of the OUT variables');
396   utl_file.put_line(l_bodyFileHandle , '      dbms_sql.variable_value(l_dsqlCursor, '':t_result'', l_result_01);');
397   utl_file.put_line(l_bodyFileHandle , '      IF (l_result_01 = 0) THEN');
398   utl_file.put_line(l_bodyFileHandle , '         l_tempResult := FALSE;');
399   utl_file.put_line(l_bodyFileHandle , '      ELSE');
400   utl_file.put_line(l_bodyFileHandle , '         l_tempResult := TRUE;');
401   utl_file.put_line(l_bodyFileHandle , '      END IF;');
402   utl_file.put_line(l_bodyFileHandle , '      -- apply the modifier on the result');
403   utl_file.put_line(l_bodyFileHandle , '      if(l_constraintRuleRec.modifier_flag = OE_PC_GLOBALS.YES_FLAG) then');
404   utl_file.put_line(l_bodyFileHandle , '         l_tempResult := NOT(l_tempResult);');
405   utl_file.put_line(l_bodyFileHandle , '      end if;');
406   utl_file.put_line(l_bodyFileHandle , ' ');
407   utl_file.put_line(l_bodyFileHandle , '      IF (i = 0) THEN');
408   utl_file.put_line(l_bodyFileHandle , '         l_currGrpResult := l_tempResult;');
409   utl_file.put_line(l_bodyFileHandle , '      ELSE');
410   utl_file.put_line(l_bodyFileHandle , '         l_currGrpResult := l_currGrpResult AND l_tempResult;');
411   utl_file.put_line(l_bodyFileHandle , '      END IF;');
412   utl_file.put_line(l_bodyFileHandle , '      -- close the cursor');
413   utl_file.put_line(l_bodyFileHandle , '      dbms_sql.close_cursor(l_dsqlCursor); ');
414   utl_file.put_line(l_bodyFileHandle , ' ');
415   utl_file.put_line(l_bodyFileHandle , '      -- increment the index');
416   utl_file.put_line(l_bodyFileHandle , '      i := i+1;');
417   utl_file.put_line(l_bodyFileHandle , '   END LOOP;  -- end validatate validators');
418   utl_file.put_line(l_bodyFileHandle , '   CLOSE C_R;');
419   utl_file.put_line(l_bodyFileHandle , '   -- did we validate any constraint rules?. if there is none then the ');
420   utl_file.put_line(l_bodyFileHandle , '   -- constraint is valid and we will return YES ');
421   utl_file.put_line(l_bodyFileHandle , '   IF (l_rule_count = 0) THEN');
422   utl_file.put_line(l_bodyFileHandle , '      x_condition_count := 0;');
423   utl_file.put_line(l_bodyFileHandle , '      x_valid_condition_group := -1;');
424   utl_file.put_line(l_bodyFileHandle , '      x_result    := OE_PC_GLOBALS.YES;');
425   utl_file.put_line(l_bodyFileHandle , '   ELSE ');
426   utl_file.put_line(l_bodyFileHandle , '      x_condition_count := l_rule_count;');
427   utl_file.put_line(l_bodyFileHandle , '      x_valid_condition_group := l_currGrpNumber;');
428   utl_file.put_line(l_bodyFileHandle , '      x_result    := l_ConstrainedStatus;');
429   utl_file.put_line(l_bodyFileHandle , '   END IF;');
430   utl_file.put_line(l_bodyFileHandle , ' -------------------------------------------');
431   utl_file.put_line(l_bodyFileHandle , ' EXCEPTION ');
432   utl_file.put_line(l_bodyFileHandle , '    WHEN OTHERS THEN ');
433   utl_file.put_line(l_bodyFileHandle , '       x_result := OE_PC_GLOBALS.ERROR; ');
434   utl_file.put_line(l_bodyFileHandle , 'END Validate_Constraint; ');
435   utl_file.put_line(l_bodyFileHandle , '------------------------------------------- ');
436      IF l_debug_level  > 0 THEN
437   OE_Debug_PUB.ADD(' generate Is_Op_Constrained  ');
438      END IF;
439 
440   utl_file.put_line(l_bodyFileHandle , '------------------------------------------- ');
441   utl_file.put_line(l_bodyFileHandle , 'FUNCTION Is_Op_Constrained ');
442   utl_file.put_line(l_bodyFileHandle , ' ( ');
443   utl_file.put_line(l_bodyFileHandle , '   p_responsibility_id             in number ');
444   utl_file.put_line(l_bodyFileHandle , '   ,p_application_id               in number '); --added for bug3631547
445   utl_file.put_line(l_bodyFileHandle , '   ,p_operation                    in varchar2 ');
446   utl_file.put_line(l_bodyFileHandle , '   ,p_column_name                  in varchar2 default NULL');
447   utl_file.put_line(l_bodyFileHandle , '   ,p_record                       in '||l_db_object_name || '%ROWTYPE');
448   utl_file.put_line(l_bodyFileHandle, '   ,p_check_all_cols_constraint    in varchar2 default ''Y''');
449   utl_file.put_line(l_bodyFileHandle, '   ,p_is_caller_defaulting         in varchar2 default ''N''');
450 utl_file.put_line(l_bodyFileHandle , ' ,x_constraint_id out nocopy number');
451 
452 utl_file.put_line(l_bodyFileHandle , ' ,x_constraining_conditions_grp out nocopy number');
453 
454 utl_file.put_line(l_bodyFileHandle , ' ,x_on_operation_action out nocopy number');
455 
456   utl_file.put_line(l_bodyFileHandle , ' ) ');
457   utl_file.put_line(l_bodyFileHandle , ' RETURN NUMBER ');
458   utl_file.put_line(l_bodyFileHandle , ' ');
459   utl_file.put_line(l_bodyFileHandle , ' IS ');
460   utl_file.put_line(l_bodyFileHandle , '  ');
461   utl_file.put_line(l_bodyFileHandle , ' --Cursors');
462   utl_file.put_line(l_bodyFileHandle , ' -------------------------------------------');
463   utl_file.put_line(l_bodyFileHandle , '    CURSOR C_C ');
464   utl_file.put_line(l_bodyFileHandle , '    IS ');
465   utl_file.put_line(l_bodyFileHandle , '    SELECT DISTINCT');
466   utl_file.put_line(l_bodyFileHandle , '      c.constraint_id, c.entity_id');
467   utl_file.put_line(l_bodyFileHandle , '      ,c.on_operation_action');
468   --utl_file.put_line(l_bodyFileHandle , '      ,c.message_name');
469   utl_file.put_line(l_bodyFileHandle , '     FROM  oe_pc_constraints c,');
470   utl_file.put_line(l_bodyFileHandle , '           oe_pc_assignments a');
471   utl_file.put_line(l_bodyFileHandle , '     WHERE (a.responsibility_id = p_responsibility_id OR a.responsibility_id IS NULL)');
472  -- utl_file.put_line(l_bodyFileHandle , '     AND   sysdate BETWEEN nvl(a.start_date_active, sysdate) AND nvl(a.end_date_active, sysdate)');
473   utl_file.put_line(l_bodyFileHandle , '     AND   a.constraint_id = c.constraint_id');
474   utl_file.put_line(l_bodyFileHandle , '     AND   c.entity_id     = G_ENTITY_ID');
475   utl_file.put_line(l_bodyFileHandle , '     AND   c.constrained_operation = p_operation');
476   utl_file.put_line(l_bodyFileHandle , '     AND   a.application_id = p_application_id OR a.application_id IS NULL'); --bug3631547
477   utl_file.put_line(l_bodyFileHandle , '     -- if caller is defaulting then DO NOT CHECK those constraints');
478   utl_file.put_line(l_bodyFileHandle , '     -- that have honored_by_def_flag = ''N''');
479   utl_file.put_line(l_bodyFileHandle , '     AND   decode(honored_by_def_flag,''N'',decode(p_is_caller_defaulting,''Y'',''N'',''Y''),');
480   utl_file.put_line(l_bodyFileHandle , '                nvl(honored_by_def_flag,''Y'')) = ''Y''');
481   utl_file.put_line(l_bodyFileHandle , '     AND   decode(c.column_name, ' || l_NULL || ',decode(p_check_all_cols_constraint,''Y'',');
482   utl_file.put_line(l_bodyFileHandle, '             nvl(p_column_name,'||l_DUMMY_COL||'),'||l_DUMMY_COL||'),c.column_name) = nvl(p_column_name,'||l_DUMMY_COL||')');
483   utl_file.put_line(l_bodyFileHandle , '     AND   NOT EXISTS (');
484   utl_file.put_line(l_bodyFileHandle , '            SELECT ' || l_Q || 'EXISTS' || l_Q);
485   utl_file.put_line(l_bodyFileHandle , '            FROM OE_PC_EXCLUSIONS e');
486   utl_file.put_line(l_bodyFileHandle , '            WHERE e.responsibility_id = p_responsibility_id');
487   utl_file.put_line(l_bodyFileHandle , '            AND   e.assignment_id     = a.assignment_id');
488   utl_file.put_line(l_bodyFileHandle , '            AND e.application_id = p_application_id OR e.application_id IS NULL'); --bug3631547
489  -- utl_file.put_line(l_bodyFileHandle , '            AND   sysdate BETWEEN nvl(e.start_date_active, sysdate)');
490  --  utl_file.put_line(l_bodyFileHandle , '                           AND nvl(e.end_date_active, sysdate)');
491   utl_file.put_line(l_bodyFileHandle , '            );');
492 utl_file.put_line(l_bodyFileHandle , '-- Cursor to select all update constraints that are applicable to insert');
493 utl_file.put_line(l_bodyFileHandle , '-- operations as well.');
494 
495   utl_file.put_line(l_bodyFileHandle , '    CURSOR C_CHECK_ON_INSERT ');
496   utl_file.put_line(l_bodyFileHandle , '    IS ');
497   utl_file.put_line(l_bodyFileHandle , '    SELECT DISTINCT');
498   utl_file.put_line(l_bodyFileHandle , '      c.constraint_id, c.entity_id');
499   utl_file.put_line(l_bodyFileHandle , '      ,c.on_operation_action');
500   --utl_file.put_line(l_bodyFileHandle , '      ,c.message_name');
501   utl_file.put_line(l_bodyFileHandle , '     FROM  oe_pc_constraints c,');
502   utl_file.put_line(l_bodyFileHandle , '           oe_pc_assignments a');
503   utl_file.put_line(l_bodyFileHandle , '     WHERE (a.responsibility_id = p_responsibility_id OR a.responsibility_id IS NULL)');
504   utl_file.put_line(l_bodyFileHandle , '     AND   a.application_id = p_application_id OR a.application_id IS NULL'); --bug3631547
505  -- utl_file.put_line(l_bodyFileHandle , '     AND   sysdate BETWEEN nvl(a.start_date_active, sysdate) AND nvl(a.end_date_active, sysdate)');
506   utl_file.put_line(l_bodyFileHandle , '     AND   a.constraint_id = c.constraint_id');
507   utl_file.put_line(l_bodyFileHandle , '     AND   c.entity_id     = G_ENTITY_ID');
508   utl_file.put_line(l_bodyFileHandle , '     AND   c.constrained_operation = OE_PC_GLOBALS.UPDATE_OP');
509   utl_file.put_line(l_bodyFileHandle , '     AND   c.check_on_insert_flag = ''Y''');
510   utl_file.put_line(l_bodyFileHandle , '     AND   nvl(c.column_name, ' || l_DUMMY_COL || ') = p_column_name');
511   utl_file.put_line(l_bodyFileHandle , '     -- if caller is defaulting then DO NOT CHECK those constraints');
512   utl_file.put_line(l_bodyFileHandle , '     -- that have honored_by_def_flag = ''N''');
513   utl_file.put_line(l_bodyFileHandle , '     AND   decode(honored_by_def_flag,''N'',decode(p_is_caller_defaulting,''Y'',''N'',''Y''),');
514   utl_file.put_line(l_bodyFileHandle , '                nvl(honored_by_def_flag,''Y'')) = ''Y''');
515   utl_file.put_line(l_bodyFileHandle , '     AND   NOT EXISTS (');
516   utl_file.put_line(l_bodyFileHandle , '            SELECT ' || l_Q || 'EXISTS' || l_Q);
517   utl_file.put_line(l_bodyFileHandle , '            FROM OE_PC_EXCLUSIONS e');
518   utl_file.put_line(l_bodyFileHandle , '            WHERE e.responsibility_id = p_responsibility_id');
519   utl_file.put_line(l_bodyFileHandle , '            AND   e.assignment_id     = a.assignment_id');
520   utl_file.put_line(l_bodyFileHandle , '            AND e.application_id = p_application_id OR e.application_id IS NULL'); --bug3631547
521 --  utl_file.put_line(l_bodyFileHandle , '            AND   sysdate BETWEEN nvl(e.start_date_active, sysdate)');
522 --  utl_file.put_line(l_bodyFileHandle , '                           AND nvl(e.end_date_active, sysdate)');
523   utl_file.put_line(l_bodyFileHandle , '            );');
524   utl_file.put_line(l_bodyFileHandle , ' --Local Variables');
525   utl_file.put_line(l_bodyFileHandle , ' -------------------------------------------');
526   utl_file.put_line(l_bodyFileHandle , '    l_validation_result   	number;');
527   utl_file.put_line(l_bodyFileHandle , '    l_condition_count     	number;');
528   utl_file.put_line(l_bodyFileHandle , '    l_valid_condition_group   	number;');
529   utl_file.put_line(l_bodyFileHandle , ' BEGIN ');
530   utl_file.put_line(l_bodyFileHandle , '    g_record   := p_record;  ');
531   utl_file.put_line(l_bodyFileHandle , '    l_validation_result   := OE_PC_GLOBALS.NO;  ');
532   utl_file.put_line(l_bodyFileHandle , '    FOR c_rec in C_C LOOP  ');
533   utl_file.put_line(l_bodyFileHandle , '        Validate_Constraint ( ');
534   utl_file.put_line(l_bodyFileHandle , '              p_constraint_id   	=> c_rec.constraint_id');
535   utl_file.put_line(l_bodyFileHandle , '              ,x_condition_count       => l_condition_count');
536   utl_file.put_line(l_bodyFileHandle , '              ,x_valid_condition_group => l_valid_condition_group');
537   utl_file.put_line(l_bodyFileHandle , '              ,x_result                => l_validation_result');
538   utl_file.put_line(l_bodyFileHandle , '              );');
539   utl_file.put_line(l_bodyFileHandle , '       IF (l_condition_count = 0 OR l_validation_result = OE_PC_GLOBALS.YES) then');
540   utl_file.put_line(l_bodyFileHandle , '          x_constraint_id           := c_rec.constraint_id;');
541   utl_file.put_line(l_bodyFileHandle , '          x_on_operation_action     := c_rec.on_operation_action;');
542   --utl_file.put_line(l_bodyFileHandle , '          x_message_name                  := c_rec.message_name;');
543   utl_file.put_line(l_bodyFileHandle , '          x_constraining_conditions_grp   := l_valid_condition_group;');
544   utl_file.put_line(l_bodyFileHandle , '          EXIT;');
545   utl_file.put_line(l_bodyFileHandle , '       END IF;');
546   utl_file.put_line(l_bodyFileHandle , '    END LOOP;');
547   utl_file.put_line(l_bodyFileHandle , '  IF ( p_operation = OE_PC_GLOBALS.CREATE_OP');
548   utl_file.put_line(l_bodyFileHandle , '       AND l_validation_result = OE_PC_GLOBALS.NO');
549   utl_file.put_line(l_bodyFileHandle , '       AND p_column_name IS NOT NULL) THEN');
550   utl_file.put_line(l_bodyFileHandle , '    FOR c_rec in C_CHECK_ON_INSERT LOOP');
551   utl_file.put_line(l_bodyFileHandle , '        Validate_Constraint ( ');
552   utl_file.put_line(l_bodyFileHandle , '              p_constraint_id   	=> c_rec.constraint_id');
553   utl_file.put_line(l_bodyFileHandle , '              ,x_condition_count       => l_condition_count');
554   utl_file.put_line(l_bodyFileHandle , '              ,x_valid_condition_group => l_valid_condition_group');
555   utl_file.put_line(l_bodyFileHandle , '              ,x_result                => l_validation_result');
556   utl_file.put_line(l_bodyFileHandle , '              );');
557   utl_file.put_line(l_bodyFileHandle , '       IF (l_condition_count = 0 OR l_validation_result = OE_PC_GLOBALS.YES) then');
558   utl_file.put_line(l_bodyFileHandle , '          x_constraint_id           := c_rec.constraint_id;');
559   utl_file.put_line(l_bodyFileHandle , '          x_on_operation_action     := c_rec.on_operation_action;');
560   --utl_file.put_line(l_bodyFileHandle , '          x_message_name                  := c_rec.message_name;');
561   utl_file.put_line(l_bodyFileHandle , '          x_constraining_conditions_grp   := l_valid_condition_group;');
562   utl_file.put_line(l_bodyFileHandle , '          EXIT;');
563   utl_file.put_line(l_bodyFileHandle , '       END IF;');
564   utl_file.put_line(l_bodyFileHandle , '    END LOOP;');
565   utl_file.put_line(l_bodyFileHandle , '  END IF;');
566   utl_file.put_line(l_bodyFileHandle , '    return l_validation_result;');
567   utl_file.put_line(l_bodyFileHandle , ' EXCEPTION ');
568   utl_file.put_line(l_bodyFileHandle , '    WHEN OTHERS THEN ');
569   utl_file.put_line(l_bodyFileHandle , '       RETURN OE_PC_GLOBALS.ERROR; ');
570   utl_file.put_line(l_bodyFileHandle , 'END Is_Op_Constrained; ');
571   utl_file.put_line(l_bodyFileHandle , '------------------------------------------- ');
572   utl_file.put_line(l_bodyFileHandle , 'END ' || l_pkg_name || ';');
573   utl_file.put_line(l_bodyFileHandle , '/');
574   utl_file.put_line(l_bodyFileHandle , 'COMMIT;');
575   -- utl_file.put_line(l_bodyFileHandle , 'EXIT;');
576   utl_file.fclose(l_bodyFileHandle);
577 
578   -- write the script to compile the spec and body
579   utl_file.put_line(l_sqlFileHandle , '-- compile the spec');
580   utl_file.put_line(l_sqlFileHandle , '@' || l_specFileName);
581   utl_file.put_line(l_sqlFileHandle , '-- compile the body');
582   utl_file.put_line(l_sqlFileHandle , '@' || l_bodyFileName );
583   utl_file.put_line(l_sqlFileHandle , 'EXIT;');
584   utl_file.fclose(l_sqlFileHandle);
585 
586 EXCEPTION
587 
588   when others then
589      IF l_debug_level  > 0 THEN
590      OE_Debug_PUB.ADD('Oe_PC_Constraints_Admin_Pub.Generate_Constraint_API: EXCEPTION');
591      END IF;
592 END Generate_Constraint_API;
593 ----------------------------------------------------------------------
594 
595 
596 -- FUNCTION Get_Authorized_WF_Roles:
597 -- Returns the list of WF Roles that are NOT constrained
598 -- by the conditions for a given constraint (p_constraint_id).
599 -- The list contains two elements:
600 -- Name: WF_ROLES.NAME
601 -- Display_Name: WF_ROLES.DISPLAY_NAME
602 -- Returns a NULL list if no auth. resps. are found.
603 
604 -- NOTE: This does not mean that these roles can perform the
605 -- constrained operation. There may be other constraints for
606 -- the same operation on this entity that are applicable to this role.
607 
608 -----------------------------------------------------
609 FUNCTION Get_Authorized_WF_Roles
610 (
611   p_constraint_id               IN NUMBER
612 , x_return_status OUT NOCOPY VARCHAR2
613 
614 )
615 RETURN OE_PC_GLOBALS.Authorized_WF_Roles_TBL
616 -----------------------------------------------------
617 IS
618 
619         CURSOR C_ASSIGNED_RESP IS
620         SELECT 'FND_RESP'||R.application_id||':'||R.responsibility_id role_name
621                 ,  R.responsibility_name role_display_name
622         FROM FND_RESPONSIBILITY_VL R
623         WHERE R.responsibility_id NOT IN (SELECT NVL(responsibility_id,R.responsibility_id)
624                                           FROM OE_PC_ASSIGNMENTS
625                                           WHERE CONSTRAINT_ID = p_constraint_id
626                                           )
627 	ORDER BY role_display_name;
628 
629         CURSOR C_EXCLUDED_RESP IS
630         SELECT 'FND_RESP'||R.application_id||':'||R.responsibility_id role_name
631                 ,  R.responsibility_name role_display_name
632         FROM FND_RESPONSIBILITY_VL R
633                 , OE_PC_EXCLUSIONS E
634         WHERE E.assignment_id = (SELECT assignment_id
635                                 FROM oe_pc_assignments
636                                 WHERE constraint_id = p_constraint_id
637                                 AND responsibility_id IS NULL)
638          AND E.responsibility_id = R.responsibility_id
639          AND E.application_id    = R.application_id  --added for bug3631547
640 	ORDER BY role_display_name;
641 
642          I      NUMBER := 1;
643 
644 	 x_WF_Roles_TBL		OE_PC_GLOBALS.Authorized_WF_Roles_TBL;
645    l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
646 BEGIN
647 
648    x_return_status := FND_API.G_RET_STS_SUCCESS;
649 
650 -- If the constraint is applicable to ALL EXCEPT some responsibilities
651 -- then select from the EXCEPT list.
652 
653    FOR C1 IN C_EXCLUDED_RESP LOOP
654 
655           x_WF_Roles_TBL(I).Name := C1.Role_Name;
656           x_WF_Roles_TBL(I).Display_Name := C1.Role_Display_Name;
657 
658           I := I+1;
659 
660    END LOOP;
661 
662 -- If there were NO responsibilities in the ALL EXCEPT list, then
663 -- return all resps. except those that are constrained (OR
664 -- ASSIGNED to this constraint.)
665 
666    IF (I = 1) THEN
667 
668      FOR C2 IN C_ASSIGNED_RESP LOOP
669 
670           x_WF_Roles_TBL(I).Name := C2.Role_Name;
671           x_WF_Roles_TBL(I).Display_Name := C2.Role_Display_Name;
672 
673           I := I+1;
674 
675      END LOOP;
676 
677    END IF;
678 
679   RETURN x_WF_Roles_TBL;
680 
681 EXCEPTION
682 
683   WHEN OTHERS THEN
684 
685         x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
686 
687         IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
688         THEN
689             OE_MSG_PUB.Add_Exc_Msg
690             (   G_PKG_NAME
691             ,   'Get_Authorized_WF_Roles'
692             );
693         END IF;
694 
695 END Get_Authorized_WF_Roles;
696 
697 -- Local procedure that should be called only from add_constraint_message
698 -- procedure
699 -- This sets the correct message on the stack based on the operation
700 -- and also sets the tokens for OBJECT and ATTRIBUTE where needed.
701 -- This does NOT set the REASON token which is added in the
702 -- add_constraint_message procedure itself
703 PROCEDURE Set_Message
704 ( p_operation			IN VARCHAR2
705 , p_group_number         IN VARCHAR2
706 , p_attribute_name       IN VARCHAR2
707 , p_object_name          IN VARCHAR2
708 )
709 IS
710    l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
711 BEGIN
712 
713     IF p_operation = OE_PC_GLOBALS.UPDATE_OP THEN
714     	IF p_attribute_name IS NOT NULL THEN
715 	   IF nvl(p_group_number,-1) = -1 THEN
716 	     FND_MESSAGE.SET_NAME('ONT','OE_PC_UPDATE_FIELD_NO_CONDN');
717 	   ELSE
718 	     FND_MESSAGE.SET_NAME('ONT','OE_PC_UPDATE_FIELD_VIOLATION');
719 	   END IF;
720         FND_MESSAGE.SET_TOKEN('ATTRIBUTE',p_attribute_name);
721 	ELSE
722 	   IF nvl(p_group_number,-1) = -1 THEN
723 	     FND_MESSAGE.SET_NAME('ONT','OE_PC_UPDATE_NO_CONDN');
724 	   ELSE
725 	     FND_MESSAGE.SET_NAME('ONT','OE_PC_UPDATE_VIOLATION');
726 	   END IF;
727 	   FND_MESSAGE.SET_TOKEN('OBJECT',p_object_name);
728     	END IF;
729     ELSIF p_operation = OE_PC_GLOBALS.CREATE_OP THEN
730 	   IF nvl(p_group_number,-1) = -1 THEN
731 	     FND_MESSAGE.SET_NAME('ONT','OE_PC_CREATE_NO_CONDN');
732 	   ELSE
733 	     FND_MESSAGE.SET_NAME('ONT','OE_PC_CREATE_VIOLATION');
734 	   END IF;
735 	   FND_MESSAGE.SET_TOKEN('OBJECT',p_object_name);
736     ELSIF p_operation = OE_PC_GLOBALS.DELETE_OP THEN
737 	   IF nvl(p_group_number,-1) = -1 THEN
738 	     FND_MESSAGE.SET_NAME('ONT','OE_PC_DELETE_NO_CONDN');
739 	   ELSE
740 	     FND_MESSAGE.SET_NAME('ONT','OE_PC_DELETE_VIOLATION');
741 	   END IF;
742 	   FND_MESSAGE.SET_TOKEN('OBJECT',p_object_name);
743     ELSIF p_operation = OE_PC_GLOBALS.CANCEL_OP THEN
744 	   IF nvl(p_group_number,-1) = -1 THEN
745 	     FND_MESSAGE.SET_NAME('ONT','OE_PC_CANCEL_NO_CONDN');
746 	   ELSE
747 	     FND_MESSAGE.SET_NAME('ONT','OE_PC_CANCEL_VIOLATION');
748 	   END IF;
749 	   FND_MESSAGE.SET_TOKEN('OBJECT',p_object_name);
750     ELSIF p_operation = OE_PC_GLOBALS.SPLIT_OP THEN
751 	   IF nvl(p_group_number,-1) = -1 THEN
752 	     FND_MESSAGE.SET_NAME('ONT','OE_PC_SPLIT_NO_CONDN');
753 	   ELSE
754 	     FND_MESSAGE.SET_NAME('ONT','OE_PC_SPLIT_VIOLATION');
755 	   END IF;
756 	   FND_MESSAGE.SET_TOKEN('OBJECT',p_object_name);
757     END IF;
758 
759 END Set_Message;
760 
761 ---------------------------------------
762 PROCEDURE Add_Constraint_Message
763 (  p_application_id			IN NUMBER
764   ,p_database_object_name		IN VARCHAR2
765   ,p_column_name		IN VARCHAR2
766   ,p_operation			IN VARCHAR2
767   ,p_constraint_id		IN NUMBER
768   ,p_on_operation_action		IN NUMBER
769   ,p_group_number		IN NUMBER
770 )
771 -----------------------------------------------------
772 IS
773 -- Fix bug#1349549:
774 -- Increased l_attribute_name length to 240 as length
775 -- of column - NAME on AK_OBJECT_ATTRIBUTES_VL was increased
776 l_attribute_name	VARCHAR2(240);
777 l_reason			VARCHAR2(2000);
778 i				NUMBER := 0;
779 l_entity_code		VARCHAR2(30);
780 l_object_name		VARCHAR2(30);
781 l_reason_length	NUMBER;
782 l_operation		VARCHAR2(30);
783 l_column_name       VARCHAR2(30);
784 CURSOR CONDN IS
785 SELECT USER_MESSAGE msg
786   FROM OE_PC_CONDITIONS_VL
787   WHERE CONSTRAINT_ID = p_constraint_id
788     AND GROUP_NUMBER = p_group_number
789     AND ENABLED_FLAG = 'Y';  --for bug 9967228;
790    l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
791 BEGIN
792 
793   l_operation := p_operation;
794 
795   -- NOTE: This procedure currently adds a message to the stack
796   -- ONLY IF operation IS NOT ALLOWED (i.e. on_operation_action = 0)
797   -- For other user actions, this procedure will have to be extended
798   -- For the initial release, the only other supported user action
799   -- is REQUIRE REASON (on_operation_action = 1) but this is also
800   -- limited to CANCEL and Ordered Quantity UPDATE operations. Messages
801   -- for this will be added in the cancellations code. (OEXUCANB.pls)
802 
803   -- if operation is NOT allowed then set the constraint ID
804   -- on the message context
805   IF p_on_operation_action = 0 THEN
806 
807 	select o.entity_code, a.name
808 	into l_entity_code, l_object_name
809 	from oe_ak_objects_ext o, ak_objects_vl a
810 	where o.database_object_name = p_database_object_name
811 	  and o.application_id = p_application_id
812 	  and a.database_object_name = o.database_object_name
813 	  and a.application_id = o.application_id;
814 
815      IF l_operation = OE_PC_GLOBALS.CREATE_OP
816 	   OR l_operation = OE_PC_GLOBALS.UPDATE_OP
817      THEN
818 
819 	  SELECT c.constrained_operation, c.column_name
820 	  INTO l_operation, l_column_name
821 	  FROM oe_pc_constraints  c
822 	  WHERE c.constraint_id = p_constraint_id;
823 
824        IF l_column_name IS NOT NULL THEN
825             -- Bug 2721841, attribute_label_long is the translated
826             -- column and not the name column.
827 	    SELECT a.attribute_label_long
828 	    INTO l_attribute_name
829 	    FROM ak_object_attributes_vl a
830 	    WHERE column_name = l_column_name
831 		 AND database_object_name = p_database_object_name
832 		 AND attribute_application_id = p_application_id;
833        END IF;
834 
835      END IF;
836 
837     OE_MSG_PUB.Update_Msg_Context
838         ( p_entity_code		=> l_entity_code
839         , p_constraint_id	=> p_constraint_id );
840 
841     -- Set the attribute name, object name tokens.
842     -- And set the message on the message stack
843     -- appropriately based on the operation
844     -- This procedure does NOT set the reason token
845     Set_Message(p_operation		=> l_operation
846 			,p_group_number	=> p_group_number
847 			,p_attribute_name	=> l_attribute_name
848 			,p_object_name		=> l_object_name
849 			);
850 
851     -- Set the REASON token if a group of conditions (group_number exists)
852     -- resulted in this constraint violation
853     IF nvl(p_group_number,-1) <> -1 THEN
854 
855       -- Fix for bug1162361:
856       -- Message length can be at the maximum 2000 chars
857       -- therefore, estimate the maximum length for the reason
858       -- token by reducing the length of the message string
859       -- without the reason token
860       l_reason_length := 2000 - length(FND_MESSAGE.GET);
861 
862       -- Re-set the message , attribute name and object name tokens
863       -- on the stack as the previous call to FND_MESSAGE.GET would
864       -- have deleted the message from the stack.
865       Set_Message(p_operation		=> l_operation
866 			,p_group_number	=> p_group_number
867 			,p_attribute_name	=> l_attribute_name
868 			,p_object_name		=> l_object_name
869 			);
870 
871       -- construct the reason token based on the user message associated
872       -- with the constraining conditions
873       -- e.g. 'order booked
874       --       at least one line shipped '
875       FOR l_condn IN CONDN LOOP
876       IF i = 0 THEN
877         	l_reason := substr(l_condn.msg,1,l_reason_length);
878       ELSE
879         	l_reason := substr(l_reason||OE_PC_GLOBALS.NEWLINE||l_condn.msg,1,l_reason_length);
880       END IF;
881       i := i+1;
882       END LOOP;
883       FND_MESSAGE.SET_TOKEN('REASON',l_reason);
884 
885     END IF; --  Add REASON token Only if group_number(set of conditions exists)
886 
887     OE_MSG_PUB.ADD;
888 
889     -- set constraint ID to null on the message context.
890     OE_MSG_PUB.Update_Msg_Context
891         ( p_entity_code		=> l_entity_code
892         , p_constraint_id	=> null );
893 
894 
895    END IF;
896 
897 EXCEPTION
898    WHEN OTHERS THEN
899         IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
900         THEN
901             OE_MSG_PUB.Add_Exc_Msg
902             (   G_PKG_NAME
903             ,   'Add_Constraint_Message'
904             );
905         END IF;
906 	   RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
907 END Add_Constraint_Message;
908 
909 END Oe_PC_Constraints_Admin_Pub;