4 PROCEDURE create_parallel_hint(
1 PACKAGE BODY CN_COLLECTION_CUSTOM_GEN AS
2 -- $Header: cncusgenb.pls 120.23.12020000.2 2012/07/25 13:20:33 scannane ship $
3 ---------------- Private Procedures -------------------+
5 x_source_string IN VARCHAR2,
6 x_parallel_hint IN OUT NOCOPY VARCHAR2 )
7 IS
8 l_notification_tables VARCHAR2(1000);
9 l_notification_aliases VARCHAR2(1000);
10 l_comma_at NUMBER;
11 l_last_comma_at NUMBER;
12 l_space_at NUMBER;
13 BEGIN
14 l_notification_tables := x_source_string;
15 LOOP
16 l_last_comma_at := instr(l_notification_tables, ',',-1,1);
17 IF(l_last_comma_at =0) THEN
18 l_space_at := instr(l_notification_tables, ' ',1,1);
19 l_notification_aliases := SUBSTR(l_notification_tables,l_space_at+1, LENGTH(l_notification_tables)-l_space_at);
20 x_parallel_hint := x_parallel_hint || ' PARALLEL('||l_notification_aliases||')';
21 EXIT;
22 END IF;
23 l_notification_aliases := SUBSTR(l_notification_tables,l_last_comma_at+1, LENGTH(l_notification_tables)-l_last_comma_at);
24 l_space_at := instr(l_notification_aliases, ' ',1,1);
25 l_notification_aliases := SUBSTR(l_notification_aliases,l_space_at+1, LENGTH(l_notification_aliases)-l_space_at);
26 x_parallel_hint := x_parallel_hint || ' ' || 'PARALLEL('||l_notification_aliases||')';
27 l_notification_tables := SUBSTR(l_notification_tables,1, l_last_comma_at-1);
28 END LOOP;
29 x_parallel_hint := x_parallel_hint || ' *'||'/';
30 END create_parallel_hint;
31 ---------------- Public Procedures -------------------+
32 -- Procedure Name
33 -- insert_cn_not_trx
34 -- Purpose
35 -- This procedure generates the Notification code
36 -- History
37 -- 03-17-00 Dave Maskell Created for Release 11i2.
38 PROCEDURE insert_cn_not_trx(
39 x_table_map_id cn_table_maps.table_map_id%TYPE,
40 x_event_id cn_events.event_id%TYPE,
41 code IN OUT NOCOPY cn_utils.code_type,
42 x_org_id IN NUMBER)
43 IS
44 l_return_status VARCHAR2(4000);
45 l_msg_count NUMBER;
46 l_msg_data VARCHAR2(4000);
47 l_notify_from VARCHAR2(4000);
48 l_notify_where VARCHAR2(4000);
49 l_collect_from VARCHAR2(4000);
50 l_collect_where VARCHAR2(4000);
51 l_table_map_rec cn_table_maps_v%ROWTYPE;
52 l_header_key VARCHAR2(100);
53 l_line_key VARCHAR2(100);
54 l_org_id NUMBER;
55 l_parallel_hint VARCHAR2(4000) := '/'||'*'||'+';
56 CURSOR c_param
57 IS
58 SELECT tmov.object_id,
59 LOWER(OBJ.name) object_name
60 FROM cn_table_map_objects tmov,
61 cn_objects obj
62 WHERE tmov.table_map_id = x_table_map_id
63 AND tmov.tm_object_type = 'PARAM'
64 AND obj.object_id = tmov.object_id
65 AND tmov.org_id = obj.org_id
66 AND tmov.org_id = x_org_id;
67 -- Added For R12 MOAC Change
68 BEGIN
69 l_org_id := x_org_id;
70 -- Added For R12 MOAC Change
71 --+
72 -- Get the Table Map details for this data source
73 --+
74 SELECT *
75 INTO l_table_map_rec
76 FROM cn_table_maps_v
77 WHERE table_map_id = x_table_map_id
78 AND org_id = l_org_id; -- Added For R12 MOAC Change
79 --+
80 -- Get the name of the Line Table primary key
81 -- using the form 'alias.column_name'
82 --+
83 l_line_key := LOWER( NVL(l_table_map_rec.source_table_alias,l_table_map_rec.source_table_name)|| '.'||l_table_map_rec.linepk_name);
84 --+
85 -- Get the name of the Header Table primary key. There may be no value for
86 -- this, in which case we need to use the string 'NULL' for the purposes of
87 -- of our generated INSERT statement.
88 --+
89 IF l_table_map_rec.hdrpk_name IS NULL THEN
90 l_header_key := 'NULL';
91 ELSE
92 l_header_key := LOWER( NVL(l_table_map_rec.header_table_alias,l_table_map_rec.header_table_name)|| '.'||l_table_map_rec.hdrpk_name);
93 END IF;
97 cn_utils.appindcr(code, 'cn_message_pkg.debug(''Inserting new transactions into CN_NOT_TRX.'');');
94 cn_utils.set_org_id(p_org_id => l_org_id);
95 cn_utils.appendcr(code);
96 cn_utils.appendcr(code, '--******** NOTIFICATION PROCESSING *********-- ');
98 cn_utils.appindcr(code, 'fnd_file.put_line(fnd_file.Log,''Inserting new transactions into CN_NOT_TRX.'');');
99 cn_utils.appendcr(code);
100 --+
101 -- Most local variables for the collect procedure are part of a static set, which is generated
102 -- for all pacakges (inclding OC and AR) at the start of the procedure by the cn_collection_gen
103 -- package. However, for the notification query of Custom data sources, we need local variables
104 -- which have the names of the any parameters which have been specified by the user on the Queries
105 -- tab of the Collections form. To allow use to do this, we need to enclose the notification query
106 -- in its own block.
107 --+
108 cn_utils.appindcr(code, 'DECLARE -- Notification Insert Block');
109 --+
110 -- Generate Declarations of parameter variables
111 --+
112 cn_utils.indent(code, 1);
113 cn_utils.appindcr(code, '--*** Declaring user-defined Notification Parameters');
114 FOR rec IN c_param
115 LOOP
116 cn_utils.appindcr(code, rec.object_name||' cn_objects.object_value%TYPE;');
117 END LOOP;
118 cn_utils.unindent(code, 1);
119 cn_utils.appindcr(code, 'BEGIN');
120 --+
121 -- Generate code to initialise parameter variables
122 --+
123 cn_utils.indent(code, 1);
124 cn_utils.appindcr(code, '--*** Initializing user-defined Notification Parameters');
125 FOR rec IN c_param
126 LOOP
127 cn_utils.appindcr(code, 'SELECT object_value');
128 cn_utils.appindcr(code, 'INTO '||rec.object_name);
129 cn_utils.appindcr(code, 'FROM cn_objects');
130 cn_utils.appindcr(code, 'WHERE object_id = '||rec.object_id||' --*** Object_Id for '||rec.object_name);
131 cn_utils.appindcr(code, 'AND org_id = '||l_org_id||';');
132 cn_utils.appendcr(code);
133 END LOOP;
134 -- Added For R12 MOAC Change
135 cn_utils.appendcr(code);
136 --+
137 -- Insert any User Code specified for the 'Pre-Notification' location
138 --+
139 Generate_User_Code( p_table_map_id => x_table_map_id,
140 p_location_name => 'Pre-Notification',
141 code => code,
142 x_org_id => l_org_id);
143 --+
144 -- Generate the FROM and WHERE clauses. Changed position fo fix Bug 6203234
145 --+
146 cn_table_maps_pvt.get_sql_clauses( p_api_version => 1.0,
147 x_return_status => l_return_status,
148 x_msg_count => l_msg_count,
152 x_notify_where => l_notify_where,
149 x_msg_data => l_msg_data,
150 p_table_map_id => x_table_map_id,
151 x_notify_from => l_notify_from,
153 x_collect_from => l_collect_from,
154 x_collect_where => l_collect_where,
155 p_org_id => l_org_id);
156 --+
157 -- Changed to fix Bug 6203234 to include PDML in Notification Query
158 --
159 --+
160 IF CN_COLLECTION_GEN.isParallelEnabled THEN
161 create_parallel_hint(x_source_string => l_notify_from, x_parallel_hint => l_parallel_hint );
162 END IF;
163 --+
164 -- Generate the INSERT INTO API .. SELECT portion of
165 -- the statement
166 --+
167 cn_utils.set_org_id(l_org_id);
168 cn_utils.appindcr(code, '--');
169 cn_utils.appindcr(code, '-- Insert new lines into cn_not_trx');
170 cn_utils.appindcr(code, '--');
171 cn_utils.appindcr(code, '--CN_DB_PARALLEL_ENABLE :'||fnd_profile.value('CN_DB_PARALLEL_ENABLE'));
172 IF CN_COLLECTION_GEN.isParallelEnabled THEN
173 cn_utils.appindcr(code, 'INSERT /'||'*+ PARALLEL(cn_not_trx) */ INTO cn_not_trx (');
174 ELSE
175 cn_utils.appindcr(code, 'INSERT INTO cn_not_trx (');
176 END IF;
177 cn_utils.indent(code, 1);
178 cn_utils.appindcr(code, 'not_trx_id,');
179 cn_utils.appindcr(code, 'batch_id,');
180 cn_utils.appindcr(code, 'notified_date,');
181 cn_utils.appindcr(code, 'notification_run_id,');
182 cn_utils.appindcr(code, 'collected_flag,');
183 cn_utils.appindcr(code, 'event_id,');
184 cn_utils.appindcr(code, 'source_trx_id,');
185 cn_utils.appindcr(code, 'source_trx_line_id,');
186 cn_utils.appindcr(code, 'source_doc_type,');
187 cn_utils.appindcr(code, 'org_id)'); -- Added For R12 MOAC Change
188 cn_utils.unindent(code, 1);
189 --+
190 -- Generate the SELECT clause for the insert statement
191 --+
192 IF CN_COLLECTION_GEN.isParallelEnabled THEN
193 cn_utils.appindcr(code, 'SELECT '||l_parallel_hint);
194 ELSE
195 cn_utils.appindcr(code, 'SELECT ');
196 END IF;
197 cn_utils.indent(code, 1);
198 cn_utils.appindcr(code, 'cn_not_trx_s.NEXTVAL,');
199 -- Changed for Bug 6203234
200 --cn_utils.appindcr(code, 'FLOOR(cn_not_trx_s.CURRVAL/cn_global_var.g_system_batch_size),');
201 cn_utils.appindcr(code, 'x_proc_audit_id,');
202 cn_utils.appindcr(code, 'SYSDATE,');
203 cn_utils.appindcr(code, 'x_proc_audit_id,');
204 cn_utils.appindcr(code, '''N'',');
205 cn_utils.appindcr(code, x_event_id||',');
206 cn_utils.appindcr(code, l_header_key||', --*** Header Table Key Column');
207 cn_utils.appindcr(code, l_line_key||', --*** Line Table Key Column');
208 cn_utils.appindcr(code, ''''||l_table_map_rec.mapping_type||''', --*** Source Type');
209 cn_utils.appindcr(code, l_org_id); -- Added For R12 MOAC Change
210 cn_utils.unindent(code, 1);
211 cn_utils.appindcr(code, 'FROM --*** Line and (optional) Header Table');
212 cn_utils.indent(code, 1);
213 cn_utils.appindcr(code, l_notify_from);
214 cn_utils.unindent(code, 1);
215 cn_utils.appindcr(code, 'WHERE --*** Header.Primary_Key = Line.Foreign_Key');
216 cn_utils.indent(code, 1);
217 cn_utils.appindcr(code, l_notify_where);
218 --+
219 -- The generated WHERE clause does not include the supplementary
220 -- WHERE clause information that the user may specified, so this
221 -- needs to be added on.
222 -- Before doing so, strip off trailing ';' on the clause, if one
223 -- is present
224 --+
225 cn_utils.appindcr(code, '--*** Any extra user-defined WHERE statement lines');
226 IF l_table_map_rec.notify_where IS NOT NULL THEN
227 cn_utils.appindcr(code, 'AND '||RTRIM(l_table_map_rec.notify_where,';'));
228 END IF;
229 cn_utils.appindcr(code, '--*** End of user-defined WHERE statement lines');
230 --+
231 -- Finish off the statement
232 --+
233 cn_utils.appindcr(code, ' AND NOT EXISTS (');
234 IF CN_COLLECTION_GEN.isParallelEnabled THEN
235 cn_utils.appindcr(code, ' SELECT /'||'*'||'+ PARALLEL(cn_not_trx) '||'*/ 1');
236 ELSE
237 cn_utils.appindcr(code, ' SELECT 1');
238 END IF;
239 cn_utils.appindcr(code, ' FROM cn_not_trx');
240 cn_utils.appindcr(code, ' WHERE source_trx_line_id = ' || l_line_key ||' --*** Line.Primary_Key');
241 cn_utils.appindcr(code, ' AND event_id = ' || x_event_id );
242 cn_utils.appindcr(code, ' AND org_id = ' || l_org_id || ');');
243 cn_utils.unindent(code, 1);
244 cn_utils.appindcr(code, 'END; -- Notification Insert Block');
245 cn_utils.appendcr(code);
246 cn_utils.appindcr(code, '-- Commit the notification inserts - they are permanent even if collection fails');
247 cn_utils.appindcr(code, 'COMMIT;');
248 cn_utils.unset_org_id();
249 cn_message_pkg.debug('*** Finished notification run ');
250 fnd_file.put_line(fnd_file.Log,'*** Finished notification run ');
251 cn_utils.set_org_id(p_org_id => l_org_id);
252 cn_utils.appendcr(code);
253 cn_utils.appendcr(code, '--******** COLLECT AJUSTMENTS (NEGATE IN API) ********-- ');
254 cn_utils.appindcr(code, '-- This will negate those adjusted trx in the API table');
255 cn_utils.appindcr(code, 'cn_not_trx_grp.col_adjustments(p_api_version => 1.0,');
259 cn_utils.appindcr(code, ' p_org_id => '|| l_org_id ||');');
256 cn_utils.appindcr(code, ' x_return_status => x_return_status,');
257 cn_utils.appindcr(code, ' x_msg_count => x_msg_count,');
258 cn_utils.appindcr(code, ' x_msg_data => x_msg_data,');
260 -- Added For R12 MOAC Changes
261 cn_utils.appendcr(code);
262 cn_utils.unset_org_id();
263 END insert_cn_not_trx;
264 --
265 -- Procedure Name
266 -- insert_comm_lines_api_select
267 -- Purpose
268 -- This procedure uses the Direct Column Mappings to
269 -- generate the 'INSERT INTO cn_comm_lines_api VALUES (...) SELECT ...'
270 -- portion of the SQL statement wich populates the api table
271 --
272 -- History
273 -- 03-17-00 Dave Maskell Created for Release 11i2.
274 --
275 --
276 PROCEDURE insert_comm_lines_api_select(
277 x_table_map_id IN cn_table_maps_v.table_map_id%TYPE,
278 code IN OUT NOCOPY cn_utils.code_type,
279 x_org_id IN NUMBER,
280 x_parallel_hint IN VARCHAR2)
281 IS -- Added For R12 MOAC Changes
282 CURSOR api_direct_maps
283 IS
284 SELECT column_map_id,
285 expression,
286 cno.NAME dest_column
287 FROM cn_column_maps ccmv,
288 cn_objects cno
289 WHERE ccmv.table_map_id = x_table_map_id
290 AND ccmv.expression IS NOT NULL
291 AND ccmv.calc_ext_table_id IS NULL
292 AND ccmv.update_clause IS NULL
293 AND ccmv.org_id = x_org_id
294 AND ccmv.destination_column_id = cno.object_id
295 AND ccmv.org_id = cno.org_id
296 -- Added For R12 MOAC Changes
297 ORDER BY dest_column;
298 BEGIN
299 cn_utils.set_org_id(p_org_id => x_org_id);
300 IF CN_COLLECTION_GEN.isParallelEnabled THEN
301 IF x_table_map_id > 0 THEN
302 cn_utils.appindcr(code, 'INSERT /'||'*+ PARALLEL(cn_comm_lines_api) */ INTO cn_comm_lines_api (');
303 ELSE
304 cn_utils.appindcr(code, 'INSERT INTO cn_comm_lines_api (');
305 END IF;
306 ELSE
307 cn_utils.appindcr(code, 'INSERT INTO cn_comm_lines_api (');
308 END IF;
309 cn_utils.indent(code, 1);
310 cn_utils.appindcr(code, 'comm_lines_api_id,');
311 cn_utils.appindcr(code, 'conc_batch_id,');
312 cn_utils.appindcr(code, 'process_batch_id,');
313 cn_utils.appindcr(code, 'org_id,');
314 -- Insert custom columns using api_direct_maps cursor
315 cn_utils.appindcr(code, '--*** Direct Mapping Destination Columns');
316 FOR l IN api_direct_maps
317 LOOP
318 cn_utils.appindcr(code, l.dest_column || ',');
319 END LOOP;
320 --Changing the number of places to shift to 2 cos of introduction of new char
321 cn_utils.strip_prev(code, 1); -- remove trailing comma
322 cn_utils.appindcr(code, ')');
323 cn_utils.unindent(code, 1);
324 -- Generate the SELECT clause for the insert statement
325 IF CN_COLLECTION_GEN.isParallelEnabled THEN
326 cn_utils.appindcr(code, 'SELECT '||x_parallel_hint);
327 ELSE
328 cn_utils.appindcr(code, 'SELECT ');
329 END IF;
330 cn_utils.indent(code, 1);
331 cn_utils.appindcr(code, 'cn_comm_lines_api_s.NEXTVAL,');
332 cn_utils.appindcr(code, 'x_conc_program_id,');
333 cn_utils.appindcr(code, 'x_batch_id,');
334 cn_utils.appindcr(code, x_org_id||',');
335 -- Select other columns using the api_direct_maps cursors. Note that
336 -- we fetch the records in the cursor in the same order as before due to
337 -- the order by column_id in the cursor where clause.
338 cn_utils.appindcr(code, '--*** Direct Mapping Source Expressions');
339 FOR l IN api_direct_maps
340 LOOP
341 -- R12 Related Changes
342 IF (l.column_map_id = -1083) THEN
343 l.expression := RTRIM(l.expression,')},')||'),'||x_org_id||')';
344 END IF;
345 IF (l.column_map_id = -1092 OR l.column_map_id = -1093) THEN
346 l.expression := RTRIM(l.expression,')},')||','||x_org_id||')';
347 END IF;
348 cn_utils.appindcr(code, l.expression || ',');
349 END LOOP;
350 cn_utils.strip_prev(code, 1); -- remove trailing comma
351 cn_utils.appendcr(code);
352 cn_utils.unindent(code, 1);
353 cn_utils.unset_org_id();
354 END insert_comm_lines_api_select;
355 --
356 -- Procedure Name
357 -- insert_comm_lines_api
358 -- Purpose
359 -- This procedure generates the code to insert into the CN_COMM_LINES_API table
360 -- This includes the generation of the the code for the Notification Process
361 -- History
362 -- 03-17-00 Dave Maskell Created for Release 11i2.
363 --
364 --
365 PROCEDURE insert_comm_lines_api(
366 x_table_map_id cn_table_maps.table_map_id%TYPE,
367 x_event_id cn_events.event_id%TYPE,
368 code IN OUT NOCOPY cn_utils.code_type,
369 x_org_id IN NUMBER)
370 IS
371 l_return_status VARCHAR2(4000);
372 l_msg_count NUMBER;
373 l_msg_data VARCHAR2(4000);
374 l_notify_from VARCHAR2(4000);
375 l_notify_where VARCHAR2(4000);
376 l_collect_from VARCHAR2(4000);
377 l_collect_where VARCHAR2(4000);
378 l_table_map_rec cn_table_maps_v%ROWTYPE;
379 l_parallel_hint VARCHAR2(4000) := '/'||'*'||'+';
380 l_org_id NUMBER;
381 BEGIN
382 l_org_id := x_org_id;
383 --+
384 -- Get the Table Map details for this data source
385 --+
386 SELECT *
387 INTO l_table_map_rec
388 FROM cn_table_maps_v
389 WHERE table_map_id = x_table_map_id
390 AND org_id = l_org_id;
391 --+
392 -- Generate the FROM and WHERE clause code. Moved here to fix Bug 6203234
393 --+
394 cn_table_maps_pvt.get_sql_clauses( p_api_version => 1.0,
398 p_table_map_id => x_table_map_id,
395 x_return_status => l_return_status,
396 x_msg_count => l_msg_count,
397 x_msg_data => l_msg_data,
399 x_notify_from => l_notify_from,
400 x_notify_where => l_notify_where,
401 x_collect_from => l_collect_from,
402 x_collect_where => l_collect_where,
403 p_org_id => l_org_id);
404 --+
405 -- Changed to fix Bug 6203234 to include PDML in Collection Query
406 --
407 --+
408 IF CN_COLLECTION_GEN.isParallelEnabled THEN
409 create_parallel_hint(x_source_string => l_collect_from,
410 x_parallel_hint => l_parallel_hint );
411 END IF;
412 --+
413 -- Generate the Collection Process code
414 --+
415 cn_utils.set_org_id(p_org_id => X_org_id);
416 cn_utils.appendcr(code);
417 cn_utils.appendcr(code, '--******** INSERT CN_COMM_LINES_API *********-- ');
418 cn_utils.appindcr(code, 'cn_message_pkg.debug(''Inserting into CN_COMM_LINES_API.'');');
419 cn_utils.appindcr(code, 'fnd_file.put_line(fnd_file.Log, ''Inserting into CN_COMM_LINES_API.'');');
420 cn_utils.appendcr(code);
421 --+
422 -- Generate the INSERT INTO API .. SELECT portion of
423 -- the statement
424 --+
425 cn_utils.appindcr(code, '--');
426 cn_utils.appindcr(code, '-- Insert new lines into CN_COMM_LINES_API');
427 cn_utils.appindcr(code, '--');
428 cn_utils.unset_org_id();
429 -- Change for Bug 6203234
430 insert_comm_lines_api_select(x_table_map_id, code, X_org_id, l_parallel_hint);
431 cn_utils.set_org_id(p_org_id => l_org_id);
432 cn_utils.appindcr(code, 'FROM --*** Line, (optional) Header, and any Extra Collection Tables');
433 cn_utils.indent(code, 1);
434 cn_utils.appindcr(code, l_collect_from);
435 cn_utils.unindent(code, 1);
436 cn_utils.appindcr(code, 'WHERE');
437 cn_utils.appindcr(code, '--*** Line.Primary_Key = cnt.source_trx_line_id AND (optional) Header.Primary_Key = cnt.source_trx_id');
438 cn_utils.indent(code, 1);
439 cn_utils.appindcr(code, l_collect_where);
440 --+
441 -- The generated WHERE clause does not include the supplementary
442 -- WHERE clause information that the user may specified, so this
443 -- needs to be added on.
444 -- Before doing so, strip off trailing ';' on the clause, if one
445 -- is present
446 --+
447 cn_utils.appindcr(code, '--*** Any extra user-defined WHERE statement lines');
448 IF l_table_map_rec.collect_where IS NOT NULL THEN
449 cn_utils.appindcr(code, 'AND '||RTRIM(l_table_map_rec.collect_where,';'));
450 END IF;
451 cn_utils.appindcr(code, '--*** End of user-defined WHERE statement lines');
452 --+
453 -- Finish off the statement
454 --+
455 cn_utils.appindcr(code, ' AND cnt.event_id = ' || x_event_id);
456 cn_utils.appindcr(code, ' AND cnt.collected_flag = ''N''');
457 cn_utils.appindcr(code, ' AND cnt.batch_id = x_batch_id');
458 cn_utils.appindcr(code, ' AND cnt.org_id = '||l_org_id ||' ;');
459 cn_utils.unindent(code, 1);
460 cn_utils.appendcr(code);
461 cn_utils.appindcr(code, 'comm_lines_api_count := SQL%ROWCOUNT;');
462 cn_utils.appendcr(code);
463 cn_utils.appindcr(code, 'cn_message_pkg.debug(''Inserted '' || comm_lines_api_count || '' line records into CN_COMM_LINES_API.'');');
464 cn_utils.appindcr(code, 'fnd_file.put_line(fnd_file.Log,''Inserted '' || comm_lines_api_count || '' line records into CN_COMM_LINES_API.'');');
465 cn_utils.appendcr(code);
466 cn_utils.appendcr(code);
467 -- Update the collected_flag in CN_NOT_TRX
468 cn_utils.appindcr(code, 'cn_message_pkg.debug(''Updating collected_flag in CN_NOT_TRX .'');');
469 cn_utils.appindcr(code, 'fnd_file.put_line(fnd_file.Log,''Updating collected_flag in CN_NOT_TRX .'');');
470 cn_utils.appendcr(code);
471 IF CN_COLLECTION_GEN.isParallelEnabled THEN
472 cn_utils.appindcr(code, 'UPDATE /'||'*+ PARALLEL(cnt) */ cn_not_trx cnt');
473 ELSE
474 cn_utils.appindcr(code, 'UPDATE cn_not_trx cnt');
475 END IF;
476 cn_utils.appindcr(code, ' SET collected_flag = ''Y''');
477 cn_utils.appindcr(code, ' WHERE cnt.event_id = '|| x_event_id);
478 cn_utils.appindcr(code, ' AND cnt.collected_flag = ''N''');
479 cn_utils.appindcr(code, ' AND cnt.batch_id = x_batch_id');
480 cn_utils.appindcr(code, ' AND cnt.org_id = '||l_org_id||' ;');
481 cn_utils.appendcr(code);
482 cn_utils.appindcr(code, 'cn_message_pkg.debug(''Updated collected_flag in cn_not_trx.'');');
483 IF CN_COLLECTION_GEN.isParallelEnabled THEN
484 cn_utils.appindcr(code, 'COMMIT;');
485 END IF;
486 cn_utils.appendcr(code);
487 cn_utils.unset_org_id();
488 EXCEPTION
489 WHEN NO_DATA_FOUND THEN
490 cn_debug.print_msg('insert_lines: in exception handler for NO_DATA_FOUND',1);
491 fnd_file.put_line(fnd_file.Log, 'insert_lines: in exception handler for NO_DATA_FOUND');
492 RETURN;
493 END insert_comm_lines_api;
494 ---------------------------------------------------------- +
495 -- Procedure Name
496 -- update_comm_lines_api
497 --
498 -- Purpose
499 -- Generates code to update the CN_COMM_LINES_API table
500 -- using Indirect Mappings
501 -- History
502 -- 16-Mar-00 Dave Maskell Created
503 --
504 PROCEDURE update_comm_lines_api(
505 x_table_map_id cn_table_maps.table_map_id%TYPE,
506 code IN OUT NOCOPY cn_utils.code_type,
507 x_org_id IN NUMBER,
508 x_event_id IN NUMBER DEFAULT NULL,
509 x_generic IN BOOLEAN DEFAULT NULL)
510 IS
511 x_dest_table_name cn_obj_tables_v.name%TYPE;
512 x_dest_alias cn_obj_tables_v.alias%TYPE;
513 x_row_count NUMBER;
517 SELECT cm.expression,
514 x_count NUMBER;
515 CURSOR indmap
516 IS
518 cm.calc_ext_table_id,
519 cm.update_clause,
520 ext.name relationship_name,
521 LOWER(obj.NAME) external_table_name,
522 LOWER(NVL(ext.alias,obj.NAME)) external_table_alias,
523 LOWER(destcol.name) dest_column
524 FROM cn_column_maps cm,
525 cn_obj_columns_v destcol,
526 cn_calc_ext_tables ext,
527 cn_objects obj
528 WHERE cm.table_map_id = x_table_map_id
529 AND cm.expression IS NOT NULL
530 AND (cm.calc_ext_table_id IS NOT NULL
531 OR cm.update_clause IS NOT NULL)
532 AND ext.calc_ext_table_id(+) = cm.calc_ext_table_id
533 AND obj.object_id(+) = ext.external_table_id
534 AND destcol.column_id = cm.destination_column_id
535 -- make sure no old pre 11iv2 mappings to CN_TRX etc. can slip in
536 AND destcol.table_id = -1008 --CN_COMM_LINES_API
537 AND cm.org_id = x_org_id
538 AND destcol.org_id = cm.org_id
539 AND ext.org_id(+) = cm.org_id
540 AND obj.org_id(+) = ext.org_id
541 ORDER BY destcol.name;
542 BEGIN
543 -- MO_GLOBAL.INIT('CN');
544 cn_utils.set_org_id(x_org_id);
545 cn_utils.appendcr(code);
546 cn_utils.appendcr(code, '--******** UPDATE CN_COMM_LINES_API ********-- ');
547 cn_utils.appindcr(code, '--*** Update columns populated by all INDIRECT mappings');
548 --+
549 -- Get name and alias of destination table (should be 'cn_comm_lines_api' and 'api')
550 --+
551 -- SELECT
552 -- LOWER(ctmv.destination_table_name),
553 -- LOWER(NVL(ctmv.destination_table_alias,ctmv.destination_table_name))
554 -- INTO
555 -- x_dest_table_name,
556 -- x_dest_alias
557 -- FROM cn_table_maps_v ctmv
558 -- WHERE ctmv.table_map_id = x_table_map_id
559 -- AND org_id =x_org_id;
560 -- Code Change added by pradeep
561 SELECT LOWER(cB.name),
562 LOWER(NVL(cB.alias , cB.name))
563 INTO x_dest_table_name,
564 x_dest_alias
565 FROM cn_table_maps ctm,
566 CN_OBJECTS CB
567 WHERE ctm.table_map_id = x_table_map_id
568 AND CB.object_id = CTM.destination_table_id
569 AND cb.org_id = ctm.org_id
570 AND cb.org_id =x_org_id;
571 -- End of Code Change
572 --+
573 -- Generate the UPDATE statement
574 --+
575 -- Check for empty cursor before adding UPDATE command.
576 --+
577 x_row_count := 0;
578 FOR rec IN indmap
579 LOOP
580 x_row_count := SQL%ROWCOUNT ;
581 IF (x_row_count > 0) THEN
582 EXIT;
583 END IF;
584 END LOOP;
585 IF x_row_count >0 THEN
586 --+
587 -- There are some mappings so generate the statement.
588 --+
589 cn_utils.appindcr(code, 'UPDATE ' || x_dest_table_name || ' ' || x_dest_alias);
590 cn_utils.appindcr(code, ' SET');
591 cn_utils.indent(code, 1);
592 FOR indmap_rec IN indmap
593 LOOP
594 cn_utils.appindcr(code, indmap_rec.dest_column || ' = ( --*** Indirect Mapping Destination Column');
595 cn_utils.appindcr(code, ' SELECT ');
596 cn_utils.appindcr(code, ' ' || indmap_rec.expression ||' --*** Indirect Mapping Source Expression');
597 IF indmap_rec.external_table_alias IS NOT NULL THEN
598 --+
599 -- This mapping is based on a defined foreign key relationship
600 --+
601 cn_utils.appindcr(code, ' --*** FROM/WHERE derived from Relationship: '||indmap_rec.relationship_name);
602 cn_utils.appindcr(code, ' FROM '||indmap_rec.external_table_name||' '||indmap_rec.external_table_alias);
603 cn_utils.appindcr(code, ' WHERE');
604 x_count := 0;
605 --+
606 -- Build where clause based on all join columns in the relationship
607 --+
608 FOR indmap_fk IN
609 (SELECT LOWER(pkcol.name) pkcolumn_name,
610 LOWER(fkcol.name) fkcolumn_name
611 FROM cn_calc_ext_tbl_dtls dtls,
612 cn_obj_columns_v pkcol,
613 cn_obj_columns_v fkcol
614 WHERE dtls.calc_ext_table_id = indmap_rec.calc_ext_table_id
615 AND pkcol.column_id = dtls.external_column_id
616 AND fkcol.column_id = dtls.internal_column_id
617 AND dtls.org_id = x_org_id
618 AND pkcol.org_id = dtls.org_id
619 AND fkcol.org_id = pkcol.org_id
620 )
621 LOOP
622 IF x_count = 0 THEN
623 x_count := 1;
624 ELSE
625 cn_utils.appindcr(code, ' AND ');
626 END IF;
627 cn_utils.appindcr(code, ' '||indmap_rec.external_table_alias||'.'||indmap_fk.pkcolumn_name||' = api.'||indmap_fk.fkcolumn_name);
628 END LOOP;
629 --Added by Ashley for MOAC
630 cn_utils.appindcr(code, ' AND api.org_id = '||x_org_id);
631 cn_utils.appindcr(code, '),');
632 ELSE
633 --+
634 -- This mapping is based on a free-form update clause
635 --+
636 cn_utils.appindcr(code, ' --*** FROM/WHERE taken from Update Clause');
637 cn_utils.appindcr(code, ' '||indmap_rec.update_clause||'),');
638 END IF;
639 END LOOP;
640 cn_utils.strip_prev(code, 1); -- remove trailing comma
641 cn_utils.appendcr(code);
642 cn_utils.unindent(code, 1);
643
644 IF (x_generic OR x_event_id = cn_global.ord_event_id )
645 THEN
646 cn_utils.appindcr(code, 'WHERE ' || x_dest_alias || '.process_batch_id = x_batch_id');
647 ELSE
648 cn_utils.appindcr(code, 'WHERE ' || x_dest_alias || '.process_batch_id = x_proc_audit_id');
649 END IF;
650
651 --Added by Ashley for MOAC
655 --+
652 cn_utils.appindcr(code, 'AND ' || x_dest_alias || '.org_id = ' || x_org_id || ';');
653 cn_utils.appendcr(code);
654 END IF;
656 -- Generate code to update the comm_lines_api_update_count variable
657 --+
658 cn_utils.appendcr(code);
659 cn_utils.appindcr(code, 'cn_message_pkg.debug(''For all INDIRECT mappings updated '' || SQL%ROWCOUNT || '' rows in cn_comm_lines_api.'');');
660 cn_utils.appindcr(code, 'fnd_file.put_line(fnd_file.Log, ''For all INDIRECT mappings updated '' || SQL%ROWCOUNT || '' rows in cn_comm_lines_api.'');');
661 cn_utils.appendcr(code);
662 cn_utils.unset_org_id();
663 EXCEPTION
664 WHEN NO_DATA_FOUND THEN
665 cn_debug.print_msg('update_lines: in exception handler for NO_DATA_FOUND', 1);
666 fnd_file.put_line(fnd_file.Log, 'update_lines: in exception handler for NO_DATA_FOUND');
667 RETURN;
668 END update_comm_lines_api;
669 --
670 -- Procedure Name
671 -- filter_comm_lines_api
672 -- Purpose
673 -- This procedure generates the code to filter out unwanted
674 -- lines from cn_comm_lines_api
675 -- History
676 -- 03-29-00 Dave Maskell Created for Release 11i2.
677 --
678 --
679 PROCEDURE filter_comm_lines_api(
680 x_table_map_id cn_table_maps.table_map_id%TYPE,
681 code IN OUT NOCOPY cn_utils.code_type,
682 x_org_id IN NUMBER)
683 IS
684 l_delete_flag VARCHAR2(1);
685 l_statement VARCHAR2(100);
686 CURSOR c_filter
687 IS
688 SELECT OBJ.object_value
689 FROM cn_table_map_objects tmov,
690 cn_objects obj
691 WHERE tmov.table_map_id = x_table_map_id
692 AND tmov.tm_object_type = 'FILTER'
693 AND tmov.object_id = obj.object_id
694 AND tmov.org_id = obj.org_id
695 AND tmov.org_id = x_org_id;
696 BEGIN
697 --+
698 -- Decide whether to DELETE lines or just set them to 'FILTERED'
699 --+
700 SELECT delete_flag
701 INTO l_delete_flag
702 FROM cn_table_maps
703 WHERE table_map_id = x_table_map_id
704 AND org_id = x_org_id;
705 IF l_delete_flag = 'Y' THEN
706 l_statement := 'DELETE FROM cn_comm_lines_api api';
707 ELSE
708 l_statement := 'UPDATE cn_comm_lines_api api SET load_status = ''FILTERED''';
709 END IF;
710 cn_utils.set_org_id(x_org_id);
711 cn_utils.appendcr(code);
712 cn_utils.appendcr(code, '--******** FILTER PROCESSING *********-- ');
713 cn_utils.appindcr(code, 'cn_message_pkg.debug(''Filtering unwanted transactions from cn_comm_lines_api.'');');
714 cn_utils.appindcr(code, 'fnd_file.put_line(fnd_file.Log, ''Filtering unwanted transactions from cn_comm_lines_api.'');');
715 cn_utils.appendcr(code);
716 --+
717 -- Generate deletion code
718 --+
719 cn_utils.appindcr(code, '--*** User-defined filter statements');
720 FOR rec IN c_filter
721 LOOP
722 --+
723 -- Before doing so, strip off trailing ';' on the clause, if one
724 -- is present
725 --+
726 IF SUBSTR(rec.object_value,LENGTH(rec.object_value),1) = ';' THEN
727 rec.object_value := SUBSTR(rec.object_value,1,LENGTH(rec.object_value)-1);
728 END IF;
729 cn_utils.appindcr(code, l_statement);
730 cn_utils.appindcr(code, 'WHERE');
731 cn_utils.appindcr(code, ' '||rec.object_value);
732 cn_utils.appindcr(code, ' AND api.process_batch_id = x_batch_id;');
733 cn_utils.appendcr(code);
734 END LOOP;
735 cn_utils.appindcr(code, '--*** End of User-defined filter statements');
736 cn_utils.unset_org_id();
737 END filter_comm_lines_api;
738 --
739 -- Procedure Name
740 -- Generate_user_code
741 -- Purpose
742 -- Gets user-specificed code for a particular location and generates that code
743 -- History
744 -- 04-03-00 Dave Maskell Created
745 --
746 PROCEDURE Generate_User_Code(
747 p_table_map_id IN NUMBER,
748 p_location_name IN VARCHAR2,
749 code IN OUT NOCOPY cn_utils.code_type,
750 x_org_id IN NUMBER)
751 IS
752 l_location_name VARCHAR2(250);
753 CURSOR c_lookup
754 IS
755 SELECT meaning
756 FROM cn_lookups
757 WHERE lookup_type ='CN_COLL_TRIGGER_EVENTS'
758 AND enabled_flag ='Y'
759 AND lookup_code = 'PRN';
760 BEGIN
761 cn_utils.set_org_id(x_org_id);
762 cn_utils.appendcr(code);
763 cn_utils.appindcr(code, '--*** User Code, Location: '||p_location_name);
764 --+
765 -- For every line of User Code registered for this location
766 --+
767 IF (upper(p_location_name) = 'PRE-NOTIFICATION') THEN
768 OPEN c_lookup;
769 FETCH c_lookup
770 INTO l_location_name;
771 CLOSE c_lookup;
772 ELSE
773 l_location_name := p_location_name;
774 END IF;
775 FOR rec IN
776 (SELECT OBJ.object_value
777 FROM cn_table_map_objects tmov,
778 cn_objects obj
779 WHERE tmov.table_map_id = p_table_map_id
780 AND obj.object_id = tmov.object_id
781 AND tmov.org_id = obj.org_id
782 AND tmov.tm_object_type = 'USERCODE'
783 AND UPPER(OBJ.name) = UPPER(l_location_name)
784 AND tmov.org_id = x_org_id
785 ORDER BY table_map_object_id
786 )
787 LOOP
788 --+
789 -- Add a terminal ';' if none was registered
790 --+
791 IF rec.object_value LIKE '%;' THEN
792 cn_utils.appindcr(code, rec.object_value);
793 ELSE
794 cn_utils.appindcr(code, rec.object_value||';');
795 END IF;
796 END LOOP;
797 cn_utils.appindcr(code, '--*** End of User Code, Location: '||p_location_name);
798 cn_utils.appendcr(code);
799 cn_utils.unset_org_id();
800 END Generate_User_Code;
801 END cn_collection_custom_gen;
[Home] [Help]