[Home] [Help]
PACKAGE BODY: APPS.OE_PC_CONC_REQUESTS
Source
1 PACKAGE BODY OE_PC_Conc_Requests as
2 /* $Header: OEXPCRQB.pls 120.0 2005/05/31 23:37:04 appldev noship $ */
3
4 G_PKG_NAME constant varchar2(30) := 'OE_PC_Conc_Requests';
5
6 G_APPLSYS_SCHEMA varchar2(30);
7
8 g_conc_mode varchar2(1);
9
10
11 -------------------------------------
12 -- Local Procedure
13 -------------------------------------
14
15 -------------------------------------------------------------------------
16 PROCEDURE Put_Line
17 (Text Varchar2)
18 IS
19 BEGIN
20
21 if g_conc_mode is null then
22
23 if nvl(fnd_profile.value('CONC_REQUEST_ID'),0) <> 0 then
24 g_conc_mode := 'Y';
25 else
26 g_conc_mode := 'N';
27 end if;
28
29 end if;
30
31 if g_conc_mode = 'Y' then
32 FND_FILE.PUT_LINE( FND_FILE.LOG, Text);
33 end if;
34
35 END Put_Line;
36
37 ------------------------------------------------------------------------
38 PROCEDURE Init_Applsys_Schema
39 IS
40 l_app_info BOOLEAN;
41 l_status VARCHAR2(30);
42 l_industry VARCHAR2(30);
43 BEGIN
44
45 if g_applsys_schema is null then
46
47 l_app_info := FND_INSTALLATION.GET_APP_INFO
48 ('FND',l_status, l_industry, g_applsys_schema);
49
50 end if;
51
52 END;
53
54 ------------------------------------------------------------------------
55 PROCEDURE Create_Package_From_Buffer(
56 p_buffer long
57 ,p_pkg_name varchar2
58 ,p_is_pkg_body varchar2
59 )
60 Is
61
62 l_bufferLength number;
63 l_lengthToWrite number;
64 l_startIndex number;
65 l_breakIndex number;
66 l_line_number number;
67 n NUMBER := 0;
68 CURSOR errors IS
69 select line, text
70 from user_errors
71 where name = p_pkg_name
72 and type = decode(p_is_pkg_body,'FALSE','PACKAGE',
73 'TRUE','PACKAGE BODY');
74
75 begin
76 l_bufferLength := length(p_buffer);
77 l_lengthToWrite := l_bufferLength;
78 l_startIndex := 1;
79
80 l_line_number := 0;
81
82 while (l_lengthToWrite > 0) loop
83
84 l_breakIndex := instr(p_buffer, OE_PC_GLOBALS.NEWLINE,l_startIndex);
85
86 l_line_number := l_line_number + 1;
87
88 ad_ddl.build_package(substr(p_buffer, l_startIndex, (l_breakIndex-l_startIndex)),
89 l_line_number);
90 l_lengthToWrite := l_bufferLength - l_breakIndex;
91 l_startIndex := l_breakIndex+1;
92
93
94 end loop;
95
96 if p_is_pkg_body = 'FALSE' then
97 PUT_LINE('Create PACKAGE SPEC :'||
98 p_pkg_name||' using AD_DDL');
99 else
100 PUT_LINE('Create PACKAGE BODY :'||
101 p_pkg_name||' using AD_DDL');
102 end if;
103
104 ad_ddl.create_package
105 (applsys_schema => g_applsys_schema
106 ,application_short_name => 'ONT'
107 ,package_name => p_pkg_name
108 ,is_package_body => p_is_pkg_body
109 ,lb => 1
110 ,ub => l_line_number);
111
112 -- if there were any errors when creating this package, print out
113 -- the errors in the log file
114 FOR error IN errors LOOP
115 if n= 0 then
116 PUT_LINE('ERROR in creating PACKAGE :'||p_pkg_name);
117 end if;
118 PUT_LINE(
119 'Line :'||error.line||' Error:'||substr(error.text,1,200));
120 n := 1;
121 END LOOP;
122
123 -- if there was an error in compiling the package, raise
124 -- an error
125 if n > 0 then
126 RAISE FND_API.G_EXC_ERROR;
127 end if;
128
129 End Create_Package_From_Buffer;
130
131 /*
132 -- BUG 2935346
133 -- This is an obsolete procedure. The code is now executed directly from
134 -- procedure Create_Validation_Packages procedure.
135 -------------------------------------
136 PROCEDURE Update_Validation_Pkgs_Table
137 ( l_sql_stmt VARCHAR2
138 )
139 IS
140 BEGIN
141
142 PUT_LINE('Execute Control Tbl Insert/Update Script');
143
144 EXECUTE IMMEDIATE l_sql_stmt;
145
146 END Update_Validation_Pkgs_Table;
147 */
148
149 -------------------------------------
150 -- Create_Validation_Packages
151 -- Called by the concurrent program OEPCGEN
152 -------------------------------------
153 PROCEDURE Create_Validation_Packages
154 (ERRBUF OUT NOCOPY /* file.sql.39 change */ VARCHAR2
155 ,RETCODE OUT NOCOPY /* file.sql.39 change */ VARCHAR2
156 )
157 is
158 l_pkg_name varchar2(30);
159 l_control_tbl_sql varchar2(2000);
160 l_pkg_spec LONG;
161 l_pkg_body LONG;
162 l_return_status varchar2(1);
163 l_msg_data varchar2(255);
164 l_msg_count number;
165 l_concReqId number;
166 l_global_record_name varchar2(61);
167 -- SQL selects only the ones that needs to be generated (new) or regenerated.
168 -- based on timestamping of the corresponding records
169 -- Selective regeneration:
170 -- to regenrate all the the packages again, one could delete all the records in
171 -- OE_PC_VALIDATION_PKGS and run this procedure. To selectively generate
172 -- validation for a specific validation tmplt or a record set, one could touch
173 -- (update the last update date) of the corresponding records and run this
174 -- procedure.
175 CURSOR C_CR IS
176 SELECT distinct
177 ve.application_id,
178 ve.application_short_name,
179 ve.entity_id,
180 ve.db_object_name,
181 ve.entity_short_name,
182 ve.entity_display_name,
183 ve.validation_entity_id,
184 ve.validation_db_object_name,
185 ve.validation_entity_short_name,
186 ve.VALIDATION_ENTITY_DISPLAY_NAME,
187 vt.validation_tmplt_id,
188 vt.validation_tmplt_short_name,
189 vt.validation_tmplt_display_name,
190 rs.record_set_id,
191 rs.record_set_short_name,
192 rs.record_set_display_name
193 FROM oe_pc_vtmplts_vl vt,
194 oe_pc_rsets_vl rs,
195 oe_pc_ventities_v ve
196 WHERE ve.validation_entity_id = vt.entity_id
197 AND vt.validation_type <> 'API'
198 AND ve.validation_entity_id = rs.entity_id
199 -- Fix bug 1260054: if validating entity <> validation entity,
200 -- then generate packages only for the primary key record set
201 AND (ve.entity_id = ve.validation_entity_id
202 OR (ve.entity_id <> ve.validation_entity_id
203 AND rs.pk_record_set_flag = 'Y'))
204 AND (ve.entity_id, ve.validation_entity_id,
205 vt.validation_tmplt_id, rs.record_set_id)
206 NOT IN
207 (SELECT vp.validating_entity_id, vp.validation_entity_id,
208 vp.validation_tmplt_id, vp.record_set_id
209 FROM OE_PC_VALIDATION_PKGS vp
210 WHERE vp.validating_entity_id = ve.entity_id
211 AND vp.validation_entity_id = ve.validation_entity_id
212 AND vp.validation_tmplt_id = vt.validation_tmplt_id
213 AND vp.record_set_id = rs.record_set_id
214 AND vp.last_update_date > vt.last_update_date
215 AND vp.last_update_date > rs.last_update_date);
216 compile_file VARCHAR2(100);
217 compile_block VARCHAR2(200);
218 l_pkg_count NUMBER;
219 begin
220
221 Init_Applsys_Schema;
222
223 --
224 PUT_LINE( 'Create_Validation_Packages: BEGIN');
225 --
226
227 -- get the rules for which validation pkags need to be created
228 l_pkg_count := 0;
229
230 FOR pkg_rec IN C_CR LOOP
231
232 PUT_LINE(' ');
233 PUT_LINE( '-- Generating Stored Procedure for Record Set/Validation tmplt:-----');
234 PUT_LINE( 'Entity : ' || pkg_rec.entity_display_name);
235 PUT_LINE( 'Validation Entity : ' || pkg_rec.validation_entity_display_name);
236 PUT_LINE( 'Validation Tmplt : ' || pkg_rec.validation_tmplt_display_name);
237 PUT_LINE( 'Record Set : ' || pkg_rec.record_set_display_name);
238
239 l_global_record_name := 'OE_' || pkg_rec.entity_short_name || '_SECURITY.g_record';
240
241 l_pkg_name := '';
242 l_pkg_spec := '';
243 l_pkg_body := '';
244 l_control_tbl_sql := '';
245 l_return_status := '';
246 l_msg_data := '';
247 l_msg_count := '';
248
249 -- make the validation pkg PL/SQL and the update/inset SQL
250 OE_PC_Constraints_Admin_Pvt.Make_Validation_Pkg(
251 p_entity_id => pkg_rec.entity_id
252 ,p_entity_short_name => pkg_rec.entity_short_name
253 ,p_db_object_name => pkg_rec.db_object_name
254 ,p_validation_entity_id => pkg_rec.validation_entity_id
255 ,p_validation_entity_short_name => pkg_rec.validation_entity_short_name
256 ,p_validation_db_object_name => pkg_rec.validation_db_object_name
257 ,p_validation_tmplt_id => pkg_rec.validation_tmplt_id
258 ,p_validation_tmplt_short_name => pkg_rec.validation_tmplt_short_name
259 ,p_record_set_id => pkg_rec.record_set_id
260 ,p_record_set_short_name => pkg_rec.record_set_short_name
261 ,p_global_record_name => l_global_record_name
262 ,x_pkg_name => l_pkg_name
263 ,x_pkg_spec => l_pkg_spec
264 ,x_pkg_body => l_pkg_body
265 ,x_control_tbl_sql => l_control_tbl_sql
266 ,x_return_status => l_return_status
267 ,x_msg_data => l_msg_data
268 ,x_msg_count => l_msg_count);
269
270 IF (l_return_status = fnd_api.G_RET_STS_SUCCESS)
271 THEN
272
273 PUT_LINE( 'Successfully generated Validation Package');
274
275 BEGIN
276
277 Create_Package_From_Buffer(l_pkg_spec, l_pkg_name, 'FALSE');
278 Create_Package_From_Buffer(l_pkg_body, l_pkg_name, 'TRUE');
279
280 -- PUT_LINE(l_control_tbl_sql);
281
282 -- BUG 2935346
283 -- Execute control tbl sql using bind values.
284
285 IF substr(l_control_tbl_sql,1,6) = 'INSERT' THEN
286
287 PUT_LINE('Execute Control Tbl Insert Script');
288 EXECUTE IMMEDIATE l_control_tbl_sql
289 USING pkg_rec.entity_id
290 , pkg_rec.validation_entity_id
291 , pkg_rec.validation_tmplt_id
292 , pkg_rec.record_set_id
293 , l_pkg_name
294 , l_pkg_name;
295
296 ELSIF substr(l_control_tbl_sql,1,6) = 'UPDATE' THEN
297
298 PUT_LINE('Execute Control Tbl Update Script');
299 EXECUTE IMMEDIATE l_control_tbl_sql
300 USING pkg_rec.entity_id
301 , pkg_rec.validation_entity_id
302 , pkg_rec.validation_tmplt_id
303 , pkg_rec.record_set_id
304 , l_pkg_name;
305
306 END IF;
307
308 -- Continue creating other validation pkgs even if there were errors
309 -- when creating this package
310 EXCEPTION
311 WHEN FND_API.G_EXC_ERROR THEN
312 NULL;
313 END;
314
315 ELSE
316
317 FND_MSG_PUB.Count_And_Get(p_count => l_msg_count
318 , p_data => l_msg_data);
319
320 PUT_LINE( 'Error in Generating the Validation Package :'||l_msg_data);
321
322 END IF;
323
324 END LOOP;
325
326 PUT_LINE(' ');
327 PUT_LINE( 'Returning with SUCCESS');
328
329 retcode := 0;
330
331 EXCEPTION
332 when FND_API.G_EXC_ERROR then
333 retcode := 2;
334 errbuf := 'Please fix the error in the log file';
335 PUT_LINE(
336 'Please fix the errors in this log file and re-run the concurrent program.');
337 when others then
338 retcode := 2;
339 errbuf := sqlerrm;
340 PUT_LINE(
341 'An Exception has occured. Returning with ERROR :'||sqlerrm);
342 END Create_Validation_Packages;
343
344
345 END OE_PC_Conc_Requests;